Merge remote-tracking branch 'origin/dev_New' into dev_New

zss

12 小时以前 599b5ff692722226d1e30fbdffc594b826bfa815

Merge remote-tracking branch 'origin/dev_New' into dev_New

 src/main/java/com/ruoyi/quality/controller/QualityReportController.java

@@ -74,8 +74,8 @@
     */
    @ApiOperation("获取热点检测指标统计")
    @GetMapping("/getTopParameters")
    public AjaxResult getTopParameters(@RequestParam("inspectType") Integer inspectType) {
        return AjaxResult.success(qualityReportService.getTopParameters(inspectType));
    public AjaxResult getTopParameters(@RequestParam("modelType") Integer modelType) {
        return AjaxResult.success(qualityReportService.getTopParameters(modelType));
    }

}

 src/main/java/com/ruoyi/quality/dto/QualityInspectStatDto.java

@@ -13,8 +13,8 @@

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "类别(0:原材料检验;1:过程检验;2:出厂检验)")
    private Integer inspectType;
    @ApiModelProperty(value = "类别(0:原材料;1:半成品;2:成品)")
    private Integer modelType;

    @ApiModelProperty(value = "总数量")
    private BigDecimal totalCount;

 src/main/java/com/ruoyi/quality/dto/QualityMonthlyDetailDto.java

@@ -18,12 +18,12 @@
    @ApiModelProperty(value = "月份")
    private String month;

    @ApiModelProperty(value = "原材料检验完成数")
    @ApiModelProperty(value = "原材料")
    private BigDecimal rawMaterialCount;

    @ApiModelProperty(value = "过程检验完成数")
    @ApiModelProperty(value = "半成品")
    private BigDecimal processCount;

    @ApiModelProperty(value = "出厂检验完成数")
    @ApiModelProperty(value = "成品")
    private BigDecimal outgoingCount;
}

 src/main/java/com/ruoyi/quality/dto/QualityMonthlyPassRateDto.java

@@ -18,8 +18,8 @@
    @ApiModelProperty(value = "月份(一月, 二月...)")
    private String month;

    @ApiModelProperty(value = "类别(0:原材料检验;1:过程检验;2:出厂检验)")
    private Integer inspectType;
    @ApiModelProperty(value = "类别(0:原材料;1:半成品;2:成品)")
    private Integer modelType;

    @ApiModelProperty(value = "总数量")
    private BigDecimal totalCount;

 src/main/java/com/ruoyi/quality/dto/QualityMonthlyPassRateWrapperDto.java

@@ -17,12 +17,12 @@
    @ApiModelProperty(value = "月份")
    private String month;

    @ApiModelProperty(value = "原材料检验数据")
    @ApiModelProperty(value = "原材料")
    private QualityPassRateDto rawMaterial;

    @ApiModelProperty(value = "过程检验数据")
    @ApiModelProperty(value = "半成品")
    private QualityPassRateDto process;

    @ApiModelProperty(value = "出厂检验数据")
    @ApiModelProperty(value = "成品")
    private QualityPassRateDto outgoing;
}

 src/main/java/com/ruoyi/quality/dto/QualityPassRateDto.java

@@ -16,8 +16,8 @@

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "类别(0:原材料检验;1:过程检验;2:出厂检验)")
    private Integer inspectType;
    @ApiModelProperty(value = "类别(0:原材料;1:半成品;2:成品)")
    private Integer modelType;

    @ApiModelProperty(value = "总数量")
    private BigDecimal totalCount;

 src/main/java/com/ruoyi/quality/mapper/QualityInspectMapper.java

@@ -3,15 +3,10 @@
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ruoyi.quality.dto.*;
import com.ruoyi.quality.pojo.QualityInspect;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import com.ruoyi.quality.dto.QualityInspectStatDto;
import com.ruoyi.quality.dto.QualityPassRateDto;
import com.ruoyi.quality.dto.QualityMonthlyPassRateDto;
import com.ruoyi.quality.dto.QualityMonthlyDetailDto;
import com.ruoyi.quality.dto.QualityParameterStatDto;

import java.util.List;

