| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111 |
- -- 入库放行审核:给指定角色授权(sys_role_menu)
- -- MySQL 5.7;可重复执行
- -- 研发主管 → 生产侧入库放行审核
- -- 质量主管 → 质检侧入库放行审核
- -- 祖先菜单(生产/生产管理、质检/质检管理)若已有则不重复插入
- SET @tenant_no = CONVERT('zkxw' USING utf8mb4) COLLATE utf8mb4_unicode_ci;
- -- 研发主管
- SET @role_rd = CONVERT('2668804724054AB6A45DF35D2108138B' USING utf8mb4) COLLATE utf8mb4_unicode_ci;
- -- 质量主管
- SET @role_qa = CONVERT('35DD110367764827ACCCFEEBA1E21699' USING utf8mb4) COLLATE utf8mb4_unicode_ci;
- -- 生产入库放行
- SET @menu_prod = CONVERT('95500000000000000000000000000001' USING utf8mb4) COLLATE utf8mb4_unicode_ci;
- -- 质检入库放行
- SET @menu_qc = CONVERT('70000000000000000000000000000014' USING utf8mb4) COLLATE utf8mb4_unicode_ci;
- SET @top_prod = CONVERT('TOP00000000000000000000000000002' USING utf8mb4) COLLATE utf8mb4_unicode_ci;
- SET @prm_id = CONVERT('PRM00000000000000000000000000001' USING utf8mb4) COLLATE utf8mb4_unicode_ci;
- SET @top_qc = CONVERT('TOP00000000000000000000000000003' USING utf8mb4) COLLATE utf8mb4_unicode_ci;
- SET @qcm_id = CONVERT('QCM00000000000000000000000000001' USING utf8mb4) COLLATE utf8mb4_unicode_ci;
- SET @web_root = CONVERT('2' USING utf8mb4) COLLATE utf8mb4_unicode_ci;
- -- 软删恢复
- UPDATE sys_role_menu
- SET del_flag = 0, update_time = NOW(), remark = 'grant storage release dual-gate'
- WHERE tenant_no COLLATE utf8mb4_unicode_ci = @tenant_no
- AND del_flag = 1
- AND (
- (role_id COLLATE utf8mb4_unicode_ci = @role_rd AND menu_id COLLATE utf8mb4_unicode_ci IN (@web_root, @top_prod, @prm_id, @menu_prod))
- OR
- (role_id COLLATE utf8mb4_unicode_ci = @role_qa AND menu_id COLLATE utf8mb4_unicode_ci IN (@web_root, @top_qc, @qcm_id, @menu_qc))
- );
- -- 研发主管:Web 根 + 生产 + 生产管理 + 生产入库放行
- 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
- UPPER(MD5(CONCAT(x.role_id, '|', x.menu_id))),
- x.role_id,
- x.menu_id,
- @tenant_no,
- 0,
- 'grant storage release dual-gate',
- 'admin',
- '',
- NOW(),
- NULL,
- NOW(),
- 0
- FROM (
- SELECT @role_rd AS role_id, @web_root AS menu_id
- UNION ALL SELECT @role_rd, @top_prod
- UNION ALL SELECT @role_rd, @prm_id
- UNION ALL SELECT @role_rd, @menu_prod
- ) x
- WHERE EXISTS (
- SELECT 1 FROM sys_role r
- WHERE r.id COLLATE utf8mb4_unicode_ci = x.role_id AND r.del_flag = 0
- )
- AND EXISTS (
- SELECT 1 FROM sys_menu m
- WHERE m.id COLLATE utf8mb4_unicode_ci = x.menu_id AND m.del_flag = 0
- )
- AND NOT EXISTS (
- SELECT 1 FROM sys_role_menu rm
- WHERE rm.role_id COLLATE utf8mb4_unicode_ci = x.role_id
- AND rm.menu_id COLLATE utf8mb4_unicode_ci = x.menu_id
- AND rm.tenant_no COLLATE utf8mb4_unicode_ci = @tenant_no
- AND rm.del_flag = 0
- );
- -- 质量主管:Web 根 + 质检 + 质检管理 + 质检入库放行
- 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
- UPPER(MD5(CONCAT(x.role_id, '|', x.menu_id))),
- x.role_id,
- x.menu_id,
- @tenant_no,
- 0,
- 'grant storage release dual-gate',
- 'admin',
- '',
- NOW(),
- NULL,
- NOW(),
- 0
- FROM (
- SELECT @role_qa AS role_id, @web_root AS menu_id
- UNION ALL SELECT @role_qa, @top_qc
- UNION ALL SELECT @role_qa, @qcm_id
- UNION ALL SELECT @role_qa, @menu_qc
- ) x
- WHERE EXISTS (
- SELECT 1 FROM sys_role r
- WHERE r.id COLLATE utf8mb4_unicode_ci = x.role_id AND r.del_flag = 0
- )
- AND EXISTS (
- SELECT 1 FROM sys_menu m
- WHERE m.id COLLATE utf8mb4_unicode_ci = x.menu_id AND m.del_flag = 0
- )
- AND NOT EXISTS (
- SELECT 1 FROM sys_role_menu rm
- WHERE rm.role_id COLLATE utf8mb4_unicode_ci = x.role_id
- AND rm.menu_id COLLATE utf8mb4_unicode_ci = x.menu_id
- AND rm.tenant_no COLLATE utf8mb4_unicode_ci = @tenant_no
- AND rm.del_flag = 0
- );
|