-- 商户充值模块：客户 GS 配置、电表/空调关联、充值记录扩展
|
|
CREATE TABLE IF NOT EXISTS `yw_customer_gs` (
|
`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,
|
`customer_id` int NOT NULL COMMENT '关联 yw_customer.id',
|
`platform_gs_id` int DEFAULT NULL COMMENT '智精灵 addGs 返回的公司 ID',
|
`is_pwr` tinyint DEFAULT 1 COMMENT '计费开关 0关 1开',
|
`is_rest_stop` tinyint DEFAULT 0 COMMENT '18:00-09:00 不停机 0否 1是',
|
`gs_bz` varchar(500) DEFAULT NULL COMMENT '备注',
|
`stop_money` decimal(12,2) DEFAULT 0 COMMENT '欠费额度（元）',
|
`left_money` decimal(12,4) DEFAULT NULL COMMENT '剩余金额，同步自 getGs',
|
`sync_date` datetime DEFAULT NULL COMMENT '余额同步时间',
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_customer_id` (`customer_id`),
|
KEY `idx_platform_gs_id` (`platform_gs_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商户空调 GS 配置';
|
|
CREATE TABLE IF NOT EXISTS `yw_customer_electrical` (
|
`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,
|
`customer_id` int NOT NULL COMMENT '关联 yw_customer.id',
|
`electrical_id` int NOT NULL COMMENT '关联 yw_electrical.id',
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_customer_electrical` (`customer_id`, `electrical_id`),
|
KEY `idx_electrical_id` (`electrical_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商户关联电表';
|
|
CREATE TABLE IF NOT EXISTS `yw_customer_conditioner` (
|
`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,
|
`customer_id` int NOT NULL COMMENT '关联 yw_customer.id',
|
`conditioner_id` int NOT NULL COMMENT '关联 yw_conditioner.id',
|
`dev_ratio` int DEFAULT 100 COMMENT '电费占比%',
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_customer_conditioner` (`customer_id`, `conditioner_id`),
|
KEY `idx_conditioner_id` (`conditioner_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商户关联空调内机';
|
|
-- yw_electrical_charge 扩展列（若已存在则跳过）
|
SET @db := DATABASE();
|
|
SET @sql := IF(
|
(SELECT COUNT(*) FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'yw_electrical_charge' AND COLUMN_NAME = 'balance_after') = 0,
|
'ALTER TABLE `yw_electrical_charge` ADD COLUMN `balance_after` decimal(12,4) DEFAULT NULL COMMENT ''充值后余额（元）'' AFTER `banlance`',
|
'SELECT 1'
|
);
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @sql := IF(
|
(SELECT COUNT(*) FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'yw_electrical_charge' AND COLUMN_NAME = 'device_info') = 0,
|
'ALTER TABLE `yw_electrical_charge` ADD COLUMN `device_info` varchar(500) DEFAULT NULL COMMENT ''设备展示信息'' AFTER `balance_after`',
|
'SELECT 1'
|
);
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
