feat: 质量管理-报表管理数据展示接口

gongchunyi

7 天以前 307f6266c8fad7e2e1c9c9a9b3b8b0925c64710f

feat: 质量管理-报表管理数据展示接口

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

@@ -1,15 +1,12 @@
package com.ruoyi.quality.controller;

import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.ruoyi.framework.web.domain.AjaxResult;
import com.ruoyi.quality.pojo.QualityTestStandardParam;
import com.ruoyi.quality.service.QualityReportService;
import com.ruoyi.quality.service.QualityTestStandardParamService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiModelProperty;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.*;

import java.util.List;

/**
 * <p>
@@ -19,6 +16,7 @@
 * @author èŠ¯å¯¼è½¯ä»¶ï¼ˆæ±Ÿè‹ï¼‰æœ‰é™å…¬å¸
 * @since 2026-01-14 03:39:49
 */
@Api(tags = "质量管理")
@RestController
@RequestMapping("/qualityReport")
public class QualityReportController {
@@ -26,6 +24,58 @@
    @Autowired
    private QualityReportService qualityReportService;

    /**
     * èŽ·å–检验统计数据
     */
    @ApiOperation("获取检验统计数据")
    @GetMapping("/getInspectStatistics")
    public AjaxResult getInspectStatistics() {
        return AjaxResult.success(qualityReportService.getInspectStatistics());
    }

    /**
     * èŽ·å–合格率统计数据
     */
    @ApiOperation("获取合格率统计数据")
    @GetMapping("/getPassRateStatistics")
    public AjaxResult getPassRateStatistics() {
        return AjaxResult.success(qualityReportService.getPassRateStatistics());
    }

    /**
     * èŽ·å–月度合格率统计数据
     */
    @ApiOperation("获取月度合格率统计数据")
    @GetMapping("/getMonthlyPassRateStatistics")
    public AjaxResult getMonthlyPassRateStatistics(@RequestParam("year") String year) {
        return AjaxResult.success(qualityReportService.getMonthlyPassRateStatistics(year));
    }

    /**
     * èŽ·å–年度总合格率统计数据
     */
    @ApiOperation("获取年度总合格率统计数据")
    @GetMapping("/getYearlyPassRateStatistics")
    public AjaxResult getYearlyPassRateStatistics(@RequestParam("year") String year) {
        return AjaxResult.success(qualityReportService.getYearlyPassRateStatistics(year));
    }

    /**
     * èŽ·å–月度完成明细数据
     */
    @ApiOperation("获取月度完成明细数据")
    @GetMapping("/getMonthlyCompletionDetails")
    public AjaxResult getMonthlyCompletionDetails(@RequestParam("year") String year) {
        return AjaxResult.success(qualityReportService.getMonthlyCompletionDetails(year));
    }

    /**
     * èŽ·å–热点检测指标统计
     */
    @ApiOperation("获取热点检测指标统计")
    @GetMapping("/getTopParameters")
    public AjaxResult getTopParameters(@RequestParam("inspectType") Integer inspectType) {
        return AjaxResult.success(qualityReportService.getTopParameters(inspectType));
    }

}

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

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,24 @@
package com.ruoyi.quality.dto;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.math.BigDecimal;
import java.io.Serializable;

@Data
@ApiModel(value = "QualityInspectStatDto", description = "质量检验统计DTO")
public class QualityInspectStatDto implements Serializable {

    private static final long serialVersionUID = 1L;

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

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

    @ApiModelProperty(value = "已完成数量")
    private BigDecimal completedCount;
}

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

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,29 @@
package com.ruoyi.quality.dto;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;
import java.math.BigDecimal;

/**
 * è´¨é‡æœˆåº¦å®Œæˆæ˜Žç»†DTO
 */
@Data
@ApiModel(value = "QualityMonthlyDetailDto", description = "质量月度完成明细DTO")
public class QualityMonthlyDetailDto implements Serializable {
    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "月份")
    private String month;

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

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

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

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

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,41 @@
package com.ruoyi.quality.dto;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;
import java.math.BigDecimal;

