zkxw/docs/sql/2026-09-01-process-route-builtin-templates.sql

-- 工艺路线：复制 4 条内置模版（深拷贝步骤及子表）
-- 前置：已执行 2026-09-01-process-route-add-is-builtin.sql
-- 幂等：同租户下目标 route_code 已存在且未删除则跳过
-- MySQL 5.7+
--
-- 映射：
--   造血干样本处理 / CC样本处理     → 造血干样本处理（内置模版） / CC样本处理-BUILTIN
--   干细胞培养 / MSC培养           → 干细胞培养（内置模版） / MSC培养-BUILTIN
--   UBMC冻存 / UBMC冻存            → UBMC冻存（内置模版） / UBMC冻存-BUILTIN
--   PBMC冻存 / PBMC冻存            → PBMC冻存（内置模版） / PBMC冻存-BUILTIN
--
-- ID 生成说明：
--   MySQL UUID() 在同一微秒批量 INSERT…SELECT 时可能撞主键。
--   新 id = UPPER(MD5(CONCAT(UUID(), 源行主键, RAND(), CONNECTION_ID())))，保证逐行唯一。
--
-- ---------------------------------------------------------------------------
-- 若上次执行报 Duplicate entry … PRIMARY，先做半截数据清理再重跑本脚本：
--
-- -- 1) 查看已写入的 BUILTIN
-- SELECT id, route_code, route_name, is_builtin, tenant_no, create_time
-- FROM biz_process_route
-- WHERE route_code LIKE '%-BUILTIN' AND del_flag = 0;
--
-- -- 2) 软删半截内置路线及其步骤/子表（仅清理本次模版种子，不动源路线）
-- UPDATE biz_process_route_step_data_collection d
--   INNER JOIN biz_process_route_step s ON s.id = d.step_id
--   INNER JOIN biz_process_route r ON r.id = s.route_id
-- SET d.del_flag = 1, d.update_time = NOW()
-- WHERE r.route_code LIKE '%-BUILTIN' AND r.del_flag = 0 AND d.del_flag = 0;
--
-- UPDATE biz_process_route_step_output o
--   INNER JOIN biz_process_route_step s ON s.id = o.step_id
--   INNER JOIN biz_process_route r ON r.id = s.route_id
-- SET o.del_flag = 1, o.update_time = NOW()
-- WHERE r.route_code LIKE '%-BUILTIN' AND r.del_flag = 0 AND o.del_flag = 0;
--
-- UPDATE biz_process_route_step_consumable c
--   INNER JOIN biz_process_route_step s ON s.id = c.step_id
--   INNER JOIN biz_process_route r ON r.id = s.route_id
-- SET c.del_flag = 1, c.update_time = NOW()
-- WHERE r.route_code LIKE '%-BUILTIN' AND r.del_flag = 0 AND c.del_flag = 0;
--
-- UPDATE biz_process_route_step_reagent g
--   INNER JOIN biz_process_route_step s ON s.id = g.step_id
--   INNER JOIN biz_process_route r ON r.id = s.route_id
-- SET g.del_flag = 1, g.update_time = NOW()
-- WHERE r.route_code LIKE '%-BUILTIN' AND r.del_flag = 0 AND g.del_flag = 0;
--
-- UPDATE biz_process_route_step s
--   INNER JOIN biz_process_route r ON r.id = s.route_id
-- SET s.del_flag = 1, s.update_time = NOW()
-- WHERE r.route_code LIKE '%-BUILTIN' AND r.del_flag = 0 AND s.del_flag = 0;
--
-- UPDATE biz_process_route
-- SET del_flag = 1, update_time = NOW()
-- WHERE route_code LIKE '%-BUILTIN' AND del_flag = 0;
-- ---------------------------------------------------------------------------

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_copy_process_route_builtin$$
CREATE PROCEDURE sp_copy_process_route_builtin(
  IN p_src_code VARCHAR(64),
  IN p_dst_code VARCHAR(64),
  IN p_dst_name VARCHAR(100)
)
BEGIN
  DECLARE v_done INT DEFAULT 0;
  DECLARE v_src_id VARCHAR(64);
  DECLARE v_tenant VARCHAR(64);
  DECLARE v_is_current CHAR(1);
  DECLARE v_new_route_id VARCHAR(64);

  DECLARE cur CURSOR FOR
    SELECT x.id, x.tenant_no, IFNULL(x.is_current, '0')
    FROM biz_process_route x
    INNER JOIN (
      SELECT
        tenant_no,
        SUBSTRING_INDEX(
          GROUP_CONCAT(id ORDER BY (IFNULL(is_current, '0') = '1') DESC, update_time DESC SEPARATOR ','),
          ',',
          1
        ) AS pick_id
      FROM biz_process_route
      WHERE route_code = p_src_code
        AND del_flag = 0
      GROUP BY tenant_no
    ) p ON p.pick_id = x.id
    WHERE NOT EXISTS (
      SELECT 1
      FROM biz_process_route t
      WHERE t.tenant_no = x.tenant_no
        AND t.route_code = p_dst_code
        AND t.del_flag = 0
    );

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_src_id, v_tenant, v_is_current;
    IF v_done = 1 THEN
      LEAVE read_loop;
    END IF;

    START TRANSACTION;

    SET v_new_route_id = UPPER(MD5(CONCAT(UUID(), v_src_id, RAND(), CONNECTION_ID())));

    INSERT INTO biz_process_route (
      id, route_code, route_name, product_type_id, version, is_current,
      equipment_ids, equipment_name, tenant_no, order_num, remark, is_builtin,
      create_by, create_dept_id, create_time, update_by, update_time, del_flag
    )
    SELECT
      v_new_route_id,
      p_dst_code,
      p_dst_name,
      product_type_id,
      version,
      v_is_current,
      equipment_ids,
      equipment_name,
      tenant_no,
      order_num,
      '内置模版',
      '1',
      create_by,
      IFNULL(create_dept_id, ''),
      NOW(),
      update_by,
      NOW(),
      0
    FROM biz_process_route
    WHERE id = v_src_id;

    DROP TEMPORARY TABLE IF EXISTS tmp_step_id_map;
    CREATE TEMPORARY TABLE tmp_step_id_map (
      old_id VARCHAR(64) NOT NULL PRIMARY KEY,
      new_id VARCHAR(64) NOT NULL
    );

    INSERT INTO tmp_step_id_map (old_id, new_id)
    SELECT
      id,
      UPPER(MD5(CONCAT(UUID(), id, RAND(), CONNECTION_ID())))
    FROM biz_process_route_step
    WHERE route_id = v_src_id
      AND del_flag = 0;

    INSERT INTO biz_process_route_step (
      id, route_id, process_id, step_order, process_code, category_id, process_name,
      description, equipment_ids, equipment_name, estimated_duration, sop_ids, sop_content,
      tenant_no, order_num, remark, create_by, create_dept_id, create_time, update_by, update_time, del_flag
    )
    SELECT
      m.new_id,
      v_new_route_id,
      s.process_id,
      s.step_order,
      s.process_code,
      s.category_id,
      s.process_name,
      s.description,
      s.equipment_ids,
      s.equipment_name,
      s.estimated_duration,
      s.sop_ids,
      s.sop_content,
      s.tenant_no,
      s.order_num,
      s.remark,
      s.create_by,
      IFNULL(s.create_dept_id, ''),
      NOW(),
      s.update_by,
      NOW(),
      0
    FROM biz_process_route_step s
    INNER JOIN tmp_step_id_map m ON m.old_id = s.id;

    INSERT INTO biz_process_route_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(MD5(CONCAT(UUID(), r.id, RAND(), CONNECTION_ID()))),
      m.new_id,
      r.reagent_code,
      r.reagent_name,
      r.spec,
      r.standard_usage,
      r.unit,
      r.is_key_material,
      r.tenant_no,
      r.order_num,
      r.remark,
      r.create_by,
      IFNULL(r.create_dept_id, ''),
      NOW(),
      r.update_by,
      NOW(),
      0
    FROM biz_process_route_step_reagent r
    INNER JOIN tmp_step_id_map m ON m.old_id = r.step_id
    WHERE r.del_flag = 0;

    INSERT INTO biz_process_route_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(MD5(CONCAT(UUID(), c.id, RAND(), CONNECTION_ID()))),
      m.new_id,
      c.consumable_code,
      c.consumable_name,
      c.spec_model,
      c.standard_usage,
      c.unit,
      c.is_required,
      c.remark,
      c.tenant_no,
      c.order_num,
      c.create_by,
      IFNULL(c.create_dept_id, ''),
      NOW(),
      c.update_by,
      NOW(),
      0
    FROM biz_process_route_step_consumable c
    INNER JOIN tmp_step_id_map m ON m.old_id = c.step_id
    WHERE c.del_flag = 0;

    INSERT INTO biz_process_route_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(MD5(CONCAT(UUID(), o.id, RAND(), CONNECTION_ID()))),
      m.new_id,
      o.output_type_id,
      o.output_name,
      o.output_barcode,
      o.tenant_no,
      o.order_num,
      o.remark,
      o.create_by,
      IFNULL(o.create_dept_id, ''),
      NOW(),
      o.update_by,
      NOW(),
      0
    FROM biz_process_route_step_output o
    INNER JOIN tmp_step_id_map m ON m.old_id = o.step_id
    WHERE o.del_flag = 0;

    INSERT INTO biz_process_route_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(MD5(CONCAT(UUID(), d.id, RAND(), CONNECTION_ID()))),
      m.new_id,
      d.collection_name,
      d.unit,
      d.tenant_no,
      d.order_num,
      d.remark,
      d.create_by,
      IFNULL(d.create_dept_id, ''),
      NOW(),
      d.update_by,
      NOW(),
      0
    FROM biz_process_route_step_data_collection d
    INNER JOIN tmp_step_id_map m ON m.old_id = d.step_id
    WHERE d.del_flag = 0;

    DROP TEMPORARY TABLE IF EXISTS tmp_step_id_map;

    COMMIT;
  END LOOP;
  CLOSE cur;
END$$

DELIMITER ;

CALL sp_copy_process_route_builtin('CC样本处理', 'CC样本处理-BUILTIN', '造血干样本处理（内置模版）');
CALL sp_copy_process_route_builtin('MSC培养', 'MSC培养-BUILTIN', '干细胞培养（内置模版）');
CALL sp_copy_process_route_builtin('UBMC冻存', 'UBMC冻存-BUILTIN', 'UBMC冻存（内置模版）');
CALL sp_copy_process_route_builtin('PBMC冻存', 'PBMC冻存-BUILTIN', 'PBMC冻存（内置模版）');

DROP PROCEDURE IF EXISTS sp_copy_process_route_builtin;

-- 校验（可选）：
-- SELECT route_code, route_name, is_builtin, remark, is_current, tenant_no
-- FROM biz_process_route
-- WHERE route_code LIKE '%-BUILTIN' AND del_flag = 0;