项目统计分析

354798ggg

2023-08-11 2bb7c861f998790b14e1d60108fc7ee3bb956fd2

 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(" )");
        //出库信息操作（getItemizeSum[钢筋和架立筋左侧汇总]；allitemize[所有三级分项]；intermediateData1[钢筋架立筋按分项汇总]；intermediateData2[钢筋网片按分项和材料汇总]；intermediateData3[报表成型]）
        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 @@
               }
            }
         }

            
            //将数据转为DataTableEntity返回
            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;