refactor(home): 优化产品进出库分析功能的数据库查询逻辑

云

6 小时以前 d90a45a49ddb0ae512ef97b5e6fc981caab5e158

refactor(home): 优化产品进出库分析功能的数据库查询逻辑

- 修改了数据库连接配置，将开发环境数据库地址更改为本地MySQL
- 更新了产品进出库分析方法的实现逻辑，使用产品类型范围替代分类ID查询
- 重构了库存Mapper的数据查询参数，从rootCategoryId改为productScope
- 在SQL映射文件中添加了递归CTE查询，支持按产品类型树结构进行数据统计
- 优化了查询条件判断，兼容原材料多个根分类及成品多级分类的场景
- 更新了数据库密码配置，移除了特殊字符以提高安全性

 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()

 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);


 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:
        # 从数据源开关/默认关闭

 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 &gt;= #{startDate}
          AND sir.create_time &lt;= #{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 &gt;= #{startDate}
        AND sir.create_time &lt;= #{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 &gt;= #{startDate}
          AND sor.create_time &lt;= #{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 &gt;= #{startDate}
        AND sor.create_time &lt;= #{endDate}
        GROUP BY DATE(sor.create_time)
        ORDER BY DATE(sor.create_time) ASC
    </select>