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;
  */