-- ============================================================
|
-- 无人值守系统 功能补全升级脚本
|
-- 数据库: 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`;
|