项目数据统计分析

354798ggg

2023-06-28 3b0d2e394dddb504452bb492203bd1b2fff05fde

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

@@ -1,10 +1,14 @@
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.config.CoreConst;
import com.product.core.dao.BaseDao;
import com.product.core.entity.DataTableEntity;
import com.product.core.entity.FieldSetEntity;
@@ -13,6 +17,8 @@
import com.product.project.management.config.Cmnconst;
import com.product.project.management.config.SystemCode;
import com.product.util.BaseUtil;

import cn.hutool.json.JSONObject;

@Component("qxlw-project-service")
public class ProjectInfoService extends AbstractBaseService{
@@ -26,7 +32,7 @@
    * @return
    */
   public DataTableEntity getProjectTypeItem(String projectType) {
      DataTableEntity dtProjectItem = baseDao.listTable(Cmnconst.PRODUCT_PROJECT_BASE_PROJECT_ITEM, "itemize_type=?", new Object[] {projectType}, new Object[] {Cmnconst.ITEMIZE, Cmnconst.ITEMIZE_CODE, Cmnconst.ITEMIZE_PARENT_CODE});
      DataTableEntity dtProjectItem = baseDao.listTable(Cmnconst.PRODUCT_PROJECT_BASE_PROJECT_ITEM, "itemize_type=?", new Object[] {projectType}, new Object[] {Cmnconst.ITEMIZE, Cmnconst.ITEMIZE_CODE, Cmnconst.ITEMIZE_TYPE, Cmnconst.ITEMIZE_PARENT_CODE});
      if (!BaseUtil.dataTableIsEmpty(dtProjectItem)) {
         DataTableEntity newDtProjectItem = BaseUtil.dataTableToTreeTable(dtProjectItem, Cmnconst.ITEMIZE_CODE, Cmnconst.ITEMIZE_PARENT_CODE, null);
         return newDtProjectItem;
@@ -35,7 +41,7 @@
   }
   
   /**
    *    通过项目主表获取项目分项（获取保存的子表分项）
    *    通过项目UUID获取项目分项（获取保存的子表分项--多个树）
    * @param uuid
    * @return
    */
@@ -48,6 +54,105 @@
   }
   
   /**
    *    通过项目UUID获取项目分项（获取保存的子表分项--单个树）
    * @param uuid
    * @return
    */
   public DataTableEntity getProjectAllItem(String uuid) {
      DataTableEntity dtProjectItemize = baseDao.listTable(Cmnconst.PRODUCT_PROJECT_ITEM, "project_uuid=?", new Object[] {uuid}, new Object[] {CoreConst.UUID, Cmnconst.ITEMIZE, Cmnconst.ITEMIZE_CODE, Cmnconst.ITEMIZE_PARENT_CODE});
      if (!BaseUtil.dataTableIsEmpty(dtProjectItemize)) {
         DataTableEntity newDtProjectItem = BaseUtil.dataTableToTreeTable(dtProjectItemize,Cmnconst.ITEMIZE_CODE, Cmnconst.ITEMIZE_PARENT_CODE, null);
         return newDtProjectItem;
      }
      return null;
   }
	
   /**
    *    获取所有项目信息
    * @return
    */
   public DataTableEntity listProject() {
      return baseDao.listTable(Cmnconst.PRODUCT_PROJECT_QXLW, null, null, new Object[] {CoreConst.UUID, Cmnconst.PROJECT_NAME});
   }
	
   /**
    *    项目数据分析
    * @param project_uuid
    * @return
    */
   public JSONObject statistAnalysis(String project_uuid) {
		
      JSONObject returnJson = new JSONObject();
		
      //获取数据库原始数据
      StringBuilder sb = new StringBuilder();
      sb.append("WITH outbound AS(")
      .append(" SELECT A.project_uuid,A.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(" WHERE project_uuid = ? ")
      .append(" GROUP BY A.project_uuid,A.project_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 ");
      DataTableEntity dtItemizeInfo = baseDao.listTable(sb.toString(), new Object[] {project_uuid});
      if(!BaseUtil.dataTableIsEmpty(dtItemizeInfo)) {
			
         Map<String, FieldSetEntity> convertData = new HashMap<>();
         String [] materialTypes = new String[3];
			
         //通过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(materialTypes[materialType])) {
               materialTypes[materialType] = materialCode;
            }else {
               materialTypes[fseItemize.getInteger(Cmnconst.MATERIAL_TYPE)]+=","+materialCode;
            }
				
            //收集行转列数据
            String itemize = fseItemize.getString(Cmnconst.PROJECT_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.TOTAL_OUTBOUND_WEIGHT, fseItemize.getString(Cmnconst.TOTAL_OUTBOUND_WEIGHT));
               fseConvertData.setValue(materialCode, outboundWeight);
               convertData.put(itemize, fseConvertData);
            }
         }
         //将数据转为DataTableEntity返回
         if (!convertData.isEmpty()) {
            DataTableEntity finalConvertData = new DataTableEntity(); 
            convertData.forEach((k,v) -> {
               finalConvertData.addFieldSetEntity(v);
            });
            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;
   }
	
   /**
    *    删除子项验证（已经生成预算的子项不能删除）
    * @param uuid
    */
@@ -57,4 +162,31 @@
         throw new BaseException(SystemCode.PROJECT_MANAGEMENT_ITEMIZE_DEL_FAIL.getValue(), SystemCode.PROJECT_MANAGEMENT_ITEMIZE_DEL_FAIL.getText());
      }
   }
	
   /**
    *    获取分项预算信息
    * @param projectUUID   项目UUID
    * @param itemizeCode   分项编码
    * @param capge         当前页
    * @param pageSize      页大小
    * @return
    */
   public DataTableEntity listItemizeBudget(String projectUUID, String itemizeCode, Integer capge, Integer pageSize) {
		
      String filter;
      List <String> params= new ArrayList<>();
      if (BaseUtil.strIsNull(itemizeCode)) {
         filter = "project_itemize_uuid IN (SELECT uuid FROM product_project_item WHERE project_uuid=?)";
         params.add(projectUUID);
      }else {
         filter = "project_itemize_uuid IN (SELECT uuid FROM product_project_item WHERE project_uuid=? AND itemize_code like ?)";
         params.add(projectUUID);
         params.add(itemizeCode+"%");
      }
      DataTableEntity dtItemizeBudget = baseDao.listTable(Cmnconst.PRODUCT_PROJECT_BUDGET, filter, params.toArray(), null, null, pageSize, capge);
      if (!BaseUtil.dataTableIsEmpty(dtItemizeBudget)) {
         baseDao.loadPromptData(dtItemizeBudget);
      }
      return dtItemizeBudget;
   }
}