optimize

java110

2023-05-22 ba7906241f787c72dc6b5342b8d873fbc203407d

optimize

 java110-bean/src/main/java/com/java110/dto/report/QueryStatisticsDto.java

@@ -30,6 +30,8 @@

    private String[] unitIds;

    private String[] objIds;


    public String getCommunityId() {
        return communityId;
@@ -119,4 +121,12 @@
    public void setUnitIds(String[] unitIds) {
        this.unitIds = unitIds;
    }

    public String[] getObjIds() {
        return objIds;
    }

    public void setObjIds(String[] objIds) {
        this.objIds = objIds;
    }
}

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

@@ -7,11 +7,93 @@
    <!-- 查询房屋数 -->
    <select id="getRoomCount" parameterType="Map" resultType="Map">

        select count(1) count
        from building_room t
        left join building_unit bu on t.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'
        left join building_owner_room_rel borr on t.room_id = borr.room_id and borr.status_cd = '0'
        left join building_owner bo on borr.owner_id = bo.owner_id and bo.status_cd = '0'
        where 1=1
        <if test="floorId != null and floorId != ''">
            and f.floor_id = #{floorId}
        </if>
        <if test="floorNum != null and floorNum != ''">
            and f.floor_num = #{floorNum}
        </if>
        <if test="unitNum != null and unitNum != ''">
            and bu.unit_num = #{unitNum}
        </if>
        <if test="roomNum != null and roomNum != ''">
            and t.room_num = #{roomNum}
        </if>
        <if test="roomNumLike !=null and roomNumLike != ''">
            and t.room_num like concat('%',#{roomNumLike},'%')
        </if>
        <if test="state !=null and state != ''">
            and t.state= #{state}
        </if>
        <if test="roomId != null and roomId != ''">
            and t.room_id = #{roomId}
        </if>
        <if test="ownerId != null and ownerId != ''">
            and bo.owner_id = #{ownerId}
        </if>
        <if test="ownerName != null and ownerName != ''">
            and bo.`name` like concat('%',#{ownerName},'%')
        </if>
        <if test="link != null and link != ''">
            and bo.link = #{link}
        </if>
    </select>

    <!-- 查询单月欠费 -->
    <select id="getRoomInfo" parameterType="Map" resultType="Map">

        select t.fee_coefficient feeCoefficient,t.section,t.remark,t.user_id userId,
        t.room_id roomId,t.layer,t.built_up_area,t.built_up_area
        builtUpArea,t.room_num,t.room_num roomNum,t.unit_id,t.unit_id unitId,t.b_id,t.b_id
        bId,t.apartment,t.state,t.community_id,t.community_id communityId,t.room_type,t.room_type roomType,
        t.room_sub_type,t.room_rent,t.room_area,t.room_sub_type roomSubType,t.room_rent roomRent,t.room_area roomArea,
        f.floor_num floorNum,bu.unit_num unitNum,bo.name ownerName,bo.owner_id ownerId,bo.link
        from building_room t
        left join building_unit bu on t.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'
        left join building_owner_room_rel borr on t.room_id = borr.room_id and borr.status_cd = '0'
        left join building_owner bo on borr.owner_id = bo.owner_id and bo.status_cd = '0'
        where 1=1
        <if test="floorId != null and floorId != ''">
            and f.floor_id = #{floorId}
        </if>
        <if test="floorNum != null and floorNum != ''">
            and f.floor_num = #{floorNum}
        </if>
        <if test="unitNum != null and unitNum != ''">
            and bu.unit_num = #{unitNum}
        </if>
        <if test="roomNum != null and roomNum != ''">
            and t.room_num = #{roomNum}
        </if>
        <if test="roomNumLike !=null and roomNumLike != ''">
            and t.room_num like concat('%',#{roomNumLike},'%')
        </if>
        <if test="state !=null and state != ''">
            and t.state= #{state}
        </if>
        <if test="roomId != null and roomId != ''">
            and t.room_id = #{roomId}
        </if>
        <if test="ownerId != null and ownerId != ''">
            and bo.owner_id = #{ownerId}
        </if>
        <if test="ownerName != null and ownerName != ''">
            and bo.`name` like concat('%',#{ownerName},'%')
        </if>
        <if test="link != null and link != ''">
            and bo.link = #{link}
        </if>
        order by t.create_time desc
        <if test="page != -1 and page != null">
            limit #{page},#{row}
        </if>
    </select>



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

@@ -673,82 +673,46 @@
        select a.obj_name objName,a.obj_id objId,a.owner_name ownerName,a.link
        from pay_fee_detail_month a
        where a.status_cd = '0'
        and a.community_id = '2023013154290059'
        and a.community_id = #{communityId}
        group by a.obj_name,a.obj_id,a.owner_name,a.link
        ) b
    </select>

    <select id="getObjFeeSummary" parameterType="Map" resultType="Map">
        select a.obj_name objName,a.obj_id objId,a.owner_name ownerName,a.link,
        select
        a.objId,pfc.feeTypeCd,
        (
        select ifnull(sum(t.receivable_amount - t.received_amount - t.discount_amount),0.0) hisOweFee
        select ifnull(sum(t.receivable_amount),0.0) hisOweFee
        from pay_fee_detail_month t
        INNER JOIN pay_fee pf on t.fee_id = pf.fee_id and pf.status_cd = '0'
        inner join pay_fee_config pfc1 on pfc1.config_id = t.config_id and pfc1.status_cd = '0'
        where t.obj_id = a.obj_id
        and t.status_cd = '0'
        and t.detail_id = '-1'
        and t.community_id= a.community_id
        and t.cur_month_time &lt; #{startDate}
        ) hisOweFee,
        (
        select ifnull(sum(t.receivable_amount - t.received_amount - t.discount_amount),0.0) curOweFee
        from pay_fee_detail_month t
        INNER JOIN pay_fee pf on t.fee_id = pf.fee_id and pf.status_cd = '0'
        where t.obj_id = a.obj_id
        and t.status_cd = '0'
        and t.community_id= a.community_id
        and t.cur_month_time &gt;= #{startDate}
        and t.obj_id = a.obj_id
        and pfc1.fee_type_cd = pfc.fee_type_cd
        and t.cur_month_time &lt; #{endDate}
        ) curOweFee,
        (
        select ifnull(sum(t.receivable_amount),0.0) curReceivableFee
        from pay_fee_detail_month t
        INNER JOIN pay_fee pf on t.fee_id = pf.fee_id and pf.status_cd = '0'
        where t.obj_id = a.obj_id
        and t.status_cd = '0'
        and t.community_id= a.community_id
        and t.cur_month_time &gt; #{startDate}
        and t.cur_month_time &lt; #{endDate}
        ) curReceivableFee,
        (
        select ifnull(sum(t.received_amount),0.0) hisReceivedFee
        from pay_fee_detail_month t
        INNER JOIN pay_fee pf on t.fee_id = pf.fee_id and pf.status_cd = '0'
        where t.obj_id = a.obj_id
        and t.status_cd = '0'
        and t.community_id= a.community_id
        and t.pay_fee_time &gt; #{startDate}
        and t.pay_fee_time &lt; #{endDate}
        and t.cur_month_time &lt; #{startDate}
        ) hisReceivedFee,
        (
        select ifnull(sum(t.received_amount),0.0) preReceivedFee
        from pay_fee_detail_month t
        INNER JOIN pay_fee pf on t.fee_id = pf.fee_id and pf.status_cd = '0'
        where t.obj_id = a.obj_id
        and t.status_cd = '0'
        and t.community_id= a.community_id
        and t.pay_fee_time &gt; #{startDate}
        and t.pay_fee_time &lt; #{endDate}
        and t.cur_month_time &gt; #{endDate}
        ) preReceivedFee,
        ) oweFee,
        (
        select ifnull(sum(t.received_amount),0.0) receivedFee
        from pay_fee_detail_month t
        INNER JOIN pay_fee pf on t.fee_id = pf.fee_id and pf.status_cd = '0'
        inner join pay_fee_config pfc1 on pfc1.config_id = t.config_id and pfc1.status_cd = '0'
        where t.obj_id = a.obj_id
        and t.status_cd = '0'
        and t.community_id= a.community_id
        and pfc1.fee_type_cd = pfc.fee_type_cd
        and t.pay_fee_time &gt; #{startDate}
        and t.pay_fee_time &lt; #{endDate}
        ) receivedFee
        from pay_fee_detail_month a
        where a.status_cd = '0'
        and a.community_id = #{communityId}
        group by a.obj_name,a.obj_id,a.owner_name,a.link
        order by  a.obj_name
        <if test="page != -1 and page != null ">
            limit #{page}, #{row}
        </if>
        inner join pay_fee_config pfc on a.config_id = pfc.config_id and pfc.status_cd = '0'
        where a.obj_id in
        <foreach collection="objIds" item="item" index="index" open="(" close=")" separator=",">
            #{item}
        </foreach>
        group by a.obj_id,pfc.fee_type_cd,a.community_id
    </select>



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

@@ -27,18 +27,18 @@
    /**
     * <p>查询房屋总数</p>
     *
     * @param queryFeeStatisticsDto 数据对象分享
     * @param roomDto 数据对象分享
     */
    @RequestMapping(value = "/getRoomCount", method = RequestMethod.POST)
    double getRoomCount(@RequestBody QueryStatisticsDto queryFeeStatisticsDto);
    long getRoomCount(@RequestBody RoomDto roomDto);



    /**
     * <p>查询房屋</p>
     *
     * @param queryFeeStatisticsDto 数据对象分享
     * @param roomDto 数据对象分享
     */
    @RequestMapping(value = "/getRoomInfo", method = RequestMethod.POST)
    List<RoomDto> getRoomInfo(@RequestBody QueryStatisticsDto queryFeeStatisticsDto);
    List<RoomDto> getRoomInfo(@RequestBody RoomDto roomDto);
}

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

@@ -6,6 +6,7 @@
import com.java110.dto.fee.FeeConfigDto;
import com.java110.dto.owner.OwnerDto;
import com.java110.dto.repair.RepairUserDto;
import com.java110.dto.report.QueryStatisticsDto;
import com.java110.dto.report.ReportDeposit;
import com.java110.dto.reportFeeMonthStatistics.ReportFeeMonthStatisticsDto;
import com.java110.po.reportFeeMonthStatistics.ReportFeeMonthStatisticsPo;
@@ -248,6 +249,7 @@

    @RequestMapping(value = "/queryOweFeeDetailMajor", method = RequestMethod.POST)
    ReportFeeMonthStatisticsDto queryOweFeeDetailMajor(@RequestBody ReportFeeMonthStatisticsDto reportFeeMonthStatisticsDto);

    /**
     * 查询费用汇总表个数
     *
@@ -413,6 +415,7 @@

    /**
     * 查询华宁物业 欠费总数
     *
     * @param reportFeeMonthStatisticsDto
     * @return
     */
@@ -421,6 +424,7 @@

    /**
     * 查询华宁物业 欠费明细 按楼栋 group by
     *
     * @param reportFeeMonthStatisticsDto
     * @return
     */
@@ -446,10 +450,11 @@
    List<Map> queryHuaningOweFeeDetail(@RequestBody Map paramInfo);

    /**
     *
     * @param ownerDto
     * @return
     */
    @RequestMapping(value = "/queryRoomAndParkingSpace", method = RequestMethod.POST)
    List<OwnerDto> queryRoomAndParkingSpace(@RequestBody OwnerDto ownerDto);


}

 java110-utils/src/main/java/com/java110/utils/util/MoneyUtil.java

