121 lines
5.4 KiB
SQL
121 lines
5.4 KiB
SQL
-- 实验室样本处理数据：补充表 + 一级模块菜单/按钮权限（不自动授权角色）
|
||
-- MySQL 5.7，可重复执行
|
||
|
||
CREATE TABLE IF NOT EXISTS biz_lab_sample_processing_extra (
|
||
id VARCHAR(64) NOT NULL,
|
||
dataset VARCHAR(16) NOT NULL COMMENT 'hsc|ubmc|msc',
|
||
sample_code VARCHAR(32) NOT NULL COMMENT '样本编码',
|
||
payload_json TEXT 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_lab_sample_extra_tenant_ds_code (tenant_no, dataset, sample_code, del_flag)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 挂到与「质检」同一 Web 虚拟根下（复用 menu:quality 的 parent_id）
|
||
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
|
||
MD5(CONCAT(quality.tenant_no, ':menu:lab-sample-processing')),
|
||
'实验室样本处理数据', quality.parent_id, 'M', '/lab-sample-processing', '/index/index',
|
||
'menu:lab-sample-processing', 1, '1', '1', quality.tenant_no,
|
||
COALESCE(quality.order_num, 50) + 1,
|
||
'20261104 会议：造血/单个核/间充质台账',
|
||
'migration', '', NOW(), 'migration', NOW(), 0
|
||
FROM sys_menu quality
|
||
WHERE quality.perms = 'menu:quality' AND quality.del_flag = 0 AND quality.status = '1'
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM sys_menu e
|
||
WHERE e.tenant_no = quality.tenant_no AND e.perms = 'menu:lab-sample-processing' AND e.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
|
||
MD5(CONCAT(p.tenant_no, ':lab:sample-processing:list')),
|
||
'造血干细胞数据', p.id, 'C', 'lab-sample-processing/hsc',
|
||
'/lab-sample-processing/hsc/index', 'lab:sample-processing:list',
|
||
1, '1', '1', p.tenant_no, 10, '造血干细胞数据列表',
|
||
'migration', '', NOW(), 'migration', NOW(), 0
|
||
FROM sys_menu p
|
||
WHERE p.perms = 'menu:lab-sample-processing' AND p.del_flag = 0 AND p.status = '1'
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM sys_menu e
|
||
WHERE e.tenant_no = p.tenant_no AND e.path = 'lab-sample-processing/hsc' AND e.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
|
||
MD5(CONCAT(p.tenant_no, ':lab:sample-processing:ubmc')),
|
||
'单个核数据', p.id, 'C', 'lab-sample-processing/ubmc',
|
||
'/lab-sample-processing/ubmc/index', 'lab:sample-processing:list',
|
||
1, '1', '1', p.tenant_no, 20, '单个核数据列表',
|
||
'migration', '', NOW(), 'migration', NOW(), 0
|
||
FROM sys_menu p
|
||
WHERE p.perms = 'menu:lab-sample-processing' AND p.del_flag = 0 AND p.status = '1'
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM sys_menu e
|
||
WHERE e.tenant_no = p.tenant_no AND e.path = 'lab-sample-processing/ubmc' AND e.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
|
||
MD5(CONCAT(p.tenant_no, ':lab:sample-processing:msc')),
|
||
'脐带间充质干细胞数据', p.id, 'C', 'lab-sample-processing/msc',
|
||
'/lab-sample-processing/msc/index', 'lab:sample-processing:list',
|
||
1, '1', '1', p.tenant_no, 30, '脐带间充质干细胞数据列表',
|
||
'migration', '', NOW(), 'migration', NOW(), 0
|
||
FROM sys_menu p
|
||
WHERE p.perms = 'menu:lab-sample-processing' AND p.del_flag = 0 AND p.status = '1'
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM sys_menu e
|
||
WHERE e.tenant_no = p.tenant_no AND e.path = 'lab-sample-processing/msc' AND e.del_flag = 0
|
||
);
|
||
|
||
-- 编辑按钮挂在列表菜单下（三页共用同一 list 权限父节点：造血页）
|
||
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
|
||
MD5(CONCAT(p.tenant_no, ':', btn.perms)),
|
||
btn.menu_name, p.id, 'F', '', NULL, btn.perms,
|
||
1, '0', '1', p.tenant_no, btn.order_num, btn.remark,
|
||
'migration', '', NOW(), 'migration', NOW(), 0
|
||
FROM sys_menu p
|
||
JOIN (
|
||
SELECT '市场字段编辑' AS menu_name, 'lab:sample-processing:market:edit' AS perms, 1 AS order_num, '仅可编辑市场部门字段' AS remark
|
||
UNION ALL SELECT '生产字段编辑', 'lab:sample-processing:production:edit', 2, '仅可编辑生产部门字段'
|
||
UNION ALL SELECT '质检字段编辑', 'lab:sample-processing:qc:edit', 3, '仅可编辑质检部门字段'
|
||
UNION ALL SELECT '部门主管', 'lab:sample-processing:manager', 4, '可编辑全部部门字段'
|
||
) btn
|
||
WHERE p.perms = 'lab:sample-processing:list'
|
||
AND p.path = 'lab-sample-processing/hsc'
|
||
AND p.del_flag = 0 AND p.status = '1'
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM sys_menu e
|
||
WHERE e.tenant_no = p.tenant_no AND e.perms = btn.perms AND e.del_flag = 0
|
||
);
|