From ff225eded880cea74563368c8c18483efc85ab28 Mon Sep 17 00:00:00 2001
From: shiyunteng <shiyunteng@example.com>
Date: 星期五, 22 五月 2026 17:29:36 +0800
Subject: [PATCH] feat:统计相关接口
---
platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/service/impl/StatisticServiceImpl.java | 287 +++++++++++
platformx-business-finance-biz/src/main/resources/mapper/ContractMapper.xml | 711 +++++++++++++++++++++++++++++
platformx-boot/src/main/resources/application-dev.yml | 10
platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/dto/AnalysisCommonDTO.java | 10
platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractAmountAnalysisVo.java | 35 +
platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractIncomeAnalysisVo.java | 106 ++++
platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractIncomeAnalysisCompVo.java | 17
platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/controller/StatisticController.java | 114 ++++
platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/entity/PaymentConfirm.java | 2
platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractAmountAnalysisCompVo.java | 16
platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractIncomeAnalysisAreaVo.java | 31 +
platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/mapper/ContractMapper.java | 41 +
platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/service/impl/PaymentOffsetServiceImpl.java | 1
platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/service/StatisticService.java | 24 +
14 files changed, 1,402 insertions(+), 3 deletions(-)
diff --git a/platformx-boot/src/main/resources/application-dev.yml b/platformx-boot/src/main/resources/application-dev.yml
index 8b6f823..47ca879 100644
--- a/platformx-boot/src/main/resources/application-dev.yml
+++ b/platformx-boot/src/main/resources/application-dev.yml
@@ -48,4 +48,12 @@
# 鏂囦欢涓婁紶閰嶇疆
file:
local:
- base-path: D:\\Users\\platformx\\Downloads\\img
\ No newline at end of file
+ base-path: D:\\Users\\platformx\\Downloads\\img
+
+# 5瀹跺崟浣峣d
+dept:
+ smj: 2056555602756063234
+ sgb: 2056556196124250113
+ jxc: 2056556317461270529
+ tfgs: 2056556421857497090
+ ymj: 2056556535640576002
\ No newline at end of file
diff --git a/platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/dto/AnalysisCommonDTO.java b/platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/dto/AnalysisCommonDTO.java
new file mode 100644
index 0000000..e2f0384
--- /dev/null
+++ b/platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/dto/AnalysisCommonDTO.java
@@ -0,0 +1,10 @@
+package com.by4cloud.platformx.business.dto;
+
+import lombok.Data;
+
+@Data
+public class AnalysisCommonDTO {
+
+ private String year;
+
+}
diff --git a/platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/entity/PaymentConfirm.java b/platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/entity/PaymentConfirm.java
index 05bd967..9f8baac 100644
--- a/platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/entity/PaymentConfirm.java
+++ b/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 '浠樻鏂瑰紡'")
diff --git a/platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractAmountAnalysisCompVo.java b/platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractAmountAnalysisCompVo.java
new file mode 100644
index 0000000..9a52127
--- /dev/null
+++ b/platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractAmountAnalysisCompVo.java
@@ -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;
+
+}
diff --git a/platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractAmountAnalysisVo.java b/platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractAmountAnalysisVo.java
new file mode 100644
index 0000000..ede1f30
--- /dev/null
+++ b/platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractAmountAnalysisVo.java
@@ -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;
+
+
+}
diff --git a/platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractIncomeAnalysisAreaVo.java b/platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractIncomeAnalysisAreaVo.java
new file mode 100644
index 0000000..f97732b
--- /dev/null
+++ b/platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractIncomeAnalysisAreaVo.java
@@ -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;
+}
diff --git a/platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractIncomeAnalysisCompVo.java b/platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractIncomeAnalysisCompVo.java
new file mode 100644
index 0000000..3182df1
--- /dev/null
+++ b/platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractIncomeAnalysisCompVo.java
@@ -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;
+}
diff --git a/platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractIncomeAnalysisVo.java b/platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractIncomeAnalysisVo.java
new file mode 100644
index 0000000..2acca1e
--- /dev/null
+++ b/platformx-business-finance-api/src/main/java/com/by4cloud/platformx/business/vo/ContractIncomeAnalysisVo.java
@@ -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;
+
+}
diff --git a/platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/controller/StatisticController.java b/platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/controller/StatisticController.java
new file mode 100644
index 0000000..b5f85aa
--- /dev/null
+++ b/platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/controller/StatisticController.java
@@ -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);
+ }
+}
diff --git a/platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/mapper/ContractMapper.java b/platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/mapper/ContractMapper.java
index ab2b09d..e095ba2 100644
--- a/platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/mapper/ContractMapper.java
+++ b/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);
}
diff --git a/platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/service/StatisticService.java b/platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/service/StatisticService.java
new file mode 100644
index 0000000..1928b7d
--- /dev/null
+++ b/platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/service/StatisticService.java
@@ -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);
+}
diff --git a/platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/service/impl/PaymentOffsetServiceImpl.java b/platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/service/impl/PaymentOffsetServiceImpl.java
index 9731065..c6a44b4 100644
--- a/platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/service/impl/PaymentOffsetServiceImpl.java
+++ b/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()
diff --git a/platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/service/impl/StatisticServiceImpl.java b/platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/service/impl/StatisticServiceImpl.java
new file mode 100644
index 0000000..dfecfbd
--- /dev/null
+++ b/platformx-business-finance-biz/src/main/java/com/by4cloud/platformx/business/service/impl/StatisticServiceImpl.java
@@ -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);
+ }
+
+}
diff --git a/platformx-business-finance-biz/src/main/resources/mapper/ContractMapper.xml b/platformx-business-finance-biz/src/main/resources/mapper/ContractMapper.xml
index 4409166..5d870b5 100644
--- a/platformx-business-finance-biz/src/main/resources/mapper/ContractMapper.xml
+++ b/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>
\ No newline at end of file
--
Gitblit v1.9.1