From e03206c08c9604decc4723c36fc25573681f2fee Mon Sep 17 00:00:00 2001
From: liding <756868258@qq.com>
Date: 星期四, 18 六月 2026 15:02:00 +0800
Subject: [PATCH] refactor(database): 优化库存管理相关数据库查询和业务逻辑
---
src/main/resources/mapper/procurementrecord/CustomStorageMapper.xml | 11 +++
src/main/resources/mapper/procurementrecord/ProcurementRecordMapper.xml | 18 ++++++
src/main/java/com/ruoyi/procurementrecord/service/impl/ProcurementRecordServiceImpl.java | 136 +++++++-------------------------------------
src/main/resources/mapper/approve/ApproveProcessMapper.xml | 4 +
4 files changed, 54 insertions(+), 115 deletions(-)
diff --git a/src/main/java/com/ruoyi/procurementrecord/service/impl/ProcurementRecordServiceImpl.java b/src/main/java/com/ruoyi/procurementrecord/service/impl/ProcurementRecordServiceImpl.java
index 19509a3..c94bbd1 100644
--- a/src/main/java/com/ruoyi/procurementrecord/service/impl/ProcurementRecordServiceImpl.java
+++ b/src/main/java/com/ruoyi/procurementrecord/service/impl/ProcurementRecordServiceImpl.java
@@ -598,56 +598,20 @@
@Override
public IPage<ProcurementPageDtoCopy> listPageCopyByProduction(Page page, ProcurementPageDto procurementDto) {
- IPage<ProcurementPageDtoCopy> procurementPageDtoCopyIPage = procurementRecordMapper.listPagePRS(page, procurementDto);
+ IPage<ProcurementPageDtoCopy> procurementPageDtoCopyIPage = procurementRecordMapper.listPageCopyByProduction(page, procurementDto);
List<ProcurementPageDtoCopy> procurementPageDtoCopyList = procurementPageDtoCopyIPage.getRecords();
- // 璁＄畻寰呭叆搴撴暟閲�
- // 鏌ヨ閲囪喘璁板綍宸插叆搴撴暟閲�
- List<Integer> collect = procurementPageDtoCopyList.stream().map(ProcurementPageDtoCopy::getId).collect(Collectors.toList());
- if(CollectionUtils.isEmpty( collect)){
- return procurementPageDtoCopyIPage;
- }
- // 1. 鏌ヨ閲囪喘璁板綍宸插叆搴撶殑鍑哄簱璁板綍锛堟寜storageId鍒嗙粍锛�
- LambdaQueryWrapper<ProcurementRecordOut> queryWrapper = new LambdaQueryWrapper<>();
- queryWrapper.in(ProcurementRecordOut::getProcurementRecordStorageId, collect);
- List<ProcurementRecordOut> recordOutList = procurementRecordOutMapper.selectList(queryWrapper);
-
- // 2. 鎸塖alesLedgerProductId鍒嗙粍锛岀粺璁℃瘡涓猧d瀵瑰簲鐨勫凡鍑哄簱鏁伴噺鎬诲拰-宸插嚭搴撴暟閲�
- Map<Long, BigDecimal> storageIdToTotalOutNumMap = recordOutList.stream()
- .collect(Collectors.groupingBy(
- ProcurementRecordOut::getSalesLedgerProductId,
- Collectors.reducing(
- BigDecimal.ZERO,
- ProcurementRecordOut::getInboundNum,
- (a, b) -> a.add(b == null ? BigDecimal.ZERO : b)
- )
- ));
- // 2. procurementPageDtoCopyList鎸塖alesLedgerProductId鍒嗙粍锛岀粺璁℃瘡涓猧d瀵瑰簲鐨勫凡鍑哄簱鏁伴噺鎬诲拰-鍏ュ簱搴撴暟閲�
- Map<Long, BigDecimal> storageIdToTotalintNumMap = procurementPageDtoCopyList.stream()
- .collect(Collectors.groupingBy(
- ProcurementPageDtoCopy::getSalesLedgerProductId,
- Collectors.reducing(
- BigDecimal.ZERO,
- ProcurementPageDtoCopy::getInboundNum,
- (a, b) -> a.add(b == null ? BigDecimal.ZERO : b)
- )
- ));
- // 3. 寰幆缁檇to璧嬪��
+ // SQL宸查�氳繃瀛愭煡璇㈣绠椾簡鍑哄簱鏁伴噺(totalInboundNum)鍜屽緟鍑哄簱鏁伴噺(inboundNum0)锛屾澶勮ˉ鍏呭簱瀛樹环鍊�
for (ProcurementPageDtoCopy dto : procurementPageDtoCopyList) {
- Integer storageId = dto.getId();
- Integer salesLedgerProductId = Integer.valueOf(Math.toIntExact(dto.getSalesLedgerProductId()));
- // 鑾峰彇褰撳墠salesLedgerProductId瀵瑰簲鐨勫凡鍑哄簱鎬绘暟锛堥粯璁�0锛�
- BigDecimal totalInboundNum = storageIdToTotalOutNumMap.getOrDefault(salesLedgerProductId, BigDecimal.ZERO);
- // 宸插嚭搴撴暟閲�
- dto.setTotalInboundNum(totalInboundNum);
- // 寰呭嚭搴撴暟閲� = 鎬绘暟閲� - 宸插嚭搴撴暟閲忥紙鎬绘暟閲忕┖鍊煎垯榛樿0锛�
-// BigDecimal totalNum = dto.getInboundNum() == null ? BigDecimal.ZERO : dto.getInboundNum();
- BigDecimal totalNum = storageIdToTotalintNumMap.getOrDefault(salesLedgerProductId, BigDecimal.ZERO);
- dto.setInboundNum(totalNum);
- dto.setInboundNum0(totalNum.subtract(totalInboundNum));
-
- // 搴撳瓨浠峰�� = 宸插嚭搴撴暟閲� * 鍗曚环锛堝崟浠风┖鍊煎垯榛樿0锛�
+ if (dto.getInboundNum0() == null) {
+ dto.setInboundNum0(dto.getInboundNum());
+ }
+ if (dto.getTotalInboundNum() == null) {
+ dto.setTotalInboundNum(BigDecimal.ZERO);
+ }
BigDecimal unitPrice = dto.getUnitPrice() == null ? BigDecimal.ZERO : dto.getUnitPrice();
- dto.setTotalPrice(totalInboundNum.multiply(unitPrice));
+ if (dto.getTotalInboundNum() != null) {
+ dto.setTotalPrice(dto.getTotalInboundNum().multiply(unitPrice));
+ }
}
return procurementPageDtoCopyIPage;
}
@@ -672,42 +636,15 @@
IPage<CustomStorage> pageList = customStorageMapper.listPageCopyByCustom(page, customStorage);
List<CustomStorage> procurementPageDtoCopyList = pageList.getRecords();
- // 璁＄畻寰呭叆搴撴暟閲�
- // 鏌ヨ閲囪喘璁板綍宸插叆搴撴暟閲�
- List<Integer> collect = procurementPageDtoCopyList.stream().map(CustomStorage::getId).collect(Collectors.toList());
- if(CollectionUtils.isEmpty( collect)){
- return pageList;
- }
- LambdaQueryWrapper<ProcurementRecordOut> procurementRecordLambdaQueryWrapper = new LambdaQueryWrapper<>();
- procurementRecordLambdaQueryWrapper.in(ProcurementRecordOut::getProcurementRecordStorageId, collect);
- procurementRecordLambdaQueryWrapper.eq(ProcurementRecordOut::getType, 3);
- List<ProcurementRecordOut> procurementRecords = procurementRecordOutMapper.selectList(procurementRecordLambdaQueryWrapper);
- if(CollectionUtils.isEmpty( procurementRecords)){
- return pageList;
- }
+ // SQL宸查�氳繃瀛愭煡璇㈣绠椾簡鍑哄簱鏁伴噺(totalInboundNum)鍜屽緟鍑哄簱鏁伴噺(inboundNum0)锛屾澶勮ˉ鍏呭簱瀛樹环鍊�
for (CustomStorage dto : procurementPageDtoCopyList) {
- // 鏍规嵁閲囪喘鍙拌处ID绛涢�夊搴旂殑鍑哄簱璁板綍
- List<ProcurementRecordOut> collect1 = procurementRecords.stream()
- .filter(ProcurementRecordOut -> ProcurementRecordOut.getProcurementRecordStorageId().equals(dto.getId()))
- .collect(Collectors.toList());
-
- // 濡傛灉娌℃湁鐩稿叧鐨勫嚭搴撹褰曪紝璺宠繃璇ユ潯鏁版嵁
- if(CollectionUtils.isEmpty(collect1)){
+ if (dto.getInboundNum0() == null) {
dto.setInboundNum0(dto.getInboundNum());
- dto.setTotalInboundNum(BigDecimal.ZERO);
- continue;
}
-
- // 璁＄畻宸插嚭搴撴暟閲忔�诲拰锛屽苟璁剧疆寰呭嚭搴撴暟閲�
- BigDecimal totalInboundNum = collect1.stream()
- .map(ProcurementRecordOut::getInboundNum)
- .reduce(BigDecimal.ZERO, BigDecimal::add);
- // 鍑哄簱鏁伴噺 = 鎬绘暟閲� - 寰呭嚭搴撴暟閲�
- dto.setTotalInboundNum(totalInboundNum);
- // 寰呭嚭搴撴暟閲� = 鎬绘暟閲� - 宸插嚭搴撴暟閲�
- dto.setInboundNum0(dto.getInboundNum().subtract(totalInboundNum));
- // 搴撳瓨浠峰��
- if(dto.getTaxInclusiveUnitPrice() != null){
+ if (dto.getTotalInboundNum() == null) {
+ dto.setTotalInboundNum(BigDecimal.ZERO);
+ }
+ if (dto.getTaxInclusiveUnitPrice() != null && dto.getInboundNum0() != null) {
dto.setTaxInclusiveTotalPrice(dto.getInboundNum0().multiply(dto.getTaxInclusiveUnitPrice()));
}
}
@@ -841,42 +778,15 @@
public IPage<ProcurementPageDtoCopy> listPageCopy(Page page, ProcurementPageDto procurementDto) {
IPage<ProcurementPageDtoCopy> procurementPageDtoCopyIPage = procurementRecordMapper.listPageCopy(page, procurementDto);
List<ProcurementPageDtoCopy> procurementPageDtoCopyList = procurementPageDtoCopyIPage.getRecords();
- // 璁＄畻寰呭叆搴撴暟閲�
- // 鏌ヨ閲囪喘璁板綍宸插叆搴撴暟閲�
- List<Integer> collect = procurementPageDtoCopyList.stream().map(ProcurementPageDtoCopy::getId).collect(Collectors.toList());
- if(CollectionUtils.isEmpty( collect)){
- return procurementPageDtoCopyIPage;
- }
- LambdaQueryWrapper<ProcurementRecordOut> procurementRecordLambdaQueryWrapper = new LambdaQueryWrapper<>();
- procurementRecordLambdaQueryWrapper.in(ProcurementRecordOut::getProcurementRecordStorageId, collect);
- procurementRecordLambdaQueryWrapper.eq(ProcurementRecordOut::getType,1);
- List<ProcurementRecordOut> procurementRecords = procurementRecordOutMapper.selectList(procurementRecordLambdaQueryWrapper);
- if(CollectionUtils.isEmpty( procurementRecords)){
- return procurementPageDtoCopyIPage;
- }
+ // SQL宸查�氳繃瀛愭煡璇㈣绠椾簡鍑哄簱鏁伴噺(totalInboundNum)鍜屽緟鍑哄簱鏁伴噺(inboundNum0)锛屾澶勮ˉ鍏呭簱瀛樹环鍊�
for (ProcurementPageDtoCopy dto : procurementPageDtoCopyList) {
- // 鏍规嵁閲囪喘鍙拌处ID绛涢�夊搴旂殑鍑哄簱璁板綍
- List<ProcurementRecordOut> collect1 = procurementRecords.stream()
- .filter(ProcurementRecordOut -> ProcurementRecordOut.getProcurementRecordStorageId().equals(dto.getId()) && ProcurementRecordOut.getType().equals(1))
- .collect(Collectors.toList());
-
- // 濡傛灉娌℃湁鐩稿叧鐨勫嚭搴撹褰曪紝璺宠繃璇ユ潯鏁版嵁
- if(CollectionUtils.isEmpty(collect1)){
+ if (dto.getInboundNum0() == null) {
dto.setInboundNum0(dto.getInboundNum());
- dto.setTotalInboundNum(BigDecimal.ZERO);
- continue;
}
-
- // 璁＄畻宸插嚭搴撴暟閲忔�诲拰锛屽苟璁剧疆寰呭嚭搴撴暟閲�
- BigDecimal totalInboundNum = collect1.stream()
- .map(ProcurementRecordOut::getInboundNum)
- .reduce(BigDecimal.ZERO, BigDecimal::add);
- // 鍑哄簱鏁伴噺 = 鎬绘暟閲� - 寰呭嚭搴撴暟閲�
- dto.setTotalInboundNum(totalInboundNum);
- // 寰呭嚭搴撴暟閲� = 鎬绘暟閲� - 宸插嚭搴撴暟閲�
- dto.setInboundNum0(dto.getInboundNum().subtract(totalInboundNum));
- // 搴撳瓨浠峰��
- if(dto.getUnitPrice() != null){
+ if (dto.getTotalInboundNum() == null) {
+ dto.setTotalInboundNum(BigDecimal.ZERO);
+ }
+ if (dto.getUnitPrice() != null && dto.getInboundNum0() != null) {
dto.setTotalPrice(dto.getInboundNum0().multiply(dto.getUnitPrice()));
}
}
diff --git a/src/main/resources/mapper/approve/ApproveProcessMapper.xml b/src/main/resources/mapper/approve/ApproveProcessMapper.xml
index 33b29a9..3f53874 100644
--- a/src/main/resources/mapper/approve/ApproveProcessMapper.xml
+++ b/src/main/resources/mapper/approve/ApproveProcessMapper.xml
@@ -38,5 +38,9 @@
<if test="req.approveType != null ">
and approve_type = #{req.approveType}
</if>
+ ORDER BY
+ CASE WHEN approve_status != 2 THEN 0 ELSE 1 END,
+ CASE WHEN approve_status != 2 THEN create_time ELSE NULL END DESC,
+ CASE WHEN approve_status = 2 THEN approve_over_time ELSE NULL END DESC
</select>
</mapper>
diff --git a/src/main/resources/mapper/procurementrecord/CustomStorageMapper.xml b/src/main/resources/mapper/procurementrecord/CustomStorageMapper.xml
index ba0e289..a01a9bf 100644
--- a/src/main/resources/mapper/procurementrecord/CustomStorageMapper.xml
+++ b/src/main/resources/mapper/procurementrecord/CustomStorageMapper.xml
@@ -7,9 +7,16 @@
sum(t1.inbound_num) as inboundNum,
sum(t1.inbound_num) as inboundNum0,
sum(t1.tax_inclusive_total_price) as taxInclusiveTotalPrice,
- SUM(t1.inbound_num) - COALESCE(SUM(t2.inbound_num), 0) AS availableStock
+ COALESCE(out_sum.total_out_num, 0) as totalInboundNum,
+ sum(t1.inbound_num) - COALESCE(out_sum.total_out_num, 0) AS inboundNum0,
+ sum(t1.inbound_num) - COALESCE(out_sum.total_out_num, 0) AS availableStock
from custom_storage t1
- left join procurement_record_out t2 on t1.id = t2.procurement_record_storage_id and t2.type = 3
+ left join (
+ select t2.procurement_record_storage_id, sum(t2.inbound_num) as total_out_num
+ from procurement_record_out t2
+ where t2.type = 3
+ group by t2.procurement_record_storage_id
+ ) out_sum on out_sum.procurement_record_storage_id = t1.id
<where>
<if test="req.productCategory != null and req.productCategory != ''">
and t1.product_category like concat('%',#{req.productCategory},'%')
diff --git a/src/main/resources/mapper/procurementrecord/ProcurementRecordMapper.xml b/src/main/resources/mapper/procurementrecord/ProcurementRecordMapper.xml
index b0166ca..97b1b19 100644
--- a/src/main/resources/mapper/procurementrecord/ProcurementRecordMapper.xml
+++ b/src/main/resources/mapper/procurementrecord/ProcurementRecordMapper.xml
@@ -138,6 +138,8 @@
sum(t1.inbound_num) as inboundNum,
sum(t1.inbound_num) as inboundNum0,
t1.inbound_num as totalInboundNum,
+ COALESCE(out_sum.total_out_num, 0) as totalInboundNum,
+ sum(t1.inbound_num) - COALESCE(out_sum.total_out_num, 0) as inboundNum0,
t1.create_time,
t1.update_time,
t1.create_by,
@@ -145,6 +147,13 @@
from procurement_record_storage t1
left join sales_ledger_product t2 on t2.id = t1.sales_ledger_product_id and t2.type = 2
left join purchase_ledger t3 on t3.id = t2.sales_ledger_id
+ left join (
+ select t_in.sales_ledger_product_id, t_in.unit_price, sum(t_out.inbound_num) as total_out_num
+ from procurement_record_storage t_in
+ inner join procurement_record_out t_out on t_out.procurement_record_storage_id = t_in.id and t_out.type = 1
+ where t_in.type = 1
+ group by t_in.sales_ledger_product_id, t_in.unit_price
+ ) out_sum on out_sum.sales_ledger_product_id = t1.sales_ledger_product_id and out_sum.unit_price = t1.unit_price
<where>
t1.type = 1
<if test="req.supplierName != null and req.supplierName != ''">
@@ -295,6 +304,8 @@
t1.unit_price,
sum(t1.inbound_num) as inboundNum,
sum(t1.inbound_num) as inboundNum0,
+ COALESCE(out_sum.total_out_num, 0) as totalInboundNum,
+ sum(t1.inbound_num) - COALESCE(out_sum.total_out_num, 0) as inboundNum0,
t1.create_time,
t1.update_time,
t1.create_by,
@@ -303,6 +314,13 @@
from procurement_record_storage t1
left join sales_ledger_product t2 on t2.id = t1.sales_ledger_product_id and t2.type = 1
left join sales_ledger t3 on t3.id = t2.sales_ledger_id
+ left join (
+ select t_in.sales_ledger_product_id, t_in.unit_price, sum(t_out.inbound_num) as total_out_num
+ from procurement_record_storage t_in
+ inner join procurement_record_out t_out on t_out.procurement_record_storage_id = t_in.id and t_out.type = 2
+ where t_in.type = 2
+ group by t_in.sales_ledger_product_id, t_in.unit_price
+ ) out_sum on out_sum.sales_ledger_product_id = t1.sales_ledger_product_id and out_sum.unit_price = t1.unit_price
<where>
t1.type = 2
<if test="req.customerName != null and req.customerName != ''">
--
Gitblit v1.9.3