all-project/script/fix-approval-status-display.sql

-- 财务项目申报审批状态显示问题排查和修复脚本
-- 日期: 2025-01-26
-- 说明: 解决审批状态字段显示数字而非中文描述的问题

-- ==========================================
-- 1. 问题排查：检查当前数据状态
-- ==========================================

-- 1.1 查看当前申报记录的状态值分布
SELECT 
    approval_status, 
    COUNT(*) as record_count,
    GROUP_CONCAT(DISTINCT project_name SEPARATOR ', ') as sample_projects
FROM cw_project_application 
GROUP BY approval_status
ORDER BY record_count DESC;

-- 1.2 查看字典类型配置是否存在
SELECT * FROM sys_dict_type WHERE dict_type = 'cw_approval_status';

-- 1.3 查看字典数据配置是否完整
SELECT 
    dict_sort,
    dict_label,
    dict_value,
    css_class,
    status
FROM sys_dict_data 
WHERE dict_type = 'cw_approval_status' 
ORDER BY dict_sort;

-- 1.4 检查是否有数字格式的状态值（问题数据）
SELECT 
    id,
    application_no,
    project_name,
    approval_status,
    create_time
FROM cw_project_application 
WHERE approval_status IN ('1', '2', '3', '4', '5', '6', '7')
ORDER BY create_time DESC
LIMIT 10;

-- ==========================================
-- 2. 问题修复：数据状态值迁移
-- ==========================================

-- 2.1 备份当前数据（可选，建议执行）
-- CREATE TABLE cw_project_application_backup_20250126 AS 
-- SELECT * FROM cw_project_application;

-- 2.2 修复数字状态值为标准字典值
UPDATE cw_project_application 
SET approval_status = CASE 
    WHEN approval_status = '1' THEN 'draft'
    WHEN approval_status = '2' THEN 'submitted'
    WHEN approval_status = '3' THEN 'approving'
    WHEN approval_status = '4' THEN 'approved'
    WHEN approval_status = '5' THEN 'rejected'
    WHEN approval_status = '6' THEN 'returned'
    WHEN approval_status = '7' THEN 'cancelled'
    ELSE approval_status
END
WHERE approval_status IN ('1', '2', '3', '4', '5', '6', '7');

-- 2.3 验证修复结果
SELECT 
    approval_status, 
    COUNT(*) as record_count
FROM cw_project_application 
GROUP BY approval_status
ORDER BY record_count DESC;

-- ==========================================
-- 3. 字典配置检查和修复
-- ==========================================

-- 3.1 确保字典类型存在
INSERT IGNORE INTO sys_dict_type VALUES 
(3000003, '审批状态', 'cw_approval_status', '0', NULL, '财务预算-审批状态', now(), NULL, NULL, '审批状态分类');

-- 3.2 确保字典数据完整
INSERT IGNORE INTO sys_dict_data VALUES 
(3000031, 0, '草稿', 'draft', 'cw_approval_status', '', '', 'N', '0', '0', '', '草稿状态', now(), NULL, NULL, NULL),
(3000032, 1, '已提交', 'submitted', 'cw_approval_status', '', '', 'N', '0', '0', '', '已提交审批', now(), NULL, NULL, NULL),
(3000033, 2, '审批中', 'approving', 'cw_approval_status', '', 'blue', 'N', '0', '0', '', '审批中', now(), NULL, NULL, NULL),
(3000034, 3, '已通过', 'approved', 'cw_approval_status', '', 'green', 'N', '0', '0', '', '审批通过', now(), NULL, NULL, NULL),
(3000035, 4, '已拒绝', 'rejected', 'cw_approval_status', '', 'red', 'N', '0', '0', '', '审批拒绝', now(), NULL, NULL, NULL),
(3000036, 5, '已退回', 'returned', 'cw_approval_status', '', 'orange', 'N', '0', '0', '', '退回修改', now(), NULL, NULL, NULL),
(3000037, 6, '已取消', 'cancelled', 'cw_approval_status', '', 'gray', 'N', '0', '0', '', '取消申报', now(), NULL, NULL, NULL);

-- ==========================================
-- 4. 清理和重置缓存（应用层面）
-- ==========================================

-- 4.1 更新字典缓存标记（如果系统支持）
-- UPDATE sys_config SET config_value = now() WHERE config_key = 'sys.dict.cache.refresh';

-- ==========================================
-- 5. 验证最终结果
-- ==========================================

-- 5.1 验证所有申报记录的状态值都是标准格式
SELECT 
    '修复后状态分布' as check_item,
    approval_status, 
    COUNT(*) as record_count
FROM cw_project_application 
GROUP BY approval_status
ORDER BY record_count DESC;

-- 5.2 验证字典配置完整性
SELECT 
    '字典配置验证' as check_item,
    dict_label,
    dict_value
FROM sys_dict_data 
WHERE dict_type = 'cw_approval_status' 
AND status = '0'
ORDER BY dict_sort;

-- 5.3 检查是否还有问题数据
SELECT 
    '问题数据检查' as check_item,
    COUNT(*) as problem_count
FROM cw_project_application 
WHERE approval_status IN ('1', '2', '3', '4', '5', '6', '7');

-- ==========================================
-- 使用说明
-- ==========================================
/*
执行步骤：

1. 先执行排查SQL（第1部分），查看当前数据状态
   - 如果approval_status列显示的是数字（1,2,7等），说明需要数据迁移
   - 如果已经是英文（draft,submitted等），说明数据正确

2. 如果需要修复，执行第2部分的UPDATE语句
   - 建议先执行备份语句
   - 然后执行状态值迁移

3. 执行第3部分确保字典配置完整

4. 执行第5部分验证修复结果

5. 重启后端服务和清理前端缓存
   - cd backend && mvn spring-boot:run -pl ruoyi-admin
   - cd web && npm run dev

注意事项：
- 执行前请先备份数据库
- 如果有正在进行的审批流程，请谨慎操作
- 修复后需要重启服务以清理缓存
*/