from io import BytesIO from pathlib import Path from django.http import HttpResponse from django.shortcuts import get_object_or_404 from django.utils import timezone from django.utils.text import slugify from rest_framework.permissions import IsAuthenticated from rest_framework.views import APIView from drf_spectacular.utils import OpenApiResponse, extend_schema from bookings.models import Booking from structures.models import Structure from .city_tax_service import ( build_configured_exemption_map, build_monthly_rate_map, build_period_bounds, calculate_city_tax_report, get_missing_city_tax_settings_fields, normalize_platform_tokens, ) from .models import StructureCityTaxSettings from .serializers_city_tax_preview import CityTaxPreviewRequestSerializer @extend_schema( tags=["City Tax"], summary="Export city tax report as XLSX", request=CityTaxPreviewRequestSerializer, responses={ 200: OpenApiResponse(description="XLSX file"), 400: OpenApiResponse(description="Validation errors"), 404: OpenApiResponse(description="Structure/settings not found"), }, ) class CityTaxExportAPIView(APIView): permission_classes = [IsAuthenticated] @staticmethod def _apply_booking_sheet_headers(ws): headers = { "A1": "id", "B1": "first_guest_status", "C1": "client_full_name", "D1": "Check in date time", "E1": "Checkout date time", "F1": "rental_name", "G1": "nights_count", "H1": "Total_guests_count", "I1": "Adults", "J1": "Children", "K1": "final_price", "L1": "created_at", "M1": "source", "N1": "guest_city_tax_base_amount", "O1": "rental_tax_amount", "P1": "estimated_total_city_tax", "Q1": "estimated_ordinary_nights", "R1": "estimated_exempt_nights", "S1": "estimated_platform_exempt_nights", "T1": "client_email", "U1": "client_phone", "V1": "Check in date format 1", "W1": "Check out date format 1", "X1": "Check in date", "Y1": "Check out date", "Z1": "Adults x nights <=8 nights", "AA1": "Minors x nights <=8 nights", "AB1": "All guests x nights", "AC1": "Long stay # of adults", "AD1": "Long stay nights", "AE1": "Adults + Minors + Long Stay", "AF1": "Validation Check", "AL1": "id", "AM1": "source", } for cell, value in headers.items(): ws[cell] = value @staticmethod def _write_booking_row(ws, row_idx, payload): ws[f"A{row_idx}"] = payload["id"] ws[f"B{row_idx}"] = payload["first_guest_status"] ws[f"C{row_idx}"] = payload["client_full_name"] ws[f"D{row_idx}"] = payload["check_in"] ws[f"E{row_idx}"] = payload["check_out"] ws[f"F{row_idx}"] = payload["rental_name"] ws[f"G{row_idx}"] = payload["nights_count"] ws[f"H{row_idx}"] = payload["total_guests_count"] ws[f"I{row_idx}"] = payload["adults"] ws[f"J{row_idx}"] = payload["children"] ws[f"K{row_idx}"] = payload["final_price"] ws[f"L{row_idx}"] = payload["created_at"] ws[f"M{row_idx}"] = payload["source"] ws[f"N{row_idx}"] = payload["guest_city_tax_base_amount"] ws[f"O{row_idx}"] = payload["rental_tax_amount"] ws[f"P{row_idx}"] = payload["estimated_total_city_tax"] ws[f"Q{row_idx}"] = payload["estimated_ordinary_nights"] ws[f"R{row_idx}"] = payload["estimated_exempt_nights"] ws[f"S{row_idx}"] = payload["estimated_platform_exempt_nights"] ws[f"T{row_idx}"] = payload["client_email"] ws[f"U{row_idx}"] = payload["client_phone"] ws[f"V{row_idx}"] = payload["check_in"][:10] ws[f"W{row_idx}"] = payload["check_out"][:10] ws[f"X{row_idx}"] = payload["check_in"][:10] ws[f"Y{row_idx}"] = payload["check_out"][:10] ws[f"Z{row_idx}"] = payload["estimated_ordinary_nights"] ws[f"AA{row_idx}"] = payload["estimated_exempt_nights"] ws[f"AB{row_idx}"] = payload["all_guest_nights"] ws[f"AC{row_idx}"] = payload["overflow_guest_count"] ws[f"AD{row_idx}"] = payload["overflow_nights"] ws[f"AE{row_idx}"] = payload["validation_total"] ws[f"AF{row_idx}"] = payload["validation_total"] == payload["all_guest_nights"] ws[f"AL{row_idx}"] = payload["id"] ws[f"AM{row_idx}"] = payload["source"] @staticmethod def _write_booking_totals_row(ws, row_idx, has_data): total_columns = [ "H", "I", "J", "P", "Q", "R", "S", "Z", "AA", "AB", "AC", "AD", "AE", ] if not has_data: for column in total_columns: ws[f"{column}{row_idx}"] = 0 return for column in total_columns: ws[f"{column}{row_idx}"] = f"=SUBTOTAL(9,${column}$2:${column}${row_idx - 1})" @staticmethod def _apply_row_style(ws, row_idx, style_map): for col, style in style_map.items(): ws[f"{col}{row_idx}"]._style = style @staticmethod def _build_summary_sheet(ws, structure_name, rows, months_range): from .city_tax_service import MONTH_SHORT ws["A1"] = "rental_name" ws["B1"] = structure_name ws["B2"] = "Total Guests" ws["C2"] = "Total Overnight Stays" ws["A3"] = "Row Labels" ws["B3"] = "Sum of Total_guests_count" ws["C3"] = "Sum of All guests x Night" month_totals = {month: {"guests": 0, "nights": 0} for month in months_range} for row in rows: month_totals[row["month"]]["guests"] += row["total_guests_count"] month_totals[row["month"]]["nights"] += row["all_guest_nights"] current_row = 4 for month in months_range: ws[f"A{current_row}"] = MONTH_SHORT[month] ws[f"B{current_row}"] = month_totals[month]["guests"] ws[f"C{current_row}"] = month_totals[month]["nights"] current_row += 1 ws[f"A{current_row}"] = "Grand Total" ws[f"B{current_row}"] = f"=SUM($B$4:$B${current_row - 1})" ws[f"C{current_row}"] = f"=SUM($C$4:$C${current_row - 1})" def post(self, request, structure_id): serializer = CityTaxPreviewRequestSerializer(data=request.data) serializer.is_valid(raise_exception=True) period = serializer.validated_data["period"] rates_override = serializer.validated_data["rates"] property_type_id = serializer.validated_data.get("property_type_id") property_id = serializer.validated_data.get("property_id") structure = get_object_or_404(Structure, id=structure_id) settings = get_object_or_404( StructureCityTaxSettings, structure=structure, is_active=True, ) missing = get_missing_city_tax_settings_fields(settings) if missing: return HttpResponse( f"Missing city tax settings: {', '.join(missing)}", status=400, ) period_start, period_end_exclusive, months_range = build_period_bounds(period) monthly_rate_map = build_monthly_rate_map(structure, period["year"]) configured_exemptions = build_configured_exemption_map( settings.exemption_reasons ) platform_exemptions = normalize_platform_tokens(settings.platform_exemptions) bookings = ( Booking.objects.filter( structure=structure, check_in_date__lt=period_end_exclusive, check_out_date__gt=period_start, ) .select_related("property") .prefetch_related("guests") ) if property_type_id: bookings = bookings.filter(property_type_id=property_type_id) if property_id: bookings = bookings.filter(property_id=property_id) result = calculate_city_tax_report( bookings=bookings.order_by("check_in_date", "id"), period=period, rates_override=rates_override, default_rate=settings.default_rate, monthly_rate_map=monthly_rate_map, max_taxable_nights=int(settings.max_taxable_nights or 0), minor_age_limit=int(settings.minor_age_limit or 0), configured_exemptions=configured_exemptions, platform_exemptions=platform_exemptions, platform_exemption_labels=list(settings.platform_exemptions or []), ) export_rows = result.export_rows template_path = ( Path(__file__).resolve().parent / "templates" / "city-tax-template.xlsx" ) if not template_path.exists(): return HttpResponse("Template file not found", status=500) from openpyxl import load_workbook from openpyxl.worksheet.table import Table, TableStyleInfo workbook = load_workbook(template_path) booking_sheet = workbook["bookings (14)"] summary_sheet = workbook["Totale Ospiti"] booking_sheet.title = "Bookings" summary_sheet.title = "Total Guests" booking_sheet.delete_cols(12, 2) self._apply_booking_sheet_headers(booking_sheet) data_style_map = { col: booking_sheet[f"{col}2"]._style for col in [ "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AL", "AM", ] } rows_to_delete = booking_sheet.max_row - 1 if rows_to_delete > 0: booking_sheet.delete_rows(2, rows_to_delete) row_idx = 2 for row_payload in export_rows: self._write_booking_row(booking_sheet, row_idx, row_payload) self._apply_row_style(booking_sheet, row_idx, data_style_map) row_idx += 1 totals_row = row_idx self._write_booking_totals_row( booking_sheet, totals_row, has_data=bool(export_rows), ) self._apply_row_style(booking_sheet, totals_row, data_style_map) for table_name in list(booking_sheet.tables.keys()): del booking_sheet.tables[table_name] table = Table(displayName="Table_1", ref=f"A1:AF{totals_row}") table.totalsRowShown = True table.tableStyleInfo = TableStyleInfo( name="TableStyleMedium2", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False, ) booking_sheet.add_table(table) summary_sheet.delete_rows(1, summary_sheet.max_row) self._build_summary_sheet( summary_sheet, structure_name=structure.name, rows=export_rows, months_range=months_range, ) output = BytesIO() workbook.save(output) output.seek(0) file_date = timezone.localdate().isoformat() structure_slug = slugify(structure.name) or f"structure-{structure.id}" filename = f"city-tax-{structure_slug}-{file_date}.xlsx" response = HttpResponse( output.getvalue(), content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", ) response["Content-Disposition"] = f'attachment; filename="{filename}"' return response