refactor(database): 优化库存管理相关数据库查询和业务逻辑

liding

17 小时以前 e03206c08c9604decc4723c36fc25573681f2fee

refactor(database): 优化库存管理相关数据库查询和业务逻辑

- 在审批流程查询中添加按状态排序逻辑，优先显示非完成状态的记录
- 重构自定义库存查询，使用子查询替代左连接方式计算可用库存数量
- 优化采购记录查询，添加出库统计子查询并完善库存数量字段映射
- 移除服务层手动计算库存数量的业务逻辑，改为依赖SQL查询结果
- 简化库存价值计算逻辑，增加空值检查和默认值处理
- 统一不同类型的库存查询接口，提升代码可维护性

 src/main/java/com/ruoyi/procurementrecord/service/impl/ProcurementRecordServiceImpl.java

@@ -598,56 +598,20 @@

    @Override
    public IPage<ProcurementPageDtoCopy> listPageCopyByProduction(Page page, ProcurementPageDto procurementDto) {
        IPage<ProcurementPageDtoCopy> procurementPageDtoCopyIPage = procurementRecordMapper.listPagePRS(page, procurementDto);
        IPage<ProcurementPageDtoCopy> procurementPageDtoCopyIPage = procurementRecordMapper.listPageCopyByProduction(page, procurementDto);
        List<ProcurementPageDtoCopy> procurementPageDtoCopyList = procurementPageDtoCopyIPage.getRecords();
        // 计算待入库数量
        // 查询采购记录已入库数量
        List<Integer> collect = procurementPageDtoCopyList.stream().map(ProcurementPageDtoCopy::getId).collect(Collectors.toList());
        if(CollectionUtils.isEmpty( collect)){
            return procurementPageDtoCopyIPage;
        }
        // 1. 查询采购记录已入库的出库记录（按storageId分组）
        LambdaQueryWrapper<ProcurementRecordOut> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.in(ProcurementRecordOut::getProcurementRecordStorageId, collect);
        List<ProcurementRecordOut> recordOutList = procurementRecordOutMapper.selectList(queryWrapper);

        // 2. 按SalesLedgerProductId分组，统计每个id对应的已出库数量总和-已出库数量
        Map<Long, BigDecimal> storageIdToTotalOutNumMap = recordOutList.stream()
                .collect(Collectors.groupingBy(
                        ProcurementRecordOut::getSalesLedgerProductId,
                        Collectors.reducing(
                                BigDecimal.ZERO,
                                ProcurementRecordOut::getInboundNum,
                                (a, b) -> a.add(b == null ? BigDecimal.ZERO : b)
                        )
                ));
        // 2. procurementPageDtoCopyList按SalesLedgerProductId分组，统计每个id对应的已出库数量总和-入库库数量
        Map<Long, BigDecimal> storageIdToTotalintNumMap = procurementPageDtoCopyList.stream()
                .collect(Collectors.groupingBy(
                        ProcurementPageDtoCopy::getSalesLedgerProductId,
                        Collectors.reducing(
                                BigDecimal.ZERO,
                                ProcurementPageDtoCopy::getInboundNum,
                                (a, b) -> a.add(b == null ? BigDecimal.ZERO : b)
                        )
                ));
        // 3. 循环给dto赋值
        // SQL已通过子查询计算了出库数量(totalInboundNum)和待出库数量(inboundNum0)，此处补充库存价值
        for (ProcurementPageDtoCopy dto : procurementPageDtoCopyList) {
            Integer storageId = dto.getId();
            Integer salesLedgerProductId = Integer.valueOf(Math.toIntExact(dto.getSalesLedgerProductId()));
            // 获取当前salesLedgerProductId对应的已出库总数（默认0）
            BigDecimal totalInboundNum = storageIdToTotalOutNumMap.getOrDefault(salesLedgerProductId, BigDecimal.ZERO);
            // 已出库数量
            dto.setTotalInboundNum(totalInboundNum);
            // 待出库数量 = 总数量 - 已出库数量（总数量空值则默认0）
//            BigDecimal totalNum = dto.getInboundNum() == null ? BigDecimal.ZERO : dto.getInboundNum();
            BigDecimal totalNum = storageIdToTotalintNumMap.getOrDefault(salesLedgerProductId, BigDecimal.ZERO);
            dto.setInboundNum(totalNum);
            dto.setInboundNum0(totalNum.subtract(totalInboundNum));

            // 库存价值 = 已出库数量 * 单价（单价空值则默认0）
            if (dto.getInboundNum0() == null) {
                dto.setInboundNum0(dto.getInboundNum());
            }
            if (dto.getTotalInboundNum() == null) {
                dto.setTotalInboundNum(BigDecimal.ZERO);
            }
            BigDecimal unitPrice = dto.getUnitPrice() == null ? BigDecimal.ZERO : dto.getUnitPrice();
            dto.setTotalPrice(totalInboundNum.multiply(unitPrice));
            if (dto.getTotalInboundNum() != null) {
                dto.setTotalPrice(dto.getTotalInboundNum().multiply(unitPrice));
            }
        }
        return procurementPageDtoCopyIPage;
    }
