logistics/cal_sell_price.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from utils.gtools import MySQLconnect\n",
    "import pandas as pd\n",
    "ads = MySQLconnect('ads')\n",
    "engine = ads.engine()\n",
    "conn = ads.connect()\n",
    "# sql = \"\"\"   SELECT\n",
    "#                 sku.SKU,\n",
    "#                 包裹数据,\n",
    "#                 成本价\n",
    "#             FROM\n",
    "#                 ods.`stg_bayshop_litfad_sku` sku\n",
    "#                 LEFT JOIN ads.new_erp_sku_size size ON sku.SKU=size.SKU\n",
    "# \t\t\t\t\t\t\t WHERE sku.状态 = \"启用\"\n",
    "# \t\t\t\t\t\t\t AND EXISTS (SELECT 1 FROM ods.stg_bayshop_litfad_spu s1 where s1.`产品PID` = sku.`产品PID`  AND s1.状态 = \"正常销售\" and s1.`产品分类` regexp \"light\")  AND sku.添加时间 >=\"2024-01-01\"\n",
    "# \"\"\"\n",
    "# df = pd.read_sql(sql, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 计算售价\n",
    "\n",
    "\n",
    "from sell.sell_price import call_sell_and_order_price\n",
    "import json\n",
    "import pandas as pd\n",
    "DATE_LIST =     [\"2024-01-01\",\"2024-02-01\",\"2024-03-01\",\"2024-04-01\",\"2024-05-01\",\"2024-06-01\",\"2024-07-01\",\"2024-08-01\",\"2024-09-01\",\"2024-10-01\",\"2024-11-01\",\"2024-12-01\",\"2025-01-01\",\"2025-02-01\",\"2025-03-01\"]\n",
    "\n",
    "def cal_sell_price(df):\n",
    "    \"\"\"\n",
    "    计算所有SKU的售价,物流分摊费\n",
    "    \"\"\"\n",
    "    for index, row in df.iterrows():\n",
    "        try:\n",
    "            package_dict = json.loads(row['包裹数据'])\n",
    "            sell_price,order_price,order_type = call_sell_and_order_price(row['成本价'], package_dict)\n",
    "        except Exception as e:\n",
    "            print(f\" {row['SKU']} 报错: {e}\")\n",
    "            continue\n",
    "        df.loc[index, '售价'] = sell_price\n",
    "        df.loc[index, '物流分摊费'] = order_price\n",
    "        df.loc[index, '物流类型'] = order_type\n",
    "        # print(f\"sku:{row['SKU']},售价:{sell_price},物流分摊费:{order_price},物流类型:{order_type}\")\n",
    "    return df\n",
    "for date in DATE_LIST:\n",
    "    sql = f\"\"\"   SELECT\n",
    "\tsku.SKU,\n",
    "\t包裹数据,\n",
    "\t成本价,\n",
    "\t产品售价,\n",
    "\t`产品品类`,\n",
    "\t`产品分类` \n",
    "FROM\n",
    "\tods.`stg_bayshop_litfad_sku` sku\n",
    "\tLEFT JOIN ads.new_erp_sku_size size ON sku.SKU = size.SKU \n",
    "\tLEFT JOIN ods.stg_bayshop_litfad_spu spu ON sku.`产品PID` =spu.`产品PID`\n",
    "WHERE\n",
    "\tsku.状态 = \"启用\" \n",
    "\tAND spu.`产品品类` REGEXP \"66\"\n",
    "\tAND spu.`状态` =\"正常销售\"\n",
    "\tAND DATE_FORMAT( sku.添加时间, \"%Y-%m-01\" )= \"{date}\"\n",
    "    \"\"\"\n",
    "    df = pd.read_sql(sql, conn)\n",
    "    df1 = cal_sell_price(df)\n",
    "    df1.to_excel(f'售价_{date}.xlsx', index=False)\n",
    "    print(f\"日期:{date}, 售价计算完成\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "美国2024版订单物流费\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "import math\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "from utils.gtools import MySQLconnect\n",
    "mat = MySQLconnect('mat')\n",
    "engine = mat.engine()\n",
    "df1 = pd.read_sql('select * from fedex2_0814', engine)\n",
    "import math\n",
    "# 美国海运订单费用,返回单个sku的订单费用和订单类型\n",
    "def us_ocean_order_price(packages):\n",
    "    # 处理包裹\n",
    "    if packages is None or len(packages) == 0:\n",
    "        return 0, '包裹数为0'\n",
    "    \n",
    "    order_fee = 0\n",
    "    base_fee = 0\n",
    "    other_fee = 0\n",
    "    order_type1 = ''  # 快递订单类型\n",
    "    # 快递费用\n",
    "    for package in packages:\n",
    "        lbs_weight = package.get_volume_weight(8.5)\n",
    "        if package.weight > 67000 or package.fst_size > 264 or lbs_weight > 67950 or package.girth > 391:\n",
    "            base_fee = 999999\n",
    "            break\n",
    "        base_fee += df1[df1['g'] >= lbs_weight]['费用'].iloc[0]\n",
    "        if package.fst_size >= 116 or package.sed_size >= 71 or package.girth >= 251:\n",
    "            other_fee += 16.3\n",
    "            order_type1 += '超长'  # 超长费\n",
    "        if package.weight >= 21000 and package.fst_size < 238 and package.girth < 315:\n",
    "            other_fee += 25.5\n",
    "            order_type1 += '超重'  # 超重费:\n",
    "        if package.fst_size >= 238 or package.girth >= 315:\n",
    "            other_fee += 118.7\n",
    "            order_type1 += '大包裹费'  # 大包裹费\n",
    "    express_fee = base_fee + other_fee\n",
    "    \n",
    "    # 卡派（步长为3）\n",
    "    ltl_base = 0\n",
    "    ltl_fee = 0\n",
    "    count1 = 0\n",
    "    count2 = 0\n",
    "    count3 = 0\n",
    "    count4 = 0\n",
    "    order_type2 = '卡派'\n",
    "    order_other_type1 = ''\n",
    "    order_other_type2 = ''\n",
    "    order_other_type3 = ''\n",
    "    order_other_type4 = ''\n",
    "    order_ltl_oversize = 0\n",
    "    order_ltl_overweight1 = 0\n",
    "    order_ltl_overweight2 = 0\n",
    "    order_ltl_overpackage = 0\n",
    "    sku_total_cubic_feet = 0\n",
    "    for package in packages:\n",
    "        cubic_feet= package.length * package.width * package.height / 1000000 * 35.3\n",
    "        sku_total_cubic_feet += cubic_feet\n",
    "        # 卡派额外费用\n",
    "        if package.fst_size>= 250:\n",
    "            count1 += 1\n",
    "            order_ltl_oversize = 118\n",
    "            order_other_type1 = '超长'\n",
    "        if package.weight >= 111000:\n",
    "            count2 += 1\n",
    "            order_ltl_overweight1 = 78\n",
    "            order_other_type2 = '超重'\n",
    "        if package.weight >= 130000:\n",
    "            count3 += 1\n",
    "            order_ltl_overweight2 = 30\n",
    "            order_other_type3 = '超重'\n",
    "        if package.fst_size >= 310:\n",
    "            count4 += 1\n",
    "            order_ltl_overpackage = 30\n",
    "            order_other_type4 = '大包裹'\n",
    "        order_type2 += order_other_type3 + order_other_type1 + order_other_type2 + order_other_type4\n",
    "\n",
    "    # 卡派基础费用 体积/1000000 *35.3\n",
    "    if sku_total_cubic_feet < 25:\n",
    "        ltl_base = round(163 / 0.45 / 2, 2)  # 181.11\n",
    "\n",
    "    elif sku_total_cubic_feet < 35:\n",
    "        ltl_base = round(180 / 0.45 / 2, 2)  # 200\n",
    "    else:\n",
    "\t        # 大于一个立方的（35立方英尺） 按照每立方英尺*5美金\n",
    "        # 最低为190美金\n",
    "        ltl_base = round(max(190, 5 * sku_total_cubic_feet) / 0.45 / 2)\n",
    "\n",
    "        \n",
    "    ltl_fee = math.ceil(count1 / 3) * order_ltl_oversize + math.ceil(count2 / 3) * order_ltl_overweight1 + math.ceil(\n",
    "        count3 / 3) * order_ltl_overweight2 + math.ceil(count4 / 3) * order_ltl_overpackage + ltl_base\n",
    "\n",
    "    if ltl_fee < express_fee:\n",
    "        order_fee = ltl_fee\n",
    "        order_type = order_type2\n",
    "    else:\n",
    "       order_fee = express_fee\n",
    "       order_type = order_type1\n",
    "    return order_fee, order_type\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "美国2024版订单物流费"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 英国海运订单费用,返回单个sku的订单费用和订单类型\n",
    "def uk_ocean_order_price(packages):\n",
    "    # 计算uk经济直达费用\n",
    "    order_fee = 0\n",
    "    express_fee = 0\n",
    "    order_type1 = ''  # 订单类型\n",
    "    ltl_fee = 0\n",
    "    if packages is None or len(packages) == 0:\n",
    "        return 0, '包裹数为0'\n",
    "    num = len(packages)\n",
    "    if num > 30:\n",
    "        return 0, '包裹数超过30'\n",
    "    for package in packages:\n",
    "        base_fee = 0\n",
    "        other_fee1 = 0\n",
    "        other_fee2 = 0\n",
    "        girth = package.girth\n",
    "        if package.fst_size <= 90 and package.sed_size <= 50 and package.trd_size <= 50 and package.weight <= 29000:\n",
    "            base_fee = 2.5\n",
    "        elif package.fst_size <= 165 and package.weight <= 39000:\n",
    "            base_fee = 4.5\n",
    "            if package.weight >= 29000:\n",
    "                other_fee1 = 17.8  # 超重费\n",
    "                order_type1 += '超重'\n",
    "            if package.fst_size > 95 or package.sed_size > 55 or package.trd_size > 55:\n",
    "                other_fee2 = 12.7  # 超长费\n",
    "                order_type1 += '超长'\n",
    "        elif package.fst_size <= 290 and package.weight <= 69000 and girth <= 410:\n",
    "            if package.weight <= 29000:\n",
    "                base_fee = (7 * 9 / 7) / 0.45\n",
    "            elif 29000 < package.weight <= 49000:\n",
    "                base_fee = (17.5 * 9 / 7) / 0.45\n",
    "            elif 49000 < package.weight <= 69000:\n",
    "                base_fee = (28 * 9 / 7) / 0.45\n",
    "            order_type1 += '大包裹'\n",
    "        else:\n",
    "            base_fee = 999999\n",
    "        express_fee += (base_fee + other_fee1 + other_fee2)\n",
    "    express_fee = round(express_fee, 2)\n",
    "\n",
    "    # 卡派 主计费实重，辅计费抛重\n",
    "    order_type2 = '卡派'\n",
    "    sku_total_cubic_feet = 0\n",
    "    for package in packages:\n",
    "        cubic_feet= package.length * package.width * package.height / 6000\n",
    "        sku_total_cubic_feet += cubic_feet\n",
    "        if package.length >310:\n",
    "            return 999999,'包裹超尺寸'\n",
    "    ltl_fee = max(151/0.45 - 2.4 /7 * sku_total_cubic_feet,2.5) \n",
    "\n",
    "    if express_fee <= ltl_fee:\n",
    "        order_fee = express_fee\n",
    "        order_type = order_type1\n",
    "    else:\n",
    "        order_fee = ltl_fee\n",
    "        order_type = order_type2\n",
    "    return round(order_fee,2), order_type\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "计算2024版本售价"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from utils.Package import Package, Package_group\n",
    "from sell.base_sell_price import SellPriceBase\n",
    "import re\n",
    "import json\n",
    "def call_sell_price(price, packages):\n",
    "    if packages is None:\n",
    "        return 0\n",
    "    litfad = SellPriceBase.litfad(packages, price,1)\n",
    "    # 修改版本,网站售价\n",
    "    sell_price = litfad.cal_sell_price()\n",
    "    return sell_price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "  0%|          | 0/40 [00:00<?, ?it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "110 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "  2%|▎         | 1/40 [00:00<00:19,  2.03it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "111 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "  5%|▌         | 2/40 [00:00<00:18,  2.09it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "112 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "  8%|▊         | 3/40 [00:01<00:17,  2.08it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "113 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 10%|█         | 4/40 [00:01<00:17,  2.02it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "114 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 12%|█▎        | 5/40 [00:02<00:17,  1.98it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "115 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 15%|█▌        | 6/40 [00:02<00:16,  2.05it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "116 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 18%|█▊        | 7/40 [00:03<00:15,  2.11it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "117 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 20%|██        | 8/40 [00:03<00:14,  2.14it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "118 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 22%|██▎       | 9/40 [00:04<00:14,  2.10it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "119 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n",
      "当前批次数据写入完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 25%|██▌       | 10/40 [00:34<04:47,  9.58s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "主表更新完成\n",
      "119 结束\n",
      "120 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 28%|██▊       | 11/40 [00:38<03:53,  8.07s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "当前批次数据写入完成\n",
      "主表更新完成\n",
      "120 结束\n",
      "121 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 30%|███       | 12/40 [00:41<03:01,  6.47s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "当前批次数据写入完成\n",
      "主表更新完成\n",
      "121 结束\n",
      "122 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 32%|███▎      | 13/40 [00:44<02:22,  5.28s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "当前批次数据写入完成\n",
      "主表更新完成\n",
      "122 结束\n",
      "123 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 35%|███▌      | 14/40 [00:47<02:00,  4.65s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "当前批次数据写入完成\n",
      "主表更新完成\n",
      "123 结束\n",
      "124 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 38%|███▊      | 15/40 [00:50<01:46,  4.27s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "当前批次数据写入完成\n",
      "主表更新完成\n",
      "124 结束\n",
      "125 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 40%|████      | 16/40 [00:56<01:53,  4.71s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "当前批次数据写入完成\n",
      "主表更新完成\n",
      "125 结束\n",
      "126 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n",
      "当前批次数据写入完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 42%|████▎     | 17/40 [01:44<06:46, 17.66s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "主表更新完成\n",
      "126 结束\n",
      "127 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n",
      "当前批次数据写入完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 45%|████▌     | 18/40 [02:37<10:24, 28.39s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "主表更新完成\n",
      "127 结束\n",
      "128 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n",
      "当前批次数据写入完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 48%|████▊     | 19/40 [03:17<11:05, 31.67s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "主表更新完成\n",
      "128 结束\n",
      "129 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n",
      "当前批次数据写入完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 50%|█████     | 20/40 [03:51<10:51, 32.59s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "主表更新完成\n",
      "129 结束\n",
      "130 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n",
      "当前批次数据写入完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 52%|█████▎    | 21/40 [04:34<11:16, 35.63s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "主表更新完成\n",
      "130 结束\n",
      "131 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n",
      "当前批次数据写入完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 55%|█████▌    | 22/40 [05:31<12:35, 41.97s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "主表更新完成\n",
      "131 结束\n",
      "132 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n",
      "当前批次数据写入完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 57%|█████▊    | 23/40 [06:13<11:56, 42.13s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "主表更新完成\n",
      "132 结束\n",
      "133 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n",
      "当前批次数据写入完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 60%|██████    | 24/40 [07:04<11:53, 44.57s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "主表更新完成\n",
      "133 结束\n",
      "134 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n",
      "当前批次数据写入完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 62%|██████▎   | 25/40 [07:50<11:18, 45.22s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "主表更新完成\n",
      "134 结束\n",
      "135 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n",
      "当前批次数据写入完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 65%|██████▌   | 26/40 [08:35<10:31, 45.09s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "主表更新完成\n",
      "135 结束\n",
      "136 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n",
      "当前批次数据写入完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 68%|██████▊   | 27/40 [09:22<09:52, 45.54s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "主表更新完成\n",
      "136 结束\n",
      "137 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 70%|███████   | 28/40 [09:26<06:37, 33.15s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "当前批次数据写入完成\n",
      "主表更新完成\n",
      "137 结束\n",
      "138 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n",
      "当前批次数据写入完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 72%|███████▎  | 29/40 [10:31<07:49, 42.68s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "主表更新完成\n",
      "138 结束\n",
      "139 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n",
      "当前批次数据写入完成\n",
      "主表更新完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 75%|███████▌  | 30/40 [11:49<08:53, 53.38s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "139 结束\n",
      "140 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n",
      "当前批次数据写入完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 78%|███████▊  | 31/40 [13:07<09:06, 60.74s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "主表更新完成\n",
      "140 结束\n",
      "141 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n",
      "当前批次数据写入完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 80%|████████  | 32/40 [13:17<06:04, 45.61s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "主表更新完成\n",
      "141 结束\n",
      "142 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n",
      "当前批次数据写入完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 82%|████████▎ | 33/40 [13:58<05:09, 44.25s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "主表更新完成\n",
      "142 结束\n",
      "143 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n",
      "当前批次数据写入完成\n",
      "主表更新完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 85%|████████▌ | 34/40 [15:06<05:07, 51.32s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "143 结束\n",
      "144 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n",
      "当前批次数据写入完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 88%|████████▊ | 35/40 [15:46<03:59, 47.85s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "主表更新完成\n",
      "144 结束\n",
      "145 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n",
      "当前批次数据写入完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 90%|█████████ | 36/40 [16:53<03:34, 53.61s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "主表更新完成\n",
      "145 结束\n",
      "146 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_34964\\4122513777.py:65: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  rows = df.to_dict(orient='records')  # 转为 list of dict\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "到这儿了\n",
      "到这儿了\n",
      "临时表清空完成\n",
      "当前批次数据写入完成\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 40/40 [17:10<00:00, 25.76s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "主表更新完成\n",
      "146 结束\n",
      "147 开始\n",
      "148 开始\n",
      "149 开始\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "from utils.gtools import MySQLconnect\n",
    "from tqdm import tqdm\n",
    "import pandas as pd\n",
    "from concurrent.futures import ThreadPoolExecutor\n",
    "def to_package(package_data):\n",
    "    packages = Package_group()\n",
    "    def extract_number(value):\n",
    "    # 提取字符串中的第一个数字\n",
    "        match = re.search(r\"[-+]?\\d*\\.\\d+|\\d+\", str(value))\n",
    "        return float(match.group()) if match else 0.0\n",
    "    package_dict = json.loads(package_data)\n",
    "    for key, package in package_dict.items():\n",
    "        package['长'] = extract_number(package['长'])\n",
    "        package['宽'] = extract_number(package['宽'])\n",
    "        package['高'] = extract_number(package['高'])\n",
    "        package['重量'] = extract_number(package['重量'])\n",
    "    \n",
    "        if package['长'] == 0 or package['宽'] == 0 or package['高'] == 0 or package['重量'] == 0:\n",
    "            return None\n",
    "        packages.add_package(Package(key,package['长'], package['宽'], package['高'], package['重量']))\n",
    "    return packages\n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    sql = \"\"\"SELECT\n",
    "\t* ,\n",
    "\tsku.`成本价`\n",
    "        FROM\n",
    "            dwd.dim_erp_sku_package_vol_info t1 \n",
    "            LEFT JOIN ods.stg_bayshop_litfad_sku sku ON t1.erp_sku = sku.SKU\n",
    "        WHERE\n",
    "            NOT EXISTS ( SELECT 1 FROM ads.sku_order_compare t2 WHERE t1.erp_sku = t2.SKU ) \n",
    "            AND id >= %s \n",
    "            AND id <= %s\n",
    "            AND `状态` = '启用'\"\"\"\n",
    "    \n",
    "    with MySQLconnect('ads') as db:\n",
    "        for i in tqdm(range(110,150)):\n",
    "            print(i,\"开始\")\n",
    "            dfsql = sql % (i*100000, (i+1)*100000-1)\n",
    "            df = pd.read_sql(dfsql, db.engine())\n",
    "            if len(df) == 0:\n",
    "                continue\n",
    "            # 包裹数据格式化\n",
    "            df['erp_package_vol'] = df.apply(lambda x: to_package(x['erp_package_vol']), axis=1)\n",
    "            # df['售价'] = df.apply(lambda x: call_sell_and_order_price(x['成本价'], x['erp_package_vol'], \"海运\")[0], axis=1)\n",
    "            # df[['美国海运','美国海运类型']] = df.apply(lambda x: us_ocean_order_price(x['erp_package_vol']), axis=1, result_type='expand')\n",
    "            # df[['英国海运','英国海运类型']] = df.apply(lambda x: uk_ocean_order_price(x['erp_package_vol']), axis=1, result_type='expand')\n",
    "            \n",
    "            def process_row(x):\n",
    "                # 单行处理逻辑\n",
    "                try:\n",
    "\n",
    "                    sell_price = call_sell_price(x['成本价'], x['erp_package_vol'])\n",
    "                    # print(\"售价计算完成\")\n",
    "                    us_price, us_type = us_ocean_order_price(x['erp_package_vol'])\n",
    "                    # print(\"美国海运计算完成\")\n",
    "                    uk_price, uk_type = uk_ocean_order_price(x['erp_package_vol'])\n",
    "                    # print(\"英国海运计算完成\")\n",
    "                    return [round(sell_price, 2), us_price, us_type, uk_price, uk_type]\n",
    "                except Exception as e:\n",
    "                    return [None, None, None, None, None]\n",
    "\n",
    "            # 用 dict 更稳妥\n",
    "            rows = df.to_dict(orient='records')  # 转为 list of dict\n",
    "            print(\"到这儿了\")\n",
    "            with ThreadPoolExecutor(max_workers=50) as executor:\n",
    "                results = list(executor.map(process_row, rows))\n",
    "            # 组装结果\n",
    "            print(\"到这儿了\")\n",
    "            result_df = pd.DataFrame(results, columns=['售价', '美国海运', '美国海运类型', '英国海运', '英国海运类型'])\n",
    "            df = pd.concat([df.reset_index(drop=True), result_df], axis=1)\n",
    "\n",
    "            # 清空临时表\n",
    "            db.cur.execute(\"TRUNCATE TABLE ads.temp_sku_order_compare;\")\n",
    "            print(\"临时表清空完成\")\n",
    "\n",
    "            # 组装需要输出的字段\n",
    "            df = df.rename(columns={\n",
    "                'erp_package_vol':'包裹数据'\n",
    "            })\n",
    "\n",
    "            columns_needed = ['SKU', '售价','包裹数据', '美国海运', '美国海运类型', '英国海运', '英国海运类型']\n",
    "            df_out = df[columns_needed]\n",
    "            # 写入当前批次数据\n",
    "            df_out.to_sql(\n",
    "                \"temp_sku_order_compare\",\n",
    "                db.eng,\n",
    "                if_exists='append',\n",
    "                index=False,\n",
    "                method='multi',\n",
    "                chunksize=500  # 分批写入\n",
    "            )\n",
    "\n",
    "            print(\"当前批次数据写入完成\")\n",
    "            # 更新主表\n",
    "            update_sql = \"\"\"\n",
    "            REPLACE INTO ads.sku_order_compare SELECT * FROM ads.temp_sku_order_compare\n",
    "            \"\"\"\n",
    "            db.cur.execute(update_sql)\n",
    "            print(\"主表更新完成\")\n",
    "            db.con.commit()\n",
    "            print(i,\"结束\")"
   ]
  }
 ],
 "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
}