antissoft / lvqianmeiye_ERP

-- ============================================
-- 修复业绩类型字段：根据品相表fl3字段更新所有相关表的F_PerformanceType
-- ============================================
-- 说明：
-- 1. 此SQL用于修复因品相表（lq_xmzl）fl3字段设置错误导致的业绩类型问题
-- 2. 会更新所有开单、耗卡、退卡相关表的F_PerformanceType字段
-- 3. 更新逻辑：根据品相表的fl3字段，重新设置所有相关表的业绩类型
-- 4. 注意：此SQL会覆盖原有的F_PerformanceType值，请确保品相表的fl3字段已正确修复
-- 
-- 执行前请确认：
-- 1. 品相表（lq_xmzl）的fl3字段已经修复正确
-- 2. 已备份相关数据表
-- 3. 建议在测试环境先执行验证
-- ============================================

-- ============================================
-- 1. 更新开单品项表（lq_kd_pxmx）
-- ============================================
-- 通过品项ID（px字段）关联品相表的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_IsEffective = 1;

-- ============================================
-- 2. 更新开单健康师业绩表（lq_kd_jksyj）
-- ============================================
-- 优先使用F_ItemId，如果没有则通过F_kdpxid关联开单品项表获取品项ID
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_IsEffective = 1
  AND xmzl.F_Id IS NOT NULL;

-- ============================================
-- 3. 更新开单科技部老师业绩表（lq_kd_kjbsyj）
-- ============================================
-- 优先使用F_ItemId，如果没有则通过F_kdpxid关联开单品项表获取品项ID
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_IsEffective = 1
  AND xmzl.F_Id IS NOT NULL;

-- ============================================
-- 4. 更新消耗品项表（lq_xh_pxmx）
-- ============================================
-- 通过品项ID（px字段）关联品相表的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_IsEffective = 1 OR pxmx.F_IsEffective IS NULL);

-- ============================================
-- 5. 更新消耗健康师业绩表（lq_xh_jksyj）
-- ============================================
-- 优先使用F_ItemId，如果没有则通过F_kdpxid关联消耗品项表获取品项ID
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_IsEffective = 1 OR jksyj.F_IsEffective IS NULL)
  AND xmzl.F_Id IS NOT NULL;

-- ============================================
-- 6. 更新消耗科技部老师业绩表（lq_xh_kjbsyj）
-- ============================================
-- 优先使用F_ItemId，如果没有则通过F_hkpxid关联消耗品项表获取品项ID
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_IsEffective = 1 OR kjbsyj.F_IsEffective IS NULL)
  AND xmzl.F_Id IS NOT NULL;

-- ============================================
-- 7. 更新退卡明细表（lq_hytk_mx）
-- ============================================
-- 通过品项ID（px字段）关联品相表的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_IsEffective = 1;

-- ============================================
-- 8. 更新退卡健康师业绩表（lq_hytk_jksyj）
-- ============================================
-- 优先使用F_ItemId，如果没有则通过F_CardReturn关联退卡明细表获取品项ID
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_IsEffective = 1
  AND xmzl.F_Id IS NOT NULL;

-- ============================================
-- 9. 更新退卡科技部老师业绩表（lq_hytk_kjbsyj）
-- ============================================
-- 优先使用F_ItemId，如果没有则通过F_CardReturn关联退卡明细表获取品项ID
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_IsEffective = 1
  AND xmzl.F_Id IS NOT NULL;

-- ============================================
-- 10. 验证更新结果（统计各表的业绩类型分布）
-- ============================================
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;

-- ============================================
-- 11. 查看品相表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;

-- ============================================
-- 12. 查看各表业绩类型的值分布（用于对比验证）
-- ============================================
SELECT 
    'lq_kd_pxmx' as table_name,
    F_PerformanceType as performance_type,
    COUNT(*) as count
FROM lq_kd_pxmx
WHERE F_IsEffective = 1
  AND F_PerformanceType IS NOT NULL AND F_PerformanceType != ''
GROUP BY F_PerformanceType
UNION ALL
SELECT 
    'lq_kd_jksyj' as table_name,
    F_PerformanceType as performance_type,
    COUNT(*) as count
FROM lq_kd_jksyj
WHERE F_IsEffective = 1
  AND F_PerformanceType IS NOT NULL AND F_PerformanceType != ''
GROUP BY F_PerformanceType
UNION ALL
SELECT 
    'lq_kd_kjbsyj' as table_name,
    F_PerformanceType as performance_type,
    COUNT(*) as count
FROM lq_kd_kjbsyj
WHERE F_IsEffective = 1
  AND F_PerformanceType IS NOT NULL AND F_PerformanceType != ''
GROUP BY F_PerformanceType
UNION ALL
SELECT 
    'lq_xh_pxmx' as table_name,
    F_PerformanceType as performance_type,
    COUNT(*) as count
FROM lq_xh_pxmx
WHERE (F_IsEffective = 1 OR F_IsEffective IS NULL)
  AND F_PerformanceType IS NOT NULL AND F_PerformanceType != ''
GROUP BY F_PerformanceType
UNION ALL
SELECT 
    'lq_xh_jksyj' as table_name,
    F_PerformanceType as performance_type,
    COUNT(*) as count
FROM lq_xh_jksyj
WHERE (F_IsEffective = 1 OR F_IsEffective IS NULL)
  AND F_PerformanceType IS NOT NULL AND F_PerformanceType != ''
GROUP BY F_PerformanceType
UNION ALL
SELECT 
    'lq_xh_kjbsyj' as table_name,
    F_PerformanceType as performance_type,
    COUNT(*) as count
FROM lq_xh_kjbsyj
WHERE (F_IsEffective = 1 OR F_IsEffective IS NULL)
  AND F_PerformanceType IS NOT NULL AND F_PerformanceType != ''
GROUP BY F_PerformanceType
UNION ALL
SELECT 
    'lq_hytk_mx' as table_name,
    F_PerformanceType as performance_type,
    COUNT(*) as count
FROM lq_hytk_mx
WHERE F_IsEffective = 1
  AND F_PerformanceType IS NOT NULL AND F_PerformanceType != ''
GROUP BY F_PerformanceType
UNION ALL
SELECT 
    'lq_hytk_jksyj' as table_name,
    F_PerformanceType as performance_type,
    COUNT(*) as count
FROM lq_hytk_jksyj
WHERE F_IsEffective = 1
  AND F_PerformanceType IS NOT NULL AND F_PerformanceType != ''
GROUP BY F_PerformanceType
UNION ALL
SELECT 
    'lq_hytk_kjbsyj' as table_name,
    F_PerformanceType as performance_type,
    COUNT(*) as count
FROM lq_hytk_kjbsyj
WHERE F_IsEffective = 1
  AND F_PerformanceType IS NOT NULL AND F_PerformanceType != ''
GROUP BY F_PerformanceType
ORDER BY table_name, count DESC;

-- ============================================
-- SQL脚本执行完成
-- ============================================
-- 说明：
-- 1. 此SQL会根据品相表（lq_xmzl）的fl3字段，更新所有相关表的F_PerformanceType字段
-- 2. 更新覆盖所有有效记录，不管原来的值是什么
-- 3. 执行后请查看验证结果，确认更新是否正确
-- 4. 如果发现数据异常，请及时回滚或联系开发人员