From 8b23e9e8aee1e533dac755efe572b657583c46d6 Mon Sep 17 00:00:00 2001
From: 354798ggg <1074825718@qq.com>
Date: 星期四, 13 七月 2023 10:22:33 +0800
Subject: [PATCH] 项目数据统计分析调整
---
product-server-project-management/src/main/java/com/product/project/management/service/ProjectBudgetService.java | 20 ++++++
product-server-project-management/src/main/java/com/product/project/management/service/ProjectInfoService.java | 122 ++++++++++++++++++++++++++++++----------
product-server-project-management/src/main/java/com/product/project/management/config/Cmnconst.java | 2
3 files changed, 111 insertions(+), 33 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 3c798f7..3b53275 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
@@ -8,6 +8,7 @@
public static final String PRODUCT_PROJECT_QXLW="product_project_qxlw"; //椤圭洰淇℃伅
public static final String PRODUCT_PROJECT_ITEM="product_project_item"; //椤圭洰鍒嗛」
public static final String PRODUCT_PROJECT_BUDGET="product_project_budget"; //棰勭畻
+ public static final String PRODUCT_PROJECT_BASE_MATERIAL="product_project_base_material";
//瀛楁鍚�
public static final String MATERIAL_TYPE="material_type";
@@ -20,6 +21,7 @@
public static final String ITEMIZE_CODE="itemize_code";
public static final String ITEMIZE_TYPE="itemize_type";
public static final String ITEMIZE_PARENT_CODE="itemize_parent_code";
+ public static final String BUDGET_CODE="budget_code";
//鍙傛暟
diff --git a/product-server-project-management/src/main/java/com/product/project/management/service/ProjectBudgetService.java b/product-server-project-management/src/main/java/com/product/project/management/service/ProjectBudgetService.java
index 3bc336b..535162e 100644
--- a/product-server-project-management/src/main/java/com/product/project/management/service/ProjectBudgetService.java
+++ b/product-server-project-management/src/main/java/com/product/project/management/service/ProjectBudgetService.java
@@ -41,17 +41,35 @@
List<Object> params = new ArrayList<>();
String projectItem = autoCreateFilter(params, fseParseData);
+ //鏍规嵁妯℃澘椤圭洰鍒嗛」鏌ヨ鏁版嵁搴撻」鐩垎椤�
FieldSetEntity fseProjectItem = baseDao.getFieldSetEntityByFilter(Cmnconst.PRODUCT_PROJECT_ITEM, "project_uuid IN (SELECT uuid FROM product_project_qxlw WHERE project_name=?) AND itemize_full_name=?", params.toArray(), false);
if(fseProjectItem==null) {
sbError.append(String.format("绗� %s 琛岄」鐩垎椤� '%s' 涓嶅瓨鍦ㄤ簬绯荤粺涓��", fseParseData.getString("~row~"), projectItem));
continue;
}
+ //璁剧疆鍏宠仈椤圭洰鍒嗛」
fseParseData.setValue("project_itemize_uuid", fseProjectItem.getUUID());
+
+ //鑾峰彇缂栫爜鐢熸垚鏉愭枡绫诲瀷
+ String budgetCode = fseParseData.getString(Cmnconst.BUDGET_CODE);
+ Integer materialType = 0;
+ if("鏋剁珛绛�".equals(budgetCode)) {
+ materialType = 1;
+ }else if("澹版祴绠�".equals(budgetCode)) {
+ materialType = 2;
+ }else {
+ //缃戠墖
+ FieldSetEntity materialInfo = baseDao.getFieldSetEntityByFilter(Cmnconst.PRODUCT_PROJECT_BASE_MATERIAL, "material_type=3 AND material_name like ?", new Object[] {fseParseData.getString(Cmnconst.MATERIAL_CODE)+"%"}, false);
+ if (materialInfo!=null) {
+ materialType = 3;
+ }
+ }
+ fseParseData.setValue(Cmnconst.MATERIAL_TYPE, materialType);
}
+ //鍒ゆ柇鏄惁鍖呭惈閿欒淇℃伅
if (!BaseUtil.strIsNull(sbError.toString())) {
throw new BaseException(SystemCode.PROJECT_BUDGET_IMPORT_FAIL.getValue(), sbError.toString());
}
-// baseDao.add(dtParseData);
return dtParseData;
}
diff --git a/product-server-project-management/src/main/java/com/product/project/management/service/ProjectInfoService.java b/product-server-project-management/src/main/java/com/product/project/management/service/ProjectInfoService.java
index 7b38697..e7de973 100644
--- a/product-server-project-management/src/main/java/com/product/project/management/service/ProjectInfoService.java
+++ b/product-server-project-management/src/main/java/com/product/project/management/service/ProjectInfoService.java
@@ -90,30 +90,62 @@
JSONObject returnJson = new JSONObject();
- //鑾峰彇鏁版嵁搴撳師濮嬫暟鎹�
- StringBuilder sb = new StringBuilder();
- sb.append("WITH outbound AS(")
- .append(" SELECT A.project_uuid,D.itemize project_itemize,C.material_type,B.material_code,SUM(B.outbound_weight)outbound_weight ")
- .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_base_material C ON B.material_uuid = C.uuid ")
- .append(" LEFT JOIN product_project_item D ON A.project_itemize = D.uuid ")
- .append(" WHERE A.flow_flag=2 AND A.project_uuid = ? ")
- .append(" GROUP BY A.project_uuid,D.itemize,C.material_type,B.material_code ")
- .append(" ) ")
- .append(" SELECT A.project_uuid,A.project_itemize,A.total_outbound_weight,B.material_type,B.material_code,B.outbound_weight ")
- .append(" FROM ( ")
- .append(" SELECT project_uuid,project_itemize,SUM(outbound_weight)total_outbound_weight ")
- .append(" FROM outbound ")
- .append(" GROUP BY project_uuid,project_itemize ")
- .append(" )A ")
- .append(" RIGHT JOIN outbound B ON A.project_uuid=B.project_uuid AND A.project_itemize=B.project_itemize ")
- .append(" ORDER BY project_itemize,material_code");
- DataTableEntity dtItemizeInfo = baseDao.listTable(sb.toString(), new Object[]{project_uuid});
+ //鏁版嵁搴撳嚭搴撲俊鎭眹鎬�
+ 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, ")
+ .append(" 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,7)) 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(" )");
+ //鍑哄簱淇℃伅鎿嶄綔锛堟灦绔嬬瓔鍜屽０娴嬬涓嶅睍绀哄瀷鍙锋眹鎬绘眰鍜岃杞垪锛屾櫘閫氶挗绛嬪拰缃戠墖灞曠ず鍨嬪彿锛屾牴鎹」鐩垎椤瑰叧鑱旀眹鎬伙級
+ StringBuilder operateInfo = new StringBuilder(1024);
+ operateInfo.append(" ,getItemizeSum AS( ")
+ .append(" SELECT itemize,SUM(outbound_weight)total_outbound_weight FROM outbound WHERE material_type IN (0,1) GROUP BY itemize ")
+ .append(" ),allitemize AS( ")
+ .append(" SELECT itemize FROM outbound GROUP BY itemize ")
+ .append(" ),intermediateData1 AS( ")
+ .append(" SELECT A.itemize,SUM(IF(`material_type`='1',outbound_weight,0)) as 'JLJ',SUM(IF(`material_type`='2',outbound_weight,0)) as 'SCG' ")
+ .append(" FROM ( SELECT itemize FROM outbound GROUP BY itemize )A ")
+ .append(" LEFT JOIN ( ")
+ .append(" SELECT project_uuid,itemize,material_type,CASE WHEN material_type=1 THEN '鏋剁珛绛�' ELSE '澹版祴绠�' END AS material_code,SUM(outbound_weight)outbound_weight ")
+ .append(" FROM outbound ")
+ .append(" WHERE material_type IN (1,2) ")
+ .append(" GROUP BY project_uuid,itemize,material_type ")
+ .append(" ) B ON A.itemize = B.itemize ")
+ .append(" GROUP BY A.itemize ")
+ .append(" ),intermediateData2 AS( ")
+ .append(" SELECT project_uuid,itemize,material_type,material_code,outbound_weight FROM outbound WHERE material_type IN (0,3) ")
+ .append(" ),intermediateData3 AS( ")
+ .append(" SELECT A.itemize,A.total_outbound_weight,A.jlj,A.scg,B.material_type,material_code,outbound_weight FROM( ")
+ .append(" SELECT A.itemize,C.total_outbound_weight,B.jlj,B.scg ")
+ .append(" FROM allitemize A ")
+ .append(" LEFT JOIN intermediateData1 B ON A.itemize = B.itemize ")
+ .append(" LEFT JOIN getItemizeSum C ON A.itemize = C.itemize ")
+ .append(" )A ")
+ .append(" LEFT JOIN intermediateData2 B ON A.itemize = B.itemize ")
+ .append(" ) ")
+ .append(" select * from intermediateData3 ");
+
+ StringBuilder totalSum = new StringBuilder("");
+ totalSum.append(" SELECT material_code,SUM(outbound_weight)outbound_weight FROM outbound WHERE material_type IN (0,3) GROUP BY material_code ")
+ .append(" UNION ALL ")
+ .append(" SELECT CASE WHEN material_type=1 THEN 'JLJ' WHEN material_type=2 THEN 'SCG' END,SUM(outbound_weight) FROM outbound WHERE material_type IN (1,2) GROUP BY material_type ")
+ .append(" UNION ALL ")
+ .append(" SELECT '鍚堣',SUM(outbound_weight) 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)) {
Map<String, FieldSetEntity> convertData = new HashMap<>();
- String[] materialTypes = new String[3];
+ String[] materialTypes = new String[4];
//閫氳繃Java寰幆灏�"鏉愭枡"鍜�"鍑哄簱閲�"鏁版嵁琛岃浆鍒�
for (int i = 0; i < dtItemizeInfo.getRows(); i++) {
@@ -123,36 +155,62 @@
String materialCode = fseItemize.getString(Cmnconst.MATERIAL_CODE);
String outboundWeight = fseItemize.getString(Cmnconst.OUTBOUND_WEIGHT);
Integer materialType = fseItemize.getInteger(Cmnconst.MATERIAL_TYPE);
-
- //鏀堕泦鑾峰彇鎵�鏈夎〃澶村垪锛堟潗鏂欑紪鐮侊級
- if (BaseUtil.strIsNull(materialTypes[materialType])) {
- materialTypes[materialType] = materialCode;
- } else {
- if(materialTypes[fseItemize.getInteger(Cmnconst.MATERIAL_TYPE)].indexOf(materialCode)<0) {
- materialTypes[fseItemize.getInteger(Cmnconst.MATERIAL_TYPE)] += "," + materialCode;
- }
- }
+
+ //鏀堕泦鑾峰彇鎵�鏈夎〃澶村垪锛堟潗鏂欑紪鐮侊級,鏋剁珛绛嬪拰澹版祴绠℃病鏈夎〃澶�
+ if (!BaseUtil.strIsNull(materialCode)) {
+ if (BaseUtil.strIsNull(materialTypes[materialType])) {
+ materialTypes[materialType] = materialCode;
+ } else {
+ if(materialTypes[fseItemize.getInteger(Cmnconst.MATERIAL_TYPE)].indexOf(materialCode)<0) {
+ materialTypes[fseItemize.getInteger(Cmnconst.MATERIAL_TYPE)] += "," + materialCode;
+ }
+ }
+ }
//鏀堕泦琛岃浆鍒楁暟鎹�
- String itemize = fseItemize.getString(Cmnconst.PROJECT_ITEMIZE);
+ String itemize = fseItemize.getString(Cmnconst.ITEMIZE);
if (convertData.containsKey(itemize)) {
convertData.get(itemize).setValue(materialCode, outboundWeight);
} else {
FieldSetEntity fseConvertData = new FieldSetEntity(Cmnconst.PRODUCT_PROJECT_QXLW);
- fseConvertData.setValue(Cmnconst.PROJECT_ITEMIZE, itemize);
+ fseConvertData.setValue(Cmnconst.ITEMIZE, itemize);
fseConvertData.setValue(Cmnconst.TOTAL_OUTBOUND_WEIGHT, fseItemize.getString(Cmnconst.TOTAL_OUTBOUND_WEIGHT));
fseConvertData.setValue(materialCode, outboundWeight);
+ fseConvertData.setValue("jlj", fseItemize.getString("jlj"));
+ fseConvertData.setValue("scg", fseItemize.getString("scg"));
convertData.put(itemize, fseConvertData);
}
}
+
+ //璁＄畻姹囨��
+ DataTableEntity dtTotalInfo = baseDao.listTable(outboundInfo.toString() + totalSum.toString(), new Object[]{project_uuid});
+ FieldSetEntity fseConvertData = new FieldSetEntity(Cmnconst.PRODUCT_PROJECT_QXLW);
+ if (!BaseUtil.dataTableIsEmpty(dtTotalInfo)) {
+ for (int i = 0; i < dtTotalInfo.getRows(); i++) {
+ FieldSetEntity fseTotalInfo = dtTotalInfo.getFieldSetEntity(i);
+ String materialCode = fseTotalInfo.getString(Cmnconst.MATERIAL_CODE);
+ String outboundWeight = fseTotalInfo.getString(Cmnconst.OUTBOUND_WEIGHT);
+ if (!BaseUtil.strIsNull(materialCode) && !BaseUtil.strIsNull(outboundWeight)) {
+ if ("鍚堣".equals(materialCode)) {
+ fseConvertData.setValue(Cmnconst.ITEMIZE, "鍚堣");
+ fseConvertData.setValue(Cmnconst.TOTAL_OUTBOUND_WEIGHT, outboundWeight);
+ }else {
+ fseConvertData.setValue(materialCode, outboundWeight);
+ }
+ }
+ }
+ }
+
//灏嗘暟鎹浆涓篋ataTableEntity杩斿洖
if (!convertData.isEmpty()) {
DataTableEntity finalConvertData = new DataTableEntity();
convertData.forEach((k, v) -> {
finalConvertData.addFieldSetEntity(v);
});
+ finalConvertData.addFieldSetEntity(fseConvertData);
returnJson.set("data", BaseUtil.dataTableEntityToJson(finalConvertData));
}
+ //灏嗚〃澶存暟鎹繑鍥�
for (int i = 0; i < materialTypes.length; i++) {
if (!BaseUtil.strIsNull(materialTypes[i])) {
returnJson.set("type" + i, materialTypes[i]);
--
Gitblit v1.9.2