-- ============================================================ -- 无人值守系统 功能补全升级脚本 -- 数据库: wrzsxt-ruoyi-xinlan -- 说明: 全部使用 IF NOT EXISTS / 动态判断,可重复执行 -- ============================================================ USE `wrzsxt-ruoyi-xinlan`; -- ------------------------------------------------------------ -- 1. 设备远程监控:遥测数据表 -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS `device_telemetry` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键', `device_ledger_id` bigint NOT NULL COMMENT '设备台账id', `device_name` varchar(255) DEFAULT NULL COMMENT '设备名称冗余', `temperature` decimal(10,2) DEFAULT NULL COMMENT '温度(℃)', `pressure` decimal(10,2) DEFAULT NULL COMMENT '压力(MPa)', `vibration` decimal(10,2) DEFAULT NULL COMMENT '振动(mm/s)', `energy` decimal(12,2) DEFAULT NULL COMMENT '能耗(kWh)', `status` varchar(20) DEFAULT 'normal' COMMENT '状态 normal/warning/alarm', `alarm_content` varchar(500) DEFAULT NULL COMMENT '报警内容', `collect_time` datetime NOT NULL COMMENT '采集时间', `create_time` datetime DEFAULT NULL, `tenant_id` bigint DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_telemetry_device_time` (`device_ledger_id`, `collect_time`), KEY `idx_telemetry_time` (`collect_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备遥测实时数据'; -- 设备阈值/远程控制配置(复用 device_ledger,缺列时动态添加) DROP PROCEDURE IF EXISTS `sp_add_column_if_absent`; DELIMITER $$ CREATE PROCEDURE `sp_add_column_if_absent`( IN p_table VARCHAR(64), IN p_column VARCHAR(64), IN p_ddl VARCHAR(500)) BEGIN IF NOT EXISTS ( SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = p_table AND column_name = p_column ) THEN SET @s = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN ', p_ddl); PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; END$$ DELIMITER ; CALL sp_add_column_if_absent('device_ledger', 'temp_min', '`temp_min` decimal(10,2) DEFAULT 20 COMMENT ''温度下限'''); CALL sp_add_column_if_absent('device_ledger', 'temp_max', '`temp_max` decimal(10,2) DEFAULT 80 COMMENT ''温度上限'''); CALL sp_add_column_if_absent('device_ledger', 'pressure_min', '`pressure_min` decimal(10,2) DEFAULT 0.20 COMMENT ''压力下限'''); CALL sp_add_column_if_absent('device_ledger', 'pressure_max', '`pressure_max` decimal(10,2) DEFAULT 1.50 COMMENT ''压力上限'''); CALL sp_add_column_if_absent('device_ledger', 'vibration_max', '`vibration_max` decimal(10,2) DEFAULT 5.00 COMMENT ''振动上限'''); CALL sp_add_column_if_absent('device_ledger', 'control_status', '`control_status` varchar(20) DEFAULT ''stopped'' COMMENT ''远程控制状态 running/stopped'''); CALL sp_add_column_if_absent('device_ledger', 'telemetry_enabled', '`telemetry_enabled` tinyint DEFAULT 1 COMMENT ''是否启用遥测'''); -- ------------------------------------------------------------ -- 2. 门禁管理 -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS `access_gate` ( `id` bigint NOT NULL AUTO_INCREMENT, `gate_name` varchar(120) NOT NULL COMMENT '门禁名称', `area` varchar(120) DEFAULT NULL COMMENT '区域', `location` varchar(255) DEFAULT NULL COMMENT '位置', `weighbridge_number` varchar(64) DEFAULT NULL COMMENT '关联地磅编号', `gate_type` varchar(20) DEFAULT 'vehicle' COMMENT '类型 vehicle-车辆 person-人员 all-混合', `device_status` varchar(20) DEFAULT 'online' COMMENT '设备状态 online/offline', `maintainer` varchar(64) DEFAULT NULL COMMENT '维护人', `maintenance_time` date DEFAULT NULL COMMENT '维护时间', `remark` varchar(500) DEFAULT NULL, `create_user` bigint DEFAULT NULL, `create_time` datetime DEFAULT NULL, `update_user` bigint DEFAULT NULL, `update_time` datetime DEFAULT NULL, `tenant_id` bigint DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='门禁设备'; CREATE TABLE IF NOT EXISTS `access_record` ( `id` bigint NOT NULL AUTO_INCREMENT, `gate_id` bigint DEFAULT NULL COMMENT '门禁id', `gate_name` varchar(120) DEFAULT NULL COMMENT '门禁名称冗余', `access_type` varchar(20) NOT NULL COMMENT '类型 vehicle/person', `plate_number` varchar(32) DEFAULT NULL COMMENT '车牌号', `rfid_tag` varchar(64) DEFAULT NULL COMMENT 'RFID标签', `carrier_name` varchar(120) DEFAULT NULL COMMENT '承运商', `person_name` varchar(64) DEFAULT NULL COMMENT '人员姓名', `id_card_face` varchar(64) DEFAULT NULL COMMENT '人脸识别结果', `work_card_no` varchar(64) DEFAULT NULL COMMENT '工卡号', `direction` varchar(10) DEFAULT 'in' COMMENT '方向 in/out', `verify_result` varchar(20) DEFAULT 'pass' COMMENT '核验结果 pass/reject', `verify_method` varchar(64) DEFAULT NULL COMMENT '核验方式 车牌识别/RFID/人脸识别/工卡', `release_status` varchar(20) DEFAULT 'released' COMMENT '放行状态 released/blocked', `weighbridge_linked` tinyint DEFAULT 0 COMMENT '是否地磅联动放行', `weighbridge_record_id` bigint DEFAULT NULL COMMENT '关联地磅记录id', `reject_reason` varchar(255) DEFAULT NULL COMMENT '拒绝原因', `access_time` datetime NOT NULL COMMENT '通行时间', `create_time` datetime DEFAULT NULL, `tenant_id` bigint DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_access_time` (`access_time`), KEY `idx_access_plate` (`plate_number`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='门禁进出记录'; CREATE TABLE IF NOT EXISTS `access_vehicle` ( `id` bigint NOT NULL AUTO_INCREMENT, `plate_number` varchar(32) NOT NULL COMMENT '车牌号', `plate_color` varchar(16) DEFAULT NULL COMMENT '车牌颜色', `rfid_tag` varchar(64) DEFAULT NULL COMMENT 'RFID标签', `carrier_name` varchar(120) DEFAULT NULL COMMENT '承运商', `vehicle_type` varchar(64) DEFAULT NULL COMMENT '车辆类型', `driver_name` varchar(64) DEFAULT NULL COMMENT '司机姓名', `driver_phone` varchar(32) DEFAULT NULL COMMENT '司机电话', `valid_start` date DEFAULT NULL COMMENT '有效期开始', `valid_end` date DEFAULT NULL COMMENT '有效期结束', `status` varchar(16) DEFAULT 'valid' COMMENT '状态 valid/disabled', `remark` varchar(255) DEFAULT NULL, `create_user` bigint DEFAULT NULL, `create_time` datetime DEFAULT NULL, `update_user` bigint DEFAULT NULL, `update_time` datetime DEFAULT NULL, `tenant_id` bigint DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_access_vehicle_plate` (`plate_number`), KEY `idx_access_vehicle_rfid` (`rfid_tag`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='车辆通行授权'; -- ------------------------------------------------------------ -- 3. 无人值守地磅:扩展字段 -- ------------------------------------------------------------ CALL sp_add_column_if_absent('unattended_operation', 'net_weight', '`net_weight` decimal(12,2) DEFAULT NULL COMMENT ''净重(kg)'''); CALL sp_add_column_if_absent('unattended_operation', 'carrier_name', '`carrier_name` varchar(120) DEFAULT NULL COMMENT ''承运商'''); CALL sp_add_column_if_absent('unattended_operation', 'order_no', '`order_no` varchar(120) DEFAULT NULL COMMENT ''匹配的订单号'''); CALL sp_add_column_if_absent('unattended_operation', 'order_type', '`order_type` varchar(20) DEFAULT NULL COMMENT ''purchase/sales'''); CALL sp_add_column_if_absent('unattended_operation', 'order_matched', '`order_matched` tinyint DEFAULT 0 COMMENT ''是否匹配订单'''); CALL sp_add_column_if_absent('unattended_operation', 'cheat_flag', '`cheat_flag` tinyint DEFAULT 0 COMMENT ''是否作弊/异常'''); CALL sp_add_column_if_absent('unattended_operation', 'cheat_reason', '`cheat_reason` varchar(255) DEFAULT NULL COMMENT ''异常原因'''); CALL sp_add_column_if_absent('unattended_operation', 'infrared_status', '`infrared_status` varchar(20) DEFAULT ''normal'' COMMENT ''红外检测 normal/offset/alarm'''); CALL sp_add_column_if_absent('unattended_operation', 'erp_push_status', '`erp_push_status` varchar(20) DEFAULT ''pending'' COMMENT ''ERP推送 pending/success/failed'''); CALL sp_add_column_if_absent('unattended_operation', 'erp_push_time', '`erp_push_time` datetime DEFAULT NULL COMMENT ''ERP推送时间'''); CALL sp_add_column_if_absent('unattended_operation', 'qr_code', '`qr_code` varchar(255) DEFAULT NULL COMMENT ''磅单二维码内容'''); CALL sp_add_column_if_absent('unattended_operation', 'weigh_no', '`weigh_no` varchar(64) DEFAULT NULL COMMENT ''磅单号,唯一'''); CALL sp_add_column_if_absent('unattended_operation', 'weigh_status', '`weigh_status` varchar(20) DEFAULT ''done'' COMMENT ''称重状态 weighing/done/abnormal'''); -- ------------------------------------------------------------ -- 4. 智能巡检:补齐路径规划/报告字段 -- ------------------------------------------------------------ CALL sp_add_column_if_absent('inspection_task', 'inspection_route', '`inspection_route` text COMMENT ''巡检路径(JSON点位)'''); CALL sp_add_column_if_absent('inspection_task', 'task_status', '`task_status` varchar(20) DEFAULT ''pending'' COMMENT ''pending/running/finished'''); CALL sp_add_column_if_absent('inspection_task', 'report_content', '`report_content` text COMMENT ''自动生成的巡检报告'''); CALL sp_add_column_if_absent('inspection_task', 'report_time', '`report_time` datetime DEFAULT NULL COMMENT ''报告生成时间'''); CALL sp_add_column_if_absent('inspection_task', 'device_source', '`device_source` varchar(20) DEFAULT ''manual'' COMMENT ''manual/video/drone/robot'''); CALL sp_add_column_if_absent('inspection_task', 'actual_start_time', '`actual_start_time` datetime DEFAULT NULL'); CALL sp_add_column_if_absent('inspection_task', 'actual_end_time', '`actual_end_time` datetime DEFAULT NULL'); -- ------------------------------------------------------------ -- 5. 承运商绩效(由地磅数据聚合,补充基础信息表) -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS `carrier_info` ( `id` bigint NOT NULL AUTO_INCREMENT, `carrier_name` varchar(120) NOT NULL COMMENT '承运商名称', `contact_person` varchar(64) DEFAULT NULL, `contact_phone` varchar(32) DEFAULT NULL, `status` varchar(20) DEFAULT 'normal', `create_time` datetime DEFAULT NULL, `update_time` datetime DEFAULT NULL, `tenant_id` bigint DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_carrier_name` (`carrier_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='承运商基础信息'; -- ------------------------------------------------------------ -- 6. 菜单:补齐五个业务模块入口(幂等) -- ------------------------------------------------------------ INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark) SELECT '门禁设备管理', 2077, 6, 'accessGate', 'environmentAccess/accessManagement/index', 1, 0, 'C', '0', '0', '', 'lock', 'admin', NOW(), '门禁设备' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE parent_id=2077 AND path='accessGate'); INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark) SELECT '设备远程监控', 2077, 1, 'deviceRemoteMonitoring', 'environmentAccess/deviceRemoteMonitoring/index', 1, 0, 'C', '0', '0', '', 'monitor', 'admin', NOW(), '温度、压力、能耗监测与远程启停' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE parent_id=2077 AND path='deviceRemoteMonitoring'); INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark) SELECT '智能巡检管理', 2077, 2, 'intelligentInspectionManagement', 'environmentAccess/intelligentInspectionManagement/index', 1, 0, 'C', '0', '0', '', 'guide', 'admin', NOW(), '路径规划、视频分析、无人机/机器人联动与自动报告' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE parent_id=2077 AND path='intelligentInspectionManagement'); INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark) SELECT '无人值守地磅', 2077, 3, 'unattendedWeighbridgeSystem', 'environmentAccess/unattendedWeighbridgeSystem/index', 1, 0, 'C', '0', '0', '', 'form', 'admin', NOW(), '自动称重、红外防作弊、ERP与二维码磅单' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE parent_id=2077 AND path='unattendedWeighbridgeSystem'); INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark) SELECT '报表中心', 2077, 7, 'unattendedReportCenter', 'environmentAccess/reportCenter/index', 1, 0, 'C', '0', '0', '', 'chart', 'admin', NOW(), '每日过磅与承运商绩效' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE parent_id=2077 AND path='unattendedReportCenter'); -- ------------------------------------------------------------ -- 7. 历史地磅记录回填:避免电子磅单二维码出现 null -- ------------------------------------------------------------ UPDATE unattended_operation SET net_weight = ROUND(gross_weight - tare_weight, 2) WHERE net_weight IS NULL AND gross_weight IS NOT NULL AND tare_weight IS NOT NULL; UPDATE unattended_operation SET weigh_no = CONCAT('WB', DATE_FORMAT(COALESCE(weight_time, create_time, NOW()), '%Y%m%d%H%i%s'), LPAD(MOD(id, 1000), 3, '0')) WHERE weigh_no IS NULL OR weigh_no = '' OR LOWER(weigh_no) = 'null'; UPDATE unattended_operation SET qr_code = CONCAT_WS('|', COALESCE(NULLIF(weigh_no, 'null'), ''), COALESCE(NULLIF(plate_number, 'null'), ''), COALESCE(CAST(net_weight AS CHAR), ''), COALESCE(DATE_FORMAT(weight_time, '%Y-%m-%d %H:%i:%s'), ''), COALESCE(NULLIF(carrier_name, 'null'), ''), COALESCE(NULLIF(weighbridge_number, 'null'), '') ); DROP PROCEDURE IF EXISTS `sp_add_column_if_absent`;