{ "cells": [ { "cell_type": "code", "id": "initial_id", "metadata": { "collapsed": true, "ExecuteTime": { "end_time": "2025-11-05T09:03:45.525420Z", "start_time": "2025-11-05T09:03:44.127181Z" } }, "source": [ "# -*- coding: utf-8 -*-\n", "import pandas as pd\n", "import datetime\n", "from config import Config\n", "from api import API\n", "from back_ground_module import CommonModule\n", "from log_config import configure_task_logger, configure_error_task_logger\n", "import time\n", "timestamp = time.time() # 返回 float，单位：秒\n", "\n", "logger = configure_task_logger()\n", "# 获取已经配置好的错误任务日志记录器\n", "error_task_logger = configure_error_task_logger()\n", "start_time = datetime.datetime.now()\n", "api_instance = API()\n", "common_module = CommonModule()\n", "output_dir = \"output\" # 设置输出目录\n", "# 创建输出目录（如果不存在）\n", "import os\n", "\n", "os.makedirs(output_dir, exist_ok=True)\n", "\n", "\n", "class UpdateNGVData:\n", " \"\"\"NGV数据每日新增\"\"\"\n", "\n", " def __init__(self):\n", " self.staff_id_list = None\n", " self.field_mapping = {}\n", " self.fields()\n", "\n", " def load_all_data(self):\n", " # 获取简道云员工id\n", " payload = {\"api_key\": \"6694d3c4fcb69ca9a111a6c4\",\n", " \"entry_id\": \"6769204a1902c9341340a1bc\",\n", " }\n", " staff_id = api_instance.entry_data_list(payload)\n", " self.staff_id_list = staff_id.get(\"data\") # api请求格式，将数据封装在data字典里\n", "\n", " @staticmethod\n", " def get_staff_id(row_item, name):\n", " \"\"\"辅助函数，用于获取员工ID\"\"\"\n", " if str(row_item[\"_widget_1734942794144\"]) == str(name): # 检查姓名是否匹配\n", " return row_item[\"_widget_1734942794145\"] # 返回员工ID\n", " return None\n", "\n", " def main(self):\n", " task_start_time = datetime.datetime.now().strftime(\"%Y-%m-%d %H:%M:%S\")\n", " try:\n", " self.load_all_data()\n", " logger.info(f\"数据加载完成\")\n", " #\n", " # data_NGV_j = common_module.get_ngv_details(days_back=1)\n", " # data_NGV_j1 = common_module.get_ngv_details(days_back=2)\n", " timestamp = time.time() # 返回 float，单位：秒\n", " #\n", " # data_NGV_j.to_csv(os.path.join(output_dir, f\"{timestamp}up_NGV_j.csv\"))\n", " # data_NGV_j1.to_csv(os.path.join(output_dir, f\"{timestamp}up_NGV_j1.csv\"))\n", " #\n", " # # 找出在 data_NGV_j 中存在但在 data_NGV_j1 中不存在的 data_id\n", " # unique_data_ids = data_NGV_j[~data_NGV_j['org_code'].isin(data_NGV_j1['org_code'])]\n", " #\n", " # # 创建一个新的 DataFrame 保存这些唯一的 data_id 及其对应的数据\n", " # new_df = data_NGV_j[data_NGV_j['org_code'].isin(unique_data_ids['org_code'])]\n", " #\n", " # # 对 new_df 进行进一步的过滤，只保留 org_type 为 \"一般\" 的记录\n", " # data_NGV_j = data_NGV_j[data_NGV_j['org_type'] == '一般']\n", " # data_NGV_j1 = data_NGV_j1[data_NGV_j1['org_type'] == '一般']\n", " # filtered_df = new_df[new_df['org_type'] == '一般']\n", " # 默认未删除\n", " filtered_df = pd.read_excel(r\"C:\\Users\\zy187\\Downloads\\异常服务跟进待办_20251105170140.xlsx\",sheet_name=\"Sheet1\")\n", " filtered_df['源ngv是否已删除'] = '未删除'\n", "\n", " # 日期字段转换为日期格式\n", " time_columns = ['date_fmt', 'saas_create_time', 'expiry_time', 'install_create_time', \"last_end_date\",\n", " \"renew_date\"]\n", " new_filtered_df = filtered_df.copy() # 复制df，以调整时间\n", " for col in time_columns:\n", " # 1. 转换为datetime类型（带错误处理）\n", " # 使用.loc安全赋值\n", " new_filtered_df[col] = pd.to_datetime(filtered_df[col], errors='coerce', utc=False)\n", "\n", " # 2. 优化后的时区转换（高效向量化操作）\n", " filtered_df[col + '_date'] = (\n", " new_filtered_df[col]\n", " # 本地化为北京时间（东八区）\n", " .dt.tz_localize('Asia/Shanghai', ambiguous='infer', nonexistent='NaT')\n", " # 转换为UTC时区\n", " .dt.tz_convert('UTC')\n", " # 格式化为ISO8601字符串\n", " .dt.strftime('%Y-%m-%dT%H:%M:%SZ')\n", " )\n", " logger.info(f\"时间转换完成\")\n", "\n", " # 人员字段转换为人员字段\n", " staff_columns = ['area_manager', 'service_impl_principal', \"service_salesmen\", \"technician\"]\n", " # 将员工列表转为DataFrame\n", " # 三重循环临时方案（确保可写入）\n", " for col in staff_columns:\n", " staff_ids = []\n", " for _, row in filtered_df.iterrows():\n", " matched = False\n", " for staff in self.staff_id_list:\n", " if str(staff['_widget_1734942794144']) == str(row[col]):\n", " staff_ids.append(staff['_widget_1734942794145'])\n", " matched = True\n", " break\n", " if not matched:\n", " staff_ids.append(None)\n", " filtered_df[col + \"_staff_id\"] = staff_ids\n", " logger.info(f\"人员转换完成\")\n", "\n", " # filtered_df.to_csv(r\"D:\\Idea Project\\SaaS_V1.3\\back_ground_module\\output\\NGV.csv\")\n", "\n", " # 生成包含所有行转换后的字典列表\n", " # all_data = [self.row_to_dict(row, self.field_mapping) for index, row in data_NGV_j1.iterrows()] # 前两天的全部数据\n", " # all_data = [self.row_to_dict(row, self.field_mapping) for index, row in data_NGV_j.iterrows()] # 前一天的全部数据\n", " all_data = [self.row_to_dict(row, self.field_mapping) for index, row in filtered_df.iterrows()] # 增量数据\n", "\n", " try:\n", " filtered_df.to_csv(os.path.join(output_dir, f\"{timestamp}NGV.csv\"))\n", " except Exception as e:\n", " error_task_logger.error(f\"NGV过滤后数据保存异常: {e}\")\n", " pass\n", "\n", " #\n", " data = {'api_key': Config.SaaS_Tasks_APP_ID, 'entry_id': Config.NGV_TASKS_ENTRY_ID, \"data_list\": all_data,\n", " \"is_start_trigger\": \"true\"}\n", "\n", " result = api_instance.entry_data_batch_create(data)\n", " logger.info(f\"数据已推送:{result}\")\n", " # result_str = str(result)\n", " # print(result_str[:500])\n", "\n", " # 保存到Excel文件\n", " # output_path = r'D:\\Idea Project\\F6+宜搭+其它(1)\\new\\文件输出\\ngv明细1.xlsx'\n", " # filtered_df.to_excel(output_path, index=False)\n", " # data_NGV_j1.to_excel( r'D:\\Idea Project\\F6+宜搭+其它(1)\\new\\文件输出\\ngv明细j1.xlsx', index=False)\n", " # data_NGV_j.to_excel( r'D:\\Idea Project\\F6+宜搭+其它(1)\\new\\文件输出\\ngv明细j.xlsx', index=False)\n", " # new_df.to_excel(r'D:\\Idea Project\\F6+宜搭+其它(1)\\new\\文件输出\\ngv明细ndf.xlsx', index=False)\n", "\n", " common_module.send_task_status(task_start_time, \"NGV新增数据\")\n", " logger.info(f\"任务完成。\")\n", " except Exception as e:\n", " error_task_logger.error(f\"任务执行时发生异常: {e}\")\n", " # common_module.send_task_error(task_start_time, \"NGV新增数据\", str(e))\n", "\n", " @staticmethod\n", " def row_to_dict(row, field_mapping):\n", " \"\"\"将一行数据转换为指定格式的字典，并确保时间类型可JSON序列化\"\"\"\n", " result = {}\n", " for col_name, widget_id in field_mapping.items():\n", " if col_name in row:\n", " value = row[col_name]\n", " if pd.isna(value):\n", " clean_value = None\n", " elif isinstance(value, (pd.Timestamp, pd.Timedelta)):\n", " clean_value = value.isoformat() # 或 str(value)\n", " elif hasattr(value, 'strftime'): # 兼容 datetime.datetime\n", " clean_value = value.strftime('%Y-%m-%dT%H:%M:%SZ')\n", " else:\n", " clean_value = value\n", " result[widget_id] = {\"value\": clean_value}\n", " return result\n", "\n", " def fields(self):\n", " self.field_mapping = dict(date_id='_widget_1734062123065', date_fmt='_widget_1734062123066',\n", " id_own_group='_widget_1734062123067', group_name='_widget_1734062123068',\n", " id_own_org='_widget_1734062123069', org_name='_widget_1734062123070',\n", " org_code='_widget_1734062123071', group_grade='_widget_1734062123072',\n", " org_type='_widget_1734062123073', org_status='_widget_1734062123074',\n", " saas_version='_widget_1734062123075', is_wechat='_widget_1734062123076',\n", " is_mini_app='_widget_1734062123077', is_wx_shop='_widget_1734062123078',\n", " is_camera_service='_widget_1734062123079',\n", " is_maintenance_service='_widget_1734062123080',\n", " saas_create_time='_widget_1734062123081', expiry_time='_widget_1734062123082',\n", " saas_use_days='_widget_1734062123083', saas_use_year='_widget_1734062123084',\n", " is_main_org='_widget_1734062123085', license_code='_widget_1734062123086',\n", " license_name='_widget_1734062123087', org_crm_id='_widget_1734062123088',\n", " province_id='_widget_1734062123089', province_name='_widget_1734062123090',\n", " city_id='_widget_1734062123091', city_name='_widget_1734062123092',\n", " area_id='_widget_1734062123093', area_name='_widget_1734062123094',\n", " region_name='_widget_1734062123095', region_short_name='_widget_1734062123096',\n", " branch_name='_widget_1734062123097', carzone_store_id='_widget_1734062123098',\n", " carzone_store_name='_widget_1734062123099',\n", " customer_carzone_id='_widget_1734062123100', salesmen='_widget_1734062123101',\n", " area_manager='_widget_1734062123102', service_salesmen='_widget_1734062123103',\n", " impl_principal='_widget_1734062123104',\n", " service_impl_principal='_widget_1734062123105',\n", " active_user_count='_widget_1734062123106', active_user_type='_widget_1734062123107',\n", " limit_user_count='_widget_1734062123108', limit_user_type='_widget_1734062123109',\n", " is_n='_widget_1734062123110', is_g='_widget_1734062123111',\n", " is_v='_widget_1734062123112', is_visited='_widget_1734062123113',\n", " is_active='_widget_1734062123114', active_status_fmt='_widget_1734062123115',\n", " bill_count_last_30_day='_widget_1734062123116',\n", " bill_day_count_last_30_day='_widget_1734062123117',\n", " bill_day_count_this_month='_widget_1734062123118',\n", " bill_count_last_7_day='_widget_1734062123119',\n", " bill_day_count_last_7_day='_widget_1734062123120', pv_count='_widget_1734062123121',\n", " uv_count='_widget_1734062123122', bill_count_1d='_widget_1734062123123',\n", " bill_count_2d='_widget_1734062123124', bill_count_3d='_widget_1734062123125',\n", " bill_count_4d='_widget_1734062123126', bill_count_5d='_widget_1734062123127',\n", " bill_count_6d='_widget_1734062123128', bill_count_7d='_widget_1734062123129',\n", " bill_count_8d='_widget_1734062123130', bill_count_9d='_widget_1734062123131',\n", " bill_count_10d='_widget_1734062123132', bill_count_11d='_widget_1734062123133',\n", " bill_count_12d='_widget_1734062123134', bill_count_13d='_widget_1734062123135',\n", " bill_count_14d='_widget_1734062123136', bill_count_15d='_widget_1734062123137',\n", " bill_count_16d='_widget_1734062123138', bill_count_17d='_widget_1734062123139',\n", " bill_count_18d='_widget_1734062123140', bill_count_19d='_widget_1734062123141',\n", " bill_count_20d='_widget_1734062123142', bill_count_21d='_widget_1734062123143',\n", " bill_count_22d='_widget_1734062123144', bill_count_23d='_widget_1734062123145',\n", " bill_count_24d='_widget_1734062123146', bill_count_25d='_widget_1734062123147',\n", " bill_count_26d='_widget_1734062123148', bill_count_27d='_widget_1734062123149',\n", " bill_count_28d='_widget_1734062123150', bill_count_29d='_widget_1734062123151',\n", " bill_count_30d='_widget_1734062123152', bill_count_31d='_widget_1734062123153',\n", " etl_time='_widget_1734062123154',\n", " maintain_bill_count_last_30_day='_widget_1734062123155',\n", " washing_bill_count_last_30_day='_widget_1734062123156',\n", " maintain_bill_day_count_last_30_day='_widget_1734062123157',\n", " washing_bill_day_count_last_30_day='_widget_1734062123158',\n", " retail_bill_count_last_30_day='_widget_1734062123159',\n", " retail_bill_day_count_last_30_day='_widget_1734062123160',\n", " purchase_bill_count_last_30_day='_widget_1734062123161',\n", " purchase_bill_day_count_last_30_day='_widget_1734062123162',\n", " card_bill_count_last_30_day='_widget_1734062123163',\n", " card_bill_day_count_last_30_day='_widget_1734062123164',\n", " gd_sales_bill_count_last_30_day='_widget_1734062123165',\n", " gd_sales_bill_day_count_last_30_day='_widget_1734062123166',\n", " g_change_flag='_widget_1734062123167', saas_package='_widget_1734062123168',\n", " manage_model='_widget_1734062123169', contacts='_widget_1734062123170',\n", " contact_number='_widget_1734062123171', contact_mobile='_widget_1734062123172',\n", " g_month_count='_widget_1734062123173', g_month_percentage='_widget_1734062123174',\n", " is_install_service='_widget_1734062123175',\n", " install_create_time='_widget_1734062123176', last_end_date='_widget_1734062123177',\n", " renew_date='_widget_1734062123178', is_chain_owner='_widget_1734062123179',\n", " group_org_count='_widget_1734062123180',\n", " recent_bill_warning_days='_widget_1734062123181',\n", " g_change_flag_d='_widget_1734062123182', g_lost_warning_days='_widget_1734062123183',\n", " saas_edition_fmt='_widget_1734062123184', g_flag_1m='_widget_1734062123185',\n", " g_flag_2m='_widget_1734062123186', g_flag_3m='_widget_1734062123187',\n", " g_flag_4m='_widget_1734062123188', g_flag_5m='_widget_1734062123189',\n", " g_flag_6m='_widget_1734062123190', g_flag_day_count='_widget_1734062123191',\n", " add_org_flag='_widget_1734062123192', pt='_widget_1734062123193',\n", " org_size='_widget_1734062123194', qualification_type_fmt='_widget_1734062123195',\n", " business_scope_fmt='_widget_1734062123196', store_type_fmt='_widget_1734062123197',\n", " area='_widget_1734062123198', station_number='_widget_1734062123199',\n", " header_type_fmt='_widget_1734062123200', org_stage='_widget_1734062123201',\n", " g_count_this_month='_widget_1734062123202',\n", " saas_customer_type='_widget_1734062123203', technician='_widget_1734062123204',\n", " tmall_maintain_service_status_desc='_widget_1734062123205',\n", " date_fmt_date='_widget_1749000071375',\n", " area_manager_staff_id='_widget_1748496855779',\n", " service_impl_principal_staff_id=\"_widget_1748496855780\",\n", " service_salesmen_staff_id=\"_widget_1748496855778\",\n", " technician_staff_id=\"_widget_1751877712235\",\n", " saas_create_time_date=\"_widget_1749000071377\",\n", " expiry_time_date=\"_widget_1749000071382\",\n", " install_create_time_date=\"_widget_1749000071384\",\n", " last_end_date_date=\"_widget_1749000071389\", renew_date_date=\"_widget_1749000071391\"\n", " , 源NGV是否已删除=\"_widget_1754285499851\")\n", "\n", "\n", "if __name__ == '__main__':\n", " start = UpdateNGVData()\n", " start.main()\n" ], "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "已获取 100 条数据\n", "已获取 146 条数据\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001B[92m2025-11-05 17:03:45,497 - api.py - task_logger - INFO - 获取了146条数据\u001B[0m\n", "\u001B[92m2025-11-05 17:03:45,498 - 4224831806.py - task_logger - INFO - 数据加载完成\u001B[0m\n", "\u001B[91m2025-11-05 17:03:45,523 - 4224831806.py - error_task_logger - ERROR - 任务执行时发生异常: 'date_fmt'\u001B[0m\n" ] } ], "execution_count": 6 } ], "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 }