antissoft / lvqianmeiye_ERP

-- =============================================================================
-- 补挂金三角业绩 jsj_id / F_jsjid（2026年7月）
-- =============================================================================
-- ⚠️ 若全量执行易报 2013 Lost connection，请改用分批版：
--    项目文档相关/sql/2026-07-30/补挂金三角业绩_jsjid_202607_v2_分批.sql
-- =============================================================================
-- 背景：
--   1) 7/29 前无入库兜底，部分开单/耗卡业绩行 jsj_id 为空
--   2) 跨战队合开时，开单头只挂了其中一个战队，其他战队健康师业绩行漏挂
--
-- 原则（与 GoldTriangleBindingHelper 一致）：
--   - 按「健康师匹配键 + 业绩月份」查 lq_jinsanjiao_user 活跃绑定
--   - 每人补挂【自己当月战队】，不按开单头战队整单覆盖（支持跨战队）
--   - 仅补 jsj_id / F_jsjid 为空的有效业绩行，已有值不覆盖
--   - 同一人同月多条 ACTIVE 绑定时，取 F_CreatorTime 最新且战队主档存在的记录
--
-- 已核对（dev 库 2026-07-30）：
--   虎啸龙吟三人未挂：开单 3 笔 / 耗卡 13 笔，共 16 条
--   跨战队样例 849167374304150789：唐宇欣 → 虎啸龙吟，柳全菊保持卧虎藏龙
--   7 月全量可补：开单 316 条、耗卡 866 条（有当月绑定且当前为空）
--
-- 执行顺序：备份 → 预览 → 更新 → 验证
-- =============================================================================

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. 备份（强烈建议）
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS bak_kd_jksyj_jsjid_202607;
CREATE TABLE bak_kd_jksyj_jsjid_202607 AS
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;

DROP TABLE IF EXISTS bak_xh_jksyj_jsjid_202607;
CREATE TABLE bak_xh_jksyj_jsjid_202607 AS
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;

-- -----------------------------------------------------------------------------
-- 1. 公共：当月「健康师 → 战队」映射（去重后每人每月一条）
--    预览绑定映射（可选，执行下面 SELECT）
-- -----------------------------------------------------------------------------
/*
SELECT
  ju1.user_id,
  ju1.F_Month,
  ju1.jsj_id,
  j.jsj AS team_name,
  ju1.F_CreatorTime
FROM lq_jinsanjiao_user ju1
INNER JOIN lq_ycsd_jsj j
  ON j.F_Id = ju1.jsj_id
 AND j.yf = ju1.F_Month
WHERE ju1.F_DeleteMark = 0
  AND ju1.status = 'ACTIVE'
  AND ju1.jsj_id IS NOT NULL
  AND ju1.jsj_id <> ''
  AND ju1.F_Month = @perf_month
  AND ju1.F_CreatorTime = (
    SELECT MAX(ju2.F_CreatorTime)
    FROM lq_jinsanjiao_user ju2
    INNER JOIN lq_ycsd_jsj j2
      ON j2.F_Id = ju2.jsj_id
     AND j2.yf = ju2.F_Month
    WHERE ju2.user_id = ju1.user_id
      AND ju2.F_Month = ju1.F_Month
      AND ju2.F_DeleteMark = 0
      AND ju2.status = 'ACTIVE'
  );
*/

-- -----------------------------------------------------------------------------
-- 2. 预览：7 月待补挂开单业绩（跨战队会按健康师分别映射）
-- -----------------------------------------------------------------------------
SELECT
  k.F_Id AS perf_id,
  k.glkdbh AS order_id,
  k.jksxm,
  kd.jsj AS header_team_name,
  k.jsj_id AS old_jsj_id,
  b.jsj_id AS new_jsj_id,
  j.jsj AS new_team_name
