业绩统计

1.统计关联企业融资数据的,要求:默认显示当前年份,对应的近三月,近半年,近一年按钮可以切换选择,当切换为非当前年份时,对应的近三月,近半年,近一年按钮变灰,不能进行切换选择,默认显示近一年的数据
/**
 * 业绩统计
 *
 * @author xiu.fu
 * @data 下午 6:13
 */
@RestController
@RequestMapping("/census")
public class PerformanceStatisticsController {

    @Resource(type = FfinAplMService.class)
    private FfinAplMService ffinAplMService;

    /**
     * 获取代理企业融资项目数
     *
     * @return com.vteam.mssme.api.entity.RespEntity
     * @author xiu.fu
     * @date 2019/10/17 0017 下午 1:17
     */
    @GetMapping("/getPerformanceStatistics/{queryType}/{year}")
    public RespEntity getPerformanceStatistics(@PathVariable String queryType, @PathVariable Integer year) {

        // 代理融资申请金额
        BigDecimal applyAmount = BigDecimal.ZERO;
        // 代理融资成功金额
        BigDecimal successAmount = BigDecimal.ZERO;
        // 代理融资成功笔数
        Integer successCount = 0;
        // 代理融资申请笔数
        Integer applyCount = 0;

        int queryCount = 3;
        if (GlobalConstants.Flag.FLAG_A.equals(queryType)) {
            queryCount = 3;
        } else if (GlobalConstants.Flag.FLAG_B.equals(queryType)) {
            queryCount = 6;
        } else if (GlobalConstants.Flag.FLAG_C.equals(queryType)) {
            queryCount = 12;
        }

        LocalDate now = LocalDate.now();
        LocalDate firstDayOfThisMonth = null;
        if (year < now.getYear()) {
            // 查询开始日(一整年)
            firstDayOfThisMonth = LocalDate.of(year, 12, 1);
        } else {
            // 查询开始日
            firstDayOfThisMonth = LocalDate.of(year, now.getMonth(), 1);

        }
        // 查询结束日
        LocalDate lastDayOfThisMonth = LocalDate.of(year, 12, 31);

        LocalDate queryDateStart = firstDayOfThisMonth.minusMonths(queryCount - 1);

        List<String> statisticsDateList = new ArrayList<>();
        for (int i = 0; i < queryCount; i++) {
            LocalDate statisticsDate = queryDateStart.plusMonths(i);
            statisticsDateList.add(DateUtils.FMT_MONTH.format(statisticsDate));
        }
        FfinAplMVo entity = new FfinAplMVo();
        entity.setPersonOrgRefcode(RequestStore.getLoginUser().getOrgRefcode());
        entity.setQueryDateTimeStart(LocalDateTime.of(queryDateStart, LocalTime.MIN));
        entity.setQueryDateTimeEnd(LocalDateTime.of(lastDayOfThisMonth, LocalTime.MAX));
        entity.setStatisticsDateList(statisticsDateList);

        List<FfinAplMVo> ffinAplMVoList = ffinAplMService.listPerformanceStatisticsInfo(entity);
        if (CollectionUtils.isNotEmpty(ffinAplMVoList)) {
            for (FfinAplMVo ffinAplMVo : ffinAplMVoList) {
                applyAmount = applyAmount.add(ffinAplMVo.getApplyAmount());
                successAmount = successAmount.add(ffinAplMVo.getSuccessAmount());
                successCount = successCount + ffinAplMVo.getSuccessCount();
                applyCount = applyCount + ffinAplMVo.getApplyCount();

            }
        }

        JSONObject data = new JSONObject();

        data.put("applyAmount", applyAmount.divide(new BigDecimal(10000), 2, BigDecimal.ROUND_HALF_UP));
        data.put("successAmount", successAmount.divide(new BigDecimal(10000), 2, BigDecimal.ROUND_HALF_UP));
        data.put("successCount", successCount);
        data.put("applyCount", applyCount);
        data.put("statisticsList", ffinAplMVoList);

        return RespEntity.ok(data);
    }

