def make_pyexcel_table(headers, row_names, default_row, with_summary=False): report_construct = Sheet(colnames=headers) row_names = list(row_names) if with_summary: row_names += ['Summary'] for row_name in row_names: additional_row = OrderedDict() # Set default values for each row additional_row[str(row_name)] = default_row report_construct.extend_rows(additional_row) return report_construct
def test_extend_rows_using_wrong_data_type(self): s = Sheet(self.data, "test") s.name_rows_by_column(0) s.extend_rows([1, 2])
def main(): output_headers = [ 'I/C Presented', 'I/C Answered', 'I/C Lost', 'Voice Mails', 'Incoming Answered (%)', 'Incoming Lost (%)', 'Average Incoming Duration', 'Average Wait Answered', 'Average Wait Lost', 'Calls Ans Within 15', 'Calls Ans Within 30', 'Calls Ans Within 45', 'Calls Ans Within 60', 'Calls Ans Within 999', 'Call Ans + 999', 'Longest Waiting Answered', 'PCA' ] settings = AppSettings(file_name=_settings) test_output = Sheet( colnames=output_headers ) for client_num in (*settings['Clients'], 'Summary'): additional_row = OrderedDict( [ (client_num, [0, 0, 0, 0, 0, 0, timedelta(0), timedelta(0), timedelta(0), 0, 0, 0, 0, 0, 0, timedelta(0), 0] ) ] ) test_output.extend_rows(additional_row) records = session_data(datetime.today().date().replace(year=2017, month=5, day=17)) # Filter Step try: for x in range(0, len(records)): match_record = records[x] matches = match(records[x+1:], match_val=match_record) if ( len(matches) > 1 and (match_record.end - match_record.start > timedelta(seconds=20)) and match_record.data['Event Summary'].get('10', timedelta(0)) == timedelta(0) ): # print('Matched value:', match_record) for a_match in matches: # if a_match == 1497228: # print(match_record.id, matches) for i, o in enumerate(records): if getattr(o, 'id') == a_match: del records[i] # print('Removed', a_match, 'at', i) break except IndexError: # x has moved past the end of the list of remaining records pass # Process Step for record in records: row_name = str(record.unique_id1) # This is how we bind our client settings if row_name in test_output.rownames and time(hour=7) <= record.start.time() <= time(hour=19): call_duration = record.end - record.start talking_time = record.data['Event Summary'].get('4', timedelta(0)) voicemail_time = record.data['Event Summary'].get('10', timedelta(0)) hold_time = sum( [record.data['Event Summary'].get(event_type, timedelta(0)) for event_type in ('5', '6', '7')], timedelta(0) ) wait_duration = call_duration - talking_time - hold_time # DO the rest of the output work if talking_time > timedelta(0): if record.unique_id1 == test_client: print('I am an answered call', record.id) test_output[row_name, 'I/C Presented'] += 1 test_output[row_name, 'I/C Answered'] += 1 test_output[row_name, 'Average Incoming Duration'] += talking_time test_output[row_name, 'Average Wait Answered'] += wait_duration # Adding to Summary test_output['Summary', 'I/C Presented'] += 1 test_output['Summary', 'I/C Answered'] += 1 test_output['Summary', 'Average Incoming Duration'] += talking_time test_output['Summary', 'Average Wait Answered'] += wait_duration # Qualify calls by duration if wait_duration <= timedelta(seconds=15): test_output[row_name, 'Calls Ans Within 15'] += 1 test_output['Summary', 'Calls Ans Within 15'] += 1 elif wait_duration <= timedelta(seconds=30): test_output[row_name, 'Calls Ans Within 30'] += 1 test_output['Summary', 'Calls Ans Within 30'] += 1 elif wait_duration <= timedelta(seconds=45): test_output[row_name, 'Calls Ans Within 45'] += 1 test_output['Summary', 'Calls Ans Within 45'] += 1 elif wait_duration <= timedelta(seconds=60): test_output[row_name, 'Calls Ans Within 60'] += 1 test_output['Summary', 'Calls Ans Within 60'] += 1 elif wait_duration <= timedelta(seconds=999): test_output[row_name, 'Calls Ans Within 999'] += 1 test_output['Summary', 'Calls Ans Within 999'] += 1 else: test_output[row_name, 'Call Ans + 999'] += 1 test_output['Summary', 'Call Ans + 999'] += 1 if wait_duration > test_output[row_name, 'Longest Waiting Answered']: test_output[row_name, 'Longest Waiting Answered'] = wait_duration if wait_duration > test_output['Summary', 'Longest Waiting Answered']: test_output['Summary', 'Longest Waiting Answered'] = wait_duration elif voicemail_time > timedelta(seconds=20): if record.unique_id1 == test_client: print('I am a voice mail call', record.id) test_output[row_name, 'I/C Presented'] += 1 test_output[row_name, 'Voice Mails'] += 1 test_output[row_name, 'Average Wait Lost'] += call_duration test_output['Summary', 'I/C Presented'] += 1 test_output['Summary', 'Voice Mails'] += 1 test_output['Summary', 'Average Wait Lost'] += call_duration elif call_duration > timedelta(seconds=20): if record.unique_id1 == test_client: print('I am a lost call', record.id) test_output[row_name, 'I/C Presented'] += 1 test_output[row_name, 'I/C Lost'] += 1 test_output[row_name, 'Average Wait Lost'] += call_duration test_output['Summary', 'I/C Presented'] += 1 test_output['Summary', 'I/C Lost'] += 1 test_output['Summary', 'Average Wait Lost'] += call_duration else: pass # Finalize step for row in test_output.rownames: try: test_output[row, 'Incoming Answered (%)'] = '{0:.1%}'.format( test_output[row, 'I/C Answered'] / test_output[row, 'I/C Presented'] ) except ZeroDivisionError: test_output[row, 'Incoming Answered (%)'] = 1.0 try: test_output[row, 'Incoming Lost (%)'] = '{0:.1%}'.format( (test_output[row, 'I/C Lost'] + test_output[row, 'I/C Lost']) / test_output[row, 'I/C Presented'] ) except ZeroDivisionError: test_output[row, 'Incoming Lost (%)'] = 0.0 try: test_output[row, 'Average Incoming Duration'] = str( chop_microseconds(test_output[row, 'Average Incoming Duration'] / test_output[row, 'I/C Answered']) ) except ZeroDivisionError: test_output[row, 'Average Incoming Duration'] = '0:00:00' try: test_output[row, 'Average Wait Answered'] = str( chop_microseconds(test_output[row, 'Average Wait Answered'] / test_output[row, 'I/C Answered']) ) except ZeroDivisionError: test_output[row, 'Average Wait Answered'] = '0:00:00' try: test_output[row, 'Average Wait Lost'] = str( chop_microseconds(test_output[row, 'Average Wait Lost'] / test_output[row, 'I/C Lost']) ) except ZeroDivisionError: test_output[row, 'Average Wait Lost'] = '0:00:00' test_output[row, 'Longest Waiting Answered'] = str( chop_microseconds(test_output[row, 'Longest Waiting Answered']) ) try: test_output[row, 'PCA'] = '{0:.1%}'.format( (test_output[row, 'Calls Ans Within 15'] + test_output[row, 'Calls Ans Within 30']) / test_output[row, 'I/C Presented'] ) except ZeroDivisionError: test_output[row, 'PCA'] = 0.0 print(test_output)
def sla_report(records, call_table, client_list=None): output_headers = [ 'I/C Presented', 'I/C Live Answered', 'I/C Abandoned', 'Voice Mails', 'Incoming Live Answered (%)', 'Incoming Received (%)', 'Incoming Abandoned (%)', 'Average Incoming Duration', 'Average Wait Answered', 'Average Wait Lost', 'Calls Ans Within 15', 'Calls Ans Within 30', 'Calls Ans Within 45', 'Calls Ans Within 60', 'Calls Ans Within 999', 'Call Ans + 999', 'Longest Waiting Answered', 'PCA' ] test_output = Sheet(colnames=output_headers) row_names = list(client_list) # row_names += ['Summary'] for row_name in row_names: additional_row = OrderedDict() # Set default values for each row additional_row[str(row_name)] = [ 0, # 'I/C Presented' 0, # 'I/C Live Answered' 0, # 'I/C Abandoned' 0, # 'Voice Mails' 0, # 'Incoming Live Answered (%)', 0, # 'Incoming Received (%)', 0, # 'Incoming Abandoned (%)' timedelta(0), # 'Average Incoming Duration' timedelta(0), # 'Average Wait Answered' timedelta(0), # 'Average Wait Lost' 0, # 'Calls Ans Within 15' 0, # 'Calls Ans Within 30' 0, # 'Calls Ans Within 45' 0, # 'Calls Ans Within 60' 0, # 'Calls Ans Within 999' 0, # 'Call Ans + 999' timedelta(0), # 'Longest Waiting Answered' 0 # 'PCA' ] test_output.extend_rows(additional_row) # Filter Step print('start filter', datetime.now(), flush=True) # Convert record ids into actual records /w cached events # records = [call_table.cache(call_table.query.get(record_id)) for record_id in records] try: for x in range(0, len(records)): match_record = records[x] matches = match(records[x + 1:], match_val=match_record) if (len(matches) > 1 and (match_record[0].end_time - match_record[0].start_time > timedelta(seconds=20)) and match_record[1].get(10, timedelta(0)) == timedelta(0)): for a_match in matches: for i, o in enumerate(records): if o[0].call_id == a_match: print('deleting', records[i]) del records[i] break except IndexError: # x has moved past the end of the list of remaining records pass # print(len(records), records[0]) print('stop filter', datetime.now(), flush=True) print('start process', datetime.now(), flush=True) # Process Step for call, events in records: # print(call) # print(events) row_name = str( call.dialed_party_number) # This is how we bind our client user # print(row_name) # print(time(hour=7) <= call.start_time.time() <= time(hour=19)) # print(row_name in test_output.rownames) if row_name in test_output.rownames and time( hour=7) <= call.start_time.time() <= time(hour=19): call_duration = call.end_time - call.start_time talking_time = events.get(4, timedelta(0)) voicemail_time = events.get(10, timedelta(0)) hold_time = sum([ events.get(event_type, timedelta(0)) for event_type in (5, 6, 7) ], timedelta(0)) wait_duration = call_duration - talking_time - hold_time # DO the rest of the output work if talking_time > timedelta(0): test_output[row_name, 'I/C Presented'] += 1 test_output[row_name, 'I/C Live Answered'] += 1 test_output[row_name, 'Average Incoming Duration'] += talking_time test_output[row_name, 'Average Wait Answered'] += wait_duration # Adding to Summary # test_output['Summary', 'I/C Presented'] += 1 # test_output['Summary', 'I/C Live Answered'] += 1 # test_output['Summary', 'Average Incoming Duration'] += talking_time # test_output['Summary', 'Average Wait Answered'] += wait_duration # Qualify calls by duration if wait_duration <= timedelta(seconds=15): test_output[row_name, 'Calls Ans Within 15'] += 1 # test_output['Summary', 'Calls Ans Within 15'] += 1 elif wait_duration <= timedelta(seconds=30): test_output[row_name, 'Calls Ans Within 30'] += 1 # test_output['Summary', 'Calls Ans Within 30'] += 1 elif wait_duration <= timedelta(seconds=45): test_output[row_name, 'Calls Ans Within 45'] += 1 # test_output['Summary', 'Calls Ans Within 45'] += 1 elif wait_duration <= timedelta(seconds=60): test_output[row_name, 'Calls Ans Within 60'] += 1 # test_output['Summary', 'Calls Ans Within 60'] += 1 elif wait_duration <= timedelta(seconds=999): test_output[row_name, 'Calls Ans Within 999'] += 1 # test_output['Summary', 'Calls Ans Within 999'] += 1 else: test_output[row_name, 'Call Ans + 999'] += 1 # test_output['Summary', 'Call Ans + 999'] += 1 if wait_duration > test_output[row_name, 'Longest Waiting Answered']: test_output[row_name, 'Longest Waiting Answered'] = wait_duration # if wait_duration > test_output['Summary', 'Longest Waiting Answered']: # test_output['Summary', 'Longest Waiting Answered'] = wait_duration elif voicemail_time > timedelta(seconds=20): # if record.unique_id1 == test_client: # print('I am a voice mail call', record.id) test_output[row_name, 'I/C Presented'] += 1 test_output[row_name, 'Voice Mails'] += 1 test_output[row_name, 'Average Wait Lost'] += call_duration # test_output['Summary', 'I/C Presented'] += 1 # test_output['Summary', 'Voice Mails'] += 1 # test_output['Summary', 'Average Wait Lost'] += call_duration elif call_duration > timedelta(seconds=20): test_output[row_name, 'I/C Presented'] += 1 test_output[row_name, 'I/C Abandoned'] += 1 test_output[row_name, 'Average Wait Lost'] += call_duration # test_output['Summary', 'I/C Presented'] += 1 # test_output['Summary', 'I/C Abandoned'] += 1 # test_output['Summary', 'Average Wait Lost'] += call_duration else: # print('passed', call) pass print('stop process', datetime.now(), flush=True) print('start finalize', datetime.now(), flush=True) # Finalize step for row_name in test_output.rownames: row_name = str(row_name) try: test_output[row_name, 'Incoming Live Answered (%)'] = '{0:.1%}'.format( test_output[row_name, 'I/C Live Answered'] / test_output[row_name, 'I/C Presented']) except ZeroDivisionError: test_output[row_name, 'Incoming Live Answered (%)'] = '{0:.1%}'.format(1.0) try: test_output[row_name, 'Incoming Live Answered (%)'] = '{0:.1%}'.format( test_output[row_name, 'I/C Live Answered'] / test_output[row_name, 'I/C Presented']) except ZeroDivisionError: test_output[row_name, 'Incoming Live Answered (%)'] = '{0:.1%}'.format(1.0) try: test_output[row_name, 'Incoming Received (%)'] = '{0:.1%}'.format( (test_output[row_name, 'I/C Live Answered'] + test_output[row_name, 'Voice Mails']) / test_output[row_name, 'I/C Presented']) except ZeroDivisionError: test_output[row_name, 'Incoming Received (%)'] = '{0:.1%}'.format(1.0) try: test_output[row_name, 'Incoming Abandoned (%)'] = '{0:.1%}'.format( test_output[row_name, 'I/C Abandoned'] / test_output[row_name, 'I/C Presented']) except ZeroDivisionError: test_output[row_name, 'Incoming Abandoned (%)'] = '{0:.1%}'.format(1.0) try: test_output[row_name, 'Average Incoming Duration'] = str( chop_microseconds( test_output[row_name, 'Average Incoming Duration'] / test_output[row_name, 'I/C Live Answered'])) except ZeroDivisionError: test_output[row_name, 'Average Incoming Duration'] = '0:00:00' except TypeError: print(row_name, test_output[row_name, 'Average Incoming Duration'], test_output[row_name, 'I/C Live Answered']) raise # print(test_output[row_name, 'Average Wait Answered']) try: test_output[row_name, 'Average Wait Answered'] = str( chop_microseconds( test_output[row_name, 'Average Wait Answered'] / test_output[row_name, 'I/C Live Answered'])) except ZeroDivisionError: test_output[row_name, 'Average Wait Answered'] = '0:00:00' try: test_output[row_name, 'Average Wait Lost'] = str( chop_microseconds(test_output[row_name, 'Average Wait Lost'] / test_output[row_name, 'I/C Abandoned'])) except ZeroDivisionError: test_output[row_name, 'Average Wait Lost'] = '0:00:00' test_output[row_name, 'Longest Waiting Answered'] = str( chop_microseconds(test_output[row_name, 'Longest Waiting Answered'])) try: test_output[row_name, 'PCA'] = '{0:.1%}'.format( (test_output[row_name, 'Calls Ans Within 15'] + test_output[row_name, 'Calls Ans Within 30']) / test_output[row_name, 'I/C Presented']) except ZeroDivisionError: test_output[row_name, 'PCA'] = '{0:.1%}'.format(1.0) print('stop finalize', datetime.now(), flush=True) return test_output