zkxw/docs/sql/migrations/2026-10-26-fix-marina-deng-sample-code-swap.sql

-- =============================================================================
-- 数据修复：马丽娜 / 邓诗婷 样本编码错乱回正
-- 基准备份：zkxw_2026-10-26_092540.sql.gz（执行前请再备份当前库）
--
-- 目标终态：
--   马丽娜 协议 XBCC20261008000001 → 样本码 ZKZX34000964
--     + 原质检/生产/接收/入库审核/缴费（曾被误改到 1007）一并回到 964
--   邓诗婷 协议 XBCC20261026000001 → 样本码 ZKZX34000842
--     （不含马丽娜的质检生产）
--
-- 不改：lab_storage_code、biz_cell_product.product_code、短信正文、软删历史行
--
-- 执行方式（MySQL 5.7）：
--   1) 先跑「一、执行前校验」全部 SELECT，确认与预期一致
--   2) 再跑「二、事务修复」；任一步报错会 ROLLBACK
--   3) 跑「三、执行后校验」
-- =============================================================================

-- ---------------------------------------------------------------------------
-- 常量（与 09:25 备份一致；若线上 ID 已变，以校验 SELECT 为准再改）
-- ---------------------------------------------------------------------------
-- 租户
-- SET @tenant = 'zkxw';
-- 马丽娜协议 / 活跃样本 / 编码池
-- 6A7CBB85EA024AF08061FD9AFB8DF6EC
-- EC3B60D2880544D9909980D36FF9704E  (当前活跃，码=842)
-- BBC01CCFDCA14E7BBE3ED34CE1B1DE47  (池 964)
-- 7C4C8FEDE210453F9D2F357D4F5A46A4  (池 842)
-- 邓诗婷协议 / 活跃样本 / 编码池
-- AAF11BCC7A584B09AFCE17AA8F7B7CD4
-- 21D2D4A063064E0B850F5C807F964079  (当前活跃，码=1007)
-- 817D230359EE4DCEA0F0CB532F099F12  (池 1007)
-- 下游（属马丽娜，当前 sample_code=1007）
-- reception 0E69E16E7E9C4B3AA9D894622BBE237B
-- qc_wo     019FC51312BB4225901014BE90A3AB2E
-- prod_wo   92F20C8B731E474985BB2C6D74A2BA3A

/* =============================================================================
   一、执行前校验（只读）—— 全部应对上，再执行第二节
   ============================================================================= */

SELECT '【预检1】活跃协议样本（期望：马丽娜=842 一条；邓诗婷=1007 一条；964 无活跃）' AS step;
SELECT ps.id,
       ps.sample_code,
       ps.sample_code_id,
       ps.collection_protocol_id,
       cp.protocol_no,
       ppc.mother_name,
       ps.del_flag,
       ps.update_time
  FROM biz_protocol_sample ps
  JOIN biz_collection_protocol cp ON cp.id = ps.collection_protocol_id AND cp.del_flag = 0
  LEFT JOIN biz_protocol_party_customer ppc
         ON ppc.collection_protocol_id = ps.collection_protocol_id AND ppc.del_flag = 0
 WHERE ps.tenant_no = 'zkxw'
   AND ps.del_flag = 0
   AND ps.sample_code IN ('ZKZX34000842', 'ZKZX34000964', 'ZKZX34001007')
 ORDER BY ps.sample_code, ps.create_time;

SELECT '【预检2】马丽娜下游当前码（期望均为 1007，协议仍为马丽娜）' AS step;
SELECT 'reception' AS tbl, id, sample_code, protocol_sample_id, collection_protocol_id, lab_storage_code
  FROM biz_sample_reception
 WHERE tenant_no = 'zkxw' AND del_flag = 0
   AND collection_protocol_id = '6A7CBB85EA024AF08061FD9AFB8DF6EC'
UNION ALL
SELECT 'qc_wo', id, sample_code, protocol_sample_id, collection_protocol_id, work_order_no
  FROM biz_qc_work_order
 WHERE tenant_no = 'zkxw' AND del_flag = 0
   AND collection_protocol_id = '6A7CBB85EA024AF08061FD9AFB8DF6EC'
