{ "cells": [ { "metadata": {}, "cell_type": "markdown", "source": "## 优惠券发放", "id": "9fa677e63a6c72e6" }, { "cell_type": "code", "id": "initial_id", "metadata": { "collapsed": true, "ExecuteTime": { "end_time": "2025-08-30T08:53:06.924141Z", "start_time": "2025-08-30T08:52:56.790166Z" } }, "source": [ "import time\n", "\n", "import requests\n", "import pandas as pd\n", "from collections import defaultdict\n", "from IPython.display import display\n", "from tqdm import tqdm\n", "\n", "cookies = {\n", " 'marketingSESSIONID': 'f25482cc-e70f-40e6-a17a-1e1ec31960c0',\n", " 'erpLanguage': 'zh-CN',\n", " 'prodOrg': '15983224677615038465',\n", " 'unp': '15983224680525881432',\n", " 'un': '15983224680525881432',\n", " '_up': '-NillNN-qyBEJ--t3vnSknvoOF52xfKPsskD23s6WOJVXvDFpJvQjaZJ9Q3d-WrAAGgt60MgQHajHWBHMKKxj0CuWypi1JgKCFP1EPEk-HbqEfURqoMh1wcK-fdRv-ZNHu3M-GTc15CyF3WsqOVVjOkfmF7COhBtrEj97Rrnq7u4ZTQ.',\n", " 'sensorsdata2015jssdkcross': '%7B%22distinct_id%22%3A%2215983224680525881432%22%2C%22first_id%22%3A%22198a89fdaa54f-0a897244f8104b8-4c657b58-2073600-198a89fdaa61b71%22%2C%22props%22%3A%7B%22%24latest_traffic_source_type%22%3A%22%E7%9B%B4%E6%8E%A5%E6%B5%81%E9%87%8F%22%2C%22%24latest_search_keyword%22%3A%22%E6%9C%AA%E5%8F%96%E5%88%B0%E5%80%BC_%E7%9B%B4%E6%8E%A5%E6%89%93%E5%BC%80%22%2C%22%24latest_referrer%22%3A%22%22%7D%2C%22%24device_id%22%3A%22198a89fdaa54f-0a897244f8104b8-4c657b58-2073600-198a89fdaa61b71%22%7D',\n", " 'tmall': 'false',\n", " 'Hm_lvt_25f5e7a3a5dbb293d7dd35d5f1be8d0a': '1756257166,1756352022,1756431231,1756538308',\n", " 'Hm_lpvt_25f5e7a3a5dbb293d7dd35d5f1be8d0a': '1756538308',\n", " 'HMACCOUNT': '55F2182717FD6AE6',\n", "}\n", "\n", "headers = {\n", " 'accept': 'application/json, text/plain, */*',\n", " 'accept-language': 'zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6',\n", " 'priority': 'u=1, i',\n", " 'referer': 'https://yunxiu.f6car.cn/erp/view/index.html',\n", " 'sec-ch-ua': '\"Not;A=Brand\";v=\"99\", \"Microsoft Edge\";v=\"139\", \"Chromium\";v=\"139\"',\n", " 'sec-ch-ua-mobile': '?0',\n", " 'sec-ch-ua-platform': '\"Windows\"',\n", " 'sec-fetch-dest': 'empty',\n", " 'sec-fetch-mode': 'cors',\n", " 'sec-fetch-site': 'same-origin',\n", " 'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36 Edg/139.0.0.0',\n", " # 'cookie': 'marketingSESSIONID=f25482cc-e70f-40e6-a17a-1e1ec31960c0; erpLanguage=zh-CN; prodOrg=15983224677615038465; unp=15983224680525881432; un=15983224680525881432; _up=-NillNN-qyBEJ--t3vnSknvoOF52xfKPsskD23s6WOJVXvDFpJvQjaZJ9Q3d-WrAAGgt60MgQHajHWBHMKKxj0CuWypi1JgKCFP1EPEk-HbqEfURqoMh1wcK-fdRv-ZNHu3M-GTc15CyF3WsqOVVjOkfmF7COhBtrEj97Rrnq7u4ZTQ.; sensorsdata2015jssdkcross=%7B%22distinct_id%22%3A%2215983224680525881432%22%2C%22first_id%22%3A%22198a89fdaa54f-0a897244f8104b8-4c657b58-2073600-198a89fdaa61b71%22%2C%22props%22%3A%7B%22%24latest_traffic_source_type%22%3A%22%E7%9B%B4%E6%8E%A5%E6%B5%81%E9%87%8F%22%2C%22%24latest_search_keyword%22%3A%22%E6%9C%AA%E5%8F%96%E5%88%B0%E5%80%BC_%E7%9B%B4%E6%8E%A5%E6%89%93%E5%BC%80%22%2C%22%24latest_referrer%22%3A%22%22%7D%2C%22%24device_id%22%3A%22198a89fdaa54f-0a897244f8104b8-4c657b58-2073600-198a89fdaa61b71%22%7D; tmall=false; Hm_lvt_25f5e7a3a5dbb293d7dd35d5f1be8d0a=1756257166,1756352022,1756431231,1756538308; Hm_lpvt_25f5e7a3a5dbb293d7dd35d5f1be8d0a=1756538308; HMACCOUNT=55F2182717FD6AE6',\n", "}\n", "\n", "\n", "# 1. 数据转换函数\n", "def convert_coupon_response_to_request(response_data, new_start_time, new_end_time):\n", " \"\"\"将API响应转换为请求格式\"\"\"\n", " template_info = response_data.get('couponTemplateInfo', {})\n", "\n", " request_data = {\n", " 'orgId': template_info.get('orgId'),\n", " 'couponName': template_info.get('couponName'),\n", " 'couponDesc': template_info.get('couponDesc'),\n", " 'totalStock': str(template_info.get('totalStock', 0)),\n", " 'takeQuota': str(template_info.get('takeQuota', 0)),\n", " 'conditionType': template_info.get('conditionType', 0),\n", " 'validityType': template_info.get('validityType', 0),\n", " 'validityBegin': new_start_time,\n", " 'validityEnd': new_end_time,\n", " 'orgRangeType': template_info.get('orgRangeType', 0),\n", " 'goodsRangeType': template_info.get('goodsRangeType', 0),\n", " }\n", "\n", " # 处理数值字段\n", " try:\n", " request_data['couponValue'] = str(float(template_info.get('couponValue', \"0.0\")))\n", " except (ValueError, TypeError):\n", " request_data['couponValue'] = \"0\"\n", "\n", " # 处理可选字段\n", " for field in ['conditionValue', 'validityDays']:\n", " value = template_info.get(field)\n", " request_data[field] = str(value) if value is not None else None\n", "\n", " # 处理列表字段\n", " list_mappings = {\n", " 'channelList': ('channelList', 'channelCode'),\n", " 'useSceneCodeList': ('useSceneList', 'sceneCode'),\n", " 'orgList': ('orgList', 'orgId') if template_info.get('orgRangeType', 0) == 0 else (None, None)\n", " }\n", "\n", " for key, (source, attr) in list_mappings.items():\n", " request_data[key] = [item.get(attr) for item in response_data.get(source, [])] if source else []\n", "\n", " # 处理商品数据\n", " if template_info.get('goodsRangeType', 0) == 0:\n", " for list_type in ['partCategoryList', 'partBrandList']:\n", " request_data[list_type] = [\n", " {k: v for k, v in item.items() if v is not None}\n", " for item in response_data.get(list_type, [])\n", " ]\n", " else:\n", " request_data.update({'partCategoryList': [], 'partBrandList': []})\n", "\n", " return request_data\n", "\n", "\n", "# 2. 主流程\n", "# 读取Excel数据\n", "input_path = r\"C:\\Users\\zy187\\Desktop\\钉钉文件\\优惠券发送正式-0830-1510.xlsx\"\n", "df = pd.read_excel(input_path, sheet_name='Sheet2')\n", "display(df.head(2)) # 预览数据\n", "\n", "# 获取所有现有优惠券\n", "print(\"正在获取现有优惠券列表...\")\n", "all_coupon_list = []\n", "params = {\n", " 'couponName': '',\n", " 'statusList': '',\n", " 'orgIdList': '',\n", " 'pageNo': '1',\n", " 'pageSize': '100',\n", " 'isDeleted': '0',\n", " 'sorts': '',\n", "}\n", "\n", "response = requests.get(\n", " 'https://yunxiu.f6car.cn/marketing/couponTemplate/paging',\n", " params=params,\n", " cookies=cookies,\n", " headers=headers,\n", ")\n", "total = int(response.json()['data']['total'])\n", "total_page = (total // 100) + 1\n", "\n", "for i in range(1, total_page + 1):\n", " params['pageNo'] = str(i)\n", " response = requests.get(\n", " 'https://yunxiu.f6car.cn/marketing/couponTemplate/paging',\n", " params=params,\n", " cookies=cookies,\n", " headers=headers,\n", " )\n", " all_coupon_list.extend([\n", " (c.get('id'), c.get('couponName'))\n", " for c in response.json()['data'].get(\"data\", [])\n", " ])\n", "\n", "# 分类统计优惠券\n", "print(\"正在分类优惠券...\")\n", "coupon_stats = pd.DataFrame(all_coupon_list, columns=['coupon_id', 'coupon_name'])\n", "coupon_counts = coupon_stats['coupon_name'].value_counts().to_dict()\n", "\n", "# 创建分类DataFrame\n", "df['出现次数'] = df['优惠券名称'].map(coupon_counts).fillna(0)\n", "create_coupon_df = df[df['出现次数'] == 1].copy()\n", "not_found_df = df[df['出现次数'] == 0].copy()\n", "duplicate_df = df[df['出现次数'] > 1].copy()\n", "\n", "# 添加ID映射\n", "id_mapping = coupon_stats.drop_duplicates('coupon_name', keep=False).set_index('coupon_name')['coupon_id']\n", "create_coupon_df['coupon_id'] = create_coupon_df['优惠券名称'].map(id_mapping)\n", "\n", "# 保存分类结果\n", "output_path = r\"D:\\Idea Project\\F6+宜搭+其它(1)\\张阳脚本\\文件输出\"\n", "not_found_df.to_excel(f\"{output_path}/未找到优惠券.xlsx\", index=False)\n", "duplicate_df.to_excel(f\"{output_path}/重复优惠券.xlsx\", index=False)\n", "\n", "# 处理时间字段\n", "print(\"正在处理时间字段...\")\n", "create_coupon_df[\"新开始时间\"] = create_coupon_df[\"新开始时间\"].astype('int64') // 10 ** 6\n", "create_coupon_df[\"新结束时间\"] = create_coupon_df[\"新结束时间\"].astype('int64') // 10 ** 6\n", "\n", "# 创建新优惠券\n", "print(\"开始创建新优惠券...\")\n", "df_multi_unique = create_coupon_df.drop_duplicates(subset=['优惠券名称', '新开始时间', \"新结束时间\", \"门店id\"]) # 去重\n", "new_id_mapping = {}\n", "results = []\n", "for _, row in df_multi_unique.iterrows():\n", " try:\n", " # 获取模板详情\n", " detail_url = f\"https://yunxiu.f6car.cn/marketing/couponTemplate/detail/{row['coupon_id']}\"\n", " detail_resp = requests.get(detail_url, cookies=cookies, headers=headers)\n", "\n", " # 转换并创建\n", " json_data = convert_coupon_response_to_request(\n", " detail_resp.json()['data'],\n", " row['新开始时间'],\n", " row['新结束时间']\n", " )\n", " create_resp = requests.post(\n", " 'https://yunxiu.f6car.cn/marketing/couponTemplate/add',\n", " cookies=cookies,\n", " headers=headers,\n", " json=json_data\n", " )\n", " new_id = create_resp.json().get('data')\n", " # 将新ID添加到映射中\n", " unique_key = (row['优惠券名称'], row['新开始时间'], row['新结束时间'], row['门店id'])\n", " new_id_mapping[unique_key] = new_id\n", "\n", " # 启用优惠券\n", " if new_id:\n", " enable_resp = requests.put(\n", " 'https://yunxiu.f6car.cn/marketing/couponTemplate/status',\n", " cookies=cookies,\n", " headers=headers,\n", " json={'id': new_id, 'status': 1}\n", " )\n", " status = '成功' if enable_resp.status_code == 200 else '启用失败'\n", " else:\n", " status = '创建失败'\n", "\n", " results.append({\n", " '原名称': row['优惠券名称'],\n", " '原ID': row['coupon_id'],\n", " '新ID': new_id,\n", " '状态': status,\n", " '开始时间': pd.to_datetime(row['新开始时间'], unit='ms'),\n", " '结束时间': pd.to_datetime(row['新结束时间'], unit='ms')\n", " })\n", "\n", " except Exception as e:\n", " results.append({\n", " '原名称': row['优惠券名称'],\n", " '原ID': row['coupon_id'],\n", " '新ID': None,\n", " '状态': f'错误: {str(e)}',\n", " '开始时间': None,\n", " '结束时间': None\n", " })\n", "\n", "\n", "# 将新ID回写到create_coupon_df中 - 使用唯一标识进行映射\n", "def get_new_id(row):\n", " unique_key = (row['优惠券名称'], row['新开始时间'], row['新结束时间'], row['门店id'])\n", " return new_id_mapping.get(unique_key)\n", "\n", "\n", "create_coupon_df['new_id'] = create_coupon_df.apply(get_new_id, axis=1)\n", "create_coupon_df.to_excel(f\"{output_path}/已创建优惠券.xlsx\", index=False)\n", "\n", "print(\"正在获取客户列表...\")\n", "params = {\n", " 'pageSize': '100',\n", " 'pageNo': '1',\n", "}\n", "\n", "response = requests.get(\n", " 'https://yunxiu.f6car.cn/member/customer/listForPermission',\n", " params=params,\n", " cookies=cookies,\n", " headers=headers,\n", ")\n", "\n", "customer_list = []\n", "total = response.json()['data']['total']\n", "total = int(total)\n", "total_page = total // 100 + 1\n", "for i in tqdm(range(1, total_page + 1), desc=\"获取客户列表\"):\n", " params['pageNo'] = i\n", " response = requests.get(\n", " 'https://yunxiu.f6car.cn/member/customer/listForPermission',\n", " params=params,\n", " cookies=cookies,\n", " headers=headers,\n", " )\n", " data_list = response.json()['data'][\"data\"]\n", " for item in data_list:\n", " customer_list.append({\n", " 'name': item['name'],\n", " 'idCustomer': item['idCustomer'],\n", " 'cellPhone': item['cellPhone'],\n", " })\n", "\n", "\n", "def create_coupon_distribution_request(customer_list, create_coupon_df):\n", " # 定义手机号清洗函数 - 确保正确处理各种格式\n", " def standardize_phone(phone):\n", " if pd.isna(phone):\n", " return None\n", " phone = str(phone).strip().replace(\" \", \"\").replace(\"-\", \"\")\n", " # 处理科学计数法问题（如1.75828e+10）\n", " if 'e+' in phone.lower():\n", " try:\n", " phone = \"{:.0f}\".format(float(phone))\n", " except:\n", " return None\n", " # 只保留数字\n", " phone = ''.join(c for c in phone if c.isdigit())\n", " # 处理86开头的情况\n", " if len(phone) > 11 and phone.startswith('86'):\n", " phone = phone[2:]\n", " return phone if len(phone) == 11 else None\n", "\n", " # 预处理数据\n", " create_coupon_df['clean_phone'] = create_coupon_df['手机号'].apply(standardize_phone)\n", " clean_customer_list = []\n", " for customer in customer_list:\n", " phone = standardize_phone(customer['cellPhone'])\n", " if phone:\n", " clean_customer_list.append({\n", " **customer,\n", " 'clean_phone': phone\n", " })\n", "\n", " # 调试：打印预处理后的数据\n", " print(\"\\n预处理后的手机号示例：\")\n", " print(\"Excel中的手机号:\", create_coupon_df['clean_phone'].head().tolist())\n", " print(\"客户列表手机号:\", [c['clean_phone'] for c in clean_customer_list[:5]])\n", "\n", " # 按手机号和门店ID分组优惠券\n", " phone_org_to_coupons = defaultdict(list)\n", "\n", " for _, row in create_coupon_df.iterrows():\n", " phone = row['clean_phone']\n", " org_id = row['门店id']\n", " if pd.notna(phone) and pd.notna(org_id) and pd.notna(row['new_id']):\n", " key = (str(phone), str(org_id)) # 确保都是字符串类型\n", " phone_org_to_coupons[key].append({\n", " 'coupon_name': row['优惠券名称'],\n", " 'coupon_id': str(row['new_id']),\n", " 'counts': int(row['客户发券数量']) if pd.notna(row['客户发券数量']) else 1\n", " })\n", "\n", " # 调试：打印分组结果\n", " print(\"\\n分组结果（前5个）：\")\n", " for (phone, org_id), coupons in list(phone_org_to_coupons.items())[:5]:\n", " print(f\"手机号: {phone}, 门店ID: {org_id}\")\n", " for coupon in coupons:\n", " print(f\" - {coupon['coupon_name']} (ID: {coupon['coupon_id']}, 数量: {coupon['counts']})\")\n", "\n", " # 创建请求体\n", " request_bodies = []\n", " matched_count = 0\n", "\n", " # 构建手机号到客户的映射\n", " phone_to_customers = defaultdict(list)\n", " for customer in clean_customer_list:\n", " phone_to_customers[customer['clean_phone']].append(customer)\n", "\n", " # 匹配逻辑\n", " for (phone, org_id), coupons in phone_org_to_coupons.items():\n", " if phone in phone_to_customers:\n", " matched_count += 1\n", " customers = phone_to_customers[phone]\n", "\n", " # 为每个匹配的客户创建请求\n", " for customer in customers:\n", " request_body = {\n", " \"takeCouponMemo\": \"\",\n", " \"remark\": \",\".join(f\"{c['coupon_name']}(x{c['counts']})\" for c in coupons),\n", " \"orgId\": \"15983224677615038465\",\n", " \"couponTakeRequiredTemplateParamList\": [\n", " {\"couponTemplateId\": c['coupon_id'], \"quantity\": c['counts']}\n", " for c in coupons\n", " ],\n", " \"operationParam\": {\n", " \"couponTemplateIdList\": list({c['coupon_id'] for c in coupons})\n", " },\n", " \"batchOperateTaskDataList\": [{\n", " \"dataId\": customer['idCustomer'],\n", " \"dataName\": customer['name'],\n", " \"dataInfo\": customer['cellPhone']\n", " }]\n", " }\n", " request_bodies.append(request_body)\n", "\n", " print(f\"\\n匹配统计：\")\n", " print(f\"唯一客户手机号数量: {len(phone_to_customers)}\")\n", " print(f\"匹配到的优惠券组数: {len(phone_org_to_coupons)}\")\n", " print(f\"成功匹配组数: {matched_count}\")\n", " print(f\"生成的请求数量: {len(request_bodies)}\")\n", "\n", " return request_bodies\n", "\n", "\n", "# 生成请求体\n", "request_bodies = create_coupon_distribution_request(customer_list, create_coupon_df)\n", "\n", "# 发送请求\n", "if request_bodies:\n", " print(\"\\n开始发放优惠券...\")\n", " success_count = 0\n", " for i, body in enumerate(tqdm(request_bodies, desc=\"发放进度\")):\n", " print(body)\n", " try:\n", " resp = requests.post(\n", " 'https://yunxiu.f6car.cn/macan/batch/operate/singleSend/coupon/from/customer',\n", " cookies=cookies,\n", " headers=headers,\n", " json=body,\n", " timeout=10\n", " )\n", " print(resp.text)\n", " if resp.status_code == 200:\n", " success_count += 1\n", " else:\n", " print(f\"请求失败 (状态码 {resp.status_code}): {resp.text}\")\n", " except Exception as e:\n", " print(f\"请求异常: {str(e)}\")\n", "\n", " print(f\"\\n发放完成：成功 {success_count}/{len(request_bodies)}\")\n", "else:\n", " print(\"\\n没有需要发放的优惠券\")\n", "\n", "# print(\"删除无用优惠券...\")\n", "# delete_list = []\n", "# for index, item in create_coupon_df.iterrows():\n", "# delete_list.append(item['new_id'])\n", "# set(delete_list)\n", "# for item in set(delete_list):\n", "# response = requests.delete(f'https://yunxiu.f6car.cn/marketing/couponTemplate/delete/{item}', cookies=cookies,\n", "# headers=headers)\n", "\n", "# 显示处理结果\n", "result_df = pd.DataFrame(results)\n", "result_df.to_excel(f\"{output_path}/处理结果.xlsx\", index=False)\n", "print(\"处理完成！结果如下：\")\n", "display(result_df)\n", "print(\"\\n检查优惠券创建结果:\")\n", "print(create_coupon_df[['优惠券名称', 'new_id', '门店id', 'clean_phone']].head())\n", "print(\"new_id 为空的数量:\", create_coupon_df['new_id'].isna().sum())\n", "print(\"\\n检查门店ID:\")\n", "print(\"门店ID类型:\", create_coupon_df['门店id'].apply(type).unique())\n", "print(\"门店ID示例:\", create_coupon_df['门店id'].head().tolist())\n", "# 检查哪些行因条件过滤被丢弃\n", "filtered_rows = create_coupon_df[\n", " (create_coupon_df['clean_phone'].isna()) |\n", " (create_coupon_df['门店id'].isna()) |\n", " (create_coupon_df['new_id'].isna())\n", " ]\n", "print(\"\\n被过滤的行数:\", len(filtered_rows))\n", "if not filtered_rows.empty:\n", " print(filtered_rows[['优惠券名称', 'clean_phone', '门店id', 'new_id']].head())\n", "# 显示各类统计\n", "print(\"\\n分类统计：\")\n", "stats = {\n", " '唯一优惠券': len(create_coupon_df),\n", " '未找到优惠券': len(not_found_df),\n", " '重复优惠券': len(duplicate_df),\n", " '成功创建': sum(result_df['状态'] == '成功'),\n", " '创建失败': len(result_df) - sum(result_df['状态'] == '成功')\n", "}\n", "\n", "display(pd.DataFrame.from_dict(stats, orient='index', columns=['数量']))" ], "outputs": [ { "data": { "text/plain": [ " 优惠券名称 新开始时间 新结束时间 手机号 客户发券数量 门店id\n", "0 亲友保养卡 2025-08-30 2030-08-30 10024094861 2 100000001" ], "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
优惠券名称新开始时间新结束时间手机号客户发券数量门店id
0亲友保养卡2025-08-302030-08-30100240948612100000001
\n", "
" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "正在获取现有优惠券列表...\n", "正在分类优惠券...\n", "正在处理时间字段...\n", "开始创建新优惠券...\n", "正在获取客户列表...\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "获取客户列表: 100%|██████████| 38/38 [00:07<00:00, 4.93it/s]\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "\n", "预处理后的手机号示例：\n", "Excel中的手机号: ['10024094861']\n", "客户列表手机号: ['20000000305', '20000000304', '20000000303', '20000000302', '20000000301']\n", "\n", "分组结果（前5个）：\n", "手机号: 10024094861, 门店ID: 100000001\n", " - 亲友保养卡 (ID: 186322, 数量: 2)\n", "\n", "匹配统计：\n", "唯一客户手机号数量: 3715\n", "匹配到的优惠券组数: 1\n", "成功匹配组数: 1\n", "生成的请求数量: 1\n", "\n", "开始发放优惠券...\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "发放进度: 100%|██████████| 1/1 [00:00<00:00, 6.78it/s]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "{'takeCouponMemo': '', 'remark': '亲友保养卡(x2)', 'orgId': '15983224677615038465', 'couponTakeRequiredTemplateParamList': [{'couponTemplateId': '186322', 'quantity': 2}], 'operationParam': {'couponTemplateIdList': ['186322']}, 'batchOperateTaskDataList': [{'dataId': '12092452697920270363', 'dataName': '海', 'dataInfo': '10024094861'}]}\n", "{\"code\":400,\"msg\":\"已有进行中的任务，请稍后重试\"}\n", "\n", "发放完成：成功 1/1\n", "处理完成！结果如下：\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\n" ] }, { "data": { "text/plain": [ " 原名称 原ID 新ID 状态 开始时间 结束时间\n", "0 亲友保养卡 186171 186322 成功 2025-08-30 2030-08-30" ], "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
原名称原ID新ID状态开始时间结束时间
0亲友保养卡186171186322成功2025-08-302030-08-30
\n", "
" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "\n", "检查优惠券创建结果:\n", " 优惠券名称 new_id 门店id clean_phone\n", "0 亲友保养卡 186322 100000001 10024094861\n", "new_id 为空的数量: 0\n", "\n", "检查门店ID:\n", "门店ID类型: []\n", "门店ID示例: [100000001]\n", "\n", "被过滤的行数: 0\n", "\n", "分类统计：\n" ] }, { "data": { "text/plain": [ " 数量\n", "唯一优惠券 1\n", "未找到优惠券 0\n", "重复优惠券 0\n", "成功创建 1\n", "创建失败 0" ], "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
数量
唯一优惠券1
未找到优惠券0
重复优惠券0
成功创建1
创建失败0
\n", "
" ] }, "metadata": {}, "output_type": "display_data" } ], "execution_count": 33 }, { "metadata": { "ExecuteTime": { "end_time": "2025-08-30T08:52:51.249957Z", "start_time": "2025-08-30T08:52:51.064373Z" } }, "cell_type": "code", "source": [ "print(\"删除无用优惠券...\")\n", "delete_list = []\n", "for index, item in create_coupon_df.iterrows():\n", " delete_list.append(item['new_id'])\n", "set(delete_list)\n", "for item in set(delete_list):\n", " response = requests.delete(f'https://yunxiu.f6car.cn/marketing/couponTemplate/delete/{item}', cookies=cookies,\n", " headers=headers)" ], "id": "7844c4495e7cbf2c", "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "删除无用优惠券...\n" ] } ], "execution_count": 32 }, { "metadata": { "ExecuteTime": { "end_time": "2025-08-30T07:46:26.980129Z", "start_time": "2025-08-30T07:46:25.536604Z" } }, "cell_type": "code", "source": [ "import requests\n", "import pandas as pd\n", "from collections import defaultdict\n", "from IPython.display import display\n", "from tqdm import tqdm\n", "\n", "import requests\n", "\n", "cookies = {\n", " 'portalSESSIONID': 'dc32262b-bda6-4234-b3c5-2b8c40d2fb60',\n", " 'erpLanguage': 'zh-CN',\n", " 'tmall': 'false',\n", " 'Hm_lvt_25f5e7a3a5dbb293d7dd35d5f1be8d0a': '1756257166,1756352022,1756431231,1756538308',\n", " 'HMACCOUNT': '55F2182717FD6AE6',\n", " 'prodOrg': '11240984669917217520',\n", " 'unp': '15865484595890778191',\n", " 'un': '15865484595890778191',\n", " '_up': '-NillNN-qyBEJ--t3vnSknvoOF53y_SJuMkA2n43U-daUfnArpjQjaZJ9Q3d-WrAAGgt60MgQHajHWBHMKKxj0CuWypi1JgKCFP1EPEk-HbqEfURqoIi1QsO-PFRv-ZNHu3M-GTc1p60EX-sq-RQgeIal1HLPxpurEj9mmjq8K60Nxc.',\n", " 'sensorsdata2015jssdkcross': '%7B%22distinct_id%22%3A%2215865484595890778191%22%2C%22first_id%22%3A%22198a89fdaa54f-0a897244f8104b8-4c657b58-2073600-198a89fdaa61b71%22%2C%22props%22%3A%7B%22%24latest_traffic_source_type%22%3A%22%E7%9B%B4%E6%8E%A5%E6%B5%81%E9%87%8F%22%2C%22%24latest_search_keyword%22%3A%22%E6%9C%AA%E5%8F%96%E5%88%B0%E5%80%BC_%E7%9B%B4%E6%8E%A5%E6%89%93%E5%BC%80%22%2C%22%24latest_referrer%22%3A%22%22%7D%2C%22%24device_id%22%3A%22198a89fdaa54f-0a897244f8104b8-4c657b58-2073600-198a89fdaa61b71%22%7D',\n", " 'Hm_lpvt_25f5e7a3a5dbb293d7dd35d5f1be8d0a': '1756539037',\n", "}\n", "\n", "headers = {\n", " 'accept': 'application/json, text/plain, */*',\n", " 'accept-language': 'zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6',\n", " 'priority': 'u=1, i',\n", " 'referer': 'https://yunxiu.f6car.cn/erp/view/index.html',\n", " 'sec-ch-ua': '\"Not;A=Brand\";v=\"99\", \"Microsoft Edge\";v=\"139\", \"Chromium\";v=\"139\"',\n", " 'sec-ch-ua-mobile': '?0',\n", " 'sec-ch-ua-platform': '\"Windows\"',\n", " 'sec-fetch-dest': 'empty',\n", " 'sec-fetch-mode': 'cors',\n", " 'sec-fetch-site': 'same-origin',\n", " 'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36 Edg/139.0.0.0',\n", " # 'cookie': 'portalSESSIONID=dc32262b-bda6-4234-b3c5-2b8c40d2fb60; erpLanguage=zh-CN; tmall=false; Hm_lvt_25f5e7a3a5dbb293d7dd35d5f1be8d0a=1756257166,1756352022,1756431231,1756538308; HMACCOUNT=55F2182717FD6AE6; prodOrg=11240984669917217520; unp=15865484595890778191; un=15865484595890778191; _up=-NillNN-qyBEJ--t3vnSknvoOF53y_SJuMkA2n43U-daUfnArpjQjaZJ9Q3d-WrAAGgt60MgQHajHWBHMKKxj0CuWypi1JgKCFP1EPEk-HbqEfURqoIi1QsO-PFRv-ZNHu3M-GTc1p60EX-sq-RQgeIal1HLPxpurEj9mmjq8K60Nxc.; sensorsdata2015jssdkcross=%7B%22distinct_id%22%3A%2215865484595890778191%22%2C%22first_id%22%3A%22198a89fdaa54f-0a897244f8104b8-4c657b58-2073600-198a89fdaa61b71%22%2C%22props%22%3A%7B%22%24latest_traffic_source_type%22%3A%22%E7%9B%B4%E6%8E%A5%E6%B5%81%E9%87%8F%22%2C%22%24latest_search_keyword%22%3A%22%E6%9C%AA%E5%8F%96%E5%88%B0%E5%80%BC_%E7%9B%B4%E6%8E%A5%E6%89%93%E5%BC%80%22%2C%22%24latest_referrer%22%3A%22%22%7D%2C%22%24device_id%22%3A%22198a89fdaa54f-0a897244f8104b8-4c657b58-2073600-198a89fdaa61b71%22%7D; Hm_lpvt_25f5e7a3a5dbb293d7dd35d5f1be8d0a=1756539037',\n", "}\n", "\n", "\n", "# 1. 数据转换函数\n", "def convert_coupon_response_to_request(response_data, new_start_time, new_end_time):\n", " \"\"\"将API响应转换为请求格式\"\"\"\n", " template_info = response_data.get('couponTemplateInfo', {})\n", "\n", " request_data = {\n", " 'orgId': template_info.get('orgId'),\n", " 'couponName': template_info.get('couponName'),\n", " 'couponDesc': template_info.get('couponDesc'),\n", " 'totalStock': str(template_info.get('totalStock', 0)),\n", " 'takeQuota': str(template_info.get('takeQuota', 0)),\n", " 'conditionType': template_info.get('conditionType', 0),\n", " 'validityType': template_info.get('validityType', 0),\n", " 'validityBegin': new_start_time,\n", " 'validityEnd': new_end_time,\n", " 'orgRangeType': template_info.get('orgRangeType', 0),\n", " 'goodsRangeType': template_info.get('goodsRangeType', 0),\n", " }\n", "\n", " # 处理数值字段\n", " try:\n", " request_data['couponValue'] = str(float(template_info.get('couponValue', \"0.0\")))\n", " except (ValueError, TypeError):\n", " request_data['couponValue'] = \"0\"\n", "\n", " # 处理可选字段\n", " for field in ['conditionValue', 'validityDays']:\n", " value = template_info.get(field)\n", " request_data[field] = str(value) if value is not None else None\n", "\n", " # 处理列表字段\n", " list_mappings = {\n", " 'channelList': ('channelList', 'channelCode'),\n", " 'useSceneCodeList': ('useSceneList', 'sceneCode'),\n", " 'orgList': ('orgList', 'orgId') if template_info.get('orgRangeType', 0) == 0 else (None, None)\n", " }\n", "\n", " for key, (source, attr) in list_mappings.items():\n", " request_data[key] = [item.get(attr) for item in response_data.get(source, [])] if source else []\n", "\n", " # 处理商品数据\n", " if template_info.get('goodsRangeType', 0) == 0:\n", " for list_type in ['partCategoryList', 'partBrandList']:\n", " request_data[list_type] = [\n", " {k: v for k, v in item.items() if v is not None}\n", " for item in response_data.get(list_type, [])\n", " ]\n", " else:\n", " request_data.update({'partCategoryList': [], 'partBrandList': []})\n", "\n", " return request_data\n", "\n", "\n", "# 2. 主流程\n", "# 读取Excel数据\n", "input_path = r\"D:\\Idea Project\\F6+宜搭+其它(1)\\张阳脚本\\文件输入\\优惠券新建模板.xlsx\"\n", "df = pd.read_excel(input_path, sheet_name='Sheet1')\n", "display(df.head(2)) # 预览数据\n", "\n", "# 获取所有现有优惠券\n", "print(\"正在获取现有优惠券列表...\")\n", "all_coupon_list = []\n", "params = {\n", " 'couponName': '',\n", " 'statusList': '',\n", " 'orgIdList': '',\n", " 'pageNo': '1',\n", " 'pageSize': '100',\n", " 'isDeleted': '0',\n", " 'sorts': '',\n", "}\n", "\n", "response = requests.get(\n", " 'https://yunxiu.f6car.cn/marketing/couponTemplate/paging',\n", " params=params,\n", " cookies=cookies,\n", " headers=headers,\n", ")\n", "total = int(response.json()['data']['total'])\n", "total_page = (total // 100) + 1\n", "\n", "for i in range(1, total_page + 1):\n", " params['pageNo'] = str(i)\n", " response = requests.get(\n", " 'https://yunxiu.f6car.cn/marketing/couponTemplate/paging',\n", " params=params,\n", " cookies=cookies,\n", " headers=headers,\n", " )\n", " all_coupon_list.extend([\n", " (c.get('id'), c.get('couponName'))\n", " for c in response.json()['data'].get(\"data\", [])\n", " ])\n", "\n", "# 分类统计优惠券\n", "print(\"正在分类优惠券...\")\n", "coupon_stats = pd.DataFrame(all_coupon_list, columns=['coupon_id', 'coupon_name'])\n", "coupon_counts = coupon_stats['coupon_name'].value_counts().to_dict()\n", "\n", "# 创建分类DataFrame\n", "df['出现次数'] = df['优惠券名称'].map(coupon_counts).fillna(0)\n", "create_coupon_df = df[df['出现次数'] == 1].copy()\n", "not_found_df = df[df['出现次数'] == 0].copy()\n", "duplicate_df = df[df['出现次数'] > 1].copy()\n", "\n", "# 添加ID映射\n", "id_mapping = coupon_stats.drop_duplicates('coupon_name', keep=False).set_index('coupon_name')['coupon_id']\n", "create_coupon_df['coupon_id'] = create_coupon_df['优惠券名称'].map(id_mapping)\n", "\n", "# 保存分类结果\n", "output_path = r\"D:\\Idea Project\\F6+宜搭+其它(1)\\张阳脚本\\文件输出\"\n", "not_found_df.to_excel(f\"{output_path}/未找到优惠券.xlsx\", index=False)\n", "duplicate_df.to_excel(f\"{output_path}/重复优惠券.xlsx\", index=False)\n", "\n", "# 处理时间字段\n", "print(\"正在处理时间字段...\")\n", "create_coupon_df[\"新开始时间\"] = create_coupon_df[\"新开始时间\"].astype('int64') // 10 ** 6\n", "create_coupon_df[\"新结束时间\"] = create_coupon_df[\"新结束时间\"].astype('int64') // 10 ** 6\n", "print(create_coupon_df)\n", "# 创建新优惠券\n", "print(\"开始创建新优惠券...\")\n", "df_multi_unique = create_coupon_df.drop_duplicates(subset=['优惠券名称', '新开始时间', \"新结束时间\", \"门店id\"]) # 去重\n", "new_id_mapping = {}\n", "results = []\n", "for _, row in df_multi_unique.iterrows():\n", " try:\n", " # 获取模板详情\n", " detail_url = f\"https://yunxiu.f6car.cn/marketing/couponTemplate/detail/{row['coupon_id']}\"\n", " detail_resp = requests.get(detail_url, cookies=cookies, headers=headers)\n", "\n", " # 转换并创建\n", " json_data = convert_coupon_response_to_request(\n", " detail_resp.json()['data'],\n", " row['新开始时间'],\n", " row['新结束时间']\n", " )\n", " create_resp = requests.post(\n", " 'https://yunxiu.f6car.cn/marketing/couponTemplate/add',\n", " cookies=cookies,\n", " headers=headers,\n", " json=json_data\n", " )\n", " new_id = create_resp.json().get('data')\n", " # 将新ID添加到映射中\n", " unique_key = (row['优惠券名称'], row['新开始时间'], row['新结束时间'], row['门店id'])\n", " new_id_mapping[unique_key] = new_id\n", "\n", " # 启用优惠券\n", " if new_id:\n", " enable_resp = requests.put(\n", " 'https://yunxiu.f6car.cn/marketing/couponTemplate/status',\n", " cookies=cookies,\n", " headers=headers,\n", " json={'id': new_id, 'status': 1}\n", " )\n", " status = '成功' if enable_resp.status_code == 200 else '启用失败'\n", " else:\n", " status = '创建失败'\n", "\n", " results.append({\n", " '原名称': row['优惠券名称'],\n", " '原ID': row['coupon_id'],\n", " '新ID': new_id,\n", " '状态': status,\n", " '开始时间': pd.to_datetime(row['新开始时间'], unit='ms'),\n", " '结束时间': pd.to_datetime(row['新结束时间'], unit='ms')\n", " })\n", "\n", " except Exception as e:\n", " results.append({\n", " '原名称': row['优惠券名称'],\n", " '原ID': row['coupon_id'],\n", " '新ID': None,\n", " '状态': f'错误: {str(e)}',\n", " '开始时间': None,\n", " '结束时间': None\n", " })\n", "\n", "\n", "# 将新ID回写到create_coupon_df中 - 使用唯一标识进行映射\n", "def get_new_id(row):\n", " unique_key = (row['优惠券名称'], row['新开始时间'], row['新结束时间'], row['门店id'])\n", " return new_id_mapping.get(unique_key)\n", "\n", "\n", "create_coupon_df['new_id'] = create_coupon_df.apply(get_new_id, axis=1)\n", "create_coupon_df.to_excel(f\"{output_path}/已创建优惠券.xlsx\", index=False)\n", "\n", "print(\"正在获取客户列表...\")\n", "params = {\n", " 'pageSize': '100',\n", " 'pageNo': '1',\n", "}\n", "\n", "response = requests.get(\n", " 'https://yunxiu.f6car.cn/member/customer/listForPermission',\n", " params=params,\n", " cookies=cookies,\n", " headers=headers,\n", ")\n", "\n", "customer_list = []\n", "total = response.json()['data']['total']\n", "total = int(total)\n", "total_page = total // 100 + 1\n", "total_page = 3\n", "for i in tqdm(range(1, total_page + 1), desc=\"获取客户列表\"):\n", " params['pageNo'] = i\n", " response = requests.get(\n", " 'https://yunxiu.f6car.cn/member/customer/listForPermission',\n", " params=params,\n", " cookies=cookies,\n", " headers=headers,\n", " )\n", " data_list = response.json()['data'][\"data\"]\n", " for item in data_list:\n", " customer_list.append({\n", " 'name': item['name'],\n", " 'idCustomer': item['idCustomer'],\n", " 'cellPhone': item['cellPhone'],\n", " })\n", "\n", "\n", "def create_coupon_distribution_request(customer_list, create_coupon_df):\n", " # 定义手机号清洗函数 - 确保正确处理各种格式\n", " def standardize_phone(phone):\n", " if pd.isna(phone):\n", " return None\n", " phone = str(phone).strip().replace(\" \", \"\").replace(\"-\", \"\")\n", " # 处理科学计数法问题（如1.75828e+10）\n", " if 'e+' in phone.lower():\n", " try:\n", " phone = \"{:.0f}\".format(float(phone))\n", " except:\n", " return None\n", " # 只保留数字\n", " phone = ''.join(c for c in phone if c.isdigit())\n", " # 处理86开头的情况\n", " if len(phone) > 11 and phone.startswith('86'):\n", " phone = phone[2:]\n", " return phone if len(phone) == 11 else None\n", "\n", " # 预处理数据\n", " create_coupon_df['clean_phone'] = create_coupon_df['手机号'].apply(standardize_phone)\n", " clean_customer_list = []\n", " for customer in customer_list:\n", " phone = standardize_phone(customer['cellPhone'])\n", " if phone:\n", " clean_customer_list.append({\n", " **customer,\n", " 'clean_phone': phone\n", " })\n", "\n", " # 调试：打印预处理后的数据\n", " print(\"\\n预处理后的手机号示例：\")\n", " print(\"Excel中的手机号:\", create_coupon_df['clean_phone'].head().tolist())\n", " print(\"客户列表手机号:\", [c['clean_phone'] for c in clean_customer_list[:5]])\n", "\n", " # 按手机号和门店ID分组优惠券\n", " phone_org_to_coupons = defaultdict(list)\n", "\n", " for _, row in create_coupon_df.iterrows():\n", " phone = row['clean_phone']\n", " org_id = row['门店id']\n", " if pd.notna(phone) and pd.notna(org_id) and pd.notna(row['new_id']):\n", " key = (str(phone), str(org_id)) # 确保都是字符串类型\n", " phone_org_to_coupons[key].append({\n", " 'coupon_name': row['优惠券名称'],\n", " 'coupon_id': str(row['new_id']),\n", " 'counts': int(row['客户发券数量']) if pd.notna(row['客户发券数量']) else 1\n", " })\n", "\n", " # 调试：打印分组结果\n", " print(\"\\n分组结果（前5个）：\")\n", " for (phone, org_id), coupons in list(phone_org_to_coupons.items())[:5]:\n", " print(f\"手机号: {phone}, 门店ID: {org_id}\")\n", " for coupon in coupons:\n", " print(f\" - {coupon['coupon_name']} (ID: {coupon['coupon_id']}, 数量: {coupon['counts']})\")\n", "\n", " # 创建请求体\n", " request_bodies = []\n", " matched_count = 0\n", "\n", " # 构建手机号到客户的映射\n", " phone_to_customers = defaultdict(list)\n", " for customer in clean_customer_list:\n", " phone_to_customers[customer['clean_phone']].append(customer)\n", "\n", " # 匹配逻辑\n", " for (phone, org_id), coupons in phone_org_to_coupons.items():\n", " if phone in phone_to_customers:\n", " matched_count += 1\n", " customers = phone_to_customers[phone]\n", "\n", " # 为每个匹配的客户创建请求\n", " for customer in customers:\n", " request_body = {\n", " \"takeCouponMemo\": \"\",\n", " \"remark\": \",\".join(f\"{c['coupon_name']}(x{c['counts']})\" for c in coupons),\n", " \"orgId\": org_id,\n", " \"couponTakeRequiredTemplateParamList\": [\n", " {\"couponTemplateId\": c['coupon_id'], \"quantity\": c['counts']}\n", " for c in coupons\n", " ],\n", " \"operationParam\": {\n", " \"couponTemplateIdList\": list({c['coupon_id'] for c in coupons})\n", " },\n", " \"batchOperateTaskDataList\": [{\n", " \"dataId\": customer['idCustomer'],\n", " \"dataName\": customer['name'],\n", " \"dataInfo\": customer['cellPhone']\n", " }]\n", " }\n", " request_bodies.append(request_body)\n", "\n", " print(f\"\\n匹配统计：\")\n", " print(f\"唯一客户手机号数量: {len(phone_to_customers)}\")\n", " print(f\"匹配到的优惠券组数: {len(phone_org_to_coupons)}\")\n", " print(f\"成功匹配组数: {matched_count}\")\n", " print(f\"生成的请求数量: {len(request_bodies)}\")\n", "\n", " return request_bodies\n", "\n", "\n", "# 生成请求体\n", "request_bodies = create_coupon_distribution_request(customer_list, create_coupon_df)\n", "print(create_coupon_df)\n", "# 发送请求\n", "if request_bodies:\n", " print(\"\\n开始发放优惠券...\")\n", " success_count = 0\n", " for i, body in enumerate(tqdm(request_bodies, desc=\"发放进度\")):\n", " print(body)\n", " try:\n", " resp = requests.post(\n", " 'https://yunxiu.f6car.cn/macan/batch/operate/singleSend/coupon/from/customer',\n", " cookies=cookies,\n", " headers=headers,\n", " json=body,\n", " timeout=10\n", " )\n", " print(resp.text)\n", " if resp.status_code == 200:\n", " success_count += 1\n", " else:\n", " print(f\"请求失败 (状态码 {resp.status_code}): {resp.text}\")\n", " except Exception as e:\n", " print(f\"请求异常: {str(e)}\")\n", "\n", " print(f\"\\n发放完成：成功 {success_count}/{len(request_bodies)}\")\n", "else:\n", " print(\"\\n没有需要发放的优惠券\")\n", "\n", "print(\"删除无用优惠券...\")\n", "delete_list = []\n", "for index, item in create_coupon_df.iterrows():\n", " delete_list.append(item['new_id'])\n", "set(delete_list)\n", "for item in set(delete_list):\n", " response = requests.delete(f'https://yunxiu.f6car.cn/marketing/couponTemplate/delete/{item}', cookies=cookies,\n", " headers=headers)\n", "\n", "# 显示处理结果\n", "result_df = pd.DataFrame(results)\n", "result_df.to_excel(f\"{output_path}/处理结果.xlsx\", index=False)\n", "print(\"处理完成！结果如下：\")\n", "display(result_df)\n", "print(\"\\n检查优惠券创建结果:\")\n", "print(create_coupon_df[['优惠券名称', 'new_id', '门店id', 'clean_phone']].head())\n", "print(\"new_id 为空的数量:\", create_coupon_df['new_id'].isna().sum())\n", "print(\"\\n检查门店ID:\")\n", "print(\"门店ID类型:\", create_coupon_df['门店id'].apply(type).unique())\n", "print(\"门店ID示例:\", create_coupon_df['门店id'].head().tolist())\n", "# 检查哪些行因条件过滤被丢弃\n", "filtered_rows = create_coupon_df[\n", " (create_coupon_df['clean_phone'].isna()) |\n", " (create_coupon_df['门店id'].isna()) |\n", " (create_coupon_df['new_id'].isna())\n", " ]\n", "print(\"\\n被过滤的行数:\", len(filtered_rows))\n", "if not filtered_rows.empty:\n", " print(filtered_rows[['优惠券名称', 'clean_phone', '门店id', 'new_id']].head())\n", "# 显示各类统计\n", "print(\"\\n分类统计：\")\n", "stats = {\n", " '唯一优惠券': len(create_coupon_df),\n", " '未找到优惠券': len(not_found_df),\n", " '重复优惠券': len(duplicate_df),\n", " '成功创建': sum(result_df['状态'] == '成功'),\n", " '创建失败': len(result_df) - sum(result_df['状态'] == '成功')\n", "}\n", "\n", "display(pd.DataFrame.from_dict(stats, orient='index', columns=['数量']))" ], "id": "aeba4eed3cefe615", "outputs": [ { "data": { "text/plain": [ " 优惠券名称 新开始时间 新结束时间 手机号 客户发券数量 门店id\n", "0 测试优惠1 2025-08-30 2028-08-30 10031122309 1 11240984669917217520\n", "1 优惠券1 2025-09-01 2026-09-01 10031122309 2 11240984669917217520" ], "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
优惠券名称新开始时间新结束时间手机号客户发券数量门店id
0测试优惠12025-08-302028-08-3010031122309111240984669917217520
1优惠券12025-09-012026-09-0110031122309211240984669917217520
\n", "
" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "正在获取现有优惠券列表...\n", "正在分类优惠券...\n", "正在处理时间字段...\n", "Empty DataFrame\n", "Columns: [优惠券名称, 新开始时间, 新结束时间, 手机号, 客户发券数量, 门店id, 出现次数, coupon_id]\n", "Index: []\n", "开始创建新优惠券...\n", "正在获取客户列表...\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "获取客户列表: 100%|██████████| 3/3 [00:00<00:00, 4.70it/s]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "\n", "预处理后的手机号示例：\n", "Excel中的手机号: []\n", "客户列表手机号: ['20000000305', '15884526584', '13190002822', '13998867796', '18635888263']\n", "\n", "分组结果（前5个）：\n", "\n", "匹配统计：\n", "唯一客户手机号数量: 300\n", "匹配到的优惠券组数: 0\n", "成功匹配组数: 0\n", "生成的请求数量: 0\n", "Empty DataFrame\n", "Columns: [优惠券名称, 新开始时间, 新结束时间, 手机号, 客户发券数量, 门店id, 出现次数, coupon_id, new_id, clean_phone]\n", "Index: []\n", "\n", "没有需要发放的优惠券\n", "删除无用优惠券...\n", "处理完成！结果如下：\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\n" ] }, { "data": { "text/plain": [ "Empty DataFrame\n", "Columns: []\n", "Index: []" ], "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
\n", "
" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "\n", "检查优惠券创建结果:\n", "Empty DataFrame\n", "Columns: [优惠券名称, new_id, 门店id, clean_phone]\n", "Index: []\n", "new_id 为空的数量: 0\n", "\n", "检查门店ID:\n", "门店ID类型: []\n", "门店ID示例: []\n", "\n", "被过滤的行数: 0\n", "\n", "分类统计：\n" ] }, { "ename": "KeyError", "evalue": "'状态'", "output_type": "error", "traceback": [ "\u001B[31m---------------------------------------------------------------------------\u001B[39m", "\u001B[31mKeyError\u001B[39m Traceback (most recent call last)", "\u001B[36mCell\u001B[39m\u001B[36m \u001B[39m\u001B[32mIn[12]\u001B[39m\u001B[32m, line 427\u001B[39m\n\u001B[32m 421\u001B[39m \u001B[38;5;66;03m# 显示各类统计\u001B[39;00m\n\u001B[32m 422\u001B[39m \u001B[38;5;28mprint\u001B[39m(\u001B[33m\"\u001B[39m\u001B[38;5;130;01m\\n\u001B[39;00m\u001B[33m分类统计：\u001B[39m\u001B[33m\"\u001B[39m)\n\u001B[32m 423\u001B[39m stats = {\n\u001B[32m 424\u001B[39m \u001B[33m'\u001B[39m\u001B[33m唯一优惠券\u001B[39m\u001B[33m'\u001B[39m: \u001B[38;5;28mlen\u001B[39m(create_coupon_df),\n\u001B[32m 425\u001B[39m \u001B[33m'\u001B[39m\u001B[33m未找到优惠券\u001B[39m\u001B[33m'\u001B[39m: \u001B[38;5;28mlen\u001B[39m(not_found_df),\n\u001B[32m 426\u001B[39m \u001B[33m'\u001B[39m\u001B[33m重复优惠券\u001B[39m\u001B[33m'\u001B[39m: \u001B[38;5;28mlen\u001B[39m(duplicate_df),\n\u001B[32m--> \u001B[39m\u001B[32m427\u001B[39m \u001B[33m'\u001B[39m\u001B[33m成功创建\u001B[39m\u001B[33m'\u001B[39m: \u001B[38;5;28msum\u001B[39m(result_df[\u001B[33m'\u001B[39m\u001B[33m状态\u001B[39m\u001B[33m'\u001B[39m] == \u001B[33m'\u001B[39m\u001B[33m成功\u001B[39m\u001B[33m'\u001B[39m),\n\u001B[32m 428\u001B[39m \u001B[33m'\u001B[39m\u001B[33m创建失败\u001B[39m\u001B[33m'\u001B[39m: \u001B[38;5;28mlen\u001B[39m(result_df) - \u001B[38;5;28msum\u001B[39m(result_df[\u001B[33m'\u001B[39m\u001B[33m状态\u001B[39m\u001B[33m'\u001B[39m] == \u001B[33m'\u001B[39m\u001B[33m成功\u001B[39m\u001B[33m'\u001B[39m)\n\u001B[32m 429\u001B[39m }\n\u001B[32m 431\u001B[39m display(pd.DataFrame.from_dict(stats, orient=\u001B[33m'\u001B[39m\u001B[33mindex\u001B[39m\u001B[33m'\u001B[39m, columns=[\u001B[33m'\u001B[39m\u001B[33m数量\u001B[39m\u001B[33m'\u001B[39m]))\n", "\u001B[36mFile \u001B[39m\u001B[32mD:\\ProgramTools\\anaconda3\\envs\\f6\\Lib\\site-packages\\pandas\\core\\frame.py:4107\u001B[39m, in \u001B[36mDataFrame.__getitem__\u001B[39m\u001B[34m(self, key)\u001B[39m\n\u001B[32m 4105\u001B[39m \u001B[38;5;28;01mif\u001B[39;00m \u001B[38;5;28mself\u001B[39m.columns.nlevels > \u001B[32m1\u001B[39m:\n\u001B[32m 4106\u001B[39m \u001B[38;5;28;01mreturn\u001B[39;00m \u001B[38;5;28mself\u001B[39m._getitem_multilevel(key)\n\u001B[32m-> \u001B[39m\u001B[32m4107\u001B[39m indexer = \u001B[38;5;28mself\u001B[39m.columns.get_loc(key)\n\u001B[32m 4108\u001B[39m \u001B[38;5;28;01mif\u001B[39;00m is_integer(indexer):\n\u001B[32m 4109\u001B[39m indexer = [indexer]\n", "\u001B[36mFile \u001B[39m\u001B[32mD:\\ProgramTools\\anaconda3\\envs\\f6\\Lib\\site-packages\\pandas\\core\\indexes\\range.py:417\u001B[39m, in \u001B[36mRangeIndex.get_loc\u001B[39m\u001B[34m(self, key)\u001B[39m\n\u001B[32m 415\u001B[39m \u001B[38;5;28;01mraise\u001B[39;00m \u001B[38;5;167;01mKeyError\u001B[39;00m(key) \u001B[38;5;28;01mfrom\u001B[39;00m\u001B[38;5;250m \u001B[39m\u001B[34;01merr\u001B[39;00m\n\u001B[32m 416\u001B[39m \u001B[38;5;28;01mif\u001B[39;00m \u001B[38;5;28misinstance\u001B[39m(key, Hashable):\n\u001B[32m--> \u001B[39m\u001B[32m417\u001B[39m \u001B[38;5;28;01mraise\u001B[39;00m \u001B[38;5;167;01mKeyError\u001B[39;00m(key)\n\u001B[32m 418\u001B[39m \u001B[38;5;28mself\u001B[39m._check_indexing_error(key)\n\u001B[32m 419\u001B[39m \u001B[38;5;28;01mraise\u001B[39;00m \u001B[38;5;167;01mKeyError\u001B[39;00m(key)\n", "\u001B[31mKeyError\u001B[39m: '状态'" ] } ], "execution_count": 12 }, { "metadata": { "ExecuteTime": { "end_time": "2025-08-30T08:00:33.451947Z", "start_time": "2025-08-30T08:00:25.547662Z" } }, "cell_type": "code", "source": [ "import requests\n", "import pandas as pd\n", "from collections import defaultdict\n", "from IPython.display import display\n", "from tqdm import tqdm\n", "\n", "import requests\n", "\n", "cookies = {\n", " 'portalSESSIONID': 'dc32262b-bda6-4234-b3c5-2b8c40d2fb60',\n", " 'erpLanguage': 'zh-CN',\n", " 'tmall': 'false',\n", " 'Hm_lvt_25f5e7a3a5dbb293d7dd35d5f1be8d0a': '1756257166,1756352022,1756431231,1756538308',\n", " 'HMACCOUNT': '55F2182717FD6AE6',\n", " 'prodOrg': '11240984669917217520',\n", " 'unp': '15865484595890778191',\n", " 'un': '15865484595890778191',\n", " '_up': '-NillNN-qyBEJ--t3vnSknvoOF53y_SJuMkA2n43U-daUfnArpjQjaZJ9Q3d-WrAAGgt60MgQHajHWBHMKKxj0CuWypi1JgKCFP1EPEk-HbqEfURqoIi1QsO-PFRv-ZNHu3M-GTc1p60EX-sq-RQgeIal1HLPxpurEj9mmjq8K60Nxc.',\n", " 'sensorsdata2015jssdkcross': '%7B%22distinct_id%22%3A%2215865484595890778191%22%2C%22first_id%22%3A%22198a89fdaa54f-0a897244f8104b8-4c657b58-2073600-198a89fdaa61b71%22%2C%22props%22%3A%7B%22%24latest_traffic_source_type%22%3A%22%E7%9B%B4%E6%8E%A5%E6%B5%81%E9%87%8F%22%2C%22%24latest_search_keyword%22%3A%22%E6%9C%AA%E5%8F%96%E5%88%B0%E5%80%BC_%E7%9B%B4%E6%8E%A5%E6%89%93%E5%BC%80%22%2C%22%24latest_referrer%22%3A%22%22%7D%2C%22%24device_id%22%3A%22198a89fdaa54f-0a897244f8104b8-4c657b58-2073600-198a89fdaa61b71%22%7D',\n", " 'Hm_lpvt_25f5e7a3a5dbb293d7dd35d5f1be8d0a': '1756539037',\n", "}\n", "\n", "headers = {\n", " 'accept': 'application/json, text/plain, */*',\n", " 'accept-language': 'zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6',\n", " 'priority': 'u=1, i',\n", " 'referer': 'https://yunxiu.f6car.cn/erp/view/index.html',\n", " 'sec-ch-ua': '\"Not;A=Brand\";v=\"99\", \"Microsoft Edge\";v=\"139\", \"Chromium\";v=\"139\"',\n", " 'sec-ch-ua-mobile': '?0',\n", " 'sec-ch-ua-platform': '\"Windows\"',\n", " 'sec-fetch-dest': 'empty',\n", " 'sec-fetch-mode': 'cors',\n", " 'sec-fetch-site': 'same-origin',\n", " 'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36 Edg/139.0.0.0',\n", " # 'cookie': 'portalSESSIONID=dc32262b-bda6-4234-b3c5-2b8c40d2fb60; erpLanguage=zh-CN; tmall=false; Hm_lvt_25f5e7a3a5dbb293d7dd35d5f1be8d0a=1756257166,1756352022,1756431231,1756538308; HMACCOUNT=55F2182717FD6AE6; prodOrg=11240984669917217520; unp=15865484595890778191; un=15865484595890778191; _up=-NillNN-qyBEJ--t3vnSknvoOF53y_SJuMkA2n43U-daUfnArpjQjaZJ9Q3d-WrAAGgt60MgQHajHWBHMKKxj0CuWypi1JgKCFP1EPEk-HbqEfURqoIi1QsO-PFRv-ZNHu3M-GTc1p60EX-sq-RQgeIal1HLPxpurEj9mmjq8K60Nxc.; sensorsdata2015jssdkcross=%7B%22distinct_id%22%3A%2215865484595890778191%22%2C%22first_id%22%3A%22198a89fdaa54f-0a897244f8104b8-4c657b58-2073600-198a89fdaa61b71%22%2C%22props%22%3A%7B%22%24latest_traffic_source_type%22%3A%22%E7%9B%B4%E6%8E%A5%E6%B5%81%E9%87%8F%22%2C%22%24latest_search_keyword%22%3A%22%E6%9C%AA%E5%8F%96%E5%88%B0%E5%80%BC_%E7%9B%B4%E6%8E%A5%E6%89%93%E5%BC%80%22%2C%22%24latest_referrer%22%3A%22%22%7D%2C%22%24device_id%22%3A%22198a89fdaa54f-0a897244f8104b8-4c657b58-2073600-198a89fdaa61b71%22%7D; Hm_lpvt_25f5e7a3a5dbb293d7dd35d5f1be8d0a=1756539037',\n", "}\n", "\n", "\n", "\n", "\n", "# 1. 数据转换函数\n", "def convert_coupon_response_to_request(response_data, new_start_time, new_end_time):\n", " \"\"\"将API响应转换为请求格式\"\"\"\n", " template_info = response_data.get('couponTemplateInfo', {})\n", "\n", " request_data = {\n", " 'orgId': template_info.get('orgId'),\n", " 'couponName': template_info.get('couponName'),\n", " 'couponDesc': template_info.get('couponDesc'),\n", " 'totalStock': str(template_info.get('totalStock', 0)),\n", " 'takeQuota': str(template_info.get('takeQuota', 0)),\n", " 'conditionType': template_info.get('conditionType', 0),\n", " 'validityType': template_info.get('validityType', 0),\n", " 'validityBegin': new_start_time,\n", " 'validityEnd': new_end_time,\n", " 'orgRangeType': template_info.get('orgRangeType', 0),\n", " 'goodsRangeType': template_info.get('goodsRangeType', 0),\n", " }\n", "\n", " # 处理数值字段\n", " try:\n", " request_data['couponValue'] = str(float(template_info.get('couponValue', \"0.0\")))\n", " except (ValueError, TypeError):\n", " request_data['couponValue'] = \"0\"\n", "\n", " # 处理可选字段\n", " for field in ['conditionValue', 'validityDays']:\n", " value = template_info.get(field)\n", " request_data[field] = str(value) if value is not None else None\n", "\n", " # 处理列表字段\n", " list_mappings = {\n", " 'channelList': ('channelList', 'channelCode'),\n", " 'useSceneCodeList': ('useSceneList', 'sceneCode'),\n", " 'orgList': ('orgList', 'orgId') if template_info.get('orgRangeType', 0) == 0 else (None, None)\n", " }\n", "\n", " for key, (source, attr) in list_mappings.items():\n", " request_data[key] = [item.get(attr) for item in response_data.get(source, [])] if source else []\n", "\n", " # 处理商品数据\n", " if template_info.get('goodsRangeType', 0) == 0:\n", " for list_type in ['partCategoryList', 'partBrandList']:\n", " request_data[list_type] = [\n", " {k: v for k, v in item.items() if v is not None}\n", " for item in response_data.get(list_type, [])\n", " ]\n", " else:\n", " request_data.update({'partCategoryList': [], 'partBrandList': []})\n", "\n", " return request_data\n", "\n", "\n", "# 2. 主流程\n", "# 读取Excel数据\n", "input_path = r\"D:\\Idea Project\\F6+宜搭+其它(1)\\张阳脚本\\文件输入\\优惠券新建模板.xlsx\"\n", "df = pd.read_excel(input_path, sheet_name='Sheet1')\n", "display(df.head(2)) # 预览数据\n", "\n", "# 获取所有现有优惠券\n", "print(\"正在获取现有优惠券列表...\")\n", "all_coupon_list = []\n", "params = {\n", " 'couponName': '',\n", " 'statusList': '',\n", " 'orgIdList': '',\n", " 'pageNo': '1',\n", " 'pageSize': '100',\n", " 'isDeleted': '0',\n", " 'sorts': '',\n", "}\n", "\n", "response = requests.get(\n", " 'https://yunxiu.f6car.cn/marketing/couponTemplate/paging',\n", " params=params,\n", " cookies=cookies,\n", " headers=headers,\n", ")\n", "total = int(response.json()['data']['total'])\n", "total_page = (total // 100) + 1\n", "\n", "for i in range(1, total_page + 1):\n", " params['pageNo'] = str(i)\n", " response = requests.get(\n", " 'https://yunxiu.f6car.cn/marketing/couponTemplate/paging',\n", " params=params,\n", " cookies=cookies,\n", " headers=headers,\n", " )\n", " all_coupon_list.extend([\n", " (c.get('id'), c.get('couponName'))\n", " for c in response.json()['data'].get(\"data\", [])\n", " ])\n", "\n", "# 分类统计优惠券\n", "print(\"正在分类优惠券...\")\n", "coupon_stats = pd.DataFrame(all_coupon_list, columns=['coupon_id', 'coupon_name'])\n", "coupon_counts = coupon_stats['coupon_name'].value_counts().to_dict()\n", "\n", "# 创建分类DataFrame\n", "df['出现次数'] = df['优惠券名称'].map(coupon_counts).fillna(0)\n", "create_coupon_df = df[df['出现次数'] == 1].copy()\n", "not_found_df = df[df['出现次数'] == 0].copy()\n", "duplicate_df = df[df['出现次数'] > 1].copy()\n", "\n", "# 添加ID映射\n", "id_mapping = coupon_stats.drop_duplicates('coupon_name', keep=False).set_index('coupon_name')['coupon_id']\n", "create_coupon_df['coupon_id'] = create_coupon_df['优惠券名称'].map(id_mapping)\n", "\n", "# 保存分类结果\n", "output_path = r\"D:\\Idea Project\\F6+宜搭+其它(1)\\张阳脚本\\文件输出\"\n", "not_found_df.to_excel(f\"{output_path}/未找到优惠券.xlsx\", index=False)\n", "duplicate_df.to_excel(f\"{output_path}/重复优惠券.xlsx\", index=False)\n", "\n", "# 处理时间字段\n", "print(\"正在处理时间字段...\")\n", "create_coupon_df[\"新开始时间\"] = create_coupon_df[\"新开始时间\"].astype('int64') // 10 ** 6\n", "create_coupon_df[\"新结束时间\"] = create_coupon_df[\"新结束时间\"].astype('int64') // 10 ** 6\n", "\n", "# 创建新优惠券\n", "print(\"开始创建新优惠券...\")\n", "df_multi_unique = create_coupon_df.drop_duplicates(subset=['优惠券名称', '新开始时间', \"新结束时间\", \"门店id\"]) # 去重\n", "new_id_mapping = {}\n", "results = []\n", "for _, row in df_multi_unique.iterrows():\n", " try:\n", " # 获取模板详情\n", " detail_url = f\"https://yunxiu.f6car.cn/marketing/couponTemplate/detail/{row['coupon_id']}\"\n", " detail_resp = requests.get(detail_url, cookies=cookies, headers=headers)\n", "\n", " # 转换并创建\n", " json_data = convert_coupon_response_to_request(\n", " detail_resp.json()['data'],\n", " row['新开始时间'],\n", " row['新结束时间']\n", " )\n", " create_resp = requests.post(\n", " 'https://yunxiu.f6car.cn/marketing/couponTemplate/add',\n", " cookies=cookies,\n", " headers=headers,\n", " json=json_data\n", " )\n", " new_id = create_resp.json().get('data')\n", " # 将新ID添加到映射中\n", " unique_key = (row['优惠券名称'], row['新开始时间'], row['新结束时间'], row['门店id'])\n", " new_id_mapping[unique_key] = new_id\n", "\n", " # 启用优惠券\n", " if new_id:\n", " enable_resp = requests.put(\n", " 'https://yunxiu.f6car.cn/marketing/couponTemplate/status',\n", " cookies=cookies,\n", " headers=headers,\n", " json={'id': new_id, 'status': 1}\n", " )\n", " status = '成功' if enable_resp.status_code == 200 else '启用失败'\n", " else:\n", " status = '创建失败'\n", "\n", " results.append({\n", " '原名称': row['优惠券名称'],\n", " '原ID': row['coupon_id'],\n", " '新ID': new_id,\n", " '状态': status,\n", " '开始时间': pd.to_datetime(row['新开始时间'], unit='ms'),\n", " '结束时间': pd.to_datetime(row['新结束时间'], unit='ms')\n", " })\n", "\n", " except Exception as e:\n", " results.append({\n", " '原名称': row['优惠券名称'],\n", " '原ID': row['coupon_id'],\n", " '新ID': None,\n", " '状态': f'错误: {str(e)}',\n", " '开始时间': None,\n", " '结束时间': None\n", " })\n", "\n", "\n", "# 将新ID回写到create_coupon_df中 - 使用唯一标识进行映射\n", "def get_new_id(row):\n", " unique_key = (row['优惠券名称'], row['新开始时间'], row['新结束时间'], row['门店id'])\n", " return new_id_mapping.get(unique_key)\n", "\n", "\n", "create_coupon_df['new_id'] = create_coupon_df.apply(get_new_id, axis=1)\n", "create_coupon_df.to_excel(f\"{output_path}/已创建优惠券.xlsx\", index=False)\n", "\n", "print(\"正在获取客户列表...\")\n", "params = {\n", " 'pageSize': '100',\n", " 'pageNo': '1',\n", "}\n", "\n", "response = requests.get(\n", " 'https://yunxiu.f6car.cn/member/customer/listForPermission',\n", " params=params,\n", " cookies=cookies,\n", " headers=headers,\n", ")\n", "\n", "customer_list = []\n", "total = response.json()['data']['total']\n", "total = int(total)\n", "total_page = total // 100 + 1\n", "total_page = 3\n", "for i in tqdm(range(1, total_page + 1), desc=\"获取客户列表\"):\n", " params['pageNo'] = i\n", " response = requests.get(\n", " 'https://yunxiu.f6car.cn/member/customer/listForPermission',\n", " params=params,\n", " cookies=cookies,\n", " headers=headers,\n", " )\n", " data_list = response.json()['data'][\"data\"]\n", " for item in data_list:\n", " customer_list.append({\n", " 'name': item['name'],\n", " 'idCustomer': item['idCustomer'],\n", " 'cellPhone': item['cellPhone'],\n", " })\n", "\n", "\n", "def create_coupon_distribution_request(customer_list, create_coupon_df):\n", " # 定义手机号清洗函数 - 确保正确处理各种格式\n", " def standardize_phone(phone):\n", " if pd.isna(phone):\n", " return None\n", " phone = str(phone).strip().replace(\" \", \"\").replace(\"-\", \"\")\n", " # 处理科学计数法问题（如1.75828e+10）\n", " if 'e+' in phone.lower():\n", " try:\n", " phone = \"{:.0f}\".format(float(phone))\n", " except:\n", " return None\n", " # 只保留数字\n", " phone = ''.join(c for c in phone if c.isdigit())\n", " # 处理86开头的情况\n", " if len(phone) > 11 and phone.startswith('86'):\n", " phone = phone[2:]\n", " return phone if len(phone) == 11 else None\n", "\n", " # 预处理数据\n", " create_coupon_df['clean_phone'] = create_coupon_df['手机号'].apply(standardize_phone)\n", " clean_customer_list = []\n", " for customer in customer_list:\n", " phone = standardize_phone(customer['cellPhone'])\n", " if phone:\n", " clean_customer_list.append({\n", " **customer,\n", " 'clean_phone': phone\n", " })\n", "\n", " # 调试：打印预处理后的数据\n", " print(\"\\n预处理后的手机号示例：\")\n", " print(\"Excel中的手机号:\", create_coupon_df['clean_phone'].head().tolist())\n", " print(\"客户列表手机号:\", [c['clean_phone'] for c in clean_customer_list[:5]])\n", "\n", " # 按手机号和门店ID分组优惠券\n", " phone_org_to_coupons = defaultdict(list)\n", "\n", " for _, row in create_coupon_df.iterrows():\n", " phone = row['clean_phone']\n", " org_id = row['门店id']\n", " if pd.notna(phone) and pd.notna(org_id) and pd.notna(row['new_id']):\n", " key = (str(phone), str(org_id)) # 确保都是字符串类型\n", " phone_org_to_coupons[key].append({\n", " 'coupon_name': row['优惠券名称'],\n", " 'coupon_id': str(row['new_id']),\n", " 'counts': int(row['客户发券数量']) if pd.notna(row['客户发券数量']) else 1\n", " })\n", "\n", " # 调试：打印分组结果\n", " print(\"\\n分组结果（前5个）：\")\n", " for (phone, org_id), coupons in list(phone_org_to_coupons.items())[:5]:\n", " print(f\"手机号: {phone}, 门店ID: {org_id}\")\n", " for coupon in coupons:\n", " print(f\" - {coupon['coupon_name']} (ID: {coupon['coupon_id']}, 数量: {coupon['counts']})\")\n", "\n", " # 创建请求体\n", " request_bodies = []\n", " matched_count = 0\n", "\n", " # 构建手机号到客户的映射\n", " phone_to_customers = defaultdict(list)\n", " for customer in clean_customer_list:\n", " phone_to_customers[customer['clean_phone']].append(customer)\n", "\n", " # 匹配逻辑\n", " for (phone, org_id), coupons in phone_org_to_coupons.items():\n", " if phone in phone_to_customers:\n", " matched_count += 1\n", " customers = phone_to_customers[phone]\n", "\n", " # 为每个匹配的客户创建请求\n", " for customer in customers:\n", " request_body = {\n", " \"takeCouponMemo\": \"\",\n", " \"remark\": \",\".join(f\"{c['coupon_name']}(x{c['counts']})\" for c in coupons),\n", " \"orgId\": org_id,\n", " \"couponTakeRequiredTemplateParamList\": [\n", " {\"couponTemplateId\": c['coupon_id'], \"quantity\": c['counts']}\n", " for c in coupons\n", " ],\n", " \"operationParam\": {\n", " \"couponTemplateIdList\": list({c['coupon_id'] for c in coupons})\n", " },\n", " \"batchOperateTaskDataList\": [{\n", " \"dataId\": customer['idCustomer'],\n", " \"dataName\": customer['name'],\n", " \"dataInfo\": customer['cellPhone']\n", " }]\n", " }\n", " request_bodies.append(request_body)\n", "\n", " print(f\"\\n匹配统计：\")\n", " print(f\"唯一客户手机号数量: {len(phone_to_customers)}\")\n", " print(f\"匹配到的优惠券组数: {len(phone_org_to_coupons)}\")\n", " print(f\"成功匹配组数: {matched_count}\")\n", " print(f\"生成的请求数量: {len(request_bodies)}\")\n", "\n", " return request_bodies\n", "print(create_coupon_df)\n", "\n", "# 生成请求体\n", "request_bodies = create_coupon_distribution_request(customer_list, create_coupon_df)\n", "print(create_coupon_df)\n", "# 发送请求\n", "if request_bodies:\n", " print(\"\\n开始发放优惠券...\")\n", " success_count = 0\n", " for i, body in enumerate(tqdm(request_bodies, desc=\"发放进度\")):\n", " print(body)\n", " try:\n", " resp = requests.post(\n", " 'https://yunxiu.f6car.cn/macan/batch/operate/singleSend/coupon/from/customer',\n", " cookies=cookies,\n", " headers=headers,\n", " json=body,\n", " timeout=10\n", " )\n", " print(resp.text)\n", " if resp.status_code == 200:\n", " success_count += 1\n", " else:\n", " print(f\"请求失败 (状态码 {resp.status_code}): {resp.text}\")\n", " except Exception as e:\n", " print(f\"请求异常: {str(e)}\")\n", "\n", " print(f\"\\n发放完成：成功 {success_count}/{len(request_bodies)}\")\n", "else:\n", " print(\"\\n没有需要发放的优惠券\")\n", "import time\n", "time.sleep(5)\n", "print(\"删除无用优惠券...\")\n", "delete_list = []\n", "for index, item in create_coupon_df.iterrows():\n", " delete_list.append(item['new_id'])\n", "set(delete_list)\n", "for item in set(delete_list):\n", " response = requests.delete(f'https://yunxiu.f6car.cn/marketing/couponTemplate/delete/{item}', cookies=cookies,\n", " headers=headers)\n", "\n", "# 显示处理结果\n", "result_df = pd.DataFrame(results)\n", "result_df.to_excel(f\"{output_path}/处理结果.xlsx\", index=False)\n", "print(\"处理完成！结果如下：\")\n", "display(result_df)\n", "print(\"\\n检查优惠券创建结果:\")\n", "print(create_coupon_df[['优惠券名称', 'new_id', '门店id', 'clean_phone']].head())\n", "print(\"new_id 为空的数量:\", create_coupon_df['new_id'].isna().sum())\n", "print(\"\\n检查门店ID:\")\n", "print(\"门店ID类型:\", create_coupon_df['门店id'].apply(type).unique())\n", "print(\"门店ID示例:\", create_coupon_df['门店id'].head().tolist())\n", "# 检查哪些行因条件过滤被丢弃\n", "filtered_rows = create_coupon_df[\n", " (create_coupon_df['clean_phone'].isna()) |\n", " (create_coupon_df['门店id'].isna()) |\n", " (create_coupon_df['new_id'].isna())\n", " ]\n", "print(\"\\n被过滤的行数:\", len(filtered_rows))\n", "if not filtered_rows.empty:\n", " print(filtered_rows[['优惠券名称', 'clean_phone', '门店id', 'new_id']].head())\n", "# 显示各类统计\n", "print(\"\\n分类统计：\")\n", "stats = {\n", " '唯一优惠券': len(create_coupon_df),\n", " '未找到优惠券': len(not_found_df),\n", " '重复优惠券': len(duplicate_df),\n", " '成功创建': sum(result_df['状态'] == '成功'),\n", " '创建失败': len(result_df) - sum(result_df['状态'] == '成功')\n", "}\n", "\n", "display(pd.DataFrame.from_dict(stats, orient='index', columns=['数量']))" ], "id": "8b95769f4d2e390c", "outputs": [ { "data": { "text/plain": [ " 优惠券名称 新开始时间 新结束时间 手机号 客户发券数量 门店id\n", "0 测试优惠1 2025-08-30 2028-08-30 10031122303 1 11240984669917217520\n", "1 优惠券1 2025-09-01 2026-09-01 10031122303 1 11240984669917217520" ], "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
优惠券名称新开始时间新结束时间手机号客户发券数量门店id
0测试优惠12025-08-302028-08-3010031122303111240984669917217520
1优惠券12025-09-012026-09-0110031122303111240984669917217520
\n", "
" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "正在获取现有优惠券列表...\n", "正在分类优惠券...\n", "正在处理时间字段...\n", "开始创建新优惠券...\n", "正在获取客户列表...\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "获取客户列表: 100%|██████████| 3/3 [00:00<00:00, 4.77it/s]\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ " 优惠券名称 新开始时间 新结束时间 手机号 客户发券数量 \\\n", "0 测试优惠1 1756512000000 1851206400000 10031122303 1 \n", "1 优惠券1 1756684800000 1788220800000 10031122303 1 \n", "\n", " 门店id 出现次数 coupon_id new_id \n", "0 11240984669917217520 1 186141 186280 \n", "1 11240984669917217520 1 186142 186281 \n", "\n", "预处理后的手机号示例：\n", "Excel中的手机号: ['10031122303', '10031122303']\n", "客户列表手机号: ['20000000305', '15884526584', '13190002822', '13998867796', '18635888263']\n", "\n", "分组结果（前5个）：\n", "手机号: 10031122303, 门店ID: 11240984669917217520\n", " - 测试优惠1 (ID: 186280, 数量: 1)\n", " - 优惠券1 (ID: 186281, 数量: 1)\n", "\n", "匹配统计：\n", "唯一客户手机号数量: 300\n", "匹配到的优惠券组数: 1\n", "成功匹配组数: 1\n", "生成的请求数量: 1\n", " 优惠券名称 新开始时间 新结束时间 手机号 客户发券数量 \\\n", "0 测试优惠1 1756512000000 1851206400000 10031122303 1 \n", "1 优惠券1 1756684800000 1788220800000 10031122303 1 \n", "\n", " 门店id 出现次数 coupon_id new_id clean_phone \n", "0 11240984669917217520 1 186141 186280 10031122303 \n", "1 11240984669917217520 1 186142 186281 10031122303 \n", "\n", "开始发放优惠券...\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "发放进度: 100%|██████████| 1/1 [00:00<00:00, 6.32it/s]\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "{'takeCouponMemo': '', 'remark': '测试优惠1(x1),优惠券1(x1)', 'orgId': '11240984669917217520', 'couponTakeRequiredTemplateParamList': [{'couponTemplateId': '186280', 'quantity': 1}, {'couponTemplateId': '186281', 'quantity': 1}], 'operationParam': {'couponTemplateIdList': ['186280', '186281']}, 'batchOperateTaskDataList': [{'dataId': '12080550064028664152', 'dataName': '东南', 'dataInfo': '10031122303'}]}\n", "{\"code\":400,\"msg\":\"已有进行中的任务，请稍后重试\"}\n", "\n", "发放完成：成功 1/1\n", "删除无用优惠券...\n", "处理完成！结果如下：\n" ] }, { "data": { "text/plain": [ " 原名称 原ID 新ID 状态 开始时间 结束时间\n", "0 测试优惠1 186141 186280 成功 2025-08-30 2028-08-30\n", "1 优惠券1 186142 186281 成功 2025-09-01 2026-09-01" ], "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
原名称原ID新ID状态开始时间结束时间
0测试优惠1186141186280成功2025-08-302028-08-30
1优惠券1186142186281成功2025-09-012026-09-01
\n", "
" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "\n", "检查优惠券创建结果:\n", " 优惠券名称 new_id 门店id clean_phone\n", "0 测试优惠1 186280 11240984669917217520 10031122303\n", "1 优惠券1 186281 11240984669917217520 10031122303\n", "new_id 为空的数量: 0\n", "\n", "检查门店ID:\n", "门店ID类型: []\n", "门店ID示例: [11240984669917217520, 11240984669917217520]\n", "\n", "被过滤的行数: 0\n", "\n", "分类统计：\n" ] }, { "data": { "text/plain": [ " 数量\n", "唯一优惠券 2\n", "未找到优惠券 0\n", "重复优惠券 0\n", "成功创建 2\n", "创建失败 0" ], "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
数量
唯一优惠券2
未找到优惠券0
重复优惠券0
成功创建2
创建失败0
\n", "
" ] }, "metadata": {}, "output_type": "display_data" } ], "execution_count": 23 } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 2 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython2", "version": "2.7.6" } }, "nbformat": 4, "nbformat_minor": 5 }