/**
 * è´¨é‡æœˆåº¦åˆæ ¼çŽ‡ç»Ÿè®¡DTO
 */
@Data
@ApiModel(value = "QualityMonthlyPassRateDto", description = "质量月度合格率统计DTO")
public class QualityMonthlyPassRateDto implements Serializable {
    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "月份(一月, äºŒæœˆ...)")
    private String month;

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

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

    @ApiModelProperty(value = "已完成数量")
    private BigDecimal completedCount;

    @ApiModelProperty(value = "合格数量")
    private BigDecimal qualifiedCount;

    @ApiModelProperty(value = "不合格数量")
    private BigDecimal unqualifiedCount;

    @ApiModelProperty(value = "完成占比")
    private BigDecimal completionRate;

    @ApiModelProperty(value = "合格率占比")
    private BigDecimal passRate;
}

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

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,28 @@
package com.ruoyi.quality.dto;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;

/**
 * è´¨é‡æœˆåº¦åˆæ ¼çŽ‡åŒ…装DTO（按月分组）
 */
@Data
@ApiModel(value = "QualityMonthlyPassRateWrapperDto", description = "质量月度合格率包装DTO（按月分组）")
public class QualityMonthlyPassRateWrapperDto implements Serializable {
    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "月份")
    private String month;

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

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

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

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

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,26 @@
package com.ruoyi.quality.dto;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;
import java.math.BigDecimal;

/**
 * æ£€éªŒæŒ‡æ ‡ç»Ÿè®¡DTO
 */
@Data
@ApiModel(value = "QualityParameterStatDto", description = "检验指标统计DTO")
public class QualityParameterStatDto implements Serializable {
    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "指标名称")
    private String name;

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

    @ApiModelProperty(value = "百分比")
    private BigDecimal percentage;
}

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

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,39 @@
package com.ruoyi.quality.dto;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;
import java.math.BigDecimal;

/**
 * è´¨é‡åˆæ ¼çŽ‡ç»Ÿè®¡DTO
 */
@Data
@ApiModel(value = "QualityPassRateDto", description = "质量合格率统计DTO")
public class QualityPassRateDto implements Serializable {

    private static final long serialVersionUID = 1L;

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

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

    @ApiModelProperty(value = "已完成数量")
    private BigDecimal completedCount;

    @ApiModelProperty(value = "合格数量")
    private BigDecimal qualifiedCount;

    @ApiModelProperty(value = "不合格数量")
    private BigDecimal unqualifiedCount;

    @ApiModelProperty(value = "完成占比")
    private BigDecimal completionRate;

    @ApiModelProperty(value = "合格率占比")
    private BigDecimal passRate;
}

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

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,24 @@
package com.ruoyi.quality.dto;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.List;

/**
 * è´¨æ£€çƒ­ç‚¹æŒ‡æ ‡ç»Ÿè®¡ç»“æžœDTO (单类型)
 */
@Data
@ApiModel(value = "QualityTopParameterDto", description = "质检热点指标统计结果DTO (单类型)")
public class QualityTopParameterDto implements Serializable {
    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "总检测项次数量")
    private BigDecimal totalCount;

    @ApiModelProperty(value = "指标统计列表 (Top 4 + å…¶ä»–)")
    private List<QualityParameterStatDto> list;
}

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

@@ -4,9 +4,14 @@
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ruoyi.quality.pojo.QualityInspect;
import com.ruoyi.quality.pojo.QualityTestStandard;
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;

@@ -22,4 +27,34 @@
     * æ ¹æ®ç”Ÿäº§ä¸»è¡¨ID批量删除过程检验
     */
    int deleteByProductMainIds(@Param("productMainIds") List<Long> productMainIds);

    /**
     * èŽ·å–检验统计数据
     */
    List<QualityInspectStatDto> getInspectStatistics();

    /**
     * èŽ·å–合格率统计数据
     */
    List<QualityPassRateDto> getPassRateStatistics();

    /**
     * èŽ·å–月度合格率统计数据
     */
    List<QualityMonthlyPassRateDto> getMonthlyPassRateStatistics(@Param("year") String year);

    /**
     * èŽ·å–年度合格率统计数据
     */
    List<QualityPassRateDto> getYearlyPassRateStatistics(@Param("year") String year);

    /**
     * èŽ·å–月度完成明细数据
     */
    List<QualityMonthlyDetailDto> getMonthlyCompletionDetails(@Param("year") String year);

    /**
     * èŽ·å–热点检测指标 Top 4 + å…¶ä»–
     */
    List<QualityParameterStatDto> getTopParameters(@Param("inspectType") Integer inspectType);
}

 src/main/java/com/ruoyi/quality/service/QualityReportService.java

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

