package com.product.project.management.service;
|
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
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.entity.FieldSetEntity;
|
import com.product.core.service.support.AbstractBaseService;
|
import com.product.project.management.config.Cmnconst;
|
import com.product.util.BaseUtil;
|
|
import cn.hutool.json.JSONObject;
|
|
@Component
|
public class ProjectStatisticAnalysisService extends AbstractBaseService{
|
|
|
@Autowired
|
BaseDao baseDao;
|
|
/**
|
* 项目数据分析
|
*
|
* @param project_uuid
|
* @return
|
*/
|
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_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(" )");
|
//出库信息操作（getItemizeSum[钢筋和架立筋左侧汇总]；allitemize[所有三级分项]；intermediateData1[钢筋架立筋按分项汇总]；intermediateData2[钢筋网片按分项和材料汇总]；intermediateData3[报表成型]）
|
StringBuilder operateInfo = new StringBuilder(1024);
|
operateInfo.append(" ,getItemizeSum AS( ")
|
.append(" SELECT itemize,ROUND(SUM(outbound_weight)/1000,1) total_outbound_weight FROM outbound WHERE ")
|
.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++) {
|
operateInfo.append(",SUM(IF(material_type=")
|
.append(listMaterialType.get(2).get(i))
|
.append(",outbound_weight,0)) as ")
|
.append(listMaterialType.get(3).get(i));
|
}
|
operateInfo.append(" FROM ( SELECT itemize FROM outbound GROUP BY itemize )A ")
|
.append(" LEFT JOIN ( ")
|
.append(" SELECT project_uuid,itemize,material_type,SUM(outbound_weight)outbound_weight,CASE ");
|
for (int i = 0; i < listMaterialType.get(2).size(); i++) {
|
operateInfo.append(" WHEN material_type=")
|
.append(listMaterialType.get(2).get(i))
|
.append(" THEN ")
|
.append("'").append(listMaterialType.get(3).get(i)).append("'");
|
}
|
|
operateInfo.append(" END AS material_code ")
|
.append(" FROM outbound ")
|
.append(" WHERE ")
|
.append(BaseUtil.buildQuestionMarkFilter(Cmnconst.MATERIAL_TYPE, listMaterialType.get(1).toArray(), false))
|
.append(" GROUP BY project_uuid,itemize,material_type ")
|
.append(" ) B ON A.itemize = B.itemize ")
|
.append(" GROUP BY A.itemize ")
|
.append(" )")
|
|
.append(",intermediateData2 AS( ")
|
.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_code,outbound_weight");
|
for (int i = 0; i < listMaterialType.get(3).size(); i++) {
|
operateInfo.append(",A.").append(listMaterialType.get(3).get(i));
|
}
|
operateInfo.append(" FROM( ")
|
.append(" SELECT A.itemize,C.total_outbound_weight ");
|
for (int i = 0; i < listMaterialType.get(3).size(); i++) {
|
operateInfo.append(",B.").append(listMaterialType.get(3).get(i));
|
}
|
operateInfo.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,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_code ")
|
.append(" UNION ALL ")
|
.append(" SELECT CASE ");
|
for (int i = 0; i < listMaterialType.get(2).size(); i++) {
|
totalSum.append(" WHEN material_type=")
|
.append(listMaterialType.get(2).get(i))
|
.append(" THEN ")
|
.append("'").append(listMaterialType.get(3).get(i)).append("'");
|
}
|
totalSum.append(" END, ROUND(SUM(outbound_weight),1)")
|
.append(" FROM outbound WHERE ")
|
.append(BaseUtil.buildQuestionMarkFilter(Cmnconst.MATERIAL_TYPE, listMaterialType.get(2).toArray(), true))
|
.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)) {
|
|
Map<String, FieldSetEntity> convertData = new HashMap<>();
|
String[] materialTypes = new String[4];
|
|
//通过Java循环将"材料"和"出库量"数据行转列
|
for (int i = 0; i < dtItemizeInfo.getRows(); i++) {
|
FieldSetEntity fseItemize = dtItemizeInfo.getFieldSetEntity(i);
|
|
//获取材料编码，材料类型，出库量
|
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])) {
|
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.ITEMIZE);
|
if (convertData.containsKey(itemize)) {
|
convertData.get(itemize).setValue(materialCode, outboundWeight);
|
} else {
|
FieldSetEntity fseConvertData = new FieldSetEntity(Cmnconst.PRODUCT_PROJECT_QXLW);
|
fseConvertData.setValue(Cmnconst.ITEMIZE, itemize);
|
fseConvertData.setValue(Cmnconst.TOTAL_OUTBOUND_WEIGHT, fseItemize.getString(Cmnconst.TOTAL_OUTBOUND_WEIGHT));
|
fseConvertData.setValue(materialCode, outboundWeight);
|
for (int j = 0; j < listMaterialType.get(3).size(); j++) {
|
fseConvertData.setValue(listMaterialType.get(3).get(j), fseItemize.getString(listMaterialType.get(3).get(j)));
|
}
|
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);
|
}
|
}
|
}
|
}
|
|
//将数据转为DataTableEntity返回
|
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]);
|
}
|
}
|
}
|
return returnJson;
|
}
|
|
|
/**
|
* 返回材料类型信息
|
* listSummary 需要汇总的材料
|
* listShowMaterialCode 需要展示材料编码的材料
|
* listNotShowMaterialCode 不需要展示材料编码的材料
|
* listNotShowMaterialCodeName 不需要展示材料编码的材料类型名称
|
* @return
|
*/
|
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");
|
if ("0".equals(dictValue)) {
|
listSummary.add(dictValue);
|
listShowMaterialCode.add(dictValue);
|
}else if ("1".equals(dictValue)) {
|
listSummary.add(dictValue);
|
listNotShowMaterialCode.add(dictValue);
|
listNotShowMaterialCodeName.add(dictLabel);
|
}else if ("3".equals(dictValue)) {
|
listShowMaterialCode.add(dictValue);
|
}else {
|
listNotShowMaterialCode.add(dictValue);
|
listNotShowMaterialCodeName.add(dictLabel);
|
}
|
}
|
returnList.add(listSummary);
|
returnList.add(listShowMaterialCode);
|
returnList.add(listNotShowMaterialCode);
|
returnList.add(listNotShowMaterialCodeName);
|
|
return returnList;
|
}
|
return null;
|
}
|
}
|
