查询列表

guoshilong

2023-11-07 1bf80aaf74322307f0dadd7023b0802b7858b30c

查询列表

 src/main/java/org/springblade/modules/house/controller/HouseRentalController.java

@@ -29,6 +29,7 @@
import org.springblade.core.secure.utils.AuthUtil;
import org.springblade.core.tool.api.R;
import org.springblade.core.tool.utils.Func;
import org.springblade.modules.house.vo.HouseRentalTenantVO;
import org.springframework.web.bind.annotation.*;
import com.baomidou.mybatisplus.core.metadata.IPage;
import org.springblade.modules.house.entity.HouseRentalEntity;
@@ -80,8 +81,8 @@
    @GetMapping("/page")
    @ApiOperationSupport(order = 3)
    @ApiOperation(value = "分页", notes = "传入houseRental")
    public R<IPage<HouseRentalVO>> page(HouseRentalVO houseRental, Query query) {
        IPage<HouseRentalVO> pages = houseRentalService.selectHouseRentalPage(Condition.getPage(query), houseRental);
    public R<IPage<HouseRentalTenantVO>> page(HouseRentalTenantVO houseRental, Query query) {
        IPage<HouseRentalTenantVO> pages = houseRentalService.selectHouseRentalPage(Condition.getPage(query), houseRental);
        return R.data(pages);
    }

@@ -157,5 +158,10 @@
        return R.status(houseRentalService.removeHouseRental(id));
    }

    @GetMapping("/getStatistics")
    public R getStatistics(){
        return R.data(houseRentalService.getStatistics());
    }


}

 src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.java

@@ -16,8 +16,11 @@
 */
package org.springblade.modules.house.mapper;

import liquibase.pro.packaged.P;
import org.apache.ibatis.annotations.Param;
import org.springblade.modules.house.entity.HouseRentalEntity;
import org.springblade.modules.house.vo.HouseRentalStatistics;
import org.springblade.modules.house.vo.HouseRentalTenantVO;
import org.springblade.modules.house.vo.HouseRentalVO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
@@ -38,7 +41,7 @@
     * @param houseRental
     * @return
     */
    List<HouseRentalVO> selectHouseRentalPage(IPage page, HouseRentalVO houseRental);
    List<HouseRentalTenantVO> selectHouseRentalPage(IPage page, @Param("vo") HouseRentalTenantVO houseRental);

    /**
     * 查询房屋出租情况
@@ -46,4 +49,6 @@
     * @return
     */
    List<HouseRentalVO> getHouseRentalListByCode(@Param("code") String code);

    List<HouseRentalStatistics> getStatistics();
}

 src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.xml

