#自动建立运单号关联
INSERT IGNORE INTO `express_ops_transcwb`(transcwb,cwb)
SELECT a.`transcwb`,a.cwb FROM express_ops_cwb_detail_b2ctemp  a 
LEFT JOIN `express_ops_transcwb` b ON (a.cwb = b.cwb)
WHERE multi_shipcwb='0' AND customerid=154  AND b.cwb IS NULL;
##更新托件状态
UPDATE express_ops_cwb_detail_b2ctemp SET isB2cSuccessFlag=1 WHERE customerid =154;


SELECT COUNT(cwb) FROM express_ops_cwb_detail_b2ctemp WHERE customerid =154 AND isB2cSuccessFlag =0;

SELECT COUNT(cwb) FROM njcb_dmp.express_ops_cwb_detail_b2ctemp WHERE credate>'2016-02-19'  AND getDataFlag=0 AND customerid='154';



DELETE FROM  dmp.`express_ops_cwb_detail_b2ctemp`;
DELETE FROM  dmp.`express_ops_cwb_detail`;
DELETE FROM  dmp.`express_ops_transcwb`;
DELETE FROM  dmp.`express_ops_order_flow`;
DELETE FROM  dmp.`express_ops_delivery_state`;
DELETE FROM  dmp.`edit_delivery_state_detail`;
DELETE FROM  dmp.`b2c_download_record`;
DELETE FROM  dmp.`express_save_b2cdata`;
DELETE FROM  dmp.`express_ops_cwb_detail_b2ctemp`;
DELETE FROM  dmp.`express_send_b2c_data`;
DELETE FROM  dmp.`express_ops_deliver_cash`;
DELETE FROM  dmp.`express_ops_pos_paydetail`;
DELETE FROM  dmp.`express_ops_transit_order`;
DELETE FROM  dmp.`express_ops_transitconf`;
DELETE FROM  dmp.`express_ops_transittemp`;
DELETE FROM  dmp.`express_ops_order_dic`;
DELETE FROM  oms.`express_send_b2c_data`;





SELECT a.customerid,IFNULL(d.dtsNum4, 0) '商家名称',a.dtsNum '所有待推送',IFNULL(b.dtsNum2, 0) '签收待推送' ,IFNULL(c.dtsNum3, 0) '推送失败数' FROM (
SELECT customerid,    COUNT(cwb) AS dtsNum 
FROM    njcb_oms.express_send_b2c_data
WHERE    send_b2c_flag = 0
AND customerid IN (124,125,127,128,143,154,172,178,179,188,192,193,194,212,213,215,219,226,227,230,231,233,238,239,252,253,254,256,261)
AND flowordertype <>1
AND posttime >= CONCAT(CURDATE(), ' 00:00:00')
AND posttime <= CONCAT(CURDATE(), ' 23:59:59')
GROUP BY    customerid 
) a 
LEFT JOIN (
SELECT    customerid,    COUNT(cwb) AS dtsNum2
FROM    njcb_oms.express_send_b2c_data
WHERE    send_b2c_flag = 0

AND flowordertype =36
AND posttime >= CONCAT(CURDATE(), ' 00:00:00')
AND posttime <= CONCAT(CURDATE(), ' 23:59:59')
GROUP BY    customerid 
) b ON a.customerid=b.customerid 
LEFT JOIN (
SELECT customerid,    COUNT(cwb) AS dtsNum3
FROM    njcb_oms.express_send_b2c_data
WHERE    send_b2c_flag = 2
AND flowordertype <> 1

AND posttime >= CONCAT(CURDATE(), ' 00:00:00')
AND posttime <= CONCAT(CURDATE(), ' 23:59:59')
GROUP BY    customerid 
) c ON a.customerid=c.customerid 
LEFT JOIN (
SELECT customerid,    yewuname AS dtsNum4   
FROM    test.shangjianame

GROUP BY    customerid 
) d ON a.customerid=d.customerid 

ORDER BY a.dtsNum DESC;





 ##临时中转表
CREATE TABLE express_ops_zhongzhuantemp AS SELECT * FROM express_ops_cwb_detail_b2ctemp WHERE 1=2;
ALTER TABLE express_ops_zhongzhuantemp  ADD PRIMARY KEY(opscwbid);
ALTER TABLE express_ops_zhongzhuantemp ADD UNIQUE INDEX tmall_notify_id_zhongzhuan (tmall_notify_id);
ALTER TABLE express_ops_zhongzhuantemp ADD INDEX  detail_cwb_idx_zhongzhuan (`cwb`);
ALTER TABLE express_ops_zhongzhuantemp ADD INDEX  detail_email_date_zhongzhuan (`emaildate`);
ALTER TABLE express_ops_zhongzhuantemp ADD INDEX  detail_getdataflag_id_zhongzhuan (`getDataFlag`);
ALTER TABLE express_ops_zhongzhuantemp ADD INDEX  detail_customerid_zhongzhuan (`customerid`);

