{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# -*- coding: utf-8 -*-\n",
    "import psycopg2\n",
    "import pandas as pd\n",
    "# 获得连接\n",
    "conn = psycopg2.connect(database=\"f6_bi\", user=\"BASIC$ro_caowei\", password=\"!ro_caowei123\", host=\"hgprecn-cn-nif1vnv0y002-cn-shanghai.hologres.aliyuncs.com\", port=\"80\")\n",
    "# 获得游标对象，一个游标对象可以对数据库进行执行操作\n",
    "cursor = conn.cursor()\n",
    "\n",
    "import datetime\n",
    "now_time = datetime.datetime.now()\n",
    "yes_time = now_time + datetime.timedelta(days=-1)\n",
    "yes_time_nyr = int(yes_time.strftime('%Y%m%d'))# 获取前一天日期\n",
    "# sql语句 建表\n",
    "sql =f\"\"\"SELECT * FROM \"public\".\"holo_ads_report_saas_profile_ngv_detail_d\" WHERE \"date_id\" = '{yes_time_nyr}' ;\"\"\"\n",
    "# 执行语句\n",
    "cursor.execute(sql)\n",
    "# 获取结果集的每一行\n",
    "rows = cursor.fetchall()\n",
    "# 获取所有字段名\n",
    "all_fields = cursor.description\n",
    "#执行结果转化为dataframe\n",
    "col = []\n",
    "for i in all_fields:\n",
    "    col.append(i[0])\n",
    "data_NGV = pd.DataFrame(list(rows),columns=col)\n",
    "data_NGV.to_excel(r'C:\\Users\\admin\\Desktop\\NGV明细.xlsx')\n",
    "# 关闭数据库连接\n",
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "读取到省市小六技术专家区域客服区域客成表单中 66 条数据！\n",
      "2023-05-11 11\n",
      "2023-07-23 2023-10-31 1262\n",
      "2023-04-14 2023-07-23 1304\n",
      "2023-01-04 2023-04-14 1224\n",
      "2022-09-26 2023-01-04 976\n",
      "2022-06-18 2022-09-26 1326\n",
      "2022-03-10 2022-06-18 1580\n",
      "2021-11-30 2022-03-10 1306\n",
      "2021-08-22 2021-11-30 1689\n",
      "2021-05-14 2021-08-22 0\n",
      "2021-02-03 2021-05-14 0\n",
      "[['693800011139268'], ['106216373838354980'], ['03051357221235925'], ['075458412020533110'], ['0254392654682418'], ['053052302136860181'], ['016222434123279671'], ['080964261223281432'], ['0615271028177307']]\n",
      "{'result': 'a662dae1-7faa-4086-812e-2fdabfe57d2c'} {'dateField_lmelabb1': '1698733604370', 'employeeField_lmeqk429': '693800011139268', 'tableField_lnsi0v71': [{'textField_lnsi0v72': '奚仲车会', 'textField_lnsi0v6s': '基础版', 'employeeField_lnsi0v6u': '055512041727184572', 'employeeField_lnsi0v6v': '1218085201677303', 'employeeField_lnsi0v6w': '693800011139268', 'dateField_lnsi0v6x': '1698733604370', 'textField_lnsi0v6y': '奚仲车会', 'textField_lnsi0v6z': 'CHS202305110229529', 'selectField_lnsi0v6q': '强化培训', 'textareaField_lnsi0v6r': '\\n                1.客户日常使用问题解答；\\n                2.跟客户查看主营分析，推荐进销存功能深度使用；\\n                3.跟客户查看车辆分析，推荐短信及其他营销功能；\\n                4.说明转介绍政策，索要转介绍；'}], 'employeeField_lmeqk423': '1218085201677303', 'employeeField_lmeqk424': '055512041727184572', 'textField_lmu0523h': '自动派发回访计划', 'editorField_lodoplg0': ['root', {}, ['p', {}, ['span', {'data-type': 'text'}, ['span', {'data-type': 'leaf'}, '']], ['a', {'href': 'https://f6car.aliwork.com/APP_UYZ0KG6L0CCNV80GZ66O/formDetail/FORM-ZK866D91O9LA4NIHCARG2DPIPCXF3Z087PPHL91?formInstId=FINST-IT5662C186KF3Z3K9C28H489DIFZ2DRP7KDOLR8&isAdmin=true'}, ['span', {'data-type': 'text'}, ['span', {'unlink': {}, 'data-type': 'leaf'}, '点击查看门店NGV']]], ['span', {'data-type': 'text'}, ['span', {'unlink': {}, 'data-type': 'leaf'}, '']]]]}\n",
      "{'result': 'd516db5d-292d-4248-a6d2-17ecb64f634d'} {'dateField_lmelabb1': '1698733604370', 'employeeField_lmeqk429': '106216373838354980', 'tableField_lnsi0v71': [{'textField_lnsi0v72': '靓车管家(欧尚超市三楼)', 'textField_lnsi0v6s': '旗舰版', 'employeeField_lnsi0v6u': '20590707351213970', 'employeeField_lnsi0v6v': '311003461041349', 'employeeField_lnsi0v6w': '106216373838354980', 'dateField_lnsi0v6x': '1698733604370', 'textField_lnsi0v6y': '靓车管家(欧尚超市三楼)', 'textField_lnsi0v6z': 'CHS202305110229543', 'selectField_lnsi0v6q': '强化培训', 'textareaField_lnsi0v6r': '\\n                1.客户日常使用问题解答；\\n                2.跟客户查看主营分析，推荐进销存功能深度使用；\\n                3.跟客户查看车辆分析，推荐短信及其他营销功能；\\n                4.说明转介绍政策，索要转介绍；'}], 'employeeField_lmeqk423': '311003461041349', 'employeeField_lmeqk424': '20590707351213970', 'textField_lmu0523h': '自动派发回访计划', 'editorField_lodoplg0': ['root', {}, ['p', {}, ['span', {'data-type': 'text'}, ['span', {'data-type': 'leaf'}, '']], ['a', {'href': 'https://f6car.aliwork.com/APP_UYZ0KG6L0CCNV80GZ66O/formDetail/FORM-ZK866D91O9LA4NIHCARG2DPIPCXF3Z087PPHL91?formInstId=FINST-9T566BD1AVJFY8OJ6H6OODMIQG6U31JC3KDOLS2&isAdmin=true'}, ['span', {'data-type': 'text'}, ['span', {'unlink': {}, 'data-type': 'leaf'}, '点击查看门店NGV']]], ['span', {'data-type': 'text'}, ['span', {'unlink': {}, 'data-type': 'leaf'}, '']]]]}\n",
      "{'result': '0e16f7da-1d42-4363-8b0a-2bbc4f9f5f82'} {'dateField_lmelabb1': '1698733604370', 'employeeField_lmeqk429': '03051357221235925', 'tableField_lnsi0v71': [{'textField_lnsi0v72': '元宝山区陆航汽车服务中心', 'textField_lnsi0v6s': '基础版', 'employeeField_lnsi0v6u': '052909055823302010', 'employeeField_lnsi0v6v': '025366033037741985', 'employeeField_lnsi0v6w': '03051357221235925', 'dateField_lnsi0v6x': '1698733604370', 'textField_lnsi0v6y': '元宝山区陆航汽车服务中心', 'textField_lnsi0v6z': 'CHS202305110229630', 'selectField_lnsi0v6q': '强化培训', 'textareaField_lnsi0v6r': '\\n                1.客户日常使用问题解答；\\n                2.跟客户查看主营分析，推荐进销存功能深度使用；\\n                3.跟客户查看车辆分析，推荐短信及其他营销功能；\\n                4.说明转介绍政策，索要转介绍；'}], 'employeeField_lmeqk423': '025366033037741985', 'employeeField_lmeqk424': '052909055823302010', 'textField_lmu0523h': '自动派发回访计划', 'editorField_lodoplg0': ['root', {}, ['p', {}, ['span', {'data-type': 'text'}, ['span', {'data-type': 'leaf'}, '']], ['a', {'href': 'https://f6car.aliwork.com/APP_UYZ0KG6L0CCNV80GZ66O/formDetail/FORM-ZK866D91O9LA4NIHCARG2DPIPCXF3Z087PPHL91?formInstId=FINST-09866181DVJFNPCL7M4XV5ZZ2XK92QAQ7KDOLJE&isAdmin=true'}, ['span', {'data-type': 'text'}, ['span', {'unlink': {}, 'data-type': 'leaf'}, '点击查看门店NGV']]], ['span', {'data-type': 'text'}, ['span', {'unlink': {}, 'data-type': 'leaf'}, '']]]]}\n",
      "{'result': '22353284-5c51-4c5a-baa8-df2efc9f9e44'} {'dateField_lmelabb1': '1698733604370', 'employeeField_lmeqk429': '03051357221235925', 'tableField_lnsi0v71': [{'textField_lnsi0v72': 'CC养车漆彩钣喷保养店', 'textField_lnsi0v6s': '尊享版', 'employeeField_lnsi0v6u': '052909055823302010', 'employeeField_lnsi0v6v': '025366033037741985', 'employeeField_lnsi0v6w': '03051357221235925', 'dateField_lnsi0v6x': '1698733604370', 'textField_lnsi0v6y': 'CC养车漆彩钣喷保养店', 'textField_lnsi0v6z': 'CHS202305110229503', 'selectField_lnsi0v6q': '强化培训', 'textareaField_lnsi0v6r': '\\n                1.客户日常使用问题解答；\\n                2.跟客户查看主营分析，推荐进销存功能深度使用；\\n                3.跟客户查看车辆分析，推荐短信及其他营销功能；\\n                4.说明转介绍政策，索要转介绍；'}], 'employeeField_lmeqk423': '025366033037741985', 'employeeField_lmeqk424': '052909055823302010', 'textField_lmu0523h': '自动派发回访计划', 'editorField_lodoplg0': ['root', {}, ['p', {}, ['span', {'data-type': 'text'}, ['span', {'data-type': 'leaf'}, '']], ['a', {'href': 'https://f6car.aliwork.com/APP_UYZ0KG6L0CCNV80GZ66O/formDetail/FORM-ZK866D91O9LA4NIHCARG2DPIPCXF3Z087PPHL91?formInstId=FINST-09866181DVJFNPCL7M4XV5ZZ2XK92RAQ7KDOL9F&isAdmin=true'}, ['span', {'data-type': 'text'}, ['span', {'unlink': {}, 'data-type': 'leaf'}, '点击查看门店NGV']]], ['span', {'data-type': 'text'}, ['span', {'unlink': {}, 'data-type': 'leaf'}, '']]]]}\n",
      "{'result': 'b1db656b-408f-435f-ad25-33f759ffbe50'} {'dateField_lmelabb1': '1698733604370', 'employeeField_lmeqk429': '075458412020533110', 'tableField_lnsi0v71': [{'textField_lnsi0v72': '小鹏养车', 'textField_lnsi0v6s': '旗舰版', 'employeeField_lnsi0v6u': '054169455736370109', 'employeeField_lnsi0v6v': '025366033037741985', 'employeeField_lnsi0v6w': '075458412020533110', 'dateField_lnsi0v6x': '1698733604370', 'textField_lnsi0v6y': '小鹏养车', 'textField_lnsi0v6z': 'CHS202305040228236', 'selectField_lnsi0v6q': '强化培训', 'textareaField_lnsi0v6r': '\\n                1.客户日常使用问题解答；\\n                2.跟客户查看主营分析，推荐进销存功能深度使用；\\n                3.跟客户查看车辆分析，推荐短信及其他营销功能；\\n                4.说明转介绍政策，索要转介绍；'}], 'employeeField_lmeqk423': '025366033037741985', 'employeeField_lmeqk424': '054169455736370109', 'textField_lmu0523h': '自动派发回访计划', 'editorField_lodoplg0': ['root', {}, ['p', {}, ['span', {'data-type': 'text'}, ['span', {'data-type': 'leaf'}, '']], ['a', {'href': 'https://f6car.aliwork.com/APP_UYZ0KG6L0CCNV80GZ66O/formDetail/FORM-ZK866D91O9LA4NIHCARG2DPIPCXF3Z087PPHL91?formInstId=FINST-09866181DVJFNPCL7M4XV5ZZ2XK92PAQ7KDOL6E&isAdmin=true'}, ['span', {'data-type': 'text'}, ['span', {'unlink': {}, 'data-type': 'leaf'}, '点击查看门店NGV']]], ['span', {'data-type': 'text'}, ['span', {'unlink': {}, 'data-type': 'leaf'}, '']]]]}\n",
      "{'result': '75383596-25fb-45b2-8fd1-7cb0168d11c6'} {'dateField_lmelabb1': '1698733604370', 'employeeField_lmeqk429': '0254392654682418', 'tableField_lnsi0v71': [{'textField_lnsi0v72': '佳程汽车维修', 'textField_lnsi0v6s': '基础版', 'employeeField_lnsi0v6u': '055512041727184572', 'employeeField_lnsi0v6v': '1218085201677303', 'employeeField_lnsi0v6w': '0254392654682418', 'dateField_lnsi0v6x': '1698733604370', 'textField_lnsi0v6y': '佳程汽车维修', 'textField_lnsi0v6z': 'CHS202305110229561', 'selectField_lnsi0v6q': '强化培训', 'textareaField_lnsi0v6r': '\\n                1.客户日常使用问题解答；\\n                2.跟客户查看主营分析，推荐进销存功能深度使用；\\n                3.跟客户查看车辆分析，推荐短信及其他营销功能；\\n                4.说明转介绍政策，索要转介绍；'}], 'employeeField_lmeqk423': '1218085201677303', 'employeeField_lmeqk424': '055512041727184572', 'textField_lmu0523h': '自动派发回访计划', 'editorField_lodoplg0': ['root', {}, ['p', {}, ['span', {'data-type': 'text'}, ['span', {'data-type': 'leaf'}, '']], ['a', {'href': 'https://f6car.aliwork.com/APP_UYZ0KG6L0CCNV80GZ66O/formDetail/FORM-ZK866D91O9LA4NIHCARG2DPIPCXF3Z087PPHL91?formInstId=FINST-09866181DVJFNPCL7M4XV5ZZ2XK92PAQ7KDOLOD&isAdmin=true'}, ['span', {'data-type': 'text'}, ['span', {'unlink': {}, 'data-type': 'leaf'}, '点击查看门店NGV']]], ['span', {'data-type': 'text'}, ['span', {'unlink': {}, 'data-type': 'leaf'}, '']]]]}\n",
      "{'result': 'f8ca1119-bb7d-4598-a668-eddc6a75d237'} {'dateField_lmelabb1': '1698733604370', 'employeeField_lmeqk429': '053052302136860181', 'tableField_lnsi0v71': [{'textField_lnsi0v72': '爱出彩汽车服务', 'textField_lnsi0v6s': '基础版', 'employeeField_lnsi0v6u': '2626065538702722', 'employeeField_lnsi0v6v': '1525590028775887', 'employeeField_lnsi0v6w': '053052302136860181', 'dateField_lnsi0v6x': '1698733604370', 'textField_lnsi0v6y': '爱出彩汽车服务', 'textField_lnsi0v6z': 'CHS202305110229642', 'selectField_lnsi0v6q': '强化培训', 'textareaField_lnsi0v6r': '\\n                1.客户日常使用问题解答；\\n                2.跟客户查看主营分析，推荐进销存功能深度使用；\\n                3.跟客户查看车辆分析，推荐短信及其他营销功能；\\n                4.说明转介绍政策，索要转介绍；'}], 'employeeField_lmeqk423': '1525590028775887', 'employeeField_lmeqk424': '2626065538702722', 'textField_lmu0523h': '自动派发回访计划', 'editorField_lodoplg0': ['root', {}, ['p', {}, ['span', {'data-type': 'text'}, ['span', {'data-type': 'leaf'}, '']], ['a', {'href': 'https://f6car.aliwork.com/APP_UYZ0KG6L0CCNV80GZ66O/formDetail/FORM-ZK866D91O9LA4NIHCARG2DPIPCXF3Z087PPHL91?formInstId=FINST-T99666B1MUJF4A3P7WMVJC80FBWM2KVQ7KDOLZF&isAdmin=true'}, ['span', {'data-type': 'text'}, ['span', {'unlink': {}, 'data-type': 'leaf'}, '点击查看门店NGV']]], ['span', {'data-type': 'text'}, ['span', {'unlink': {}, 'data-type': 'leaf'}, '']]]]}\n",
      "{'result': '9a02831c-f0cc-48ce-87c3-64ddae247ce0'} {'dateField_lmelabb1': '1698733604370', 'employeeField_lmeqk429': '016222434123279671', 'tableField_lnsi0v71': [{'textField_lnsi0v72': '鞍山市腾飞宝业精车维护驿站', 'textField_lnsi0v6s': '基础版', 'employeeField_lnsi0v6u': '052909055823302010', 'employeeField_lnsi0v6v': '025366033037741985', 'employeeField_lnsi0v6w': '016222434123279671', 'dateField_lnsi0v6x': '1698733604370', 'textField_lnsi0v6y': '鞍山市腾飞宝业精车维护驿站', 'textField_lnsi0v6z': 'CHS202103160116956', 'selectField_lnsi0v6q': '强化培训', 'textareaField_lnsi0v6r': '\\n                1.客户日常使用问题解答；\\n                2.跟客户查看主营分析，推荐进销存功能深度使用；\\n                3.跟客户查看车辆分析，推荐短信及其他营销功能；\\n                4.说明转介绍政策，索要转介绍；'}], 'employeeField_lmeqk423': '025366033037741985', 'employeeField_lmeqk424': '052909055823302010', 'textField_lmu0523h': '自动派发回访计划', 'editorField_lodoplg0': ['root', {}, ['p', {}, ['span', {'data-type': 'text'}, ['span', {'data-type': 'leaf'}, '']], ['a', {'href': 'https://f6car.aliwork.com/APP_UYZ0KG6L0CCNV80GZ66O/formDetail/FORM-ZK866D91O9LA4NIHCARG2DPIPCXF3Z087PPHL91?formInstId=FINST-9H666R71VSJFBN1PFSRBTA3BTX4N3WZL3KDOLH&isAdmin=true'}, ['span', {'data-type': 'text'}, ['span', {'unlink': {}, 'data-type': 'leaf'}, '点击查看门店NGV']]], ['span', {'data-type': 'text'}, ['span', {'unlink': {}, 'data-type': 'leaf'}, '']]]]}\n",
      "{'result': '47b39d40-74d8-4ee9-81ff-d32fbb4e00cb'} {'dateField_lmelabb1': '1698733604370', 'employeeField_lmeqk429': '080964261223281432', 'tableField_lnsi0v71': [{'textField_lnsi0v72': '友通汽车服务', 'textField_lnsi0v6s': '标准版', 'employeeField_lnsi0v6u': '0103433535667605', 'employeeField_lnsi0v6v': '232229053125844557', 'employeeField_lnsi0v6w': '080964261223281432', 'dateField_lnsi0v6x': '1698733604370', 'textField_lnsi0v6y': '友通汽车服务', 'textField_lnsi0v6z': 'CHS202305110229625', 'selectField_lnsi0v6q': '强化培训', 'textareaField_lnsi0v6r': '\\n                1.客户日常使用问题解答；\\n                2.跟客户查看主营分析，推荐进销存功能深度使用；\\n                3.跟客户查看车辆分析，推荐短信及其他营销功能；\\n                4.说明转介绍政策，索要转介绍；'}], 'employeeField_lmeqk423': '232229053125844557', 'employeeField_lmeqk424': '0103433535667605', 'textField_lmu0523h': '自动派发回访计划', 'editorField_lodoplg0': ['root', {}, ['p', {}, ['span', {'data-type': 'text'}, ['span', {'data-type': 'leaf'}, '']], ['a', {'href': 'https://f6car.aliwork.com/APP_UYZ0KG6L0CCNV80GZ66O/formDetail/FORM-ZK866D91O9LA4NIHCARG2DPIPCXF3Z087PPHL91?formInstId=FINST-T99666B1MUJF4A3P7WMVJC80FBWM2JVQ7KDOL7F&isAdmin=true'}, ['span', {'data-type': 'text'}, ['span', {'unlink': {}, 'data-type': 'leaf'}, '点击查看门店NGV']]], ['span', {'data-type': 'text'}, ['span', {'unlink': {}, 'data-type': 'leaf'}, '']]]]}\n",
      "{'result': '85c158f6-088d-43ca-b815-9c8a3f859a07'} {'dateField_lmelabb1': '1698733604370', 'employeeField_lmeqk429': '0615271028177307', 'tableField_lnsi0v71': [{'textField_lnsi0v72': '黑马养车', 'textField_lnsi0v6s': '标准版', 'employeeField_lnsi0v6u': '180720602439816818', 'employeeField_lnsi0v6v': '1525590028775887', 'employeeField_lnsi0v6w': '0615271028177307', 'dateField_lnsi0v6x': '1698733604370', 'textField_lnsi0v6y': '黑马养车', 'textField_lnsi0v6z': 'CHS202305110229501', 'selectField_lnsi0v6q': '强化培训', 'textareaField_lnsi0v6r': '\\n                1.客户日常使用问题解答；\\n                2.跟客户查看主营分析，推荐进销存功能深度使用；\\n                3.跟客户查看车辆分析，推荐短信及其他营销功能；\\n                4.说明转介绍政策，索要转介绍；'}], 'employeeField_lmeqk423': '1525590028775887', 'employeeField_lmeqk424': '180720602439816818', 'textField_lmu0523h': '自动派发回访计划', 'editorField_lodoplg0': ['root', {}, ['p', {}, ['span', {'data-type': 'text'}, ['span', {'data-type': 'leaf'}, '']], ['a', {'href': 'https://f6car.aliwork.com/APP_UYZ0KG6L0CCNV80GZ66O/formDetail/FORM-ZK866D91O9LA4NIHCARG2DPIPCXF3Z087PPHL91?formInstId=FINST-T99666B1MUJF4A3P7WMVJC80FBWM2JVQ7KDOL6F&isAdmin=true'}, ['span', {'data-type': 'text'}, ['span', {'unlink': {}, 'data-type': 'leaf'}, '点击查看门店NGV']]], ['span', {'data-type': 'text'}, ['span', {'unlink': {}, 'data-type': 'leaf'}, '']]]]}\n"
     ]
    }
   ],
   "source": [
    "# -*- coding: utf-8 -*-\n",
    "import psycopg2\n",
    "import pandas as pd\n",
    "# 获得连接\n",
    "conn = psycopg2.connect(database=\"f6_bi\", user=\"BASIC$ro_caowei\", password=\"!ro_caowei123\", host=\"hgprecn-cn-nif1vnv0y002-cn-shanghai.hologres.aliyuncs.com\", port=\"80\")\n",
    "# 获得游标对象，一个游标对象可以对数据库进行执行操作\n",
    "cursor = conn.cursor()\n",
    "\n",
    "import datetime\n",
    "now_time = datetime.datetime.now()\n",
    "yes_time = now_time + datetime.timedelta(days=-1)\n",
    "yes_time_nyr = int(yes_time.strftime('%Y%m%d'))# 获取前一天日期\n",
    "# sql语句 建表\n",
    "sql =f\"\"\"SELECT * FROM \"public\".\"holo_ads_report_saas_profile_ngv_detail_d\" WHERE \"date_id\" = '{yes_time_nyr}' ;\"\"\"\n",
    "# 执行语句\n",
    "cursor.execute(sql)\n",
    "# 获取结果集的每一行\n",
    "rows = cursor.fetchall()\n",
    "# 获取所有字段名\n",
    "all_fields = cursor.description\n",
    "#执行结果转化为dataframe\n",
    "col = []\n",
    "for i in all_fields:\n",
    "    col.append(i[0])\n",
    "data_NGV = pd.DataFrame(list(rows),columns=col)\n",
    "# data_NGV.to_excel(r'C:\\Users\\admin\\Desktop\\NGV明细.xlsx')\n",
    "# 关闭数据库连接\n",
    "cursor.close()\n",
    "conn.close()\n",
    "import pandas as pd\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import requests\n",
    "import json\n",
    "import time\n",
    "import re\n",
    "from datetime import datetime\n",
    "from dateutil.relativedelta import relativedelta\n",
    "from pathlib import Path\n",
    "from urllib.parse import quote\n",
    "from io import BytesIO\n",
    "\n",
    "ROOT = Path('.').absolute()  # 当前工作目录\n",
    "\n",
    "\n",
    "# 生成 token，参数不需要修改\n",
    "def generateToken() -> str:\n",
    "    \"\"\" 生成 token \"\"\"\n",
    "\n",
    "    token_api = 'https://api.dingtalk.com/v1.0/oauth2/accessToken'\n",
    "\n",
    "    # 该信息在钉钉开放应用中\n",
    "    data = {\n",
    "        \"appKey\": \"ding5kqocon5s9oph5uq\",\n",
    "        \"appSecret\": 'HL1jgsIIfLAC0eTH0A1m4mwxUDqbgsiPeCCGGE3ocM6qJBTIW7Ivt9drxF_Z4Kb_'\n",
    "    }\n",
    "\n",
    "    res = requests.post(token_api, json=data)\n",
    "    token = res.json()['accessToken']\n",
    "\n",
    "    return token\n",
    "\n",
    "def read_instances(token, formUuid, page, n,formatted_today,formatted_today_two):\n",
    "    \"\"\" 函数功能：读取普通表单的所有数据 \"\"\"\n",
    "\n",
    "    api = f'https://api.dingtalk.com//v1.0/yida/forms/instances/search'\n",
    "\n",
    "    headers = {\n",
    "        \"Content-Type\": \"application/json\",\n",
    "        \"x-acs-dingtalk-access-token\": token\n",
    "    }\n",
    "\n",
    "    formData = {\n",
    "        \"appType\" : \"APP_UYZ0KG6L0CCNV80GZ66O\",\n",
    "        \"systemToken\" : \"XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2\",\n",
    "        \"userId\" : \"yida_pub_account\",\n",
    "        \"language\" : \"zh_CN\",\n",
    "        \"formUuid\" : formUuid,\n",
    "        \"createFromTimeGMT\" : formatted_today,\n",
    "        \"createToTimeGMT\" : formatted_today_two,\n",
    "        \"currentPage\" : page,\n",
    "        \"pageSize\" : n\n",
    "    }\n",
    "\n",
    "    res = requests.post(api, headers=headers, json=formData)\n",
    "    return res.json()\n",
    "\n",
    "def initiate_process(TOKEN,formUuid,processCode,formData):\n",
    "    \"\"\" 发起宜搭审批流程   \"\"\"\n",
    "    api = f'https://api.dingtalk.com//v1.0/yida/processes/instances/start'\n",
    "\n",
    "    headers = {\n",
    "        \"Content-Type\": \"application/json\",\n",
    "        \"x-acs-dingtalk-access-token\": TOKEN\n",
    "    }\n",
    "    payload = {\n",
    "        \"appType\" : \"APP_UYZ0KG6L0CCNV80GZ66O\",\n",
    "        \"systemToken\" : \"XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2\",\n",
    "        \"userId\" : \"yida_pub_account\",\n",
    "        \"language\" : \"zh_CN\",\n",
    "        \"formUuid\" : formUuid,\n",
    "        \"formDataJson\" : json.dumps(formData, cls=NpEncoder),\n",
    "        \"processCode\" : processCode,\n",
    "        }\n",
    "\n",
    "    res = requests.post(api, headers=headers, json=payload)\n",
    "    return res.json()\n",
    "def read_instances_new(token, formUuid, page, n):\n",
    "    \"\"\" 函数功能：读取普通表单的所有数据 \"\"\"\n",
    "\n",
    "    api = f'https://api.dingtalk.com//v1.0/yida/forms/instances/search'\n",
    "\n",
    "    headers = {\n",
    "        \"Content-Type\": \"application/json\",\n",
    "        \"x-acs-dingtalk-access-token\": token\n",
    "    }\n",
    "\n",
    "    formData = {\n",
    "  \"appType\" : \"APP_UYZ0KG6L0CCNV80GZ66O\",\n",
    "  \"systemToken\" : \"XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2\",\n",
    "  \"userId\" : \"yida_pub_account\",\n",
    "  \"language\" : \"zh_CN\",\n",
    "  \"formUuid\" : formUuid,\n",
    "  \"currentPage\" : page,\n",
    "  \"pageSize\" : n\n",
    "    }\n",
    "\n",
    "    res = requests.post(api, headers=headers, json=formData)\n",
    "    return res.json()\n",
    "def read_instances_ngv(token, formUuid, page, n,searchField):\n",
    "    \"\"\" 函数功能：读取普通表单的所有数据 \"\"\"\n",
    "\n",
    "    api = f'https://api.dingtalk.com//v1.0/yida/forms/instances/search'\n",
    "\n",
    "    headers = {\n",
    "        \"Content-Type\": \"application/json\",\n",
    "        \"x-acs-dingtalk-access-token\": token\n",
    "    }\n",
    "\n",
    "    formData = {\n",
    "        \"appType\" : \"APP_UYZ0KG6L0CCNV80GZ66O\",\n",
    "        \"systemToken\" : \"XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2\",\n",
    "        \"userId\" : \"yida_pub_account\",\n",
    "        \"language\" : \"zh_CN\",\n",
    "        \"formUuid\" : formUuid,\n",
    "        \"searchFieldJson\": json.dumps(searchField),\n",
    "        \"currentPage\" : page,\n",
    "        \"pageSize\" : n\n",
    "    }\n",
    "\n",
    "    res = requests.post(api, headers=headers, json=formData)\n",
    "    return res.json()\n",
    "class NpEncoder(json.JSONEncoder):\n",
    "    def default(self, obj):\n",
    "        if isinstance(obj, np.integer):\n",
    "            return int(obj)\n",
    "        elif isinstance(obj, np.floating):\n",
    "            return float(obj)\n",
    "        elif isinstance(obj, np.ndarray):\n",
    "            return obj.tolist()\n",
    "        else:\n",
    "            return super(NpEncoder, self).default(obj)\n",
    "        \n",
    "TOKEN = generateToken()\n",
    "import datetime\n",
    "now_time = datetime.datetime.now()\n",
    "today = now_time + datetime.timedelta(days=-173)\n",
    "formatted_today = today.strftime(\"%Y-%m-%d\")\n",
    "today_two = now_time + datetime.timedelta(days=-172)\n",
    "formatted_today_two = today_two.strftime(\"%Y-%m-%d\")\n",
    "timestamp_ms = int(time.time() * 1000) # 当然日期时间戳 毫秒级\n",
    "'''读取省市小六技术专家区域客服区域客成 '''\n",
    "FORMID = \"FORM-TP866D918DFCA4FW79YZU5X43FO32QZJQDZJL7\"  #省市小六技术专家区域客服区域客成\n",
    "# 读取流程表单数据\n",
    "form_data = read_instances_new(token=TOKEN, formUuid=FORMID, page=1, n=100)\n",
    "PAGES = form_data.get('totalCount')//100 + 1\n",
    "textField_gif29wy = {}\n",
    "textField_3athky8 = {}\n",
    "textField_3hgho1m = {}\n",
    "textField_nc7gskc = {}\n",
    "textField_qk1e5di = {}\n",
    "\"\"\"  获取全量数据  \"\"\"\n",
    "for i in range(1, PAGES+1):\n",
    "    form_data = read_instances_new(token=TOKEN, formUuid=FORMID, page=i, n=100)\n",
    "    for data in form_data.get('data'):\n",
    "        textField_gif29wy[data['formData']['textField_3hgho1m']]=data['formData']['textField_gif29wy'] #区域客成id\n",
    "        textField_3athky8[data['formData']['textField_3hgho1m']]=data['formData']['textField_3athky8'] #区域客服id\n",
    "        textField_3hgho1m[data['formData']['textField_3hgho1m']]=data['formData']['textField_3hgho1m'] #小六id\n",
    "        textField_nc7gskc[data['formData']['textField_3hgho1m']]=data['formData']['textField_nc7gskc'] #技术专家id\n",
    "        textField_qk1e5di[data['formData']['textField_3hgho1m']]=data['formData']['textField_lntniove'] #区域经理id\n",
    "print(f'读取到省市小六技术专家区域客服区域客成表单中 {len(textField_gif29wy)} 条数据！')\n",
    "# [流程]新签服务流程  读取待处理数据\n",
    "json_data = []\n",
    "read_data = read_instances(TOKEN, \"FORM-9X766NA1SOATBRSH2K42D8BJCX7L326HI64TKG5\", 1, 100,formatted_today,formatted_today_two)\n",
    "PAGES = read_data.get('totalCount')//100 + 1\n",
    "print(formatted_today,read_data.get('totalCount'))\n",
    "for a in range(1,PAGES + 1):\n",
    "    read_data = read_instances(TOKEN, \"FORM-9X766NA1SOATBRSH2K42D8BJCX7L326HI64TKG5\", a, 100,formatted_today,formatted_today_two)  \n",
    "    for i in range(0,len(read_data[\"data\"])):\n",
    "        json_data.append(read_data[\"data\"][i]['formData'])\n",
    "# [流程]新签服务流程  读取待处理数据\n",
    "json_data_all = []\n",
    "for i in range(0,1000,100):\n",
    "    now_time = datetime.datetime.now()\n",
    "    today = now_time + datetime.timedelta(days=-(i+100))\n",
    "    formatted_today = today.strftime(\"%Y-%m-%d\")\n",
    "    today_two = now_time + datetime.timedelta(days=-i)\n",
    "    formatted_today_two = today_two.strftime(\"%Y-%m-%d\")\n",
    "    timestamp_ms = int(time.time() * 1000) # 当然日期时间戳 毫秒级\n",
    "    read_data = read_instances(TOKEN, \"FORM-9X766NA1SOATBRSH2K42D8BJCX7L326HI64TKG5\", 1, 100,formatted_today,formatted_today_two)\n",
    "    PAGES = read_data.get('totalCount')//100 + 1\n",
    "    print(formatted_today,formatted_today_two,read_data.get('totalCount'))\n",
    "    for a in range(1,PAGES + 1):\n",
    "        read_data = read_instances(TOKEN, \"FORM-9X766NA1SOATBRSH2K42D8BJCX7L326HI64TKG5\", a, 100,formatted_today,formatted_today_two)  \n",
    "        for i in range(0,len(read_data[\"data\"])):\n",
    "            json_data_all.append(read_data[\"data\"][i]['formData'])\n",
    "df = pd.DataFrame(json_data_all)\n",
    "# 将所有字段设置为字符串类型\n",
    "df = df.astype(str)\n",
    "data_NGV = data_NGV[['org_code', 'id_own_group']]\n",
    "data_NGV = data_NGV.rename(columns={'org_code': 'orgCode'})\n",
    "result = pd.merge(df, data_NGV, on='orgCode', how='left')\n",
    "data_details_not_null = result.sort_values(by='openTime', ascending=True).drop_duplicates(subset='id_own_group')\n",
    "# 重置索引\n",
    "data_details_not_null = data_details_not_null.reset_index(drop=True)\n",
    "data_details_all = data_details_not_null.copy()  # 替换名称 v2 \n",
    "# 获取待回访人员信息+去重\n",
    "name_list = []\n",
    "for data in json_data:\n",
    "    name_list.append(data['employeeField_kyi1dqth_id'])\n",
    "unique_arr = []\n",
    "[unique_arr.append(x) for x in name_list if x not in unique_arr]\n",
    "print(unique_arr)\n",
    "\n",
    "# 每日工作计划\n",
    "formUuid = \"FORM-4V966N81OMEEH85QDP8XW4AH1AIZ2GKRLZTML21\"\n",
    "processCode = \"TPROC--4V966N81OMEEH85QDP8XW4AH1AIZ21ORLZTML31\"\n",
    "# 遍历数据并进行创建\n",
    "for name in unique_arr:\n",
    "    tableField_lnsi0v71  = {}\n",
    "    tableField_lnsi0v71['dateField_lmelabb1'] = \"\"\n",
    "    tableField_lnsi0v71['employeeField_lmeqk429'] = \"\"\n",
    "    for data in json_data:\n",
    "        if data['employeeField_kyi1dqth_id'] == name and data['orgCode'] in data_details_all['orgCode'].values:\n",
    "            data_one = {} # 子表单\n",
    "            data_two = {} # 主表单\n",
    "            # 子表单\n",
    "            data_one['textField_lnsi0v72'] = data['orgName']\n",
    "            data_one['textField_lnsi0v6s'] = data[\"saasEdition\"]  # 销售版本\n",
    "            try:\n",
    "                data_one['employeeField_lnsi0v6u'] = textField_nc7gskc[name[0]]  # 技术专家\n",
    "                data_one['employeeField_lnsi0v6v'] = textField_qk1e5di[name[0]]   # 区域经理\n",
    "                if textField_3hgho1m[name[0]] ==\"\":\n",
    "                    data_one['employeeField_lnsi0v6w'] = data['technician_id'][0]  # 运营顾问\n",
    "                else:\n",
    "                    data_one['employeeField_lnsi0v6w'] = textField_3hgho1m[name[0]]  # 运营顾问\n",
    "            except:\n",
    "                data_one['employeeField_lnsi0v6u'] = data['technician_id'][0]  # 技术专家\n",
    "                data_one['employeeField_lnsi0v6v'] = data['manager_id'][0] # 区域经理\n",
    "                data_one['employeeField_lnsi0v6w'] = data['technician_id'][0]  # 运营顾问\n",
    "            data_one['dateField_lnsi0v6x'] = str(timestamp_ms)  # 计划日期\n",
    "            data_one['textField_lnsi0v6y'] = data[\"orgName\"]  # 门店名称-拷贝\n",
    "            data_one['textField_lnsi0v6z'] = data[\"orgCode\"]  # 门店编码\n",
    "            data_one['selectField_lnsi0v6q'] = \"强化培训\" # 拜访目的\n",
    "            # data_one['textField_lnsi0v70'] = data_result.loc[i,\"id_own_group\"] # 公司id\n",
    "            data_one['textareaField_lnsi0v6r'] = '''\n",
    "                1.客户日常使用问题解答；\n",
    "                2.跟客户查看主营分析，推荐进销存功能深度使用；\n",
    "                3.跟客户查看车辆分析，推荐短信及其他营销功能；\n",
    "                4.说明转介绍政策，索要转介绍；''' # 拜访目的说明\n",
    "            # 加入数据\n",
    "            tableField_lnsi0v71['tableField_lnsi0v71'] = [data_one]\n",
    "            # 主表单\n",
    "            # data_two['dateField_lmelabb1'] = str(timestamp_ms)\n",
    "            # data_two['employeeField_lmeqk429'] = name\n",
    "            tableField_lnsi0v71['dateField_lmelabb1'] = str(timestamp_ms)\n",
    "            try:\n",
    "                if name[0] ==\"\":\n",
    "                    tableField_lnsi0v71['employeeField_lmeqk429'] = data['technician_id'][0] # 运营顾问\n",
    "                else:\n",
    "                    tableField_lnsi0v71['employeeField_lmeqk429'] = name[0]\n",
    "                tableField_lnsi0v71['employeeField_lmeqk423'] = textField_qk1e5di[name[0]] # 区域经理\n",
    "                tableField_lnsi0v71['employeeField_lmeqk424'] = textField_nc7gskc[name[0]] # 技术专家\n",
    "            except:\n",
    "                tableField_lnsi0v71['employeeField_lmeqk429'] = data['technician_id'][0] # 运营顾问\n",
    "                tableField_lnsi0v71['employeeField_lmeqk423'] = data['manager_id'][0] # 区域经理\n",
    "                tableField_lnsi0v71['employeeField_lmeqk424'] = data['technician_id'][0] # 技术专家\n",
    "            tableField_lnsi0v71['textField_lmu0523h'] = \"自动派发回访计划\" # 状态备用\n",
    "            # 富文本 超链接 NGV\n",
    "            try:\n",
    "                form_data_ngv = read_instances_ngv(token=TOKEN, formUuid=\"FORM-ZK866D91O9LA4NIHCARG2DPIPCXF3Z087PPHL91\", page=1, n=100, searchField={'textField_zc1iowp': data[\"orgCode\"]})\n",
    "                tableField_lnsi0v71['editorField_lodoplg0'] = [\"root\",{},[\"p\",{},[\"span\",{\"data-type\":\"text\"},[\"span\",{\"data-type\":\"leaf\"},\"\"]],[\"a\",{\"href\":\"https://f6car.aliwork.com/APP_UYZ0KG6L0CCNV80GZ66O/formDetail/FORM-ZK866D91O9LA4NIHCARG2DPIPCXF3Z087PPHL91?formInstId=\"+form_data_ngv['data'][0]['formInstanceId']+\"&isAdmin=true\"},[\"span\",{\"data-type\":\"text\"},[\"span\",{\"unlink\":{},\"data-type\":\"leaf\"},\"点击查看门店NGV\"]]],[\"span\",{\"data-type\":\"text\"},[\"span\",{\"unlink\":{},\"data-type\":\"leaf\"},\"\"]]]] # 富文本 超链接 NGV\n",
    "            except:\n",
    "                pass\n",
    "            res=initiate_process(TOKEN,formUuid,processCode,tableField_lnsi0v71)\n",
    "            print(res,tableField_lnsi0v71)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "F6processing",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}