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
