管路&气柜产能负载对返回结果集进行排序

hongjli

2025-05-23 771fc6353fe9ef5e9f1095de75f29210adfe3751

 aps-modules/aps-core/src/main/java/com/aps/core/service/impl/ApsGasPipingRouteStatServiceImpl.java

@@ -6,10 +6,12 @@
import com.aps.common.core.utils.uuid.IdUtils;
import com.aps.common.security.utils.SecurityUtils;
import com.aps.core.domain.*;
import com.aps.core.domain.ApsPlate.ApsPlateStandardRequireError;
import com.aps.core.mapper.*;
import com.aps.core.service.IApsGasMaterialUsageService;
import com.aps.core.service.IApsGasPipingRouteStatService;
import com.aps.core.service.IApsStandardProcessService;
import com.aps.core.mapper.ApsPlateStandardRequireErrorMapper;
import jakarta.servlet.http.HttpServletResponse;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
@@ -58,6 +60,9 @@

    @Autowired
    private IApsStandardProcessService apsStandardProcessService;

    @Autowired
    private ApsPlateStandardRequireErrorMapper apsPlateStandardRequireErrorMapper;

    /**
     * 查询气体管路产能负载统计
@@ -1117,6 +1122,12 @@
        // 处理手工气体工单数据 - 使用联合查询获取所有相关数据
        List<Map<String, Object>> moDataList = apsGasPipingRouteStatMapper.selectMoRouteData();
        
        // 获取所有的MO数据，用于检查哪些MO没有关联到工艺路线
        List<Map<String, Object>> allMoData = apsGasPipingRouteStatMapper.selectAllMoData();
        
        // 记录已关联工艺路线的MO编号
        Set<String> processedMoSet = new HashSet<>();
        
        // 按工单号分组，便于处理同一工单的不同工序
        Map<String, List<Map<String, Object>>> moGroups = moDataList.stream()
                .collect(Collectors.groupingBy(data -> data.get("work_order_no").toString()));
@@ -1124,6 +1135,9 @@
        // 处理每个工单的工艺路线
        for (String workOrderNo : moGroups.keySet()) {
            List<Map<String, Object>> workOrderData = moGroups.get(workOrderNo);
            
            // 标记该工单已处理
            processedMoSet.add(workOrderNo);
            
            // 按工序号排序
            workOrderData.sort((a, b) -> {
@@ -1202,6 +1216,36 @@
                
                statList.addAll(processList);
            }
        }
        
        // 处理没有关联到工艺路线的MO数据
        List<ApsPlateStandardRequireError> errorList = new ArrayList<>();
        for (Map<String, Object> moData : allMoData) {
            String workOrderNo = moData.get("mo").toString();
            // 如果该工单没有被处理过，说明没有关联到工艺路线
            if (!processedMoSet.contains(workOrderNo)) {
                ApsPlateStandardRequireError error = ApsPlateStandardRequireError.builder()
                        .id(Long.valueOf(IdUtils.fastSimpleUUID().hashCode()))
                        .batchNumber(batchNumber)
                        .requireId(IdUtil.getSnowflakeNextId())
                        .docNum(workOrderNo)
                        .itemNum(moData.get("material_code").toString())
                        .orgCode(moData.get("factory").toString())
                        .message("标准工艺路线不存在")
                        .delFlag("0")
                        .build();
                
                // 设置基类属性
                error.setCreateBy(SecurityUtils.getUsername());
                error.setCreateTime(truncateToSeconds(DateUtils.getNowDate()));
                
                errorList.add(error);
            }
        }
        
        // 批量插入异常数据
        if (!errorList.isEmpty()) {
            apsPlateStandardRequireErrorMapper.batchInsert(errorList);
        }
        
        // 批量插入数据
@@ -1445,9 +1489,61 @@
        // 查询原始数据（不依赖数据库聚合）
        List<Map<String, Object>> rawData = apsGasPipingRouteStatMapper.selectRawStatData(queryParams);
        
        // 如果没有查询到原始数据，但需要返回所有工序/车间的数据
        if (rawData.isEmpty()) {
            // 创建没有时间限制的基础查询参数
            Map<String, Object> baseParams = new HashMap<>(queryParams);
            baseParams.remove("yearStart");
            baseParams.remove("monthStart");
            baseParams.remove("yearEnd");
            baseParams.remove("monthEnd");
            
            // 直接从aps_gas_piping_route_stat表查询所有可能的工序和车间
            List<Map<String, Object>> baseData = apsGasPipingRouteStatMapper.selectBaseStatData(baseParams);
            
            // 为每个工序/车间创建基于时间的空数据
            for (Map<String, Object> data : baseData) {
                String rowGroupValue = getStringValue(data, rowGroupBy);
                if (rowGroupValue == null || rowGroupValue.trim().isEmpty()) continue;
                
                Map<String, Object> rowEntry = new HashMap<>();
                Map<String, Object> rowDetail = new HashMap<>();
                
                // 添加基本信息
                String plant = getStringValue(data, "plant");
                String major = getStringValue(data, "major");
                String workshop = getStringValue(data, "workshop");
                
                if (groupByPlant) rowDetail.put("plant", plant);
                if (groupByMajor) rowDetail.put("major", major);
                if (groupByWorkshop) rowDetail.put("workshop", workshop);
                
                // 处理工序名称 - 如果rowGroupBy为workshop，需要添加processName字段
                if ("workshop".equals(rowGroupBy)) {
                    String processName = getStringValue(data, "processName");
                    rowDetail.put("processName", processName != null ? processName : "");
                }
                
                // 为每个时间点创建零值数据
                List<Map<String, Object>> timeDataList = new ArrayList<>();
                for (String timePoint : timePoints) {
                    Map<String, Object> pointData = new HashMap<>();
                    pointData.put("planDay", timePoint);
                    pointData.put("requireTimes", BigDecimal.ZERO);
                    pointData.put("designTimes", BigDecimal.ZERO); 
                    pointData.put("capacityLoad", BigDecimal.ZERO);
                    timeDataList.add(pointData);
                }
                
                rowDetail.put("timeData", timeDataList);
                rowEntry.put(rowGroupValue, rowDetail);
                plantTable.add(rowEntry);
            }
            
            result.put("plantTable", plantTable);
            result.put("timePoints", timePoints);
            result.put("rowGroupBy", rowGroupBy);
            
            return result;
        }
        
@@ -1455,6 +1551,8 @@
        // 使用组合key来实现多维度分组（动态rowGroupBy字段 + 可选的工厂/专业/车间）
        Map<String, Map<String, Object>> groupInfoMap = new HashMap<>();
        Map<String, Map<String, BigDecimal>> groupTimeDataMap = new HashMap<>();
        // 存储每个groupKey对应的processName集合
        Map<String, Set<String>> groupProcessNamesMap = new HashMap<>();
        
        // 遍历原始数据，按多维度分组进行聚合
        for (Map<String, Object> data : rawData) {
@@ -1506,6 +1604,14 @@
                groupInfoMap.put(groupKey, groupInfo);
            }
            
            // 收集processName
            if (processName != null && !processName.trim().isEmpty()) {
                if (!groupProcessNamesMap.containsKey(groupKey)) {
                    groupProcessNamesMap.put(groupKey, new HashSet<>());
                }
                groupProcessNamesMap.get(groupKey).add(processName);
            }
            
            // 计算时间点Key
            String timeKey;
            LocalDate planStartLocalDate = processPlanStartDay.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
@@ -1552,8 +1658,17 @@
                rowDetail.put("workshop", groupInfo.get("workshop"));
            }
            
            // 保留工序名称信息，以便前端展示
            if (!"processName".equals(rowGroupBy)) {
            // 处理processName - 如果rowGroupBy为workshop，则将所有processName用分号连接
            if ("workshop".equals(rowGroupBy)) {
                Set<String> processNames = groupProcessNamesMap.getOrDefault(groupKey, new HashSet<>());
                if (!processNames.isEmpty()) {
                    String joinedProcessNames = String.join(";", processNames);
                    rowDetail.put("processName", joinedProcessNames);
                } else {
                    rowDetail.put("processName", groupInfo.get("processName"));
                }
            } else if (!"processName".equals(rowGroupBy)) {
                // 保留工序名称信息，以便前端展示
                rowDetail.put("processName", groupInfo.get("processName"));
            }
            
@@ -1564,13 +1679,13 @@
            for (String timePoint : timePoints) {
                Map<String, Object> pointData = new HashMap<>();
                pointData.put("planDay", timePoint);
                pointData.put("designTimes", 0); // 设计工时暂时给0
                
                // 获取该时间点的需求工时，如果不存在则设为0
                BigDecimal requireTimes = timeMap.getOrDefault(timePoint, BigDecimal.ZERO);
                pointData.put("requireTimes", requireTimes);
                
                // 计算产能负荷（暂时为0）
                // 设计工时和产能负荷稍后计算
                pointData.put("designTimes", 0);
                pointData.put("capacityLoad", 0);
                
                timeDataList.add(pointData);
@@ -1580,11 +1695,189 @@
            rowEntry.put(rowGroupValue, rowDetail);
            plantTable.add(rowEntry);
        }
        

        // 在返回前查询设计产能数据并计算产能负荷
        for (Map<String, Object> rowEntry : plantTable) {
            for (String rowKey : rowEntry.keySet()) {
                Map<String, Object> rowDetail = (Map<String, Object>) rowEntry.get(rowKey);
                List<Map<String, Object>> timeDataList = (List<Map<String, Object>>) rowDetail.get("timeData");
                
                // 获取基本信息
                String plant = rowDetail.containsKey("plant") ? (String) rowDetail.get("plant") : null;
                String major = rowDetail.containsKey("major") ? (String) rowDetail.get("major") : null;
                String workshop = rowDetail.containsKey("workshop") ? (String) rowDetail.get("workshop") : null;
                
                // 处理不同的rowGroupBy情况
                if ("workshop".equals(rowGroupBy) && rowDetail.containsKey("processName")) {
                    // 情况1: 按workshop聚合，需要拆分processName字段
                    String processNamesStr = (String) rowDetail.get("processName");
                    String[] processNames = processNamesStr.split(";");
                    
                    // 优化查询 - 创建缓存，按月份缓存查询结果
                    // Key: year-month-processName, Value: 查询结果列表
                    Map<String, List<ApsGasPipelineCapacityPlan>> capacityPlanCache = new HashMap<>();
                    
                    // 遍历每个时间点
                    for (Map<String, Object> timeData : timeDataList) {
                        String planDay = (String) timeData.get("planDay");
                        BigDecimal requireTimes = new BigDecimal(timeData.get("requireTimes").toString());
                        BigDecimal totalDesignTimes = BigDecimal.ZERO;
                        
                        // 拆分年月日
                        String[] dateParts = planDay.split("-");
                        String year = dateParts[0];
                        String month = dateParts[1];
                        // 统一month格式为整数字符串，去掉前导零
                        month = String.valueOf(Integer.parseInt(month));
                        String yearMonth = year + "-" + month;
                        
                        // 累加多个工序的设计产能
                        for (String processName : processNames) {
                            // 构建缓存键
                            String cacheKey = yearMonth + "-" + processName.trim();
                            
                            // 从缓存获取或查询数据
                            List<ApsGasPipelineCapacityPlan> capacityPlans;
                            if (capacityPlanCache.containsKey(cacheKey)) {
                                capacityPlans = capacityPlanCache.get(cacheKey);
                            } else {
                                // 使用专用查询方法查询设计产能数据
                                // 按文档要求：根据process_name和plant去aps_gas_pipeline_capacity_plan表中查询
                                capacityPlans = apsGasPipelineCapacityPlanMapper.selectDesignCapacityForInterface2(
                                        processName.trim(), year, month, null, plant);
                                
                                // 将结果存入缓存
                                capacityPlanCache.put(cacheKey, capacityPlans);
                            }
                            
                            // 累加设计产能值
                            for (ApsGasPipelineCapacityPlan plan : capacityPlans) {
                                if ("day".equalsIgnoreCase(timeGranularity)) {
                                    // 日粒度使用日产出数量
                                    if (plan.getDayProduceAllNum() != null) {
                                        totalDesignTimes = totalDesignTimes.add(plan.getDayProduceAllNum());
                                    }
                                } else {
                                    // 月粒度使用月产出总数量
                                    if (plan.getMonthProduceAllNum() != null) {
                                        totalDesignTimes = totalDesignTimes.add(plan.getMonthProduceAllNum());
                                    }
                                }
                            }
                        }
                        
                        // 更新设计工时
                        timeData.put("designTimes", totalDesignTimes);
                        
                        // 计算产能负荷 = 需求产能/设计产能×100%
                        if (totalDesignTimes.compareTo(BigDecimal.ZERO) > 0) {
                            BigDecimal capacityLoad = requireTimes
                                    .divide(totalDesignTimes, 2, RoundingMode.HALF_UP)
                                    .multiply(new BigDecimal(100));
                            timeData.put("capacityLoad", capacityLoad);
                        } else {
                            timeData.put("capacityLoad", 0);
                        }
                    }
                } else {
                    // 情况2: 按processName或其他字段聚合
                    String processName = rowDetail.containsKey("processName") ? 
                            (String) rowDetail.get("processName") : rowKey;
                    
                    // 优化查询 - 创建缓存，按月份缓存查询结果
                    Map<String, List<ApsGasPipelineCapacityPlan>> capacityPlanCache = new HashMap<>();
                    
                    // 遍历每个时间点
                    for (Map<String, Object> timeData : timeDataList) {
                        String planDay = (String) timeData.get("planDay");
                        BigDecimal requireTimes = new BigDecimal(timeData.get("requireTimes").toString());
                        
                        // 拆分年月日
                        String[] dateParts = planDay.split("-");
                        String year = dateParts[0];
                        String month = dateParts[1];
                        // 统一month格式为整数字符串，去掉前导零
                        month = String.valueOf(Integer.parseInt(month));
                        String yearMonth = year + "-" + month;
                        
                        // 构建缓存键
                        String cacheKey = yearMonth + "-" + processName;
                        
                        // 从缓存获取或查询数据
                        List<ApsGasPipelineCapacityPlan> capacityPlans;
                        if (capacityPlanCache.containsKey(cacheKey)) {
                            capacityPlans = capacityPlanCache.get(cacheKey);
                        } else {
                            // 使用专用查询方法查询设计产能数据
                            // 按文档要求：根据process_name和plant去aps_gas_pipeline_capacity_plan表中查询
                            capacityPlans = apsGasPipelineCapacityPlanMapper.selectDesignCapacityForInterface2(
                                    processName, year, month, null, plant);
                            
                            // 将结果存入缓存
                            capacityPlanCache.put(cacheKey, capacityPlans);
                        }
                        
                        // 累加设计产能值
                        BigDecimal totalDesignTimes = BigDecimal.ZERO;
                        for (ApsGasPipelineCapacityPlan plan : capacityPlans) {
                            if ("day".equalsIgnoreCase(timeGranularity)) {
                                // 日粒度使用日产出数量
                                if (plan.getDayProduceAllNum() != null) {
                                    totalDesignTimes = totalDesignTimes.add(plan.getDayProduceAllNum());
                                }
                            } else {
                                // 月粒度使用月产出总数量
                                if (plan.getMonthProduceAllNum() != null) {
                                    totalDesignTimes = totalDesignTimes.add(plan.getMonthProduceAllNum());
                                }
                            }
                        }
                        
                        // 更新设计工时
                        timeData.put("designTimes", totalDesignTimes);
                        
                        // 计算产能负荷 = 需求产能/设计产能×100%
                        if (totalDesignTimes.compareTo(BigDecimal.ZERO) > 0) {
                            BigDecimal capacityLoad = requireTimes
                                    .divide(totalDesignTimes, 2, RoundingMode.HALF_UP)
                                    .multiply(new BigDecimal(100));
                            timeData.put("capacityLoad", capacityLoad);
                        } else {
                            timeData.put("capacityLoad", 0);
                        }
                    }
                }
            }
        }

        result.put("plantTable", plantTable);
        result.put("timePoints", timePoints);
        result.put("rowGroupBy", rowGroupBy);
        
        // 根据文档注意点3，添加排序逻辑
        if (!plantTable.isEmpty()) {
            // 对聚合结果进行排序
            Collections.sort(plantTable, (map1, map2) -> {
                String key1 = map1.keySet().iterator().next();
                String key2 = map2.keySet().iterator().next();
                
                // 首先按rowGroupBy排序（processName或workshop）
                int result1 = key1.compareTo(key2);
                if (result1 != 0) {
                    return result1;
                }
                
                // 如果rowGroupBy相同，再按plant排序
                Map<String, Object> detail1 = (Map<String, Object>) map1.get(key1);
                Map<String, Object> detail2 = (Map<String, Object>) map2.get(key2);
                
                String plant1 = detail1.containsKey("plant") ? (String) detail1.get("plant") : "";
                String plant2 = detail2.containsKey("plant") ? (String) detail2.get("plant") : "";
                
                return plant1.compareTo(plant2);
            });
        }
        
        return result;
    }