优化欠费明细表加入大计小计

java110

2021-08-13 60534aef93bfb5b33b0feab3c826dd418af47457

优化欠费明细表加入大计小计

 java110-db/src/main/resources/mapper/report/ReportFeeMonthStatisticsServiceDaoImplMapper.xml

@@ -669,7 +669,7 @@
    </select>
    <!-- 查询费用月统计信息 add by wuxw 2018-07-03 -->
    <select id="queryFeeBreakdownMajor" parameterType="Map" resultType="Map">
        select  SUM(t.receivable_amount)
        select SUM(t.receivable_amount)
        allReceivableAmount,SUM(t.received_amount)
        allReceivedAmount,SUM(t.owe_amount) allOweAmount
        from report_fee_month_statistics t
@@ -998,6 +998,58 @@
        HAVING oweAmount > 0
        <if test="page != -1 and page != null ">
            limit #{page}, #{row}
        </if>
    </select>
    <!-- 查询费用月统计信息 add by wuxw 2018-07-03 -->
    <select id="queryOweFeeDetailMajor" parameterType="Map" resultType="Map">
        select SUM(t.receivable_amount)
        allReceivableAmount,SUM(t.received_amount)
        allReceivedAmount,SUM(t.owe_amount) allOweAmount
        from report_fee_month_statistics t
        inner join pay_fee_config pfc on t.config_id = pfc.config_id and pfc.status_cd = '0'
        left join building_room br on t.obj_id = br.room_id and t.obj_type = '3333' and br.status_cd = '0'
        left join building_unit bu on br.unit_id = bu.unit_id and bu.status_cd = '0'
        left join f_floor f on bu.floor_id = f.floor_id and f.status_cd = '0'
        inner join pay_fee pf on t.fee_id = pf.fee_id and pf.community_id = t.community_id and pf.status_cd = '0'
        where t.status_cd = '0'
        <if test="roomNum !=null and roomNum != ''">
            and br.room_num= #{roomNum}
        </if>
        <if test="unitId !=null and unitId != ''">
            and bu.unit_id= #{unitId}
        </if>
        <if test="floorId !=null and floorId != ''">
            and f.floor_id = #{floorId}
        </if>
        <if test="objName !=null and objName != ''">
            and t.obj_name= #{objName}
        </if>
        <if test="feeYear !=null and feeYear != ''">
            and t.fee_year= #{feeYear}
        </if>
        <if test="feeMonth !=null and feeMonth != ''">
            and t.fee_month= #{feeMonth}
        </if>
        <if test="feeId !=null and feeId != ''">
            and t.fee_id= #{feeId}
        </if>
        <if test="configId !=null and configId != ''">
            and t.config_id= #{configId}
        </if>
        <if test="objId !=null and objId != ''">
            and t.obj_id= #{objId}
        </if>
        <if test="feeName !=null and feeName != ''">
            and t.fee_name= #{feeName}
        </if>
        <if test="communityId !=null and communityId != ''">
            and t.community_id= #{communityId}
        </if>
        <if test="startTime !=null">
            and t.create_time &gt;= #{startTime}
        </if>
        <if test="endTime !=null">
            and t.create_time &lt;= #{endTime}
        </if>
    </select>

@@ -1732,10 +1784,12 @@
    </select>

    <select id="getFloorReceivableInformation" parameterType="Map" resultType="Map">
        select f.floor_num floorNum,f.`name`,sum(t.receivable_amount) receivableAmount,SUM(t.received_amount) receivedAmount,SUM(t.owe_amount) oweAmount
        select f.floor_num floorNum,f.`name`,sum(t.receivable_amount) receivableAmount,SUM(t.received_amount)
        receivedAmount,SUM(t.owe_amount) oweAmount
        from report_fee_month_statistics t
        inner join pay_fee pf on t.fee_id = pf.fee_id and pf.status_cd = '0'
        inner join building_room br on br.room_id = t.obj_id and t.obj_type = '3333' and br.community_id = t.community_id and br.status_cd = '0'
        inner join building_room br on br.room_id = t.obj_id and t.obj_type = '3333' and br.community_id =
        t.community_id and br.status_cd = '0'
        inner join building_unit bu on bu.unit_id = br.unit_id and bu.status_cd = '0'
        inner join f_floor f on f.floor_id = bu.floor_id and f.status_cd = '0'
        where t.community_id = #{communityId}
@@ -1743,10 +1797,11 @@
    </select>

    <select id="getFeeConfigReceivableInformation" parameterType="Map" resultType="Map">
        select t.fee_name feeName,sum(t.receivable_amount) receivableAmount,SUM(t.received_amount) receivedAmount,SUM(t.owe_amount) oweAmount
        select t.fee_name feeName,sum(t.receivable_amount) receivableAmount,SUM(t.received_amount)
        receivedAmount,SUM(t.owe_amount) oweAmount
        from report_fee_month_statistics t
        inner join pay_fee pf on t.fee_id = pf.fee_id and pf.status_cd = '0'
        where t.community_id =  #{communityId}
        where t.community_id = #{communityId}
        group by t.fee_name
    </select>

