项目统计分析

354798ggg

2023-08-11 2bb7c861f998790b14e1d60108fc7ee3bb956fd2

项目统计分析

 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";

 product-server-project-management/src/main/java/com/product/project/management/service/ProjectStatisticAnalysisService.java

@@ -39,15 +39,15 @@
        //数据库出库信息汇总
        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);
@@ -79,7 +79,7 @@
            .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))
@@ -89,12 +89,12 @@
        .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));
        }
@@ -112,9 +112,9 @@
        .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++) {