etrade / dili-xtrade · Files

use dili_etrade;
ALTER TABLE `dili_etrade`.`order_payment_fee` 
DROP COLUMN `coupon_no`,
CHANGE COLUMN `coupon_id` `order_good_id` bigint NULL DEFAULT NULL COMMENT '商品id',
MODIFY COLUMN `modified_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
MODIFY COLUMN `fund_item` bigint NULL DEFAULT NULL COMMENT '资金项id',
ADD COLUMN `rule_id` bigint NULL COMMENT '规则id',
ADD COLUMN `rule_name` varchar(100) NULL DEFAULT NULL COMMENT '规则名称',
ADD COLUMN `his_rule_id` bigint NULL COMMENT '历史规则id',
ADD INDEX `ordergoods`(`order_good_id`),
ADD INDEX `orderid`(`order_id`);

ALTER TABLE `dili_etrade`.`order_goods` 
ADD COLUMN `product_seller_fee` bigint NULL COMMENT '商品卖方手续费',
ADD COLUMN `product_seller_fee_state` tinyint NULL COMMENT '商品卖方手续费缴费状态',
ADD COLUMN `code` varchar(40) NULL COMMENT '子单号',
ADD INDEX `orderCode`(`order_code`);

ALTER TABLE `dili_etrade`.`order` 
ADD COLUMN `totle_seller_fee` bigint NULL COMMENT '卖方手续费总计';

ALTER TABLE `dili_etrade`.`order_payment` 
ADD COLUMN `totle_seller_fee` bigint NULL COMMENT '卖方手续费总计',
ADD COLUMN `seller_fee_state` tinyint NULL COMMENT '卖方手续费缴费状态';

UPDATE `dili_etrade`.order_goods ordg
LEFT JOIN ( SELECT ordt.order_id orderid, MIN( ordt.id ) minid  FROM order_goods ordt WHERE ordt.state = 1  GROUP BY ordt.order_id) ord ON ord.orderid = ordg.order_id 
SET `code` = CASE ordg.id - ord.minid + 1 WHEN ordg.id - ord.minid + 1 > 9 THEN CONCAT( ordg.order_code, ordg.id - ord.minid + 1 ) ELSE CONCAT( ordg.order_code, '0', ordg.id - ord.minid + 1 ) END 
WHERE ordg.state = 1