UNION ALL
SELECT 'prod_wo', id, sample_code, protocol_sample_id, collection_protocol_id, work_order_no
  FROM biz_production_work_order
 WHERE tenant_no = 'zkxw' AND del_flag = 0
   AND collection_protocol_id = '6A7CBB85EA024AF08061FD9AFB8DF6EC'
UNION ALL
SELECT 'storage_rev', id, sample_code, NULL, protocol_id, protocol_no
  FROM biz_storage_release_review
 WHERE tenant_no = 'zkxw' AND del_flag = 0
   AND protocol_id = '6A7CBB85EA024AF08061FD9AFB8DF6EC';

SELECT '【预检3】码分布计数（活跃）' AS step;
SELECT sample_code, COUNT(*) AS cnt
  FROM biz_protocol_sample
 WHERE tenant_no = 'zkxw' AND del_flag = 0
   AND sample_code IN ('ZKZX34000842', 'ZKZX34000964', 'ZKZX34001007')
 GROUP BY sample_code;

SELECT sample_code, COUNT(*) AS qc_task_cnt
  FROM biz_qc_task
 WHERE tenant_no = 'zkxw' AND del_flag = 0
   AND sample_code IN ('ZKZX34000842', 'ZKZX34000964', 'ZKZX34001007')
 GROUP BY sample_code;

SELECT sample_code, collection_protocol_id, COUNT(*) AS pay_cnt
  FROM biz_protocol_sample_payment
 WHERE tenant_no = 'zkxw' AND del_flag = 0
   AND sample_code IN ('ZKZX34000842', 'ZKZX34000964', 'ZKZX34001007')
 GROUP BY sample_code, collection_protocol_id;

/* =============================================================================
   二、事务修复
   A 马丽娜 842 → 964（仅协议侧；下游此时在 1007，不会被带走）
   B 马丽娜协议下 1007 → 964（质检/生产/缴费等，按协议 ID 定向）
   C 邓诗婷 1007 → 842
   ============================================================================= */

START TRANSACTION;

-- 说明：库内同时存在 utf8mb4_unicode_ci / utf8mb4_general_ci 列。
-- 字符串比较一律用 BINARY，避免会话变量 collation 混用报错。
SET NAMES utf8mb4;
SET @now      := NOW();
SET @operator := 'data_fix_20261026';

SET @marina_sample_id := NULL;
SET @deng_sample_id := NULL;

SELECT id INTO @marina_sample_id
  FROM biz_protocol_sample
 WHERE del_flag = 0
   AND BINARY tenant_no = BINARY 'zkxw'
   AND BINARY collection_protocol_id = BINARY '6A7CBB85EA024AF08061FD9AFB8DF6EC'
   AND BINARY sample_code = BINARY 'ZKZX34000842'
 LIMIT 1;

SELECT id INTO @deng_sample_id
  FROM biz_protocol_sample
 WHERE del_flag = 0
   AND BINARY tenant_no = BINARY 'zkxw'
   AND BINARY collection_protocol_id = BINARY 'AAF11BCC7A584B09AFCE17AA8F7B7CD4'
   AND BINARY sample_code = BINARY 'ZKZX34001007'
 LIMIT 1;

SET @gate := 0;
SELECT IF(@marina_sample_id IS NULL OR @marina_sample_id = '', 1, 0)
     + IF(@deng_sample_id IS NULL OR @deng_sample_id = '', 1, 0)
     + (SELECT COUNT(*) FROM biz_protocol_sample
         WHERE del_flag = 0 AND BINARY tenant_no = BINARY 'zkxw'
           AND BINARY sample_code = BINARY 'ZKZX34000964')
     + (SELECT COUNT(*) FROM biz_protocol_sample
         WHERE del_flag = 0 AND BINARY tenant_no = BINARY 'zkxw'
           AND BINARY sample_code = BINARY 'ZKZX34000842'
           AND id <> @marina_sample_id)
  INTO @gate;

SELECT @marina_sample_id AS marina_sample_id,
       @deng_sample_id   AS deng_sample_id,
       @gate             AS gate_should_be_0;

SET @__abort := (0 + 1) / IF(@gate = 0, 1, 0);

