platformx-boot/src/main/resources/application-dev.yml
@@ -48,4 +48,12 @@ # 文件上传配置 file: local: base-path: D:\\Users\\platformx\\Downloads\\img base-path: D:\\Users\\platformx\\Downloads\\img # 5家单位id dept: smj: 2056555602756063234 sgb: 2056556196124250113 jxc: 2056556317461270529 tfgs: 2056556421857497090 ymj: 2056556535640576002 platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/dto/AnalysisCommonDTO.java
New file @@ -0,0 +1,10 @@ package com.by4cloud.platformx.business.dto; import lombok.Data; @Data public class AnalysisCommonDTO { private String year; } platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/entity/PaymentConfirm.java
@@ -85,7 +85,7 @@ private String bipId; /** * 付款方式 0/现汇 1/承兑 数据字典 * 付款方式 0/现汇 1/承兑 2/抵抹账 */ @Schema(description = "付款方式 ") @Column(columnDefinition="int(1) comment '付款方式'") platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractAmountAnalysisCompVo.java
New file @@ -0,0 +1,16 @@ package com.by4cloud.platformx.business.vo; import lombok.Data; @Data public class ContractAmountAnalysisCompVo { private String yf; private String je; private String tb; private String hb; } platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractAmountAnalysisVo.java
New file @@ -0,0 +1,35 @@ package com.by4cloud.platformx.business.vo; import lombok.Data; @Data public class ContractAmountAnalysisVo { private String compName; private String month1; private String month2; private String month3; private String month4; private String month5; private String month6; private String month7; private String month8; private String month9; private String month10; private String month11; private String month12; } platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractIncomeAnalysisAreaVo.java
New file @@ -0,0 +1,31 @@ package com.by4cloud.platformx.business.vo; import lombok.Data; @Data public class ContractIncomeAnalysisAreaVo { private String areaId; private String areaName; private String sgbze; private String sgbzb; private String smjze; private String smjzb; private String jxcze; private String jxczb; private String tfze; private String tfzb; private String ymjze; private String ymjzb; } platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractIncomeAnalysisCompVo.java
New file @@ -0,0 +1,17 @@ package com.by4cloud.platformx.business.vo; import lombok.Data; @Data public class ContractIncomeAnalysisCompVo { private String yf; private String sr; private String xh; private String cd; private String qt; } platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractIncomeAnalysisVo.java
New file @@ -0,0 +1,106 @@ package com.by4cloud.platformx.business.vo; import lombok.Data; @Data public class ContractIncomeAnalysisVo { private String compName; private String srMonth1; private String xhMonth1; private String cdMonth1; private String qtMonth1; private String srMonth2; private String xhMonth2; private String cdMonth2; private String qtMonth2; private String srMonth3; private String xhMonth3; private String cdMonth3; private String qtMonth3; private String srMonth4; private String xhMonth4; private String cdMonth4; private String qtMonth4; private String srMonth5; private String xhMonth5; private String cdMonth5; private String qtMonth5; private String srMonth6; private String xhMonth6; private String cdMonth6; private String qtMonth6; private String srMonth7; private String xhMonth7; private String cdMonth7; private String qtMonth7; private String srMonth8; private String xhMonth8; private String cdMonth8; private String qtMonth8; private String srMonth9; private String xhMonth9; private String cdMonth9; private String qtMonth9; private String srMonth10; private String xhMonth10; private String cdMonth10; private String qtMonth10; private String srMonth11; private String xhMonth11; private String cdMonth11; private String qtMonth11; private String srMonth12; private String xhMonth12; private String cdMonth12; private String qtMonth12; } platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/controller/StatisticController.java
New file @@ -0,0 +1,114 @@ package com.by4cloud.platformx.business.controller; import com.by4cloud.platformx.business.dto.AnalysisCommonDTO; import com.by4cloud.platformx.business.entity.SalesMan; import com.by4cloud.platformx.business.service.StatisticService; import com.by4cloud.platformx.common.core.util.R; import com.by4cloud.platformx.common.log.annotation.SysLog; import io.swagger.v3.oas.annotations.Operation; import io.swagger.v3.oas.annotations.security.SecurityRequirement; import io.swagger.v3.oas.annotations.tags.Tag; import lombok.RequiredArgsConstructor; import org.springframework.http.HttpHeaders; import org.springframework.security.access.prepost.PreAuthorize; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestBody; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; /** * 统计模块 * * @author syt * @date 2026年5月22日 09:41:29 */ @RestController @RequiredArgsConstructor @RequestMapping("/statistic" ) @Tag(description = "statistic" , name = "统计模块" ) @SecurityRequirement(name = HttpHeaders.AUTHORIZATION) public class StatisticController { private final StatisticService statisticService; /** * 合同金额数据分析 * @return R */ @PostMapping("/contractAmountAnalysis") public R contractAmountAnalysis(@RequestBody AnalysisCommonDTO commonDTO) { return statisticService.contractAmountAnalysis(commonDTO); } /** * 子公司合同金额数据分析 * @return R */ @PostMapping("/contractAmountAnalysisByComp") public R contractAmountAnalysisByComp(@RequestBody AnalysisCommonDTO commonDTO) { return statisticService.contractAmountAnalysisByComp(commonDTO); } /** * 收入数据分析 * @return R */ @PostMapping("/contractIncomeAnalysis") public R contractIncomeAnalysis(@RequestBody AnalysisCommonDTO commonDTO) { return statisticService.contractIncomeAnalysis(commonDTO); } /** * 子公司收入数据分析 * @return R */ @PostMapping("/contractIncomeAnalysisByComp") public R contractIncomeAnalysisByComp(@RequestBody AnalysisCommonDTO commonDTO) { return statisticService.contractIncomeAnalysisByComp(commonDTO); } /** * 分区域合同金额统计 * @return R */ @PostMapping("/contractAmountAnalysisByArea") public R contractAmountAnalysisByArea(@RequestBody AnalysisCommonDTO commonDTO) { return statisticService.contractAmountAnalysisByArea(commonDTO); } /** * 分行业合同金额统计 * @return R */ @PostMapping("/contractAmountAnalysisByIndustry") public R contractAmountAnalysisByIndustry(@RequestBody AnalysisCommonDTO commonDTO) { return statisticService.contractAmountAnalysisByIndustry(commonDTO); } /** * 分市场合同金额统计 * @return R */ @PostMapping("/contractAmountAnalysisByMarket") public R contractAmountAnalysisByMarket(@RequestBody AnalysisCommonDTO commonDTO) { return statisticService.contractAmountAnalysisByMarket(commonDTO); } /** * 分供应属性业务合同金额统计 * @return R */ @PostMapping("/contractAmountAnalysisBySpcBus") public R contractAmountAnalysisBySpcBus(@RequestBody AnalysisCommonDTO commonDTO) { return statisticService.contractAmountAnalysisBySpcBus(commonDTO); } /** * 分出口业务合同金额统计 * @return R */ @PostMapping("/contractAmountAnalysisByExportBus") public R contractAmountAnalysisByExportBus(@RequestBody AnalysisCommonDTO commonDTO) { return statisticService.contractAmountAnalysisByExportBus(commonDTO); } } platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/mapper/ContractMapper.java
@@ -1,13 +1,16 @@ package com.by4cloud.platformx.business.mapper; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.by4cloud.platformx.business.dto.AnalysisCommonDTO; import com.by4cloud.platformx.business.dto.CreditSalesQueryDTO; import com.by4cloud.platformx.business.entity.Contract; import com.by4cloud.platformx.business.vo.ContractCreditSalesPageVo; import com.by4cloud.platformx.business.vo.*; import com.by4cloud.platformx.common.data.datascope.DataScope; import com.by4cloud.platformx.common.data.datascope.PlatformxBaseMapper; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; import java.util.List; /** * @author 28254 @@ -15,4 +18,40 @@ @Mapper public interface ContractMapper extends PlatformxBaseMapper<Contract> { Page<ContractCreditSalesPageVo> pageByCredit(Page page, @Param("queryDTO") CreditSalesQueryDTO queryDTO, DataScope comp_id); List<ContractAmountAnalysisVo> contractAmountAnalysis(@Param("queryDTO")AnalysisCommonDTO commonDTO,@Param("compIds")List<Long> compIds); List<ContractIncomeAnalysisVo> contractIncomeAnalysis(@Param("queryDTO")AnalysisCommonDTO commonDTO,@Param("compIds")List<Long> compIds); void setSession(); List<ContractIncomeAnalysisCompVo> contractIncomeAnalysisByComp(@Param("queryDTO")AnalysisCommonDTO commonDTO,@Param("compId") Long compId); List<ContractIncomeAnalysisAreaVo> contractAmountAnalysisByArea(@Param("queryDTO")AnalysisCommonDTO commonDTO ,@Param("compIds")List<Long> compIds,@Param("smjCompId") Long smj, @Param("sgbCompId")Long sgb, @Param("jxcCompId")Long jxc, @Param("tfCompId") Long tfgs,@Param("ymjCompId") Long ymj); List<ContractIncomeAnalysisAreaVo> contractAmountAnalysisByIndustry(@Param("queryDTO")AnalysisCommonDTO commonDTO ,@Param("compIds")List<Long> compIds,@Param("smjCompId") Long smj, @Param("sgbCompId")Long sgb, @Param("jxcCompId")Long jxc, @Param("tfCompId") Long tfgs,@Param("ymjCompId") Long ymj); List<ContractIncomeAnalysisAreaVo> contractAmountAnalysisByMarket(@Param("queryDTO")AnalysisCommonDTO commonDTO ,@Param("compIds")List<Long> compIds,@Param("smjCompId") Long smj, @Param("sgbCompId")Long sgb, @Param("jxcCompId")Long jxc, @Param("tfCompId") Long tfgs,@Param("ymjCompId") Long ymj); List<ContractIncomeAnalysisAreaVo> contractAmountAnalysisBySpcBus(@Param("queryDTO")AnalysisCommonDTO commonDTO ,@Param("compIds")List<Long> compIds,@Param("smjCompId") Long smj, @Param("sgbCompId")Long sgb, @Param("jxcCompId")Long jxc, @Param("tfCompId") Long tfgs,@Param("ymjCompId") Long ymj); List<ContractIncomeAnalysisAreaVo> contractAmountAnalysisByExportBus(@Param("queryDTO")AnalysisCommonDTO commonDTO ,@Param("compIds")List<Long> compIds,@Param("smjCompId") Long smj, @Param("sgbCompId")Long sgb, @Param("jxcCompId")Long jxc, @Param("tfCompId") Long tfgs,@Param("ymjCompId") Long ymj); List<ContractAmountAnalysisCompVo> contractAmountAnalysisByComp(@Param("queryDTO")AnalysisCommonDTO commonDTO,@Param("compId") Long compId); } platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/service/StatisticService.java
New file @@ -0,0 +1,24 @@ package com.by4cloud.platformx.business.service; import com.by4cloud.platformx.business.dto.AnalysisCommonDTO; import com.by4cloud.platformx.common.core.util.R; public interface StatisticService { R contractAmountAnalysis(AnalysisCommonDTO commonDTO); R contractIncomeAnalysis(AnalysisCommonDTO commonDTO); R contractIncomeAnalysisByComp(AnalysisCommonDTO commonDTO); R contractAmountAnalysisByArea(AnalysisCommonDTO commonDTO); R contractAmountAnalysisByIndustry(AnalysisCommonDTO commonDTO); R contractAmountAnalysisByMarket(AnalysisCommonDTO commonDTO); R contractAmountAnalysisBySpcBus(AnalysisCommonDTO commonDTO); R contractAmountAnalysisByExportBus(AnalysisCommonDTO commonDTO); R contractAmountAnalysisByComp(AnalysisCommonDTO commonDTO); } platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/service/impl/PaymentOffsetServiceImpl.java
@@ -55,6 +55,7 @@ entity.setConfirmTime(paymentOffset.getOffsetTime()); entity.setTransationAmount(paymentOffset.getOffsetAmount()); entity.setTotalAmount(paymentOffset.getOffsetAmount().add(lastTotal)); entity.setPayType(2); paymentConfirmMapper.insert(entity); //更新付款阶段付款 List<ContractPaymentSchedule> scheduleList = contractPaymentScheduleMapper.selectList(Wrappers.<ContractPaymentSchedule>lambdaQuery() platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/service/impl/StatisticServiceImpl.java
New file @@ -0,0 +1,287 @@ package com.by4cloud.platformx.business.service.impl; import cn.hutool.core.bean.BeanUtil; import cn.hutool.core.util.ArrayUtil; import cn.hutool.core.util.StrUtil; import com.by4cloud.platformx.admin.api.entity.SysDept; import com.by4cloud.platformx.admin.api.entity.SysDictItem; import com.by4cloud.platformx.admin.api.feign.RemoteDeptService; import com.by4cloud.platformx.admin.api.feign.RemoteDictService; import com.by4cloud.platformx.business.dto.AnalysisCommonDTO; import com.by4cloud.platformx.business.mapper.ContractMapper; import com.by4cloud.platformx.business.service.StatisticService; import com.by4cloud.platformx.business.vo.*; import com.by4cloud.platformx.common.core.util.R; import com.by4cloud.platformx.common.security.util.SecurityUtils; import lombok.RequiredArgsConstructor; import org.springframework.beans.BeanUtils; import org.springframework.beans.factory.annotation.Value; import org.springframework.stereotype.Service; import java.util.ArrayList; import java.util.Collections; import java.util.List; import java.util.Map; import java.util.stream.Collectors; import java.util.stream.Stream; @Service @RequiredArgsConstructor public class StatisticServiceImpl implements StatisticService { private final ContractMapper contractMapper; private final RemoteDeptService remoteDeptService; private final RemoteDictService remoteDictService; @Value("${dept.smj}") private String smj; @Value("${dept.sgb}") private String sgb; @Value("${dept.jxc}") private String jxc; @Value("${dept.tfgs}") private String tfgs; @Value("${dept.ymj}") private String ymj; @Override public R contractAmountAnalysis(AnalysisCommonDTO commonDTO) { List<Long> compIds = new ArrayList<>(); compIds.add(Long.valueOf(sgb)); compIds.add(Long.valueOf(smj)); compIds.add(Long.valueOf(tfgs)); compIds.add(Long.valueOf(ymj)); compIds.add(Long.valueOf(jxc)); List<ContractAmountAnalysisVo> contractAmountAnalysisVoList = contractMapper.contractAmountAnalysis(commonDTO,compIds); return R.ok(contractAmountAnalysisVoList); } @Override public R contractAmountAnalysisByComp(AnalysisCommonDTO commonDTO) { List<ContractAmountAnalysisCompVo> contractIncomeAnalysisVoList = contractMapper.contractAmountAnalysisByComp(commonDTO, SecurityUtils.getUser().getCompId()); return R.ok(contractIncomeAnalysisVoList); } @Override public R contractIncomeAnalysis(AnalysisCommonDTO commonDTO) { List<Long> compIds = new ArrayList<>(); compIds.add(Long.valueOf(sgb)); compIds.add(Long.valueOf(smj)); compIds.add(Long.valueOf(tfgs)); compIds.add(Long.valueOf(ymj)); compIds.add(Long.valueOf(jxc)); List<ContractIncomeAnalysisVo> contractIncomeAnalysisVoList = contractMapper.contractIncomeAnalysis(commonDTO,compIds); return R.ok(contractIncomeAnalysisVoList); } @Override public R contractIncomeAnalysisByComp(AnalysisCommonDTO commonDTO) { List<ContractIncomeAnalysisCompVo> contractIncomeAnalysisVoList = contractMapper.contractIncomeAnalysisByComp(commonDTO, SecurityUtils.getUser().getCompId()); return R.ok(contractIncomeAnalysisVoList); } @Override public R contractAmountAnalysisByArea(AnalysisCommonDTO commonDTO) { R<List<SysDictItem>> r = remoteDictService.getDictByType("company_area"); List<SysDictItem> items = r.getData(); if (ArrayUtil.isEmpty(items.toArray())){ return R.failed("区域字段获取异常"); } List<Long> compIds = new ArrayList<>(); compIds.add(Long.valueOf(sgb)); compIds.add(Long.valueOf(smj)); compIds.add(Long.valueOf(tfgs)); compIds.add(Long.valueOf(ymj)); compIds.add(Long.valueOf(jxc)); List<ContractIncomeAnalysisAreaVo> contractIncomeAnalysisVoList = items.stream().map(item -> { ContractIncomeAnalysisAreaVo analysisAreaVo = new ContractIncomeAnalysisAreaVo(); analysisAreaVo.setAreaId(item.getItemValue()); analysisAreaVo.setAreaName(item.getLabel()); return analysisAreaVo; }).collect(Collectors.toList()); List<ContractIncomeAnalysisAreaVo> statisticList = contractMapper.contractAmountAnalysisByArea(commonDTO ,compIds,Long.valueOf(smj),Long.valueOf(sgb),Long.valueOf(jxc),Long.valueOf(tfgs),Long.valueOf(ymj)); contractIncomeAnalysisVoList.stream().forEach(vo1->{ statisticList.stream().forEach(vo2->{ if (StrUtil.equals(vo1.getAreaId(),vo2.getAreaId())){ vo1.setJxczb(vo2.getJxczb()); vo1.setJxcze(vo2.getJxcze()); vo1.setSgbzb(vo2.getSgbzb()); vo1.setSgbze(vo2.getSgbze()); vo1.setSmjzb(vo2.getSmjzb()); vo1.setSmjze(vo2.getSmjze()); vo1.setTfzb(vo2.getTfzb()); vo1.setTfze(vo2.getTfze()); vo1.setYmjzb(vo2.getYmjzb()); vo1.setYmjze(vo2.getYmjze()); } }); }); return R.ok(contractIncomeAnalysisVoList); } @Override public R contractAmountAnalysisByIndustry(AnalysisCommonDTO commonDTO) { R<List<SysDictItem>> r = remoteDictService.getDictByType("industry_sector"); List<SysDictItem> items = r.getData(); if (ArrayUtil.isEmpty(items.toArray())){ return R.failed("区域字段获取异常"); } List<Long> compIds = new ArrayList<>(); compIds.add(Long.valueOf(sgb)); compIds.add(Long.valueOf(smj)); compIds.add(Long.valueOf(tfgs)); compIds.add(Long.valueOf(ymj)); compIds.add(Long.valueOf(jxc)); List<ContractIncomeAnalysisAreaVo> contractIncomeAnalysisVoList = items.stream().map(item -> { ContractIncomeAnalysisAreaVo analysisAreaVo = new ContractIncomeAnalysisAreaVo(); analysisAreaVo.setAreaId(item.getItemValue()); analysisAreaVo.setAreaName(item.getLabel()); return analysisAreaVo; }).collect(Collectors.toList()); List<ContractIncomeAnalysisAreaVo> statisticList = contractMapper.contractAmountAnalysisByIndustry(commonDTO ,compIds,Long.valueOf(smj),Long.valueOf(sgb),Long.valueOf(jxc),Long.valueOf(tfgs),Long.valueOf(ymj)); contractIncomeAnalysisVoList.stream().forEach(vo1->{ statisticList.stream().forEach(vo2->{ if (StrUtil.equals(vo1.getAreaId(),vo2.getAreaId())){ vo1.setJxczb(vo2.getJxczb()); vo1.setJxcze(vo2.getJxcze()); vo1.setSgbzb(vo2.getSgbzb()); vo1.setSgbze(vo2.getSgbze()); vo1.setSmjzb(vo2.getSmjzb()); vo1.setSmjze(vo2.getSmjze()); vo1.setTfzb(vo2.getTfzb()); vo1.setTfze(vo2.getTfze()); vo1.setYmjzb(vo2.getYmjzb()); vo1.setYmjze(vo2.getYmjze()); } }); }); return R.ok(contractIncomeAnalysisVoList); } @Override public R contractAmountAnalysisByMarket(AnalysisCommonDTO commonDTO) { R<List<SysDictItem>> r = remoteDictService.getDictByType("market_type"); List<SysDictItem> items = r.getData(); if (ArrayUtil.isEmpty(items.toArray())){ return R.failed("区域字段获取异常"); } List<Long> compIds = new ArrayList<>(); compIds.add(Long.valueOf(sgb)); compIds.add(Long.valueOf(smj)); compIds.add(Long.valueOf(tfgs)); compIds.add(Long.valueOf(ymj)); compIds.add(Long.valueOf(jxc)); List<ContractIncomeAnalysisAreaVo> contractIncomeAnalysisVoList = items.stream().map(item -> { ContractIncomeAnalysisAreaVo analysisAreaVo = new ContractIncomeAnalysisAreaVo(); analysisAreaVo.setAreaId(item.getItemValue()); analysisAreaVo.setAreaName(item.getLabel()); return analysisAreaVo; }).collect(Collectors.toList()); List<ContractIncomeAnalysisAreaVo> statisticList = contractMapper.contractAmountAnalysisByMarket(commonDTO ,compIds,Long.valueOf(smj),Long.valueOf(sgb),Long.valueOf(jxc),Long.valueOf(tfgs),Long.valueOf(ymj)); contractIncomeAnalysisVoList.stream().forEach(vo1->{ statisticList.stream().forEach(vo2->{ if (StrUtil.equals(vo1.getAreaId(),vo2.getAreaId())){ vo1.setJxczb(vo2.getJxczb()); vo1.setJxcze(vo2.getJxcze()); vo1.setSgbzb(vo2.getSgbzb()); vo1.setSgbze(vo2.getSgbze()); vo1.setSmjzb(vo2.getSmjzb()); vo1.setSmjze(vo2.getSmjze()); vo1.setTfzb(vo2.getTfzb()); vo1.setTfze(vo2.getTfze()); vo1.setYmjzb(vo2.getYmjzb()); vo1.setYmjze(vo2.getYmjze()); } }); }); return R.ok(contractIncomeAnalysisVoList); } @Override public R contractAmountAnalysisBySpcBus(AnalysisCommonDTO commonDTO) { R<List<SysDictItem>> r = remoteDictService.getDictByType("supply_attribute"); List<SysDictItem> items = r.getData(); if (ArrayUtil.isEmpty(items.toArray())){ return R.failed("区域字段获取异常"); } List<Long> compIds = new ArrayList<>(); compIds.add(Long.valueOf(sgb)); compIds.add(Long.valueOf(smj)); compIds.add(Long.valueOf(tfgs)); compIds.add(Long.valueOf(ymj)); compIds.add(Long.valueOf(jxc)); List<ContractIncomeAnalysisAreaVo> contractIncomeAnalysisVoList = items.stream().map(item -> { ContractIncomeAnalysisAreaVo analysisAreaVo = new ContractIncomeAnalysisAreaVo(); analysisAreaVo.setAreaId(item.getItemValue()); analysisAreaVo.setAreaName(item.getLabel()); return analysisAreaVo; }).collect(Collectors.toList()); List<ContractIncomeAnalysisAreaVo> statisticList = contractMapper.contractAmountAnalysisBySpcBus(commonDTO ,compIds,Long.valueOf(smj),Long.valueOf(sgb),Long.valueOf(jxc),Long.valueOf(tfgs),Long.valueOf(ymj)); contractIncomeAnalysisVoList.stream().forEach(vo1->{ statisticList.stream().forEach(vo2->{ if (StrUtil.equals(vo1.getAreaId(),vo2.getAreaId())){ vo1.setJxczb(vo2.getJxczb()); vo1.setJxcze(vo2.getJxcze()); vo1.setSgbzb(vo2.getSgbzb()); vo1.setSgbze(vo2.getSgbze()); vo1.setSmjzb(vo2.getSmjzb()); vo1.setSmjze(vo2.getSmjze()); vo1.setTfzb(vo2.getTfzb()); vo1.setTfze(vo2.getTfze()); vo1.setYmjzb(vo2.getYmjzb()); vo1.setYmjze(vo2.getYmjze()); } }); }); return R.ok(contractIncomeAnalysisVoList); } @Override public R contractAmountAnalysisByExportBus(AnalysisCommonDTO commonDTO) { R<List<SysDictItem>> r = remoteDictService.getDictByType("export_attr"); List<SysDictItem> items = r.getData(); if (ArrayUtil.isEmpty(items.toArray())){ return R.failed("区域字段获取异常"); } List<Long> compIds = new ArrayList<>(); compIds.add(Long.valueOf(sgb)); compIds.add(Long.valueOf(smj)); compIds.add(Long.valueOf(tfgs)); compIds.add(Long.valueOf(ymj)); compIds.add(Long.valueOf(jxc)); List<ContractIncomeAnalysisAreaVo> contractIncomeAnalysisVoList = items.stream().map(item -> { ContractIncomeAnalysisAreaVo analysisAreaVo = new ContractIncomeAnalysisAreaVo(); analysisAreaVo.setAreaId(item.getItemValue()); analysisAreaVo.setAreaName(item.getLabel()); return analysisAreaVo; }).collect(Collectors.toList()); List<ContractIncomeAnalysisAreaVo> statisticList = contractMapper.contractAmountAnalysisByExportBus(commonDTO ,compIds,Long.valueOf(smj),Long.valueOf(sgb),Long.valueOf(jxc),Long.valueOf(tfgs),Long.valueOf(ymj)); contractIncomeAnalysisVoList.stream().forEach(vo1->{ statisticList.stream().forEach(vo2->{ if (StrUtil.equals(vo1.getAreaId(),vo2.getAreaId())){ vo1.setJxczb(vo2.getJxczb()); vo1.setJxcze(vo2.getJxcze()); vo1.setSgbzb(vo2.getSgbzb()); vo1.setSgbze(vo2.getSgbze()); vo1.setSmjzb(vo2.getSmjzb()); vo1.setSmjze(vo2.getSmjze()); vo1.setTfzb(vo2.getTfzb()); vo1.setTfze(vo2.getTfze()); vo1.setYmjzb(vo2.getYmjzb()); vo1.setYmjze(vo2.getYmjze()); } }); }); return R.ok(contractIncomeAnalysisVoList); } } platformx-business-finance-biz/src/main/resources/mapper/ContractMapper.xml
@@ -4,6 +4,9 @@ <mapper namespace="com.by4cloud.platformx.business.mapper.ContractMapper"> <update id="setSession"> SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; </update> <select id="pageByCredit" resultType="com.by4cloud.platformx.business.vo.ContractCreditSalesPageVo"> SELECT id, @@ -100,4 +103,712 @@ </otherwise> </choose> </select> <select id="contractAmountAnalysis" resultType="com.by4cloud.platformx.business.vo.ContractAmountAnalysisVo"> SELECT party_b_name AS 'compName', SUM(m01) AS 'month1', SUM(m02) AS 'month2', SUM(m03) AS 'month3', SUM(m04) AS 'month4', SUM(m05) AS 'month5', SUM(m06) AS 'month6', SUM(m07) AS 'month7', SUM(m08) AS 'month8', SUM(m09) AS 'month9', SUM(m10) AS 'month10', SUM(m11) AS 'month11', SUM(m12) AS 'month12' FROM ( SELECT COALESCE(party_b, '未知甲方') AS party_b_name, SUM(CASE WHEN MONTH(sign_date) = 1 THEN amount ELSE 0 END) AS m01, SUM(CASE WHEN MONTH(sign_date) = 2 THEN amount ELSE 0 END) AS m02, SUM(CASE WHEN MONTH(sign_date) = 3 THEN amount ELSE 0 END) AS m03, SUM(CASE WHEN MONTH(sign_date) = 4 THEN amount ELSE 0 END) AS m04, SUM(CASE WHEN MONTH(sign_date) = 5 THEN amount ELSE 0 END) AS m05, SUM(CASE WHEN MONTH(sign_date) = 6 THEN amount ELSE 0 END) AS m06, SUM(CASE WHEN MONTH(sign_date) = 7 THEN amount ELSE 0 END) AS m07, SUM(CASE WHEN MONTH(sign_date) = 8 THEN amount ELSE 0 END) AS m08, SUM(CASE WHEN MONTH(sign_date) = 9 THEN amount ELSE 0 END) AS m09, SUM(CASE WHEN MONTH(sign_date) = 10 THEN amount ELSE 0 END) AS m10, SUM(CASE WHEN MONTH(sign_date) = 11 THEN amount ELSE 0 END) AS m11, SUM(CASE WHEN MONTH(sign_date) = 12 THEN amount ELSE 0 END) AS m12, SUM(amount) AS total_amt FROM contract WHERE del_flag = '0' AND amount IS NOT NULL AND sign_date IS NOT NULL AND YEAR(sign_date) = #{queryDTO.year} AND party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> GROUP BY party_b_id, party_b UNION ALL SELECT '总计' AS party_b_name, SUM(CASE WHEN MONTH(sign_date) = 1 THEN amount ELSE 0 END), SUM(CASE WHEN MONTH(sign_date) = 2 THEN amount ELSE 0 END), SUM(CASE WHEN MONTH(sign_date) = 3 THEN amount ELSE 0 END), SUM(CASE WHEN MONTH(sign_date) = 4 THEN amount ELSE 0 END), SUM(CASE WHEN MONTH(sign_date) = 5 THEN amount ELSE 0 END), SUM(CASE WHEN MONTH(sign_date) = 6 THEN amount ELSE 0 END), SUM(CASE WHEN MONTH(sign_date) = 7 THEN amount ELSE 0 END), SUM(CASE WHEN MONTH(sign_date) = 8 THEN amount ELSE 0 END), SUM(CASE WHEN MONTH(sign_date) = 9 THEN amount ELSE 0 END), SUM(CASE WHEN MONTH(sign_date) = 10 THEN amount ELSE 0 END), SUM(CASE WHEN MONTH(sign_date) = 11 THEN amount ELSE 0 END), SUM(CASE WHEN MONTH(sign_date) = 12 THEN amount ELSE 0 END), SUM(amount) FROM contract WHERE del_flag = '0' AND amount IS NOT NULL AND sign_date IS NOT NULL AND YEAR(sign_date) = #{queryDTO.year} AND party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) AS pivot_table GROUP BY party_b_name ORDER BY CASE WHEN party_b_name = '总计' THEN 1 ELSE 0 END, party_b_name; </select> <select id="contractIncomeAnalysis" resultType="com.by4cloud.platformx.business.vo.ContractIncomeAnalysisVo"> SELECT compName, SUM(CASE WHEN month_num = 1 THEN total_amount ELSE 0 END) AS 'srMonth1', SUM(CASE WHEN month_num = 1 AND pay_type = 0 THEN total_amount ELSE 0 END) AS 'xhMonth1', SUM(CASE WHEN month_num = 1 AND pay_type = 1 THEN total_amount ELSE 0 END) AS 'cdMonth1', SUM(CASE WHEN month_num = 1 AND pay_type = 2 THEN total_amount ELSE 0 END) AS 'qtMonth1', SUM(CASE WHEN month_num = 2 THEN total_amount ELSE 0 END) AS 'srMonth2', SUM(CASE WHEN month_num = 2 AND pay_type = 0 THEN total_amount ELSE 0 END) AS 'xhMonth2', SUM(CASE WHEN month_num = 2 AND pay_type = 1 THEN total_amount ELSE 0 END) AS 'cdMonth2', SUM(CASE WHEN month_num = 2 AND pay_type = 2 THEN total_amount ELSE 0 END) AS 'qtMonth2', SUM(CASE WHEN month_num = 3 THEN total_amount ELSE 0 END) AS 'srMonth3', SUM(CASE WHEN month_num = 3 AND pay_type = 0 THEN total_amount ELSE 0 END) AS 'xhMonth3', SUM(CASE WHEN month_num = 3 AND pay_type = 1 THEN total_amount ELSE 0 END) AS 'cdMonth3', SUM(CASE WHEN month_num = 3 AND pay_type = 2 THEN total_amount ELSE 0 END) AS 'qtMonth3', SUM(CASE WHEN month_num = 4 THEN total_amount ELSE 0 END) AS 'srMonth4', SUM(CASE WHEN month_num = 4 AND pay_type = 0 THEN total_amount ELSE 0 END) AS 'xhMonth4', SUM(CASE WHEN month_num = 4 AND pay_type = 1 THEN total_amount ELSE 0 END) AS 'cdMonth4', SUM(CASE WHEN month_num = 4 AND pay_type = 2 THEN total_amount ELSE 0 END) AS 'qtMonth4', SUM(CASE WHEN month_num = 5 THEN total_amount ELSE 0 END) AS 'srMonth5', SUM(CASE WHEN month_num = 5 AND pay_type = 0 THEN total_amount ELSE 0 END) AS 'xhMonth5', SUM(CASE WHEN month_num = 5 AND pay_type = 1 THEN total_amount ELSE 0 END) AS 'cdMonth5', SUM(CASE WHEN month_num = 5 AND pay_type = 2 THEN total_amount ELSE 0 END) AS 'qtMonth5', SUM(CASE WHEN month_num = 6 THEN total_amount ELSE 0 END) AS 'srMonth6', SUM(CASE WHEN month_num = 6 AND pay_type = 0 THEN total_amount ELSE 0 END) AS 'xhMonth6', SUM(CASE WHEN month_num = 6 AND pay_type = 1 THEN total_amount ELSE 0 END) AS 'cdMonth6', SUM(CASE WHEN month_num = 6 AND pay_type = 2 THEN total_amount ELSE 0 END) AS 'qtMonth6', SUM(CASE WHEN month_num = 7 THEN total_amount ELSE 0 END) AS 'srMonth7', SUM(CASE WHEN month_num = 7 AND pay_type = 0 THEN total_amount ELSE 0 END) AS 'xhMonth7', SUM(CASE WHEN month_num = 7 AND pay_type = 1 THEN total_amount ELSE 0 END) AS 'cdMonth7', SUM(CASE WHEN month_num = 7 AND pay_type = 2 THEN total_amount ELSE 0 END) AS 'qtMonth7', SUM(CASE WHEN month_num = 8 THEN total_amount ELSE 0 END) AS 'srMonth8', SUM(CASE WHEN month_num = 8 AND pay_type = 0 THEN total_amount ELSE 0 END) AS 'xhMonth8', SUM(CASE WHEN month_num = 8 AND pay_type = 1 THEN total_amount ELSE 0 END) AS 'cdMonth8', SUM(CASE WHEN month_num = 8 AND pay_type = 2 THEN total_amount ELSE 0 END) AS 'qtMonth8', SUM(CASE WHEN month_num = 9 THEN total_amount ELSE 0 END) AS 'srMonth9', SUM(CASE WHEN month_num = 9 AND pay_type = 0 THEN total_amount ELSE 0 END) AS 'xhMonth9', SUM(CASE WHEN month_num = 9 AND pay_type = 1 THEN total_amount ELSE 0 END) AS 'cdMonth9', SUM(CASE WHEN month_num = 9 AND pay_type = 2 THEN total_amount ELSE 0 END) AS 'qtMonth9', SUM(CASE WHEN month_num = 10 THEN total_amount ELSE 0 END) AS 'srMonth10', SUM(CASE WHEN month_num = 10 AND pay_type = 0 THEN total_amount ELSE 0 END) AS 'xhMonth10', SUM(CASE WHEN month_num = 10 AND pay_type = 1 THEN total_amount ELSE 0 END) AS 'cdMonth10', SUM(CASE WHEN month_num = 10 AND pay_type = 2 THEN total_amount ELSE 0 END) AS 'qtMonth10', SUM(CASE WHEN month_num = 11 THEN total_amount ELSE 0 END) AS 'srMonth11', SUM(CASE WHEN month_num = 11 AND pay_type = 0 THEN total_amount ELSE 0 END) AS 'xhMonth11', SUM(CASE WHEN month_num = 11 AND pay_type = 1 THEN total_amount ELSE 0 END) AS 'cdMonth11', SUM(CASE WHEN month_num = 11 AND pay_type = 2 THEN total_amount ELSE 0 END) AS 'qtMonth11', SUM(CASE WHEN month_num = 12 THEN total_amount ELSE 0 END) AS 'srMonth12', SUM(CASE WHEN month_num = 12 AND pay_type = 0 THEN total_amount ELSE 0 END) AS 'xhMonth12', SUM(CASE WHEN month_num = 12 AND pay_type = 1 THEN total_amount ELSE 0 END) AS 'cdMonth12', SUM(CASE WHEN month_num = 12 AND pay_type = 2 THEN total_amount ELSE 0 END) AS 'qtMonth12' FROM ( SELECT COALESCE(c.party_b, '未知单位') AS compName, MONTH(confirm_time) AS month_num, pay_type, COALESCE(transation_amount, 0) AS total_amount FROM payment_confirm pc,contract c WHERE pc.del_flag = '0' And c.del_flag = '0' AND pc.contract_id = c.id AND confirm_time IS NOT NULL AND transation_amount IS NOT NULL AND YEAR(confirm_time) = #{queryDTO.year} AND party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) AS source_data GROUP BY compName </select> <select id="contractIncomeAnalysisByComp" resultType="com.by4cloud.platformx.business.vo.ContractIncomeAnalysisCompVo"> SELECT months.yf, COALESCE ( SUM( transation_amount ), 0 ) AS sr, COALESCE ( SUM( CASE WHEN pay_type = 0 THEN COALESCE ( transation_amount, 0 ) ELSE 0 END ), 0 ) AS xh, COALESCE ( SUM( CASE WHEN pay_type = 1 THEN COALESCE ( transation_amount, 0 ) ELSE 0 END ), 0 ) AS cd, COALESCE ( SUM( CASE WHEN pay_type = 2 THEN COALESCE ( transation_amount, 0 ) ELSE 0 END ), 0 ) AS qt FROM ( SELECT '1月' AS yf UNION ALL SELECT '2月' UNION ALL SELECT '3月' UNION ALL SELECT '4月' UNION ALL SELECT '5月' UNION ALL SELECT '6月' UNION ALL SELECT '7月' UNION ALL SELECT '8月' UNION ALL SELECT '9月' UNION ALL SELECT '10月' UNION ALL SELECT '11月' UNION ALL SELECT '12月' ) AS months LEFT JOIN payment_confirm pc ON months.yf = CONCAT( MONTH ( pc.confirm_time ), '月' ) AND pc.del_flag = '0' AND pc.confirm_time IS NOT NULL AND pc.transation_amount IS NOT NULL AND YEAR ( pc.confirm_time ) = #{queryDTO.year} AND pc.comp_id = #{compId} WHERE 1 = 1 GROUP BY months.yf </select> <select id="contractAmountAnalysisByArea" resultType="com.by4cloud.platformx.business.vo.ContractIncomeAnalysisAreaVo"> SELECT b.area_id, SUM( CASE WHEN c.party_b_id = #{sgbCompId} THEN c.amount ELSE 0 END ) AS sgbze, ROUND( SUM( CASE WHEN c.party_b_id = #{sgbCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS zgbzb, SUM( CASE WHEN c.party_b_id = #{jxcCompId} THEN c.amount ELSE 0 END ) AS jxcze, ROUND( SUM( CASE WHEN c.party_b_id = #{jxcCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS jxczb , SUM( CASE WHEN c.party_b_id = #{smjCompId} THEN c.amount ELSE 0 END ) AS smjze, ROUND( SUM( CASE WHEN c.party_b_id = #{smjCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS smjzb , SUM( CASE WHEN c.party_b_id = #{ymjCompId} THEN c.amount ELSE 0 END ) AS ymjze, ROUND( SUM( CASE WHEN c.party_b_id = #{ymjCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS ymjzb , SUM( CASE WHEN c.party_b_id = #{tfCompId} THEN c.amount ELSE 0 END ) AS tfze, ROUND( SUM( CASE WHEN c.party_b_id = #{tfCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS tfzb FROM contract c JOIN business_customer b ON c.party_a_id = b.id WHERE b.area_id IS NOT NULL AND YEAR ( sign_date ) = #{queryDTO.year} GROUP BY b.area_id </select> <select id="contractAmountAnalysisByIndustry" resultType="com.by4cloud.platformx.business.vo.ContractIncomeAnalysisAreaVo"> SELECT b.industry_id area_id, SUM( CASE WHEN c.party_b_id = #{sgbCompId} THEN c.amount ELSE 0 END ) AS sgbze, ROUND( SUM( CASE WHEN c.party_b_id = #{sgbCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS zgbzb, SUM( CASE WHEN c.party_b_id = #{jxcCompId} THEN c.amount ELSE 0 END ) AS jxcze, ROUND( SUM( CASE WHEN c.party_b_id = #{jxcCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS jxczb , SUM( CASE WHEN c.party_b_id = #{smjCompId} THEN c.amount ELSE 0 END ) AS smjze, ROUND( SUM( CASE WHEN c.party_b_id = #{smjCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS smjzb , SUM( CASE WHEN c.party_b_id = #{ymjCompId} THEN c.amount ELSE 0 END ) AS ymjze, ROUND( SUM( CASE WHEN c.party_b_id = #{ymjCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS ymjzb , SUM( CASE WHEN c.party_b_id = #{tfCompId} THEN c.amount ELSE 0 END ) AS tfze, ROUND( SUM( CASE WHEN c.party_b_id = #{tfCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS tfzb FROM contract c JOIN business_customer b ON c.party_a_id = b.id WHERE b.industry_id IS NOT NULL AND YEAR ( sign_date ) = #{queryDTO.year} GROUP BY b.industry_id </select> <select id="contractAmountAnalysisByMarket" resultType="com.by4cloud.platformx.business.vo.ContractIncomeAnalysisAreaVo"> SELECT b.class_id area_id, SUM( CASE WHEN c.party_b_id = #{sgbCompId} THEN c.amount ELSE 0 END ) AS sgbze, ROUND( SUM( CASE WHEN c.party_b_id = #{sgbCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS zgbzb, SUM( CASE WHEN c.party_b_id = #{jxcCompId} THEN c.amount ELSE 0 END ) AS jxcze, ROUND( SUM( CASE WHEN c.party_b_id = #{jxcCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS jxczb , SUM( CASE WHEN c.party_b_id = #{smjCompId} THEN c.amount ELSE 0 END ) AS smjze, ROUND( SUM( CASE WHEN c.party_b_id = #{smjCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS smjzb , SUM( CASE WHEN c.party_b_id = #{ymjCompId} THEN c.amount ELSE 0 END ) AS ymjze, ROUND( SUM( CASE WHEN c.party_b_id = #{ymjCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS ymjzb , SUM( CASE WHEN c.party_b_id = #{tfCompId} THEN c.amount ELSE 0 END ) AS tfze, ROUND( SUM( CASE WHEN c.party_b_id = #{tfCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS tfzb FROM contract c JOIN business_customer b ON c.party_a_id = b.id WHERE b.class_id IS NOT NULL AND YEAR ( sign_date ) = #{queryDTO.year} GROUP BY b.class_id </select> <select id="contractAmountAnalysisByExportBus" resultType="com.by4cloud.platformx.business.vo.ContractIncomeAnalysisAreaVo"> SELECT b.export_service_attr area_id, SUM( CASE WHEN c.party_b_id = #{sgbCompId} THEN c.amount ELSE 0 END ) AS sgbze, ROUND( SUM( CASE WHEN c.party_b_id = #{sgbCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS zgbzb, SUM( CASE WHEN c.party_b_id = #{jxcCompId} THEN c.amount ELSE 0 END ) AS jxcze, ROUND( SUM( CASE WHEN c.party_b_id = #{jxcCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS jxczb , SUM( CASE WHEN c.party_b_id = #{smjCompId} THEN c.amount ELSE 0 END ) AS smjze, ROUND( SUM( CASE WHEN c.party_b_id = #{smjCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS smjzb , SUM( CASE WHEN c.party_b_id = #{ymjCompId} THEN c.amount ELSE 0 END ) AS ymjze, ROUND( SUM( CASE WHEN c.party_b_id = #{ymjCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS ymjzb , SUM( CASE WHEN c.party_b_id = #{tfCompId} THEN c.amount ELSE 0 END ) AS tfze, ROUND( SUM( CASE WHEN c.party_b_id = #{tfCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS tfzb FROM contract c JOIN business_customer b ON c.party_a_id = b.id WHERE b.export_service_attr IS NOT NULL AND YEAR ( sign_date ) = #{queryDTO.year} GROUP BY b.export_service_attr </select> <select id="contractAmountAnalysisBySpcBus" resultType="com.by4cloud.platformx.business.vo.ContractIncomeAnalysisAreaVo"> SELECT c.supply_attribute area_id, SUM( CASE WHEN c.party_b_id = #{sgbCompId} THEN c.amount ELSE 0 END ) AS sgbze, ROUND( SUM( CASE WHEN c.party_b_id = #{sgbCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS zgbzb, SUM( CASE WHEN c.party_b_id = #{jxcCompId} THEN c.amount ELSE 0 END ) AS jxcze, ROUND( SUM( CASE WHEN c.party_b_id = #{jxcCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS jxczb , SUM( CASE WHEN c.party_b_id = #{smjCompId} THEN c.amount ELSE 0 END ) AS smjze, ROUND( SUM( CASE WHEN c.party_b_id = #{smjCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS smjzb , SUM( CASE WHEN c.party_b_id = #{ymjCompId} THEN c.amount ELSE 0 END ) AS ymjze, ROUND( SUM( CASE WHEN c.party_b_id = #{ymjCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS ymjzb , SUM( CASE WHEN c.party_b_id = #{tfCompId} THEN c.amount ELSE 0 END ) AS tfze, ROUND( SUM( CASE WHEN c.party_b_id = #{tfCompId} THEN c.amount ELSE 0 END ) / ( SELECT SUM( amount ) FROM contract WHERE party_b_id IN <foreach collection="compIds" item="id" open="(" close=")" separator=","> #{id} </foreach> ) * 100, 0 ) AS tfzb FROM contract c WHERE c.supply_attribute IS NOT NULL AND YEAR ( sign_date ) = #{queryDTO.year} GROUP BY c.supply_attribute </select> <select id="contractAmountAnalysisByComp" resultType="com.by4cloud.platformx.business.vo.ContractAmountAnalysisCompVo"> SELECT m.stat_month AS yf, COALESCE(curr.total_amount, 0) AS je, CASE WHEN prev.total_amount IS NULL OR prev.total_amount = 0 THEN NULL ELSE ROUND( (COALESCE(curr.total_amount, 0) - prev.total_amount) / prev.total_amount * 100, 2 ) END AS hb, CASE WHEN last_year.total_amount IS NULL OR last_year.total_amount = 0 THEN NULL ELSE ROUND( (COALESCE(curr.total_amount, 0) - last_year.total_amount) / last_year.total_amount * 100, 2 ) END AS tb FROM ( SELECT DATE_FORMAT(MAKEDATE(${queryDTO.year}, 1), '%Y-%m') AS stat_month, 1 AS month_num UNION ALL SELECT DATE_FORMAT(DATE_ADD(MAKEDATE(${queryDTO.year}, 1), INTERVAL 1 MONTH), '%Y-%m'), 2 UNION ALL SELECT DATE_FORMAT(DATE_ADD(MAKEDATE(${queryDTO.year}, 1), INTERVAL 2 MONTH), '%Y-%m'), 3 UNION ALL SELECT DATE_FORMAT(DATE_ADD(MAKEDATE(${queryDTO.year}, 1), INTERVAL 3 MONTH), '%Y-%m'), 4 UNION ALL SELECT DATE_FORMAT(DATE_ADD(MAKEDATE(${queryDTO.year}, 1), INTERVAL 4 MONTH), '%Y-%m'), 5 UNION ALL SELECT DATE_FORMAT(DATE_ADD(MAKEDATE(${queryDTO.year}, 1), INTERVAL 5 MONTH), '%Y-%m'), 6 UNION ALL SELECT DATE_FORMAT(DATE_ADD(MAKEDATE(${queryDTO.year}, 1), INTERVAL 6 MONTH), '%Y-%m'), 7 UNION ALL SELECT DATE_FORMAT(DATE_ADD(MAKEDATE(${queryDTO.year}, 1), INTERVAL 7 MONTH), '%Y-%m'), 8 UNION ALL SELECT DATE_FORMAT(DATE_ADD(MAKEDATE(${queryDTO.year}, 1), INTERVAL 8 MONTH), '%Y-%m'), 9 UNION ALL SELECT DATE_FORMAT(DATE_ADD(MAKEDATE(${queryDTO.year}, 1), INTERVAL 9 MONTH), '%Y-%m'), 10 UNION ALL SELECT DATE_FORMAT(DATE_ADD(MAKEDATE(${queryDTO.year}, 1), INTERVAL 10 MONTH), '%Y-%m'), 11 UNION ALL SELECT DATE_FORMAT(DATE_ADD(MAKEDATE(${queryDTO.year}, 1), INTERVAL 11 MONTH), '%Y-%m'), 12 ) m LEFT JOIN ( SELECT DATE_FORMAT(sign_date, '%Y-%m') AS stat_month, SUM(amount) AS total_amount FROM contract WHERE YEAR(sign_date) = #{queryDTO.year} AND party_b_id = #{compId} GROUP BY DATE_FORMAT(sign_date, '%Y-%m') ) curr ON m.stat_month = curr.stat_month LEFT JOIN ( SELECT DATE_FORMAT(sign_date, '%Y-%m') AS stat_month, MONTH(sign_date) AS month_num, SUM(amount) AS total_amount FROM contract WHERE YEAR(sign_date) = YEAR(CURDATE()) AND party_b_id = #{compId} GROUP BY DATE_FORMAT(sign_date, '%Y-%m'), MONTH(sign_date) ) prev ON m.month_num = prev.month_num + 1 LEFT JOIN ( SELECT MONTH(sign_date) AS month_num, SUM(amount) AS total_amount FROM contract WHERE YEAR(sign_date) = YEAR(CURDATE()) - 1 AND party_b_id = #{compId} GROUP BY MONTH(sign_date) ) last_year ON m.month_num = last_year.month_num ORDER BY m.month_num </select> </mapper>
