From 599b5ff692722226d1e30fbdffc594b826bfa815 Mon Sep 17 00:00:00 2001
From: zss <zss@example.com>
Date: 星期一, 02 二月 2026 11:32:37 +0800
Subject: [PATCH] Merge remote-tracking branch 'origin/dev_New' into dev_New
---
src/main/java/com/ruoyi/quality/service/impl/QualityReportServiceImpl.java | 23 +-
src/main/java/com/ruoyi/quality/dto/QualityMonthlyPassRateDto.java | 4
src/main/java/com/ruoyi/quality/dto/QualityInspectStatDto.java | 4
src/main/java/com/ruoyi/quality/mapper/QualityInspectMapper.java | 9
src/main/java/com/ruoyi/quality/dto/QualityMonthlyPassRateWrapperDto.java | 6
src/main/java/com/ruoyi/quality/controller/QualityReportController.java | 4
src/main/java/com/ruoyi/quality/dto/QualityMonthlyDetailDto.java | 6
src/main/java/com/ruoyi/quality/dto/QualityPassRateDto.java | 4
src/main/resources/mapper/quality/QualityInspectMapper.xml | 485 ++++++++++++++++++++++++++++--------------------
9 files changed, 308 insertions(+), 237 deletions(-)
diff --git a/src/main/java/com/ruoyi/quality/controller/QualityReportController.java b/src/main/java/com/ruoyi/quality/controller/QualityReportController.java
index b3e7025..e60216d 100644
--- a/src/main/java/com/ruoyi/quality/controller/QualityReportController.java
+++ b/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));
}
}
diff --git a/src/main/java/com/ruoyi/quality/dto/QualityInspectStatDto.java b/src/main/java/com/ruoyi/quality/dto/QualityInspectStatDto.java
index 9b39c94..f1e92d6 100644
--- a/src/main/java/com/ruoyi/quality/dto/QualityInspectStatDto.java
+++ b/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;
diff --git a/src/main/java/com/ruoyi/quality/dto/QualityMonthlyDetailDto.java b/src/main/java/com/ruoyi/quality/dto/QualityMonthlyDetailDto.java
index a89d25d..c8ac682 100644
--- a/src/main/java/com/ruoyi/quality/dto/QualityMonthlyDetailDto.java
+++ b/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;
}
diff --git a/src/main/java/com/ruoyi/quality/dto/QualityMonthlyPassRateDto.java b/src/main/java/com/ruoyi/quality/dto/QualityMonthlyPassRateDto.java
index 9591a73..c38f715 100644
--- a/src/main/java/com/ruoyi/quality/dto/QualityMonthlyPassRateDto.java
+++ b/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;
diff --git a/src/main/java/com/ruoyi/quality/dto/QualityMonthlyPassRateWrapperDto.java b/src/main/java/com/ruoyi/quality/dto/QualityMonthlyPassRateWrapperDto.java
index 0bdbc39..f0d517b 100644
--- a/src/main/java/com/ruoyi/quality/dto/QualityMonthlyPassRateWrapperDto.java
+++ b/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;
}
diff --git a/src/main/java/com/ruoyi/quality/dto/QualityPassRateDto.java b/src/main/java/com/ruoyi/quality/dto/QualityPassRateDto.java
index 354579e..b322528 100644
--- a/src/main/java/com/ruoyi/quality/dto/QualityPassRateDto.java
+++ b/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;
diff --git a/src/main/java/com/ruoyi/quality/mapper/QualityInspectMapper.java b/src/main/java/com/ruoyi/quality/mapper/QualityInspectMapper.java
index 2958970..388ebcc 100644
--- a/src/main/java/com/ruoyi/quality/mapper/QualityInspectMapper.java
+++ b/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);
}
diff --git a/src/main/java/com/ruoyi/quality/service/impl/QualityReportServiceImpl.java b/src/main/java/com/ruoyi/quality/service/impl/QualityReportServiceImpl.java
index ee11f8e..f01f315 100644
--- a/src/main/java/com/ruoyi/quality/service/impl/QualityReportServiceImpl.java
+++ b/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)
diff --git a/src/main/resources/mapper/quality/QualityInspectMapper.xml b/src/main/resources/mapper/quality/QualityInspectMapper.xml
index 8058455..0efab8c 100644
--- a/src/main/resources/mapper/quality/QualityInspectMapper.xml
+++ b/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 < 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 '浜屾湀'
@@ -199,96 +203,121 @@
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
+ END AS month,
+
+ 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 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 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,
- 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
+ SELECT t.modelType,
+
+ COALESCE(SUM(
+ CASE
+ 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
- UNION ALL
- SELECT month_num + 1
- FROM months
- WHERE month_num < 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))
+ WITH RECURSIVE months AS (SELECT 1 AS month_num
+ UNION ALL
+ SELECT month_num + 1
+ FROM months
+ WHERE month_num < 12)
SELECT CASE m.month_num
WHEN 1 THEN '涓�鏈�'
WHEN 2 THEN '浜屾湀'
@@ -302,21 +331,66 @@
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
+ END AS month,
+
+ /* 鍘熸潗鏂� */
+ 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}
+ 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
+ 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 <= 4
+
UNION ALL
+
SELECT '鍏朵粬妫�娴�' AS name,
SUM(count) AS count,
5 AS rn
FROM ranked
- WHERE rn > 4
- HAVING SUM(count) > 0) t
- ORDER BY rn
+ WHERE rn > 4
+ HAVING SUM(count) > 0) t
+ ORDER BY rn;
+
</select>
+
</mapper>
--
Gitblit v1.9.3