-- 修复人次记录表中F_HasBilling字段
-- 对于耗卡类型的人次记录，如果会员有开单记录（F_IsEffective=1且Sfyj>0），则设置F_HasBilling=1

-- 1. 先查看需要修复的记录数量
SELECT COUNT(*) as need_fix_count
FROM lq_person_times_record ptr
WHERE ptr.F_BusinessType = '耗卡'
  AND ptr.F_HasBilling = 0
  AND ptr.F_MemberId IS NOT NULL
  AND ptr.F_MemberId != ''
  AND EXISTS (
      SELECT 1
      FROM lq_kd_kdjlb kd
      WHERE kd.Kdhy = ptr.F_MemberId
        AND kd.F_IsEffective = 1
        AND kd.Sfyj > 0
  );

-- 2. 修复耗卡类型的人次记录
UPDATE lq_person_times_record ptr
INNER JOIN (
    SELECT DISTINCT Kdhy
    FROM lq_kd_kdjlb
    WHERE F_IsEffective = 1
      AND Sfyj > 0
) kd ON ptr.F_MemberId = kd.Kdhy
SET ptr.F_HasBilling = 1
WHERE ptr.F_BusinessType = '耗卡'
  AND ptr.F_HasBilling = 0
  AND ptr.F_MemberId IS NOT NULL
  AND ptr.F_MemberId != '';

-- 3. 验证修复结果（可选）
SELECT COUNT(*) as fixed_count
FROM lq_person_times_record ptr
WHERE ptr.F_BusinessType = '耗卡'
  AND ptr.F_HasBilling = 1
  AND ptr.F_MemberId IS NOT NULL
  AND ptr.F_MemberId != ''
  AND EXISTS (
      SELECT 1
      FROM lq_kd_kdjlb kd
      WHERE kd.Kdhy = ptr.F_MemberId
        AND kd.F_IsEffective = 1
        AND kd.Sfyj > 0
  );

