AiDOPWarehouse/doc/plan/sql/S1S4_UAT_迁移至AIDOP租户_20260605.sql

-- 将已导入的 S1-S4 UAT 数据从 tenant_id=1300000000001 迁至 AIDOP 租户 797403760988229
-- 状态：2026-06-05 已在 aidopdev 执行成功（crm_seorder 50 条 @797403760988229）
-- 幂等：仅更新仍留在旧租户且带 S1S4_UAT 批次标记的数据

SET @old_tid  := 1300000000001;
SET @new_tid  := 797403760988229;
SET @factory  := 797403760988229;

START TRANSACTION;

DROP TEMPORARY TABLE IF EXISTS tmp_uat_bills;
CREATE TEMPORARY TABLE tmp_uat_bills AS
SELECT bill_no, Id AS so_id
FROM crm_seorder
WHERE tenant_id = @old_tid
  AND bill_from LIKE '%S1S4_UAT%';

DROP TEMPORARY TABLE IF EXISTS tmp_uat_pos;
CREATE TEMPORARY TABLE tmp_uat_pos AS
SELECT DISTINCT PurOrd
FROM PurOrdMaster
WHERE tenant_id = @old_tid
  AND (Remark LIKE '%S1S4_UAT%' OR SalesOrd IN (SELECT bill_no FROM tmp_uat_bills));

DROP TEMPORARY TABLE IF EXISTS tmp_uat_mo;
CREATE TEMPORARY TABLE tmp_uat_mo AS
SELECT DISTINCT mo.Id AS mo_id, mo.morder_no
FROM mes_morder mo
INNER JOIN mes_moentry me ON me.moentry_moid = mo.Id AND me.tenant_id = @old_tid
INNER JOIN crm_seorderentry se ON se.Id = me.soentry_id
WHERE se.bill_no IN (SELECT bill_no FROM tmp_uat_bills)
  AND mo.tenant_id = @old_tid;

UPDATE crm_seorder
SET tenant_id = @new_tid, factory_id = @factory
WHERE tenant_id = @old_tid
  AND bill_from LIKE '%S1S4_UAT%';

UPDATE crm_seorderentry e
INNER JOIN tmp_uat_bills b ON e.bill_no = b.bill_no
SET e.tenant_id = @new_tid, e.factory_id = @factory
WHERE e.tenant_id = @old_tid;

UPDATE mes_moentry me
INNER JOIN crm_seorderentry se ON se.Id = me.soentry_id
INNER JOIN tmp_uat_bills b ON se.bill_no = b.bill_no
SET me.tenant_id = @new_tid, me.factory_id = @factory
WHERE me.tenant_id = @old_tid;

UPDATE mes_morder mo
INNER JOIN tmp_uat_mo t ON mo.Id = t.mo_id
SET mo.tenant_id = @new_tid, mo.factory_id = @factory
WHERE mo.tenant_id = @old_tid;

UPDATE WorkOrdMaster w
SET w.tenant_id = @new_tid
WHERE w.tenant_id = @old_tid
  AND (w.SalesJob IN (SELECT bill_no FROM tmp_uat_bills)
       OR w.WorkOrd IN (SELECT morder_no FROM tmp_uat_mo));

UPDATE srm_pr_main
SET tenant_id = @new_tid, factory_id = @factory
WHERE tenant_id = @old_tid
  AND pr_billno LIKE 'PR-UAT-%';

UPDATE PurOrdMaster
SET tenant_id = @new_tid
WHERE tenant_id = @old_tid
  AND PurOrd IN (SELECT PurOrd FROM tmp_uat_pos);

UPDATE PurOrdDetail pd
INNER JOIN tmp_uat_pos p ON pd.PurOrd = p.PurOrd
SET pd.tenant_id = @new_tid
WHERE pd.tenant_id = @old_tid;

UPDATE srm_polist_ds
SET tenant_id = @new_tid
WHERE tenant_id = @old_tid
  AND ponumber LIKE 'PO-UAT-%';

UPDATE mdp_std_so
SET tenant_id = @new_tid
WHERE tenant_id = @old_tid
  AND order_no IN (SELECT bill_no FROM tmp_uat_bills);

UPDATE dwd_ship_trans
SET tenant_id = @new_tid
WHERE tenant_id = @old_tid
  AND order_no IN (SELECT bill_no FROM tmp_uat_bills);

UPDATE dwd_order_schedule_trans
SET tenant_id = @new_tid
WHERE tenant_id = @old_tid
  AND sales_order_no IN (SELECT bill_no FROM tmp_uat_bills);

COMMIT;

SELECT tenant_id, COUNT(*) AS so_cnt
FROM crm_seorder
WHERE bill_from LIKE '%S1S4_UAT%'
GROUP BY tenant_id;

SELECT COUNT(*) AS aidop_uat_so
FROM crm_seorder
WHERE tenant_id = @new_tid AND bill_from LIKE '%S1S4_UAT%';