antissoft / lvqianmeiye_ERP

-- =============================================================================
-- 补挂金三角业绩 jsj_id / F_jsjid（2026年7月）— V2 分批版【全公司】
-- =============================================================================
-- 范围说明（生产数据迁库后 2026-07-30 核对）：
--   - 7 月未挂：开单约 3339、耗卡约 3638（含大量「当月无战队绑定」历史行，勿全量盲补）
--   - 本脚本可安全补挂：开单 319、耗卡 891（当月有 ACTIVE 绑定且 jsj_id 为空）
--   - 虎啸龙吟仍待补：开单 3 + 耗卡 13 = 16 条（步骤 2 可先秒级验证）
--
-- 适用场景：
--   全量 UPDATE + 关联子查询易触发 2013 Lost connection，本脚本改为：
--   1) 先建「健康师→战队」映射临时表（只算一次）
--   2) 每次 UPDATE LIMIT 100，可反复执行直到影响 0 行
--   3) 仅补空值，不覆盖已有 jsj_id（支持跨战队、支持断点续跑）
--
-- 执行建议（Navicat / DBeaver）：
--   - 不要一次运行整个文件；按「步骤」分段选中执行
--   - 执行前可先跑：SET SESSION net_read_timeout = 600;
--   - 步骤 C/D 重复执行同一段，直到「影响行数 = 0」
-- =============================================================================

SET NAMES utf8mb4;

-- 可选：拉长会话超时，减少 2013
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'; -- 虎啸龙吟

-- =============================================================================
-- 步骤 0：断点检查（看上次是否已部分执行）
-- =============================================================================
SELECT 'bak_kd' AS item, COUNT(*) AS cnt
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'bak_kd_jksyj_jsjid_202607'
UNION ALL
SELECT 'bak_xh', COUNT(*)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'bak_xh_jksyj_jsjid_202607';

-- 虎啸龙吟三人 16 条关键行状态（空=未补，有值=已补）
SELECT 'kd' AS typ, k.F_Id, k.jksxm, k.jsj_id
FROM lq_kd_jksyj k
WHERE k.F_Id IN (
  '849792273540449542','852022282741089541','852022282741089542'
)
UNION ALL
SELECT 'xh', x.F_Id, x.jksxm, x.F_jsjid
FROM lq_xh_jksyj x
WHERE x.F_Id IN (
  '843749838901216517','843749838901216518','844872313659720965',
  '846897478727894277','846652670822319365','846692856495080709',
  '848020321473660165','848553719795549445','849587037194421509',
  '850906583914251525','851690507896620293','851690507900814597',
  '851690507905008901'
);

-- =============================================================================
-- 步骤 1：备份（仅未挂行，很快；若已存在可跳过）
-- =============================================================================
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;

-- =============================================================================
-- 步骤 2：虎啸龙吟三人 16 条（秒级完成，建议先跑这段）
--         跨战队单 849167374304150789：只补唐宇欣，不动柳全菊
-- =============================================================================
UPDATE lq_kd_jksyj
SET jsj_id = @huxiao_jsj
WHERE F_Id IN (
  '849792273540449542',
  '852022282741089541',
  '852022282741089542'
)
  AND F_IsEffective = 1
  AND (jsj_id IS NULL OR jsj_id = '');

UPDATE lq_xh_jksyj
SET F_jsjid = @huxiao_jsj
WHERE F_Id IN (
  '843749838901216517','843749838901216518','844872313659720965',
  '846897478727894277','846652670822319365','846692856495080709',
  '848020321473660165','848553719795549445','849587037194421509',
  '850906583914251525','851690507896620293','851690507900814597',
  '851690507905008901'
)
  AND F_IsEffective = 1
  AND (F_jsjid IS NULL OR F_jsjid = '');

-- =============================================================================
-- 步骤 3：构建映射表 tmp_jsj_user_bind_202607（全公司 7 月，每人一条）
-- =============================================================================
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;

-- =============================================================================
-- 步骤 4：待补数量（执行前后对比用）
-- =============================================================================
SELECT 'kd_todo' AS typ, 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_todo', 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;

-- =============================================================================
-- 步骤 5C：分批补开单（每次 100 行 —— 重复执行本段直到影响 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 100
) batch ON batch.F_Id = k.F_Id
SET k.jsj_id = batch.jsj_id;

-- 查看还剩多少（应为 0 才停）
SELECT COUNT(*) AS kd_remain
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;

-- =============================================================================
-- 步骤 5D：分批补耗卡（每次 100 行 —— 重复执行本段直到影响 0 行）
-- =============================================================================
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 100
) batch ON batch.F_Id = x.F_Id
SET x.F_jsjid = batch.jsj_id;

SELECT COUNT(*) AS xh_remain
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;

-- =============================================================================
-- 步骤 6：验证（虎啸龙吟 7 月）
-- =============================================================================
SELECT j.jsj AS team_name, SUM(CAST(k.jksyj AS DECIMAL(18,2))) AS billing_total
FROM lq_kd_jksyj k
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = k.jsj_id
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';

-- 预期 billing_total（生产迁库后）：120756.00（修复前约 115576 + 待补 5180）

SELECT k.jksxm, SUM(CAST(k.jksyj AS DECIMAL(18,2))) AS billing_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'
GROUP BY k.jksxm ORDER BY billing_amt DESC;

SELECT x.jksxm, SUM(x.jksyj) AS consume_amt
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
GROUP BY x.jksxm ORDER BY consume_amt DESC;
-- 预期 consume 合计（生产迁库后）：84469.49（修复前约 78920.31 + 待补 5549.18）

-- 跨战队单：柳全菊=卧虎藏龙，唐宇欣=虎啸龙吟
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;

-- =============================================================================
-- 步骤 7：清理临时表（验证通过后）
-- =============================================================================
-- DROP TABLE IF EXISTS tmp_jsj_user_bind_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;