-- ============================================ -- MES 数据采集(需求6 鸡爪数采对接)初始化脚本 -- 可重复执行 -- ============================================ -- 1. 采集设备表 CREATE TABLE IF NOT EXISTS `mes_dc_device` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID', `code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '设备编号(设备端上报时使用)', `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '设备名称', `device_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'CHICKEN_FEET_COUNTER' COMMENT '设备类型', `model` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '设备型号', `serial_no` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '设备序列号', `location` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '安装位置', `status` tinyint NOT NULL DEFAULT 0 COMMENT '设备状态:0 启用 / 1 禁用', `token` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '采集令牌(设备端 X-Device-Token)', `work_order_id` bigint NULL DEFAULT NULL COMMENT '当前绑定生产工单ID', `work_order_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '当前绑定生产工单编号(冗余)', `bind_time` datetime NULL DEFAULT NULL COMMENT '绑定工单时间', `last_heartbeat_time` datetime NULL DEFAULT NULL COMMENT '最后心跳时间', `last_ingest_time` datetime NULL DEFAULT NULL COMMENT '最后数据上报时间', `firmware_version` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '固件版本', `device_ip` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '设备IP', `remark` varchar(500) 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, UNIQUE INDEX `uk_code` (`code`) USING BTREE, INDEX `idx_work_order_id` (`work_order_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'MES 数据采集设备' ROW_FORMAT = Dynamic; -- 2. 采集记录表 CREATE TABLE IF NOT EXISTS `mes_dc_record` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID', `device_id` bigint NOT NULL COMMENT '采集设备ID', `device_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '设备编号(冗余)', `device_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '设备名称(冗余)', `work_order_id` bigint NULL DEFAULT NULL COMMENT '采集时绑定的生产工单ID', `work_order_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '生产工单编号(冗余)', `product_id` bigint NULL DEFAULT NULL COMMENT '产品ID', `product_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '产品编码(冗余)', `product_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '产品名称(冗余)', `batch_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '生产批次号', `record_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '设备侧记录序号', `biz_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '设备侧唯一键(用于幂等)', `quantity` int NOT NULL COMMENT '本次采集数量', `total_quantity` int NULL DEFAULT NULL COMMENT '设备累计数量(设备只报累计值时用于计算差值)', `weight` decimal(12, 2) NULL DEFAULT NULL COMMENT '本次重量(g)', `total_weight` decimal(12, 2) NULL DEFAULT NULL COMMENT '设备累计重量(g)', `record_time` datetime NOT NULL COMMENT '设备采集时间', `receive_time` datetime NOT NULL COMMENT '服务器接收时间', `online` bit(1) NOT NULL DEFAULT b'1' COMMENT '是否在线实时上报(离线补报为 0)', `raw_data` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '原始上报报文', `status` tinyint NOT NULL DEFAULT 0 COMMENT '记录状态:0 正常 / 1 已更正 / 2 已作废', `original_quantity` int NULL DEFAULT NULL COMMENT '更正前的原始数量(首次更正时留痕)', `correct_reason` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '更正/作废原因', `remark` varchar(500) 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, UNIQUE INDEX `uk_device_biz` (`device_id`, `biz_id`) USING BTREE, INDEX `idx_device_time` (`device_id`, `record_time`) USING BTREE, INDEX `idx_work_order_id` (`work_order_id`) USING BTREE, INDEX `idx_record_time` (`record_time`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'MES 数据采集记录' ROW_FORMAT = Dynamic; -- 3. 字典类型 INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT 'MES 数据采集设备类型', 'mes_dc_device_type', 0, 'MES 数据采集设备类型', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_dict_type` WHERE `type` = 'mes_dc_device_type' AND `deleted` = b'0'); INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT 'MES 数据采集设备状态', 'mes_dc_device_status', 0, 'MES 数据采集设备状态', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_dict_type` WHERE `type` = 'mes_dc_device_status' AND `deleted` = b'0'); INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT 'MES 数据采集记录状态', 'mes_dc_record_status', 0, 'MES 数据采集记录状态', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_dict_type` WHERE `type` = 'mes_dc_record_status' AND `deleted` = b'0'); -- 4. 字典数据 INSERT INTO `system_dict_data` (`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT 1, '鸡爪计数称重设备', 'CHICKEN_FEET_COUNTER', 'mes_dc_device_type', 0, 'primary', '', '鸡爪自动计数/称重设备', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_dict_data` WHERE `dict_type` = 'mes_dc_device_type' AND `value` = 'CHICKEN_FEET_COUNTER' AND `deleted` = b'0'); INSERT INTO `system_dict_data` (`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT 1, '启用', '0', 'mes_dc_device_status', 0, 'success', '', '', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_dict_data` WHERE `dict_type` = 'mes_dc_device_status' AND `value` = '0' AND `deleted` = b'0'); INSERT INTO `system_dict_data` (`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT 2, '禁用', '1', 'mes_dc_device_status', 0, 'danger', '', '', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_dict_data` WHERE `dict_type` = 'mes_dc_device_status' AND `value` = '1' AND `deleted` = b'0'); INSERT INTO `system_dict_data` (`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT 1, '正常', '0', 'mes_dc_record_status', 0, 'success', '', '', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_dict_data` WHERE `dict_type` = 'mes_dc_record_status' AND `value` = '0' AND `deleted` = b'0'); INSERT INTO `system_dict_data` (`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT 2, '已更正', '1', 'mes_dc_record_status', 0, 'warning', '', '', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_dict_data` WHERE `dict_type` = 'mes_dc_record_status' AND `value` = '1' AND `deleted` = b'0'); INSERT INTO `system_dict_data` (`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT 3, '已作废', '2', 'mes_dc_record_status', 0, 'danger', '', '', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_dict_data` WHERE `dict_type` = 'mes_dc_record_status' AND `value` = '2' AND `deleted` = b'0'); -- 5. 菜单:生产管控 → 数据采集 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`) SELECT 1080085, '数据采集', '', 1, 60, 5100, 'dc', 'ep:data-line', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 1080085); -- 5.1 设备管理 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`) SELECT 1080086, '设备管理', '', 2, 1, 1080085, 'device', 'ep:monitor', 'mes/dc/device/index', 'MesDcDevice', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 1080086); 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`) SELECT 1080087, '设备查询', 'mes:dc-device:query', 3, 1, 1080086, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 1080087); 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`) SELECT 1080088, '设备新增', 'mes:dc-device:create', 3, 2, 1080086, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 1080088); 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`) SELECT 1080089, '设备修改', 'mes:dc-device:update', 3, 3, 1080086, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 1080089); 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`) SELECT 1080090, '设备删除', 'mes:dc-device:delete', 3, 4, 1080086, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 1080090); 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`) SELECT 1080091, '重置令牌', 'mes:dc-device:reset-token', 3, 5, 1080086, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 1080091); 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`) SELECT 1080092, '绑定工单', 'mes:dc-device:bind', 3, 6, 1080086, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 1080092); -- 5.2 实时看板 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`) SELECT 1080093, '实时看板', '', 2, 2, 1080085, 'dashboard', 'ep:data-board', 'mes/dc/dashboard/index', 'MesDcDashboard', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 1080093); 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`) SELECT 1080094, '看板查询', 'mes:dc-statistics:query', 3, 1, 1080093, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 1080094); -- 5.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`) SELECT 1080095, '采集记录', '', 2, 3, 1080085, 'record', 'ep:list', 'mes/dc/record/index', 'MesDcRecord', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 1080095); 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`) SELECT 1080096, '记录查询', 'mes:dc-record:query', 3, 1, 1080095, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 1080096); 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`) SELECT 1080097, '记录更正', 'mes:dc-record:correct', 3, 2, 1080095, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 1080097); 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`) SELECT 1080098, '记录作废', 'mes:dc-record:cancel', 3, 3, 1080095, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 1080098); 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`) SELECT 1080099, '记录导出', 'mes:dc-record:export', 3, 4, 1080095, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 1080099); -- 5.4 统计分析 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`) SELECT 1080100, '统计分析', '', 2, 4, 1080085, 'statistics', 'ep:trend-charts', 'mes/dc/statistics/index', 'MesDcStatistics', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 1080100); 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`) SELECT 1080101, '统计查询', 'mes:dc-statistics:query', 3, 1, 1080100, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 1080101); -- 6. 角色授权(与现有生产/质检菜单保持一致:普通角色 + 155/160/162/163) INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT r.id, m.id, '1', NOW(), '1', NOW(), b'0' FROM `system_role` r JOIN `system_menu` m ON m.id IN (1080085, 1080086, 1080087, 1080088, 1080089, 1080090, 1080091, 1080092, 1080093, 1080094, 1080095, 1080096, 1080097, 1080098, 1080099, 1080100, 1080101) AND m.deleted = b'0' WHERE r.id IN (2, 155, 160, 162, 163) AND r.deleted = b'0' AND NOT EXISTS (SELECT 1 FROM `system_role_menu` rm WHERE rm.role_id = r.id AND rm.menu_id = m.id);