all-project/docs/设计文档/database/rs-role-permissions.sql

-- =================================================
-- 人事管理模块权限配置脚本
-- 创建日期: 2025-07-05
-- 作者: cursor
-- 说明: 配置人事管理模块的角色权限
-- =================================================

-- 检查并获取角色ID
DECLARE @adminRoleId INT = (SELECT role_id FROM sys_role WHERE role_key = 'admin');
DECLARE @commonRoleId INT = (SELECT role_id FROM sys_role WHERE role_key = 'common');

-- =============================
-- 1. 人事管理模块菜单权限
-- =============================

-- 获取人事管理顶级菜单及其子菜单ID
DECLARE @rsParentMenu INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '人事管理' AND parent_id = 0);

-- 获取职位管理菜单ID
DECLARE @jobMenu INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '职位管理' AND parent_id = @rsParentMenu);
-- 获取职位管理按钮ID
DECLARE @jobQuery INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '职位管理查询' AND parent_id = @jobMenu);
DECLARE @jobAdd INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '职位管理新增' AND parent_id = @jobMenu);
DECLARE @jobEdit INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '职位管理修改' AND parent_id = @jobMenu);
DECLARE @jobRemove INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '职位管理删除' AND parent_id = @jobMenu);
DECLARE @jobExport INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '职位管理导出' AND parent_id = @jobMenu);

-- 获取招聘中心菜单ID
DECLARE @publicJobMenu INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '招聘中心' AND parent_id = @rsParentMenu);

-- 获取申请管理菜单ID
DECLARE @appMenu INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '申请管理' AND parent_id = @rsParentMenu);
-- 获取申请管理按钮ID
DECLARE @appQuery INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '申请管理查询' AND parent_id = @appMenu);
DECLARE @appAdd INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '申请管理新增' AND parent_id = @appMenu);
DECLARE @appEdit INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '申请管理修改' AND parent_id = @appMenu);
DECLARE @appRemove INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '申请管理删除' AND parent_id = @appMenu);
DECLARE @appExport INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '申请管理导出' AND parent_id = @appMenu);

-- 获取应聘者管理菜单ID
DECLARE @applicantMenu INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '应聘者管理' AND parent_id = @rsParentMenu);
-- 获取应聘者管理按钮ID
DECLARE @applicantQuery INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '应聘者管理查询' AND parent_id = @applicantMenu);
DECLARE @applicantAdd INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '应聘者管理新增' AND parent_id = @applicantMenu);
DECLARE @applicantEdit INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '应聘者管理修改' AND parent_id = @applicantMenu);
DECLARE @applicantRemove INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '应聘者管理删除' AND parent_id = @applicantMenu);

-- 获取教育经历管理菜单ID
DECLARE @eduMenu INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '教育经历管理' AND parent_id = @rsParentMenu);
-- 获取教育经历管理按钮ID
DECLARE @eduQuery INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '教育经历管理查询' AND parent_id = @eduMenu);
DECLARE @eduAdd INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '教育经历管理新增' AND parent_id = @eduMenu);
DECLARE @eduEdit INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '教育经历管理修改' AND parent_id = @eduMenu);
DECLARE @eduRemove INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '教育经历管理删除' AND parent_id = @eduMenu);

-- 获取工作经历管理菜单ID
DECLARE @expMenu INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '工作经历管理' AND parent_id = @rsParentMenu);
-- 获取工作经历管理按钮ID
DECLARE @expQuery INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '工作经历管理查询' AND parent_id = @expMenu);
DECLARE @expAdd INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '工作经历管理新增' AND parent_id = @expMenu);
DECLARE @expEdit INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '工作经历管理修改' AND parent_id = @expMenu);
DECLARE @expRemove INT = (SELECT menu_id FROM sys_menu WHERE menu_name = '工作经历管理删除' AND parent_id = @expMenu);

