zkxw/docs/sql/2026-09-01-production-task-set-builtin-templates.sql

-- 生产任务组合模版：按 4 条内置工艺路线创建内置模版
-- 前置：
--   1) 2026-09-01-process-route-builtin-templates.sql（内置路线已存在）
--   2) 2026-09-01-production-task-set-add-is-builtin.sql
-- 幂等：同租户同集合名称已存在且未删除则跳过
-- MySQL 5.7+
--
-- 映射（集合名 = 内置路线名）：
--   CC样本处理-BUILTIN  → 造血干样本处理（内置模版）
--   MSC培养-BUILTIN     → 干细胞培养（内置模版）
--   UBMC冻存-BUILTIN    → UBMC冻存（内置模版）
--   PBMC冻存-BUILTIN    → PBMC冻存（内置模版）
-- 条目任务名统一：{lab_storage_code}样本处理

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_seed_production_task_set_builtin$$
CREATE PROCEDURE sp_seed_production_task_set_builtin(
  IN p_route_code VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  IN p_set_name VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
)
BEGIN
  DECLARE v_done INT DEFAULT 0;
  DECLARE v_tenant VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
  DECLARE v_route_id VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  DECLARE v_route_name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  DECLARE v_set_id VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

  DECLARE cur CURSOR FOR
    SELECT
      r.tenant_no,
      r.id,
      IFNULL(r.route_name, '')
    FROM biz_process_route r
    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_route_code
        AND del_flag = 0
      GROUP BY tenant_no
    ) p ON p.pick_id = r.id
    WHERE NOT EXISTS (
      SELECT 1
      FROM biz_production_task_set s
      WHERE s.tenant_no = CONVERT(r.tenant_no USING utf8mb4) COLLATE utf8mb4_general_ci
        AND s.set_name = p_set_name
        AND s.del_flag = 0
    );

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

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

    -- MD5+源键，避免 UUID() 同微秒撞主键
    SET v_set_id = UPPER(MD5(CONCAT(UUID(), v_route_id, RAND(), CONNECTION_ID())));

    INSERT INTO biz_production_task_set (
      id, set_name, status, tenant_no, order_num, remark, is_builtin,
      create_by, create_dept_id, create_time, update_by, update_time, del_flag
    ) VALUES (
      v_set_id,
      p_set_name,
      '1',
      CONVERT(v_tenant USING utf8mb4) COLLATE utf8mb4_general_ci,
      0,
      '内置模版',
      '1',
      NULL,
      '',
      NOW(),
      NULL,
      NOW(),
      0
    );

    INSERT INTO biz_production_task_set_item (
      id, set_id, task_name, route_id, route_name, description, remark,
      tenant_no, order_num, create_by, create_dept_id, create_time, update_by, update_time, del_flag
    ) VALUES (
      UPPER(MD5(CONCAT(UUID(), v_set_id, RAND(), CONNECTION_ID()))),
      v_set_id,
      '{lab_storage_code}样本处理',
      v_route_id,
      v_route_name,
      NULL,
      NULL,
      CONVERT(v_tenant USING utf8mb4) COLLATE utf8mb4_general_ci,
      1,
      NULL,
      '',
      NOW(),
      NULL,
      NOW(),
      0
    );
  END LOOP;
  CLOSE cur;
END$$

DELIMITER ;

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

DROP PROCEDURE IF EXISTS sp_seed_production_task_set_builtin;

-- 校验（可选）：
-- SELECT set_name, status, is_builtin, remark, tenant_no
-- FROM biz_production_task_set
-- WHERE is_builtin = '1' AND del_flag = 0;
-- SELECT s.set_name, i.task_name, i.route_name
-- FROM biz_production_task_set s
-- JOIN biz_production_task_set_item i ON i.set_id = s.id AND i.del_flag = 0
-- WHERE s.is_builtin = '1' AND s.del_flag = 0;