zkxw/docs/sql/migrations/2026-10-28-prod-relink-deleted-child-refs.sql

-- 【生产环境脚本】
-- 依据生产备份 zkxw_2026-10-28_094047.sql.gz（2026-10-28 09:40）核对后编写。
-- 不要在测试库执行。测试库已用同目录下文件名带【测试数据库脚本】的文件处理过。
-- 生产库请执行本文件。2026-10-27 18:04 那份脚本的待修复集合与本备份一致，但请以本文件为准。
--
-- 背景：保存步骤产出物、协议样本、捐赠样本时曾经整表软删再插入，
--       下游仍指向 del_flag=1 的旧行。代码尚未发到生产，本备份仍是修复前的数据。
-- 策略：只在同业务键下恰好有一条有效行时改指。找不到或有多条时不动。
-- 幂等：已指向有效行的记录不会进入映射；重复执行影响行数为 0。
-- MySQL 5.7。跨表字符串比较统一 utf8mb4_general_ci，避免 1267。
-- 本脚本未在任何库执行。
--
-- 相对该备份的预期影响：
--   质检工单 production_step_output_id          11
--   生产工单 protocol_sample_id -> 协议样本     16
--   生产工单 protocol_sample_id -> 捐赠样本     70
--   质检短信 protocol_sample_id                 15
--   按样本缴费方案                              0
-- 缴费方案有 3 条仍指向已软删协议样本，但同租户同编码下没有有效样本，本脚本不改：
--   ZKZX34000856、ZKZX34000175、ZKZX34000199
-- 样本接收、质检工单上的样本 id 在该备份中已指向有效行，本脚本不包含这两处。
--
-- 与 2026-10-27 18:04 备份相比，上述 11 / 16 / 70 / 15 条映射没有变化。
-- 过夜新增、本脚本不会改到的行：
--   废液 NK-UBMC202603005：原有效产出物 F2755086E1AE49C1B10FA6E4624D5AE7 已被软删，
--   当前有效行是 3513B95B3FDC400C9744277B8919DAB4。没有质检工单指向被删的那一行。
--   新捐赠样本 KYJZ2026100055、KYJZ2026100056、KYJZ2026100057。
--   KYJZ2026100055 另有一条已软删样本，没有生产工单指向它。

-- ---------------------------------------------------------------------------
-- 1. 质检工单 -> 步骤产出物
--    同一步骤、同租户，名称、条码、产出类型一致，且只有一条有效产出物
-- ---------------------------------------------------------------------------

DROP TEMPORARY TABLE IF EXISTS tmp_qc_output_relink;
CREATE TEMPORARY TABLE tmp_qc_output_relink (
  qc_id VARCHAR(64) NOT NULL COLLATE utf8mb4_general_ci,
  new_output_id VARCHAR(64) NOT NULL COLLATE utf8mb4_general_ci,
  PRIMARY KEY (qc_id)
) ENGINE=MEMORY;

INSERT INTO tmp_qc_output_relink (qc_id, new_output_id)
SELECT q.id, MIN(live.id)
FROM biz_qc_work_order q
JOIN biz_production_task_step_output old
  ON old.id = q.production_step_output_id
 AND old.del_flag = 1
JOIN biz_production_task_step_output live
  ON live.step_id = old.step_id
 AND live.tenant_no = old.tenant_no
 AND live.del_flag = 0
 AND live.id <> old.id
 AND IFNULL(live.output_name, '') = IFNULL(old.output_name, '')
 AND IFNULL(live.output_barcode, '') = IFNULL(old.output_barcode, '')
 AND IFNULL(live.output_type_id, '') = IFNULL(old.output_type_id, '')
WHERE q.del_flag = 0
  AND IFNULL(q.production_step_output_id, '') <> ''
GROUP BY q.id
HAVING COUNT(*) = 1;

UPDATE biz_qc_work_order q
JOIN tmp_qc_output_relink m ON m.qc_id = q.id
SET q.production_step_output_id = m.new_output_id,
    q.update_time = NOW()
WHERE q.del_flag = 0
  AND q.production_step_output_id <> m.new_output_id;

DROP TEMPORARY TABLE IF EXISTS tmp_qc_output_relink;

-- ---------------------------------------------------------------------------
-- 2. 生产工单 / 缴费方案 / 质检短信 -> 协议样本或捐赠样本
--    同租户、同编码恰好一条有效样本
-- ---------------------------------------------------------------------------

DROP TEMPORARY TABLE IF EXISTS tmp_sample_ref_relink;
CREATE TEMPORARY TABLE tmp_sample_ref_relink (
  row_id VARCHAR(64) NOT NULL COLLATE utf8mb4_general_ci,
  new_sample_id VARCHAR(64) NOT NULL COLLATE utf8mb4_general_ci,
  PRIMARY KEY (row_id)
) ENGINE=MEMORY;

-- 生产工单 -> 采集协议样本
INSERT INTO tmp_sample_ref_relink (row_id, new_sample_id)
SELECT w.id, MIN(live.id)
FROM biz_production_work_order w
JOIN biz_protocol_sample old
  ON old.id COLLATE utf8mb4_general_ci = w.protocol_sample_id COLLATE utf8mb4_general_ci
 AND old.del_flag = 1