@@ -56,5 +51,5 @@
    /**
     * 获取热点检测指标 Top 4 + 其他
     */
    List<QualityParameterStatDto> getTopParameters(@Param("inspectType") Integer inspectType);
    List<QualityParameterStatDto> getTopParameters(@Param("modelType") Integer modelType);
}

 src/main/java/com/ruoyi/quality/service/impl/QualityReportServiceImpl.java

@@ -1,13 +1,10 @@
package com.ruoyi.quality.service.impl;

import com.ruoyi.basic.service.IProductModelService;
import com.ruoyi.basic.service.IProductService;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.quality.dto.QualityPassRateDto;
import com.ruoyi.quality.dto.QualityInspectStatDto;
import com.ruoyi.quality.dto.QualityMonthlyPassRateDto;
import com.ruoyi.quality.dto.QualityMonthlyDetailDto;
import com.ruoyi.quality.dto.QualityParameterStatDto;
import com.ruoyi.quality.dto.QualityMonthlyPassRateWrapperDto;
import com.ruoyi.quality.dto.QualityTopParameterDto;
import com.ruoyi.production.service.ProductOrderService;
import com.ruoyi.quality.dto.*;
import com.ruoyi.quality.mapper.QualityInspectMapper;
import com.ruoyi.quality.service.QualityReportService;
import org.springframework.beans.BeanUtils;
@@ -59,11 +56,11 @@
                QualityPassRateDto passRateDto = new QualityPassRateDto();
                BeanUtils.copyProperties(dto, passRateDto);

                if (dto.getInspectType() == 0) {
                if (dto.getModelType() == 0) {
                    wrapper.setRawMaterial(passRateDto);
                } else if (dto.getInspectType() == 1) {
                } else if (dto.getModelType() == 1) {
                    wrapper.setProcess(passRateDto);
                } else if (dto.getInspectType() == 2) {
                } else if (dto.getModelType() == 2) {
                    wrapper.setOutgoing(passRateDto);
                }
            }
