feat(库存): 库存列表与库存报表支持按采购订单号过滤

gaoluyang

4 天以前 befe7f238921f08091ba22f8a6c067e9aced28ba

feat(库存): 库存列表与库存报表支持按采购订单号过滤

采购入库关联口径：record_type=7 时 record_id 为 purchase_ledger.id，
record_type=10 时 record_id 为 quality_inspect.id，经 purchase_ledger_id 关联采购台账。

Co-Authored-By: Claude Code <noreply@anthropic.com>

 src/main/java/com/ruoyi/stock/dto/StockInventoryDto.java

@@ -83,6 +83,10 @@
    @JsonFormat(pattern = "yyyy-MM-dd")
    private LocalDate endDate;

    //采购订单号（purchase_ledger.purchase_contract_number）
    @Schema(description = "采购订单号")
    private String purchaseOrderNo;

    private BigDecimal totalStockIn;
    private BigDecimal totalStockOut;
    private BigDecimal currentStock;

 src/main/resources/mapper/stock/StockInventoryMapper.xml

@@ -86,6 +86,27 @@
        </if>
    </select>

    <!-- 按采购订单号过滤：别名需暴露 product_model_id、batch_no 两列。
         采购入库来源：record_type=7 直接采购入库（record_id=purchase_ledger.id）；
         record_type=10 采购-质检合格入库（record_id=quality_inspect.id，经 purchase_ledger_id 关联）。 -->
    <sql id="purchaseOrderNoExists">
        exists (
            select 1
            from stock_in_record sir_po
            left join purchase_ledger pl_po_direct
                   on pl_po_direct.id = sir_po.record_id and sir_po.record_type = '7'
            left join quality_inspect qi_po
                   on qi_po.id = sir_po.record_id and sir_po.record_type = '10'
            left join purchase_ledger pl_po_inspect
                   on pl_po_inspect.id = qi_po.purchase_ledger_id
            where sir_po.product_model_id = ${alias}.product_model_id
              and (sir_po.batch_no = ${alias}.batch_no
                   or (sir_po.batch_no is null and ${alias}.batch_no is null))
              and (pl_po_direct.purchase_contract_number like concat('%', #{ew.purchaseOrderNo}, '%')
                   or pl_po_inspect.purchase_contract_number like concat('%', #{ew.purchaseOrderNo}, '%'))
        )
    </sql>

    <select id="pageListCombinedStockInventory" resultType="com.ruoyi.stock.dto.StockInventoryDto">
        WITH RECURSIVE product_tree AS (
        SELECT id
@@ -292,6 +313,12 @@
            <if test="ew.batchNo != null and ew.batchNo !=''">
                and combined.batch_no like concat('%',#{ew.batchNo},'%')
            </if>
            <if test="ew.purchaseOrderNo != null and ew.purchaseOrderNo !=''">
                and
                <include refid="purchaseOrderNoExists">
                    <property name="alias" value="combined"/>
                </include>
            </if>
        </where>
        group by
        product_model_id,
@@ -476,6 +503,23 @@
                <if test="ew.endDate != null">
                    and sir.create_time &lt; DATE_ADD(#{ew.endDate}, INTERVAL 1 DAY)
                </if>
                <if test="ew.purchaseOrderNo != null and ew.purchaseOrderNo !=''">
                    and (
                        exists (
                            select 1 from purchase_ledger pl_po
                            where pl_po.id = sir.record_id
                              and sir.record_type = '7'
                              and pl_po.purchase_contract_number like concat('%', #{ew.purchaseOrderNo}, '%')
                        )
                        or exists (
                            select 1 from quality_inspect qi_po
                            join purchase_ledger pl_po on pl_po.id = qi_po.purchase_ledger_id
                            where qi_po.id = sir.record_id
                              and sir.record_type = '10'
                              and pl_po.purchase_contract_number like concat('%', #{ew.purchaseOrderNo}, '%')
                        )
                    )
                </if>
            </where>
            union all
            select sor.id,
@@ -511,6 +555,12 @@
                <if test="ew.endDate != null">
                    and sor.create_time &lt; DATE_ADD(#{ew.endDate}, INTERVAL 1 DAY)
                </if>
                <if test="ew.purchaseOrderNo != null and ew.purchaseOrderNo !=''">
                    and
                    <include refid="purchaseOrderNoExists">
                        <property name="alias" value="sor"/>
                    </include>
                </if>
            </where>
        ) t
        left join (
@@ -540,6 +590,13 @@
        0 as amount,
        '' as record_type
        FROM stock_inventory
        <where>
            <if test="ew.purchaseOrderNo != null and ew.purchaseOrderNo !=''">
                <include refid="purchaseOrderNoExists">
                    <property name="alias" value="stock_inventory"/>
                </include>
            </if>
        </where>
        GROUP BY product_model_id

        UNION ALL
@@ -563,6 +620,12 @@
            </if>
            <if test="ew.endDate != null">
                and stock_in_record.create_time &lt; DATE_ADD(#{ew.endDate}, INTERVAL 1 DAY)
            </if>
            <if test="ew.purchaseOrderNo != null and ew.purchaseOrderNo !=''">
                and
                <include refid="purchaseOrderNoExists">
                    <property name="alias" value="stock_in_record"/>
                </include>
            </if>
        </where>
        GROUP BY product_model_id
@@ -589,6 +652,12 @@
            <if test="ew.endDate != null">
                and stock_out_record.create_time &lt; DATE_ADD(#{ew.endDate}, INTERVAL 1 DAY)
            </if>
            <if test="ew.purchaseOrderNo != null and ew.purchaseOrderNo !=''">
                and
                <include refid="purchaseOrderNoExists">
                    <property name="alias" value="stock_out_record"/>
                </include>
            </if>
        </where>
        GROUP BY product_model_id
        ) combined_data