房屋数据统计优化调整

zhongrj

2024-04-29 b055c0c7bcf9532849dee6e529df8dc454203d1a

房屋数据统计优化调整

 src/main/java/org/springblade/modules/discuss/service/impl/UserTopicsServiceImpl.java

@@ -320,8 +320,6 @@
        excelWriter.write(publicopinion, classExcelSheet.build());
        // 完成Excel写入并释放资源
        excelWriter.finish();


    }

    /**

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

@@ -80,7 +80,14 @@

    Integer getHouseStatisticsOne(String code, Long userId, String aoiCode, String buildingCode, String unitCode, String roleType);

    Integer getHouseStatisticsTwo(String code, Long userId, String aoiCode, String buildingCode, String unitCode, String roleType);
    Integer getHouseStatisticsTwo(@Param("code") String code,
                                  @Param("aoiCode") String aoiCode,
                                  @Param("buildingCode") String buildingCode,
                                  @Param("unitCode") String unitCode,
                                  @Param("house") HouseVO house,
                                  @Param("isAdministrator") Integer isAdministrator,
                                  @Param("regionChildCodesList")List<String> regionChildCodesList,
                                  @Param("gridCodeList")List<String> gridCodeList);

    Integer getHouseStatisticsThree(String code, Long userId, String aoiCode, String buildingCode, String unitCode, String roleType);


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

@@ -450,55 +450,91 @@

    <select id="getHouseStatisticsTwo" resultType="java.lang.Integer">
        SELECT
        count( 1 )
        count(*)
        FROM
        (
        SELECT DISTINCT
        jda.address_code
        FROM
        jczz_house jh
        LEFT JOIN jczz_doorplate_address jda ON jda.address_code = jh.house_code
        jczz_doorplate_address jda
        LEFT JOIN jczz_house jh ON jda.address_code = jh.house_code AND jh.is_deleted = 0
        LEFT JOIN jczz_grid jg on jg.grid_code = jh.grid_code and jg.is_deleted = 0
        LEFT JOIN jczz_police_affairs_grid jpag on jh.jw_grid_code= jpag.jw_grid_code and jpag.is_deleted = 0
        WHERE
        jda.nei_code = #{code}
        AND jh.is_deleted = 0
        and jda.doorplate_type = '户室牌'
        <if test="buildingCode != null  and buildingCode != ''">
            and jda.building_code=#{buildingCode}
        </if>

        <if test="unitCode != null  and unitCode != ''">
            and jda.unit_code=#{unitCode}
            AND jda.unit_code is not null
        </if>

        <if test="aoiCode != null  and aoiCode != ''">
            and jda.aoi_code=#{aoiCode}
        </if>
        <if test="userId != null and roleType == '1'">
            AND jda.address_code IN (
            SELECT DISTINCT
            jgr.house_code
            FROM
            jczz_grid jg
            LEFT JOIN jczz_gridman jgm ON jg.id = jgm.grid_id
            LEFT JOIN jczz_grid_range jgr ON jgr.grid_id = jg.id
            WHERE
            jgm.user_id = #{userId}
            AND jg.is_deleted = 0
            )
        </if>
        <if test="userId != null and roleType == '3'">
            AND jda.address_code IN (SELECT
            jda.address_code
            FROM
            jczz_doorplate_address jda
            LEFT JOIN jczz_community jc ON jc.CODE = jda.nei_code
            WHERE
            jc.res_police_user_id like concat('%',#{userId},'%'))
            )
        </if>
        ) a

        <choose>
            <when test="(buildingCode != null  and buildingCode != '') or
                (unitCode != null  and unitCode != '') or (aoiCode != null  and aoiCode != '')">
                <if test="buildingCode != null  and buildingCode != ''">
                    and jda.building_code=#{buildingCode}
                </if>
                <if test="unitCode != null  and unitCode != ''">
                    and jda.unit_code=#{unitCode}
                    AND jda.unit_code is not null
                </if>
                <if test="aoiCode != null  and aoiCode != ''">
                    and jda.aoi_code=#{aoiCode}
                </if>
            </when>
            <otherwise>
                <if test="isAdministrator==2">
                    <choose>
                        <when test="house.roleName != null and house.roleName != ''">
                            <if test="house.roleName=='wgy'">
                                <choose>
                                    <when test="gridCodeList !=null and gridCodeList.size()>0">
                                        and jh.grid_code in
                                        <foreach collection="gridCodeList" item="code" open="(" close=")" separator=",">
                                            #{code}
                                        </foreach>
                                    </when>
                                    <otherwise>
                                        and jh.grid_code in ('')
                                    </otherwise>
                                </choose>
                            </if>
                            <if test="house.roleName=='mj'">
                                <choose>
                                    <when test="regionChildCodesList !=null and regionChildCodesList.size()>0">
                                        and jpag.community_code in
                                        <foreach collection="regionChildCodesList" item="code" open="(" close=")"
                                                 separator=",">
                                            #{code}
                                        </foreach>
                                    </when>
                                    <otherwise>
                                        and jpag.community_code in ('')
                                    </otherwise>
                                </choose>
                            </if>
                        </when>
                        <otherwise>
                            <choose>
                                <when test="regionChildCodesList !=null and regionChildCodesList.size()>0">
                                    and
                                    (
                                    jg.grid_code in
                                    <foreach collection="regionChildCodesList" item="code" open="(" close=")" separator=",">
                                        #{code}
                                    </foreach>
                                    or
                                    jpag.community_code in
                                    <foreach collection="regionChildCodesList" item="code" open="(" close=")" separator=",">
                                        #{code}
                                    </foreach>
                                    )
                                </when>
                                <otherwise>
                                    and
                                    (
                                    jg.grid_code in ('')
                                    or
                                    jpag.community_code in ('')
                                    )
                                </otherwise>
                            </choose>
                        </otherwise>
                    </choose>
                </if>
            </otherwise>
        </choose>
    </select>


@@ -825,7 +861,7 @@
            LEFT JOIN jczz_user_house_label juhl ON juhl.house_code = jh.house_code and juhl.lable_type = 2
            where jda.doorplate_type = '户室牌'
            <if test="house.communityCode != null  and house.communityCode != ''">
                and jg.community_code = #{house.communityCode}
                and jda.nei_code = #{house.communityCode}
            </if>
            <if test="isAdministrator==2">
                <choose>

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

@@ -778,17 +778,6 @@
            AND jg.is_deleted = 0
            )
        </if>

        <if test="userId != null and roleType == '3'">
            AND jda.address_code IN (SELECT
            jda.address_code
            FROM
            jczz_doorplate_address jda
            LEFT JOIN jczz_community jc ON jc.CODE = jda.nei_code
            WHERE
            jc.res_police_user_id like concat('%',#{userId},'%'))
            )
        </if>
        GROUP BY age
    </select>


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

@@ -825,28 +825,25 @@

    @Override
    public Map<String, Object> getHouseStatistics(String code, String roleType, String aoiCode, String buildingCode, String unitCode) {
        HouseVO house = new HouseVO();
        CommonParamSet commonParamSet = new CommonParamSet<>().invoke(HouseVO.class, house);
        Map<String, Object> objectObjectHashMap = new HashMap<>();
        if (roleType.equals("2")) {
            //     result1 查询楼栋数  result2 查询房屋套数 result3 查询住户数  result4 查询单元数
            Integer result1 = baseMapper.getHouseStatisticsOne(code, null, aoiCode, buildingCode, unitCode, roleType);
            Integer result2 = baseMapper.getHouseStatisticsTwo(code, null, aoiCode, buildingCode, unitCode, roleType);
            Integer result3 = baseMapper.getHouseStatisticsThree(code, null, aoiCode, buildingCode, unitCode, roleType);
            Integer result4 = baseMapper.getHouseStatisticsFour(code, null, aoiCode, buildingCode, unitCode, roleType);
            objectObjectHashMap.put("result1", result1);
            objectObjectHashMap.put("result2", result2);
            objectObjectHashMap.put("result3", result3);
            objectObjectHashMap.put("result4", result4);
        } else {

            Integer result1 = baseMapper.getHouseStatisticsOne(code, AuthUtil.getUserId(), aoiCode, buildingCode, unitCode, roleType);
            Integer result2 = baseMapper.getHouseStatisticsTwo(code, AuthUtil.getUserId(), aoiCode, buildingCode, unitCode, roleType);
            Integer result3 = baseMapper.getHouseStatisticsThree(code, AuthUtil.getUserId(), aoiCode, buildingCode, unitCode, roleType);
            Integer result4 = baseMapper.getHouseStatisticsFour(code, AuthUtil.getUserId(), aoiCode, buildingCode, unitCode, roleType);
            objectObjectHashMap.put("result1", result1);
            objectObjectHashMap.put("result2", result2);
            objectObjectHashMap.put("result3", result3);
            objectObjectHashMap.put("result4", result4);
        Long userId = null;
        String roleName = SpringUtils.getRequestParam("roleName");
        if (!Strings.isBlank(roleName) && roleName.equals("mj")){
            userId = AuthUtil.getUserId();
        }
        Integer result1 = baseMapper.getHouseStatisticsOne(code, userId, aoiCode, buildingCode, unitCode, roleType);
        Integer result2  = baseMapper.getHouseStatisticsTwo(code, aoiCode, buildingCode, unitCode, house,
            commonParamSet.getIsAdministrator(),
            commonParamSet.getRegionChildCodesList(),
            commonParamSet.getGridCodeList());
        Integer result3 = baseMapper.getHouseStatisticsThree(code, userId, aoiCode, buildingCode, unitCode, roleType);
        Integer result4 = baseMapper.getHouseStatisticsFour(code, userId, aoiCode, buildingCode, unitCode, roleType);
        objectObjectHashMap.put("result1", result1);
        objectObjectHashMap.put("result2", result2);
        objectObjectHashMap.put("result3", result3);
        objectObjectHashMap.put("result4", result4);
        return objectObjectHashMap;
    }