779 lines
33 KiB
Python
779 lines
33 KiB
Python
import calendar
|
|
from io import BytesIO
|
|
|
|
from openpyxl import Workbook
|
|
from openpyxl.chart import BarChart, LineChart, PieChart, Reference
|
|
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
|
|
from openpyxl.utils import get_column_letter
|
|
|
|
BLUE_FILL = PatternFill(start_color="2F75B5", end_color="2F75B5", fill_type="solid")
|
|
GREEN_FILL = PatternFill(start_color="00B050", end_color="00B050", fill_type="solid")
|
|
HEADER_FONT = Font(name="Calibri", size=11, bold=True, color="FFFFFF")
|
|
BOLD_FONT = Font(name="Calibri", size=11, bold=True)
|
|
DATA_FONT = Font(name="Calibri", size=11)
|
|
PCT_FMT = "0.0%"
|
|
NUM_FMT = "0.0"
|
|
THIN = Side(style="thin")
|
|
BORDER_ALL = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
|
|
CENTER = Alignment(horizontal="center", vertical="center", wrap_text=True)
|
|
RIGHT_ALIGN = Alignment(horizontal="right", vertical="center")
|
|
|
|
|
|
def _style_header(ws, row, col, value):
|
|
cell = ws.cell(row=row, column=col, value=value)
|
|
cell.font = HEADER_FONT
|
|
cell.fill = BLUE_FILL
|
|
cell.alignment = CENTER
|
|
cell.border = BORDER_ALL
|
|
return cell
|
|
|
|
|
|
def _style_data(ws, row, col, value, fmt=None):
|
|
cell = ws.cell(row=row, column=col, value=value)
|
|
cell.font = DATA_FONT
|
|
cell.alignment = CENTER
|
|
cell.border = BORDER_ALL
|
|
if fmt:
|
|
cell.number_format = fmt
|
|
return cell
|
|
|
|
|
|
def _style_bold(ws, row, col, value, fmt=None):
|
|
cell = ws.cell(row=row, column=col, value=value)
|
|
cell.font = BOLD_FONT
|
|
cell.border = BORDER_ALL
|
|
cell.alignment = CENTER
|
|
if fmt:
|
|
cell.number_format = fmt
|
|
return cell
|
|
|
|
|
|
def _strip_tz(dt):
|
|
if dt is None:
|
|
return None
|
|
return dt.replace(tzinfo=None)
|
|
|
|
|
|
def _write_all_quarters_sheet(wb, service):
|
|
rows = service.get_all_complaints_raw()
|
|
ws = wb.create_sheet("ALL Quarters")
|
|
|
|
headers = [
|
|
"Week", "#", "Reference", "Source", "Location", "Department", "Domain",
|
|
"Created At", "Entered By", "Timeline",
|
|
"Form Sent", "Activated At", "Assigned To",
|
|
"Sent To Dept", "Time Entry→Send",
|
|
"First Reminder", "Time 1st Reminder→Send",
|
|
"Second Reminder", "Escalated At",
|
|
"Closed At", "Resolved At",
|
|
"Processing Hours", "Response Date", "Status", "Satisfaction",
|
|
]
|
|
for i, h in enumerate(headers, 1):
|
|
_style_header(ws, 1, i, h)
|
|
|
|
for idx, r in enumerate(rows, 2):
|
|
week_num = (r["created_at"].day - 1) // 15 + 1
|
|
week_label = "1st Half" if r["created_at"].day <= 15 else "2nd Half"
|
|
col = 1
|
|
_style_data(ws, idx, col, f"{week_label} of the month, From {'1' if r['created_at'].day <= 15 else '16'} to {'15' if r['created_at'].day <= 15 else str(calendar.monthrange(r['created_at'].year, r['created_at'].month)[1])}"); col += 1
|
|
_style_data(ws, idx, col, idx - 1); col += 1
|
|
_style_data(ws, idx, col, r["reference"]); col += 1
|
|
_style_data(ws, idx, col, r["source"]); col += 1
|
|
_style_data(ws, idx, col, r["location"]); col += 1
|
|
_style_data(ws, idx, col, r["department"]); col += 1
|
|
_style_data(ws, idx, col, r["domain"]); col += 1
|
|
_style_data(ws, idx, col, _strip_tz(r["created_at"])); col += 1
|
|
_style_data(ws, idx, col, r["entered_by"]); col += 1
|
|
_style_data(ws, idx, col, r["timeline_bucket"]); col += 1
|
|
_style_data(ws, idx, col, _strip_tz(r["form_sent_at"])); col += 1
|
|
_style_data(ws, idx, col, _strip_tz(r["activated_at"])); col += 1
|
|
_style_data(ws, idx, col, r["assigned_to"]); col += 1
|
|
_style_data(ws, idx, col, _strip_tz(r["sent_at"])); col += 1
|
|
_style_data(ws, idx, col, r["time_entry_to_send"]); col += 1
|
|
_style_data(ws, idx, col, _strip_tz(r["first_reminder"])); col += 1
|
|
_style_data(ws, idx, col, r["time_first_reminder_to_send"]); col += 1
|
|
_style_data(ws, idx, col, _strip_tz(r.get("second_reminder"))); col += 1
|
|
_style_data(ws, idx, col, _strip_tz(r["escalated_at"])); col += 1
|
|
_style_data(ws, idx, col, _strip_tz(r["closed_at"])); col += 1
|
|
_style_data(ws, idx, col, _strip_tz(r["resolved_at"])); col += 1
|
|
_style_data(ws, idx, col, r["processing_hours"]); col += 1
|
|
_style_data(ws, idx, col, r["response_date"]); col += 1
|
|
_style_data(ws, idx, col, r["status"]); col += 1
|
|
_style_data(ws, idx, col, r["satisfaction"]); col += 1
|
|
|
|
for c in range(1, len(headers) + 1):
|
|
ws.column_dimensions[get_column_letter(c)].width = 18
|
|
ws.column_dimensions["B"].width = 8
|
|
ws.column_dimensions["C"].width = 20
|
|
|
|
|
|
def _write_kpi_sheet(wb, service):
|
|
kpi = service.get_kpi_data()
|
|
satisfaction = service.get_satisfaction_data()
|
|
moh_kpi = service.get_moh_kpi_data()
|
|
months = service.active_month_labels
|
|
active_months = service.active_months
|
|
total_col = 2 + len(months)
|
|
|
|
def _kpi_row(ws, row, label, get_val, total_val, is_pct=False):
|
|
_style_data(ws, row, 1, label)
|
|
for i, m in enumerate(active_months):
|
|
v = get_val(m)
|
|
fmt = PCT_FMT if is_pct else None
|
|
_style_data(ws, row, 2 + i, v, fmt)
|
|
_style_data(ws, row, total_col, total_val, PCT_FMT if is_pct else None)
|
|
|
|
blocks = [
|
|
{
|
|
"kpi_label": "KPI #1",
|
|
"header": "Numerator/Denominator",
|
|
"rows": [
|
|
("Total Number of Closed Complaints", lambda m: kpi["months"][m]["closed"], kpi["totals"]["closed"]),
|
|
("Total Number of Complaints", lambda m: kpi["months"][m]["total"], kpi["totals"]["total"]),
|
|
("Result (%)", lambda m: kpi["months"][m]["resolution_rate"], kpi["totals"]["resolution_rate"], True),
|
|
],
|
|
},
|
|
{
|
|
"kpi_label": "KPI #2",
|
|
"header": "Numerator/Denominator",
|
|
"rows": [
|
|
("Number of Complaints Resolved Within 72 Hours", lambda m: kpi["months"][m]["resolved_72h"], kpi["totals"]["resolved_72h"]),
|
|
("Total Number of Complaints", lambda m: kpi["months"][m]["total"], kpi["totals"]["total"]),
|
|
("Result (%)", lambda m: kpi["months"][m]["resolved_72h_rate"], kpi["totals"]["resolved_72h_rate"], True),
|
|
],
|
|
},
|
|
{
|
|
"kpi_label": "KPI #3",
|
|
"header": "Satisfaction Rate",
|
|
"rows": [
|
|
("Number\u00a0of\u00a0Satisfied\u00a0Responses", lambda m: satisfaction["months"][m]["satisfied"], satisfaction["totals"]["satisfied"]),
|
|
("Total Number of Patient Surveyed (Responses)", lambda m: satisfaction["months"][m]["surveyed"], satisfaction["totals"]["surveyed"]),
|
|
("Result (%)", lambda m: satisfaction["months"][m]["rate"], satisfaction["totals"]["rate"], True),
|
|
],
|
|
},
|
|
{
|
|
"kpi_label": "KPI #3",
|
|
"header": "Response Rate - Calls",
|
|
"rows": [
|
|
("Total Responses", lambda m: satisfaction["call_months"][m]["responses"], satisfaction["call_totals"]["responses"]),
|
|
("Total Complaints", lambda m: satisfaction["call_months"][m]["total"], satisfaction["call_totals"]["total"]),
|
|
("Result (%)", lambda m: satisfaction["call_months"][m]["rate"], satisfaction["call_totals"]["rate"], True),
|
|
],
|
|
},
|
|
{
|
|
"kpi_label": "KPI #3",
|
|
"header": "Response Rate - Survey",
|
|
"rows": [
|
|
("Total Responses", lambda m: satisfaction["survey_months"][m]["responses"], satisfaction["survey_totals"]["responses"]),
|
|
("Total Complaints", lambda m: satisfaction["survey_months"][m]["total"], satisfaction["survey_totals"]["total"]),
|
|
("Result (%)", lambda m: satisfaction["survey_months"][m]["rate"], satisfaction["survey_totals"]["rate"], True),
|
|
],
|
|
},
|
|
{
|
|
"kpi_label": "KPI #3",
|
|
"header": "MOH KPI",
|
|
"rows": [
|
|
("Complaints which received a (satisfied) scoring on its resolution", lambda m: moh_kpi["moh"]["months"][m]["satisfied_scoring"], moh_kpi["moh"]["totals"]["satisfied_scoring"]),
|
|
("Total number of complaints received", lambda m: moh_kpi["moh"]["months"][m]["total"], moh_kpi["moh"]["totals"]["total"]),
|
|
("Result (%)", lambda m: moh_kpi["moh"]["months"][m]["rate"], moh_kpi["moh"]["totals"]["rate"], True),
|
|
],
|
|
},
|
|
{
|
|
"kpi_label": "KPI #3",
|
|
"header": "MOH KPI - Real Numbers",
|
|
"rows": [
|
|
("Complaints which received a (satisfied) scoring on its resolution", lambda m: moh_kpi["real"]["months"][m]["satisfied_scoring"], moh_kpi["real"]["totals"]["satisfied_scoring"]),
|
|
("Total number of complaints received", lambda m: moh_kpi["real"]["months"][m]["total"], moh_kpi["real"]["totals"]["total"]),
|
|
("Result (%)", lambda m: moh_kpi["real"]["months"][m]["rate"], moh_kpi["real"]["totals"]["rate"], True),
|
|
],
|
|
},
|
|
{
|
|
"kpi_label": "KPI #4",
|
|
"header": "Numerator/Denominator",
|
|
"rows": [
|
|
("Total Responses Within 48 Hours", lambda m: kpi["months"][m]["resolved_48h"], kpi["totals"]["resolved_48h"]),
|
|
("Total Number of Complaints", lambda m: kpi["months"][m]["total"], kpi["totals"]["total"]),
|
|
("Result (%)", lambda m: kpi["months"][m]["resolved_48h_rate"], kpi["totals"]["resolved_48h_rate"], True),
|
|
],
|
|
},
|
|
]
|
|
|
|
ws = wb.create_sheet("1. KPI")
|
|
row = 1
|
|
|
|
for block in blocks:
|
|
_style_header(ws, row, 1, block["header"])
|
|
for i, ml in enumerate(months):
|
|
_style_header(ws, row, 2 + i, ml)
|
|
_style_header(ws, row, total_col, "Total")
|
|
ws.cell(row=row, column=total_col + 2, value=block["kpi_label"]).font = BOLD_FONT
|
|
ws.merge_cells(start_row=row, start_column=total_col + 2, end_row=row + 2, end_column=total_col + 2)
|
|
|
|
for row_data in block["rows"]:
|
|
row += 1
|
|
is_pct = len(row_data) > 3 and row_data[3]
|
|
_kpi_row(ws, row, row_data[0], row_data[1], row_data[2], is_pct)
|
|
|
|
row += 2
|
|
|
|
for c in range(1, total_col + 3):
|
|
ws.column_dimensions[get_column_letter(c)].width = 16
|
|
|
|
|
|
def _write_source_table_sheet(wb, service):
|
|
source = service.get_source_breakdown()
|
|
source_totals = service.get_source_totals()
|
|
location = service.get_location_breakdown()
|
|
dept_type = service.get_dept_type_breakdown()
|
|
dept_type_monthly = service.get_dept_type_monthly_table()
|
|
monthly_summary = service.get_monthly_summary()
|
|
location_ratios = service.get_location_ratios()
|
|
quarterly_volumes = service.get_quarterly_volumes()
|
|
active_months = service.active_months
|
|
months = [calendar.month_abbr[m].upper() for m in active_months]
|
|
|
|
ws = wb.create_sheet("2. 1st Table")
|
|
|
|
def _month_col(idx):
|
|
return 3 * idx + 5
|
|
|
|
def _write_month_header(ws, row):
|
|
_style_header(ws, row, 1, "%")
|
|
_style_header(ws, row, 2, "Number")
|
|
for i, m in enumerate(active_months):
|
|
c = _month_col(i)
|
|
_style_header(ws, row, c, months[i])
|
|
|
|
def _write_subrow(ws, row, label, annual_total, get_monthly_count, get_monthly_pct):
|
|
_style_data(ws, row, 4, annual_total)
|
|
for i, m in enumerate(active_months):
|
|
c = _month_col(i)
|
|
_style_data(ws, row, c, label)
|
|
cnt = get_monthly_count(m)
|
|
if cnt:
|
|
_style_data(ws, row, c + 1, cnt)
|
|
pct = get_monthly_pct(m)
|
|
if pct:
|
|
_style_data(ws, row, c + 2, pct, PCT_FMT)
|
|
|
|
def _write_toprow(ws, row, pct, count, label, sub_label, sub_annual, get_sub_count, get_sub_pct):
|
|
_style_data(ws, row, 1, pct, PCT_FMT)
|
|
_style_data(ws, row, 2, count)
|
|
_style_data(ws, row, 3, label)
|
|
_write_subrow(ws, row, sub_label, sub_annual, get_sub_count, get_sub_pct)
|
|
|
|
def _write_total_row(ws, row, total_all, monthly_totals):
|
|
_style_data(ws, row, 1, "%")
|
|
_style_data(ws, row, 2, "Number")
|
|
_style_bold(ws, row, 4, total_all)
|
|
for i, m in enumerate(active_months):
|
|
c = _month_col(i)
|
|
_style_data(ws, row, c, "Total")
|
|
_style_data(ws, row, c + 1, monthly_totals[m])
|
|
_style_data(ws, row, c + 2, 1, PCT_FMT)
|
|
|
|
row = 1
|
|
_write_month_header(ws, row)
|
|
|
|
total_all = sum(source[m]["total"] for m in active_months)
|
|
ext_total = sum(source[m]["external"] for m in active_months)
|
|
int_total = sum(source[m]["internal"] for m in active_months)
|
|
|
|
row = 2
|
|
ins_total = sum(source[m]["insurance"] for m in active_months)
|
|
_write_toprow(ws, row,
|
|
round(ext_total / total_all, 3) if total_all else 0, ext_total, "External",
|
|
"Insurance company", ins_total,
|
|
lambda m: source[m]["insurance"],
|
|
lambda m: round(source[m]["insurance"] / source[m]["total"], 3) if source[m]["total"] else 0)
|
|
|
|
row = 3
|
|
moh_total = sum(source[m]["moh"] for m in active_months)
|
|
_write_subrow(ws, row, "MOH", moh_total,
|
|
lambda m: source[m]["moh"],
|
|
lambda m: round(source[m]["moh"] / source[m]["total"], 3) if source[m]["total"] else 0)
|
|
|
|
row = 4
|
|
chi_total = sum(source[m]["chi"] for m in active_months)
|
|
_write_subrow(ws, row, "CHI", chi_total,
|
|
lambda m: source[m]["chi"],
|
|
lambda m: round(source[m]["chi"] / source[m]["total"], 3) if source[m]["total"] else 0)
|
|
|
|
row = 5
|
|
pat_total = sum(source[m]["patients"] for m in active_months)
|
|
_write_toprow(ws, row,
|
|
round(int_total / total_all, 3) if total_all else 0, int_total, "Internal",
|
|
"Patients", pat_total,
|
|
lambda m: source[m]["patients"],
|
|
lambda m: round(source[m]["patients"] / source[m]["total"], 3) if source[m]["total"] else 0)
|
|
|
|
row = 6
|
|
rel_total = sum(source[m]["relatives"] for m in active_months)
|
|
_write_subrow(ws, row, "Patient's relatives", rel_total,
|
|
lambda m: source[m]["relatives"],
|
|
lambda m: round(source[m]["relatives"] / source[m]["total"], 3) if source[m]["total"] else 0)
|
|
|
|
row = 7
|
|
_write_total_row(ws, row, total_all, {m: source[m]["total"] for m in active_months})
|
|
|
|
row = 9
|
|
for label, key in [("In-Patient", "ip"), ("Out-Patient", "op"), ("ER", "er")]:
|
|
total_v = sum(location[m][key] for m in active_months)
|
|
_style_data(ws, row, 1, round(total_v / total_all, 3) if total_all else 0, PCT_FMT)
|
|
_style_data(ws, row, 2, total_v)
|
|
_style_data(ws, row, 3, label)
|
|
_style_data(ws, row, 4, total_v)
|
|
for i, m in enumerate(active_months):
|
|
c = _month_col(i)
|
|
_style_data(ws, row, c, label)
|
|
cnt = location[m][key]
|
|
if cnt:
|
|
_style_data(ws, row, c + 1, cnt)
|
|
_style_data(ws, row, c + 2, round(cnt / source[m]["total"], 3) if source[m]["total"] else 0, PCT_FMT)
|
|
row += 1
|
|
|
|
_write_total_row(ws, row, total_all, {m: source[m]["total"] for m in active_months})
|
|
|
|
row += 2
|
|
for label, key in [("Medical", "medical"), ("Admin", "admin"), ("Nursing", "nursing"), ("Support Services", "support")]:
|
|
total_v = sum(dept_type[m][key] for m in active_months)
|
|
_style_data(ws, row, 1, round(total_v / total_all, 3) if total_all else 0, PCT_FMT)
|
|
_style_data(ws, row, 2, total_v)
|
|
_style_data(ws, row, 3, label)
|
|
_style_data(ws, row, 4, total_v)
|
|
for i, m in enumerate(active_months):
|
|
c = _month_col(i)
|
|
_style_data(ws, row, c, label)
|
|
cnt = dept_type[m][key]
|
|
if cnt:
|
|
_style_data(ws, row, c + 1, cnt)
|
|
_style_data(ws, row, c + 2, round(cnt / dept_type[m]["total"], 3) if dept_type[m]["total"] else 0, PCT_FMT)
|
|
row += 1
|
|
|
|
_write_total_row(ws, row, total_all, {m: source[m]["total"] for m in active_months})
|
|
|
|
row += 2
|
|
summary_headers = ["Month", "MOH Complaints", "CHI", "Insurance Company", "Internal", "Total Complaints", "MOH Percentage", "CCHI Percentage"]
|
|
for i, h in enumerate(summary_headers):
|
|
_style_header(ws, row, 5 + i, h)
|
|
|
|
for ms in monthly_summary:
|
|
row += 1
|
|
_style_data(ws, row, 5, ms["month"])
|
|
_style_data(ws, row, 6, ms["moh"])
|
|
_style_data(ws, row, 7, ms["chi"])
|
|
_style_data(ws, row, 8, ms["insurance"])
|
|
_style_data(ws, row, 9, ms["internal"])
|
|
_style_data(ws, row, 10, ms["total"])
|
|
_style_data(ws, row, 11, ms["moh_pct"], PCT_FMT)
|
|
_style_data(ws, row, 12, ms["chi_pct"], PCT_FMT)
|
|
|
|
row += 2
|
|
source_total_items = [
|
|
("Internal Complaints", source_totals["internal"]["count"], "% Internal", source_totals["internal"]["pct"]),
|
|
("External Complaints", source_totals["external"]["count"], "% External", source_totals["external"]["pct"]),
|
|
("MOH", source_totals["moh"]["count"], "% MOH", source_totals["moh"]["pct"]),
|
|
("CHI", source_totals["chi"]["count"], "% CHI", source_totals["chi"]["pct"]),
|
|
("Insurance Company", source_totals["insurance"]["count"], "% Insurance Comp.", source_totals["insurance"]["pct"]),
|
|
]
|
|
for label, count, pct_label, pct in source_total_items:
|
|
_style_bold(ws, row, 6, label)
|
|
_style_data(ws, row, 7, count)
|
|
_style_bold(ws, row, 9, pct_label)
|
|
_style_data(ws, row, 10, pct, PCT_FMT)
|
|
row += 1
|
|
|
|
row += 1
|
|
for area, title, comp_col, pat_col in [
|
|
("ip", "In-Patient Complaints vs Patients", "In-Patient Complaints", "Total In-Patient "),
|
|
("op", "Out-Patient Complaints vs Patients", "Out-Patient Complaint", "Total Out-Patient"),
|
|
("er", "ER Complaints vs Patients", "ER Complaints", "Total ER Patients"),
|
|
]:
|
|
headers = ["Month", comp_col, pat_col, "Persentage"]
|
|
for i, h in enumerate(headers):
|
|
_style_header(ws, row, 5 + i, h)
|
|
for i, m in enumerate(active_months):
|
|
row += 1
|
|
d = location_ratios["months"][m]
|
|
_style_data(ws, row, 5, months[i])
|
|
_style_data(ws, row, 6, d[f"{area}_complaints"])
|
|
_style_data(ws, row, 7, d[f"{area}_patients"])
|
|
_style_data(ws, row, 8, d[f"{area}_ratio"], PCT_FMT)
|
|
row += 1
|
|
t = location_ratios["totals"][area]
|
|
_style_bold(ws, row, 5, "TOTAL")
|
|
_style_bold(ws, row, 6, t["complaints"])
|
|
_style_bold(ws, row, 7, t["patients"])
|
|
_style_bold(ws, row, 8, t["ratio"], PCT_FMT)
|
|
row += 1
|
|
|
|
row += 1
|
|
summary_ratio_data = [
|
|
("In-Patient Complaints", location_ratios["totals"]["ip"]["complaints"],
|
|
"Total In-Patient ", location_ratios["totals"]["ip"]["patients"],
|
|
"Ratio", location_ratios["totals"]["ip"]["ratio"]),
|
|
("Out-Patient Complaint", location_ratios["totals"]["op"]["complaints"],
|
|
"Total Out-Patient", location_ratios["totals"]["op"]["patients"],
|
|
"Ratio", location_ratios["totals"]["op"]["ratio"]),
|
|
("ER Complaints", location_ratios["totals"]["er"]["complaints"],
|
|
"Total ER Patients", location_ratios["totals"]["er"]["patients"],
|
|
"Ratio", location_ratios["totals"]["er"]["ratio"]),
|
|
]
|
|
for comp_label, comp_count, pat_label, pat_count, ratio_label, ratio_val in summary_ratio_data:
|
|
_style_bold(ws, row, 10, comp_label)
|
|
_style_data(ws, row, 11, comp_count)
|
|
_style_bold(ws, row, 12, ratio_val, PCT_FMT)
|
|
row += 1
|
|
|
|
row += 1
|
|
for q_num in ["1", "2", "3", "4"]:
|
|
q_data = quarterly_volumes.get(q_num, {})
|
|
q_months = q_data.get("months", {})
|
|
_style_header(ws, row, 10, "Area/Unit")
|
|
for i, m_num in enumerate(sorted(q_months.keys())):
|
|
_style_header(ws, row, 11 + i, months[m_num - 1])
|
|
_style_header(ws, row, 14, "Total")
|
|
|
|
row += 1
|
|
for area_label, area_key in [("OPD", "OPD"), ("ER", "ER"), ("IP", "IP")]:
|
|
_style_data(ws, row, 10, area_label)
|
|
q_total = 0
|
|
for i, m_num in enumerate(sorted(q_months.keys())):
|
|
val = q_months[m_num].get(area_key, 0)
|
|
_style_data(ws, row, 11 + i, val)
|
|
q_total += val
|
|
_style_data(ws, row, 14, q_total)
|
|
row += 1
|
|
|
|
row += 1
|
|
dept_headers = ["Month", "Medical", "Admin", "Nursing", "Support Services", "Total Complaints"]
|
|
for i, h in enumerate(dept_headers):
|
|
_style_header(ws, row, 5 + i, h)
|
|
|
|
for mr in dept_type_monthly["months"]:
|
|
row += 1
|
|
_style_data(ws, row, 5, mr["month"])
|
|
_style_data(ws, row, 6, mr["medical"])
|
|
_style_data(ws, row, 7, mr["admin"])
|
|
_style_data(ws, row, 8, mr["nursing"])
|
|
_style_data(ws, row, 9, mr["support"])
|
|
_style_data(ws, row, 10, mr["total"])
|
|
|
|
row += 1
|
|
_style_bold(ws, row, 5, "TOTAL")
|
|
_style_bold(ws, row, 6, dept_type_monthly["totals"]["medical"])
|
|
_style_bold(ws, row, 7, dept_type_monthly["totals"]["admin"])
|
|
_style_bold(ws, row, 8, dept_type_monthly["totals"]["nursing"])
|
|
_style_bold(ws, row, 9, dept_type_monthly["totals"]["support"])
|
|
_style_bold(ws, row, 10, dept_type_monthly["totals"]["total"])
|
|
|
|
row += 1
|
|
_style_bold(ws, row, 5, "% From total")
|
|
_style_bold(ws, row, 6, dept_type_monthly["percentages"]["medical"], PCT_FMT)
|
|
_style_bold(ws, row, 7, dept_type_monthly["percentages"]["admin"], PCT_FMT)
|
|
_style_bold(ws, row, 8, dept_type_monthly["percentages"]["nursing"], PCT_FMT)
|
|
_style_bold(ws, row, 9, dept_type_monthly["percentages"]["support"], PCT_FMT)
|
|
_style_bold(ws, row, 10, dept_type_monthly["percentages"]["total"], PCT_FMT)
|
|
|
|
row += 2
|
|
for i, h in enumerate(["Medical", "Admin", "Nursing", "Support Services", "Total Complaints"]):
|
|
_style_header(ws, row, 5 + i, h)
|
|
row += 1
|
|
_style_data(ws, row, 5, "Persentage")
|
|
_style_data(ws, row, 6, dept_type_monthly["percentages"]["medical"], PCT_FMT)
|
|
_style_data(ws, row, 7, dept_type_monthly["percentages"]["admin"], PCT_FMT)
|
|
_style_data(ws, row, 8, dept_type_monthly["percentages"]["nursing"], PCT_FMT)
|
|
_style_data(ws, row, 9, dept_type_monthly["percentages"]["support"], PCT_FMT)
|
|
_style_data(ws, row, 10, dept_type_monthly["percentages"]["total"], PCT_FMT)
|
|
row += 1
|
|
_style_bold(ws, row, 5, "TOTAL")
|
|
_style_bold(ws, row, 6, dept_type_monthly["totals"]["medical"])
|
|
_style_bold(ws, row, 7, dept_type_monthly["totals"]["admin"])
|
|
_style_bold(ws, row, 8, dept_type_monthly["totals"]["nursing"])
|
|
_style_bold(ws, row, 9, dept_type_monthly["totals"]["support"])
|
|
_style_bold(ws, row, 10, dept_type_monthly["totals"]["total"])
|
|
|
|
for c in range(1, 42):
|
|
ws.column_dimensions[get_column_letter(c)].width = 14
|
|
ws.column_dimensions["C"].width = 22
|
|
|
|
|
|
def _write_escalated_sheet(wb, service):
|
|
escalated = service.get_escalated_breakdown()
|
|
by_cat = escalated["by_category"]
|
|
by_source = escalated.get("by_source", {})
|
|
|
|
ws = wb.create_sheet("3. Escalated Complaints")
|
|
|
|
categories = [
|
|
("Medical", "medical"),
|
|
("Non-Medical", "non_medical"),
|
|
("Nursing", "nursing"),
|
|
("Support Services", "support"),
|
|
]
|
|
|
|
cat_headers = []
|
|
for cat_label, cat_key in categories:
|
|
cat_headers.append((cat_label, "Sub-department"))
|
|
cat_headers.append((cat_label, "Escalated"))
|
|
|
|
num_cols = len(cat_headers)
|
|
for i in range(num_cols):
|
|
_style_header(ws, 1, i + 1, cat_headers[i][1])
|
|
for i in range(0, num_cols, 2):
|
|
ws.cell(row=1, column=i + 1).value = cat_headers[i][0]
|
|
ws.merge_cells(start_row=1, start_column=i + 1, end_row=1, end_column=i + 2)
|
|
ws.cell(row=1, column=i + 1).font = HEADER_FONT
|
|
ws.cell(row=1, column=i + 1).fill = BLUE_FILL
|
|
ws.cell(row=1, column=i + 1).alignment = CENTER
|
|
|
|
_style_header(ws, 2, 1, "Sub-department")
|
|
_style_header(ws, 2, 2, "Escalated")
|
|
for i in range(2, num_cols, 2):
|
|
_style_header(ws, 2, i + 1, "Sub-department")
|
|
_style_header(ws, 2, i + 2, "Escalated")
|
|
|
|
max_rows = max(
|
|
len(by_cat.get(cat_key, {})) for _, cat_key in categories
|
|
)
|
|
|
|
cat_totals = {}
|
|
for cat_label, cat_key in categories:
|
|
depts = by_cat.get(cat_key, {})
|
|
cat_totals[cat_key] = {
|
|
"total_escalated": sum(depts.values()),
|
|
"total_complaints": 0,
|
|
}
|
|
sorted_depts = sorted(depts.items(), key=lambda x: -x[1])
|
|
col_start = categories.index((cat_label, cat_key)) * 2 + 1
|
|
for r, (dept_name, count) in enumerate(sorted_depts):
|
|
_style_data(ws, 3 + r, col_start, dept_name)
|
|
_style_data(ws, 3 + r, col_start + 1, count)
|
|
total_row = 3 + max_rows
|
|
_style_bold(ws, total_row, col_start, "Total")
|
|
_style_bold(ws, total_row, col_start + 1, sum(depts.values()))
|
|
|
|
total_complaints_row = total_row + 1
|
|
_style_bold(ws, total_complaints_row, col_start, "Total Complaints")
|
|
per_dept = service.get_per_department_breakdown()
|
|
dept_names = [d["name"] for d in per_dept["categories"].get(cat_key, [])]
|
|
tc = sum(d["total"] for d in per_dept["categories"].get(cat_key, []))
|
|
_style_bold(ws, total_complaints_row, col_start + 1, tc)
|
|
|
|
pct_row = total_complaints_row + 1
|
|
esc_count = sum(depts.values())
|
|
_style_bold(ws, pct_row, col_start, "% Escalated Complaints")
|
|
_style_bold(ws, pct_row, col_start + 1, round(esc_count / tc, 3) if tc > 0 else 0, PCT_FMT)
|
|
|
|
summary_row = 3 + max_rows + 4
|
|
ws.cell(row=summary_row, column=1, value="Total number of Escalated Complaints").font = BOLD_FONT
|
|
row = summary_row + 1
|
|
for label, key in [("Internal Complaints", "internal"), ("MOH", "moh"), ("CHI", "chi")]:
|
|
_style_bold(ws, row, 1, label)
|
|
_style_data(ws, row, 2, by_source.get(key, 0))
|
|
row += 1
|
|
_style_bold(ws, row, 1, "Total")
|
|
_style_data(ws, row, 2, sum(by_source.values()))
|
|
|
|
for c in range(1, num_cols + 1):
|
|
ws.column_dimensions[get_column_letter(c)].width = 30 if c % 2 == 1 else 12
|
|
|
|
|
|
def _write_per_department_sheet(wb, service):
|
|
per_dept = service.get_per_department_breakdown()
|
|
categories = per_dept["categories"]
|
|
source_totals = per_dept["source_totals"]
|
|
|
|
ws = wb.create_sheet("4. Each dep. complaints")
|
|
|
|
cat_configs = [
|
|
("Medical", "medical", 2),
|
|
("Non-Medical", "non_medical", 12),
|
|
("Nursing", "nursing", 20),
|
|
("Support Services", "support", 28),
|
|
]
|
|
|
|
for cat_label, cat_key, col_start in cat_configs:
|
|
_style_header(ws, 1, col_start, cat_label)
|
|
ws.merge_cells(start_row=1, start_column=col_start, end_row=1, end_column=col_start + 7)
|
|
|
|
sub_headers = ["Sub-dept", "MOH", "CHI", "Insurance", "Internal", "Total", "Escalated", "Response Rate (Days)"]
|
|
for i, h in enumerate(sub_headers):
|
|
_style_header(ws, 2, col_start + i, h)
|
|
|
|
depts = categories.get(cat_key, [])
|
|
for r, d in enumerate(depts):
|
|
row = 3 + r
|
|
_style_data(ws, row, col_start, d["name"])
|
|
_style_data(ws, row, col_start + 1, d["moh"])
|
|
_style_data(ws, row, col_start + 2, d["chi"])
|
|
_style_data(ws, row, col_start + 3, d["insurance"])
|
|
_style_data(ws, row, col_start + 4, d["internal"])
|
|
_style_data(ws, row, col_start + 5, d["total"])
|
|
_style_data(ws, row, col_start + 6, d["escalated"])
|
|
_style_data(ws, row, col_start + 7, d["avg_response_days"], NUM_FMT)
|
|
|
|
total_row = 3 + len(depts)
|
|
_style_bold(ws, total_row, col_start, "Total")
|
|
_style_bold(ws, total_row, col_start + 1, sum(d["moh"] for d in depts))
|
|
_style_bold(ws, total_row, col_start + 2, sum(d["chi"] for d in depts))
|
|
_style_bold(ws, total_row, col_start + 3, sum(d["insurance"] for d in depts))
|
|
_style_bold(ws, total_row, col_start + 4, sum(d["internal"] for d in depts))
|
|
_style_bold(ws, total_row, col_start + 5, sum(d["total"] for d in depts))
|
|
_style_bold(ws, total_row, col_start + 6, sum(d["escalated"] for d in depts))
|
|
|
|
summary_row = 3 + max(len(categories.get(ck, [])) for _, ck, _ in cat_configs) + 3
|
|
|
|
source_items = [
|
|
("MOH", source_totals["moh"]),
|
|
("CHI", source_totals["chi"]),
|
|
("Internal", source_totals["internal"]),
|
|
("Insurance Co.", source_totals["insurance"]),
|
|
("Total", source_totals["total"]),
|
|
]
|
|
for label, val in source_items:
|
|
_style_bold(ws, summary_row, 2, label)
|
|
_style_data(ws, summary_row, 3, val)
|
|
summary_row += 1
|
|
|
|
avg_row = summary_row + 1
|
|
_style_bold(ws, avg_row, 2, "Average Response Rate")
|
|
avg_row += 1
|
|
avg_resp = per_dept.get("avg_response_days", {})
|
|
for cat_label, cat_key, _ in cat_configs:
|
|
_style_data(ws, avg_row, 2, cat_label)
|
|
_style_data(ws, avg_row, 3, avg_resp.get(cat_key, 0), NUM_FMT)
|
|
avg_row += 1
|
|
|
|
for c in range(1, 40):
|
|
ws.column_dimensions[get_column_letter(c)].width = 12
|
|
for _, _, cs in cat_configs:
|
|
ws.column_dimensions[get_column_letter(cs)].width = 30
|
|
|
|
|
|
def _write_response_rate_sheet(wb, title, rate_data):
|
|
ws = wb.create_sheet(title)
|
|
_style_header(ws, 1, 1, "Timeline")
|
|
_style_header(ws, 1, 2, "Count")
|
|
_style_header(ws, 1, 3, "Rate")
|
|
|
|
buckets = ["24h", "48h", "72h", "72h+"]
|
|
for i, b in enumerate(buckets):
|
|
row = 2 + i
|
|
_style_data(ws, row, 1, b)
|
|
_style_data(ws, row, 2, rate_data["counts"].get(b, 0))
|
|
_style_data(ws, row, 3, rate_data["rates"].get(b, 0), PCT_FMT)
|
|
|
|
row = 6
|
|
_style_bold(ws, row, 1, "Total")
|
|
_style_data(ws, row, 2, rate_data["total"])
|
|
|
|
individual = rate_data.get("individual", [])
|
|
if individual:
|
|
row += 2
|
|
_style_header(ws, row, 1, "Reference")
|
|
_style_header(ws, row, 2, "Department")
|
|
_style_header(ws, row, 3, "Hours")
|
|
_style_header(ws, row, 4, "Bucket")
|
|
_style_header(ws, row, 5, "Status")
|
|
for i, item in enumerate(individual):
|
|
r = row + 1 + i
|
|
_style_data(ws, r, 1, item["ref"])
|
|
_style_data(ws, r, 2, item["dept"])
|
|
_style_data(ws, r, 3, item["hours"], NUM_FMT)
|
|
_style_data(ws, r, 4, item["bucket"])
|
|
_style_data(ws, r, 5, item["status"])
|
|
|
|
chart = PieChart()
|
|
chart.title = f"{title}"
|
|
data_ref = Reference(ws, min_col=2, min_row=1, max_row=5)
|
|
cats_ref = Reference(ws, min_col=1, min_row=2, max_row=5)
|
|
chart.add_data(data_ref, titles_from_data=True)
|
|
chart.set_categories(cats_ref)
|
|
ws.add_chart(chart, "G2")
|
|
|
|
ws.column_dimensions["A"].width = 18
|
|
ws.column_dimensions["B"].width = 30
|
|
ws.column_dimensions["C"].width = 12
|
|
ws.column_dimensions["D"].width = 14
|
|
ws.column_dimensions["E"].width = 12
|
|
|
|
|
|
def _write_per_dept_response_rate_sheets(wb, service):
|
|
dept_times = service.get_per_dept_response_times()
|
|
|
|
sheet_configs = [
|
|
("8.1 Response Rate Medical", ["medical"]),
|
|
("8.2 Response Rate Non-Medical", ["non_medical", "admin"]),
|
|
("8.3 RR Nursing&Support", ["nursing", "support"]),
|
|
]
|
|
|
|
for sheet_title, domain_types in sheet_configs:
|
|
ws = wb.create_sheet(sheet_title)
|
|
|
|
all_depts = {}
|
|
for dt in domain_types:
|
|
all_depts.update(dept_times.get(dt, {}))
|
|
|
|
if not all_depts:
|
|
continue
|
|
|
|
dept_names = sorted(all_depts.keys())
|
|
col = 2
|
|
for dept_name in dept_names:
|
|
cell = ws.cell(row=1, column=col, value=dept_name)
|
|
cell.font = HEADER_FONT
|
|
cell.fill = BLUE_FILL
|
|
cell.alignment = CENTER
|
|
cell.border = BORDER_ALL
|
|
ws.merge_cells(start_row=1, start_column=col, end_row=1, end_column=col)
|
|
|
|
cell2 = ws.cell(row=2, column=col)
|
|
cell2.value = "Time between processing and sending"
|
|
cell2.font = Font(name="Calibri", size=9, italic=True)
|
|
cell2.alignment = CENTER
|
|
cell2.border = BORDER_ALL
|
|
|
|
times = all_depts[dept_name]
|
|
for i, t in enumerate(times):
|
|
r = 3 + i
|
|
if t.get("status") == "Escalated":
|
|
_style_data(ws, r, col, "Escalated")
|
|
elif t.get("status") == "Closed":
|
|
_style_data(ws, r, col, "Closed")
|
|
else:
|
|
hours = t["hours"]
|
|
days = int(hours // 24)
|
|
remaining_hours = int(hours % 24)
|
|
td_str = f"{days}d {remaining_hours}h" if days > 0 else f"{int(hours)}h"
|
|
_style_data(ws, r, col, td_str)
|
|
|
|
col += 1
|
|
|
|
ws.column_dimensions["A"].width = 5
|
|
for c in range(2, col):
|
|
ws.column_dimensions[get_column_letter(c)].width = 22
|
|
|
|
|
|
def generate_complaint_quarterly_excel(service):
|
|
wb = Workbook()
|
|
wb.remove(wb.active)
|
|
|
|
_write_all_quarters_sheet(wb, service)
|
|
_write_kpi_sheet(wb, service)
|
|
_write_source_table_sheet(wb, service)
|
|
_write_escalated_sheet(wb, service)
|
|
_write_per_department_sheet(wb, service)
|
|
|
|
response_rates = service.get_response_rates()
|
|
_write_response_rate_sheet(wb, "5. Internal Response Rate", response_rates["internal"])
|
|
_write_response_rate_sheet(wb, "6. MOH Response Rate", response_rates["moh"])
|
|
_write_response_rate_sheet(wb, "7. CHI Response Rate", response_rates["chi"])
|
|
|
|
_write_per_dept_response_rate_sheets(wb, service)
|
|
|
|
buf = BytesIO()
|
|
wb.save(buf)
|
|
buf.seek(0)
|
|
return buf
|