From befe7f238921f08091ba22f8a6c067e9aced28ba Mon Sep 17 00:00:00 2001
From: gaoluyang <2820782392@qq.com>
Date: 星期三, 04 十一月 2026 11:25:41 +0800
Subject: [PATCH] feat(库存): 库存列表与库存报表支持按采购订单号过滤

---
 src/main/java/com/ruoyi/stock/dto/StockInventoryDto.java |    4 ++
 src/main/resources/mapper/stock/StockInventoryMapper.xml |   69 ++++++++++++++++++++++++++++++++++
 2 files changed, 73 insertions(+), 0 deletions(-)

diff --git a/src/main/java/com/ruoyi/stock/dto/StockInventoryDto.java b/src/main/java/com/ruoyi/stock/dto/StockInventoryDto.java
index 278ec8a..4fbb2cf 100644
--- a/src/main/java/com/ruoyi/stock/dto/StockInventoryDto.java
+++ b/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;
diff --git a/src/main/resources/mapper/stock/StockInventoryMapper.xml b/src/main/resources/mapper/stock/StockInventoryMapper.xml
index ceaa8e7..5f21695 100644
--- a/src/main/resources/mapper/stock/StockInventoryMapper.xml
+++ b/src/main/resources/mapper/stock/StockInventoryMapper.xml
@@ -86,6 +86,27 @@
         </if>
     </select>
 
+    <!-- 鎸夐噰璐鍗曞彿杩囨护锛氬埆鍚嶉渶鏆撮湶 product_model_id銆乥atch_no 涓ゅ垪銆�
+         閲囪喘鍏ュ簱鏉ユ簮锛歳ecord_type=7 鐩存帴閲囪喘鍏ュ簱锛坮ecord_id=purchase_ledger.id锛夛紱
+         record_type=10 閲囪喘-璐ㄦ鍚堟牸鍏ュ簱锛坮ecord_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

--
Gitblit v1.9.3