JOIN biz_protocol_sample live
  ON live.del_flag = 0
 AND live.tenant_no COLLATE utf8mb4_general_ci = old.tenant_no COLLATE utf8mb4_general_ci
 AND IFNULL(live.sample_code, '') COLLATE utf8mb4_general_ci = IFNULL(old.sample_code, '') COLLATE utf8mb4_general_ci
 AND IFNULL(live.sample_code, '') <> ''
 AND live.id COLLATE utf8mb4_general_ci <> old.id COLLATE utf8mb4_general_ci
WHERE w.del_flag = 0
GROUP BY w.id
HAVING COUNT(*) = 1;

UPDATE biz_production_work_order w
JOIN tmp_sample_ref_relink m
  ON m.row_id COLLATE utf8mb4_general_ci = w.id COLLATE utf8mb4_general_ci
SET w.protocol_sample_id = m.new_sample_id,
    w.update_time = NOW()
WHERE w.del_flag = 0;

DELETE FROM tmp_sample_ref_relink;

-- 生产工单 -> 科研捐赠样本（工单的 protocol_sample_id 存捐赠样本 ID）
INSERT INTO tmp_sample_ref_relink (row_id, new_sample_id)
SELECT w.id, MIN(live.id)
FROM biz_production_work_order w
JOIN biz_research_donation_sample old
  ON old.id COLLATE utf8mb4_general_ci = w.protocol_sample_id COLLATE utf8mb4_general_ci
 AND old.del_flag = 1
JOIN biz_research_donation_sample live
  ON live.del_flag = 0
 AND live.tenant_no COLLATE utf8mb4_general_ci = old.tenant_no COLLATE utf8mb4_general_ci
 AND IFNULL(live.sample_code, '') COLLATE utf8mb4_general_ci = IFNULL(old.sample_code, '') COLLATE utf8mb4_general_ci
 AND IFNULL(live.sample_code, '') <> ''
 AND live.id COLLATE utf8mb4_general_ci <> old.id COLLATE utf8mb4_general_ci
WHERE w.del_flag = 0
GROUP BY w.id
HAVING COUNT(*) = 1;

UPDATE biz_production_work_order w
JOIN tmp_sample_ref_relink m
  ON m.row_id COLLATE utf8mb4_general_ci = w.id COLLATE utf8mb4_general_ci
SET w.protocol_sample_id = m.new_sample_id,
    w.update_time = NOW()
WHERE w.del_flag = 0;

DELETE FROM tmp_sample_ref_relink;

-- 按样本缴费方案。该备份中可唯一匹配的行数为 0，下面语句不会改数据。
INSERT INTO tmp_sample_ref_relink (row_id, new_sample_id)
SELECT p.id, MIN(live.id)
FROM biz_protocol_sample_payment p
JOIN biz_protocol_sample old
  ON old.id COLLATE utf8mb4_general_ci = p.protocol_sample_id COLLATE utf8mb4_general_ci
 AND old.del_flag = 1
JOIN biz_protocol_sample live
  ON live.del_flag = 0
 AND live.tenant_no COLLATE utf8mb4_general_ci = old.tenant_no COLLATE utf8mb4_general_ci
 AND IFNULL(live.sample_code, '') COLLATE utf8mb4_general_ci = IFNULL(old.sample_code, '') COLLATE utf8mb4_general_ci
 AND IFNULL(live.sample_code, '') <> ''
 AND live.id COLLATE utf8mb4_general_ci <> old.id COLLATE utf8mb4_general_ci
WHERE p.del_flag = 0
GROUP BY p.id
HAVING COUNT(*) = 1;

UPDATE biz_protocol_sample_payment p
JOIN tmp_sample_ref_relink m
  ON m.row_id COLLATE utf8mb4_general_ci = p.id COLLATE utf8mb4_general_ci
SET p.protocol_sample_id = m.new_sample_id,
    p.update_time = NOW()
WHERE p.del_flag = 0;

DELETE FROM tmp_sample_ref_relink;

-- 质检短信。del_flag 是字符 '0'
INSERT INTO tmp_sample_ref_relink (row_id, new_sample_id)
SELECT s.id, MIN(live.id)
FROM biz_qc_sms_notification s
JOIN biz_protocol_sample old
  ON old.id COLLATE utf8mb4_general_ci = s.protocol_sample_id COLLATE utf8mb4_general_ci
 AND old.del_flag = 1
JOIN biz_protocol_sample live
  ON live.del_flag = 0
 AND live.tenant_no COLLATE utf8mb4_general_ci = old.tenant_no COLLATE utf8mb4_general_ci
 AND IFNULL(live.sample_code, '') COLLATE utf8mb4_general_ci = IFNULL(old.sample_code, '') COLLATE utf8mb4_general_ci
 AND IFNULL(live.sample_code, '') <> ''
 AND live.id COLLATE utf8mb4_general_ci <> old.id COLLATE utf8mb4_general_ci
WHERE s.del_flag = '0'
GROUP BY s.id
HAVING COUNT(*) = 1;

UPDATE biz_qc_sms_notification s
JOIN tmp_sample_ref_relink m
  ON m.row_id COLLATE utf8mb4_general_ci = s.id COLLATE utf8mb4_general_ci
SET s.protocol_sample_id = m.new_sample_id,
    s.update_time = NOW()
WHERE s.del_flag = '0';

DROP TEMPORARY TABLE IF EXISTS tmp_sample_ref_relink;