@@ -35,4 +35,6 @@

        return feeTotalPrice.doubleValue();
    }


}

 java110-utils/src/test/java/com/java110/utils/AppTest.java

@@ -5,6 +5,7 @@
import junit.framework.TestCase;
import junit.framework.TestSuite;

import java.math.BigDecimal;
import java.util.Calendar;
import java.util.List;

@@ -37,16 +38,12 @@
     */
    public void testApp()
    {
        String startTime = "2023-03-02 04:00:00";
        String endTime = "2024-01-01 00:00:00";
        BigDecimal curFeePrice = new BigDecimal(346.815+"");
        curFeePrice = curFeePrice.multiply(new BigDecimal(1));

        curFeePrice = curFeePrice.setScale(2,BigDecimal.ROUND_HALF_UP);

        List<String> months = DateUtil.getMonthBetweenDate(startTime,endTime);

        for(String month : months) {
            System.out.println(month);
        }

        System.out.printf(curFeePrice.doubleValue()+"");

    }
}

 service-fee/src/main/java/com/java110/fee/cmd/fee/ListFeeCmd.java

@@ -376,6 +376,8 @@

    }



    /**
     * 根据合同来算单价
     *

 service-report/src/main/java/com/java110/report/cmd/reportFeeMonthStatistics/QueryReportFeeDetailRoomCmd.java

@@ -1,19 +1,28 @@
package com.java110.report.cmd.reportFeeMonthStatistics;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.java110.core.annotation.Java110Cmd;
import com.java110.core.context.ICmdDataFlowContext;
import com.java110.core.event.cmd.Cmd;
import com.java110.core.event.cmd.CmdEvent;
import com.java110.dto.RoomDto;
import com.java110.dto.report.QueryStatisticsDto;
import com.java110.intf.community.IRoomV1InnerServiceSMO;
import com.java110.intf.report.IReportFeeMonthStatisticsInnerServiceSMO;
import com.java110.report.statistics.IBaseDataStatistics;
import com.java110.report.statistics.IFeeStatistics;
import com.java110.utils.exception.CmdException;
import com.java110.utils.util.Assert;
import com.java110.vo.ResultVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;

import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 查询房屋费用明细表
@@ -28,6 +37,9 @@

    @Autowired
    private IBaseDataStatistics baseDataStatisticsImpl;

    @Autowired
    private IReportFeeMonthStatisticsInnerServiceSMO reportFeeMonthStatisticsInnerServiceSMOImpl;

    @Override
    public void validate(CmdEvent event, ICmdDataFlowContext context, JSONObject reqJson) throws CmdException, ParseException {
@@ -50,7 +62,70 @@
        queryStatisticsDto.setFeeTypeCd(reqJson.getString("feeTypeCd"));
        queryStatisticsDto.setOwnerName(reqJson.getString("ownerName"));
        queryStatisticsDto.setLink(reqJson.getString("link"));
        baseDataStatisticsImpl.getRoomCount(queryStatisticsDto);
        queryStatisticsDto.setPage(reqJson.getInteger("page"));
        queryStatisticsDto.setRow(reqJson.getInteger("row"));
        long count = baseDataStatisticsImpl.getRoomCount(queryStatisticsDto);
        List<RoomDto> rooms = null;
        if (count > 0) {
            rooms = baseDataStatisticsImpl.getRoomInfo(queryStatisticsDto);
        } else {
            rooms = new ArrayList<>();
        }

        // todo 计算 房屋欠费实收数据
        JSONArray datas = computeRoomOweReceivedFee(rooms,queryStatisticsDto);

        ResultVo resultVo = new ResultVo((int) Math.ceil((double) count / (double) queryStatisticsDto.getRow()), count, datas);

        ResponseEntity<String> responseEntity = new ResponseEntity<String>(resultVo.toString(), HttpStatus.OK);
        context.setResponseEntity(responseEntity);

    }

    /**
     * 计算房屋欠费 实收费用
     *
     * @param rooms
     * @return
     */
    private JSONArray computeRoomOweReceivedFee(List<RoomDto> rooms,QueryStatisticsDto queryStatisticsDto) {
        if (rooms == null || rooms.size() < 1) {
            return new JSONArray();
        }

        JSONArray datas = new JSONArray();
        JSONObject data = null;

        List<String> objIds = new ArrayList<>();
        for (RoomDto roomDto : rooms) {
            objIds.add(roomDto.getRoomId());
            data = new JSONObject();
            data.put("roomId",roomDto.getRoomId());
            data.put("roomName",roomDto.getFloorNum()+"-"+roomDto.getUnitNum()+"-"+roomDto.getRoomNum());
            data.put("ownerName",roomDto.getOwnerName());
            data.put("ownerId",roomDto.getOwnerId());
            data.put("link",roomDto.getLink());
            datas.add(data);
        }

        queryStatisticsDto.setObjIds(objIds.toArray(new String[objIds.size()]));
        List<Map> infos = feeStatisticsImpl.getObjFeeSummary(queryStatisticsDto);

        if(infos == null || infos.size() < 1){
            return datas;
        }

        for(int dataIndex = 0; dataIndex < datas.size();dataIndex ++){
            data = datas.getJSONObject(dataIndex);
            for(Map info : infos){
                if(!data.get("roomId").toString().equals(info.get("objId"))){
                    continue;
                }
                data.put("oweFee"+info.get("feeTypeCd").toString(),info.get("oweFee"));
                data.put("receivedFee"+info.get("feeTypeCd").toString(),info.get("receivedFee"));
            }
        }

        return datas;
    }
}

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

