all-project/test/RO20250126008-update-progress-fix.sql

-- ==========================================
-- 工单RO20250126008更新进度按钮修复脚本
-- 功能：针对具体工单RO20250126008和维修人员zwc007的权限问题修复
-- 时间：2025-06-30 18:45:00
-- 说明：精准定位和修复特定工单的显示问题
-- ==========================================

-- 第一部分：工单RO20250126008诊断查询
-- ==========================================

-- 1. 检查工单RO20250126008的基本信息和当前状态
SELECT '=== 工单RO20250126008基本信息 ===' 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 '已完成'
        WHEN '5' THEN '已评价'
        WHEN '6' THEN '已归档'
        WHEN '9' THEN '已取消'
        ELSE CONCAT('未知状态: ', ro.order_status)
    END as status_description,
    ro.assignee_id,
    u_assignee.user_name as assignee_name,
    u_assignee.nick_name as assignee_nick_name,
    ro.assign_time,
    ro.create_time,
    ro.repair_location,
    ro.priority_level
FROM pm_repair_order ro
LEFT JOIN sys_user u_assignee ON ro.assignee_id = u_assignee.user_id
WHERE ro.order_no = 'RO20250126008';

-- 2. 检查zwc007用户信息
SELECT '=== 用户zwc007信息检查 ===' as message;
SELECT 
    u.user_id,
    u.user_name,
    u.nick_name,
    u.status as user_status,
    CASE u.status WHEN '0' THEN '正常' WHEN '1' THEN '停用' ELSE '未知' END as status_text
FROM sys_user u
WHERE u.user_name = 'zwc007';

-- 3. 检查当前分派情况匹配性
SELECT '=== 工单分派匹配性检查 ===' as message;
SELECT 
    ro.order_no,
    ro.assignee_id as current_assignee_id,
    u_current.user_name as current_assignee_name,
    u_zwc.user_id as zwc007_user_id,
    u_zwc.user_name as zwc007_name,
    CASE 
        WHEN ro.assignee_id = u_zwc.user_id THEN '✓ zwc007是负责人'
        WHEN ro.assignee_id IS NULL THEN '✗ 工单未分派'
        ELSE CONCAT('✗ 工单分派给其他人: ', u_current.user_name)
    END as assignment_status,
    CASE 
        WHEN ro.order_status = '2' AND ro.assignee_id = u_zwc.user_id THEN '✅ 应显示更新进度按钮'
        WHEN ro.order_status != '2' THEN CONCAT('❌ 工单状态不是已分派: ', ro.order_status)
        WHEN ro.assignee_id != u_zwc.user_id THEN '❌ zwc007不是负责人'
        ELSE '❌ 不满足显示条件'
    END as button_display_status
FROM pm_repair_order ro
LEFT JOIN sys_user u_current ON ro.assignee_id = u_current.user_id
CROSS JOIN (SELECT user_id, user_name FROM sys_user WHERE user_name = 'zwc007') u_zwc
WHERE ro.order_no = 'RO20250126008';

-- 4. 检查zwc007的角色和权限
SELECT '=== zwc007角色和权限检查 ===' as message;
SELECT 
    u.user_name,
    r.role_name,
    r.role_key,
    CASE WHEN r.role_key = 'maintenance_worker' THEN '✓ 维修人员角色' ELSE '其他角色' END as role_type
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'
ORDER BY r.role_key;

-- 检查关键权限
SELECT '=== zwc007更新进度权限检查 ===' as message;
SELECT 
    m.menu_name,
    m.perms,
    m.menu_type,
    CASE WHEN m.perms = 'pm:repairOrder:updateProgress' THEN '*** 关键权限 ***' ELSE '' END as importance
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 = 'pm:repairOrder:updateProgress';

-- 第二部分：问题修复操作
-- ==========================================

-- 修复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 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');

-- 修复3：为维修人员角色分配权限
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';

-- 修复4：将工单RO20250126008分派给zwc007（关键修复）
UPDATE pm_repair_order 
SET 
    assignee_id = (SELECT user_id FROM sys_user WHERE user_name = 'zwc007'),
    assign_time = CASE WHEN assign_time IS NULL THEN now() ELSE assign_time END,
    order_status = '2',  -- 设置为已分派状态
    update_time = now(),
    update_by = 1
WHERE order_no = 'RO20250126008'
AND EXISTS (SELECT 1 FROM sys_user WHERE user_name = 'zwc007');

-- 修复5：记录分派操作到进度表
INSERT INTO pm_repair_progress (
    order_id, progress_type, progress_status, progress_detail, 
    progress_time, location_info, remark,
    tenant_id, create_dept, create_by, create_time, update_by, update_time, deleted
)
SELECT 
    ro.id, 
    '2',  -- 分派类型
    '已分派给维修人员',
    CONCAT('工单已分派给维修人员zwc007，负责处理报修问题'),
    now(),
    ro.repair_location,
    '系统自动分派 - 修复更新进度按钮显示问题',
    '000000', 103, 1, now(), 1, now(), 0
