Coverage for apps/appointments/reports/app_report.py: 3%
203 statements
« prev ^ index » next coverage.py v6.4.4, created at 2024-05-09 16:59 -0600
1import functools
2from io import BytesIO
4import xlsxwriter
5from django.core.files.base import ContentFile
7from apps.products.models import Product
9from ..models import Appointment, AppointmentCharge
12def create_appointment_report(instance: Appointment):
13 """
14 Reporte General
15 """
17 from_date = instance.from_date
18 until_date = instance.to_date
19 practitioner = instance.practitioner
20 organization = instance.organization
22 # Create a workbook and add a worksheet.
23 output = BytesIO()
24 workbook_name = f"Reporte General del {from_date} al {until_date}.xlsx"
25 workbook = xlsxwriter.Workbook(output)
26 worksheet_name = f"Por fecha"
27 worksheet = workbook.add_worksheet(worksheet_name)
29 # get the queryset for appointment charges
30 queryset = Appointment.objects.filter(
31 organization=instance.organization,
32 date__gte=instance.from_date,
33 date__lte=instance.to_date,
34 ).order_by("date", "start_timestamp")
36 if instance.practitioner is not None:
37 queryset.filter(practitioner__in=instance.owner_practitioners())
39 # Write image for report (row 1)
40 worksheet.set_row(0, 40)
41 worksheet.embed_image(0, 1, "./app/TualLogo.png")
43 # Write title of table (rows 2 and 3)
44 title_format = workbook.add_format(
45 {
46 "bold": True,
47 "font_color": "red",
48 "font_size": 30,
49 }
50 )
52 subtitle_format = workbook.add_format({"bold": True, "italic": True})
54 worksheet.merge_range(2, 1, 2, 3, "Reporte General", title_format)
55 worksheet.merge_range(3, 1, 3, 3, f"Del {from_date} al {until_date}", subtitle_format)
57 # Row 4 empty
59 # Write headers of table (row 5 and 6)
60 header_row_format = workbook.add_format({"bold": True, "italic": True, "bg_color": "#50C878"})
61 date_row_format = workbook.add_format(
62 {"num_format": "[$-x-sysdate]dddd, mmmm dd, yyyy", "align": "left", "bold": True, "bg_color": "#E2E2E2"}
63 )
64 date_row_money_format = workbook.add_format(
65 {
66 "num_format": "$#,##0.00",
67 "bg_color": "#E2E2E2",
68 "bold": True,
69 }
70 )
71 date_row_money_format_red = workbook.add_format(
72 {
73 "num_format": "$#,##0.00",
74 "bg_color": "#E2E2E2",
75 "bold": True,
76 "font_color": "red",
77 }
78 )
79 patient_row_time_format = workbook.add_format({"num_format": "h:mm AM/PM", "align": "center"})
80 patient_row_money_format = workbook.add_format({"num_format": "$#,##0.00"})
81 patient_row_money_format_red = workbook.add_format(
82 {
83 "num_format": "$#,##0.00",
84 "font_color": "red",
85 }
86 )
88 appointment_charge_row_money_format = workbook.add_format({"font_size": 8, "num_format": "$#,##0.00"})
89 appointment_charge_row_money_format_red = workbook.add_format(
90 {
91 "font_size": 8,
92 "num_format": "$#,##0.00",
93 "font_color": "red",
94 }
95 )
96 appointment_charge_row_text_format = workbook.add_format(
97 {
98 "font_size": 8,
99 }
100 )
102 start_row_for_headers = 5
104 worksheet.write(start_row_for_headers, 1, "Fecha", header_row_format)
105 worksheet.write(start_row_for_headers, 2, "Hora Consulta", header_row_format)
106 worksheet.write(start_row_for_headers, 3, "Tipo de Consulta", header_row_format)
107 worksheet.write(start_row_for_headers, 4, "Paciente", header_row_format)
108 worksheet.write(start_row_for_headers, 5, "Tipo de Servicio", header_row_format)
109 worksheet.write(start_row_for_headers, 6, "Servicio", header_row_format)
110 worksheet.write(start_row_for_headers, 7, "Cantidad", header_row_format)
111 worksheet.write(start_row_for_headers, 8, "Precio Unitario", header_row_format)
112 worksheet.write(start_row_for_headers, 9, "Venta", header_row_format)
113 worksheet.write(start_row_for_headers, 10, "Costo Consultorio", header_row_format)
114 worksheet.write(start_row_for_headers, 11, "Costo Proveedores", header_row_format)
115 worksheet.write(start_row_for_headers, 12, "Costo Total", header_row_format)
116 worksheet.write(start_row_for_headers, 13, "Utilidad", header_row_format)
117 worksheet.write(start_row_for_headers, 14, "Pagado", header_row_format)
118 worksheet.write(start_row_for_headers, 15, "Adeudado", header_row_format)
119 worksheet.write(start_row_for_headers, 16, "Facturación", header_row_format)
121 worksheet.set_column("K:N", None, None, {"level": 1, "hidden": False})
122 worksheet.set_column("O:O", None, None, {"collapsed": True})
123 worksheet.set_column(8, 15, 13)
125 # Start with the content
126 ##########
128 row = start_row_for_headers + 1
129 current_date = None
130 current_appointment = None
131 date_rows = []
132 patient_rows = []
133 date_totals_row_values = [0, 0, 0, 0, 0, 0, 0]
134 patient_totals_row_values = [0, 0, 0, 0, 0, 0, 0]
135 product_totals_row_values = {}
136 initial_date_row = row
137 initial_patient_row = row + 1
139 for appointment in queryset:
141 if appointment.date != current_date:
143 worksheet.write(
144 initial_date_row,
145 8,
146 "",
147 date_row_money_format,
148 )
149 worksheet.write(
150 initial_date_row,
151 9,
152 date_totals_row_values[0],
153 date_row_money_format,
154 )
155 worksheet.write(
156 initial_date_row,
157 10,
158 date_totals_row_values[1],
159 date_row_money_format,
160 )
161 worksheet.write(
162 initial_date_row,
163 11,
164 date_totals_row_values[2],
165 date_row_money_format,
166 )
167 worksheet.write(
168 initial_date_row,
169 12,
170 date_totals_row_values[3],
171 date_row_money_format,
172 )
173 worksheet.write(
174 initial_date_row,
175 13,
176 date_totals_row_values[4],
177 date_row_money_format,
178 )
179 worksheet.write(
180 initial_date_row,
181 14,
182 date_totals_row_values[5],
183 date_row_money_format,
184 )
185 worksheet.write(
186 initial_date_row,
187 15,
188 date_totals_row_values[6],
189 date_row_money_format_red,
190 )
192 date_totals_row_values = [0, 0, 0, 0, 0, 0, 0]
193 date_current_row_values = [0, 0, 0, 0, 0, 0, 0]
195 initial_date_row = row
196 date_rows.append(row)
197 worksheet.merge_range(row, 1, row, 7, appointment.date, date_row_format)
198 row += 1
199 current_appointment = None
200 patient_rows = []
202 if appointment != current_appointment:
204 worksheet.write(
205 initial_patient_row,
206 8,
207 "",
208 patient_row_money_format,
209 )
210 worksheet.write(
211 initial_patient_row,
212 9,
213 patient_totals_row_values[0],
214 patient_row_money_format,
215 )
216 worksheet.write(
217 initial_patient_row,
218 10,
219 patient_totals_row_values[1],
220 patient_row_money_format,
221 )
222 worksheet.write(
223 initial_patient_row,
224 11,
225 patient_totals_row_values[2],
226 patient_row_money_format,
227 )
228 worksheet.write(
229 initial_patient_row,
230 12,
231 patient_totals_row_values[3],
232 patient_row_money_format,
233 )
234 worksheet.write(
235 initial_patient_row,
236 13,
237 patient_totals_row_values[4],
238 patient_row_money_format,
239 )
240 worksheet.write(
241 initial_patient_row,
242 14,
243 patient_totals_row_values[5],
244 patient_row_money_format,
245 )
246 worksheet.write(
247 initial_patient_row,
248 15,
249 patient_totals_row_values[6],
250 patient_row_money_format_red,
251 )
252 patient_totals_row_values = [0, 0, 0, 0, 0, 0, 0]
253 patient_current_row_values = [0, 0, 0, 0, 0, 0, 0]
255 initial_patient_row = row
256 patient_rows.append(row)
257 worksheet.set_row(row, None, None, {"level": 1, "collapsed": True})
258 worksheet.write(row, 2, getattr(appointment, "start_time", "Sin Hora"), patient_row_time_format)
259 worksheet.write(row, 3, getattr(appointment.type_of, "name", ""), patient_row_time_format)
260 worksheet.merge_range(row, 4, row, 8, getattr(appointment.patient, "full_name", "Paciente sin Nombre"))
262 row += 1
264 for appointment_charge in appointment.charges.all():
266 worksheet.write(row, 5, appointment_charge.product._meta.verbose_name, appointment_charge_row_text_format)
267 worksheet.write(row, 6, appointment_charge.product.name, appointment_charge_row_text_format)
268 worksheet.write(row, 7, appointment_charge.quantity, appointment_charge_row_text_format)
269 worksheet.write(row, 8, appointment_charge.price.amount, appointment_charge_row_money_format)
270 worksheet.write(row, 9, appointment_charge.total_price.amount, appointment_charge_row_money_format)
271 worksheet.write(row, 10, appointment_charge.total_shared_cost.amount, appointment_charge_row_money_format)
272 worksheet.write(row, 11, appointment_charge.total_hard_cost.amount, appointment_charge_row_money_format)
273 worksheet.write(
274 row,
275 12,
276 appointment_charge.total_shared_cost.amount + appointment_charge.total_hard_cost.amount,
277 appointment_charge_row_money_format,
278 )
279 worksheet.write(
280 row,
281 13,
282 appointment_charge.total_price.amount
283 - (appointment_charge.total_shared_cost.amount + appointment_charge.total_hard_cost.amount),
284 appointment_charge_row_money_format,
285 )
286 worksheet.write(row, 14, appointment_charge.paid_amount.amount, appointment_charge_row_money_format)
287 worksheet.write(row, 15, appointment_charge.pending_amount.amount, appointment_charge_row_money_format_red)
288 worksheet.write(row, 16, appointment_charge.pk)
289 worksheet.set_row(row, None, None, {"level": 2, "hidden": False})
291 patient_current_row_values = [
292 appointment_charge.total_price.amount,
293 appointment_charge.total_shared_cost.amount,
294 appointment_charge.total_hard_cost.amount,
295 appointment_charge.total_shared_cost.amount + appointment_charge.total_hard_cost.amount,
296 appointment_charge.total_price.amount
297 - (appointment_charge.total_shared_cost.amount + appointment_charge.total_hard_cost.amount),
298 appointment_charge.paid_amount.amount,
299 appointment_charge.pending_amount.amount,
300 ]
301 patient_totals_row_values = list(
302 map(lambda x, y: x + y, patient_totals_row_values, patient_current_row_values)
303 )
305 date_current_row_values = [
306 appointment_charge.total_price.amount,
307 appointment_charge.total_shared_cost.amount,
308 appointment_charge.total_hard_cost.amount,
309 appointment_charge.total_shared_cost.amount + appointment_charge.total_hard_cost.amount,
310 appointment_charge.total_price.amount
311 - (appointment_charge.total_shared_cost.amount + appointment_charge.total_hard_cost.amount),
312 appointment_charge.paid_amount.amount,
313 appointment_charge.pending_amount.amount,
314 ]
315 date_totals_row_values = list(map(lambda x, y: x + y, date_totals_row_values, date_current_row_values))
317 product_current_row_values = [
318 appointment_charge.quantity,
319 appointment_charge.total_price.amount,
320 appointment_charge.total_shared_cost.amount,
321 appointment_charge.total_hard_cost.amount,
322 appointment_charge.total_shared_cost.amount + appointment_charge.total_hard_cost.amount,
323 appointment_charge.total_price.amount
324 - (appointment_charge.total_shared_cost.amount + appointment_charge.total_hard_cost.amount),
325 appointment_charge.paid_amount.amount,
326 appointment_charge.pending_amount.amount,
327 ]
329 product_totals_row_values[appointment_charge.product.pk] = list(
330 map(
331 lambda x, y: x + y,
332 product_totals_row_values.setdefault(appointment_charge.product.pk, [0, 0, 0, 0, 0, 0, 0, 0]),
333 product_current_row_values,
334 )
335 )
337 row += 1
338 current_date = appointment_charge.appointment.date
339 current_appointment = appointment_charge.appointment
341 worksheet.write(
342 initial_date_row,
343 8,
344 "",
345 date_row_money_format,
346 )
347 worksheet.write(
348 initial_date_row,
349 9,
350 date_totals_row_values[0],
351 date_row_money_format,
352 )
353 worksheet.write(
354 initial_date_row,
355 10,
356 date_totals_row_values[1],
357 date_row_money_format,
358 )
359 worksheet.write(
360 initial_date_row,
361 11,
362 date_totals_row_values[2],
363 date_row_money_format,
364 )
365 worksheet.write(
366 initial_date_row,
367 12,
368 date_totals_row_values[3],
369 date_row_money_format,
370 )
371 worksheet.write(
372 initial_date_row,
373 13,
374 date_totals_row_values[4],
375 date_row_money_format,
376 )
377 worksheet.write(
378 initial_date_row,
379 14,
380 date_totals_row_values[5],
381 date_row_money_format,
382 )
383 worksheet.write(
384 initial_date_row,
385 15,
386 date_totals_row_values[6],
387 date_row_money_format_red,
388 )
390 worksheet.write(
391 initial_patient_row,
392 8,
393 "",
394 patient_row_money_format,
395 )
397 worksheet.write(
398 initial_patient_row,
399 9,
400 patient_totals_row_values[0],
401 patient_row_money_format,
402 )
403 worksheet.write(
404 initial_patient_row,
405 10,
406 patient_totals_row_values[1],
407 patient_row_money_format,
408 )
409 worksheet.write(
410 initial_patient_row,
411 11,
412 patient_totals_row_values[2],
413 patient_row_money_format,
414 )
415 worksheet.write(
416 initial_patient_row,
417 12,
418 patient_totals_row_values[3],
419 patient_row_money_format,
420 )
421 worksheet.write(
422 initial_patient_row,
423 13,
424 patient_totals_row_values[4],
425 patient_row_money_format,
426 )
427 worksheet.write(
428 initial_patient_row,
429 14,
430 patient_totals_row_values[5],
431 patient_row_money_format,
432 )
433 worksheet.write(
434 initial_patient_row,
435 15,
436 patient_totals_row_values[6],
437 patient_row_money_format_red,
438 )
440 # Resumen por tipo de productos
441 category_row_format = workbook.add_format(
442 {
443 # "bg_color": "#8A39E3",
444 "bg_color": "#9FC095",
445 "bold": True,
446 }
447 )
448 category_row_money_format = workbook.add_format(
449 {
450 "num_format": "$#,##0.00",
451 # "bg_color": "#8A39E3",
452 "bg_color": "#9FC095",
453 "bold": True,
454 }
455 )
456 category_row_money_format_red = workbook.add_format(
457 {
458 "num_format": "$#,##0.00",
459 # "bg_color": "#8A39E3",
460 "bg_color": "#9FC095",
461 "font_color": "red",
462 "bold": True,
463 }
464 )
466 product_row_money_format = workbook.add_format(
467 {
468 "num_format": "$#,##0.00",
469 }
470 )
471 product_row_money_format_red = workbook.add_format(
472 {
473 "num_format": "$#,##0.00",
474 "font_color": "red",
475 }
476 )
477 product_row_format = workbook.add_format({})
478 row += 5
479 current_category = None
480 current_product = None
481 category_rows = []
482 category_totals_row_values = [0, 0, 0, 0, 0, 0, 0, 0]
483 initial_category_row = row
485 new_category_dict = {}
487 for product_pk, product_totals_list in product_totals_row_values.items():
489 product = Product.objects.get(pk=product_pk)
490 category = product._meta.verbose_name_plural
492 if not category in new_category_dict:
493 new_category_dict[category] = {}
495 new_category_dict[category][product_pk] = product_totals_list
497 for category, products in new_category_dict.items():
499 if category != current_category:
500 current_category = category
502 worksheet.write(
503 initial_category_row,
504 7,
505 category_totals_row_values[0],
506 category_row_format,
507 )
509 worksheet.write(
510 initial_category_row,
511 8,
512 "",
513 category_row_money_format,
514 )
516 worksheet.write(
517 initial_category_row,
518 9,
519 category_totals_row_values[1],
520 category_row_money_format,
521 )
522 worksheet.write(
523 initial_category_row,
524 10,
525 category_totals_row_values[2],
526 category_row_money_format,
527 )
528 worksheet.write(
529 initial_category_row,
530 11,
531 category_totals_row_values[3],
532 category_row_money_format,
533 )
534 worksheet.write(
535 initial_category_row,
536 12,
537 category_totals_row_values[4],
538 category_row_money_format,
539 )
540 worksheet.write(
541 initial_category_row,
542 13,
543 category_totals_row_values[5],
544 category_row_money_format,
545 )
546 worksheet.write(
547 initial_category_row,
548 14,
549 category_totals_row_values[6],
550 category_row_money_format,
551 )
552 worksheet.write(
553 initial_category_row,
554 15,
555 category_totals_row_values[7],
556 category_row_money_format_red,
557 )
559 category_totals_row_values = [0, 0, 0, 0, 0, 0, 0, 0]
560 initial_category_row = row
561 worksheet.merge_range(row, 1, row, 6, category, category_row_format)
562 row += 1
564 for product_pk, product_totals_list in products.items():
565 product = Product.objects.get(pk=product_pk)
567 worksheet.write(row, 3, product.name, product_row_format)
568 worksheet.write(row, 7, product_totals_list[0], product_row_format)
569 worksheet.write(row, 9, product_totals_list[1], product_row_money_format)
570 worksheet.write(row, 10, product_totals_list[2], product_row_money_format)
571 worksheet.write(row, 11, product_totals_list[3], product_row_money_format)
572 worksheet.write(row, 12, product_totals_list[4], product_row_money_format)
573 worksheet.write(row, 13, product_totals_list[5], product_row_money_format)
574 worksheet.write(row, 14, product_totals_list[6], product_row_money_format)
575 worksheet.write(row, 15, product_totals_list[7], product_row_money_format_red)
577 category_totals_row_values = list(map(lambda x, y: x + y, category_totals_row_values, product_totals_list))
579 row += 1
581 worksheet.write(
582 initial_category_row,
583 7,
584 category_totals_row_values[0],
585 category_row_format,
586 )
587 worksheet.write(
588 initial_category_row,
589 8,
590 "",
591 category_row_money_format,
592 )
593 worksheet.write(
594 initial_category_row,
595 9,
596 category_totals_row_values[1],
597 category_row_money_format,
598 )
599 worksheet.write(
600 initial_category_row,
601 10,
602 category_totals_row_values[2],
603 category_row_money_format,
604 )
605 worksheet.write(
606 initial_category_row,
607 11,
608 category_totals_row_values[3],
609 category_row_money_format,
610 )
611 worksheet.write(
612 initial_category_row,
613 12,
614 category_totals_row_values[4],
615 category_row_money_format,
616 )
617 worksheet.write(
618 initial_category_row,
619 13,
620 category_totals_row_values[5],
621 category_row_money_format,
622 )
623 worksheet.write(
624 initial_category_row,
625 14,
626 category_totals_row_values[6],
627 category_row_money_format,
628 )
629 worksheet.write(
630 initial_category_row,
631 15,
632 category_totals_row_values[7],
633 category_row_money_format_red,
634 )
636 worksheet.autofit()
638 workbook.close()
639 xlsx_data = output.getvalue()
641 xlsx_file = ContentFile(xlsx_data)
643 instance.export_file.save(workbook_name, xlsx_file)
644 instance.save()