# -*- coding : utf-8 -*- import re import os import time import tqdm import json import glob import base64 import pymysql import requests import operator import datetime import traceback import threadpool import numpy as np import matplotlib.pyplot as plt from pydub import AudioSegment from scipy.io import wavfile from openpyxl import Workbook, load_workbook wav_dir = './wav_dir' def get_huanbei_data_transfer(min_date, max_date): wb = Workbook() ws = wb.active ws.append(['result_id', 'customre_id', 'workspace_id', 'company_id', 'dm_session_id', 'call_start_time']) pool = threadpool.ThreadPool(20) connection_ab = pymysql.connect(# host="lingxi-sale.rwlb.zhangbei.rds.aliyuncs.com", host="39.103.215.119", port=3306, user="liuguoping", passwd="Moxi123#", db="outbound_sale_platform", charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor) cursor_ab = connection_ab.cursor() connection = pymysql.connect(host="39.103.215.119", port=3308, user="zhangjian", passwd="Lingxi@123", db="data_center_temp", charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor ) cursor = connection.cursor() sql = 'select case_id, result_id, customer_id, company_id, agent_id, full_name, ' \ 'dm_session_id, call_start_time, answer_time, transfer_time, call_end_time, ' \ 'total_call_duration, human_answer_duration, voice_path ' \ 'from outbound_call_result ' \ 'where call_start_time >= "{} 00:00:00" ' \ 'and call_start_time <= "{} 23:59:59" ' \ 'and company_id = 2165 ' \ 'and call_status in ("normalConnection", "transferFail") ' \ 'and transfer_time is not null ' \ 'and queue_id in (411, 418) ' \ 'and robot_answer_duration > 1; '.format(min_date, max_date) # 'and transfer_time is not null ' \ # 'and call_status in ("normalConnection", "transferFail") ' \ # 'and full_name != ""; ' sql_ab = 'select score_json, score ' \ 'from outbound_call_case ' \ 'where id = {}; ' cursor.execute(sql) results = cursor.fetchall() parameter = list() for result in tqdm.tqdm(results): case_id = result['case_id'] agent_id = result['agent_id'] result_id = result['result_id'] customer_id = result['customer_id'] company_id = result['company_id'] workspace_id = result['company_id'] dm_session_id = result['dm_session_id'] call_start_time = result['call_start_time'] call_answer_time = result['answer_time'] transfer_time = result['transfer_time'] call_end_time = result['call_end_time'] human_duration = result['human_answer_duration'] answer_duration = result['total_call_duration'] voice_path = result['voice_path'] full_name = result['full_name'] ip = "还呗" # print( case_id ) # cursor_ab.execute(sql_ab.format(case_id)) # res = cursor_ab.fetchall() # print( res ) # ws.append([json.loads(res[0]['score_json'])['score']]) # ws.append([case_id, customer_id, company_id, result_id, dm_session_id, workspace_id, 6, '通话成功', agent_id, human_duration]) '''url = 'http://8.142.18.63:9900/realTimeLabel?callId={}&workspaceIdUser={}&workspaceIdSeat={}'.format(dm_session_id, '301', '304') response = requests.get(url, timeout=500)''' # ds_task('通话成功', company_id, customer_id, result_id, dm_session_id, '222', 'loan_before', 6) '''URL = "https://sale.xi-ai.com/dataCenter/dsmodel/getCaseHistory" body_data = {'companyId': company_id, 'customerId': str(customer_id)} response = requests.request("POST", URL, json=body_data) results = json.loads(response.text) for res in results['data']: if res['breakStatus']: print(customer_id, res['breakStatus'], call_start_time) cursor_ab.execute(sql_ab.format(customer_id, company_id)) results = cursor_ab.fetchall() for res in results: if res['stage']: print( customer_id, res['stage'], )''' parameter.append(([ dm_session_id, result_id, customer_id, workspace_id, company_id, dm_session_id, call_start_time, call_answer_time, call_answer_time, transfer_time, call_end_time, answer_duration, human_duration, '', '', 6, 'loan_before', '通话成功', voice_path, ip, ], None)) # wb.save('./2022-05-05.xlsx') requests = threadpool.makeRequests(send_task, parameter) [pool.putRequest(req) for req in requests] pool.wait() def get_huanbei_data(min_date, max_date): wb = Workbook() ws = wb.active ws.append(['result_id', 'customre_id', 'workspace_id', 'company_id', 'dm_session_id', 'call_start_time']) pool = threadpool.ThreadPool(20) connection_ab = pymysql.connect(# host="lingxi-sale.rwlb.zhangbei.rds.aliyuncs.com", host="39.103.215.119", port=3306, user="liuguoping", passwd="Moxi123#", db="outbound_sale_platform", charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor) cursor_ab = connection_ab.cursor() connection = pymysql.connect(host="39.103.215.119", port=3308, user="zhangjian", passwd="Lingxi@123", db="data_center_temp", charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor ) cursor = connection.cursor() sql = 'select case_id, result_id, customer_id, company_id, agent_id, full_name, ' \ 'dm_session_id, call_start_time, answer_time, transfer_time, call_end_time, ' \ 'total_call_duration, human_answer_duration, voice_path ' \ 'from outbound_call_result ' \ 'where call_start_time >= "{} 00:00:00" ' \ 'and call_start_time <= "{} 23:59:59" ' \ 'and company_id = 2165 ' \ 'and call_status in ("normalConnection", "transferFail") ' \ 'and queue_id in (411, 418) ' \ 'and robot_answer_duration > 1; '.format(min_date, max_date) # 'and transfer_time is not null ' \ # 'and call_status in ("normalConnection", "transferFail") ' \ # 'and full_name != ""; ' sql_ab = 'select score_json, score ' \ 'from outbound_call_case ' \ 'where id = {}; ' cursor.execute(sql) results = cursor.fetchall() parameter = list() for result in tqdm.tqdm(results): case_id = result['case_id'] agent_id = result['agent_id'] result_id = result['result_id'] customer_id = result['customer_id'] company_id = result['company_id'] workspace_id = result['company_id'] dm_session_id = result['dm_session_id'] call_start_time = result['call_start_time'] call_answer_time = result['answer_time'] transfer_time = result['transfer_time'] call_end_time = result['call_end_time'] human_duration = result['human_answer_duration'] answer_duration = result['total_call_duration'] voice_path = result['voice_path'] full_name = result['full_name'] ip = "还呗" # print( case_id ) # cursor_ab.execute(sql_ab.format(case_id)) # res = cursor_ab.fetchall() # print( res ) # ws.append([json.loads(res[0]['score_json'])['score']]) # ws.append([case_id, customer_id, company_id, result_id, dm_session_id, workspace_id, 6, '通话成功', agent_id, human_duration]) '''url = 'http://8.142.18.63:9900/realTimeLabel?callId={}&workspaceIdUser={}&workspaceIdSeat={}'.format(dm_session_id, '301', '304') response = requests.get(url, timeout=500)''' ds_task('通话成功', company_id, customer_id, result_id, dm_session_id, '222', 'loan_before', 6) '''URL = "https://sale.xi-ai.com/dataCenter/dsmodel/getCaseHistory" body_data = {'companyId': company_id, 'customerId': str(customer_id)} response = requests.request("POST", URL, json=body_data) results = json.loads(response.text) for res in results['data']: if res['breakStatus']: print(customer_id, res['breakStatus'], call_start_time) cursor_ab.execute(sql_ab.format(customer_id, company_id)) results = cursor_ab.fetchall() for res in results: if res['stage']: print( customer_id, res['stage'], )''' '''parameter.append(([ dm_session_id, result_id, customer_id, workspace_id, company_id, dm_session_id, call_start_time, call_answer_time, call_answer_time, transfer_time, call_end_time, answer_duration, human_duration, '', '', 6, 'loan_before', '通话成功', voice_path, ip, ], None))''' wb.save('./2022-05-05.xlsx') '''requests = threadpool.makeRequests(send_task, parameter) [pool.putRequest(req) for req in requests] pool.wait()''' def send_task( call_id, result_id, customer_id, workspace_id, company_id, session_id, call_start_time, end_time, call_answer_time, transfer_time, call_end_time, answer_duration, human_duration, voice_type, asr_type, call_type, company_type, call_status, voice_path, ip,): url = 'http://localhost:9505/jingdong_feishizhuan?' \ 'callId={}&resultId={}&customerId={}&workspaceId={}&companyId={}&sessionId={}&' \ 'createTime={}&endTime={}&answerTime={}&transferTime={}&answerEndTime={}&talkTime={}&humanDuration={}&' \ 'asrType={}&callStatus={}&callType={}&companyType={}&wavPath={}&IP={}'.format( call_id, result_id, customer_id, workspace_id, company_id, session_id, call_start_time, end_time, call_answer_time, transfer_time, call_end_time, answer_duration, human_duration, asr_type, call_status, call_type, company_type, voice_path, ip ) response = requests.get(url) def ds_task(call_status, company_id, customer_id, result_id, session_id, workspace_id, company_type, call_type): url = 'http://121.89.246.101:8955/sale/distribute' data = { 'callStatus': call_status, 'companyId': company_id, 'customerId': customer_id, 'dmSessionId': session_id, 'resultId': result_id, 'workSpaceId': workspace_id, 'companyType': company_type, 'callType': int(call_type) } # print( 'DS data : {}'.format(data) ) response = requests.post(url, data=json.dumps(data)) # print( response.text ) if __name__ == '__main__': # get_data('2132') min_date = datetime.date.today() - datetime.timedelta(days=1) max_date = datetime.date.today() - datetime.timedelta(days=1) print( min_date, max_date ) get_huanbei_data_transfer(min_date, max_date) get_huanbei_data(min_date, max_date) # print( data_list ) '''wb = load_workbook('./2022-05-05.xlsx') ws = wb[wb.sheetnames[0]] list_score_test = list() for i, row in enumerate(ws.values): if i != 0: list_score_test.append(row[0]) # score_test = dat['result_id'] # list_score_test = list(score_test) len(list_score_test) plt.hist(list_score_test) threshold = 0.990001 list_score_h1 = [s for s in list_score_test if s >= threshold] print('天使比例为：', len(list_score_h1) / len(list_score_test)) threshold = 0.282847 list_score_h2 = [s for s in list_score_test if s >= threshold] print('中立比例为：', (len(list_score_h2) - len(list_score_h1)) / len(list_score_test)) threshold = 0 list_score_h3 = [s for s in list_score_test if s >= threshold] print('不认同比例为：', (len(list_score_h3) - len(list_score_h2)) / len(list_score_test)) threshold = -1 list_score_h4 = [s for s in list_score_test if s >= threshold] time.sleep(30)'''