import com.baomidou.mybatisplus.extension.service.IService;
import com.ruoyi.quality.pojo.QualityTestStandardParam;
import com.ruoyi.quality.dto.QualityInspectStatDto;
import com.ruoyi.quality.dto.QualityPassRateDto;
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 java.util.List;

/**
 * <p>
@@ -11,6 +17,17 @@
 * @author èŠ¯å¯¼è½¯ä»¶ï¼ˆæ±Ÿè‹ï¼‰æœ‰é™å…¬å¸
 * @since 2026-01-13 03:39:49
 */
public interface QualityReportService  {
public interface QualityReportService {

    List<QualityInspectStatDto> getInspectStatistics();

    List<QualityPassRateDto> getPassRateStatistics();

    List<QualityMonthlyPassRateWrapperDto> getMonthlyPassRateStatistics(String year);

    List<QualityPassRateDto> getYearlyPassRateStatistics(String year);

    List<QualityMonthlyDetailDto> getMonthlyCompletionDetails(String year);

    QualityTopParameterDto getTopParameters(Integer inspectType);
}

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

@@ -1,9 +1,109 @@
package com.ruoyi.quality.service.impl;

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.quality.mapper.QualityInspectMapper;
import com.ruoyi.quality.service.QualityReportService;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;


@Service
public class QualityReportServiceImpl implements QualityReportService {

    @Autowired
    private QualityInspectMapper qualityInspectMapper;

    @Override
    public List<QualityInspectStatDto> getInspectStatistics() {
        return qualityInspectMapper.getInspectStatistics();
    }

    @Override
    public List<QualityPassRateDto> getPassRateStatistics() {
        return qualityInspectMapper.getPassRateStatistics();
    }

    @Override
    public List<QualityMonthlyPassRateWrapperDto> getMonthlyPassRateStatistics(String year) {
        if (StringUtils.isEmpty(year)) {
            return new ArrayList<>();
        }
        List<QualityMonthlyPassRateDto> flatData = qualityInspectMapper.getMonthlyPassRateStatistics(year);

        // æŒ‰æœˆä»½åˆ†ç»„，并保持顺序
        Map<String, List<QualityMonthlyPassRateDto>> groupedByMonth = flatData.stream()
                .collect(Collectors.groupingBy(QualityMonthlyPassRateDto::getMonth, LinkedHashMap::new, Collectors.toList()));

        List<QualityMonthlyPassRateWrapperDto> result = new ArrayList<>();

        groupedByMonth.forEach((month, dtos) -> {
            QualityMonthlyPassRateWrapperDto wrapper = new QualityMonthlyPassRateWrapperDto();
            wrapper.setMonth(month);

            for (QualityMonthlyPassRateDto dto : dtos) {
                QualityPassRateDto passRateDto = new QualityPassRateDto();
                BeanUtils.copyProperties(dto, passRateDto);

                if (dto.getInspectType() == 0) {
                    wrapper.setRawMaterial(passRateDto);
                } else if (dto.getInspectType() == 1) {
                    wrapper.setProcess(passRateDto);
                } else if (dto.getInspectType() == 2) {
                    wrapper.setOutgoing(passRateDto);
                }
            }
            result.add(wrapper);
        });

        return result;
    }

    @Override
    public List<QualityPassRateDto> getYearlyPassRateStatistics(String year) {
        if (StringUtils.isEmpty(year)) {
            return new ArrayList<>();
        }
        return qualityInspectMapper.getYearlyPassRateStatistics(year);
    }