@@ -34,30 +34,30 @@
    /**
     * 查询房屋总数
     *
     * @param queryStatisticsDto 数据对象分享
     * @param roomDto 数据对象分享
     * @return
     */
    @Override
    public double getRoomCount(@RequestBody QueryStatisticsDto queryStatisticsDto) {
        int info = baseDataStatisticsServiceDaoImpl.getRoomCount(BeanConvertUtil.beanCovertMap(queryStatisticsDto));
    public long getRoomCount(@RequestBody RoomDto roomDto) {
        int info = baseDataStatisticsServiceDaoImpl.getRoomCount(BeanConvertUtil.beanCovertMap(roomDto));
        return info;
    }

    /**
     * 查询房屋信息
     *
     * @param queryStatisticsDto 数据对象分享
     * @param roomDto 数据对象分享
     * @return
     */
    @Override
    public List<RoomDto> getRoomInfo(@RequestBody QueryStatisticsDto queryStatisticsDto) {
        int page = queryStatisticsDto.getPage();
    public List<RoomDto> getRoomInfo(@RequestBody RoomDto roomDto) {
        int page = roomDto.getPage();

        if (page != PageDto.DEFAULT_PAGE) {
            queryStatisticsDto.setPage((page - 1) * queryStatisticsDto.getRow());
            roomDto.setPage((page - 1) * roomDto.getRow());
        }

        List<Map> info = baseDataStatisticsServiceDaoImpl.getRoomInfo(BeanConvertUtil.beanCovertMap(queryStatisticsDto));
        List<Map> info = baseDataStatisticsServiceDaoImpl.getRoomInfo(BeanConvertUtil.beanCovertMap(roomDto));
        return BeanConvertUtil.covertBeanList(info, RoomDto.class);
    }
}

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

@@ -9,6 +9,7 @@
import com.java110.dto.fee.FeeConfigDto;
import com.java110.dto.owner.OwnerDto;
import com.java110.dto.repair.RepairUserDto;
import com.java110.dto.report.QueryStatisticsDto;
import com.java110.dto.report.ReportDeposit;
import com.java110.dto.reportFeeMonthStatistics.ReportFeeMonthStatisticsDto;
import com.java110.intf.report.IReportFeeMonthStatisticsInnerServiceSMO;
@@ -595,6 +596,8 @@
        return deposits;
    }



    public IReportFeeMonthStatisticsServiceDao getReportFeeMonthStatisticsServiceDaoImpl() {
        return reportFeeMonthStatisticsServiceDaoImpl;
    }

 service-report/src/main/java/com/java110/report/statistics/IBaseDataStatistics.java