@@ -32,8 +32,29 @@
    </resultMap>


    <select id="selectHouseRentalPage" resultMap="houseRentalResultMap">
        select * from jczz_house_rental where is_deleted = 0
    <select id="selectHouseRentalPage" resultType="org.springblade.modules.house.vo.HouseRentalTenantVO">
        SELECT jhr.*,jda.address_name as houseName,b.tenantName,b.phone
        FROM jczz_house_rental jhr
                 LEFT JOIN jczz_doorplate_address jda ON jda.address_code = jhr.house_code
                 LEFT JOIN (
                    SELECT jht.housing_rental_id,jht.name as tenantName,jht.phone
                    FROM jczz_house_tenant jht RIGHT JOIN (
                    SELECT MAX(ID) as id,housing_rental_id
                    FROM jczz_house_tenant
                    WHERE is_deleted = 0
                        <if test="vo.tenantName != null and vo.tenantName != ''">
                            AND name LIKE CONCAT('%',#{vo.tenantName},'%')
                        </if>
                    GROUP BY  housing_rental_id
                    ) a ON a.id = jht.id
        ) b ON b.housing_rental_id = jhr.id
        WHERE jhr.is_deleted = 0
        <if test="vo.auditStatus != null and vo.auditStatus != '' or vo.auditStatus == 0 ">
            AND jhr.audit_status  = #{vo.auditStatus}
        </if>
        <if test="vo.tenantName != null and vo.tenantName != ''">
            AND b.tenantName LIKE CONCAT('%',#{vo.tenantName},'%')
        </if>
    </select>

    <!--查询房屋出租情况-->
@@ -49,5 +70,49 @@
        and house_code = #{code}
    </select>

    <select id="getStatistics" resultType="org.springblade.modules.house.vo.HouseRentalStatistics">
            SELECT
                'longTerm' AS term,COUNT( 1 ) total,temp.personNum
            FROM(
                    SELECT COUNT( 1 ) personNum
                    FROM jczz_house_rental jhr
                        LEFT JOIN jczz_house_tenant jht ON jht.housing_rental_id = jhr.id
                    WHERE
                        TIMESTAMPDIFF( MONTH, jhr.rental_time, jhr.due_time )>= 8
                      AND jhr.is_deleted = 0  and jht.is_deleted = 0
                    GROUP BY jht.housing_rental_id
                ) TEMP
            GROUP BY TEMP.personNum

            UNION ALL

            SELECT 'middleTerm' AS term,COUNT( 1 ) total, temp.personNum
            FROM(
                    SELECT COUNT( 1 ) personNum
                    FROM jczz_house_rental jhr
                             LEFT JOIN jczz_house_tenant jht ON jht.housing_rental_id = jhr.id
                    WHERE
                        4 &lt;= TIMESTAMPDIFF( MONTH, rental_time, due_time )
                      AND TIMESTAMPDIFF( MONTH, rental_time, due_time )&lt;=8
                      AND jhr.is_deleted = 0  and jht.is_deleted = 0
                    GROUP BY jht.housing_rental_id
                ) TEMP
            GROUP BY TEMP.personNum

            UNION ALL

            SELECT 'shortTerm' AS term,COUNT( 1 ) total,temp.personNum
            FROM(
                    SELECT COUNT( 1 ) personNum
                    FROM jczz_house_rental jhr
                             LEFT JOIN jczz_house_tenant jht ON jht.housing_rental_id = jhr.id
                    WHERE
                        TIMESTAMPDIFF( MONTH, rental_time, due_time )&lt;4
                      AND jhr.is_deleted = 0  and jht.is_deleted = 0
                    GROUP BY jht.housing_rental_id
                ) TEMP
            GROUP BY TEMP.personNum
    </select>


</mapper>

 src/main/java/org/springblade/modules/house/service/IHouseRentalService.java

@@ -18,6 +18,8 @@

import com.baomidou.mybatisplus.extension.service.IService;
import org.springblade.modules.house.entity.HouseRentalEntity;
import org.springblade.modules.house.vo.HouseRentalStatistics;
import org.springblade.modules.house.vo.HouseRentalTenantVO;
import org.springblade.modules.house.vo.HouseRentalVO;
import org.springblade.core.mp.base.BaseService;
import com.baomidou.mybatisplus.core.metadata.IPage;
@@ -39,7 +41,7 @@
     * @param houseRental
     * @return
     */
    IPage<HouseRentalVO> selectHouseRentalPage(IPage<HouseRentalVO> page, HouseRentalVO houseRental);
    IPage<HouseRentalTenantVO> selectHouseRentalPage(IPage<HouseRentalTenantVO> page, HouseRentalTenantVO houseRental);

    /**
     * 查询房屋出租情况
@@ -68,4 +70,6 @@
     * @return
     */
    Boolean updateHouseRental(HouseRentalVO houseRental);

    List<HouseRentalStatistics> getStatistics();
}

 src/main/java/org/springblade/modules/house/service/impl/HouseRentalServiceImpl.java

@@ -22,10 +22,11 @@
import org.springblade.modules.house.entity.HouseRentalEntity;
import org.springblade.modules.house.entity.HouseTenantEntity;
import org.springblade.modules.house.service.IHouseTenantService;
import org.springblade.modules.house.vo.HouseRentalStatistics;
import org.springblade.modules.house.vo.HouseRentalTenantVO;
import org.springblade.modules.house.vo.HouseRentalVO;
import org.springblade.modules.house.mapper.HouseRentalMapper;
import org.springblade.modules.house.service.IHouseRentalService;
import org.springblade.core.mp.base.BaseServiceImpl;
import org.springblade.modules.house.vo.HouseTenantVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@@ -34,7 +35,6 @@

import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.stream.Collectors;

@@ -57,7 +57,7 @@
     * @return
     */
    @Override
    public IPage<HouseRentalVO> selectHouseRentalPage(IPage<HouseRentalVO> page, HouseRentalVO houseRental) {
    public IPage<HouseRentalTenantVO> selectHouseRentalPage(IPage<HouseRentalTenantVO> page, HouseRentalTenantVO houseRental) {
        return page.setRecords(baseMapper.selectHouseRentalPage(page, houseRental));
    }

@@ -188,4 +188,9 @@
        // 返回
        return update && addFlag && updateFlag && removeFlag;
    }

    @Override
    public List<HouseRentalStatistics> getStatistics() {
        return baseMapper.getStatistics();
    }
}

 src/main/java/org/springblade/modules/house/vo/HouseRentalStatistics.java

New file
@@ -0,0 +1,19 @@
package org.springblade.modules.house.vo;

import lombok.Data;

/**
 * 房屋出租统计
 */
@Data
public class HouseRentalStatistics {

    //周期
    private String term;

    //户数
    private Integer total;

    //一户的人数
    private Integer personNum;
}

 src/main/java/org/springblade/modules/house/vo/HouseRentalTenantVO.java

New file
@@ -0,0 +1,25 @@
package org.springblade.modules.house.vo;

import lombok.Data;
import org.springblade.modules.house.entity.HouseRentalEntity;
import org.springblade.modules.house.entity.HouseTenantEntity;

import java.util.List;

/**
 * 房屋租户VO
 */
@Data
public class HouseRentalTenantVO extends HouseRentalEntity {

    //租户名
    private String tenantName;

    //房屋名
    private String houseName;

    //电话
    private String phone;


}

 src/main/java/org/springblade/modules/task/mapper/TaskReportForRepairsMapper.xml

@@ -30,28 +30,12 @@
    </select>
    <select id="getStatisticsCount" resultType="org.springblade.modules.task.vo.TaskReportStatistics">
        SELECT
            CASE
                TEMP.TOTAL
                WHEN TEMP.TOTAL != 0 THEN
                    TEMP.TOTAL ELSE 0
                END AS TOTAL,
            CASE
                TEMP.HANDLE
                WHEN TEMP.HANDLE != 0 THEN
                    TEMP.HANDLE ELSE 0
                END AS HANDLE
            COUNT( 1 ) AS total,
            SUM( CASE WHEN STATUS = 10 THEN 1 ELSE 0 END ) AS handle
        FROM
            (
                SELECT
                    COUNT( 1 ) AS total,
                    SUM( CASE WHEN STATUS = 10 THEN 1 ELSE 0 END ) AS handle
                FROM
                    jczz_task_report_for_repairs
                WHERE
                    is_deleted = 0
            ) TEMP


            jczz_task_report_for_repairs
        WHERE
            is_deleted = 0
    </select>