    @Override
    public List<QualityMonthlyDetailDto> getMonthlyCompletionDetails(String year) {
        if (StringUtils.isEmpty(year)) {
            return new ArrayList<>();
        }
        return qualityInspectMapper.getMonthlyCompletionDetails(year);
    }

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

        BigDecimal total = list.stream()
                .map(QualityParameterStatDto::getCount)
                .reduce(BigDecimal.ZERO, BigDecimal::add);

        QualityTopParameterDto result = new QualityTopParameterDto();
        result.setTotalCount(total);
        result.setList(list);

        return result;
    }
}

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

@@ -23,7 +23,7 @@
            AND check_time &gt;= DATE_FORMAT(#{qualityInspect.entryDateStart},'%Y-%m-%d')
        </if>
        <if test="qualityInspect.entryDateEnd != null and qualityInspect.entryDateEnd != '' ">
            AND  check_time &lt;= DATE_FORMAT(#{qualityInspect.entryDateEnd},'%Y-%m-%d')
            AND check_time &lt;= DATE_FORMAT(#{qualityInspect.entryDateEnd},'%Y-%m-%d')
        </if>
        ORDER BY check_time DESC
    </select>
@@ -55,4 +55,286 @@
        </foreach>
    </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>

    <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
              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;
    </select>

    <select id="getMonthlyPassRateStatistics" resultType="com.ruoyi.quality.dto.QualityMonthlyPassRateDto">
        WITH RECURSIVE
            months AS (SELECT 1 AS month_num
                       UNION ALL
                       SELECT month_num + 1
                       FROM months
                       WHERE month_num &lt; 12),
            types AS (SELECT 0 AS inspectType
                      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)
        SELECT CASE b.month_num
                   WHEN 1 THEN '一月'
                   WHEN 2 THEN '二月'
                   WHEN 3 THEN '三月'
                   WHEN 4 THEN '四月'
                   WHEN 5 THEN '五月'
                   WHEN 6 THEN '六月'
                   WHEN 7 THEN '七月'
                   WHEN 8 THEN '八月'
                   WHEN 9 THEN '九月'
                   WHEN 10 THEN '十月'
                   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,
               CASE
                   WHEN COALESCE(p.totalCount, 0) = 0 THEN 0
                   ELSE ROUND(COALESCE(q.completedCount, 0) / p.totalCount * 100, 2)
                   END                         AS completionRate,
               CASE
                   WHEN COALESCE(q.completedCount, 0) = 0 THEN 0
                   ELSE ROUND(COALESCE(q.qualifiedCount, 0) / q.completedCount * 100, 2)
                   END                         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
    </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,
               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
              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
    </select>

    <select id="getMonthlyCompletionDetails" resultType="com.ruoyi.quality.dto.QualityMonthlyDetailDto">
        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))
        SELECT CASE m.month_num
                   WHEN 1 THEN '一月'
                   WHEN 2 THEN '二月'
                   WHEN 3 THEN '三月'
                   WHEN 4 THEN '四月'
                   WHEN 5 THEN '五月'
                   WHEN 6 THEN '六月'
                   WHEN 7 THEN '七月'
                   WHEN 8 THEN '八月'
                   WHEN 9 THEN '九月'
                   WHEN 10 THEN '十月'
                   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
        FROM months m
                 LEFT JOIN qi_data d ON m.month_num = d.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, COUNT(*) AS count
                                  FROM quality_inspect_param qip
                                           JOIN quality_inspect qi ON qip.inspect_id = qi.id
                                  WHERE qi.inspect_type = #{inspectType}
                                  GROUP BY qip.parameter_item),
             total AS (SELECT SUM(count) as total_count
                       FROM parameter_counts),
             ranked AS (SELECT name, count, ROW_NUMBER() OVER (ORDER BY count DESC) as rn
                        FROM parameter_counts)
        SELECT name,
               count,
               CASE
                   WHEN (SELECT total_count FROM total) = 0 THEN 0
                   ELSE ROUND(count / (SELECT total_count FROM total) * 100, 2)
                   END as percentage
        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 count &gt; 0) sub
        ORDER BY rn
    </select>
</mapper>