-- ===================================================================== -- 诚发五金菜单对标《附件一:技术方案-诚发五金(2026)》 -- 目标:侧边栏的 7 个业务模块及其子菜单与方案章节标题一致; -- 方案未涉及的标准品页面只隐藏(visible=0),不删除数据; -- 工单助手为 APP,本次不建菜单。 -- 适用库:诚发五金本地库 CFWJ(单租户,脚本不含 tenant_id) -- 幂等:可重复执行;执行前整表备份到 system_menu_bak_20261102 -- 注意:只调整 type=1/2;type=3 按钮不移动、不隐藏,接口权限不受影响。 -- ===================================================================== SET NAMES utf8mb4; -- ==================== 0. 备份(DDL 会隐式提交,必须放在事务外) ==================== CREATE TABLE IF NOT EXISTS `system_menu_bak_20261102` AS SELECT * FROM `system_menu`; START TRANSACTION; -- ==================== 1. 定位 7 个业务模块 ==================== SET @plan_id = (SELECT id FROM `system_menu` WHERE deleted = b'0' AND parent_id = 0 AND name = '计划排程' ORDER BY id LIMIT 1); SET @prod_id = (SELECT id FROM `system_menu` WHERE deleted = b'0' AND parent_id = 0 AND name = '生产管控' ORDER BY id LIMIT 1); SET @dev_id = (SELECT id FROM `system_menu` WHERE deleted = b'0' AND parent_id = 0 AND name = '设备管理' ORDER BY id LIMIT 1); SET @pur_id = (SELECT id FROM `system_menu` WHERE deleted = b'0' AND parent_id = 0 AND name = '采购管理' ORDER BY id LIMIT 1); SET @wms_id = (SELECT id FROM `system_menu` WHERE deleted = b'0' AND parent_id = 0 AND name = '仓储物流' ORDER BY id LIMIT 1); SET @sale_id = (SELECT id FROM `system_menu` WHERE deleted = b'0' AND parent_id = 0 AND name = '营销管理' ORDER BY id LIMIT 1); SET @hrm_id = (SELECT id FROM `system_menu` WHERE deleted = b'0' AND parent_id = 0 AND name = '人力资源' ORDER BY id LIMIT 1); -- ==================== 2. 计划排程 ==================== -- 1.1 计划查询与筛选 / 1.2 新增生产计划 / 1.3 计划列表管理 → 同一个「生产计划」页面 UPDATE `system_menu` SET `name` = '生产计划', `parent_id` = @plan_id, `sort` = 1, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 602504; -- ==================== 3. 生产管控 ==================== -- 2.1 生产工单 UPDATE `system_menu` SET `name` = '生产工单', `parent_id` = @prod_id, `sort` = 1, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 5530; -- 2.1③「查看执行情况」依赖报工数据:保留生产报工页面(方案未单列,作为工单执行的配套页) UPDATE `system_menu` SET `name` = '生产报工', `parent_id` = @prod_id, `sort` = 2, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 5550; -- 2.2 领料管理(目录:领料出库 / 余料入库) -- 说明:方案 2.2②「补领辅料」与 2.2①「领料出库」在同一张领料单上完成 -- (同页面用「领料类型」区分,工具栏已有「补领辅料」按钮),合并为「领料出库」一个菜单,不重复建菜单。 SET @issue_dir = (SELECT id FROM `system_menu` WHERE deleted = b'0' AND type = 1 AND parent_id = @prod_id AND name = '领料管理' ORDER BY id LIMIT 1); INSERT INTO `system_menu` (`name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT '领料管理', '', 1, 3, @prod_id, 'issue', 'ep:box', NULL, NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE @issue_dir IS NULL; SET @issue_dir = (SELECT id FROM `system_menu` WHERE deleted = b'0' AND type = 1 AND parent_id = @prod_id AND name = '领料管理' ORDER BY id LIMIT 1); UPDATE `system_menu` SET `name` = '领料出库', `parent_id` = @issue_dir, `sort` = 1, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 5805; UPDATE `system_menu` SET `name` = '余料入库', `parent_id` = @issue_dir, `sort` = 2, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 5840; -- 2.3 生产主体等基础设置不单列菜单,归入支撑模块「基础数据」;方案未列的生产排产隐藏 SET @basic_id = (SELECT id FROM `system_menu` WHERE deleted = b'0' AND parent_id = 0 AND name = '基础数据' ORDER BY id LIMIT 1); UPDATE `system_menu` SET `name` = '工厂设置', `parent_id` = @basic_id, `sort` = 10, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 1075495; UPDATE `system_menu` SET `name` = '车间设置', `parent_id` = @basic_id, `sort` = 11, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 5160; UPDATE `system_menu` SET `name` = '工作站设置', `parent_id` = @basic_id, `sort` = 12, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 5170; -- 设备类型同属基础主数据,归入「基础数据」 UPDATE `system_menu` SET `name` = '设备类型', `parent_id` = @basic_id, `sort` = 13, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 5310; -- 清空后的旧目录/生产排产隐藏 UPDATE `system_menu` SET `visible` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` IN (5101, 5700, 5540); -- ==================== 4. 设备管理 ==================== -- 3.1 设备基础信息管理(设备台账) UPDATE `system_menu` SET `name` = '设备基础信息管理', `parent_id` = @dev_id, `sort` = 1, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 5320; -- 3.2 设备状态与日常操作(目录:设备借用 / 维修单) SET @dev_daily_dir = (SELECT id FROM `system_menu` WHERE deleted = b'0' AND type = 1 AND parent_id = @dev_id AND name = '设备状态与日常操作' ORDER BY id LIMIT 1); INSERT INTO `system_menu` (`name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT '设备状态与日常操作', '', 1, 2, @dev_id, 'daily', 'ep:switch', NULL, NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE @dev_daily_dir IS NULL; SET @dev_daily_dir = (SELECT id FROM `system_menu` WHERE deleted = b'0' AND type = 1 AND parent_id = @dev_id AND name = '设备状态与日常操作' ORDER BY id LIMIT 1); UPDATE `system_menu` SET `name` = '设备借用', `parent_id` = @dev_daily_dir, `sort` = 1, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 1075514; UPDATE `system_menu` SET `name` = '维修单', `parent_id` = @dev_daily_dir, `sort` = 2, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 5370; -- 3.3 设备维护与检修管理(目录:点检保养项目 / 方案 / 点检记录 / 保养记录) SET @dev_maint_dir = (SELECT id FROM `system_menu` WHERE deleted = b'0' AND type = 1 AND parent_id = @dev_id AND name = '设备维护与检修管理' ORDER BY id LIMIT 1); INSERT INTO `system_menu` (`name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT '设备维护与检修管理', '', 1, 3, @dev_id, 'maintenance', 'ep:tools', NULL, NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE @dev_maint_dir IS NULL; SET @dev_maint_dir = (SELECT id FROM `system_menu` WHERE deleted = b'0' AND type = 1 AND parent_id = @dev_id AND name = '设备维护与检修管理' ORDER BY id LIMIT 1); UPDATE `system_menu` SET `name` = '点检保养项目', `parent_id` = @dev_maint_dir, `sort` = 1, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 5330; UPDATE `system_menu` SET `name` = '点检保养方案', `parent_id` = @dev_maint_dir, `sort` = 2, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 5350; UPDATE `system_menu` SET `name` = '点检记录', `parent_id` = @dev_maint_dir, `sort` = 3, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 5360; UPDATE `system_menu` SET `name` = '保养记录', `parent_id` = @dev_maint_dir, `sort` = 4, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 5340; -- 3.4 数据工具与批量操作(设备统计分析) UPDATE `system_menu` SET `name` = '数据工具与批量操作', `parent_id` = @dev_id, `sort` = 4, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 1075521; -- ==================== 5. 采购管理 ==================== -- 4.1 供应商管理(目录:供应商档案 / 合同管理) UPDATE `system_menu` SET `name` = '供应商管理', `parent_id` = @pur_id, `sort` = 1, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 602524; UPDATE `system_menu` SET `name` = '供应商档案', `parent_id` = 602524, `sort` = 1, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 602525; UPDATE `system_menu` SET `name` = '合同管理', `parent_id` = 602524, `sort` = 2, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 1075523; -- 4.2 采购下单 UPDATE `system_menu` SET `name` = '采购下单', `parent_id` = @pur_id, `sort` = 2, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 1075529; -- 4.3 采购申请单(目录:申请单列表 / 采购申请统计) SET @req_dir = (SELECT id FROM `system_menu` WHERE deleted = b'0' AND type = 1 AND parent_id = @pur_id AND name = '采购申请单' ORDER BY id LIMIT 1); INSERT INTO `system_menu` (`name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT '采购申请单', '', 1, 3, @pur_id, 'request-bill', 'ep:document-add', NULL, NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE @req_dir IS NULL; SET @req_dir = (SELECT id FROM `system_menu` WHERE deleted = b'0' AND type = 1 AND parent_id = @pur_id AND name = '采购申请单' ORDER BY id LIMIT 1); UPDATE `system_menu` SET `name` = '申请单列表', `parent_id` = @req_dir, `sort` = 1, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 602498; UPDATE `system_menu` SET `name` = '采购申请统计', `parent_id` = @req_dir, `sort` = 2, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 1075536; -- 4.4 申请采购单 UPDATE `system_menu` SET `name` = '申请采购单', `parent_id` = @pur_id, `sort` = 4, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 1075530; -- 4.5 采购订单 UPDATE `system_menu` SET `name` = '采购订单', `parent_id` = @pur_id, `sort` = 5, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 2666; -- ==================== 6. 仓储物流 ==================== -- 5.1 仓库管理(目录) SET @wh_dir = (SELECT id FROM `system_menu` WHERE deleted = b'0' AND type = 1 AND parent_id = @wms_id AND name = '仓库管理' ORDER BY id LIMIT 1); INSERT INTO `system_menu` (`name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT '仓库管理', '', 1, 1, @wms_id, 'warehouse-manage', 'ep:house', NULL, NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE @wh_dir IS NULL; SET @wh_dir = (SELECT id FROM `system_menu` WHERE deleted = b'0' AND type = 1 AND parent_id = @wms_id AND name = '仓库管理' ORDER BY id LIMIT 1); UPDATE `system_menu` SET `name` = '仓库设置', `parent_id` = @wh_dir, `sort` = 1, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 5781; UPDATE `system_menu` SET `name` = '库存现有量', `parent_id` = @wh_dir, `sort` = 2, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 5786; UPDATE `system_menu` SET `name` = '库存预警', `parent_id` = @wh_dir, `sort` = 3, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 1075538; UPDATE `system_menu` SET `name` = '转移调拨', `parent_id` = @wh_dir, `sort` = 4, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 5922; UPDATE `system_menu` SET `name` = '盘点方案', `parent_id` = @wh_dir, `sort` = 5, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 5951; UPDATE `system_menu` SET `name` = '盘点任务', `parent_id` = @wh_dir, `sort` = 6, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 5957; UPDATE `system_menu` SET `name` = '库存事务流水', `parent_id` = @wh_dir, `sort` = 7, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 602476; -- 5.2 出入库管理(目录) SET @io_dir = (SELECT id FROM `system_menu` WHERE deleted = b'0' AND type = 1 AND parent_id = @wms_id AND name = '出入库管理' ORDER BY id LIMIT 1); INSERT INTO `system_menu` (`name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT '出入库管理', '', 1, 2, @wms_id, 'in-out', 'ep:sort', NULL, NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE @io_dir IS NULL; SET @io_dir = (SELECT id FROM `system_menu` WHERE deleted = b'0' AND type = 1 AND parent_id = @wms_id AND name = '出入库管理' ORDER BY id LIMIT 1); UPDATE `system_menu` SET `name` = '采购入库', `parent_id` = @io_dir, `sort` = 1, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 5798; UPDATE `system_menu` SET `name` = '产品入库', `parent_id` = @io_dir, `sort` = 2, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 5819; UPDATE `system_menu` SET `name` = '发货通知', `parent_id` = @io_dir, `sort` = 3, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 5867; UPDATE `system_menu` SET `name` = '销售出库', `parent_id` = @io_dir, `sort` = 4, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 5970; -- 5.3 分拣配送 UPDATE `system_menu` SET `name` = '分拣配送', `parent_id` = @wms_id, `sort` = 3, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 1075543; -- ==================== 7. 营销管理 ==================== -- 6.1 开单 + 6.2 订单管理:同一张销售订单(「新增」即代客/窗口开单),合并为「订单管理」一个菜单,不重复建菜单。 UPDATE `system_menu` SET `name` = '订单管理', `parent_id` = @sale_id, `sort` = 1, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 2638; -- 6.3 退货管理 UPDATE `system_menu` SET `name` = '退货管理', `parent_id` = @sale_id, `sort` = 2, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 5847; -- 6.4 报价单 UPDATE `system_menu` SET `name` = '报价单', `parent_id` = @sale_id, `sort` = 3, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 602512; -- 6.5 预订货管理 UPDATE `system_menu` SET `name` = '预订货管理', `parent_id` = @sale_id, `sort` = 4, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 1075568; -- 6.6 客户管理 UPDATE `system_menu` SET `name` = '客户管理', `parent_id` = @sale_id, `sort` = 5, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 2391; -- 6.7 销售统计(目录) UPDATE `system_menu` SET `name` = '销售统计', `parent_id` = @sale_id, `sort` = 6, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 2560; UPDATE `system_menu` SET `name` = '销售款统计', `parent_id` = 2560, `sort` = 6, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 1075574; -- ==================== 8. 人力资源 ==================== UPDATE `system_menu` SET `name` = '合同管理', `parent_id` = @hrm_id, `sort` = 1, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 1063105; UPDATE `system_menu` SET `name` = '资产管理', `parent_id` = @hrm_id, `sort` = 2, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 1075548; UPDATE `system_menu` SET `name` = '员工管理', `parent_id` = @hrm_id, `sort` = 3, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 6006; UPDATE `system_menu` SET `name` = '办公用品管理', `parent_id` = @hrm_id, `sort` = 4, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 1075557; -- 7.5 组织机构管理(目录:部门管理 / 岗位管理) SET @org_dir = (SELECT id FROM `system_menu` WHERE deleted = b'0' AND type = 1 AND parent_id = @hrm_id AND name = '组织机构管理' ORDER BY id LIMIT 1); INSERT INTO `system_menu` (`name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT '组织机构管理', '', 1, 5, @hrm_id, 'organization', 'ep:office-building', NULL, NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE @org_dir IS NULL; SET @org_dir = (SELECT id FROM `system_menu` WHERE deleted = b'0' AND type = 1 AND parent_id = @hrm_id AND name = '组织机构管理' ORDER BY id LIMIT 1); UPDATE `system_menu` SET `name` = '部门管理', `parent_id` = @org_dir, `sort` = 1, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 103; UPDATE `system_menu` SET `name` = '岗位管理', `parent_id` = @org_dir, `sort` = 2, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 104; -- 7.6 账号管理 UPDATE `system_menu` SET `name` = '账号管理', `parent_id` = @hrm_id, `sort` = 6, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 100; -- 7.7 人事审批管理 UPDATE `system_menu` SET `name` = '人事审批管理', `parent_id` = @hrm_id, `sort` = 7, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 6002; -- 7.8 考勤管理 UPDATE `system_menu` SET `name` = '考勤管理', `parent_id` = @hrm_id, `sort` = 8, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 6010; -- 7.9 薪资管理 UPDATE `system_menu` SET `name` = '薪资管理', `parent_id` = @hrm_id, `sort` = 9, `status` = 0, `visible` = b'1', `deleted` = b'0', `updater` = '1', `update_time` = NOW() WHERE `id` = 6020; -- ==================== 9. 为新菜单补角色映射(按同级已有菜单复制) ==================== -- 领料管理目录:复制「领料出库」的角色 INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT DISTINCT rm.`role_id`, @issue_dir, '1', NOW(), '1', NOW(), b'0' FROM `system_role_menu` rm WHERE rm.`menu_id` = 5805 AND NOT EXISTS (SELECT 1 FROM `system_role_menu` x WHERE x.`role_id` = rm.`role_id` AND x.`menu_id` = @issue_dir); -- 采购申请单目录:复制「采购申请」的角色 INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT DISTINCT rm.`role_id`, @req_dir, '1', NOW(), '1', NOW(), b'0' FROM `system_role_menu` rm WHERE rm.`menu_id` = 602498 AND NOT EXISTS (SELECT 1 FROM `system_role_menu` x WHERE x.`role_id` = rm.`role_id` AND x.`menu_id` = @req_dir); -- 组织机构管理目录:复制「部门管理」的角色 INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT DISTINCT rm.`role_id`, @org_dir, '1', NOW(), '1', NOW(), b'0' FROM `system_role_menu` rm WHERE rm.`menu_id` = 103 AND NOT EXISTS (SELECT 1 FROM `system_role_menu` x WHERE x.`role_id` = rm.`role_id` AND x.`menu_id` = @org_dir); -- 设备状态与日常操作目录:复制「设备基础信息管理」的角色 INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT DISTINCT rm.`role_id`, @dev_daily_dir, '1', NOW(), '1', NOW(), b'0' FROM `system_role_menu` rm WHERE rm.`menu_id` = 5320 AND NOT EXISTS (SELECT 1 FROM `system_role_menu` x WHERE x.`role_id` = rm.`role_id` AND x.`menu_id` = @dev_daily_dir); -- 设备维护与检修管理目录:复制「设备基础信息管理」的角色 INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT DISTINCT rm.`role_id`, @dev_maint_dir, '1', NOW(), '1', NOW(), b'0' FROM `system_role_menu` rm WHERE rm.`menu_id` = 5320 AND NOT EXISTS (SELECT 1 FROM `system_role_menu` x WHERE x.`role_id` = rm.`role_id` AND x.`menu_id` = @dev_maint_dir); -- 仓库管理目录:复制「仓库设置」的角色 INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT DISTINCT rm.`role_id`, @wh_dir, '1', NOW(), '1', NOW(), b'0' FROM `system_role_menu` rm WHERE rm.`menu_id` = 5781 AND NOT EXISTS (SELECT 1 FROM `system_role_menu` x WHERE x.`role_id` = rm.`role_id` AND x.`menu_id` = @wh_dir); -- 出入库管理目录:复制「采购入库」的角色 INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT DISTINCT rm.`role_id`, @io_dir, '1', NOW(), '1', NOW(), b'0' FROM `system_role_menu` rm WHERE rm.`menu_id` = 5798 AND NOT EXISTS (SELECT 1 FROM `system_role_menu` x WHERE x.`role_id` = rm.`role_id` AND x.`menu_id` = @io_dir); -- ==================== 10. 隐藏方案未涉及的页面 ==================== DROP TEMPORARY TABLE IF EXISTS `tmp_keep_menu`; CREATE TEMPORARY TABLE `tmp_keep_menu` (`id` bigint NOT NULL PRIMARY KEY); INSERT INTO `tmp_keep_menu` (`id`) VALUES -- 计划排程 (@plan_id), (602504), -- 生产管控 (@prod_id), (5530), (5550), (@issue_dir), (5805), (5840), -- 设备管理 (@dev_id), (5320), (@dev_daily_dir), (1075514), (5370), (@dev_maint_dir), (5330), (5350), (5360), (5340), (1075521), -- 采购管理(采购下单已并入采购订单页签、申请采购单已并入采购申请单页签,均隐藏) (@pur_id), (602524), (602525), (1075523), (@req_dir), (602498), (1075536), (2666), -- 仓储物流 (@wms_id), (@wh_dir), (5781), (5786), (1075538), (5922), (5951), (5957), (602476), (@io_dir), (5798), (5819), (5867), (5970), (1075543), -- 营销管理 (@sale_id), (2638), (5847), (602512), (1075568), (2391), (2560), (2712), (2561), (2736), (2737), (2738), (1075574), -- 人力资源 (@hrm_id), (1063105), (1075548), (6006), (1075557), (@org_dir), (103), (104), (100), (6002), (6003), (6004), (6005), (1065211), (6010), (6013), (6011), (6012), (6014), (6015), (6016), (6017), (6020), (6021), (6022), (6023), (602445), (602449), (6024); -- 计算 7 个业务模块下的全部目录/菜单 DROP TEMPORARY TABLE IF EXISTS `tmp_business_menu`; CREATE TEMPORARY TABLE `tmp_business_menu` (`id` bigint NOT NULL PRIMARY KEY); INSERT INTO `tmp_business_menu` (`id`) WITH RECURSIVE tree AS ( SELECT `id` FROM `system_menu` WHERE `deleted` = b'0' AND `parent_id` = 0 AND `id` IN (@plan_id, @prod_id, @dev_id, @pur_id, @wms_id, @sale_id, @hrm_id) UNION ALL SELECT m.`id` FROM `system_menu` m JOIN tree t ON m.`parent_id` = t.`id` WHERE m.`deleted` = b'0' ) SELECT `id` FROM tree; -- 保留项可见 UPDATE `system_menu` SET `visible` = b'1', `status` = 0 WHERE `deleted` = b'0' AND `type` IN (1, 2) AND `id` IN (SELECT `id` FROM `tmp_keep_menu`); -- 业务模块内方案未涉及的页面隐藏 UPDATE `system_menu` SET `visible` = b'0' WHERE `deleted` = b'0' AND `type` IN (1, 2) AND `id` IN (SELECT `id` FROM `tmp_business_menu`) AND `id` NOT IN (SELECT `id` FROM `tmp_keep_menu`); DROP TEMPORARY TABLE IF EXISTS `tmp_keep_menu`; DROP TEMPORARY TABLE IF EXISTS `tmp_business_menu`; COMMIT; -- ==================== 验证 ==================== -- 1) 一级菜单 SELECT `id`, `name`, `sort`, `visible` FROM `system_menu` WHERE `deleted` = b'0' AND `parent_id` = 0 AND `type` = 1 ORDER BY `sort`; -- 2) 业务模块二级菜单 SELECT p.`name` AS module, m.`id`, m.`name`, m.`type`, m.`sort`, m.`visible` FROM `system_menu` m JOIN `system_menu` p ON p.`id` = m.`parent_id` WHERE m.`deleted` = b'0' AND m.`type` IN (1, 2) AND p.`parent_id` = 0 AND p.`id` IN (@plan_id, @prod_id, @dev_id, @pur_id, @wms_id, @sale_id, @hrm_id) ORDER BY p.`sort`, m.`sort`;