258 lines
11 KiB
Transact-SQL
258 lines
11 KiB
Transact-SQL
-- =============================================
|
||
-- 营销活动管理模块菜单初始化脚本
|
||
-- 用于在后台管理系统中添加营销活动管理相关菜单
|
||
-- 包含：优惠券管理、广告管理、排行榜管理
|
||
--
|
||
-- 注意：此脚本需要在 Admin 数据库中执行
|
||
-- 表名使用小写（menus, roles, permissions, role_menus, role_permissions）
|
||
-- =============================================
|
||
|
||
-- 注意：执行此脚本前请确保：
|
||
-- 1. 已存在超级管理员角色 (Code = 'super_admin')
|
||
-- 2. 数据库中已有基础菜单数据
|
||
|
||
-- 声明变量
|
||
DECLARE @MarketingMenuId 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/marketing')
|
||
BEGIN
|
||
INSERT INTO menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
|
||
VALUES (0, N'营销活动', '/business/marketing', 'Layout', 'Promotion', 1, NULL, 40, 1, 0, 0, GETDATE());
|
||
|
||
SET @MarketingMenuId = SCOPE_IDENTITY();
|
||
PRINT N'创建营销活动目录，ID: ' + CAST(@MarketingMenuId AS VARCHAR);
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
SELECT @MarketingMenuId = Id FROM menus WHERE Path = '/business/marketing';
|
||
UPDATE menus SET ParentId = 0, Component = 'Layout' WHERE Id = @MarketingMenuId AND ParentId <> 0;
|
||
PRINT N'营销活动目录已存在，ID: ' + CAST(@MarketingMenuId AS VARCHAR);
|
||
END
|
||
|
||
-- =============================================
|
||
-- 2. 创建优惠券管理子菜单
|
||
-- =============================================
|
||
|
||
-- 2.1 优惠券列表
|
||
IF NOT EXISTS (SELECT 1 FROM menus WHERE Path = '/business/coupon/list')
|
||
BEGIN
|
||
INSERT INTO menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
|
||
VALUES (@MarketingMenuId, N'优惠券管理', '/business/coupon/list', 'business/coupon/list', 'Ticket', 2, 'coupon:list', 1, 1, 0, 1, GETDATE());
|
||
PRINT N'创建优惠券管理菜单';
|
||
END
|
||
|
||
-- 2.2 优惠券领取记录
|
||
IF NOT EXISTS (SELECT 1 FROM menus WHERE Path = '/business/coupon/receive')
|
||
BEGIN
|
||
INSERT INTO menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
|
||
VALUES (@MarketingMenuId, N'领取记录', '/business/coupon/receive', 'business/coupon/receive', 'Document', 2, 'coupon:list', 2, 1, 0, 1, GETDATE());
|
||
PRINT N'创建优惠券领取记录菜单';
|
||
END
|
||
|
||
-- =============================================
|
||
-- 3. 创建广告管理子菜单
|
||
-- =============================================
|
||
|
||
-- 3.1 广告列表
|
||
IF NOT EXISTS (SELECT 1 FROM menus WHERE Path = '/business/advert/list')
|
||
BEGIN
|
||
INSERT INTO menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
|
||
VALUES (@MarketingMenuId, N'广告管理', '/business/advert/list', 'business/advert/list', 'Picture', 2, 'advert:list', 3, 1, 0, 1, GETDATE());
|
||
PRINT N'创建广告管理菜单';
|
||
END
|
||
|
||
|
||
-- =============================================
|
||
-- 4. 创建排行榜管理子菜单
|
||
-- =============================================
|
||
|
||
-- 4.1 周榜奖品
|
||
IF NOT EXISTS (SELECT 1 FROM menus WHERE Path = '/business/rank/week')
|
||
BEGIN
|
||
INSERT INTO menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
|
||
VALUES (@MarketingMenuId, N'周榜奖品', '/business/rank/week', 'business/rank/week', 'Trophy', 2, 'rank:list', 4, 1, 0, 1, GETDATE());
|
||
PRINT N'创建周榜奖品菜单';
|
||
END
|
||
|
||
-- 4.2 周榜中奖记录
|
||
IF NOT EXISTS (SELECT 1 FROM menus WHERE Path = '/business/rank/week-log')
|
||
BEGIN
|
||
INSERT INTO menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
|
||
VALUES (@MarketingMenuId, N'周榜记录', '/business/rank/week-log', 'business/rank/week-log', 'Document', 2, 'rank:list', 5, 1, 0, 1, GETDATE());
|
||
PRINT N'创建周榜中奖记录菜单';
|
||
END
|
||
|
||
-- 4.3 月榜奖品
|
||
IF NOT EXISTS (SELECT 1 FROM menus WHERE Path = '/business/rank/month')
|
||
BEGIN
|
||
INSERT INTO menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
|
||
VALUES (@MarketingMenuId, N'月榜奖品', '/business/rank/month', 'business/rank/month', 'Medal', 2, 'rank:list', 6, 1, 0, 1, GETDATE());
|
||
PRINT N'创建月榜奖品菜单';
|
||
END
|
||
|
||
-- 4.4 月榜中奖记录
|
||
IF NOT EXISTS (SELECT 1 FROM menus WHERE Path = '/business/rank/month-log')
|
||
BEGIN
|
||
INSERT INTO menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
|
||
VALUES (@MarketingMenuId, N'月榜记录', '/business/rank/month-log', 'business/rank/month-log', 'Document', 2, 'rank:list', 7, 1, 0, 1, GETDATE());
|
||
PRINT N'创建月榜中奖记录菜单';
|
||
END
|
||
|
||
-- 4.5 用户排行榜
|
||
IF NOT EXISTS (SELECT 1 FROM menus WHERE Path = '/business/rank/user')
|
||
BEGIN
|
||
INSERT INTO menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
|
||
VALUES (@MarketingMenuId, N'用户排行榜', '/business/rank/user', 'business/rank/user', 'DataLine', 2, 'rank:list', 8, 1, 0, 1, GETDATE());
|
||
PRINT N'创建用户排行榜菜单';
|
||
END
|
||
|
||
-- =============================================
|
||
-- 5. 添加优惠券管理相关权限
|
||
-- =============================================
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'coupon:list')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'优惠券列表', 'coupon:list', N'营销活动', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'coupon:add')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'新增优惠券', 'coupon:add', N'营销活动', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'coupon:edit')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'编辑优惠券', 'coupon:edit', N'营销活动', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'coupon:delete')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'删除优惠券', 'coupon:delete', N'营销活动', GETDATE());
|
||
|
||
-- =============================================
|
||
-- 6. 添加广告管理相关权限
|
||
-- =============================================
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'advert:list')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'广告列表', 'advert:list', N'营销活动', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'advert:add')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'新增广告', 'advert:add', N'营销活动', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'advert:edit')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'编辑广告', 'advert:edit', N'营销活动', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'advert:delete')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'删除广告', 'advert:delete', N'营销活动', GETDATE());
|
||
|
||
-- =============================================
|
||
-- 7. 添加排行榜管理相关权限
|
||
-- =============================================
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'rank:list')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'排行榜列表', 'rank:list', N'营销活动', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'rank:add')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'新增排行榜奖品', 'rank:add', N'营销活动', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'rank:edit')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'编辑排行榜奖品', 'rank:edit', N'营销活动', GETDATE());
|
||
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'rank:delete')
|
||
INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'删除排行榜奖品', 'rank:delete', N'营销活动', GETDATE());
|
||
|
||
-- =============================================
|
||
-- 8. 为超级管理员角色分配新菜单
|
||
-- =============================================
|
||
IF @SuperAdminRoleId IS NOT NULL
|
||
BEGIN
|
||
-- 分配营销活动目录
|
||
IF NOT EXISTS (SELECT 1 FROM role_menus WHERE RoleId = @SuperAdminRoleId AND MenuId = @MarketingMenuId)
|
||
BEGIN
|
||
INSERT INTO role_menus (RoleId, MenuId) VALUES (@SuperAdminRoleId, @MarketingMenuId);
|
||
PRINT N'为超级管理员分配营销活动目录';
|
||
END
|
||
|
||
-- 分配所有新创建的子菜单
|
||
INSERT INTO role_menus (RoleId, MenuId)
|
||
SELECT @SuperAdminRoleId, m.Id
|
||
FROM menus m
|
||
WHERE m.ParentId = @MarketingMenuId
|
||
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 (
|
||
'coupon:list', 'coupon:add', 'coupon:edit', 'coupon:delete',
|
||
'advert:list', 'advert:add', 'advert:edit', 'advert:delete',
|
||
'rank:list', 'rank:add', 'rank:edit', 'rank:delete'
|
||
)
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM role_permissions rp
|
||
WHERE rp.RoleId = @SuperAdminRoleId AND rp.PermissionId = p.Id
|
||
);
|
||
|
||
PRINT N'为超级管理员分配营销活动权限';
|
||
END
|
||
|
||
-- =============================================
|
||
-- 9. 验证结果
|
||
-- =============================================
|
||
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/marketing%'
|
||
OR m.Path LIKE '/business/coupon%'
|
||
OR m.Path LIKE '/business/advert%'
|
||
OR m.Path LIKE '/business/rank%'
|
||
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 'coupon:%'
|
||
OR p.Code LIKE 'advert:%'
|
||
OR p.Code LIKE 'rank:%'
|
||
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/marketing%'
|
||
OR m.Path LIKE '/business/coupon%'
|
||
OR m.Path LIKE '/business/advert%'
|
||
OR m.Path LIKE '/business/rank%'
|
||
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 'coupon:%'
|
||
OR p.Code LIKE 'advert:%'
|
||
OR p.Code LIKE 'rank:%'
|
||
ORDER BY r.Name, p.Code;
|
||
|
||
PRINT N'';
|
||
PRINT N'营销活动管理模块菜单初始化完成';
|