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 < 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 < 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 < 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 < 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