-- 更新历史数据的业绩类型字段
-- 业绩类型根据品项表（lq_xmzl）的 fl3 字段来设置
-- fl3 字段是"分类③-工资用"，用于区分不同的业绩类型

-- 1. 更新开单品项表（通过品项ID关联品项表的fl3字段）
UPDATE lq_kd_pxmx pxmx
INNER JOIN lq_xmzl xmzl ON pxmx.px = xmzl.F_Id
SET pxmx.F_PerformanceType = COALESCE(xmzl.fl3, '')
WHERE (pxmx.F_PerformanceType IS NULL OR pxmx.F_PerformanceType = '')
  AND pxmx.F_IsEffective = 1;

-- 2. 更新开单健康师业绩表（通过品项ID或开单品项明细ID关联品项表的fl3字段）
UPDATE lq_kd_jksyj jksyj
LEFT JOIN lq_kd_pxmx pxmx ON jksyj.F_kdpxid = pxmx.F_Id
LEFT JOIN lq_xmzl xmzl ON COALESCE(jksyj.F_ItemId, pxmx.px) = xmzl.F_Id
SET jksyj.F_PerformanceType = COALESCE(xmzl.fl3, '')
WHERE (jksyj.F_PerformanceType IS NULL OR jksyj.F_PerformanceType = '')
  AND jksyj.F_IsEffective = 1
  AND xmzl.F_Id IS NOT NULL;

-- 3. 更新开单科技部老师业绩表（通过品项ID或开单品项明细ID关联品项表的fl3字段）
UPDATE lq_kd_kjbsyj kjbsyj
LEFT JOIN lq_kd_pxmx pxmx ON kjbsyj.F_kdpxid = pxmx.F_Id
LEFT JOIN lq_xmzl xmzl ON COALESCE(kjbsyj.F_ItemId, pxmx.px) = xmzl.F_Id
SET kjbsyj.F_PerformanceType = COALESCE(xmzl.fl3, '')
WHERE (kjbsyj.F_PerformanceType IS NULL OR kjbsyj.F_PerformanceType = '')
  AND kjbsyj.F_IsEffective = 1
  AND xmzl.F_Id IS NOT NULL;

-- 4. 更新消耗品项表（通过品项ID关联品项表的fl3字段）
UPDATE lq_xh_pxmx pxmx
INNER JOIN lq_xmzl xmzl ON pxmx.px = xmzl.F_Id
SET pxmx.F_PerformanceType = COALESCE(xmzl.fl3, '')
WHERE (pxmx.F_PerformanceType IS NULL OR pxmx.F_PerformanceType = '')
  AND (pxmx.F_IsEffective = 1 OR pxmx.F_IsEffective IS NULL);

-- 5. 更新消耗健康师业绩表（通过品项ID关联品项表的fl3字段）
UPDATE lq_xh_jksyj jksyj
LEFT JOIN lq_xh_pxmx pxmx ON jksyj.F_kdpxid = pxmx.F_Id
LEFT JOIN lq_xmzl xmzl ON COALESCE(jksyj.F_ItemId, pxmx.px) = xmzl.F_Id
SET jksyj.F_PerformanceType = COALESCE(xmzl.fl3, '')
WHERE (jksyj.F_PerformanceType IS NULL OR jksyj.F_PerformanceType = '')
  AND (jksyj.F_IsEffective = 1 OR jksyj.F_IsEffective IS NULL)
  AND xmzl.F_Id IS NOT NULL;

-- 6. 更新消耗科技部老师业绩表（通过品项ID关联品项表的fl3字段）
UPDATE lq_xh_kjbsyj kjbsyj
LEFT JOIN lq_xh_pxmx pxmx ON kjbsyj.F_hkpxid = pxmx.F_Id
LEFT JOIN lq_xmzl xmzl ON COALESCE(kjbsyj.F_ItemId, pxmx.px) = xmzl.F_Id
SET kjbsyj.F_PerformanceType = COALESCE(xmzl.fl3, '')
WHERE (kjbsyj.F_PerformanceType IS NULL OR kjbsyj.F_PerformanceType = '')
  AND (kjbsyj.F_IsEffective = 1 OR kjbsyj.F_IsEffective IS NULL)
  AND xmzl.F_Id IS NOT NULL;