/* ---------- A. 马丽娜活跃样本 842 → 964 + 编码池切换 ---------- */
UPDATE biz_protocol_sample
   SET sample_code    = 'ZKZX34000964',
       sample_code_id = 'BBC01CCFDCA14E7BBE3ED34CE1B1DE47',
       update_by      = @operator,
       update_time    = @now
 WHERE id = @marina_sample_id
   AND del_flag = 0
   AND BINARY sample_code = BINARY 'ZKZX34000842';

UPDATE biz_sample_code
   SET issued = 1, update_by = @operator, update_time = @now
 WHERE BINARY id = BINARY 'BBC01CCFDCA14E7BBE3ED34CE1B1DE47'
   AND BINARY tenant_no = BINARY 'zkxw' AND del_flag = 0;

UPDATE biz_sample_code
   SET issued = 0, update_by = @operator, update_time = @now
 WHERE BINARY id = BINARY '7C4C8FEDE210453F9D2F357D4F5A46A4'
   AND BINARY tenant_no = BINARY 'zkxw' AND del_flag = 0
   AND NOT EXISTS (
         SELECT 1 FROM biz_protocol_sample
          WHERE del_flag = 0 AND BINARY tenant_no = BINARY 'zkxw'
            AND BINARY sample_code_id = BINARY '7C4C8FEDE210453F9D2F357D4F5A46A4'
            AND id <> @marina_sample_id
       );

UPDATE biz_protocol_sample_transport
   SET sample_code = 'ZKZX34000964',
       sample_id   = @marina_sample_id,
       update_by   = @operator,
       update_time = @now
 WHERE del_flag = 0
   AND BINARY tenant_no = BINARY 'zkxw'
   AND BINARY collection_protocol_id = BINARY '6A7CBB85EA024AF08061FD9AFB8DF6EC'
   AND BINARY sample_code = BINARY 'ZKZX34000842';

INSERT INTO biz_sample_code_correction_log (
  id, reception_source, old_sample_code, new_sample_code, impact_json, reason,
  tenant_no, order_num, remark, create_by, create_dept_id, create_time, update_by, update_time, del_flag
) VALUES (
  REPLACE(UUID(), '-', ''),
  'cell_storage', 'ZKZX34000842', 'ZKZX34000964',
  JSON_OBJECT(
    'biz_protocol_sample', 1,
    'biz_sample_code', 1,
    'biz_protocol_sample_transport', 1,
    'note', 'stepA_marina_protocol_only'
  ),
  '数据修复：马丽娜协议样本 842→964（下游另按协议定向迁移）',
  'zkxw', 0, 'fix_marina_deng_20261026_stepA',
  @operator, '', @now, @operator, @now, 0
);

/* ---------- B. 马丽娜协议下 1007 → 964（质检/生产/缴费） ---------- */
UPDATE biz_sample_reception
   SET sample_code         = 'ZKZX34000964',
       protocol_sample_id  = @marina_sample_id,
       update_by           = @operator,
       update_time         = @now
 WHERE del_flag = 0
   AND BINARY tenant_no = BINARY 'zkxw'
   AND BINARY collection_protocol_id = BINARY '6A7CBB85EA024AF08061FD9AFB8DF6EC'
   AND BINARY sample_code = BINARY 'ZKZX34001007';

UPDATE biz_qc_work_order
   SET sample_code        = 'ZKZX34000964',
       protocol_sample_id = @marina_sample_id,
       update_by          = @operator,
       update_time        = @now
 WHERE del_flag = 0
   AND BINARY tenant_no = BINARY 'zkxw'
   AND BINARY collection_protocol_id = BINARY '6A7CBB85EA024AF08061FD9AFB8DF6EC'
   AND BINARY sample_code = BINARY 'ZKZX34001007';

UPDATE biz_qc_task t
   JOIN biz_qc_work_order w
     ON w.id = t.work_order_id
    AND w.del_flag = 0
    AND BINARY w.tenant_no = BINARY 'zkxw'
    AND BINARY w.collection_protocol_id = BINARY '6A7CBB85EA024AF08061FD9AFB8DF6EC'
   SET t.sample_code = 'ZKZX34000964',
       t.update_by   = @operator,
       t.update_time = @now
 WHERE t.del_flag = 0
   AND BINARY t.tenant_no = BINARY 'zkxw'
   AND BINARY t.sample_code = BINARY 'ZKZX34001007';

