-- ============================================================ -- 电力数转增强 - 第四阶段(计划排程:电力作业计划)数据库变更 -- 适用:MySQL 8.4+ -- 说明:电力作业计划新表 + 计划类型/状态字典(幂等) -- 注意:本脚本不包含 tenant_id(项目未启用多租户) -- ============================================================ -- ---------- 1. 电力作业计划主表 ---------- CREATE TABLE IF NOT EXISTS `mes_pro_maintenance_plan` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `code` varchar(64) NOT NULL COMMENT '计划编码', `name` varchar(200) DEFAULT NULL COMMENT '计划名称', `plan_type` tinyint DEFAULT NULL COMMENT '计划类型(1=电网运行,2=发电,3=变电运维,4=线路检修)', `station` varchar(200) DEFAULT NULL COMMENT '站点', `line` varchar(200) DEFAULT NULL COMMENT '线路', `team_id` bigint DEFAULT NULL COMMENT '班组编号', `team_name` varchar(64) DEFAULT NULL COMMENT '班组名称', `plan_date` datetime DEFAULT NULL COMMENT '计划日期', `status` tinyint NOT NULL DEFAULT '0' COMMENT '状态(0=草稿,10=已下发,20=已完成,30=已取消)', `remark` varchar(500) DEFAULT NULL COMMENT '备注', `creator` varchar(64) DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', PRIMARY KEY (`id`), UNIQUE KEY `uk_code` (`code`), KEY `idx_plan_type` (`plan_type`), KEY `idx_status` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='MES 电力作业计划表'; -- ---------- 2. 电力作业计划备件需求明细表 ---------- CREATE TABLE IF NOT EXISTS `mes_pro_maintenance_plan_item` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `plan_id` bigint NOT NULL COMMENT '电力作业计划编号', `item_id` bigint NOT NULL COMMENT '物料/备件编号', `quantity` decimal(24,6) NOT NULL COMMENT '需求数量', `remark` varchar(500) DEFAULT NULL COMMENT '备注', `creator` varchar(64) DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', PRIMARY KEY (`id`), KEY `idx_plan_id` (`plan_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='MES 电力作业计划备件需求明细表'; -- ---------- 3. 字典:电力作业计划类型 / 状态 ---------- INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `deleted_time`) VALUES ('MES 电力作业计划类型', 'mes_pro_maintenance_plan_type', 0, 'MES 电力作业计划类型(1=电网运行,2=发电,3=变电运维,4=线路检修)', '1', NOW(), '1', NOW(), b'0', NULL), ('MES 电力作业计划状态', 'mes_pro_maintenance_plan_status', 0, 'MES 电力作业计划状态(0=草稿,10=已下发,20=已完成,30=已取消)', '1', NOW(), '1', NOW(), b'0', NULL) ON DUPLICATE KEY UPDATE `name` = VALUES(`name`); INSERT INTO `system_dict_data` (`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (1, '电网运行', '1', 'mes_pro_maintenance_plan_type', 0, 'primary', '', '电网运行计划', '1', NOW(), '1', NOW(), b'0'), (2, '发电', '2', 'mes_pro_maintenance_plan_type', 0, 'success', '', '发电计划', '1', NOW(), '1', NOW(), b'0'), (3, '变电运维', '3', 'mes_pro_maintenance_plan_type', 0, 'warning', '', '变电运维计划', '1', NOW(), '1', NOW(), b'0'), (4, '线路检修', '4', 'mes_pro_maintenance_plan_type', 0, 'info', '', '线路检修计划', '1', NOW(), '1', NOW(), b'0'), (0, '草稿', '0', 'mes_pro_maintenance_plan_status', 0, 'default', '', '草稿', '1', NOW(), '1', NOW(), b'0'), (1, '已下发', '10', 'mes_pro_maintenance_plan_status', 0, 'processing', '', '已下发执行', '1', NOW(), '1', NOW(), b'0'), (2, '已完成', '20', 'mes_pro_maintenance_plan_status', 0, 'success', '', '已完成', '1', NOW(), '1', NOW(), b'0'), (3, '已取消', '30', 'mes_pro_maintenance_plan_status', 0, 'warning', '', '已取消', '1', NOW(), '1', NOW(), b'0') ON DUPLICATE KEY UPDATE `label` = VALUES(`label`), `sort` = VALUES(`sort`); -- ---------- 4. 菜单:电力作业计划 + 按钮权限(父菜单 5700 生产管理) ---------- INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (1065220, '电力作业计划', '', 2, 3, 5700, 'maintenance-plan', 'ep:calendar', 'mes/pro/maintenance-plan/index', 'MesProMaintenancePlan', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0') ON DUPLICATE KEY UPDATE `name` = VALUES(`name`); INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (1065221, '电力作业计划查询', 'mes:pro-maintenance-plan:query', 3, 1, 1065220, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065222, '电力作业计划创建', 'mes:pro-maintenance-plan:create', 3, 2, 1065220, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065223, '电力作业计划更新', 'mes:pro-maintenance-plan:update', 3, 3, 1065220, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065224, '电力作业计划删除', 'mes:pro-maintenance-plan:delete', 3, 4, 1065220, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065225, '电力作业计划导出', 'mes:pro-maintenance-plan:export', 3, 5, 1065220, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065226, '电力作业计划生成采购计划', 'mes:pro-maintenance-plan:generate-purchase-plan', 3, 6, 1065220, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0') ON DUPLICATE KEY UPDATE `name` = VALUES(`name`); -- ---------- 5. 计划表:生成的工单引用 + 生成工单按钮权限 ---------- SET @col = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_maintenance_plan' AND column_name = 'work_order_id'); SET @sql = IF(@col = 0, 'ALTER TABLE mes_pro_maintenance_plan ADD COLUMN work_order_id BIGINT NULL COMMENT ''生成的工单编号'' AFTER status', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @col = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_maintenance_plan' AND column_name = 'work_order_code'); SET @sql = IF(@col = 0, 'ALTER TABLE mes_pro_maintenance_plan ADD COLUMN work_order_code VARCHAR(64) NULL COMMENT ''生成的工单编码'' AFTER work_order_id', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (1065227, '电力作业计划生成工单', 'mes:pro-maintenance-plan:generate-work-order', 3, 7, 1065220, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0') ON DUPLICATE KEY UPDATE `name` = VALUES(`name`); -- 角色授权(普通角色2 / 租户管理员109、111)全部新增菜单 INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `tenant_id`) SELECT r.role_id, m.menu_id, '1', NOW(), '1', NOW(), b'0', 1 FROM (SELECT 2 AS role_id UNION ALL SELECT 109 UNION ALL SELECT 111) r CROSS JOIN (SELECT 1065211 AS menu_id UNION ALL SELECT 1065212 UNION ALL SELECT 1065213 UNION ALL SELECT 1065214 UNION ALL SELECT 1065215 UNION ALL SELECT 1065216 UNION ALL SELECT 1065217 UNION ALL SELECT 1065218 UNION ALL SELECT 1065219 UNION ALL SELECT 1065220 UNION ALL SELECT 1065221 UNION ALL SELECT 1065222 UNION ALL SELECT 1065223 UNION ALL SELECT 1065224 UNION ALL SELECT 1065225 UNION ALL SELECT 1065226 UNION ALL SELECT 1065227) m LEFT JOIN `system_role_menu` existing ON existing.role_id = r.role_id AND existing.menu_id = m.menu_id AND existing.deleted = b'0' WHERE existing.id IS NULL;