all-project/docs/设计文档/database/09-overseas-application-menu.sql

-- 出国申请管理模块菜单权限配置
-- 创建时间: 2025-01-28
-- 模块说明: 为出国申请管理功能创建菜单和权限配置
-- 包含: 出国申请管理、出国审批管理的完整菜单权限体系

-- 使用雪花算法生成ID (这里使用固定ID，实际应用中应使用雪花算法)
SET @baseMenuId = 2200;  -- 基础ID，每个新菜单递增

-- 查找人事管理顶级菜单ID
SET @parentId = (SELECT menu_id FROM sys_menu WHERE menu_name = '人事管理' AND parent_id = 0);

-- 如果人事管理菜单不存在，则创建
INSERT IGNORE INTO sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES (2050, '人事管理', 0, 50, 'rs', null, '', 1, 0, 'M', '0', '0', '', 'peoples', 1, now(), null, null, '人事管理目录');

-- 确保获取到人事管理菜单ID
SET @parentId = IFNULL(@parentId, 2050);

-- 出国申请管理菜单
INSERT INTO sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES (@baseMenuId, '出国申请管理', @parentId, 10, 'overseas', 'rs/overseas/index', '', 1, 0, 'C', '0', '0', 'rs:overseas:application:list', 'guide', 1, now(), null, null, '出国申请管理菜单');

-- 获取出国申请管理菜单ID
SET @overseasMenuId = @baseMenuId;
SET @baseMenuId = @baseMenuId + 1;

-- 出国申请管理按钮权限

-- 出国申请查询权限
INSERT INTO sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES (@baseMenuId, '出国申请查询', @overseasMenuId, 1, '', '', '', 1, 0, 'F', '0', '0', 'rs:overseas:application:query', '#', 1, now(), null, null, '');
SET @baseMenuId = @baseMenuId + 1;

-- 出国申请新增权限
INSERT INTO sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES (@baseMenuId, '出国申请新增', @overseasMenuId, 2, '', '', '', 1, 0, 'F', '0', '0', 'rs:overseas:application:add', '#', 1, now(), null, null, '');
SET @baseMenuId = @baseMenuId + 1;

-- 出国申请修改权限
INSERT INTO sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES (@baseMenuId, '出国申请修改', @overseasMenuId, 3, '', '', '', 1, 0, 'F', '0', '0', 'rs:overseas:application:edit', '#', 1, now(), null, null, '');
SET @baseMenuId = @baseMenuId + 1;

-- 出国申请删除权限
INSERT INTO sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES (@baseMenuId, '出国申请删除', @overseasMenuId, 4, '', '', '', 1, 0, 'F', '0', '0', 'rs:overseas:application:remove', '#', 1, now(), null, null, '');
SET @baseMenuId = @baseMenuId + 1;

-- 出国申请导出权限
INSERT INTO sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES (@baseMenuId, '出国申请导出', @overseasMenuId, 5, '', '', '', 1, 0, 'F', '0', '0', 'rs:overseas:application:export', '#', 1, now(), null, null, '');
SET @baseMenuId = @baseMenuId + 1;

-- 出国申请提交权限
INSERT INTO sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES (@baseMenuId, '出国申请提交', @overseasMenuId, 6, '', '', '', 1, 0, 'F', '0', '0', 'rs:overseas:application:submit', '#', 1, now(), null, null, '');
SET @baseMenuId = @baseMenuId + 1;

-- 出国申请取消权限
INSERT INTO sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES (@baseMenuId, '出国申请取消', @overseasMenuId, 7, '', '', '', 1, 0, 'F', '0', '0', 'rs:overseas:application:cancel', '#', 1, now(), null, null, '');
SET @baseMenuId = @baseMenuId + 1;

-- 出国申请审批权限
INSERT INTO sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES (@baseMenuId, '出国申请审批', @overseasMenuId, 8, '', '', '', 1, 0, 'F', '0', '0', 'rs:overseas:application:approve', '#', 1, now(), null, null, '');
SET @baseMenuId = @baseMenuId + 1;

-- 出国记录查询权限
INSERT INTO sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES (@baseMenuId, '出国记录查询', @overseasMenuId, 9, '', '', '', 1, 0, 'F', '0', '0', 'rs:overseas:record:query', '#', 1, now(), null, null, '');
SET @baseMenuId = @baseMenuId + 1;

-- 出国记录新增权限
INSERT INTO sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES (@baseMenuId, '出国记录新增', @overseasMenuId, 10, '', '', '', 1, 0, 'F', '0', '0', 'rs:overseas:record:add', '#', 1, now(), null, null, '');
SET @baseMenuId = @baseMenuId + 1;

