all-project/test/zwc007-update-progress-diagnosis.sql

-- ==========================================
-- 维修人员zwc007更新进度按钮问题诊断和修复
-- 功能：诊断和修复维修人员看不见更新进度按钮的问题
-- 时间：2025-06-30 18:35:00
-- 说明：全面检查角色、权限、工单状态等因素
-- ==========================================

-- 第一部分：问题诊断查询
-- ==========================================

-- 1. 检查zwc007用户信息和角色分配
SELECT '=== 用户zwc007基本信息检查 ===' as message;
SELECT 
    u.user_id,
    u.user_name,
    u.nick_name,
    u.status as user_status,
    GROUP_CONCAT(r.role_name) as role_names,
    GROUP_CONCAT(r.role_key) as role_keys
FROM sys_user u
LEFT JOIN sys_user_role ur ON u.user_id = ur.user_id
LEFT JOIN sys_role r ON ur.role_id = r.role_id
WHERE u.user_name = 'zwc007'
GROUP BY u.user_id;

-- 2. 检查zwc007是否有maintenance_worker角色
SELECT '=== 维修人员角色检查 ===' as message;
SELECT 
    CASE 
        WHEN COUNT(*) > 0 THEN 'zwc007具有维修人员角色' 
        ELSE 'zwc007缺少维修人员角色！' 
    END as role_status
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 u.user_name = 'zwc007' AND r.role_key = 'maintenance_worker';

-- 3. 检查zwc007的权限配置
SELECT '=== zwc007权限配置检查 ===' as message;
SELECT DISTINCT
    m.menu_name,
    m.perms,
    m.menu_type,
    CASE WHEN m.perms = 'pm:repairOrder:updateProgress' THEN '*** 关键权限 ***' ELSE '' END as important
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
JOIN sys_role_menu rm ON r.role_id = rm.role_id
JOIN sys_menu m ON rm.menu_id = m.menu_id
WHERE u.user_name = 'zwc007' 
AND m.perms LIKE 'pm:repairOrder:%'
ORDER BY m.perms;

-- 4. 检查工单分派情况（查看zwc007是否为任何工单的负责人）
SELECT '=== 工单分派情况检查 ===' as message;
SELECT 
    ro.id,
    ro.order_no,
    ro.title,
    ro.order_status,
    CASE ro.order_status
        WHEN '1' THEN '待分派'
        WHEN '2' THEN '已分派'
        WHEN '3' THEN '进行中'
        WHEN '4' THEN '已完成'
        ELSE '其他状态'
    END as status_text,
    ro.assignee_id,
    u_assignee.user_name as assignee_name,
    CASE 
        WHEN u_zwc.user_id = ro.assignee_id THEN '是负责人 ✓' 
        ELSE '非负责人 ✗' 
    END as is_assignee
FROM pm_repair_order ro
LEFT JOIN sys_user u_assignee ON ro.assignee_id = u_assignee.user_id
CROSS JOIN (SELECT user_id FROM sys_user WHERE user_name = 'zwc007') u_zwc
WHERE ro.assignee_id IS NOT NULL
ORDER BY ro.create_time DESC
LIMIT 10;

-- 5. 检查状态为"已分派"且zwc007为负责人的工单（这些工单应该显示更新进度按钮）
SELECT '=== 应显示更新进度按钮的工单 ===' as message;
SELECT 
    ro.id,
    ro.order_no,
    ro.title,
    ro.order_status,
    '应该显示更新进度按钮' as expected_button
FROM pm_repair_order ro
JOIN sys_user u ON ro.assignee_id = u.user_id
WHERE u.user_name = 'zwc007' 
AND ro.order_status = '2'  -- 已分派状态
ORDER BY ro.create_time DESC;

-- 第二部分：问题修复方案
-- ==========================================

-- 修复1：确保zwc007有维修人员角色
INSERT IGNORE INTO sys_user_role (user_id, role_id)
SELECT u.user_id, r.role_id
FROM sys_user u, sys_role r
WHERE u.user_name = 'zwc007' 
AND r.role_key = 'maintenance_worker'
AND NOT EXISTS (
    SELECT 1 FROM sys_user_role ur2 
    WHERE ur2.user_id = u.user_id AND ur2.role_id = r.role_id
);

