-- ============================================================
|
-- MES 注塑机数采对接
|
-- 数据库：mom-kyzn
|
-- 日期：2026-10-20
|
-- 说明：
|
-- 1. 设备台账增加数采设备编号/启用标记
|
-- 2. 新建实时、周期、工艺、参数变更、报警、原始报文表
|
-- 3. 新增数采监控菜单与权限
|
-- 脚本可重复执行。
|
-- ============================================================
|
|
-- ---------- 1. 设备台账扩展 ----------
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_dv_machinery' AND column_name = 'collect_device_code');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_dv_machinery ADD COLUMN collect_device_code VARCHAR(64) NULL COMMENT ''数采设备编号（MQTT devId，如 C01）'' AFTER workstation_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_dv_machinery' AND column_name = 'collect_enabled');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_dv_machinery ADD COLUMN collect_enabled BIT(1) NOT NULL DEFAULT b''0'' COMMENT ''是否启用数采'' AFTER collect_device_code',
|
'SELECT 1');
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.statistics
|
WHERE table_schema = DATABASE() AND table_name = 'mes_dv_machinery' AND index_name = 'uk_collect_device_code');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_dv_machinery ADD UNIQUE INDEX uk_collect_device_code (collect_device_code)',
|
'SELECT 1');
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- ---------- 2. 数采表 ----------
|
CREATE TABLE IF NOT EXISTS mes_collect_realtime (
|
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '编号',
|
machinery_id BIGINT DEFAULT NULL COMMENT '设备台账编号',
|
device_code VARCHAR(64) NOT NULL COMMENT '数采设备编号',
|
task_id BIGINT DEFAULT NULL COMMENT '生产任务编号',
|
work_order_id BIGINT DEFAULT NULL COMMENT '生产工单编号',
|
collect_time DATETIME(3) NOT NULL COMMENT '采集时间',
|
sts INT DEFAULT NULL COMMENT '运行状态：1待机 2生产',
|
opm INT DEFAULT NULL COMMENT '运行模式：0手动 1半自动 2电眼自动 3时间自动 4调模使用',
|
asts INT DEFAULT NULL COMMENT '附加状态',
|
ot DECIMAL(10,2) DEFAULT NULL COMMENT '油温',
|
temps JSON DEFAULT NULL COMMENT '各段温度 T1..Tn',
|
data_json JSON 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_collect_realtime_device (device_code),
|
KEY idx_collect_realtime_machinery (machinery_id),
|
KEY idx_collect_realtime_work_order (work_order_id)
|
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'MES 数采-设备实时状态';
|
|
CREATE TABLE IF NOT EXISTS mes_collect_realtime_history (
|
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '编号',
|
machinery_id BIGINT DEFAULT NULL COMMENT '设备台账编号',
|
device_code VARCHAR(64) NOT NULL COMMENT '数采设备编号',
|
task_id BIGINT DEFAULT NULL COMMENT '生产任务编号',
|
work_order_id BIGINT DEFAULT NULL COMMENT '生产工单编号',
|
collect_time DATETIME(3) NOT NULL COMMENT '采集时间',
|
sts INT DEFAULT NULL COMMENT '运行状态：1待机 2生产',
|
opm INT DEFAULT NULL COMMENT '运行模式：0手动 1半自动 2电眼自动 3时间自动 4调模使用',
|
asts INT DEFAULT NULL COMMENT '附加状态',
|
ot DECIMAL(10,2) DEFAULT NULL COMMENT '油温',
|
temps JSON DEFAULT NULL COMMENT '各段温度 T1..Tn',
|
data_json JSON 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_collect_realtime_history (device_code, collect_time),
|
KEY idx_collect_realtime_history_device_time (device_code, collect_time),
|
KEY idx_collect_realtime_history_work_order (work_order_id, collect_time),
|
KEY idx_collect_realtime_history_collect_time (collect_time)
|
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'MES 数采-实时状态历史';
|
|
CREATE TABLE IF NOT EXISTS mes_collect_cycle (
|
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '编号',
|
machinery_id BIGINT DEFAULT NULL COMMENT '设备台账编号',
|
device_code VARCHAR(64) NOT NULL COMMENT '数采设备编号',
|
task_id BIGINT DEFAULT NULL COMMENT '生产任务编号',
|
work_order_id BIGINT DEFAULT NULL COMMENT '生产工单编号',
|
cycle_no BIGINT NOT NULL DEFAULT 0 COMMENT '周期序号（CYCN）',
|
collect_time DATETIME(3) NOT NULL COMMENT '采集时间',
|
ecyct DECIMAL(14,4) DEFAULT NULL COMMENT '周期时间（ECYCT）',
|
eiss DECIMAL(14,4) DEFAULT NULL COMMENT '射出起点（EISS）',
|
eivm DECIMAL(14,4) DEFAULT NULL COMMENT '最大射速（EIVM）',
|
eipm DECIMAL(14,4) DEFAULT NULL COMMENT '最大射压（EIPM）',
|
esipt DECIMAL(14,4) DEFAULT NULL COMMENT '转保压时间（ESIPT）',
|
esipp DECIMAL(14,4) DEFAULT NULL COMMENT '转保压压力（ESIPP）',
|
esips DECIMAL(14,4) DEFAULT NULL COMMENT '转保压位置（ESIPS）',
|
eipt DECIMAL(14,4) DEFAULT NULL COMMENT '射出时间（EIPT）',
|
eipse DECIMAL(14,4) DEFAULT NULL COMMENT '射出终点位置（EIPSE）',
|
eplst DECIMAL(14,4) DEFAULT NULL COMMENT '储料时间（EPLST）',
|
eplsse DECIMAL(14,4) DEFAULT NULL COMMENT '储料终点（EPLSSE）',
|
eplspm DECIMAL(14,4) DEFAULT NULL COMMENT '最大储料压力（EPLSPM）',
|
et_temps JSON DEFAULT NULL COMMENT '生产温度 ET1..ETn',
|
data_json JSON 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_collect_cycle (device_code, cycle_no, collect_time),
|
KEY idx_collect_cycle_device_time (device_code, collect_time),
|
KEY idx_collect_cycle_work_order (work_order_id, collect_time)
|
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'MES 数采-周期过程数据';
|
|
CREATE TABLE IF NOT EXISTS mes_collect_tech (
|
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '编号',
|
machinery_id BIGINT DEFAULT NULL COMMENT '设备台账编号',
|
device_code VARCHAR(64) NOT NULL COMMENT '数采设备编号',
|
task_id BIGINT DEFAULT NULL COMMENT '生产任务编号',
|
work_order_id BIGINT DEFAULT NULL COMMENT '生产工单编号',
|
collect_time DATETIME(3) NOT NULL COMMENT '采集时间',
|
data_json JSON 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_collect_tech_device (device_code),
|
KEY idx_collect_tech_work_order (work_order_id)
|
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'MES 数采-工艺参数快照';
|
|
CREATE TABLE IF NOT EXISTS mes_collect_operate_log (
|
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '编号',
|
machinery_id BIGINT DEFAULT NULL COMMENT '设备台账编号',
|
device_code VARCHAR(64) NOT NULL COMMENT '数采设备编号',
|
task_id BIGINT DEFAULT NULL COMMENT '生产任务编号',
|
work_order_id BIGINT DEFAULT NULL COMMENT '生产工单编号',
|
var_id VARCHAR(64) DEFAULT NULL COMMENT '变量编码',
|
old_value VARCHAR(512) DEFAULT NULL COMMENT '修改前的值',
|
new_value VARCHAR(512) DEFAULT NULL COMMENT '修改后的值',
|
modify_time DATETIME(3) DEFAULT NULL COMMENT '参数修改时间',
|
collect_time DATETIME(3) NOT NULL COMMENT '报文采集时间',
|
data_json JSON 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_collect_operate_log_device_time (device_code, modify_time),
|
KEY idx_collect_operate_log_work_order (work_order_id, modify_time),
|
KEY idx_collect_operate_log_var (var_id)
|
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'MES 数采-参数变更日志';
|
|
CREATE TABLE IF NOT EXISTS mes_collect_alarm (
|
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '编号',
|
machinery_id BIGINT DEFAULT NULL COMMENT '设备台账编号',
|
device_code VARCHAR(64) NOT NULL COMMENT '数采设备编号',
|
task_id BIGINT DEFAULT NULL COMMENT '生产任务编号',
|
work_order_id BIGINT DEFAULT NULL COMMENT '生产工单编号',
|
wm_id INT DEFAULT NULL COMMENT '报警标识：2产生 0消失',
|
wm_msg VARCHAR(512) DEFAULT NULL COMMENT '报警内容',
|
wm_time DATETIME(3) DEFAULT NULL COMMENT '报警时间',
|
status TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0未消除 1已消除',
|
clear_time DATETIME(3) DEFAULT NULL COMMENT '消除时间',
|
collect_time DATETIME(3) NOT NULL COMMENT '报文采集时间',
|
data_json JSON 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_collect_alarm_device_status (device_code, status),
|
KEY idx_collect_alarm_time (wm_time),
|
KEY idx_collect_alarm_work_order (work_order_id)
|
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'MES 数采-报警记录';
|
|
CREATE TABLE IF NOT EXISTS mes_collect_message (
|
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '编号',
|
machinery_id BIGINT DEFAULT NULL COMMENT '设备台账编号',
|
device_code VARCHAR(64) DEFAULT NULL COMMENT '数采设备编号',
|
mqtt_topic VARCHAR(255) DEFAULT NULL COMMENT 'MQTT 主题',
|
msg_type VARCHAR(32) DEFAULT NULL COMMENT '数据类型：realtime/spc/tech/opLog/alarm/unknown',
|
event_time DATETIME(3) DEFAULT NULL COMMENT '设备数据时间',
|
raw_payload LONGTEXT COMMENT '原始报文',
|
process_status TINYINT NOT NULL DEFAULT 0 COMMENT '处理状态：0成功 1失败',
|
error_msg VARCHAR(1024) DEFAULT NULL COMMENT '失败原因',
|
receive_time DATETIME(3) NOT 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_collect_message_device_time (device_code, receive_time),
|
KEY idx_collect_message_type_time (msg_type, receive_time),
|
KEY idx_collect_message_receive_time (receive_time)
|
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'MES 数采-原始报文';
|
|
-- ---------- 3. 菜单与权限 ----------
|
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 (5392, '数采监控', 'mes:collect:query', 2, 9, 5300, 'collect/monitor', 'ep:monitor',
|
'mes/collect/monitor/index', 'MesCollectMonitor', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0')
|
ON DUPLICATE KEY UPDATE name = VALUES(name), permission = VALUES(permission), parent_id = VALUES(parent_id),
|
path = VALUES(path), component = VALUES(component), component_name = VALUES(component_name),
|
status = VALUES(status), visible = VALUES(visible), deleted = b'0';
|
|
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 (5393, '报警确认', 'mes:collect-alarm:ack', 3, 1, 5392, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0')
|
ON DUPLICATE KEY UPDATE name = VALUES(name), permission = VALUES(permission), parent_id = VALUES(parent_id),
|
status = VALUES(status), deleted = b'0';
|
|
INSERT IGNORE INTO system_role_menu (role_id, menu_id, creator, create_time, updater, update_time, deleted)
|
VALUES (1, 5392, '1', NOW(), '1', NOW(), b'0'),
|
(1, 5393, '1', NOW(), '1', NOW(), b'0');
|
