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

-- ==========================================
-- 更新报修订单维修人员数据脚本
-- 功能：将现有报修订单的维修人员更新为拥有"维修人员"角色的真实用户
-- 时间：2025-06-30 14:15:00
-- 说明：确保测试数据的真实性和一致性
-- ==========================================

-- 查看当前拥有"维修人员"角色的用户
SELECT '=== 拥有维修人员角色的用户 ===' as info;
SELECT 
    u.user_id,
    u.user_name,
    u.nick_name,
    d.dept_name
FROM sys_user u
LEFT JOIN sys_dept d ON u.dept_id = d.dept_id
INNER JOIN sys_user_role ur ON u.user_id = ur.user_id
INNER JOIN sys_role r ON ur.role_id = r.role_id
WHERE r.role_name = '维修人员'
AND u.status = '0'
AND u.del_flag = '0'
ORDER BY u.user_id;

-- 查看当前需要更新的报修订单
SELECT '=== 需要更新的报修订单 ===' as info;
SELECT 
    id, 
    order_no, 
    title, 
    assignee_id, 
    assignee_name, 
    order_status
FROM pm_repair_order 
WHERE assignee_id IS NOT NULL 
ORDER BY id;

-- 更新报修订单的维修人员
-- 将订单分配给两个维修人员：zwc005(陆雄) 和 zwc007(邹凯)

-- 更新订单 1002, 1004, 1009, 1010 分配给 zwc005(陆雄)
UPDATE pm_repair_order 
SET 
    assignee_id = 2125,
    assignee_name = '总务处干事陆雄',
    update_time = now(),
    update_by = 1
WHERE id IN (1002, 1004, 1009, 1010);

-- 更新订单 1003, 1005, 1008 分配给 zwc007(邹凯)
UPDATE pm_repair_order 
SET 
    assignee_id = 2127,
    assignee_name = '总务处干事邹凯',
    update_time = now(),
    update_by = 1
WHERE id IN (1003, 1005, 1008);

-- 验证更新结果
SELECT '=== 更新后的报修订单 ===' as info;
SELECT 
    ro.id,
    ro.order_no,
    ro.title,
    ro.assignee_id,
    ro.assignee_name,
    ro.order_status,
    u.user_name as assignee_username,
    d.dept_name as assignee_dept
FROM pm_repair_order ro
LEFT JOIN sys_user u ON ro.assignee_id = u.user_id
LEFT JOIN sys_dept d ON u.dept_id = d.dept_id
WHERE ro.assignee_id IS NOT NULL 
ORDER BY ro.id;

-- 统计更新结果
SELECT '=== 更新统计 ===' as info;
SELECT 
    COUNT(*) as total_assigned_orders,
    COUNT(CASE WHEN assignee_id = 2125 THEN 1 END) as orders_assigned_to_luoxiong,
    COUNT(CASE WHEN assignee_id = 2127 THEN 1 END) as orders_assigned_to_zoukai
FROM pm_repair_order 
WHERE assignee_id IS NOT NULL;