-- ============================================================ -- 电力数转增强 - 第一阶段(生产管控)数据库变更 -- 适用:MySQL 8.4+ -- 说明:字段变更使用"先检查再 ALTER"模式,可重复执行(幂等) -- 注意:本脚本不包含 tenant_id(项目未启用多租户) -- ============================================================ -- ---------- 1. 生产工单表:关联营销订单 ---------- SET @col = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order' AND column_name = 'sale_order_id'); SET @sql = IF(@col = 0, 'ALTER TABLE mes_pro_work_order ADD COLUMN sale_order_id BIGINT NULL COMMENT ''关联营销订单(ERP销售订单)编号'' AFTER client_id', '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_work_order' AND column_name = 'sale_order_no'); SET @sql = IF(@col = 0, 'ALTER TABLE mes_pro_work_order ADD COLUMN sale_order_no VARCHAR(64) NULL COMMENT ''关联营销订单(ERP销售订单)单号'' AFTER sale_order_id', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- ---------- 2. 生产领料单表:单据类型(1=正常领料,2=紧急补料) ---------- SET @col = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_product_issue' AND column_name = 'issue_type'); SET @sql = IF(@col = 0, 'ALTER TABLE mes_wm_product_issue ADD COLUMN issue_type INT NULL COMMENT ''单据类型(1=正常领料,2=紧急补料)'' AFTER task_id', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- ---------- 3. 生产任务表:班组派工 ---------- SET @col = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_task' AND column_name = 'team_id'); SET @sql = IF(@col = 0, 'ALTER TABLE mes_pro_task ADD COLUMN team_id BIGINT NULL COMMENT ''班组编号'' AFTER process_id', '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_task' AND column_name = 'team_name'); SET @sql = IF(@col = 0, 'ALTER TABLE mes_pro_task ADD COLUMN team_name VARCHAR(64) NULL COMMENT ''班组名称'' AFTER team_id', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- ============================================================ -- 4. 数据字典 -- ============================================================ -- 4.1 工单来源类型:新增 运维计划(3) INSERT INTO `system_dict_data` (`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (3, '运维计划', '3', 'mes_pro_work_order_source_type', 0, 'primary', '', '', '1', NOW(), '1', NOW(), b'0') ON DUPLICATE KEY UPDATE `label` = VALUES(`label`), `sort` = VALUES(`sort`); -- 4.2 工单类型:新增 电力运维(4)/发电(5)/检修(6) INSERT INTO `system_dict_data` (`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (4, '电力运维', '4', 'mes_pro_work_order_type', 0, 'primary', '', '', '1', NOW(), '1', NOW(), b'0') ON DUPLICATE KEY UPDATE `label` = VALUES(`label`), `sort` = VALUES(`sort`); INSERT INTO `system_dict_data` (`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (5, '发电', '5', 'mes_pro_work_order_type', 0, 'warning', '', '', '1', NOW(), '1', NOW(), b'0') ON DUPLICATE KEY UPDATE `label` = VALUES(`label`), `sort` = VALUES(`sort`); INSERT INTO `system_dict_data` (`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (6, '检修', '6', 'mes_pro_work_order_type', 0, 'info', '', '', '1', NOW(), '1', NOW(), b'0') ON DUPLICATE KEY UPDATE `label` = VALUES(`label`), `sort` = VALUES(`sort`); -- 4.3 新增字典类型:MES 生产领料单据类型 INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `deleted_time`) VALUES ('MES 生产领料单据类型', 'mes_wm_product_issue_type', 0, 'MES 生产领料单据类型(1=正常领料,2=紧急补料)', '1', NOW(), '1', NOW(), b'0', NULL) ON DUPLICATE KEY UPDATE `name` = VALUES(`name`); -- 4.4 领料单据类型:1=正常领料,2=紧急补料 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_wm_product_issue_type', 0, 'primary', '', '正常生产领料', '1', NOW(), '1', NOW(), b'0') ON DUPLICATE KEY UPDATE `label` = VALUES(`label`), `sort` = VALUES(`sort`); INSERT INTO `system_dict_data` (`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (2, '紧急补料', '2', 'mes_wm_product_issue_type', 0, 'warning', '', '生产现场紧急补料', '1', NOW(), '1', NOW(), b'0') ON DUPLICATE KEY UPDATE `label` = VALUES(`label`), `sort` = VALUES(`sort`);