@@ -1,6 +1,9 @@
package com.java110.report.statistics;

import com.java110.dto.RoomDto;
import com.java110.dto.report.QueryStatisticsDto;

import java.util.List;

/**
 * 基础数据统计
@@ -15,6 +18,13 @@
    long getRoomCount(QueryStatisticsDto queryStatisticsDto);

    /**
     * 查询房屋
     * @param queryStatisticsDto
     * @return
     */
    List<RoomDto> getRoomInfo(QueryStatisticsDto queryStatisticsDto);

    /**
     * 查询空闲房屋数
     * @param queryStatisticsDto
     * @return

 service-report/src/main/java/com/java110/report/statistics/impl/BaseDataStatisticsImpl.java

@@ -10,6 +10,8 @@
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * 基础数据统计类
 */
@@ -35,7 +37,24 @@
        roomDto.setCommunityId(queryStatisticsDto.getCommunityId());
        roomDto.setFloorId(queryStatisticsDto.getFloorId());
        addRoomNumCondition(queryStatisticsDto, roomDto);
        return roomV1InnerServiceSMOImpl.queryRoomsCount(roomDto);
        return baseDataStatisticsInnerServiceSMOImpl.getRoomCount(roomDto);
    }

    /**
     * 查询房屋信息
     *
     * @param queryStatisticsDto
     * @return
     */
    @Override
    public List<RoomDto> getRoomInfo(QueryStatisticsDto queryStatisticsDto) {
        RoomDto roomDto = new RoomDto();
        roomDto.setCommunityId(queryStatisticsDto.getCommunityId());
        roomDto.setFloorId(queryStatisticsDto.getFloorId());
        roomDto.setPage(queryStatisticsDto.getPage());
        roomDto.setRow(queryStatisticsDto.getRow());
        addRoomNumCondition(queryStatisticsDto, roomDto);
        return baseDataStatisticsInnerServiceSMOImpl.getRoomInfo(roomDto);
    }