302 lines
10 KiB
Python
302 lines
10 KiB
Python
import calendar
|
|
from io import BytesIO
|
|
|
|
from openpyxl import Workbook
|
|
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")
|
|
HEADER_FONT = Font(name="Calibri", size=11, bold=True, color="FFFFFF")
|
|
DATA_FONT = Font(name="Calibri", size=10)
|
|
HEADER_ALIGN = Alignment(horizontal="center", vertical="center", wrap_text=True)
|
|
DATA_ALIGN = Alignment(horizontal="center", vertical="center", wrap_text=True)
|
|
RTL_ALIGN = Alignment(horizontal="right", vertical="center", wrap_text=True)
|
|
THIN = Side(style="thin")
|
|
THIN_BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
|
|
|
|
VERT_MERGE_HEADERS = [
|
|
(2, "No."),
|
|
(3, "رقم الشكوى"),
|
|
(4, "رقم الملف"),
|
|
(5, "جهة الشكوى"),
|
|
(6, "الموقع"),
|
|
(7, "القسم الرئيس"),
|
|
(8, "القسم الفرعي"),
|
|
(9, "تاريخ إستلام الشكوى"),
|
|
(10, "المدخل"),
|
|
(14, "رقم الجوال"),
|
|
(15, "Time line"),
|
|
(18, "Employee"),
|
|
(23, "Employee"),
|
|
(26, "Employee"),
|
|
(27, "الوقت بين التحرير والارسال"),
|
|
(30, "Employee"),
|
|
(31, "الوقت بين اول ايميل والارسال"),
|
|
(34, "Employee"),
|
|
(35, "الوقت بين ثاني ايميل والاول"),
|
|
(38, "Employee"),
|
|
(39, "الوقت بين التصعيد والارسال"),
|
|
(42, "Employee"),
|
|
(43, "الوقت بين الاغلاق والارسال"),
|
|
(46, "Employee"),
|
|
(47, "الوقت بين المعالجة و الارسال"),
|
|
(50, "الوقت بين الرد والارسال"),
|
|
(51, "اسم الشخص المشتكى عليه"),
|
|
(52, "موضوع الشكوى الأساسية"),
|
|
(53, "الشكوى باختصار (عربي)"),
|
|
(54, "الشكوى باختصار English"),
|
|
(55, "توثيق تذكيرات للقسم"),
|
|
(56, "تاريخ التذكير"),
|
|
(57, "سبب تأخير القسم بالرد"),
|
|
(58, "سبب تأخير اغلاق الشكوى خلال 72 ساعه"),
|
|
(59, "الشخص المتأخر"),
|
|
(60, "Satisfied/Dissatisfied"),
|
|
(61, "الاجراء المتخذ من قبل القسم المعني"),
|
|
(62, "نتيجة الاجراء المتخذ بعد التحقيق"),
|
|
(63, "حلول واقتراحات"),
|
|
(64, "Recommendation/Action plan"),
|
|
(65, "Responsible Department"),
|
|
(66, "The Rightful Side"),
|
|
(67, "PR - Observations / Patient Supervisor"),
|
|
]
|
|
|
|
HORIZ_MERGE_HEADERS = [
|
|
(11, 13, "MOH & CHI"),
|
|
(16, 17, "إرسال نموذج الشكوى"),
|
|
(19, 20, "تحرير الشكوى"),
|
|
(21, 22, "تفعيل الشكوى"),
|
|
(24, 25, "تم ارسال الشكوى"),
|
|
(28, 29, "First Reminder Sent"),
|
|
(32, 33, "Second Reminder Sent"),
|
|
(36, 37, "Escalated"),
|
|
(40, 41, "Closed"),
|
|
(44, 45, "Resolved"),
|
|
(48, 49, "تاريخ الرد"),
|
|
]
|
|
|
|
DATE_TIME_SUBS = {
|
|
11: ("Date", "Time"),
|
|
16: ("Date", "Time"),
|
|
19: ("Date", "Time"),
|
|
21: ("Date", "Time"),
|
|
24: ("Date", "Time"),
|
|
28: ("Date", "Time"),
|
|
32: ("Date", "Time"),
|
|
36: ("Date", "Time"),
|
|
40: ("Date", "Time"),
|
|
44: ("Date", "Time"),
|
|
48: ("Date", "Time"),
|
|
}
|
|
|
|
|
|
def _dt(v):
|
|
return v.date() if v else ""
|
|
|
|
|
|
def _tm(v):
|
|
return v.strftime("%H:%M") if v else ""
|
|
|
|
|
|
def _write_headers(ws):
|
|
cell = ws.cell(row=1, column=1, value="Week")
|
|
cell.font = HEADER_FONT
|
|
cell.fill = BLUE_FILL
|
|
cell.alignment = HEADER_ALIGN
|
|
cell.border = THIN_BORDER
|
|
ws.merge_cells(start_row=1, start_column=1, end_row=2, end_column=1)
|
|
|
|
for col, label in VERT_MERGE_HEADERS:
|
|
cell = ws.cell(row=1, column=col, value=label)
|
|
cell.font = HEADER_FONT
|
|
cell.fill = BLUE_FILL
|
|
cell.alignment = HEADER_ALIGN
|
|
cell.border = THIN_BORDER
|
|
ws.merge_cells(start_row=1, start_column=col, end_row=2, end_column=col)
|
|
bc = ws.cell(row=2, column=col)
|
|
bc.font = HEADER_FONT
|
|
bc.fill = BLUE_FILL
|
|
bc.border = THIN_BORDER
|
|
|
|
for start_col, end_col, label in HORIZ_MERGE_HEADERS:
|
|
cell = ws.cell(row=1, column=start_col, value=label)
|
|
cell.font = HEADER_FONT
|
|
cell.fill = BLUE_FILL
|
|
cell.alignment = HEADER_ALIGN
|
|
cell.border = THIN_BORDER
|
|
ws.merge_cells(start_row=1, start_column=start_col, end_row=1, end_column=end_col)
|
|
for c in range(start_col + 1, end_col + 1):
|
|
bc = ws.cell(row=1, column=c)
|
|
bc.font = HEADER_FONT
|
|
bc.fill = BLUE_FILL
|
|
bc.border = THIN_BORDER
|
|
|
|
date_l, time_l = DATE_TIME_SUBS.get(start_col, ("Date", "Time"))
|
|
dc = ws.cell(row=2, column=start_col, value=date_l)
|
|
dc.font = HEADER_FONT
|
|
dc.fill = BLUE_FILL
|
|
dc.alignment = HEADER_ALIGN
|
|
dc.border = THIN_BORDER
|
|
tc = ws.cell(row=2, column=end_col, value=time_l)
|
|
tc.font = HEADER_FONT
|
|
tc.fill = BLUE_FILL
|
|
tc.alignment = HEADER_ALIGN
|
|
tc.border = THIN_BORDER
|
|
|
|
|
|
def _write_data_rows(ws, queryset):
|
|
complaints = list(queryset.order_by("created_at"))
|
|
if not complaints:
|
|
return
|
|
|
|
weeks = {}
|
|
for idx, c in enumerate(complaints, 1):
|
|
day = c.created_at.day
|
|
if day <= 7:
|
|
wk = 1
|
|
elif day <= 15:
|
|
wk = 2
|
|
elif day <= 22:
|
|
wk = 3
|
|
elif day <= 28:
|
|
wk = 4
|
|
else:
|
|
wk = 5
|
|
weeks[idx] = wk
|
|
|
|
row_num = 3
|
|
current_week = None
|
|
week_start_row = None
|
|
|
|
for idx, c in enumerate(complaints, 1):
|
|
wk = weeks[idx]
|
|
|
|
if wk != current_week:
|
|
if current_week is not None and week_start_row is not None and row_num - 1 > week_start_row:
|
|
ws.merge_cells(start_row=week_start_row, start_column=1, end_row=row_num - 1, end_column=1)
|
|
cell = ws.cell(row=row_num, column=1, value=f"Wk {wk}")
|
|
cell.font = DATA_FONT
|
|
cell.alignment = Alignment(horizontal="center", vertical="center")
|
|
cell.border = THIN_BORDER
|
|
current_week = wk
|
|
week_start_row = row_num
|
|
|
|
inv_dept = None
|
|
for id_obj in c.involved_departments.all():
|
|
if id_obj.is_primary:
|
|
inv_dept = id_obj
|
|
break
|
|
if inv_dept is None and c.involved_departments.exists():
|
|
inv_dept = c.involved_departments.first()
|
|
|
|
satisfaction = ""
|
|
if c.satisfaction:
|
|
satisfaction = c.satisfaction.replace("_", " ").title()
|
|
|
|
resolution_outcome = ""
|
|
if c.resolution_outcome:
|
|
resolution_outcome = c.resolution_outcome.replace("_", " ").title()
|
|
|
|
vals = [
|
|
idx,
|
|
c.reference_number or "",
|
|
c.file_number or "",
|
|
c.source.name_en if c.source else (c.complaint_source_type or ""),
|
|
c.location.name if c.location else "",
|
|
c.domain.name_en if c.domain else "",
|
|
c.category.name_en if c.category else "",
|
|
c.created_at,
|
|
c.created_by.get_full_name() if c.created_by else "",
|
|
c.moh_reference_date, "", "",
|
|
c.contact_phone or "",
|
|
c.due_at,
|
|
_dt(c.form_sent_at), _tm(c.form_sent_at),
|
|
c.created_by.get_full_name() if c.created_by else "",
|
|
_dt(c.created_at), _tm(c.created_at),
|
|
_dt(c.activated_at), _tm(c.activated_at),
|
|
c.assigned_to.get_full_name() if c.assigned_to else "",
|
|
_dt(c.forwarded_to_dept_at), _tm(c.forwarded_to_dept_at),
|
|
c.assigned_to.get_full_name() if c.assigned_to else "",
|
|
"",
|
|
_dt(c.reminder_sent_at), _tm(c.reminder_sent_at),
|
|
"",
|
|
"",
|
|
_dt(c.second_reminder_sent_at), _tm(c.second_reminder_sent_at),
|
|
"",
|
|
"",
|
|
_dt(c.escalated_at), _tm(c.escalated_at),
|
|
"",
|
|
"",
|
|
_dt(c.closed_at), _tm(c.closed_at),
|
|
c.closed_by.get_full_name() if c.closed_by else "",
|
|
"",
|
|
_dt(c.resolved_at), _tm(c.resolved_at),
|
|
c.resolved_by.get_full_name() if c.resolved_by else "",
|
|
"",
|
|
_dt(c.response_date) if c.response_date else "", "",
|
|
"",
|
|
c.staff_name or "",
|
|
c.complaint_subject or c.title or "",
|
|
c.ai_brief_ar or "",
|
|
c.ai_brief_en or "",
|
|
inv_dept.response_notes if inv_dept else "",
|
|
inv_dept.first_reminder_sent_at if inv_dept else "",
|
|
inv_dept.delay_reason if inv_dept else c.explanation_delay_reason or "",
|
|
c.delay_reason_closure or "",
|
|
inv_dept.delayed_person if inv_dept else "",
|
|
satisfaction,
|
|
c.action_taken_by_dept or "",
|
|
c.action_result or "",
|
|
c.recommendation_action_plan or "",
|
|
c.recommendation_action_plan or "",
|
|
c.department.name if c.department else "",
|
|
resolution_outcome,
|
|
"",
|
|
]
|
|
|
|
for col, val in enumerate(vals, 2):
|
|
cell = ws.cell(row=row_num, column=col, value=val)
|
|
cell.font = DATA_FONT
|
|
cell.alignment = RTL_ALIGN if col in (53, 54, 61, 62, 63, 64) else DATA_ALIGN
|
|
cell.border = THIN_BORDER
|
|
|
|
row_num += 1
|
|
|
|
if week_start_row and row_num - 1 > week_start_row:
|
|
ws.merge_cells(start_row=week_start_row, start_column=1, end_row=row_num - 1, end_column=1)
|
|
|
|
|
|
def _set_col_widths(ws):
|
|
ws.column_dimensions[get_column_letter(1)].width = 8
|
|
ws.column_dimensions[get_column_letter(2)].width = 5
|
|
for c in range(3, 68):
|
|
w = 14
|
|
if c in (9,):
|
|
w = 16
|
|
elif c in (52, 53, 54, 61, 62, 63, 64):
|
|
w = 35
|
|
elif c in (13, 15):
|
|
w = 14
|
|
ws.column_dimensions[get_column_letter(c)].width = w
|
|
ws.freeze_panes = "C3"
|
|
|
|
|
|
def generate_complaint_monthly_excel(service):
|
|
queryset = service._base_qs().prefetch_related(
|
|
"involved_departments__department",
|
|
)
|
|
month_label = f"{service.year}-{service.month:02d}"
|
|
month_abbr = calendar.month_abbr[service.month].upper()
|
|
|
|
wb = Workbook()
|
|
ws = wb.active
|
|
ws.title = month_abbr
|
|
|
|
_write_headers(ws)
|
|
_write_data_rows(ws, queryset)
|
|
_set_col_widths(ws)
|
|
|
|
buf = BytesIO()
|
|
wb.save(buf)
|
|
buf.seek(0)
|
|
return buf
|