{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# 读取系统现有NGV明细" ] }, { "cell_type": "code", "execution_count": 134, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "读取到市-技术专家id表单中 370 条数据!\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_new = 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", "# 基础函数配置\n", "import pandas as pd\n", "import pandas as pd\n", "import requests\n", "from pathlib import Path\n", "from urllib.parse import quote\n", "import json\n", "import numpy as np\n", "import time\n", "\n", "ROOT = Path('.').absolute() # 当前工作目录\n", "\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_put(token, formInstanceId,data_new):\n", " \"\"\" 函数功能:读取普通表单的所有数据 \"\"\"\n", "\n", " api = f'https://api.dingtalk.com//v1.0/yida/forms/instances'\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\" : \"2268275546837446\", # 曹伟 id\n", " \"language\" : \"zh_CN\",\n", " \"useLatestVersion\" : \"false\",\n", " \"formInstanceId\" : formInstanceId,\n", " \"updateFormDataJson\" : json.dumps(data_new, cls=NpEncoder)\n", " }\n", "\n", " res = requests.put(api, headers=headers, json=formData)\n", " return res.json()\n", "\n", "def read_instances(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", "\n", "def read_delete(token, formInstanceId):\n", " \"\"\" 函数功能:调用本接口删除表单数据。 \"\"\"\n", "\n", " api = f'https://api.dingtalk.com//v1.0/yida/forms/instances'\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", " \"formInstanceId\" : formInstanceId\n", " }\n", "\n", " res = requests.delete(api, headers=headers, json=formData)\n", " return res.json()\n", "\n", "def read_new(FORMID,formData):\n", " \"\"\" 通过实例id 获取表单内容 \"\"\"\n", " api = f'https://api.dingtalk.com/v1.0/yida/forms/instances'\n", "\n", " headers = {\n", " \"Content-Type\": \"application/json\",\n", " \"x-acs-dingtalk-access-token\": TOKEN\n", " }\n", " payload = {\n", " \"formUuid\" : FORMID,\n", " \"appType\" : \"APP_UYZ0KG6L0CCNV80GZ66O\",\n", " \"formDataJson\" : json.dumps(formData, cls=NpEncoder),\n", " \"systemToken\" : \"XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2\",\n", " \"language\" : \"zh_CN\",\n", " \"userId\" : \"yida_pub_account\"\n", " }\n", "\n", " res = requests.post(api, headers=headers, json=payload)\n", " print(res.json())\n", "\n", " return res.json()\n", "\n", "\n", "def component(FORMID,TOKEN):\n", " \"\"\" 获取组件信息 \"\"\"\n", " api = f'https://api.dingtalk.com//v1.0/yida/forms/formFields'\n", "\n", " headers = {\n", " \"Content-Type\": \"application/json\",\n", " \"x-acs-dingtalk-access-token\": TOKEN\n", " }\n", " payload = {\n", " \"formUuid\" : FORMID,\n", " \"appType\" : \"APP_UYZ0KG6L0CCNV80GZ66O\",\n", " # \"formDataJson\" : json.dumps(formData, cls=NpEncoder),\n", " \"systemToken\" : \"XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2\",\n", " # \"language\" : \"zh_CN\",\n", " \"userId\" : \"yida_pub_account\"\n", " }\n", "\n", " res = requests.get(api, headers=headers, json=payload)\n", "\n", " return res.json()\n", "def Batch_creation(FORMID,TOKEN,ALL_formData):\n", " \"\"\" 获取组件信息 \"\"\"\n", " api = f'https://api.dingtalk.com//v1.0/yida/forms/instances/batchSave'\n", "\n", " headers = {\n", " \"Content-Type\": \"application/json\",\n", " \"x-acs-dingtalk-access-token\": TOKEN\n", " }\n", " payload = {\n", " # \"formDataJson\" : json.dumps(formData, cls=NpEncoder),\n", " \"noExecuteExpression\" : \"true\",\n", " \"formUuid\" : FORMID,\n", " \"appType\" : \"APP_UYZ0KG6L0CCNV80GZ66O\",\n", " \"asynchronousExecution\" : \"false\",\n", " \"systemToken\" : \"XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2\",\n", " \"keepRunningAfterException\" : \"true\",\n", " \"userId\" : \"yida_pub_account\",\n", " \"formDataJsonList\" : json.dumps(ALL_formData, cls=NpEncoder),\n", " }\n", "\n", " res = requests.post(api, headers=headers, json=payload)\n", " return res.json()\n", "\n", "def delete_in_batches(FORMID,TOKEN,ALL_DATA_instance):\n", " \"\"\" 批量删除表单实例 \"\"\"\n", " api = f'https://api.dingtalk.com//v1.0/yida/forms/instances/batchRemove'\n", "\n", " headers = {\n", " \"Content-Type\": \"application/json\",\n", " \"x-acs-dingtalk-access-token\": TOKEN\n", " }\n", "\n", " payload = {\n", " \"formUuid\" : FORMID,\n", " \"appType\" : \"APP_UYZ0KG6L0CCNV80GZ66O\",\n", " \"asynchronousExecution\" : \"false\",\n", " \"systemToken\" : \"XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2\",\n", " \"formInstanceIdList\" : json.dumps(ALL_DATA_instance, cls=NpEncoder),\n", " \"userId\" : \"yida_pub_account\",\n", " \"executeExpression\" : \"false\" # 不触发\n", " }\n", "\n", " res = requests.post(api, headers=headers, json=payload)\n", " return res.json()\n", "\n", "def delete_in(TOKEN,formInstanceIdList):\n", " \"\"\" 逐条删除表单实例 \"\"\"\n", " api = f'https://api.dingtalk.com//v1.0/yida/forms/instances?appType=APP_UYZ0KG6L0CCNV80GZ66O&systemToken=XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2&userId=yida_pub_account&language=zh_CN&formInstanceId={formInstanceIdList}'\n", "\n", " headers = {\n", " \"Content-Type\": \"application/json\",\n", " \"x-acs-dingtalk-access-token\": TOKEN\n", " }\n", " res = requests.delete(api, headers=headers)\n", " return res.json()\n", "\n", "def read_form_instances(token, formUuid, page=1, n=100, searchField={},createFromTimeGMT='',createToTimeGMT=''):\n", " \"\"\" 函数功能:读取普通表单的所有数据 -- 应用:F6客户服务 \"\"\"\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", " 'currentPage': page,\n", " 'pageSize': n,\n", " \"appType\": \"APP_UYZ0KG6L0CCNV80GZ66O\",\n", " \"systemToken\": \"XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2\",\n", " \"userId\": \"yida_pub_account\",\n", " \"language\": \"zh_CN\",\n", " \"formUuid\": formUuid,\n", " 'createFromTimeGMT':createFromTimeGMT,\n", " 'createToTimeGMT':createToTimeGMT,\n", " \"searchFieldJson\": json.dumps(searchField)\n", " }\n", "\n", " res = requests.post(api, headers=headers, json=formData)\n", " return res.json()\n", "\n", "def create_form_instances(token, formUuid, formData={}):\n", " \"\"\" 函数功能:创建普通表单实例 -- 应用:F6客户服务 \"\"\"\n", "\n", " api = 'https://api.dingtalk.com/v1.0/yida/forms/instances'\n", "\n", " headers = {\n", " \"Content-Type\": \"application/json\",\n", " \"x-acs-dingtalk-access-token\": token\n", " }\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)\n", " }\n", "\n", " res = requests.post(api, headers=headers, json=payload)\n", " return res\n", "def read_instances_1108(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", "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", "TOKEN = generateToken() \n", "'''读取员工对应关系:宜搭员工-ID对应表 '''\n", "FORMID = \"FORM-EA866E715PF9YA7ECCAGSABX91Q72PVA3WRFL6\" # 宜搭员工-ID对应表 FORM-EA866E715PF9YA7ECCAGSABX91Q72PVA3WRFL6\n", "# 读取流程表单数据\n", "form_data = read_instances(token=TOKEN, formUuid=FORMID, page=1, n=100)\n", "PAGES = form_data.get('totalCount')//100 + 1\n", "ALL_DATA_staff = {}\n", "\"\"\" 获取全量数据 \"\"\"\n", "for i in range(1, PAGES+1):\n", " # form_data = read_processes_instances(token=TOKEN, formUuid=FORMID, createFromTimeGMT=CREATE_FROM, createToTimeGMT=CREATE_TO, page=i, n=100, searchField={'textField_l7if5ff9': '否'})\n", " form_data = read_instances(token=TOKEN, formUuid=FORMID, page=i, n=100)\n", " for data in form_data.get('data'):\n", " ALL_DATA_staff[data['formData']['textField_lfrw3u58']]=data['formData']['textField_lfrw3u59']\n", "\n", "'''获取表内控件信息 '''\n", "# FORMID = \"FORM-ZK866D91O9LA4NIHCARG2DPIPCXF3Z087PPHL91\"\n", "# component_list = component(FORMID,TOKEN)\n", "# for i in range(len(component_list['result'])):\n", "# componentName = component_list['result'][i]['componentName']\n", "# name_value = component_list['result'][i]['label']['value']\n", "# fieldId = component_list['result'][i]['fieldId']\n", "# print(\"'\",fieldId,\"':\",\"data_NGV[\",name_value,\"][i],\")\n", "\n", "'''读取市-技术专家id '''\n", "FORMID = \"FORM-A8666NA1FJDEVPBB6VVTOCMP47UP2J2L6SPML2\" #市-技术专家id\n", "# 读取流程表单数据\n", "form_data = read_instances(token=TOKEN, formUuid=FORMID, page=1, n=100)\n", "PAGES = form_data.get('totalCount')//100 + 1\n", "textField_lmps6q39 = {}\n", "\"\"\" 获取全量数据 \"\"\"\n", "for i in range(1, PAGES+1):\n", " # form_data = read_processes_instances(token=TOKEN, formUuid=FORMID, createFromTimeGMT=CREATE_FROM, createToTimeGMT=CREATE_TO, page=i, n=100, searchField={'textField_l7if5ff9': '否'})\n", " form_data = read_instances(token=TOKEN, formUuid=FORMID, page=i, n=100)\n", " for data in form_data.get('data'):\n", " textField_lmps6q39[data['formData']['textField_lmps6q38']]=data['formData']['textField_lmps6q39'] #技术专家id\n", "print(f'读取到市-技术专家id表单中 {len(textField_lmps6q39)} 条数据!')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "读取ngv 门店编码:实例id" ] }, { "cell_type": "code", "execution_count": 118, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "表单数据共32028条!\n" ] } ], "source": [ "NAME_LIST = [\"无\",\"江苏省\",\"河南省\",\"浙江省\",\"陕西省\",\"河北省\",\"山东省\",\"辽宁省\",\"湖南省\",\"四川省\",\"广东省\",\"上海市\",\"贵州省\",\"云南省\",\"安徽省\",\"湖北省\",\"重庆市\",\"新疆维吾尔自治区\",\"内蒙古自治区\",\"北京市\",\"天津市\",\"山西省\",\"甘肃省\",\"福建省\",\"吉林省\",\"广西壮族自治区\",\"黑龙江省\",\"江西省\",\"宁夏回族自治区\",\"青海省\",\"海南省\",\"西藏自治区\",\"中华人民共和国\",\"香港特别行政区\"]\n", "data_new = {}\n", "FormDatas = []\n", "# 1.读取普通表单数据\n", "for name in NAME_LIST:\n", " searchField={'textField_1q6dt9a':name}\n", " res = read_form_instances(token=TOKEN, formUuid='FORM-ZK866D91O9LA4NIHCARG2DPIPCXF3Z087PPHL91',searchField=searchField)\n", " totalCount = res.get('totalCount')\n", " if totalCount>5000:\n", " # 创建一个异常提醒\n", " create_form_instances(token=TOKEN,formUuid='FORM-5Q966D91S1B8C9PP76L2O5BS9C2L3TXQ4JGELZ',formData={'textField_legj59bj':'NGV数据同步读取数据超过5000异常提醒。'})\n", " break\n", " else:\n", " PAGES = totalCount//100 + 1\n", " for page in range(1,PAGES+1):\n", " # print(name,page)\n", " res = read_form_instances(token=TOKEN, formUuid='FORM-ZK866D91O9LA4NIHCARG2DPIPCXF3Z087PPHL91',searchField=searchField,page=page,n=100)\n", " FormDatas.extend(res.get('data'))\n", "for v in range(0,len(FormDatas)):\n", " data_new[FormDatas[v]['formData']['textField_zc1iowp']] = FormDatas[v]['formInstanceId']\n", "print(f\"表单数据共{len(data_new)}条!\") #耗时7分钟左右" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "读取数据库中ngv明细,比对表中不存在的数据进行新增" ] }, { "cell_type": "code", "execution_count": 123, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
| \n", " | org_code | \n", "Value | \n", "
|---|---|---|
| 0 | \n", "CHS202304180225447 | \n", "FINST-5F766771MPRF4GZPF71LABD941Z825CSDEPOLYC | \n", "
| 1 | \n", "CHS202302130204982 | \n", "FINST-5F766771MPRF4GZPF71LABD941Z825CSDEPOLXC | \n", "
| 2 | \n", "CHS202109030139905 | \n", "FINST-9I6666D1GHSFPXBZBGL2S4R8PUYZ2UTLDEPOLM | \n", "
| 3 | \n", "CHS201812240004932 | \n", "FINST-ZW966U81FISF80VQ9IR1F5GTXFKS31RIDEPOL81 | \n", "
| 4 | \n", "CHS202103050116025 | \n", "FINST-ZW966U81FISF80VQ9IR1F5GTXFKS31RIDEPOL01 | \n", "
| ... | \n", "... | \n", "... | \n", "
| 32023 | \n", "CHS201611201081 | \n", "FINST-HBD66K813URFHIBL7VJ8X67OGKUX2JED11POLW6 | \n", "
| 32024 | \n", "CHS202102020115078 | \n", "FINST-KH766OB1LYRFXGPFFK8QU5475QLH3TU811POLM1 | \n", "
| 32025 | \n", "CHS201912150045862 | \n", "FINST-IT5662C1NVRFYX9O6ICT17YHPXF42QP751POLE1 | \n", "
| 32026 | \n", "CHS201902260005872 | \n", "FINST-09866181RPRFNAUWCXQ848RO94DN2RVC21POLH1 | \n", "
| 32027 | \n", "CHS201912310050120 | \n", "FINST-XJ866N713QRFR2SA68F2T9B3HO4M2XOH51POLWM | \n", "
32028 rows × 2 columns
\n", "| \n", " | index | \n", "date_id | \n", "date_fmt | \n", "id_own_group | \n", "group_name | \n", "id_own_org | \n", "org_name | \n", "org_code | \n", "group_grade | \n", "org_type | \n", "... | \n", "org_size | \n", "qualification_type_fmt | \n", "business_scope_fmt | \n", "store_type_fmt | \n", "area | \n", "station_number | \n", "header_type_fmt | \n", "org_stage | \n", "g_count_this_month | \n", "saas_customer_type | \n", "
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | \n", "553 | \n", "20231107 | \n", "2023-11-07 | \n", "10907434497420857190 | \n", "钟山区小何轮胎经营部 | \n", "10907434497420857188 | \n", "小何轮胎经营部 | \n", "CHS202010230109177 | \n", "普通客户(VIP) | \n", "一般 | \n", "... | \n", "小 | \n", "无维修资质 | \n", "保养,洗车,维修,美容,轮胎 | \n", "综合维修厂 | \n", "160.00 | \n", "3 | \n", "轮胎 | \n", "流失 | \n", "0 | \n", "C | \n", "
| 1 | \n", "1300 | \n", "20231107 | \n", "2023-11-07 | \n", "10907434497485151571 | \n", "绵阳经开区匠心汽修 | \n", "10907434497485151569 | \n", "匠心汽修总店 | \n", "CHS202101090113916 | \n", "普通客户(VIP) | \n", "一般 | \n", "... | \n", "大 | \n", "三类资格 | \n", "保养,改装,洗车,维修,美容,装潢精品,轮胎,钣喷 | \n", "综合修理厂 | \n", "2000.00 | \n", "18 | \n", "自有单店-无需选图片 | \n", "续约 | \n", "7 | \n", "E | \n", "
| 2 | \n", "1302 | \n", "20231107 | \n", "2023-11-07 | \n", "10907434497486024053 | \n", "呼和浩特市玉泉区玛莉超级汽车服务门店 | \n", "10907434497486024051 | \n", "玛莉超级汽车会 | \n", "CHS202101110113958 | \n", "普通客户(VIP) | \n", "一般 | \n", "... | \n", "小 | \n", "三类资格 | \n", "保养,维修 | \n", "快修快保店 | \n", "200.00 | \n", "3 | \n", "自有单店-无需选图片 | \n", "流失 | \n", "0 | \n", "C | \n", "
3 rows × 140 columns
\n", "