-- 项目管理权限、分类主键和流程安全升级。 -- 执行前请备份数据库;本脚本只创建权限项,不自动扩大普通角色权限。 SET NAMES utf8mb4; DROP PROCEDURE IF EXISTS `add_column_if_missing`; DELIMITER $$ CREATE PROCEDURE `add_column_if_missing`(IN p_table varchar(64), IN p_column varchar(64), IN p_definition varchar(1000)) BEGIN IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=p_table AND COLUMN_NAME=p_column) THEN SET @ddl=CONCAT('ALTER TABLE `',p_table,'` ADD COLUMN `',p_column,'` ',p_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; END$$ DELIMITER ; CALL add_column_if_missing('project_management_info','project_category_id',"bigint DEFAULT NULL COMMENT '项目分类ID' AFTER project_classification"); DROP PROCEDURE IF EXISTS `add_column_if_missing`; -- 名称唯一的历史项目可安全回填;重名分类留给用户重新选择。 UPDATE project_management_info p JOIN (SELECT category_name,MIN(id) id FROM project_management_category WHERE is_deleted=0 GROUP BY category_name HAVING COUNT(*)=1) c ON c.category_name=p.project_classification SET p.project_category_id=c.id WHERE p.project_category_id IS NULL AND p.is_delete=0; SET @project_list_menu=2285; UPDATE sys_menu SET perms='project:info:list',visible='0',status='0',update_time=NOW() WHERE menu_id=@project_list_menu; INSERT INTO sys_menu(menu_name,parent_id,order_num,path,component,route_name,is_frame,is_cache,menu_type,visible,status,perms,icon,create_by,create_time,remark) SELECT '查看项目',@project_list_menu,11,'#',NULL,'',1,0,'F','0','0','project:info:query','#','admin',NOW(),'' WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='project:info:query'); INSERT INTO sys_menu(menu_name,parent_id,order_num,path,component,route_name,is_frame,is_cache,menu_type,visible,status,perms,icon,create_by,create_time,remark) SELECT '新增项目',@project_list_menu,12,'#',NULL,'',1,0,'F','0','0','project:info:add','#','admin',NOW(),'' WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='project:info:add'); INSERT INTO sys_menu(menu_name,parent_id,order_num,path,component,route_name,is_frame,is_cache,menu_type,visible,status,perms,icon,create_by,create_time,remark) SELECT '修改项目',@project_list_menu,13,'#',NULL,'',1,0,'F','0','0','project:info:edit','#','admin',NOW(),'' WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='project:info:edit'); INSERT INTO sys_menu(menu_name,parent_id,order_num,path,component,route_name,is_frame,is_cache,menu_type,visible,status,perms,icon,create_by,create_time,remark) SELECT '删除项目',@project_list_menu,14,'#',NULL,'',1,0,'F','0','0','project:info:remove','#','admin',NOW(),'' WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='project:info:remove'); INSERT INTO sys_menu(menu_name,parent_id,order_num,path,component,route_name,is_frame,is_cache,menu_type,visible,status,perms,icon,create_by,create_time,remark) SELECT '开始项目',@project_list_menu,15,'#',NULL,'',1,0,'F','0','0','project:start','#','admin',NOW(),'' WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='project:start'); -- Ensure the category menu exists even when this hardening script is applied independently. INSERT INTO sys_menu(menu_name,parent_id,order_num,path,component,route_name,is_frame,is_cache,menu_type,visible,status,perms,icon,create_by,create_time,remark) SELECT '项目分类',2276,2,'projectCategory','projectManagement/projectCategory/index','ProjectCategory',1,0,'C','0','0','project:category:list','tree','admin',NOW(),'多层树形项目分类维护' WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE parent_id=2276 AND path='projectCategory'); -- Workflow and execution button permissions. INSERT INTO sys_menu(menu_name,parent_id,order_num,path,component,route_name,is_frame,is_cache,menu_type,visible,status,perms,icon,create_by,create_time,remark) SELECT '模块维护',@project_list_menu,21,'#',NULL,'',1,0,'F','0','0','project:module:edit','#','admin',NOW(),'' WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='project:module:edit'); INSERT INTO sys_menu(menu_name,parent_id,order_num,path,component,route_name,is_frame,is_cache,menu_type,visible,status,perms,icon,create_by,create_time,remark) SELECT '模块删除',@project_list_menu,22,'#',NULL,'',1,0,'F','0','0','project:module:remove','#','admin',NOW(),'' WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='project:module:remove'); INSERT INTO sys_menu(menu_name,parent_id,order_num,path,component,route_name,is_frame,is_cache,menu_type,visible,status,perms,icon,create_by,create_time,remark) SELECT '新增或修改跟进',@project_list_menu,23,'#',NULL,'',1,0,'F','0','0','project:follow:add','#','admin',NOW(),'' WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='project:follow:add'); INSERT INTO sys_menu(menu_name,parent_id,order_num,path,component,route_name,is_frame,is_cache,menu_type,visible,status,perms,icon,create_by,create_time,remark) SELECT '删除跟进',@project_list_menu,24,'#',NULL,'',1,0,'F','0','0','project:follow:remove','#','admin',NOW(),'' WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='project:follow:remove'); INSERT INTO sys_menu(menu_name,parent_id,order_num,path,component,route_name,is_frame,is_cache,menu_type,visible,status,perms,icon,create_by,create_time,remark) SELECT '新增或修改汇报',@project_list_menu,25,'#',NULL,'',1,0,'F','0','0','project:report:add','#','admin',NOW(),'' WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='project:report:add'); INSERT INTO sys_menu(menu_name,parent_id,order_num,path,component,route_name,is_frame,is_cache,menu_type,visible,status,perms,icon,create_by,create_time,remark) SELECT '删除汇报',@project_list_menu,26,'#',NULL,'',1,0,'F','0','0','project:report:remove','#','admin',NOW(),'' WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='project:report:remove'); INSERT INTO sys_menu(menu_name,parent_id,order_num,path,component,route_name,is_frame,is_cache,menu_type,visible,status,perms,icon,create_by,create_time,remark) SELECT '新增或修改资料',@project_list_menu,27,'#',NULL,'',1,0,'F','0','0','project:material:add','#','admin',NOW(),'' WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='project:material:add'); INSERT INTO sys_menu(menu_name,parent_id,order_num,path,component,route_name,is_frame,is_cache,menu_type,visible,status,perms,icon,create_by,create_time,remark) SELECT '删除资料',@project_list_menu,28,'#',NULL,'',1,0,'F','0','0','project:material:remove','#','admin',NOW(),'' WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='project:material:remove'); INSERT INTO sys_menu(menu_name,parent_id,order_num,path,component,route_name,is_frame,is_cache,menu_type,visible,status,perms,icon,create_by,create_time,remark) SELECT '维护项目阶段',@project_list_menu,29,'#',NULL,'',1,0,'F','0','0','project:phase:edit','#','admin',NOW(),'' WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='project:phase:edit'); SET @project_category_menu=(SELECT menu_id FROM sys_menu WHERE parent_id=2276 AND path='projectCategory' ORDER BY menu_id LIMIT 1); INSERT INTO sys_menu(menu_name,parent_id,order_num,path,component,route_name,is_frame,is_cache,menu_type,visible,status,perms,icon,create_by,create_time,remark) SELECT '维护项目分类',@project_category_menu,1,'#',NULL,'',1,0,'F','0','0','project:category:edit','#','admin',NOW(),'' WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='project:category:edit'); INSERT INTO sys_menu(menu_name,parent_id,order_num,path,component,route_name,is_frame,is_cache,menu_type,visible,status,perms,icon,create_by,create_time,remark) SELECT '删除项目分类',@project_category_menu,2,'#',NULL,'',1,0,'F','0','0','project:category:remove','#','admin',NOW(),'' WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='project:category:remove'); -- 超级管理员角色补齐按钮;其他角色由“角色管理”按岗位勾选。 INSERT IGNORE INTO sys_role_menu(role_id,menu_id) SELECT 1,menu_id FROM sys_menu WHERE status='0' AND perms IN ( 'project:info:list','project:info:query','project:info:add','project:info:edit','project:info:remove','project:start', 'project:module:edit','project:module:remove','project:follow:add','project:follow:remove', 'project:report:add','project:report:remove','project:material:add','project:material:remove', 'project:phase:edit','project:category:list','project:category:edit','project:category:remove' );