all-project/script/fix-approval-records.sql

-- 修复审批记录数据脚本
-- 问题：审批人设置错误（使用申报人作为审批人）
-- 解决：删除错误记录，为submitted和approving状态的申报重新创建审批记录

-- ============================================
-- 1. 备份现有错误数据（可选）
-- ============================================
/*
CREATE TABLE cw_approval_record_backup_20250801 AS 
SELECT * FROM cw_approval_record WHERE 1=1;
*/

-- ============================================
-- 2. 删除错误的审批记录
-- ============================================
-- 删除审批人是申报人自己的错误记录
DELETE ar FROM cw_approval_record ar 
INNER JOIN cw_project_application pa ON ar.application_id = pa.id
WHERE ar.approver_id = pa.create_by;

SELECT '删除错误审批记录完成' as status;

-- ============================================
-- 3. 检查需要重新创建审批记录的申报
-- ============================================
SELECT 
    id, 
    application_no, 
    project_name, 
    approval_status,
    department_id,
    '需要重新创建审批记录' as note
FROM cw_project_application 
WHERE approval_status IN ('submitted', 'approving') 
AND deleted = 0
ORDER BY submit_time DESC;

-- ============================================
-- 4. 为教务处申报创建正确的审批记录（jwc001作为部门审批人）
-- ============================================

-- 获取需要处理的申报ID
SET @dept_id = 1938484570826956801; -- 教务处
SET @dept_approver_id = 2101; -- jwc001 (教务处处长江建)
SET @finance_approver_id = 1; -- admin (临时财务审批人)
SET @leader_approver_id = 1;  -- admin (临时领导审批人)

-- 为每个submitted状态的申报创建审批记录
INSERT INTO cw_approval_record (
    id, application_id, application_no, node_code, node_name, node_order,
    approver_id, approver_name, is_delegate, original_approver_id, original_approver_name,
    node_status, approve_action, approve_opinion, start_time, end_time, expect_finish_time,
    is_overtime, config_id, config_snapshot, create_by, create_time, update_by, update_time,
    create_dept, tenant_id
)
SELECT 
    -- 为部门审批记录生成ID
    CONCAT(pa.id, '01') as id,
    pa.id as application_id,
    pa.application_no,
    'dept_approve' as node_code,
    '部门审批' as node_name,
    1 as node_order,
    @dept_approver_id as approver_id,
    '教务处处长江建' as approver_name,
    0 as is_delegate,
    NULL as original_approver_id,
    NULL as original_approver_name,
    'waiting' as node_status,
    NULL as approve_action,
    NULL as approve_opinion,
    NOW() as start_time,
    NULL as end_time,
    NULL as expect_finish_time,
    0 as is_overtime,
    NULL as config_id,
    NULL as config_snapshot,
    pa.create_by as create_by,
    NOW() as create_time,
    pa.create_by as update_by,
    NOW() as update_time,
    pa.create_dept as create_dept,
    '000000' as tenant_id
FROM cw_project_application pa
WHERE pa.department_id = @dept_id 
AND pa.approval_status IN ('submitted', 'approving')
AND pa.deleted = 0;

-- 为每个申报创建财务审批记录（pending状态）
INSERT INTO cw_approval_record (
    id, application_id, application_no, node_code, node_name, node_order,
    approver_id, approver_name, is_delegate, original_approver_id, original_approver_name,
    node_status, approve_action, approve_opinion, start_time, end_time, expect_finish_time,
    is_overtime, config_id, config_snapshot, create_by, create_time, update_by, update_time,
    create_dept, tenant_id
)
SELECT 
    -- 为财务审批记录生成ID
    CONCAT(pa.id, '02') as id,
    pa.id as application_id,
    pa.application_no,
    'finance_approve' as node_code,
    '财务审批' as node_name,
    2 as node_order,
    @finance_approver_id as approver_id,
    '系统管理员' as approver_name,
    0 as is_delegate,
    NULL as original_approver_id,
    NULL as original_approver_name,
    'pending' as node_status,
    NULL as approve_action,
    NULL as approve_opinion,
    NOW() as start_time,
    NULL as end_time,
    NULL as expect_finish_time,
    0 as is_overtime,
    NULL as config_id,
    NULL as config_snapshot,
    pa.create_by as create_by,
    NOW() as create_time,
    pa.create_by as update_by,
    NOW() as update_time,
    pa.create_dept as create_dept,
    '000000' as tenant_id
FROM cw_project_application pa
WHERE pa.department_id = @dept_id 
AND pa.approval_status IN ('submitted', 'approving')
AND pa.deleted = 0;

-- 为每个申报创建领导审批记录（pending状态）
INSERT INTO cw_approval_record (
    id, application_id, application_no, node_code, node_name, node_order,
    approver_id, approver_name, is_delegate, original_approver_id, original_approver_name,
    node_status, approve_action, approve_opinion, start_time, end_time, expect_finish_time,
    is_overtime, config_id, config_snapshot, create_by, create_time, update_by, update_time,
    create_dept, tenant_id
)
SELECT 
    -- 为领导审批记录生成ID
    CONCAT(pa.id, '03') as id,
    pa.id as application_id,
    pa.application_no,
    'leader_approve' as node_code,
    '领导审批' as node_name,
    3 as node_order,
    @leader_approver_id as approver_id,
    '系统管理员' as approver_name,
    0 as is_delegate,
    NULL as original_approver_id,
    NULL as original_approver_name,
    'pending' as node_status,
    NULL as approve_action,
    NULL as approve_opinion,
    NOW() as start_time,
    NULL as end_time,
    NULL as expect_finish_time,
    0 as is_overtime,
    NULL as config_id,
    NULL as config_snapshot,
    pa.create_by as create_by,
    NOW() as create_time,
    pa.create_by as update_by,
    NOW() as update_time,
    pa.create_dept as create_dept,
    '000000' as tenant_id
FROM cw_project_application pa
WHERE pa.department_id = @dept_id 
AND pa.approval_status IN ('submitted', 'approving')
AND pa.deleted = 0;

-- ============================================
-- 5. 验证修复结果
-- ============================================

-- 检查审批记录是否正确创建
SELECT 
    ar.application_id,
    ar.application_no,
    ar.node_code,
    ar.node_name,
    ar.node_status,
    ar.approver_id,
    ar.approver_name,
    pa.approval_status
FROM cw_approval_record ar
INNER JOIN cw_project_application pa ON ar.application_id = pa.id
WHERE pa.department_id = @dept_id 
AND pa.approval_status IN ('submitted', 'approving')
ORDER BY ar.application_id, ar.node_order;

-- 统计修复结果
SELECT 
    '修复完成' as status,
    COUNT(DISTINCT ar.application_id) as fixed_applications,
    COUNT(*) as total_approval_records
FROM cw_approval_record ar
INNER JOIN cw_project_application pa ON ar.application_id = pa.id
WHERE pa.department_id = @dept_id 
AND pa.approval_status IN ('submitted', 'approving');

SELECT '数据修复脚本执行完成' as result;