47 lines
1.7 KiB
SQL
47 lines
1.7 KiB
SQL
-- 修复脚本：将已经处于pending_receiving_approval状态的一级需求部门任务的第一个部门子任务状态更新为pending_approval
|
||
-- 执行时间：2026-01-22
|
||
-- 说明：修复一级需求部门任务在接单部门审核时子任务状态不正确的问题
|
||
|
||
-- 1. 查找需要修复的任务
|
||
-- 查询条件：approval_status='pending_receiving_approval' AND is_primary_task=1 AND is_pipeline=1
|
||
SELECT
|
||
t.id AS task_id,
|
||
t.task_no,
|
||
t.title,
|
||
COUNT(ti.id) AS pending_subtask_count
|
||
FROM task t
|
||
LEFT JOIN task_item ti ON t.id = ti.task_id AND ti.status = 'pending' AND ti.is_locked = 0
|
||
WHERE t.approval_status = 'pending_receiving_approval'
|
||
AND t.is_primary_task = 1
|
||
AND t.is_pipeline = 1
|
||
AND t.deleted = 0
|
||
GROUP BY t.id, t.task_no, t.title;
|
||
|
||
-- 2. 更新第一个部门的子任务状态为pending_approval
|
||
-- 更新条件：status='pending' AND is_locked=0（这些是第一个部门的子任务）
|
||
UPDATE task_item ti
|
||
INNER JOIN task t ON ti.task_id = t.id
|
||
SET ti.status = 'pending_approval',
|
||
ti.update_time = NOW()
|
||
WHERE t.approval_status = 'pending_receiving_approval'
|
||
AND t.is_primary_task = 1
|
||
AND t.is_pipeline = 1
|
||
AND t.deleted = 0
|
||
AND ti.status = 'pending'
|
||
AND ti.is_locked = 0;
|
||
|
||
-- 3. 验证修复结果
|
||
SELECT
|
||
t.id AS task_id,
|
||
t.task_no,
|
||
t.title,
|
||
COUNT(CASE WHEN ti.status = 'pending_approval' THEN 1 END) AS pending_approval_count,
|
||
COUNT(CASE WHEN ti.status = 'pending' THEN 1 END) AS pending_count
|
||
FROM task t
|
||
LEFT JOIN task_item ti ON t.id = ti.task_id
|
||
WHERE t.approval_status = 'pending_receiving_approval'
|
||
AND t.is_primary_task = 1
|
||
AND t.is_pipeline = 1
|
||
AND t.deleted = 0
|
||
GROUP BY t.id, t.task_no, t.title;
|