python_files/cppc_python脚本/服务器脚本/EC_招商CRM.py

# -*- 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)