@@ -1755,12 +1810,12 @@
    <select id="queryNoFeeRoomsCount" parameterType="Map" resultType="Map">
        select count(1) count
        from building_room t
        inner join building_unit bu on t.unit_id  = bu.unit_id and bu.status_cd = '0'
         inner join f_floor f on bu.floor_id = f.floor_id and f.status_cd = '0'
         where t.status_cd = '0'
         and not exists(
             select 1 from pay_fee pf where t.room_id = pf.payer_obj_id and pf.status_cd = '0' and pf.state = '2008001'
         )
        inner join building_unit bu on t.unit_id = bu.unit_id and bu.status_cd = '0'
        inner join f_floor f on bu.floor_id = f.floor_id and f.status_cd = '0'
        where t.status_cd = '0'
        and not exists(
        select 1 from pay_fee pf where t.room_id = pf.payer_obj_id and pf.status_cd = '0' and pf.state = '2008001'
        )
        and t.community_id = #{communityId}
    </select>


 java110-interface/src/main/java/com/java110/intf/report/IReportFeeMonthStatisticsInnerServiceSMO.java

@@ -188,6 +188,8 @@
    @RequestMapping(value = "/queryOweFeeDetail", method = RequestMethod.POST)
    List<ReportFeeMonthStatisticsDto> queryOweFeeDetail(@RequestBody ReportFeeMonthStatisticsDto reportFeeMonthStatisticsDto);

    @RequestMapping(value = "/queryOweFeeDetailMajor", method = RequestMethod.POST)
    ReportFeeMonthStatisticsDto queryOweFeeDetailMajor(@RequestBody ReportFeeMonthStatisticsDto reportFeeMonthStatisticsDto);
    /**
     * 查询费用汇总表个数
     *
@@ -350,4 +352,5 @@
    @RequestMapping(value = "/queryFeeDepositAmount", method = RequestMethod.POST)
    List<ReportDeposit> queryFeeDepositAmount(@RequestBody ReportDeposit reportDeposit);


}

 service-report/src/main/java/com/java110/report/bmo/reportFeeMonthStatistics/impl/GetReportFeeMonthStatisticsBMOImpl.java

@@ -222,7 +222,14 @@
        List<ReportFeeMonthStatisticsDto> reportFeeMonthStatisticsDtos = null;
        if (count > 0) {
            reportFeeMonthStatisticsDtos = reportFeeMonthStatisticsInnerServiceSMOImpl.queryOweFeeDetail(reportFeeMonthStatisticsDto);

            ReportFeeMonthStatisticsDto tmpReportFeeMonthStatisticsDto = reportFeeMonthStatisticsInnerServiceSMOImpl.queryOweFeeDetailMajor(reportFeeMonthStatisticsDto);
            if(reportFeeMonthStatisticsDtos != null && reportFeeMonthStatisticsDtos.size()> 0){
                for(ReportFeeMonthStatisticsDto reportFeeMonthStatisticsDto1 : reportFeeMonthStatisticsDtos){
                    reportFeeMonthStatisticsDto1.setAllReceivableAmount(tmpReportFeeMonthStatisticsDto.getAllReceivableAmount());
                    reportFeeMonthStatisticsDto1.setAllReceivedAmount(tmpReportFeeMonthStatisticsDto.getAllReceivedAmount());
                    reportFeeMonthStatisticsDto1.setAllOweAmount(tmpReportFeeMonthStatisticsDto.getAllOweAmount());
                }
            }
            freshReportOweDay(reportFeeMonthStatisticsDtos);
        } else {
            reportFeeMonthStatisticsDtos = new ArrayList<>();

 service-report/src/main/java/com/java110/report/dao/IReportFeeMonthStatisticsServiceDao.java

@@ -308,4 +308,6 @@
    Map queryReportFloorUnitFeeSummaryMajor(Map beanCovertMap);

    Map queryFeeBreakdownMajor(Map beanCovertMap);

    Map queryOweFeeDetailMajor(Map beanCovertMap);
}

 service-report/src/main/java/com/java110/report/dao/impl/ReportFeeMonthStatisticsServiceDaoImpl.java

@@ -218,6 +218,8 @@
        return businessReportFeeMonthStatisticsInfos;
    }



    @Override
    public int queryOweFeeDetailCount(Map info) {
        logger.debug("查询费用月统计数据 入参 info : {}", info);
@@ -238,6 +240,14 @@

        return businessReportFeeMonthStatisticsInfos;
    }
    @Override
    public Map queryOweFeeDetailMajor(Map info) {
        logger.debug("查询费用queryOweFeeDetailMajor 入参 info : {}", info);

        List<Map> businessReportFeeMonthStatisticsInfos = sqlSessionTemplate.selectList("reportFeeMonthStatisticsServiceDaoImpl.queryOweFeeDetailMajor", info);

        return businessReportFeeMonthStatisticsInfos.get(0);
    }

    @Override
    public Map queryPayFeeDetailCount(Map info) {

 service-report/src/main/java/com/java110/report/smo/impl/ReportFeeMonthStatisticsInnerServiceSMOImpl.java

@@ -233,6 +233,14 @@
    }

    @Override
    public ReportFeeMonthStatisticsDto queryOweFeeDetailMajor(@RequestBody ReportFeeMonthStatisticsDto reportFeeMonthStatisticsDto) {
        ReportFeeMonthStatisticsDto reportFeeMonthStatistics = BeanConvertUtil.covertBean(
                reportFeeMonthStatisticsServiceDaoImpl.queryOweFeeDetailMajor(
                        BeanConvertUtil.beanCovertMap(reportFeeMonthStatisticsDto)), ReportFeeMonthStatisticsDto.class);
        return reportFeeMonthStatistics;
    }

    @Override
    public JSONObject queryPayFeeDetailCount(@RequestBody ReportFeeMonthStatisticsDto reportFeeMonthStatisticsDto) {
        Map info = reportFeeMonthStatisticsServiceDaoImpl.queryPayFeeDetailCount(BeanConvertUtil.beanCovertMap(reportFeeMonthStatisticsDto));