205 lines
7.9 KiB
Transact-SQL
205 lines
7.9 KiB
Transact-SQL
-- =============================================
|
||
-- 系统配置模块菜单初始化脚本
|
||
-- 用于在后台管理系统中添加系统配置相关菜单
|
||
-- =============================================
|
||
|
||
-- 注意：执行此脚本前请确保：
|
||
-- 1. 已存在超级管理员角色 (Code = 'super_admin')
|
||
-- 2. 数据库中已有基础菜单数据
|
||
|
||
-- 声明变量
|
||
DECLARE @ConfigMenuId INT;
|
||
DECLARE @SuperAdminRoleId INT;
|
||
|
||
-- 获取超级管理员角色ID
|
||
SELECT @SuperAdminRoleId = Id FROM Roles WHERE Code = 'super_admin';
|
||
|
||
-- =============================================
|
||
-- 1. 创建系统配置目录（顶级菜单）
|
||
-- =============================================
|
||
IF NOT EXISTS (SELECT 1 FROM Menus WHERE Path = '/business/config')
|
||
BEGIN
|
||
INSERT INTO Menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
|
||
VALUES (0, N'系统配置', '/business/config', 'Layout', 'Setting', 1, NULL, 60, 1, 0, 0, GETDATE());
|
||
|
||
SET @ConfigMenuId = SCOPE_IDENTITY();
|
||
PRINT N'创建系统配置目录，ID: ' + CAST(@ConfigMenuId AS VARCHAR);
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
SELECT @ConfigMenuId = Id FROM Menus WHERE Path = '/business/config';
|
||
-- 确保是顶级菜单
|
||
UPDATE Menus SET ParentId = 0, Component = 'Layout' WHERE Id = @ConfigMenuId AND ParentId <> 0;
|
||
PRINT N'系统配置目录已存在，ID: ' + CAST(@ConfigMenuId AS VARCHAR);
|
||
END
|
||
|
||
-- =============================================
|
||
-- 2. 创建系统配置子菜单
|
||
-- =============================================
|
||
|
||
-- 2.1 基础设置
|
||
IF NOT EXISTS (SELECT 1 FROM Menus WHERE Path = '/business/config/base')
|
||
BEGIN
|
||
INSERT INTO Menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
|
||
VALUES (@ConfigMenuId, N'基础设置', '/business/config/base', 'business/config/base', 'HomeFilled', 2, 'config:view', 1, 1, 0, 1, GETDATE());
|
||
PRINT N'创建基础设置菜单';
|
||
END
|
||
|
||
-- 2.2 微信支付配置
|
||
IF NOT EXISTS (SELECT 1 FROM Menus WHERE Path = '/business/config/weixinpay')
|
||
BEGIN
|
||
INSERT INTO Menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
|
||
VALUES (@ConfigMenuId, N'微信支付配置', '/business/config/weixinpay', 'business/config/weixinpay', 'ChatDotRound', 2, 'config:view', 2, 1, 0, 1, GETDATE());
|
||
PRINT N'创建微信支付配置菜单';
|
||
END
|
||
|
||
-- 2.3 支付宝配置
|
||
IF NOT EXISTS (SELECT 1 FROM Menus WHERE Path = '/business/config/alipay')
|
||
BEGIN
|
||
INSERT INTO Menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
|
||
VALUES (@ConfigMenuId, N'支付宝配置', '/business/config/alipay', 'business/config/alipay', 'Wallet', 2, 'config:view', 3, 1, 0, 1, GETDATE());
|
||
PRINT N'创建支付宝配置菜单';
|
||
END
|
||
|
||
-- 2.4 小程序配置
|
||
IF NOT EXISTS (SELECT 1 FROM Menus WHERE Path = '/business/config/miniprogram')
|
||
BEGIN
|
||
INSERT INTO Menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
|
||
VALUES (@ConfigMenuId, N'小程序配置', '/business/config/miniprogram', 'business/config/miniprogram', 'Cellphone', 2, 'config:view', 4, 1, 0, 1, GETDATE());
|
||
PRINT N'创建小程序配置菜单';
|
||
END
|
||
|
||
-- 2.5 H5配置
|
||
IF NOT EXISTS (SELECT 1 FROM Menus WHERE Path = '/business/config/h5')
|
||
BEGIN
|
||
INSERT INTO Menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
|
||
VALUES (@ConfigMenuId, N'H5配置', '/business/config/h5', 'business/config/h5', 'Monitor', 2, 'config:view', 5, 1, 0, 1, GETDATE());
|
||
PRINT N'创建H5配置菜单';
|
||
END
|
||
|
||
-- 2.6 上传配置
|
||
IF NOT EXISTS (SELECT 1 FROM Menus WHERE Path = '/business/config/uploads')
|
||
BEGIN
|
||
INSERT INTO Menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
|
||
VALUES (@ConfigMenuId, N'上传配置', '/business/config/uploads', 'business/config/uploads', 'Upload', 2, 'config:view', 6, 1, 0, 1, GETDATE());
|
||
PRINT N'创建上传配置菜单';
|
||
END
|
||
|
||
-- 2.7 签到配置
|
||
IF NOT EXISTS (SELECT 1 FROM Menus WHERE Path = '/business/config/sign')
|
||
BEGIN
|
||
INSERT INTO Menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
|
||
VALUES (@ConfigMenuId, N'签到配置', '/business/config/sign', 'business/config/sign', 'Calendar', 2, 'config:view', 7, 1, 0, 1, GETDATE());
|
||
PRINT N'创建签到配置菜单';
|
||
END
|
||
|
||
-- 2.8 系统设置
|
||
IF NOT EXISTS (SELECT 1 FROM Menus WHERE Path = '/business/config/system')
|
||
BEGIN
|
||
INSERT INTO Menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
|
||
VALUES (@ConfigMenuId, N'系统设置', '/business/config/system', 'business/config/system', 'Tools', 2, 'config:view', 8, 1, 0, 1, GETDATE());
|
||
PRINT N'创建系统设置菜单';
|
||
END
|
||
|
||
-- =============================================
|
||
-- 3. 添加系统配置相关权限（如果不存在）
|
||
-- =============================================
|
||
|
||
-- 配置查看权限
|
||
IF NOT EXISTS (SELECT 1 FROM Permissions WHERE Code = 'config:view')
|
||
INSERT INTO Permissions (Name, Code, Module, CreatedAt) VALUES (N'查看配置', 'config:view', N'系统配置', GETDATE());
|
||
|
||
-- 配置编辑权限
|
||
IF NOT EXISTS (SELECT 1 FROM Permissions WHERE Code = 'config:edit')
|
||
INSERT INTO Permissions (Name, Code, Module, CreatedAt) VALUES (N'编辑配置', 'config:edit', N'系统配置', GETDATE());
|
||
|
||
-- =============================================
|
||
-- 4. 为超级管理员角色分配新菜单
|
||
-- =============================================
|
||
IF @SuperAdminRoleId IS NOT NULL
|
||
BEGIN
|
||
-- 分配系统配置目录
|
||
IF NOT EXISTS (SELECT 1 FROM RoleMenus WHERE RoleId = @SuperAdminRoleId AND MenuId = @ConfigMenuId)
|
||
BEGIN
|
||
INSERT INTO RoleMenus (RoleId, MenuId) VALUES (@SuperAdminRoleId, @ConfigMenuId);
|
||
PRINT N'为超级管理员分配系统配置目录';
|
||
END
|
||
|
||
-- 分配所有新创建的子菜单
|
||
INSERT INTO RoleMenus (RoleId, MenuId)
|
||
SELECT @SuperAdminRoleId, m.Id
|
||
FROM Menus m
|
||
WHERE m.ParentId = @ConfigMenuId
|
||
AND NOT EXISTS (SELECT 1 FROM RoleMenus rm WHERE rm.RoleId = @SuperAdminRoleId AND rm.MenuId = m.Id);
|
||
|
||
PRINT N'为超级管理员分配系统配置子菜单';
|
||
|
||
-- 分配新增的权限
|
||
INSERT INTO RolePermissions (RoleId, PermissionId)
|
||
SELECT @SuperAdminRoleId, p.Id
|
||
FROM Permissions p
|
||
WHERE p.Code IN ('config:view', 'config:edit')
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM RolePermissions rp
|
||
WHERE rp.RoleId = @SuperAdminRoleId AND rp.PermissionId = p.Id
|
||
);
|
||
|
||
PRINT N'为超级管理员分配系统配置权限';
|
||
END
|
||
|
||
-- =============================================
|
||
-- 5. 验证结果
|
||
-- =============================================
|
||
PRINT N'';
|
||
PRINT N'========== 菜单创建结果 ==========';
|
||
SELECT
|
||
m.Id,
|
||
m.ParentId,
|
||
m.Name,
|
||
m.Path,
|
||
m.Component,
|
||
m.MenuType,
|
||
m.Permission,
|
||
m.SortOrder
|
||
FROM Menus m
|
||
WHERE m.Path LIKE '/business/config%'
|
||
ORDER BY m.ParentId, m.SortOrder;
|
||
|
||
PRINT N'';
|
||
PRINT N'========== 权限创建结果 ==========';
|
||
SELECT
|
||
p.Id,
|
||
p.Name,
|
||
p.Code,
|
||
p.Module
|
||
FROM Permissions p
|
||
WHERE p.Code LIKE 'config:%'
|
||
ORDER BY p.Code;
|
||
|
||
PRINT N'';
|
||
PRINT N'========== 角色菜单分配结果 ==========';
|
||
SELECT
|
||
r.Name AS RoleName,
|
||
m.Name AS MenuName,
|
||
m.Path
|
||
FROM RoleMenus rm
|
||
INNER JOIN Roles r ON rm.RoleId = r.Id
|
||
INNER JOIN Menus m ON rm.MenuId = m.Id
|
||
WHERE m.Path LIKE '/business/config%'
|
||
ORDER BY r.Name, m.Path;
|
||
|
||
PRINT N'';
|
||
PRINT N'========== 角色权限分配结果 ==========';
|
||
SELECT
|
||
r.Name AS RoleName,
|
||
p.Name AS PermissionName,
|
||
p.Code
|
||
FROM RolePermissions rp
|
||
INNER JOIN Roles r ON rp.RoleId = r.Id
|
||
INNER JOIN Permissions p ON rp.PermissionId = p.Id
|
||
WHERE p.Code LIKE 'config:%'
|
||
ORDER BY r.Name, p.Code;
|
||
|
||
PRINT N'';
|
||
PRINT N'系统配置模块菜单初始化完成';
|