shanxia2.0/docs/sql/merge_work_order_tables.sql

-- ============================================================
-- 工单表合并迁移：biz_temp_* → 主表 + work_order_type
-- Date: 2026-05-24
-- 执行顺序：先本脚本，再 work_order_unification_phase1.sql
-- 适用：仍保留 biz_temp_qc_work_order / biz_temp_production_work_order 的旧库
-- ============================================================

-- 1. biz_qc_work_order: 加 work_order_type（若已存在则跳过，需 MySQL 8+ 或手动检查）
-- ALTER TABLE biz_qc_work_order
--   ADD COLUMN work_order_type VARCHAR(20) NOT NULL DEFAULT 'inbound'
--   COMMENT '工单类型: inbound=入库, temporary=临时' AFTER work_order_no;

-- 2. reception 关联字段改为可空（临时工单不需要）
-- ALTER TABLE biz_qc_work_order
--   MODIFY COLUMN reception_id VARCHAR(64) NULL,
--   MODIFY COLUMN protocol_sample_id VARCHAR(64) NULL,
--   MODIFY COLUMN collection_protocol_id VARCHAR(64) NULL,
--   MODIFY COLUMN reception_source VARCHAR(20) NULL DEFAULT 'cell_storage';

-- 3. 迁移临时质检工单（按 id 去重，避免重复执行）
INSERT INTO biz_qc_work_order (
  id, work_order_no, work_order_type, work_order_status,
  has_lab, has_third_party, third_party_fee_total,
  configured_time, configured_by,
  tenant_no, order_num, remark,
  create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT
  t.id, t.work_order_no, 'temporary', t.work_order_status,
  t.has_lab, t.has_third_party, t.third_party_fee_total,
  t.configured_time, t.configured_by,
  t.tenant_no, t.order_num, t.remark,
  t.create_by, t.create_dept_id, t.create_time, t.update_by, t.update_time, t.del_flag
FROM biz_temp_qc_work_order t
WHERE NOT EXISTS (SELECT 1 FROM biz_qc_work_order m WHERE m.id = t.id);

-- 4. biz_production_work_order: 加 work_order_type / configured_time / configured_by（若未加请手动 ALTER）
-- ALTER TABLE biz_production_work_order ADD COLUMN work_order_type ...
-- ALTER TABLE biz_production_work_order ADD COLUMN configured_time DATETIME NULL ...
-- ALTER TABLE biz_production_work_order ADD COLUMN configured_by VARCHAR(64) NULL ...

-- 5. 迁移临时生产工单
INSERT INTO biz_production_work_order (
  id, work_order_no, work_order_type, work_order_status,
  configured_time, configured_by,
  tenant_no, order_num, remark,
  create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT
  t.id, t.work_order_no, 'temporary', t.work_order_status,
  t.configured_time, t.configured_by,
  t.tenant_no, t.order_num, t.remark,
  t.create_by, t.create_dept_id, t.create_time, t.update_by, t.update_time, t.del_flag
FROM biz_temp_production_work_order t
WHERE NOT EXISTS (SELECT 1 FROM biz_production_work_order m WHERE m.id = t.id);

-- 6. 删除临时表（确认迁移成功后执行）
DROP TABLE IF EXISTS biz_temp_qc_work_order;
DROP TABLE IF EXISTS biz_temp_production_work_order;