-- ============================================================================
|
-- 需求：基础参数(工艺参数)可与工序绑定，绑定后报工需填写参数字段
|
-- 一键整合脚本：可直接手动执行（含幂等保护，重复执行不报错）
|
--
|
-- 第一部分：功能迁移（工序-参数绑定）
|
-- 第二部分：活库漂移修复（代码已引用但库缺失的列/表，需补齐才能建任务/报工）
|
-- ============================================================================
|
|
-- ============================================================================
|
-- 第一部分：功能迁移
|
-- ============================================================================
|
|
-- ---------- 1.1 工序/任务/工单工序 增加 param_template_id（参数模板关联，功能链路前置） ----------
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_pro_task' AND column_name = 'param_template_id');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_pro_task ADD COLUMN param_template_id bigint DEFAULT NULL COMMENT ''工艺参数模板编号'' AFTER process_id',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_pro_route_process' AND column_name = 'param_template_id');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_pro_route_process ADD COLUMN param_template_id bigint DEFAULT NULL COMMENT ''工艺参数模板编号'' AFTER process_id',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order_process' AND column_name = 'param_template_id');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_pro_work_order_process ADD COLUMN param_template_id bigint DEFAULT NULL COMMENT ''工艺参数模板编号'' AFTER process_id',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- ---------- 1.2 新建 工序-工艺参数绑定表 ----------
|
CREATE TABLE IF NOT EXISTS `mes_pro_process_param` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`process_id` bigint NOT NULL COMMENT '工序编号（关联 mes_pro_process）',
|
`process_param_id` bigint NOT NULL COMMENT '工艺参数编号（关联 mes_pd_process_param）',
|
`required` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否必填',
|
`sort` int NULL DEFAULT NULL COMMENT '排序',
|
`remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '备注',
|
`creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL 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`) USING BTREE,
|
INDEX `idx_process_id`(`process_id` ASC) USING BTREE,
|
INDEX `idx_process_param_id`(`process_param_id` ASC) USING BTREE
|
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'MES 工序-工艺参数绑定' ROW_FORMAT = DYNAMIC;
|
|
-- ---------- 1.3 参数记录主表模板字段允许为空（工序绑定参数生成时无模板） ----------
|
ALTER TABLE mes_pro_work_order_param_record MODIFY COLUMN template_id bigint NULL DEFAULT NULL COMMENT '参数模板编号（工序绑定参数生成时为空）';
|
ALTER TABLE mes_pro_work_order_param_record MODIFY COLUMN template_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '模板名称（快照）';
|
|
-- ---------- 1.4 参数记录明细增加 required 快照（报工必填校验用） ----------
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order_param_record_detail' AND column_name = 'required');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_pro_work_order_param_record_detail ADD COLUMN required bit(1) NOT NULL DEFAULT b''0'' COMMENT ''是否必填（快照）'' AFTER param_type',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- ============================================================================
|
-- 第二部分：活库漂移修复（代码已引用但库缺失；若你的库已有这些列/表可忽略）
|
-- ============================================================================
|
|
-- ---------- 2.1 mes_pro_work_order 缺失列（报工审批 BPM 相关） ----------
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order' AND column_name = 'is_feedback_approve');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_pro_work_order ADD COLUMN is_feedback_approve tinyint(1) DEFAULT NULL COMMENT ''是否开启报工审批 (1: 开启, 0/NULL: 关闭)'' AFTER deleted',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order' AND column_name = 'process_definition_key');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_pro_work_order ADD COLUMN process_definition_key varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ''BPM 流程定义 Key'' AFTER is_feedback_approve',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order' AND column_name = 'feedback_role');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_pro_work_order ADD COLUMN feedback_role varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ''报工角色 (多个角色以逗号分隔)'' AFTER process_definition_key',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- ---------- 2.2 mes_pro_feedback 缺失列（报工 BPM 相关） ----------
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_pro_feedback' AND column_name = 'process_instance_id');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_pro_feedback ADD COLUMN process_instance_id varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ''BPM 流程实例编号'' AFTER remark',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_pro_feedback' AND column_name = 'process_definition_key');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_pro_feedback ADD COLUMN process_definition_key varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ''BPM 流程定义 Key'' AFTER process_instance_id',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- ---------- 2.3 缺失表：任务指定报工人员、任务停工记录（此前迁移未应用） ----------
|
CREATE TABLE IF NOT EXISTS `mes_pro_task_assignee` (
|
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`task_id` bigint(20) NOT NULL COMMENT '生产任务 ID',
|
`user_id` bigint(20) NOT NULL COMMENT '系统用户 ID (指定报工人员)',
|
`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_task_id` (`task_id`),
|
KEY `idx_user_id` (`user_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='生产任务指定报工人员表';
|
|
CREATE TABLE IF NOT EXISTS `mes_pro_task_pause_record` (
|
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`task_id` bigint(20) NOT NULL COMMENT '任务编号',
|
`work_order_id` bigint(20) DEFAULT NULL COMMENT '工单编号',
|
`workstation_id` bigint(20) DEFAULT NULL COMMENT '工作站编号',
|
`reason` varchar(255) NOT NULL COMMENT '停工原因',
|
`pause_time` datetime NOT NULL COMMENT '停工时间',
|
`pause_user_id` bigint(20) NOT NULL COMMENT '停工操作人ID',
|
`resume_time` datetime DEFAULT NULL COMMENT '复工时间',
|
`resume_user_id` bigint(20) DEFAULT NULL COMMENT '复工操作人ID',
|
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
`creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 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`) USING BTREE,
|
KEY `idx_task_id` (`task_id`) USING BTREE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='生产任务停工记录表';
|
|
-- ============================================================================
|
-- 执行完成。可执行以下语句核对：
|
-- SHOW TABLES LIKE 'mes_pro_process_param';
|
-- SHOW COLUMNS FROM mes_pro_work_order_param_record_detail LIKE 'required';
|
-- ============================================================================
|
