""" Report Generator Module Generates Excel and PDF reports from query results """ import pandas as pd from datetime import datetime from pathlib import Path import unicodedata import re import matplotlib matplotlib.use('Agg') # Non-interactive backend import matplotlib.pyplot as plt import matplotlib.ticker as ticker from openpyxl import Workbook # Romanian diacritics mapping for PDF (Helvetica doesn't support them) DIACRITICS_MAP = { 'ă': 'a', 'Ă': 'A', 'â': 'a', 'Â': 'A', 'î': 'i', 'Î': 'I', 'ș': 's', 'Ș': 'S', 'ş': 's', 'Ş': 'S', # Alternative encoding 'ț': 't', 'Ț': 'T', 'ţ': 't', 'Ţ': 'T', # Alternative encoding } def remove_diacritics(text): """Remove Romanian diacritics from text for PDF compatibility""" if not isinstance(text, str): return text for diacritic, replacement in DIACRITICS_MAP.items(): text = text.replace(diacritic, replacement) return text def sanitize_for_pdf(value, max_length=None): """Sanitize value for PDF: remove diacritics and optionally truncate""" if value is None: return '' text = str(value) text = remove_diacritics(text) if max_length and len(text) > max_length: text = text[:max_length-3] + '...' return text from openpyxl.styles import Font, Alignment, PatternFill, Border, Side from openpyxl.utils.dataframe import dataframe_to_rows from openpyxl.chart import BarChart, LineChart, PieChart, Reference from openpyxl.utils import get_column_letter from reportlab.lib import colors from reportlab.lib.pagesizes import A4, landscape from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle from reportlab.lib.units import cm, mm from reportlab.platypus import ( SimpleDocTemplate, Paragraph, Spacer, Table, TableStyle, PageBreak, Image, KeepTogether ) from reportlab.lib.enums import TA_CENTER, TA_LEFT, TA_RIGHT import io class ExcelReportGenerator: """Generate Excel reports with multiple sheets and formatting""" def __init__(self, output_path: Path): self.output_path = output_path self.wb = Workbook() # Remove default sheet self.wb.remove(self.wb.active) # Define styles self.header_font = Font(bold=True, color='FFFFFF', size=11) self.header_fill = PatternFill(start_color='366092', end_color='366092', fill_type='solid') self.alert_fill = PatternFill(start_color='FF6B6B', end_color='FF6B6B', fill_type='solid') self.warning_fill = PatternFill(start_color='FFE66D', end_color='FFE66D', fill_type='solid') self.good_fill = PatternFill(start_color='4ECDC4', end_color='4ECDC4', fill_type='solid') self.border = Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) # Style for manager-friendly explanations self.explanation_fill = PatternFill(start_color='F8F9FA', end_color='F8F9FA', fill_type='solid') self.explanation_border = Border( left=Side(style='thin', color='DEE2E6'), right=Side(style='thin', color='DEE2E6'), top=Side(style='thin', color='DEE2E6'), bottom=Side(style='thin', color='DEE2E6') ) def add_sheet(self, name: str, df: pd.DataFrame, title: str = None, description: str = None, legend: dict = None): """Add a formatted sheet to the workbook with optional legend""" # Truncate sheet name to 31 chars (Excel limit) sheet_name = name[:31] ws = self.wb.create_sheet(title=sheet_name) start_row = 1 # Add title if provided if title: ws.cell(row=start_row, column=1, value=title) ws.cell(row=start_row, column=1).font = Font(bold=True, size=14) start_row += 1 # Add description if provided if description: ws.cell(row=start_row, column=1, value=description) ws.cell(row=start_row, column=1).font = Font(italic=True, size=10, color='666666') start_row += 1 # Add timestamp ws.cell(row=start_row, column=1, value=f"Generat: {datetime.now().strftime('%Y-%m-%d %H:%M')}") ws.cell(row=start_row, column=1).font = Font(size=9, color='999999') start_row += 1 # Add legend if provided if legend: start_row += 1 ws.cell(row=start_row, column=1, value="Explicații calcule:") ws.cell(row=start_row, column=1).font = Font(bold=True, size=9, color='336699') start_row += 1 for col_name, explanation in legend.items(): ws.cell(row=start_row, column=1, value=f"• {col_name}: {explanation}") ws.cell(row=start_row, column=1).font = Font(size=8, color='666666') start_row += 1 start_row += 1 if df is None or df.empty: ws.cell(row=start_row, column=1, value="Nu exista date pentru această analiză.") return # Write headers for col_idx, col_name in enumerate(df.columns, 1): cell = ws.cell(row=start_row, column=col_idx, value=col_name) cell.font = self.header_font cell.fill = self.header_fill cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) cell.border = self.border # Write data for row_idx, row in enumerate(df.itertuples(index=False), start_row + 1): for col_idx, value in enumerate(row, 1): cell = ws.cell(row=row_idx, column=col_idx, value=value) cell.border = self.border # Format numbers if isinstance(value, (int, float)): cell.number_format = '#,##0.00' if isinstance(value, float) else '#,##0' cell.alignment = Alignment(horizontal='right') # Highlight negative values if isinstance(value, (int, float)) and value < 0: cell.fill = self.alert_fill # Highlight low margins col_name = df.columns[col_idx - 1].lower() if 'procent' in col_name or 'marja' in col_name: if isinstance(value, (int, float)): if value < 10: cell.fill = self.alert_fill elif value < 15: cell.fill = self.warning_fill elif value > 25: cell.fill = self.good_fill # Highlight TREND column for YoY sheets if col_name == 'trend': if isinstance(value, str): if value in ('CRESTERE', 'IMBUNATATIRE', 'DIVERSIFICARE'): cell.fill = self.good_fill elif value in ('SCADERE', 'DETERIORARE', 'CONCENTRARE', 'PIERDUT'): cell.fill = self.alert_fill elif value == 'ATENTIE': cell.fill = self.warning_fill # Highlight STATUS column if col_name == 'status' or col_name == 'acoperire': if isinstance(value, str): if value == 'OK': cell.fill = self.good_fill elif value in ('ATENTIE', 'NECESAR'): cell.fill = self.warning_fill elif value in ('ALERTA', 'DEFICIT', 'RISC MARE'): cell.fill = self.alert_fill # Highlight variatie columns (positive = green, negative = red) if 'variatie' in col_name: if isinstance(value, (int, float)): if value > 0: cell.fill = self.good_fill elif value < 0: cell.fill = self.alert_fill # Auto-adjust column widths for col_idx, col_name in enumerate(df.columns, 1): max_length = len(str(col_name)) for row in df.itertuples(index=False): cell_value = row[col_idx - 1] if cell_value: max_length = max(max_length, len(str(cell_value))) adjusted_width = min(max_length + 2, 50) ws.column_dimensions[get_column_letter(col_idx)].width = adjusted_width # Freeze header row ws.freeze_panes = ws.cell(row=start_row + 1, column=1) def add_sheet_with_recommendations(self, name: str, df: pd.DataFrame, recommendations_df: pd.DataFrame, title: str = None, description: str = None, legend: dict = None, top_n_recommendations: int = 5): """Adauga sheet formatat cu KPIs si top recomandari dedesubt""" sheet_name = name[:31] ws = self.wb.create_sheet(title=sheet_name) start_row = 1 # Adauga titlu if title: ws.cell(row=start_row, column=1, value=title) ws.cell(row=start_row, column=1).font = Font(bold=True, size=14) start_row += 1 # Adauga descriere if description: ws.cell(row=start_row, column=1, value=description) ws.cell(row=start_row, column=1).font = Font(italic=True, size=10, color='666666') start_row += 1 # Adauga timestamp ws.cell(row=start_row, column=1, value=f"Generat: {datetime.now().strftime('%Y-%m-%d %H:%M')}") ws.cell(row=start_row, column=1).font = Font(size=9, color='999999') start_row += 2 # === SECTIUNEA 1: KPIs === if df is not None and not df.empty: # Header for col_idx, col_name in enumerate(df.columns, 1): cell = ws.cell(row=start_row, column=col_idx, value=col_name) cell.font = self.header_font cell.fill = self.header_fill cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) cell.border = self.border # Date for row_idx, row in enumerate(df.itertuples(index=False), start_row + 1): for col_idx, value in enumerate(row, 1): cell = ws.cell(row=row_idx, column=col_idx, value=value) cell.border = self.border if isinstance(value, (int, float)): cell.number_format = '#,##0.00' if isinstance(value, float) else '#,##0' cell.alignment = Alignment(horizontal='right') start_row = start_row + len(df) + 3 # === SECTIUNEA 2: TOP RECOMANDARI === if recommendations_df is not None and not recommendations_df.empty: ws.cell(row=start_row, column=1, value="Top Recomandari Prioritare") ws.cell(row=start_row, column=1).font = Font(bold=True, size=12, color='366092') start_row += 1 # Sorteaza dupa prioritate (ALERTA primul, apoi ATENTIE, apoi OK) df_sorted = recommendations_df.copy() status_order = {'ALERTA': 0, 'ATENTIE': 1, 'OK': 2} df_sorted['_order'] = df_sorted['STATUS'].map(status_order).fillna(3) df_sorted = df_sorted.sort_values('_order').head(top_n_recommendations) df_sorted = df_sorted.drop(columns=['_order']) # Coloane de afisat display_cols = ['STATUS', 'CATEGORIE', 'INDICATOR', 'VALOARE', 'RECOMANDARE'] display_cols = [c for c in display_cols if c in df_sorted.columns] # Header cu background mov rec_header_fill = PatternFill(start_color='8E44AD', end_color='8E44AD', fill_type='solid') for col_idx, col_name in enumerate(display_cols, 1): cell = ws.cell(row=start_row, column=col_idx, value=col_name) cell.font = self.header_font cell.fill = rec_header_fill cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) cell.border = self.border # Randuri cu colorare dupa status for row_idx, (_, row) in enumerate(df_sorted.iterrows(), start_row + 1): status = row.get('STATUS', 'OK') for col_idx, col_name in enumerate(display_cols, 1): value = row.get(col_name, '') cell = ws.cell(row=row_idx, column=col_idx, value=value) cell.border = self.border cell.alignment = Alignment(wrap_text=True) # Colorare conditionala if status == 'ALERTA': cell.fill = PatternFill(start_color='FADBD8', end_color='FADBD8', fill_type='solid') elif status == 'ATENTIE': cell.fill = PatternFill(start_color='FCF3CF', end_color='FCF3CF', fill_type='solid') else: cell.fill = PatternFill(start_color='D5F5E3', end_color='D5F5E3', fill_type='solid') # Auto-adjust latime coloane for col_idx in range(1, 8): ws.column_dimensions[get_column_letter(col_idx)].width = 22 ws.freeze_panes = ws.cell(row=5, column=1) def add_consolidated_sheet(self, name: str, sections: list, sheet_title: str = None, sheet_description: str = None): """ Add a consolidated sheet with multiple sections separated visually. Args: name: Sheet name (max 31 chars) sections: List of dicts with keys: - 'title': Section title (str) - 'df': DataFrame with data - 'description': Optional section description (str) - 'legend': Optional dict with column explanations sheet_title: Overall sheet title sheet_description: Overall sheet description """ sheet_name = name[:31] ws = self.wb.create_sheet(title=sheet_name) start_row = 1 # Add overall sheet title if sheet_title: ws.cell(row=start_row, column=1, value=sheet_title) ws.cell(row=start_row, column=1).font = Font(bold=True, size=16) start_row += 1 # Add overall description if sheet_description: ws.cell(row=start_row, column=1, value=sheet_description) ws.cell(row=start_row, column=1).font = Font(italic=True, size=10, color='666666') start_row += 1 # Add timestamp ws.cell(row=start_row, column=1, value=f"Generat: {datetime.now().strftime('%Y-%m-%d %H:%M')}") ws.cell(row=start_row, column=1).font = Font(size=9, color='999999') start_row += 2 # Process each section for section in sections: section_title = section.get('title', '') df = section.get('df') description = section.get('description', '') explanation = section.get('explanation', '') legend = section.get('legend', {}) # Section separator separator_fill = PatternFill(start_color='2C3E50', end_color='2C3E50', fill_type='solid') for col in range(1, 10): # Wide separator # Use >>> instead of === to avoid Excel formula interpretation cell = ws.cell(row=start_row, column=col, value='' if col > 1 else f'>>> {section_title}') cell.fill = separator_fill cell.font = Font(bold=True, color='FFFFFF', size=11) start_row += 1 # Manager-friendly explanation box (if provided) if explanation: # Merge cells for explanation box (columns 1-8) ws.merge_cells(start_row=start_row, start_column=1, end_row=start_row, end_column=8) cell = ws.cell(row=start_row, column=1, value=f"💡 {explanation}") cell.fill = self.explanation_fill cell.border = self.explanation_border cell.font = Font(size=9, color='555555') cell.alignment = Alignment(wrap_text=True, vertical='center') ws.row_dimensions[start_row].height = 40 # Taller row for wrapped text # Apply border to merged cells for col in range(1, 9): ws.cell(row=start_row, column=col).fill = self.explanation_fill ws.cell(row=start_row, column=col).border = self.explanation_border start_row += 1 # Section description (technical) if description: ws.cell(row=start_row, column=1, value=description) ws.cell(row=start_row, column=1).font = Font(italic=True, size=9, color='666666') start_row += 1 start_row += 1 # Check for empty data if df is None or df.empty: ws.cell(row=start_row, column=1, value="Nu exista date pentru această secțiune.") ws.cell(row=start_row, column=1).font = Font(italic=True, color='999999') start_row += 3 continue # Write headers for col_idx, col_name in enumerate(df.columns, 1): cell = ws.cell(row=start_row, column=col_idx, value=col_name) cell.font = self.header_font cell.fill = self.header_fill cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) cell.border = self.border # Write data for row_idx, row in enumerate(df.itertuples(index=False), start_row + 1): for col_idx, value in enumerate(row, 1): cell = ws.cell(row=row_idx, column=col_idx, value=value) cell.border = self.border # Format numbers if isinstance(value, (int, float)): cell.number_format = '#,##0.00' if isinstance(value, float) else '#,##0' cell.alignment = Alignment(horizontal='right') # Highlight based on column name col_name = df.columns[col_idx - 1].lower() # Status coloring if col_name == 'status' or col_name == 'acoperire': if isinstance(value, str): if value == 'OK': cell.fill = self.good_fill elif value in ('ATENTIE', 'NECESAR'): cell.fill = self.warning_fill elif value in ('ALERTA', 'DEFICIT', 'RISC MARE'): cell.fill = self.alert_fill # Trend coloring if col_name == 'trend': if isinstance(value, str): if value in ('CRESTERE', 'IMBUNATATIRE', 'DIVERSIFICARE'): cell.fill = self.good_fill elif value in ('SCADERE', 'DETERIORARE', 'CONCENTRARE', 'PIERDUT'): cell.fill = self.alert_fill elif value == 'ATENTIE': cell.fill = self.warning_fill # Variatie coloring if 'variatie' in col_name: if isinstance(value, (int, float)): if value > 0: cell.fill = self.good_fill elif value < 0: cell.fill = self.alert_fill # Margin coloring if 'procent' in col_name or 'marja' in col_name: if isinstance(value, (int, float)): if value < 10: cell.fill = self.alert_fill elif value < 15: cell.fill = self.warning_fill elif value > 25: cell.fill = self.good_fill start_row = start_row + len(df) + 2 # Add legend for this section if legend: ws.cell(row=start_row, column=1, value="Legendă:") ws.cell(row=start_row, column=1).font = Font(bold=True, size=8, color='336699') start_row += 1 for col_name, explanation in legend.items(): ws.cell(row=start_row, column=1, value=f"• {col_name}: {explanation}") ws.cell(row=start_row, column=1).font = Font(size=8, color='666666') start_row += 1 # Space between sections start_row += 2 # Auto-adjust column widths for col_idx in range(1, 12): ws.column_dimensions[get_column_letter(col_idx)].width = 18 # Freeze title row ws.freeze_panes = ws.cell(row=5, column=1) def save(self): """Save the workbook""" self.wb.save(self.output_path) print(f"✓ Excel salvat: {self.output_path}") class PDFReportGenerator: """Generate PDF executive summary with charts""" def __init__(self, output_path: Path, company_name: str = "Data Intelligence Report"): self.output_path = output_path self.company_name = company_name self.elements = [] self.styles = getSampleStyleSheet() # Custom styles self.styles.add(ParagraphStyle( name='CustomTitle', parent=self.styles['Title'], fontSize=24, spaceAfter=30, alignment=TA_CENTER )) self.styles.add(ParagraphStyle( name='SectionHeader', parent=self.styles['Heading1'], fontSize=14, spaceBefore=20, spaceAfter=10, textColor=colors.HexColor('#366092') )) self.styles.add(ParagraphStyle( name='AlertHeader', parent=self.styles['Heading2'], fontSize=12, textColor=colors.red, spaceBefore=15, spaceAfter=8 )) self.styles.add(ParagraphStyle( name='SmallText', parent=self.styles['Normal'], fontSize=8, textColor=colors.gray )) # Table cell styles with word wrapping self.styles.add(ParagraphStyle( name='TableCell', parent=self.styles['Normal'], fontSize=7, leading=9, wordWrap='CJK', )) self.styles.add(ParagraphStyle( name='TableCellBold', parent=self.styles['Normal'], fontSize=7, leading=9, fontName='Helvetica-Bold', )) # Explanation style for manager-friendly text boxes self.styles.add(ParagraphStyle( name='Explanation', parent=self.styles['Normal'], fontSize=9, textColor=colors.HexColor('#555555'), backColor=colors.HexColor('#F8F9FA'), borderPadding=8, spaceBefore=5, spaceAfter=10, )) def make_cell_paragraph(self, text, bold=False): """Create a Paragraph for table cell with word wrapping""" style = self.styles['TableCellBold'] if bold else self.styles['TableCell'] return Paragraph(sanitize_for_pdf(text), style) def add_explanation(self, text: str): """Add a manager-friendly explanation box before a section""" # Create a table with background color to simulate a box explanation_para = Paragraph(sanitize_for_pdf(text), self.styles['Explanation']) box_table = Table([[explanation_para]], colWidths=[16*cm]) box_table.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (-1, -1), colors.HexColor('#F8F9FA')), ('BOX', (0, 0), (-1, -1), 0.5, colors.HexColor('#DEE2E6')), ('TOPPADDING', (0, 0), (-1, -1), 8), ('BOTTOMPADDING', (0, 0), (-1, -1), 8), ('LEFTPADDING', (0, 0), (-1, -1), 10), ('RIGHTPADDING', (0, 0), (-1, -1), 10), ])) self.elements.append(box_table) self.elements.append(Spacer(1, 0.3*cm)) def add_title_page(self, report_date: datetime = None): """Add title page""" if report_date is None: report_date = datetime.now() self.elements.append(Spacer(1, 3*cm)) self.elements.append(Paragraph(remove_diacritics(self.company_name), self.styles['CustomTitle'])) self.elements.append(Spacer(1, 1*cm)) self.elements.append(Paragraph( f"Raport generat: {report_date.strftime('%d %B %Y, %H:%M')}", self.styles['Normal'] )) self.elements.append(Paragraph( "Perioada analizata: Ultimele 12 luni", self.styles['Normal'] )) self.elements.append(PageBreak()) def add_kpi_section(self, kpi_df: pd.DataFrame): """Add KPI summary section""" self.elements.append(Paragraph("Sumar Executiv - KPIs", self.styles['SectionHeader'])) if kpi_df is not None and not kpi_df.empty: # Header row with bold style header_style = ParagraphStyle( 'TableHeaderCell', parent=self.styles['Normal'], fontSize=9, fontName='Helvetica-Bold', textColor=colors.white ) data = [[ Paragraph('Indicator', header_style), Paragraph('Valoare', header_style), Paragraph('UM', header_style) ]] for _, row in kpi_df.iterrows(): data.append([ self.make_cell_paragraph(row.get('INDICATOR', '')), self.make_cell_paragraph(row.get('VALOARE', '')), self.make_cell_paragraph(row.get('UM', '')) ]) table = Table(data, colWidths=[8*cm, 4*cm, 2*cm]) table.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#366092')), ('TEXTCOLOR', (0, 0), (-1, 0), colors.white), ('ALIGN', (0, 0), (-1, -1), 'LEFT'), ('ALIGN', (1, 1), (1, -1), 'RIGHT'), ('VALIGN', (0, 0), (-1, -1), 'TOP'), ('BOTTOMPADDING', (0, 0), (-1, 0), 12), ('TOPPADDING', (0, 1), (-1, -1), 4), ('BOTTOMPADDING', (0, 1), (-1, -1), 4), ('GRID', (0, 0), (-1, -1), 0.5, colors.gray), ('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.HexColor('#f0f0f0')]) ])) self.elements.append(table) self.elements.append(Spacer(1, 0.5*cm)) def add_alerts_section(self, alerts_data: dict): """Add critical alerts section""" self.elements.append(Paragraph("Alerte Critice", self.styles['SectionHeader'])) # Vanzari sub cost if 'vanzari_sub_cost' in alerts_data and not alerts_data['vanzari_sub_cost'].empty: df = alerts_data['vanzari_sub_cost'] count = len(df) total_loss = df['PIERDERE'].sum() if 'PIERDERE' in df.columns else 0 self.elements.append(Paragraph( f"VANZARI SUB COST: {count} tranzactii cu pierdere totala de {abs(total_loss):,.2f} RON", self.styles['AlertHeader'] )) # Show top 5 top5 = df.head(5) if not top5.empty: cols_to_show = ['FACTURA', 'CLIENT', 'PRODUS', 'PIERDERE'] cols_to_show = [c for c in cols_to_show if c in top5.columns] if cols_to_show: # Header with Paragraph data = [[self.make_cell_paragraph(c, bold=True) for c in cols_to_show]] for _, row in top5.iterrows(): row_data = [self.make_cell_paragraph(row.get(c, '')) for c in cols_to_show] data.append(row_data) table = Table(data, colWidths=[3*cm, 4*cm, 5*cm, 2*cm]) table.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#c0392b')), ('TEXTCOLOR', (0, 0), (-1, 0), colors.white), ('VALIGN', (0, 0), (-1, -1), 'TOP'), ('TOPPADDING', (0, 0), (-1, -1), 4), ('BOTTOMPADDING', (0, 0), (-1, -1), 4), ('GRID', (0, 0), (-1, -1), 0.5, colors.gray), ])) self.elements.append(table) # Clienti cu marja mica if 'clienti_marja_mica' in alerts_data and not alerts_data['clienti_marja_mica'].empty: df = alerts_data['clienti_marja_mica'] count = len(df) self.elements.append(Spacer(1, 0.3*cm)) self.elements.append(Paragraph( f"CLIENTI CU MARJA MICA (<15%): {count} clienti necesita renegociere", self.styles['AlertHeader'] )) top5 = df.head(5) if not top5.empty: cols_to_show = ['CLIENT', 'VANZARI_FARA_TVA', 'PROCENT_MARJA'] cols_to_show = [c for c in cols_to_show if c in top5.columns] if cols_to_show: # Header with Paragraph data = [[self.make_cell_paragraph(c, bold=True) for c in cols_to_show]] for _, row in top5.iterrows(): data.append([ self.make_cell_paragraph(row.get('CLIENT', '')), self.make_cell_paragraph(f"{row.get('VANZARI_FARA_TVA', 0):,.0f}"), self.make_cell_paragraph(f"{row.get('PROCENT_MARJA', 0):.1f}%") ]) table = Table(data, colWidths=[6*cm, 3*cm, 2*cm]) table.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#e67e22')), ('TEXTCOLOR', (0, 0), (-1, 0), colors.white), ('VALIGN', (0, 0), (-1, -1), 'TOP'), ('TOPPADDING', (0, 0), (-1, -1), 4), ('BOTTOMPADDING', (0, 0), (-1, -1), 4), ('GRID', (0, 0), (-1, -1), 0.5, colors.gray), ])) self.elements.append(table) self.elements.append(Spacer(1, 0.5*cm)) def add_chart_image(self, fig, title: str): """Add a matplotlib figure as image""" self.elements.append(Paragraph(remove_diacritics(title), self.styles['SectionHeader'])) # Save figure to buffer buf = io.BytesIO() fig.savefig(buf, format='png', dpi=200, bbox_inches='tight') buf.seek(0) # Add to PDF img = Image(buf, width=16*cm, height=10*cm) self.elements.append(img) self.elements.append(Spacer(1, 0.5*cm)) plt.close(fig) def add_table_section(self, title: str, df: pd.DataFrame, columns: list = None, max_rows: int = 15): """Add a data table section with word-wrapped cells""" self.elements.append(Paragraph(remove_diacritics(title), self.styles['SectionHeader'])) if df is None or df.empty: self.elements.append(Paragraph("Nu exista date.", self.styles['Normal'])) return # Select columns if columns: cols = [c for c in columns if c in df.columns] else: cols = list(df.columns)[:6] # Max 6 columns for PDF if not cols: return # Prepare data with Paragraph cells for word wrapping data = [[self.make_cell_paragraph(c, bold=True) for c in cols]] for _, row in df.head(max_rows).iterrows(): row_data = [] for col in cols: val = row.get(col, '') if isinstance(val, float): row_data.append(self.make_cell_paragraph(f"{val:,.2f}")) elif isinstance(val, int): row_data.append(self.make_cell_paragraph(f"{val:,}")) else: row_data.append(self.make_cell_paragraph(val)) data.append(row_data) # Calculate column widths n_cols = len(cols) col_width = 16*cm / n_cols table = Table(data, colWidths=[col_width] * n_cols) table.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#366092')), ('TEXTCOLOR', (0, 0), (-1, 0), colors.white), ('ALIGN', (0, 0), (-1, -1), 'LEFT'), ('VALIGN', (0, 0), (-1, -1), 'TOP'), ('TOPPADDING', (0, 0), (-1, -1), 4), ('BOTTOMPADDING', (0, 0), (-1, -1), 4), ('GRID', (0, 0), (-1, -1), 0.5, colors.gray), ('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.HexColor('#f5f5f5')]) ])) self.elements.append(table) if len(df) > max_rows: self.elements.append(Paragraph( f"... si inca {len(df) - max_rows} inregistrari (vezi Excel pentru lista completa)", self.styles['SmallText'] )) self.elements.append(Spacer(1, 0.5*cm)) def add_page_break(self): """Add page break""" self.elements.append(PageBreak()) def add_consolidated_page(self, page_title: str, sections: list): """ Add a consolidated PDF page with multiple sections and word-wrapped cells. Args: page_title: Main title for the page sections: List of dicts with keys: - 'title': Section title (str) - 'df': DataFrame with data - 'columns': List of columns to display (optional) - 'max_rows': Max rows to display (default 15) """ # Page title self.elements.append(Paragraph(remove_diacritics(page_title), self.styles['SectionHeader'])) self.elements.append(Spacer(1, 0.3*cm)) for section in sections: section_title = section.get('title', '') df = section.get('df') columns = section.get('columns') max_rows = section.get('max_rows', 15) # Sub-section title subsection_style = ParagraphStyle( name='SubSection', parent=self.styles['Heading2'], fontSize=11, spaceBefore=10, spaceAfter=5, textColor=colors.HexColor('#2C3E50') ) self.elements.append(Paragraph(remove_diacritics(section_title), subsection_style)) if df is None or df.empty: self.elements.append(Paragraph("Nu exista date.", self.styles['Normal'])) self.elements.append(Spacer(1, 0.3*cm)) continue # Select columns if columns: cols = [c for c in columns if c in df.columns] else: cols = list(df.columns)[:6] # Max 6 columns if not cols: continue # Prepare data with Paragraph cells for word wrapping data = [[self.make_cell_paragraph(c, bold=True) for c in cols]] for _, row in df.head(max_rows).iterrows(): row_data = [] for col in cols: val = row.get(col, '') if isinstance(val, float): row_data.append(self.make_cell_paragraph(f"{val:,.2f}")) elif isinstance(val, int): row_data.append(self.make_cell_paragraph(f"{val:,}")) else: row_data.append(self.make_cell_paragraph(val)) data.append(row_data) # Calculate column widths n_cols = len(cols) col_width = 16*cm / n_cols table = Table(data, colWidths=[col_width] * n_cols) # Build style with conditional row colors for status table_style = [ ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#366092')), ('TEXTCOLOR', (0, 0), (-1, 0), colors.white), ('ALIGN', (0, 0), (-1, -1), 'LEFT'), ('VALIGN', (0, 0), (-1, -1), 'TOP'), ('TOPPADDING', (0, 0), (-1, -1), 4), ('BOTTOMPADDING', (0, 0), (-1, -1), 4), ('GRID', (0, 0), (-1, -1), 0.5, colors.gray), ('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.HexColor('#f5f5f5')]) ] # Color status cells if STATUS column exists if 'STATUS' in cols: status_col_idx = cols.index('STATUS') for row_idx, row in enumerate(df.head(max_rows).itertuples(index=False), 1): status_val = str(row[df.columns.get_loc('STATUS')]) if 'STATUS' in df.columns else '' if status_val == 'ALERTA': table_style.append(('BACKGROUND', (status_col_idx, row_idx), (status_col_idx, row_idx), colors.HexColor('#FF6B6B'))) elif status_val == 'ATENTIE': table_style.append(('BACKGROUND', (status_col_idx, row_idx), (status_col_idx, row_idx), colors.HexColor('#FFE66D'))) elif status_val == 'OK': table_style.append(('BACKGROUND', (status_col_idx, row_idx), (status_col_idx, row_idx), colors.HexColor('#4ECDC4'))) table.setStyle(TableStyle(table_style)) self.elements.append(table) if len(df) > max_rows: self.elements.append(Paragraph( f"... si inca {len(df) - max_rows} inregistrari", self.styles['SmallText'] )) self.elements.append(Spacer(1, 0.4*cm)) def add_recommendations_section(self, recommendations_df: pd.DataFrame): """Add recommendations section with status colors""" self.elements.append(Paragraph("Recomandari Cheie", self.styles['SectionHeader'])) if recommendations_df is None or recommendations_df.empty: self.elements.append(Paragraph("Nu au fost generate recomandari.", self.styles['Normal'])) return # Show top 7 most important recommendations (ALERTA first, then ATENTIE) df_sorted = recommendations_df.copy() status_order = {'ALERTA': 0, 'ATENTIE': 1, 'OK': 2} df_sorted['_order'] = df_sorted['STATUS'].map(status_order) df_sorted = df_sorted.sort_values('_order').head(7) for _, row in df_sorted.iterrows(): status = sanitize_for_pdf(row.get('STATUS', 'OK')) indicator = sanitize_for_pdf(row.get('INDICATOR', '')) valoare = sanitize_for_pdf(row.get('VALOARE', '')) explicatie = sanitize_for_pdf(row.get('EXPLICATIE', '')) recomandare = sanitize_for_pdf(row.get('RECOMANDARE', '')) # Color based on status if status == 'ALERTA': status_color = colors.HexColor('#c0392b') bg_color = colors.HexColor('#fadbd8') elif status == 'ATENTIE': status_color = colors.HexColor('#d68910') bg_color = colors.HexColor('#fef9e7') else: status_color = colors.HexColor('#27ae60') bg_color = colors.HexColor('#d5f5e3') # Create a small table for each recommendation data = [ [f"[{status}] {indicator}: {valoare}"], [explicatie], [f"Actiune: {recomandare}"] ] table = Table(data, colWidths=[16*cm]) table.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (-1, 0), status_color), ('TEXTCOLOR', (0, 0), (-1, 0), colors.white), ('BACKGROUND', (0, 1), (-1, -1), bg_color), ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'), ('FONTSIZE', (0, 0), (-1, -1), 8), ('BOTTOMPADDING', (0, 0), (-1, -1), 6), ('TOPPADDING', (0, 0), (-1, -1), 4), ('LEFTPADDING', (0, 0), (-1, -1), 8), ])) self.elements.append(table) self.elements.append(Spacer(1, 0.2*cm)) self.elements.append(Spacer(1, 0.3*cm)) def save(self): """Generate and save PDF""" doc = SimpleDocTemplate( str(self.output_path), pagesize=A4, rightMargin=2*cm, leftMargin=2*cm, topMargin=2*cm, bottomMargin=2*cm ) doc.build(self.elements) print(f"✓ PDF salvat: {self.output_path}") # Modern minimalist chart colors CHART_COLORS = { 'primary': '#2C3E50', # Dark blue-gray 'secondary': '#7F8C8D', # Gray 'accent': '#E74C3C', # Red for alerts/negative 'positive': '#27AE60', # Green for positive trends 'light': '#ECF0F1', # Light background } def setup_chart_style(): """Apply modern minimalist styling to charts""" plt.rcParams.update({ 'font.family': 'sans-serif', 'font.size': 10, 'axes.titlesize': 12, 'axes.titleweight': 'bold', 'axes.spines.top': False, 'axes.spines.right': False, 'axes.grid': True, 'grid.alpha': 0.3, 'grid.linestyle': '--', 'figure.facecolor': 'white', 'axes.facecolor': 'white', 'axes.edgecolor': '#7F8C8D', 'xtick.color': '#7F8C8D', 'ytick.color': '#7F8C8D', }) def create_monthly_chart(df: pd.DataFrame) -> plt.Figure: """Create monthly sales chart - modern minimalist style""" setup_chart_style() if df is None or df.empty: fig, ax = plt.subplots(figsize=(12, 5)) ax.text(0.5, 0.5, 'Nu exista date', ha='center', va='center') return fig fig, ax = plt.subplots(figsize=(12, 5)) x = range(len(df)) # Single color bars with clean styling bars = ax.bar(x, df['VANZARI_FARA_TVA'], color=CHART_COLORS['primary'], alpha=0.85, edgecolor='white', linewidth=0.5) # Add value labels on top of bars for bar in bars: height = bar.get_height() ax.text(bar.get_x() + bar.get_width()/2., height, f'{height/1000:,.0f}k', ha='center', va='bottom', fontsize=8, color='#555') # Clean axis formatting ax.set_xlabel('Luna', fontsize=10, color='#555') ax.set_ylabel('Vanzari (RON)', fontsize=10, color='#555') ax.yaxis.set_major_formatter(ticker.FuncFormatter(lambda x, p: f'{x/1000:,.0f}k')) ax.set_xticks(x) ax.set_xticklabels(df['LUNA'], rotation=45, ha='right', fontsize=9) # Add subtle trend line if margin data exists if 'MARJA_BRUTA' in df.columns: ax2 = ax.twinx() ax2.plot(x, df['MARJA_BRUTA'], color=CHART_COLORS['accent'], linewidth=2, marker='o', markersize=4, label='Marja Bruta', alpha=0.8) ax2.set_ylabel('Marja (RON)', fontsize=10, color=CHART_COLORS['accent']) ax2.yaxis.set_major_formatter(ticker.FuncFormatter(lambda x, p: f'{x/1000:,.0f}k')) ax2.spines['right'].set_visible(True) ax2.spines['right'].set_color(CHART_COLORS['accent']) ax2.tick_params(axis='y', colors=CHART_COLORS['accent']) ax.set_title('Evolutia Vanzarilor Lunare', fontsize=12, fontweight='bold', color='#2C3E50') plt.tight_layout() return fig def create_client_concentration_chart(df: pd.DataFrame) -> plt.Figure: """Create client concentration chart - horizontal bars (easier to read than pie)""" setup_chart_style() if df is None or df.empty: fig, ax = plt.subplots(figsize=(12, 6)) ax.text(0.5, 0.5, 'Nu exista date', ha='center', va='center') return fig fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(14, 5)) top10 = df.head(10) # Left: Horizontal bar chart showing client share (cleaner than pie) y_pos = range(len(top10)-1, -1, -1) # Reverse for top-to-bottom colors = [CHART_COLORS['primary'] if pct < 25 else CHART_COLORS['accent'] for pct in top10['PROCENT_DIN_TOTAL']] bars = ax1.barh(y_pos, top10['PROCENT_DIN_TOTAL'], color=colors, alpha=0.85, edgecolor='white', linewidth=0.5) ax1.set_yticks(y_pos) ax1.set_yticklabels([c[:25] for c in top10['CLIENT']], fontsize=9) ax1.set_xlabel('% din Vanzari Totale', fontsize=10) ax1.set_title('Top 10 Clienti - Pondere Vanzari', fontsize=11, fontweight='bold') # Add percentage labels for bar, pct in zip(bars, top10['PROCENT_DIN_TOTAL']): ax1.text(bar.get_width() + 0.5, bar.get_y() + bar.get_height()/2, f'{pct:.1f}%', va='center', fontsize=8, color='#555') # Add 25% threshold line ax1.axvline(x=25, color=CHART_COLORS['accent'], linestyle='--', alpha=0.7, label='Prag risc 25%') ax1.legend(loc='lower right', fontsize=8) # Right: Pareto chart with cumulative line x = range(len(top10)) ax2.bar(x, top10['VANZARI'], color=CHART_COLORS['primary'], alpha=0.85, edgecolor='white', linewidth=0.5) # Cumulative line on secondary axis ax2_twin = ax2.twinx() ax2_twin.plot(x, top10['PROCENT_CUMULAT'], color=CHART_COLORS['accent'], linewidth=2, marker='o', markersize=4) ax2_twin.axhline(y=80, color=CHART_COLORS['positive'], linestyle='--', alpha=0.7, linewidth=1.5, label='Prag 80%') ax2.set_xticks(x) ax2.set_xticklabels([c[:12] for c in top10['CLIENT']], rotation=45, ha='right', fontsize=8) ax2.set_ylabel('Vanzari (RON)', fontsize=10) ax2.yaxis.set_major_formatter(ticker.FuncFormatter(lambda x, p: f'{x/1000:,.0f}k')) ax2_twin.set_ylabel('% Cumulat', fontsize=10, color=CHART_COLORS['accent']) ax2_twin.set_ylim(0, 105) ax2.set_title('Analiza Pareto - Concentrare Clienti', fontsize=11, fontweight='bold') ax2_twin.legend(loc='center right', fontsize=8) plt.tight_layout() return fig def create_production_chart(df: pd.DataFrame) -> plt.Figure: """Create production vs resale comparison chart - modern minimalist style""" setup_chart_style() if df is None or df.empty: fig, ax = plt.subplots(figsize=(10, 5)) ax.text(0.5, 0.5, 'Nu exista date', ha='center', va='center') return fig fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(12, 5)) x = range(len(df)) # Left: Sales by type (two-color scheme: primary + secondary) bar_colors = [CHART_COLORS['primary'], CHART_COLORS['secondary']][:len(df)] if len(df) > 2: bar_colors = [CHART_COLORS['primary']] * len(df) bars = ax1.bar(x, df['VANZARI_FARA_TVA'], color=bar_colors, alpha=0.85, edgecolor='white', linewidth=0.5) ax1.set_xticks(x) ax1.set_xticklabels(df['TIP_PRODUS'], rotation=15, fontsize=9) ax1.set_ylabel('Vanzari (RON)', fontsize=10) ax1.set_title('Vanzari per Tip Produs', fontsize=11, fontweight='bold') ax1.yaxis.set_major_formatter(ticker.FuncFormatter(lambda x, p: f'{x/1000:,.0f}k')) # Add value labels for bar in bars: height = bar.get_height() ax1.text(bar.get_x() + bar.get_width()/2., height, f'{height/1000:,.0f}k', ha='center', va='bottom', fontsize=9, color='#555') # Right: Margin % with color-coded status colors = [CHART_COLORS['positive'] if m > 20 else CHART_COLORS['secondary'] if m > 15 else CHART_COLORS['accent'] for m in df['PROCENT_MARJA']] bars2 = ax2.bar(x, df['PROCENT_MARJA'], color=colors, alpha=0.85, edgecolor='white', linewidth=0.5) ax2.set_xticks(x) ax2.set_xticklabels(df['TIP_PRODUS'], rotation=15, fontsize=9) ax2.set_ylabel('Marja (%)', fontsize=10) ax2.set_title('Marja per Tip Produs', fontsize=11, fontweight='bold') ax2.axhline(y=15, color=CHART_COLORS['accent'], linestyle='--', alpha=0.7, linewidth=1.5, label='Prag minim 15%') ax2.legend(loc='upper right', fontsize=8) # Add value labels for bar, val in zip(bars2, df['PROCENT_MARJA']): ax2.text(bar.get_x() + bar.get_width()/2., bar.get_height(), f'{val:.1f}%', ha='center', va='bottom', fontsize=9, color='#555') plt.tight_layout() return fig def create_cash_cycle_chart(df: pd.DataFrame) -> plt.Figure: """Create cash conversion cycle visualization - modern minimalist style""" setup_chart_style() if df is None or df.empty: fig, ax = plt.subplots(figsize=(10, 5)) ax.text(0.5, 0.5, 'Nu exista date', ha='center', va='center') return fig fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(14, 5)) # Extract values indicators = df['INDICATOR'].tolist() if 'INDICATOR' in df.columns else [] zile = df['ZILE'].tolist() if 'ZILE' in df.columns else [] if not indicators or not zile: ax1.text(0.5, 0.5, 'Date incomplete', ha='center', va='center') ax2.text(0.5, 0.5, 'Date incomplete', ha='center', va='center') return fig # Simplified color scheme colors_map = { 'DIO': CHART_COLORS['primary'], # Dark blue for inventory 'DSO': CHART_COLORS['secondary'], # Gray for receivables 'DPO': CHART_COLORS['positive'], # Green for payables (reduces cycle) 'CCC': CHART_COLORS['accent'] # Red for total cycle result } bar_colors = [] for ind in indicators: for key, color in colors_map.items(): if key in ind.upper(): bar_colors.append(color) break else: bar_colors.append(CHART_COLORS['secondary']) # Left: Component bars x = range(len(indicators)) bars = ax1.bar(x, zile, color=bar_colors, alpha=0.85, edgecolor='white', linewidth=0.5) ax1.set_xticks(x) ax1.set_xticklabels([ind[:20] for ind in indicators], rotation=45, ha='right', fontsize=9) ax1.set_ylabel('Zile', fontsize=10) ax1.set_title('Ciclu Conversie Cash - Componente', fontsize=11, fontweight='bold') # Add value labels for bar, val in zip(bars, zile): ax1.text(bar.get_x() + bar.get_width()/2., bar.get_height(), f'{int(val)}', ha='center', va='bottom', fontsize=10, fontweight='bold', color='#555') # Right: Formula visualization dio = next((z for i, z in zip(indicators, zile) if 'DIO' in i.upper()), 0) dso = next((z for i, z in zip(indicators, zile) if 'DSO' in i.upper() and 'DIO' not in i.upper()), 0) dpo = next((z for i, z in zip(indicators, zile) if 'DPO' in i.upper()), 0) ccc = dio + dso - dpo bars2 = ax2.bar([0, 1, 2], [dio, dso, dpo], color=[CHART_COLORS['primary'], CHART_COLORS['secondary'], CHART_COLORS['positive']], alpha=0.85, edgecolor='white', linewidth=0.5) # CCC result line with color based on health if ccc > 60: ccc_color = CHART_COLORS['accent'] elif ccc > 30: ccc_color = CHART_COLORS['secondary'] else: ccc_color = CHART_COLORS['positive'] ax2.axhline(y=ccc, color=ccc_color, linewidth=3, linestyle='--', label=f'CCC = {int(ccc)} zile') ax2.set_xticks([0, 1, 2]) ax2.set_xticklabels(['DIO\n(+Stoc)', 'DSO\n(+Incasare)', 'DPO\n(-Plata)'], fontsize=9) ax2.set_ylabel('Zile', fontsize=10) ax2.set_title('Formula: DIO + DSO - DPO = CCC', fontsize=11, fontweight='bold') ax2.legend(loc='upper right', fontsize=9) # Add value labels for bar, val in zip(bars2, [dio, dso, dpo]): ax2.text(bar.get_x() + bar.get_width()/2., bar.get_height(), f'{int(val)}', ha='center', va='bottom', fontsize=10, fontweight='bold', color='#555') # Status annotation if ccc > 60: verdict = "Ciclu lung - capital blocat mult timp" verdict_color = CHART_COLORS['accent'] elif ccc > 30: verdict = "Ciclu moderat - poate fi optimizat" verdict_color = CHART_COLORS['secondary'] else: verdict = "Ciclu eficient - capital rotit rapid" verdict_color = CHART_COLORS['positive'] ax2.text(0.5, -0.18, verdict, transform=ax2.transAxes, ha='center', fontsize=10, color=verdict_color, fontweight='bold') plt.tight_layout() return fig