@@ -672,42 +636,15 @@
        IPage<CustomStorage> pageList = customStorageMapper.listPageCopyByCustom(page, customStorage);

        List<CustomStorage> procurementPageDtoCopyList = pageList.getRecords();
        // 计算待入库数量
        // 查询采购记录已入库数量
        List<Integer> collect = procurementPageDtoCopyList.stream().map(CustomStorage::getId).collect(Collectors.toList());
        if(CollectionUtils.isEmpty( collect)){
            return pageList;
        }
        LambdaQueryWrapper<ProcurementRecordOut> procurementRecordLambdaQueryWrapper = new LambdaQueryWrapper<>();
        procurementRecordLambdaQueryWrapper.in(ProcurementRecordOut::getProcurementRecordStorageId, collect);
        procurementRecordLambdaQueryWrapper.eq(ProcurementRecordOut::getType, 3);
        List<ProcurementRecordOut> procurementRecords = procurementRecordOutMapper.selectList(procurementRecordLambdaQueryWrapper);
        if(CollectionUtils.isEmpty( procurementRecords)){
            return pageList;
        }
        // SQL已通过子查询计算了出库数量(totalInboundNum)和待出库数量(inboundNum0)，此处补充库存价值
        for (CustomStorage dto : procurementPageDtoCopyList) {
            // 根据采购台账ID筛选对应的出库记录
            List<ProcurementRecordOut> collect1 = procurementRecords.stream()
                    .filter(ProcurementRecordOut -> ProcurementRecordOut.getProcurementRecordStorageId().equals(dto.getId()))
                    .collect(Collectors.toList());

            // 如果没有相关的出库记录，跳过该条数据
            if(CollectionUtils.isEmpty(collect1)){
            if (dto.getInboundNum0() == null) {
                dto.setInboundNum0(dto.getInboundNum());
                dto.setTotalInboundNum(BigDecimal.ZERO);
                continue;
            }

            // 计算已出库数量总和，并设置待出库数量
            BigDecimal totalInboundNum = collect1.stream()
                    .map(ProcurementRecordOut::getInboundNum)
                    .reduce(BigDecimal.ZERO, BigDecimal::add);
            // 出库数量 = 总数量 - 待出库数量
            dto.setTotalInboundNum(totalInboundNum);
            // 待出库数量 = 总数量 - 已出库数量
            dto.setInboundNum0(dto.getInboundNum().subtract(totalInboundNum));
            // 库存价值
            if(dto.getTaxInclusiveUnitPrice() != null){
            if (dto.getTotalInboundNum() == null) {
                dto.setTotalInboundNum(BigDecimal.ZERO);
            }
            if (dto.getTaxInclusiveUnitPrice() != null && dto.getInboundNum0() != null) {
                dto.setTaxInclusiveTotalPrice(dto.getInboundNum0().multiply(dto.getTaxInclusiveUnitPrice()));
            }
        }
@@ -841,42 +778,15 @@
    public IPage<ProcurementPageDtoCopy> listPageCopy(Page page, ProcurementPageDto procurementDto) {
        IPage<ProcurementPageDtoCopy> procurementPageDtoCopyIPage = procurementRecordMapper.listPageCopy(page, procurementDto);
        List<ProcurementPageDtoCopy> procurementPageDtoCopyList = procurementPageDtoCopyIPage.getRecords();
        // 计算待入库数量
        // 查询采购记录已入库数量
        List<Integer> collect = procurementPageDtoCopyList.stream().map(ProcurementPageDtoCopy::getId).collect(Collectors.toList());
        if(CollectionUtils.isEmpty( collect)){
            return procurementPageDtoCopyIPage;
        }
        LambdaQueryWrapper<ProcurementRecordOut> procurementRecordLambdaQueryWrapper = new LambdaQueryWrapper<>();
        procurementRecordLambdaQueryWrapper.in(ProcurementRecordOut::getProcurementRecordStorageId, collect);
        procurementRecordLambdaQueryWrapper.eq(ProcurementRecordOut::getType,1);
        List<ProcurementRecordOut> procurementRecords = procurementRecordOutMapper.selectList(procurementRecordLambdaQueryWrapper);
        if(CollectionUtils.isEmpty( procurementRecords)){
            return procurementPageDtoCopyIPage;
        }
        // SQL已通过子查询计算了出库数量(totalInboundNum)和待出库数量(inboundNum0)，此处补充库存价值
        for (ProcurementPageDtoCopy dto : procurementPageDtoCopyList) {
            // 根据采购台账ID筛选对应的出库记录
            List<ProcurementRecordOut> collect1 = procurementRecords.stream()
                    .filter(ProcurementRecordOut -> ProcurementRecordOut.getProcurementRecordStorageId().equals(dto.getId()) && ProcurementRecordOut.getType().equals(1))
                    .collect(Collectors.toList());

            // 如果没有相关的出库记录，跳过该条数据
            if(CollectionUtils.isEmpty(collect1)){
            if (dto.getInboundNum0() == null) {
                dto.setInboundNum0(dto.getInboundNum());
                dto.setTotalInboundNum(BigDecimal.ZERO);
                continue;
            }

            // 计算已出库数量总和，并设置待出库数量
            BigDecimal totalInboundNum = collect1.stream()
                    .map(ProcurementRecordOut::getInboundNum)
                    .reduce(BigDecimal.ZERO, BigDecimal::add);
            // 出库数量 = 总数量 - 待出库数量
            dto.setTotalInboundNum(totalInboundNum);
            // 待出库数量 = 总数量 - 已出库数量
            dto.setInboundNum0(dto.getInboundNum().subtract(totalInboundNum));
            // 库存价值
            if(dto.getUnitPrice() != null){
            if (dto.getTotalInboundNum() == null) {
                dto.setTotalInboundNum(BigDecimal.ZERO);
            }
            if (dto.getUnitPrice() != null && dto.getInboundNum0() != null) {
                dto.setTotalPrice(dto.getInboundNum0().multiply(dto.getUnitPrice()));
            }
        }

 src/main/resources/mapper/approve/ApproveProcessMapper.xml

@@ -38,5 +38,9 @@
        <if test="req.approveType != null ">
            and approve_type = #{req.approveType}
        </if>
        ORDER BY
            CASE WHEN approve_status != 2 THEN 0 ELSE 1 END,
            CASE WHEN approve_status != 2 THEN create_time ELSE NULL END DESC,
            CASE WHEN approve_status = 2 THEN approve_over_time ELSE NULL END DESC
    </select>
</mapper>

 src/main/resources/mapper/procurementrecord/CustomStorageMapper.xml

@@ -7,9 +7,16 @@
               sum(t1.inbound_num) as inboundNum,
               sum(t1.inbound_num) as inboundNum0,
               sum(t1.tax_inclusive_total_price) as taxInclusiveTotalPrice,
               SUM(t1.inbound_num) - COALESCE(SUM(t2.inbound_num), 0) AS availableStock
               COALESCE(out_sum.total_out_num, 0) as totalInboundNum,
               sum(t1.inbound_num) - COALESCE(out_sum.total_out_num, 0) AS inboundNum0,
               sum(t1.inbound_num) - COALESCE(out_sum.total_out_num, 0) AS availableStock
               from custom_storage t1
               left join procurement_record_out t2 on t1.id = t2.procurement_record_storage_id and t2.type = 3
               left join (
                   select t2.procurement_record_storage_id, sum(t2.inbound_num) as total_out_num
                   from procurement_record_out t2
                   where t2.type = 3
                   group by t2.procurement_record_storage_id
               ) out_sum on out_sum.procurement_record_storage_id = t1.id
        <where>
            <if test="req.productCategory != null and req.productCategory != ''">
                and t1.product_category like  concat('%',#{req.productCategory},'%')

 src/main/resources/mapper/procurementrecord/ProcurementRecordMapper.xml

@@ -138,6 +138,8 @@
        sum(t1.inbound_num) as inboundNum,
        sum(t1.inbound_num) as inboundNum0,
        t1.inbound_num as totalInboundNum,
        COALESCE(out_sum.total_out_num, 0) as totalInboundNum,
        sum(t1.inbound_num) - COALESCE(out_sum.total_out_num, 0) as inboundNum0,
        t1.create_time,
        t1.update_time,
        t1.create_by,
@@ -145,6 +147,13 @@
        from  procurement_record_storage t1
        left join sales_ledger_product t2 on t2.id = t1.sales_ledger_product_id and t2.type = 2
        left join purchase_ledger t3 on t3.id = t2.sales_ledger_id
        left join (
            select t_in.sales_ledger_product_id, t_in.unit_price, sum(t_out.inbound_num) as total_out_num
            from procurement_record_storage t_in
            inner join procurement_record_out t_out on t_out.procurement_record_storage_id = t_in.id and t_out.type = 1
            where t_in.type = 1
            group by t_in.sales_ledger_product_id, t_in.unit_price
        ) out_sum on out_sum.sales_ledger_product_id = t1.sales_ledger_product_id and out_sum.unit_price = t1.unit_price
        <where>
            t1.type = 1
            <if test="req.supplierName != null and req.supplierName != ''">
@@ -295,6 +304,8 @@
        t1.unit_price,
        sum(t1.inbound_num) as inboundNum,
        sum(t1.inbound_num) as inboundNum0,
        COALESCE(out_sum.total_out_num, 0) as totalInboundNum,
        sum(t1.inbound_num) - COALESCE(out_sum.total_out_num, 0) as inboundNum0,
        t1.create_time,
        t1.update_time,
        t1.create_by,
@@ -303,6 +314,13 @@
        from  procurement_record_storage t1
        left join sales_ledger_product t2 on t2.id = t1.sales_ledger_product_id and t2.type = 1
        left join sales_ledger t3 on t3.id = t2.sales_ledger_id
        left join (
            select t_in.sales_ledger_product_id, t_in.unit_price, sum(t_out.inbound_num) as total_out_num
            from procurement_record_storage t_in
            inner join procurement_record_out t_out on t_out.procurement_record_storage_id = t_in.id and t_out.type = 2
            where t_in.type = 2
            group by t_in.sales_ledger_product_id, t_in.unit_price
        ) out_sum on out_sum.sales_ledger_product_id = t1.sales_ledger_product_id and out_sum.unit_price = t1.unit_price
        <where>
            t1.type = 2
            <if test="req.customerName != null and req.customerName != ''">