    /**
     * 获取邀请企业的融资项目及代理融资项目数
     *
     * @param
     * @return com.vteam.vtarm.api.RespEntity
     * @author xiu.fu
     * @date 2020/5/21 下午 3:05
     */
    @GetMapping("/getPerformanceCodeStatistics/{queryType}/{year}")
    public RespEntity getPerformanceCodeStatistics(@PathVariable String queryType, @PathVariable Integer year) {

        // 代理融资申请金额
        BigDecimal applyAmount = BigDecimal.ZERO;
        // 代理融资成功金额
        BigDecimal successAmount = BigDecimal.ZERO;
        // 代理融资成功笔数
        Integer successCount = 0;
        // 代理融资申请笔数
        Integer applyCount = 0;

        // 融资申请金额
        BigDecimal applyAmountSum = BigDecimal.ZERO;
        // 融资成功金额
        BigDecimal successAmountSum = BigDecimal.ZERO;
        // 融资成功笔数
        Integer successCountSum = 0;
        // 融资申请笔数
        Integer applyCountSum = 0;

        int queryCount = 3;
        if (GlobalConstants.Flag.FLAG_A.equals(queryType)) {
            queryCount = 3;
        } else if (GlobalConstants.Flag.FLAG_B.equals(queryType)) {
            queryCount = 6;
        } else if (GlobalConstants.Flag.FLAG_C.equals(queryType)) {
            queryCount = 12;
        }

        LocalDate now = LocalDate.now();
        LocalDate firstDayOfThisMonth = null;
        if (year < now.getYear()) {
            // 查询开始日(一整年)
            firstDayOfThisMonth = LocalDate.of(year, 12, 1);
        } else {
            // 查询开始日
            firstDayOfThisMonth = LocalDate.of(year, now.getMonth(), 1);

        }
        // 查询结束日
        LocalDate lastDayOfThisMonth = LocalDate.of(year, 12, 31);

        LocalDate queryDateStart = firstDayOfThisMonth.minusMonths(queryCount - 1);

        List<String> statisticsDateList = new ArrayList<>();
        for (int i = 0; i < queryCount; i++) {
            LocalDate statisticsDate = queryDateStart.plusMonths(i);
            statisticsDateList.add(DateUtils.FMT_MONTH.format(statisticsDate));
        }
        FfinAplMVo entity = new FfinAplMVo();
        entity.setPersonOrgRefcode(RequestStore.getLoginUser().getOrgRefcode());
        entity.setQueryDateTimeStart(LocalDateTime.of(queryDateStart, LocalTime.MIN));
        entity.setQueryDateTimeEnd(LocalDateTime.of(lastDayOfThisMonth, LocalTime.MAX));
        entity.setStatisticsDateList(statisticsDateList);

        List<FfinAplMVo> ffinAplMVoList = ffinAplMService.listPerformanceStatisticsInfo(entity);
        if (CollectionUtils.isNotEmpty(ffinAplMVoList)) {
            for (FfinAplMVo ffinAplMVo : ffinAplMVoList) {
                applyAmount = applyAmount.add(ffinAplMVo.getApplyAmount());
                successAmount = successAmount.add(ffinAplMVo.getSuccessAmount());
                successCount = successCount + ffinAplMVo.getSuccessCount();
                applyCount = applyCount + ffinAplMVo.getApplyCount();

            }
        }

        List<FfinAplMVo> ffinAplMVoLists = ffinAplMService.listPerformanceCodeStatisticsInfo(entity);
        if (CollectionUtils.isNotEmpty(ffinAplMVoLists)) {
            for (FfinAplMVo ffinAplMVo : ffinAplMVoLists) {
                applyAmountSum = applyAmount.add(ffinAplMVo.getApplyAmount());
                successAmountSum = successAmount.add(ffinAplMVo.getSuccessAmount());
                successCountSum = successCount + ffinAplMVo.getSuccessCount();
                applyCountSum = applyCount + ffinAplMVo.getApplyCount();

            }
        }

        JSONObject data = new JSONObject();

        data.put("applyAmountSum", applyAmountSum.divide(new BigDecimal(10000), 2, BigDecimal.ROUND_HALF_UP));
        data.put("successAmountSum", successAmountSum.divide(new BigDecimal(10000), 2, BigDecimal.ROUND_HALF_UP));
        data.put("successCountSum", successCountSum);
        data.put("applyCountSum", applyCountSum);
        data.put("statisticsList", ffinAplMVoLists);

        return RespEntity.ok(data);
    }
2.这个统计的sql,有点复杂

<select id="listPerformanceStatisticsInfo" resultType="com.vteam.mssme.entity.vo.FfinAplMVo">
    SELECT
    COALESCE (TEMP2.FINANCING_COUNT, 0) applyCount,
    COALESCE (TEMP2.FINANCING_AMT_SUM, 0) applyAmount,
    COALESCE (TEMP3.LOAN_COUNT, 0) successCount,
    COALESCE (TEMP3.LOAN_AMT_SUM, 0) successAmount,
    TEMP1.DATE_YM DATE_POINT
    FROM <include refid="financingSummaryStatisticsDateTable"/> TEMP1
    LEFT JOIN (
        SELECT
        COUNT(APL.ORG_REFCODE) FINANCING_COUNT,
        SUM(APL.FINANCING_AMT) FINANCING_AMT_SUM,
        TO_CHAR(APL.CREATE_DATE, 'YYYY-MM') CREATE_DATE
        FROM FFIN_APL_M APL
        INNER JOIN FBPA_ORG_DTC_M ORGDTC ON ORGDTC.DEL_FLAG = '0' AND ORGDTC.RELATED_ORG_REFCODE = APL.ORG_REFCODE
        WHERE APL.DEL_FLAG = '0'
        <![CDATA[  AND APL.AGENT_REFCODE<>'0' ]]>
        <!-- 合伙人企业流水号 -->
        <if test=" null != personOrgRefcode and '' != personOrgRefcode ">
            AND ORGDTC.ORG_REFCODE = #{personOrgRefcode}
        </if>
        AND APL.FINANCING_STATUS  IN('1','3','6','7','10','20','30')
        <!-- 申请日期 -->
        <![CDATA[ AND TO_CHAR(APL.CREATE_DATE,'yyyy-MM-dd') >= TO_CHAR(#{queryDateTimeStart},'yyyy-MM-dd') ]]>
        <![CDATA[ AND TO_CHAR(APL.CREATE_DATE,'yyyy-MM-dd') <= TO_CHAR(#{queryDateTimeEnd},'yyyy-MM-dd') ]]>
        GROUP BY TO_CHAR(APL.CREATE_DATE, 'YYYY-MM')
        ) TEMP2 ON TEMP1.DATE_YM = TEMP2.CREATE_DATE
    LEFT JOIN (
        SELECT
        COUNT(APL.ORG_REFCODE) LOAN_COUNT,
        SUM(APL.LOAN_AMT) LOAN_AMT_SUM,
        TO_CHAR(APL.LOAN_DATE, 'YYYY-MM') LOAN_DATE
        FROM FFIN_APL_M APL
        INNER JOIN FBPA_ORG_DTC_M ORGDTC ON ORGDTC.DEL_FLAG = '0' AND ORGDTC.RELATED_ORG_REFCODE = APL.ORG_REFCODE
        WHERE APL.DEL_FLAG = '0'
        <![CDATA[  AND APL.AGENT_REFCODE<>'0' ]]>
        AND APL.FINANCING_STATUS = '7'
        <!-- 合伙人企业流水号 -->
        <if test=" null != personOrgRefcode and '' != personOrgRefcode ">
            AND ORGDTC.ORG_REFCODE = #{personOrgRefcode}
        </if>
        <!-- 签约日期 -->
        <![CDATA[ AND TO_CHAR(APL.LOAN_DATE,'yyyy-MM-dd') >=  TO_CHAR(#{queryDateTimeStart},'yyyy-MM-dd') ]]>
        <![CDATA[ AND TO_CHAR(APL.LOAN_DATE,'yyyy-MM-dd') <=  TO_CHAR(#{queryDateTimeEnd},'yyyy-MM-dd') ]]>
        GROUP BY TO_CHAR(APL.LOAN_DATE, 'YYYY-MM')
        ) TEMP3 ON TEMP1.DATE_YM = TEMP3.LOAN_DATE
        ORDER BY TEMP1.DATE_YM
</select>

1.页面效果