antissoft / lvqianmeiye_ERP

  -- 修复会员沉睡天数数据

  -- 问题：LastConsumeTime 与有效耗卡 MAX(hksj) 不一致（含零金额耗卡、LastConsumeTime 偏新/偏旧）
  -- 解决方案：不限制消耗金额，按有效耗卡 MAX(hksj) 纠正 LastConsumeTime，并按运行时/批量口径重算 SleepDays、SleepStartTime
  -- 沉睡阈值默认 60 天（与 MemberInfoUpdateConfig.SleepDaysThresholds 最小值一致）

  
  -- 1. 修复单个会员（陈秋竹）
  UPDATE lq_khxx kh
  INNER JOIN (
      SELECT 
          hy,
          MAX(hksj) as last_consume_time
      FROM lq_xh_hyhk
      WHERE hymc = '陈秋竹'
          AND F_IsEffective = 1

      GROUP BY hy
  ) consume ON kh.F_Id = consume.hy
  SET 
      kh.F_LastConsumeTime = consume.last_consume_time,

      kh.F_SleepDays = GREATEST(0, DATEDIFF(CURDATE(), consume.last_consume_time)),
      kh.F_SleepStartTime = CASE 
          WHEN GREATEST(0, DATEDIFF(CURDATE(), consume.last_consume_time)) >= 60
              AND kh.F_SleepStartTime IS NULL 
          THEN DATE(consume.last_consume_time)
          WHEN GREATEST(0, DATEDIFF(CURDATE(), consume.last_consume_time)) < 60
          THEN NULL
          ELSE kh.F_SleepStartTime
      END

  WHERE kh.khmc = '陈秋竹'
      AND kh.F_IsEffective = 1;
  

  -- 2. 批量修复所有会员（根据最新的有效耗卡记录更新 LastConsumeTime）

  -- 注意：执行此SQL前请先备份数据库
  UPDATE lq_khxx kh
  INNER JOIN (
      SELECT 
          hy,
          MAX(hksj) as last_consume_time
      FROM lq_xh_hyhk
      WHERE F_IsEffective = 1

      GROUP BY hy
  ) consume ON kh.F_Id = consume.hy
  SET 
      kh.F_LastConsumeTime = consume.last_consume_time,

      kh.F_SleepDays = GREATEST(0, DATEDIFF(CURDATE(), consume.last_consume_time)),
      kh.F_SleepStartTime = CASE 
          WHEN GREATEST(0, DATEDIFF(CURDATE(), consume.last_consume_time)) >= 60
              AND kh.F_SleepStartTime IS NULL 
          THEN DATE(consume.last_consume_time)
          WHEN GREATEST(0, DATEDIFF(CURDATE(), consume.last_consume_time)) < 60
          THEN NULL
          ELSE kh.F_SleepStartTime
      END

  WHERE kh.F_IsEffective = 1
      AND (

          -- 纠正 LastConsumeTime 为空、偏旧或偏新的脏数据

          kh.F_LastConsumeTime IS NULL 

          OR kh.F_LastConsumeTime <> consume.last_consume_time

      );
  

  -- 3. 无有效耗卡会员的 fallback（LastConsumeTime 置空，SleepDays 按 F_CreateTime 计算）
  -- 建议优先调用 API：POST /api/Extend/LqKhxx/BatchUpdateMemberInfo
  -- 若需 SQL 手工修复，可参考：
  -- UPDATE lq_khxx kh
  -- LEFT JOIN (
  --     SELECT hy, MAX(hksj) AS last_consume_time
  --     FROM lq_xh_hyhk
  --     WHERE F_IsEffective = 1
  --     GROUP BY hy
  -- ) consume ON kh.F_Id = consume.hy
  -- SET
  --     kh.F_LastConsumeTime = NULL,
  --     kh.F_SleepDays = GREATEST(0, DATEDIFF(CURDATE(), kh.F_CreateTime)),
  --     kh.F_SleepStartTime = CASE
  --         WHEN GREATEST(0, DATEDIFF(CURDATE(), kh.F_CreateTime)) >= 60 AND kh.F_SleepStartTime IS NULL
  --         THEN DATE(kh.F_CreateTime)
  --         WHEN GREATEST(0, DATEDIFF(CURDATE(), kh.F_CreateTime)) < 60
  --         THEN NULL
  --         ELSE kh.F_SleepStartTime
  --     END
  -- WHERE kh.F_IsEffective = 1
  --     AND consume.hy IS NULL;