{ "cells": [ { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_18728\\4048319598.py:101: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n", " df=pd.read_sql(sql,db.con)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ " 包裹测量时间 order_id SKU 订单时间 \\\n", "0 2025-03-01 14:06:35 250225025414549 22696 2025-February-25th \n", "1 2025-04-02 15:44:20 250330050647870 220934 2025-March-30th \n", "2 2025-03-03 16:31:27 250225062602893 221468 2025-February-25th \n", "3 2025-03-15 10:37:19 250311002220317 222890 2025-March-11th \n", "4 2025-04-03 18:16:25 250331190637251 224072 2025-March-31st \n", "... ... ... ... ... \n", "13489 2025-05-04 16:39:49 250429213610207 2207148642 2025-April-29th \n", "13490 2025-05-04 13:03:06 250501025810935 2207161446 2025-May-1st \n", "13491 2025-05-05 17:41:38 250430063602142 2207176415 2025-April-30th \n", "13492 2025-05-02 13:33:34 250430035236822 2207192083 2025-April-30th \n", "13493 2025-05-08 20:15:50 250430104809255 2207202010 2025-April-30th \n", "\n", " 包裹号 快递公司 运输方式 目的国 postcode ERP采购价 \\\n", "0 991344640 海MS-FEDEX 海运 United States 55414 50.0 \n", "1 991399418 海MS-FEDEX 海运 United States 37014 45.0 \n", "2 991348761 海MS-FEDEX02 海运 United States 11803 65.0 \n", "3 991369401 海MS-FEDEX02 海运 United States 54568-9248 35.0 \n", "4 991401728 海MS-FEDEX 海运 United States 34112 180.0 \n", "... ... ... ... ... ... ... \n", "13489 991447512 海MS-FEDEX-SAIR-H 海运 United States 32168 NaN \n", "13490 991446594 海MS-FEDEX-SAIR-H 海运 United States 92647 NaN \n", "13491 991449178 海MS-FEDEX-SAIR-H 海运 United States 90019 518.0 \n", "13492 991444756 海MS-FEDEX-SAIR-H 海运 United States 78572 228.0 \n", "13493 991454248 海MS-FEDEX-SAIR-H 海运 United States 89129 NaN \n", "\n", " ERP包裹数据 \\\n", "0 {\"包裹1\": {\"宽\": \"21.0\", \"长\": \"21.0\", \"高\": \"21.0\"... \n", "1 {\"包裹1\": {\"宽\": \"41.0\", \"长\": \"41.0\", \"高\": \"21.0\"... \n", "2 {\"包裹1\": {\"宽\": \"32.0\", \"长\": \"33.0\", \"高\": \"28.0\"... \n", "3 {\"包裹1\": {\"宽\": \"28.0\", \"长\": \"28.0\", \"高\": \"13.0\"... \n", "4 {\"包裹1\": {\"宽\": \"25.0\", \"长\": \"75.0\", \"高\": \"28.0\"... \n", "... ... \n", "13489 {\"包裹1\": {\"宽\": \"70.0\", \"长\": \"190.0\", \"高\": \"20.0... \n", "13490 {\"包裹1\": {\"宽\": \"48.0\", \"长\": \"54.0\", \"高\": \"14.0\"... \n", "13491 {\"包裹1\": {\"宽\": \"30.0\", \"长\": \"193.0\", \"高\": \"13.0... \n", "13492 {\"包裹1\": {\"宽\": \"51.0\", \"长\": \"53.0\", \"高\": \"33.0\"... \n", "13493 {\"包裹1\": {\"宽\": \"70.0\", \"长\": \"130.0\", \"高\": \"45.0... \n", "\n", " 实际包裹数据 \n", "0 {\"991344640\": {\"长\": 45.00, \"宽\": 45.00, \"高\": 17... \n", "1 {\"991399418\": {\"长\": 41.00, \"宽\": 41.00, \"高\": 21... \n", "2 {\"991348761\": {\"长\": 31.00, \"宽\": 31.00, \"高\": 24... \n", "3 {\"991369401\": {\"长\": 28.00, \"宽\": 28.00, \"高\": 12... \n", "4 {\"991401728\": {\"长\": 75.00, \"宽\": 25.00, \"高\": 16... \n", "... ... \n", "13489 {\"991447512\": {\"长\": 208.00, \"宽\": 54.00, \"高\": 1... \n", "13490 {\"991446594\": {\"长\": 53.00, \"宽\": 48.00, \"高\": 20... \n", "13491 {\"991449178\": {\"长\": 193.00, \"宽\": 30.00, \"高\": 1... \n", "13492 {\"991444756\": {\"长\": 53.00, \"宽\": 51.00, \"高\": 33... \n", "13493 {\"991454248\": {\"长\": 141.00, \"宽\": 76.00, \"高\": 2... \n", "\n", "[13494 rows x 12 columns]\n" ] } ], "source": [ "import pandas as pd\n", "from utils.gtools import MySQLconnect\n", "\n", "# 读取需要计算的包裹信息\n", "with MySQLconnect('ods') as db:\n", " sql = r\"\"\" \n", " WITH\n", "t1 AS (\n", "SELECT\n", "order_id,\n", "SKU,\n", "order_date,\n", "sum(CASE WHEN opl.order_product_id LIKE '%\\_%' ESCAPE '\\\\' \n", " AND opl.order_product_id NOT LIKE '%\\_%\\_%' ESCAPE '\\\\' THEN product_num END) AS product_num,\n", "DATE_FORMAT(order_date,\"%Y-%m-%d\") AS 订单时间,\n", "count(opl.SKU) AS 产品种类\n", "FROM\n", "dws.fact_order_product_list opl\n", "WHERE\n", " NOT EXISTS (\n", " SELECT 1 \n", " FROM dws.log_order_reissue_detail AS r \n", " WHERE r.order_product_id = opl.order_product_id\n", " )\n", "AND order_date >= \"20250101\"\n", "AND order_date < \"20250601\"\n", "AND SKU <> \"\"\n", "GROUP BY order_id\n", ")\n", ",\n", "t2 AS (\n", "SELECT\t\t\t\n", " a.`包裹测量时间`,\n", "\t\t\t\t\t\tt1.order_id,\n", "\t\t\t\t\t\tt1.SKU,\n", "\t\t\t\t\t\tt1.order_date,\n", " a.包裹号,\n", " a.快递公司,\n", " a.运输方式,\n", "\t\t\t\t\t\ta.`目的国`,\n", " d.postcode,\n", " CONCAT(\n", " '\"', b.package, '\": {',\n", " '\"长\": ', length, ', ',\n", " '\"宽\": ', width, ', ',\n", " '\"高\": ', hight, ', ',\n", " '\"重量\": ', weight, '}'\n", " ) AS package_json\n", " FROM\n", "\t\t\t\tt1\n", " LEFT JOIN order_express a ON t1.order_id = a.单号\n", " JOIN package_vol_info b ON a.`包裹号` = b.package\n", " JOIN order_list d ON a.`单号` = d.order_id \n", " WHERE\n", " a.`包裹状态` IN ( '客户签收', '已经投递') \n", " AND b.hight > 0 \n", " AND b.length > 0 \n", " AND b.width > 0 \n", " AND b.hight > 0 \n", " AND b.weight > 0\n", " AND a.`目的国` = \"United States\"\n", "\t\t\t\t\t\tAND t1.product_num = 1\n", "\t\t\t\t\t\tAND t1.产品种类=1\n", "\t\t\t\t\t\tAND a.`包裹测量时间` >= '2025-03-01'\n", "\t\t\t\t\t\tAND a.`包裹测量时间` < '2025-06-01'\n", "),\n", "t3 AS (\n", "SELECT\n", "t2.*,\n", "sku.成本价 AS ERP采购价,\n", "ess.包裹数据 AS ERP包裹数据,\n", "CONCAT('{', GROUP_CONCAT(package_json SEPARATOR ','), '}') AS 实际包裹数据,\n", "ROW_NUMBER() OVER (PARTITION BY SKU ORDER BY 包裹测量时间 DESC) as rn\n", "FROM\n", "t2\n", "LEFT JOIN ads.new_erp_sku_size ess ON t2.SKU=ess.SKU\n", "LEFT JOIN stg_bayshop_litfad_sku sku ON t2.SKU=sku.SKU\n", "WHERE\n", "ess.`包裹数据`<>''\n", "GROUP BY order_id\n", ")\n", "SELECT\n", "包裹测量时间,\n", "order_id,\n", "SKU,\n", "DATE_FORMAT(order_date,\"%Y-%M-%D\") AS 订单时间,\n", "包裹号,\n", "`快递公司`,\n", "`运输方式`,\n", "`目的国`,\n", "postcode,\n", "ERP采购价,\n", "ERP包裹数据,\n", "实际包裹数据\n", "FROM\n", "t3\n", "WHERE\n", "rn=1\n", "\n", " \"\"\"\n", " df=pd.read_sql(sql,db.con)\n", " print(df)\n", " df.to_clipboard(index=False)\n", "\n", " " ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "from utils.countryOperator import OperateCountry\n", "from utils.logisticsBill import BillFactory\n", "from utils.Package import Package, Package_group\n", "import pandas as pd\n", "# 美国 \n", "from utils.logisticsBill import Billing\n", "import requests\n", "def get_package(packages_id):\n", " \"\"\"\n", " 根据订单号的包裹ID,返回包裹类，包裹明细，包裹实重，包裹体积重6000\n", " \"\"\"\n", " url = f'https://cp.maso.hk/index.php?main=biphp&act=package_fund&key=JJ57S744ZJR26ORGRMMSJ8V4D4UVF5AU&package={package_id}'\n", " resp = requests.get(url).json()\n", " if resp['code'] == \"0\":\n", " weight = int(float(resp['data'][0]['weight'])*1000)\n", " package_length = resp['data'][0]['l'].replace(\",\",\"\") if len(resp['data'][0]['l'])>0 else \"0\"\n", " package_width = resp['data'][0]['w'].replace(\",\",\"\") if len(resp['data'][0]['w'])>0 else \"0\"\n", " package_hight = resp['data'][0]['h'].replace(\",\",\"\") if len(resp['data'][0]['h'])>0 else \"0\"\n", " return float(package_length),float(package_width),float(package_hight),int(weight)\n", "\n", "# df1=pd.read_excel(r\"F:\\DOCUMENTS\\WXWork\\1688854527635889\\Cache\\File\\2025-05\\新建Microsoft Excel 工作表 (2)(1).xlsx\",sheet_name=\"包裹成本信息\")\n", "# df = df1[(df1['快递公司']==\"海MS-大健-METRO\")|(df1['快递公司']==\"海MS-大健-CEVA\")|(df1['快递公司']==\"空LAX-大健-CEVA\")]\n", "# df = df1\n", "df_grouped= df.groupby('order_id')\n", "# for index,row in df.iterrows():\n", "# if \"CEVA\" in row['快递公司']:\n", "# df.loc[index,'类型'] = \"卡派ceva\"\n", "# elif \"GIGA\" in row['快递公司']:\n", "# df.loc[index,'类型'] = \"卡派giga\"\n", "# elif \"METRO\" in row['快递公司']:\n", "# df.loc[index,'类型'] = \"卡派metro\"\n", "# elif any(x in row['快递公司'] for x in [\"FEDEX-SAIR-G\", \"FEDEX-SAIR-H\", \"FEDEX02\"]):\n", "# df.loc[index,'类型'] = \"快递\"\n", "# else:\n", "# df.loc[index,'类型'] = \"其他\"\n", "\n", "for order_num, group in df_grouped:\n", " if pd.isna(order_num) or str(order_num).lower() == \"nan\" or order_num == 0 or order_num == \"手工包裹\":\n", " continue\n", " order_num = str(int(order_num))\n", " # opCountry = OperateCountry(group['目的国'].iloc[0])\n", " opCountry = OperateCountry('US')\n", " postcode = group['postcode'].iloc[0]\n", " if pd.isna(postcode) or str(postcode).lower() == \"nan\":\n", " continue\n", " packages1= Package_group()\n", " packages2= Package_group()\n", " packages3= Package_group()\n", "\n", " # 记录包裹号\n", " packages_id1=[]\n", " packages_id2=[]\n", " packages_id3=[]\n", "\n", " if \"海\" in group['快递公司'].iloc[0]:\n", " head_type = 1\n", " else:\n", " head_type = 0\n", "\n", " for index,row in group.iterrows():\n", " print(f\"正在处理包裹 {row['包裹号']}\")\n", " df.at[index, 'order_id'] = str(int(row['order_id']))\n", " # print(f\"正在处理订单 {df.loc[index,'order_id']}\")\n", " if any(pd.isna([row['长'], row['宽'], row['高'], row['重量']])):\n", " row['长'],row['宽'],row['高'],row['重量'] = get_package(row['包裹号'])\n", "\n", " if \"FEDEX-SAIR-G\" in row['快递公司']:\n", " company_name = \"Fedex-GROUD\"\n", " elif \"FEDEX-SAIR-H\" in row['快递公司']:\n", " company_name = \"Fedex-HOME\"\n", " elif \"FEDEX02\" in row['快递公司']:\n", " company_name = \"Fedex-彩虹小马\"\n", "\n", " package = Package(row['包裹号'],row['长'],row['宽'],row['高'],row['重量'])\n", " if row['类型'] == \"快递\":\n", " packages= Package_group()\n", " packages.add_package(package)\n", " bill = Billing(str(index),opCountry,packages,postcode,company_name=company_name,head_type=head_type,beizhu='1')\n", " head_price = bill.head_amount[0]\n", " tail_price = bill.tail_amount[0]\n", " df.loc[index,'头程'] = head_price\n", " df.loc[index,'尾端'] = tail_price\n", " elif row['类型'] == \"卡派ceva\":\n", " packages1.add_package(package)\n", " packages_id1.append(row['包裹号'])\n", " elif row['类型'] == \"卡派giga\":\n", " packages2.add_package(package)\n", " packages_id2.append(row['包裹号'])\n", " elif row['类型'] == \"卡派metro\":\n", " packages3.add_package(package)\n", " packages_id3.append(row['包裹号'])\n", " else:\n", " continue\n", " \n", " if len(packages1)>0:\n", " bill1 = Billing(str(index),opCountry,packages1,postcode,company_name=\"大健-CEVA\",head_type=head_type,beizhu='1')\n", " for package_id in packages_id1:\n", " df.loc[df['包裹号']==package_id,'头程'] = bill1.head_amount[0]\n", " df.loc[df['包裹号']==package_id,'尾端'] = bill1.tail_amount[0]\n", " if len(packages2)>0:\n", " bill2 = Billing(str(index),opCountry,packages2,postcode,company_name=\"大健-GIGA\",head_type=head_type,beizhu='1')\n", " for package_id in packages_id2:\n", " df.loc[df['包裹号']==package_id,'头程'] = bill2.head_amount[0]\n", " df.loc[df['包裹号']==package_id,'尾端'] = bill2.tail_amount[0]\n", " if len(packages3)>0:\n", " bill3 = Billing(str(index),opCountry,packages3,postcode,company_name=\"大健-Metro\",head_type=head_type,beizhu='1')\n", " for package_id in packages_id3:\n", " df.loc[df['包裹号']==package_id,'头程'] = bill3.head_amount[0]\n", " df.loc[df['包裹号']==package_id,'尾端'] = bill3.tail_amount[0]\n", "\n", "df.to_clipboard(index=False)\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.to_clipboard(index=False)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "from utils.countryOperator import OperateCountry\n", "from utils.logisticsBill import BillFactory\n", "from utils.Package import Package, Package_group\n", "# 美国 \n", "from utils.logisticsBill import Billing\n", "\n", "df_grouped= df.groupby('订单号')\n", "calculated_results = []\n", "for order_num, group in df_grouped:\n", " # opCountry = OperateCountry(group['目的国'].iloc[0])\n", " opCountry = OperateCountry('US')\n", " postcode = '33900'\n", " packages= Package_group()\n", " packages_dict = {}\n", " volume_weight = 0\n", " weight = 0\n", " for index,row in group.iterrows():\n", " package = Package(row['包裹号'],row['长'],row['宽'],row['高'],row['重量'])\n", " packages.add_package(package)\n", " packages_dict[row['包裹号']] = {\n", " \"长\": row['长'],\n", " \"宽\": row['宽'],\n", " \"高\": row['高'],\n", " \"重量\": row['重量']\n", " }\n", " # weight += row['重量']/1000\n", " # volume_weight += package.get_volume_weight(6000)\n", " # postcode = row['postcode']\n", " # head_type = 1 if row['运输方式'] == '海运' else 0\n", " # try:\n", " # bill = Billing(str(index),opCountry,packages,postcode,company_name=None,head_type=head_type,beizhu='1')\n", " # head_price = bill.head_amount[0]\n", " # tail_price = bill.tail_amount[0]\n", " # tail_currency = bill.tail_amount[1]\n", " # except:\n", " # head_price ='出错'\n", " # tail_price = '出错'\n", " # tail_currency = '出错'\n", " result = {\n", " '订单号': order_num,\n", " '目的国': group['目的国'].iloc[0], # Same for all rows in the group\n", " '快递公司': group['快递公司'].iloc[0], # Same for all rows in the group\n", " '运输方式': group['运输方式'].iloc[0], # Same for all rows in the group\n", " 'postcode': group['postcode'].iloc[0], # Same for all rows in the group\n", " '销售额USD':group['销售额'].iloc[0],\n", " '真实尾端CNY': group['真实尾端CNY'].sum(),# Same for all rows in the group\n", " '真实头程CNY': group['真实头程CNY'].sum(), # Same for all rows in the group\n", " \n", " # '头程预测': head_price,\n", " # '尾端预测': tail_price,\n", " # '尾端货币': tail_currency,\n", " '产品品类': group['产品品类'].iloc[0], # Same for all rows in the group\n", " '产品分类': group['产品分类'].iloc[0], # Same for all rows in the group\n", " 'SKU': group['SKU'].iloc[0], # Same for all rows in the group\n", " '订单日期': group['订单日期'].iloc[0], # Same for all rows in the group\n", " 'packages_dict': packages_dict,\n", " '包裹数据':group['包裹数据'].iloc[0],\n", "\n", " }\n", " calculated_results.append(result)\n", "\n", " print(packages_dict)\n", "calculated_df = pd.DataFrame(calculated_results)\n", "# 将calculated_df的订单号改为order_id\n", "calculated_df.rename(columns={'订单号':'order_id'},inplace=True)\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "len(df)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "from utils.gtools import MySQLconnect\n", "\n", "ods = MySQLconnect(\"ods\")\n", "engine = ods.engine()\n", "cursor = ods.connect().cursor()\n", "\n", "\n", "batch_size = 50000 # 每次查询 500 个 order_id，避免 SQL 语句过长\n", "order_id_list = df[\"order_id\"].tolist()\n", "\n", "# 存储分批查询的结果\n", "result_dfs1 = []\n", "result_dfs2 = []\n", "for i in range(0, len(order_id_list), batch_size):\n", " batch_order_ids = order_id_list[i:i + batch_size] # 取当前批次的 order_id\n", " param = \",\".join(f\"'{order_id}'\" for order_id in batch_order_ids)\n", "\n", " purchase_order_sql = f\"\"\"\n", " WITH t1 AS (\n", " SELECT LEFT(ol.out_detials_outlink_id, 15) AS order_id,\n", " SUM(out_detials_qty * price) AS instock_cost,\n", " NULL AS buy_cost\n", " FROM ods.outstock_list ol\n", " JOIN ods.instock_list il ON ol.store_in_id = il.id \n", " WHERE LEFT(ol.out_detials_outlink_id, 15) IN ({param})\n", " GROUP BY LEFT(ol.out_detials_outlink_id, 15)\n", " \n", " UNION ALL\n", " \n", " SELECT LEFT(order_product_id, 15) AS order_id, \n", " NULL AS instock_cost,\n", " SUM(buy_num * actual_price) AS buy_cost\n", " FROM warehouse_purchasing\n", " WHERE LEFT(order_product_id, 15) IN ({param}) \n", " AND buy_audit = \"采购完成\"\n", " GROUP BY LEFT(order_product_id, 15)\n", " )\n", " SELECT order_id,\n", " SUM(CASE \n", " WHEN instock_cost IS NULL THEN buy_cost\n", " ELSE instock_cost \n", " END) AS 采购成本\n", " FROM t1 \n", " GROUP BY order_id\n", " \"\"\"\n", "\n", "# sql_biaozhun = f\"\"\"\n", " \n", "# SELECT\n", "# order_id,\n", "# sum(s1.`成本价`*product_num) AS \"入库采购价\"\n", "# FROM\n", "# dws.order_product_list opl LEFT JOIN ods.stg_bayshop_litfad_sku s1 on s1.sku = opl.sku\n", "# LEFT JOIN dwd.dim_erp_sku_package_vol_info des on des.erp_sku = opl.sku\n", " \n", "# WHERE\n", "# order_id IN ({param}) \n", "# AND order_product_id regexp '^[0-9]{{15}}_[1-9][0-9]*$'\n", "# GROUP BY order_id \n", " \n", "# \"\"\"\n", " batch_df1 = pd.read_sql(purchase_order_sql, con=engine) # 运行 SQL 查询\n", " result_dfs1.append(batch_df1) # 存入结果列表\n", " # batch_df2 = pd.read_sql(sql_biaozhun, con=engine) # 运行 SQL 查询\n", " # result_dfs2.append(batch_df2) # 存入结果列表\n", " print(f\"已完成 {i + batch_size} 个 order_id 的查询\")\n", "\n", "# 合并所有查询结果\n", "purchase_order_df1 = pd.concat(result_dfs1, ignore_index=True)\n", "# purchase_order_df2 = pd.concat(result_dfs2, ignore_index=True)\n", "purchase_order_df1[\"order_id\"] = purchase_order_df1[\"order_id\"].astype(str)\n", "# purchase_order_df2[\"order_id\"] = purchase_order_df2[\"order_id\"].astype(str)\n", "# purchase_order_df = pd.merge(purchase_order_df1, purchase_order_df2, on='order_id', how='left')\n", "\n", "# 转换数据类型，确保匹配\n", "df[\"order_id\"] = df[\"order_id\"].astype(str)\n", "\n", "# 进行合并\n", "order_id_df_cal = pd.merge(df, purchase_order_df1, on='order_id', how='left')\n", "# order_id_df_cal.drop_duplicates(subset=[\"order_id\"], inplace=True)\n", "# 复制到剪贴板\n", "order_id_df_cal.to_clipboard(index=False)\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "print(len(df),len(purchase_order_df1),len(order_id_df_cal))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# 用系统售价模型和实际体积计算实际应该有的售价和订单价" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import json\n", "import pandas as pd\n", "order_id_df_cal=pd.read_excel(r\"F:\\DOCUMENTS\\WXWork\\1688854527635889\\Cache\\File\\2025-05\\新建Microsoft Excel 工作表 (2)(1).xlsx\",sheet_name=\"包裹成本信息\")\n", "df=order_id_df_cal.groupby('order_id')\n", "for order_num,group in df:\n", " packages_dict = {}\n", " for index,row in group.iterrows():\n", " if row[\"长\"]==0 or row[\"宽\"]==0 or row[\"高\"]==0 or row[\"重量\"]==0 or pd.isnull(row[\"长\"]) or pd.isnull(row[\"宽\"]) or pd.isnull(row[\"高\"]) or pd.isnull(row[\"重量\"]):\n", " continue\n", " package_dict = {\n", " \"长\":row['长'],\n", " \"宽\":row['宽'],\n", " \"高\":row['高'],\n", " \"重量\":row['重量']\n", " }\n", " packages_dict[row['包裹号']] = package_dict\n", " print(packages_dict)\n", " order_id_df_cal.loc[order_id_df_cal['order_id']==order_num,'packages_dict'] = json.dumps(packages_dict) if len(packages_dict)>0 else None\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# 用系统售价模型和实际体积计算实际应该有的售价和订单价\n", "from sell.sell_price import call_sell_and_order_price\n", "import re\n", "order_id_df_cal['预测售价'] = 0\n", "for index, row in order_id_df_cal.iterrows():\n", " # 如果没有预测售价这一列，正常执行\n", " if row['预测售价'] > 0:\n", " continue\n", " print(row['packages_dict'])\n", " if not isinstance(row['packages_dict'], str):\n", " print(f\"跳过第 {index} 行，packages_dict 不是字符串：{row['packages_dict']}\")\n", " continue\n", " if not isinstance(package_dict, dict) or not package_dict:\n", " continue\n", " package_dict = json.loads(row['packages_dict'])\n", " # biaozhun_dict = row['包裹数据']\n", " # if isinstance(biaozhun_dict, str):\n", " # biaozhun_dict = eval(biaozhun_dict)\n", " # if biaozhun_dict is None:\n", " # continue\n", "\n", " # for key, value in biaozhun_dict.items():\n", " # for k, v in value.items():\n", " # if isinstance(v, str):\n", " # v = v.replace(',', '') # 移除千分位逗号\n", " # numbers = re.findall(r\"\\d+\\.?\\d*\", v) # 提取所有数字部分\n", " # if numbers:\n", " # biaozhun_dict[key][k] = float(numbers[0])\n", "\n", " \n", "\n", " price = row['总订单采购成本']\n", " # price1=row['入库采购价']\n", " sell_price,order_price,order_type = call_sell_and_order_price(price, package_dict)\n", " # sell_price1,order_price1,order_type1 = call_sell_and_order_price(price1, biaozhun_dict)\n", " order_id_df_cal.loc[order_id_df_cal['order_id']==row['order_id'],'预测售价'] = sell_price\n", " order_id_df_cal.loc[order_id_df_cal['order_id']==row['order_id'],'预测订单价'] = order_price\n", " order_id_df_cal.loc[order_id_df_cal['order_id']==row['order_id'],'预测订单类型'] = order_type\n", " # order_id_df_cal.loc[index,'ERP售价'] = sell_price1\n", " # order_id_df_cal.loc[index,'ERP订单价'] = order_price1\n", " # order_id_df_cal.loc[index,'ERP订单类型'] = order_type1\n", " print(f\"第{index+1}个订单{row['order_id']}的预测售价为{sell_price}，预测订单价为{order_price}，订单类型为{order_type}\")\n", "order_id_df_cal.to_clipboard(index=False)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "order_id_df_cal.to_clipboard(index=False)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import re\n", "\n", "for index, row in order_id_df_cal.iterrows():\n", " package_dict = row['packages_dict']\n", " \n", " if not isinstance(package_dict, dict):\n", " continue # 跳过无效行\n", "\n", " price = row['总订单采购成本']\n", "\n", " try:\n", " sell_price, order_price, order_type = call_sell_and_order_price(price, package_dict)\n", " except Exception as e:\n", " print(f\"订单 {row['order_id']} 报错: {e}\")\n", " continue\n", "\n", " order_id_df_cal.loc[index, '预测售价'] = sell_price\n", " order_id_df_cal.loc[index, '预测订单价'] = order_price\n", " order_id_df_cal.loc[index, '预测订单类型'] = order_type\n", "\n", " print(f\"第{index+1}个订单 {row['order_id']} 的预测售价为 {sell_price}，预测订单价为 {order_price}，订单类型为 {order_type}\")\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "order_id_df_cal.to_clipboard(index=False)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "计算各条目订单SKU的标准体积下的物流成本和采购成本\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "from utils.gtools import MySQLconnect\n", "from utils.countryOperator import OperateCountry\n", "from utils.logisticsBill import BillFactory\n", "from utils.Package import Package, Package_group\n", "import json\n", "# 美国 \n", "from utils.logisticsBill import Billing\n", "import re\n", "\n", "def get_package_info_by_sql(order_df):\n", " packages = Package_group()\n", " for index, row in order_df.iterrows():\n", " order_id = row['订单号']\n", " if row['包裹数据'] is None:\n", " return None, \"\"\n", " row['包裹数据'] = json.loads(row['包裹数据'])\n", " item_list = []\n", " for package in row['包裹数据'].values():\n", " item = {}\n", " for key, value in package.items():\n", " try:\n", " # 使用正则表达式提取数字部分\n", " number_str = re.findall(r\"[-+]?\\d*\\.\\d+|\\d+\", str(value))\n", " if number_str:\n", " item[key] = float(number_str[0]) # 取第一个匹配到的数字并转换为 float\n", " else:\n", " item[key] = value # 如果没有数字部分，保留原值\n", " except ValueError:\n", " item[key] = value # 如果遇到无法转换的值，保留原值\n", " item_list.append(item)\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", " return order_id, packages\n", "\n", "\n", "ods = MySQLconnect(\"ods\")\n", "engine = ods.engine()\n", "cursor = ods.connect().cursor()\n", "with MySQLconnect(\"ods\") as db:\n", " engine = db.engine()\n", " cursor = db.connect().cursor()\n", " sql = \"\"\"\n", " WITH t1 as \n", " (\n", " SELECT\n", " opl.order_id,\n", " -- opl.cate_3,\n", " `产品品类`,\n", " `产品分类`,\n", " # opl.SKU,\n", " SUM(opl.as_value_amount) AS 销售额,\n", " COUNT(`产品分类`) AS 条目数,\n", " sum(sku.`成本价`*product_num) AS \"入库采购价\"\n", " FROM\n", " dws.fact_order_product_list opl \n", " LEFT JOIN stg_bayshop_litfad_sku sku ON opl.SKU = sku.SKU\n", " LEFT JOIN stg_bayshop_litfad_spu spu ON sku.`产品PID` = spu.`产品PID`\n", " LEFT JOIN order_express oe ON opl.order_id = oe.单号\n", " WHERE\n", " opl.order_date >= '20240901'\n", " AND opl.order_date<'20250101'\n", " AND opl.site_name REGEXP 'Litfad'\n", " AND opl.SKU IS NOT NULL\n", " AND EXISTS (\n", " SELECT 1 \n", " FROM order_express oe_sub \n", " WHERE oe_sub.单号 = opl.order_id \n", " AND oe_sub.包裹状态 REGEXP '签收|投递'\n", " )\n", " GROUP BY opl.order_id\n", " )\n", " SELECT\n", " a.目的国,\n", " a.单号 AS `订单号`,\n", " a.运输方式,\n", " d.order_price_dollar AS '销售额',\n", " d.postcode,\n", " t1.产品品类,\n", " t1.产品分类,\n", " t1.入库采购价\n", " FROM\n", " order_express a\n", " JOIN order_list d ON a.`单号` = d.order_id \n", " JOIN t1 ON a.单号 = t1.order_id\n", " WHERE\n", " 目的国 IN ('United Kingdom','United States','Australia','Germany','Spain','France')\n", " AND t1.条目数 = 1\n", " AND t1.产品分类 IS NOT NULL\n", " ORDER BY d.order_id\n", " \"\"\"\n", " biaozhun_df = pd.read_sql(sql, engine)\n", " print(f\"获取订单基本信息{biaozhun_df.shape}\")\n", " batch_size = 10000 \n", " order_id_list = biaozhun_df[\"订单号\"].tolist()\n", " result_dfs=[]\n", " for i in range(0, len(order_id_list), batch_size):\n", " batch_order_ids = order_id_list[i:i + batch_size] # 取当前批次的 order_id\n", " param = \",\".join(f\"'{order_id}'\" for order_id in batch_order_ids)\n", "\n", " packages_sql =f\"\"\"\n", " SELECT\n", " CAST(opl.order_id AS CHAR) AS 订单号,\n", " opl.SKU,\n", " 包裹数据\n", " FROM\n", " dws.order_product_list opl\n", " LEFT JOIN ads.new_erp_sku_size spi ON opl.SKU =spi.SKU\n", " WHERE\n", " order_id in ({param})\n", " AND\n", " opl.order_product_id REGEXP '[0-9]{{15}}_[0-9]*$'\n", " \"\"\"\n", " batch_df = pd.read_sql(packages_sql, engine)\n", " result_df = batch_df.groupby(\"订单号\").apply(lambda x: pd.Series(get_package_info_by_sql(x), index=[\"订单号\", \"Packages\"])).reset_index(drop=True)\n", " result_dfs.append(result_df)\n", " print(f\"已完成 {i} / {len(order_id_list)}\")\n", " packages_df = pd.concat(result_dfs, ignore_index=True)\n", " # # 去除空值\n", " # packages_df = packages_df[packages_df['Packages'].notnull()]\n", " # 转换数据类型，确保匹配\n", " packages_df[\"订单号\"] = packages_df[\"订单号\"].astype(str)\n", " biaozhun_df[\"订单号\"] = biaozhun_df[\"订单号\"].astype(str)\n", " # 进行合并\n", " biaozhun_df = pd.merge(biaozhun_df, packages_df, on='订单号', how='left')\n", " biaozhun_df.drop_duplicates(subset=[\"订单号\"], inplace=True)\n", " print(biaozhun_df.head())\n", "\n", " for index, row in biaozhun_df.iterrows():\n", " order_id = row['订单号']\n", " packages = row['Packages'] \n", " opCountry = OperateCountry('US')\n", " postcode ='33900'\n", " head_type = 1 if row['运输方式'] == '海运' else 0\n", " if packages is None:\n", " continue\n", " try:\n", " bill = Billing(str(index),opCountry,packages,postcode,company_name=None,head_type=head_type,beizhu='1')\n", " head_price = bill.head_amount[0]\n", " tail_price = bill.tail_amount[0]\n", " tail_currency = bill.tail_amount[1]\n", " except:\n", " head_price ='出错'\n", " tail_price = '出错'\n", " tail_currency = '出错'\n", " print(f\"{order_id} 头费:{head_price} {bill.head_amount[1]} 尾费:{tail_price} {tail_currency}\")\n", " biaozhun_df.loc[index, '标准头程CNY'] = head_price\n", " biaozhun_df.loc[index, '标准尾程'] = tail_price\n", " biaozhun_df.loc[index, '尾程币种'] = tail_currency\n", "biaozhun_df.to_clipboard(index=False)\n", "\n", " " ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "biaozhun_df.to_clipboard(index=False)\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "from utils.gtools import MySQLconnect\n", "import pandas as pd\n", "df=pd.read_excel(r\"F:\\DOCUMENTS\\WXWork\\1688854527635889\\Cache\\File\\2025-03\\导出订单维护任务数据2025-3-20.xlsx\",sheet_name=\"导出订单维护任务数据2025-3-20\")\n", "# df['订单数量'] = df['订单数量'].astype(str)\n", "# df = df[df['订单数量'].str.len() > 0]\n", "# df['订单数量'] = df['订单数量'].astype(float)\n", "# df = df[df['订单数量']>0]\n", "df=df[['erp sku','订单号']]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "from utils.countryOperator import OperateCountry\n", "from utils.logisticsBill import BillFactory\n", "from utils.Package import Package, Package_group\n", "# 美国 \n", "from utils.logisticsBill import Billing\n", "df = df[df['erp sku'] != 0]\n", "# df的订单号转换字符串\n", "df['订单号'] = df['订单号'].astype(str)\n", "# 过滤掉订单号长度为0的行\n", "df = df[df['订单号'].str.len() > 0]\n", "\n", "sku_df = pd.DataFrame()\n", "with MySQLconnect(\"ods\") as db:\n", " engine = db.engine()\n", " conn = db.connect()\n", " cursor = conn.cursor()\n", " df_group = df.groupby('erp sku')\n", " for sku, group in df_group:\n", " for index1,row in group.iterrows():\n", " order_list = group[\"订单号\"].tolist()\n", " param = \",\".join(f\"'{order_id}'\" for order_id in order_list)\n", " sql = f\"\"\"\n", " SELECT\n", " order_id\n", " FROM\n", " dws.fact_order_product_list\n", " WHERE\n", " SKU = {sku}\n", " AND order_id IN ({param})\n", " and site_type IN (\"独立站\")\n", " group by order_id\n", " \"\"\"\n", " # 找到sku的订单号，根据订单号计算该订单号的利润情况\n", " order_id_df = pd.read_sql(sql, con=engine)\n", " if order_id_df.empty:\n", " continue\n", " order_id_df['sku']=sku\n", " sku_df = pd.concat([sku_df, order_id_df], axis=0, ignore_index=True)\n", " print(f\"sku:{sku} 订单号数量:{len(order_id_df)}\")\n", "print(sku_df)\n", " " ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "ods = MySQLconnect(\"ods\")\n", "engine = ods.engine()\n", "cursor = ods.connect().cursor()\n", "sql = \"\"\"\n", " SELECT\n", "opl.order_id,\n", "`产品品类`,\n", "`产品分类`,\n", "SUM(opl.as_value_amount) AS 销售额,\n", "COUNT(`产品分类`) AS 条目数\n", "FROM\n", "dws.fact_order_product_list opl \n", "LEFT JOIN stg_bayshop_litfad_sku sku ON opl.SKU = sku.SKU\n", "LEFT JOIN stg_bayshop_litfad_spu spu ON sku.`产品PID` = spu.`产品PID`\n", "WHERE\n", "opl.order_date >= '20240901'\n", "AND opl.order_date<'20250101'\n", "AND opl.site_name REGEXP 'Litfad'\n", "GROUP BY opl.order_id\n", "\"\"\"\n", "sku_df = pd.read_sql(sql, con=engine)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# 找采购价\n", "from utils.gtools import MySQLconnect\n", "\n", "ods = MySQLconnect(\"ods\")\n", "engine = ods.engine()\n", "cursor = ods.connect().cursor()\n", "import pandas as pd\n", "order_id_list = sku_df[\"order_id\"].tolist()\n", "param = \",\".join(f\"'{order_id}'\" for order_id in order_id_list)\n", "# 取采购费\n", "purchase_order_sql = f\"\"\"\n", "with t1 AS (SELECT LEFT\n", " ( ol.out_detials_outlink_id, 15 ) AS order_id,\n", " SUM( out_detials_qty * price )/ 7 AS instock_cost,\n", " NULL AS buy_cost\n", "FROM\n", " ods.outstock_list ol\n", " JOIN ods.instock_list il ON ol.store_in_id = il.id \n", "WHERE\n", " LEFT ( ol.out_detials_outlink_id, 15 ) IN ({param}) \n", "GROUP BY\n", " LEFT ( ol.out_detials_outlink_id, 15 )\n", "UNION ALL\n", "SELECT\n", " LEFT ( order_product_id, 15 ) as order_id, \n", " NULL as instock_cost,\n", " SUM(buy_num * actual_price)/7 AS buy_cost\n", "FROM\n", " `warehouse_purchasing`\n", "WHERE\n", " LEFT ( order_product_id, 15 ) IN ({param}) \n", " AND buy_audit = \"采购完成\"\n", "group by LEFT ( order_product_id, 15 )\n", " )\n", "\n", "SELECT\n", "order_id,\n", "SUM(CASE \n", "WHEN instock_cost is null THEN\n", " buy_cost\n", "ELSE\n", " instock_cost END) AS pur_cost\n", "FROM\n", "t1 \n", "GROUP BY order_id\n", "\n", "\"\"\"\n", "purchase_order_df = pd.read_sql(purchase_order_sql, con=engine)\n", "\n", "# 转换数据类型，确保匹配\n", "purchase_order_df[\"order_id\"] = purchase_order_df[\"order_id\"].astype(str)\n", "sku_df[\"order_id\"] = sku_df[\"order_id\"].astype(str)\n", "\n", "# 进行合并\n", "sku_df = pd.merge(sku_df, purchase_order_df, on='order_id', how='left')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# sku_df=sku_df[['order_id','sku','pur_cost_y']]\n", "sku_df.columns=['order_id','sku','采购价']\n", "sku_df = sku_df[sku_df['采购价']>0] \n", "print(sku_df.head())\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# 取包裹数据\n", "package_sql = f\"\"\"\n", " SELECT\n", " a.目的国,\n", " a.单号 AS `订单号`,\n", " a.包裹号,\n", " a.快递公司,\n", " a.运输方式,\n", " d.order_price_dollar AS `销售额`,\n", " d.postcode,\n", " b.length AS `长`,\n", " b.width AS `宽`,\n", " b.hight AS `高`,\n", " b.weight AS `重量`\n", " FROM\n", " order_express a\n", " JOIN package_vol_info b ON a.`包裹号` = b.package\n", " JOIN order_list d ON a.`单号` = d.order_id\n", " WHERE\n", " a.`包裹状态` IN ( '客户签收', '已经投递' ) \n", " AND b.hight > 0 \n", " AND b.length > 0 \n", " AND b.width > 0 \n", " AND b.hight > 0 \n", " AND b.weight > 0 \n", " AND a.单号 IN ({param})\n", " ORDER BY\n", " order_id\n", " \"\"\"\n", "package_df = pd.read_sql(package_sql, con=engine)\n", "print(\"获取包裹数据成功\")\n", "i = 0\n", "package_grouped= package_df.groupby('订单号')\n", "calculated_results = []\n", "for order_id, group2 in package_grouped:\n", " print(f\"开始计算订单号{order_id}的账单,第{i}个订单\")\n", " i += 1\n", " opCountry = OperateCountry(group2['目的国'].iloc[0])\n", " packages= Package_group()\n", " for index,row in group2.iterrows():\n", " package = Package(row['包裹号'],row['长'],row['宽'],row['高'],row['重量'])\n", " packages.add_package(package)\n", " postcode = row['postcode']\n", " head_type = 1 if row['运输方式'] == '海运' else 0\n", " try:\n", " bill = Billing(str(index),opCountry,packages,postcode,company_name=None,head_type=head_type,beizhu='1')\n", " head_price = bill.head_amount[0]\n", " tail_price = bill.tail_amount[0]\n", " tail_currency = bill.tail_amount[1]\n", " except:\n", " head_price ='出错'\n", " tail_price = '出错'\n", " tail_currency = '出错'\n", " print(f\"{bill}\")\n", " result = {\n", " '订单号': order_id,\n", " '目的国': group2['目的国'].iloc[0], # Same for all rows in the group\n", " '快递公司': group2['快递公司'].iloc[0], # Same for all rows in the group\n", " '运输方式': group2['运输方式'].iloc[0], # Same for all rows in the group\n", " 'postcode': group2['postcode'].iloc[0], # Same for all rows in the group\n", " '销售额USD':group2['销售额'].iloc[0],\n", " '头程预测': head_price,\n", " '尾端预测': tail_price,\n", " '尾端货币': tail_currency,\n", " }\n", " calculated_results.append(result)\n", "calculated_df = pd.DataFrame(calculated_results)\n", "calculated_df.rename(columns={'订单号':'order_id'},inplace=True)\n", "sku_df[\"order_id\"] = sku_df[\"order_id\"].astype(str)\n", "calculated_df[\"order_id\"] = calculated_df[\"order_id\"].astype(str)\n", "# 进行合并\n", "order_id_df_cal = pd.merge(calculated_df, sku_df, on='order_id', how='left')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# order_id_df_cal = pd.merge(calculated_df, sku_df, on='order_id', how='left')\n", "order_id_df_cal.to_clipboard(index=False)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "from logisticsClass.logisticsTail_US import MetroLogistics_US\n", "from utils.countryOperator import OperateCountry\n", "from utils.logisticsBill import BillFactory\n", "from utils.Package import Package, Package_group\n", "import pandas as pd\n", "from utils.gtools import MySQLconnect\n", "# 美国 \n", "from utils.logisticsBill import Billing\n", "ods = MySQLconnect('ods')\n", "cursor = ods.connect().cursor()\n", "sql = f\"\"\"SELECT\n", "\toe.`包裹号`,\n", "\toe.`单号`,\n", "\toe.`关联提单号`,\n", "\toe.`包裹状态`,\n", "\toe.`目的国`,\n", "\toe.`快递公司`,\n", "\tol.postcode,\n", "\tpvi.length,\n", "\tpvi.width,\n", "\tpvi.hight,\n", "\tpvi.weight\n", "FROM\n", "\t`order_express` oe left JOIN package_vol_info pvi ON oe.`包裹号` = pvi.package\n", "\tLEFT JOIN order_list ol ON oe.`单号` =ol.order_id\n", "WHERE\n", "\t关联提单号 IN (\n", "\t\t'BMOU4594999',\n", "\t\t'CAIU9043860',\n", "\t'GVCU5328406',\n", "\t'TCNU7259447')\n", "\tAND `包裹状态` NOT REGEXP '已作废'\n", " \"\"\"\n", "df = pd.read_sql(sql, ods.connect())\n", "print(\"查询完成\")\n", "calculated_results = []\n", "# package_list = \",\".join(str(item) for item in df['ORDER#'].tolist())\n", "# print(package_list)\n", "# query = f\"SELECT package,length,width,hight,weight FROM `package_vol_info` WHERE `package` IN ({package_list})\"\n", "# result = cursor.execute(query)\n", "# packages_info = cursor.fetchall()\n", "\n", "# print(packages_info)\n", "# 将df和result合并\n", "# new_df = pd.DataFrame(packages_info, columns=['package', 'length', 'width', 'hight', 'weight'])\n", "# df = df.merge(new_df, left_on='ORDER#', right_on='package')\n", "# df = df.drop(columns='package')\n", "df_grouped= df.groupby('单号')\n", "\n", "MetroLogistics_US.refresh()\n", "for order_num, group in df_grouped:\n", " # opCountry = OperateCountry(group['目的国'].iloc[0])\n", " opCountry = OperateCountry('US')\n", " postcode = str(group['postcode'].iloc[0])\n", " packages= Package_group()\n", " packages_dict = {}\n", " # volume_weight = 0\n", " # weight = 0\n", " for index,row in group.iterrows():\n", " length = float(row['length']) \n", " width = float(row['width'])\n", " hight = float(row['hight'])\n", " weight = float(row['weight'])\n", " package = Package(row['包裹号'],length,width,hight,weight)\n", " packages.add_package(package)\n", " # packages_dict[row['包裹号']] = {\n", " # \"长\": row['长'],\n", " # \"宽\": row['宽'],\n", " # \"高\": row['高'],\n", " # \"重量\": row['重量']\n", " # }\n", " # weight += row['重量']/1000\n", " # volume_weight += package.get_volume_weight(6000)\n", " # postcode = row['postcode']\n", " # head_type = 1 if row['运输方式'] == '海运' else 0\n", " try:\n", " bill1 = Billing(str(index),opCountry,packages,postcode,company_name='大健-Metro',head_type=1,beizhu='1')\n", " tail_price1 = bill1.tail_amount[0]\n", " except:\n", " tail_price1 = '出错'\n", " try:\n", " bill2 = Billing(str(index),opCountry,packages,postcode,company_name='大健-CEVA',head_type=1,beizhu='1')\n", " tail_price2 = bill2.tail_amount[0]\n", " except:\n", " tail_price2 = '出错'\n", " try:\n", " bill3 = Billing(str(index),opCountry,packages,postcode,company_name='大健-GIGA',head_type=1,beizhu='1')\n", " tail_price3 = bill3.tail_amount[0]\n", " except:\n", " tail_price3 = '出错'\n", " \n", " df.loc[df['单号']==order_num,'大健-METRO']= tail_price1\n", " df.loc[df['单号']==order_num,'大健-CEVA']= tail_price2\n", " df.loc[df['单号']==order_num,'大健-GIGA']= tail_price3\n", " print(order_num)\n", "# print(result)\n", "# calculated_results.append(result)\n", "# # print(packages_dict)\n", "# calculated_df = pd.DataFrame(calculated_results)\n", "# 将calculated_df的订单号改为order_id\n", "# calculated_df.rename(columns={'订单号':'order_id'},inplace=True)\n", "print(df)\n", "df.to_clipboard(index=False)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "from utils.countryOperator import OperateCountry\n", "from utils.logisticsBill import BillFactory\n", "from utils.Package import Package, Package_group\n", "import pandas as pd\n", "from utils.gtools import MySQLconnect\n", "# 美国 \n", "from utils.logisticsBill import Billing\n", "ods = MySQLconnect('ods')\n", "cursor = ods.connect().cursor()\n", "df = pd.read_excel(r'F:\\DOCUMENTS\\WXWork\\1688854527635889\\Cache\\File\\2025-04\\Litfad 4-10-25 with data analysis-2(1).xlsx',\n", " sheet_name='CA- 96004885')\n", "package_list = \",\".join(str(item) for item in df['ORDER#'].tolist())\n", "print(package_list)\n", "query = f\"SELECT package,length,width,hight,weight FROM `package_vol_info` WHERE `package` IN ({package_list})\"\n", "result = cursor.execute(query)\n", "packages_info = cursor.fetchall()\n", "package_df = pd.DataFrame(packages_info, columns=['package','length','width','hight', 'weight'])\n", "package_df.to_clipboard(index=False)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "from utils.gtools import MySQLconnect\n", "ods = MySQLconnect('ods')\n", "engine = ods.engine()\n", "cursor = ods.connect().cursor()\n", "df = pd.read_excel(r'D:\\test\\logistics\\售价_2024-01-01.xlsx')\n", "batch_size = 50000 # 每次查询 500 个 order_id，避免 SQL 语句过长\n", "sku_list = df[\"SKU\"].tolist() \n", "result_dfs1 = []\n", "for i in range(0, len(sku_list), batch_size):\n", " batch_order_ids = sku_list[i:i + batch_size] # 取当前批次的 order_id\n", " param = \",\".join(f\"'{sku}'\" for sku in batch_order_ids)\n", "\n", " sku_sql = f\"\"\"\n", " SELECT\n", " SKU,\n", " 产品售价,\n", " `产品品类`,\n", " `产品分类`\n", " FROM\n", " `stg_bayshop_litfad_sku` sku\n", " LEFT JOIN stg_bayshop_litfad_spu spu ON sku.`产品PID` =spu.`产品PID`\n", " WHERE\n", " SKU IN ({param}) \n", " \"\"\" \n", " batch_df1 = pd.read_sql(sku_sql, con=engine) # 运行 SQL 查询\n", " result_dfs1.append(batch_df1)\n", " print(f\"已完成 {i} / {len(sku_list)}\")\n", "sku_df = pd.concat(result_dfs1) # 合并结果\n", "merge_df = pd.merge(df, sku_df, on=\"SKU\", how=\"left\") # 合并数据\n", "merge_df.to_excel(r'D:\\test\\logistics\\售价_sku_1-9月.xlsx', index=False) # 保存结果" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "筛选取原尺寸还是理论预估尺寸\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_8364\\2603640831.py:4: FutureWarning: The provided callable is currently using SeriesGroupBy.max. In a future version of pandas, the provided callable will be used directly. To keep current behavior pass the string \"max\" instead.\n", " all_df['SPU最大涨幅']=all_df.groupby('SPU')['售价涨跌幅'].transform(max)\n", "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_8364\\2603640831.py:5: FutureWarning: The provided callable is currently using SeriesGroupBy.min. In a future version of pandas, the provided callable will be used directly. To keep current behavior pass the string \"min\" instead.\n", " all_df['SPU最小涨幅']=all_df.groupby('SPU')['售价涨跌幅'].transform(min)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "10{\"stdout\":\"[{\\\"variableName\\\": \\\"ID_TO_MEANING\\\", \\\"type\\\": \\\"dictionary\\\", \\\"supportedEngines\\\": [\\\"pandas\\\"], \\\"isLocalVariable\\\": true, \\\"rawType\\\": \\\"builtins.dict\\\"}, {\\\"variableName\\\": \\\"NULL\\\", \\\"type\\\": \\\"unknown\\\", \\\"supportedEngines\\\": [\\\"pandas\\\"], \\\"isLocalVariable\\\": true, \\\"rawType\\\": \\\"_pydevd_bundle.pydevd_constants.Null\\\"}]\\n\",\"stderr\":\"\",\"mime\":[]}\n", "10{\"stdout\":\"[{\\\"variableName\\\": \\\"ID_TO_MEANING\\\", \\\"type\\\": \\\"dictionary\\\", \\\"supportedEngines\\\": [\\\"pandas\\\"], \\\"isLocalVariable\\\": true, \\\"rawType\\\": \\\"builtins.dict\\\"}, {\\\"variableName\\\": \\\"NULL\\\", \\\"type\\\": \\\"unknown\\\", \\\"supportedEngines\\\": [\\\"pandas\\\"], \\\"isLocalVariable\\\": true, \\\"rawType\\\": \\\"_pydevd_bundle.pydevd_constants.Null\\\"}]\\n\",\"stderr\":\"\",\"mime\":[]}\n", "10{\"stdout\":\"[{\\\"variableName\\\": \\\"ID_TO_MEANING\\\", \\\"type\\\": \\\"dictionary\\\", \\\"supportedEngines\\\": [\\\"pandas\\\"], \\\"isLocalVariable\\\": true, \\\"rawType\\\": \\\"builtins.dict\\\"}, {\\\"variableName\\\": \\\"NULL\\\", \\\"type\\\": \\\"unknown\\\", \\\"supportedEngines\\\": [\\\"pandas\\\"], \\\"isLocalVariable\\\": true, \\\"rawType\\\": \\\"_pydevd_bundle.pydevd_constants.Null\\\"}]\\n\",\"stderr\":\"\",\"mime\":[]}\n" ] } ], "source": [ "import pandas as pd\n", "all_df = pd.read_excel('单包裹SKU售价分析1.xlsx',sheet_name=\"Sheet1\")\n", "all_df = all_df[(all_df['是否有过修改记录']==\"否\")&(all_df['使用尺寸售价']!=\"ERP售价\")]\n", "all_df['SPU最大涨幅']=all_df.groupby('SPU')['售价涨跌幅'].transform(max)\n", "all_df['SPU最小涨幅']=all_df.groupby('SPU')['售价涨跌幅'].transform(min)\n", "\n", "filtered_df = all_df[(all_df['SPU最大涨幅'] <= 0.5) & (all_df['SPU最小涨幅'] >= -0.5)]\n", "for index,row in filtered_df.iterrows():\n", " if row['使用尺寸售价']==\"实际体积售价\":\n", " length = str(row['长'])\n", " width = str(row['宽'])\n", " height = str(row['高'])\n", " weight = str(row['重量'])\n", " else:\n", " length = str(row['理论长'])\n", " width = str(row['理论宽'])\n", " height = str(row['理论高'])\n", " weight = str(row['理论重量'])\n", " filtered_df.loc['尺寸重量']=weight+\"|\"+length+\"*\"+width+\"*\"+height+\"*1,\"\n", " print(filtered_df.loc['尺寸重量'])\n", "spu_list = filtered_df['SPU'].unique()\n", "filtered_df = filtered_df[['SKU','成本价','尺寸重量']]\n", "filtered_df " ] } ], "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 }