项目数据统计分析调整

354798ggg

2023-07-13 8b23e9e8aee1e533dac755efe572b657583c46d6

项目数据统计分析调整

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


    //参数

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

 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);
                        }
                    }
                }
            }
            
            //将数据转为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]);