-- 7. 更新退卡明细表（通过品项ID关联品项表的fl3字段）
UPDATE lq_hytk_mx mx
INNER JOIN lq_xmzl xmzl ON mx.px = xmzl.F_Id
SET mx.F_PerformanceType = COALESCE(xmzl.fl3, '')
WHERE (mx.F_PerformanceType IS NULL OR mx.F_PerformanceType = '')
  AND mx.F_IsEffective = 1;

-- 8. 更新退卡健康师业绩表（通过品项ID关联品项表的fl3字段）
UPDATE lq_hytk_jksyj jksyj
LEFT JOIN lq_hytk_mx mx ON jksyj.F_CardReturn = mx.F_Id
LEFT JOIN lq_xmzl xmzl ON COALESCE(jksyj.F_ItemId, mx.px) = xmzl.F_Id
SET jksyj.F_PerformanceType = COALESCE(xmzl.fl3, '')
WHERE (jksyj.F_PerformanceType IS NULL OR jksyj.F_PerformanceType = '')
  AND jksyj.F_IsEffective = 1
  AND xmzl.F_Id IS NOT NULL;

-- 9. 更新退卡科技部老师业绩表（通过品项ID关联品项表的fl3字段）
UPDATE lq_hytk_kjbsyj kjbsyj
LEFT JOIN lq_hytk_mx mx ON kjbsyj.F_CardReturn = mx.F_Id
LEFT JOIN lq_xmzl xmzl ON COALESCE(kjbsyj.F_ItemId, mx.px) = xmzl.F_Id
SET kjbsyj.F_PerformanceType = COALESCE(xmzl.fl3, '')
WHERE (kjbsyj.F_PerformanceType IS NULL OR kjbsyj.F_PerformanceType = '')
  AND kjbsyj.F_IsEffective = 1
  AND xmzl.F_Id IS NOT NULL;

-- 10. 更新业绩明细表（lq_yjmxb）
-- 业绩明细表通常是汇总表，可能包含开单和消耗的数据
-- 由于无法准确判断每条记录的具体来源，这里先设置为空
-- 如果需要更精确的更新，可以根据业务逻辑关联其他表来判断
-- 例如：如果业绩明细表有开单编号字段，可以关联开单表判断为'开单'
-- 如果业绩明细表有耗卡编号字段，可以关联耗卡表判断为'消耗'
UPDATE lq_yjmxb 
SET F_PerformanceType = NULL
WHERE F_PerformanceType IS NULL OR F_PerformanceType = '';

-- 注意：如果业绩明细表有明确的关联字段，可以使用以下逻辑进行更精确的更新：
-- UPDATE lq_yjmxb yj
-- LEFT JOIN lq_kd_kdjlb kd ON yj.关联字段 = kd.F_Id
-- LEFT JOIN lq_xh_hyhk xh ON yj.关联字段 = xh.F_Id
-- SET yj.F_PerformanceType = CASE 
--     WHEN kd.F_Id IS NOT NULL THEN '开单'
--     WHEN xh.F_Id IS NOT NULL THEN '消耗'
--     ELSE NULL
-- END
-- WHERE yj.F_PerformanceType IS NULL OR yj.F_PerformanceType = '';

-- 验证更新结果（统计各表的业绩类型分布）
SELECT 
    'lq_kd_pxmx' as table_name,
    COUNT(*) as total_count,
    SUM(CASE WHEN F_PerformanceType IS NOT NULL AND F_PerformanceType != '' THEN 1 ELSE 0 END) as '已设置',
    SUM(CASE WHEN F_PerformanceType IS NULL OR F_PerformanceType = '' THEN 1 ELSE 0 END) as '未设置'
FROM lq_kd_pxmx
WHERE F_IsEffective = 1
UNION ALL
SELECT 
    'lq_kd_jksyj' as table_name,
    COUNT(*) as total_count,
    SUM(CASE WHEN F_PerformanceType IS NOT NULL AND F_PerformanceType != '' THEN 1 ELSE 0 END) as '已设置',
    SUM(CASE WHEN F_PerformanceType IS NULL OR F_PerformanceType = '' THEN 1 ELSE 0 END) as '未设置'