FROM pm_repair_order ro
WHERE ro.order_no = 'RO20250126008'
AND NOT EXISTS (
    SELECT 1 FROM pm_repair_progress pp 
    WHERE pp.order_id = ro.id 
    AND pp.progress_type = '2' 
    AND pp.progress_detail LIKE '%zwc007%'
);

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

-- 验证1：确认工单状态和分派
SELECT '=== 修复后：工单状态验证 ===' as message;
SELECT 
    ro.order_no,
    ro.title,
    ro.order_status,
    CASE ro.order_status
        WHEN '2' THEN '✅ 已分派状态 - 应显示更新进度按钮'
        ELSE CONCAT('❌ 状态异常: ', ro.order_status)
    END as status_check,
    ro.assignee_id,
    u.user_name as assignee_name,
    u.nick_name as assignee_nick_name,
    CASE 
        WHEN u.user_name = 'zwc007' THEN '✅ zwc007是负责人'
        ELSE '❌ 负责人不是zwc007'
    END as assignee_check,
    ro.assign_time
FROM pm_repair_order ro
LEFT JOIN sys_user u ON ro.assignee_id = u.user_id
WHERE ro.order_no = 'RO20250126008';

-- 验证2：确认zwc007权限配置
SELECT '=== 修复后：zwc007权限验证 ===' as message;
SELECT 
    '维修人员角色' as permission_type,
    CASE 
        WHEN COUNT(*) > 0 THEN '✅ 已配置'
        ELSE '❌ 未配置'
    END as 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'

UNION ALL

SELECT 
    '更新进度权限' as permission_type,
    CASE 
        WHEN COUNT(*) > 0 THEN '✅ 已配置'
        ELSE '❌ 未配置'
    END as 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
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 = 'pm:repairOrder:updateProgress';

-- 验证3：确认按钮显示条件
SELECT '=== 修复后：按钮显示条件验证 ===' as message;
SELECT 
    ro.order_no,
    '工单状态 = 2 (已分派)' as condition_1,
    CASE WHEN ro.order_status = '2' THEN '✅ 满足' ELSE '❌ 不满足' END as check_1,
    '用户是维修负责人' as condition_2,
    CASE WHEN ro.assignee_id = u.user_id THEN '✅ 满足' ELSE '❌ 不满足' END as check_2,
    '用户有maintenance_worker角色' as condition_3,
    CASE WHEN role_count.cnt > 0 THEN '✅ 满足' ELSE '❌ 不满足' END as check_3,
    '用户有updateProgress权限' as condition_4,
    CASE WHEN perm_count.cnt > 0 THEN '✅ 满足' ELSE '❌ 不满足' END as check_4,
    CASE 
        WHEN ro.order_status = '2' 
         AND ro.assignee_id = u.user_id 
         AND role_count.cnt > 0 
         AND perm_count.cnt > 0 
        THEN '🎉 所有条件满足 - 应显示更新进度按钮'
        ELSE '❌ 条件不满足 - 按钮不会显示'
    END as final_result
FROM pm_repair_order ro
JOIN sys_user u ON u.user_name = 'zwc007'
LEFT JOIN (
    SELECT ur.user_id, COUNT(*) as cnt
    FROM sys_user_role ur
    JOIN sys_role r ON ur.role_id = r.role_id
    WHERE r.role_key = 'maintenance_worker'
    GROUP BY ur.user_id
) role_count ON role_count.user_id = u.user_id
LEFT JOIN (
    SELECT ur.user_id, COUNT(*) as cnt
    FROM sys_user_role ur
    JOIN sys_role_menu rm ON ur.role_id = rm.role_id
    JOIN sys_menu m ON rm.menu_id = m.menu_id
    WHERE m.perms = 'pm:repairOrder:updateProgress'
    GROUP BY ur.user_id
) perm_count ON perm_count.user_id = u.user_id
WHERE ro.order_no = 'RO20250126008';

-- 第四部分：操作指导和确认
-- ==========================================
SELECT '=== 修复完成提示 ===' as message,
'1. 工单RO20250126008已分派给zwc007' as step1,
'2. 工单状态已设置为已分派(状态码2)' as step2,
'3. zwc007已配置维修人员角色和权限' as step3,
'4. 请重启前后端服务清除权限缓存' as step4,
'5. 使用zwc007账号登录查看该工单' as step5,
'6. 确认更新进度按钮是否显示' as step6;

-- 显示需要重启服务的提醒
SELECT '⚠️ 重要提醒' as alert_title,
'权限配置修改后必须重启前后端服务' as alert_message,
'用户需要重新登录以刷新权限缓存' as additional_note;