字段调整

chen.llin

2025-12-24 5dca49269399c21333b16d62945c7f74570f9ad5

 src/main/java/com/zy/asrs/service/impl/MonthlySettleServiceImpl.java

@@ -3,10 +3,12 @@
import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.baomidou.mybatisplus.plugins.Page;
import com.baomidou.mybatisplus.service.impl.ServiceImpl;
import com.core.common.Cools;
import com.core.common.SnowflakeIdWorker;
import com.core.exception.CoolException;
import com.zy.asrs.entity.MonthlySettle;
import com.zy.asrs.entity.MonthlySettleDetail;
import com.zy.asrs.entity.result.MaterialInOutRawDTO;
import com.zy.asrs.entity.result.MaterialInOutStatDTO;
import com.zy.asrs.entity.result.MonthlySettleResultVO;
import com.zy.asrs.entity.result.MonthlySettleStatisticsVO;
@@ -90,12 +92,21 @@
        cal.set(Calendar.MILLISECOND, 999);
        endDate = cal.getTime();
        
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        // 注意：order_time 是 varchar 类型，需要格式化为完整的日期时间字符串进行比较
        SimpleDateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String startDateStr = dateFormat.format(startDate);
        // 起始日期设置为当天的 00:00:00
        Calendar startCal = Calendar.getInstance();
        startCal.setTime(startDate);
        startCal.set(Calendar.HOUR_OF_DAY, 0);
        startCal.set(Calendar.MINUTE, 0);
        startCal.set(Calendar.SECOND, 0);
        startCal.set(Calendar.MILLISECOND, 0);
        String startDateStr = dateTimeFormat.format(startCal.getTime());
        String endDateStr = dateTimeFormat.format(endDate);
        int count = this.baseMapper.countUnfinishedOrdersInRange(startDateStr, endDateStr);
        return count > 0;
        // 分两次查询：入库和出库
        int pakinCount = this.baseMapper.countUnfinishedOrdersInRangePakin(startDateStr, endDateStr);
        int pakoutCount = this.baseMapper.countUnfinishedOrdersInRangePakout(startDateStr, endDateStr);
        return (pakinCount + pakoutCount) > 0;
    }

    @Override