###订单报文寄存关联
CREATE TABLE express_ops_zhongzhuandetail (
	id BIGINT(11) PRIMARY KEY,
	cwb VARCHAR(100) UNIQUE COMMENT '中转单号',
	credate DATETIME COMMENT '创建时间',
	customerid INT(11) COMMENT '供应商id',
	b2cnum INT(11) COMMENT 'b2c连接点',
	zhongzhuan_id BIGINT(11) COMMENT '哪次报文下发'
)ENGINE = INNODB  CHARACTER SET UTF8 COLLATE utf8_general_ci;
ALTER TABLE express_ops_zhongzhuandetail ADD INDEX zhongzhuandetail_customerid_index (customerid);
ALTER TABLE express_ops_zhongzhuandetail ADD INDEX zhongzhuandetail_b2cnum_index (b2cnum);

###报文寄存
 CREATE TABLE express_ops_zhongzhuan_order (
	id BIGINT(11) PRIMARY KEY,
	b2cnum INT(11) COMMENT 'b2c连接点',
	customerid INT(11) COMMENT '供应商id',
	content TEXT COMMENT '报文详情',
	received INT(2) COMMENT '新系统是否已经接收',
	params TEXT COMMENT 'content其他对接参数等 json格式'
)ENGINE = INNODB  CHARACTER SET UTF8 COLLATE utf8_general_ci;
ALTER TABLE express_ops_zhongzhuan_order ADD INDEX zhongzhuan_customerid_index (customerid);
ALTER TABLE express_ops_zhongzhuan_order ADD INDEX zhongzhuan_b2cnum_index (b2cnum);

###地市订单中转关系表
CREATE TABLE express_ops_zhongzhuanconf(
	id BIGINT(11) PRIMARY KEY,
	b2cnum INT(11) COMMENT 'b2c连接点',
	customerid INT(11) COMMENT '供应商id',
	province VARCHAR(25) COMMENT '省',
	city VARCHAR(25) COMMENT '市'
)ENGINE = INNODB  CHARACTER SET UTF8 COLLATE utf8_general_ci;
ALTER TABLE express_ops_zhongzhuanconf ADD INDEX zhongzhuanconf_customerid_index (customerid);
ALTER TABLE express_ops_zhongzhuanconf ADD INDEX zhongzhuanconf_b2cnum_index (b2cnum);







 ##临时中转表
CREATE TABLE express_ops_transittemp AS SELECT * FROM express_ops_cwb_detail_b2ctemp WHERE 1=2;
ALTER TABLE express_ops_transittemp  ADD PRIMARY KEY(opscwbid);
ALTER TABLE express_ops_transittemp ADD UNIQUE INDEX tmall_notify_id_zhongzhuan (tmall_notify_id);
ALTER TABLE express_ops_transittemp ADD INDEX  detail_cwb_idx_zhongzhuan (`cwb`);
ALTER TABLE express_ops_zhongzhuantemp ADD INDEX  detail_email_date_zhongzhuan (`emaildate`);
ALTER TABLE express_ops_transittemp ADD INDEX  detail_getdataflag_id_zhongzhuan (`getDataFlag`);
ALTER TABLE express_ops_transittemp ADD INDEX  detail_customerid_zhongzhuan (`customerid`);

###订单报文寄存关联
CREATE TABLE express_ops_transitdetail (
	id BIGINT(11) PRIMARY KEY AUTO_INCREMENT,
	cwb VARCHAR(100) UNIQUE COMMENT '中转单号',
	credate DATETIME COMMENT '创建时间',
	customerid INT(11) COMMENT '供应商id',
	b2cnum INT(11) COMMENT 'b2c连接点',
	zhongzhuan_id BIGINT(11) COMMENT '哪次报文下发'
)ENGINE = INNODB  CHARACTER SET UTF8 COLLATE utf8_general_ci;
ALTER TABLE express_ops_transitdetail ADD INDEX zhongzhuandetail_customerid_index (customerid);
ALTER TABLE express_ops_transitdetail ADD INDEX zhongzhuandetail_b2cnum_index (b2cnum);

###报文寄存
 CREATE TABLE express_ops_transit_order (
	id BIGINT(11) PRIMARY KEY AUTO_INCREMENT,
	b2cnum INT(11) COMMENT 'b2c连接点',
	customerid INT(11) COMMENT '供应商id',
	content TEXT COMMENT '报文详情',
	received INT(2) COMMENT '新系统是否已经接收 0：未接收 1：已接收',
	transit INT(2) COMMENT '是否发往新系统 0:不发 1：发送',
	params TEXT COMMENT 'content其他对接参数等 json格式'
)ENGINE = INNODB  CHARACTER SET UTF8 COLLATE utf8_general_ci;
ALTER TABLE express_ops_transit_order ADD INDEX zhongzhuan_customerid_index (customerid);
ALTER TABLE express_ops_transit_order ADD INDEX zhongzhuan_b2cnum_index (b2cnum);

 ###地市订单中转关系表
