class ExportTherapyBookingsExcelHTML(generic.View):
def get(self, request, *args, **kwargs):
start_date = request.GET.get('start_date')
end_date = request.GET.get('end_date')
single_date = request.GET.get('date')
format_type = request.GET.get('format', 'vertical')
if not single_date and not start_date:
return HttpResponse("Provide date OR start_date & end_date", status=400)
all_bookings = []
# Data Fetching Logic
if single_date:
data, *_ = get_therapy_bookings_by_date(single_date, user=request.user)
all_bookings.extend(data)
else:
try:
start = datetime.strptime(start_date, "%Y-%m-%d")
end = datetime.strptime(end_date, "%Y-%m-%d")
while start <= end:
date_str = start.strftime("%Y-%m-%d")
data, *_ = get_therapy_bookings_by_date(date_str, user=request.user)
all_bookings.extend(data)
start += timedelta(days=1)
except ValueError:
return HttpResponse("Invalid date format. Use YYYY-MM-DD", status=400)
def clean(val):
if val is None:
return ''
return str(val).replace('\n', ' ').strip()
def get_date_time(dt_val):
"""Splits '2023-10-25 14:30:00' into ('2023-10-25', '14:30:00')"""
if not dt_val: return '', ''
dt_str = str(dt_val).strip()
if 'T' in dt_str:
parts = dt_str.split('T')
return parts[0], parts[1][:8]
elif ' ' in dt_str:
parts = dt_str.split(' ', 1)
return parts[0], parts[1][:8]
return dt_str, ''
# CSS optimized for Excel's HTML renderer
html = """
<html>
<head>
<meta charset="utf-8">
<style>
body { font-family: 'Calibri', 'Arial', sans-serif; }
table { border-collapse: collapse; width: 100%; font-size: 11pt; }
th, td {
border: 1px solid #BFBFBF;
padding: 6px 8px;
vertical-align: middle;
text-align: left;
}
.parent-header { background-color: #244061; color: #FFFFFF; font-weight: bold; }
.child-header { background-color: #DCE6F1; color: #17365D; font-weight: bold; }
/* Force Excel to treat cell as text to prevent scientific notation and date errors */
.text-format { mso-number-format: "\@"; white-space: nowrap; }
.data-row td { color: #000000; }
</style>
</head>
<body>
<table>
"""
if format_type == 'horizontal':
# TABLE HEADERS
html += """
<tr>
<th class="parent-header">Date</th><th class="parent-header">Time</th>
<th class="parent-header">Name</th><th class="parent-header">Therapist</th>
<th class="parent-header">Title</th><th class="parent-header">Sessions</th>
<th class="parent-header">Med. Sent Days</th><th class="parent-header">Age</th>
<th class="parent-header">Address</th><th class="parent-header">Accepted Time</th>
<th class="parent-header">T|C|C|S</th><th class="parent-header">Invoice</th>
<th class="parent-header">Amount</th><th class="parent-header">Commission</th>
<th class="parent-header">Payout</th><th class="parent-header">Status</th>
<th class="parent-header">Location</th>
<th class="child-header">Session No</th><th class="child-header">Appointment Time</th>
<th class="child-header">Duration</th><th class="child-header">Medicines</th>
<th class="child-header">Medicines Sent</th><th class="child-header">Pre Notes</th>
<th class="child-header">Post Notes</th><th class="child-header">Session Status</th>
<th class="child-header">Reason</th><th class="child-header">Phone No.</th>
<th class="child-header">Therapist Name</th><th class="child-header">Med Img</th>
<th class="child-header">Med Picked At</th><th class="child-header">Therapy Start</th>
<th class="child-header">Therapy End</th><th class="child-header">Pay Amt</th>
<th class="child-header">Pay Status</th><th class="child-header">Comm.</th>
<th class="child-header">Payout Amt</th><th class="child-header">Payout Status</th>
</tr>
"""
for booking in all_bookings:
b_date, b_time = get_date_time(booking.get('start_time'))
for tb in booking.get('therapy_bookings', []):
# Prepare Parent Row segment
p_seg = [
clean(b_date), clean(b_time), clean(tb.get('patient_name')),
clean(tb.get('today_therapist_name')), clean(tb.get('therapy_title_with_duration')),
clean(tb.get('cur_session_by_total_sessions')).replace(" ", ""),
clean(tb.get('medicine_sent_for_days')), clean(tb.get('patient_age')),
clean(f"{tb.get('address_flat_no_building', '')}, {tb.get('address_city', '')}"),
clean(tb.get('treatment_accepted_at')),
clean(f"{tb.get('is_bed_needed')} | {tb.get('is_steam_needed')}"),
clean(tb.get('invoice_url')), clean(tb.get('total_amount')),
clean(tb.get('therapist_total_commission')), clean(tb.get('total_payout_amount')),
clean(tb.get('payment_status')), clean(tb.get('location'))
]
for s in tb.get('sessions', []):
if s.get('status') == 'INACTIVE': continue
s_seg = [
clean(s.get('session_no')), clean(s.get('scheduled_appointment_start_time')),
clean(s.get('duration')), clean(s.get('session_description')),
clean(s.get('medicine_sent_uploaded')), clean(s.get('pre_therapy_notes')),
clean(s.get('post_therapy_notes')), clean(s.get('status')),
clean(s.get('cancel_reason') or 'N/A'), clean(s.get('therapist_phone_number')),
clean(s.get('therapist_name')), "Available", clean(s.get('med_picked_at')),
clean(s.get('start_qr_token_session_validated_at') or s.get(
'start_otp_token_session_validated_at')),
clean(s.get('end_qr_token_session_validated_at') or s.get(
'end_otp_token_session_validated_at')),
clean(s.get('payment_amount')), clean(s.get('payment_status')),
clean(s.get('therapist_commission')), clean(s.get('payout_amount')),
clean(s.get('payout_status'))
]
full_row = p_seg + s_seg
html += "<tr class='data-row'>"
for idx, val in enumerate(full_row):
if idx in [30, 31] and val:
try:
if isinstance(val, str):
val = datetime.fromisoformat(val)
val = val.strftime("%Y-%m-%d %H:%M")
except:
pass
if idx == 16:
css = ' style="mso-number-format:\\@;"'
else:
css = ' class="text-format"' if idx in [5, 26, 30, 31] else ''
html += f"<td{css}>{val}</td>"
html += "</tr>"
else:
# VERTICAL LAYOUT
for booking in all_bookings:
b_date, b_time = get_date_time(booking.get('start_time'))
for tb in booking.get('therapy_bookings', []):
# Header for Parent
html += '<tr class="parent-header"><th>Date</th><th>Time</th><th>Name</th><th>Therapist</th><th>Title</th><th>Sessions</th><th>Med Sent</th><th>Age</th><th>Address</th><th>Accepted</th><th>T|C|C|S</th><th>Invoice</th><th>Amt</th><th>Comm</th><th>Payout</th><th>Status</th><th>Location</th></tr>'
sess_str = clean(tb.get('cur_session_by_total_sessions')).replace(" ", "")
html += f"""
<tr class="data-row">
<td>{clean(b_date)}</td><td>{clean(b_time)}</td><td>{clean(tb.get('patient_name'))}</td>
<td>{clean(tb.get('today_therapist_name'))}</td><td>{clean(tb.get('therapy_title_with_duration'))}</td>
<td class="text-format">{sess_str}</td><td>{clean(tb.get('medicine_sent_for_days'))}</td>
<td>{clean(tb.get('patient_age'))}</td><td>{clean(tb.get('address_city', ''))}</td>
<td>{clean(tb.get('treatment_accepted_at'))}</td><td>{clean(tb.get('is_bed_needed'))}</td>
<td>{clean(tb.get('invoice_url'))}</td><td>{clean(tb.get('total_amount'))}</td>
<td>{clean(tb.get('therapist_total_commission'))}</td><td>{clean(tb.get('total_payout_amount'))}</td>
<td>{clean(tb.get('payment_status'))}</td><td>{clean(tb.get('total_payout_amount'))}</td>
</tr>
"""
# Header for Sessions
html += '<tr class="child-header"><th>Sess No</th><th>Appointment Time</th><th>Duration</th><th>Medicines</th><th>Medicines Sent</th><th>Pre Notes</th><th>Post Notes</th><th>Status</th><th>Reason</th><th>Phone No.</th><th>Name</th><th>Med Img</th><th>Med Picked</th><th>Therapy Start</th><th>Therapy End</th><th>Payment Amt</th><th>Payment Status</th><th>Commission</th><th>Payout</th><th>Status</th></tr>'
for s in tb.get('sessions', []):
if s.get('status') == 'INACTIVE': continue
ph_no = clean(s.get('therapist_phone_number'))
t_start = clean(s.get('start_qr_token_session_validated_at') or s.get(
'start_otp_token_session_validated_at'))
t_end = clean(
s.get('end_qr_token_session_validated_at') or s.get('end_otp_token_session_validated_at'))
html += f"""
<tr class="data-row">
<td>{clean(s.get('session_no'))}</td><td>{clean(s.get('scheduled_appointment_start_time'))}</td>
<td>{clean(s.get('duration'))}</td><td>{clean(s.get('session_description'))}</td>
<td>{clean(s.get('medicine_sent_uploaded'))}</td><td>{clean(s.get('pre_therapy_notes'))}</td>
<td>{clean(s.get('post_therapy_notes'))}</td><td>{clean(s.get('status'))}</td>
<td>{clean(s.get('cancel_reason') or 'N/A')}</td><td class="text-format">{ph_no}</td>
<td>{clean(s.get('therapist_name'))}</td><td>Available</td><td>{clean(s.get('med_picked_at'))}</td>
<td class="text-format">{t_start}</td><td class="text-format">{t_end}</td>
<td>{clean(s.get('payment_amount'))}</td><td>{clean(s.get('payment_status'))}</td>
<td>{clean(s.get('therapist_commission'))}</td><td>{clean(s.get('payout_amount'))}</td><td>{clean(s.get('payout_status'))}</td>
</tr>
"""
html += "</table></body></html>"
response = HttpResponse(content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=\"therapy_bookings.xls\"'
response.write(html.encode('utf-8'))
return response
Comments
Post a Comment