From 2bb7c861f998790b14e1d60108fc7ee3bb956fd2 Mon Sep 17 00:00:00 2001
From: 354798ggg <1074825718@qq.com>
Date: 星期五, 11 八月 2023 15:18:26 +0800
Subject: [PATCH] 项目统计分析
---
product-server-project-management/src/main/java/com/product/project/management/config/Cmnconst.java | 2 +-
product-server-project-management/src/main/java/com/product/project/management/service/ProjectStatisticAnalysisService.java | 54 +++++++++++++++++++++++++++---------------------------
2 files changed, 28 insertions(+), 28 deletions(-)
diff --git a/product-server-project-management/src/main/java/com/product/project/management/config/Cmnconst.java b/product-server-project-management/src/main/java/com/product/project/management/config/Cmnconst.java
index 9402a08..f563d28 100644
--- a/product-server-project-management/src/main/java/com/product/project/management/config/Cmnconst.java
+++ b/product-server-project-management/src/main/java/com/product/project/management/config/Cmnconst.java
@@ -14,7 +14,7 @@
//瀛楁鍚�
public static final String MATERIAL_TYPE="material_type";
- public static final String MATERIAL_CODE="material_model";
+ public static final String MATERIAL_CODE="material_code";
public static final String OUTBOUND_WEIGHT="outbound_weight";
public static final String TOTAL_OUTBOUND_WEIGHT="total_outbound_weight";
public static final String PROJECT_NAME="project_name";
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 e8e663d..3edb86c 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
@@ -19,11 +19,11 @@
@Component
public class ProjectStatisticAnalysisService extends AbstractBaseService{
-
-
+
+
@Autowired
BaseDao baseDao;
-
+
/**
* 椤圭洰鏁版嵁鍒嗘瀽
*
@@ -33,21 +33,21 @@
public JSONObject statistAnalysis(String project_uuid) {
JSONObject returnJson = new JSONObject();
-
+
List<List<String>> listMaterialType = getReportMaterialType();
//鏁版嵁搴撳嚭搴撲俊鎭眹鎬�
StringBuilder outboundInfo = new StringBuilder(512);
outboundInfo.append(" WITH outbound AS( ")
- .append(" SELECT project_uuid,itemize,material_type,material_model,SUM(outbound_weight)outbound_weight FROM ( ")
+ .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_model,B.outbound_weight, ")
+ .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_model ")
+ .append(" )A GROUP BY project_uuid,itemize,material_type,material_code ")
.append(" )");
//鍑哄簱淇℃伅鎿嶄綔锛坓etItemizeSum[閽㈢瓔鍜屾灦绔嬬瓔宸︿晶姹囨�籡锛沘llitemize[鎵�鏈変笁绾у垎椤筣锛沬ntermediateData1[閽㈢瓔鏋剁珛绛嬫寜鍒嗛」姹囨�籡锛沬ntermediateData2[閽㈢瓔缃戠墖鎸夊垎椤瑰拰鏉愭枡姹囨�籡锛沬ntermediateData3[鎶ヨ〃鎴愬瀷]锛�
StringBuilder operateInfo = new StringBuilder(1024);
@@ -56,11 +56,11 @@
.append(BaseUtil.buildQuestionMarkFilter(Cmnconst.MATERIAL_TYPE, listMaterialType.get(0).toArray(), true))
.append(" GROUP BY itemize ")
.append(" )")
-
+
.append(",allitemize AS( ")
.append(" SELECT itemize FROM outbound GROUP BY itemize ")
.append(" )")
-
+
.append(",intermediateData1 AS( ")
.append(" SELECT A.itemize ");
for (int i = 0; i < listMaterialType.get(2).size(); i++) {
@@ -78,8 +78,8 @@
.append(" THEN ")
.append("'").append(listMaterialType.get(3).get(i)).append("'");
}
-
- operateInfo.append(" END AS material_model ")
+
+ operateInfo.append(" END AS material_code ")
.append(" FROM outbound ")
.append(" WHERE ")
.append(BaseUtil.buildQuestionMarkFilter(Cmnconst.MATERIAL_TYPE, listMaterialType.get(1).toArray(), false))
@@ -87,14 +87,14 @@
.append(" ) B ON A.itemize = B.itemize ")
.append(" GROUP BY A.itemize ")
.append(" )")
-
+
.append(",intermediateData2 AS( ")
- .append(" SELECT project_uuid,itemize,material_type,material_model,outbound_weight FROM outbound WHERE ")
+ .append(" SELECT project_uuid,itemize,material_type,material_code,outbound_weight FROM outbound WHERE ")
.append(BaseUtil.buildQuestionMarkFilter(Cmnconst.MATERIAL_TYPE, listMaterialType.get(1).toArray(), true))
.append(" )")
-
+
.append(",intermediateData3 AS( ")
- .append(" SELECT A.itemize,A.total_outbound_weight,B.material_type,material_model,outbound_weight");
+ .append(" SELECT A.itemize,A.total_outbound_weight,B.material_type,material_code,outbound_weight");
for (int i = 0; i < listMaterialType.get(3).size(); i++) {
operateInfo.append(",A.").append(listMaterialType.get(3).get(i));
}
@@ -110,11 +110,11 @@
.append(" LEFT JOIN intermediateData2 B ON A.itemize = B.itemize ")
.append(" ) ")
.append(" select * from intermediateData3 ");
-
+
StringBuilder totalSum = new StringBuilder("");
- totalSum.append(" SELECT material_model,ROUND(SUM(outbound_weight),1) outbound_weight FROM outbound WHERE ")
+ totalSum.append(" SELECT material_code,ROUND(SUM(outbound_weight),1) outbound_weight FROM outbound WHERE ")
.append(BaseUtil.buildQuestionMarkFilter(Cmnconst.MATERIAL_TYPE, listMaterialType.get(1).toArray(), true))
- .append(" GROUP BY material_model ")
+ .append(" GROUP BY material_code ")
.append(" UNION ALL ")
.append(" SELECT CASE ");
for (int i = 0; i < listMaterialType.get(2).size(); i++) {
@@ -129,7 +129,7 @@
.append(" GROUP BY material_type ")
.append(" UNION ALL ")
.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});
if (!BaseUtil.dataTableIsEmpty(dtItemizeInfo)) {
@@ -145,7 +145,7 @@
String materialCode = fseItemize.getString(Cmnconst.MATERIAL_CODE);
String outboundWeight = fseItemize.getString(Cmnconst.OUTBOUND_WEIGHT);
Integer materialType = fseItemize.getInteger(Cmnconst.MATERIAL_TYPE);
-
+
//鏀堕泦鑾峰彇鎵�鏈夎〃澶村垪锛堟潗鏂欑紪鐮侊級,鏋剁珛绛嬪拰澹版祴绠℃病鏈夎〃澶�
if (!BaseUtil.strIsNull(materialCode)) {
if (BaseUtil.strIsNull(materialTypes[materialType])) {
@@ -174,7 +174,7 @@
convertData.put(itemize, fseConvertData);
}
}
-
+
//璁＄畻姹囨��
DataTableEntity dtTotalInfo = baseDao.listTable(outboundInfo.toString() + totalSum.toString(), new Object[]{project_uuid});
FieldSetEntity fseConvertData = new FieldSetEntity(Cmnconst.PRODUCT_PROJECT_QXLW);
@@ -193,7 +193,7 @@
}
}
}
-
+
//灏嗘暟鎹浆涓篋ataTableEntity杩斿洖
if (!convertData.isEmpty()) {
DataTableEntity finalConvertData = new DataTableEntity();
@@ -212,8 +212,8 @@
}
return returnJson;
}
-
-
+
+
/**
* 杩斿洖鏉愭枡绫诲瀷淇℃伅
* listSummary 闇�瑕佹眹鎬荤殑鏉愭枡
@@ -225,13 +225,13 @@
public List<List<String>> getReportMaterialType() {
DataTableEntity dtMaterialType = baseDao.listTable(Cmnconst.PRODUCT_PROJECT_BASE_MATERIAL_TYPE, null, new Object[] {}, new Object[] {"material_type_name","material_type_value"});
if (!BaseUtil.dataTableIsEmpty(dtMaterialType)) {
-
+
List<List<String>> returnList = new ArrayList<>();
List<String> listSummary = new ArrayList<>();
List<String> listShowMaterialCode = new ArrayList<>();
List<String> listNotShowMaterialCode = new ArrayList<>();
List<String> listNotShowMaterialCodeName = new ArrayList<>();
-
+
for (int i = 0; i < dtMaterialType.getRows(); i++) {
String dictValue = dtMaterialType.getFieldSetEntity(i).getString("material_type_value");
String dictLabel = dtMaterialType.getFieldSetEntity(i).getString("material_type_name");
@@ -253,7 +253,7 @@
returnList.add(listShowMaterialCode);
returnList.add(listNotShowMaterialCode);
returnList.add(listNotShowMaterialCodeName);
-
+
return returnList;
}
return null;
--
Gitblit v1.9.2