78 lines
3.4 KiB
SQL
78 lines
3.4 KiB
SQL
-- 小程序运输管理 → 危化品证上传（独立 H5）。MySQL 5.7。
|
|
-- 2026-10-17 已只读确认测试库：1(移动端) → MINI_PL_ROOT(运输管理)。
|
|
-- 执行前备份 sys_menu / sys_role_menu，并确认目标库及下面的父级查询返回一行。
|
|
-- 不新建/更改父级，不改变 Web 菜单和业务表，不额外授予业务写权限。
|
|
SET NAMES utf8mb4;
|
|
START TRANSACTION;
|
|
|
|
SELECT p.id, p.menu_name, p.parent_id, p.tenant_no
|
|
FROM sys_menu p
|
|
JOIN sys_menu root ON root.id = p.parent_id
|
|
WHERE p.id = 'MINI_PL_ROOT' AND p.parent_id = '1'
|
|
AND p.path = '/mini/pl' AND p.tenant_no = 'shanxia2.0' AND p.del_flag = 0
|
|
AND root.path = '/mini' AND root.parent_id = '0'
|
|
AND root.tenant_no = p.tenant_no AND root.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
|
|
'329823eb-e3af-49b2-b656-08f2c2a713f3', '危化品证上传', p.id, 'C', 'mp-webview', NULL,
|
|
'mini:pl:plan-cert:list', 1, '1', '1', p.tenant_no, 5,
|
|
'小程序 WebView 危化品证上传', 'system', '', NOW(), NULL, NOW(), 0
|
|
FROM sys_menu p
|
|
JOIN sys_menu root ON root.id = p.parent_id
|
|
WHERE p.id = 'MINI_PL_ROOT' AND p.parent_id = '1'
|
|
AND p.path = '/mini/pl' AND p.tenant_no = 'shanxia2.0' AND p.del_flag = 0
|
|
AND root.path = '/mini' AND root.parent_id = '0'
|
|
AND root.tenant_no = p.tenant_no AND root.del_flag = 0
|
|
ON DUPLICATE KEY UPDATE visible = '1', status = '1', del_flag = 0, update_time = NOW();
|
|
|
|
-- 随已有 Web 列表权限角色补齐移动根、运输目录、叶子；不依赖历史 Web 菜单 ID。
|
|
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
|
|
MD5(CONCAT(r.id, '|', target.menu_id, '|mini-pl-plan-cert-webview|v1')),
|
|
r.id, target.menu_id, r.tenant_no, 0, '小程序危化品证上传：随 Web 列表权限补绑',
|
|
'system', '', NOW(), NULL, NOW(), 0
|
|
FROM sys_role r
|
|
CROSS JOIN (
|
|
SELECT '1' AS menu_id UNION ALL SELECT 'MINI_PL_ROOT'
|
|
UNION ALL SELECT '329823eb-e3af-49b2-b656-08f2c2a713f3'
|
|
) target
|
|
WHERE r.del_flag = 0 AND r.tenant_no = 'shanxia2.0'
|
|
AND EXISTS (
|
|
SELECT 1 FROM sys_menu leaf
|
|
JOIN sys_menu p ON p.id = leaf.parent_id
|
|
JOIN sys_menu root ON root.id = p.parent_id
|
|
WHERE leaf.id = '329823eb-e3af-49b2-b656-08f2c2a713f3'
|
|
AND leaf.perms = 'mini:pl:plan-cert:list' AND leaf.del_flag = 0
|
|
AND leaf.tenant_no = r.tenant_no
|
|
AND p.id = 'MINI_PL_ROOT' AND p.parent_id = '1' AND p.path = '/mini/pl'
|
|
AND p.tenant_no = r.tenant_no AND p.del_flag = 0
|
|
AND root.path = '/mini' AND root.parent_id = '0'
|
|
AND root.tenant_no = r.tenant_no AND root.del_flag = 0
|
|
)
|
|
AND EXISTS (
|
|
SELECT 1 FROM sys_role_menu web
|
|
JOIN sys_menu m ON m.id = web.menu_id AND m.del_flag = 0
|
|
WHERE web.role_id = r.id AND web.del_flag = 0
|
|
AND web.tenant_no = r.tenant_no AND m.tenant_no = r.tenant_no
|
|
AND m.perms = 'pl:plan-cert:list'
|
|
)
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM sys_role_menu existing
|
|
WHERE existing.role_id = r.id AND existing.menu_id = target.menu_id
|
|
AND existing.tenant_no = r.tenant_no AND existing.del_flag = 0
|
|
)
|
|
ON DUPLICATE KEY UPDATE del_flag = 0, update_time = NOW();
|
|
|
|
COMMIT;
|
|
|
|
SELECT id, menu_name, parent_id, perms, visible, status, del_flag
|
|
FROM sys_menu WHERE id = '329823eb-e3af-49b2-b656-08f2c2a713f3';
|