antissoft / lvqianmeiye_ERP

-- =============================================================================
-- 2026年7月 · 金三角 jsj_id / F_jsjid 漏挂 · 摸底 + 全量修复（生产迁库 2026-07-30 核对）
-- =============================================================================
-- 【一、7月数据总览】
--   开单 lq_kd_jksyj 未挂 jsj_id     : 3339 条
--   耗卡 lq_xh_jksyj 未挂 F_jsjid    : 3638 条
--   退卡 lq_hytk_jksyj 可补          : 0 条
--
-- 【二、未挂原因拆分（勿全量盲补）】
--   T区虚拟账号（开单/耗卡）         : 2941 / 2311  → 不补
--   非T区但当月无战队绑定（开单/耗卡）: 79 / 436    → 不补
--   ★ 有当月 ACTIVE 绑定仍空（应修） : 319 / 891    → 本脚本修复
--   合计应修                         : 1210 条（涉及健康师约 111+126 人次）
--
-- 【三、按战队待补耗卡 TOP（891条内）】
--   星耀队249、无敌38、百万35、雄霸天下29、飞跃29、超越27、进宝26…
--   虎啸龙吟 13 条耗卡 + 3 条开单
--
-- 【四、虎啸龙吟修复后预期（验证用）】
--   开单：115576 + 5180 = 120756
--   耗卡：78920.31 + 5549.18 = 84469.49
--
-- 【五、执行方式】Navicat 分段执行；步骤 4 存储过程可一键跑完 1210 条
--   生产库若开启 GTID：勿用 CREATE TABLE AS SELECT（已改为显式建表）
--   原则：仅补空值；按「健康师+jkszh优先+业绩月」查 ACTIVE 绑定；不覆盖已有值
-- =============================================================================

SET NAMES utf8mb4;
SET SESSION net_read_timeout = 600;
SET SESSION net_write_timeout = 600;
SET SESSION wait_timeout = 600;
SET SESSION innodb_lock_wait_timeout = 120;

SET @month_start = '2026-07-01 00:00:00';
SET @month_end   = '2026-07-31 23:59:59';
SET @perf_month  = '202607';
SET @huxiao_jsj  = '841830228446676229';

-- =============================================================================
-- 步骤 1：摸底（修复前跑，修复后 kd_remain/xh_remain 应为 0）
-- =============================================================================
SELECT 'kd_unbound' AS metric, COUNT(*) AS cnt
FROM lq_kd_jksyj
WHERE F_IsEffective = 1 AND (jsj_id IS NULL OR jsj_id = '')
  AND yjsj >= @month_start AND yjsj <= @month_end
UNION ALL
SELECT 'kd_fixable', COUNT(*)
FROM lq_kd_jksyj k
INNER JOIN lq_jinsanjiao_user ju
  ON ju.user_id = COALESCE(NULLIF(TRIM(k.jkszh), ''), NULLIF(TRIM(k.jks), ''))
 AND ju.F_Month = @perf_month AND ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE'
 AND ju.jsj_id IS NOT NULL AND ju.jsj_id <> ''
WHERE k.F_IsEffective = 1 AND (k.jsj_id IS NULL OR k.jsj_id = '')
  AND k.yjsj >= @month_start AND k.yjsj <= @month_end
UNION ALL
SELECT 'xh_unbound', COUNT(*)
FROM lq_xh_jksyj
WHERE F_IsEffective = 1 AND (F_jsjid IS NULL OR F_jsjid = '')
  AND yjsj >= @month_start AND yjsj <= @month_end
UNION ALL
SELECT 'xh_fixable', COUNT(*)
FROM lq_xh_jksyj x
INNER JOIN lq_jinsanjiao_user ju
  ON ju.user_id = COALESCE(NULLIF(TRIM(x.jkszh), ''), NULLIF(TRIM(x.jks), ''))
 AND ju.F_Month = @perf_month AND ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE'
 AND ju.jsj_id IS NOT NULL AND ju.jsj_id <> ''
WHERE x.F_IsEffective = 1 AND (x.F_jsjid IS NULL OR x.F_jsjid = '')
  AND x.yjsj >= @month_start AND x.yjsj <= @month_end;

-- 按战队待补数量（耗卡）
SELECT j.jsj AS team_name, COUNT(*) AS xh_fixable
FROM lq_xh_jksyj x
INNER JOIN lq_jinsanjiao_user ju
  ON ju.user_id = COALESCE(NULLIF(TRIM(x.jkszh), ''), NULLIF(TRIM(x.jks), ''))
 AND ju.F_Month = @perf_month AND ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE'
 AND ju.jsj_id IS NOT NULL AND ju.jsj_id <> ''
