def get_best5_operators(p_upravlenie):
response_data = {}
pyodbc.pooling = False
operators_list = get_operators_list()
operators = operators_list[p_upravlenie]
logid_list = []
for value in operators:
logid_list.append(value[1])
logid_str = list2str(logid_list)
try:
conn = pyodbc.connect(r'%s' % (variables.connect_to_db_cms_cfg))
except Exception:
response_data['status'] = variables.connection_error_to_db_cms
try:
cursor = conn.cursor()
query = "select limit 4 starttime from hagent a where a.row_date = date(sysdate) group by starttime order by starttime desc"
cursor.execute(query)
starttime_lst = []
for value in cursor:
starttime_lst.append(value[0])
starttime_str = list2str(starttime_lst)
query = "select limit 5 b.item_name, a.logid, a.attrib_id, sum(a.acdcalls) \
from hagent a, \
synonyms b\
where a.row_date = date(sysdate) \
and b.item_type = 'agname' \
and a.logid = b.value\
and a.logid in (" + logid_str + ") \
and a.starttime in (" + starttime_str + ") \
group by b.item_name, a.logid, a.attrib_id \
having sum(a.acdcalls) > 0 \
order by sum(a.acdcalls) desc"
cursor.execute(query)
nList = []
for name, logid, usrtab, cnt in cursor:
nList.append({
'name': name.strip(),
'usrtab': int(usrtab.strip()),
'cnt': int(cnt)
})
response_data = {'result': nList, 'status': variables.successful}
except Exception:
response_data['status'] = traceback.format_exc()
cursor.close()
conn.close()
return response_data
from dashboard.data import splitsGroup
from dashboard.data.global_functions import list2str
sectorsDict = {
'cards': {
'longname': 'Карточки',
'enableCallBack': 'Y',
'enableIndexGraphic': 'Y',
'enableInGraphicPage': 'Y',
'splitsStr': list2str(splitsGroup.main_cards),
'splitsLst': splitsGroup.main_cards,
'splits_cbaStr': list2str(splitsGroup.cards_cba),
'splits_cbaLst': splitsGroup.cards_cba,
'graphicLineColor': '#FF4500',
'archive': 'N'
},
# 'cardsLine': {
# 'longname': 'Карточки (Line)',
# 'enableCallBack': 'N',
# 'enableIndexGraphic': 'N',
# 'splitsStr': list2str(splitsGroup.cardsLine),
# 'splitsLst': splitsGroup.cardsLine,
# 'graphicLineColor': '',
# 'archive': 'N'
# },
'retail': {
'longname': 'Розница',
'enableCallBack': 'Y',
'enableIndexGraphic': 'Y',
'enableInGraphicPage': 'Y',
'splitsStr': list2str(splitsGroup.retail),
]
for skills in [vip_pos, pos, minipos, hm]:
for index, skills_id in enumerate(skills):
if index != 0:
pos_modified.append(skills_id)
uprk = [vip, premium, amex, pos_modified, cards]
# ---------------------- РУБС ----------------------
retail = ['retail', '20', '21', '22', '111'] # 111 - Retail_Line_2
retail_cba = ['retail_cba', '29']
rubs = [retail]
# ---------------------- ИКЦ ----------------------
sales = ['sales', '33', '34', '35']
utp = [sales]
sales_str = list2str(sales[1:])
# ----------------- Soft -------------------
soft = ['soft', '42', '43']
# ------------------------------------------
posobie = ['posobie', '114', '115']
# ------------------------------------------
otdelenyaATM = ['otdelenyaATM', '116', '117']
# ------------------------------------------
onboarding = ['onboarding', '118', '119']
# ------------------------------------------
tranOutsourcing = ['tranOutsourcing', '201']
# ---------------------- Для основного дашборда ----------------------
main_cards = [
'cards',
]
for skills in [
def report_incoming_split_calls(ndate):
context = {}
nSplits_VDNs = [
# 0 элемент - наименование скилла
# 1 элемент - список VDN, входящие вызовы (прямые) из внешних номеров
# 2 элемент - список тестовых VDN, вызовы направленные напрямую в очередь
# 3 элемент - список VDN, регистрация в очереди
# 4 элемент - список VDN, трансферы из других скиллов в очередь
# 5 элемент - список VDN, трансферы из Omilia
# 6 элемент - список VDN, трансферы из IVR
# 7 элемент - список Splits, номера скилл групп
[
'premium', [79401, 79402, 79403, 79414], [], [79502, 79503, 79504],
[79551, 79552, 79553], [], [], [2, 3, 4]
],
[
'onlinebank', [79407, 79408, 79409, 79419, 79425, 79496, 79865],
[79874], [79500, 79501], [79559, 79560], [79850, 79851], [],
[26, 27, 28]
],
[
'pos', [79410, 79411, 79412, 79422, 79426], [], [79505, 79506],
[79562, 79563], [79852, 79853], [], [17, 18, 19]
],
[
'amex', [79415, 79423], [79497, 79498, 79499], [],
[79554, 79555, 79556], [], [], [5, 6, 7]
],
[
'vip_onlinebank', [79418], [79509, 79510], [], [79557, 79558], [],
[], [65, 66]
],
['vip_pos', [79421], [], [], [79561], [], [], [8, 64]],
['H&M', [79494], [], [], [], [], [], [40]],
['VIP', [79413], [], [], [79550], [], [], [36]],
['minipos', [], [], [], [79576, 79577, 79578], [], [], [37, 38, 39]],
[
'card', [79532], [79873, 79592], [], [79564, 79565, 79566],
[79515, 79584],
[
79427, 79428, 79429, 79430, 79431, 79432, 79433, 79434, 79546,
79547, 79548, 79549
], [11, 12, 13]
],
[
'homebank', [], [79871], [], [79579, 79580, 79581], [79516, 79585],
[
79482, 79483, 79484, 79485, 79486, 79487, 79488, 79489, 79490,
79491, 79492, 79493
], [14, 15, 16]
],
[
'retail', [79595, 79597], [79872], [],
[79570, 79571, 79572, 79475, 79476], [79517, 79586],
[
79448, 79449, 79450, 79451, 79452, 79453, 79454, 79455, 79456,
79457, 79458, 79459
], [20, 21, 22]
],
[
'payments', [], [], [], [79567, 79568, 79569], [79518, 79587],
[
79436, 79437, 79438, 79439, 79440, 79441, 79442, 79443, 79444,
79445, 79446, 79447
], [30, 31, 32]
],
# ['sales',[79511],[],[79463,79467,79471],[79573,79574,79575],[79519,79588],[79460,79461,79462,79464,79465,79466,79468,79469,79470],[33,34,35]]
]
nIVR_VDNs = [
# 0 элемент - список VDN, входящие вызовы
# 1 элемент - список VDN, балансер
# 2 элемент - VDN IVR Avaya
[79404, 79405, 79406, 79416, 79417, 79424, 79533, 79495, 79600, 79866],
[79598],
[79538]
]
# примечание
# "doper": Дата
# "incomingCalls": Общее количество входящих вызовов
# "incomingCalls_Outflowcalls": Общее количество переведенных вызовов на другой VDN на уровне первого VDN (только прямые вызовы)
# "abandonedCalls": Общее количество потерянных вызовов
# "incomingCalls_inQueue": Общее количество поставленных на очередь вызовов (только прямые вызовы)
# "tranFromOperator_inQueue": Общее количество вызовов переведенных с других скилл групп
# "tranFromOmilia_inQueue": Общее количество вызовов переведенных с Омилия
# "tranFromIVR_inQueue": Общее количество вызовов переведенных с IVR
# "service_level": Service Level
# "callsoffered": Общее количество вызовов на уровне очереди (SPLIT)
# "acdcalls": Общее количество принятых вызовов на уровне очереди
# "abncalls": Общее количество потерянных вызово на уровне очереди
def getQuery_vdn(strvdn, indate):
return "select row_date, sum(incalls), sum(outflowcalls) \
from dvdn \
where row_date = to_date('" + ndate + "', '%Y-%m-%d') \
and vdn in (" + strvdn + ") \
group by row_date"
# Подключаемся к CMS
try:
conn = pyodbc.connect(r'%s' % (connect_to_db_cms_cfg))
except Exception:
return {'status': connection_error_to_db_cms}
try:
cursor = conn.cursor()
context['doper'] = ndate
# Собираем данные принятым/потерянным звонкам
for split in nSplits_VDNs:
name = split[0]
context[name] = {}
tmpContext = {
'incomingCalls': 0,
'incomingCalls_Outflowcalls': 0,
'abandonedCalls': 0,
'incomingCalls_inQueue': 0,
'incomingCalls_Outflowcalls_inQueue': 0,
'tranFromOperator_inQueue': 0,
'tranFromOperator_Outflowcalls': 0,
'tranFromOmilia_inQueue': 0,
'tranFromOmilia_Outflowcalls': 0,
'tranFromIVR_inQueue': 0,
'tranFromIVR_Outflowcalls': 0,
'all_Outflowcalls': 0,
'service_level': 0,
'callsoffered': 0,
'acdcalls': 0,
'prc_acdcalls': 0,
'abncalls': 0,
'prc_abncalls': 0,
'acd_do_40sec': 0,
'acd_do_60sec': 0,
'acd_do_2min': 0,
'acd_do_5min': 0,
'acd_do_10min': 0,
'acd_ot_10min': 0,
'abn_do_10sec': 4,
'abn_do_15sec': 0,
'abn_do_20sec': 0,
'abn_do_30sec': 0,
'abn_do_40sec': 0,
'abn_do_60sec': 0,
'abn_do_2min': 0,
'abn_do_5min': 0,
'abn_do_10min': 0,
'abn_ot_10min': 0,
}
# Сбор данных по элементу 1
# список VDN, входящие вызовы (прямые) из внешних номеров
if len(split[1]) > 0:
tmpStrVDNs = list2str(split[1])
query = getQuery_vdn(tmpStrVDNs, str(ndate))
cursor.execute(query)
for doper, incalls, outflowcalls in cursor:
# Если между VDN номера является VDN-ом очереди
if len(split[3]) == 0:
inQueue = int(incalls)
else:
inQueue = 0
tmpContext['incomingCalls'] = int(incalls)
tmpContext['incomingCalls_Outflowcalls'] = int(
outflowcalls)
tmpContext['incomingCalls_inQueue'] = inQueue
# Сбор данных по элементу 2
# вызовы направленные напрямую в очередь, для увеличения общего входа
if len(split[2]) > 0:
tmpStrVDNs = list2str(split[2])
query = getQuery_vdn(tmpStrVDNs, ndate)
cursor.execute(query)
for doper, incalls, outflowcalls in cursor:
tmpContext['incomingCalls'] += int(incalls)
# Сбор данных по элементу 3
# постановка в очередь
if len(split[3]) > 0:
tmpStrVDNs = list2str(split[3])
query = getQuery_vdn(tmpStrVDNs, ndate)
cursor.execute(query)
for doper, incalls, outflowcalls in cursor:
tmpContext['incomingCalls_inQueue'] = int(incalls)
tmpContext['incomingCalls_Outflowcalls_inQueue'] = int(
outflowcalls)
if name == 'sales':
tmpContext['incomingCalls_inQueue'] -= int(outflowcalls)
# Сбор данных по элементу 4
# трансферы из других скиллов в очередь
if len(split[4]) > 0:
tmpStrVDNs = list2str(split[4])
query = getQuery_vdn(tmpStrVDNs, ndate)
cursor.execute(query)
for doper, incalls, outflowcalls in cursor:
tmpContext['tranFromOperator_inQueue'] = int(incalls)
tmpContext['tranFromOperator_Outflowcalls'] = int(
outflowcalls)
if name == 'sales':
tmpContext['tranFromOperator_inQueue'] -= int(outflowcalls)
# Сбор данных по элементу 5
# трансферы из Omilia
if len(split[5]) > 0:
tmpStrVDNs = list2str(split[5])
query = getQuery_vdn(tmpStrVDNs, ndate)
cursor.execute(query)
for doper, incalls, outflowcalls in cursor:
tmpContext['tranFromOmilia_inQueue'] = int(incalls)
tmpContext['tranFromOmilia_Outflowcalls'] = int(
outflowcalls)
if name == 'sales':
tmpContext['tranFromOmilia_inQueue'] -= int(outflowcalls)
# Сбор данных по элементу 6
# трансферы из IVR
if len(split[6]) > 0:
tmpStrVDNs = list2str(split[6])
query = getQuery_vdn(tmpStrVDNs, ndate)
cursor.execute(query)
for doper, incalls, outflowcalls in cursor:
tmpContext['tranFromIVR_inQueue'] = int(incalls)
tmpContext['tranFromIVR_Outflowcalls'] = int(outflowcalls)
if name == 'sales':
tmpContext['tranFromIVR_inQueue'] -= int(outflowcalls)
# Только для УТП
if name == 'sales':
ivr_sales = tmpContext['incomingCalls_inQueue'] - tmpContext[
'incomingCalls_Outflowcalls']
tmpContext['tranFromIVR_inQueue'] += ivr_sales
tmpContext['incomingCalls_inQueue'] -= ivr_sales
# Сбор данных по элементу 7
tmpStrSplits = list2str(split[7])
query = "select sum(acceptable) as acceptable, \
sum(slvlabns) as slvlabns, \
sum(callsoffered) as callsoffered, \
sum(acdcalls) acdcalls, \
sum(abncalls) abncalls, \
(sum(acdcalls1) + sum(acdcalls2) + sum(acdcalls3) + sum(acdcalls4) + sum(acdcalls5)) acd_do_40sec, \
sum(acdcalls6) acd_do_60sec, \
sum(acdcalls7) acd_do_2min, \
sum(acdcalls8) acd_do_5min, \
sum(acdcalls9) acd_do_10min, \
sum(acdcalls10) acd_ot_10min, \
sum(abncalls1) abn_do_10sec, \
sum(abncalls2) abn_do_15sec, \
sum(abncalls3) abn_do_20sec, \
sum(abncalls4) abn_do_30sec, \
sum(abncalls5) abn_do_40sec, \
sum(abncalls6) abn_do_60sec, \
sum(abncalls7) abn_do_2min, \
sum(abncalls8) abn_do_5min, \
sum(abncalls9) abn_do_10min, \
sum(abncalls10) abn_ot_10min \
from dsplit \
where split in (" + tmpStrSplits + ") \
and row_date = to_date('" + ndate + "', '%Y-%m-%d')"
cursor.execute(query)
for acceptable, slvlabns, callsoffered, acdcalls, abncalls, \
acd_do_40sec, acd_do_60sec, acd_do_2min, acd_do_5min, acd_do_10min, acd_ot_10min, \
abn_do_10sec, abn_do_15sec, abn_do_20sec, abn_do_30sec, \
abn_do_40sec, abn_do_60sec, abn_do_2min, abn_do_5min, abn_do_10min, abn_ot_10min in cursor:
# Считаем потерянные вызовы
sumInQueue = tmpContext['incomingCalls_inQueue'] + \
tmpContext['tranFromOperator_inQueue'] + \
tmpContext['tranFromOmilia_inQueue'] + \
tmpContext['tranFromIVR_inQueue']
if name == 'sales':
incomingCalls_abn = tmpContext[
'incomingCalls'] - tmpContext[
'incomingCalls_Outflowcalls']
all_outflow = tmpContext['incomingCalls_Outflowcalls_inQueue'] + \
tmpContext['tranFromOperator_Outflowcalls'] + \
tmpContext['tranFromOmilia_Outflowcalls'] + \
tmpContext['tranFromIVR_Outflowcalls']
tmpContext['all_Outflowcalls'] = all_outflow
tmpContext[
'abandonedCalls'] = sumInQueue - all_outflow - int(
callsoffered) + incomingCalls_abn
else:
incomingCalls_abn = tmpContext[
'incomingCalls'] - tmpContext['incomingCalls_inQueue']
tmpContext['abandonedCalls'] = (
sumInQueue - int(callsoffered)) + incomingCalls_abn
tmpContext['service_level'] = get_prc(
int(acceptable) + int(slvlabns), int(callsoffered))
tmpContext['callsoffered'] = int(callsoffered)
tmpContext['acdcalls'] = int(acdcalls)
tmpContext['prc_acdcalls'] = get_prc(int(acdcalls),
int(callsoffered))
tmpContext['abncalls'] = int(abncalls)
tmpContext['prc_abncalls'] = get_prc(int(abncalls),
int(callsoffered))
tmpContext['acceptable'] = int(acceptable)
tmpContext['slvlabns'] = int(slvlabns)
tmpContext['acd_do_40sec'] = int(acd_do_40sec)
tmpContext['acd_do_60sec'] = int(acd_do_60sec)
tmpContext['acd_do_2min'] = int(acd_do_2min)
tmpContext['acd_do_5min'] = int(acd_do_5min)
tmpContext['acd_do_10min'] = int(acd_do_10min)
tmpContext['acd_ot_10min'] = int(acd_ot_10min)
tmpContext['abn_do_10sec'] = int(abn_do_10sec)
tmpContext['abn_do_15sec'] = int(abn_do_15sec)
tmpContext['abn_do_20sec'] = int(abn_do_20sec)
tmpContext['abn_do_30sec'] = int(abn_do_30sec)
tmpContext['abn_do_40sec'] = int(abn_do_40sec)
tmpContext['abn_do_60sec'] = int(abn_do_60sec)
tmpContext['abn_do_2min'] = int(abn_do_2min)
tmpContext['abn_do_5min'] = int(abn_do_5min)
tmpContext['abn_do_10min'] = int(abn_do_10min)
tmpContext['abn_ot_10min'] = int(abn_ot_10min)
context[name] = tmpContext
# Сбор данных по VDN IVR-ов
ivrContext = {
'ivrVDN_IncomingCalls': 0,
'ivrVDN_IncomingCalls_balancer': 0,
'ivrVDN_AbandonedCalls': 0,
'ivrVDN_Omilia': 0,
'ivrVDN_Avaya': 0,
'ivrAvaya_totalCalls': 0,
'ivrAvaya_successfulCalls': 0,
'ivrAvaya_breakedCalls': 0,
'ivrAvaya_transferedCalls': 0,
'ivrOmilia_totalCalls': 0,
'ivrOmilia_farHupCalls': 0,
'ivrOmilia_NearHupCalls': 0,
'ivrOmilia_TeardownCalls': 0,
'ivrOmilia_TransfersCalls': 0
}
# 0 элемент - список VDN, входящие вызовы
tmpStrVDNs = list2str(nIVR_VDNs[0])
query = getQuery_vdn(tmpStrVDNs, str(ndate))
cursor.execute(query)
for doper, incalls, outflowcalls in cursor:
ivrContext['ivrVDN_IncomingCalls'] = int(incalls)
# 1 элемент - список VDN, балансер
tmpStrVDNs = list2str(nIVR_VDNs[1])
query = getQuery_vdn(tmpStrVDNs, str(ndate))
cursor.execute(query)
for doper, incalls, outflowcalls in cursor:
ivrContext['ivrVDN_IncomingCalls_balancer'] = int(incalls)
# 2 элемент - VDN IVR Avaya
tmpStrVDNs = list2str(nIVR_VDNs[2])
query = getQuery_vdn(tmpStrVDNs, str(ndate))
cursor.execute(query)
for doper, incalls, outflowcalls in cursor:
ivrContext['ivrVDN_Avaya'] = int(incalls)
# Расчет потерянных вызовов
ivrContext['ivrVDN_AbandonedCalls'] = ivrContext[
'ivrVDN_IncomingCalls'] - ivrContext[
'ivrVDN_IncomingCalls_balancer']
cursor.close()
conn.close()
except Exception:
cursor.close()
conn.close()
return {'status': traceback.format_exc()}
# Подключаемся к IVR
try:
pyodbc.pooling = False
conn = pyodbc.connect(r'%s' % (connect_to_db_ivr_cfg))
except Exception:
return {'status': connection_error_to_db_cmsreport}
try:
cursor = conn.cursor()
cursor.execute("select successful, breaked, transfered \
from cmsreport.dbo.ivr_day_history \
where calendar_day = '" + ndate + "'")
for ivr_successful, ivr_breaked, ivr_transfered in cursor:
ivrContext['ivrAvaya_successfulCalls'] = int(ivr_successful)
ivrContext['ivrAvaya_breakedCalls'] = int(ivr_breaked)
ivrContext['ivrAvaya_transferedCalls'] = int(ivr_transfered)
ivrContext['ivrAvaya_totalCalls'] = ivrContext['ivrAvaya_successfulCalls'] + \
ivrContext['ivrAvaya_breakedCalls'] + \
ivrContext['ivrAvaya_transferedCalls']
cursor.close()
conn.close()
except Exception:
cursor.close()
conn.close()
return {'status': traceback.format_exc()}
# Подключаемся к Omilia
try:
conn = mysql.connector.connect(**connect_to_db_omilia_cfg)
except Exception:
return {'status': connection_error_to_db_mysql}
try:
cursor = conn.cursor()
query = ("SELECT IFNULL(END_TYPE,'TEARDOWN'), count(1) as 'CNT' \
FROM diamant.DIALOGS \
WHERE APP_NAME in ('Halyk_Bank_Prod','Halyk_Pilot') \
AND STEPS_NUM !='0' \
AND DATE_FORMAT(DIALOG_DATE, '%Y-%m-%d') = DATE_FORMAT('" +
ndate + "', '%Y-%m-%d') \
GROUP BY IFNULL(END_TYPE,'TEARDOWN')")
cursor.execute(query)
for end_type, count in cursor:
if end_type == 'FAR_HUP':
ivrContext['ivrOmilia_farHupCalls'] = int(count)
elif end_type == 'NEAR_HUP':
ivrContext['ivrOmilia_NearHupCalls'] = int(count)
elif end_type == 'TEARDOWN':
ivrContext['ivrOmilia_TeardownCalls'] = int(count)
elif end_type == 'TRANSFER':
ivrContext['ivrOmilia_TransfersCalls'] = int(count)
ivrContext['ivrOmilia_totalCalls'] = ivrContext['ivrOmilia_farHupCalls'] + \
ivrContext['ivrOmilia_NearHupCalls'] + \
ivrContext['ivrOmilia_TeardownCalls'] + \
ivrContext['ivrOmilia_TransfersCalls']
cursor.close()
conn.close()
except Exception:
cursor.close()
conn.close()
return {'status': traceback.format_exc()}
# Расчет потерянных вызовов
ivrContext['ivrVDN_AbandonedCalls'] = ivrContext['ivrVDN_IncomingCalls'] - \
ivrContext['ivrAvaya_totalCalls'] - \
ivrContext['ivrOmilia_totalCalls']
context['ivr'] = ivrContext
# Собираем свод
splitsLst = []
for rec in nSplits_VDNs:
splitsLst.append(rec[0])
context['total'] = {
'incomingCalls': 0,
'abandonedCalls': 0,
'tranFromOperator_inQueue': 0,
'tranFromOmilia_inQueue': 0,
'tranFromIVR_inQueue': 0,
'all_Outflowcalls': 0,
'callsoffered': 0,
'acdcalls': 0,
'abncalls': 0,
'acceptable': 0,
'slvlabns': 0,
'acd_do_40sec': 0,
'acd_do_60sec': 0,
'acd_do_2min': 0,
'acd_do_5min': 0,
'acd_do_10min': 0,
'acd_ot_10min': 0,
'abn_do_10sec': 0,
'abn_do_15sec': 0,
'abn_do_20sec': 0,
'abn_do_30sec': 0,
'abn_do_40sec': 0,
'abn_do_60sec': 0,
'abn_do_2min': 0,
'abn_do_5min': 0,
'abn_do_10min': 0,
'abn_ot_10min': 0
}
for split in splitsLst:
for key in context['total']:
context['total'][key] += context[split][key]
context['total']['service_level'] = get_prc(
context['total']['acceptable'] + context['total']['slvlabns'],
context['total']['callsoffered'])
context['total']['prc_acdcalls'] = get_prc(
context['total']['acdcalls'], context['total']['callsoffered'])
context['total']['prc_abncalls'] = get_prc(
context['total']['abncalls'], context['total']['callsoffered'])
context['total']['incomingCalls'] += context['ivr']['ivrVDN_IncomingCalls']
context['total']['ivrAvaya_processedCalls'] = context['ivr']['ivrAvaya_successfulCalls'] + \
context['ivr']['ivrAvaya_breakedCalls']
context['total']['ivrOmilia_processedCalls'] = context['ivr']['ivrOmilia_farHupCalls'] + \
context['ivr']['ivrOmilia_NearHupCalls'] + \
context['ivr']['ivrOmilia_TeardownCalls']
return {'status': successful, 'result': context}
def get_MainReportData():
p_days = '7'
nList = []
# Подключаемся к CMS
try:
conn = pyodbc.connect(r'%s' % (connect_to_db_cms_cfg))
except Exception:
return {'status': connection_error_to_db_cms}
try:
cursor = conn.cursor()
allSectorsSplitsLst = []
for sector in sectorsDict:
for split in sectorsDict[sector]['splitsLst']:
allSectorsSplitsLst.append(split)
if sectorsDict[sector]['enableCallBack'] == 'Y':
for split in sectorsDict[sector]['splits_cbaLst']:
allSectorsSplitsLst.append(split)
allSectorsSplitsLstStr = list2str(allSectorsSplitsLst)
# Собираем данные принятым/потерянным звонкам
query = "select row_date, \
sum(acceptable) as acceptable, \
sum(slvlabns) as slvlabns, \
sum(callsoffered) as callsoffered, \
sum(acdcalls) acdcalls, \
sum(abncalls) abncalls, \
(sum(acdcalls1) + sum(acdcalls2) + sum(acdcalls3) + sum(acdcalls4) + sum(acdcalls5)) acd_do_40sec, \
sum(acdcalls6) acd_do_60sec, \
sum(acdcalls7) acd_do_2min, \
sum(acdcalls8) acd_do_5min, \
sum(acdcalls9) acd_do_10min, \
sum(acdcalls10) acd_ot_10min, \
sum(abncalls1) abn_do_10sec, \
sum(abncalls2) abn_do_15sec, \
sum(abncalls3) abn_do_20sec, \
sum(abncalls4) abn_do_30sec, \
sum(abncalls5) abn_do_40sec, \
sum(abncalls6) abn_do_60sec, \
sum(abncalls7) abn_do_2min, \
sum(abncalls8) abn_do_5min, \
sum(abncalls9) abn_do_10min, \
sum(abncalls10) abn_ot_10min \
from dsplit \
where split in (" + allSectorsSplitsLstStr + ") \
and row_date < date(sysdate) \
and row_date >= date(sysdate) - " + p_days + " \
group by row_date \
order by row_date desc"
cursor.execute(query)
for doper, acceptable, slvlabns, callsoffered, acdcalls, abncalls, \
acd_do_40sec, acd_do_60sec, acd_do_2min, acd_do_5min, acd_do_10min, acd_ot_10min, \
abn_do_10sec, abn_do_15sec, abn_do_20sec, abn_do_30sec, \
abn_do_40sec, abn_do_60sec, abn_do_2min, abn_do_5min, abn_do_10min, abn_ot_10min in cursor:
nList.append({
'doper':
str(doper),
'service_level':
get_prc(int(acceptable) + int(slvlabns), int(callsoffered)),
'callsoffered':
int(callsoffered),
'acdcalls':
int(acdcalls),
'abncalls':
int(abncalls),
'acd_do_40sec':
int(acd_do_40sec),
'acd_do_60sec':
int(acd_do_60sec),
'acd_do_2min':
int(acd_do_2min),
'acd_do_5min':
int(acd_do_5min),
'acd_do_10min':
int(acd_do_10min),
'acd_ot_10min':
int(acd_ot_10min),
'abn_do_10sec':
int(abn_do_10sec),
'abn_do_15sec':
int(abn_do_15sec),
'abn_do_20sec':
int(abn_do_20sec),
'abn_do_30sec':
int(abn_do_30sec),
'abn_do_40sec':
int(abn_do_40sec),
'abn_do_60sec':
int(abn_do_60sec),
'abn_do_2min':
int(abn_do_2min),
'abn_do_5min':
int(abn_do_5min),
'abn_do_10min':
int(abn_do_10min),
'abn_ot_10min':
int(abn_ot_10min),
'ivr_successful':
0,
'ivr_breaked':
0
})
allIncomingCallsVdn = []
for number in incomingCallsVdnDict:
for vdn in incomingCallsVdnDict[number]['vdnLst']:
allIncomingCallsVdn.append(vdn)
allIncomingCallsVdnStr = list2str(allIncomingCallsVdn)
# Собираем данные по объему входящих звонков
query = "select row_date, SUM(incalls)\
from dvdn \
where row_date >= date(sysdate) - " + p_days + " \
and row_date < date(sysdate) \
and vdn in (" + allIncomingCallsVdnStr + ") \
group by row_date \
order by row_date"
cursor.execute(query)
for doper, incalls in cursor:
for index, values in enumerate(nList):
if str(doper) == str(values['doper']):
nList[index]['vdn_incalls'] = int(incalls)
break
# Собираем данные по пропущенным звонкам
# из cm прямое подключение к операторам = 79401,79402,79403,79407,79408,79409,79410,79411,79412,79413,79414,79415,79418,79419,79421,79422,79423,79425,79426,79865, \
# из cm в aep = 79404,79405,79406,79416,79417,79424,79533,79495,79600,79496,79866
# из aep в omilia = 79598
# из ivr в omilia = 79854
query = "select row_date, SUM(abncalls) abncalls \
from dvdn \
where row_date >= date(sysdate) - " + p_days + " \
and row_date < date(sysdate) \
and vdn in ( \
79401,79402,79403,79407,79408,79409,79410,79411,79412,79413,79414,79415,79418,79419,79421,79422,79423,79425,79426,79865, \
79404,79405,79406,79416,79417,79424,79533,79495,79600,79496,79866, \
79598,79854) \
group by row_date \
order by row_date"
cursor.execute(query)
for doper, abncalls in cursor:
for index, values in enumerate(nList):
if str(doper) == str(values['doper']):
nList[index]['vdn_abncalls'] = int(abncalls)
cursor.close()
conn.close()
except Exception:
cursor.close()
conn.close()
return {'status': traceback.format_exc()}
# Подключаемся к IVR
try:
pyodbc.pooling = False
conn = pyodbc.connect(r'%s' % (connect_to_db_ivr_cfg))
except Exception:
return {'status': connection_error_to_db_cmsreport}
try:
cursor = conn.cursor()
cursor.execute("select top " + p_days + " \
calendar_day, \
successful, \
breaked \
from cmsreport.dbo.ivr_day_history \
order by calendar_day DESC")
for doper, ivr_successful, ivr_breaked in cursor:
for index, values in enumerate(nList):
if str(doper) == str(values['doper']):
nList[index]['ivr_successful'] = int(ivr_successful)
nList[index]['ivr_breaked'] = int(ivr_breaked)
break
cursor.close()
conn.close()
except Exception:
cursor.close()
conn.close()
return {'status': traceback.format_exc()}
# Подключаемся к Omilia
try:
conn = mysql.connector.connect(**connect_to_db_omilia_cfg)
except Exception:
return {'status': connection_error_to_db_mysql}
try:
cursor = conn.cursor()
query = (
"SELECT DATE_FORMAT(DIALOG_DATE, '%Y-%m-%d') as 'DATE', END_TYPE, count(1) as 'CNT' \
FROM diamant.DIALOGS \
WHERE APP_NAME in ('Halyk_Bank_Prod','Halyk_Pilot') \
AND STEPS_NUM !='0' \
AND DIALOG_DATE >= DATE_SUB(CURDATE(), INTERVAL " + p_days +
" DAY) \
AND DIALOG_DATE < CURDATE() \
AND END_TYPE in ('FAR_HUP','NEAR_HUP') \
GROUP BY DATE_FORMAT(DIALOG_DATE, '%Y-%m-%d'), END_TYPE \
ORDER BY DATE_FORMAT(DIALOG_DATE, '%Y-%m-%d') DESC")
cursor.execute(query)
for doper, end_type, count in cursor:
for index, values in enumerate(nList):
if str(doper) == str(values['doper']):
nList[index]['omilia_' + end_type.swapcase()] = int(count)
cursor.close()
conn.close()
except Exception:
cursor.close()
conn.close()
return {'status': traceback.format_exc()}
# Расчет процентов
try:
for index, values in enumerate(nList):
for tag in [
'omilia_far_hup', 'omilia_near_hup', 'ivr_successful',
'callsoffered', 'acdcalls', 'abncalls', 'vdn_incalls'
]:
if tag not in values:
nList[index][tag] = 0
nList[index]['omilia_prc'] = get_prc_1(
values['omilia_far_hup'] + values['omilia_near_hup'],
values['vdn_incalls'])
nList[index]['ivr_successful_prc'] = get_prc_1(
values['ivr_successful'], values['vdn_incalls'])
nList[index]['callsoffered_prc'] = get_prc_1(
values['callsoffered'], values['vdn_incalls'])
nList[index]['acdcalls_prc'] = get_prc_1(values['acdcalls'],
values['callsoffered'])
nList[index]['abncalls_prc'] = get_prc_1(values['abncalls'],
values['callsoffered'])
for tag in [
'acd_do_40sec', 'acd_do_60sec', 'acd_do_2min',
'acd_do_5min', 'acd_do_10min', 'acd_ot_10min'
]:
nList[index][tag + '_prc'] = get_prc_1(values[tag],
values['acdcalls'])
for tag in [
'abn_do_10sec', 'abn_do_15sec', 'abn_do_20sec',
'abn_do_30sec', 'abn_do_40sec', 'abn_do_60sec',
'abn_do_2min', 'abn_do_5min', 'abn_do_10min',
'abn_ot_10min'
]:
nList[index][tag + '_prc'] = get_prc_1(values[tag],
values['abncalls'])
except Exception:
return {'status': traceback.format_exc()}
return {
'status': successful,
'allSectorsSplitsLstStr': allSectorsSplitsLstStr,
'allIncomingCallsVdnStr': allIncomingCallsVdnStr,
'result': nList,
}
}
connect_to_db_cms_cfg = 'DSN=cms;UID=cms;PWD=Avaya_Kh@lyk_cms;ANSI=True'
#connect_to_db_cms_cfg = 'DSN=cms;UID=DidarK;PWD=cmsuser'
p_url = 'http://172.27.48.98:8080/scgi-bin/main'
p_url_agents = 'http://172.27.48.99:8080/scgi-bin/agentrange?range='
auth_values = ('admin', 'admin')
# ------------------- Карта звонков - VDN ------------------
# ----- Звонки через вектор, попадают напрямую к операторам -----
tran_cm_vdns = [
79401, 79402, 79403, 79407, 79408, 79409, 79410, 79411, 79412, 79414,
79415, 79418, 79419, 79422, 79423, 79425, 79426, 79413, 79421, 79865
]
tran_cm_vdns_str = list2str(tran_cm_vdns)
tran_vdns_operators = [
79502, 79503, 79504, 79500, 79501, 79505, 79506, 79502, 79503, 79504,
79497, 79498, 79499, 79509, 79510, 79494
]
tran_vdns_operators_str = list2str(tran_vdns_operators)
# ----- Звонки через IVR или Omilia -----
tran_cm_aep = [
79404, 79405, 79406, 79416, 79417, 79424, 79533, 79495, 79600, 79496, 79866
]
tran_cm_aep_str = list2str(tran_cm_aep)
tran_aep_omilia = [79598]
tran_aep_omilia_str = list2str(tran_aep_omilia)
def get_service_level():
nListOfTime = []
response_data = {}
pyodbc.pooling = False
today = datetime.now()
month = int(today.strftime('%m'))
year = int(today.strftime('%Y'))
daysRange = range(1, 32)
daysLst = []
for value in daysRange:
daysLst.append(value)
def getMonthName(value):
if value == '01':
return 'Январь'
elif value == '02':
return 'Февраль'
elif value == '03':
return 'Март'
elif value == '04':
return 'Апрель'
elif value == '05':
return 'Май'
elif value == '06':
return 'Июнь'
elif value == '07':
return 'Июль'
elif value == '08':
return 'Август'
elif value == '09':
return 'Сентябрь'
elif value == '10':
return 'Октябрь'
elif value == '11':
return 'Ноябрь'
elif value == '12':
return 'Декабрь'
try:
conn = pyodbc.connect(r'%s' % (variables.connect_to_db_cms_cfg))
except Exception:
return {'status': variables.connection_error_to_db_cms}
try:
context = {}
graphicSectorsDict = {}
allSectorsData = {'splitsLst': []}
cursor = conn.cursor()
for sector in sectorsDict:
for split in sectorsDict[sector]['splitsLst']:
allSectorsData['splitsLst'].append(split)
if sectorsDict[sector]['enableCallBack'] == 'Y':
for split in sectorsDict[sector]['splits_cbaLst']:
allSectorsData['splitsLst'].append(split)
if 'archive' in sectorsDict[sector]:
if sectorsDict[sector]['archive'] == 'Y':
continue
if 'enableInGraphicPage' in sectorsDict[sector]:
if sectorsDict[sector]['enableInGraphicPage'] == 'Y':
graphicSectorsDict[sector] = sectorsDict[sector]
graphicSectorsDict['all'] = {
'splitsLst': allSectorsData['splitsLst'],
'splitsStr': list2str(allSectorsData['splitsLst']),
}
for sector in graphicSectorsDict:
splitsStr = graphicSectorsDict[sector]['splitsStr']
query = "select row_date, \
SUM(CALLSOFFERED) CALLSOFFERED, \
SUM(ACCEPTABLE) ACCEPTABLE, \
SUM(SLVLABNS) SLVLABNS \
from dsplit \
where split in (" + splitsStr + ") \
and to_date(to_char(row_date, '%Y-%m'), '%Y-%m') >= to_date('" + str(
year) + "-01', '%Y-%m') \
group by row_date \
order by row_date"
cursor.execute(query)
for p_row_date, p_callsoffered, p_acceptable, p_slvlabns in cursor:
nList = []
m_doper = p_row_date.strftime('%m')
m_key = 'month_' + m_doper
d_doper = p_row_date.strftime('%d')
d_key = 'day' + str(int(d_doper))
if sector not in context:
context[sector] = {}
if m_key not in context[sector]:
context[sector][m_key] = {'name': getMonthName(m_doper)}
if d_key not in context[sector][m_key]:
context[sector][m_key][d_key] = {}
nPrc = get_prc((int(p_acceptable) + int(p_slvlabns)),
int(p_callsoffered))
if p_callsoffered == 0:
nPrc = 100
dateStr = p_row_date.strftime('%d.%m.%Y')
context[sector][m_key][d_key] = {
'doper': dateStr,
'sl': nPrc,
'callsoffered': int(p_callsoffered),
'acceptable': int(p_acceptable),
'slvlabns': int(p_slvlabns)
}
cursor.close()
conn.close()
context['days_range'] = daysLst
context['status'] = variables.successful
return context
except Exception:
cursor.close()
conn.close()
return {'status': traceback.format_exc()}