FROM lq_kd_jksyj k
INNER JOIN (
  SELECT ju1.user_id, ju1.F_Month, ju1.jsj_id
  FROM lq_jinsanjiao_user ju1
  INNER JOIN lq_ycsd_jsj jx ON jx.F_Id = ju1.jsj_id AND jx.yf = ju1.F_Month
  WHERE ju1.F_DeleteMark = 0
    AND ju1.status = 'ACTIVE'
    AND ju1.jsj_id IS NOT NULL AND ju1.jsj_id <> ''
    AND ju1.F_CreatorTime = (
      SELECT MAX(ju2.F_CreatorTime)
      FROM lq_jinsanjiao_user ju2
      INNER JOIN lq_ycsd_jsj j2 ON j2.F_Id = ju2.jsj_id AND j2.yf = ju2.F_Month
      WHERE ju2.user_id = ju1.user_id
        AND ju2.F_Month = ju1.F_Month
        AND ju2.F_DeleteMark = 0
        AND ju2.status = 'ACTIVE'
    )
) b
  ON b.user_id = COALESCE(NULLIF(TRIM(k.jkszh), ''), NULLIF(TRIM(k.jks), ''))
 AND b.F_Month = DATE_FORMAT(k.yjsj, '%Y%m')
INNER JOIN lq_ycsd_jsj j ON j.F_Id = b.jsj_id
LEFT JOIN lq_kd_kdjlb kd ON kd.F_Id = k.glkdbh
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
ORDER BY k.yjsj, k.jksxm;
-- 预期约 316 行

-- -----------------------------------------------------------------------------
-- 3. 预览：7 月待补挂耗卡业绩
-- -----------------------------------------------------------------------------
SELECT
  x.F_Id AS perf_id,
  x.glkdbh AS order_id,
  x.jksxm,
  x.F_jsjid AS old_jsj_id,
  b.jsj_id AS new_jsj_id,
  j.jsj AS new_team_name
FROM lq_xh_jksyj x
INNER JOIN (
  SELECT ju1.user_id, ju1.F_Month, ju1.jsj_id
  FROM lq_jinsanjiao_user ju1
  INNER JOIN lq_ycsd_jsj jx ON jx.F_Id = ju1.jsj_id AND jx.yf = ju1.F_Month
  WHERE ju1.F_DeleteMark = 0
    AND ju1.status = 'ACTIVE'
    AND ju1.jsj_id IS NOT NULL AND ju1.jsj_id <> ''
    AND ju1.F_CreatorTime = (
      SELECT MAX(ju2.F_CreatorTime)
      FROM lq_jinsanjiao_user ju2
      INNER JOIN lq_ycsd_jsj j2 ON j2.F_Id = ju2.jsj_id AND j2.yf = ju2.F_Month
      WHERE ju2.user_id = ju1.user_id
        AND ju2.F_Month = ju1.F_Month
        AND ju2.F_DeleteMark = 0
        AND ju2.status = 'ACTIVE'
    )
) b
  ON b.user_id = COALESCE(NULLIF(TRIM(x.jkszh), ''), NULLIF(TRIM(x.jks), ''))
 AND b.F_Month = DATE_FORMAT(x.yjsj, '%Y%m')
INNER JOIN lq_ycsd_jsj j ON j.F_Id = b.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
ORDER BY x.yjsj, x.jksxm;
-- 预期约 866 行

-- -----------------------------------------------------------------------------
-- 4A. 【可选·最小范围】仅补虎啸龙吟三人 16 条（已逐条核对 F_Id）
--     跨战队单 849167374304150789 只补唐宇欣，不动柳全菊卧虎藏龙
-- -----------------------------------------------------------------------------
UPDATE lq_kd_jksyj
SET jsj_id = @huxiao_jsj
WHERE F_Id IN (
  '849792273540449542', -- 唐宇欣，开单 849167374304150789（头=卧虎藏龙）
  '852022282741089541', -- 唐宇欣，开单 851999330188395781
  '852022282741089542'  -- 李萌，  开单 851999330188395781
)
  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 = '');

