-- 空调多联机模块：网关/电表/计费/操作记录/用量 + 内机表扩展
|
|
CREATE TABLE IF NOT EXISTS `yw_conditioner_gateway` (
|
`id` int NOT NULL AUTO_INCREMENT,
|
`creator` int DEFAULT NULL,
|
`create_date` datetime DEFAULT NULL,
|
`editor` int DEFAULT NULL,
|
`edit_date` datetime DEFAULT NULL,
|
`isdeleted` int DEFAULT 0,
|
`remark` varchar(500) DEFAULT NULL,
|
`platform_wg_id` int DEFAULT NULL COMMENT '平台网关ID',
|
`wg_mac` varchar(64) NOT NULL COMMENT '网关MAC',
|
`wg_bz` varchar(500) DEFAULT NULL COMMENT '备注',
|
`online_status` varchar(20) DEFAULT NULL COMMENT '在线/离线',
|
`last_sync_date` datetime DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_wg_mac` (`wg_mac`),
|
KEY `idx_online_status` (`online_status`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='智精灵网关镜像';
|
|
CREATE TABLE IF NOT EXISTS `yw_conditioner_gateway_log` (
|
`id` int NOT NULL AUTO_INCREMENT,
|
`creator` int DEFAULT NULL,
|
`create_date` datetime DEFAULT NULL,
|
`editor` int DEFAULT NULL,
|
`edit_date` datetime DEFAULT NULL,
|
`isdeleted` int DEFAULT 0,
|
`remark` varchar(500) DEFAULT NULL,
|
`gateway_id` int DEFAULT NULL,
|
`wg_mac` varchar(64) DEFAULT NULL,
|
`old_status` varchar(20) DEFAULT NULL,
|
`new_status` varchar(20) DEFAULT NULL,
|
`log_time` datetime DEFAULT NULL,
|
`source` varchar(32) DEFAULT NULL COMMENT 'manual/schedule',
|
PRIMARY KEY (`id`),
|
KEY `idx_gateway_id` (`gateway_id`),
|
KEY `idx_wg_mac` (`wg_mac`),
|
KEY `idx_log_time` (`log_time`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='网关上下线记录';
|
|
CREATE TABLE IF NOT EXISTS `yw_conditioner_meter` (
|
`id` int NOT NULL AUTO_INCREMENT,
|
`creator` int DEFAULT NULL,
|
`create_date` datetime DEFAULT NULL,
|
`editor` int DEFAULT NULL,
|
`edit_date` datetime DEFAULT NULL,
|
`isdeleted` int DEFAULT 0,
|
`remark` varchar(500) DEFAULT NULL,
|
`platform_db_id` int DEFAULT NULL,
|
`db_name` varchar(128) DEFAULT NULL,
|
`db_adr` varchar(64) DEFAULT NULL,
|
`wg_mac` varchar(64) DEFAULT NULL,
|
`wg_id` int DEFAULT NULL,
|
`xy_id` int DEFAULT NULL,
|
`xy_name` varchar(128) DEFAULT NULL,
|
`dlj_mac` varchar(64) DEFAULT NULL COMMENT '多联机MAC',
|
`btl` int DEFAULT NULL COMMENT '波特率',
|
`jy` int DEFAULT NULL COMMENT '是否校验 0无校验 1校验',
|
`db_bb` int DEFAULT NULL COMMENT '变比',
|
`standby_share` varchar(32) DEFAULT NULL COMMENT '待机分摊',
|
`outdoor_loop` int DEFAULT NULL COMMENT '外机回路号',
|
`power_kw` decimal(12,4) DEFAULT NULL,
|
`total_dl` decimal(12,4) DEFAULT NULL,
|
`db_data` text,
|
`db_uptime` varchar(32) DEFAULT NULL,
|
`last_sync_date` datetime DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
KEY `idx_platform_db_id` (`platform_db_id`),
|
KEY `idx_wg_mac` (`wg_mac`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='智精灵电表镜像';
|
|
CREATE TABLE IF NOT EXISTS `yw_conditioner_billing` (
|
`id` int NOT NULL AUTO_INCREMENT,
|
`creator` int DEFAULT NULL,
|
`create_date` datetime DEFAULT NULL,
|
`editor` int DEFAULT NULL,
|
`edit_date` datetime DEFAULT NULL,
|
`isdeleted` int DEFAULT 0,
|
`remark` varchar(500) DEFAULT NULL,
|
`platform_dev_id` int DEFAULT NULL,
|
`dev_name` varchar(128) DEFAULT NULL,
|
`wg_mac` varchar(64) DEFAULT NULL,
|
`kw_type` int DEFAULT NULL COMMENT '0时长1能耗2电表',
|
`fan_arg` decimal(12,4) DEFAULT NULL,
|
`fan_kw` decimal(12,4) DEFAULT NULL,
|
`hig_kw` decimal(12,4) DEFAULT NULL,
|
`mid_kw` decimal(12,4) DEFAULT NULL,
|
`low_kw` decimal(12,4) DEFAULT NULL,
|
`kt_dj` decimal(12,4) DEFAULT NULL,
|
`last_sync_date` datetime DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
KEY `idx_platform_dev_id` (`platform_dev_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='智精灵计费系数镜像';
|
|
CREATE TABLE IF NOT EXISTS `yw_conditioner_actions` (
|
`id` int NOT NULL AUTO_INCREMENT,
|
`creator` int DEFAULT NULL,
|
`create_date` datetime DEFAULT NULL,
|
`editor` int DEFAULT NULL,
|
`edit_date` datetime DEFAULT NULL,
|
`isdeleted` int DEFAULT 0,
|
`remark` varchar(500) DEFAULT NULL,
|
`conditioner_id` int DEFAULT NULL,
|
`platform_dev_id` int DEFAULT NULL,
|
`dev_name` varchar(128) DEFAULT NULL,
|
`wg_mac` varchar(64) DEFAULT NULL,
|
`action_type` int NOT NULL COMMENT '1开关2模式3风速4温度5锁定6查电量7查功率',
|
`action_content` varchar(500) DEFAULT NULL,
|
`result_status` int DEFAULT 1 COMMENT '0失败1成功',
|
`result_msg` varchar(500) DEFAULT NULL,
|
`source` varchar(64) DEFAULT NULL COMMENT '操作来源',
|
`request_body` text,
|
`response_body` text,
|
PRIMARY KEY (`id`),
|
KEY `idx_conditioner_id` (`conditioner_id`),
|
KEY `idx_platform_dev_id` (`platform_dev_id`),
|
KEY `idx_create_date` (`create_date`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='空调设备控制记录';
|
|
CREATE TABLE IF NOT EXISTS `yw_conditioner_usage` (
|
`id` int NOT NULL AUTO_INCREMENT,
|
`creator` int DEFAULT NULL,
|
`create_date` datetime DEFAULT NULL,
|
`editor` int DEFAULT NULL,
|
`edit_date` datetime DEFAULT NULL,
|
`isdeleted` int DEFAULT 0,
|
`remark` varchar(500) DEFAULT NULL,
|
`platform_dev_id` int NOT NULL,
|
`dev_name` varchar(128) DEFAULT NULL,
|
`usage_date` date NOT NULL,
|
`sum_time` decimal(12,2) DEFAULT NULL,
|
`sum_dl` decimal(12,4) DEFAULT NULL,
|
`sum_df` decimal(12,4) DEFAULT NULL,
|
`gs_id` int DEFAULT NULL,
|
`sync_date` datetime DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_dev_date_gs` (`platform_dev_id`,`usage_date`,`gs_id`),
|
KEY `idx_usage_date` (`usage_date`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备日用量镜像';
|
|
-- 扩展 yw_conditioner（首次执行；列已存在时会报错可忽略）
|
ALTER TABLE `yw_conditioner` ADD COLUMN `platform_dev_id` int DEFAULT NULL COMMENT '平台设备ID';
|
ALTER TABLE `yw_conditioner` ADD COLUMN `wg_id` int DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `wg_mac` varchar(64) DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `wg_qid` varchar(64) DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `pid` varchar(16) DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `online` varchar(20) DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `pwr` int DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `mode` int DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `fan` int DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `fan_set` int DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `temp` int DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `temp_set` int DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `kt_lock` int DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `stop_logo` int DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `uptime` varchar(32) DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `floor_id` int DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `floor_name` varchar(64) DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `room_id` int DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `room_name` varchar(64) DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `dev_type_id` int DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `dev_type_name` varchar(64) DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `lock_pwr` int DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `lock_mode` int DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `lock_fan` int DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `lock_min_temp` int DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `lock_max_temp` int DEFAULT NULL;
|
ALTER TABLE `yw_conditioner` ADD COLUMN `last_sync_date` datetime DEFAULT NULL;
|
