用mysql计算快递费，首重续重：
— 申通
select
oi.order_time,
s.tracking_number as ‘运单号’,
oi.taobao_order_sn as ‘淘宝订单号’,
pa.pay_name as ‘支付方式’,
p.name as ‘组织’,f.facility_name as ‘仓库’,
es.shipping_name as ‘订单快递’,
oi.shipping_id,es.default_carrier_id as carrier_id,
oi.facility_id,
if(oi.district <>’0′,oi.district,if(oi.city <>’0′,oi.city,oi.province)) as region_id,
c.first_fee as ‘首重费’,
c.continued_fee as ‘续重费’,
c.first_weight as ‘首重’,
s.shipping_leqee_weight/1000 as ‘系统重量’,
cw.weight as ‘快递公司重量’,
if(oi.facility_id in(‘19568548′,’3580047′,’49858449′,’19568549′,’3633071’,
‘22143846’,’22143847′,’24196974′,’76065524′),
if( ceil(s.SHIPPING_LEQEE_WEIGHT/1000 *2)/2 – c.first_weight > 0,ceil(s.SHIPPING_LEQEE_WEIGHT/1000 *2)/2 – c.first_weight ,0),
if( ceil((s.SHIPPING_LEQEE_WEIGHT/1000 – c.first_weight)/c.first_weight) > 0,ceil((s.SHIPPING_LEQEE_WEIGHT/1000 – c.first_weight)/c.first_weight),0)
)
as ‘系统续重的重量’,
ifnull( format( (c.first_fee +
if(oi.facility_id in(‘19568548′,’3580047′,’49858449′,’19568549′,’3633071’,
‘22143846’,’22143847′,’24196974′,’76065524′),
if( ceil(s.SHIPPING_LEQEE_WEIGHT/1000 *2)/2 – c.first_weight > 0,ceil(s.SHIPPING_LEQEE_WEIGHT/1000 *2)/2 – c.first_weight ,0),
if( ceil((s.SHIPPING_LEQEE_WEIGHT/1000 – c.first_weight)/c.first_weight) > 0,ceil((s.SHIPPING_LEQEE_WEIGHT/1000 – c.first_weight)/c.first_weight),0)
)
* c.continued_fee),2 ),0) as ‘系统快递费’,
if(oi.facility_id in(‘19568548′,’3580047′,’49858449′,’19568549′,’3633071’,
‘22143846’,’22143847′,’24196974′,’76065524′),
if( ceil(cw.weight *2)/2 – c.first_weight > 0,ceil(cw.weight *2)/2 – c.first_weight ,0),
if( ceil((cw.weight – c.first_weight)/c.first_weight) > 0,ceil((cw.weight – c.first_weight)/c.first_weight),0)
)
as ‘快递公司续重的重量’,
ifnull( format( (c.first_fee +
if(oi.facility_id in(‘19568548′,’3580047′,’49858449′,’19568549′,’3633071’,
‘22143846’,’22143847′,’24196974′,’76065524′),
if( ceil(cw.weight *2)/2 – c.first_weight > 0,ceil(cw.weight *2)/2 – c.first_weight ,0),
if( ceil((cw.weight – c.first_weight)/c.first_weight) > 0,ceil((cw.weight – c.first_weight)/c.first_weight),0)
)
* c.continued_fee),2 ),0) as ‘快递公司快递费’
FROM
romeo.company_tracking_weight cw
inner join romeo.shipment s ON cw.tracking_number = s.tracking_number
inner JOIN romeo.order_shipment os ON s.shipment_id = os.shipment_id
inner JOIN ecshop.ecs_order_info oi ON os.order_id = oi.order_id
inner JOIN romeo.party p ON p.party_id = convert(oi.party_id using utf8)
inner JOIN ecshop.ecs_payment pa ON pa.pay_id = oi.pay_id
inner JOIN ecshop.ecs_shipping es ON es.shipping_id = oi.shipping_id
inner JOIN ecshop.ecs_shipping es2 ON es2.shipping_id = s.shipment_type_id
inner JOIN romeo.facility f ON f.facility_id = oi.facility_id
left JOIN ecshop.ecs_carriage c ON c.carrier_id = oi.shipping_id
and oi.facility_id = c.facility_id
and if(oi.district <>’0′,oi.district,if(oi.city <>’0′,oi.city,oi.province)) = c.region_id
where oi.shipping_id = 89
group by cw.tracking_number
这样可以根据面单号及系统维护的首重续重规则，快递公司给的重量来计算快递费了！