UPDATE biz_production_work_order
   SET sample_code        = 'ZKZX34000964',
       protocol_sample_id = @marina_sample_id,
       update_by          = @operator,
       update_time        = @now
 WHERE del_flag = 0
   AND BINARY tenant_no = BINARY 'zkxw'
   AND BINARY collection_protocol_id = BINARY '6A7CBB85EA024AF08061FD9AFB8DF6EC'
   AND BINARY sample_code = BINARY 'ZKZX34001007';

UPDATE biz_sample_production_reception pr
   JOIN biz_sample_reception r
     ON r.id = pr.reception_id
    AND r.del_flag = 0
    AND BINARY r.tenant_no = BINARY 'zkxw'
    AND BINARY r.collection_protocol_id = BINARY '6A7CBB85EA024AF08061FD9AFB8DF6EC'
   SET pr.sample_code = 'ZKZX34000964',
       pr.update_by   = @operator,
       pr.update_time = @now
 WHERE pr.del_flag = 0
   AND BINARY pr.tenant_no = BINARY 'zkxw'
   AND BINARY pr.sample_code = BINARY 'ZKZX34001007';

UPDATE biz_production_task_input ti
   JOIN biz_production_task pt
     ON pt.id = ti.task_id AND pt.del_flag = 0 AND BINARY pt.tenant_no = BINARY 'zkxw'
   JOIN biz_production_work_order pw
     ON pw.id = pt.work_order_id
    AND pw.del_flag = 0
    AND BINARY pw.tenant_no = BINARY 'zkxw'
    AND BINARY pw.collection_protocol_id = BINARY '6A7CBB85EA024AF08061FD9AFB8DF6EC'
   SET ti.input_code  = 'ZKZX34000964',
       ti.update_by   = @operator,
       ti.update_time = @now
 WHERE ti.del_flag = 0
   AND BINARY ti.tenant_no = BINARY 'zkxw'
   AND BINARY ti.input_type = BINARY 'sample'
   AND BINARY ti.input_code = BINARY 'ZKZX34001007';

UPDATE biz_storage_release_review
   SET sample_code = 'ZKZX34000964',
       sample_name = 'ZKZX34000964',
       update_by   = @operator,
       update_time = @now
 WHERE del_flag = 0
   AND BINARY tenant_no = BINARY 'zkxw'
   AND BINARY protocol_id = BINARY '6A7CBB85EA024AF08061FD9AFB8DF6EC'
   AND BINARY sample_code = BINARY 'ZKZX34001007';

UPDATE biz_protocol_sample_payment
   SET sample_code        = 'ZKZX34000964',
       protocol_sample_id = @marina_sample_id,
       update_by          = @operator,
       update_time        = @now
 WHERE del_flag = 0
   AND BINARY tenant_no = BINARY 'zkxw'
   AND BINARY collection_protocol_id = BINARY '6A7CBB85EA024AF08061FD9AFB8DF6EC'
   AND BINARY sample_code = BINARY 'ZKZX34001007';

UPDATE biz_protocol_payment_schedule
   SET sample_code = 'ZKZX34000964',
       update_by   = @operator,
       update_time = @now
 WHERE del_flag = 0
   AND BINARY tenant_no = BINARY 'zkxw'
   AND BINARY collection_protocol_id = BINARY '6A7CBB85EA024AF08061FD9AFB8DF6EC'
   AND BINARY sample_code = BINARY 'ZKZX34001007';

UPDATE biz_protocol_payment_transfer
   SET sample_code = 'ZKZX34000964',
       update_by   = @operator,
       update_time = @now
 WHERE del_flag = 0
   AND BINARY tenant_no = BINARY 'zkxw'
   AND BINARY collection_protocol_id = BINARY '6A7CBB85EA024AF08061FD9AFB8DF6EC'
   AND BINARY sample_code = BINARY 'ZKZX34001007';

