@@ -0,0 +1,409 @@
-- 【一次性回补】UC 开头实验室入库编码 → 补派「脐带MSC原代培养」5 步生产任务
-- 与种子脚本分开：先执行 2026-11-13-uc-msc-builtin-task-set.sql（或确保模版已存在），再执行本文件。
-- 等价于 backend/scripts/backfill_uc_msc_production_tasks.py --apply 的候选范围。
--
-- 候选条件（须全部满足）：
-- 1) 样本接收 accept_conclusion=js、del_flag=0
-- 2) lab_storage_code 以 UC/uc 开头（非空）
-- 3) 关联入库生产工单 work_order_type=inbound、状态 dpz/jxz、del_flag=0
-- 4) 该工单当前无任何有效生产任务（已有任务如 UC2026010 跳过）
--
-- 动作：
-- - 每工单插入 1 条 biz_production_task（状态 dpz，任务名 {入库编码}样本处理）
-- - 从 UC 内置路线复制 5 个步骤到 biz_production_task_step（及试剂/耗材/产出/采集子表）
-- - 工单状态 dpz → jxz（已是 jxz/ywc 的不改 ywc；本脚本候选不含 ywc）
--
-- 幂等：已有有效任务的工单不会进入候选；重复执行影响行数应为 0。
-- 前置：biz_process_route.route_code='MSC原代-UC-BUILTIN'（或固定 id UCMSCROUTE…）及 5 步须已存在。
-- MySQL 5.7 / utf8mb4。未在生产库执行；执行前请先跑下方「预览」SELECT。
SET NAMES utf8mb4 ;
-- ---------------------------------------------------------------------------
-- 0. 解析 UC 内置工艺路线
-- ---------------------------------------------------------------------------
SET @ uc_route_id : = (
SELECT r . id
FROM biz_process_route r
WHERE r . del_flag = 0
AND (
r . id = ' UCMSCROUTEA5CC11F194118AC54085447A '
OR r . route_code = ' MSC原代-UC-BUILTIN '
OR ( r . route_name = ' 脐带MSC原代培养（内置模版） ' AND r . is_builtin = ' 1 ' )
)
ORDER BY
CASE
WHEN r . id = ' UCMSCROUTEA5CC11F194118AC54085447A ' THEN 0
WHEN r . route_code = ' MSC原代-UC-BUILTIN ' THEN 1
ELSE 2
END
LIMIT 1
) ;
SET @ uc_step_count : = (
SELECT COUNT ( * )
FROM biz_process_route_step s
WHERE s . route_id = @ uc_route_id
AND s . del_flag = 0
) ;
-- 预览：模版是否就绪（应 route 非空且 step_count=5）
SELECT @ uc_route_id AS uc_route_id , @ uc_step_count AS uc_step_count ;
-- 若模版未就绪则主动失败（避免插入无步骤任务）
SET @ uc_ready : = IF ( @ uc_route_id IS NOT NULL AND @ uc_step_count = 5 , 1 , 0 ) ;
-- 下面用 @uc_ready 约束所有写操作；为 0 时 INSERT 选不出行
-- ---------------------------------------------------------------------------
-- 1. 候选工单临时表
-- ---------------------------------------------------------------------------
DROP TEMPORARY TABLE IF EXISTS tmp_uc_wo_backfill ;
CREATE TEMPORARY TABLE tmp_uc_wo_backfill (
seq INT NOT NULL AUTO_INCREMENT ,
work_order_id VARCHAR ( 64 ) NOT NULL ,
reception_id VARCHAR ( 64 ) NOT NULL ,
lab_storage_code VARCHAR ( 32 ) NOT NULL ,
tenant_no VARCHAR ( 64 ) NOT NULL ,
sample_code VARCHAR ( 32 ) NULL ,
task_id VARCHAR ( 64 ) NULL ,
task_no VARCHAR ( 32 ) NULL ,
route_id VARCHAR ( 64 ) NULL ,
PRIMARY KEY ( seq ) ,
UNIQUE KEY uk_wo ( work_order_id )
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci ;
INSERT INTO tmp_uc_wo_backfill (
work_order_id , reception_id , lab_storage_code , tenant_no , sample_code , route_id
)
SELECT
wo . id ,
rec . id ,
TRIM ( rec . lab_storage_code ) ,
wo . tenant_no ,
wo . sample_code ,
@ uc_route_id
FROM biz_production_work_order wo
INNER JOIN biz_sample_reception rec
ON rec . id = wo . reception_id
AND rec . tenant_no = wo . tenant_no
AND rec . del_flag = 0
WHERE @ uc_ready = 1
AND wo . del_flag = 0
AND wo . work_order_type = ' inbound '
AND wo . work_order_status IN ( ' dpz ' , ' jxz ' )
AND rec . accept_conclusion = ' js '
AND rec . lab_storage_code IS NOT NULL
AND TRIM ( rec . lab_storage_code ) < > ' '
AND (
rec . lab_storage_code LIKE ' UC% '
OR rec . lab_storage_code LIKE ' uc% '
)
AND NOT EXISTS (
SELECT 1
FROM biz_production_task t
WHERE t . work_order_id = wo . id
AND t . tenant_no = wo . tenant_no
AND t . del_flag = 0
)
ORDER BY rec . reception_time ASC , wo . create_time ASC ;
-- 预览候选（执行写库前请核对）
SELECT
seq ,
work_order_id ,
lab_storage_code ,
sample_code ,
tenant_no ,
reception_id
FROM tmp_uc_wo_backfill
ORDER BY seq ;
SELECT COUNT ( * ) AS candidate_count FROM tmp_uc_wo_backfill ;
-- 分配 task_id / task_no（
SET @ task_day : = DATE_FORMAT ( NOW ( ) , ' %Y%m%d ' ) ;
SET @ task_no_base : = (
SELECT IFNULL ( MAX ( CAST ( SUBSTRING ( t . task_no , 11 ) AS UNSIGNED ) ) , 0 )
FROM biz_production_task t
WHERE t . task_no LIKE CONCAT ( ' RW ' , @ task_day , ' % ' )
) ;
UPDATE tmp_uc_wo_backfill
SET
task_id = UPPER ( REPLACE ( UUID ( ) , ' - ' , ' ' ) ) ,
task_no = CONCAT ( ' RW ' , @ task_day , LPAD ( @ task_no_base + seq , 4 , ' 0 ' ) ) ;
-- ---------------------------------------------------------------------------
-- 2. 插入生产任务
-- ---------------------------------------------------------------------------
INSERT INTO biz_production_task (
id , task_no , work_order_id , order_source , task_type , task_name , description ,
route_id , task_status , current_assignee_user_id , started_time , completed_time ,
conclusion , remark , attachments , equipment_ids , equipment_name ,
tenant_no , order_num , create_by , create_dept_id , create_time , update_by , update_time , del_flag
)
SELECT
c . task_id ,
c . task_no ,
c . work_order_id ,
' inbound ' ,
' cell_storage ' ,
CONCAT ( c . lab_storage_code , ' 样本处理 ' ) ,
' 脐带MSC原代培养（内置模版） ' ,
c . route_id ,
' dpz ' ,
NULL ,
NULL ,
NULL ,
NULL ,
' 一次性SQL回补：UC入库编码自动派发 ' ,
NULL ,
r . equipment_ids ,
r . equipment_name ,
c . tenant_no ,
0 ,
NULL ,
' ' ,
NOW ( ) ,
NULL ,
NOW ( ) ,
0
FROM tmp_uc_wo_backfill c
INNER JOIN biz_process_route r
ON r . id = c . route_id
AND r . del_flag = 0
WHERE @ uc_ready = 1
AND c . task_id IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM biz_production_task t
WHERE t . work_order_id = c . work_order_id
AND t . tenant_no = c . tenant_no
AND t . del_flag = 0
) ;
-- ---------------------------------------------------------------------------
-- 3. 步骤映射：路线步骤 → 新任务步骤 ID
-- ---------------------------------------------------------------------------
DROP TEMPORARY TABLE IF EXISTS tmp_uc_step_map ;
CREATE TEMPORARY TABLE tmp_uc_step_map (
task_id VARCHAR ( 64 ) NOT NULL ,
work_order_id VARCHAR ( 64 ) NOT NULL ,
tenant_no VARCHAR ( 64 ) NOT NULL ,
route_step_id VARCHAR ( 64 ) NOT NULL ,
task_step_id VARCHAR ( 64 ) NOT NULL ,
step_order INT NOT NULL ,
PRIMARY KEY ( task_id , route_step_id ) ,
KEY ix_route_step ( route_step_id ) ,
KEY ix_task_step ( task_step_id )
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci ;
INSERT INTO tmp_uc_step_map (
task_id , work_order_id , tenant_no , route_step_id , task_step_id , step_order
)
SELECT
c . task_id ,
c . work_order_id ,
c . tenant_no ,
rs . id ,
UPPER ( REPLACE ( UUID ( ) , ' - ' , ' ' ) ) ,
rs . step_order
FROM tmp_uc_wo_backfill c
INNER JOIN biz_process_route_step rs
ON rs . route_id = c . route_id
AND rs . del_flag = 0
WHERE @ uc_ready = 1
AND EXISTS (
SELECT 1 FROM biz_production_task t
WHERE t . id = c . task_id AND t . del_flag = 0
) ;
-- ---------------------------------------------------------------------------
-- 4. 插入任务步骤
-- ---------------------------------------------------------------------------
INSERT INTO biz_production_task_step (
id , task_id , step_order , process_code , category_id , process_name , description ,
equipment_ids , equipment_name , estimated_duration , actual_duration ,
sop_ids , sop_content , step_status , planned_start_time , started_time ,
completed_time , completed_by , tenant_no , order_num , remark ,
create_by , create_dept_id , create_time , update_by , update_time , del_flag
)
SELECT
m . task_step_id ,
m . task_id ,
rs . step_order ,
rs . process_code ,
rs . category_id ,
rs . process_name ,
rs . description ,
rs . equipment_ids ,
rs . equipment_name ,
rs . estimated_duration ,
NULL ,
rs . sop_ids ,
rs . sop_content ,
' dzx ' ,
NULL ,
NULL ,
NULL ,
NULL ,
m . tenant_no ,
IFNULL ( rs . order_num , rs . step_order ) ,
NULL ,
NULL ,
' ' ,
NOW ( ) ,
NULL ,
NOW ( ) ,
0
FROM tmp_uc_step_map m
INNER JOIN biz_process_route_step rs
ON rs . id = m . route_step_id
AND rs . del_flag = 0
WHERE NOT EXISTS (
SELECT 1 FROM biz_production_task_step x
WHERE x . id = m . task_step_id
) ;
-- ---------------------------------------------------------------------------
-- 5. 复制路线步骤子表 → 任务步骤子表
-- ---------------------------------------------------------------------------
INSERT INTO biz_production_task_step_reagent (
id , step_id , reagent_code , reagent_name , spec , standard_usage , unit ,
is_key_material , tenant_no , order_num , remark ,
create_by , create_dept_id , create_time , update_by , update_time , del_flag
)
SELECT
UPPER ( REPLACE ( UUID ( ) , ' - ' , ' ' ) ) ,
m . task_step_id ,
src . reagent_code ,
src . reagent_name ,
src . spec ,
src . standard_usage ,
src . unit ,
src . is_key_material ,
m . tenant_no ,
src . order_num ,
src . remark ,
NULL ,
' ' ,
NOW ( ) ,
NULL ,
NOW ( ) ,
0
FROM tmp_uc_step_map m
INNER JOIN biz_process_route_step_reagent src
ON src . step_id = m . route_step_id
AND src . del_flag = 0 ;
INSERT INTO biz_production_task_step_consumable (
id , step_id , consumable_code , consumable_name , spec_model , standard_usage ,
unit , is_required , remark , tenant_no , order_num ,
create_by , create_dept_id , create_time , update_by , update_time , del_flag
)
SELECT
UPPER ( REPLACE ( UUID ( ) , ' - ' , ' ' ) ) ,
m . task_step_id ,
src . consumable_code ,
src . consumable_name ,
src . spec_model ,
src . standard_usage ,
src . unit ,
src . is_required ,
src . remark ,
m . tenant_no ,
src . order_num ,
NULL ,
' ' ,
NOW ( ) ,
NULL ,
NOW ( ) ,
0
FROM tmp_uc_step_map m
INNER JOIN biz_process_route_step_consumable src
ON src . step_id = m . route_step_id
AND src . del_flag = 0 ;
INSERT INTO biz_production_task_step_output (
id , step_id , output_type_id , output_name , output_barcode ,
tenant_no , order_num , remark ,
create_by , create_dept_id , create_time , update_by , update_time , del_flag
)
SELECT
UPPER ( REPLACE ( UUID ( ) , ' - ' , ' ' ) ) ,
m . task_step_id ,
src . output_type_id ,
src . output_name ,
src . output_barcode ,
m . tenant_no ,
src . order_num ,
src . remark ,
NULL ,
' ' ,
NOW ( ) ,
NULL ,
NOW ( ) ,
0
FROM tmp_uc_step_map m
INNER JOIN biz_process_route_step_output src
ON src . step_id = m . route_step_id
AND src . del_flag = 0 ;
INSERT INTO biz_production_task_step_data_collection (
id , step_id , collection_name , unit , tenant_no , order_num , remark ,
create_by , create_dept_id , create_time , update_by , update_time , del_flag
)
SELECT
UPPER ( REPLACE ( UUID ( ) , ' - ' , ' ' ) ) ,
m . task_step_id ,
src . collection_name ,
src . unit ,
m . tenant_no ,
src . order_num ,
src . remark ,
NULL ,
' ' ,
NOW ( ) ,
NULL ,
NOW ( ) ,
0
FROM tmp_uc_step_map m
INNER JOIN biz_process_route_step_data_collection src
ON src . step_id = m . route_step_id
AND src . del_flag = 0 ;
-- ---------------------------------------------------------------------------
-- 6. 回写工单状态：有任务则 jxz（不改 ywc）
-- ---------------------------------------------------------------------------
UPDATE biz_production_work_order wo
INNER JOIN tmp_uc_wo_backfill c ON c . work_order_id = wo . id
SET
wo . work_order_status = ' jxz ' ,
wo . update_time = NOW ( )
WHERE wo . del_flag = 0
AND wo . work_order_status = ' dpz '
AND EXISTS (
SELECT 1 FROM biz_production_task t
WHERE t . work_order_id = wo . id
AND t . tenant_no = wo . tenant_no
AND t . del_flag = 0
) ;
-- ---------------------------------------------------------------------------
-- 7. 结果核对
-- ---------------------------------------------------------------------------
SELECT
c . lab_storage_code ,
c . work_order_id ,
wo . work_order_no ,
wo . work_order_status ,
t . id AS task_id ,
t . task_no ,
t . task_name ,
( SELECT COUNT ( * ) FROM biz_production_task_step s WHERE s . task_id = t . id AND s . del_flag = 0 ) AS step_count
FROM tmp_uc_wo_backfill c
LEFT JOIN biz_production_work_order wo ON wo . id = c . work_order_id
LEFT JOIN biz_production_task t ON t . id = c . task_id AND t . del_flag = 0
ORDER BY c . seq ;
DROP TEMPORARY TABLE IF EXISTS tmp_uc_step_map ;
DROP TEMPORARY TABLE IF EXISTS tmp_uc_wo_backfill ;
cykj