-- 修复2：确保维修人员角色有updateProgress权限
INSERT IGNORE INTO sys_role_menu (role_id, menu_id)
SELECT r.role_id, m.menu_id
FROM sys_role r, sys_menu m
WHERE r.role_key = 'maintenance_worker'
AND m.perms = 'pm:repairOrder:updateProgress'
AND NOT EXISTS (
    SELECT 1 FROM sys_role_menu rm2 
    WHERE rm2.role_id = r.role_id AND rm2.menu_id = m.menu_id
);

-- 修复3：如果updateProgress权限不存在，则创建该权限
INSERT INTO sys_menu (
    menu_id, menu_name, parent_id, order_num, path, component, 
    query_param, is_frame, is_cache, menu_type, visible, status, 
    perms, icon, create_by, create_time, update_by, update_time, remark
) 
SELECT 
    2117, '更新进度', 2110, 6, '', '', 
    '', 1, 0, 'F', '0', '0', 
    'pm:repairOrder:updateProgress', '#', 1, now(), 1, now(), '更新工单进度权限'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = 'pm:repairOrder:updateProgress');

-- 修复4：为维修人员角色分配新创建的updateProgress权限
INSERT IGNORE INTO sys_role_menu (role_id, menu_id)
SELECT r.role_id, m.menu_id
FROM sys_role r, sys_menu m
WHERE r.role_key = 'maintenance_worker'
AND m.perms = 'pm:repairOrder:updateProgress';

-- 修复5：创建测试工单，确保zwc007为负责人且状态为已分派
INSERT INTO pm_repair_order (
    id, order_no, title, description, repair_location, category_id, 
    priority_level, order_status, reporter_name, reporter_phone, 
    reporter_dept, assignee_id, assign_time, expected_finish_time,
    tenant_id, create_dept, create_by, create_time, update_by, update_time, deleted
)
SELECT 
    9999, 'RO-TEST-20250630-001', 'zwc007测试工单-更新进度', 
    '用于测试zwc007更新进度按钮显示的测试工单', '测试楼101', 1,
    2, '2', '测试报修人', '13800138000',
    '测试部门', u.user_id, now(), DATE_ADD(now(), INTERVAL 7 DAY),
    '000000', 103, 1, now(), 1, now(), 0
FROM sys_user u
WHERE u.user_name = 'zwc007'
AND NOT EXISTS (SELECT 1 FROM pm_repair_order WHERE order_no = 'RO-TEST-20250630-001');

-- 第三部分：修复后验证
-- ==========================================

-- 验证1：确认zwc007角色和权限
SELECT '=== 修复后：zwc007角色和权限验证 ===' as message;
SELECT 
    u.user_name,
    r.role_name,
    r.role_key,
    COUNT(DISTINCT m.menu_id) as permission_count,
    GROUP_CONCAT(DISTINCT m.perms ORDER BY m.perms) as permissions
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
JOIN sys_role_menu rm ON r.role_id = rm.role_id
JOIN sys_menu m ON rm.menu_id = m.menu_id
WHERE u.user_name = 'zwc007' 
AND m.perms LIKE 'pm:repairOrder:%'
GROUP BY u.user_id, r.role_id
ORDER BY r.role_key;

-- 验证2：确认updateProgress权限是否正确配置
SELECT '=== 修复后：updateProgress权限配置验证 ===' as message;
SELECT 
    r.role_name,
    r.role_key,
    m.menu_name,
    m.perms,
    '权限已配置' as status
FROM sys_role r
JOIN sys_role_menu rm ON r.role_id = rm.role_id
JOIN sys_menu m ON rm.menu_id = m.menu_id
WHERE m.perms = 'pm:repairOrder:updateProgress'
AND r.role_key IN ('maintenance_worker', 'property_maintenance_admin');

-- 验证3：确认zwc007负责的已分派工单
SELECT '=== 修复后：zwc007负责的已分派工单 ===' as message;
SELECT 
    ro.id,
    ro.order_no,
    ro.title,
    ro.order_status,
    '应显示更新进度按钮' as expected_result
FROM pm_repair_order ro
JOIN sys_user u ON ro.assignee_id = u.user_id
WHERE u.user_name = 'zwc007' 
AND ro.order_status = '2'
ORDER BY ro.create_time DESC;

-- 第四部分：操作指导
-- ==========================================
SELECT '=== 操作指导 ===' as message, 
'1. 执行以上SQL脚本完成修复' as step1,
'2. 重启前后端服务清除缓存' as step2,
'3. 使用zwc007账号重新登录系统' as step3,
'4. 查看已分派状态且自己为负责人的工单' as step4,
'5. 确认更新进度按钮是否显示' as step5;