shanxia2.0/docs/sql/migrations/2026-10-17-mini-pl-plan-cert-webview-menus.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';