all-project/test/update-repair-orders-reporter.sql

-- ==========================================
-- 报修订单报修人数据更新脚本
-- 功能：将测试环境中的虚假报修人更新为数据库中的真实用户
-- 时间：2025-06-30 15:30:00
-- 说明：将原始的测试报修人（ID: 10001-10012）替换为真实系统用户
-- ==========================================

-- 第一步：备份原始数据（可选，用于回滚）
-- SELECT id, order_no, title, reporter_id, reporter_name, reporter_phone, reporter_dept 
-- FROM pm_repair_order 
-- WHERE reporter_id BETWEEN 10001 AND 10012 
-- ORDER BY id;

-- 第二步：更新报修人为真实用户
-- 更新策略：从不同学院和部门选择合适的用户作为报修人，确保数据真实性

-- 订单 1001：宿舍A304水龙头漏水 -> 智控学院学生张三
UPDATE pm_repair_order 
SET 
    reporter_id = 2001,
    reporter_name = '智控学院张三',
    reporter_phone = '13800138001',
    reporter_dept = '智能控制学院',
    update_time = NOW(),
    update_by = 1
WHERE id = 1001 AND reporter_id = 10001;

-- 订单 1002：教学楼B201投影仪故障 -> 智造学院老师陈刚
UPDATE pm_repair_order 
SET 
    reporter_id = 2011,
    reporter_name = '智造学院陈刚',
    reporter_phone = '13800138011',
    reporter_dept = '智能制造学院',
    update_time = NOW(),
    update_by = 1
WHERE id = 1002 AND reporter_id = 10002;

-- 订单 1003：图书馆空调不制热 -> 医药学院老师郑华
UPDATE pm_repair_order 
SET 
    reporter_id = 2021,
    reporter_name = '医药学院郑华',
    reporter_phone = '13800138021',
    reporter_dept = '医药健康学院',
    update_time = NOW(),
    update_by = 1
WHERE id = 1003 AND reporter_id = 10003;

-- 订单 1004：办公室B302灯管不亮 -> 餐饮学院许冰
UPDATE pm_repair_order 
SET 
    reporter_id = 2031,
    reporter_name = '餐饮学院许冰',
    reporter_phone = '13800138031',
    reporter_dept = '餐饮休闲学院',
    update_time = NOW(),
    update_by = 1
WHERE id = 1004 AND reporter_id = 10004;

-- 订单 1005：宿舍C205门锁故障 -> 数信学院学生廖洋
UPDATE pm_repair_order 
SET 
    reporter_id = 2041,
    reporter_name = '数信学院廖洋',
    reporter_phone = '13800138041',
    reporter_dept = '数字信息学院',
    update_time = NOW(),
    update_by = 1
WHERE id = 1005 AND reporter_id = 10005;

-- 订单 1006：食堂桌椅松动 -> 人文学院谭老师
UPDATE pm_repair_order 
SET 
    reporter_id = 2071,
    reporter_name = '人文学院谭老师',
    reporter_phone = '13800138071',
    reporter_dept = '人文学院',
    update_time = NOW(),
    update_by = 1
WHERE id = 1006 AND reporter_id = 10006;

-- 订单 1007：实验楼D101网络故障 -> 生态园林金老师
UPDATE pm_repair_order 
SET 
    reporter_id = 2081,
    reporter_name = '生态园林金老师',
    reporter_phone = '13800138081',
    reporter_dept = '生态园林学院',
    update_time = NOW(),
    update_by = 1
WHERE id = 1007 AND reporter_id = 10007;

-- 订单 1008：体育馆洗手间堵塞 -> 智控学院学生李四
UPDATE pm_repair_order 
SET 
    reporter_id = 2002,
    reporter_name = '智控学院李四',
    reporter_phone = '13800138002',
    reporter_dept = '智能控制学院',
    update_time = NOW(),
    update_by = 1
WHERE id = 1008 AND reporter_id = 10008;

-- 订单 1009：停车场路灯损坏 -> 办公室主任褚宁华
UPDATE pm_repair_order 
SET 
    reporter_id = 2061,
    reporter_name = '办公室主任褚宁华',
    reporter_phone = '13800138061',
    reporter_dept = '办公室',
    update_time = NOW(),
    update_by = 1
WHERE id = 1009 AND reporter_id = 10009;

-- 订单 1010：学生活动中心音响故障 -> 人事处处长林明
UPDATE pm_repair_order 
SET 
    reporter_id = 2091,
    reporter_name = '人事处处长林明',
    reporter_phone = '13800138091',
    reporter_dept = '人事处',
    update_time = NOW(),
    update_by = 1
WHERE id = 1010 AND reporter_id = 10010;

-- 订单 1011：宿舍E506窗户漏风 -> 智造学院学生刘敏
UPDATE pm_repair_order 
SET 
    reporter_id = 2012,
    reporter_name = '智造学院刘敏',
    reporter_phone = '13800138012',
    reporter_dept = '智能制造学院',
    update_time = NOW(),
    update_by = 1
WHERE id = 1011 AND reporter_id = 10011;

-- 订单 1012：计算机实验室空调噪音大 -> 数信学院老师马悦  
UPDATE pm_repair_order 
SET 
    reporter_id = 2042,
    reporter_name = '数信学院马悦',
    reporter_phone = '13800138042',
    reporter_dept = '数字信息学院',
    update_time = NOW(),
    update_by = 1
WHERE id = 1012 AND reporter_id = 10012;

-- 第三步：验证更新结果
SELECT 
    id as '订单ID',
    order_no as '工单编号',
    title as '标题',
    reporter_id as '报修人ID',
    reporter_name as '报修人姓名',
    reporter_phone as '联系电话',
    reporter_dept as '所属部门',
    CASE order_status
        WHEN '1' THEN '待处理'
        WHEN '2' THEN '已分派'
        WHEN '3' THEN '进行中'
        WHEN '4' THEN '已完成'
        WHEN '5' THEN '已评价'
        ELSE '未知状态'
    END as '状态'
FROM pm_repair_order 
WHERE id BETWEEN 1001 AND 1012
ORDER BY id;

-- 第四步：统计更新情况
SELECT 
    '报修人更新统计' as message,
    COUNT(*) as total_orders,
    COUNT(CASE WHEN reporter_id BETWEEN 2001 AND 2130 THEN 1 END) as updated_orders,
    COUNT(CASE WHEN reporter_id BETWEEN 10001 AND 10012 THEN 1 END) as old_orders
FROM pm_repair_order 
WHERE id BETWEEN 1001 AND 1012;

-- ==========================================
-- 执行说明：
-- 1. 本脚本将12个测试报修订单的报修人更新为真实系统用户
-- 2. 选择了来自不同学院和部门的用户，确保数据的多样性
-- 3. 保持了报修内容与报修人身份的合理性
-- 4. 更新后的报修人都是数据库中存在的真实系统用户
-- ==========================================