736 lines
36 KiB
Python
736 lines
36 KiB
Python
# -*- coding: utf-8 -*-
|
|
import psycopg2
|
|
import pandas as pd
|
|
# 获得连接
|
|
conn = psycopg2.connect(database="f6_bi", user="BASIC$ro_caowei", password="!ro_caowei123", host="hgprecn-cn-nif1vnv0y002-cn-shanghai.hologres.aliyuncs.com", port="80")
|
|
# 获得游标对象，一个游标对象可以对数据库进行执行操作
|
|
cursor = conn.cursor()
|
|
|
|
import datetime
|
|
now_time = datetime.datetime.now()
|
|
yes_time = now_time + datetime.timedelta(days=-1)
|
|
yes_time_nyr = int(yes_time.strftime('%Y%m%d'))# 获取前一天日期
|
|
# sql语句 建表
|
|
sql =f"""SELECT * FROM "public"."holo_ads_report_saas_profile_ngv_detail_d" WHERE "date_id" = '{yes_time_nyr}';"""
|
|
# 执行语句
|
|
cursor.execute(sql)
|
|
# 获取结果集的每一行
|
|
rows = cursor.fetchall()
|
|
# 获取所有字段名
|
|
all_fields = cursor.description
|
|
#执行结果转化为dataframe
|
|
col = []
|
|
for i in all_fields:
|
|
col.append(i[0])
|
|
data_NGV_new = pd.DataFrame(list(rows),columns=col)
|
|
# data_NGV.to_excel(r'C:\Users\admin\Desktop\NGV明细.xlsx')
|
|
# 关闭数据库连接
|
|
cursor.close()
|
|
conn.close()
|
|
# 基础函数配置
|
|
import pandas as pd
|
|
import pandas as pd
|
|
import requests
|
|
from pathlib import Path
|
|
from urllib.parse import quote
|
|
import json
|
|
import numpy as np
|
|
import time
|
|
|
|
ROOT = Path('.').absolute() # 当前工作目录
|
|
|
|
def generateToken() -> str:
|
|
""" 生成 token """
|
|
|
|
token_api = 'https://api.dingtalk.com/v1.0/oauth2/accessToken'
|
|
|
|
# 该信息在钉钉开放应用中
|
|
data = {
|
|
"appKey": "ding5kqocon5s9oph5uq",
|
|
"appSecret": 'HL1jgsIIfLAC0eTH0A1m4mwxUDqbgsiPeCCGGE3ocM6qJBTIW7Ivt9drxF_Z4Kb_'
|
|
}
|
|
|
|
res = requests.post(token_api, json=data)
|
|
token = res.json()['accessToken']
|
|
|
|
return token
|
|
|
|
def read_instances_put(token, formInstanceId,data_new):
|
|
""" 函数功能：读取普通表单的所有数据 """
|
|
|
|
api = f'https://api.dingtalk.com//v1.0/yida/forms/instances'
|
|
|
|
headers = {
|
|
"Content-Type": "application/json",
|
|
"x-acs-dingtalk-access-token": token
|
|
}
|
|
|
|
formData = {
|
|
"appType" : "APP_UYZ0KG6L0CCNV80GZ66O",
|
|
"systemToken" : "XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2",
|
|
"userId" : "2268275546837446", # 曹伟 id
|
|
"language" : "zh_CN",
|
|
"useLatestVersion" : "false",
|
|
"formInstanceId" : formInstanceId,
|
|
"updateFormDataJson" : json.dumps(data_new, cls=NpEncoder)
|
|
}
|
|
|
|
res = requests.put(api, headers=headers, json=formData)
|
|
return res.json()
|
|
|
|
def read_instances(token, formUuid, page, n):
|
|
""" 函数功能：读取普通表单的所有数据 """
|
|
|
|
api = f'https://api.dingtalk.com//v1.0/yida/forms/instances/search'
|
|
|
|
headers = {
|
|
"Content-Type": "application/json",
|
|
"x-acs-dingtalk-access-token": token
|
|
}
|
|
|
|
formData = {
|
|
"appType" : "APP_UYZ0KG6L0CCNV80GZ66O",
|
|
"systemToken" : "XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2",
|
|
"userId" : "yida_pub_account",
|
|
"language" : "zh_CN",
|
|
"formUuid" : formUuid,
|
|
"currentPage" : page,
|
|
"pageSize" : n
|
|
}
|
|
|
|
res = requests.post(api, headers=headers, json=formData)
|
|
return res.json()
|
|
|
|
def read_delete(token, formInstanceId):
|
|
""" 函数功能：调用本接口删除表单数据。 """
|
|
|
|
api = f'https://api.dingtalk.com//v1.0/yida/forms/instances'
|
|
|
|
headers = {
|
|
"Content-Type": "application/json",
|
|
"x-acs-dingtalk-access-token": token
|
|
}
|
|
|
|
formData = {
|
|
"appType" : "APP_UYZ0KG6L0CCNV80GZ66O",
|
|
"systemToken" : "XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2",
|
|
"userId" : "yida_pub_account",
|
|
"language" : "zh_CN",
|
|
"formInstanceId" : formInstanceId
|
|
}
|
|
|
|
res = requests.delete(api, headers=headers, json=formData)
|
|
return res.json()
|
|
|
|
def read_new(FORMID,formData):
|
|
""" 通过实例id 获取表单内容 """
|
|
api = f'https://api.dingtalk.com/v1.0/yida/forms/instances'
|
|
|
|
headers = {
|
|
"Content-Type": "application/json",
|
|
"x-acs-dingtalk-access-token": TOKEN
|
|
}
|
|
payload = {
|
|
"formUuid" : FORMID,
|
|
"appType" : "APP_UYZ0KG6L0CCNV80GZ66O",
|
|
"formDataJson" : json.dumps(formData, cls=NpEncoder),
|
|
"systemToken" : "XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2",
|
|
"language" : "zh_CN",
|
|
"userId" : "yida_pub_account"
|
|
}
|
|
|
|
res = requests.post(api, headers=headers, json=payload)
|
|
print(res.json())
|
|
|
|
return res.json()
|
|
|
|
|
|
def component(FORMID,TOKEN):
|
|
""" 获取组件信息 """
|
|
api = f'https://api.dingtalk.com//v1.0/yida/forms/formFields'
|
|
|
|
headers = {
|
|
"Content-Type": "application/json",
|
|
"x-acs-dingtalk-access-token": TOKEN
|
|
}
|
|
payload = {
|
|
"formUuid" : FORMID,
|
|
"appType" : "APP_UYZ0KG6L0CCNV80GZ66O",
|
|
# "formDataJson" : json.dumps(formData, cls=NpEncoder),
|
|
"systemToken" : "XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2",
|
|
# "language" : "zh_CN",
|
|
"userId" : "yida_pub_account"
|
|
}
|
|
|
|
res = requests.get(api, headers=headers, json=payload)
|
|
|
|
return res.json()
|
|
def Batch_creation(FORMID,TOKEN,ALL_formData):
|
|
""" 获取组件信息 """
|
|
api = f'https://api.dingtalk.com//v1.0/yida/forms/instances/batchSave'
|
|
|
|
headers = {
|
|
"Content-Type": "application/json",
|
|
"x-acs-dingtalk-access-token": TOKEN
|
|
}
|
|
payload = {
|
|
# "formDataJson" : json.dumps(formData, cls=NpEncoder),
|
|
"noExecuteExpression" : "true",
|
|
"formUuid" : FORMID,
|
|
"appType" : "APP_UYZ0KG6L0CCNV80GZ66O",
|
|
"asynchronousExecution" : "false",
|
|
"systemToken" : "XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2",
|
|
"keepRunningAfterException" : "true",
|
|
"userId" : "yida_pub_account",
|
|
"formDataJsonList" : json.dumps(ALL_formData, cls=NpEncoder),
|
|
}
|
|
|
|
res = requests.post(api, headers=headers, json=payload)
|
|
return res.json()
|
|
|
|
def delete_in_batches(FORMID,TOKEN,ALL_DATA_instance):
|
|
""" 批量删除表单实例 """
|
|
api = f'https://api.dingtalk.com//v1.0/yida/forms/instances/batchRemove'
|
|
|
|
headers = {
|
|
"Content-Type": "application/json",
|
|
"x-acs-dingtalk-access-token": TOKEN
|
|
}
|
|
|
|
payload = {
|
|
"formUuid" : FORMID,
|
|
"appType" : "APP_UYZ0KG6L0CCNV80GZ66O",
|
|
"asynchronousExecution" : "false",
|
|
"systemToken" : "XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2",
|
|
"formInstanceIdList" : json.dumps(ALL_DATA_instance, cls=NpEncoder),
|
|
"userId" : "yida_pub_account",
|
|
"executeExpression" : "false" # 不触发
|
|
}
|
|
|
|
res = requests.post(api, headers=headers, json=payload)
|
|
return res.json()
|
|
|
|
def delete_in(TOKEN,formInstanceIdList):
|
|
""" 逐条删除表单实例 """
|
|
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}'
|
|
|
|
headers = {
|
|
"Content-Type": "application/json",
|
|
"x-acs-dingtalk-access-token": TOKEN
|
|
}
|
|
res = requests.delete(api, headers=headers)
|
|
return res.json()
|
|
|
|
def read_form_instances(token, formUuid, page=1, n=100, searchField={},createFromTimeGMT='',createToTimeGMT=''):
|
|
""" 函数功能：读取普通表单的所有数据 -- 应用：F6客户服务 """
|
|
|
|
api = f'https://api.dingtalk.com/v1.0/yida/forms/instances/search'
|
|
|
|
headers = {
|
|
"Content-Type": "application/json",
|
|
"x-acs-dingtalk-access-token": token
|
|
}
|
|
|
|
formData = {
|
|
'currentPage': page,
|
|
'pageSize': n,
|
|
"appType": "APP_UYZ0KG6L0CCNV80GZ66O",
|
|
"systemToken": "XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2",
|
|
"userId": "yida_pub_account",
|
|
"language": "zh_CN",
|
|
"formUuid": formUuid,
|
|
'createFromTimeGMT':createFromTimeGMT,
|
|
'createToTimeGMT':createToTimeGMT,
|
|
"searchFieldJson": json.dumps(searchField)
|
|
}
|
|
|
|
res = requests.post(api, headers=headers, json=formData)
|
|
return res.json()
|
|
|
|
def create_form_instances(token, formUuid, formData={}):
|
|
""" 函数功能：创建普通表单实例 -- 应用：F6客户服务 """
|
|
|
|
api = 'https://api.dingtalk.com/v1.0/yida/forms/instances'
|
|
|
|
headers = {
|
|
"Content-Type": "application/json",
|
|
"x-acs-dingtalk-access-token": token
|
|
}
|
|
|
|
payload = {
|
|
"appType": "APP_UYZ0KG6L0CCNV80GZ66O",
|
|
"systemToken": "XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2",
|
|
"userId": "yida_pub_account",
|
|
"language": "zh_CN",
|
|
"formUuid": formUuid,
|
|
"formDataJson": json.dumps(formData)
|
|
}
|
|
|
|
res = requests.post(api, headers=headers, json=payload)
|
|
return res
|
|
def read_instances_1108(token, formUuid, page, n,formatted_today,formatted_today_two):
|
|
""" 函数功能：读取普通表单的所有数据 """
|
|
|
|
api = f'https://api.dingtalk.com//v1.0/yida/forms/instances/search'
|
|
|
|
headers = {
|
|
"Content-Type": "application/json",
|
|
"x-acs-dingtalk-access-token": token
|
|
}
|
|
|
|
formData = {
|
|
"appType" : "APP_UYZ0KG6L0CCNV80GZ66O",
|
|
"systemToken" : "XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2",
|
|
"userId" : "yida_pub_account",
|
|
"language" : "zh_CN",
|
|
"formUuid" : formUuid,
|
|
"createFromTimeGMT" : formatted_today,
|
|
"createToTimeGMT" : formatted_today_two,
|
|
"currentPage" : page,
|
|
"pageSize" : n
|
|
}
|
|
|
|
res = requests.post(api, headers=headers, json=formData)
|
|
return res.json()
|
|
class NpEncoder(json.JSONEncoder):
|
|
def default(self, obj):
|
|
if isinstance(obj, np.integer):
|
|
return int(obj)
|
|
elif isinstance(obj, np.floating):
|
|
return float(obj)
|
|
elif isinstance(obj, np.ndarray):
|
|
return obj.tolist()
|
|
else:
|
|
return super(NpEncoder, self).default(obj)
|
|
TOKEN = generateToken()
|
|
'''读取员工对应关系：宜搭员工-ID对应表 '''
|
|
FORMID = "FORM-EA866E715PF9YA7ECCAGSABX91Q72PVA3WRFL6" # 宜搭员工-ID对应表 FORM-EA866E715PF9YA7ECCAGSABX91Q72PVA3WRFL6
|
|
# 读取流程表单数据
|
|
form_data = read_instances(token=TOKEN, formUuid=FORMID, page=1, n=100)
|
|
PAGES = form_data.get('totalCount')//100 + 1
|
|
ALL_DATA_staff = {}
|
|
""" 获取全量数据 """
|
|
for i in range(1, PAGES+1):
|
|
# form_data = read_processes_instances(token=TOKEN, formUuid=FORMID, createFromTimeGMT=CREATE_FROM, createToTimeGMT=CREATE_TO, page=i, n=100, searchField={'textField_l7if5ff9': '否'})
|
|
form_data = read_instances(token=TOKEN, formUuid=FORMID, page=i, n=100)
|
|
for data in form_data.get('data'):
|
|
ALL_DATA_staff[data['formData']['textField_lfrw3u58']]=data['formData']['textField_lfrw3u59']
|
|
|
|
'''获取表内控件信息 '''
|
|
# FORMID = "FORM-ZK866D91O9LA4NIHCARG2DPIPCXF3Z087PPHL91"
|
|
# component_list = component(FORMID,TOKEN)
|
|
# for i in range(len(component_list['result'])):
|
|
# componentName = component_list['result'][i]['componentName']
|
|
# name_value = component_list['result'][i]['label']['value']
|
|
# fieldId = component_list['result'][i]['fieldId']
|
|
# print("'",fieldId,"':","data_NGV[",name_value,"][i],")
|
|
|
|
'''读取市-技术专家id '''
|
|
FORMID = "FORM-A8666NA1FJDEVPBB6VVTOCMP47UP2J2L6SPML2" #市-技术专家id
|
|
# 读取流程表单数据
|
|
form_data = read_instances(token=TOKEN, formUuid=FORMID, page=1, n=100)
|
|
PAGES = form_data.get('totalCount')//100 + 1
|
|
textField_lmps6q39 = {}
|
|
""" 获取全量数据 """
|
|
for i in range(1, PAGES+1):
|
|
# form_data = read_processes_instances(token=TOKEN, formUuid=FORMID, createFromTimeGMT=CREATE_FROM, createToTimeGMT=CREATE_TO, page=i, n=100, searchField={'textField_l7if5ff9': '否'})
|
|
form_data = read_instances(token=TOKEN, formUuid=FORMID, page=i, n=100)
|
|
for data in form_data.get('data'):
|
|
textField_lmps6q39[data['formData']['textField_lmps6q38']]=data['formData']['textField_lmps6q39'] #技术专家id
|
|
print(f'读取到市-技术专家id表单中 {len(textField_lmps6q39)} 条数据！')
|
|
|
|
NAME_LIST = ["无","江苏省","河南省","浙江省","陕西省","河北省","山东省","辽宁省","湖南省","四川省","广东省","上海市","贵州省","云南省","安徽省","湖北省","重庆市","新疆维吾尔自治区","内蒙古自治区","北京市","天津市","山西省","甘肃省","福建省","吉林省","广西壮族自治区","黑龙江省","江西省","宁夏回族自治区","青海省","海南省","西藏自治区","中华人民共和国","香港特别行政区"]
|
|
data_new = {}
|
|
FormDatas = []
|
|
# 1.读取普通表单数据
|
|
for name in NAME_LIST:
|
|
searchField={'textField_1q6dt9a':name}
|
|
res = read_form_instances(token=TOKEN, formUuid='FORM-ZK866D91O9LA4NIHCARG2DPIPCXF3Z087PPHL91',searchField=searchField)
|
|
totalCount = res.get('totalCount')
|
|
if totalCount>5000:
|
|
# 创建一个异常提醒
|
|
create_form_instances(token=TOKEN,formUuid='FORM-5Q966D91S1B8C9PP76L2O5BS9C2L3TXQ4JGELZ',formData={'textField_legj59bj':'NGV数据同步读取数据超过5000异常提醒。'})
|
|
break
|
|
else:
|
|
PAGES = totalCount//100 + 1
|
|
for page in range(1,PAGES+1):
|
|
# print(name,page)
|
|
res = read_form_instances(token=TOKEN, formUuid='FORM-ZK866D91O9LA4NIHCARG2DPIPCXF3Z087PPHL91',searchField=searchField,page=page,n=100)
|
|
FormDatas.extend(res.get('data'))
|
|
for v in range(0,len(FormDatas)):
|
|
data_new[FormDatas[v]['formData']['textField_zc1iowp']] = FormDatas[v]['formInstanceId']
|
|
print(f"表单数据共{len(data_new)}条!") #耗时7分钟左右
|
|
|
|
data_new
|
|
# 将字典转换为DataFrame
|
|
df_data_new = pd.DataFrame(list(data_new.items()), columns=['org_code', 'Value'])
|
|
df_data_new
|
|
|
|
# 使用isin方法筛选出A表中id_own_org字段不在B表中的行
|
|
c_data = data_NGV_new[~data_NGV_new['org_code'].isin(df_data_new['org_code'])]
|
|
# 输出C表内容
|
|
c_data = c_data.reset_index()
|
|
data_NGV = c_data.copy()
|
|
|
|
try:
|
|
alen_ong = []
|
|
for c in range(0,len(data_NGV["date_fmt"])):
|
|
searchField={'textField_zc1iowp':data_NGV["org_code"][c]}
|
|
res = read_form_instances(token=TOKEN, formUuid='FORM-ZK866D91O9LA4NIHCARG2DPIPCXF3Z087PPHL91',searchField=searchField)
|
|
totalCount = res.get('totalCount')
|
|
if totalCount > 0:
|
|
alen_ong.extend(str(c))
|
|
print(f"表单数据{totalCount}条!",data_NGV["org_code"][c])
|
|
if len(alen_ong) > 0:
|
|
for d in range(0,len(alen_ong)):
|
|
data_NGV = data_NGV.drop(int(alen_ong[d]))
|
|
except:
|
|
print("data_NGV:",len(data_NGV))
|
|
data_NGV.reset_index(drop=True, inplace=True)
|
|
|
|
'''遍历数据进行新建'''
|
|
data_NGV = data_NGV.astype('string')
|
|
data_NGV = data_NGV.fillna('',inplace=False)
|
|
for a in range(0,len(data_NGV["date_fmt"]),100):
|
|
ALL_formData = []
|
|
for i in range(a,a+100): # for i in range(len(data_NGV["date_fmt"])):
|
|
try:
|
|
formData = {
|
|
'textField_nnyw1ge': data_NGV["date_id"][i],
|
|
'dateField_e9jt02o': int(time.mktime(time.strptime(data_NGV["date_fmt"][i], "%Y-%m-%d"))*1000) if data_NGV["date_fmt"][i] !='' else '',
|
|
'textField_ek9jnne': data_NGV["id_own_group"][i],
|
|
'textField_35i6110': data_NGV["group_name"][i],
|
|
'textField_l8nc9f2': data_NGV["id_own_org"][i],
|
|
'textField_tdn6hsy': data_NGV["org_name"][i],
|
|
'textField_zc1iowp': data_NGV["org_code"][i],
|
|
'textField_2rulk5e': data_NGV["group_grade"][i],
|
|
'textField_c54tztg': data_NGV["org_type"][i],
|
|
'textField_qrtnel7': data_NGV["org_status"][i],
|
|
'textField_7xgxe9g': data_NGV["saas_version"][i],
|
|
'textField_pnig1i1': data_NGV["is_wechat"][i],
|
|
'textField_qiovtdj': data_NGV["is_mini_app"][i],
|
|
'textField_i0h0iqa': data_NGV["is_wx_shop"][i],
|
|
'textField_qczj9vq': data_NGV["is_camera_service"][i],
|
|
'textField_jouovty': data_NGV["is_maintenance_service"][i],
|
|
'dateField_o1v1emn': int(time.mktime(time.strptime(data_NGV["saas_create_time"][i], "%Y-%m-%d %H:%M:%S"))*1000) if data_NGV["saas_create_time"][i] !='' else '',
|
|
'dateField_6zdcfnf': int(time.mktime(time.strptime(data_NGV["expiry_time"][i], "%Y-%m-%d %H:%M:%S"))*1000) if data_NGV["expiry_time"][i] !='' else '',
|
|
'textField_8uae70x': data_NGV["saas_use_days"][i],
|
|
'textField_cjkvqpn': data_NGV["saas_use_year"][i],
|
|
'textField_yhtmsxx': data_NGV["is_main_org"][i],
|
|
'textField_gfigtpn': data_NGV["license_code"][i],
|
|
'textField_662foev': data_NGV["license_name"][i],
|
|
'textField_lco27te': data_NGV["org_crm_id"][i],
|
|
'textField_1tygbmq': data_NGV["province_id"][i],
|
|
'textField_z2rntja': data_NGV["city_id"][i],
|
|
'textField_ohybc2g': data_NGV["city_name"][i],
|
|
'textField_9oupoal': data_NGV["area_id"][i],
|
|
'textField_m0rzgwb': data_NGV["area_name"][i],
|
|
'textField_69po7o9': data_NGV["region_name"][i],
|
|
'textField_vqxr0d9': data_NGV["region_short_name"][i],
|
|
'textField_5wr3tj9': data_NGV["branch_name"][i],
|
|
'textField_6hz7i81': data_NGV["carzone_store_id"][i],
|
|
'textField_86wu9qm': data_NGV["carzone_store_name"][i],
|
|
'textField_129quhy': data_NGV["customer_carzone_id"][i],
|
|
'textField_j7i4mhn': data_NGV["salesmen"][i],
|
|
'textField_qms107e': data_NGV["area_manager"][i],
|
|
'textField_qc3blb5': data_NGV["service_salesmen"][i],
|
|
'textField_vk0hf2u': data_NGV["impl_principal"][i],
|
|
'textField_3lfom9a': data_NGV["service_impl_principal"][i],
|
|
'textField_1ud1o2l': data_NGV["active_user_count"][i],
|
|
'textField_kpdh7qc': data_NGV["active_user_type"][i],
|
|
'textField_n2y4bmf': data_NGV["limit_user_count"][i],
|
|
'textField_k5o5u2d': data_NGV["limit_user_type"][i],
|
|
'textField_729y7jp': data_NGV["is_n"][i],
|
|
'textField_six7heq': data_NGV["is_g"][i],
|
|
'textField_tipmhx4': data_NGV["is_v"][i],
|
|
'textField_qclt1dm': data_NGV["is_visited"][i],
|
|
'textField_0sk94f9': data_NGV["is_active"][i],
|
|
'textField_bzszf71': data_NGV["active_status_fmt"][i],
|
|
'textField_c061ojt': data_NGV["bill_count_last_30_day"][i],
|
|
'textField_07zkoii': data_NGV["bill_day_count_last_30_day"][i],
|
|
'textField_3vnzvsx': data_NGV["bill_day_count_this_month"][i],
|
|
'textField_3mxwdc4': data_NGV["bill_count_last_7_day"][i],
|
|
'textField_4q3bh5w': data_NGV["bill_day_count_last_7_day"][i],
|
|
'textField_u0rgodd': data_NGV["pv_count"][i],
|
|
'textField_eau5xx6': data_NGV["uv_count"][i],
|
|
'textField_tayz1vv': data_NGV["bill_count_1d"][i],
|
|
'textField_k4b1a7x': data_NGV["bill_count_2d"][i],
|
|
'textField_7lfqnm3': data_NGV["bill_count_3d"][i],
|
|
'textField_g24x9fv': data_NGV["bill_count_4d"][i],
|
|
'textField_0v3pm0r': data_NGV["bill_count_5d"][i],
|
|
'textField_1nxvats': data_NGV["bill_count_6d"][i],
|
|
'textField_vylq7uh': data_NGV["bill_count_7d"][i],
|
|
'textField_0n1zd7o': data_NGV["bill_count_8d"][i],
|
|
'textField_uuatjx7': data_NGV["bill_count_9d"][i],
|
|
'textField_e16m62c': data_NGV["bill_count_10d"][i],
|
|
'textField_anv4rco': data_NGV["bill_count_11d"][i],
|
|
'textField_pz5cg65': data_NGV["bill_count_12d"][i],
|
|
'textField_hamkvor': data_NGV["bill_count_13d"][i],
|
|
'textField_kw9nvef': data_NGV["bill_count_14d"][i],
|
|
'textField_ujbyern': data_NGV["bill_count_15d"][i],
|
|
'textField_uqp3072': data_NGV["bill_count_16d"][i],
|
|
'textField_2qsei19': data_NGV["bill_count_17d"][i],
|
|
'textField_p5x4u8t': data_NGV["bill_count_18d"][i],
|
|
'textField_2lv8ksl': data_NGV["bill_count_19d"][i],
|
|
'textField_unl61g8': data_NGV["bill_count_20d"][i],
|
|
'textField_cvtamin': data_NGV["bill_count_21d"][i],
|
|
'textField_z0hs1c4': data_NGV["bill_count_22d"][i],
|
|
'textField_9jln7lp': data_NGV["bill_count_23d"][i],
|
|
'textField_jlst4j8': data_NGV["bill_count_24d"][i],
|
|
'textField_ct6z2pu': data_NGV["bill_count_25d"][i],
|
|
'textField_6s2qy9p': data_NGV["bill_count_26d"][i],
|
|
'textField_l9lyxnr': data_NGV["bill_count_27d"][i],
|
|
'textField_sz5xht3': data_NGV["bill_count_28d"][i],
|
|
'textField_ccodrkd': data_NGV["bill_count_29d"][i],
|
|
'textField_dy7c14l': data_NGV["bill_count_30d"][i],
|
|
'textField_delij7o': data_NGV["bill_count_31d"][i],
|
|
'dateField_4vp44kc': int(time.mktime(time.strptime(data_NGV["etl_time"][i], "%Y-%m-%d %H:%M:%S"))*1000) if data_NGV["etl_time"][i] !='' else '',
|
|
'textField_0d97bpy': data_NGV["maintain_bill_count_last_30_day"][i],
|
|
'textField_3nbi33y': data_NGV["washing_bill_count_last_30_day"][i],
|
|
'textField_17yhr79': data_NGV["maintain_bill_day_count_last_30_day"][i],
|
|
'textField_e1mrwvb': data_NGV["washing_bill_day_count_last_30_day"][i],
|
|
'textField_8s47o64': data_NGV["retail_bill_count_last_30_day"][i],
|
|
'textField_jr9otam': data_NGV["retail_bill_day_count_last_30_day"][i],
|
|
'textField_mtg1zn1': data_NGV["purchase_bill_count_last_30_day"][i],
|
|
'textField_96ggfjo': data_NGV["purchase_bill_day_count_last_30_day"][i],
|
|
'textField_hk7pe9z': data_NGV["card_bill_count_last_30_day"][i],
|
|
'textField_yj3vbwq': data_NGV["card_bill_day_count_last_30_day"][i],
|
|
'textField_n2hdr45': data_NGV["gd_sales_bill_count_last_30_day"][i],
|
|
'textField_p7xgdah': data_NGV["gd_sales_bill_day_count_last_30_day"][i],
|
|
'textField_7xr2ar8': data_NGV["g_change_flag"][i],
|
|
'textField_gpzxxmk': data_NGV["saas_package"][i],
|
|
'textField_tq9g8in': data_NGV["manage_model"][i],
|
|
'textField_zzchgop': data_NGV["contacts"][i],
|
|
'textField_a2znmfb': data_NGV["contact_number"][i],
|
|
'textField_0sadows': data_NGV["contact_mobile"][i],
|
|
'textField_hz3ujlo': data_NGV["g_month_count"][i],
|
|
'textField_m1gdeaa': data_NGV["g_month_percentage"][i],
|
|
'textField_y7ds4oo': data_NGV["is_install_service"][i],
|
|
'dateField_hiuaj0y': int(time.mktime(time.strptime(data_NGV["install_create_time"][i], "%Y-%m-%d %H:%M:%S"))*1000) if data_NGV["install_create_time"][i] !='' else '',
|
|
'dateField_yubgpmy': int(time.mktime(time.strptime(data_NGV["last_end_date"][i], "%Y-%m-%d"))*1000) if data_NGV["last_end_date"][i] !='' else '',
|
|
'dateField_8st5q1a': int(time.mktime(time.strptime(data_NGV["renew_date"][i], "%Y-%m-%d"))*1000) if data_NGV["renew_date"][i] !=''else '',
|
|
'textField_u8h91r2': data_NGV["is_chain_owner"][i],
|
|
'textField_qzzz8ux': data_NGV["group_org_count"][i],
|
|
'textField_qzjf8mc': data_NGV["recent_bill_warning_days"][i],
|
|
'textField_lvyu7z2': data_NGV["g_change_flag_d"][i],
|
|
'textField_d72wy3d': data_NGV["g_lost_warning_days"][i],
|
|
'textField_11ebiwp': data_NGV["saas_edition_fmt"][i],
|
|
'textField_4yu5vlq': data_NGV["g_flag_1m"][i],
|
|
'textField_8ydk27g': data_NGV["g_flag_2m"][i],
|
|
'textField_ysoij3v': data_NGV["g_flag_3m"][i],
|
|
'textField_a4t6iwp': data_NGV["g_flag_4m"][i],
|
|
'textField_z1xfteb': data_NGV["g_flag_5m"][i],
|
|
'textField_56x8u44': data_NGV["g_flag_6m"][i],
|
|
'textField_zscafbj': data_NGV["g_flag_day_count"][i],
|
|
'textField_4c0rpdd': data_NGV["add_org_flag"][i],
|
|
'textField_14we48g': data_NGV["pt"][i],
|
|
'textField_gukslqe': data_NGV["org_size"][i],
|
|
'textField_y0tw39j': data_NGV["qualification_type_fmt"][i],
|
|
'textField_3euralk': data_NGV["business_scope_fmt"][i],
|
|
'textField_4jji26g': data_NGV["store_type_fmt"][i],
|
|
'textField_z2mrvyk': data_NGV["area"][i],
|
|
'textField_e7qmeix': data_NGV["station_number"][i],
|
|
'textField_x9q5k8p': data_NGV["header_type_fmt"][i],
|
|
'textField_h423ob8': data_NGV["org_stage"][i],
|
|
'textField_w6dk6au': data_NGV["g_count_this_month"][i],
|
|
'textField_67sllhy': data_NGV["saas_customer_type"][i]
|
|
}
|
|
try:
|
|
if data_NGV["province_name"][i]=="":
|
|
formData['textField_1q6dt9a'] = "无"
|
|
else:
|
|
formData['textField_1q6dt9a'] = data_NGV["province_name"][i]
|
|
except:
|
|
formData['textField_1q6dt9a'] = "无"
|
|
try:
|
|
formData['employeeField_llc00m15'] = ALL_DATA_staff[data_NGV["service_impl_principal"][i]] # 运营负责人_成员字段
|
|
except:
|
|
formData['employeeField_llc00m15'] = ""
|
|
try:
|
|
formData['employeeField_llc00m16'] = ALL_DATA_staff[data_NGV["area_manager"][i]] # 区域经理_成员字段
|
|
except:
|
|
formData['employeeField_llc00m16'] = ""
|
|
try:
|
|
formData['employeeField_lmpsfc04'] = textField_lmps6q39[data_NGV["city_name"][i]] # 技术专家_成员字段
|
|
except:
|
|
formData['employeeField_lmpsfc04'] = ""
|
|
|
|
ALL_formData.append(json.dumps(formData, cls=NpEncoder))
|
|
except:
|
|
pass
|
|
FORMID = "FORM-ZK866D91O9LA4NIHCARG2DPIPCXF3Z087PPHL91"
|
|
res_new = Batch_creation(FORMID,TOKEN,ALL_formData)
|
|
print("新建第",i,"条数据!")
|
|
|
|
|
|
'''遍历数据进行更新'''
|
|
data_NGV_new = data_NGV_new.astype('string')
|
|
data_NGV_new = data_NGV_new.fillna('',inplace=False)
|
|
for a in range(0,len(data_NGV_new["date_fmt"]),100):
|
|
for i in range(a,a+100): # for i in range(len(data_NGV_new["date_fmt"])):
|
|
try:
|
|
formData = {
|
|
'textField_nnyw1ge': data_NGV_new["date_id"][i],
|
|
'dateField_e9jt02o': int(time.mktime(time.strptime(data_NGV_new["date_fmt"][i], "%Y-%m-%d"))*1000) if data_NGV_new["date_fmt"][i] !='' else '',
|
|
'textField_ek9jnne': data_NGV_new["id_own_group"][i],
|
|
'textField_35i6110': data_NGV_new["group_name"][i],
|
|
'textField_l8nc9f2': data_NGV_new["id_own_org"][i],
|
|
'textField_tdn6hsy': data_NGV_new["org_name"][i],
|
|
'textField_zc1iowp': data_NGV_new["org_code"][i],
|
|
'textField_2rulk5e': data_NGV_new["group_grade"][i],
|
|
'textField_c54tztg': data_NGV_new["org_type"][i],
|
|
'textField_qrtnel7': data_NGV_new["org_status"][i],
|
|
'textField_7xgxe9g': data_NGV_new["saas_version"][i],
|
|
'textField_pnig1i1': data_NGV_new["is_wechat"][i],
|
|
'textField_qiovtdj': data_NGV_new["is_mini_app"][i],
|
|
'textField_i0h0iqa': data_NGV_new["is_wx_shop"][i],
|
|
'textField_qczj9vq': data_NGV_new["is_camera_service"][i],
|
|
'textField_jouovty': data_NGV_new["is_maintenance_service"][i],
|
|
'dateField_o1v1emn': int(time.mktime(time.strptime(data_NGV_new["saas_create_time"][i], "%Y-%m-%d %H:%M:%S"))*1000) if data_NGV_new["saas_create_time"][i] !='' else '',
|
|
'dateField_6zdcfnf': int(time.mktime(time.strptime(data_NGV_new["expiry_time"][i], "%Y-%m-%d %H:%M:%S"))*1000) if data_NGV_new["expiry_time"][i] !='' else '',
|
|
'textField_8uae70x': data_NGV_new["saas_use_days"][i],
|
|
'textField_cjkvqpn': data_NGV_new["saas_use_year"][i],
|
|
'textField_yhtmsxx': data_NGV_new["is_main_org"][i],
|
|
'textField_gfigtpn': data_NGV_new["license_code"][i],
|
|
'textField_662foev': data_NGV_new["license_name"][i],
|
|
'textField_lco27te': data_NGV_new["org_crm_id"][i],
|
|
'textField_1tygbmq': data_NGV_new["province_id"][i],
|
|
'textField_z2rntja': data_NGV_new["city_id"][i],
|
|
'textField_ohybc2g': data_NGV_new["city_name"][i],
|
|
'textField_9oupoal': data_NGV_new["area_id"][i],
|
|
'textField_m0rzgwb': data_NGV_new["area_name"][i],
|
|
'textField_69po7o9': data_NGV_new["region_name"][i],
|
|
'textField_vqxr0d9': data_NGV_new["region_short_name"][i],
|
|
'textField_5wr3tj9': data_NGV_new["branch_name"][i],
|
|
'textField_6hz7i81': data_NGV_new["carzone_store_id"][i],
|
|
'textField_86wu9qm': data_NGV_new["carzone_store_name"][i],
|
|
'textField_129quhy': data_NGV_new["customer_carzone_id"][i],
|
|
'textField_j7i4mhn': data_NGV_new["salesmen"][i],
|
|
'textField_qms107e': data_NGV_new["area_manager"][i],
|
|
'textField_qc3blb5': data_NGV_new["service_salesmen"][i],
|
|
'textField_vk0hf2u': data_NGV_new["impl_principal"][i],
|
|
'textField_3lfom9a': data_NGV_new["service_impl_principal"][i],
|
|
'textField_1ud1o2l': data_NGV_new["active_user_count"][i],
|
|
'textField_kpdh7qc': data_NGV_new["active_user_type"][i],
|
|
'textField_n2y4bmf': data_NGV_new["limit_user_count"][i],
|
|
'textField_k5o5u2d': data_NGV_new["limit_user_type"][i],
|
|
'textField_729y7jp': data_NGV_new["is_n"][i],
|
|
'textField_six7heq': data_NGV_new["is_g"][i],
|
|
'textField_tipmhx4': data_NGV_new["is_v"][i],
|
|
'textField_qclt1dm': data_NGV_new["is_visited"][i],
|
|
'textField_0sk94f9': data_NGV_new["is_active"][i],
|
|
'textField_bzszf71': data_NGV_new["active_status_fmt"][i],
|
|
'textField_c061ojt': data_NGV_new["bill_count_last_30_day"][i],
|
|
'textField_07zkoii': data_NGV_new["bill_day_count_last_30_day"][i],
|
|
'textField_3vnzvsx': data_NGV_new["bill_day_count_this_month"][i],
|
|
'textField_3mxwdc4': data_NGV_new["bill_count_last_7_day"][i],
|
|
'textField_4q3bh5w': data_NGV_new["bill_day_count_last_7_day"][i],
|
|
'textField_u0rgodd': data_NGV_new["pv_count"][i],
|
|
'textField_eau5xx6': data_NGV_new["uv_count"][i],
|
|
'textField_tayz1vv': data_NGV_new["bill_count_1d"][i],
|
|
'textField_k4b1a7x': data_NGV_new["bill_count_2d"][i],
|
|
'textField_7lfqnm3': data_NGV_new["bill_count_3d"][i],
|
|
'textField_g24x9fv': data_NGV_new["bill_count_4d"][i],
|
|
'textField_0v3pm0r': data_NGV_new["bill_count_5d"][i],
|
|
'textField_1nxvats': data_NGV_new["bill_count_6d"][i],
|
|
'textField_vylq7uh': data_NGV_new["bill_count_7d"][i],
|
|
'textField_0n1zd7o': data_NGV_new["bill_count_8d"][i],
|
|
'textField_uuatjx7': data_NGV_new["bill_count_9d"][i],
|
|
'textField_e16m62c': data_NGV_new["bill_count_10d"][i],
|
|
'textField_anv4rco': data_NGV_new["bill_count_11d"][i],
|
|
'textField_pz5cg65': data_NGV_new["bill_count_12d"][i],
|
|
'textField_hamkvor': data_NGV_new["bill_count_13d"][i],
|
|
'textField_kw9nvef': data_NGV_new["bill_count_14d"][i],
|
|
'textField_ujbyern': data_NGV_new["bill_count_15d"][i],
|
|
'textField_uqp3072': data_NGV_new["bill_count_16d"][i],
|
|
'textField_2qsei19': data_NGV_new["bill_count_17d"][i],
|
|
'textField_p5x4u8t': data_NGV_new["bill_count_18d"][i],
|
|
'textField_2lv8ksl': data_NGV_new["bill_count_19d"][i],
|
|
'textField_unl61g8': data_NGV_new["bill_count_20d"][i],
|
|
'textField_cvtamin': data_NGV_new["bill_count_21d"][i],
|
|
'textField_z0hs1c4': data_NGV_new["bill_count_22d"][i],
|
|
'textField_9jln7lp': data_NGV_new["bill_count_23d"][i],
|
|
'textField_jlst4j8': data_NGV_new["bill_count_24d"][i],
|
|
'textField_ct6z2pu': data_NGV_new["bill_count_25d"][i],
|
|
'textField_6s2qy9p': data_NGV_new["bill_count_26d"][i],
|
|
'textField_l9lyxnr': data_NGV_new["bill_count_27d"][i],
|
|
'textField_sz5xht3': data_NGV_new["bill_count_28d"][i],
|
|
'textField_ccodrkd': data_NGV_new["bill_count_29d"][i],
|
|
'textField_dy7c14l': data_NGV_new["bill_count_30d"][i],
|
|
'textField_delij7o': data_NGV_new["bill_count_31d"][i],
|
|
'dateField_4vp44kc': int(time.mktime(time.strptime(data_NGV_new["etl_time"][i], "%Y-%m-%d %H:%M:%S"))*1000) if data_NGV_new["etl_time"][i] !='' else '',
|
|
'textField_0d97bpy': data_NGV_new["maintain_bill_count_last_30_day"][i],
|
|
'textField_3nbi33y': data_NGV_new["washing_bill_count_last_30_day"][i],
|
|
'textField_17yhr79': data_NGV_new["maintain_bill_day_count_last_30_day"][i],
|
|
'textField_e1mrwvb': data_NGV_new["washing_bill_day_count_last_30_day"][i],
|
|
'textField_8s47o64': data_NGV_new["retail_bill_count_last_30_day"][i],
|
|
'textField_jr9otam': data_NGV_new["retail_bill_day_count_last_30_day"][i],
|
|
'textField_mtg1zn1': data_NGV_new["purchase_bill_count_last_30_day"][i],
|
|
'textField_96ggfjo': data_NGV_new["purchase_bill_day_count_last_30_day"][i],
|
|
'textField_hk7pe9z': data_NGV_new["card_bill_count_last_30_day"][i],
|
|
'textField_yj3vbwq': data_NGV_new["card_bill_day_count_last_30_day"][i],
|
|
'textField_n2hdr45': data_NGV_new["gd_sales_bill_count_last_30_day"][i],
|
|
'textField_p7xgdah': data_NGV_new["gd_sales_bill_day_count_last_30_day"][i],
|
|
'textField_7xr2ar8': data_NGV_new["g_change_flag"][i],
|
|
'textField_gpzxxmk': data_NGV_new["saas_package"][i],
|
|
'textField_tq9g8in': data_NGV_new["manage_model"][i],
|
|
'textField_zzchgop': data_NGV_new["contacts"][i],
|
|
'textField_a2znmfb': data_NGV_new["contact_number"][i],
|
|
'textField_0sadows': data_NGV_new["contact_mobile"][i],
|
|
'textField_hz3ujlo': data_NGV_new["g_month_count"][i],
|
|
'textField_m1gdeaa': data_NGV_new["g_month_percentage"][i],
|
|
'textField_y7ds4oo': data_NGV_new["is_install_service"][i],
|
|
'dateField_hiuaj0y': int(time.mktime(time.strptime(data_NGV_new["install_create_time"][i], "%Y-%m-%d %H:%M:%S"))*1000) if data_NGV_new["install_create_time"][i] !='' else '',
|
|
'dateField_yubgpmy': int(time.mktime(time.strptime(data_NGV_new["last_end_date"][i], "%Y-%m-%d"))*1000) if data_NGV_new["last_end_date"][i] !='' else '',
|
|
'dateField_8st5q1a': int(time.mktime(time.strptime(data_NGV_new["renew_date"][i], "%Y-%m-%d"))*1000) if data_NGV_new["renew_date"][i] !=''else '',
|
|
'textField_u8h91r2': data_NGV_new["is_chain_owner"][i],
|
|
'textField_qzzz8ux': data_NGV_new["group_org_count"][i],
|
|
'textField_qzjf8mc': data_NGV_new["recent_bill_warning_days"][i],
|
|
'textField_lvyu7z2': data_NGV_new["g_change_flag_d"][i],
|
|
'textField_d72wy3d': data_NGV_new["g_lost_warning_days"][i],
|
|
'textField_11ebiwp': data_NGV_new["saas_edition_fmt"][i],
|
|
'textField_4yu5vlq': data_NGV_new["g_flag_1m"][i],
|
|
'textField_8ydk27g': data_NGV_new["g_flag_2m"][i],
|
|
'textField_ysoij3v': data_NGV_new["g_flag_3m"][i],
|
|
'textField_a4t6iwp': data_NGV_new["g_flag_4m"][i],
|
|
'textField_z1xfteb': data_NGV_new["g_flag_5m"][i],
|
|
'textField_56x8u44': data_NGV_new["g_flag_6m"][i],
|
|
'textField_zscafbj': data_NGV_new["g_flag_day_count"][i],
|
|
'textField_4c0rpdd': data_NGV_new["add_org_flag"][i],
|
|
'textField_14we48g': data_NGV_new["pt"][i],
|
|
'textField_gukslqe': data_NGV_new["org_size"][i],
|
|
'textField_y0tw39j': data_NGV_new["qualification_type_fmt"][i],
|
|
'textField_3euralk': data_NGV_new["business_scope_fmt"][i],
|
|
'textField_4jji26g': data_NGV_new["store_type_fmt"][i],
|
|
'textField_z2mrvyk': data_NGV_new["area"][i],
|
|
'textField_e7qmeix': data_NGV_new["station_number"][i],
|
|
'textField_x9q5k8p': data_NGV_new["header_type_fmt"][i],
|
|
'textField_h423ob8': data_NGV_new["org_stage"][i],
|
|
'textField_w6dk6au': data_NGV_new["g_count_this_month"][i],
|
|
'textField_67sllhy': data_NGV_new["saas_customer_type"][i]
|
|
}
|
|
try:
|
|
if data_NGV_new["province_name"][i]=="":
|
|
formData['textField_1q6dt9a'] = "无"
|
|
else:
|
|
formData['textField_1q6dt9a'] = data_NGV_new["province_name"][i]
|
|
except:
|
|
formData['textField_1q6dt9a'] = "无"
|
|
try:
|
|
formData['employeeField_llc00m15'] = ALL_DATA_staff[data_NGV_new["service_impl_principal"][i]] # 运营负责人_成员字段
|
|
except:
|
|
formData['employeeField_llc00m15'] = ""
|
|
try:
|
|
formData['employeeField_llc00m16'] = ALL_DATA_staff[data_NGV_new["area_manager"][i]] # 区域经理_成员字段
|
|
except:
|
|
formData['employeeField_llc00m16'] = ""
|
|
try:
|
|
formData['employeeField_lmpsfc04'] = textField_lmps6q39[data_NGV_new["city_name"][i]] # 技术专家_成员字段
|
|
except:
|
|
formData['employeeField_lmpsfc04'] = ""
|
|
|
|
read_instances_put(TOKEN, data_new[data_NGV_new["org_code"][i]],formData)
|
|
print("更新第",i,"条数据!")
|
|
except:
|
|
pass
|
|
|