ylrz_saas_his_scrm/docs/计费模块菜单SQL-租户账号.sql

-- 计费模块菜单 SQL（租户账号）
-- 说明：
-- 1) 本脚本用于租户账号菜单与权限初始化；
-- 2) 幂等执行，不会重复插入；
-- 3) 默认挂在 path='tenant' 的父菜单下，找不到则挂根节点(0)；
-- 4) 需将 @tenantRoleKey 修改为你系统真实租户角色 key。

SET @now = NOW();
SET @createBy = 'admin';

-- TODO: 改为你系统真实租户角色 key
SET @tenantRoleKey = 'tenant_admin';

-- 找父菜单：优先租户管理目录
SELECT @parentId := menu_id
FROM sys_menu
WHERE path = 'tenant'
ORDER BY menu_id DESC
LIMIT 1;

SET @parentId = IFNULL(@parentId, 0);

-- 1) 租户账号页面菜单（C）
INSERT INTO sys_menu
(menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
SELECT
  '费用明细（我的）', @parentId, 32, 'billingTenant', 'saas/billingTenant/index', 1, 0, 'C', '0', '0', 'fee:billing:tenant:list', '#',
  @createBy, @now, '', NULL, 'SaaS租户查看自己费用明细'
FROM dual
WHERE NOT EXISTS (
  SELECT 1 FROM sys_menu WHERE component = 'saas/billingTenant/index'
);

-- 页面菜单ID
SELECT @tenantPageMenuId := menu_id
FROM sys_menu
WHERE component = 'saas/billingTenant/index'
LIMIT 1;

-- 2) 租户账号查询按钮（F）
INSERT INTO sys_menu
(menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
SELECT
  '租户费用明细查询', @tenantPageMenuId, 1, '#', '', 1, 0, 'F', '0', '0', 'fee:billing:tenant:list', '#',
  @createBy, @now, '', NULL, '租户费用明细查询权限'
FROM dual
WHERE @tenantPageMenuId IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM sys_menu
    WHERE parent_id = @tenantPageMenuId
      AND perms = 'fee:billing:tenant:list'
      AND menu_type = 'F'
  );

-- 3) 绑定租户角色
SELECT @tenantRoleId := role_id
FROM sys_role
WHERE role_key = @tenantRoleKey
LIMIT 1;

INSERT INTO sys_role_menu (role_id, menu_id)
SELECT @tenantRoleId, m.menu_id
FROM sys_menu m
WHERE @tenantRoleId IS NOT NULL
  AND m.menu_id = @tenantPageMenuId
  AND NOT EXISTS (
    SELECT 1 FROM sys_role_menu rm WHERE rm.role_id = @tenantRoleId AND rm.menu_id = m.menu_id
  );