kmall-doc/00db/02变更记录/2017-11-03/kmall取推单数据.MD

Kmall通关数据

表

订单表：iwebshop_order

订单商品表：iwebshop_order_goods

收款单：iwebshop_collection_doc

用户信息表：iwebshop_member

货品表：iwebshop_products

通关单证

三单

海关电子订单、海关运单（e码头订单）、海关支付单；

推送方式

海关电子订单、海关运单，通过中网ERP系统；

海关支付单，通过工行系统推送；

中网商城数据采集

海关电子 订单、海关运单

-- 海关电子订单、海关运单
SELECT t.order_no AS 商户订单编号,
  t.user_id 订购人平台号, t1.true_name AS 订购人姓名, t1.id_type AS 订购人证件类型, t1.id_code AS 订购人证件号码, t1.mobile AS 订购人手机,
  t.accept_name AS 收货人姓名, t.mobile AS 收货人手机, '中国' AS 国家名,
  t2.area_name AS 省, t3.area_name AS 市, t4.area_name AS 区县, t.address AS 收货人地址,
  '142' AS 国家编码, t.province AS 省代码, t.city AS 市代码, t.area AS 区县代码,
  t.distribution, t5.name AS 快递方式, t.seller_id, t6.true_name AS 商户名称
FROM iwebshop_order t
  LEFT OUTER JOIN iwebshop_member t1 ON t.user_id=t1.user_id
  LEFT OUTER JOIN iwebshop_areas t2 ON t.province= t2.area_id
  LEFT OUTER JOIN iwebshop_areas t3 ON t.city= t3.area_id
  LEFT OUTER JOIN iwebshop_areas t4 ON t.area= t4.area_id
  LEFT OUTER JOIN iwebshop_delivery t5 ON t.distribution=t5.id
  LEFT OUTER JOIN iwebshop_seller t6 ON t.seller_id=t6.id
  LEFT OUTER JOIN iwebshop_order_cus t7 ON t.order_no=t7.order_no
WHERE /*t.status='5' AND*/ t.pay_status='1' AND t7.cus_order_flag='0' AND cus_waybill_flag='0';

海关电子订单、海关运单商品明细

-- 海关电子订单、海关运单, 商品明细
SELECT t.order_no, t2.goods_no, t2.sell_price, t2.cost_price, t1.goods_nums,
   t2.sell_price*t1.goods_nums AS sell_total_pice, t2.cost_price*t1.goods_nums AS cost_total_pice
   FROM iwebshop_order t
   LEFT OUTER JOIN iwebshop_order_goods t1 ON t.id=t1.order_id
   LEFT OUTER JOIN iwebshop_goods t2 ON t1.goods_id=t2.id
   LEFT OUTER JOIN iwebshop_order_cus t3 ON t.order_no=t3.order_no
WHERE /*is_send='1' AND*/ t3.cus_order_flag='0' AND t3.cus_waybill_flag='0'
   /*AND t.seller_id='5' AND t.order_no IN ('20171030170639350764')*/;

海关支付单

-- 海关支付单
SELECT t.order_no AS 商户订单编号,
  t1.true_name AS 支付人姓名, t1.id_type AS 支付人证件类型, t1.id_code AS 支付人证件号码, t1.mobile AS 支付人电话,
  t.trade_no AS 支付交易编号, t.real_amount AS 支付金额, '142' AS 支付币制, t.pay_time AS 支付时间, t.pay_type
FROM iwebshop_order t
  LEFT OUTER JOIN iwebshop_member t1 ON t.user_id=t1.user_id
  LEFT OUTER JOIN iwebshop_order_cus t2 ON t.order_no=t2.order_no
WHERE /*t.status='5'*/ AND t.pay_status='1' AND t2.cus_pay_bill_flag='0';