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')