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