def get_online_data():
try:
response_data = {}
conn = mysql.connector.connect(**variables.connect_to_db_omilia_cfg)
cursor = conn.cursor()
except Exception:
return {'status': variables.connection_error_to_db_mysql}
# Основные данные
try:
query = ("SELECT END_TYPE, count(1) \
FROM diamant.DIALOGS \
WHERE APP_NAME in (" + APP_NAME + ") \
AND STEPS_NUM !='0' \
AND DIALOG_DATE >= CURDATE() \
AND END_TYPE IS NOT NULL \
GROUP BY END_TYPE \
UNION ALL \
SELECT 'DURATION', ROUND(AVG(DURATION)) \
FROM diamant.DIALOGS \
WHERE APP_NAME in (" + APP_NAME + ") \
AND STEPS_NUM !='0' \
AND DIALOG_DATE >= CURDATE() \
AND DURATION > 5000")
total = 0
context = {}
cursor.execute(query)
for p_type, p_cnt in cursor:
if p_type == 'DURATION':
context['AVG_DURATION'] = int(p_cnt)
else:
context[p_type] = int(p_cnt)
total = total + int(p_cnt)
for p_type in ['FAR_HUP', 'NEAR_HUP', 'TRANSFER']:
context[p_type + '_PRC'] = get_prc_1(context[p_type], total)
context['TOTAL_SUM'] = total
context['SERVICE_LEVEL'] = get_prc(
(int(context['FAR_HUP']) + int(context['NEAR_HUP'])), total)
response_data['main'] = context
except Exception:
return {'status': traceback.format_exc()}
# Переводы на операторов
try:
query = ("SELECT EXIT_POINT, COUNT(DIALOG_ID) \
FROM diamant.DIALOGS \
WHERE APP_NAME Like '%Halyk%' \
AND STEPS_NUM !='0' \
AND DIALOG_DATE >= CURDATE() \
AND END_TYPE = 'TRANSFER' \
GROUP BY EXIT_POINT")
total = 0
nList = []
cursor.execute(query)
for p_exit_point, p_cnt in cursor:
nList.append({'vdn': str(p_exit_point), 'cnt': int(p_cnt)})
total += int(p_cnt)
for index, value in enumerate(nList):
nList[index]['prc'] = get_prc(value['cnt'], total)
response_data['transfers'] = nList
except Exception:
return {'status': traceback.format_exc()}
# Темы обращения
try:
query = ("SELECT ACTIVE_INTENT, COUNT(DISTINCT(DIALOG_ID)) \
FROM diamant.DIALOG_STEP_EVENTS \
WHERE APP_NAME in (" + APP_NAME + ") \
AND ACTIVE_INTENT NOT IN ('EndDialog','Transfer','Transfer-Card-Num-Incorrect','undefined') \
AND DIALOG_DATE >= CURDATE() \
GROUP BY ACTIVE_INTENT")
total = 0
nList = []
cursor.execute(query)
for p_intent, p_cnt in cursor:
nList.append({'theme': str(p_intent), 'cnt': int(p_cnt)})
total += int(p_cnt)
for index, value in enumerate(nList):
nList[index]['prc'] = get_prc_1(value['cnt'], total)
# сортировка
newlist = sorted(nList, key=lambda k: k['cnt'], reverse=True)
response_data['themes'] = newlist
except Exception:
return {'status': traceback.format_exc()}
conn.close()
cursor.close()
response_data['status'] = variables.successful
return response_data
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,
}
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()}
def get_GraphicData():
timeLst = []
hoursKeys = []
hoursLabel = []
sysdate = datetime.now()
response_data = {'data': {}}
# Если 12 ночи, то выдаем данные за предыдущий день
if int(sysdate.strftime("%H")) == 0:
lastDay = True
date = sysdate - timedelta(days=1)
dateStr = date.strftime("%Y-%m-%d")
# иначе, за сегодня
else:
lastDay = False
dateStr = sysdate.strftime("%Y-%m-%d")
starttime = datetime.strptime(dateStr + ' 01:00:00', '%Y-%m-%d %H:%M:%S')
timeLst.append(starttime)
for value in range(0, 23):
nextime = starttime + timedelta(hours=1)
if lastDay:
if starttime.strftime("%H%M") == '2300':
nextime = datetime.strptime(dateStr + ' 23:59:00',
'%Y-%m-%d %H:%M:%S')
else:
if int(nextime.strftime("%H")) > int(sysdate.strftime("%H")):
break
if starttime.strftime("%H%M") == '2300':
break
timeLst.append(nextime)
starttime = nextime
try:
pyodbc.pooling = False
conn = pyodbc.connect(r'%s' % (variables.connect_to_db_cms_cfg))
except Exception:
return {'status': variables.connection_error_to_db_cms}
try:
cursor = conn.cursor()
for date in timeLst:
hoursKeys.append('hour_' + str(date.strftime("%H")))
if str(date.strftime("%H%M")) == '2359':
hourLabel = '00:00'
else:
hourLabel = str(date.strftime("%H:%M"))
hoursLabel.append(hourLabel)
endTimeStr = str(int(date.strftime("%H%M")))
startTimeDate = date - timedelta(hours=1)
startTimeStr = str(int(startTimeDate.strftime("%H%M")))
for sector in sectorsDict:
if 'archive' in sectorsDict[sector]:
if sectorsDict[sector]['archive'] == 'Y':
continue
splits = sectorsDict[sector]['splitsStr']
if sectorsDict[sector]['enableIndexGraphic'] == 'N':
continue
query = "select count(logid) \
from (select logid from hagent \
where row_date = to_date('" + dateStr + "', '%Y-%m-%d') \
and split in (" + splits + ") \
and starttime >= " + startTimeStr + " \
and starttime < " + endTimeStr + "\
and ringcalls > 0 \
group by logid)"
cursor.execute(query)
result = cursor.fetchone()
operatorsCount = int(result[0])
query = "select sum(CALLSOFFERED), \
sum(ACCEPTABLE), \
sum(SLVLABNS), \
sum(ACDCALLS), \
sum(ABNCALLS), \
sum(OUTFLOWCALLS) \
from hsplit \
where row_date = to_date('" + dateStr + "', '%Y-%m-%d') \
and split in (" + splits + ") \
and starttime >= " + startTimeStr + " \
and starttime < " + endTimeStr
cursor.execute(query)
for p_callsoffered, p_acceptable, p_slvlabns, p_acdcalls, p_abncalls, p_outflowcalls in cursor:
nPrc = get_prc((int(p_acceptable) + int(p_slvlabns)),
int(p_callsoffered))
if p_callsoffered == 0:
nPrc = 100
if sector not in response_data['data']:
response_data['data'][sector] = {}
if 'hours' not in response_data['data'][sector]:
response_data['data'][sector]['hours'] = {}
response_data['data'][sector]['hours'][
'hour_' + str(date.strftime("%H"))] = {
'time': str(date.strftime("%H:%M")),
'sl': nPrc,
'callsoffered': int(p_callsoffered),
'acceptable': int(p_acceptable),
'slvlabns': int(p_slvlabns),
'acdcalls': int(p_acdcalls),
'abncalls': int(p_abncalls),
'outflowcalls': int(p_outflowcalls),
'operators_count': operatorsCount
}
response_data['data'][sector]['longname'] = sectorsDict[
sector]['longname']
response_data['data'][sector][
'graphicLineColor'] = sectorsDict[sector][
'graphicLineColor']
cursor.close()
conn.close()
response_data['hours_keys'] = hoursKeys
response_data['hours'] = hoursLabel
response_data['status'] = variables.successful
return response_data
except Exception:
cursor.close()
conn.close()
return {'status': traceback.format_exc()}
def get_OtherParametrsData():
# Подключаемся к CMS
try:
conn = pyodbc.connect(r'%s' % (variables.connect_to_db_cms_cfg))
except Exception:
return {'status': variables.connection_error_to_db_cms}
try:
cursor = conn.cursor()
# Считаем общее количество поступивщих звонков в ВКЦ
query = "select 1, SUM(incalls) from hvdn \
where row_date = date(sysdate) \
and vdn in (" + variables.cc_incalls_str + ")"
cursor.execute(query)
for value in cursor:
incalls = int(nvl(value[1], 0))
# Считаем общее количество поступивщих звонков в ВКЦ по скиллам
query = "select 1, SUM(CALLSOFFERED) from hsplit \
where split in (" + all_depart_skills_str + ") and ROW_DATE = date(sysdate)"
cursor.execute(query)
for value in cursor:
callsoffered = int(nvl(value[1], 0))
# FCR. Счиатем общее количество повторяющихся звонков
query = "select 1, count(1) \
from (select calling_pty, count(1) \
from call_rec \
where row_date = date(sysdate) \
and len(calling_pty) > 7 \
and firstvdn != '79599' \
group by calling_pty \
having count(1) > 1)"
cursor.execute(query)
for value in cursor:
double_rings = int(value[1])
# Считаем общий ACR
query = "select lastdigits, count(1) \
from call_rec \
where row_date = date(sysdate) \
and dialed_num = '79599' \
and lastdigits in ('1','2','3','4','5') \
and len(calling_pty) > 7 \
group by lastdigits"
cursor.execute(query)
summa = 0
summa_count = 0
for osenka, count in cursor:
summa += int(osenka) * int(count)
summa_count += int(count)
# Количество оцененных вызовов
query = "select 1, count(1) \
from (select callid \
from call_rec \
where row_date = date(sysdate) \
and dialed_num = '79599' \
and lastdigits in ('1','2','3','4','5') \
and len(calling_pty) > 7 \
group by callid)"
cursor.execute(query)
for value in cursor:
rated_calls = int(value[1])
response_data = {
'data': {
'acr': {
'all': get_acr_value(summa, summa_count)
},
'fcr': get_prc(incalls - double_rings, incalls),
'incalls': incalls,
'rated_calls': rated_calls,
'rated_calls_prc': get_prc_2(rated_calls, callsoffered),
'callsoffered': callsoffered,
'double_rings': double_rings,
}
}
# Считаем ACR в разрезе скилл групп
query = "select sum(case when firstvdn = 79599 then 0 else dispsplit end), \
sum(case when firstvdn = 79599 then to_number(lastdigits) else 0 end) \
from call_rec a \
where row_date = date(sysdate) \
and len(calling_pty) > 7 \
and callid in (select callid from call_rec \
where row_date = date(sysdate) \
and dispsplit is not null \
and len(calling_pty) > 7 \
group by callid) \
and callid in (select callid from call_rec \
where row_date = date(sysdate) \
and dialed_num = '79599' \
and lastdigits in ('1','2','3','4','5') \
and len(calling_pty) > 7 \
group by callid) \
group by callid"
cursor.execute(query)
context = {}
for sector in sectorsDict:
if sectorsDict[sector]['archive'] == 'Y':
continue
if len(sectorsDict[sector]['splitsLst']) == 0:
continue
context[sector] = {
'sum': 0,
'count': 0,
'splitsLst': sectorsDict[sector]['splitsLst']
}
for value in cursor:
split = str(value[0])
lastdigits = int(value[1])
for sector in context:
if split in context[sector]['splitsLst']:
context[sector]['sum'] += lastdigits
context[sector]['count'] += 1
for sector in context:
response_data['data']['acr'][sector] = get_acr_value(
context[sector]['sum'], context[sector]['count'])
cursor.close()
conn.close()
except Exception:
cursor.close()
conn.close()
return {'status': traceback.format_exc()}
response_data['status'] = variables.successful
return response_data
def get_SplitsData():
sysdate = datetime.now()
context = {
'day': {
'total': {}
},
'month': {
'total': {}
},
'month_start_date': '01.' + sysdate.strftime('%m.%Y')
}
try:
sysdate = datetime.now()
dataWS = splitsData.getSplit_WS()
if dataWS['status'] != variables.successful:
return dataWS
else:
dataWS = dataWS['data']
dataCMS_Day = splitsData.getSplitCMS_Day_MemCache(None)
if dataCMS_Day['status'] != variables.successful:
return dataCMS_Day
else:
dataCMS_Day = dataCMS_Day['data']
if int(sysdate.strftime('%d')) != 1:
dataCMS_Month = splitsData.getSplitCMS_Month_MemCache(None)
if dataCMS_Month['status'] != variables.successful:
return dataCMS_Month
else:
dataCMS_Month = dataCMS_Month['data']
for sector in sectorsDict:
if sectorsDict[sector]['archive'] == 'Y':
continue
if sectorsDict[sector]['splitsStr'] == None:
continue
if sector not in context['day']:
context['day'][sector] = {}
if 'longname' not in context['day'][sector]:
context['day'][sector]['longname'] = sectorsDict[sector][
'longname']
if sector not in context['month']:
context['month'][sector] = {}
for splitItem in splitItemsDict:
if splitItem in dataWS[sector]:
valueWS = dataWS[sector][splitItem]
else:
valueWS = 0
if splitItem in dataCMS_Day[sector]:
valueDay = dataCMS_Day[sector][splitItem]
else:
valueDay = 0
context['day'][sector][splitItem] = valueWS + valueDay
if splitItem not in context['day']['total']:
context['day']['total'][splitItem] = 0
if splitItem == 'oldest':
if context['day'][sector][splitItem] > context['day'][
'total'][splitItem]:
context['day']['total'][splitItem] = context['day'][
sector][splitItem]
else:
context['day']['total'][splitItem] += context['day'][
sector][splitItem]
if splitItemsDict[splitItem]['calcMonth'] == 'Y':
if int(sysdate.strftime('%d')) == 1:
context['month'][sector][splitItem] = context['day'][
sector][splitItem]
context['month']['total'][splitItem] = context['day'][
'total'][splitItem]
else:
if splitItemsDict[splitItem]['calcMonth'] == 'Y':
if splitItem in dataCMS_Month[sector]:
valueMonth = dataCMS_Month[sector][splitItem]
else:
valueMonth = 0
context['month'][sector][splitItem] = context[
'day'][sector][splitItem] + valueMonth
if splitItem not in context['month']['total']:
context['month']['total'][splitItem] = 0
context['month']['total'][splitItem] += context[
'month'][sector][splitItem]
# Если включен Call Back Assists
if sectorsDict[sector]['enableCallBack'] == 'Y':
context['day'][sector]['cba'] = dataWS[sector]['cba']
for value in ['day', 'month']:
for sector in context[value]:
try:
sl = get_prc(
context[value][sector]['acceptable'] +
context[value][sector]['slvlabns'],
context[value][sector]['callsoffered'])
context[value][sector]['sl'] = sl
if value == 'day':
asa = get_asa_value(context[value][sector]['anstime'],
context[value][sector]['acdcalls'])
context[value][sector]['asa'] = asa
att = get_aat_value(context[value][sector]['acdtime'],
context[value][sector]['acdcalls'])
context[value][sector]['att'] = att
except Exception:
return {
'status': traceback.format_exc(),
'sector': sector,
'value': value
}
return {'data': context, 'status': variables.successful}
except Exception:
return {'status': traceback.format_exc()}
def get_online_data():
try:
pyodbc.pooling = False
conn = pyodbc.connect(r'%s' % (variables.connect_to_db_ivr_cfg))
except Exception:
return {'status': variables.connection_error_to_db_cmsreport}
try:
cursor = conn.cursor()
cursor.execute("select ISNULL(max(TODATE),cast(GetDate() as Date)), \
ISNULL(SUM(successful),0), \
ISNULL(SUM(transfered),0), \
ISNULL(SUM(breaked),0) \
from cmsreport.dbo.ivr_hour_history \
where cast(FROMDATE as Date) = cast(GETDATE() as Date)")
result = cursor.fetchone()
max_todate = result[0]
arc_successful = result[1]
arc_transfered = result[2]
arc_breaked = result[3]
query = "SELECT VarValue, COUNT(1) AS CNT \
FROM EPCDR.dbo.VPAppLog \
WHERE ActivityName = 'CallStatus' \
AND VarName = 'AppVariables:CallStatus' \
AND ApplicationID = 'test redirect' \
AND VarValue in ('0','1','2') \
AND LogTimestamp >= '" + str(max_todate) + "' \
AND LogTimestamp < DATEADD(hour, DATEDIFF(hour, 0, GETDATE() + '01:00:00'), 0) \
GROUP BY VarValue"
cursor.execute(query)
p_successful = 0
p_transfered = 0
p_breaked = 0
for VarValue, count in cursor:
if VarValue == '1':
p_successful = int(count)
elif VarValue == '2':
p_transfered = int(count)
elif VarValue == '0':
p_breaked = int(count)
all_successful = arc_successful + p_successful
all_transfered = arc_transfered + p_transfered
all_breaked = arc_breaked + p_breaked
p_all = all_successful + all_transfered + all_breaked
response_data = {
'status': variables.successful,
'result': {
'successful': all_successful,
'successful_prc': get_prc(all_successful, p_all),
'transfered': all_transfered,
'transfered_prc': get_prc(all_transfered, p_all),
'breaked': all_breaked,
'breaked_prc': get_prc(all_breaked, p_all),
'all': p_all
}
}
except Exception:
response_data = {'status': traceback.format_exc()}
cursor.close()
conn.close()
return response_data