Coverage for apps/appointments/reports/app_charge_report.py: 5%
138 statements
« prev ^ index » next coverage.py v6.4.4, created at 2024-04-25 14:08 -0600
1import functools
2from io import BytesIO
4import xlsxwriter
5from django.core.files.base import ContentFile
6from django.db.models import F
8from ..models import Appointment, AppointmentCharge
11def create_appointment_charge_report(instance: AppointmentCharge):
12 """
13 Reporte de Adeudos
14 """
16 practitioner = instance.practitioner
17 organization = instance.organization
19 # Create a workbook and add a worksheet.
20 output = BytesIO()
21 workbook_name = f"Reporte de Adeudos al {instance.created_at}.xlsx"
22 workbook = xlsxwriter.Workbook(output)
23 worksheet_name = f"Por fecha"
24 worksheet = workbook.add_worksheet(worksheet_name)
26 worksheet.set_column(0, 0, 2)
27 worksheet.set_column(1, 1, 15)
28 worksheet.set_column(2, 2, 8)
29 worksheet.set_column(3, 3, 12)
31 # get the queryset for appointment charges
32 queryset = AppointmentCharge.objects.filter(
33 appointment__organization=instance.organization,
34 # pending_amount__gt=0,
35 ).order_by("appointment__date", "appointment__start_time", "appointment__patient")
37 if instance.practitioner is not None:
38 queryset.filter(practitioner=instance.practitioner)
40 # Write image for report (row 1)
41 worksheet.set_row(0, 40)
42 worksheet.embed_image(0, 1, "./app/TualLogo.png")
44 # Write title of table (rows 2 and 3)
45 title_format = workbook.add_format(
46 {
47 "bold": True,
48 "font_color": "red",
49 "font_size": 30,
50 }
51 )
53 subtitle_format = workbook.add_format({"bold": True, "italic": True})
55 worksheet.merge_range(2, 1, 2, 3, "Reporte de Adeudos", title_format)
56 worksheet.merge_range(3, 1, 3, 3, f"Fecha de Reporte: {instance.created_at}", subtitle_format)
58 # Row 4 empty
60 # Write headers of table (row 5 and 6)
61 header_row_format = workbook.add_format({"bold": True, "italic": True, "bg_color": "#FF6600"})
62 date_row_format = workbook.add_format(
63 {"num_format": "[$-x-sysdate]dddd, mmmm dd, yyyy", "align": "left", "bold": True, "bg_color": "#E2E2E2"}
64 )
65 date_row_money_format = workbook.add_format(
66 {
67 "num_format": "$#,##0.00",
68 "bg_color": "#E2E2E2",
69 "bold": True,
70 }
71 )
72 patient_row_time_format = workbook.add_format({"num_format": "h:mm AM/PM", "align": "center"})
73 patient_row_money_format = workbook.add_format({"num_format": "$#,##0.00"})
75 appointment_charge_row_money_format = workbook.add_format({"font_size": 8, "num_format": "$#,##0.00"})
76 appointment_charge_row_text_format = workbook.add_format(
77 {
78 "font_size": 8,
79 }
80 )
82 start_row_for_headers = 5
84 worksheet.write(start_row_for_headers, 1, "Fecha", header_row_format)
85 worksheet.write(start_row_for_headers, 2, "Hora Consulta", header_row_format)
86 worksheet.write(start_row_for_headers, 3, "Paciente", header_row_format)
87 worksheet.write(start_row_for_headers, 4, "Tipo de Servicio", header_row_format)
88 worksheet.write(start_row_for_headers, 5, "Servicio", header_row_format)
89 worksheet.write(start_row_for_headers, 6, "Cantidad", header_row_format)
90 worksheet.write(start_row_for_headers, 7, "Precio Unitario", header_row_format)
91 worksheet.write(start_row_for_headers, 8, "Venta", header_row_format)
92 worksheet.write(start_row_for_headers, 9, "Costo Consultorio", header_row_format)
93 worksheet.write(start_row_for_headers, 10, "Costo Proveedores", header_row_format)
94 worksheet.write(start_row_for_headers, 11, "Costo Total", header_row_format)
95 worksheet.write(start_row_for_headers, 12, "Utilidad", header_row_format)
96 worksheet.write(start_row_for_headers, 13, "Pagado", header_row_format)
97 worksheet.write(start_row_for_headers, 14, "Adeudado", header_row_format)
98 worksheet.write(start_row_for_headers, 15, "Facturación", header_row_format)
100 worksheet.set_column("E:H", None, None, {"level": 1, "hidden": True})
101 worksheet.set_column("I:I", None, None, {"collapsed": True})
102 worksheet.set_column("J:M", None, None, {"level": 1, "hidden": True})
103 worksheet.set_column("N:N", None, None, {"collapsed": True})
105 row = start_row_for_headers + 1
106 current_date = None
107 current_appointment = None
108 date_rows = []
109 patient_rows = []
110 date_totals_row_values = [0, 0, 0, 0, 0, 0, 0]
111 patient_totals_row_values = [0, 0, 0, 0, 0, 0, 0]
112 initial_date_row = row
113 initial_patient_row = row + 1
115 for appointment_charge in queryset:
116 if not appointment_charge.pending_amount.amount > 0:
117 continue
119 if appointment_charge.appointment.date != current_date:
121 worksheet.write(
122 initial_date_row,
123 8,
124 date_totals_row_values[0],
125 date_row_money_format,
126 )
127 worksheet.write(
128 initial_date_row,
129 9,
130 date_totals_row_values[1],
131 date_row_money_format,
132 )
133 worksheet.write(
134 initial_date_row,
135 10,
136 date_totals_row_values[2],
137 date_row_money_format,
138 )
139 worksheet.write(
140 initial_date_row,
141 11,
142 date_totals_row_values[3],
143 date_row_money_format,
144 )
145 worksheet.write(
146 initial_date_row,
147 12,
148 date_totals_row_values[4],
149 date_row_money_format,
150 )
151 worksheet.write(
152 initial_date_row,
153 13,
154 date_totals_row_values[5],
155 date_row_money_format,
156 )
157 worksheet.write(
158 initial_date_row,
159 14,
160 date_totals_row_values[6],
161 date_row_money_format,
162 )
163 date_totals_row_values = [0, 0, 0, 0, 0, 0, 0]
164 date_current_row_values = [0, 0, 0, 0, 0, 0, 0]
166 initial_date_row = row
167 date_rows.append(row)
168 worksheet.merge_range(row, 1, row, 7, appointment_charge.date, date_row_format)
170 row += 1
171 current_appointment = None
172 patient_rows = []
174 if appointment_charge.appointment != current_appointment:
176 worksheet.write(
177 initial_patient_row,
178 8,
179 patient_totals_row_values[0],
180 patient_row_money_format,
181 )
182 worksheet.write(
183 initial_patient_row,
184 9,
185 patient_totals_row_values[1],
186 patient_row_money_format,
187 )
188 worksheet.write(
189 initial_patient_row,
190 10,
191 patient_totals_row_values[2],
192 patient_row_money_format,
193 )
194 worksheet.write(
195 initial_patient_row,
196 11,
197 patient_totals_row_values[3],
198 patient_row_money_format,
199 )
200 worksheet.write(
201 initial_patient_row,
202 12,
203 patient_totals_row_values[4],
204 patient_row_money_format,
205 )
206 worksheet.write(
207 initial_patient_row,
208 13,
209 patient_totals_row_values[5],
210 patient_row_money_format,
211 )
212 worksheet.write(
213 initial_patient_row,
214 14,
215 patient_totals_row_values[6],
216 patient_row_money_format,
217 )
218 patient_totals_row_values = [0, 0, 0, 0, 0, 0, 0]
219 patient_current_row_values = [0, 0, 0, 0, 0, 0, 0]
221 initial_patient_row = row
222 patient_rows.append(row)
223 worksheet.set_row(row, None, None, {"level": 1, "collapsed": True})
224 worksheet.write(row, 2, appointment_charge.appointment.start_time, patient_row_time_format)
225 worksheet.merge_range(row, 3, row, 7, appointment_charge.appointment.patient.full_name)
226 row += 1
228 worksheet.write(row, 4, appointment_charge.product._meta.verbose_name, appointment_charge_row_text_format)
229 worksheet.write(row, 5, appointment_charge.product.name, appointment_charge_row_text_format)
230 worksheet.write(row, 6, appointment_charge.quantity, appointment_charge_row_text_format)
231 worksheet.write(row, 7, appointment_charge.price.amount, appointment_charge_row_money_format)
232 worksheet.write(row, 8, appointment_charge.total_price.amount, appointment_charge_row_money_format)
233 worksheet.write(row, 9, appointment_charge.total_shared_cost.amount, appointment_charge_row_money_format)
234 worksheet.write(row, 10, appointment_charge.total_hard_cost.amount, appointment_charge_row_money_format)
235 worksheet.write(
236 row,
237 11,
238 appointment_charge.total_shared_cost.amount + appointment_charge.total_hard_cost.amount,
239 appointment_charge_row_money_format,
240 )
241 worksheet.write(
242 row,
243 12,
244 appointment_charge.total_price.amount
245 - (appointment_charge.total_shared_cost.amount + appointment_charge.total_hard_cost.amount),
246 appointment_charge_row_money_format,
247 )
248 worksheet.write(row, 13, appointment_charge.paid_amount.amount, appointment_charge_row_money_format)
249 worksheet.write(row, 14, appointment_charge.pending_amount.amount, appointment_charge_row_money_format)
250 worksheet.write(row, 15, appointment_charge.pk)
251 worksheet.set_row(row, None, None, {"level": 2, "hidden": True})
253 patient_current_row_values = [
254 appointment_charge.total_price.amount,
255 appointment_charge.total_shared_cost.amount,
256 appointment_charge.total_hard_cost.amount,
257 appointment_charge.total_shared_cost.amount + appointment_charge.total_hard_cost.amount,
258 appointment_charge.total_price.amount
259 - (appointment_charge.total_shared_cost.amount + appointment_charge.total_hard_cost.amount),
260 appointment_charge.paid_amount.amount,
261 appointment_charge.pending_amount.amount,
262 ]
263 patient_totals_row_values = list(map(lambda x, y: x + y, patient_totals_row_values, patient_current_row_values))
265 date_current_row_values = [
266 appointment_charge.total_price.amount,
267 appointment_charge.total_shared_cost.amount,
268 appointment_charge.total_hard_cost.amount,
269 appointment_charge.total_shared_cost.amount + appointment_charge.total_hard_cost.amount,
270 appointment_charge.total_price.amount
271 - (appointment_charge.total_shared_cost.amount + appointment_charge.total_hard_cost.amount),
272 appointment_charge.paid_amount.amount,
273 appointment_charge.pending_amount.amount,
274 ]
275 date_totals_row_values = list(map(lambda x, y: x + y, date_totals_row_values, date_current_row_values))
277 row += 1
278 current_date = appointment_charge.appointment.date
279 current_appointment = appointment_charge.appointment
281 worksheet.write(
282 initial_date_row,
283 8,
284 date_totals_row_values[0],
285 date_row_money_format,
286 )
287 worksheet.write(
288 initial_date_row,
289 9,
290 date_totals_row_values[1],
291 date_row_money_format,
292 )
293 worksheet.write(
294 initial_date_row,
295 10,
296 date_totals_row_values[2],
297 date_row_money_format,
298 )
299 worksheet.write(
300 initial_date_row,
301 11,
302 date_totals_row_values[3],
303 date_row_money_format,
304 )
305 worksheet.write(
306 initial_date_row,
307 12,
308 date_totals_row_values[4],
309 date_row_money_format,
310 )
311 worksheet.write(
312 initial_date_row,
313 13,
314 date_totals_row_values[5],
315 date_row_money_format,
316 )
317 worksheet.write(
318 initial_date_row,
319 14,
320 date_totals_row_values[6],
321 date_row_money_format,
322 )
324 worksheet.write(
325 initial_patient_row,
326 8,
327 patient_totals_row_values[0],
328 patient_row_money_format,
329 )
330 worksheet.write(
331 initial_patient_row,
332 9,
333 patient_totals_row_values[1],
334 patient_row_money_format,
335 )
336 worksheet.write(
337 initial_patient_row,
338 10,
339 patient_totals_row_values[2],
340 patient_row_money_format,
341 )
342 worksheet.write(
343 initial_patient_row,
344 11,
345 patient_totals_row_values[3],
346 patient_row_money_format,
347 )
348 worksheet.write(
349 initial_patient_row,
350 12,
351 patient_totals_row_values[4],
352 patient_row_money_format,
353 )
354 worksheet.write(
355 initial_patient_row,
356 13,
357 patient_totals_row_values[5],
358 patient_row_money_format,
359 )
360 worksheet.write(
361 initial_patient_row,
362 14,
363 patient_totals_row_values[6],
364 patient_row_money_format,
365 )
367 worksheet.autofit()
369 workbook.close()
370 xlsx_data = output.getvalue()
372 xlsx_file = ContentFile(xlsx_data)
374 instance.export_file.save(workbook_name, xlsx_file)
375 instance.save()