""" Realise Export Service - Generates Excel reports for realized/actual data only WITHOUT budget information. Format showing complete financial report with: - Indicateurs d'exploitation (DUREE EXPLOITATION, CAPACITE, OCCUPATION, etc.) - Revenus par catégorie et département - Charges détaillées par catégorie - Résultat net Columns: Réalisé N, Réalisé N-1, Réalisé N-2, Écart N-1, Écart N-2 (avec années dynamiques basées sur l'année sélectionnée) """ from datetime import date, datetime from typing import List, Dict, Optional, Any from io import BytesIO from decimal import Decimal from openpyxl import Workbook from openpyxl.styles import Font, Alignment, Border, Side, PatternFill from openpyxl.utils import get_column_letter from openpyxl.worksheet.worksheet import Worksheet from sqlalchemy.orm import Session from sqlalchemy import func, and_ from app.models.folio import FolioItem, Folio from app.models.reservation import Reservation, ReservationStatus, ReservationSource from app.models.room import Room from app.models.department import Department MOIS_NAMES = [ "Janvier", "Fevrier", "Mars", "Avril", "Mai", "Juin", "Juillet", "Août", "Septembre", "Octobre", "Novembre", "Decembre" ] MOIS_NAMES_FR = [ "Janvier", "Février", "Mars", "Avril", "Mai", "Juin", "Juillet", "Août", "Septembre", "Octobre", "Novembre", "Décembre" ] # Structure complète du rapport financier réalisé # Section 1: Indicateurs d'exploitation EXPLOITATION_INDICATORS = [ {"label": "DUREE EXPLOITATION jour", "type": "indicator", "key": "exploitation_days"}, {"label": "CAPACITE CHAMBRE jour", "type": "indicator", "key": "room_capacity"}, {"label": "% OCCUPATION CHAMBRE", "type": "percent", "key": "occupancy_rate"}, {"label": "TOTAL CHAMBRES VENDUES", "type": "indicator", "key": "rooms_sold"}, {"label": "INDICE FREQUENTATION", "type": "header_label"}, {"label": "CAPACITE LIT jour", "type": "indicator", "key": "bed_capacity"}, {"label": "% OCCUPATION LIT", "type": "percent", "key": "bed_occupancy_rate"}, {"label": "TOTAL LITS VENDUS", "type": "indicator", "key": "beds_sold"}, {"label": "", "type": "spacer"}, {"label": "NUITEES ALLOTEES", "type": "indicator", "key": "nights_alloted"}, {"label": "NUITEES PASSAGE", "type": "indicator", "key": "nights_passage"}, {"label": "NOMBRE DE COUVERTS", "type": "indicator", "key": "covers"}, {"label": "", "type": "spacer"}, {"label": "PMV nuitée allotée", "type": "currency", "key": "pmv_alloted"}, {"label": "PMV nuitée PASSAGE", "type": "currency", "key": "pmv_passage"}, {"label": "", "type": "spacer"}, {"label": "RATIO TOTAL FOOD AND BEVERAGE", "type": "percent", "key": "fb_ratio"}, {"label": "", "type": "spacer"}, {"label": "REV / PAR", "type": "currency", "key": "revpar"}, {"label": "TREV / PAR", "type": "currency", "key": "trevpar"}, {"label": "GRC", "type": "currency", "key": "grc"}, ] # Section 2: Revenus détaillés REVENUE_LINES = [ {"label": "", "type": "spacer"}, {"label": "CA HEBERGEMENT ALLOTE", "type": "revenue", "key": "ca_hebergement_allote"}, {"label": "DONT CA RESTAURATION ALLOTE", "type": "revenue", "key": "ca_restauration_allote"}, {"label": "CA HEBERGEMENT PASSAGE", "type": "revenue", "key": "ca_hebergement_passage"}, {"label": "DONT CA RESTAURATION PASSAGE", "type": "revenue", "key": "ca_restauration_passage"}, {"label": "TOTAL CA HEBERGEMENT", "type": "total_highlight", "key": "total_ca_hebergement"}, {"label": "TOTAL CA FOOD AND BEVERAGE", "type": "total_highlight", "key": "total_ca_fb"}, {"label": "Telephone", "type": "revenue", "key": "telephone"}, {"label": "Boutique", "type": "revenue", "key": "boutique"}, {"label": "Blanchisserie", "type": "revenue", "key": "blanchisserie"}, {"label": "SPA", "type": "revenue", "key": "spa"}, {"label": "Location salles", "type": "revenue", "key": "location_salles"}, {"label": "Revenus sur Location", "type": "revenue", "key": "revenus_location"}, {"label": "Divers autres ventes", "type": "revenue", "key": "divers_ventes"}, {"label": "AUTRES VENTES", "type": "total_highlight", "key": "autres_ventes"}, {"label": "CHIFFRE D'AFFAIRE TTC", "type": "total_highlight", "key": "ca_ttc"}, {"label": "Taux TVA", "type": "info"}, {"label": "TVA", "type": "revenue", "key": "tva"}, {"label": "CHIFFRE D'AFFAIRE HT", "type": "total_highlight", "key": "ca_ht"}, ] # Section 3: Charges détaillées EXPENSE_LINES = [ {"label": "", "type": "spacer"}, {"label": "", "type": "spacer"}, {"label": "COUT ALIMENTAIRE", "type": "expense_highlight", "key": "cout_alimentaire"}, {"label": "AUTRES COUTS DES VENTES", "type": "expense_highlight", "key": "autres_couts_ventes"}, {"label": "SALAIRES & CHARGES", "type": "expense_highlight", "key": "salaires_charges"}, {"label": "", "type": "spacer"}, {"label": "Pdts & fourn. entretien", "type": "expense", "key": "pdts_entretien"}, {"label": "Pdts & fourn. accueil", "type": "expense", "key": "pdts_accueil"}, {"label": "Pdts & fourn. consommable", "type": "expense", "key": "pdts_consommable"}, {"label": "PME & uniformes", "type": "expense", "key": "pme_uniformes"}, {"label": "Fourn. techn. & mat. atelier", "type": "expense", "key": "fourn_tech"}, {"label": "Papeterie & imprimés", "type": "expense", "key": "papeterie"}, {"label": "Pdts & fourn. animation", "type": "expense", "key": "pdts_animation"}, {"label": "Autres produits et fournitures", "type": "expense", "key": "autres_pdts"}, {"label": "1 - Pdts et fournitures", "type": "subtotal", "key": "total_pdts_fournitures"}, {"label": "Eau", "type": "expense", "key": "eau"}, {"label": "Electricité", "type": "expense", "key": "electricite"}, {"label": "Gaz", "type": "expense", "key": "gaz"}, {"label": "Combustibles", "type": "expense", "key": "combustibles"}, {"label": "2 - Eau & Energie", "type": "subtotal", "key": "total_eau_energie"}, {"label": "Locations", "type": "expense", "key": "locations"}, {"label": "Blanchissage client", "type": "expense", "key": "blanchissage_client"}, {"label": "Blanchissage Hotel", "type": "expense", "key": "blanchissage_hotel"}, {"label": "Entretien & Reparation", "type": "expense", "key": "entretien_reparation"}, {"label": "Gardiennage", "type": "expense", "key": "gardiennage"}, {"label": "Maintenance", "type": "expense", "key": "maintenance"}, {"label": "Autres services extérieurs", "type": "expense", "key": "autres_services"}, {"label": "3 - St loc & entretien", "type": "subtotal", "key": "total_st_loc_entretien"}, {"label": "Assurances Hotel", "type": "expense", "key": "assurances_hotel"}, {"label": "Assurances Vehicules", "type": "expense", "key": "assurances_vehicules"}, {"label": "Honoraires comptable + Avocat", "type": "expense", "key": "honoraires_comptable"}, {"label": "Honoraires autres", "type": "expense", "key": "honoraires_autres"}, {"label": "autres charges", "type": "expense", "key": "autres_charges"}, {"label": "4 - Assurances & honoraires", "type": "subtotal", "key": "total_assurances"}, {"label": "Annonces & insertions", "type": "expense", "key": "annonces"}, {"label": "Cadeaux publicitaires", "type": "expense", "key": "cadeaux_pub"}, {"label": "Publications", "type": "expense", "key": "publications"}, {"label": "Dépliants & imprimés", "type": "expense", "key": "depliants"}, {"label": "Divers", "type": "expense", "key": "divers_pub"}, {"label": "5 - Frais de publicité", "type": "subtotal", "key": "total_frais_pub"}, {"label": "Telephone administratif", "type": "expense", "key": "telephone_admin"}, {"label": "Telecopieur", "type": "expense", "key": "telecopieur"}, {"label": "Telex", "type": "expense", "key": "telex"}, {"label": "Frais postaux", "type": "expense", "key": "frais_postaux"}, {"label": "6 - Telecom", "type": "subtotal", "key": "total_telecom"}, {"label": "Taxes assises s/ les salaires (sauf taxe professionnelle)", "type": "expense", "key": "taxes_salaires"}, {"label": "taxe formation professionnelle", "type": "expense", "key": "taxe_formation"}, {"label": "Autres taxes", "type": "expense", "key": "autres_taxes"}, {"label": "Cotisations", "type": "expense", "key": "cotisations"}, {"label": "Redevances", "type": "expense", "key": "redevances"}, {"label": "7 - Taxes cot. et redevances", "type": "subtotal", "key": "total_taxes_cot"}, {"label": "Documentation", "type": "expense", "key": "documentation"}, {"label": "Petit aménagement", "type": "expense", "key": "petit_amenagement"}, {"label": "Deplacement & reception", "type": "expense", "key": "deplacement_reception"}, {"label": "Service bancaire", "type": "expense", "key": "service_bancaire"}, {"label": "Autres frais", "type": "expense", "key": "autres_frais"}, {"label": "8 - Autres frais d'exploitation", "type": "subtotal", "key": "total_autres_frais"}, {"label": "TOTAL AUTRES CHARGES D'EXPLOIT.", "type": "total_highlight", "key": "total_autres_charges"}, {"label": "TOTAL CHARGES", "type": "total_highlight", "key": "total_charges"}, ] # Section 4: Produits annexes et résultat RESULT_LINES = [ {"label": "", "type": "spacer"}, {"label": "Commissions", "type": "revenue", "key": "commissions"}, {"label": "Gain de change", "type": "revenue", "key": "gain_change"}, {"label": "Revenus locations", "type": "revenue", "key": "revenus_locations"}, {"label": "PRODUITS ANNEXES", "type": "total_highlight", "key": "produits_annexes"}, {"label": "R.B.E. 1", "type": "result_highlight", "key": "rbe_1"}, {"label": "", "type": "spacer"}, {"label": "Provision retraite/ Grosses Reparations", "type": "expense", "key": "provision_retraite"}, {"label": "Charges T.V.A Mesure Covid", "type": "expense", "key": "charges_tva_covid"}, {"label": "Frais financiers", "type": "expense", "key": "frais_financiers"}, {"label": "Taxe foncière", "type": "expense", "key": "taxe_fonciere"}, {"label": "Revenus sur Mise à disposition lacaux", "type": "revenue", "key": "revenus_mise_dispo"}, {"label": "Autres Frais / résultat exceptionnel", "type": "expense", "key": "autres_frais_except"}, {"label": "CHARGES ADM. & FINANCIERES", "type": "total_highlight", "key": "charges_adm_fin"}, {"label": "", "type": "spacer"}, {"label": "dont Amortissements sur travaux", "type": "expense", "key": "amort_travaux"}, {"label": "AMORTISSEMENTS", "type": "total_highlight", "key": "amortissements"}, {"label": "", "type": "spacer"}, {"label": "PROVISIONS fees", "type": "expense", "key": "provisions_fees"}, {"label": "", "type": "spacer"}, {"label": "", "type": "spacer"}, {"label": "Impôts", "type": "expense", "key": "impots"}, {"label": "RESULTAT NET", "type": "result_final", "key": "resultat_net"}, ] class RealiseExportService: """Service for exporting realized/actual data without budgets.""" def __init__(self, db: Session): self.db = db self._setup_styles() def _setup_styles(self): """Configure Excel styles.""" self.header_font = Font(bold=True, size=11) self.title_font = Font(bold=True, size=12) self.money_format = '#,##0.00' self.integer_format = '#,##0' self.percent_format = '0.00%' # Yellow highlight for totals and important lines self.highlight_fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid") # Light yellow for labels self.label_fill = PatternFill(start_color="FFFF99", end_color="FFFF99", fill_type="solid") # Green for écarts self.ecart_fill = PatternFill(start_color="90EE90", end_color="90EE90", fill_type="solid") # Header blue self.header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid") self.header_font_white = Font(bold=True, color="FFFFFF") self.thin_border = Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) def _get_days_in_month(self, year: int, month: int) -> int: """Get number of days in a month.""" if month == 12: next_month = date(year + 1, 1, 1) else: next_month = date(year, month + 1, 1) return (next_month - date(year, month, 1)).days def _get_room_capacity(self, establishment_id: int) -> int: """Get total room capacity for establishment.""" rooms = self.db.query(Room).filter( Room.establishment_id == establishment_id, Room.is_active == True ).all() return len(rooms) if rooms else 74 def _get_bed_capacity(self, establishment_id: int) -> int: """Get total bed capacity for establishment.""" rooms = self.db.query(Room).filter( Room.establishment_id == establishment_id, Room.is_active == True ).all() if not rooms: return 148 total = 0 for r in rooms: if r.room_type: total += r.room_type.capacity_adults + r.room_type.capacity_children else: total += 2 return total def _get_revenue_by_department( self, establishment_id: int, start_date: date, end_date: date ) -> Dict[str, float]: """Get revenue grouped by department code for a period.""" items = self.db.query( FolioItem.department_code, func.sum(FolioItem.total_ttc).label('total_ttc'), func.sum(FolioItem.total_ht).label('total_ht') ).join( FolioItem.folio ).join( Folio.reservation ).join( Reservation.room ).filter( Room.establishment_id == establishment_id, FolioItem.created_at >= start_date, FolioItem.created_at < end_date, FolioItem.is_voided == False ).group_by(FolioItem.department_code).all() return { item.department_code: { 'ttc': float(item.total_ttc or 0), 'ht': float(item.total_ht or 0) } for item in items } def _get_monthly_data( self, establishment_id: int, year: int, month: int ) -> Dict[str, Any]: """Get all monthly data for the report.""" start_date = date(year, month, 1) if month == 12: end_date = date(year + 1, 1, 1) else: end_date = date(year, month + 1, 1) days_in_month = self._get_days_in_month(year, month) room_capacity = self._get_room_capacity(establishment_id) bed_capacity = self._get_bed_capacity(establishment_id) # Get reservations reservations = self.db.query(Reservation).join(Room).filter( Room.establishment_id == establishment_id, Reservation.check_in_date < end_date, Reservation.check_out_date >= start_date, Reservation.status.in_([ ReservationStatus.CONFIRMED, ReservationStatus.CHECKED_IN, ReservationStatus.CHECKED_OUT ]) ).all() rooms_sold = 0 beds_sold = 0 nights_alloted = 0 nights_passage = 0 for res in reservations: actual_start = max(res.check_in_date, start_date) actual_end = min(res.check_out_date, end_date) nights = (actual_end - actual_start).days rooms_sold += nights beds_sold += nights * (res.adults + (res.children or 0)) if res.source in [ReservationSource.OTA, ReservationSource.AGENCY, ReservationSource.CORPORATE]: nights_alloted += nights else: nights_passage += nights # Revenue by department revenue_by_dept = self._get_revenue_by_department(establishment_id, start_date, end_date) # Calculate totals total_revenue_ttc = sum(r['ttc'] for r in revenue_by_dept.values()) total_revenue_ht = sum(r['ht'] for r in revenue_by_dept.values()) # F&B revenue fb_depts = ['REST', 'BAR', 'ROOM_SERVICE', 'MINIBAR'] fb_revenue = sum(revenue_by_dept.get(d, {}).get('ttc', 0) for d in fb_depts) # Hébergement revenue heberg_revenue = revenue_by_dept.get('HEBERG', {}).get('ttc', 0) # Calculate indicators total_room_capacity = room_capacity * days_in_month total_bed_capacity = bed_capacity * days_in_month occupancy_rate = (rooms_sold / total_room_capacity) if total_room_capacity > 0 else 0 bed_occupancy_rate = (beds_sold / total_bed_capacity) if total_bed_capacity > 0 else 0 fb_ratio = (fb_revenue / total_revenue_ttc) if total_revenue_ttc > 0 else 0 frequency_index = (beds_sold / rooms_sold) if rooms_sold > 0 else 0 revpar = (heberg_revenue / total_room_capacity) if total_room_capacity > 0 else 0 trevpar = (total_revenue_ttc / total_room_capacity) if total_room_capacity > 0 else 0 pmv_alloted = (heberg_revenue * (nights_alloted / (nights_alloted + nights_passage)) / nights_alloted) if nights_alloted > 0 else 0 pmv_passage = (heberg_revenue * (nights_passage / (nights_alloted + nights_passage)) / nights_passage) if nights_passage > 0 else 0 grc = (total_revenue_ttc / beds_sold) if beds_sold > 0 else 0 # Covers (restaurant) covers = self.db.query(func.count(FolioItem.id)).join(Folio).join(Reservation).join(Room).filter( Room.establishment_id == establishment_id, FolioItem.created_at >= start_date, FolioItem.created_at < end_date, FolioItem.department_code == 'REST', FolioItem.is_voided == False ).scalar() or 0 return { # Exploitation indicators "exploitation_days": days_in_month, "room_capacity": room_capacity, "occupancy_rate": occupancy_rate, "rooms_sold": rooms_sold, "bed_capacity": bed_capacity, "bed_occupancy_rate": bed_occupancy_rate, "beds_sold": beds_sold, "frequency_index": frequency_index, "nights_alloted": nights_alloted, "nights_passage": nights_passage, "covers": covers, "pmv_alloted": pmv_alloted, "pmv_passage": pmv_passage, "fb_ratio": fb_ratio, "revpar": revpar, "trevpar": trevpar, "grc": grc, # Revenue data "ca_hebergement_allote": heberg_revenue * (nights_alloted / (nights_alloted + nights_passage)) if (nights_alloted + nights_passage) > 0 else 0, "ca_restauration_allote": fb_revenue * (nights_alloted / (nights_alloted + nights_passage)) if (nights_alloted + nights_passage) > 0 else 0, "ca_hebergement_passage": heberg_revenue * (nights_passage / (nights_alloted + nights_passage)) if (nights_alloted + nights_passage) > 0 else 0, "ca_restauration_passage": fb_revenue * (nights_passage / (nights_alloted + nights_passage)) if (nights_alloted + nights_passage) > 0 else 0, "total_ca_hebergement": heberg_revenue, "total_ca_fb": fb_revenue, "telephone": revenue_by_dept.get('TELEPHONE', {}).get('ttc', 0), "boutique": revenue_by_dept.get('BOUTIQUE', {}).get('ttc', 0), "blanchisserie": revenue_by_dept.get('BLANCHISSERIE', {}).get('ttc', 0), "spa": revenue_by_dept.get('SPA', {}).get('ttc', 0), "location_salles": revenue_by_dept.get('LOCATION_SALLES', {}).get('ttc', 0), "revenus_location": revenue_by_dept.get('REVENUS_LOCATION', {}).get('ttc', 0), "divers_ventes": revenue_by_dept.get('AUTRES', {}).get('ttc', 0), "autres_ventes": sum(revenue_by_dept.get(d, {}).get('ttc', 0) for d in ['TELEPHONE', 'BOUTIQUE', 'BLANCHISSERIE', 'SPA', 'AUTRES']), "ca_ttc": total_revenue_ttc, "tva": total_revenue_ttc - total_revenue_ht, "ca_ht": total_revenue_ht, # Charges (initialized to 0, to be populated from actual expense data) "cout_alimentaire": 0, "autres_couts_ventes": 0, "salaires_charges": 0, "pdts_entretien": 0, "pdts_accueil": 0, "pdts_consommable": 0, "pme_uniformes": 0, "fourn_tech": 0, "papeterie": 0, "pdts_animation": 0, "autres_pdts": 0, "total_pdts_fournitures": 0, "eau": 0, "electricite": 0, "gaz": 0, "combustibles": 0, "total_eau_energie": 0, "locations": 0, "blanchissage_client": 0, "blanchissage_hotel": 0, "entretien_reparation": 0, "gardiennage": 0, "maintenance": 0, "autres_services": 0, "total_st_loc_entretien": 0, "assurances_hotel": 0, "assurances_vehicules": 0, "honoraires_comptable": 0, "honoraires_autres": 0, "autres_charges": 0, "total_assurances": 0, "annonces": 0, "cadeaux_pub": 0, "publications": 0, "depliants": 0, "divers_pub": 0, "total_frais_pub": 0, "telephone_admin": 0, "telecopieur": 0, "telex": 0, "frais_postaux": 0, "total_telecom": 0, "taxes_salaires": 0, "taxe_formation": 0, "autres_taxes": 0, "cotisations": 0, "redevances": 0, "total_taxes_cot": 0, "documentation": 0, "petit_amenagement": 0, "deplacement_reception": 0, "service_bancaire": 0, "autres_frais": 0, "total_autres_frais": 0, "total_autres_charges": 0, "total_charges": 0, # Results "commissions": 0, "gain_change": 0, "revenus_locations": 0, "produits_annexes": 0, "rbe_1": total_revenue_ht, # Simplified "provision_retraite": 0, "charges_tva_covid": 0, "frais_financiers": 0, "taxe_fonciere": 0, "revenus_mise_dispo": 0, "autres_frais_except": 0, "charges_adm_fin": 0, "amort_travaux": 0, "amortissements": 0, "provisions_fees": 0, "impots": 0, "resultat_net": total_revenue_ht, # Simplified } def _apply_header_style(self, ws: Worksheet, row: int, start_col: int, end_col: int): """Apply header style to cells.""" for col in range(start_col, end_col + 1): cell = ws.cell(row=row, column=col) cell.fill = self.header_fill cell.font = self.header_font_white cell.alignment = Alignment(horizontal='center', vertical='center') cell.border = self.thin_border def _create_monthly_realise_sheet( self, wb: Workbook, month: int, establishment_id: int, year: int, establishment_name: str = "SALY HOTEL" ): """Create monthly sheet with complete financial data.""" sheet_name = MOIS_NAMES[month - 1] ws = wb.create_sheet(sheet_name) month_name = MOIS_NAMES_FR[month - 1] # Get data for 3 years data_n = self._get_monthly_data(establishment_id, year, month) data_n1 = self._get_monthly_data(establishment_id, year - 1, month) data_n2 = self._get_monthly_data(establishment_id, year - 2, month) # Row 1: Hotel name and column headers ws.cell(row=1, column=1, value=establishment_name) ws.cell(row=1, column=1).font = self.title_font # Column headers with dynamic years ws.cell(row=1, column=2, value=f"Réalisé {year}") ws.cell(row=1, column=3, value=f"Réalisé {year - 1}") ws.cell(row=1, column=4, value=f"Réalisé {year - 2}") ws.cell(row=1, column=5, value="Ecart") ws.cell(row=1, column=6, value="Ecart") # Sub-headers ws.cell(row=2, column=2, value=month_name) ws.cell(row=2, column=3, value=month_name) ws.cell(row=2, column=4, value=month_name) ws.cell(row=2, column=5, value="Réalisé - N-1") ws.cell(row=2, column=6, value="Réalisé - N-2") # Apply header styles for col in range(2, 7): ws.cell(row=1, column=col).font = self.header_font ws.cell(row=1, column=col).alignment = Alignment(horizontal='center') ws.cell(row=2, column=col).alignment = Alignment(horizontal='center') # Write all sections row = 3 # Section 1: Exploitation indicators for line_def in EXPLOITATION_INDICATORS: row = self._write_data_row(ws, row, line_def, data_n, data_n1, data_n2) # Section 2: Revenue lines for line_def in REVENUE_LINES: row = self._write_data_row(ws, row, line_def, data_n, data_n1, data_n2) # Section 3: Expense lines for line_def in EXPENSE_LINES: row = self._write_data_row(ws, row, line_def, data_n, data_n1, data_n2) # Section 4: Results for line_def in RESULT_LINES: row = self._write_data_row(ws, row, line_def, data_n, data_n1, data_n2) # Column widths ws.column_dimensions['A'].width = 45 for col in range(2, 7): ws.column_dimensions[get_column_letter(col)].width = 14 def _write_data_row( self, ws: Worksheet, row: int, line_def: Dict, data_n: Dict, data_n1: Dict, data_n2: Dict ) -> int: """Write a single data row and return the next row number.""" label = line_def["label"] line_type = line_def["type"] ws.cell(row=row, column=1, value=label) if line_type == "spacer": return row + 1 if line_type in ("header_label", "section_header"): ws.cell(row=row, column=1).font = self.header_font return row + 1 if line_type == "info": return row + 1 # Get values key = line_def.get("key", "") val_n = data_n.get(key, 0) if key else 0 val_n1 = data_n1.get(key, 0) if key else 0 val_n2 = data_n2.get(key, 0) if key else 0 # Write values ws.cell(row=row, column=2, value=val_n) ws.cell(row=row, column=3, value=val_n1) ws.cell(row=row, column=4, value=val_n2) # Calculate écarts ecart_n1 = val_n - val_n1 ecart_n2 = val_n - val_n2 ws.cell(row=row, column=5, value=ecart_n1) ws.cell(row=row, column=6, value=ecart_n2) # Apply formatting based on type if line_type == "percent": for col in range(2, 7): ws.cell(row=row, column=col).number_format = self.percent_format elif line_type in ("currency", "revenue", "expense"): for col in range(2, 7): ws.cell(row=row, column=col).number_format = self.money_format elif line_type == "indicator": for col in range(2, 7): ws.cell(row=row, column=col).number_format = self.integer_format # Apply highlight styles if line_type in ("total_highlight", "expense_highlight", "subtotal"): ws.cell(row=row, column=1).fill = self.highlight_fill ws.cell(row=row, column=1).font = self.header_font for col in range(2, 7): ws.cell(row=row, column=col).fill = self.highlight_fill ws.cell(row=row, column=col).number_format = self.money_format if line_type == "result_highlight": ws.cell(row=row, column=1).font = self.header_font if line_type == "result_final": ws.cell(row=row, column=1).fill = self.highlight_fill ws.cell(row=row, column=1).font = Font(bold=True, size=12) for col in range(2, 7): ws.cell(row=row, column=col).fill = self.highlight_fill ws.cell(row=row, column=col).font = Font(bold=True) ws.cell(row=row, column=col).number_format = self.money_format return row + 1 def _create_cumul_realise_sheet( self, wb: Workbook, end_month: int, establishment_id: int, year: int, establishment_name: str = "SALY HOTEL" ): """Create cumulative sheet for realized data.""" sheet_name = f"Cumul {MOIS_NAMES[end_month - 1]}" ws = wb.create_sheet(sheet_name) month_name = MOIS_NAMES_FR[end_month - 1] # Aggregate data for all months up to end_month cumul_n = {} cumul_n1 = {} cumul_n2 = {} for m in range(1, end_month + 1): data_n = self._get_monthly_data(establishment_id, year, m) data_n1 = self._get_monthly_data(establishment_id, year - 1, m) data_n2 = self._get_monthly_data(establishment_id, year - 2, m) for key in data_n: if key not in cumul_n: cumul_n[key] = 0 cumul_n1[key] = 0 cumul_n2[key] = 0 # Sum for cumulative (except for rates which need recalculation) if key not in ['occupancy_rate', 'bed_occupancy_rate', 'fb_ratio', 'frequency_index']: cumul_n[key] += data_n.get(key, 0) cumul_n1[key] += data_n1.get(key, 0) cumul_n2[key] += data_n2.get(key, 0) # Recalculate rates for cumulative total_days = sum(self._get_days_in_month(year, m) for m in range(1, end_month + 1)) room_cap = self._get_room_capacity(establishment_id) bed_cap = self._get_bed_capacity(establishment_id) if room_cap * total_days > 0: cumul_n['occupancy_rate'] = cumul_n.get('rooms_sold', 0) / (room_cap * total_days) cumul_n1['occupancy_rate'] = cumul_n1.get('rooms_sold', 0) / (room_cap * total_days) cumul_n2['occupancy_rate'] = cumul_n2.get('rooms_sold', 0) / (room_cap * total_days) if bed_cap * total_days > 0: cumul_n['bed_occupancy_rate'] = cumul_n.get('beds_sold', 0) / (bed_cap * total_days) cumul_n1['bed_occupancy_rate'] = cumul_n1.get('beds_sold', 0) / (bed_cap * total_days) cumul_n2['bed_occupancy_rate'] = cumul_n2.get('beds_sold', 0) / (bed_cap * total_days) if cumul_n.get('ca_ttc', 0) > 0: cumul_n['fb_ratio'] = cumul_n.get('total_ca_fb', 0) / cumul_n.get('ca_ttc', 1) if cumul_n1.get('ca_ttc', 0) > 0: cumul_n1['fb_ratio'] = cumul_n1.get('total_ca_fb', 0) / cumul_n1.get('ca_ttc', 1) if cumul_n2.get('ca_ttc', 0) > 0: cumul_n2['fb_ratio'] = cumul_n2.get('total_ca_fb', 0) / cumul_n2.get('ca_ttc', 1) # Row 1: Hotel name and column headers ws.cell(row=1, column=1, value=establishment_name) ws.cell(row=1, column=1).font = self.title_font # Column headers with dynamic years ws.cell(row=1, column=2, value=f"Cumul {year}") ws.cell(row=1, column=3, value=f"Cumul {year - 1}") ws.cell(row=1, column=4, value=f"Cumul {year - 2}") ws.cell(row=1, column=5, value="Ecart") ws.cell(row=1, column=6, value="Ecart") # Sub-headers ws.cell(row=2, column=2, value=f"Jan-{month_name}") ws.cell(row=2, column=3, value=f"Jan-{month_name}") ws.cell(row=2, column=4, value=f"Jan-{month_name}") ws.cell(row=2, column=5, value="Cumul - N-1") ws.cell(row=2, column=6, value="Cumul - N-2") # Apply header styles for col in range(2, 7): ws.cell(row=1, column=col).font = self.header_font ws.cell(row=1, column=col).alignment = Alignment(horizontal='center') ws.cell(row=2, column=col).alignment = Alignment(horizontal='center') # Write all sections row = 3 for line_def in EXPLOITATION_INDICATORS: row = self._write_data_row(ws, row, line_def, cumul_n, cumul_n1, cumul_n2) for line_def in REVENUE_LINES: row = self._write_data_row(ws, row, line_def, cumul_n, cumul_n1, cumul_n2) for line_def in EXPENSE_LINES: row = self._write_data_row(ws, row, line_def, cumul_n, cumul_n1, cumul_n2) for line_def in RESULT_LINES: row = self._write_data_row(ws, row, line_def, cumul_n, cumul_n1, cumul_n2) # Column widths ws.column_dimensions['A'].width = 45 for col in range(2, 7): ws.column_dimensions[get_column_letter(col)].width = 14 def _create_ratio_fb_sheet( self, wb: Workbook, establishment_id: int, year: int ): """Create Ratio F&B sheet.""" ws = wb.create_sheet("Ratio F&B") ws.cell(row=1, column=1, value=f"RATIO F&B {year}") ws.cell(row=1, column=1).font = self.title_font ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=14) # Headers ws.cell(row=3, column=1, value="Indicateur") for i, mois in enumerate(MOIS_NAMES_FR, start=2): ws.cell(row=3, column=i, value=mois[:3]) ws.cell(row=3, column=14, value="Total") self._apply_header_style(ws, 3, 1, 14) # Ratio row ws.cell(row=4, column=1, value="Ratio F&B / CA Total") ws.cell(row=4, column=1).font = self.header_font for month in range(1, 13): data = self._get_monthly_data(establishment_id, year, month) ws.cell(row=4, column=month + 1, value=data.get('fb_ratio', 0)) ws.cell(row=4, column=month + 1).number_format = self.percent_format ws.cell(row=4, column=14, value=f"=AVERAGE(B4:M4)") ws.cell(row=4, column=14).number_format = self.percent_format # Column widths ws.column_dimensions['A'].width = 25 for col in range(2, 15): ws.column_dimensions[get_column_letter(col)].width = 10 def _create_cumul_ratio_fb_sheet( self, wb: Workbook, establishment_id: int, year: int ): """Create Cumul Ratio F&B sheet.""" ws = wb.create_sheet("Cumul Ratio F&B") ws.cell(row=1, column=1, value=f"CUMUL RATIO F&B {year}") ws.cell(row=1, column=1).font = self.title_font ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=13) # Headers ws.cell(row=3, column=1, value="Cumul jusqu'à") for i, mois in enumerate(MOIS_NAMES_FR, start=2): ws.cell(row=3, column=i, value=mois[:3]) self._apply_header_style(ws, 3, 1, 13) # Cumul Ratio F&B row ws.cell(row=4, column=1, value="Ratio F&B Cumulé") ws.cell(row=4, column=1).font = self.header_font for end_month in range(1, 13): ws.cell(row=4, column=end_month + 1, value=f"='Ratio F&B'!{get_column_letter(end_month + 1)}4") ws.cell(row=4, column=end_month + 1).number_format = self.percent_format # Column widths ws.column_dimensions['A'].width = 25 for col in range(2, 14): ws.column_dimensions[get_column_letter(col)].width = 10 def generate_realise_report( self, establishment_id: int, year: int, establishment_name: str = "SALY HOTEL" ) -> BytesIO: """ Generate complete Réalisé report Excel file (without budgets). Returns BytesIO containing the Excel file. """ wb = Workbook() # Remove default sheet default_sheet = wb.active wb.remove(default_sheet) # Create Ratio F&B sheets first self._create_ratio_fb_sheet(wb, establishment_id, year) self._create_cumul_ratio_fb_sheet(wb, establishment_id, year) # Monthly sheets for month in range(1, 13): self._create_monthly_realise_sheet( wb, month, establishment_id, year, establishment_name ) # Cumul sheets (February to December) for month in range(2, 13): self._create_cumul_realise_sheet( wb, month, establishment_id, year, establishment_name ) # Save to BytesIO buffer = BytesIO() wb.save(buffer) buffer.seek(0) return buffer def generate_reference_file( self, year: int = 2025 ) -> BytesIO: """ Generate a reference Excel file with sample data. """ wb = Workbook() default_sheet = wb.active wb.remove(default_sheet) import random random.seed(42) # Ratio F&B sheets ws = wb.create_sheet("Ratio F&B") ws.cell(row=1, column=1, value=f"RATIO F&B {year}") ws.cell(row=1, column=1).font = self.title_font ws.cell(row=3, column=1, value="Indicateur") for i, mois in enumerate(MOIS_NAMES_FR, start=2): ws.cell(row=3, column=i, value=mois[:3]) ws.cell(row=3, column=14, value="Total") self._apply_header_style(ws, 3, 1, 14) ws.cell(row=4, column=1, value="Ratio F&B / CA Total") for month in range(1, 13): ws.cell(row=4, column=month + 1, value=random.uniform(0.30, 0.45)) ws.cell(row=4, column=month + 1).number_format = self.percent_format ws.cell(row=4, column=14, value="=AVERAGE(B4:M4)") ws.cell(row=4, column=14).number_format = self.percent_format ws.column_dimensions['A'].width = 25 for col in range(2, 15): ws.column_dimensions[get_column_letter(col)].width = 10 # Cumul Ratio F&B ws = wb.create_sheet("Cumul Ratio F&B") ws.cell(row=1, column=1, value=f"CUMUL RATIO F&B {year}") ws.cell(row=1, column=1).font = self.title_font ws.cell(row=3, column=1, value="Cumul jusqu'à") for i, mois in enumerate(MOIS_NAMES_FR, start=2): ws.cell(row=3, column=i, value=mois[:3]) self._apply_header_style(ws, 3, 1, 13) ws.cell(row=4, column=1, value="Ratio F&B Cumulé") for month in range(1, 13): ws.cell(row=4, column=month + 1, value=f"='Ratio F&B'!{get_column_letter(month + 1)}4") ws.cell(row=4, column=month + 1).number_format = self.percent_format ws.column_dimensions['A'].width = 25 for col in range(2, 14): ws.column_dimensions[get_column_letter(col)].width = 10 # Monthly sheets with sample data for month in range(1, 13): sheet_name = MOIS_NAMES[month - 1] ws = wb.create_sheet(sheet_name) month_name = MOIS_NAMES_FR[month - 1] days_in_month = self._get_days_in_month(year, month) room_capacity = 74 bed_capacity = 148 # Headers ws.cell(row=1, column=1, value="SALY HOTEL") ws.cell(row=1, column=1).font = self.title_font ws.cell(row=1, column=2, value=f"Réalisé {year}") ws.cell(row=1, column=3, value=f"Réalisé {year - 1}") ws.cell(row=1, column=4, value=f"Réalisé {year - 2}") ws.cell(row=1, column=5, value="Ecart") ws.cell(row=1, column=6, value="Ecart") ws.cell(row=2, column=2, value=month_name) ws.cell(row=2, column=3, value=month_name) ws.cell(row=2, column=4, value=month_name) ws.cell(row=2, column=5, value="Réalisé - N-1") ws.cell(row=2, column=6, value="Réalisé - N-2") for col in range(2, 7): ws.cell(row=1, column=col).font = self.header_font ws.cell(row=1, column=col).alignment = Alignment(horizontal='center') ws.cell(row=2, column=col).alignment = Alignment(horizontal='center') # Generate sample data season_factor = 1.0 + 0.3 * (1 if month in [6, 7, 8, 12] else (-0.2 if month in [1, 2, 11] else 0)) rooms_sold_n = int(room_capacity * days_in_month * 0.65 * season_factor) sample_data_n = { "exploitation_days": days_in_month, "room_capacity": room_capacity, "occupancy_rate": rooms_sold_n / (room_capacity * days_in_month), "rooms_sold": rooms_sold_n, "bed_capacity": bed_capacity, "bed_occupancy_rate": rooms_sold_n * 1.8 / (bed_capacity * days_in_month), "beds_sold": int(rooms_sold_n * 1.8), "frequency_index": 1.8, "nights_alloted": int(rooms_sold_n * 0.4), "nights_passage": int(rooms_sold_n * 0.6), "covers": int(rooms_sold_n * 2.5), "pmv_alloted": random.uniform(45000, 65000), "pmv_passage": random.uniform(50000, 75000), "fb_ratio": random.uniform(0.35, 0.42), "revpar": random.uniform(35000, 55000), "trevpar": random.uniform(40000, 60000), "grc": random.uniform(25000, 45000), "ca_hebergement_allote": random.uniform(5000000, 10000000), "ca_restauration_allote": random.uniform(2000000, 5000000), "ca_hebergement_passage": random.uniform(8000000, 15000000), "ca_restauration_passage": random.uniform(3000000, 7000000), "total_ca_hebergement": random.uniform(15000000, 25000000), "total_ca_fb": random.uniform(5000000, 12000000), "telephone": random.uniform(50000, 200000), "boutique": random.uniform(100000, 500000), "blanchisserie": random.uniform(50000, 200000), "spa": random.uniform(200000, 800000), "location_salles": random.uniform(0, 100000), "revenus_location": random.uniform(0, 50000), "divers_ventes": random.uniform(50000, 200000), "autres_ventes": random.uniform(500000, 1500000), "ca_ttc": random.uniform(20000000, 40000000), "tva": random.uniform(2000000, 4000000), "ca_ht": random.uniform(18000000, 36000000), "cout_alimentaire": random.uniform(2000000, 5000000), "autres_couts_ventes": random.uniform(500000, 1500000), "salaires_charges": random.uniform(5000000, 10000000), "total_charges": random.uniform(15000000, 25000000), "resultat_net": random.uniform(-5000000, 10000000), } # Fill in other keys with 0 for line_def in EXPLOITATION_INDICATORS + REVENUE_LINES + EXPENSE_LINES + RESULT_LINES: key = line_def.get("key", "") if key and key not in sample_data_n: sample_data_n[key] = 0 sample_data_n1 = {k: v * random.uniform(0.9, 1.1) if isinstance(v, (int, float)) else v for k, v in sample_data_n.items()} sample_data_n2 = {k: v * random.uniform(0.85, 1.05) if isinstance(v, (int, float)) else v for k, v in sample_data_n.items()} # Write data row = 3 for line_def in EXPLOITATION_INDICATORS: row = self._write_data_row(ws, row, line_def, sample_data_n, sample_data_n1, sample_data_n2) for line_def in REVENUE_LINES: row = self._write_data_row(ws, row, line_def, sample_data_n, sample_data_n1, sample_data_n2) for line_def in EXPENSE_LINES: row = self._write_data_row(ws, row, line_def, sample_data_n, sample_data_n1, sample_data_n2) for line_def in RESULT_LINES: row = self._write_data_row(ws, row, line_def, sample_data_n, sample_data_n1, sample_data_n2) ws.column_dimensions['A'].width = 45 for col in range(2, 7): ws.column_dimensions[get_column_letter(col)].width = 14 # Cumul sheets for end_month in range(2, 13): sheet_name = f"Cumul {MOIS_NAMES[end_month - 1]}" ws = wb.create_sheet(sheet_name) month_name = MOIS_NAMES_FR[end_month - 1] ws.cell(row=1, column=1, value="SALY HOTEL") ws.cell(row=1, column=1).font = self.title_font ws.cell(row=1, column=2, value=f"Cumul {year}") ws.cell(row=1, column=3, value=f"Cumul {year - 1}") ws.cell(row=1, column=4, value=f"Cumul {year - 2}") ws.cell(row=1, column=5, value="Ecart") ws.cell(row=1, column=6, value="Ecart") ws.cell(row=2, column=2, value=f"Jan-{month_name}") ws.cell(row=2, column=3, value=f"Jan-{month_name}") ws.cell(row=2, column=4, value=f"Jan-{month_name}") ws.cell(row=2, column=5, value="Cumul - N-1") ws.cell(row=2, column=6, value="Cumul - N-2") for col in range(2, 7): ws.cell(row=1, column=col).font = self.header_font # Add structure row = 3 for line_def in EXPLOITATION_INDICATORS + REVENUE_LINES + EXPENSE_LINES + RESULT_LINES: label = line_def["label"] ws.cell(row=row, column=1, value=label) if line_def["type"] in ("header_label", "section_header", "total_highlight", "expense_highlight", "subtotal", "result_final"): ws.cell(row=row, column=1).font = self.header_font if line_def["type"] in ("total_highlight", "expense_highlight", "result_final"): ws.cell(row=row, column=1).fill = self.highlight_fill row += 1 ws.column_dimensions['A'].width = 45 for col in range(2, 7): ws.column_dimensions[get_column_letter(col)].width = 14 # Save buffer = BytesIO() wb.save(buffer) buffer.seek(0) return buffer