-- 商户 H5：微信支付订单、轮播图、客户首次充值标记、关联扩展
|
|
CREATE TABLE IF NOT EXISTS `yw_wx_pay_order` (
|
`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` tinyint DEFAULT 0,
|
`remark` varchar(500) DEFAULT NULL,
|
`order_no` varchar(64) NOT NULL COMMENT '商户订单号',
|
`customer_id` int NOT NULL COMMENT '付款商户',
|
`order_type` tinyint NOT NULL COMMENT '0电表充值 1空调充值 2账单缴费',
|
`biz_ref_id` int DEFAULT NULL COMMENT 'electrical_id/customer_id/bill_id',
|
`biz_record_id` int DEFAULT NULL COMMENT 'yw_electrical_charge.id 或 yw_contract_revenue.id',
|
`amount` decimal(12,2) NOT NULL,
|
`status` tinyint DEFAULT 0 COMMENT '0待支付 1成功 2失败 3关闭',
|
`wx_transaction_id` varchar(64) DEFAULT NULL,
|
`pay_time` datetime DEFAULT NULL,
|
`openid` varchar(128) DEFAULT NULL,
|
`request_snapshot` text COMMENT '下单参数JSON',
|
`status_info` varchar(500) DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_order_no` (`order_no`),
|
KEY `idx_customer` (`customer_id`),
|
KEY `idx_status` (`status`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='微信H5支付订单';
|
|
CREATE TABLE IF NOT EXISTS `yw_h5_banner` (
|
`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` tinyint DEFAULT 0,
|
`remark` varchar(500) DEFAULT NULL,
|
`title` varchar(200) DEFAULT NULL,
|
`image_url` varchar(500) NOT NULL,
|
`link_url` varchar(500) DEFAULT NULL,
|
`sortnum` int DEFAULT 0,
|
`status` tinyint DEFAULT 0 COMMENT '0启用 1禁用',
|
`scope` tinyint DEFAULT 1 COMMENT '1商户工作台',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='H5轮播图';
|
|
SET @db = DATABASE();
|
|
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'yw_customer' AND COLUMN_NAME = 'first_recharge_done') = 0,
|
'ALTER TABLE `yw_customer` ADD COLUMN `first_recharge_done` tinyint DEFAULT 0 COMMENT ''是否已完成首次充值制'' AFTER `openid`',
|
'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_customer_electrical' AND COLUMN_NAME = 'bind_source') = 0,
|
'ALTER TABLE `yw_customer_electrical` ADD COLUMN `bind_source` tinyint DEFAULT 1 COMMENT ''0手动 1合同自动'' AFTER `electrical_id`, ADD COLUMN `contract_id` int DEFAULT NULL AFTER `bind_source`',
|
'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 = 'wx_order_no') = 0,
|
'ALTER TABLE `yw_electrical_charge` ADD COLUMN `wx_order_no` varchar(64) DEFAULT NULL COMMENT ''微信支付订单号'' AFTER `customer_id`',
|
'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_contract_revenue' AND COLUMN_NAME = 'wx_order_no') = 0,
|
'ALTER TABLE `yw_contract_revenue` ADD COLUMN `wx_order_no` varchar(64) DEFAULT NULL COMMENT ''微信支付订单号'' AFTER `bill_id`',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
