antissoft / lvqianmeiye_ERP

-- =============================================================================
-- 生产环境修复脚本：金三角「幽灵战队」绑定 + 业绩 jsj_id 纠正
-- 日期：2026-08-06
--
-- 【背景】
-- 1) 删除金三角主表时未同步软删成员绑定 → ACTIVE 幽灵绑定
-- 2) 开单/耗卡/退卡业绩仍挂已删 jsj_id → 战队业绩漏算
-- 3) 部分业绩 jsj_id 为空，但当月已有有效战队（可选补全，默认关闭）
-- 4) 极少数同人同月多条 ACTIVE（主表都存在）
--
-- 【执行顺序（必须按此顺序）】
--   A. 只读预览（确认影响面）
--   B. 事务外做持久备份表
--   C. START TRANSACTION → UPDATE → 验证 → COMMIT 或 ROLLBACK
--
-- 【生产安全】
-- - 建议业务低峰执行；C2 会更新业绩表，可能短暂锁行。
-- - MySQL 的 CREATE TABLE 会隐式提交，故备份必须在事务外完成。
-- - 开启 GTID（enforce_gtid_consistency）时禁止 CREATE TABLE ... SELECT，备份用 CREATE LIKE + INSERT。
-- - 任一步行数异常 / C5 验证非 0：ROLLBACK，不要 COMMIT。
-- - 本脚本幂等：已修复数据不会再次命中。
-- - 无法归队的幽灵业绩（当月已无有效绑定）故意不改，避免错挂战队。
-- - C3 补空 jsj_id 默认注释：全量/大范围更新易锁等待，确认需要后再手工打开。
--
-- 【回滚参考（仅在误 COMMIT 且备份表仍在时）】
--   用 bak_* 表按 F_Id 回写原 jsj_id / 绑定状态；确认无误后再 DROP bak_*。
-- =============================================================================


-- #############################################################################
-- A. 只读预览（可单独先跑，不改数据）
-- #############################################################################

SELECT 'A1_orphan_active_binding' AS item, COUNT(*) AS cnt
FROM lq_jinsanjiao_user ju
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id
WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE' AND j.F_Id IS NULL;

SELECT ju.F_Id, ju.user_id, ju.user_name, ju.jsj_id, ju.F_Month, ju.is_leader, ju.F_CreatorTime
FROM lq_jinsanjiao_user ju
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id
WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE' AND j.F_Id IS NULL
ORDER BY ju.F_Month DESC, ju.user_name
LIMIT 200;

SELECT 'A2_orphan_kd' AS item, COUNT(*) AS cnt,
       COALESCE(SUM(CAST(k.jksyj AS DECIMAL(18,2))), 0) AS amt
FROM lq_kd_jksyj k
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = k.jsj_id
WHERE k.F_IsEffective = 1 AND k.jsj_id IS NOT NULL AND k.jsj_id <> '' AND j.F_Id IS NULL;

SELECT 'A3_orphan_xh' AS item, COUNT(*) AS cnt, COALESCE(SUM(x.jksyj), 0) AS amt
FROM lq_xh_jksyj x
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = x.F_jsjid
WHERE x.F_IsEffective = 1 AND x.F_jsjid IS NOT NULL AND x.F_jsjid <> '' AND j.F_Id IS NULL;

SELECT 'A4_orphan_tk' AS item, COUNT(*) AS cnt, COALESCE(SUM(h.jksyj), 0) AS amt
FROM lq_hytk_jksyj h
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = h.F_jsjid
WHERE (h.F_IsEffective = 1 OR h.F_IsEffective IS NULL)
  AND h.F_jsjid IS NOT NULL AND h.F_jsjid <> '' AND j.F_Id IS NULL;

SELECT 'A5_multi_active' AS item, COUNT(*) AS cnt
FROM (
  SELECT ju.user_id, ju.F_Month
  FROM lq_jinsanjiao_user ju
  INNER JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id AND j.yf = ju.F_Month
  WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE'
  GROUP BY ju.user_id, ju.F_Month
  HAVING COUNT(*) > 1
) t;

-- A6：可迁移的幽灵开单（当月存在有效绑定）— 预估 C2 影响面
SELECT 'A6_migratable_orphan_kd' AS item, COUNT(*) AS cnt,
       COALESCE(SUM(CAST(k.jksyj AS DECIMAL(18,2))), 0) AS amt
