soc/project_data/sql/1.0.11/soc.sql

drop table if exists `iot_websocket_msg`;
CREATE TABLE `iot_websocket_msg` (
     `id` varchar(125) COLLATE utf8mb4_general_ci NOT NULL COMMENT '消息id',
     `send_by` int DEFAULT NULL COMMENT '发送方;0:主机,1:平台',
     `event` varchar(125) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '事件类型',
     `org_id` bigint DEFAULT NULL COMMENT '机构id',
     `org_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
     `iot_code` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '目标主机',
     `server_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '主机名称',
     `status` int DEFAULT NULL COMMENT '消息状态;0:处理中,1:处理完成,2:重试中,3:重试失败',
     `receive_content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '收到消息内容',
     `reply_content` text COLLATE utf8mb4_general_ci COMMENT '回复内容',
     `ready_send_time` datetime DEFAULT NULL COMMENT '准备发送时间',
     `really_send_time` datetime DEFAULT NULL COMMENT '重试成功时间',
     `fire_time` datetime DEFAULT NULL COMMENT '触发时间',
     `max_retry_times` int DEFAULT NULL COMMENT '最大重试次数',
     `cur_retry_times` int DEFAULT NULL COMMENT '当前重试次数',
     `retry_interval` int DEFAULT NULL COMMENT '重试间隔,分钟',
     `create_time` datetime DEFAULT NULL COMMENT '创建时间',
     `update_time` datetime DEFAULT NULL COMMENT '最后一次修改时间',
     PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;





DELIMITER ??
DROP PROCEDURE IF EXISTS schema_change ??
CREATE PROCEDURE schema_change()
BEGIN

    IF NOT EXISTS(SELECT *
                  FROM information_schema.columns
                  WHERE table_schema = DATABASE()
                    AND table_name = 'iot_server_product'
                    AND column_name = 'models') THEN
        ALTER TABLE  `iot_server_product`
            ADD COLUMN `models` varchar(255) NULL AFTER `categories`;
        delete from iot_server_product;
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('01e50bb0-17cd-24bf-3db9-3a141541ea12', 'FSU_Theft', 'FSU 防盗传感器', 1, '[\r\n  \"FSU\"\r\n]', '[\r\n  \"Normal\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('055e9571-52b0-06ab-c359-3a14623b2d8d', 'FSU_DoPowerCollection', 'FSU 回路通电状态', 1, '[\r\n  \"FSU\"\r\n]', '[\r\n  \"Normal\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('06f8fc23-26c8-edc1-bd8b-3a141540c0fb', 'FSU_AirConditioner', 'FSU 空调', 1, '[\r\n  \"FSU\"\r\n]', '[\r\n  \"Normal\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('0f26e066-9e07-beb3-55bd-3a147bd0d913', 'FSU_DoPowerControlBoxButton', 'FSU DO8小时控电箱按钮', 1, '[\r\n  \"FSU\"\r\n]', '[\r\n  \"Normal\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('15367b47-d383-48aa-99ea-a1c5dc573056', 'FSU_DoPowerControl', 'FSU DO8小时控电', 1, '[\r\n  \"FSU\"\r\n]', '[\r\n  \"Normal\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('19ed86f7-780d-29ec-4bc7-3a13a7097ea1', 'VGSII_Hik', '海康视频存储设备2023年入围设备', 1, '[\r\n  \"Dvs\"\r\n]', '[\r\n  \"Hik\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('1b7adc13-af87-fe22-7d49-3a141540fad9', 'FSU_Water', 'FSU 水浸传感器', 1, '[\r\n  \"FSU\"\r\n]', '[\r\n  \"Normal\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('2369b2eb-f0ae-7f68-f49c-3a0ad342f60a', 'InAnter_BM1600NTSmall', '英安特BM1600NTSmall报警主机', 1, '[\r\n  \"AlarmHost\"\r\n]', '[\r\n  \"GlobalNT2/NT3\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('2f7b0f00-43b1-07b2-dfcd-3a0bba6d4ccb', 'Hik_DS19A', 'Hik_DS19A系列报警主机', 1, '[\r\n  \"AlarmHost\"\r\n]', '[\r\n  \"DS-19A\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('384287b5-f556-b9cc-e84e-3a1057095e04', 'HikModule', '海康报警网络模块', 1, '[\r\n  \"AlarmHost\"\r\n]', '[\r\n  \"Mta120\",\r\n	\"Mta128\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('51a1f179-8fcf-ea4f-988c-3a13f9904a04', 'FSU_Gateway', 'FSU主机', 1, '[\r\n  \"FSU\"\r\n]', '[\r\n  \"GXX-ZMAF-3100\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('54eb3a1c-8f7d-4b9e-9a5c-2e8f66750000', 'FSU_SmartMeter', 'FSU 智能电表', 1, '[\r\n  \"FSU\"\r\n]', '[\r\n  \"Normal\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('649e9822-b487-6b9f-865b-3a0ad341b9c0', 'FengYe_H402', '枫叶HD402报警主机', 1, '[\r\n  \"AlarmHost\"\r\n]', '[\r\n  \"HD402\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('6acce932-9e54-e3d3-5241-3a13d1d3584a', 'DetectionHost', '物联网主机产品，VIMD服务', 1, '[\r\n  \"DetectionHost\"\r\n]', '[\r\n  \"DetectionHost\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('6acce932-9e54-e3d3-5d4a-3a13d1d3584a', 'IoTServer', 'IoT内置产品，IoT服务', 1, '[\r\n  \"IoT\",\r\n  \"IoTServer\"\r\n]', '[\r\n  \"IoTServer\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('6ba7b810-9dad-11d1-80b4-00c04fd430c8', 'FSU_Infrared', 'FSU 红外传感器', 1, '[\r\n  \"FSU\"\r\n]', '[\r\n  \"Normal\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('73af892b-2bee-4126-b498-7460c483509d', 'FSU_ThreePhaseACVoltage', 'FSU 三相电电压传感器', 1, '[\r\n  \"FSU\"\r\n]', '[\r\n  \"Normal\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('783bb246-6ebe-7e69-e055-3a14adfe10c9', 'VGSII_DaHua_V1', '大华视频存储设备（通用设备）', 1, '[\r\n  \"Dvs\"\r\n]', '[\r\n  \"Normal\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('7a5f5e54-17d7-4c2e-99b2-e4d6181f8b62', 'FSU_Gas', 'FSU 燃气传感器', 1, '[\r\n  \"FSU\"\r\n]', '[\r\n  \"Normal\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('85659b02-4fe7-cb85-6b00-3a14153fec6e', 'FSU_Smoke', 'FSU 烟雾传感器', 1, '[\r\n  \"FSU\"\r\n]', '[\r\n  \"Normal\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('92467897-92ef-647f-e55e-3a13b7f3ca9a', 'VGSII_DaHua', '大华视频存储设备2023年入围设备', 1, '[\r\n  \"Dvs\"\r\n]', '[\r\n  \"DaHua\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('98308508-be46-8eb3-ff40-3a14153f55f9', 'FSU_TemperatureAndHumidity', 'FSU 温湿度传感器', 1, '[\r\n  \"FSU\"\r\n]', '[\r\n  \"Normal\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('a1ae8610-70d1-b856-5bdf-3a141540732d', 'FSU_Ups', 'UPS', 1, '[\r\n  \"FSU\"\r\n]', '[\r\n  \"Normal\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('abce638c-33c6-bc91-39dd-3a1415401bcd', 'FSU_DoorMagnetic', 'FSU 门磁传感器', 1, '[\r\n  \"FSU\"\r\n]', '[\r\n  \"Normal\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('bed76841-d3ee-2fd0-64ed-3a0d374167a5', 'HengTong_CKWU01C', '恒通CKWU01C报警主机', 1, '[\r\n  \"AlarmHost\"\r\n]', '[\r\n  \"CKWU01C\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('ce00cabd-b2c2-8532-f56f-3a10556f2bfe', 'MtaOCX', '迈特安网络报警模块', 1, '[\r\n  \"AlarmHost\"\r\n]', '[\r\n  \"Mta120\",\r\n	\"Mta128\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('cead84cb-7d99-1b7f-b309-3a0ca885fbe5', 'CrossProcessDemo', 'CrossProcessDemo', 0, '[\r\n  \"AlarmHost\"\r\n]', NULL);
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('e9d22150-a123-c88d-568e-3a0cd6d1b434', 'HoneywellOCX_IPM', 'HoneywellOCX_IPM', 1, '[\r\n  \"AlarmHost\"\r\n]', '[\r\n  \"Standard\",\r\n  \"PT_23\",\r\n  \"Vista120\",\r\n  \"Vista10P\",\r\n  \"Vista20P\",\r\n  \"Super_23\",\r\n  \"Vista_Super\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('f6a73ea2-1cc7-d7ff-8018-3a0ad3408d98', 'DaHuaAlarmHost', 'DaHua报警主机', 1, '[\r\n  \"AlarmHost\"\r\n]', '[\r\n  \"ARC2008\",\r\n  \"ARC2016C\",\r\n  \"ARC2016C_V3\",\r\n  \"ARC9016C\",\r\n  \"ARC9016C_V3\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('f81d4fae-7dec-11d0-a765-00a0c91e6bf6', 'FSU_RollingShutterDoor', 'FSU 卷帘门传感器', 1, '[\r\n  \"FSU\"\r\n]', '[\r\n  \"Normal\"\r\n]');
        INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('f9c5d636-dac4-d515-dfbe-3a0d3da7d805', 'BOSCH_CMS', 'BOSCH_CMS报警主机', 1, '[\r\n  \"AlarmHost\"\r\n]', '[\r\n  \"CMS40\",\r\n	\"CMS41\"\r\n]');
    end if;




        IF NOT EXISTS(SELECT *
                      FROM information_schema.columns
                      WHERE table_schema = DATABASE()
                        AND table_name = 'iot_device_info'
                        AND column_name = 'enable') THEN
            ALTER TABLE `iot_device_info`
                ADD COLUMN `enable` int(11) NULL DEFAULT 0 COMMENT '0:启用，1：禁用' AFTER `update_by`;
        end if;

    IF NOT EXISTS(SELECT *
                  FROM information_schema.columns
                  WHERE table_schema = DATABASE()
                    AND table_name = 'iot_device_info'
                    AND column_name = 'sync_status') THEN
        ALTER TABLE `iot_device_info`
            ADD COLUMN `sync_status` int(0) NULL DEFAULT 0 COMMENT '下发状态;0未下发,1下发中,2下发成功,3下发失败' AFTER `enable`;
        update iot_device_info set sync_status = 2;
    end if;


        IF NOT EXISTS(SELECT *
                      FROM information_schema.columns
                      WHERE table_schema = DATABASE()
                        AND table_name = 'iot_alarm_subsystem'
                        AND column_name = 'enable') THEN
            ALTER TABLE `iot_alarm_subsystem`
                ADD COLUMN `enable` int NULL DEFAULT 0 COMMENT '0:启用，1：禁用' AFTER `source`;
        end if;

        IF NOT EXISTS(SELECT *
                      FROM information_schema.columns
                      WHERE table_schema = DATABASE()
                        AND table_name = 'iot_alarm_subsystem_log'
                        AND column_name = 'enable') THEN
            ALTER TABLE `iot_alarm_subsystem_log`
                ADD COLUMN `enable` int NULL DEFAULT 0 COMMENT '0:启用，1：禁用' AFTER `update_by`;
        end if;

        IF NOT EXISTS(SELECT *
                      FROM information_schema.columns
                      WHERE table_schema = DATABASE()
                        AND table_name = 'iot_alarm_defence_area'
                        AND column_name = 'enable') THEN
            ALTER TABLE `iot_alarm_defence_area`
                ADD COLUMN `enable` int NULL DEFAULT 0 COMMENT '0:启用，1：禁用' AFTER `module_address`;
        end if;

        IF NOT EXISTS(SELECT *
                      FROM information_schema.columns
                      WHERE table_schema = DATABASE()
                        AND table_name = 'iot_device_info_extend'
                        AND column_name = 'listen_address') THEN
            ALTER TABLE `iot_device_info_extend`
                ADD COLUMN `listen_address` varchar(255) NULL COMMENT '监听地址' AFTER `update_by`,
                ADD COLUMN `listen_port` int(11) NULL COMMENT '监听端口' AFTER `listen_address`,
                ADD COLUMN `device_mac` varchar(255) NULL COMMENT '设备mac地址' AFTER `listen_port`,
                ADD COLUMN `device_user_code` varchar(255) NULL COMMENT '主机操作码' AFTER `device_mac`,
                ADD COLUMN `iot_address` varchar(255) NULL COMMENT '控制地址' AFTER `device_user_code`,
                ADD COLUMN `host_device_type` varchar(255) NULL COMMENT '主机类型' AFTER `iot_address`;
        end if;













    END ??
DELIMITER ;
CALL schema_change();

-- 新增设备同步状态字典
delete from sys_dict_type where dict_type='sync_status';
delete from sys_dict_data where dict_type='sync_status';
INSERT INTO sys_dict_type(`dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('设备同步状态', 'sync_status', '0', '超级管理员', '2024-11-13 16:21:39', '', NULL, NULL);
INSERT INTO sys_dict_data(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ( 0, '未下发', '9', 'sync_status', NULL, 'default', 'N', '0', '超级管理员', '2024-11-13 16:22:05', '', NULL, NULL);
INSERT INTO sys_dict_data(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ( 1, '下发中', '1', 'sync_status', NULL, 'default', 'N', '0', '超级管理员', '2024-11-13 16:22:18', '', NULL, NULL);
INSERT INTO sys_dict_data(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ( 2, '下发成功', '2', 'sync_status', NULL, 'default', 'N', '0', '超级管理员', '2024-11-13 16:22:52', '', NULL, NULL);
INSERT INTO sys_dict_data(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ( 3, '下发失败', '3', 'sync_status', NULL, 'default', 'N', '0', '超级管理员', '2024-11-13 16:23:01', '', NULL, NULL);