# -*- codeing = utf-8 -*- # @Time : 2022/8/29 14:43 # @Author : Clown # @File : EC_招商CRM.py # @Software : PyCharm import requests import hashlib import time import csv import json import pandas as pd import math from datetime import timedelta, datetime from dateutil.parser import parse # corpid : 17409173 # get_sign : 签名算法 def get_sign(app_id, app_secret, timestamp): ''' :param app_id: 应用ip 914890189023739904 :param app_secret: 应用密钥 Ur2K41t71RrxYn7eWhN :param timestamp: 时间戳 毫秒 :return: 加密密文 ''' str = 'appId=' + app_id + '&appSecret=' + app_secret + '&timeStamp=' + timestamp hl = hashlib.md5() hl.update(str.encode(encoding='utf-8')) return hl.hexdigest().upper() def getCustomDet(corpid,sign,timestamp,pageNo): url = 'https://open.workec.com/v2/customer/queryList' headers = {'Content-Type':'application/json', 'X-Ec-Cid':corpid, 'X-Ec-Sign':sign, 'X-Ec-TimeStamp':timestamp} # 全局浏览 # json = { # "__grouble_id": "x" # } # 排序浏览 asc升序，desc降序 json = { "pageNo": pageNo, "pageSize": 50, 'orderBy':[{"sortField":'createTime', "sortType":'desc'}] } resp = requests.post(url,headers=headers,json=json).json() # print(resp) return resp def selectCustomInfo(corpid,sign,timestamp,selectInfoFile,df_out_path): df = pd.read_excel(selectInfoFile,dtype=str) infoList = df['号码'] print(len(infoList)) df_out = [] infoList50 = [] for n in range(round(len(infoList)/50+1)): x = 50 * n y = 50 * (n + 1) info = ','.join(infoList[x:y]) url = 'https://open.workec.com/v2/customer/queryExist' headers = {'Content-Type': 'application/json', 'X-Ec-Cid': corpid, 'X-Ec-Sign': sign, 'X-Ec-TimeStamp': timestamp} json_params = {"mobile":str(info)} resps = requests.post(url,headers=headers,json=json_params).json()['data'] for resp in resps: df_out_dict = {} num = resp['num'] df_out_dict['mobile'] = resp['mobile'] df_out_dict['queryExistNum'] = num print(info,num) df_out.append(df_out_dict) n += 1 df_out = pd.DataFrame(df_out) df_out.to_excel(df_out_path) def getOkCustom(corpid,sign,timestamp,pageNo): url = 'https://open.workec.com/v2/customer/queryList' headers = {'Content-Type': 'application/json', 'X-Ec-Cid': corpid, 'X-Ec-Sign': sign, 'X-Ec-TimeStamp': timestamp} # 全局浏览 # json = { # "__grouble_id": "x" # } # 排序浏览 asc升序，desc降序 json = { "labelIds":[83625192], "pageNo": pageNo, "pageSize": 50, 'orderBy': [{"sortField": 'createTime', "sortType": 'desc'}] } resp = requests.post (url, headers=headers, json=json).json() # print(resp) return resp def getInfo(users_dict,corpid,sign,timestamp,pagesNo,out_file): f = open(out_file,mode='w',encoding="utf-8-sig",newline='') csv_writer = csv.writer(f) title = ['创建日期','资源id','资源来源','最后跟进人','组别','最后跟进时间','备注'] csv_writer.writerow(title) for pageNo in range(pagesNo): info_data = getCustomDet(corpid,sign,timestamp,pageNo+1) for info in info_data['data']['list']: createTime = info['createTime'] crmId = info['crmId'] channel = info['channel'] lastFollowUserId = 'userID:' +str(info['lastFollowUserId']) try: lastFollowUser = users_dict[lastFollowUserId]['userName'] deptName = users_dict[lastFollowUserId]['deptName'] except: lastFollowUser = lastFollowUserId deptName = '未知' try: lastContactTime = info['lastContactTime']['time'] except: lastContactTime = '' try: memo = info['memo'] except: memo = '' row = [createTime,crmId,channel,lastFollowUser,deptName,lastContactTime,memo] csv_writer.writerow(row) print('page:'+ str(pageNo) + 'ok') def getOkInfo(users_dict,corpid,sign,timestamp,pagesNo,out_file): f = open(out_file,mode='w',encoding="utf-8-sig",newline='') csv_writer = csv.writer(f) title = ['创建日期','资源id','资源来源','最后跟进人','组别','最后跟进时间','备注'] csv_writer.writerow(title) for pageNo in range(pagesNo): info_data = getOkCustom(corpid,sign,timestamp,pageNo+1) for info in info_data['data']['list']: createTime = info['createTime'] crmId = info['crmId'] channel = info['channel'] lastFollowUserId = 'userID:' +str(info['lastFollowUserId']) try: lastFollowUser = users_dict[lastFollowUserId]['userName'] deptName = users_dict[lastFollowUserId]['deptName'] except: lastFollowUser = lastFollowUserId deptName = '未知' try: lastContactTime = info['lastContactTime']['time'] except: lastContactTime = '' try: memo = info['memo'] except: memo = '' row = [createTime,crmId,channel,lastFollowUser,deptName,lastContactTime,memo] csv_writer.writerow(row) print('page:'+ str(pageNo) + 'ok') def getUserInfo(corpid,sign,timestamp): #获取组织架构信息，主要是对应上id和人名 url = 'https://open.workec.com/v2/org/struct/info' params = 'needUser=true' headers = {'Content-Type': 'application/json', 'X-Ec-Cid': corpid, 'X-Ec-Sign': sign, 'X-Ec-TimeStamp': timestamp} resp = requests.get(url,params=params,headers=headers).json() return resp # def runT(): # pages = OrdersInfoByPages() # executor = ThreadPoolExecutor (pages) # for i in range(pages): # offset = i*100 # executor.submit (lookupPagesInfo, offset) # executor.shutdown(wait=True) def getLabel(corpid,sign,timestamp): url = 'https://open.workec.com/v2/label/getLabelInfo' json_params = {'groupValue':''} headers = {'Content-Type': 'application/json', 'X-Ec-Cid': corpid, 'X-Ec-Sign': sign, 'X-Ec-TimeStamp': timestamp} resp = requests.post(url, json=json_params, headers=headers).text print(resp) def addCustomers(corpid,sign,timestamp,customerList): #新增客户 #来源渠道备注：47625为400电话， #customerList = [{}] url = 'https://open.workec.com/v2/customer/addCustomer' headers = {'Content-Type': 'application/json', 'X-Ec-Cid': corpid, 'X-Ec-Sign': sign, 'X-Ec-TimeStamp': timestamp} json_params = {'optUserId':17409174, 'list':customerList, 'notify':False, 'repeat':False, 'supply_detail':False} resp = requests.post(url,headers=headers,json=json_params).json() failureList = resp['data']['failureList'] successIdList = resp['data']['successIdList'] if len(successIdList) == 1: print('ok') elif len(failureList) == 1: print('error') def getChannelSource(corpid,sign,timestamp): #查询来源信息 url= 'https://open.workec.com/v2/customer/getChannelSource' headers = {'Content-Type': 'application/json', 'X-Ec-Cid': corpid, 'X-Ec-Sign': sign, 'X-Ec-TimeStamp': timestamp} resp = requests.get(url,headers=headers).json() print(json.dumps(resp,ensure_ascii=False)) def findUserInfoById(corpid,sign,timestamp,userId): url = 'https://open.workec.com/v2/org/user/findUserInfoById' headers = {'Content-Type': 'application/json', 'X-Ec-Cid': corpid, 'X-Ec-Sign': sign, 'X-Ec-TimeStamp': timestamp} json_params = {'userId':str(userId),'deptInfo':False} resp = requests.post(url,headers=headers,json=json_params).json() # print(resp) return resp def getCustomersTrajectory(corpid,sign,timestamp,trajectoryRequestDict): state_dict = {"1001": "发送短信", "1002": "拨打对方电话", "1003": "网站客服会话", "1004": "EC会话", "1005": "QQ会话", "1006": "发送邮件", "1007": "接听对方电话", "1008": "接收邮件", "1009": "电话会议", "1010": "拨打云总机电话", "1011": "接听云总机电话", "2003": "日历提醒", "2001": "定时提醒", "2002": "提醒销售计划", "3001": "新增客户", "3002": "新增客户资料", "3003": "更新客户资料", "3004": "更新客户标签", "3005": "关联EC", "3006": "取消EC关联", "3007": "关联QQ", "3008": "取消QQ关联", "3009": "转让客户", "3010": "合并客户", "3011": "分配客户", "3012": "领取客户", "3013": "放弃客户", "3014": "添加销售计划", "3015": "修改销售计划", "3016": "使用销售模板", "3017": "更新客户阶段", "3018": "关联公司", "3019": "取消公司关联", "3020": "上传头像", "3021": "新增共享同事", "3022": "取消共享同事", "3023": "退出共享关系", "3024": "转为公司导入", "4000": "添加跟进记录", "6000": "微信活动", "7000": "拜访客户"} crmIds = trajectoryRequestDict['crmIds'] lastContactTime = trajectoryRequestDict['lastContactTime'] memo = trajectoryRequestDict['memo'] #memo决定是否为首次获取日志 #查询用户信息 url_crmInfo = 'https://open.workec.com/v2/customer/queryList' json_params_crmInfo = {'crmIds': [crmIds]} url = 'https://open.workec.com/v2/customer/getTrajectory' headers = {'Content-Type': 'application/json', 'X-Ec-Cid': corpid, 'X-Ec-Sign': sign, 'X-Ec-TimeStamp': timestamp} resp_crmInfo = requests.post (url_crmInfo, headers=headers, json=json_params_crmInfo).json ()['data'] # print(resp_crmInfo) #获取当前跟进人 try: followerName = findUserInfoById (corpid, sign, timestamp, resp_crmInfo['list'][0]['lastFollowUserId'])['data']['name'] except: followerName = '离职员工' #获取创建时间及最近一次跟进时间 try: createTime = resp_crmInfo['list'][0]['createTime'] lastTime = resp_crmInfo['list'][0]['contactTime'] except: createTime = resp_crmInfo['list'][0]['createTime'] lastTime = createTime # print(createTime,lastTime) #依据是否首次获取跟进日志，构建日志查询参数 endTime = parse (str (parse (str (lastTime)) + timedelta (seconds=2))).strftime ('%Y-%m-%d %H:%M:%S') #首次 if int(memo) == 0: startTime = parse (str (parse (str (createTime)) + timedelta (seconds=-2))).strftime ('%Y-%m-%d %H:%M:%S') # 判断日期间隔 n = math.ceil ((parse (str (endTime)).timestamp () - parse (str (startTime)).timestamp ()) / 3600 / 24 / 30) if n <= 1: json_params = [{'crmIds':str(crmIds), 'date':{'startTime':startTime, 'endTime':endTime}}] #日期间隔大于30天 else: json_params = [] x = 1 for i in range (n): if x < n: m = parse (str (parse (str (startTime)) + timedelta (days=30))).strftime ('%Y-%m-%d %H:%M:%S') x += 1 else: m = endTime json_param = {'crmIds':str(crmIds), 'date':{'startTime':startTime, 'endTime':m}} json_params.append(json_param) startTime = parse (str (parse (str (m)) + timedelta (seconds=1))).strftime ('%Y-%m-%d %H:%M:%S') # 二次 elif int(memo) == 1: startTime = parse (str (parse (str (lastContactTime.replace('T',' '))) + timedelta (seconds=1))).strftime ('%Y-%m-%d %H:%M:%S') json_params = [{'crmIds': str (crmIds), 'date': {'startTime': startTime, 'endTime': endTime}}] log = '' for json_param in json_params: resp = requests.post(url,headers=headers,json=json_param).json()['data'] trajectoryList = resp['trajectoryList'] #当前时间内是否有更新 if len(trajectoryList) == 0: trajectoryResult = {'trajectoryLog': log, 'result':-2, 'followerName': followerName, 'lastContactTime': lastTime.replace (' ', 'T')} else: for trajectory in trajectoryList: createTime = trajectory['createTime'] trajectoryType = trajectory['trajectoryType'] trajectoryName = state_dict[str(trajectoryType)] userId = trajectory['userId'] try: userName = findUserInfoById (corpid, sign, timestamp, userId)['data']['name'] if userName == '谢总': userName = '系统分配' else: ... except: userName = '员工已离职' if trajectoryType == 4000: recordInfo = f"\n备注:【{trajectory['content']}】" elif trajectoryType in [3009]: receiveUserIds = trajectory['receiveUserIds'] try: receiveUserName = findUserInfoById (corpid, sign, timestamp, receiveUserIds)['data']['name'] except: receiveUserName = '离职员工' recordInfo = f'\n备注:客户转让至【{receiveUserName}】' else: recordInfo = '' text_out = f'{createTime}--跟进动作:{trajectoryName}--操作人:{userName}{recordInfo}' # print(text_out) log = log + text_out + '\n' # print(trajectoryType) if trajectoryType == 3013: mark = -1 elif trajectoryType in [3001,3011,3012,3009]: mark = 0 else: mark = 1 trajectoryResult = {'trajectoryLog':log, 'result':mark, 'followerName':followerName, 'lastContactTime':lastTime.replace(' ','T')} # print(trajectoryResult) return trajectoryResult def runTrajectory(trajectoryRequestDict): ''' result 为int 数值为0，1，-1 【0表示未跟进，1表示跟进中，-1表示客户被放弃】 ''' corpid = '17409173' timestamp = str (int (round (time.time () * 1000))) app_id = '914890189023739904' app_secret = 'Ur2K41t71RrxYn7eWhN' sign = get_sign (app_id, app_secret, str (timestamp)) try: trajectoryResult = getCustomersTrajectory(corpid,sign,timestamp,trajectoryRequestDict) except Exception as e: print(e) print ('input Info Error1!') trajectoryResult= {'trajectoryLog': '参数有误', 'followerName': '', 'result': 'error', 'lastContactTime':'Null'} return trajectoryResult # 批量查询客户信息 def selectCustomsInfo(clue_photo_num_list,cycle_hours): # 入参格式 clue_photo_num_list = ['13775234802,19232722757,19903546661,18164685587,18655626682,13152011389,18709596396,15536158166,13752862728,17767157888,13812552265,18751168688,13868980059,15994211909,15330337158,18069510127,16685833888,13989666719,18082610680,13629286297,15151093376,15349370007,18696933445,13777447476,19218748440,13765636503,18655626682,15703592119,15703592119,15996391796,16613882968,18968498788,18261563639,18012565368,18012565368,13867934378,13813841224,18969513827,13765636503,18984319715,18963978060,15514586363,13813000590,13894722167,13100716617,13100716617,15202388770,18716736100,13801584013'] corpid = '17409173' timestamp = str(int(round(time.time() * 1000))) app_id = '914890189023739904' app_secret = 'Ur2K41t71RrxYn7eWhN' sign = get_sign(app_id, app_secret, str(timestamp)) url = 'https://open.workec.com/v2/customer/queryList' headers = {'Content-Type': 'application/json', 'X-Ec-Cid': corpid, 'X-Ec-Sign': sign, 'X-Ec-TimeStamp': timestamp} out_list = [] for group in clue_photo_num_list: params = {'mobile':group} resp = requests.post(url,headers=headers,json=params).text resp = json.loads(resp) for i in resp['data']['list']: createTime = i['createTime'] noteTime = (parse(createTime)+timedelta(hours = cycle_hours)).strftime('%Y-%m-%d %H:%M:%S') crmId = i['crmId'] mobile = i['mobile'][-11:] print(mobile,crmId,createTime,cycle_hours,noteTime,'') out_list.append((mobile,crmId,createTime,cycle_hours,noteTime,None,'同步crmId')) print(out_list) return out_list if __name__ == '__main__': if 1==0: corpid = '17409173' timestamp = str(int(round(time.time() * 1000))) app_id = '914890189023739904' app_secret = 'Ur2K41t71RrxYn7eWhN' sign = get_sign(app_id, app_secret, str(timestamp)) # getLabel (corpid, sign, str(timestamp)) crmIds = [6649346389,6716036532,6713477481] trajectoryRequestDict = {'crmIds':8428482275,'memo':0,'lastContactTime':0} getCustomersTrajectory (corpid, sign, timestamp, trajectoryRequestDict) clue_photo_num_list = ['16685833888,13765636503,18751168688,15994211909,18969513827,15330337158,13100716617,18716736100,13801584013,15900818382,18696933445,18752808628,15202388770,18069510127,18164685587,18957915191,15180810078,15161112338,18963978060,16613882968,18082610680,13752862728,13812552265,13011848788,13629286297,15349370007,13914701944,13867934378,18611013399,13777126490,13349994865,15996391796,18984319715,18968498788,18012565368,15050580681,15605853405,18131725305,15703592119,18709596396,15514586363,13851034333,13777447476,13813000590,18280502701,19154961250,13152011389,15536158166,18609680601,18655626682', '19903546661,13894722167,13989666719,13813841224,13775234802,15507281555,19218748440,15151093376,17767157888,19232722757,18762280927,18261563639,13868980059'] cycle_hours = 15 selectCustomsInfo(clue_photo_num_list,cycle_hours) if 1 == 0: user_data = getUserInfo (corpid, sign, str(timestamp)) # print(json.dumps(user_data,ensure_ascii=False)) users_dict = {} for user in user_data['data']['users']: deptId = user['deptId'] if deptId == 17440441: users_dict['userID:' + str (user['userId'])] = {"userName": user['userName'], "deptName": '招商一部'} elif deptId == 17440442: users_dict['userID:' + str (user['userId'])] = {"userName": user['userName'], "deptName": '招商二部'} elif deptId == 18659257: users_dict['userID:' + str (user['userId'])] = {"userName": user['userName'], "deptName": '招商三部'} else: ... # print(json.dumps(users_dict,ensure_ascii=False)) if 1 == 0: # print(sign) out_file = f'C:/Users/ClownHe/Desktop/导出/{timestamp}招商数据.csv' total_data = getCustomDet(corpid,sign,str(timestamp),1) pagesNo = total_data['data']['page']['maxPageNo'] getInfo (users_dict, corpid, sign, str(timestamp), pagesNo, out_file) if 1 == 0: out_file = f'C:/Users/ClownHe/Desktop/导出/{timestamp}招商签约数据.csv' total_data = getOkCustom (corpid, sign, str (timestamp), 1) pagesNo = total_data['data']['page']['maxPageNo'] getOkInfo (users_dict, corpid, sign, str (timestamp), pagesNo, out_file) getChannelSource (corpid, sign, timestamp) selectInfoFile = 'C:/Users/ClownHe/Desktop/goods/浆小白线索数据.xlsx' df_out_path = 'C:/Users/ClownHe/Desktop/goods/浆小白线索数据(核对).xlsx' selectCustomInfo (corpid, sign, str (timestamp), selectInfoFile, df_out_path) # list_a = [{ # "followUserId":17409174, # "mobile":"18795412345", # "name":"测试数据请忽略", # 'memo':'400热线' # }] # addCustomers (corpid, sign, timestamp, list_a)