FROM lq_kd_jksyj k
LEFT JOIN lq_ycsd_jsj j_old ON j_old.F_Id = k.jsj_id
INNER JOIN (
  SELECT x.user_id, x.F_Month,
         SUBSTRING_INDEX(GROUP_CONCAT(x.jsj_id ORDER BY x.F_CreatorTime DESC, x.F_Id DESC), ',', 1) AS jsj_id
  FROM (
    SELECT ju.user_id, ju.F_Month, ju.jsj_id, ju.F_Id, ju.F_CreatorTime
    FROM lq_jinsanjiao_user ju
    INNER JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id AND j.yf = ju.F_Month
    WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE'
  ) x
  GROUP BY x.user_id, x.F_Month
) v ON v.user_id = COALESCE(NULLIF(k.jks, ''), NULLIF(k.jkszh, ''))
   AND v.F_Month = DATE_FORMAT(k.yjsj, '%Y%m')
WHERE k.F_IsEffective = 1
  AND k.jsj_id IS NOT NULL AND k.jsj_id <> ''
  AND j_old.F_Id IS NULL
  AND v.jsj_id <> k.jsj_id;


-- #############################################################################
-- B. 事务外持久备份（CREATE TABLE 会隐式提交，必须放在事务外）
--    GTID 环境禁止 CREATE TABLE ... SELECT，故拆成 CREATE LIKE + INSERT SELECT。
--    若表名已存在，请先改名或 DROP（确认无用后再删）
-- #############################################################################

CREATE TABLE bak_20260806_jinsanjiao_user_orphan LIKE lq_jinsanjiao_user;
INSERT INTO bak_20260806_jinsanjiao_user_orphan
SELECT ju.*
FROM lq_jinsanjiao_user ju
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id
WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE' AND j.F_Id IS NULL;

CREATE TABLE bak_20260806_kd_jksyj_orphan LIKE lq_kd_jksyj;
INSERT INTO bak_20260806_kd_jksyj_orphan
SELECT k.*
FROM lq_kd_jksyj k
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = k.jsj_id
WHERE k.F_IsEffective = 1 AND k.jsj_id IS NOT NULL AND k.jsj_id <> '' AND j.F_Id IS NULL;

CREATE TABLE bak_20260806_xh_jksyj_orphan LIKE lq_xh_jksyj;
INSERT INTO bak_20260806_xh_jksyj_orphan
SELECT x.*
FROM lq_xh_jksyj x
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = x.F_jsjid
WHERE x.F_IsEffective = 1 AND x.F_jsjid IS NOT NULL AND x.F_jsjid <> '' AND j.F_Id IS NULL;

CREATE TABLE bak_20260806_hytk_jksyj_orphan LIKE lq_hytk_jksyj;
INSERT INTO bak_20260806_hytk_jksyj_orphan
SELECT h.*
FROM lq_hytk_jksyj h
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = h.F_jsjid
WHERE (h.F_IsEffective = 1 OR h.F_IsEffective IS NULL)
  AND h.F_jsjid IS NOT NULL AND h.F_jsjid <> '' AND j.F_Id IS NULL;

SELECT 'bak_user' AS t, COUNT(*) AS cnt FROM bak_20260806_jinsanjiao_user_orphan
UNION ALL SELECT 'bak_kd', COUNT(*) FROM bak_20260806_kd_jksyj_orphan
UNION ALL SELECT 'bak_xh', COUNT(*) FROM bak_20260806_xh_jksyj_orphan
UNION ALL SELECT 'bak_tk', COUNT(*) FROM bak_20260806_hytk_jksyj_orphan;

-- 备份行数应与 A1~A4 预览一致；不一致则停止，不要进入 C 段。


-- #############################################################################
-- C. 事务内修复
-- #############################################################################

START TRANSACTION;

SET SESSION group_concat_max_len = 102400;

-- C0. 当月有效绑定（ACTIVE + 主表存在 + 同人同月取最新）
-- 注意：勿对同一 TEMPORARY TABLE 自关联（MySQL 会报 Can't reopen table）
DROP TEMPORARY TABLE IF EXISTS tmp_valid_jsj_binding;
CREATE TEMPORARY TABLE tmp_valid_jsj_binding AS
SELECT
  x.user_id,
  x.F_Month,
  SUBSTRING_INDEX(GROUP_CONCAT(x.jsj_id ORDER BY x.F_CreatorTime DESC, x.F_Id DESC), ',', 1) AS jsj_id
FROM (
  SELECT ju.user_id, ju.F_Month, ju.jsj_id, ju.F_Id, ju.F_CreatorTime
  FROM lq_jinsanjiao_user ju
  INNER JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id AND j.yf = ju.F_Month
  WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE'
) x
GROUP BY x.user_id, x.F_Month;


-- ---------- C1 软删幽灵 ACTIVE 绑定 ----------
UPDATE lq_jinsanjiao_user ju
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id
SET ju.F_DeleteMark = 1,
    ju.status = 'INACTIVE',
    ju.F_LastModifyTime = NOW(),
    ju.F_LastModifyUserId = 'prod-fix-20260806-orphan-binding'
