From d90a45a49ddb0ae512ef97b5e6fc981caab5e158 Mon Sep 17 00:00:00 2001
From: 云 <2163098428@qq.com>
Date: 星期二, 15 九月 2026 15:49:26 +0800
Subject: [PATCH] refactor(home): 优化产品进出库分析功能的数据库查询逻辑
---
src/main/java/com/ruoyi/stock/mapper/StockInventoryMapper.java | 4 +-
src/main/resources/mapper/stock/StockInventoryMapper.xml | 50 ++++++++++++++++++++++---
src/main/resources/application-dev.yml | 4 +-
src/main/java/com/ruoyi/home/service/impl/HomeServiceImpl.java | 26 ++++--------
4 files changed, 57 insertions(+), 27 deletions(-)
diff --git a/src/main/java/com/ruoyi/home/service/impl/HomeServiceImpl.java b/src/main/java/com/ruoyi/home/service/impl/HomeServiceImpl.java
index 2d3e608..93bb807 100644
--- a/src/main/java/com/ruoyi/home/service/impl/HomeServiceImpl.java
+++ b/src/main/java/com/ruoyi/home/service/impl/HomeServiceImpl.java
@@ -1088,26 +1088,18 @@
@Override
public List<Map<String, Object>> productInOutAnalysis(Integer type) {
- String targetName;
- if (type == 1) {
- targetName = "鍘熸潗鏂�";
+ // 1=鍘熸潗鏂� 2=鎴愬搧 3=鍗婃垚鍝侊紝涓庡墠绔� ProductTypeSwitch 鍙栧�间竴鑷达紱
+ // 鑼冨洿鍒ゅ畾鏀逛负鎸夊垎绫绘爲鏍硅妭鐐圭殑 product_type锛屽吋瀹瑰師鏉愭枡澶氫釜鏍瑰垎绫诲強鎴愬搧澶氱骇鍒嗙被
+ String productScope;
+ if (type == null || type == 1) {
+ productScope = "鍘熸潗鏂�";
} else if (type == 2) {
- targetName = "鎴愬搧";
+ productScope = "鎴愬搧";
} else if (type == 3) {
- targetName = "鍗婃垚鍝�";
+ productScope = "鍗婃垚鍝�";
} else {
return new ArrayList<>();
}
-
- LambdaQueryWrapper<Product> queryWrapper = new LambdaQueryWrapper<>();
- queryWrapper.eq(Product::getProductName, targetName).isNull(Product::getParentId);
- Product rootCategory = productMapper.selectOne(queryWrapper);
-
- if (rootCategory == null) {
- log.warn("鏈壘鍒板悕绉颁负 {} 鐨勯《绾т骇鍝佸垎绫�", targetName);
- return new ArrayList<>();
- }
- Long rootCategoryId = rootCategory.getId();
LocalDate now = LocalDate.now();
DateTimeFormatter displayDtf = DateTimeFormatter.ofPattern("M/d");
@@ -1115,10 +1107,10 @@
String startDate = now.minusDays(6).toString();
String endDate = now.toString();
- List<Map<String, Object>> inCounts = stockInventoryMapper.selectDailyStockInCounts(rootCategoryId, startDate,
+ List<Map<String, Object>> inCounts = stockInventoryMapper.selectDailyStockInCounts(productScope, startDate,
endDate + " 23:59:59");
- List<Map<String, Object>> outCounts = stockInventoryMapper.selectDailyStockOutCounts(rootCategoryId, startDate,
+ List<Map<String, Object>> outCounts = stockInventoryMapper.selectDailyStockOutCounts(productScope, startDate,
endDate + " 23:59:59");
Map<LocalDate, BigDecimal> inMap = inCounts.stream()
diff --git a/src/main/java/com/ruoyi/stock/mapper/StockInventoryMapper.java b/src/main/java/com/ruoyi/stock/mapper/StockInventoryMapper.java
index 07fd0db..215dab9 100644
--- a/src/main/java/com/ruoyi/stock/mapper/StockInventoryMapper.java
+++ b/src/main/java/com/ruoyi/stock/mapper/StockInventoryMapper.java
@@ -45,9 +45,9 @@
int selectStorageProductCountByDate(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);
- List<Map<String, Object>> selectDailyStockInCounts(@Param("rootCategoryId") Long rootCategoryId, @Param("startDate") String startDate, @Param("endDate") String endDate);
+ List<Map<String, Object>> selectDailyStockInCounts(@Param("productScope") String productScope, @Param("startDate") String startDate, @Param("endDate") String endDate);
- List<Map<String, Object>> selectDailyStockOutCounts(@Param("rootCategoryId") Long rootCategoryId, @Param("startDate") String startDate, @Param("endDate") String endDate);
+ List<Map<String, Object>> selectDailyStockOutCounts(@Param("productScope") String productScope, @Param("startDate") String startDate, @Param("endDate") String endDate);
BigDecimal selectTotalByDate(@Param("now") LocalDate now);
diff --git a/src/main/resources/application-dev.yml b/src/main/resources/application-dev.yml
index 9eff9ac..213f7a7 100644
--- a/src/main/resources/application-dev.yml
+++ b/src/main/resources/application-dev.yml
@@ -74,9 +74,9 @@
druid:
# 涓诲簱鏁版嵁婧�
master:
- url: jdbc:mysql://1.15.17.182:9999/product-inventory-management-new?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
+ url: jdbc:mysql://localhost:3306/product-inventory-management-jcck?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
username: root
- password: xd@123456..
+ password: 123456
# 浠庡簱鏁版嵁婧�
slave:
# 浠庢暟鎹簮寮�鍏�/榛樿鍏抽棴
diff --git a/src/main/resources/mapper/stock/StockInventoryMapper.xml b/src/main/resources/mapper/stock/StockInventoryMapper.xml
index 517a17d..dc519a6 100644
--- a/src/main/resources/mapper/stock/StockInventoryMapper.xml
+++ b/src/main/resources/mapper/stock/StockInventoryMapper.xml
@@ -265,27 +265,65 @@
</select>
<select id="selectDailyStockInCounts" resultType="java.util.Map">
+ WITH RECURSIVE get_root AS (SELECT id,
+ parent_id,
+ id AS root_id
+ FROM product
+ WHERE parent_id IS NULL
+ UNION ALL
+ SELECT p.id,
+ p.parent_id,
+ gr.root_id
+ FROM product p
+ INNER JOIN get_root gr ON p.parent_id = gr.id)
SELECT DATE(sir.create_time) AS date,
SUM(sir.stock_in_num) AS count
FROM stock_in_record sir
JOIN product_model pm ON sir.product_model_id = pm.id
JOIN product p ON pm.product_id = p.id
- WHERE (p.parent_id = #{rootCategoryId} OR p.id = #{rootCategoryId})
- AND sir.create_time >= #{startDate}
- AND sir.create_time <= #{endDate}
+ JOIN get_root gr ON gr.id = p.id
+ JOIN product rp ON rp.id = gr.root_id
+ WHERE
+ <choose>
+ <when test="productScope == '鎴愬搧'">rp.product_type = '鎴愬搧'</when>
+ <when test="productScope == '鍗婃垚鍝�'">rp.product_type = '鍗婃垚鍝�'</when>
+ <otherwise>(rp.product_type IS NULL
+ OR (rp.product_type != '鎴愬搧' AND rp.product_type != '鍗婃垚鍝�'))</otherwise>
+ </choose>
+ AND sir.create_time >= #{startDate}
+ AND sir.create_time <= #{endDate}
GROUP BY DATE(sir.create_time)
ORDER BY DATE(sir.create_time) ASC
</select>
<select id="selectDailyStockOutCounts" resultType="java.util.Map">
+ WITH RECURSIVE get_root AS (SELECT id,
+ parent_id,
+ id AS root_id
+ FROM product
+ WHERE parent_id IS NULL
+ UNION ALL
+ SELECT p.id,
+ p.parent_id,
+ gr.root_id
+ FROM product p
+ INNER JOIN get_root gr ON p.parent_id = gr.id)
SELECT DATE(sor.create_time) AS date,
SUM(sor.stock_out_num) AS count
FROM stock_out_record sor
JOIN product_model pm ON sor.product_model_id = pm.id
JOIN product p ON pm.product_id = p.id
- WHERE (p.parent_id = #{rootCategoryId} OR p.id = #{rootCategoryId})
- AND sor.create_time >= #{startDate}
- AND sor.create_time <= #{endDate}
+ JOIN get_root gr ON gr.id = p.id
+ JOIN product rp ON rp.id = gr.root_id
+ WHERE
+ <choose>
+ <when test="productScope == '鎴愬搧'">rp.product_type = '鎴愬搧'</when>
+ <when test="productScope == '鍗婃垚鍝�'">rp.product_type = '鍗婃垚鍝�'</when>
+ <otherwise>(rp.product_type IS NULL
+ OR (rp.product_type != '鎴愬搧' AND rp.product_type != '鍗婃垚鍝�'))</otherwise>
+ </choose>
+ AND sor.create_time >= #{startDate}
+ AND sor.create_time <= #{endDate}
GROUP BY DATE(sor.create_time)
ORDER BY DATE(sor.create_time) ASC
</select>
--
Gitblit v1.9.3