def test_cut_region(self):
data = [
# 0 1 2 3 4 5 6
[1, 2, 3, 4, 5, 6, 7], # 0
[21, 22, 23, 24, 25, 26, 27],
[31, 32, 33, 34, 35, 36, 37],
[41, 42, 43, 44, 45, 46, 47],
[51, 52, 53, 54, 55, 56, 57] # 4
]
s = Sheet(data)
data = s.cut([1, 1], [4, 5])
expected = [
[22, 23, 24, 25],
[32, 33, 34, 35],
[42, 43, 44, 45]
]
expected2 = [
# 0 1 2 3 4 5 6
[1, 2, 3, 4, 5, 6, 7], # 0
[21, '', '', '', '', 26, 27],
[31, '', '', '', '', 36, 37],
[41, '', '', '', '', 46, 47],
[51, 52, 53, 54, 55, 56, 57] # 4
]
assert data == expected
assert s.to_array() == expected2
def test_column_names2(self):
data = [
[1, 2, 3],
[4, 5, 6],
[7, 8, 9]
]
sheet = Sheet(data)
sheet.colnames = ["Column", "Column", "Column"]
assert sheet.colnames == ["Column", "Column-1", "Column-2"]
def test_set_csv_attribute():
sheet = Sheet()
sheet.csv = "a,b,c"
expected = dedent("""
csv:
+---+---+---+
| a | b | c |
+---+---+---+""").strip('\n')
eq_(str(sheet), expected)
def test_row_names2(self):
data = [
[-1, -2, -3],
[1, 2, 3],
[4, 5, 6],
[7, 8, 9]
]
sheet = Sheet(data)
sheet.rownames = ["Row"] * 4
assert sheet.rownames == ["Row", "Row-1", "Row-2", "Row-3"]
def collate_wb_to_sheet(wb=()):
headers = ['row_names'] + wb[0].colnames
sheet_to_replace_wb = Sheet(colnames=headers)
unique_records = UniqueDict()
for sheet in wb:
for i, name in enumerate(sheet.rownames):
unique_records[name] = sheet.row_at(i)
for rec in sorted(unique_records.keys()):
sheet_to_replace_wb.row += [rec] + unique_records[rec]
sheet_to_replace_wb.name_rows_by_column(0)
return sheet_to_replace_wb
def test_cut_and_paste_region_within_limits_at_edge(self):
data = [
# 0 1 2 3 4 5 6
[1, 2, 3, 4, 5, 6, 7], # 0
[21, 22, 23, 24, 25, 26, 27],
[31, 32, 33, 34, 35, 36, 37],
[41, 42, 43, 44, 45, 46, 47],
[51, 52, 53, 54, 55, 56, 57] # 4
]
s = Sheet(data)
data = s.cut([1, 1], [4, 5])
s.paste([0, 0], rows=data)
expected = [
[22, 23, 24, 25, 5, 6, 7],
[32, 33, 34, 35, '', 26, 27],
[42, 43, 44, 45, '', 36, 37],
[41, '', '', '', '', 46, 47],
[51, 52, 53, 54, 55, 56, 57]
]
assert expected == s.to_array()
def test_delete_named_column(self):
s = Sheet(self.data, "test")
s.name_columns_by_row(0)
del s.column["Column 2"]
assert s.number_of_columns() == 2
s.column["Column 2"] # bang
def test_formatter_by_named_column(self):
"""Test one named column"""
s = Sheet(self.data, "test")
s.name_columns_by_row(0)
s.column.format("Column 1", str)
eq_(s.column["Column 1"], ["1", "4", "7"])
def test_add(self):
s = Sheet(self.data, "test")
s.name_columns_by_row(0)
data = OrderedDict({"Column 4": [10, 11, 12]})
s = s.column + data
assert s.column["Column 4"] == [10, 11, 12]
def make_summary(headers):
todays_summary = Sheet()
todays_summary.row += headers
todays_summary.name_columns_by_row(0)
return todays_summary
def test_negative_row_index(self):
s = Sheet(self.data, "test")
data = s.column[-1]
eq_(data, ["Column 3", 3, 6, 9])
def test_dot_notation(self):
s = Sheet(self.data, "test")
s.name_rows_by_column(0)
eq_(s.row.Row_3, [7, 8, 9])
def test_delete_named_row(self):
s = Sheet(self.data, "test")
s.name_rows_by_column(0)
del s.row["Row 2"]
assert s.number_of_rows() == 3
s.row["Row 2"] # already deleted
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 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 test_delete_column(self):
s = Sheet(self.data, "test")
del s.column[1, 2]
assert s.number_of_columns() == 1
s.column["Column 2"] # access it after deletion, bang
def test_non_filter(self):
data = []
s = Sheet(data)
s.filter("abc") # bang
def test_apply_row_formatter(self):
s = Sheet(self.data)
s.apply_formatter(RowFormatter(0, str))
assert s.row[0] == s.row[1]
def test_apply_column_formatter(self):
s = Sheet(self.data)
s.apply_formatter(ColumnFormatter(0, float))
assert s.column[0] == [1, 1, 1.1, 1.1, 2, 2]
def test_apply_sheet_formatter(self):
s = Sheet(self.data)
s.apply_formatter(SheetFormatter(float))
assert s.row[0] == s.row[1]
assert s.column[0] == [1, 1, 1.1, 1.1, 2, 2]
def test_series3(self):
custom_columns = ["C1", "C2", "C3"]
Sheet(self.data,
"test",
colnames=custom_columns,
name_columns_by_row=0)
def test_sheet_register_presentation():
Sheet.register_presentation('dummy')
s = Sheet([[1, 2]])
assert s.dummy == FIXTURE
def test_row_series_to_dict(self):
s = Sheet(self.data, "test")
s.name_rows_by_column(0)
content = s.to_dict(True)
keys = ["Row 0", "Row 1", "Row 2", "Row 3"]
assert keys == list(content.keys())
def test_get_csv_stream():
sheet = Sheet()
sheet.csv = "a,b,c"
stream = sheet.stream.csv
expected = "a,b,c\r\n"
eq_(stream.getvalue(), expected)
def test_iadd(self):
s = Sheet(self.data, "test")
s.name_rows_by_column(0)
data = OrderedDict({"Row 5": [10, 11, 12]})
s.row += data
assert s.row["Row 5"] == [10, 11, 12]
def test_delete_indexed_row2(self):
s = Sheet(self.data, "test")
s.name_rows_by_column(0)
s.delete_named_row_at(2)
assert s.number_of_rows() == 3
s.row["Row 2"] # already deleted
def test_add_wrong_type(self):
s = Sheet(self.data, "test")
s.name_rows_by_column(0)
s = s.row + "string type" # bang
def test_delete_indexed_row3(self):
s = Sheet(self.data, "test")
s.name_rows_by_column(0)
del s.row["Row 0", "Row 1"]
assert s.number_of_rows() == 2
s.row["Row 1"] # already deleted
def test_delete_row(self):
s = Sheet(self.data, "test")
del s.row[1, 2]
assert s.number_of_rows() == 2
s.row["Row 1"] # already deleted
def test_set_named_row(self):
s = Sheet(self.data, "test")
s.name_rows_by_column(0)
s.row["Row 2"] = [11, 11, 11]
assert s.row["Row 2"] == [11, 11, 11]
def test_dot_notation(self):
s = Sheet(self.data, "test")
s.name_columns_by_row(2)
eq_(s.column.Column_3, [3, 6, 9])
def test_negative_row_index(self):
s = Sheet(self.data, "test")
data = s.row[-1]
eq_(data, self.data[-1])
def test_set_indexed_row(self):
s = Sheet(self.data, "test")
s.name_columns_by_row(2)
s.row[0] = [10000, 1, 11]
assert s.row[0] == [10000, 1, 11]
def test_set_indexed_column(self):
s = Sheet(self.data, "test", name_rows_by_column=0)
s.column[0] = [12, 3, 4, 5]
assert s.column[0] == [12, 3, 4, 5]
def test_add(self):
s = Sheet(self.data, "test")
s.name_columns_by_row(0)
data = OrderedDict({"Column 4": [10, 11, 12]})
s = s.column + data
eq_(s.column.Column_4, [10, 11, 12])
def test_rownames(self):
s = Sheet(self.data, "test", name_rows_by_column=0)
eq_(s.rownames, ["Row 0", "Row 1", "Row 2", "Row 3"])
custom_rows = ["R0", "R1", "R2", "R3"]
s.rownames = custom_rows
eq_(s.rownames, custom_rows)
def test_add_wrong_type(self):
"""Add string type"""
s = Sheet(self.data, "test")
s.name_columns_by_row(0)
s = s.column + "string type" # bang
def test_rownames2(self):
custom_rows = ["R0", "R1", "R2", "R3"]
s = Sheet(self.data, "test", rownames=custom_rows)
eq_(s.rownames, custom_rows)
def test_delete_indexed_column(self):
s = Sheet(self.data, "test")
s.name_columns_by_row(0)
s.delete_named_column_at(1)
assert s.number_of_columns() == 2
s.column["Column 2"] # access it after deletion, bang
def test_rownames3(self):
custom_rows = ["R0", "R1", "R2", "R3"]
Sheet(self.data, "test", name_rows_by_column=0, rownames=custom_rows)
def test_series2(self):
custom_columns = ["C1", "C2", "C3"]
s = Sheet(self.data, "test", colnames=custom_columns)
assert s.colnames == custom_columns
def test_formatter_by_named_row_2(self):
s = Sheet(self.data, "test")
s.name_rows_by_column(0)
s.row.format("Row 1", str)
eq_(s.row["Row 1"], ["1", "2", "3"])
def test_formatter_by_named_column_2(self):
s = Sheet(self.data, "test")
s.name_columns_by_row(2)
s.column.format("Column 1", str)
assert s.column["Column 1"] == ["1", "4", "7"]
def main():
directory = join(dirname(dirname(abspath(__file__))), 'raw_files')
reports = {
'agent_time_card': open_wb(get_book(file_name=join(directory, 'Agent_Time_Card.xlsx'))),
'feature_trace': open_wb(get_book(file_name=join(directory, 'Agent_Realtime_Feature_Trace.xlsx')))
}
schedule = create_schedule(
get_sheet(file_name=join(r'M:/Help Desk/', 'Schedules for OPS.xlsx'),
name_columns_by_row=0,
name_rows_by_column=0)
)
# Bind read function to respective reports
check_time_card = MethodType(read_time_card, reports['agent_time_card'])
check_feature_card = MethodType(read_feature_card, reports['feature_trace'])
output = Sheet(colnames=['', '% BW', '% Avail', 'Absences', 'Late'])
summary = [0, 0, 0]
try:
for sheet_name, emp_data in in_schedule(schedule):
try:
tc_data = check_time_card(sheet_name, emp_data)
except KeyError:
pass
else:
dnd_time = check_feature_card(sheet_name)
output.row += [
'{row_name}'.format(
row_name=emp_data.ext
),
'{val:.1%}'.format(
val=safe_div(dnd_time, tc_data['Duration'])
),
'{val:.1%}'.format(
val=(1 - safe_div(dnd_time, tc_data['Duration']))
),
VALID_DAYS_IN_MONTH - tc_data['Log Events'],
tc_data['Late']
]
summary[0] += dnd_time
summary[1] += tc_data['Duration']
summary[2] += safe_div(dnd_time, tc_data['Duration'])
else:
output.name_rows_by_column(0)
print(output)
print(
'{val:.1%}'.format(
val=safe_div(summary[0], summary[1])
)
)
print(
'{val:.1%}'.format(
val=safe_div(summary[2], 30)
)
)
output.save_as(filename=join(dirname(dirname(abspath(__file__))), 'output', 'outfile.xlsx'))
except Exception:
print(traceback.format_exc())
print('completed life cycle')
