antissoft / lvqianmeiye_ERP

-- ============================================
-- 修复储扣表（lq_kd_deductinfo）中 F_ItemCategory 字段为空的数据
-- ============================================
-- 说明：此脚本用于修复储扣表中品项分类字段为空的历史数据
-- 
-- 问题原因：
-- 1. 更新开单时使用了错误的字段（item.DeductId 而不是 item.ItemId）来查询品项分类
-- 2. 如果 item.ItemId 为空或无效，查询会返回 null
-- 
-- 修复逻辑：
-- 通过 F_ItemId 关联 lq_xmzl 表，获取品项分类（qt2）并更新到 F_ItemCategory 字段
-- 
-- 注意事项：
-- - 只更新有效记录（F_IsEffective = 1）
-- - 只更新分类字段为空的记录
-- - 只更新品项分类存在且不为空的记录

-- ============================================
-- 1. 查看需要修复的记录数
-- ============================================
SELECT 
    COUNT(*) as TotalNullCount,
    COUNT(CASE WHEN item.qt2 IS NOT NULL THEN 1 END) as CanFixCount
FROM lq_kd_deductinfo deduct
LEFT JOIN lq_xmzl item ON deduct.F_ItemId = item.F_Id
WHERE deduct.F_IsEffective = 1 
  AND deduct.F_ItemCategory IS NULL;

-- ============================================
-- 2. 修复历史数据：从项目资料表中获取品项分类
-- ============================================
UPDATE lq_kd_deductinfo deduct
INNER JOIN lq_xmzl item ON deduct.F_ItemId = item.F_Id
SET deduct.F_ItemCategory = item.qt2
WHERE deduct.F_IsEffective = 1 
  AND deduct.F_ItemCategory IS NULL
  AND item.qt2 IS NOT NULL;

-- ============================================
-- 3. 验证修复结果
-- ============================================
-- 查看修复后的统计信息
SELECT 
    COUNT(*) as TotalCount,
    COUNT(F_ItemCategory) as HasCategoryCount,
    COUNT(*) - COUNT(F_ItemCategory) as NullCategoryCount
FROM lq_kd_deductinfo
WHERE F_IsEffective = 1;

-- 查看仍然为空的记录（需要人工处理）
SELECT 
    deduct.F_Id,
    deduct.F_BillingId,
    deduct.F_ItemId,
    deduct.F_ItemName,
    deduct.F_ItemCategory,
    item.qt2 as ItemQt2,
    CASE 
        WHEN item.F_Id IS NULL THEN '品项不存在'
        WHEN item.qt2 IS NULL THEN '品项分类为空'
        ELSE '其他原因'
    END as Reason
FROM lq_kd_deductinfo deduct
LEFT JOIN lq_xmzl item ON deduct.F_ItemId = item.F_Id
WHERE deduct.F_IsEffective = 1 
  AND deduct.F_ItemCategory IS NULL
LIMIT 10;