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