-- -----------------------------------------------------------------------------
-- 4B. 【推荐·全量兜底】7 月所有漏挂且能匹配当月绑定的业绩行（含跨战队）
--     若已执行 4A，重复执行 4B 也安全（仅补空值）
-- -----------------------------------------------------------------------------
UPDATE lq_kd_jksyj k
INNER JOIN (
  SELECT ju1.user_id, ju1.F_Month, ju1.jsj_id
  FROM lq_jinsanjiao_user ju1
  INNER JOIN lq_ycsd_jsj jx ON jx.F_Id = ju1.jsj_id AND jx.yf = ju1.F_Month
  WHERE ju1.F_DeleteMark = 0
    AND ju1.status = 'ACTIVE'
    AND ju1.jsj_id IS NOT NULL AND ju1.jsj_id <> ''
    AND ju1.F_CreatorTime = (
      SELECT MAX(ju2.F_CreatorTime)
      FROM lq_jinsanjiao_user ju2
      INNER JOIN lq_ycsd_jsj j2 ON j2.F_Id = ju2.jsj_id AND j2.yf = ju2.F_Month
      WHERE ju2.user_id = ju1.user_id
        AND ju2.F_Month = ju1.F_Month
        AND ju2.F_DeleteMark = 0
        AND ju2.status = 'ACTIVE'
    )
) b
  ON b.user_id = COALESCE(NULLIF(TRIM(k.jkszh), ''), NULLIF(TRIM(k.jks), ''))
 AND b.F_Month = DATE_FORMAT(k.yjsj, '%Y%m')
SET k.jsj_id = b.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;

UPDATE lq_xh_jksyj x
INNER JOIN (
  SELECT ju1.user_id, ju1.F_Month, ju1.jsj_id
  FROM lq_jinsanjiao_user ju1
  INNER JOIN lq_ycsd_jsj jx ON jx.F_Id = ju1.jsj_id AND jx.yf = ju1.F_Month
  WHERE ju1.F_DeleteMark = 0
    AND ju1.status = 'ACTIVE'
    AND ju1.jsj_id IS NOT NULL AND ju1.jsj_id <> ''
    AND ju1.F_CreatorTime = (
      SELECT MAX(ju2.F_CreatorTime)
      FROM lq_jinsanjiao_user ju2
      INNER JOIN lq_ycsd_jsj j2 ON j2.F_Id = ju2.jsj_id AND j2.yf = ju2.F_Month
      WHERE ju2.user_id = ju1.user_id
        AND ju2.F_Month = ju1.F_Month
        AND ju2.F_DeleteMark = 0
        AND ju2.status = 'ACTIVE'
    )
) b
  ON b.user_id = COALESCE(NULLIF(TRIM(x.jkszh), ''), NULLIF(TRIM(x.jks), ''))
 AND b.F_Month = DATE_FORMAT(x.yjsj, '%Y%m')
SET x.F_jsjid = b.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;

-- -----------------------------------------------------------------------------
-- 5. 验证：虎啸龙吟 7 月战队业绩（应与接口 GetMyTeamPerformance 一致）
-- -----------------------------------------------------------------------------
SELECT
  k.jksxm,
  SUM(CAST(k.jksyj AS DECIMAL(18,2))) AS billing_amt,
  COUNT(*) AS billing_cnt
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;
-- 预期：李巧娇 42380 / 唐宇欣 37520 / 李萌 16056，合计 95956

SELECT
  x.jksxm,
  SUM(x.jksyj) AS consume_amt,
  COUNT(*) AS consume_cnt
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;
-- 预期：唐宇欣 25961.33 / 李巧娇 18508.17 / 李萌 10979.03，合计 55448.53

-- 跨战队单验证：同一开单两行应分属不同战队
SELECT k.F_Id, 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;
-- 预期：柳全菊→卧虎藏龙(841830264337335557)，唐宇欣→虎啸龙吟(841830228446676229)

-- 仍为空且当月有绑定的残留（应为 0）
SELECT 'kd' AS typ, COUNT(*) AS remain
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
  AND EXISTS (
    SELECT 1 FROM lq_jinsanjiao_user ju
    WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE'
      AND ju.F_Month = DATE_FORMAT(k.yjsj, '%Y%m')
      AND ju.user_id = COALESCE(NULLIF(TRIM(k.jkszh), ''), NULLIF(TRIM(k.jks), ''))
  )
UNION ALL
SELECT 'xh', COUNT(*)
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
  AND EXISTS (
    SELECT 1 FROM lq_jinsanjiao_user ju
    WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE'
      AND ju.F_Month = DATE_FORMAT(x.yjsj, '%Y%m')
      AND ju.user_id = COALESCE(NULLIF(TRIM(x.jkszh), ''), NULLIF(TRIM(x.jks), ''))
  );

-- -----------------------------------------------------------------------------
-- 6. 回滚（如需）
-- -----------------------------------------------------------------------------
-- 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;