12 KiB
12 KiB
In [4]:
# 根据订单号，查询实际包裹尺寸，实际包裹实重，实际包裹体积重， # bill账单给头程单价，实际尾端计费重，预估尾端成本，尾端附加费 import os import pandas as pd from utils.Package import Package,Package_group from utils.gtools import MySQLconnect from utils.countryOperator import OperateCountry from utils.logisticsBill import BillFactory, Billing import requests import json ods = MySQLconnect("ods") engine = ods.engine() def get_package_info_by_sql(order_df): packages = Package_group() packages_str = "" for index, row in order_df.iterrows(): if row['包裹数据'] is None: return None,"" row['包裹数据'] = json.loads(row['包裹数据']) item_list = [ {key: float(value) for key, value in package.items()} for package in row['包裹数据'].values() ] for item in item_list: if item['长'] == 0 or item['宽'] == 0 or item['高'] == 0 or item['重量'] == 0: return None,"" package = Package(row['SKU'], item['长'], item['宽'], item['高'], item['重量']) packages.add_package(package) # 实际包裹尺寸 package_str = f"{ item['重量']}|{item['长']}*{ item['宽']}*{item['高']}" packages_str += package_str + "," return packages,packages_str def get_biaozhun_bill(opCountry,order_id,postcode,convey,amount): print(order_id) # 取标准包裹数据 sql = """SELECT order_id, opl.SKU, 包裹数据 FROM dws.order_product_list opl LEFT JOIN ads.sku_package_new spi ON opl.SKU =spi.SKU WHERE order_id = %s AND opl.order_product_id REGEXP "[0-9]{15}_[0-9]*$" """ order_package_date = pd.read_sql(sql, engine, params=(order_id,)) print(order_package_date) if order_package_date['包裹数据'] is None: return "",0,0,0,0,0,"","",0 # 取账单数据 beizhu = amount conveys = 1 if convey == "海运" else 0 try: packages,packages_str = get_package_info_by_sql(order_package_date) if packages is None: return "",0,0,0,0,0,"","",0 bill = Billing(str(order_id),opCountry,packages,postcode,company_name=None,head_type=conveys,beizhu=beizhu) print(bill) tail_amount = bill.bill_dict()["预测尾端"] total_weight = sum([p.weight/1000 for p in packages.packages]) _type = bill.bill_dict()["尾端渠道"] head_amount = bill.bill_dict()["预测头程CNY"] total_amount = bill.bill_dict()["总金额USD"] volume_weight = bill.bill_dict()["体积重"] per_head = bill.bill_dict()["头程单价"] other_detail = bill.get_other_fee() return packages_str,total_weight,volume_weight,per_head,head_amount,tail_amount,other_detail,_type,total_amount except ZeroDivisionError as e: print(e) return "",0,0,0,0,0,"","",0
In [5]:
sql = """SELECT CONCAT("[",GROUP_CONCAT(pr.包裹号),"]") AS package_group , ol.order_id , ol.postcode, ol.delivery_country, ol.convey, ols.`货币`, ols.`订单总额`, ols.支付方式, ol.order_price_dollar FROM parcel pr LEFT JOIN dwd.order_list ol ON ol.order_id = pr.订单号 left JOIN ods.order_list_supplement ols ON ol.order_id = ols.订单号 WHERE order_id IN (250111180013924 , 250114055637547 , 250113020908795 , 250113090041954 , 250112064002567 , 250112105838284 , 250111100026591 , 250112131002633 , 250113090613446 , 250115164425035 , 250111231028179 , 250117024809584 , 250117005435286 , 250117100906322 , 250113100616085 , 250117053614017 , 250113011509113 , 250119211402563 , 250119153413596 , 250117234053287 , 250127001437947 , 250127175227235 , 250129051002053 , 250121125819971 , 250122061036006 , 250129112827456 , 250121212647006 , 250124213014331 , 250202204402848 , 250130194409986 , 250128015414537 , 250201063711441 , 250129075002066 ) GROUP BY ol.order_id """ order_id_df = pd.read_sql(sql,engine) order_id_df[["包裹尺寸详情","实重","体积重","头程单价","头程费用(CNY)","尾端费用(当地货币)","尾端附加费","尾端渠道","总金额(USD)"]] = order_id_df.apply(lambda x: get_biaozhun_bill(OperateCountry(x['delivery_country']), x['order_id'], x['postcode'], x['convey'], x['order_price_dollar']),axis=1,result_type='expand')
250111100026591
order_id SKU 包裹数据
0 250111100026591 2206000579 None
250111180013924
order_id SKU 包裹数据
0 250111180013924 2204856252 None
250111231028179
order_id SKU 包裹数据
0 250111231028179 2205859024 None
250112064002567
order_id SKU 包裹数据
0 250112064002567 2205793890 None
250112105838284
order_id SKU 包裹数据
0 250112105838284 2205594270 None
250112131002633
order_id SKU 包裹数据
0 250112131002633 2205979853 None
250113011509113
order_id SKU 包裹数据
0 250113011509113 2205380959 None
250113020908795
order_id SKU 包裹数据
0 250113020908795 2201598238 None
250113090041954
order_id SKU 包裹数据
0 250113090041954 2205665390 None
250113090613446
order_id SKU 包裹数据
0 250113090613446 2201598243 None
250113100616085
order_id SKU 包裹数据
0 250113100616085 2204814402 None
250114055637547
order_id SKU 包裹数据
0 250114055637547 2205905912 None
250115164425035
order_id SKU 包裹数据
0 250115164425035 2205672778 None
250117005435286
order_id SKU 包裹数据
0 250117005435286 2205415197 None
1 250117005435286 2205415198 None
2 250117005435286 2205415200 None
250117024809584
order_id SKU 包裹数据
0 250117024809584 2203733825 None
250117053614017
order_id SKU 包裹数据
0 250117053614017 2205969448 None
250117100906322
order_id SKU 包裹数据
0 250117100906322 2201598255 None
250117234053287
order_id SKU 包裹数据
0 250117234053287 2205565699 None
250119153413596
order_id SKU 包裹数据
0 250119153413596 2205878673 None
250119211402563
order_id SKU 包裹数据
0 250119211402563 2205520825 None
250121125819971
order_id SKU 包裹数据
0 250121125819971 2202074323 None
250121212647006
order_id SKU 包裹数据
0 250121212647006 2205788907 None
250122061036006
order_id SKU 包裹数据
0 250122061036006 2206135206 None
250124213014331
order_id SKU 包裹数据
0 250124213014331 2205788467 None
250127001437947
order_id SKU 包裹数据
0 250127001437947 2205511833 None
250127175227235
order_id SKU 包裹数据
0 250127175227235 2205780462 None
250128015414537
order_id SKU 包裹数据
0 250128015414537 2206279144 None
250129051002053
order_id SKU 包裹数据
0 250129051002053 2205511832 None
250129075002066
order_id SKU 包裹数据
0 250129075002066 2205905094 None
250129112827456
order_id SKU 包裹数据
0 250129112827456 2205550813 None
250130194409986
order_id SKU 包裹数据
0 250130194409986 2205025464 None
1 250130194409986 2205025465 None
2 250130194409986 2205025466 None
250201063711441
order_id SKU 包裹数据
0 250201063711441 2205524464 None
250202204402848
order_id SKU 包裹数据
0 250202204402848 2205780463 None
In [ ]:
In [3]:
order_id_df.to_clipboard()
In [ ]:
#