From 5303724901f83673d2cd0e33a80ea9a51a60c786 Mon Sep 17 00:00:00 2001
From: gongchunyi <deslre0381@gmail.com>
Date: 星期五, 03 四月 2026 11:12:27 +0800
Subject: [PATCH] fix： 质量管理-报表管理修改
---
src/main/resources/mapper/quality/QualityInspectMapper.xml | 435 +++++++++++++++++++++++-------------------------------
1 files changed, 184 insertions(+), 251 deletions(-)
diff --git a/src/main/resources/mapper/quality/QualityInspectMapper.xml b/src/main/resources/mapper/quality/QualityInspectMapper.xml
index 48fb369..0ccfbea 100644
--- a/src/main/resources/mapper/quality/QualityInspectMapper.xml
+++ b/src/main/resources/mapper/quality/QualityInspectMapper.xml
@@ -75,8 +75,23 @@
</delete>
<select id="getInspectStatistics" resultType="com.ruoyi.quality.dto.QualityInspectStatDto">
- SELECT CASE pp.product_name
- WHEN '鍘熸潗鏂�' THEN 0
+ WITH RECURSIVE product_tree AS (SELECT id,
+ parent_id,
+ product_name,
+ id AS root_id,
+ product_name AS root_name
+ FROM product
+ WHERE product_name IN ('鍘熸枡', '鍗婃垚鍝�', '鎴愬搧')
+ UNION ALL
+ SELECT p.id,
+ p.parent_id,
+ p.product_name,
+ pt.root_id,
+ pt.root_name
+ FROM product p
+ INNER JOIN product_tree pt ON p.parent_id = pt.id)
+ SELECT CASE pt.root_name
+ WHEN '鍘熸枡' THEN 0
WHEN '鍗婃垚鍝�' THEN 1
WHEN '鎴愬搧' THEN 2
END AS modelType,
@@ -88,110 +103,61 @@
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
-
+ FROM product_tree pt
+ INNER JOIN product_model pm ON pm.product_id = pt.id
+ LEFT JOIN quality_inspect qi ON qi.product_model_id = pm.id
+ GROUP BY pt.root_id, pt.root_name
</select>
<select id="getPassRateStatistics" resultType="com.ruoyi.quality.dto.QualityPassRateDto">
- SELECT t.modelType,
+ WITH RECURSIVE product_tree AS (SELECT id,
+ product_name,
+ CASE product_name
+ WHEN '鍘熸枡' THEN 0
+ WHEN '鍗婃垚鍝�' THEN 1
+ WHEN '鎴愬搧' THEN 2
+ END AS modelType
+ FROM product
+ WHERE product_name IN ('鍘熸枡', '鍗婃垚鍝�', '鎴愬搧')
- COALESCE(SUM(qi.quantity), 0) AS totalCount,
+ UNION ALL
- COALESCE(SUM(
- CASE
- WHEN qi.inspect_state = 1 THEN qi.quantity
- ELSE 0
- END
- ), 0) AS completedCount,
+ SELECT p.id,
+ p.product_name,
+ pt.modelType
+ FROM product p
+ INNER JOIN product_tree pt ON p.parent_id = pt.id)
- COALESCE(SUM(
- CASE
- WHEN qi.inspect_state = 1
- AND qi.check_result = '鍚堟牸'
- THEN qi.quantity
- ELSE 0
- END
- ), 0) AS qualifiedCount,
+ SELECT base.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,
- 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,
+ ROUND(SUM(CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END)
+ / 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
+ IF(SUM(CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END) = 0, 0,
+ ROUND(SUM(CASE WHEN qi.inspect_state = 1 AND qi.check_result = '鍚堟牸' THEN qi.quantity ELSE 0 END)
+ / SUM(CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END) * 100, 2)
+ ) AS passRate
FROM (SELECT 0 AS modelType
UNION ALL
SELECT 1
UNION ALL
- SELECT 2) t
- 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)
- )
+ SELECT 2) base
+ LEFT JOIN product_tree pt ON base.modelType = pt.modelType
+ LEFT JOIN product_model pm ON pm.product_id = pt.id
+ LEFT JOIN quality_inspect qi ON qi.product_model_id = pm.id
- GROUP BY t.modelType
- ORDER BY t.modelType;
+ GROUP BY base.modelType
+ ORDER BY base.modelType
</select>
-
<select id="getMonthlyPassRateStatistics" resultType="com.ruoyi.quality.dto.QualityMonthlyPassRateDto">
WITH RECURSIVE
@@ -200,14 +166,24 @@
SELECT month_num + 1
FROM months
WHERE month_num < 12),
- types AS (SELECT 0 AS modelType
- UNION ALL
- SELECT 1
- UNION ALL
- SELECT 2),
- base AS (SELECT m.month_num, t.modelType
- FROM months m
- CROSS JOIN types t)
+ product_tree AS (SELECT id,
+ product_name,
+ CASE product_name
+ WHEN '鍘熸枡' THEN 0
+ WHEN '鍗婃垚鍝�' THEN 1
+ WHEN '鎴愬搧' THEN 2
+ END AS modelType
+ FROM product
+ WHERE product_name IN ('鍘熸枡', '鍗婃垚鍝�', '鎴愬搧')
+
+ UNION ALL
+
+ SELECT p.id, p.product_name, pt.modelType
+ FROM product p
+ INNER JOIN product_tree pt ON p.parent_id = pt.id),
+ base_matrix AS (SELECT m.month_num, t.modelType
+ FROM months m
+ CROSS JOIN (SELECT 0 AS modelType UNION ALL SELECT 1 UNION ALL SELECT 2) t)
SELECT CASE b.month_num
WHEN 1 THEN '涓�鏈�'
@@ -222,121 +198,102 @@
WHEN 10 THEN '鍗佹湀'
WHEN 11 THEN '鍗佷竴鏈�'
WHEN 12 THEN '鍗佷簩鏈�'
- END AS month,
-
+ 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,
+ 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,
+ ROUND(SUM(CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END) / 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
+ IF(SUM(CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END) = 0, 0,
+ ROUND(SUM(CASE WHEN qi.inspect_state = 1 AND qi.check_result = '鍚堟牸' THEN qi.quantity ELSE 0 END)
+ / SUM(CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END) * 100, 2)
+ ) AS passRate
- FROM base b
- 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)
- )
+ FROM base_matrix b
+ LEFT JOIN product_tree pt ON b.modelType = pt.modelType
+ -- 鍏宠仈浜у搧妯″瀷
+ LEFT JOIN product_model pm ON pm.product_id = pt.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
GROUP BY b.month_num, b.modelType
- ORDER BY b.month_num, b.modelType;
-
+ ORDER BY b.month_num, b.modelType
</select>
<select id="getYearlyPassRateStatistics" resultType="com.ruoyi.quality.dto.QualityPassRateDto">
+ WITH RECURSIVE
+ product_tree AS (SELECT id,
+ product_name,
+ CASE product_name
+ WHEN '鍘熸枡' THEN 0
+ WHEN '鍗婃垚鍝�' THEN 1
+ WHEN '鎴愬搧' THEN 2
+ END AS modelType
+ FROM product
+ WHERE product_name IN ('鍘熸枡', '鍗婃垚鍝�', '鎴愬搧')
+
+ UNION ALL
+
+ SELECT p.id, p.product_name, pt.modelType
+ FROM product p
+ INNER JOIN product_tree pt ON p.parent_id = pt.id),
+ types AS (SELECT 0 AS modelType
+ UNION ALL
+ SELECT 1
+ UNION ALL
+ SELECT 2)
+
SELECT t.modelType,
+ COALESCE(SUM(qi.quantity), 0) AS totalCount,
- 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
+ COALESCE(SUM(CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END), 0) AS completedCount,
- FROM (SELECT 0 AS modelType
- UNION ALL
- SELECT 1
- UNION ALL
- SELECT 2) t
- 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 = '鍚堟牸'
+ COALESCE(SUM(CASE WHEN qi.inspect_state = 1 AND qi.check_result = '鍚堟牸' THEN qi.quantity ELSE 0 END),
+ 0) AS qualifiedCount,
+
+ IF(SUM(CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END) = 0, 0,
+ ROUND(SUM(CASE WHEN qi.inspect_state = 1 AND qi.check_result = '鍚堟牸' THEN qi.quantity ELSE 0 END)
+ / SUM(CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END) * 100, 2)
+ ) AS passRate
+
+ FROM types t
+ LEFT JOIN product_tree pt ON t.modelType = pt.modelType
+ LEFT JOIN product_model pm ON pm.product_id = pt.id
+ LEFT JOIN quality_inspect qi ON qi.product_model_id = pm.id
+ AND YEAR(qi.check_time) = #{year}
GROUP BY t.modelType
- ORDER 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)
+ WITH RECURSIVE
+ months AS (SELECT 1 AS month_num
+ UNION ALL
+ SELECT month_num + 1
+ FROM months
+ WHERE month_num < 12),
+ product_tree AS (SELECT id, product_name AS root_name
+ FROM product
+ WHERE product_name IN ('鍘熸枡', '鍗婃垚鍝�', '鎴愬搧')
+
+ UNION ALL
+
+ SELECT p.id, pt.root_name
+ FROM product p
+ INNER JOIN product_tree pt ON p.parent_id = pt.id)
SELECT CASE m.month_num
WHEN 1 THEN '涓�鏈�'
WHEN 2 THEN '浜屾湀'
@@ -350,39 +307,17 @@
WHEN 10 THEN '鍗佹湀'
WHEN 11 THEN '鍗佷竴鏈�'
WHEN 12 THEN '鍗佷簩鏈�'
- END AS month,
+ END AS month,
- /* 鍘熸潗鏂� */
- COALESCE(SUM(
- CASE
- WHEN pp.product_name = '鍘熸潗鏂�'
- THEN qi.quantity
- ELSE 0
- END
- ), 0) AS rawMaterialCount,
+ COALESCE(SUM(CASE WHEN pt.root_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 pt.root_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
+ COALESCE(SUM(CASE WHEN pt.root_name = '鎴愬搧' THEN qi.quantity ELSE 0 END), 0) AS outgoingCount
FROM months m
- 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 product_tree pt ON 1 = 1
+ LEFT JOIN product_model pm ON pm.product_id = pt.id
LEFT JOIN quality_inspect qi
ON qi.product_model_id = pm.id
AND qi.inspect_state = 1
@@ -390,33 +325,36 @@
AND MONTH(qi.check_time) = m.month_num
GROUP BY m.month_num
- ORDER 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,
- 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,
- ROW_NUMBER() OVER (ORDER BY count DESC) AS rn
- FROM parameter_counts),
- total AS (SELECT SUM(count) AS total_count
- FROM parameter_counts)
+ WITH RECURSIVE
+ -- 1. 鎵惧埌瀵瑰簲鐨勬牴鑺傜偣
+ root_node AS (SELECT id, product_name
+ FROM product
+ WHERE (
+ (#{modelType} = 1 AND product_name = '鍘熸枡')
+ OR (#{modelType} = 2 AND product_name = '鍗婃垚鍝�')
+ OR (#{modelType} = 3 AND product_name = '鎴愬搧')
+ )),
+ target_ids AS (SELECT id
+ FROM root_node
+ UNION ALL
+ SELECT p.id
+ FROM product p
+ INNER JOIN target_ids ti ON p.parent_id = ti.id),
+ 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_state = 1
+ AND qi.product_id IN (SELECT id FROM target_ids)
+ GROUP BY qip.parameter_item),
+ ranked AS (SELECT name, count, ROW_NUMBER() OVER (ORDER BY count DESC) AS rn
+ FROM parameter_counts),
+ total AS (SELECT SUM(count) AS total_count
+ FROM parameter_counts)
SELECT name,
count,
@@ -427,17 +365,12 @@
FROM (SELECT name, count, rn
FROM ranked
WHERE rn <= 4
-
UNION ALL
-
- SELECT '鍏朵粬妫�娴�' AS name,
- SUM(count) AS count,
- 5 AS rn
+ SELECT '鍏朵粬妫�娴�' AS name, SUM(count) AS count, 5 AS rn
FROM ranked
WHERE rn > 4
HAVING SUM(count) > 0) t
- ORDER BY rn;
-
+ ORDER BY rn
</select>
--
Gitblit v1.9.3