INNER JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id AND j.yf = ju.F_Month
WHERE x.F_IsEffective = 1 AND (x.F_jsjid IS NULL OR x.F_jsjid = '')
  AND x.yjsj >= @month_start AND x.yjsj <= @month_end
GROUP BY j.jsj
ORDER BY xh_fixable DESC;

-- 按日待补（耗卡，看 7/29-30 是否在内）
SELECT DATE_FORMAT(x.yjsj, '%Y-%m-%d') AS d, COUNT(*) AS xh_fixable
FROM lq_xh_jksyj x
INNER JOIN lq_jinsanjiao_user ju
  ON ju.user_id = COALESCE(NULLIF(TRIM(x.jkszh), ''), NULLIF(TRIM(x.jks), ''))
 AND ju.F_Month = @perf_month AND ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE'
 AND ju.jsj_id IS NOT NULL AND ju.jsj_id <> ''
WHERE x.F_IsEffective = 1 AND (x.F_jsjid IS NULL OR x.F_jsjid = '')
  AND x.yjsj >= @month_start AND x.yjsj <= @month_end
GROUP BY DATE_FORMAT(x.yjsj, '%Y-%m-%d')
ORDER BY d;

-- =============================================================================
-- 步骤 2：备份（仅 7 月未挂行；GTID 环境勿用 CREATE TABLE AS SELECT）
-- =============================================================================
CREATE TABLE IF NOT EXISTS bak_kd_jksyj_jsjid_202607 (
  F_Id          VARCHAR(64)  NOT NULL,
  glkdbh        VARCHAR(64)  NULL,
  jks           VARCHAR(64)  NULL,
  jkszh         VARCHAR(64)  NULL,
  jksxm         VARCHAR(128) NULL,
  yjsj          DATETIME     NULL,
  jsj_id        VARCHAR(64)  NULL,
  F_IsEffective INT          NULL,
  PRIMARY KEY (F_Id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bak_xh_jksyj_jsjid_202607 (
  F_Id          VARCHAR(64)  NOT NULL,
  glkdbh        VARCHAR(64)  NULL,
  jks           VARCHAR(64)  NULL,
  jkszh         VARCHAR(64)  NULL,
  jksxm         VARCHAR(128) NULL,
  yjsj          DATETIME     NULL,
  F_jsjid       VARCHAR(64)  NULL,
  F_IsEffective INT          NULL,
  PRIMARY KEY (F_Id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO bak_kd_jksyj_jsjid_202607
SELECT k.F_Id, k.glkdbh, k.jks, k.jkszh, k.jksxm, k.yjsj, k.jsj_id, k.F_IsEffective
FROM lq_kd_jksyj k
WHERE k.F_IsEffective = 1 AND (k.jsj_id IS NULL OR k.jsj_id = '')
  AND k.yjsj >= @month_start AND k.yjsj <= @month_end;

INSERT IGNORE INTO bak_xh_jksyj_jsjid_202607
SELECT x.F_Id, x.glkdbh, x.jks, x.jkszh, x.jksxm, x.yjsj, x.F_jsjid, x.F_IsEffective
FROM lq_xh_jksyj x
WHERE x.F_IsEffective = 1 AND (x.F_jsjid IS NULL OR x.F_jsjid = '')
  AND x.yjsj >= @month_start AND x.yjsj <= @month_end;

SELECT 'bak_kd_rows' AS item, COUNT(*) AS cnt FROM bak_kd_jksyj_jsjid_202607
UNION ALL
SELECT 'bak_xh_rows', COUNT(*) FROM bak_xh_jksyj_jsjid_202607;

-- =============================================================================
-- 步骤 3：建映射表（7月每人一条战队，同月多条 ACTIVE 取 F_CreatorTime 最新）
-- =============================================================================
DROP TABLE IF EXISTS tmp_jsj_user_bind_202607;

CREATE TABLE tmp_jsj_user_bind_202607 (
  user_id   VARCHAR(64) NOT NULL,
  jsj_id    VARCHAR(64) NOT NULL,
  team_name VARCHAR(128) NULL,
  PRIMARY KEY (user_id),
  KEY idx_jsj_id (jsj_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO tmp_jsj_user_bind_202607 (user_id, jsj_id, team_name)
SELECT x.user_id, MIN(x.jsj_id) AS jsj_id, MIN(j.jsj) AS team_name
FROM (
  SELECT ju.user_id, ju.jsj_id
  FROM lq_jinsanjiao_user ju
  INNER JOIN (
    SELECT user_id, MAX(F_CreatorTime) AS max_ct
    FROM lq_jinsanjiao_user
    WHERE F_DeleteMark = 0 AND status = 'ACTIVE'
      AND F_Month = @perf_month
      AND jsj_id IS NOT NULL AND jsj_id <> ''
    GROUP BY user_id
  ) latest ON latest.user_id = ju.user_id AND latest.max_ct = ju.F_CreatorTime
  INNER JOIN lq_ycsd_jsj j0 ON j0.F_Id = ju.jsj_id AND j0.yf = ju.F_Month
  WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE'
    AND ju.F_Month = @perf_month
    AND ju.jsj_id IS NOT NULL AND ju.jsj_id <> ''
) x
INNER JOIN lq_ycsd_jsj j ON j.F_Id = x.jsj_id
GROUP BY x.user_id;

SELECT COUNT(*) AS bind_user_cnt FROM tmp_jsj_user_bind_202607;

-- =============================================================================
-- 步骤 4A：【推荐】存储过程一键分批修复（每批 200 行，自动循环至完成）
--         Navicat 请整段选中执行（含 DELIMITER）
-- =============================================================================
DROP PROCEDURE IF EXISTS sp_fix_jsjid_202607;

DELIMITER $$

CREATE PROCEDURE sp_fix_jsjid_202607()
BEGIN
  DECLARE v_rows INT DEFAULT 1;
  DECLARE v_kd_total INT DEFAULT 0;
  DECLARE v_xh_total INT DEFAULT 0;

  SET v_rows = 1;
  WHILE v_rows > 0 DO
    UPDATE lq_kd_jksyj k
    INNER JOIN (
      SELECT k2.F_Id, b2.jsj_id
      FROM lq_kd_jksyj k2
      INNER JOIN tmp_jsj_user_bind_202607 b2
        ON b2.user_id = COALESCE(NULLIF(TRIM(k2.jkszh), ''), NULLIF(TRIM(k2.jks), ''))
      WHERE k2.F_IsEffective = 1
        AND (k2.jsj_id IS NULL OR k2.jsj_id = '')
        AND k2.yjsj >= @month_start AND k2.yjsj <= @month_end
      ORDER BY k2.F_Id
      LIMIT 200
    ) batch ON batch.F_Id = k.F_Id
    SET k.jsj_id = batch.jsj_id;
    SET v_rows = ROW_COUNT();
    SET v_kd_total = v_kd_total + v_rows;
  END WHILE;

  SET v_rows = 1;
  WHILE v_rows > 0 DO
    UPDATE lq_xh_jksyj x
    INNER JOIN (
      SELECT x2.F_Id, b2.jsj_id
      FROM lq_xh_jksyj x2
      INNER JOIN tmp_jsj_user_bind_202607 b2
        ON b2.user_id = COALESCE(NULLIF(TRIM(x2.jkszh), ''), NULLIF(TRIM(x2.jks), ''))
      WHERE x2.F_IsEffective = 1
        AND (x2.F_jsjid IS NULL OR x2.F_jsjid = '')
        AND x2.yjsj >= @month_start AND x2.yjsj <= @month_end
      ORDER BY x2.F_Id
      LIMIT 200
    ) batch ON batch.F_Id = x.F_Id
    SET x.F_jsjid = batch.jsj_id;
    SET v_rows = ROW_COUNT();
    SET v_xh_total = v_xh_total + v_rows;
  END WHILE;

  SELECT v_kd_total AS kd_fixed_rows, v_xh_total AS xh_fixed_rows,
         v_kd_total + v_xh_total AS total_fixed_rows;
END$$

DELIMITER ;

CALL sp_fix_jsjid_202607();

-- 可选：跑完后删除过程
-- DROP PROCEDURE IF EXISTS sp_fix_jsjid_202607;

-- =============================================================================
-- 步骤 4B：【备选】手动分批（若存储过程不可用，每次执行下面两段直到 remain=0）
-- =============================================================================
/*
UPDATE lq_kd_jksyj k
INNER JOIN (
  SELECT k2.F_Id, b2.jsj_id
  FROM lq_kd_jksyj k2
  INNER JOIN tmp_jsj_user_bind_202607 b2
    ON b2.user_id = COALESCE(NULLIF(TRIM(k2.jkszh), ''), NULLIF(TRIM(k2.jks), ''))
  WHERE k2.F_IsEffective = 1 AND (k2.jsj_id IS NULL OR k2.jsj_id = '')
    AND k2.yjsj >= @month_start AND k2.yjsj <= @month_end
  ORDER BY k2.F_Id LIMIT 200
) batch ON batch.F_Id = k.F_Id
SET k.jsj_id = batch.jsj_id;

UPDATE lq_xh_jksyj x
INNER JOIN (
  SELECT x2.F_Id, b2.jsj_id
  FROM lq_xh_jksyj x2
  INNER JOIN tmp_jsj_user_bind_202607 b2
    ON b2.user_id = COALESCE(NULLIF(TRIM(x2.jkszh), ''), NULLIF(TRIM(x2.jks), ''))
  WHERE x2.F_IsEffective = 1 AND (x2.F_jsjid IS NULL OR x2.F_jsjid = '')
    AND x2.yjsj >= @month_start AND x2.yjsj <= @month_end
  ORDER BY x2.F_Id LIMIT 200
) batch ON batch.F_Id = x.F_Id
SET x.F_jsjid = batch.jsj_id;
*/

-- =============================================================================
-- 步骤 5：修复后验证（kd_remain / xh_remain 必须为 0）
-- =============================================================================
SELECT 'kd_remain' AS metric, COUNT(*) AS cnt
FROM lq_kd_jksyj k
INNER JOIN tmp_jsj_user_bind_202607 b
  ON b.user_id = COALESCE(NULLIF(TRIM(k.jkszh), ''), NULLIF(TRIM(k.jks), ''))
WHERE k.F_IsEffective = 1 AND (k.jsj_id IS NULL OR k.jsj_id = '')
  AND k.yjsj >= @month_start AND k.yjsj <= @month_end
UNION ALL
SELECT 'xh_remain', COUNT(*)
FROM lq_xh_jksyj x
INNER JOIN tmp_jsj_user_bind_202607 b
  ON b.user_id = COALESCE(NULLIF(TRIM(x.jkszh), ''), NULLIF(TRIM(x.jks), ''))
WHERE x.F_IsEffective = 1 AND (x.F_jsjid IS NULL OR x.F_jsjid = '')
  AND x.yjsj >= @month_start AND x.yjsj <= @month_end;

-- 虎啸龙吟 7 月合计（预期开单 120756 / 耗卡 84469.49）
SELECT 'huxiao_kd' AS typ,
       COUNT(*) AS cnt,
       SUM(CAST(k.jksyj AS DECIMAL(18,2))) AS amt
FROM lq_kd_jksyj k
WHERE k.jsj_id = @huxiao_jsj AND k.F_IsEffective = 1
  AND k.yjsj >= @month_start AND k.yjsj <= @month_end
  AND k.jksyj IS NOT NULL AND k.jksyj <> '' AND k.jksyj <> '0'
UNION ALL
SELECT 'huxiao_xh', COUNT(*), SUM(x.jksyj)
FROM lq_xh_jksyj x
WHERE x.F_jsjid = @huxiao_jsj AND x.F_IsEffective = 1
  AND x.yjsj >= @month_start AND x.yjsj <= @month_end
  AND x.jksyj IS NOT NULL AND x.jksyj <> 0;

-- 跨战队单 849167374304150789：柳全菊=卧虎藏龙，唐宇欣=虎啸龙吟
SELECT k.jksxm, k.jsj_id, j.jsj
FROM lq_kd_jksyj k
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = k.jsj_id
WHERE k.glkdbh = '849167374304150789' AND k.F_IsEffective = 1;

-- 截图典型漏挂（陈小琴/何兴宇/曹悦）应已有 F_jsjid
SELECT x.F_Id, x.jksxm, x.F_jsjid, j.jsj
FROM lq_xh_jksyj x
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = x.F_jsjid
WHERE x.F_Id IN (
  '852358650214221061','852358650222609669',
  '852358722184283397','852358722188477701','852358722196866309'
);

-- =============================================================================
-- 步骤 6：清理（验证通过后）
-- =============================================================================
-- DROP TABLE IF EXISTS tmp_jsj_user_bind_202607;
-- DROP PROCEDURE IF EXISTS sp_fix_jsjid_202607;

-- =============================================================================
-- 回滚（慎用）
-- =============================================================================
/*
UPDATE lq_kd_jksyj k
INNER JOIN bak_kd_jksyj_jsjid_202607 b ON b.F_Id = k.F_Id
SET k.jsj_id = b.jsj_id;

UPDATE lq_xh_jksyj x
INNER JOIN bak_xh_jksyj_jsjid_202607 b ON b.F_Id = x.F_Id
SET x.F_jsjid = b.F_jsjid;
*/