INSERT INTO biz_sample_code_correction_log (
  id, reception_source, old_sample_code, new_sample_code, impact_json, reason,
  tenant_no, order_num, remark, create_by, create_dept_id, create_time, update_by, update_time, del_flag
) VALUES (
  REPLACE(UUID(), '-', ''),
  'cell_storage', 'ZKZX34001007', 'ZKZX34000964',
  JSON_OBJECT('note', 'stepB_marina_downstream_by_protocol'),
  '数据修复：马丽娜下游质检/生产/缴费 1007→964（按协议定向，不含邓诗婷）',
  'zkxw', 0, 'fix_marina_deng_20261026_stepB',
  @operator, '', @now, @operator, @now, 0
);

/* ---------- C. 邓诗婷 1007 → 842 ---------- */
SELECT COUNT(*) INTO @deng_1007_left_qc
  FROM biz_qc_work_order
 WHERE del_flag = 0 AND BINARY tenant_no = BINARY 'zkxw'
   AND BINARY sample_code = BINARY 'ZKZX34001007';

SELECT COUNT(*) INTO @deng_1007_left_prod
  FROM biz_production_work_order
 WHERE del_flag = 0 AND BINARY tenant_no = BINARY 'zkxw'
   AND BINARY sample_code = BINARY 'ZKZX34001007';

SET @gate2 := IF(@deng_1007_left_qc + @deng_1007_left_prod = 0, 0, 1)
            + (SELECT COUNT(*) FROM biz_protocol_sample
                WHERE del_flag = 0 AND BINARY tenant_no = BINARY 'zkxw'
                  AND BINARY sample_code = BINARY 'ZKZX34000842');
SELECT @deng_1007_left_qc AS leftover_qc_on_1007,
       @deng_1007_left_prod AS leftover_prod_on_1007,
       @gate2 AS gate2_should_be_0;
SET @__abort2 := (0 + 1) / IF(@gate2 = 0, 1, 0);

UPDATE biz_protocol_sample
   SET sample_code    = 'ZKZX34000842',
       sample_code_id = '7C4C8FEDE210453F9D2F357D4F5A46A4',
       update_by      = @operator,
       update_time    = @now
 WHERE id = @deng_sample_id
   AND del_flag = 0
   AND BINARY sample_code = BINARY 'ZKZX34001007';

UPDATE biz_sample_code
   SET issued = 1, update_by = @operator, update_time = @now
 WHERE BINARY id = BINARY '7C4C8FEDE210453F9D2F357D4F5A46A4'
   AND BINARY tenant_no = BINARY 'zkxw' AND del_flag = 0;

UPDATE biz_sample_code
   SET issued = 0, update_by = @operator, update_time = @now
 WHERE BINARY id = BINARY '817D230359EE4DCEA0F0CB532F099F12'
   AND BINARY tenant_no = BINARY 'zkxw' AND del_flag = 0
   AND NOT EXISTS (
         SELECT 1 FROM biz_protocol_sample
          WHERE del_flag = 0 AND BINARY tenant_no = BINARY 'zkxw'
            AND BINARY sample_code_id = BINARY '817D230359EE4DCEA0F0CB532F099F12'
       );

UPDATE biz_protocol_sample_payment
   SET sample_code        = 'ZKZX34000842',
       protocol_sample_id = @deng_sample_id,
       update_by          = @operator,
       update_time        = @now
 WHERE del_flag = 0
   AND BINARY tenant_no = BINARY 'zkxw'
   AND BINARY collection_protocol_id = BINARY 'AAF11BCC7A584B09AFCE17AA8F7B7CD4'
   AND BINARY sample_code = BINARY 'ZKZX34001007';

UPDATE biz_protocol_payment_schedule
   SET sample_code = 'ZKZX34000842',
       update_by   = @operator,
       update_time = @now
 WHERE del_flag = 0
   AND BINARY tenant_no = BINARY 'zkxw'
   AND BINARY collection_protocol_id = BINARY 'AAF11BCC7A584B09AFCE17AA8F7B7CD4'
   AND BINARY sample_code = BINARY 'ZKZX34001007';

UPDATE biz_protocol_payment_transfer
   SET sample_code = 'ZKZX34000842',
       update_by   = @operator,
       update_time = @now
 WHERE del_flag = 0
   AND BINARY tenant_no = BINARY 'zkxw'
   AND BINARY collection_protocol_id = BINARY 'AAF11BCC7A584B09AFCE17AA8F7B7CD4'
   AND BINARY sample_code = BINARY 'ZKZX34001007';

