NickKalar/Incident_Report_Generator
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
Incident_Report_Generator/transform.py
Nicholas Kalar 5c5582088a Initial commit
2025-11-05 12:58:07 -05:00

258 lines
13 KiB
Python
Raw Permalink Blame History

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)
Reference in New Issue View Git Blame Copy Permalink