-- =============================
-- 2. 为管理员角色添加所有人事管理权限
-- =============================

-- 检查并添加菜单权限 (先删除可能存在的权限，再添加)
-- 顶级菜单权限
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id = @rsParentMenu;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @rsParentMenu);

-- 职位管理权限
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id = @jobMenu;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @jobMenu);
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id IN (@jobQuery, @jobAdd, @jobEdit, @jobRemove, @jobExport);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @jobQuery);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @jobAdd);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @jobEdit);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @jobRemove);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @jobExport);

-- 招聘中心权限
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id = @publicJobMenu;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @publicJobMenu);

-- 申请管理权限
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id = @appMenu;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @appMenu);
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id IN (@appQuery, @appAdd, @appEdit, @appRemove, @appExport);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @appQuery);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @appAdd);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @appEdit);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @appRemove);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @appExport);

-- 应聘者管理权限
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id = @applicantMenu;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @applicantMenu);
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id IN (@applicantQuery, @applicantAdd, @applicantEdit, @applicantRemove);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @applicantQuery);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @applicantAdd);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @applicantEdit);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @applicantRemove);

-- 教育经历管理权限
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id = @eduMenu;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @eduMenu);
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id IN (@eduQuery, @eduAdd, @eduEdit, @eduRemove);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @eduQuery);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @eduAdd);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @eduEdit);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @eduRemove);

-- 工作经历管理权限
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id = @expMenu;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @expMenu);
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id IN (@expQuery, @expAdd, @expEdit, @expRemove);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @expQuery);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @expAdd);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @expEdit);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @expRemove);

-- =============================
-- 3. 为普通用户添加有限的人事管理权限
-- =============================

-- 招聘中心权限（查看公开职位）
DELETE FROM sys_role_menu WHERE role_id = @commonRoleId AND menu_id = @rsParentMenu;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@commonRoleId, @rsParentMenu);
DELETE FROM sys_role_menu WHERE role_id = @commonRoleId AND menu_id = @publicJobMenu;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@commonRoleId, @publicJobMenu);

-- 职位申请权限（仅查询）
DELETE FROM sys_role_menu WHERE role_id = @commonRoleId AND menu_id = @jobMenu;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@commonRoleId, @jobMenu);
DELETE FROM sys_role_menu WHERE role_id = @commonRoleId AND menu_id = @jobQuery;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@commonRoleId, @jobQuery);

-- 申请管理权限（仅新增）
DELETE FROM sys_role_menu WHERE role_id = @commonRoleId AND menu_id = @appMenu;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@commonRoleId, @appMenu);
DELETE FROM sys_role_menu WHERE role_id = @commonRoleId AND menu_id = @appAdd;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@commonRoleId, @appAdd);
DELETE FROM sys_role_menu WHERE role_id = @commonRoleId AND menu_id = @appQuery;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@commonRoleId, @appQuery);

-- =============================
-- 4. 确认权限分配
-- =============================

-- 输出验证信息
SELECT '人事管理模块权限分配完成' AS Status;
SELECT COUNT(*) AS AdminPermissionCount 
FROM sys_role_menu 
WHERE role_id = @adminRoleId 
AND menu_id IN (
  @rsParentMenu, @jobMenu, @publicJobMenu, @appMenu, @applicantMenu, @eduMenu, @expMenu,
  @jobQuery, @jobAdd, @jobEdit, @jobRemove, @jobExport,
  @appQuery, @appAdd, @appEdit, @appRemove, @appExport,
  @applicantQuery, @applicantAdd, @applicantEdit, @applicantRemove,
  @eduQuery, @eduAdd, @eduEdit, @eduRemove,
  @expQuery, @expAdd, @expEdit, @expRemove
);

SELECT COUNT(*) AS CommonUserPermissionCount 
FROM sys_role_menu 
WHERE role_id = @commonRoleId 
AND menu_id IN (@rsParentMenu, @publicJobMenu, @jobMenu, @jobQuery, @appMenu, @appAdd, @appQuery);