{ "cells": [ { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "# 区域续费率" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ ":32: UserWarning: Pandas requires version '1.4.3' or newer of 'xlsxwriter' (version '1.3.8' currently installed).\n", " data_details.to_excel(r'C:\\Users\\admin\\Desktop\\holo_ads_report_sales_saas_to_renew_org_summary_income_d.xlsx')\n", ":67: UserWarning: Pandas requires version '1.4.3' or newer of 'xlsxwriter' (version '1.3.8' currently installed).\n", " data_details.to_excel(r'C:\\Users\\admin\\Desktop\\holo_ads_report_sales_saas_to_renew_org_regular_income_d.xlsx')\n", ":89: UserWarning: Pandas requires version '1.4.3' or newer of 'xlsxwriter' (version '1.3.8' currently installed).\n", " result_new.to_excel(r\"C:\\Users\\admin\\Desktop\\SaaS续费率_区域续费率.xlsx\")\n" ] } ], "source": [ "# -*- coding: utf-8 -*-\n", "# 基础函数配置\n", "import psycopg2\n", "import pandas as pd\n", "import calendar\n", "import datetime\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", "from decimal import Decimal\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", "# sql语句 建表\n", "sql =f\"\"\"SELECT * FROM \"public\".\"holo_ads_report_sales_saas_to_renew_org_summary_income_d\" WHERE \"pt\" >= '20230101';\"\"\"\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_details = pd.DataFrame(list(rows),columns=col)\n", "data_details.to_excel(r'C:\\Users\\admin\\Desktop\\holo_ads_report_sales_saas_to_renew_org_summary_income_d.xlsx')\n", "# 关闭数据库连接\n", "cursor.close()\n", "conn.close()\n", "\n", "data_details['version_amount_total'] = data_details['version_amount_total'].apply(lambda x: float(x) if isinstance(x, Decimal) else x)\n", "data_details['unversion_amount_total'] = data_details['unversion_amount_total'].apply(lambda x: float(x) if isinstance(x, Decimal) else x)\n", "data_details['irregular_amount_total'] = data_details['irregular_amount_total'].apply(lambda x: float(x) if isinstance(x, Decimal) else x)\n", "\n", "selected_fields = ['date_fmt', 'version_amount_total', 'unversion_amount_total', 'irregular_amount_total', 'area_manager', 'region_name']\n", "new_df_data = data_details[selected_fields]\n", "df = new_df_data.copy()\n", "df['all_result_d'] = df[['version_amount_total', 'unversion_amount_total', 'irregular_amount_total']].sum(axis=1)\n", "# df['frequently_result_d'] = df[['version_amount_total', 'unversion_amount_total']].sum(axis=1)\n", "result_molecule = df[df['area_manager'].notnull() & df['region_name'].notnull() & df['date_fmt'].notnull()].groupby(['date_fmt','area_manager', 'region_name'])['all_result_d'].sum().reset_index()\n", "result_molecule\n", "\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", "# sql语句 建表\n", "sql =f\"\"\"SELECT * FROM \"public\".\"holo_ads_report_sales_saas_to_renew_org_regular_income_d\" WHERE \"pt\" >= '20230101';\"\"\"\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_details = pd.DataFrame(list(rows),columns=col)\n", "data_details.to_excel(r'C:\\Users\\admin\\Desktop\\holo_ads_report_sales_saas_to_renew_org_regular_income_d.xlsx')\n", "# 关闭数据库连接\n", "cursor.close()\n", "conn.close()\n", "\n", "selected_fields = ['date_fmt', 'version_amount_total', 'area_manager', 'region_name']\n", "data_details['date_fmt'] = data_details['date_fmt'].str.slice(0, 6)\n", "new_df_data = data_details[selected_fields]\n", "df = new_df_data.copy()\n", "\n", "# df['all_result_d'] = df[['version_amount_total', 'unversion_amount_total', 'irregular_amount_total']].sum(axis=1)\n", "# df['frequently_result_d'] = df[['version_amount_total', 'unversion_amount_total']].sum(axis=1)\n", "result_denominator = df[df['area_manager'].notnull() & df['region_name'].notnull() & df['date_fmt'].notnull()].groupby(['date_fmt','area_manager', 'region_name'])['version_amount_total'].sum().reset_index()\n", "result_new = pd.merge(result_molecule, result_denominator, on=['date_fmt','area_manager', 'region_name'], how='left')\n", "result_new['percentage'] = result_new['all_result_d'].astype(int) / result_new['version_amount_total']\n", "result_new['all_result_d'] = round(result_new['all_result_d'], 2)\n", "result_new['version_amount_total'] = result_new['version_amount_total'].astype(float).round(2)\n", "result_new['percentage'] = result_new['percentage'].astype(float).round(2)/100\n", "# 删除percentage列为空的行\n", "result_new = result_new.dropna(subset=['percentage'])\n", "# 重置索引\n", "result_new = result_new.reset_index(drop=True)\n", "result_new.to_excel(r\"C:\\Users\\admin\\Desktop\\SaaS续费率_区域续费率.xlsx\")" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "新建第 99 条数据! {'result': ['FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KL9M', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLAM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLBM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLCM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLDM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLEM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLFM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLGM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLHM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLIM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLJM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLKM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLLM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLMM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLNM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLOM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLPM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLQM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLRM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLSM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLTM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLUM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLVM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLWM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLXM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLYM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLZM', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KL0N', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KL1N', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KL2N', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KL3N', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KL4N', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KL5N', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KL6N', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KL7N', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KL8N', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KL9N', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLAN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLBN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLCN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLDN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLEN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLFN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLGN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLHN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLIN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLJN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLKN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLLN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLMN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLNN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLON', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLPN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLQN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLRN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLSN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLTN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLUN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLVN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLWN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLXN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLYN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLZN', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KL0O', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KL1O', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KL2O', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KL3O', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KL4O', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KL5O', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KL6O', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KL7O', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KL8O', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KL9O', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLAO', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLBO', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLCO', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLDO', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLEO', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLFO', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLGO', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLHO', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLIO', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLJO', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLKO', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLLO', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLMO', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLNO', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLOO', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLPO', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLQO', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLRO', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLSO', 'FINST-RL966L81XWKC9R6LCYJUJDFDXCK92XZICP7KLTO']}\n", "数据新建成功！\n" ] } ], "source": [ "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):\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\" : \"true\",\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\" : \"true\",\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", "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')//10 + 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", "data_details = result_new.copy()\n", "data_details = data_details.astype('string')\n", "data_details = data_details.fillna('',inplace=False)\n", "for a in range(0,len(data_details[\"date_fmt\"]),100):\n", " ALL_formData = []\n", " for i in range(a,a+100): # for i in range(len(data_NGV[\"date_fmt\"])):\n", " try:\n", " formData = {\n", " 'textField_lk7ot5i8':data_details[\"date_fmt\"][i],#\"date_fmt\"\n", " # 'employeeField_lk7ot5ig':data_details[\"area_manager\"][i],#\"area_manager\"\n", " 'textField_lk7ot5ia':data_details[\"region_name\"][i],#\"region_name\"\n", " 'textField_lk7ot5ib':data_details[\"all_result_d\"][i],#\"all_result_d\"\n", " 'textField_lk7ot5ic':data_details[\"version_amount_total\"][i],#\"version_amount_total\"\n", " 'textField_lk7ot5id':data_details[\"percentage\"][i],#\"percentage\"\n", " }\n", " try:\n", " formData['employeeField_lk7ot5ig'] = ALL_DATA_staff[data_details[\"area_manager\"][i]]\n", " except:\n", " formData['employeeField_lk7ot5ig'] = \"\"\n", "\n", " ALL_formData.append(json.dumps(formData, cls=NpEncoder)) \n", " except:\n", " pass\n", " FORMID = \"FORM-TD966Z810ILCWMTI9IWFF6V83NSO3OT1TO7KL1\" \n", " res_new = Batch_creation(FORMID,TOKEN,ALL_formData)\n", " print(\"新建第\",i,\"条数据!\",res_new)\n", " time.sleep(10)\n", "\n", "'''校验是否新建正常'''\n", "time.sleep(120)\n", "FORMID = \"FORM-TD966Z810ILCWMTI9IWFF6V83NSO3OT1TO7KL1\" \n", "form_data = read_instances(token=TOKEN, formUuid=FORMID, page=1, n=100)\n", "if int(form_data.get('totalCount')) ==len(data_details):\n", " print(\"数据新建成功！\")\n", "else:\n", " \n", " def start_instance_process(token: str, name):\n", "\n", " \"\"\"发送宜搭表单 -- 发起流程表单\n", "\n", " Args:\n", " token \n", " data:需要发送的数据字典\n", " \"\"\"\n", "\n", " yida_api = \"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", "\n", " send_data = {\n", " \"textField_l9fe0uiw\": name,\n", " \"textField_l9fe0uiv\": name\n", " }\n", "\n", " payload = {\n", " \"appType\": \"APP_TNVBVZ3K8G56HG03Z45Q\",\n", " \"systemToken\": \"CH7669818R0WN18TYTYJ42PE6GY22WZN0BYWKD1\",\n", " \"userId\": \"yida_pub_account\",# 超级管理员账号\n", " \"language\": \"zh_CN\",\n", " \"formUuid\": \"FORM-UX866Q61GNLAZBCIEDF77BGVIIR83K82WYPHLH2\",\n", " \"formDataJson\": json.dumps(send_data),\n", " \"processCode\":\"TPROC--UX866Q61GNLAZBCIEDF77BGVIIR83M92WYPHLI2\"\n", " }\n", "\n", " res = requests.post(yida_api, headers=headers, json=payload)\n", " return res\n", " try:\n", " name = \"区域续费率 新建条数不正确！\"\n", " res_yujing = start_instance_process(TOKEN,name)\n", " except:\n", " pass" ] }, { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "# 全国续费率" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ ":113: SettingWithCopyWarning: \n", "A value is trying to be set on a copy of a slice from a DataFrame.\n", "Try using .loc[row_indexer,col_indexer] = value instead\n", "\n", "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n", " new_df_data['is_renew'] = new_df_data['is_renew'].replace(0, 1)\n", ":127: UserWarning: Pandas requires version '1.4.3' or newer of 'xlsxwriter' (version '1.3.8' currently installed).\n", " result_new.to_excel(r\"C:\\Users\\admin\\Desktop\\SaaS续费率_全国续费率.xlsx\")\n" ] } ], "source": [ "# -*- coding: utf-8 -*-\n", "# 基础函数配置\n", "import psycopg2\n", "import pandas as pd\n", "import calendar\n", "import datetime\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", "from decimal import Decimal\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", "# sql语句 建表\n", "sql =f\"\"\"SELECT * FROM \"public\".\"holo_ads_report_sales_saas_to_renew_org_summary_income_d\" WHERE \"pt\" >= '20230101';\"\"\"\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_details = 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", "data_details['version_amount_total'] = data_details['version_amount_total'].apply(lambda x: float(x) if isinstance(x, Decimal) else x)\n", "data_details['unversion_amount_total'] = data_details['unversion_amount_total'].apply(lambda x: float(x) if isinstance(x, Decimal) else x)\n", "data_details['irregular_amount_total'] = data_details['irregular_amount_total'].apply(lambda x: float(x) if isinstance(x, Decimal) else x)\n", "\n", "selected_fields = ['date_fmt', 'version_amount_total', 'unversion_amount_total', 'irregular_amount_total', 'service_impl_principal']\n", "new_df_data = data_details[selected_fields]\n", "df = new_df_data.copy()\n", "df['all_result_d'] = df[['version_amount_total', 'unversion_amount_total', 'irregular_amount_total']].sum(axis=1)\n", "# df['frequently_result_d'] = df[['version_amount_total', 'unversion_amount_total']].sum(axis=1)\n", "result_molecule = df[df['service_impl_principal'].notnull() & df['date_fmt'].notnull()].groupby(['date_fmt','service_impl_principal'])['all_result_d'].sum().reset_index()\n", "result_molecule\n", "\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", "# sql语句 建表\n", "sql =f\"\"\"SELECT * FROM \"public\".\"holo_ads_report_sales_saas_to_renew_org_regular_income_d\" WHERE \"pt\" >= '20230101';\"\"\"\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_details = 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", "data_details['version_amount_total'] = data_details['version_amount_total'].apply(lambda x: float(x) if isinstance(x, Decimal) else x)\n", "\n", "selected_fields = ['date_fmt', 'version_amount_total', 'service_impl_principal']\n", "data_details['date_fmt'] = data_details['date_fmt'].str.slice(0, 6)\n", "new_df_data = data_details[selected_fields]\n", "df = new_df_data.copy()\n", "# df['all_result_d'] = df[['version_amount_total', 'unversion_amount_total', 'irregular_amount_total']].sum(axis=1)\n", "# df['frequently_result_d'] = df[['version_amount_total', 'unversion_amount_total']].sum(axis=1)\n", "result_denominator = df[df['service_impl_principal'].notnull() & df['date_fmt'].notnull()].groupby(['date_fmt','service_impl_principal'])['version_amount_total'].sum().reset_index()\n", "result_denominator\n", "result_new = pd.merge(result_molecule, result_denominator, on=['date_fmt','service_impl_principal'], how='left')\n", "result_new['percentage'] = result_new['all_result_d'] / result_new['version_amount_total'] * 100\n", "result_new_A = result_new.copy()\n", "\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", "# sql语句 建表\n", "sql =f\"\"\"SELECT * FROM \"public\".\"holo_ads_report_sales_saas_regular_income_snapshot_m\" WHERE \"pt\" >= '202301';\"\"\"\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_details = 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", "selected_fields = ['month_id', 'is_renew', 'service_impl_principal']\n", "new_df_data = data_details[selected_fields]\n", "new_df_data_filtration = new_df_data.loc[new_df_data['is_renew'] == 1]\n", "result_denominator_filtration = new_df_data_filtration[new_df_data_filtration['service_impl_principal'].notnull() & new_df_data_filtration['month_id'].notnull()& new_df_data_filtration['is_renew'].notnull()].groupby(['month_id','service_impl_principal'])['is_renew'].sum().reset_index()\n", "new_df_data['is_renew'] = new_df_data['is_renew'].replace(0, 1)\n", "result_denominator = new_df_data[new_df_data['service_impl_principal'].notnull() & new_df_data['month_id'].notnull()& new_df_data['is_renew'].notnull()].groupby(['month_id','service_impl_principal'])['is_renew'].sum().reset_index()\n", "result_new = pd.merge(result_denominator, result_denominator_filtration, on=['month_id','service_impl_principal'], how='left')\n", "result_new = result_new.rename(columns={'month_id': 'date_fmt'})\n", "result_new['is_renew_y'] = result_new['is_renew_y'].replace(np.nan, 0)\n", "result_new['percentage'] = result_new['is_renew_y'] / result_new['is_renew_x'] * 100\n", "result_new_B = result_new.copy()\n", "result_new = pd.merge(result_new_A, result_new_B, on=['date_fmt','service_impl_principal'], how='left')\n", "result_new['all_result_d'] = round(result_new['all_result_d'], 2)\n", "result_new['version_amount_total'] = round(result_new['version_amount_total'], 2)\n", "result_new['percentage_x'] = round(result_new['percentage_x'], 2)\n", "result_new['is_renew_x'] = round(result_new['is_renew_x'], 2)\n", "result_new['is_renew_y'] = round(result_new['is_renew_y'], 2)\n", "result_new['percentage_y'] = round(result_new['percentage_y'], 2)/100\n", "result_new.to_excel(r\"C:\\Users\\admin\\Desktop\\SaaS续费率_全国续费率.xlsx\")" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "新建第 99 条数据! {'result': ['FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLG', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLH', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLI', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLJ', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLK', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLL', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLM', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLN', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLO', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLP', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLQ', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLR', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLS', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLT', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLU', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLV', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLW', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLX', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLY', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLZ', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL01', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL11', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL21', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL31', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL41', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL51', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL61', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL71', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL81', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL91', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLA1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLB1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLC1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLD1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLE1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLF1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLG1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLH1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLI1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLJ1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLK1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLL1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLM1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLN1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLO1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLP1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLQ1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLR1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLS1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLT1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLU1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLV1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLW1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLX1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLY1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLZ1', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL02', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL12', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL22', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL32', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL42', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL52', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL62', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL72', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL82', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL92', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLA2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLB2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLC2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLD2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLE2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLF2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLG2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLH2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLI2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLJ2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLK2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLL2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLM2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLN2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLO2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLP2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLQ2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLR2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLS2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLT2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLU2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLV2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLW2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLX2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLY2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KLZ2', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL03', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL13', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL23', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL33', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL43', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL53', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL63', 'FINST-KH766OB1RHLCC67H8EU7K57S5NSD25WYRP7KL73']}\n", "新建第 199 条数据! {'result': ['FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3GT6SP7KL2I', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL3I', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL4I', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL5I', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL6I', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL7I', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL8I', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL9I', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLAI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLBI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLCI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLDI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLEI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLFI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLGI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLHI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLII', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLJI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLKI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLLI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLMI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLNI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLOI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLPI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLQI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLRI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLSI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLTI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLUI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLVI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLWI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLXI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLYI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLZI', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL0J', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL1J', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL2J', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL3J', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL4J', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL5J', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL6J', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL7J', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL8J', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL9J', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLAJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLBJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLCJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLDJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLEJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLFJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLGJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLHJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLIJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLJJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLKJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLLJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLMJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLNJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLOJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLPJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLQJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLRJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLSJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLTJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLUJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLVJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLWJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLXJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLYJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLZJ', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL0K', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL1K', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL2K', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL3K', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL4K', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL5K', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL6K', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL7K', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL8K', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KL9K', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLAK', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLBK', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLCK', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLDK', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLEK', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLFK', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLGK', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLHK', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLIK', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLJK', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLKK', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLLK', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLMK', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLNK', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLOK', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLPK', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLQK', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLRK', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLSK', 'FINST-1V766G819ZKCHW0J8D7YJ9NO18KL3HT6SP7KLTK']}\n", "新建第 299 条数据! {'result': ['FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLP', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLQ', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLR', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLS', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLT', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLU', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLV', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLW', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLX', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLY', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLZ', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KL01', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KL11', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KL21', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KL31', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KL41', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KL51', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KL61', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KL71', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KL81', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KL91', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLA1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLB1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLC1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLD1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLE1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLF1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLG1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLH1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLI1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLJ1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLK1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLL1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLM1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3CRESP7KLN1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLO1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLP1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLQ1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLR1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLS1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLT1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLU1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLV1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLW1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLX1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLY1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLZ1', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KL02', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KL12', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KL22', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KL32', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KL42', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KL52', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KL62', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KL72', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KL82', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KL92', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLA2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLB2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLC2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLD2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLE2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLF2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLG2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLH2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLI2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLJ2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLK2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLL2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLM2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLN2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLO2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLP2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLQ2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLR2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLS2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLT2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLU2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLV2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLW2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLX2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLY2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLZ2', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KL03', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KL13', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KL23', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KL33', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KL43', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KL53', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KL63', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KL73', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KL83', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KL93', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLA3', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLB3', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLC3', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLD3', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLE3', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLF3', 'FINST-KH766OB1OELCJGBW7SGS4AYSSVBJ3DRESP7KLG3']}\n", "新建第 399 条数据! {'result': ['FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLD3', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLE3', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLF3', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLG3', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLH3', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLI3', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLJ3', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLK3', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLL3', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLM3', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLN3', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLO3', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLP3', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLQ3', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLR3', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLS3', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLT3', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLU3', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLV3', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLW3', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLX3', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLY3', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLZ3', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KL04', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KL14', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KL24', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KL34', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KL44', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KL54', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KL64', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KL74', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KL84', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KL94', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLA4', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLB4', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLC4', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLD4', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLE4', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLF4', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLG4', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLH4', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLI4', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLJ4', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLK4', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLL4', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLM4', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLN4', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLO4', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLP4', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLQ4', 'FINST-JS766JD1XZKC3TAY5YSMF90UX4ZP2ROMSP7KLR4']}\n", "数据新建成功！\n" ] } ], "source": [ "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):\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\" : \"true\",\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\" : \"true\",\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", "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')//10 + 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", "data_details = result_new.copy()\n", "data_details = data_details.astype('string')\n", "data_details = data_details.fillna('',inplace=False)\n", "for a in range(0,len(data_details[\"date_fmt\"]),100):\n", " ALL_formData = []\n", " for i in range(a,a+100): # for i in range(len(data_NGV[\"date_fmt\"])):\n", " try:\n", " formData = {\n", " 'textField_lk7pibrx':data_details[\"date_fmt\"][i],#\"date_fmt\"\n", " # 'employeeField_lk7pibs5':data_details[\"service_impl_principal\"][i],#\"service_impl_principal\"\n", " 'textField_lk7pibrz':data_details[\"all_result_d\"][i],#\"all_result_d\"\n", " 'textField_lk7pibs0':data_details[\"version_amount_total\"][i],#\"version_amount_total\"\n", " 'textField_lk7pibs1':data_details[\"percentage_x\"][i],#\"percentage_x\"\n", " 'textField_lk7pibs2':data_details[\"is_renew_x\"][i],#\"is_renew_x\"\n", " 'textField_lk7pibs3':data_details[\"is_renew_y\"][i],#\"is_renew_y\"\n", " 'textField_lk7pibs4':data_details[\"percentage_y\"][i],#\"percentage_y\"\n", " }\n", " try:\n", " formData['employeeField_lk7pibs5'] = ALL_DATA_staff[data_details[\"service_impl_principal\"][i]]\n", " except:\n", " formData['employeeField_lk7pibs5'] = \"\"\n", "\n", " ALL_formData.append(json.dumps(formData, cls=NpEncoder)) \n", " except:\n", " pass\n", " FORMID = \"FORM-L8966281FILCMAXK7QDGW5E0SZ3Y2CAJHP7KL1\" \n", " res_new = Batch_creation(FORMID,TOKEN,ALL_formData)\n", " print(\"新建第\",i,\"条数据!\",res_new)\n", " time.sleep(10)\n", "\n", "'''校验是否新建正常'''\n", "time.sleep(120)\n", "FORMID = \"FORM-L8966281FILCMAXK7QDGW5E0SZ3Y2CAJHP7KL1\" \n", "form_data = read_instances(token=TOKEN, formUuid=FORMID, page=1, n=100)\n", "if int(form_data.get('totalCount')) ==len(data_details):\n", " print(\"数据新建成功！\")\n", "else:\n", " \n", " def start_instance_process(token: str, name):\n", "\n", " \"\"\"发送宜搭表单 -- 发起流程表单\n", "\n", " Args:\n", " token \n", " data:需要发送的数据字典\n", " \"\"\"\n", "\n", " yida_api = \"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", "\n", " send_data = {\n", " \"textField_l9fe0uiw\": name,\n", " \"textField_l9fe0uiv\": name\n", " }\n", "\n", " payload = {\n", " \"appType\": \"APP_TNVBVZ3K8G56HG03Z45Q\",\n", " \"systemToken\": \"CH7669818R0WN18TYTYJ42PE6GY22WZN0BYWKD1\",\n", " \"userId\": \"yida_pub_account\",# 超级管理员账号\n", " \"language\": \"zh_CN\",\n", " \"formUuid\": \"FORM-UX866Q61GNLAZBCIEDF77BGVIIR83K82WYPHLH2\",\n", " \"formDataJson\": json.dumps(send_data),\n", " \"processCode\":\"TPROC--UX866Q61GNLAZBCIEDF77BGVIIR83M92WYPHLI2\"\n", " }\n", "\n", " res = requests.post(yida_api, headers=headers, json=payload)\n", " return res\n", " try:\n", " name = \"全国续费率 新建条数不正确！\"\n", " res_yujing = start_instance_process(TOKEN,name)\n", " except:\n", " pass" ] } ], "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" }, "orig_nbformat": 4 }, "nbformat": 4, "nbformat_minor": 2 }