""" Exploitation Export Service - Generates structured Excel reports for exploitation statistics (US - Usage Statistics) compatible with verif_budget_us_2022.py verification script. Structure du fichier US_ANNEE_XXXX.xlsx : - REAL {year} / REAL {year} CUMUL → réalisations mensuelles et cumulées - REAL {year-1} / REAL {year-1} CUMUL → réalisations année précédente - REAL {year-2} / REAL {year-2} CUMUL → réalisations N-2 - CEG … CEG12 → états mensuels (1=Jan … 12=Déc) - HEBERGEMENT … HEBERGEMENT 12 → détail hébergement - RESTAURATION … RESTAURATION 12 → détail restauration - BOUTIQUES … BOUTIQUES 12 → détail boutiques - AUTRES DEP … AUTRES DEP 12 → autres dépenses - ADMINISTRATION … ADMINISTRATION 12 - ENTRETIEN … ENTRETIEN 12 - ANIMATION … ANIMATION 12 - SYNTHESE … SYNTHESE 12 → synthèse mensuelle """ from datetime import date, datetime from typing import List, Dict, Optional, Any from io import BytesIO from decimal import Decimal import random 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.stats import DailyStats from app.models.folio import FolioItem, Folio from app.models.reservation import Reservation from app.models.room import Room # Constantes MOIS_NAMES = [ "Janvier", "Fevrier", "Mars", "Avril", "Mai", "Juin", "Juillet", "Aout", "Septembre", "Octobre", "Novembre", "Decembre" ] # Familles de feuilles (suffixe vide = mois 1, sinon chiffre pour mois 2-12) def get_family_sheets(family_name: str) -> List[str]: """Generate sheet names for a family (12 months).""" return [family_name] + [f"{family_name} {i}" for i in range(2, 13)] FAMILIES = { "CEG": ["CEG"] + [f"CEG{i}" for i in range(2, 13)], "HEBERGEMENT": get_family_sheets("HEBERGEMENT"), "RESTAURATION": get_family_sheets("RESTAURATION"), "BOUTIQUES": get_family_sheets("BOUTIQUES"), "AUTRES DEP": ["AUTRES DEP"] + [f"AUTRES DEP {i}" for i in range(2, 7)] + ["AUTRE DEP 7"] + [f"AUTRES DEP {i}" for i in range(8, 13)], "ADMINISTRATION": get_family_sheets("ADMINISTRATION"), "ENTRETIEN": get_family_sheets("ENTRETIEN"), "ANIMATION": get_family_sheets("ANIMATION"), "SYNTHESE": get_family_sheets("SYNTHESE"), } # Lignes standard pour CEG (Compte d'Exploitation Général) CEG_LINES = [ {"label": "PRODUITS D'EXPLOITATION", "type": "header"}, {"label": "Hébergement", "type": "revenue", "dept": "HEBERG"}, {"label": "Restauration", "type": "revenue", "dept": "REST"}, {"label": "Bar", "type": "revenue", "dept": "BAR"}, {"label": "Room Service", "type": "revenue", "dept": "ROOM_SERVICE"}, {"label": "Boutiques", "type": "revenue", "dept": "BOUTIQUE"}, {"label": "Autres produits", "type": "revenue", "dept": "AUTRES"}, {"label": "TOTAL PRODUITS", "type": "total_revenue"}, {"label": "", "type": "spacer"}, {"label": "CHARGES D'EXPLOITATION", "type": "header"}, {"label": "Coût matières F&B", "type": "expense", "dept": "FB_COST"}, {"label": "Charges de personnel", "type": "expense", "dept": "PERSONNEL"}, {"label": "Charges externes", "type": "expense", "dept": "EXTERNES"}, {"label": "Impôts et taxes", "type": "expense", "dept": "IMPOTS"}, {"label": "Dotations aux amortissements", "type": "expense", "dept": "AMORT"}, {"label": "TOTAL CHARGES", "type": "total_expense"}, {"label": "", "type": "spacer"}, {"label": "RESULTAT D'EXPLOITATION", "type": "result"}, ] # Lignes pour HEBERGEMENT HEBERGEMENT_LINES = [ {"label": "STATISTIQUES CHAMBRES", "type": "header"}, {"label": "Capacité (chambres)", "type": "stat", "key": "capacity_rooms"}, {"label": "Chambres disponibles", "type": "stat", "key": "rooms_available"}, {"label": "Chambres vendues", "type": "stat", "key": "rooms_sold"}, {"label": "Chambres gratuites", "type": "stat", "key": "rooms_complimentary"}, {"label": "Taux d'occupation (%)", "type": "kpi", "key": "occupancy_rate"}, {"label": "", "type": "spacer"}, {"label": "STATISTIQUES CLIENTS", "type": "header"}, {"label": "Nuitées vendues", "type": "stat", "key": "nights_sold"}, {"label": "Arrivées", "type": "stat", "key": "arrivals"}, {"label": "Départs", "type": "stat", "key": "departures"}, {"label": "Clients en maison", "type": "stat", "key": "in_house"}, {"label": "No-shows", "type": "stat", "key": "no_shows"}, {"label": "Annulations", "type": "stat", "key": "cancellations"}, {"label": "Indice de fréquentation", "type": "kpi", "key": "frequency_index"}, {"label": "", "type": "spacer"}, {"label": "CHIFFRE D'AFFAIRES", "type": "header"}, {"label": "CA Hébergement", "type": "revenue", "key": "revenue_accommodation"}, {"label": "ADR (PM)", "type": "kpi", "key": "adr"}, {"label": "RevPAR", "type": "kpi", "key": "revpar"}, {"label": "Recette moyenne client", "type": "kpi", "key": "average_guest_revenue"}, ] # Lignes pour RESTAURATION RESTAURATION_LINES = [ {"label": "CHIFFRE D'AFFAIRES", "type": "header"}, {"label": "Restaurant", "type": "revenue", "dept": "REST"}, {"label": "Bar", "type": "revenue", "dept": "BAR"}, {"label": "Room Service", "type": "revenue", "dept": "ROOM_SERVICE"}, {"label": "Minibar", "type": "revenue", "dept": "MINIBAR"}, {"label": "TOTAL CA F&B", "type": "total_revenue"}, {"label": "", "type": "spacer"}, {"label": "CHARGES DIRECTES", "type": "header"}, {"label": "Coût matières nourriture", "type": "expense", "dept": "FOOD_COST"}, {"label": "Coût matières boissons", "type": "expense", "dept": "BEV_COST"}, {"label": "TOTAL COUT MATIERES", "type": "total_cost"}, {"label": "", "type": "spacer"}, {"label": "RATIOS", "type": "header"}, {"label": "Ratio coût matières (%)", "type": "ratio", "key": "cost_ratio"}, {"label": "Marge brute (%)", "type": "ratio", "key": "gross_margin"}, ] # Lignes pour BOUTIQUES BOUTIQUES_LINES = [ {"label": "CHIFFRE D'AFFAIRES", "type": "header"}, {"label": "Ventes marchandises", "type": "revenue", "dept": "BOUTIQUE"}, {"label": "Spa & Wellness", "type": "revenue", "dept": "SPA"}, {"label": "Blanchisserie", "type": "revenue", "dept": "BLANCHISSERIE"}, {"label": "Autres services", "type": "revenue", "dept": "AUTRES"}, {"label": "TOTAL CA BOUTIQUES", "type": "total_revenue"}, {"label": "", "type": "spacer"}, {"label": "CHARGES", "type": "header"}, {"label": "Achat marchandises", "type": "expense", "dept": "ACHAT_MARCH"}, {"label": "Fournitures spa", "type": "expense", "dept": "FOUR_SPA"}, {"label": "TOTAL CHARGES", "type": "total_expense"}, {"label": "", "type": "spacer"}, {"label": "MARGE BOUTIQUES", "type": "result"}, ] # Lignes pour AUTRES DEP (Autres Départements) AUTRES_DEP_LINES = [ {"label": "AUTRES REVENUS", "type": "header"}, {"label": "Parking", "type": "revenue", "dept": "PARKING"}, {"label": "Téléphone", "type": "revenue", "dept": "TELEPHONE"}, {"label": "Location salles", "type": "revenue", "dept": "LOC_SALLES"}, {"label": "Commissions", "type": "revenue", "dept": "COMMISSIONS"}, {"label": "Divers", "type": "revenue", "dept": "DIVERS"}, {"label": "TOTAL AUTRES REVENUS", "type": "total_revenue"}, ] # Lignes pour ADMINISTRATION ADMINISTRATION_LINES = [ {"label": "CHARGES ADMINISTRATIVES", "type": "header"}, {"label": "Salaires direction", "type": "expense", "dept": "SAL_DIRECTION"}, {"label": "Salaires administratifs", "type": "expense", "dept": "SAL_ADMIN"}, {"label": "Frais de bureau", "type": "expense", "dept": "FRAIS_BUREAU"}, {"label": "Honoraires", "type": "expense", "dept": "HONORAIRES"}, {"label": "Assurances", "type": "expense", "dept": "ASSURANCES"}, {"label": "Télécommunications", "type": "expense", "dept": "TELECOM"}, {"label": "TOTAL ADMINISTRATION", "type": "total_expense"}, ] # Lignes pour ENTRETIEN ENTRETIEN_LINES = [ {"label": "CHARGES D'ENTRETIEN", "type": "header"}, {"label": "Salaires technique", "type": "expense", "dept": "SAL_TECH"}, {"label": "Énergie (électricité)", "type": "expense", "dept": "ELECTRICITE"}, {"label": "Eau", "type": "expense", "dept": "EAU"}, {"label": "Gaz / Fuel", "type": "expense", "dept": "GAZ"}, {"label": "Entretien bâtiment", "type": "expense", "dept": "ENT_BAT"}, {"label": "Entretien équipements", "type": "expense", "dept": "ENT_EQUIP"}, {"label": "Fournitures entretien", "type": "expense", "dept": "FOUR_ENT"}, {"label": "TOTAL ENTRETIEN", "type": "total_expense"}, ] # Lignes pour ANIMATION ANIMATION_LINES = [ {"label": "CHARGES ANIMATION", "type": "header"}, {"label": "Salaires animation", "type": "expense", "dept": "SAL_ANIM"}, {"label": "Animations & spectacles", "type": "expense", "dept": "SPECTACLES"}, {"label": "Sports & loisirs", "type": "expense", "dept": "SPORTS"}, {"label": "Excursions", "type": "expense", "dept": "EXCURSIONS"}, {"label": "Fournitures animation", "type": "expense", "dept": "FOUR_ANIM"}, {"label": "TOTAL ANIMATION", "type": "total_expense"}, ] # Lignes pour SYNTHESE SYNTHESE_LINES = [ {"label": "SYNTHESE MENSUELLE", "type": "header"}, {"label": "", "type": "spacer"}, {"label": "CHIFFRE D'AFFAIRES", "type": "header"}, {"label": "Hébergement", "type": "revenue_ref", "ref_sheet": "CEG", "ref_row": 6}, {"label": "Restauration (F&B)", "type": "revenue_ref", "ref_sheet": "CEG", "ref_row": 7}, {"label": "Boutiques & Autres", "type": "revenue_ref", "ref_sheet": "CEG", "ref_row": 10}, {"label": "TOTAL CA", "type": "total_revenue"}, {"label": "", "type": "spacer"}, {"label": "INDICATEURS CLES", "type": "header"}, {"label": "Taux d'occupation", "type": "kpi_ref", "ref_sheet": "HEBERGEMENT", "ref_row": 10}, {"label": "ADR", "type": "kpi_ref", "ref_sheet": "HEBERGEMENT", "ref_row": 22}, {"label": "RevPAR", "type": "kpi_ref", "ref_sheet": "HEBERGEMENT", "ref_row": 23}, {"label": "", "type": "spacer"}, {"label": "RESULTAT", "type": "header"}, {"label": "Résultat d'exploitation", "type": "result_ref", "ref_sheet": "CEG", "ref_row": 22}, ] class ExploitationExportService: """Service for exporting exploitation statistics to Excel format.""" 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=14) self.money_format = '#,##0' self.percent_format = '0.0%' self.decimal_format = '0.00' self.header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid") self.header_font_white = Font(bold=True, color="FFFFFF", size=10) self.subheader_fill = PatternFill(start_color="95B3D7", end_color="95B3D7", fill_type="solid") self.total_fill = PatternFill(start_color="DCE6F1", end_color="DCE6F1", fill_type="solid") self.result_fill = PatternFill(start_color="C5D9A4", end_color="C5D9A4", fill_type="solid") self.thin_border = Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) def _apply_header_style(self, ws: Worksheet, row: int, max_col: int): """Apply header style to a row.""" for col in range(1, max_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 get_monthly_stats( self, establishment_id: int, year: int, month: int ) -> Dict[str, Any]: """Get aggregated statistics for a specific month.""" from calendar import monthrange _, last_day = monthrange(year, month) start_date = date(year, month, 1) end_date = date(year, month, last_day) # Query daily stats stats = self.db.query(DailyStats).filter( DailyStats.establishment_id == establishment_id, DailyStats.stat_date >= start_date, DailyStats.stat_date <= end_date ).all() if not stats: return self._empty_monthly_stats() # Aggregate result = { 'rooms_available': sum(s.rooms_available or 0 for s in stats), 'rooms_sold': sum(s.rooms_sold or 0 for s in stats), 'rooms_complimentary': sum(s.rooms_complimentary or 0 for s in stats), 'nights_sold': sum(s.nights_sold or 0 for s in stats), 'arrivals': sum(s.arrivals or 0 for s in stats), 'departures': sum(s.departures or 0 for s in stats), 'in_house': sum(s.in_house or 0 for s in stats) // max(len(stats), 1), # Average 'no_shows': sum(s.no_shows or 0 for s in stats), 'cancellations': sum(s.cancellations or 0 for s in stats), 'revenue_accommodation': sum(s.revenue_accommodation or 0 for s in stats), 'revenue_fb': sum(s.revenue_fb or 0 for s in stats), 'revenue_boutique': sum(s.revenue_boutique or 0 for s in stats), 'revenue_other': sum(s.revenue_other or 0 for s in stats), 'revenue_total': sum(s.revenue_total or 0 for s in stats), } # Calculate KPIs result['occupancy_rate'] = (result['rooms_sold'] / result['rooms_available'] * 100) if result['rooms_available'] > 0 else 0 result['adr'] = (result['revenue_accommodation'] / result['rooms_sold']) if result['rooms_sold'] > 0 else 0 result['revpar'] = (result['revenue_accommodation'] / result['rooms_available']) if result['rooms_available'] > 0 else 0 result['frequency_index'] = (result['nights_sold'] / result['rooms_sold']) if result['rooms_sold'] > 0 else 0 result['average_guest_revenue'] = (result['revenue_total'] / result['nights_sold']) if result['nights_sold'] > 0 else 0 return result def _empty_monthly_stats(self) -> Dict[str, Any]: """Return empty stats structure.""" return { 'rooms_available': 0, 'rooms_sold': 0, 'rooms_complimentary': 0, 'nights_sold': 0, 'arrivals': 0, 'departures': 0, 'in_house': 0, 'no_shows': 0, 'cancellations': 0, 'revenue_accommodation': 0, 'revenue_fb': 0, 'revenue_boutique': 0, 'revenue_other': 0, 'revenue_total': 0, 'occupancy_rate': 0, 'adr': 0, 'revpar': 0, 'frequency_index': 0, 'average_guest_revenue': 0, } def _create_real_sheet( self, wb: Workbook, sheet_name: str, year: int, establishment_id: int, is_cumul: bool = False ): """Create REAL {year} or REAL {year} CUMUL sheet.""" ws = wb.create_sheet(sheet_name) # Title title = f"RÉALISATIONS {year}" + (" - CUMUL" if is_cumul else "") ws.cell(row=1, column=1, value=title) 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=4, column=1, value="Code") ws.cell(row=4, column=2, value="Libellé") for i, mois in enumerate(MOIS_NAMES, start=3): ws.cell(row=4, column=i, value=mois[:3].upper()) ws.cell(row=4, column=15, value="TOTAL") self._apply_header_style(ws, 4, 15) # Data rows row = 5 for line_def in CEG_LINES: label = line_def["label"] line_type = line_def["type"] if line_type == "spacer": row += 1 continue ws.cell(row=row, column=2, value=label) if line_type == "header": ws.cell(row=row, column=2).font = self.header_font ws.cell(row=row, column=2).fill = self.subheader_fill elif line_type in ("revenue", "expense"): dept = line_def.get("dept", "") ws.cell(row=row, column=1, value=dept) # Reference CEG sheets for values for month in range(1, 13): ceg_sheet = FAMILIES["CEG"][month - 1] ws.cell(row=row, column=month + 2, value=f"='{ceg_sheet}'!E{row}") ws.cell(row=row, column=month + 2).number_format = self.money_format # Total ws.cell(row=row, column=15, value=f"=SUM(C{row}:N{row})") ws.cell(row=row, column=15).number_format = self.money_format elif line_type == "total_revenue": ws.cell(row=row, column=2).font = self.header_font for col in range(3, 16): ws.cell(row=row, column=col, value=f"=SUM({get_column_letter(col)}6:{get_column_letter(col)}11)") ws.cell(row=row, column=col).number_format = self.money_format ws.cell(row=row, column=col).fill = self.total_fill elif line_type == "total_expense": ws.cell(row=row, column=2).font = self.header_font for col in range(3, 16): ws.cell(row=row, column=col, value=f"=SUM({get_column_letter(col)}14:{get_column_letter(col)}18)") ws.cell(row=row, column=col).number_format = self.money_format ws.cell(row=row, column=col).fill = self.total_fill elif line_type == "result": ws.cell(row=row, column=2).font = self.header_font for col in range(3, 16): # Total Produits - Total Charges ws.cell(row=row, column=col, value=f"={get_column_letter(col)}12-{get_column_letter(col)}19") ws.cell(row=row, column=col).number_format = self.money_format ws.cell(row=row, column=col).fill = self.result_fill row += 1 # Column widths ws.column_dimensions['A'].width = 12 ws.column_dimensions['B'].width = 30 for col in range(3, 16): ws.column_dimensions[get_column_letter(col)].width = 12 def _create_ceg_sheet( self, wb: Workbook, month: int, year: int, establishment_id: int ): """Create CEG (Compte d'Exploitation Général) sheet for a month.""" sheet_name = FAMILIES["CEG"][month - 1] ws = wb.create_sheet(sheet_name) mois_name = MOIS_NAMES[month - 1].upper() # Title ws.cell(row=1, column=1, value=f"COMPTE D'EXPLOITATION GÉNÉRAL - {mois_name} {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=8) # Headers with year comparison headers = ["Code", "Libellé", "Budget", f"Réel {year}", "Écart", f"Réel {year-1}", f"Réel {year-2}", "Évolution"] for col, header in enumerate(headers, start=1): ws.cell(row=4, column=col, value=header) self._apply_header_style(ws, 4, 8) # Data rows row = 5 for line_def in CEG_LINES: label = line_def["label"] line_type = line_def["type"] if line_type == "spacer": row += 1 continue ws.cell(row=row, column=2, value=label) if line_type == "header": ws.cell(row=row, column=2).font = self.header_font ws.cell(row=row, column=2).fill = self.subheader_fill elif line_type in ("revenue", "expense"): dept = line_def.get("dept", "") ws.cell(row=row, column=1, value=dept) # Budget (column C) ws.cell(row=row, column=3).number_format = self.money_format # Real current year (column D) - Reference from REAL sheet ws.cell(row=row, column=4, value=f"='REAL {year}'!{get_column_letter(month + 2)}{row}") ws.cell(row=row, column=4).number_format = self.money_format # Écart (column E) ws.cell(row=row, column=5, value=f"=D{row}-C{row}") ws.cell(row=row, column=5).number_format = self.money_format # Real N-1 (column F) ws.cell(row=row, column=6, value=f"='REAL {year-1}'!{get_column_letter(month + 2)}{row}") ws.cell(row=row, column=6).number_format = self.money_format # Real N-2 (column G) ws.cell(row=row, column=7, value=f"='REAL {year-2}'!{get_column_letter(month + 2)}{row}") ws.cell(row=row, column=7).number_format = self.money_format # Evolution % (column H) ws.cell(row=row, column=8, value=f"=IF(F{row}<>0,(D{row}-F{row})/F{row},0)") ws.cell(row=row, column=8).number_format = self.percent_format elif line_type == "total_revenue": ws.cell(row=row, column=2).font = self.header_font for col in range(3, 9): if col == 8: ws.cell(row=row, column=col, value=f"=IF(F{row}<>0,(D{row}-F{row})/F{row},0)") ws.cell(row=row, column=col).number_format = self.percent_format else: ws.cell(row=row, column=col, value=f"=SUM({get_column_letter(col)}6:{get_column_letter(col)}11)") ws.cell(row=row, column=col).number_format = self.money_format ws.cell(row=row, column=col).fill = self.total_fill elif line_type == "total_expense": ws.cell(row=row, column=2).font = self.header_font for col in range(3, 9): if col == 8: ws.cell(row=row, column=col, value=f"=IF(F{row}<>0,(D{row}-F{row})/F{row},0)") ws.cell(row=row, column=col).number_format = self.percent_format else: ws.cell(row=row, column=col, value=f"=SUM({get_column_letter(col)}14:{get_column_letter(col)}18)") ws.cell(row=row, column=col).number_format = self.money_format ws.cell(row=row, column=col).fill = self.total_fill elif line_type == "result": ws.cell(row=row, column=2).font = self.header_font for col in range(3, 9): if col == 8: ws.cell(row=row, column=col, value=f"=IF(F{row}<>0,(D{row}-F{row})/F{row},0)") ws.cell(row=row, column=col).number_format = self.percent_format else: ws.cell(row=row, column=col, value=f"={get_column_letter(col)}12-{get_column_letter(col)}19") ws.cell(row=row, column=col).number_format = self.money_format ws.cell(row=row, column=col).fill = self.result_fill row += 1 # Column widths ws.column_dimensions['A'].width = 12 ws.column_dimensions['B'].width = 30 for col in range(3, 9): ws.column_dimensions[get_column_letter(col)].width = 14 def _create_family_sheet( self, wb: Workbook, family: str, month: int, year: int, lines: List[Dict] ): """Create a detail sheet for a specific family and month.""" sheet_name = FAMILIES[family][month - 1] ws = wb.create_sheet(sheet_name) mois_name = MOIS_NAMES[month - 1].upper() # Title ws.cell(row=1, column=1, value=f"{family} - {mois_name} {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=7) # Headers headers = ["Code", "Libellé", "Budget", f"Réel {year}", "Écart", f"N-1", "Évol."] for col, header in enumerate(headers, start=1): ws.cell(row=4, column=col, value=header) self._apply_header_style(ws, 4, 7) # Data rows row = 5 for line_def in lines: label = line_def["label"] line_type = line_def["type"] if line_type == "spacer": row += 1 continue ws.cell(row=row, column=2, value=label) if line_type == "header": ws.cell(row=row, column=2).font = self.header_font ws.cell(row=row, column=2).fill = self.subheader_fill elif line_type in ("revenue", "expense", "stat", "kpi"): dept_or_key = line_def.get("dept", line_def.get("key", "")) ws.cell(row=row, column=1, value=dept_or_key) # Budget ws.cell(row=row, column=3).number_format = self.money_format # Real ws.cell(row=row, column=4).number_format = self.money_format # Écart ws.cell(row=row, column=5, value=f"=D{row}-C{row}") ws.cell(row=row, column=5).number_format = self.money_format # N-1 ws.cell(row=row, column=6).number_format = self.money_format # Evolution ws.cell(row=row, column=7, value=f"=IF(F{row}<>0,(D{row}-F{row})/F{row},0)") ws.cell(row=row, column=7).number_format = self.percent_format elif "total" in line_type: ws.cell(row=row, column=2).font = self.header_font for col in range(3, 8): ws.cell(row=row, column=col).fill = self.total_fill if col == 7: ws.cell(row=row, column=col, value=f"=IF(F{row}<>0,(D{row}-F{row})/F{row},0)") ws.cell(row=row, column=col).number_format = self.percent_format else: ws.cell(row=row, column=col).number_format = self.money_format elif line_type == "result": ws.cell(row=row, column=2).font = self.header_font for col in range(3, 8): ws.cell(row=row, column=col).fill = self.result_fill ws.cell(row=row, column=col).number_format = self.money_format row += 1 # Column widths ws.column_dimensions['A'].width = 15 ws.column_dimensions['B'].width = 28 for col in range(3, 8): ws.column_dimensions[get_column_letter(col)].width = 12 def _create_synthese_sheet( self, wb: Workbook, month: int, year: int ): """Create SYNTHESE sheet for a month with references to other sheets.""" sheet_name = FAMILIES["SYNTHESE"][month - 1] ceg_sheet = FAMILIES["CEG"][month - 1] heberg_sheet = FAMILIES["HEBERGEMENT"][month - 1] ws = wb.create_sheet(sheet_name) mois_name = MOIS_NAMES[month - 1].upper() # Title ws.cell(row=1, column=1, value=f"SYNTHÈSE - {mois_name} {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=5) # Headers headers = ["Indicateur", "Valeur", "Budget", "Écart", "N-1"] for col, header in enumerate(headers, start=1): ws.cell(row=3, column=col, value=header) self._apply_header_style(ws, 3, 5) # Data rows with references row = 4 for line_def in SYNTHESE_LINES: label = line_def["label"] line_type = line_def["type"] if line_type == "spacer": row += 1 continue ws.cell(row=row, column=1, value=label) if line_type == "header": ws.cell(row=row, column=1).font = self.header_font ws.cell(row=row, column=1).fill = self.subheader_fill elif line_type in ("revenue_ref", "kpi_ref", "result_ref"): ref_sheet = line_def.get("ref_sheet", "CEG") ref_row = line_def.get("ref_row", 5) # Get correct sheet name for this month if ref_sheet == "CEG": actual_sheet = ceg_sheet elif ref_sheet == "HEBERGEMENT": actual_sheet = heberg_sheet else: actual_sheet = ref_sheet # Value (reference to actual sheet) ws.cell(row=row, column=2, value=f"='{actual_sheet}'!D{ref_row}") ws.cell(row=row, column=2).number_format = self.money_format # Budget ws.cell(row=row, column=3, value=f"='{actual_sheet}'!C{ref_row}") ws.cell(row=row, column=3).number_format = self.money_format # Écart ws.cell(row=row, column=4, value=f"=B{row}-C{row}") ws.cell(row=row, column=4).number_format = self.money_format # N-1 ws.cell(row=row, column=5, value=f"='{actual_sheet}'!F{ref_row}") ws.cell(row=row, column=5).number_format = self.money_format elif line_type == "total_revenue": ws.cell(row=row, column=1).font = self.header_font for col in range(2, 6): ws.cell(row=row, column=col).fill = self.total_fill ws.cell(row=row, column=col).number_format = self.money_format row += 1 # Column widths ws.column_dimensions['A'].width = 25 for col in range(2, 6): ws.column_dimensions[get_column_letter(col)].width = 15 def generate_exploitation_report( self, establishment_id: int, year: int ) -> BytesIO: """ Generate complete exploitation statistics report Excel file. Returns BytesIO containing the Excel file. """ wb = Workbook() # Remove default sheet default_sheet = wb.active wb.remove(default_sheet) # Create REAL sheets for 3 years for y in [year, year - 1, year - 2]: self._create_real_sheet(wb, f"REAL {y}", y, establishment_id) self._create_real_sheet(wb, f"REAL {y} CUMUL", y, establishment_id, is_cumul=True) # Create CEG sheets (12 months) for month in range(1, 13): self._create_ceg_sheet(wb, month, year, establishment_id) # Create family detail sheets (12 months each) family_lines = { "HEBERGEMENT": HEBERGEMENT_LINES, "RESTAURATION": RESTAURATION_LINES, "BOUTIQUES": BOUTIQUES_LINES, "AUTRES DEP": AUTRES_DEP_LINES, "ADMINISTRATION": ADMINISTRATION_LINES, "ENTRETIEN": ENTRETIEN_LINES, "ANIMATION": ANIMATION_LINES, } for family, lines in family_lines.items(): for month in range(1, 13): self._create_family_sheet(wb, family, month, year, lines) # Create SYNTHESE sheets (12 months) for month in range(1, 13): self._create_synthese_sheet(wb, month, year) # Save to BytesIO buffer = BytesIO() wb.save(buffer) buffer.seek(0) return buffer def generate_reference_file( self, year: int = 2022, output_path: Optional[str] = None ) -> BytesIO: """ Generate a reference Excel file with sample data. Used for testing the verification script. """ wb = Workbook() default_sheet = wb.active wb.remove(default_sheet) random.seed(42) # Reproducible # Generate sample data for each year sample_data = {} for y in [year, year - 1, year - 2]: sample_data[y] = self._generate_sample_year_data(y) # Create REAL sheets for y in [year, year - 1, year - 2]: self._create_real_sheet_with_data(wb, f"REAL {y}", y, sample_data[y]) self._create_real_cumul_sheet_with_data(wb, f"REAL {y} CUMUL", y, sample_data[y]) # Create CEG sheets for month in range(1, 13): self._create_ceg_sheet_with_data(wb, month, year, sample_data) # Create family sheets family_lines = { "HEBERGEMENT": HEBERGEMENT_LINES, "RESTAURATION": RESTAURATION_LINES, "BOUTIQUES": BOUTIQUES_LINES, "AUTRES DEP": AUTRES_DEP_LINES, "ADMINISTRATION": ADMINISTRATION_LINES, "ENTRETIEN": ENTRETIEN_LINES, "ANIMATION": ANIMATION_LINES, } for family, lines in family_lines.items(): for month in range(1, 13): self._create_family_sheet_with_data(wb, family, month, year, lines, sample_data) # Create SYNTHESE sheets for month in range(1, 13): self._create_synthese_sheet(wb, month, year) # Save buffer = BytesIO() wb.save(buffer) buffer.seek(0) if output_path: with open(output_path, 'wb') as f: f.write(buffer.getvalue()) buffer.seek(0) return buffer def _generate_sample_year_data(self, year: int) -> Dict[int, Dict[str, float]]: """Generate sample monthly data for a year.""" data = {} base_multiplier = 1.0 + (year - 2020) * 0.05 # 5% growth per year for month in range(1, 13): # Seasonal variation season_factor = 1.0 if month in [7, 8, 12]: # High season season_factor = 1.3 elif month in [1, 2, 11]: # Low season season_factor = 0.7 data[month] = { # Revenue "HEBERG": random.randint(15000000, 25000000) * base_multiplier * season_factor, "REST": random.randint(5000000, 10000000) * base_multiplier * season_factor, "BAR": random.randint(2000000, 5000000) * base_multiplier * season_factor, "ROOM_SERVICE": random.randint(1000000, 3000000) * base_multiplier * season_factor, "BOUTIQUE": random.randint(500000, 2000000) * base_multiplier * season_factor, "AUTRES": random.randint(300000, 1000000) * base_multiplier * season_factor, # Expenses "FB_COST": random.randint(3000000, 6000000) * base_multiplier, "PERSONNEL": random.randint(8000000, 12000000) * base_multiplier, "EXTERNES": random.randint(2000000, 4000000) * base_multiplier, "IMPOTS": random.randint(1000000, 2000000) * base_multiplier, "AMORT": random.randint(1500000, 2500000) * base_multiplier, # Stats "rooms_available": 30 * (28 + month % 3), "rooms_sold": int(30 * (28 + month % 3) * 0.65 * season_factor), "occupancy_rate": 65 * season_factor, } return data def _create_real_sheet_with_data( self, wb: Workbook, sheet_name: str, year: int, year_data: Dict[int, Dict[str, float]] ): """Create REAL sheet with actual sample data.""" ws = wb.create_sheet(sheet_name) # Title ws.cell(row=1, column=1, value=f"RÉALISATIONS {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=15) # Headers ws.cell(row=4, column=1, value="Code") ws.cell(row=4, column=2, value="Libellé") for i, mois in enumerate(MOIS_NAMES, start=3): ws.cell(row=4, column=i, value=mois[:3].upper()) ws.cell(row=4, column=15, value="TOTAL") self._apply_header_style(ws, 4, 15) # Data rows row = 5 for line_def in CEG_LINES: label = line_def["label"] line_type = line_def["type"] if line_type == "spacer": row += 1 continue ws.cell(row=row, column=2, value=label) if line_type == "header": ws.cell(row=row, column=2).font = self.header_font ws.cell(row=row, column=2).fill = self.subheader_fill elif line_type in ("revenue", "expense"): dept = line_def.get("dept", "") ws.cell(row=row, column=1, value=dept) for month in range(1, 13): value = year_data.get(month, {}).get(dept, 0) ws.cell(row=row, column=month + 2, value=value) ws.cell(row=row, column=month + 2).number_format = self.money_format ws.cell(row=row, column=15, value=f"=SUM(C{row}:N{row})") ws.cell(row=row, column=15).number_format = self.money_format elif line_type == "total_revenue": ws.cell(row=row, column=2).font = self.header_font for col in range(3, 16): ws.cell(row=row, column=col, value=f"=SUM({get_column_letter(col)}6:{get_column_letter(col)}11)") ws.cell(row=row, column=col).number_format = self.money_format ws.cell(row=row, column=col).fill = self.total_fill elif line_type == "total_expense": ws.cell(row=row, column=2).font = self.header_font for col in range(3, 16): ws.cell(row=row, column=col, value=f"=SUM({get_column_letter(col)}14:{get_column_letter(col)}18)") ws.cell(row=row, column=col).number_format = self.money_format ws.cell(row=row, column=col).fill = self.total_fill elif line_type == "result": ws.cell(row=row, column=2).font = self.header_font for col in range(3, 16): ws.cell(row=row, column=col, value=f"={get_column_letter(col)}12-{get_column_letter(col)}19") ws.cell(row=row, column=col).number_format = self.money_format ws.cell(row=row, column=col).fill = self.result_fill row += 1 # Column widths ws.column_dimensions['A'].width = 12 ws.column_dimensions['B'].width = 30 for col in range(3, 16): ws.column_dimensions[get_column_letter(col)].width = 11 def _create_real_cumul_sheet_with_data( self, wb: Workbook, sheet_name: str, year: int, year_data: Dict[int, Dict[str, float]] ): """Create REAL CUMUL sheet with cumulative data.""" base_sheet = f"REAL {year}" ws = wb.create_sheet(sheet_name) # Title ws.cell(row=1, column=1, value=f"RÉALISATIONS {year} - CUMUL") ws.cell(row=1, column=1).font = self.title_font ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=15) # Headers ws.cell(row=4, column=1, value="Code") ws.cell(row=4, column=2, value="Libellé") for i, mois in enumerate(MOIS_NAMES, start=3): ws.cell(row=4, column=i, value=f"Cum {mois[:3]}") ws.cell(row=4, column=15, value="TOTAL") self._apply_header_style(ws, 4, 15) # Data rows with cumulative formulas row = 5 for line_def in CEG_LINES: label = line_def["label"] line_type = line_def["type"] if line_type == "spacer": row += 1 continue ws.cell(row=row, column=2, value=label) if line_type == "header": ws.cell(row=row, column=2).font = self.header_font ws.cell(row=row, column=2).fill = self.subheader_fill elif line_type in ("revenue", "expense", "total_revenue", "total_expense", "result"): dept = line_def.get("dept", "") if dept: ws.cell(row=row, column=1, value=dept) # Cumulative formulas for month in range(1, 13): col = month + 2 # Sum from column C (Jan) to current column formula = f"=SUM('{base_sheet}'!C{row}:{get_column_letter(col)}{row})" ws.cell(row=row, column=col, value=formula) ws.cell(row=row, column=col).number_format = self.money_format if line_type in ("total_revenue", "total_expense"): ws.cell(row=row, column=col).fill = self.total_fill elif line_type == "result": ws.cell(row=row, column=col).fill = self.result_fill ws.cell(row=row, column=15, value=f"=N{row}") ws.cell(row=row, column=15).number_format = self.money_format row += 1 # Column widths ws.column_dimensions['A'].width = 12 ws.column_dimensions['B'].width = 30 for col in range(3, 16): ws.column_dimensions[get_column_letter(col)].width = 11 def _create_ceg_sheet_with_data( self, wb: Workbook, month: int, year: int, all_data: Dict[int, Dict[int, Dict[str, float]]] ): """Create CEG sheet with sample data.""" sheet_name = FAMILIES["CEG"][month - 1] ws = wb.create_sheet(sheet_name) mois_name = MOIS_NAMES[month - 1].upper() # Title ws.cell(row=1, column=1, value=f"COMPTE D'EXPLOITATION GÉNÉRAL - {mois_name} {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=8) # Headers headers = ["Code", "Libellé", "Budget", f"Réel {year}", "Écart", f"Réel {year-1}", f"Réel {year-2}", "Évol."] for col, header in enumerate(headers, start=1): ws.cell(row=4, column=col, value=header) self._apply_header_style(ws, 4, 8) # Data row = 5 for line_def in CEG_LINES: label = line_def["label"] line_type = line_def["type"] if line_type == "spacer": row += 1 continue ws.cell(row=row, column=2, value=label) if line_type == "header": ws.cell(row=row, column=2).font = self.header_font ws.cell(row=row, column=2).fill = self.subheader_fill elif line_type in ("revenue", "expense"): dept = line_def.get("dept", "") ws.cell(row=row, column=1, value=dept) # Get values for each year val_current = all_data.get(year, {}).get(month, {}).get(dept, 0) val_n1 = all_data.get(year - 1, {}).get(month, {}).get(dept, 0) val_n2 = all_data.get(year - 2, {}).get(month, {}).get(dept, 0) budget = val_current * random.uniform(0.95, 1.05) ws.cell(row=row, column=3, value=budget) ws.cell(row=row, column=3).number_format = self.money_format ws.cell(row=row, column=4, value=val_current) ws.cell(row=row, column=4).number_format = self.money_format ws.cell(row=row, column=5, value=f"=D{row}-C{row}") ws.cell(row=row, column=5).number_format = self.money_format ws.cell(row=row, column=6, value=val_n1) ws.cell(row=row, column=6).number_format = self.money_format ws.cell(row=row, column=7, value=val_n2) ws.cell(row=row, column=7).number_format = self.money_format ws.cell(row=row, column=8, value=f"=IF(F{row}<>0,(D{row}-F{row})/F{row},0)") ws.cell(row=row, column=8).number_format = self.percent_format elif line_type == "total_revenue": ws.cell(row=row, column=2).font = self.header_font for col in range(3, 9): if col == 5: ws.cell(row=row, column=col, value=f"=D{row}-C{row}") elif col == 8: ws.cell(row=row, column=col, value=f"=IF(F{row}<>0,(D{row}-F{row})/F{row},0)") ws.cell(row=row, column=col).number_format = self.percent_format else: ws.cell(row=row, column=col, value=f"=SUM({get_column_letter(col)}6:{get_column_letter(col)}11)") if col != 8: ws.cell(row=row, column=col).number_format = self.money_format ws.cell(row=row, column=col).fill = self.total_fill elif line_type == "total_expense": ws.cell(row=row, column=2).font = self.header_font for col in range(3, 9): if col == 5: ws.cell(row=row, column=col, value=f"=D{row}-C{row}") elif col == 8: ws.cell(row=row, column=col, value=f"=IF(F{row}<>0,(D{row}-F{row})/F{row},0)") ws.cell(row=row, column=col).number_format = self.percent_format else: ws.cell(row=row, column=col, value=f"=SUM({get_column_letter(col)}14:{get_column_letter(col)}18)") if col != 8: ws.cell(row=row, column=col).number_format = self.money_format ws.cell(row=row, column=col).fill = self.total_fill elif line_type == "result": ws.cell(row=row, column=2).font = self.header_font for col in range(3, 9): if col == 5: ws.cell(row=row, column=col, value=f"=D{row}-C{row}") elif col == 8: ws.cell(row=row, column=col, value=f"=IF(F{row}<>0,(D{row}-F{row})/F{row},0)") ws.cell(row=row, column=col).number_format = self.percent_format else: ws.cell(row=row, column=col, value=f"={get_column_letter(col)}12-{get_column_letter(col)}19") if col != 8: ws.cell(row=row, column=col).number_format = self.money_format ws.cell(row=row, column=col).fill = self.result_fill row += 1 # Column widths ws.column_dimensions['A'].width = 12 ws.column_dimensions['B'].width = 30 for col in range(3, 9): ws.column_dimensions[get_column_letter(col)].width = 14 def _create_family_sheet_with_data( self, wb: Workbook, family: str, month: int, year: int, lines: List[Dict], all_data: Dict[int, Dict[int, Dict[str, float]]] ): """Create family detail sheet with sample data.""" sheet_name = FAMILIES[family][month - 1] ws = wb.create_sheet(sheet_name) mois_name = MOIS_NAMES[month - 1].upper() # Title ws.cell(row=1, column=1, value=f"{family} - {mois_name} {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=7) # Headers headers = ["Code", "Libellé", "Budget", f"Réel {year}", "Écart", f"N-1", "Évol."] for col, header in enumerate(headers, start=1): ws.cell(row=4, column=col, value=header) self._apply_header_style(ws, 4, 7) # Data row = 5 for line_def in lines: label = line_def["label"] line_type = line_def["type"] if line_type == "spacer": row += 1 continue ws.cell(row=row, column=2, value=label) if line_type == "header": ws.cell(row=row, column=2).font = self.header_font ws.cell(row=row, column=2).fill = self.subheader_fill elif line_type in ("revenue", "expense", "stat", "kpi"): dept_or_key = line_def.get("dept", line_def.get("key", "")) ws.cell(row=row, column=1, value=dept_or_key) val_current = all_data.get(year, {}).get(month, {}).get(dept_or_key, random.randint(100000, 5000000)) val_n1 = all_data.get(year - 1, {}).get(month, {}).get(dept_or_key, val_current * 0.95) budget = val_current * random.uniform(0.95, 1.05) ws.cell(row=row, column=3, value=budget) ws.cell(row=row, column=3).number_format = self.money_format ws.cell(row=row, column=4, value=val_current) ws.cell(row=row, column=4).number_format = self.money_format ws.cell(row=row, column=5, value=f"=D{row}-C{row}") ws.cell(row=row, column=5).number_format = self.money_format ws.cell(row=row, column=6, value=val_n1) ws.cell(row=row, column=6).number_format = self.money_format ws.cell(row=row, column=7, value=f"=IF(F{row}<>0,(D{row}-F{row})/F{row},0)") ws.cell(row=row, column=7).number_format = self.percent_format elif "total" in line_type or line_type == "result": ws.cell(row=row, column=2).font = self.header_font fill = self.result_fill if line_type == "result" else self.total_fill for col in range(3, 8): ws.cell(row=row, column=col).fill = fill if col == 7: ws.cell(row=row, column=col, value=f"=IF(F{row}<>0,(D{row}-F{row})/F{row},0)") ws.cell(row=row, column=col).number_format = self.percent_format else: ws.cell(row=row, column=col).number_format = self.money_format row += 1 # Column widths ws.column_dimensions['A'].width = 15 ws.column_dimensions['B'].width = 28 for col in range(3, 8): ws.column_dimensions[get_column_letter(col)].width = 12