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