361 lines
12 KiB
Python
361 lines
12 KiB
Python
# -*- coding: utf-8 -*-
|
||
import psycopg2
|
||
import pandas as pd
|
||
from datetime import date, timedelta
|
||
# 获得连接
|
||
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 = pd.DataFrame(list(rows),columns=col)
|
||
# data_NGV.to_excel(r'C:\Users\admin\Desktop\NGV明细.xlsx')
|
||
# 关闭数据库连接
|
||
cursor.close()
|
||
conn.close()
|
||
|
||
|
||
data_NGV['条件'] = (data_NGV['org_type'] == "一般") & (data_NGV['org_status'] == '留存') & (data_NGV['group_grade'].isin(['全国KA（FMVP）','区域KA（MVP）'])) & (data_NGV['is_main_org'] == 1)
|
||
data_NGV = data_NGV.loc[data_NGV["条件"] == True]
|
||
# 重置索引
|
||
data_NGV = data_NGV.reset_index(drop=True)
|
||
print(f'读取NGV大客户回访 {len(data_NGV)} 条数据！')
|
||
# 基础函数配置
|
||
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
|
||
from datetime import date, timedelta
|
||
|
||
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(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 initiate_process(TOKEN,formUuid,processCode,formData):
|
||
""" 发起宜搭审批流程 """
|
||
api = f'https://api.dingtalk.com//v1.0/yida/processes/instances/start'
|
||
|
||
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, cls=NpEncoder),
|
||
"processCode" : processCode,
|
||
}
|
||
|
||
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" : "true",
|
||
"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()
|
||
|
||
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)
|
||
|
||
import binascii
|
||
import time
|
||
import random
|
||
from pyDes import des, CBC, PAD_PKCS5
|
||
import requests
|
||
|
||
TOKEN = generateToken()
|
||
'''读取省市小六技术专家区域客服区域客成 '''
|
||
FORMID = "FORM-TP866D918DFCA4FW79YZU5X43FO32QZJQDZJL7" #省市小六技术专家区域客服区域客成
|
||
try:
|
||
# 读取流程表单数据
|
||
form_data = read_instances(token=TOKEN, formUuid=FORMID, page=1, n=100)
|
||
PAGES = form_data.get('totalCount')//100 + 1
|
||
textField_qk1e5di = {}
|
||
textField_nc7gskc = {}
|
||
""" 获取全量数据 """
|
||
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_qk1e5di[data['formData']['textField_m3hchxc']]=data['formData']['textField_qk1e5di'] #区域经理
|
||
textField_nc7gskc[data['formData']['textField_m3hchxc']]=data['formData']['textField_nc7gskc'] #技术专家id
|
||
print(f'读取省市小六技术专家区域客服区域客成表单中 {len(textField_nc7gskc)} 条数据！')
|
||
|
||
|
||
'''遍历数据进行新建'''
|
||
data_NGV = data_NGV.astype('string')
|
||
data_NGV = data_NGV.fillna('',inplace=False)
|
||
|
||
# 【+大客户】[表单]大客户日常回访_复制
|
||
formUuid = "FORM-WY766581O9U90C8E8HPWA4TI52OT3ZE94LLGLK"
|
||
processCode = "TPROC--WY766581O9U90C8E8HPWA4TI52OT3PG94LLGLL"
|
||
|
||
from datetime import datetime
|
||
# 获取当前日期
|
||
now = datetime.now()
|
||
# 格式化日期为年月日格式
|
||
formatted_date = now.strftime("%Y-%m-%d")
|
||
formatted_date_key = {
|
||
"2024-01-11":"24年一季度回访",
|
||
"2024-03-25":"24年二季度回访",
|
||
"2024-06-24":"24年三季度回访",
|
||
"2024-09-24":"24年四季度回访",
|
||
}
|
||
for i in range(0,len(data_NGV["date_fmt"])):
|
||
try:
|
||
formData = {
|
||
'selectField_lhhf16ou':formatted_date_key[formatted_date], # 回访任务
|
||
'textField_la80kiyd':data_NGV["id_own_group"][i], # 公司ID
|
||
'textField_lho8er5r':data_NGV["group_name"][i], # 公司名称
|
||
'textField_lc8vmo71':data_NGV["group_grade"][i], # 客户等级
|
||
'textField_la80kiym':data_NGV["province_name"][i], # 所在省
|
||
'textField_lhiibg69':textField_qk1e5di[data_NGV["city_name"][i]], # 战区经理
|
||
'employeeField_lho9zttv':textField_nc7gskc[data_NGV["city_name"][i]], # 技术专家
|
||
'radioField_lhe9agsx':"未开始" # 目前回访进展
|
||
}
|
||
res=initiate_process(TOKEN,formUuid,processCode,formData)
|
||
print(res,formData)
|
||
except:
|
||
'''校验是否新建正常'''
|
||
FORMID = "FORM-L8966281PTZA73CDBTGQBDLM628M2P4X1OYHL0"
|
||
def start_instance_process(token: str, name):
|
||
|
||
"""发送宜搭表单 -- 发起流程表单
|
||
|
||
Args:
|
||
token
|
||
data:需要发送的数据字典
|
||
"""
|
||
|
||
yida_api = "https://api.dingtalk.com/v1.0/yida/processes/instances/start"
|
||
|
||
headers = {
|
||
"Content-Type": "application/json",
|
||
"x-acs-dingtalk-access-token": token
|
||
}
|
||
|
||
send_data = {
|
||
"textField_l9fe0uiw": name,
|
||
"textField_l9fe0uiv": name
|
||
}
|
||
|
||
payload = {
|
||
"appType": "APP_TNVBVZ3K8G56HG03Z45Q",
|
||
"systemToken": "CH7669818R0WN18TYTYJ42PE6GY22WZN0BYWKD1",
|
||
"userId": "yida_pub_account",# 超级管理员账号
|
||
"language": "zh_CN",
|
||
"formUuid": "FORM-UX866Q61GNLAZBCIEDF77BGVIIR83K82WYPHLH2",
|
||
"formDataJson": json.dumps(send_data),
|
||
"processCode":"TPROC--UX866Q61GNLAZBCIEDF77BGVIIR83M92WYPHLI2"
|
||
}
|
||
|
||
res = requests.post(yida_api, headers=headers, json=payload)
|
||
return res
|
||
try:
|
||
name = f"大客户回访 未成功新建，请检查！{data_NGV['id_own_group'][i]}"
|
||
res_yujing = start_instance_process(TOKEN,name)
|
||
except:
|
||
pass
|
||
|
||
|
||
|
||
except:
|
||
'''校验是否新建正常'''
|
||
FORMID = "FORM-L8966281PTZA73CDBTGQBDLM628M2P4X1OYHL0"
|
||
def start_instance_process(token: str, name):
|
||
|
||
"""发送宜搭表单 -- 发起流程表单
|
||
|
||
Args:
|
||
token
|
||
data:需要发送的数据字典
|
||
"""
|
||
|
||
yida_api = "https://api.dingtalk.com/v1.0/yida/processes/instances/start"
|
||
|
||
headers = {
|
||
"Content-Type": "application/json",
|
||
"x-acs-dingtalk-access-token": token
|
||
}
|
||
|
||
send_data = {
|
||
"textField_l9fe0uiw": name,
|
||
"textField_l9fe0uiv": name
|
||
}
|
||
|
||
payload = {
|
||
"appType": "APP_TNVBVZ3K8G56HG03Z45Q",
|
||
"systemToken": "CH7669818R0WN18TYTYJ42PE6GY22WZN0BYWKD1",
|
||
"userId": "yida_pub_account",# 超级管理员账号
|
||
"language": "zh_CN",
|
||
"formUuid": "FORM-UX866Q61GNLAZBCIEDF77BGVIIR83K82WYPHLH2",
|
||
"formDataJson": json.dumps(send_data),
|
||
"processCode":"TPROC--UX866Q61GNLAZBCIEDF77BGVIIR83M92WYPHLI2"
|
||
}
|
||
|
||
res = requests.post(yida_api, headers=headers, json=payload)
|
||
return res
|
||
try:
|
||
name = f"大客户回访 表单数据读取失败，请检查！{data_NGV['id_own_group'][i]}"
|
||
res_yujing = start_instance_process(TOKEN,name)
|
||
except:
|
||
pass |