import csv import pandas as pd import xlsxwriter import csv_transformer as csv_t import pandas_helper as ph def get_letter_from_col(col): # Excel uses capital English characters for it's columns. # To simplify finding it, we can mod the index with 26 and # return the indexed character as done below. # NOTE: Creaks after index 701. alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' result = '' if col > 25: result += alphabet[(col // 26)-1 % 26] result += alphabet[col % 26] return result def make_and_insert_title_box(text, row_offset = 0, col_offset = 0): global title_row global title_col title_options = { 'height': 40, 'width': 360, 'fill': {'color': '#D3D3D3'}, 'align': {'vertical': 'middle', 'horizontal': 'center'}, 'font': {'bold': True, 'underline': True, 'size': 16} } title_row += row_offset title_col += col_offset worksheet_charts.insert_textbox(title_row, title_col, text, title_options) def make_and_insert_body_box(text, row_offset = 0, col_offset = 0, font_size = 125): global body_row global body_col body_options = { 'height': 178, 'width' : 360, 'align' : {'vertical': 'middle', 'horizontal': 'center'}, 'font' : {'size': font_size}, } body_row += row_offset body_col += col_offset worksheet_charts.insert_textbox(body_row, body_col, text, body_options) if __name__ == '__main__': ###### Process Row CSV ###### raw_data = csv_t.CSVTransform('incident.csv') ###### Time Based Counters ###### (df_p_d_count, pd_col, pd_row) = ph.set_data_frame_transposed(raw_data.get_priority_daily_counters()) (df_p_w_count, pw_col, pw_row) = ph.set_data_frame_transposed(raw_data.get_priority_weekly_counters()) (df_p_lw_count, plw_col, plw_row) = ph.set_data_frame_transposed(raw_data.get_priority_rolling_week_counters()) (df_p_m_count, pm_col, pm_row) = ph.set_data_frame_transposed(raw_data.get_priority_monthly_counters()) ###### New Activation Location Incident Counters ###### (df_na_p_count, nap_col, nap_row) = ph.set_data_frame_transposed(raw_data.get_na_priority_counter()) (df_na_ci_count, naci_col, naci_row) = ph.set_data_frame_transposed(raw_data.get_na_ci_counter()) ###### Category Counters ###### (df_se_count, df_se_col, df_se_row) = ph.set_data_frame(raw_data.get_se_counter()) (df_loc_count, df_loc_col, df_loc_row) = ph.set_data_frame(raw_data.get_location_counter()) (df_ci_count, df_ci_col, df_ci_row) = ph.set_data_frame(raw_data.get_ci_counter()) (df_na_count, df_na_col, df_na_row) = ph.set_data_frame(raw_data.get_new_activations_counter()) ###### Time Based Incident Data ###### (df_daily_incidents, _, _) = ph.set_data_frame(raw_data.get_daily_incident()) if not df_daily_incidents.empty: df_daily_incidents = df_daily_incidents.set_index('number') (df_weekly_incidents, _, _) = ph.set_data_frame(raw_data.get_weekly_incident()) if not df_weekly_incidents.empty: df_weekly_incidents = df_weekly_incidents.set_index('number') (df_8_to_14_incidents, _, _) = ph.set_data_frame(raw_data.get_8_to_14_incident()) if not df_8_to_14_incidents.empty: df_8_to_14_incidents = df_8_to_14_incidents.set_index('number') (df_monthly_incidents, _, _) = ph.set_data_frame(raw_data.get_monthly_incident()) if not df_monthly_incidents.empty: df_monthly_incidents = df_monthly_incidents.set_index('number') with open('incident.csv', newline='', encoding='cp1252') as csvfile: df_raw_data = pd.DataFrame(data=csv.DictReader(csvfile)) df_raw_data = df_raw_data.set_index('number') with pd.ExcelWriter(f'./outputs/Incident_Report_{raw_data._today}.xlsx', engine='xlsxwriter') as writer: workbook = writer.book bold_text = workbook.add_format({'bold': True}) worksheet_charts = workbook.add_worksheet('Charts') worksheet_tables = workbook.add_worksheet('Tables') ###### Text Boxes for at-a-glance information title_row = 1 title_col = 2 body_row = title_row + 2 body_col = title_col make_and_insert_title_box('New Incidents Logged Today') make_and_insert_body_box(str(raw_data.get_daily_total())) make_and_insert_title_box('New Incidents Logged Last 7 Days', col_offset=6) make_and_insert_body_box(str(raw_data.get_weekly_total()), col_offset=6) make_and_insert_title_box('New Incidents Logged This Year', col_offset=6) make_and_insert_body_box(str(raw_data.get_yearly_total()), col_offset=6) make_and_insert_title_box('P1 Monthly Average Resolved Time', col_offset=6) make_and_insert_body_box(str(raw_data.get_p1_sla_average()), col_offset=6, font_size=36) make_and_insert_title_box("P2 Monthly Average Resolved Time", col_offset=6) make_and_insert_body_box(str(raw_data.get_p2_sla_average()), col_offset=6, font_size=36) make_and_insert_title_box('Work In Progress Incidents', row_offset=12, col_offset=-24) make_and_insert_body_box(str(raw_data.get_wip_total()), row_offset=12, col_offset=-24) make_and_insert_title_box('On Hold Incidents', col_offset=6) make_and_insert_body_box(str(raw_data.get_on_hold_total()), col_offset=6) make_and_insert_title_box('Resolved/Closed Last 7 days', col_offset=6) make_and_insert_body_box(str(raw_data.get_weekly_resolved()), col_offset=6) make_and_insert_title_box('P3 Monthly Average Resolved Time', col_offset=6) make_and_insert_body_box(str(raw_data.get_p3_sla_average()), col_offset=6, font_size=36) make_and_insert_title_box('P4 Monthly Average Resolved Time', col_offset=6) make_and_insert_body_box(str(raw_data.get_p4_sla_average()), col_offset=6, font_size=36) ################################## ### Start Left 'Tables' Column ### ##### Includes their charts ###### ################################## tables_start_row = 1 tables_start_col = 1 space = 3 col_letter = get_letter_from_col(tables_start_col) worksheet_tables.write(col_letter + str(tables_start_row), 'Daily Incidents by Priority and Status', bold_text) df_p_d_count.to_excel(writer, sheet_name='Tables', startrow=tables_start_row, startcol=tables_start_col) chart = ph.make_bar_chart(workbook, 'Incident Chart - Daily', pd_row, pd_col, tables_start_row+1, tables_start_col+1) worksheet_charts.insert_chart(25, 1, chart) tables_start_row = tables_start_row + pd_row + space worksheet_tables.write(col_letter + str(tables_start_row), 'Incidents for Last 7 Days by Priority and Status', bold_text) df_p_w_count.to_excel(writer, sheet_name='Tables', startrow=tables_start_row, startcol=tables_start_col) chart = ph.make_bar_chart(workbook, 'Incident Chart - Last 7 Days', pw_row, pw_col, tables_start_row+1, tables_start_col+1) worksheet_charts.insert_chart(48, 1, chart) tables_start_row = tables_start_row + pd_row + space worksheet_tables.write(col_letter + str(tables_start_row), 'Incidents Between 8 and 14 Days by Priority and Status', bold_text) df_p_lw_count.to_excel(writer, sheet_name='Tables', startrow=tables_start_row, startcol=tables_start_col) chart = ph.make_bar_chart(workbook, 'Incident Chart - Last 8-14 Days', plw_row, plw_col, tables_start_row+1, tables_start_col+1) worksheet_charts.insert_chart(71, 1, chart) tables_start_row = tables_start_row + pd_row + space worksheet_tables.write(col_letter + str(tables_start_row), 'Monthly incidents by Priority Count', bold_text) df_p_m_count.to_excel(writer, sheet_name='Tables', startrow=tables_start_row, startcol=tables_start_col) chart = ph.make_bar_chart(workbook, 'Incident Chart - Last 8-14 Days', pm_row, pm_col, tables_start_row+1, tables_start_col+1) worksheet_charts.insert_chart(91, 1, chart) tables_start_row = tables_start_row + pd_row + space worksheet_tables.write(col_letter + str(tables_start_row), 'New Activations Priority Count', bold_text) df_na_p_count.to_excel(writer, sheet_name='Tables', startrow=tables_start_row, startcol=tables_start_col) chart = ph.make_bar_chart(workbook, 'New Activation Site - Incident Priority', nap_row, nap_col, tables_start_row+1, tables_start_col+1) if chart.series: worksheet_charts.insert_chart(48, 25, chart) tables_start_row = tables_start_row + pd_row + space worksheet_tables.write(col_letter + str(tables_start_row), 'New Activations Platform', bold_text) df_na_ci_count.to_excel(writer, sheet_name='Tables', startrow=tables_start_row, startcol=tables_start_col) chart = ph.make_bar_chart(workbook, 'New Activation Site - Incident Platform', nap_row, nap_col, tables_start_row+1, tables_start_col+1) if chart.series: worksheet_charts.insert_chart(71, 25, chart) ################################ ### End Left 'Tables' Column ### ################################ #################################### ### Start Middle 'Tables' Column ### ###### Includes their charts ####### #################################### tables_start_row = 1 tables_start_col += max(pd_col, pw_col, plw_col, pm_col, nap_col, naci_col) + space col_letter = get_letter_from_col(tables_start_col) worksheet_tables.write(col_letter + str(tables_start_row), 'Incident Count By Support Personnel - CY', bold_text) df_se_count.to_excel(writer, sheet_name='Tables', startrow=tables_start_row, startcol=tables_start_col) chart = ph.make_bar_chart(workbook, 'Incident Chart - Assignee - CY', df_se_row, df_se_col, tables_start_row+1, tables_start_col+1) worksheet_charts.insert_chart(25, 13, chart) tables_start_row = tables_start_row + df_se_col + space worksheet_tables.write(col_letter + str(tables_start_row), 'Incident Count By Known Platform', bold_text) df_ci_count.to_excel(writer, sheet_name='Tables', startrow=tables_start_row, startcol=tables_start_col) chart = ph.make_bar_chart(workbook, 'Incident Chart - Platform - CY', df_ci_row, df_ci_col, tables_start_row+1, tables_start_col+1) worksheet_charts.insert_chart(78, 13, chart) tables_start_row = tables_start_row + df_ci_col + space worksheet_tables.write(col_letter + str(tables_start_row), 'Incident Count By New Activation Site', bold_text) df_na_count.to_excel(writer, sheet_name='Tables', startrow=tables_start_row, startcol=tables_start_col) chart = ph.make_bar_chart(workbook, 'Incident Chart - New Activations', df_na_row, df_na_col, tables_start_row+1, tables_start_col+1) if chart.series: worksheet_charts.insert_chart(25, 25, chart) ################################## ### End Middle 'Tables' Column ### ################################## ################################### ### Start Right 'Tables' Column ### ###### Includes their charts ###### ################################### tables_start_row = 1 tables_start_col += max(df_se_row, df_ci_row, df_na_row) + space col_letter = get_letter_from_col(tables_start_col) worksheet_tables.write(col_letter + str(tables_start_row), 'Incident Count By Location Number - CY', bold_text) df_loc_count.to_excel(writer, sheet_name='Tables', startrow=tables_start_row, startcol=tables_start_col) chart = ph.make_pie_chart(workbook, 'Incident Chart - Location Number - CY', df_loc_row, df_loc_col, tables_start_row+1, tables_start_col+1) worksheet_charts.insert_chart(48, 13, chart) ################################# ### End Right 'Tables' Column ### ################################# ### Extra Sheets ### df_daily_incidents.to_excel(writer, sheet_name='Daily Incidents', startrow=0, startcol=0) df_weekly_incidents.to_excel(writer, sheet_name='Last 7 Day Incidents', startrow=0, startcol=0) df_8_to_14_incidents.to_excel(writer, sheet_name='8 to 14 Day Incidents', startrow=0, startcol=0) df_monthly_incidents.to_excel(writer, sheet_name='Monthly Incidents', startrow=0, startcol=0) df_raw_data.to_excel(writer, sheet_name='Raw Data', startrow=0, startcol=0)