UPDATE biz_protocol_sample_transport
   SET sample_code = 'ZKZX34000842',
       sample_id   = @deng_sample_id,
       update_by   = @operator,
       update_time = @now
 WHERE del_flag = 0
   AND BINARY tenant_no = BINARY 'zkxw'
   AND BINARY collection_protocol_id = BINARY 'AAF11BCC7A584B09AFCE17AA8F7B7CD4'
   AND BINARY sample_code = BINARY 'ZKZX34001007';

INSERT INTO biz_sample_code_correction_log (
  id, reception_source, old_sample_code, new_sample_code, impact_json, reason,
  tenant_no, order_num, remark, create_by, create_dept_id, create_time, update_by, update_time, del_flag
) VALUES (
  REPLACE(UUID(), '-', ''),
  'cell_storage', 'ZKZX34001007', 'ZKZX34000842',
  JSON_OBJECT('note', 'stepC_deng_1007_to_842'),
  '数据修复：邓诗婷协议样本 1007→842',
  'zkxw', 0, 'fix_marina_deng_20261026_stepC',
  @operator, '', @now, @operator, @now, 0
);

COMMIT;

/* =============================================================================
   三、执行后校验（期望）
   - 马丽娜活跃样本 = 964；质检/生产 sample_code = 964；protocol_sample_id = 马丽娜活跃样本
   - 邓诗婷活跃样本 = 842；无 ZJ202610090001 / SC202610090001
   - 1007 无活跃协议样本
   ============================================================================= */

SELECT '【验1】活跃样本归属' AS step;
SELECT ps.sample_code, cp.protocol_no, ppc.mother_name, ps.id AS protocol_sample_id
  FROM biz_protocol_sample ps
  JOIN biz_collection_protocol cp ON cp.id = ps.collection_protocol_id
  LEFT JOIN biz_protocol_party_customer ppc
         ON ppc.collection_protocol_id = ps.collection_protocol_id AND ppc.del_flag = 0
 WHERE ps.tenant_no = 'zkxw' AND ps.del_flag = 0
   AND ps.sample_code IN ('ZKZX34000842', 'ZKZX34000964', 'ZKZX34001007')
 ORDER BY ps.sample_code;

SELECT '【验2】马丽娜下游' AS step;
SELECT 'qc_wo' AS tbl, work_order_no, sample_code, protocol_sample_id, collection_protocol_id, conclusion
  FROM biz_qc_work_order
 WHERE id = '019FC51312BB4225901014BE90A3AB2E'
UNION ALL
SELECT 'prod_wo', work_order_no, sample_code, protocol_sample_id, collection_protocol_id, work_order_status
  FROM biz_production_work_order
 WHERE id = '92F20C8B731E474985BB2C6D74A2BA3A'
UNION ALL
SELECT 'reception', id, sample_code, protocol_sample_id, collection_protocol_id, lab_storage_code
  FROM biz_sample_reception
 WHERE id = '0E69E16E7E9C4B3AA9D894622BBE237B';

SELECT '【验3】三码活跃样本数（期望 842=1, 964=1, 1007=0）' AS step;
SELECT sample_code, COUNT(*) AS cnt
  FROM biz_protocol_sample
 WHERE tenant_no = 'zkxw' AND del_flag = 0
   AND sample_code IN ('ZKZX34000842', 'ZKZX34000964', 'ZKZX34001007')
 GROUP BY sample_code;

SELECT '【验4】邓诗婷协议下不应有该质检/生产工单' AS step;
SELECT COUNT(*) AS deng_should_be_0
  FROM biz_qc_work_order
 WHERE tenant_no = 'zkxw' AND del_flag = 0
   AND collection_protocol_id = 'AAF11BCC7A584B09AFCE17AA8F7B7CD4';

SELECT COUNT(*) AS leftover_1007_qc_should_be_0
  FROM biz_qc_task
 WHERE tenant_no = 'zkxw' AND del_flag = 0 AND sample_code = 'ZKZX34001007';