2111 lines
91 KiB
Plaintext
2111 lines
91 KiB
Plaintext
{
|
||
"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": [
|
||
"<div>\n",
|
||
"<style scoped>\n",
|
||
" .dataframe tbody tr th:only-of-type {\n",
|
||
" vertical-align: middle;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe tbody tr th {\n",
|
||
" vertical-align: top;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe thead th {\n",
|
||
" text-align: right;\n",
|
||
" }\n",
|
||
"</style>\n",
|
||
"<table border=\"1\" class=\"dataframe\">\n",
|
||
" <thead>\n",
|
||
" <tr style=\"text-align: right;\">\n",
|
||
" <th></th>\n",
|
||
" <th>优惠券名称</th>\n",
|
||
" <th>新开始时间</th>\n",
|
||
" <th>新结束时间</th>\n",
|
||
" <th>手机号</th>\n",
|
||
" <th>客户发券数量</th>\n",
|
||
" <th>门店id</th>\n",
|
||
" </tr>\n",
|
||
" </thead>\n",
|
||
" <tbody>\n",
|
||
" <tr>\n",
|
||
" <th>0</th>\n",
|
||
" <td>亲友保养卡</td>\n",
|
||
" <td>2025-08-30</td>\n",
|
||
" <td>2030-08-30</td>\n",
|
||
" <td>10024094861</td>\n",
|
||
" <td>2</td>\n",
|
||
" <td>100000001</td>\n",
|
||
" </tr>\n",
|
||
" </tbody>\n",
|
||
"</table>\n",
|
||
"</div>"
|
||
]
|
||
},
|
||
"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": [
|
||
"<div>\n",
|
||
"<style scoped>\n",
|
||
" .dataframe tbody tr th:only-of-type {\n",
|
||
" vertical-align: middle;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe tbody tr th {\n",
|
||
" vertical-align: top;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe thead th {\n",
|
||
" text-align: right;\n",
|
||
" }\n",
|
||
"</style>\n",
|
||
"<table border=\"1\" class=\"dataframe\">\n",
|
||
" <thead>\n",
|
||
" <tr style=\"text-align: right;\">\n",
|
||
" <th></th>\n",
|
||
" <th>原名称</th>\n",
|
||
" <th>原ID</th>\n",
|
||
" <th>新ID</th>\n",
|
||
" <th>状态</th>\n",
|
||
" <th>开始时间</th>\n",
|
||
" <th>结束时间</th>\n",
|
||
" </tr>\n",
|
||
" </thead>\n",
|
||
" <tbody>\n",
|
||
" <tr>\n",
|
||
" <th>0</th>\n",
|
||
" <td>亲友保养卡</td>\n",
|
||
" <td>186171</td>\n",
|
||
" <td>186322</td>\n",
|
||
" <td>成功</td>\n",
|
||
" <td>2025-08-30</td>\n",
|
||
" <td>2030-08-30</td>\n",
|
||
" </tr>\n",
|
||
" </tbody>\n",
|
||
"</table>\n",
|
||
"</div>"
|
||
]
|
||
},
|
||
"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类型: [<class 'int'>]\n",
|
||
"门店ID示例: [100000001]\n",
|
||
"\n",
|
||
"被过滤的行数: 0\n",
|
||
"\n",
|
||
"分类统计：\n"
|
||
]
|
||
},
|
||
{
|
||
"data": {
|
||
"text/plain": [
|
||
" 数量\n",
|
||
"唯一优惠券 1\n",
|
||
"未找到优惠券 0\n",
|
||
"重复优惠券 0\n",
|
||
"成功创建 1\n",
|
||
"创建失败 0"
|
||
],
|
||
"text/html": [
|
||
"<div>\n",
|
||
"<style scoped>\n",
|
||
" .dataframe tbody tr th:only-of-type {\n",
|
||
" vertical-align: middle;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe tbody tr th {\n",
|
||
" vertical-align: top;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe thead th {\n",
|
||
" text-align: right;\n",
|
||
" }\n",
|
||
"</style>\n",
|
||
"<table border=\"1\" class=\"dataframe\">\n",
|
||
" <thead>\n",
|
||
" <tr style=\"text-align: right;\">\n",
|
||
" <th></th>\n",
|
||
" <th>数量</th>\n",
|
||
" </tr>\n",
|
||
" </thead>\n",
|
||
" <tbody>\n",
|
||
" <tr>\n",
|
||
" <th>唯一优惠券</th>\n",
|
||
" <td>1</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>未找到优惠券</th>\n",
|
||
" <td>0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>重复优惠券</th>\n",
|
||
" <td>0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>成功创建</th>\n",
|
||
" <td>1</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>创建失败</th>\n",
|
||
" <td>0</td>\n",
|
||
" </tr>\n",
|
||
" </tbody>\n",
|
||
"</table>\n",
|
||
"</div>"
|
||
]
|
||
},
|
||
"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": [
|
||
"<div>\n",
|
||
"<style scoped>\n",
|
||
" .dataframe tbody tr th:only-of-type {\n",
|
||
" vertical-align: middle;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe tbody tr th {\n",
|
||
" vertical-align: top;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe thead th {\n",
|
||
" text-align: right;\n",
|
||
" }\n",
|
||
"</style>\n",
|
||
"<table border=\"1\" class=\"dataframe\">\n",
|
||
" <thead>\n",
|
||
" <tr style=\"text-align: right;\">\n",
|
||
" <th></th>\n",
|
||
" <th>优惠券名称</th>\n",
|
||
" <th>新开始时间</th>\n",
|
||
" <th>新结束时间</th>\n",
|
||
" <th>手机号</th>\n",
|
||
" <th>客户发券数量</th>\n",
|
||
" <th>门店id</th>\n",
|
||
" </tr>\n",
|
||
" </thead>\n",
|
||
" <tbody>\n",
|
||
" <tr>\n",
|
||
" <th>0</th>\n",
|
||
" <td>测试优惠1</td>\n",
|
||
" <td>2025-08-30</td>\n",
|
||
" <td>2028-08-30</td>\n",
|
||
" <td>10031122309</td>\n",
|
||
" <td>1</td>\n",
|
||
" <td>11240984669917217520</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>1</th>\n",
|
||
" <td>优惠券1</td>\n",
|
||
" <td>2025-09-01</td>\n",
|
||
" <td>2026-09-01</td>\n",
|
||
" <td>10031122309</td>\n",
|
||
" <td>2</td>\n",
|
||
" <td>11240984669917217520</td>\n",
|
||
" </tr>\n",
|
||
" </tbody>\n",
|
||
"</table>\n",
|
||
"</div>"
|
||
]
|
||
},
|
||
"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": [
|
||
"<div>\n",
|
||
"<style scoped>\n",
|
||
" .dataframe tbody tr th:only-of-type {\n",
|
||
" vertical-align: middle;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe tbody tr th {\n",
|
||
" vertical-align: top;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe thead th {\n",
|
||
" text-align: right;\n",
|
||
" }\n",
|
||
"</style>\n",
|
||
"<table border=\"1\" class=\"dataframe\">\n",
|
||
" <thead>\n",
|
||
" <tr style=\"text-align: right;\">\n",
|
||
" <th></th>\n",
|
||
" </tr>\n",
|
||
" </thead>\n",
|
||
" <tbody>\n",
|
||
" </tbody>\n",
|
||
"</table>\n",
|
||
"</div>"
|
||
]
|
||
},
|
||
"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": [
|
||
"<div>\n",
|
||
"<style scoped>\n",
|
||
" .dataframe tbody tr th:only-of-type {\n",
|
||
" vertical-align: middle;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe tbody tr th {\n",
|
||
" vertical-align: top;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe thead th {\n",
|
||
" text-align: right;\n",
|
||
" }\n",
|
||
"</style>\n",
|
||
"<table border=\"1\" class=\"dataframe\">\n",
|
||
" <thead>\n",
|
||
" <tr style=\"text-align: right;\">\n",
|
||
" <th></th>\n",
|
||
" <th>优惠券名称</th>\n",
|
||
" <th>新开始时间</th>\n",
|
||
" <th>新结束时间</th>\n",
|
||
" <th>手机号</th>\n",
|
||
" <th>客户发券数量</th>\n",
|
||
" <th>门店id</th>\n",
|
||
" </tr>\n",
|
||
" </thead>\n",
|
||
" <tbody>\n",
|
||
" <tr>\n",
|
||
" <th>0</th>\n",
|
||
" <td>测试优惠1</td>\n",
|
||
" <td>2025-08-30</td>\n",
|
||
" <td>2028-08-30</td>\n",
|
||
" <td>10031122303</td>\n",
|
||
" <td>1</td>\n",
|
||
" <td>11240984669917217520</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>1</th>\n",
|
||
" <td>优惠券1</td>\n",
|
||
" <td>2025-09-01</td>\n",
|
||
" <td>2026-09-01</td>\n",
|
||
" <td>10031122303</td>\n",
|
||
" <td>1</td>\n",
|
||
" <td>11240984669917217520</td>\n",
|
||
" </tr>\n",
|
||
" </tbody>\n",
|
||
"</table>\n",
|
||
"</div>"
|
||
]
|
||
},
|
||
"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": [
|
||
"<div>\n",
|
||
"<style scoped>\n",
|
||
" .dataframe tbody tr th:only-of-type {\n",
|
||
" vertical-align: middle;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe tbody tr th {\n",
|
||
" vertical-align: top;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe thead th {\n",
|
||
" text-align: right;\n",
|
||
" }\n",
|
||
"</style>\n",
|
||
"<table border=\"1\" class=\"dataframe\">\n",
|
||
" <thead>\n",
|
||
" <tr style=\"text-align: right;\">\n",
|
||
" <th></th>\n",
|
||
" <th>原名称</th>\n",
|
||
" <th>原ID</th>\n",
|
||
" <th>新ID</th>\n",
|
||
" <th>状态</th>\n",
|
||
" <th>开始时间</th>\n",
|
||
" <th>结束时间</th>\n",
|
||
" </tr>\n",
|
||
" </thead>\n",
|
||
" <tbody>\n",
|
||
" <tr>\n",
|
||
" <th>0</th>\n",
|
||
" <td>测试优惠1</td>\n",
|
||
" <td>186141</td>\n",
|
||
" <td>186280</td>\n",
|
||
" <td>成功</td>\n",
|
||
" <td>2025-08-30</td>\n",
|
||
" <td>2028-08-30</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>1</th>\n",
|
||
" <td>优惠券1</td>\n",
|
||
" <td>186142</td>\n",
|
||
" <td>186281</td>\n",
|
||
" <td>成功</td>\n",
|
||
" <td>2025-09-01</td>\n",
|
||
" <td>2026-09-01</td>\n",
|
||
" </tr>\n",
|
||
" </tbody>\n",
|
||
"</table>\n",
|
||
"</div>"
|
||
]
|
||
},
|
||
"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类型: [<class 'int'>]\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": [
|
||
"<div>\n",
|
||
"<style scoped>\n",
|
||
" .dataframe tbody tr th:only-of-type {\n",
|
||
" vertical-align: middle;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe tbody tr th {\n",
|
||
" vertical-align: top;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe thead th {\n",
|
||
" text-align: right;\n",
|
||
" }\n",
|
||
"</style>\n",
|
||
"<table border=\"1\" class=\"dataframe\">\n",
|
||
" <thead>\n",
|
||
" <tr style=\"text-align: right;\">\n",
|
||
" <th></th>\n",
|
||
" <th>数量</th>\n",
|
||
" </tr>\n",
|
||
" </thead>\n",
|
||
" <tbody>\n",
|
||
" <tr>\n",
|
||
" <th>唯一优惠券</th>\n",
|
||
" <td>2</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>未找到优惠券</th>\n",
|
||
" <td>0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>重复优惠券</th>\n",
|
||
" <td>0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>成功创建</th>\n",
|
||
" <td>2</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>创建失败</th>\n",
|
||
" <td>0</td>\n",
|
||
" </tr>\n",
|
||
" </tbody>\n",
|
||
"</table>\n",
|
||
"</div>"
|
||
]
|
||
},
|
||
"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
|
||
}
|