From 75339563cebc86759a1e8addab9ab5382b4fd1a1 Mon Sep 17 00:00:00 2001
From: 354798ggg <1074825718@qq.com>
Date: 星期五, 01 十二月 2023 10:34:57 +0800
Subject: [PATCH] 入库统计报表
---
product-server-project-management/src/main/java/com/product/project/management/config/SystemCode.java | 1
product-server-project-management/src/main/java/com/product/project/management/controller/ProjectStatisticAnalysisController.java | 31 ++++++++++
product-server-project-management/src/main/java/com/product/project/management/service/ProjectInventoryReportService.java | 66 ++++++++++++++++++++++
product-server-project-management/src/main/java/com/product/project/management/service/ProjectStatisticAnalysisService.java | 27 +++++----
4 files changed, 113 insertions(+), 12 deletions(-)
diff --git a/product-server-project-management/src/main/java/com/product/project/management/config/SystemCode.java b/product-server-project-management/src/main/java/com/product/project/management/config/SystemCode.java
index 301e0ff..c9e5e37 100644
--- a/product-server-project-management/src/main/java/com/product/project/management/config/SystemCode.java
+++ b/product-server-project-management/src/main/java/com/product/project/management/config/SystemCode.java
@@ -21,6 +21,7 @@
PROJeCT_BUDGET_CLEAR_FAIL_HAS_OUTBOUND("棰勭畻娓呯悊澶辫触锛屽凡缁忓瓨鍦ㄥ嚭搴撴暟鎹紒", ModuleEnum.QXLW_PROJECT.getValue() + "008"),
PROJeCT_OUT_CANCELOUTBOUND_ERROR("鍑哄簱鎾ら攢澶辫触锛�", ModuleEnum.QXLW_PROJECT.getValue() + "009"),
+ PROJECT_INVENTORY_DATA_QUERY_FAIL("鏉愭枡鍏ュ簱鏁版嵁鏌ヨ澶辫触锛�", ModuleEnum.QXLW_PROJECT.getValue() + "010")
;
diff --git a/product-server-project-management/src/main/java/com/product/project/management/controller/ProjectStatisticAnalysisController.java b/product-server-project-management/src/main/java/com/product/project/management/controller/ProjectStatisticAnalysisController.java
index 7b32d3a..7453491 100644
--- a/product-server-project-management/src/main/java/com/product/project/management/controller/ProjectStatisticAnalysisController.java
+++ b/product-server-project-management/src/main/java/com/product/project/management/controller/ProjectStatisticAnalysisController.java
@@ -13,6 +13,7 @@
import com.product.module.sys.version.ApiVersion;
import com.product.project.management.config.Cmnconst;
import com.product.project.management.config.SystemCode;
+import com.product.project.management.service.ProjectInventoryReportService;
import com.product.project.management.service.ProjectStatisticAnalysisService;
import com.product.util.BaseUtil;
import com.product.util.ResultInfo;
@@ -23,6 +24,9 @@
@Autowired
ProjectStatisticAnalysisService projectStatisticAnalysisService;
+
+ @Autowired
+ ProjectInventoryReportService projectInventoryReportService;
/**
* 鑾峰彇椤圭洰鍒嗘瀽鏁版嵁
@@ -50,4 +54,31 @@
return this.error(SystemCode.PROJECT_INFO_OPERATE_ERROR.getValue(), SystemCode.PROJECT_INFO_OPERATE_ERROR.getText() + e.getMessage());
}
}
+
+ /**
+ * 鏉愭枡鍏ュ簱缁熻
+ *
+ * @param request
+ * @return
+ */
+ @RequestMapping(value = "/inventory-report/{version}", method = RequestMethod.POST)
+ @ApiVersion(1)
+ public String inventoryReport(HttpServletRequest request) {
+ try {
+ //鑾峰彇鍙傛暟
+ FieldSetEntity fse = BaseUtil.getFieldSetEntity(request);
+
+ //鍒ゆ柇鏌ヨ骞翠唤鏄惁涓虹┖
+ if (BaseUtil.strIsNull(fse.getString("query_year"))) {
+ return this.error(SystemCode.SYSTEM_FORM_COUNT.getValue(), SystemCode.SYSTEM_FORM_COUNT.getText());
+ }
+ return OK_List(projectInventoryReportService.inventoryStatisticReport(fse.getDate("query_year","yyyy")));
+ } catch (BaseException e) {
+ e.printStackTrace();
+ return this.error(e);
+ } catch (Exception e) {
+ e.printStackTrace();
+ return this.error(SystemCode.PROJECT_INVENTORY_DATA_QUERY_FAIL.getValue(), SystemCode.PROJECT_INVENTORY_DATA_QUERY_FAIL.getText() + e.getMessage());
+ }
+ }
}
diff --git a/product-server-project-management/src/main/java/com/product/project/management/service/ProjectInventoryReportService.java b/product-server-project-management/src/main/java/com/product/project/management/service/ProjectInventoryReportService.java
new file mode 100644
index 0000000..20710ef
--- /dev/null
+++ b/product-server-project-management/src/main/java/com/product/project/management/service/ProjectInventoryReportService.java
@@ -0,0 +1,66 @@
+package com.product.project.management.service;
+
+
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.stereotype.Component;
+
+import com.product.core.dao.BaseDao;
+import com.product.core.entity.DataTableEntity;
+import com.product.core.service.support.AbstractBaseService;
+
+/**
+ * 椤圭洰鍏ュ簱缁熻鎶ヨ〃
+ * @author 86151
+ *
+ */
+@Component
+public class ProjectInventoryReportService extends AbstractBaseService{
+
+ @Autowired
+ BaseDao baseDao;
+
+ static final String [] monthName = new String [] {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sept","Oct","Nov","Dece"};
+
+ /**
+ * 鍏ュ簱缁熻鎶ヨ〃
+ * @param queryYear
+ * @return
+ */
+ public DataTableEntity inventoryStatisticReport(String queryYear) {
+
+
+
+ StringBuilder sb = new StringBuilder(1024);
+ //鍘熷鏁版嵁
+ sb.append(" WITH rawData AS(")
+ .append(" SELECT A.material_code, MONTH(B.inbound_date)inbound_month, A.inbound_number ")
+ .append(" FROM product_project_purchase_inbound_sub A ")
+ .append(" LEFT JOIN product_project_purchase_inbound B ON A.inbound_uuid = B.uuid ")
+ .append(" WHERE YEAR(inbound_date) = ").append(queryYear)
+ .append(" ), ");
+
+ //琛屼笓鍒楁暟鎹�
+ sb.append(" convertData AS( ")
+ .append(" SELECT material_code, ");
+ for (int i = 0; i < monthName.length; i++) {
+ sb.append("SUM(CASE WHEN inbound_month = ").append(i+1).append(" THEN inbound_number END) AS ").append(monthName[i]).append(",");
+ }
+ sb.append(" ROUND(SUM(inbound_number)/1000, 1) AS YearSummary ")
+ .append(" FROM rawData ")
+ .append(" GROUP BY material_code ")
+ .append(" ORDER BY material_code ")
+ .append(" ) ");
+
+ //搴曢儴姹囨��
+ sb.append(" SELECT * FROM convertData ")
+ .append(" UNION ALL ")
+ .append(" SELECT '鏈堟�昏(t)', ");
+ for (int i = 0; i < monthName.length; i++) {
+ sb.append("ROUND(SUM(").append(monthName[i]).append(")/1000, 1),");
+ }
+ sb.append(" SUM(YearSummary) ")
+ .append(" FROM convertData ");
+
+ return baseDao.listTable(sb.toString(), new Object[] {});
+ }
+}
\ No newline at end of file
diff --git a/product-server-project-management/src/main/java/com/product/project/management/service/ProjectStatisticAnalysisService.java b/product-server-project-management/src/main/java/com/product/project/management/service/ProjectStatisticAnalysisService.java
index 3edb86c..fa632cd 100644
--- a/product-server-project-management/src/main/java/com/product/project/management/service/ProjectStatisticAnalysisService.java
+++ b/product-server-project-management/src/main/java/com/product/project/management/service/ProjectStatisticAnalysisService.java
@@ -39,16 +39,19 @@
//鏁版嵁搴撳嚭搴撲俊鎭眹鎬�
StringBuilder outboundInfo = new StringBuilder(512);
outboundInfo.append(" WITH outbound AS( ")
- .append(" SELECT project_uuid,itemize,material_type,material_code,SUM(outbound_weight)outbound_weight FROM ( ")
- .append(" SELECT ")
- .append(" A.project_uuid,B.material_type,B.material_code,B.outbound_weight, ")
- .append(" (SELECT itemize FROM product_project_item WHERE project_uuid = A.project_uuid AND itemize_code = substring(C.itemize_code,1,11)) itemize ")
- .append(" FROM product_project_outbound A ")
- .append(" LEFT JOIN product_project_outbound_sub B ON A.uuid=B.outbound_uuid ")
- .append(" LEFT JOIN product_project_item C ON C.uuid = A.project_itemize ")
- .append(" WHERE A.flow_flag=2 AND A.project_uuid=? ")
- .append(" )A GROUP BY project_uuid,itemize,material_type,material_code ")
- .append(" )");
+ .append(" SELECT project_uuid,itemize,material_type,material_code,SUM(outbound_weight)outbound_weight FROM ( ")
+ .append(" SELECT A.project_uuid,A.material_type,A.material_code,A.outbound_weight,B.itemize ")
+ .append(" FROM( ")
+ .append(" SELECT A.project_uuid,B.material_type,B.material_code,B.outbound_weight,substring(C.itemize_code,1,11) itemize ")
+ .append(" FROM product_project_outbound A ")
+ .append(" LEFT JOIN product_project_outbound_sub B ON A.uuid=B.outbound_uuid ")
+ .append(" LEFT JOIN product_project_item C ON C.uuid = A.project_itemize ")
+ .append(" WHERE A.flow_flag=2 AND A.project_uuid=? ")
+ .append(" )A ")
+ .append(" LEFT JOIN product_project_item B ON A.itemize = B.itemize_code ")
+ .append(" WHERE B.project_uuid = ? ")
+ .append(" )A GROUP BY project_uuid,itemize,material_type,material_code ")
+ .append(")");
//鍑哄簱淇℃伅鎿嶄綔锛坓etItemizeSum[閽㈢瓔鍜屾灦绔嬬瓔宸︿晶姹囨�籡锛沘llitemize[鎵�鏈変笁绾у垎椤筣锛沬ntermediateData1[閽㈢瓔鏋剁珛绛嬫寜鍒嗛」姹囨�籡锛沬ntermediateData2[閽㈢瓔缃戠墖鎸夊垎椤瑰拰鏉愭枡姹囨�籡锛沬ntermediateData3[鎶ヨ〃鎴愬瀷]锛�
StringBuilder operateInfo = new StringBuilder(1024);
operateInfo.append(" ,getItemizeSum AS( ")
@@ -131,7 +134,7 @@
.append(" SELECT '鍚堣',ROUND(SUM(outbound_weight)/1000,1) FROM outbound WHERE material_type IN (0,1,3) GROUP BY project_uuid ");
//鑾峰彇SQL瑙ｆ瀽鐨勫熀鏈暟鎹紝鍒╃敤JAVA灏嗛」鐩垎椤瑰搴旂殑涓嶅悓鏉愭枡琛岃浆鍒�
- DataTableEntity dtItemizeInfo = baseDao.listTable(outboundInfo.toString() + operateInfo.toString(), new Object[]{project_uuid});
+ DataTableEntity dtItemizeInfo = baseDao.listTable(outboundInfo.toString() + operateInfo.toString(), new Object[]{project_uuid, project_uuid});
if (!BaseUtil.dataTableIsEmpty(dtItemizeInfo)) {
Map<String, FieldSetEntity> convertData = new HashMap<>();
@@ -176,7 +179,7 @@
}
//璁＄畻姹囨��
- DataTableEntity dtTotalInfo = baseDao.listTable(outboundInfo.toString() + totalSum.toString(), new Object[]{project_uuid});
+ DataTableEntity dtTotalInfo = baseDao.listTable(outboundInfo.toString() + totalSum.toString(), new Object[]{project_uuid, project_uuid});
FieldSetEntity fseConvertData = new FieldSetEntity(Cmnconst.PRODUCT_PROJECT_QXLW);
if (!BaseUtil.dataTableIsEmpty(dtTotalInfo)) {
for (int i = 0; i < dtTotalInfo.getRows(); i++) {
--
Gitblit v1.9.2