-- 出国记录修改权限
INSERT INTO sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES (@baseMenuId, '出国记录修改', @overseasMenuId, 11, '', '', '', 1, 0, 'F', '0', '0', 'rs:overseas:record:edit', '#', 1, now(), null, null, '');
SET @baseMenuId = @baseMenuId + 1;

-- 出国记录删除权限
INSERT INTO sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES (@baseMenuId, '出国记录删除', @overseasMenuId, 12, '', '', '', 1, 0, 'F', '0', '0', 'rs:overseas:record:remove', '#', 1, now(), null, null, '');
SET @baseMenuId = @baseMenuId + 1;

-- 出国记录导出权限
INSERT INTO sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES (@baseMenuId, '出国记录导出', @overseasMenuId, 13, '', '', '', 1, 0, 'F', '0', '0', 'rs:overseas:record:export', '#', 1, now(), null, null, '');
SET @baseMenuId = @baseMenuId + 1;

-- 出国记录创建权限
INSERT INTO sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES (@baseMenuId, '出国记录创建', @overseasMenuId, 14, '', '', '', 1, 0, 'F', '0', '0', 'rs:overseas:record:create', '#', 1, now(), null, null, '');
SET @baseMenuId = @baseMenuId + 1;

-- 出国记录完成权限
INSERT INTO sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES (@baseMenuId, '出国记录完成', @overseasMenuId, 15, '', '', '', 1, 0, 'F', '0', '0', 'rs:overseas:record:complete', '#', 1, now(), null, null, '');
SET @baseMenuId = @baseMenuId + 1;

-- 出国审批管理菜单
INSERT INTO sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES (@baseMenuId, '出国审批管理', @parentId, 11, 'overseas/approval', 'rs/overseas/approval', '', 1, 0, 'C', '0', '0', 'rs:overseas:approval:list', 'audit', 1, now(), null, null, '出国审批管理菜单');

-- 获取出国审批管理菜单ID
SET @approvalMenuId = @baseMenuId;
SET @baseMenuId = @baseMenuId + 1;

-- 出国审批管理按钮权限

-- 出国审批查询权限
INSERT INTO sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES (@baseMenuId, '出国审批查询', @approvalMenuId, 1, '', '', '', 1, 0, 'F', '0', '0', 'rs:overseas:approval:query', '#', 1, now(), null, null, '');
SET @baseMenuId = @baseMenuId + 1;

-- 出国审批新增权限
INSERT INTO sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES (@baseMenuId, '出国审批新增', @approvalMenuId, 2, '', '', '', 1, 0, 'F', '0', '0', 'rs:overseas:approval:add', '#', 1, now(), null, null, '');
SET @baseMenuId = @baseMenuId + 1;

-- 出国审批修改权限
INSERT INTO sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES (@baseMenuId, '出国审批修改', @approvalMenuId, 3, '', '', '', 1, 0, 'F', '0', '0', 'rs:overseas:approval:edit', '#', 1, now(), null, null, '');
SET @baseMenuId = @baseMenuId + 1;

-- 出国审批删除权限
INSERT INTO sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES (@baseMenuId, '出国审批删除', @approvalMenuId, 4, '', '', '', 1, 0, 'F', '0', '0', 'rs:overseas:approval:remove', '#', 1, now(), null, null, '');
SET @baseMenuId = @baseMenuId + 1;

-- 出国审批导出权限
INSERT INTO sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES (@baseMenuId, '出国审批导出', @approvalMenuId, 5, '', '', '', 1, 0, 'F', '0', '0', 'rs:overseas:approval:export', '#', 1, now(), null, null, '');

-- 为管理员角色分配权限
-- 获取管理员角色ID
SET @adminRoleId = (SELECT role_id FROM sys_role WHERE role_key = 'admin' LIMIT 1);

-- 为管理员角色分配出国申请管理菜单的所有权限
INSERT INTO sys_role_menu (role_id, menu_id) 
SELECT @adminRoleId, menu_id FROM sys_menu 
WHERE menu_id >= @overseasMenuId AND menu_id <= (@overseasMenuId + 15)
AND NOT EXISTS (SELECT 1 FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id = sys_menu.menu_id);

-- 为管理员角色分配出国审批管理菜单的所有权限
INSERT INTO sys_role_menu (role_id, menu_id) 
SELECT @adminRoleId, menu_id FROM sys_menu 
WHERE menu_id >= @approvalMenuId AND menu_id <= (@approvalMenuId + 5)
AND NOT EXISTS (SELECT 1 FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id = sys_menu.menu_id);

-- 确认菜单创建成功
SELECT '出国申请管理模块菜单权限配置完成!' AS message;