From b70456bfd425b107f68cfd60c6e6b25e143cea7c Mon Sep 17 00:00:00 2001
From: liding <756868258@qq.com>
Date: 星期五, 20 三月 2026 09:20:41 +0800
Subject: [PATCH] fix:1.库存报表（耗材和仓库筛选优化） 2.耗材和仓储物流导出字段优化 3.入库导出优化
---
src/main/resources/mapper/consumables/ConsumablesInventoryMapper.xml | 42 ++++++++++----------
src/main/java/com/ruoyi/stock/dto/StockInventoryDto.java | 5 +-
src/main/resources/mapper/stock/StockInventoryMapper.xml | 45 +++++++++++++++++++++-
src/main/java/com/ruoyi/consumables/dto/ConsumablesInventoryDto.java | 6 +-
src/main/java/com/ruoyi/consumables/service/impl/ConsumablesUnInventoryServiceImpl.java | 8 ++--
src/main/java/com/ruoyi/consumables/controller/ConsumablesInventoryController.java | 4 +-
src/main/java/com/ruoyi/consumables/dto/ConsumablesInRecordDto.java | 2
7 files changed, 76 insertions(+), 36 deletions(-)
diff --git a/src/main/java/com/ruoyi/consumables/controller/ConsumablesInventoryController.java b/src/main/java/com/ruoyi/consumables/controller/ConsumablesInventoryController.java
index fbede68..561521a 100644
--- a/src/main/java/com/ruoyi/consumables/controller/ConsumablesInventoryController.java
+++ b/src/main/java/com/ruoyi/consumables/controller/ConsumablesInventoryController.java
@@ -82,13 +82,13 @@
consumablesInventoryService.exportConsumablesInventory(response, consumablesInventoryDto);
}
- @GetMapping("ConsumablesInventoryPage")
+ @GetMapping("/ConsumablesInventoryPage")
@ApiOperation("搴撳瓨鎶ヨ〃鏌ヨ")
public R ConsumablesInventoryPage(Page page, ConsumablesInventoryDto consumablesInventoryDto) {
return R.ok(consumablesInventoryService.consumablesInventoryPage(consumablesInventoryDto,page));
}
- @GetMapping("ConsumablesInAndOutRecord")
+ @GetMapping("/ConsumablesInAndOutRecord")
@ApiOperation("缁熻鍚勪釜浜у搧鐨勫叆搴撳拰鍑哄簱璁板綍")
public R ConsumablesInAndOutRecord(ConsumablesInventoryDto consumablesInventoryDto, Page page) {
return R.ok(consumablesInventoryService.consumablesInAndOutRecord(consumablesInventoryDto,page));
diff --git a/src/main/java/com/ruoyi/consumables/dto/ConsumablesInRecordDto.java b/src/main/java/com/ruoyi/consumables/dto/ConsumablesInRecordDto.java
index 06b146e..77b36c9 100644
--- a/src/main/java/com/ruoyi/consumables/dto/ConsumablesInRecordDto.java
+++ b/src/main/java/com/ruoyi/consumables/dto/ConsumablesInRecordDto.java
@@ -31,7 +31,7 @@
private String createBy;
//鐜板瓨閲�
- private String currentconsumables;
+ private String currentStock;
//鐜板噣閲�
private BigDecimal currentWeight;
diff --git a/src/main/java/com/ruoyi/consumables/dto/ConsumablesInventoryDto.java b/src/main/java/com/ruoyi/consumables/dto/ConsumablesInventoryDto.java
index 9625664..98565a1 100644
--- a/src/main/java/com/ruoyi/consumables/dto/ConsumablesInventoryDto.java
+++ b/src/main/java/com/ruoyi/consumables/dto/ConsumablesInventoryDto.java
@@ -36,9 +36,9 @@
@JsonFormat(pattern = "yyyy-MM-dd")
private LocalDate endMonth;
- private BigDecimal totalconsumablesIn;
- private BigDecimal totalconsumablesOut;
- private BigDecimal currentconsumables;
+ private BigDecimal totalStockIn;
+ private BigDecimal totalStockOut;
+ private BigDecimal currentStock;
private BigDecimal unLockedQuantity;
diff --git a/src/main/java/com/ruoyi/consumables/service/impl/ConsumablesUnInventoryServiceImpl.java b/src/main/java/com/ruoyi/consumables/service/impl/ConsumablesUnInventoryServiceImpl.java
index 48217d6..7c841fd 100644
--- a/src/main/java/com/ruoyi/consumables/service/impl/ConsumablesUnInventoryServiceImpl.java
+++ b/src/main/java/com/ruoyi/consumables/service/impl/ConsumablesUnInventoryServiceImpl.java
@@ -36,8 +36,8 @@
public class ConsumablesUnInventoryServiceImpl extends ServiceImpl<ConsumablesUnInventoryMapper, ConsumablesUnInventory> implements ConsumablesUnInventoryService {
private ConsumablesUnInventoryMapper consumablesUnInventoryMapper;
- private ConsumablesOutRecordService ConsumablesOutRecordService;
- private ConsumablesInRecordService ConsumablesInRecordService;
+ private ConsumablesOutRecordService consumablesOutRecordService;
+ private ConsumablesInRecordService consumablesInRecordService;
@Override
public IPage<ConsumablesUnInventoryDto> pageConsumablesUnInventory(Page page, ConsumablesUnInventoryDto consumablesUnInventoryDto) {
@@ -54,7 +54,7 @@
consumablesInRecordDto.setStockInNum(consumablesUnInventoryDto.getQualitity());
consumablesInRecordDto.setProductModelId(consumablesUnInventoryDto.getProductModelId());
consumablesInRecordDto.setType("1");
- ConsumablesInRecordService.add(consumablesInRecordDto);
+ consumablesInRecordService.add(consumablesInRecordDto);
//鍐嶈繘琛屾柊澧炲簱瀛樻暟閲忓簱瀛�
//鍏堟煡璇㈠簱瀛樿〃涓殑浜у搧鏄惁瀛樺湪锛屼笉瀛樺湪鏂板锛屽瓨鍦ㄦ洿鏂�
ConsumablesUnInventory oldConsumablesUnInventory = consumablesUnInventoryMapper.selectOne(new QueryWrapper<ConsumablesUnInventory>().lambda().eq(ConsumablesUnInventory::getProductModelId, consumablesUnInventoryDto.getProductModelId()));
@@ -82,7 +82,7 @@
consumablesOutRecordDto.setStockOutNum(consumablesUnInventoryDto.getQualitity());
consumablesOutRecordDto.setProductModelId(consumablesUnInventoryDto.getProductModelId());
consumablesOutRecordDto.setType("1");
- ConsumablesOutRecordService.add(consumablesOutRecordDto);
+ consumablesOutRecordService.add(consumablesOutRecordDto);
ConsumablesUnInventory oldConsumablesInventory = consumablesUnInventoryMapper.selectOne(new QueryWrapper<ConsumablesUnInventory>().lambda().eq(ConsumablesUnInventory::getProductModelId, consumablesUnInventoryDto.getProductModelId()));
if (ObjectUtils.isEmpty(oldConsumablesInventory)) {
throw new RuntimeException("浜у搧搴撳瓨涓嶅瓨鍦�");
diff --git a/src/main/java/com/ruoyi/stock/dto/StockInventoryDto.java b/src/main/java/com/ruoyi/stock/dto/StockInventoryDto.java
index dc48e5f..ac5aacc 100644
--- a/src/main/java/com/ruoyi/stock/dto/StockInventoryDto.java
+++ b/src/main/java/com/ruoyi/stock/dto/StockInventoryDto.java
@@ -1,6 +1,5 @@
package com.ruoyi.stock.dto;
-import com.baomidou.mybatisplus.annotation.TableField;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.ruoyi.stock.pojo.StockInventory;
import io.swagger.annotations.ApiModelProperty;
@@ -71,6 +70,8 @@
private String weighbridgeDocPath;
@ApiModelProperty("浜у搧绫诲瀷")
- @TableField(exist = false)
private Integer productType;
+
+ @ApiModelProperty("鐜板噣閲�(鍚�)")
+ private BigDecimal currentWeight;
}
diff --git a/src/main/resources/mapper/consumables/ConsumablesInventoryMapper.xml b/src/main/resources/mapper/consumables/ConsumablesInventoryMapper.xml
index 3f7f8d4..110a257 100644
--- a/src/main/resources/mapper/consumables/ConsumablesInventoryMapper.xml
+++ b/src/main/resources/mapper/consumables/ConsumablesInventoryMapper.xml
@@ -85,7 +85,7 @@
select
product_model_id,
sum(net_weight) as total_net_weight
- from Consumables_in_record
+ from consumables_in_record
group by product_model_id
) sir on si.product_model_id = sir.product_model_id
-- 鍑哄簱鍑�閲�
@@ -93,7 +93,7 @@
select
product_model_id,
sum(net_weight) as total_net_weight
- from Consumables_out_record
+ from consumables_out_record
group by product_model_id
) sor on si.product_model_id = sor.product_model_id
@@ -126,7 +126,7 @@
select
product_model_id,
sum(net_weight) as total_net_weight
- from Consumables_in_record
+ from consumables_in_record
group by product_model_id
) sir on si.product_model_id = sir.product_model_id
-- 鍑哄簱鍑�閲�
@@ -134,7 +134,7 @@
select
product_model_id,
sum(net_weight) as total_net_weight
- from Consumables_out_record
+ from consumables_out_record
group by product_model_id
) sor on si.product_model_id = sor.product_model_id
where 1 = 1
@@ -146,7 +146,7 @@
<select id="consumablesInventoryPage" resultType="com.ruoyi.consumables.dto.ConsumablesInRecordDto">
select
sir.*,
- si.qualitity as current_consumables,
+ si.qualitity as current_stock,
pm.model,
pm.unit,
p.product_name,
@@ -155,7 +155,7 @@
-- 褰撳墠鍑�閲� = 鍏ュ簱鍑�閲� - 鍑哄簱鍑�閲�
IFNULL(inWeight.total_in_weight,0) - IFNULL(outWeight.total_out_weight,0) as current_weight
- from Consumables_in_record sir
+ from consumables_in_record sir
left join consumables_inventory si
on sir.product_model_id = si.product_model_id
@@ -174,7 +174,7 @@
select
product_model_id,
sum(net_weight) as total_in_weight
- from Consumables_in_record
+ from consumables_in_record
group by product_model_id
) inWeight
on sir.product_model_id = inWeight.product_model_id
@@ -184,7 +184,7 @@
select
product_model_id,
sum(net_weight) as total_out_weight
- from Consumables_out_record
+ from consumables_out_record
group by product_model_id
) outWeight
on sir.product_model_id = outWeight.product_model_id
@@ -208,9 +208,9 @@
pm.model,
pm.unit,
p.product_name,
- MAX(current_inventory) as current_consumables,
- SUM(CASE WHEN record_type = 'in' THEN amount ELSE 0 END) as total_Consumables_in,
- SUM(CASE WHEN record_type = 'out' THEN amount ELSE 0 END) as total_Consumables_out
+ MAX(current_inventory) as current_stock,
+ SUM(CASE WHEN record_type = 'in' THEN amount ELSE 0 END) as total_stock_in,
+ SUM(CASE WHEN record_type = 'out' THEN amount ELSE 0 END) as total_stock_out
FROM (
SELECT
product_model_id,
@@ -225,16 +225,16 @@
SELECT
product_model_id,
0 as current_inventory,
- SUM(Consumables_in_num) as amount,
+ SUM(stock_in_num) as amount,
'in' as record_type
- FROM Consumables_in_record
+ FROM consumables_in_record
<where>
type = 0
<if test="ew.startMonth != null">
- and Consumables_in_record.create_time >= #{ew.startMonth}
+ and consumables_in_record.create_time >= #{ew.startMonth}
</if>
<if test="ew.endMonth != null">
- and Consumables_in_record.create_time <= #{ew.endMonth}
+ and consumables_in_record.create_time <= #{ew.endMonth}
</if>
</where>
GROUP BY product_model_id
@@ -244,16 +244,16 @@
SELECT
product_model_id,
0 as current_inventory,
- SUM(Consumables_out_num) as amount,
+ SUM(stock_out_num) as amount,
'out' as record_type
- FROM Consumables_out_record
+ FROM consumables_out_record
<where>
type = 0
<if test="ew.startMonth != null">
- and Consumables_out_record.create_time >= #{ew.startMonth}
+ and consumables_out_record.create_time >= #{ew.startMonth}
</if>
<if test="ew.endMonth != null">
- and Consumables_out_record.create_time <= #{ew.endMonth}
+ and consumables_out_record.create_time <= #{ew.endMonth}
</if>
</where>
GROUP BY product_model_id
@@ -300,7 +300,7 @@
<select id="selectDailyConsumablesInCounts" resultType="java.util.Map">
SELECT DATE(sir.create_time) AS date,
- SUM(sir.Consumables_in_num) AS count
+ SUM(sir.stock_in_num) AS count
FROM consumables_in_record sir
JOIN product_model pm ON sir.product_model_id = pm.id
JOIN product p ON pm.product_id = p.id
@@ -313,7 +313,7 @@
<select id="selectDailyConsumablesOutCounts" resultType="java.util.Map">
SELECT DATE(sor.create_time) AS date,
- SUM(sor.Consumables_out_num) AS count
+ SUM(sor.stock_out_num) AS count
FROM consumables_out_record sor
JOIN product_model pm ON sor.product_model_id = pm.id
JOIN product p ON pm.product_id = p.id
diff --git a/src/main/resources/mapper/stock/StockInventoryMapper.xml b/src/main/resources/mapper/stock/StockInventoryMapper.xml
index 7bc72a7..4427ee6 100644
--- a/src/main/resources/mapper/stock/StockInventoryMapper.xml
+++ b/src/main/resources/mapper/stock/StockInventoryMapper.xml
@@ -103,7 +103,7 @@
<select id="listStockInventoryExportData" resultType="com.ruoyi.stock.execl.StockInventoryExportData">
select si.qualitity,
-- 褰撳墠鍑�閲� = 鍏ュ簱鍑�閲� - 鍑哄簱鍑�閲�
- (COALESCE(sir.total_net_weight, 0) - COALESCE(sor.total_net_weight, 0)) AS net_weight,-
+ (COALESCE(sir.total_net_weight, 0) - COALESCE(sor.total_net_weight, 0)) AS net_weight,
pm.model,
pm.unit,
p.product_name,
@@ -197,7 +197,8 @@
p.product_name,
MAX(current_inventory) as current_stock,
SUM(CASE WHEN record_type = 'in' THEN amount ELSE 0 END) as total_stock_in,
- SUM(CASE WHEN record_type = 'out' THEN amount ELSE 0 END) as total_stock_out
+ SUM(CASE WHEN record_type = 'out' THEN amount ELSE 0 END) as total_stock_out,
+ (COALESCE(in_weight.total_net_weight, 0) - COALESCE(out_weight.total_net_weight, 0)) AS current_weight
FROM (
SELECT
product_model_id,
@@ -245,6 +246,42 @@
</where>
GROUP BY product_model_id
) combined_data
+
+ -- 宸﹁繛鎺ュ叆搴撳噣閲嶅悎璁�
+ LEFT JOIN (
+ SELECT
+ product_model_id,
+ SUM(net_weight) AS total_net_weight
+ FROM stock_in_record
+ <where>
+ type = 0
+ <if test="ew.startMonth != null">
+ and create_time >= #{ew.startMonth}
+ </if>
+ <if test="ew.endMonth != null">
+ and create_time <= #{ew.endMonth}
+ </if>
+ </where>
+ GROUP BY product_model_id
+ ) in_weight ON combined_data.product_model_id = in_weight.product_model_id
+
+ -- 宸﹁繛鎺ュ嚭搴撳噣閲嶅悎璁�
+ LEFT JOIN (
+ SELECT
+ product_model_id,
+ SUM(net_weight) AS total_net_weight
+ FROM stock_out_record
+ <where>
+ type = 0
+ <if test="ew.startMonth != null">
+ and create_time >= #{ew.startMonth}
+ </if>
+ <if test="ew.endMonth != null">
+ and create_time <= #{ew.endMonth}
+ </if>
+ </where>
+ GROUP BY product_model_id
+ ) out_weight ON combined_data.product_model_id = out_weight.product_model_id
LEFT JOIN product_model pm ON pm.id = combined_data.product_model_id
LEFT JOIN product p ON p.id = pm.product_id
<where>
@@ -258,7 +295,9 @@
GROUP BY
pm.model,
pm.unit,
- p.product_name
+ p.product_name,
+ in_weight.total_net_weight,
+ out_weight.total_net_weight
</select>
<select id="selectTotal" resultType="java.math.BigDecimal">
select ifnull(sum(qualitity), 0)
--
Gitblit v1.9.3