FROM lq_kd_jksyj
WHERE F_IsEffective = 1
UNION ALL
SELECT 
    'lq_kd_kjbsyj' as table_name,
    COUNT(*) as total_count,
    SUM(CASE WHEN F_PerformanceType IS NOT NULL AND F_PerformanceType != '' THEN 1 ELSE 0 END) as '已设置',
    SUM(CASE WHEN F_PerformanceType IS NULL OR F_PerformanceType = '' THEN 1 ELSE 0 END) as '未设置'
FROM lq_kd_kjbsyj
WHERE F_IsEffective = 1
UNION ALL
SELECT 
    'lq_xh_pxmx' as table_name,
    COUNT(*) as total_count,
    SUM(CASE WHEN F_PerformanceType IS NOT NULL AND F_PerformanceType != '' THEN 1 ELSE 0 END) as '已设置',
    SUM(CASE WHEN F_PerformanceType IS NULL OR F_PerformanceType = '' THEN 1 ELSE 0 END) as '未设置'
FROM lq_xh_pxmx
WHERE F_IsEffective = 1 OR F_IsEffective IS NULL
UNION ALL
SELECT 
    'lq_xh_jksyj' as table_name,
    COUNT(*) as total_count,
    SUM(CASE WHEN F_PerformanceType IS NOT NULL AND F_PerformanceType != '' THEN 1 ELSE 0 END) as '已设置',
    SUM(CASE WHEN F_PerformanceType IS NULL OR F_PerformanceType = '' THEN 1 ELSE 0 END) as '未设置'
FROM lq_xh_jksyj
WHERE F_IsEffective = 1 OR F_IsEffective IS NULL
UNION ALL
SELECT 
    'lq_xh_kjbsyj' as table_name,
    COUNT(*) as total_count,
    SUM(CASE WHEN F_PerformanceType IS NOT NULL AND F_PerformanceType != '' THEN 1 ELSE 0 END) as '已设置',
    SUM(CASE WHEN F_PerformanceType IS NULL OR F_PerformanceType = '' THEN 1 ELSE 0 END) as '未设置'
FROM lq_xh_kjbsyj
WHERE F_IsEffective = 1 OR F_IsEffective IS NULL
UNION ALL
SELECT 
    'lq_hytk_mx' as table_name,
    COUNT(*) as total_count,
    SUM(CASE WHEN F_PerformanceType IS NOT NULL AND F_PerformanceType != '' THEN 1 ELSE 0 END) as '已设置',
    SUM(CASE WHEN F_PerformanceType IS NULL OR F_PerformanceType = '' THEN 1 ELSE 0 END) as '未设置'
FROM lq_hytk_mx
WHERE F_IsEffective = 1
UNION ALL
SELECT 
    'lq_hytk_jksyj' as table_name,
    COUNT(*) as total_count,
    SUM(CASE WHEN F_PerformanceType IS NOT NULL AND F_PerformanceType != '' THEN 1 ELSE 0 END) as '已设置',
    SUM(CASE WHEN F_PerformanceType IS NULL OR F_PerformanceType = '' THEN 1 ELSE 0 END) as '未设置'
FROM lq_hytk_jksyj
WHERE F_IsEffective = 1
UNION ALL
SELECT 
    'lq_hytk_kjbsyj' as table_name,
    COUNT(*) as total_count,
    SUM(CASE WHEN F_PerformanceType IS NOT NULL AND F_PerformanceType != '' THEN 1 ELSE 0 END) as '已设置',
    SUM(CASE WHEN F_PerformanceType IS NULL OR F_PerformanceType = '' THEN 1 ELSE 0 END) as '未设置'
FROM lq_hytk_kjbsyj
WHERE F_IsEffective = 1;

-- 查看业绩类型的值分布（从品项表fl3字段）
SELECT 
    'lq_xmzl.fl3 值分布' as description,
    fl3 as performance_type,
    COUNT(*) as count
FROM lq_xmzl
WHERE fl3 IS NOT NULL AND fl3 != ''
GROUP BY fl3
ORDER BY count DESC;