zkxw/docs/sql/migrations/2026-11-12-lab-sample-process-ledger.sql

-- Issue #98：实验室样本处理数据台账（补录表 + 质检管理菜单）
-- MySQL 5.7，可重复执行。

CREATE TABLE IF NOT EXISTS biz_lab_sample_process_supplement (
  id VARCHAR(64) NOT NULL,
  lab_storage_code VARCHAR(32) NOT NULL COMMENT '实验室入库编码（唯一业务键）',
  reception_source VARCHAR(20) NOT NULL DEFAULT 'cell_storage' COMMENT 'cell_storage|research_donation',
  sample_type_name VARCHAR(100) NULL COMMENT '样本类型名称快照',
  payload TEXT NOT NULL COMMENT '补录字段 JSON',
  tenant_no VARCHAR(64) NOT NULL,
  order_num INT NULL,
  remark VARCHAR(512) NULL,
  create_by VARCHAR(64) NULL,
  create_dept_id VARCHAR(64) NOT NULL DEFAULT '',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_by VARCHAR(64) NULL,
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  del_flag SMALLINT NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY ix_tenant_lab_code (tenant_no, lab_storage_code, del_flag),
  KEY ix_tenant_source_type (tenant_no, reception_source, sample_type_name, del_flag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 菜单：挂在「质检管理」下（以样本接收菜单同父节点为准）
INSERT INTO sys_menu (
    id, menu_name, parent_id, menu_type, path, component, perms,
    is_frame, visible, status, tenant_no, order_num, remark,
    create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT
    INSERT(INSERT(MD5(CONCAT('lab-sample-process:', parent.tenant_no, ':', parent.id)),
                  13, 1, '3'), 17, 1, '8'),
    '实验室样本处理数据', parent.parent_id, 'C',
    'lab-sample-process',
    'quality-inspection/lab-sample-process/index',
    'biz:labSampleProcess:list',
    1, '1', '1', parent.tenant_no, 35,
    '质检管理-实验室样本处理数据台账',
    'migration', '', NOW(), 'migration', NOW(), 0
FROM sys_menu AS parent
WHERE parent.del_flag = 0 AND parent.status = '1'
  AND parent.perms = 'biz:sampleReception:list'
  AND parent.parent_id IS NOT NULL
  AND parent.parent_id != ''
  AND NOT EXISTS (
      SELECT 1 FROM sys_menu AS existing
      WHERE existing.tenant_no = parent.tenant_no
        AND existing.perms = 'biz:labSampleProcess:list'
        AND existing.del_flag = 0
  );

-- 补录按钮
INSERT INTO sys_menu (
    id, menu_name, parent_id, menu_type, path, component, perms,
    is_frame, visible, status, tenant_no, order_num, remark,
    create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT
    INSERT(INSERT(MD5(CONCAT('lab-sample-process-sup:', m.tenant_no, ':', m.id)),
                  13, 1, '3'), 17, 1, '8'),
    '补录', m.id, 'F', '', NULL,
    'biz:labSampleProcess:supplement',
    1, '1', '1', m.tenant_no, 10,
    '实验室样本处理数据-补录',
    'migration', '', NOW(), 'migration', NOW(), 0
FROM sys_menu AS m
WHERE m.del_flag = 0 AND m.status = '1'
  AND m.perms = 'biz:labSampleProcess:list'
  AND NOT EXISTS (
      SELECT 1 FROM sys_menu AS existing
      WHERE existing.parent_id = m.id
        AND existing.tenant_no = m.tenant_no
        AND existing.perms = 'biz:labSampleProcess:supplement'
        AND existing.del_flag = 0
  );

-- 导出按钮
INSERT INTO sys_menu (
    id, menu_name, parent_id, menu_type, path, component, perms,
    is_frame, visible, status, tenant_no, order_num, remark,
    create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT
    INSERT(INSERT(MD5(CONCAT('lab-sample-process-exp:', m.tenant_no, ':', m.id)),
                  13, 1, '3'), 17, 1, '8'),
    '导出', m.id, 'F', '', NULL,
    'biz:labSampleProcess:export',
    1, '1', '1', m.tenant_no, 20,
    '实验室样本处理数据-导出',
    'migration', '', NOW(), 'migration', NOW(), 0
FROM sys_menu AS m
WHERE m.del_flag = 0 AND m.status = '1'
  AND m.perms = 'biz:labSampleProcess:list'
  AND NOT EXISTS (
      SELECT 1 FROM sys_menu AS existing
      WHERE existing.parent_id = m.id
        AND existing.tenant_no = m.tenant_no
        AND existing.perms = 'biz:labSampleProcess:export'
        AND existing.del_flag = 0
  );

-- 授权给拥有样本接收列表权限的角色
INSERT INTO sys_role_menu (
    id, role_id, menu_id, tenant_no, order_num, remark,
    create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT
    INSERT(INSERT(MD5(CONCAT('lab-sample-process-rm:', r.tenant_no, ':', r.id, ':', m.id)),
                  13, 1, '3'), 17, 1, '8'),
    r.id,
    m.id,
    r.tenant_no,
    NULL,
    'migration: grant lab sample process from sample reception roles',
    'migration',
    '',
    NOW(),
    'migration',
    NOW(),
    0
FROM sys_role AS r
JOIN sys_role_menu AS src
  ON src.role_id = r.id AND src.tenant_no = r.tenant_no AND src.del_flag = 0
JOIN sys_menu AS srcm
  ON srcm.id = src.menu_id AND srcm.tenant_no = src.tenant_no AND srcm.del_flag = 0
 AND srcm.perms = 'biz:sampleReception:list'
JOIN sys_menu AS m
  ON m.tenant_no = r.tenant_no
 AND m.perms IN (
   'biz:labSampleProcess:list',
   'biz:labSampleProcess:supplement',
   'biz:labSampleProcess:export'
 )
 AND m.del_flag = 0
 AND m.status = '1'
WHERE r.del_flag = 0
  AND NOT EXISTS (
      SELECT 1 FROM sys_role_menu AS existing
      WHERE existing.role_id = r.id
        AND existing.menu_id = m.id
        AND existing.tenant_no = r.tenant_no
        AND existing.del_flag = 0
  );

SELECT tenant_no, parent_id, id, menu_name, path, component, perms
FROM sys_menu
WHERE perms LIKE 'biz:labSampleProcess:%' AND del_flag = 0
ORDER BY tenant_no, order_num, perms;