租房统计查询修改

zhongrj

2023-11-09 adec60ee0cac3c28995df5b15d88b8f761cf2655

租房统计查询修改

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

@@ -158,9 +158,13 @@
        return R.status(houseRentalService.removeHouseRental(id));
    }

    /**
     * 获取统计数据
     * @return
     */
    @GetMapping("/getStatistics")
    public R getStatistics(){
        return R.data(houseRentalService.getStatistics());
    public R getStatistics(HouseRentalTenantVO houseRental){
        return R.data(houseRentalService.getStatistics(houseRental));
    }



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

@@ -50,5 +50,9 @@
     */
    List<HouseRentalVO> getHouseRentalListByCode(@Param("code") String code);

    List<HouseRentalStatistics> getStatistics();
    /**
     * 获取统计数据
     * @return
     */
    List<HouseRentalStatistics> getStatistics(@Param("vo") HouseRentalTenantVO houseRental);
}

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

@@ -31,29 +31,55 @@
        </collection>
    </resultMap>


    <!--自定义列表查询-->
    <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
        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 test="vo.auditStatus != null and vo.auditStatus != ''">
            <if test="vo.auditStatus ==0 || vo.auditStatus ==1">
                AND jhr.audit_status  = #{vo.auditStatus}
            </if>
            <if test="vo.auditStatus ==10">
                AND date_format(jhr.due_time,'%Y-%m-%d')&gt;= date_format(now(),'%Y-%m-%d')
            </if>
            <if test="vo.auditStatus ==20">
                AND TIMESTAMPDIFF( day, now(), jhr.due_time )&lt;30
            </if>
            <if test="vo.auditStatus ==30">
                AND date_format(jhr.due_time,'%Y-%m-%d')&lt; date_format(now(),'%Y-%m-%d')
            </if>
        </if>
        <if test="vo.tenantName != null and vo.tenantName != ''">
            AND b.tenantName LIKE CONCAT('%',#{vo.tenantName},'%')
        </if>
        <if test="vo.tenantName != null and vo.tenantName != ''">
            AND b.tenantName LIKE CONCAT('%',#{vo.tenantName},'%')
        </if>
        <if test="vo.dldType != null">
            <if test="vo.dldType ==1 ">
                AND TIMESTAMPDIFF( MONTH, jhr.rental_time, jhr.due_time )>= 8
            </if>
            <if test="vo.dldType ==2 ">
                AND  4 &lt;= TIMESTAMPDIFF( MONTH, jhr.rental_time, jhr.due_time )
                AND TIMESTAMPDIFF( MONTH, jhr.rental_time, jhr.due_time )&lt;=8
            </if>
            <if test="vo.dldType ==3 ">
                AND TIMESTAMPDIFF( MONTH, jhr.rental_time, jhr.due_time )&lt;4
            </if>
        </if>
    </select>

@@ -72,46 +98,48 @@

    <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
            'longTerm' as term,count(1) total,ifnull(sum(num),0) as personNum
            FROM jczz_house_rental jhr
            LEFT JOIN
            (
                select housing_rental_id,count(*) num from jczz_house_tenant where is_deleted = 0 GROUP BY housing_rental_id
            ) jht
            ON jht.housing_rental_id = jhr.id
            WHERE jhr.is_deleted = 0
            AND TIMESTAMPDIFF( MONTH, jhr.rental_time, jhr.due_time )>= 8
            <if test="vo.auditStatus != null and vo.auditStatus != '' or vo.auditStatus == 0 ">
                AND jhr.audit_status  = #{vo.auditStatus}
            </if>

            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
            SELECT 'middleTerm' AS term,count(1) total,ifnull(sum(num),0) as personNum
            FROM jczz_house_rental jhr
            LEFT JOIN
            (
            select housing_rental_id,count(*) num from jczz_house_tenant where is_deleted = 0 GROUP BY housing_rental_id
            ) jht
            ON jht.housing_rental_id = jhr.id
            WHERE jhr.is_deleted = 0
            AND  4 &lt;= TIMESTAMPDIFF( MONTH, rental_time, due_time ) AND TIMESTAMPDIFF( MONTH, rental_time, due_time )&lt;=8
            <if test="vo.auditStatus != null and vo.auditStatus != '' or vo.auditStatus == 0 ">
                AND jhr.audit_status  = #{vo.auditStatus}
            </if>

            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 'shortTerm' AS term,count(1) total,ifnull(sum(num),0) as personNum
            FROM jczz_house_rental jhr
            LEFT JOIN
            (
            select housing_rental_id,count(*) num from jczz_house_tenant where is_deleted = 0 GROUP BY housing_rental_id
            ) jht
            ON jht.housing_rental_id = jhr.id
            WHERE jhr.is_deleted = 0
            AND TIMESTAMPDIFF( MONTH, rental_time, due_time )&lt;4
            <if test="vo.auditStatus != null and vo.auditStatus != '' or vo.auditStatus == 0 ">
                AND jhr.audit_status  = #{vo.auditStatus}
            </if>
    </select>



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

@@ -71,5 +71,9 @@
     */
    Boolean updateHouseRental(HouseRentalVO houseRental);

    List<HouseRentalStatistics> getStatistics();
    /**
     * 获取统计数据
     * @return
     */
    Object getStatistics(HouseRentalTenantVO houseRental);
}

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

@@ -36,6 +36,7 @@
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
@@ -189,8 +190,15 @@
        return update && addFlag && updateFlag && removeFlag;
    }

    /**
     * 获取统计数据
     * @return
     */
    @Override
    public List<HouseRentalStatistics> getStatistics() {
        return baseMapper.getStatistics();
    public Object getStatistics(HouseRentalTenantVO houseRental) {
        // 查询
        List<HouseRentalStatistics> statistics = baseMapper.getStatistics(houseRental);
        // 返回
        return statistics;
    }
}

 src/main/java/org/springblade/modules/place/service/impl/PlaceExtServiceImpl.java

@@ -85,12 +85,14 @@
        boolean flag = false;
        // 设置更新时间
        placeExt.setConfirmTime(new Date());
        placeExt.setConfirmUserId(AuthUtil.getUserId());
        // 更新数据
        boolean b = updateById(placeExt);
        if (b) {
            PlaceExtEntity entity = getById(placeExt.getId());
            // 更新任务表状态
            TaskEntity taskEntity = new TaskEntity();
            taskEntity.setId(placeExt.getTaskId());
            taskEntity.setId(entity.getTaskId());
            taskEntity.setStatus(placeExt.getConfirmFlag());
            flag = taskService.updateById(taskEntity);
        }

 src/main/java/org/springblade/modules/task/service/impl/TaskReportForRepairsServiceImpl.java

@@ -28,6 +28,7 @@
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.baomidou.mybatisplus.core.metadata.IPage;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;

@@ -107,16 +108,19 @@
     * @return
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean checkReportForRepairs(TaskReportForRepairsEntity taskReportForRepairs) {
        boolean flag = false;
        // 设置更新时间
        taskReportForRepairs.setConfirmTime(new Date());
        taskReportForRepairs.setConfirmUserId(AuthUtil.getUserId());
        // 更新数据
        boolean b = updateById(taskReportForRepairs);
        if (b) {
            TaskReportForRepairsEntity entity = getById(taskReportForRepairs.getId());
            // 更新任务表状态
            TaskEntity taskEntity = new TaskEntity();
            taskEntity.setId(taskReportForRepairs.getTaskId());
            taskEntity.setId(entity.getTaskId());
            taskEntity.setStatus(taskReportForRepairs.getConfirmFlag());
            flag = taskService.updateById(taskEntity);
        }