MES/yawei-mes/.sql/2026-03-23_00_班次班组数据补齐.sql

-- ============================================================
-- 报工单班次和班组数据补齐脚本
-- 版本：v2.0.014  日期：2026-03-23  作者：Claude
-- 问题：老版数据存储的是 dict_value，新版前端需要 dict_label
-- ============================================================

-- ============================================================
-- 1. 查看当前字典配置
-- ============================================================
-- 班次字典
SELECT '=== 班次字典 ===' AS info;
SELECT dict_label, dict_value, remark
FROM sys_dict_data
WHERE dict_type = 'sys_shift_type'
ORDER BY dict_sort;

-- 班组字典
SELECT '=== 班组字典 ===' AS info;
SELECT dict_label, dict_value, remark
FROM sys_dict_data
WHERE dict_type = 'production_team'
ORDER BY dict_sort;

-- ============================================================
-- 2. 查看当前报工单中的原始数据分布
-- ============================================================
-- 班次分布
SELECT '=== 班次数据分布 ===' AS info;
SELECT shift_name, COUNT(*) as count
FROM pro_report
WHERE shift_name IS NOT NULL AND shift_name != ''
GROUP BY shift_name
ORDER BY shift_name;

-- 班组分布
SELECT '=== 班组数据分布 ===' AS info;
SELECT team_name, COUNT(*) as count
FROM pro_report
WHERE team_name IS NOT NULL AND team_name != ''
GROUP BY team_name
ORDER BY team_name;

-- ============================================================
-- 3. 执行数据更新
-- ============================================================

-- ================== 班次更新 ==================
-- 早班：MORNING -> 早班
UPDATE pro_report
SET shift_name = '早班'
WHERE shift_name = 'MORNING';

-- 中班：AFTERNOON -> 中班
UPDATE pro_report
SET shift_name = '中班'
WHERE shift_name = 'AFTERNOON';

-- 晚班：NIGHT -> 晚班
UPDATE pro_report
SET shift_name = '晚班'
WHERE shift_name = 'NIGHT';

-- 如果使用的是 A/B/C 值（根据字典配置调整）
-- UPDATE pro_report SET shift_name = '早班' WHERE shift_name = 'A';
-- UPDATE pro_report SET shift_name = '中班' WHERE shift_name = 'B';
-- UPDATE pro_report SET shift_name = '晚班' WHERE shift_name = 'C';

-- ================== 班组更新 ==================
-- 甲班：GROUP_A -> 甲班
UPDATE pro_report
SET team_name = '甲班'
WHERE team_name = 'GROUP_A';

-- 乙班：GROUP_B -> 乙班
UPDATE pro_report
SET team_name = '乙班'
WHERE team_name = 'GROUP_B';

-- 丙班：GROUP_C -> 丙班
UPDATE pro_report
SET team_name = '丙班'
WHERE team_name = 'GROUP_C';

-- 丁班（如有）
-- UPDATE pro_report SET team_name = '丁班' WHERE team_name = 'GROUP_D';

-- ============================================================
-- 4. 验证更新结果
-- ============================================================
SELECT '=== 更新后班次分布 ===' AS info;
SELECT shift_name, COUNT(*) as count
FROM pro_report
WHERE shift_name IS NOT NULL AND shift_name != ''
GROUP BY shift_name
ORDER BY shift_name;

SELECT '=== 更新后班组分布 ===' AS info;
SELECT team_name, COUNT(*) as count
FROM pro_report
WHERE team_name IS NOT NULL AND team_name != ''
GROUP BY team_name
ORDER BY team_name;

-- ============================================================
-- 5. 影响行数统计（执行前可预估）
-- ============================================================
SELECT '=== 影响行数预估 ===' AS info;
SELECT
    (SELECT COUNT(*) FROM pro_report WHERE shift_name = 'MORNING') AS '早班待更新',
    (SELECT COUNT(*) FROM pro_report WHERE shift_name = 'AFTERNOON') AS '中班待更新',
    (SELECT COUNT(*) FROM pro_report WHERE shift_name = 'NIGHT') AS '晚班待更新',
    (SELECT COUNT(*) FROM pro_report WHERE team_name = 'GROUP_A') AS '甲班待更新',
    (SELECT COUNT(*) FROM pro_report WHERE team_name = 'GROUP_B') AS '乙班待更新',
    (SELECT COUNT(*) FROM pro_report WHERE team_name = 'GROUP_C') AS '丙班待更新';

-- ============================================================
-- 执行说明
-- ============================================================
-- 1. 请在测试环境先执行此脚本
-- 2. 验证数据无误后再在生产环境执行
-- 3. 执行前建议备份数据库
-- 4. 如果你的字典值与脚本中不同，请根据实际情况调整 UPDATE 语句
-- ============================================================