From b9fe740d4441b79b42bf7777046114bd13cd44cf Mon Sep 17 00:00:00 2001
From: liyong <18434998025@163.com>
Date: 星期二, 27 一月 2026 10:25:17 +0800
Subject: [PATCH] refactor(sales): 库存报表-日报-月报-进出项报表
---
src/main/java/com/ruoyi/stock/service/StockInventoryService.java | 7 +
src/main/java/com/ruoyi/stock/dto/StockInventoryDto.java | 21 +++++
src/main/java/com/ruoyi/stock/execl/StockInventoryExportData.java | 10 +-
src/main/resources/mapper/sales/SalesLedgerProductMapper.xml | 2
src/main/java/com/ruoyi/stock/mapper/StockInventoryMapper.java | 4 +
src/main/resources/mapper/stock/StockInventoryMapper.xml | 94 +++++++++++++++++++++++
src/main/java/com/ruoyi/stock/controller/StockInventoryController.java | 20 ++++-
src/main/java/com/ruoyi/stock/service/impl/StockInventoryServiceImpl.java | 60 +++++++++++---
src/main/java/com/ruoyi/stock/dto/StockInRecordDto.java | 3
9 files changed, 197 insertions(+), 24 deletions(-)
diff --git a/src/main/java/com/ruoyi/stock/controller/StockInventoryController.java b/src/main/java/com/ruoyi/stock/controller/StockInventoryController.java
index 7807de1..a1d1210 100644
--- a/src/main/java/com/ruoyi/stock/controller/StockInventoryController.java
+++ b/src/main/java/com/ruoyi/stock/controller/StockInventoryController.java
@@ -62,9 +62,9 @@
}
- @GetMapping("importStockInventory")
+ @PostMapping("importStockInventory")
@ApiOperation("瀵煎叆搴撳瓨")
- public R importStockInventory(MultipartFile file) {
+ public R importStockInventory(MultipartFile file) {
return stockInventoryService.importStockInventory(file);
}
@@ -78,7 +78,19 @@
@PostMapping("/exportStockInventory")
@ApiOperation("瀵煎嚭搴撳瓨")
- public void exportStockInventory(HttpServletResponse response,StockInventoryDto stockInventoryDto) {
- stockInventoryService.exportStockInventory(response,stockInventoryDto);
+ public void exportStockInventory(HttpServletResponse response, StockInventoryDto stockInventoryDto) {
+ stockInventoryService.exportStockInventory(response, stockInventoryDto);
+ }
+
+ @GetMapping("stockInventoryPage")
+ @ApiOperation("搴撳瓨鎶ヨ〃鏌ヨ")
+ public R stockInventoryPage(Page page, StockInventoryDto stockInventoryDto) {
+ return R.ok(stockInventoryService.stockInventoryPage(stockInventoryDto,page));
+ }
+
+ @GetMapping("stockInAndOutRecord")
+ @ApiOperation("缁熻鍚勪釜浜у搧鐨勫叆搴撳拰鍑哄簱璁板綍")
+ public R stockInAndOutRecord(StockInventoryDto stockInventoryDto,Page page) {
+ return R.ok(stockInventoryService.stockInAndOutRecord(stockInventoryDto,page));
}
}
diff --git a/src/main/java/com/ruoyi/stock/dto/StockInRecordDto.java b/src/main/java/com/ruoyi/stock/dto/StockInRecordDto.java
index 4d6540c..5c58f53 100644
--- a/src/main/java/com/ruoyi/stock/dto/StockInRecordDto.java
+++ b/src/main/java/com/ruoyi/stock/dto/StockInRecordDto.java
@@ -23,4 +23,7 @@
private String timeStr;
private String createBy;
+
+ //鐜板瓨閲�
+ private String currentStock;
}
diff --git a/src/main/java/com/ruoyi/stock/dto/StockInventoryDto.java b/src/main/java/com/ruoyi/stock/dto/StockInventoryDto.java
index 709facf..37b5efe 100644
--- a/src/main/java/com/ruoyi/stock/dto/StockInventoryDto.java
+++ b/src/main/java/com/ruoyi/stock/dto/StockInventoryDto.java
@@ -1,7 +1,12 @@
package com.ruoyi.stock.dto;
+import com.fasterxml.jackson.annotation.JsonFormat;
import com.ruoyi.stock.pojo.StockInventory;
import lombok.Data;
+import org.springframework.format.annotation.DateTimeFormat;
+
+import java.math.BigDecimal;
+import java.time.LocalDate;
@Data
public class StockInventoryDto extends StockInventory {
@@ -16,4 +21,20 @@
//鍏ュ簱绫诲瀷瀵瑰簲鐨刬d
private Long recordId;
+
+ @DateTimeFormat(pattern = "yyyy-MM-dd")
+ @JsonFormat(pattern = "yyyy-MM-dd")
+ private LocalDate reportDate;
+
+ //搴撳瓨鏈堟姤鏌ヨ瀛楁
+ @DateTimeFormat(pattern = "yyyy-MM-dd")
+ @JsonFormat(pattern = "yyyy-MM-dd")
+ private LocalDate startMonth;
+ @DateTimeFormat(pattern = "yyyy-MM-dd")
+ @JsonFormat(pattern = "yyyy-MM-dd")
+ private LocalDate endMonth;
+
+ private BigDecimal totalStockIn;
+ private BigDecimal totalStockOut;
+ private BigDecimal currentStock;
}
diff --git a/src/main/java/com/ruoyi/stock/execl/StockInventoryExportData.java b/src/main/java/com/ruoyi/stock/execl/StockInventoryExportData.java
index e95ccf2..3daa2a8 100644
--- a/src/main/java/com/ruoyi/stock/execl/StockInventoryExportData.java
+++ b/src/main/java/com/ruoyi/stock/execl/StockInventoryExportData.java
@@ -29,10 +29,10 @@
@Excel(name = "澶囨敞")
private String remark;
-
- @Excel(name = "鏈�鏂版洿鏂版椂闂�")
- @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
- @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
- private LocalDateTime updateTime;
+//
+// @Excel(name = "鏈�鏂版洿鏂版椂闂�")
+// @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
+// @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
+// private LocalDateTime updateTime;
}
diff --git a/src/main/java/com/ruoyi/stock/mapper/StockInventoryMapper.java b/src/main/java/com/ruoyi/stock/mapper/StockInventoryMapper.java
index fd14b37..16b0519 100644
--- a/src/main/java/com/ruoyi/stock/mapper/StockInventoryMapper.java
+++ b/src/main/java/com/ruoyi/stock/mapper/StockInventoryMapper.java
@@ -2,6 +2,7 @@
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
+import com.ruoyi.stock.dto.StockInRecordDto;
import com.ruoyi.stock.dto.StockInventoryDto;
import com.ruoyi.stock.execl.StockInventoryExportData;
import com.ruoyi.stock.pojo.StockInventory;
@@ -31,4 +32,7 @@
List<StockInventoryExportData> listStockInventoryExportData(@Param("ew") StockInventoryDto stockInventoryDto);
+ IPage<StockInRecordDto> stockInventoryPage(@Param("ew") StockInventoryDto stockInventoryDto, Page page);
+
+ IPage<StockInventoryDto> stockInAndOutRecord(@Param("ew") StockInventoryDto stockInventoryDto, Page page);
}
diff --git a/src/main/java/com/ruoyi/stock/service/StockInventoryService.java b/src/main/java/com/ruoyi/stock/service/StockInventoryService.java
index 37571f6..d4b5171 100644
--- a/src/main/java/com/ruoyi/stock/service/StockInventoryService.java
+++ b/src/main/java/com/ruoyi/stock/service/StockInventoryService.java
@@ -3,12 +3,14 @@
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ruoyi.framework.web.domain.R;
+import com.ruoyi.stock.dto.StockInRecordDto;
import com.ruoyi.stock.dto.StockInventoryDto;
import com.ruoyi.stock.pojo.StockInventory;
import com.baomidou.mybatisplus.extension.service.IService;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
+import java.util.List;
/**
* <p>
@@ -29,4 +31,9 @@
R importStockInventory(MultipartFile file);
void exportStockInventory(HttpServletResponse response, StockInventoryDto stockInventoryDto);
+
+ IPage<StockInRecordDto> stockInventoryPage(StockInventoryDto stockInventoryDto,Page page);
+
+ IPage<StockInventoryDto> stockInAndOutRecord(StockInventoryDto stockInventoryDto,Page page);
+
}
diff --git a/src/main/java/com/ruoyi/stock/service/impl/StockInventoryServiceImpl.java b/src/main/java/com/ruoyi/stock/service/impl/StockInventoryServiceImpl.java
index 35e2f13..e103f75 100644
--- a/src/main/java/com/ruoyi/stock/service/impl/StockInventoryServiceImpl.java
+++ b/src/main/java/com/ruoyi/stock/service/impl/StockInventoryServiceImpl.java
@@ -4,7 +4,6 @@
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
-import com.ruoyi.account.pojo.BorrowInfo;
import com.ruoyi.common.enums.StockQualifiedRecordTypeEnum;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.framework.web.domain.R;
@@ -26,6 +25,7 @@
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
+import java.util.ArrayList;
import java.util.List;
/**
@@ -101,32 +101,54 @@
@Override
public R importStockInventory(MultipartFile file) {
try {
- final StringBuffer[] errorMsg = {new StringBuffer()};
- //鏌ヨ鎵�鏈夌殑浜у搧
- List<SalesLedgerProduct> salesLedgerProducts =salesLedgerProductMapper.selectProduct();
+ // 鏌ヨ鎵�鏈夌殑浜у搧
+ List<SalesLedgerProduct> salesLedgerProducts = salesLedgerProductMapper.selectProduct();
ExcelUtil<StockInventoryExportData> util = new ExcelUtil<StockInventoryExportData>(StockInventoryExportData.class);
List<StockInventoryExportData> list = util.importExcel(file.getInputStream());
- list.stream().forEach(dto -> {
- salesLedgerProducts.stream().forEach(item->{
- if (item.getProductCategory().equals(dto.getProductName())&&item.getSpecificationModel().equals(dto.getModel())) {
- //鏇存柊搴撳瓨
+
+ // 璁板綍鏈壘鍒板尮閰嶉」鐨勬暟鎹�
+ List<String> unmatchedRecords = new ArrayList<>();
+
+ list.forEach(dto -> {
+ boolean matched = false;
+ for (SalesLedgerProduct item : salesLedgerProducts) {
+ if (item.getProductCategory().equals(dto.getProductName()) &&
+ item.getSpecificationModel().equals(dto.getModel())) {
StockInventoryDto stockInventoryDto = new StockInventoryDto();
stockInventoryDto.setRecordId(0L);
stockInventoryDto.setRecordType(StockQualifiedRecordTypeEnum.CUSTOMIZATION_STOCK_IN.getCode());
stockInventoryDto.setQualitity(dto.getQualitity());
+ stockInventoryDto.setRemark(dto.getRemark());
stockInventoryDto.setProductModelId(item.getProductModelId());
- this.addstockInventory(stockInventoryDto); }else {
- errorMsg[0] = errorMsg[0].append("浜у搧鍚嶇О锛�"+dto.getProductName()+"瑙勬牸锛�"+dto.getModel()+"涓嶅瓨鍦�").append("\n");
+ this.addstockInventory(stockInventoryDto);
+ matched = true;
+ break; // 鎵惧埌鍖归厤椤瑰悗璺冲嚭寰幆
}
- });
+ }
+ if (!matched) {
+ // 璁板綍鏈尮閰嶇殑鏁版嵁
+ String unmatchedInfo = String.format("浜у搧鍚嶇О锛�%s锛岃鏍煎瀷鍙凤細%s",
+ dto.getProductName(), dto.getModel());
+ unmatchedRecords.add(unmatchedInfo);
+ }
});
- return R.ok(errorMsg[0]);
- }catch (Exception e){
+ // 鏋勫缓杩斿洖淇℃伅
+ StringBuilder message = new StringBuilder();
+ if (!unmatchedRecords.isEmpty()) {
+ message.append("浠ヤ笅浜у搧鏈壘鍒板尮閰嶉」锛歕n");
+ for (String record : unmatchedRecords) {
+ message.append(record).append("\n");
+ }
+ throw new RuntimeException(message.toString());
+ }
+ } catch (Exception e) {
e.printStackTrace();
+ return R.fail("瀵煎叆澶辫触锛�" + e.getMessage());
}
- return R.fail();
+ return R.ok("瀵煎叆鎴愬姛");
}
+
@Override
public void exportStockInventory(HttpServletResponse response, StockInventoryDto stockInventoryDto) {
@@ -135,4 +157,14 @@
ExcelUtil<StockInventoryExportData> util = new ExcelUtil<>(StockInventoryExportData.class);
util.exportExcel(response,list, "搴撳瓨淇℃伅");
}
+
+ @Override
+ public IPage<StockInRecordDto> stockInventoryPage(StockInventoryDto stockInventoryDto, Page page) {
+ return stockInventoryMapper.stockInventoryPage(stockInventoryDto,page);
+ }
+
+ @Override
+ public IPage<StockInventoryDto> stockInAndOutRecord(StockInventoryDto stockInventoryDto, Page page) {
+ return stockInventoryMapper.stockInAndOutRecord(stockInventoryDto,page);
+ }
}
diff --git a/src/main/resources/mapper/sales/SalesLedgerProductMapper.xml b/src/main/resources/mapper/sales/SalesLedgerProductMapper.xml
index 71b776c..018f08e 100644
--- a/src/main/resources/mapper/sales/SalesLedgerProductMapper.xml
+++ b/src/main/resources/mapper/sales/SalesLedgerProductMapper.xml
@@ -158,7 +158,7 @@
select
p.product_name as product_category,
pm.model as specification_model,
- pm.id
+ pm.id as product_model_id
from product_model pm
left join product p on pm.product_id = p.id
</select>
diff --git a/src/main/resources/mapper/stock/StockInventoryMapper.xml b/src/main/resources/mapper/stock/StockInventoryMapper.xml
index 1f729f2..b3f54ee 100644
--- a/src/main/resources/mapper/stock/StockInventoryMapper.xml
+++ b/src/main/resources/mapper/stock/StockInventoryMapper.xml
@@ -73,5 +73,99 @@
and p.product_name like concat('%',#{ew.productName},'%')
</if>
</select>
+ <select id="stockInventoryPage" resultType="com.ruoyi.stock.dto.StockInRecordDto">
+ select sir.*,si.qualitity,
+ pm.model,
+ pm.unit,
+ p.product_name
+ from
+ stock_in_record sir
+ left join stock_inventory si on sir.product_model_id = si.product_model_id
+ left join product_model pm on sir.product_model_id = pm.id
+ left join product p on pm.product_id = p.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>
+ </select>
+
+ <select id="stockInAndOutRecord" resultType="com.ruoyi.stock.dto.StockInventoryDto">
+ SELECT
+ pm.model,
+ pm.unit,
+ 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
+ FROM (
+ SELECT
+ product_model_id,
+ SUM(qualitity) as current_inventory,
+ 0 as amount,
+ '' as record_type
+ FROM stock_inventory
+ GROUP BY product_model_id
+
+ UNION ALL
+
+ SELECT
+ product_model_id,
+ 0 as current_inventory,
+ SUM(stock_in_num) as amount,
+ 'in' as record_type
+ FROM stock_in_record
+ <where>
+ type = 0
+ <if test="ew.startMonth != null">
+ and stock_in_record.create_time >= #{ew.startMonth}
+ </if>
+ <if test="ew.endMonth != null">
+ and stock_in_record.create_time <= #{ew.endMonth}
+ </if>
+ </where>
+ GROUP BY product_model_id
+
+ UNION ALL
+
+ SELECT
+ product_model_id,
+ 0 as current_inventory,
+ SUM(stock_out_num) as amount,
+ 'out' as record_type
+ FROM stock_out_record
+ <where>
+ type = 0
+ <if test="ew.startMonth != null">
+ and stock_out_record.create_time >= #{ew.startMonth}
+ </if>
+ <if test="ew.endMonth != null">
+ and stock_out_record.create_time <= #{ew.endMonth}
+ </if>
+ </where>
+ GROUP BY product_model_id
+ ) combined_data
+ LEFT JOIN product_model pm ON pm.id = combined_data.product_model_id
+ LEFT JOIN product p ON p.id = pm.product_id
+ <where>
+ <if test="ew.productName != null and ew.productName !=''">
+ and p.product_name like concat('%',#{ew.productName},'%')
+ </if>
+ <if test="ew.model != null and ew.model !=''">
+ and pm.model like concat('%',#{ew.model},'%')
+ </if>
+ </where>
+ GROUP BY
+ pm.model,
+ pm.unit,
+ p.product_name
+ </select>
</mapper>
--
Gitblit v1.9.3