249 lines
12 KiB
Transact-SQL
249 lines
12 KiB
Transact-SQL
-- =============================================
|
||
-- 福利与任务模块菜单初始化脚本
|
||
-- 用于在后台管理系统中添加福利与任务相关菜单
|
||
-- 包含：奖励管理、签到配置、任务管理、权益等级
|
||
--
|
||
-- 注意：此脚本需要在 Admin 数据库中执行
|
||
-- 表名使用小写（menus, roles, permissions, role_menus, role_permissions）
|
||
-- =============================================
|
||
|
||
-- 注意：执行此脚本前请确保：
|
||
-- 1. 已存在超级管理员角色 (Code = 'super_admin')
|
||
-- 2. 数据库中已有基础菜单数据
|
||
|
||
-- 声明变量
|
||
DECLARE @WelfareMenuId BIGINT;
|
||
DECLARE @SuperAdminRoleId BIGINT;
|
||
|
||
-- 获取超级管理员角色ID
|
||
SELECT @SuperAdminRoleId = Id FROM roles WHERE Code = 'super_admin';
|
||
|
||
-- =============================================
|
||
-- 1. 创建福利与任务目录（顶级菜单）
|
||
-- =============================================
|
||
IF NOT EXISTS (SELECT 1 FROM menus WHERE Path = '/business/welfare')
|
||
BEGIN
|
||
INSERT INTO menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
|
||
VALUES (0, N'福利与任务', '/business/welfare', 'Layout', 'Present', 1, NULL, 80, 1, 0, 0, GETDATE());
|
||
|
||
SET @WelfareMenuId = SCOPE_IDENTITY();
|
||
PRINT N'创建福利与任务目录，ID: ' + CAST(@WelfareMenuId AS VARCHAR);
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
SELECT @WelfareMenuId = Id FROM menus WHERE Path = '/business/welfare';
|
||
UPDATE menus SET ParentId = 0, Component = 'Layout' WHERE Id = @WelfareMenuId AND ParentId <> 0;
|
||
PRINT N'福利与任务目录已存在，ID: ' + CAST(@WelfareMenuId AS VARCHAR);
|
||
END
|
||
|
||
-- =============================================
|
||
-- 2. 创建奖励管理子菜单
|
||
-- =============================================
|
||
IF NOT EXISTS (SELECT 1 FROM menus WHERE Path = '/business/reward/list')
|
||
BEGIN
|
||
INSERT INTO menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
|
||
VALUES (@WelfareMenuId, N'奖励管理', '/business/reward/list', 'business/reward/list', 'Medal', 2, 'reward:list', 1, 1, 0, 1, GETDATE());
|
||
PRINT N'创建奖励管理菜单';
|
||
END
|
||
|
||
-- =============================================
|
||
-- 3. 创建签到配置子菜单
|
||
-- =============================================
|
||
IF NOT EXISTS (SELECT 1 FROM menus WHERE Path = '/business/signconfig/list')
|
||
BEGIN
|
||
INSERT INTO menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
|
||
VALUES (@WelfareMenuId, N'签到配置', '/business/signconfig/list', 'business/signconfig/list', 'Calendar', 2, 'signconfig:list', 2, 1, 0, 1, GETDATE());
|
||
PRINT N'创建签到配置菜单';
|
||
END
|
||
|
||
-- =============================================
|
||
-- 4. 创建任务管理子菜单
|
||
-- =============================================
|
||
IF NOT EXISTS (SELECT 1 FROM menus WHERE Path = '/business/task/list')
|
||
BEGIN
|
||
INSERT INTO menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
|
||
VALUES (@WelfareMenuId, N'任务管理', '/business/task/list', 'business/task/list', 'List', 2, 'task:list', 3, 1, 0, 1, GETDATE());
|
||
PRINT N'创建任务管理菜单';
|
||
END
|
||
|
||
-- =============================================
|
||
-- 5. 创建权益等级子菜单
|
||
-- =============================================
|
||
IF NOT EXISTS (SELECT 1 FROM menus WHERE Path = '/business/qylevel/list')
|
||
BEGIN
|
||
INSERT INTO menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
|
||
VALUES (@WelfareMenuId, N'权益等级', '/business/qylevel/list', 'business/qylevel/list', 'Trophy', 2, 'qylevel:list', 4, 1, 0, 1, GETDATE());
|
||
PRINT N'创建权益等级菜单';
|
||
END
|
||
|
||
|
||
-- =============================================
|
||
-- 6. 添加奖励管理相关权限
|
||
-- =============================================
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'reward:list')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'奖励列表', 'reward:list', N'福利与任务', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'reward:add')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'新增奖励', 'reward:add', N'福利与任务', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'reward:edit')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'编辑奖励', 'reward:edit', N'福利与任务', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'reward:delete')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'删除奖励', 'reward:delete', N'福利与任务', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'reward:status')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'奖励状态', 'reward:status', N'福利与任务', GETDATE());
|
||
|
||
-- =============================================
|
||
-- 7. 添加签到配置相关权限
|
||
-- =============================================
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'signconfig:list')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'签到配置列表', 'signconfig:list', N'福利与任务', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'signconfig:add')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'新增签到配置', 'signconfig:add', N'福利与任务', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'signconfig:edit')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'编辑签到配置', 'signconfig:edit', N'福利与任务', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'signconfig:delete')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'删除签到配置', 'signconfig:delete', N'福利与任务', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'signconfig:status')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'签到配置状态', 'signconfig:status', N'福利与任务', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'signconfig:sort')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'签到配置排序', 'signconfig:sort', N'福利与任务', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'signconfig:reward')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'签到奖励配置', 'signconfig:reward', N'福利与任务', GETDATE());
|
||
|
||
-- =============================================
|
||
-- 8. 添加任务管理相关权限
|
||
-- =============================================
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'task:list')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'任务列表', 'task:list', N'福利与任务', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'task:add')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'新增任务', 'task:add', N'福利与任务', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'task:edit')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'编辑任务', 'task:edit', N'福利与任务', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'task:delete')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'删除任务', 'task:delete', N'福利与任务', GETDATE());
|
||
|
||
-- =============================================
|
||
-- 9. 添加权益等级相关权限
|
||
-- =============================================
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'qylevel:list')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'权益等级列表', 'qylevel:list', N'福利与任务', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'qylevel:edit')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'编辑权益等级', 'qylevel:edit', N'福利与任务', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'qylevel:prize-list')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'权益奖品列表', 'qylevel:prize-list', N'福利与任务', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'qylevel:prize-add')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'新增权益奖品', 'qylevel:prize-add', N'福利与任务', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'qylevel:prize-edit')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'编辑权益奖品', 'qylevel:prize-edit', N'福利与任务', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'qylevel:prize-delete')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'删除权益奖品', 'qylevel:prize-delete', N'福利与任务', GETDATE());
|
||
|
||
|
||
-- =============================================
|
||
-- 10. 为超级管理员角色分配新菜单
|
||
-- =============================================
|
||
IF @SuperAdminRoleId IS NOT NULL
|
||
BEGIN
|
||
-- 分配福利与任务目录
|
||
IF NOT EXISTS (SELECT 1 FROM role_menus WHERE RoleId = @SuperAdminRoleId AND MenuId = @WelfareMenuId)
|
||
BEGIN
|
||
INSERT INTO role_menus (RoleId, MenuId) VALUES (@SuperAdminRoleId, @WelfareMenuId);
|
||
PRINT N'为超级管理员分配福利与任务目录';
|
||
END
|
||
|
||
-- 分配所有新创建的子菜单
|
||
INSERT INTO role_menus (RoleId, MenuId)
|
||
SELECT @SuperAdminRoleId, m.Id
|
||
FROM menus m
|
||
WHERE m.ParentId = @WelfareMenuId
|
||
AND NOT EXISTS (SELECT 1 FROM role_menus rm WHERE rm.RoleId = @SuperAdminRoleId AND rm.MenuId = m.Id);
|
||
|
||
PRINT N'为超级管理员分配福利与任务子菜单';
|
||
|
||
-- 分配新增的权限
|
||
INSERT INTO role_permissions (RoleId, PermissionId)
|
||
SELECT @SuperAdminRoleId, p.Id
|
||
FROM permissions p
|
||
WHERE p.Code IN (
|
||
'reward:list', 'reward:add', 'reward:edit', 'reward:delete', 'reward:status',
|
||
'signconfig:list', 'signconfig:add', 'signconfig:edit', 'signconfig:delete', 'signconfig:status', 'signconfig:sort', 'signconfig:reward',
|
||
'task:list', 'task:add', 'task:edit', 'task:delete',
|
||
'qylevel:list', 'qylevel:edit', 'qylevel:prize-list', 'qylevel:prize-add', 'qylevel:prize-edit', 'qylevel:prize-delete'
|
||
)
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM role_permissions rp
|
||
WHERE rp.RoleId = @SuperAdminRoleId AND rp.PermissionId = p.Id
|
||
);
|
||
|
||
PRINT N'为超级管理员分配福利与任务权限';
|
||
END
|
||
|
||
-- =============================================
|
||
-- 11. 验证结果
|
||
-- =============================================
|
||
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/welfare%'
|
||
OR m.Path LIKE '/business/reward%'
|
||
OR m.Path LIKE '/business/signconfig%'
|
||
OR m.Path LIKE '/business/task%'
|
||
OR m.Path LIKE '/business/qylevel%'
|
||
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 'reward:%'
|
||
OR p.Code LIKE 'signconfig:%'
|
||
OR p.Code LIKE 'task:%'
|
||
OR p.Code LIKE 'qylevel:%'
|
||
ORDER BY p.Code;
|
||
|
||
PRINT N'';
|
||
PRINT N'========== 角色菜单分配结果 ==========';
|
||
SELECT
|
||
r.Name AS RoleName,
|
||
m.Name AS MenuName,
|
||
m.Path
|
||
FROM role_menus rm
|
||
INNER JOIN roles r ON rm.RoleId = r.Id
|
||
INNER JOIN menus m ON rm.MenuId = m.Id
|
||
WHERE m.Path LIKE '/business/welfare%'
|
||
OR m.Path LIKE '/business/reward%'
|
||
OR m.Path LIKE '/business/signconfig%'
|
||
OR m.Path LIKE '/business/task%'
|
||
OR m.Path LIKE '/business/qylevel%'
|
||
ORDER BY r.Name, m.Path;
|
||
|
||
PRINT N'';
|
||
PRINT N'========== 角色权限分配结果 ==========';
|
||
SELECT
|
||
r.Name AS RoleName,
|
||
p.Name AS PermissionName,
|
||
p.Code
|
||
FROM role_permissions rp
|
||
INNER JOIN roles r ON rp.RoleId = r.Id
|
||
INNER JOIN permissions p ON rp.PermissionId = p.Id
|
||
WHERE p.Code LIKE 'reward:%'
|
||
OR p.Code LIKE 'signconfig:%'
|
||
OR p.Code LIKE 'task:%'
|
||
OR p.Code LIKE 'qylevel:%'
|
||
ORDER BY r.Name, p.Code;
|
||
|
||
PRINT N'';
|
||
PRINT N'福利与任务模块菜单初始化完成';
|