all-project/test/指派人员数据未更新问题诊断SQL.sql

-- ==========================================
-- 指派人员数据未更新问题诊断脚本
-- 功能：诊断维修人员角色配置和分派功能相关问题
-- 时间：2025-06-30 18:55:00
-- 说明：完整分析维修人员查询和分派功能的问题
-- ==========================================

-- 第一部分：检查维修人员角色配置
-- ==========================================

-- 1. 查询所有可能的维修相关角色
SELECT 
    role_id, 
    role_name, 
    role_key, 
    status,
    del_flag,
    create_time,
    remark
FROM sys_role 
WHERE (role_name LIKE '%维修%' 
   OR role_key LIKE '%maintenance%' 
   OR role_key LIKE '%worker%'
   OR role_name LIKE '%工人%'
   OR role_name LIKE '%技工%')
   AND del_flag = '0'
ORDER BY role_id;

-- 2. 查询角色ID=5的角色信息（XML中可能使用的角色）
SELECT 
    role_id, 
    role_name, 
    role_key, 
    status,
    remark
FROM sys_role 
WHERE role_id = 5;

-- 3. 查询所有拥有维修相关角色的用户
SELECT 
    u.user_id,
    u.user_name,
    u.nick_name,
    u.status as user_status,
    r.role_id,
    r.role_name,
    r.role_key,
    d.dept_name
FROM sys_user u
JOIN sys_user_role ur ON u.user_id = ur.user_id
JOIN sys_role r ON ur.role_id = r.role_id
LEFT JOIN sys_dept d ON u.dept_id = d.dept_id
WHERE (r.role_name LIKE '%维修%' 
   OR r.role_key LIKE '%maintenance%' 
   OR r.role_key LIKE '%worker%'
   OR r.role_id = 5)
   AND u.del_flag = '0'
   AND r.del_flag = '0'
ORDER BY u.user_id;

-- 第二部分：测试当前维修人员查询SQL
-- ==========================================

-- 4. 测试原始SQL（可能有问题的版本）
SELECT 
    u.user_id as value,
    u.nick_name as label,
    u.user_name as userName,
    d.dept_name as deptName
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;

-- 5. 测试修复后的SQL（支持多种角色匹配）
SELECT 
    u.user_id as value,
    u.nick_name as label,
    u.user_name as userName,
    d.dept_name as deptName
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_key = 'maintenance_worker' 
   OR r.role_name = '维修人员' 
   OR r.role_id = 5
   OR r.role_name LIKE '%维修%'
   OR r.role_key LIKE '%worker%')
AND u.status = '0'
AND u.del_flag = '0'
ORDER BY u.user_id;

-- 第三部分：检查分派相关数据
-- ==========================================

-- 6. 查询最近的分派记录，检查数据完整性
SELECT 
    id,
    order_no,
    title,
    order_status,
    assignee_id,
    assignee_name,  -- 检查此字段是否为空
    assign_time,
    assign_remark,  -- 检查备注是否保存
    create_time,
    update_time
FROM pm_repair_order 
WHERE assign_time IS NOT NULL 
   OR assignee_id IS NOT NULL
ORDER BY COALESCE(assign_time, update_time) DESC
LIMIT 10;

-- 7. 查询待分派的工单（用于测试）
SELECT 
    id,
    order_no,
    title,
    order_status,
    assignee_id,
    assignee_name,
    reporter_name,
    create_time
FROM pm_repair_order 
WHERE order_status = '1'  -- 待分派
   AND deleted = 0
ORDER BY create_time DESC
LIMIT 5;

-- 第四部分：创建测试数据（如果需要）
-- ==========================================

-- 8. 创建维修人员角色（如果不存在）
INSERT INTO sys_role (
    role_id, tenant_id, role_name, role_key, role_sort, data_scope, 
    menu_check_strictly, dept_check_strictly, status, del_flag, 
    create_by, create_time, remark
)
SELECT 
    5, '000000', '维修人员', 'maintenance_worker', 5, '1', 
    1, 1, '0', '0', 1, NOW(), '物业维修人员角色'
WHERE NOT EXISTS (
    SELECT 1 FROM sys_role 
    WHERE role_key = 'maintenance_worker' OR role_id = 5
);

-- 9. 为测试用户zwc007分配维修人员角色
INSERT IGNORE INTO sys_user_role (user_id, role_id)
SELECT u.user_id, 5
FROM sys_user u
WHERE u.user_name = 'zwc007' 
  AND EXISTS (SELECT 1 FROM sys_role WHERE role_id = 5);

-- 第五部分：验证修复结果
-- ==========================================

-- 10. 验证维修人员角色分配
SELECT 
    '维修人员角色验证' as check_type,
    COUNT(*) as count,
    GROUP_CONCAT(u.user_name) as users
FROM sys_user u
JOIN sys_user_role ur ON u.user_id = ur.user_id
JOIN sys_role r ON ur.role_id = r.role_id
WHERE (r.role_key = 'maintenance_worker' OR r.role_id = 5)
  AND u.status = '0' AND u.del_flag = '0';

-- 11. 验证维修人员查询结果
SELECT 
    '维修人员查询验证' as check_type,
    COUNT(*) as total_workers,
    MAX(u.user_name) as sample_user
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_key = 'maintenance_worker' 
   OR r.role_name = '维修人员' 
   OR r.role_id = 5)
AND u.status = '0'
AND u.del_flag = '0';

-- 结束
SELECT '诊断脚本执行完成' as status, NOW() as execution_time;