-- 客户编号冗余到收发货人表
alter table `ecw_order_consignor`
    add column `customer_number` VARCHAR(50) DEFAULT NULL COMMENT '客户编号';
alter table `ecw_order_consignee`
    add column `customer_number` VARCHAR(50) DEFAULT NULL COMMENT '客户编号';

-- 批量刷新订单收发货人的客户编号信息
    update ecw_order_consignor nor join ecw_customer u on nor.customer_id = u.id set nor.customer_number = u.number;
    update ecw_order_consignee nee join ecw_customer u on nee.customer_id = u.id set nee.customer_number = u.number;

-- 提货相关数据冗余到订单表
alter table `ecw_order`
    add column `pick_state` tinyint DEFAULT 0 COMMENT '提货状态：0 未提货 1 部分提货 2 已提货';
alter table `ecw_order`
    add column `pick_ratio` decimal(5,2) DEFAULT '0.00' COMMENT '提货率';
alter table `ecw_order`
    add column `pick_num` int DEFAULT 0 COMMENT '提货箱数';

-- 批量刷新订单提货数量、提货率、提货状态
update ecw_order t LEFT JOIN (select a.order_id,sum(a.pick_num) as pickNum from ecw_order_pickup a where a.deleted=0 GROUP BY a.order_id) t1 on t.order_no=t1.order_id set t.pick_num=t1.pickNum,t.pick_ratio=ROUND(t1.pickNum/t.sum_num,2)*100,t.pick_state=(case when t1.pickNum is null then 0 when t1.pickNum=t.sum_num then 3 else 2 end);
-- 已提货和部分提货的订单批量刷新订单主状态
update ecw_order t set t.`status`=16 where t.`status` in (20,21);