@@ -139,21 +150,91 @@
            throw new CoolException("月结时间范围内存在未完成的订单，无法进行月结");
        }

        // 统计物料出入库数量（合并入库和出库）
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        // 统计物料出入库数量（分别查询两个表，在Java代码中合并）
        // 注意：order_time 是 varchar 类型，需要格式化为完整的日期时间字符串进行比较
        SimpleDateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String startDateStr = dateFormat.format(startDate);
        // 起始日期设置为当天的 00:00:00
        Calendar startCal = Calendar.getInstance();
        startCal.setTime(startDate);
        startCal.set(Calendar.HOUR_OF_DAY, 0);
        startCal.set(Calendar.MINUTE, 0);
        startCal.set(Calendar.SECOND, 0);
        startCal.set(Calendar.MILLISECOND, 0);
        String startDateStr = dateTimeFormat.format(startCal.getTime());
        String endDateStr = dateTimeFormat.format(endDate);
        List<MaterialInOutStatDTO> materialStats = this.baseMapper.statisticsMaterialInOut(startDateStr, endDateStr);
        
        // 分别查询入库表和出库表
        List<MaterialInOutRawDTO> pakinDataList = this.baseMapper.statisticsMaterialInOutFromPakin(startDateStr, endDateStr);
        List<MaterialInOutRawDTO> pakoutDataList = this.baseMapper.statisticsMaterialInOutFromPakout(startDateStr, endDateStr);
        
        // 合并两个表的数据
        List<MaterialInOutRawDTO> rawDataList = new java.util.ArrayList<>();
        if (pakinDataList != null && !pakinDataList.isEmpty()) {
            rawDataList.addAll(pakinDataList);
        }
        if (pakoutDataList != null && !pakoutDataList.isEmpty()) {
            rawDataList.addAll(pakoutDataList);
        }
        
        // 检查是否有出入库历史订单
        if (materialStats == null || materialStats.isEmpty()) {
        if (rawDataList == null || rawDataList.isEmpty()) {
            throw new CoolException("月结时间范围内没有出入库历史订单，无法进行月结");
        }
        
        // 在Java代码中处理分类统计逻辑
        // 1. 处理NULL值（batch、brand、maktx）
        // 2. 根据物料编码、批次、品牌分组
        // 3. 根据 pakin_pakout_status 区分入库和出库
        Map<String, MaterialInOutStatDTO> materialStatsMap = new HashMap<>();
        Map<String, String> maktxMap = new HashMap<>(); // 存储物料名称（取第一个非空值）
        
        for (MaterialInOutRawDTO raw : rawDataList) {
            // 处理NULL值
            String matnr = raw.getMatnr();
            String batch = raw.getBatch() != null ? raw.getBatch() : "";
            String brand = raw.getBrand() != null ? raw.getBrand() : "";
            String maktx = raw.getMaktx() != null ? raw.getMaktx() : "";
            String key = matnr + "_" + batch + "_" + brand;
            
            // 保存物料名称（取第一个非空值）
            if (maktx != null && !maktx.isEmpty() && !maktxMap.containsKey(key)) {
                maktxMap.put(key, maktx);
            }
            
            MaterialInOutStatDTO stat = materialStatsMap.get(key);
            if (stat == null) {
                stat = new MaterialInOutStatDTO();
                stat.setMatnr(matnr);
                stat.setMaktx(maktxMap.getOrDefault(key, ""));
                stat.setBatch(batch);
                stat.setBrand(brand);
                stat.setInQty(BigDecimal.ZERO);
                stat.setOutQty(BigDecimal.ZERO);
                materialStatsMap.put(key, stat);
            }
            
            // 根据 pakin_pakout_status 分类统计
            // 1 = 入库，2 = 出库，0 = 未知（已在SQL中过滤）
            BigDecimal qty = raw.getQty() != null ? raw.getQty() : BigDecimal.ZERO;
            Integer status = raw.getPakinPakoutStatus();
            if (status != null && qty.compareTo(BigDecimal.ZERO) > 0) {
                if (status == 1) {
                    // 入库
                    stat.setInQty(stat.getInQty().add(qty));
                } else if (status == 2) {
                    // 出库
                    stat.setOutQty(stat.getOutQty().add(qty));
                }
            }
        }
        
        // 转换为List
        List<MaterialInOutStatDTO> materialStats = new java.util.ArrayList<>(materialStatsMap.values());

        // 获取上一个月结记录（用于计算期初库存）
        MonthlySettle previousSettle = getLatestSettle();
        // 获取上一个月结记录（用于计算期初库存和承接上一期的物料）
        MonthlySettle   previousSettle = getLatestSettle();
        Map<String, BigDecimal> previousEndingQtyMap = new HashMap<>();
        Map<String, PreviousSettleEndingQtyDTO> previousDetailMap = new HashMap<>(); // 存储上一期的完整明细信息
        if (previousSettle != null) {
            List<PreviousSettleEndingQtyDTO> previousDetails = this.baseMapper.getPreviousSettleEndingQty(previousSettle.getId());
            for (PreviousSettleEndingQtyDTO detail : previousDetails) {
@@ -162,6 +243,7 @@
                            (detail.getBrand() != null ? detail.getBrand() : "");
                BigDecimal endingQty = detail.getEndingQty() != null ? detail.getEndingQty() : BigDecimal.ZERO;
                previousEndingQtyMap.put(key, endingQty);
                previousDetailMap.put(key, detail); // 保存完整信息，用于后续创建明细
            }
        }

@@ -184,7 +266,20 @@
        BigDecimal totalOutQty = BigDecimal.ZERO;
        int materialCount = 0;

        // 创建月结明细
        // 创建 Map 存储本期有出入库的物料（用于判断是否需要从上一期继承）
        Map<String, MaterialInOutStatDTO> currentMaterialMap = new HashMap<>();
        for (MaterialInOutStatDTO stat : materialStats) {
            String matnr = stat.getMatnr();
            String batch = stat.getBatch() != null ? stat.getBatch() : "";
            String brand = stat.getBrand() != null ? stat.getBrand() : "";
            String key = matnr + "_" + batch + "_" + brand;
            currentMaterialMap.put(key, stat);
        }

        // 收集所有明细记录，用于批量插入
        List<MonthlySettleDetail> detailList = new java.util.ArrayList<>();

        // 1. 处理本期有出入库的物料
        for (MaterialInOutStatDTO stat : materialStats) {
            // 1. 提取基础信息
            String matnr = stat.getMatnr();
@@ -199,21 +294,76 @@
            // 3. 计算库存相关数量
            BigDecimal beginningQty = getBeginningQty(matnr, batch, brand, previousEndingQtyMap);
            BigDecimal endingQty = calculateEndingQty(beginningQty, inQty, outQty);
            BigDecimal stockQtyDecimal = getCurrentStockQty(matnr, batch);
            BigDecimal diffQty = calculateDiffQty(stockQtyDecimal, endingQty);
            // 差异数量 = 期末库存 - 期初库存（期末大于期初时为正数）
            BigDecimal diffQty = calculateDiffQty(beginningQty, endingQty);
            
            // 4. 创建并保存明细记录
            // 4. 创建明细记录（暂不插入）
            MonthlySettleDetail detail = buildMonthlySettleDetail(
                monthlySettle.getId(), settleNo, matnr, batch, maktx, brand,
                beginningQty, inQty, outQty, endingQty, stockQtyDecimal, diffQty
                beginningQty, inQty, outQty, endingQty, diffQty
            );
            detail.setIsDeleted(0); // 未删除
            monthlySettleDetailMapper.insert(detail);
            detailList.add(detail);

            // 5. 累计统计
            totalInQty = totalInQty.add(inQty);
            totalOutQty = totalOutQty.add(outQty);
            materialCount++;
        }

        // 2. 处理上一期存在但本期没有出入库的物料（需要承接显示）
        if (previousSettle != null && !previousDetailMap.isEmpty()) {
            for (Map.Entry<String, PreviousSettleEndingQtyDTO> entry : previousDetailMap.entrySet()) {
                String key = entry.getKey();
                // 如果本期没有出入库，但上一期有期末库存（且不为0），则需要创建一条记录
                if (!currentMaterialMap.containsKey(key)) {
                    PreviousSettleEndingQtyDTO previousDetail = entry.getValue();
                    BigDecimal previousEndingQty = previousDetail.getEndingQty() != null ? previousDetail.getEndingQty() : BigDecimal.ZERO;
                    
                    // 如果上一期的期末库存不为0，或者即使为0也要显示（用于对比）
                    // 这里我们总是创建记录，即使上一期期末库存为0，因为用户要求"如果上一期存在过，本期库存归零也要显示一条为0的记录"
                    String matnr = previousDetail.getMatnr();
                    String batch = previousDetail.getBatch() != null ? previousDetail.getBatch() : "";
                    String brand = previousDetail.getBrand() != null ? previousDetail.getBrand() : "";
                    String maktx = previousDetail.getMaktx() != null ? previousDetail.getMaktx() : "";
                    
                    // 本期没有出入库，所以入库和出库数量都为0
                    BigDecimal inQty = BigDecimal.ZERO;
                    BigDecimal outQty = BigDecimal.ZERO;
                    
                    // 期初库存 = 上一期的期末库存
                    BigDecimal beginningQty = previousEndingQty;
                    // 期末库存 = 期初 + 入库 - 出库 = 期初（因为本期没有出入库）
                    BigDecimal endingQty = beginningQty;
                    // 差异数量 = 期初库存 - 期末库存（期初大于期末时为正数）
                    BigDecimal diffQty = calculateDiffQty(beginningQty, endingQty);
                    
                    // 创建明细记录（暂不插入）
                    MonthlySettleDetail detail = buildMonthlySettleDetail(
                        monthlySettle.getId(), settleNo, matnr, batch, maktx, brand,
                        beginningQty, inQty, outQty, endingQty, diffQty
                    );
                    detail.setIsDeleted(0); // 未删除
                    detailList.add(detail);
                    
                    // 累计统计（虽然入库和出库为0，但也要计入物料种类数）
                    materialCount++;
                }
            }
        }

        // 分批插入，每批1000条，避免一次性插入过多数据
        if (!detailList.isEmpty()) {
            int batchSize = 1000;
            for (int i = 0; i < detailList.size(); i += batchSize) {
                int end = Math.min(i + batchSize, detailList.size());
                List<MonthlySettleDetail> batch = detailList.subList(i, end);
                for (MonthlySettleDetail detail : batch) {
                    if (monthlySettleDetailMapper.insert(detail) <= 0) {
                        throw new CoolException("插入月结明细失败");
                    }
                }
            }
        }

        // 更新月结主记录
@@ -311,18 +461,21 @@
    }

    /**
     * 获取当前实际库存数量
     * 获取当前实际库存数量（已废弃，改用批量查询）
     * @deprecated 使用批量查询方法替代，避免N+1查询问题
     */
    @Deprecated
    private BigDecimal getCurrentStockQty(String matnr, String batch) {
        Double stockQty = manLocDetlService.queryStockAnfme(matnr, batch);
        return stockQty != null ? BigDecimal.valueOf(stockQty) : BigDecimal.ZERO;
    }

    /**
     * 计算差异数量（实际库存-期末库存）
     * 计算差异数量（期末库存-期初库存）
     * 期末大于期初时为正数，表示库存增加
     */
    private BigDecimal calculateDiffQty(BigDecimal stockQty, BigDecimal endingQty) {
        return stockQty.subtract(endingQty);
    private BigDecimal calculateDiffQty(BigDecimal beginningQty, BigDecimal endingQty) {
        return endingQty.subtract(beginningQty);
    }

    /**
@@ -331,7 +484,7 @@
    private MonthlySettleDetail buildMonthlySettleDetail(
            Long settleId, String settleNo, String matnr, String batch, String maktx, String brand,
            BigDecimal beginningQty, BigDecimal inQty, BigDecimal outQty, BigDecimal endingQty,
            BigDecimal stockQty, BigDecimal diffQty) {
            BigDecimal diffQty) {
        MonthlySettleDetail detail = new MonthlySettleDetail();
        // 基本信息
        detail.setSettleId(settleId);
@@ -345,7 +498,8 @@
        detail.setInQty(inQty);
        detail.setOutQty(outQty);
        detail.setEndingQty(endingQty);
        detail.setStockQty(stockQty);
        // stock_qty 字段已废弃，实际库存等于期末库存，不再单独存储
        // detail.setStockQty(endingQty);
        detail.setDiffQty(diffQty);
        // 时间信息
        detail.setCreateTime(new Date());
@@ -381,5 +535,59 @@
        settle.setUpdateTime(new Date());
        this.updateById(settle);
    }

    @Override
    public boolean isOrderTimeInSettledRange(String orderTime) {
        if (Cools.isEmpty(orderTime)) {
            return false;
        }
        try {
            // 解析订单业务时间
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Date orderDate = sdf.parse(orderTime);
            
            // 查询所有已月结的记录（status=1且未删除）
            EntityWrapper<MonthlySettle> wrapper = new EntityWrapper<>();
            wrapper.eq("status", 1);
            wrapper.eq("is_deleted", 0);
            List<MonthlySettle> settledList = this.selectList(wrapper);
            
            if (settledList == null || settledList.isEmpty()) {
                return false;
            }
            
            // 检查订单时间是否在任何已月结的区间内
            for (MonthlySettle settle : settledList) {
                Date startDate = settle.getStartDate();
                Date endDate = settle.getEndDate();
                
                // 确保startDate是当天的00:00:00
                Calendar startCal = Calendar.getInstance();
                startCal.setTime(startDate);
                startCal.set(Calendar.HOUR_OF_DAY, 0);
                startCal.set(Calendar.MINUTE, 0);
                startCal.set(Calendar.SECOND, 0);
                startCal.set(Calendar.MILLISECOND, 0);
                
                // 确保endDate是当天的23:59:59.999
                Calendar endCal = Calendar.getInstance();
                endCal.setTime(endDate);
                endCal.set(Calendar.HOUR_OF_DAY, 23);
                endCal.set(Calendar.MINUTE, 59);
                endCal.set(Calendar.SECOND, 59);
                endCal.set(Calendar.MILLISECOND, 999);
                
                // 判断订单时间是否在区间内：[startDate的00:00:00, endDate的23:59:59.999]
                if (!orderDate.before(startCal.getTime()) && !orderDate.after(endCal.getTime())) {
                    return true;
                }
            }
            
            return false;
        } catch (Exception e) {
            log.error("检查订单时间是否在已月结区间内失败", e);
            return false;
        }
    }
}