@@ -90,11 +87,11 @@
    }

    @Override
    public QualityTopParameterDto getTopParameters(Integer inspectType) {
        if (inspectType == null) {
    public QualityTopParameterDto getTopParameters(Integer modelType) {
        if (modelType == null) {
            return new QualityTopParameterDto();
        }
        List<QualityParameterStatDto> list = qualityInspectMapper.getTopParameters(inspectType);
        List<QualityParameterStatDto> list = qualityInspectMapper.getTopParameters(modelType);

        BigDecimal total = list.stream()
                .map(QualityParameterStatDto::getCount)

 src/main/resources/mapper/quality/QualityInspectMapper.xml

@@ -56,84 +56,123 @@
    </delete>

    <select id="getInspectStatistics" resultType="com.ruoyi.quality.dto.QualityInspectStatDto">
        SELECT 0                                                                                   AS inspectType,
               COALESCE((SELECT SUM(inbound_num) FROM procurement_record_storage WHERE type = 1), 0) +
               COALESCE((SELECT SUM(quantity) FROM quality_unqualified WHERE inspect_type = 0), 0) AS totalCount,
               COALESCE((SELECT SUM(inbound_num)
                         FROM procurement_record_storage
                         WHERE type = 1
                           AND quality_inspect_id != 0
                           AND quality_inspect_id IS NOT NULL), 0) +
               COALESCE((SELECT SUM(quantity) FROM quality_unqualified WHERE inspect_type = 0), 0) AS completedCount
        UNION ALL
        SELECT 1                                                   AS inspectType,
               COALESCE((SELECT SUM(inbound_num)
                         FROM procurement_record_storage
                         WHERE type = 2
                           AND sales_ledger_product_id = 0), 0)    AS totalCount,
               COALESCE((SELECT SUM(inbound_num)
                         FROM procurement_record_storage
                         WHERE type = 2
                           AND sales_ledger_product_id = 0
                           AND quality_inspect_id != 0
                           AND quality_inspect_id IS NOT NULL), 0) AS completedCount
        UNION ALL
        SELECT 2                                                   AS inspectType,
               COALESCE((SELECT SUM(inbound_num)
                         FROM procurement_record_storage
                         WHERE type = 2
                           AND sales_ledger_product_id != 0), 0)   AS totalCount,
               COALESCE((SELECT SUM(inbound_num)
                         FROM procurement_record_storage
                         WHERE type = 2
                           AND sales_ledger_product_id != 0
                           AND quality_inspect_id != 0
                           AND quality_inspect_id IS NOT NULL), 0) AS completedCount
        SELECT CASE pp.product_name
                   WHEN '原材料' THEN 0
                   WHEN '半成品' THEN 1
                   WHEN '成品' THEN 2
                   END                     AS modelType,

               IFNULL(SUM(qi.quantity), 0) AS totalCount,

               IFNULL(SUM(CASE
                              WHEN qi.inspect_state = 1 THEN qi.quantity
                              ELSE 0
                   END), 0)                AS completedCount

        FROM product p
                 INNER JOIN product pp
                            ON p.parent_id = pp.id
                 LEFT JOIN product_model pm
                           ON pm.product_id = p.id
                 LEFT JOIN quality_inspect qi
                           ON qi.product_model_id = pm.id

        WHERE pp.product_name IN ('原材料', '半成品', '成品')

        GROUP BY pp.product_name

    </select>

    <select id="getPassRateStatistics" resultType="com.ruoyi.quality.dto.QualityPassRateDto">
        SELECT t.inspectType,
               COALESCE(prs.totalCount, 0)                                            AS totalCount,
               COALESCE(qi.completedCount, 0)                                         AS completedCount,
               COALESCE(qi.qualifiedCount, 0)                                         AS qualifiedCount,
               COALESCE(qi.unqualifiedCount, 0)                                       AS unqualifiedCount,
               IF(COALESCE(prs.totalCount, 0) = 0, 0,
                  ROUND(COALESCE(qi.completedCount, 0) / prs.totalCount * 100, 2))    AS completionRate,
               IF(COALESCE(qi.completedCount, 0) = 0, 0,
                  ROUND(COALESCE(qi.qualifiedCount, 0) / qi.completedCount * 100, 2)) AS passRate
        FROM (SELECT 0 AS inspectType
        SELECT t.modelType,

               COALESCE(SUM(qi.quantity), 0) AS totalCount,

               COALESCE(SUM(
                                CASE
                                    WHEN qi.inspect_state = 1 THEN qi.quantity
                                    ELSE 0
                                    END
                        ), 0)                AS completedCount,

               COALESCE(SUM(
                                CASE
                                    WHEN qi.inspect_state = 1
                                        AND qi.check_result = '合格'
                                        THEN qi.quantity
                                    ELSE 0
                                    END
                        ), 0)                AS qualifiedCount,

               COALESCE(SUM(
                                CASE
                                    WHEN qi.inspect_state = 1
                                        AND qi.check_result = '不合格'
                                        THEN qi.quantity
                                    ELSE 0
                                    END
                        ), 0)                AS unqualifiedCount,

            /* 完成率 */
               IF(COALESCE(SUM(qi.quantity), 0) = 0, 0,
                  ROUND(
                          COALESCE(SUM(
                                           CASE
                                               WHEN qi.inspect_state = 1 THEN qi.quantity
                                               ELSE 0
                                               END
                                   ), 0)
                              / SUM(qi.quantity) * 100, 2)
               )                             AS completionRate,

            /* 合格率 */
               IF(COALESCE(SUM(
                                   CASE
                                       WHEN qi.inspect_state = 1 THEN qi.quantity
                                       ELSE 0
                                       END
                           ), 0) = 0, 0,
                  ROUND(
                          COALESCE(SUM(
                                           CASE
                                               WHEN qi.inspect_state = 1
                                                   AND qi.check_result = '合格'
                                                   THEN qi.quantity
                                               ELSE 0
                                               END
                                   ), 0)
                              /
                          COALESCE(SUM(
                                           CASE
                                               WHEN qi.inspect_state = 1 THEN qi.quantity
                                               ELSE 0
                                               END
                                   ), 0) * 100, 2)
               )                             AS passRate

        FROM (SELECT 0 AS modelType
              UNION ALL
              SELECT 1
              UNION ALL
              SELECT 2) t
                 LEFT JOIN (SELECT inspect_type,
                                   SUM(quantity)                                 AS completedCount,
                                   SUM(IF(check_result = '合格', quantity, 0))   AS qualifiedCount,
                                   SUM(IF(check_result = '不合格', quantity, 0)) AS unqualifiedCount
                            FROM quality_inspect
                            WHERE inspect_state = 1
                            GROUP BY inspect_type) qi
                           ON t.inspectType = qi.inspect_type
                 LEFT JOIN (SELECT 0           AS inspectType,
                                   COALESCE((SELECT SUM(inbound_num) FROM procurement_record_storage WHERE type = 1),
                                            0) +
                                   COALESCE((SELECT SUM(quantity) FROM quality_unqualified WHERE inspect_type = 0),
                                            0) AS totalCount
                            UNION ALL
                            SELECT 1                                                AS inspectType,
                                   COALESCE((SELECT SUM(inbound_num)
                                             FROM procurement_record_storage
                                             WHERE type = 2
                                               AND sales_ledger_product_id = 0), 0) AS totalCount
                            UNION ALL
                            SELECT 2                                                 AS inspectType,
                                   COALESCE((SELECT SUM(inbound_num)
                                             FROM procurement_record_storage
                                             WHERE type = 2
                                               AND sales_ledger_product_id != 0), 0) AS totalCount) prs
                           ON t.inspectType = prs.inspectType
        ORDER BY t.inspectType;
                 LEFT JOIN product p
                           ON 1 = 1
                 LEFT JOIN product pp
                           ON p.parent_id = pp.id
                 LEFT JOIN product_model pm
                           ON pm.product_id = p.id
                 LEFT JOIN quality_inspect qi
                           ON qi.product_model_id = pm.id
                               AND (
                                  (pp.product_name = '原材料' AND t.modelType = 0) OR
                                  (pp.product_name = '半成品' AND t.modelType = 1) OR
                                  (pp.product_name = '成品' AND t.modelType = 2)
                                  )

        GROUP BY t.modelType
        ORDER BY t.modelType;
    </select>


    <select id="getMonthlyPassRateStatistics" resultType="com.ruoyi.quality.dto.QualityMonthlyPassRateDto">
        WITH RECURSIVE
@@ -142,50 +181,15 @@
                       SELECT month_num + 1
                       FROM months
                       WHERE month_num &lt; 12),
            types AS (SELECT 0 AS inspectType
            types AS (SELECT 0 AS modelType
                      UNION ALL
                      SELECT 1
                      UNION ALL
                      SELECT 2),
            base AS (SELECT m.month_num, t.inspectType
                     FROM months m,
                          types t),
            qi_data AS (SELECT MONTH(check_time)                                               AS month_num,
                               inspect_type,
                               SUM(quantity)                                                   AS completedCount,
                               SUM(CASE WHEN check_result = '合格' THEN quantity ELSE 0 END)   AS qualifiedCount,
                               SUM(CASE WHEN check_result = '不合格' THEN quantity ELSE 0 END) AS unqualifiedCount
                        FROM quality_inspect
                        WHERE YEAR(check_time) = #{year}
                          AND inspect_state = 1
                        GROUP BY MONTH(check_time), inspect_type),
            prs_data AS (SELECT month_num,
                                inspectType,
                                SUM(totalCount) AS totalCount
                         FROM (SELECT MONTH(create_time) AS month_num,
                                      0                  AS inspectType,
                                      inbound_num        AS totalCount
                               FROM procurement_record_storage
                               WHERE type = 1
                                 AND YEAR(create_time) = #{year}
                               UNION ALL
                               SELECT MONTH(check_time) AS month_num,
                                      0                 AS inspectType,
                                      quantity          AS totalCount
                               FROM quality_unqualified
                               WHERE inspect_type = 0
                                 AND YEAR(check_time) = #{year}
                               UNION ALL
                               SELECT MONTH(create_time) AS month_num,
                                      CASE
                                          WHEN sales_ledger_product_id = 0 THEN 1
                                          ELSE 2
                                          END            AS inspectType,
                                      inbound_num        AS totalCount
                               FROM procurement_record_storage
                               WHERE type = 2
                                 AND YEAR(create_time) = #{year}) sub
                         GROUP BY month_num, inspectType)
            base AS (SELECT m.month_num, t.modelType
                     FROM months m
                              CROSS JOIN types t)

        SELECT CASE b.month_num
                   WHEN 1 THEN '一月'
                   WHEN 2 THEN '二月'
@@ -200,95 +204,120 @@
                   WHEN 11 THEN '十一月'
                   WHEN 12 THEN '十二月'
                   END                         AS month,
               b.inspectType,
               COALESCE(p.totalCount, 0)       AS totalCount,
               COALESCE(q.completedCount, 0)   AS completedCount,
               COALESCE(q.qualifiedCount, 0)   AS qualifiedCount,
               COALESCE(q.unqualifiedCount, 0) AS unqualifiedCount,

               b.modelType,

            /* 总数量 */
               COALESCE(SUM(qi.quantity), 0) AS totalCount,

            /* 已完成 */
               COALESCE(SUM(
                                CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END
                        ), 0)                AS completedCount,

            /* 合格 */
               COALESCE(SUM(
               CASE
                   WHEN COALESCE(p.totalCount, 0) = 0 THEN 0
                   ELSE ROUND(COALESCE(q.completedCount, 0) / p.totalCount * 100, 2)
                   END                         AS completionRate,
                                    WHEN qi.inspect_state = 1 AND qi.check_result = '合格'
                                        THEN qi.quantity
                                    ELSE 0
                                    END
                        ), 0)                AS qualifiedCount,

            /* 不合格 */
               COALESCE(SUM(
               CASE
                   WHEN COALESCE(q.completedCount, 0) = 0 THEN 0
                   ELSE ROUND(COALESCE(q.qualifiedCount, 0) / q.completedCount * 100, 2)
                   END                         AS passRate
                                    WHEN qi.inspect_state = 1 AND qi.check_result = '不合格'
                                        THEN qi.quantity
                                    ELSE 0
                                    END
                        ), 0)                AS unqualifiedCount,

            /* 完成率 */
               IF(COALESCE(SUM(qi.quantity), 0) = 0, 0,
                  ROUND(
                          COALESCE(SUM(
                                           CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END
                                   ), 0) / SUM(qi.quantity) * 100, 2
                  )
               )                             AS completionRate,

            /* 合格率 */
               IF(COALESCE(SUM(
                                   CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END
                           ), 0) = 0, 0,
                  ROUND(
                          COALESCE(SUM(
                                           CASE
                                               WHEN qi.inspect_state = 1 AND qi.check_result = '合格'
                                                   THEN qi.quantity
                                               ELSE 0
                                               END
                                   ), 0)
                              /
                          COALESCE(SUM(
                                           CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END
                                   ), 0) * 100, 2
                  )
               )                             AS passRate

        FROM base b
                 LEFT JOIN qi_data q ON b.month_num = q.month_num AND b.inspectType = q.inspect_type
                 LEFT JOIN prs_data p ON b.month_num = p.month_num AND b.inspectType = p.inspectType
        ORDER BY b.month_num, b.inspectType
                 LEFT JOIN product p ON 1 = 1
                 LEFT JOIN product pp ON p.parent_id = pp.id
                 LEFT JOIN product_model pm ON pm.product_id = p.id
                 LEFT JOIN quality_inspect qi
                           ON qi.product_model_id = pm.id
                               AND YEAR(qi.check_time) = #{year}
                               AND MONTH(qi.check_time) = b.month_num
                               AND (
                                  (pp.product_name = '原材料' AND b.modelType = 0)
                                      OR (pp.product_name = '半成品' AND b.modelType = 1)
                                      OR (pp.product_name = '成品' AND b.modelType = 2)
                                  )

        GROUP BY b.month_num, b.modelType
        ORDER BY b.month_num, b.modelType;

    </select>

    <select id="getYearlyPassRateStatistics" resultType="com.ruoyi.quality.dto.QualityPassRateDto">
        SELECT t.inspectType,
               COALESCE(prs.totalCount, 0)      AS totalCount,
               COALESCE(qi.completedCount, 0)   AS completedCount,
               COALESCE(qi.qualifiedCount, 0)   AS qualifiedCount,
               COALESCE(qi.unqualifiedCount, 0) AS unqualifiedCount,
        SELECT t.modelType,

               COALESCE(SUM(
               CASE
                   WHEN COALESCE(prs.totalCount, 0) = 0 THEN 0
                   ELSE ROUND(COALESCE(qi.completedCount, 0) / prs.totalCount * 100, 2)
                   END                          AS completionRate,
               CASE
                   WHEN COALESCE(qi.completedCount, 0) = 0 THEN 0
                   ELSE ROUND(COALESCE(qi.qualifiedCount, 0) / qi.completedCount * 100, 2)
                   END                          AS passRate
        FROM (SELECT 0 AS inspectType
                                    WHEN pp.product_name = '原材料' AND t.modelType = 0 THEN qi.quantity
                                    WHEN pp.product_name = '半成品' AND t.modelType = 1 THEN qi.quantity
                                    WHEN pp.product_name = '成品' AND t.modelType = 2 THEN qi.quantity
                                    ELSE 0
                                    END
                        ), 0) AS totalCount

        FROM (SELECT 0 AS modelType
              UNION ALL
              SELECT 1
              UNION ALL
              SELECT 2) t
                 LEFT JOIN (SELECT inspect_type,
                                   SUM(quantity)                                                   AS completedCount,
                                   SUM(CASE WHEN check_result = '合格' THEN quantity ELSE 0 END)   AS qualifiedCount,
                                   SUM(CASE WHEN check_result = '不合格' THEN quantity ELSE 0 END) AS unqualifiedCount
                            FROM quality_inspect
                            WHERE YEAR(check_time) = #{year}
                              AND inspect_state = 1
                            GROUP BY inspect_type) qi
                           ON t.inspectType = qi.inspect_type
                 LEFT JOIN (SELECT 0                                               AS inspectType,
                                   COALESCE((SELECT SUM(inbound_num)
                                             FROM procurement_record_storage
                                             WHERE type = 1
                                               AND YEAR(create_time) = #{year}), 0) +
                                   COALESCE((SELECT SUM(quantity)
                                             FROM quality_unqualified
                                             WHERE inspect_type = 0
                                               AND YEAR(check_time) = #{year}), 0) AS totalCount
                            UNION ALL
                            SELECT 1                                                AS inspectType,
                                   COALESCE((SELECT SUM(inbound_num)
                                             FROM procurement_record_storage
                                             WHERE type = 2
                                               AND sales_ledger_product_id = 0
                                               AND YEAR(create_time) = #{year}), 0) AS totalCount
                            UNION ALL
                            SELECT 2                                                AS inspectType,
                                   COALESCE((SELECT SUM(inbound_num)
                                             FROM procurement_record_storage
                                             WHERE type = 2
                                               AND sales_ledger_product_id != 0
                                               AND YEAR(create_time) = #{year}), 0) AS totalCount) prs
                           ON t.inspectType = prs.inspectType
        ORDER BY t.inspectType
                 LEFT JOIN product p ON 1 = 1
                 LEFT JOIN product pp ON p.parent_id = pp.id
                 LEFT JOIN product_model pm ON pm.product_id = p.id
                 LEFT JOIN quality_inspect qi
                           ON qi.product_model_id = pm.id
                               AND YEAR(qi.check_time) = #{year}
                               AND qi.inspect_state = 1
                               AND qi.check_result = '合格'

        GROUP BY t.modelType
        ORDER BY t.modelType;
    </select>



    <select id="getMonthlyCompletionDetails" resultType="com.ruoyi.quality.dto.QualityMonthlyDetailDto">
        WITH RECURSIVE
            months AS (SELECT 1 AS month_num
        WITH RECURSIVE months AS (SELECT 1 AS month_num
                       UNION ALL
                       SELECT month_num + 1
                       FROM months
                       WHERE month_num &lt; 12),
            qi_data AS (SELECT MONTH(check_time)                                        AS month_num,
                               SUM(CASE WHEN inspect_type = 0 THEN quantity ELSE 0 END) AS rawMaterialCount,
                               SUM(CASE WHEN inspect_type = 1 THEN quantity ELSE 0 END) AS processCount,
                               SUM(CASE WHEN inspect_type = 2 THEN quantity ELSE 0 END) AS outgoingCount
                        FROM quality_inspect
                        WHERE YEAR(check_time) = #{year}
                          AND inspect_state = 1
                        GROUP BY MONTH(check_time))
                                  WHERE month_num &lt; 12)
        SELECT CASE m.month_num
                   WHEN 1 THEN '一月'
                   WHEN 2 THEN '二月'
@@ -303,20 +332,65 @@
                   WHEN 11 THEN '十一月'
                   WHEN 12 THEN '十二月'
                   END                         AS month,
               COALESCE(d.rawMaterialCount, 0) AS rawMaterialCount,
               COALESCE(d.processCount, 0)     AS processCount,
               COALESCE(d.outgoingCount, 0)    AS outgoingCount

            /* 原材料 */
               COALESCE(SUM(
                                CASE
                                    WHEN pp.product_name = '原材料'
                                        THEN qi.quantity
                                    ELSE 0
                                    END
                        ), 0) AS rawMaterialCount,

            /* 半成品 */
               COALESCE(SUM(
                                CASE
                                    WHEN pp.product_name = '半成品'
                                        THEN qi.quantity
                                    ELSE 0
                                    END
                        ), 0) AS processCount,

            /* 成品 */
               COALESCE(SUM(
                                CASE
                                    WHEN pp.product_name = '成品'
                                        THEN qi.quantity
                                    ELSE 0
                                    END
                        ), 0) AS outgoingCount

        FROM months m
                 LEFT JOIN qi_data d ON m.month_num = d.month_num
        ORDER BY m.month_num
                 LEFT JOIN product p ON 1 = 1
                 LEFT JOIN product pp ON p.parent_id = pp.id
                 LEFT JOIN product_model pm ON pm.product_id = p.id
                 LEFT JOIN quality_inspect qi
                           ON qi.product_model_id = pm.id
                               AND qi.inspect_state = 1
                               AND YEAR(qi.check_time) = #{year}
                               AND MONTH(qi.check_time) = m.month_num

        GROUP BY m.month_num
        ORDER BY m.month_num;

    </select>

    <select id="getTopParameters" resultType="com.ruoyi.quality.dto.QualityParameterStatDto">
        WITH parameter_counts AS (SELECT qip.parameter_item            AS name,
                                         SUM(COALESCE(qi.quantity, 0)) AS count
                                  FROM quality_inspect qi
                                           JOIN quality_inspect_param qip ON qip.inspect_id = qi.id
                                  WHERE qi.inspect_type = #{inspectType}
                                         COUNT(*)           AS count
                                  FROM quality_inspect_param qip
                                           JOIN quality_inspect qi
                                                ON qip.inspect_id = qi.id
                                           JOIN product p
                                                ON qi.product_id = p.id
                                           JOIN product pp
                                                ON p.parent_id = pp.id
                                  WHERE qi.inspect_state = 1
                                    AND (
                                      (#{modelType} = 1 AND pp.product_name = '原材料')
                                          OR (#{modelType} = 2 AND pp.product_name = '半成品')
                                          OR (#{modelType} = 3 AND pp.product_name = '成品')
                                      )
                                  GROUP BY qip.parameter_item),
             ranked AS (SELECT name,
                               count,
@@ -324,6 +398,7 @@
                        FROM parameter_counts),
             total AS (SELECT SUM(count) AS total_count
                       FROM parameter_counts)

        SELECT name,
               count,
               CASE
@@ -333,14 +408,18 @@
        FROM (SELECT name, count, rn
              FROM ranked
              WHERE rn &lt;= 4

              UNION ALL

              SELECT '其他检测' AS name,
                     SUM(count) AS count,
                     5          AS rn
              FROM ranked
              WHERE rn &gt; 4
              HAVING SUM(count) &gt; 0) t
        ORDER BY rn
              WHERE rn > 4
              HAVING SUM(count) > 0) t
        ORDER BY rn;

    </select>


</mapper>