WHERE ju.F_DeleteMark = 0
  AND ju.status = 'ACTIVE'
  AND j.F_Id IS NULL;

SELECT ROW_COUNT() AS c1_orphan_binding_updated;


-- ---------- C2 迁移幽灵业绩 → 有效战队（仅当月有有效绑定时） ----------
-- 开单
UPDATE lq_kd_jksyj k
LEFT JOIN lq_ycsd_jsj j_old ON j_old.F_Id = k.jsj_id
INNER JOIN tmp_valid_jsj_binding v
  ON v.user_id = COALESCE(NULLIF(k.jks, ''), NULLIF(k.jkszh, ''))
 AND v.F_Month = DATE_FORMAT(k.yjsj, '%Y%m')
SET k.jsj_id = v.jsj_id
WHERE k.F_IsEffective = 1
  AND k.jsj_id IS NOT NULL AND k.jsj_id <> ''
  AND j_old.F_Id IS NULL
  AND v.jsj_id <> k.jsj_id;

SELECT ROW_COUNT() AS c2_kd_updated;

-- 耗卡
UPDATE lq_xh_jksyj x
LEFT JOIN lq_ycsd_jsj j_old ON j_old.F_Id = x.F_jsjid
INNER JOIN tmp_valid_jsj_binding v
  ON v.user_id = COALESCE(NULLIF(x.jks, ''), NULLIF(x.jkszh, ''))
 AND v.F_Month = DATE_FORMAT(x.yjsj, '%Y%m')
SET x.F_jsjid = v.jsj_id
WHERE x.F_IsEffective = 1
  AND x.F_jsjid IS NOT NULL AND x.F_jsjid <> ''
  AND j_old.F_Id IS NULL
  AND v.jsj_id <> x.F_jsjid;

SELECT ROW_COUNT() AS c2_xh_updated;

-- 退卡
UPDATE lq_hytk_jksyj h
LEFT JOIN lq_ycsd_jsj j_old ON j_old.F_Id = h.F_jsjid
INNER JOIN tmp_valid_jsj_binding v
  ON v.user_id = COALESCE(NULLIF(h.jks, ''), NULLIF(h.jkszh, ''))
 AND v.F_Month = DATE_FORMAT(h.tksj, '%Y%m')
SET h.F_jsjid = v.jsj_id
WHERE (h.F_IsEffective = 1 OR h.F_IsEffective IS NULL)
  AND h.F_jsjid IS NOT NULL AND h.F_jsjid <> ''
  AND j_old.F_Id IS NULL
  AND v.jsj_id <> h.F_jsjid;

SELECT ROW_COUNT() AS c2_tk_updated;


-- ---------- C3 补全空 jsj_id（默认关闭；需要时去掉注释，建议仅近 3 个月） ----------
-- SET @perf_from := DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH), '%Y-%m-01');
--
-- SELECT COUNT(*) AS kd_empty_preview
-- FROM lq_kd_jksyj k
-- INNER JOIN tmp_valid_jsj_binding v
--   ON v.user_id = COALESCE(NULLIF(k.jks, ''), NULLIF(k.jkszh, ''))
--  AND v.F_Month = DATE_FORMAT(k.yjsj, '%Y%m')
-- WHERE k.F_IsEffective = 1
--   AND k.yjsj >= @perf_from
--   AND (k.jsj_id IS NULL OR k.jsj_id = '');
--
-- UPDATE lq_kd_jksyj k
-- INNER JOIN tmp_valid_jsj_binding v
--   ON v.user_id = COALESCE(NULLIF(k.jks, ''), NULLIF(k.jkszh, ''))
--  AND v.F_Month = DATE_FORMAT(k.yjsj, '%Y%m')
-- SET k.jsj_id = v.jsj_id
-- WHERE k.F_IsEffective = 1
--   AND k.yjsj >= @perf_from
--   AND (k.jsj_id IS NULL OR k.jsj_id = '');
-- SELECT ROW_COUNT() AS c3_kd_empty_filled;
--
-- UPDATE lq_xh_jksyj x
-- INNER JOIN tmp_valid_jsj_binding v
--   ON v.user_id = COALESCE(NULLIF(x.jks, ''), NULLIF(x.jkszh, ''))
--  AND v.F_Month = DATE_FORMAT(x.yjsj, '%Y%m')
-- SET x.F_jsjid = v.jsj_id
-- WHERE x.F_IsEffective = 1
--   AND x.yjsj >= @perf_from
--   AND (x.F_jsjid IS NULL OR x.F_jsjid = '');
-- SELECT ROW_COUNT() AS c3_xh_empty_filled;
--
-- UPDATE lq_hytk_jksyj h
-- INNER JOIN tmp_valid_jsj_binding v
--   ON v.user_id = COALESCE(NULLIF(h.jks, ''), NULLIF(h.jkszh, ''))
--  AND v.F_Month = DATE_FORMAT(h.tksj, '%Y%m')
-- SET h.F_jsjid = v.jsj_id
-- WHERE (h.F_IsEffective = 1 OR h.F_IsEffective IS NULL)
--   AND h.tksj >= @perf_from
--   AND (h.F_jsjid IS NULL OR h.F_jsjid = '');
-- SELECT ROW_COUNT() AS c3_tk_empty_filled;