CREATE TABLE express_ops_transitconf(
	id BIGINT(11) PRIMARY KEY AUTO_INCREMENT,
	b2cnum INT(11) COMMENT 'b2c连接点',
	customerid INT(11) COMMENT '供应商id',
	province VARCHAR(25) COMMENT '省',
	city VARCHAR(25) COMMENT '市',
	state INT(2) DEFAULT 0 COMMENT '0 启用 1 禁用',
    url VARCHAR(1024) COMMENT '新系统接口地址'
)ENGINE = INNODB  CHARACTER SET UTF8 COLLATE utf8_general_ci;
ALTER TABLE express_ops_transitconf ADD INDEX zhongzhuanconf_customerid_index (customerid);
ALTER TABLE express_ops_transitconf ADD INDEX zhongzhuanconf_b2cnum_index (b2cnum);



##添加内部错误码 不可重复执行
ALTER TABLE express_set_reason ADD COLUMN CODE VARCHAR(10) UNIQUE COMMENT '内部错误码';
ALTER TABLE express_set_reason ADD COLUMN del INT(1) DEFAULT 0 COMMENT '是否启用 0：启用 1：删除';
UPDATE express_set_reason SET del =1;

###补充最新的短语
INSERT INTO express_set_reason(reasoncontent,CODE,reasontype,del) VALUES   ('客户改双休日假日配送','1001','2',0),
('客户改工作日送','1002','2',0),
('客户更改时间','1003','2',0),
('客户改地点自提','1004','2',0),
('面单电话错误','1005','2',0),
('面单电话联系不上','1006','2',0),
('客户不在收件地址','1007','2',0),
('客户改地址需中转','1008','2',0),
('面单地址错误变更地址','1009','2',0),
('运输延误到站','1010','2',0),
('仓库发货延误','1011','2',0),
('分拨错误','1012','2',0),
('人力或设备不足','1013','2',0),
('地址偏远','1014','2',0),
('地址偏远转承运商配送','1015','2',0),
('天气原因','1016','2',0),
('极端恶劣天气、灾害或限行等（慎用）','1017','2',0),
('PDA/POS/系统问题','1018','2',0),
('包裹丢失','4001','9',0),
('本人签收','3001','12',0),
('客户委托他人代签','3002','12',0),
('客户委托存自提柜','3003','12',0),
('供货商拦截订单','3004','12',0),
('未开箱，无理由拒收或退货','2001','3',0),
('开箱，完好但拒收或退货','2002','3',0),
('电话和上门无法联系客户','2003','3',0),
('面单地址错误、实际地址非我司覆盖','2004','3',0),
('包装完好内物污损','2005','3',0),
('包装完好内物错漏','2006','3',0),
('包装污损','2007','3',0),
('未按承诺时间送达致拒收','2008','3',0),
('极端恶劣天气、灾害或限行等（慎用）','2009','3',0);

## 1 中转原因 2 滞留原因 3 退货原因 4 配送结果备注 5 入库备注 6 退货再投原因 7 服务投诉 8 未刷卡原因 9 丢失原因 10 催件投诉 11 客服备注 12 配送成功



http://localhost:8080/dmp/tmall/interface
partner CNDCP-WAREHOUSE-KEY
sign_type MD5
sign ZmRiMWIwODAxYmVlZDVlZjQ1NzcwODk3YjUwMWQzM2E=
notify_id 00000000664501EE000000E8E0A74E50
notify_type tms_order_notify
notify_time 2016-02-26 11:42:34
<request> <order_code>LBX032314111719696</order_code> <tms_service_code>DAYUPEI-0015</tms_service_code> <tms_order_code>201602261142</tms_order_code> <receiver_name>桃子</receiver_name> <receiver_zip>000000</receiver_zip> <receiver_province>浙江省</receiver_province> <receiver_city>杭州市</receiver_city> <receiver_district>上城区</receiver_district> <receiver_address>小营街道 建国中路146号赢时通租车(杭州建国路店)</receiver_address> <receiver_mobile>18066666666</receiver_mobile> <package_weight>7.0</package_weight> <wms_code>STORE_192237</wms_code> <wms_address>测试地址001</wms_address> <item_list> <item> <item_quantity>1</item_quantity> <insuredAmount>250000</insuredAmount> <extendFields>is_precious:1</extendFields> <item_name>***</item_name> </item> <item> <item_quantity>1</item_quantity> <insuredAmount>600000</insuredAmount> <extendFields>is_precious:1</extendFields> <item_name>***</item_name> </item> </item_list> <totalInsuredAmount>850000</totalInsuredAmount> </request>