From 9a30c5cc13afe9e8bea880cd39cb74470f682d10 Mon Sep 17 00:00:00 2001
From: gaoluyang <2820782392@qq.com>
Date: 星期三, 04 十一月 2026 11:13:58 +0800
Subject: [PATCH] fix(库存): 库存报表合并出入库明细并修正进出存统计
---
src/main/java/com/ruoyi/stock/dto/StockInventoryDto.java | 8 ++
src/main/resources/mapper/stock/StockInventoryMapper.xml | 130 ++++++++++++++++++++++++++++++++++++-------
src/main/java/com/ruoyi/stock/dto/StockInRecordDto.java | 14 ++++
3 files changed, 130 insertions(+), 22 deletions(-)
diff --git a/src/main/java/com/ruoyi/stock/dto/StockInRecordDto.java b/src/main/java/com/ruoyi/stock/dto/StockInRecordDto.java
index f4f03ba..74182f3 100644
--- a/src/main/java/com/ruoyi/stock/dto/StockInRecordDto.java
+++ b/src/main/java/com/ruoyi/stock/dto/StockInRecordDto.java
@@ -4,6 +4,8 @@
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;
+import java.math.BigDecimal;
+import java.time.LocalDate;
import java.util.List;
@@ -43,4 +45,16 @@
@Schema(description = "婧愬崟鍙�")
private String sourceOrderNo;
+ @Schema(description = "鍑哄叆搴撴柟鍚戯細in=鍏ュ簱锛宱ut=鍑哄簱")
+ private String direction;
+
+ @Schema(description = "鍑哄簱鎵规")
+ private String outboundBatches;
+
+ @Schema(description = "鍑哄簱鏁伴噺")
+ private BigDecimal stockOutNum;
+
+ @Schema(description = "鍑哄簱鏃ユ湡")
+ private LocalDate outDate;
+
}
diff --git a/src/main/java/com/ruoyi/stock/dto/StockInventoryDto.java b/src/main/java/com/ruoyi/stock/dto/StockInventoryDto.java
index 32a95aa..278ec8a 100644
--- a/src/main/java/com/ruoyi/stock/dto/StockInventoryDto.java
+++ b/src/main/java/com/ruoyi/stock/dto/StockInventoryDto.java
@@ -75,6 +75,14 @@
@JsonFormat(pattern = "yyyy-MM-dd")
private LocalDate endMonth;
+ //搴撳瓨杩涘嚭瀛樻姤琛ㄦ煡璇㈠瓧娈碉紙鎸夋棩鏈熻寖鍥达級
+ @DateTimeFormat(pattern = "yyyy-MM-dd")
+ @JsonFormat(pattern = "yyyy-MM-dd")
+ private LocalDate startDate;
+ @DateTimeFormat(pattern = "yyyy-MM-dd")
+ @JsonFormat(pattern = "yyyy-MM-dd")
+ private LocalDate endDate;
+
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 5acee04..ceaa8e7 100644
--- a/src/main/resources/mapper/stock/StockInventoryMapper.xml
+++ b/src/main/resources/mapper/stock/StockInventoryMapper.xml
@@ -422,38 +422,111 @@
group by batch_no, product_model_id, model, unit, product_name, product_id
</select>
<select id="stockInventoryPage" resultType="com.ruoyi.stock.dto.StockInRecordDto">
- select sir.*,si.qualitity as current_stock,
+ select t.id,
+ t.inbound_batches,
+ t.outbound_batches,
+ t.batch_no,
+ t.stock_in_num,
+ t.stock_out_num,
+ t.record_type,
+ t.record_id,
+ t.product_model_id,
+ t.remark,
+ t.direction,
+ t.out_date,
+ t.create_time,
+ t.create_user,
+ t.type,
+ si.current_stock,
pm.model,
pm.unit,
p.product_name,
su.nick_name as create_by
- from
- stock_in_record sir
+ from (
+ select sir.id,
+ sir.inbound_batches,
+ null as outbound_batches,
+ sir.batch_no,
+ sir.stock_in_num,
+ null as stock_out_num,
+ sir.record_type,
+ sir.record_id,
+ sir.product_model_id,
+ sir.remark,
+ 'in' as direction,
+ null as out_date,
+ sir.create_time,
+ sir.create_user,
+ sir.type
+ from stock_in_record sir
+ <where>
+ <if test="ew.reportDate != null">
+ and sir.create_time >= #{ew.reportDate}
+ and sir.create_time < DATE_ADD(#{ew.reportDate}, INTERVAL 1 DAY)
+ </if>
+ <if test="ew.startMonth != null">
+ and sir.create_time >= #{ew.startMonth}
+ </if>
+ <if test="ew.endMonth != null">
+ and sir.create_time < DATE_ADD(#{ew.endMonth}, INTERVAL 1 DAY)
+ </if>
+ <if test="ew.startDate != null">
+ and sir.create_time >= #{ew.startDate}
+ </if>
+ <if test="ew.endDate != null">
+ and sir.create_time < DATE_ADD(#{ew.endDate}, INTERVAL 1 DAY)
+ </if>
+ </where>
+ union all
+ select sor.id,
+ null as inbound_batches,
+ sor.outbound_batches,
+ sor.batch_no,
+ null as stock_in_num,
+ sor.stock_out_num,
+ sor.record_type,
+ sor.record_id,
+ sor.product_model_id,
+ sor.remark,
+ 'out' as direction,
+ sor.out_date,
+ sor.create_time,
+ sor.create_user,
+ sor.type
+ from stock_out_record sor
+ <where>
+ <if test="ew.reportDate != null">
+ and sor.create_time >= #{ew.reportDate}
+ and sor.create_time < DATE_ADD(#{ew.reportDate}, INTERVAL 1 DAY)
+ </if>
+ <if test="ew.startMonth != null">
+ and sor.create_time >= #{ew.startMonth}
+ </if>
+ <if test="ew.endMonth != null">
+ and sor.create_time < DATE_ADD(#{ew.endMonth}, INTERVAL 1 DAY)
+ </if>
+ <if test="ew.startDate != null">
+ and sor.create_time >= #{ew.startDate}
+ </if>
+ <if test="ew.endDate != null">
+ and sor.create_time < DATE_ADD(#{ew.endDate}, INTERVAL 1 DAY)
+ </if>
+ </where>
+ ) t
left join (
- select product_model_id, sum(qualitity) as qualitity
+ select product_model_id, sum(qualitity) as current_stock
from stock_inventory
group by product_model_id
- ) si on sir.product_model_id = si.product_model_id
- left join product_model pm on sir.product_model_id = pm.id
+ ) si on t.product_model_id = si.product_model_id
+ left join product_model pm on t.product_model_id = pm.id
left join product p on pm.product_id = p.id
- left join sys_user su on sir.create_user = su.user_id
- <where>
- <if test="ew.reportDate != null">
- and sir.create_time >= #{ew.reportDate}
- and sir.create_time < DATE_ADD(#{ew.reportDate}, INTERVAL 1 DAY)
- </if>
- <if test="ew.startMonth != null">
- and sir.create_time >= #{ew.startMonth}
- </if>
- <if test="ew.endMonth != null">
- and sir.create_time <= #{ew.endMonth}
- </if>
- </where>
- order by sir.id desc
+ left join sys_user su on t.create_user = su.user_id
+ order by t.create_time desc, t.id desc
</select>
<select id="stockInAndOutRecord" resultType="com.ruoyi.stock.dto.StockInventoryDto">
SELECT
+ combined_data.product_model_id,
pm.model,
pm.unit,
p.product_name,
@@ -483,7 +556,13 @@
and stock_in_record.create_time >= #{ew.startMonth}
</if>
<if test="ew.endMonth != null">
- and stock_in_record.create_time <= #{ew.endMonth}
+ and stock_in_record.create_time < DATE_ADD(#{ew.endMonth}, INTERVAL 1 DAY)
+ </if>
+ <if test="ew.startDate != null">
+ and stock_in_record.create_time >= #{ew.startDate}
+ </if>
+ <if test="ew.endDate != null">
+ and stock_in_record.create_time < DATE_ADD(#{ew.endDate}, INTERVAL 1 DAY)
</if>
</where>
GROUP BY product_model_id
@@ -502,7 +581,13 @@
and stock_out_record.create_time >= #{ew.startMonth}
</if>
<if test="ew.endMonth != null">
- and stock_out_record.create_time <= #{ew.endMonth}
+ and stock_out_record.create_time < DATE_ADD(#{ew.endMonth}, INTERVAL 1 DAY)
+ </if>
+ <if test="ew.startDate != null">
+ and stock_out_record.create_time >= #{ew.startDate}
+ </if>
+ <if test="ew.endDate != null">
+ and stock_out_record.create_time < DATE_ADD(#{ew.endDate}, INTERVAL 1 DAY)
</if>
</where>
GROUP BY product_model_id
@@ -518,6 +603,7 @@
</if>
</where>
GROUP BY
+ combined_data.product_model_id,
pm.model,
pm.unit,
p.product_name
--
Gitblit v1.9.3