From c99dda111f11698d2689c9095ed96dd9e4fa32cd Mon Sep 17 00:00:00 2001
From: xiaoyi <908147524@qq.com>
Date: 星期五, 30 十月 2026 11:32:15 +0800
Subject: [PATCH] fix(qc): 清洗历史质检数据,修复首页合格率超过 100%

---
 sql/mysql/fix_qc_check_quantity_20261030.sql |   72 ++++++++++++++++++++++++++++++++++++
 1 files changed, 72 insertions(+), 0 deletions(-)

diff --git a/sql/mysql/fix_qc_check_quantity_20261030.sql b/sql/mysql/fix_qc_check_quantity_20261030.sql
new file mode 100644
index 0000000..57a23b6
--- /dev/null
+++ b/sql/mysql/fix_qc_check_quantity_20261030.sql
@@ -0,0 +1,72 @@
+-- ============================================================
+-- 淇棣栭〉銆屾潵鏂欏悎鏍肩巼 / 鍑鸿揣鍚堟牸鐜囥�嶈秴杩� 100% 鐨勯棶棰�
+-- 鐢熸垚鏃堕棿锛�2026-10-30
+--
+-- 闂鐜拌薄锛氶椤� KPI 鏄剧ず
+--   鏉ユ枡妫�楠屽悎鏍肩巼 133703.70%锛堝簲涓� 100%锛�
+--   鍑鸿揣妫�楠屽悎鏍肩巼  88642.86%锛堝簲涓� 100%锛�
+--
+-- 鏍瑰洜锛氬悎鏍肩巼绠楁硶 = 鍚堟牸鏁伴噺 / 妫�楠屾暟閲忥紙鏍囧噯璐ㄩ噺鍙e緞锛�
+--       浣嗗巻鍙插鍏ユ暟鎹殑 check_quantity锛堟楠屾暟閲忥級琚啓鎴愪簡鍥哄畾灏忓��
+--       锛圛QC 鍏ㄤ负 5锛孫QC 鍏ㄤ负 1锛夛紝鑰� qualified_quantity 濉殑鏄埌璐�/鍑鸿揣鎬婚噺銆�
+--       鍒嗗瓙杩滃ぇ浜庡垎姣� 鈫� 鍚堟牸鐜囩垎琛ㄣ��
+--
+-- 浠g爜閫昏緫锛圡esQcIqcServiceImpl:127锛夛細
+--   check_quantity = qualified_quantity + unqualified_quantity
+--
+-- 鏁版嵁浜嬪疄锛堟竻娲楀墠宸叉牳瀵癸級锛�
+--   IQC 27 鏉★細qualified + unqualified = received_quantity  鍏ㄩ儴鍚诲悎
+--   OQC  7 鏉★細qualified + unqualified = out_quantity       鍏ㄩ儴鍚诲悎
+--   鈫� 鍙渶鎶� check_quantity 鏀规垚杩欎釜鍜屽嵆鍙紝涓嶅姩鍏跺畠浠讳綍鍒�
+--
+-- 褰卞搷锛氫慨澶嶅悗 IQC / OQC 鍚堟牸鐜囧潎鎭㈠涓� 100.00%
+-- 澶囦唤锛�/data/backup/mysql/manual/pre_qc_quantity_fix_*.sql
+-- 骞傜瓑锛氬彲閲嶅鎵ц锛堝凡姝g‘鐨勪笉浼氬啀鍙橈級
+-- ============================================================
+
+-- ---------- 1. 娓呮礂鍓嶅揩鐓� ----------
+SELECT 'IQC-娓呮礂鍓�' AS 鐜妭, COUNT(*) AS 璁板綍鏁�,
+       SUM(check_quantity) AS 妫�楠岄噺鍚堣, SUM(qualified_quantity) AS 鍚堟牸閲忓悎璁�,
+       ROUND(SUM(qualified_quantity)/SUM(check_quantity)*100, 2) AS 褰撳墠鍚堟牸鐜�
+  FROM mes_qc_iqc WHERE deleted = 0;
+
+SELECT 'OQC-娓呮礂鍓�' AS 鐜妭, COUNT(*) AS 璁板綍鏁�,
+       SUM(check_quantity) AS 妫�楠岄噺鍚堣, SUM(qualified_quantity) AS 鍚堟牸閲忓悎璁�,
+       ROUND(SUM(qualified_quantity)/SUM(check_quantity)*100, 2) AS 褰撳墠鍚堟牸鐜�
+  FROM mes_qc_oqc WHERE deleted = 0;
+
+-- ---------- 2. 淇 IQC锛氭楠屾暟閲� = 鍚堟牸鏁伴噺 + 涓嶅悎鏍兼暟閲� ----------
+UPDATE mes_qc_iqc
+   SET check_quantity = qualified_quantity + unqualified_quantity,
+       updater        = '1',
+       update_time    = NOW()
+ WHERE deleted = 0
+   AND ABS(check_quantity - (qualified_quantity + unqualified_quantity)) > 0.001;
+
+-- ---------- 3. 淇 OQC锛氭楠屾暟閲� = 鍚堟牸鏁伴噺 + 涓嶅悎鏍兼暟閲� ----------
+UPDATE mes_qc_oqc
+   SET check_quantity = qualified_quantity + unqualified_quantity,
+       updater        = '1',
+       update_time    = NOW()
+ WHERE deleted = 0
+   AND ABS(check_quantity - (qualified_quantity + unqualified_quantity)) > 0.001;
+
+-- ---------- 4. 娓呮礂鍚庢牎楠� ----------
+SELECT 'IQC-娓呮礂鍚�' AS 鐜妭, COUNT(*) AS 璁板綍鏁�,
+       SUM(check_quantity) AS 妫�楠岄噺鍚堣, SUM(qualified_quantity) AS 鍚堟牸閲忓悎璁�,
+       ROUND(SUM(qualified_quantity)/SUM(check_quantity)*100, 2) AS 鍚堟牸鐜�
+  FROM mes_qc_iqc WHERE deleted = 0;
+
+SELECT 'OQC-娓呮礂鍚�' AS 鐜妭, COUNT(*) AS 璁板綍鏁�,
+       SUM(check_quantity) AS 妫�楠岄噺鍚堣, SUM(qualified_quantity) AS 鍚堟牸閲忓悎璁�,
+       ROUND(SUM(qualified_quantity)/SUM(check_quantity)*100, 2) AS 鍚堟牸鐜�
+  FROM mes_qc_oqc WHERE deleted = 0;
+
+-- ---------- 5. 纭娌℃湁浠讳綍涓�鏉″啀杩濆弽鍏紡 ----------
+SELECT 'IQC 杩濆弽鍏紡鏉℃暟' AS 妫�鏌ラ」,
+       SUM(CASE WHEN ABS(check_quantity - (qualified_quantity + unqualified_quantity)) > 0.001 THEN 1 ELSE 0 END) AS 鏉℃暟
+  FROM mes_qc_iqc WHERE deleted = 0;
+
+SELECT 'OQC 杩濆弽鍏紡鏉℃暟' AS 妫�鏌ラ」,
+       SUM(CASE WHEN ABS(check_quantity - (qualified_quantity + unqualified_quantity)) > 0.001 THEN 1 ELSE 0 END) AS 鏉℃暟
+  FROM mes_qc_oqc WHERE deleted = 0;

--
Gitblit v1.9.3