-- ============================================
-- 批量更新品项明细表的品项分类字段
-- ============================================
-- 说明：此脚本用于批量更新以下表的 F_ItemCategory 字段
-- 1. lq_kd_pxmx（开单品项明细表）
-- 2. lq_kd_deductinfo（开单扣减信息表）
-- 3. lq_xh_pxmx（耗卡品项明细表）
-- 4. lq_hytk_mx（会员退卡明细表）
-- 
-- 数据来源：从关联的项目资料表（lq_xmzl）的 qt2 字段获取品项分类
-- 
-- 关联关系：
-- - lq_kd_pxmx.px = lq_xmzl.F_Id
-- - lq_kd_deductinfo.F_ItemId = lq_xmzl.F_Id
-- - lq_xh_pxmx.px = lq_xmzl.F_Id
-- - lq_hytk_mx.px = lq_xmzl.F_Id
-- 
-- 更新逻辑：
-- - 更新所有记录（不判断是否有效）
-- - 只更新关联的项目资料存在且qt2字段有值的记录
-- - 从 lq_xmzl.qt2 字段获取品项分类（医美/科美/生美）

-- ============================================
-- 1. 更新开单品项明细表的品项分类字段
-- ============================================
UPDATE lq_kd_pxmx pxmx
INNER JOIN lq_xmzl xmzl ON pxmx.px = xmzl.F_Id
SET pxmx.F_ItemCategory = xmzl.qt2
WHERE xmzl.qt2 IS NOT NULL
  AND xmzl.qt2 != '';

-- ============================================
-- 2. 更新开单扣减信息表的品项分类字段
-- ============================================
UPDATE lq_kd_deductinfo deduct
INNER JOIN lq_xmzl xmzl ON deduct.F_ItemId = xmzl.F_Id
SET deduct.F_ItemCategory = xmzl.qt2
WHERE xmzl.qt2 IS NOT NULL
  AND xmzl.qt2 != '';

-- ============================================
-- 3. 更新耗卡品项明细表的品项分类字段
-- ============================================
UPDATE lq_xh_pxmx pxmx
INNER JOIN lq_xmzl xmzl ON pxmx.px = xmzl.F_Id
SET pxmx.F_ItemCategory = xmzl.qt2
WHERE xmzl.qt2 IS NOT NULL
  AND xmzl.qt2 != '';

-- ============================================
-- 4. 更新会员退卡明细表的品项分类字段
-- ============================================
UPDATE lq_hytk_mx mx
INNER JOIN lq_xmzl xmzl ON mx.px = xmzl.F_Id
SET mx.F_ItemCategory = xmzl.qt2
WHERE xmzl.qt2 IS NOT NULL
  AND xmzl.qt2 != '';

-- ============================================
-- 5. 验证更新结果
-- ============================================
-- 查看 lq_kd_pxmx 更新后的统计信息
-- SELECT 
--     '开单品项明细' AS 表名,
--     F_ItemCategory AS 品项分类,
--     COUNT(*) AS 记录数
-- FROM lq_kd_pxmx
-- GROUP BY F_ItemCategory
-- ORDER BY 记录数 DESC;

-- 查看 lq_kd_deductinfo 更新后的统计信息
-- SELECT 
--     '开单扣减信息' AS 表名,
--     F_ItemCategory AS 品项分类,
--     COUNT(*) AS 记录数
-- FROM lq_kd_deductinfo
-- GROUP BY F_ItemCategory
-- ORDER BY 记录数 DESC;

-- 查看 lq_xh_pxmx 更新后的统计信息
-- SELECT 
--     '耗卡品项明细' AS 表名,
--     F_ItemCategory AS 品项分类,
--     COUNT(*) AS 记录数
-- FROM lq_xh_pxmx
-- GROUP BY F_ItemCategory
-- ORDER BY 记录数 DESC;

-- 查看 lq_hytk_mx 更新后的统计信息
-- SELECT 
--     '会员退卡明细' AS 表名,
--     F_ItemCategory AS 品项分类,
--     COUNT(*) AS 记录数
-- FROM lq_hytk_mx
-- GROUP BY F_ItemCategory
-- ORDER BY 记录数 DESC;

-- 查看未更新的记录数（关联的项目资料不存在或qt2为空）
-- 
-- lq_kd_pxmx 未更新记录数
-- SELECT COUNT(*) AS 未更新记录数
-- FROM lq_kd_pxmx pxmx
-- LEFT JOIN lq_xmzl xmzl ON pxmx.px = xmzl.F_Id
-- WHERE xmzl.F_Id IS NULL 
--    OR xmzl.qt2 IS NULL 
--    OR xmzl.qt2 = '';

-- lq_kd_deductinfo 未更新记录数
-- SELECT COUNT(*) AS 未更新记录数
-- FROM lq_kd_deductinfo deduct
-- LEFT JOIN lq_xmzl xmzl ON deduct.F_ItemId = xmzl.F_Id
-- WHERE xmzl.F_Id IS NULL 
--    OR xmzl.qt2 IS NULL 
--    OR xmzl.qt2 = '';

-- lq_xh_pxmx 未更新记录数
-- SELECT COUNT(*) AS 未更新记录数
-- FROM lq_xh_pxmx pxmx
-- LEFT JOIN lq_xmzl xmzl ON pxmx.px = xmzl.F_Id
-- WHERE xmzl.F_Id IS NULL 
--    OR xmzl.qt2 IS NULL 
--    OR xmzl.qt2 = '';

-- lq_hytk_mx 未更新记录数
-- SELECT COUNT(*) AS 未更新记录数
-- FROM lq_hytk_mx mx
-- LEFT JOIN lq_xmzl xmzl ON mx.px = xmzl.F_Id
-- WHERE xmzl.F_Id IS NULL 
--    OR xmzl.qt2 IS NULL 
--    OR xmzl.qt2 = '';