-- ---------- C4 同人同月多 ACTIVE：只留最新（按主键更新，降低锁范围） ----------
DROP TEMPORARY TABLE IF EXISTS tmp_keep_binding;
CREATE TEMPORARY TABLE tmp_keep_binding AS
SELECT ju.user_id, ju.F_Month,
       SUBSTRING_INDEX(GROUP_CONCAT(ju.F_Id ORDER BY ju.F_CreatorTime DESC, ju.F_Id DESC), ',', 1) AS keep_id
FROM lq_jinsanjiao_user ju
INNER JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id AND j.yf = ju.F_Month
WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE'
GROUP BY ju.user_id, ju.F_Month
HAVING COUNT(*) > 1;

DROP TEMPORARY TABLE IF EXISTS tmp_close_binding_ids;
CREATE TEMPORARY TABLE tmp_close_binding_ids AS
SELECT ju.F_Id
FROM lq_jinsanjiao_user ju
INNER JOIN tmp_keep_binding k
  ON k.user_id = ju.user_id AND k.F_Month = ju.F_Month
INNER JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id AND j.yf = ju.F_Month
WHERE ju.F_DeleteMark = 0
  AND ju.status = 'ACTIVE'
  AND ju.F_Id <> k.keep_id;

UPDATE lq_jinsanjiao_user ju
INNER JOIN tmp_close_binding_ids c ON c.F_Id = ju.F_Id
SET ju.F_DeleteMark = 1,
    ju.status = 'INACTIVE',
    ju.F_LastModifyTime = NOW(),
    ju.F_LastModifyUserId = 'prod-fix-20260806-multi-active';

SELECT ROW_COUNT() AS c4_multi_active_closed;


-- ---------- C5 事务内验证（全部应为 0 才可 COMMIT） ----------
SELECT 'remain_orphan_active_binding' AS check_item, COUNT(*) AS cnt
FROM lq_jinsanjiao_user ju
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id
WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE' AND j.F_Id IS NULL
UNION ALL
SELECT 'remain_migratable_orphan_kd', COUNT(*)
FROM lq_kd_jksyj k
LEFT JOIN lq_ycsd_jsj j_old ON j_old.F_Id = k.jsj_id
INNER JOIN tmp_valid_jsj_binding v
  ON v.user_id = COALESCE(NULLIF(k.jks, ''), NULLIF(k.jkszh, ''))
 AND v.F_Month = DATE_FORMAT(k.yjsj, '%Y%m')
WHERE k.F_IsEffective = 1 AND k.jsj_id <> '' AND j_old.F_Id IS NULL AND v.jsj_id <> k.jsj_id
UNION ALL
SELECT 'remain_multi_active', COUNT(*)
FROM (
  SELECT ju.user_id, ju.F_Month
  FROM lq_jinsanjiao_user ju
  INNER JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id AND j.yf = ju.F_Month
  WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE'
  GROUP BY ju.user_id, ju.F_Month
  HAVING COUNT(*) > 1
) t;

-- 无法归队的幽灵业绩（当月无有效绑定）仅观察，不阻塞提交
SELECT COUNT(*) AS orphan_kd_no_valid_binding
FROM lq_kd_jksyj k
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = k.jsj_id
LEFT JOIN tmp_valid_jsj_binding v
  ON v.user_id = COALESCE(NULLIF(k.jks, ''), NULLIF(k.jkszh, ''))
 AND v.F_Month = DATE_FORMAT(k.yjsj, '%Y%m')
WHERE k.F_IsEffective = 1
  AND k.jsj_id IS NOT NULL AND k.jsj_id <> ''
  AND j.F_Id IS NULL
  AND v.jsj_id IS NULL;


-- #############################################################################
-- D. 提交或回滚（二选一，手动执行）
-- #############################################################################
-- 验证三项 remain_* 均为 0 后执行：
-- COMMIT;

-- 有问题执行：
-- ROLLBACK;

-- 回滚不会删除 B 段备份表；备份表可在确认业务无误 3~7 天后择期 DROP。