Prism

import pandas as pd
import smtplib
from email.message import EmailMessage
from pathlib import Path

def generate_executive_finance_report(master_data_path):
    # 1. Caricamento dati vettorializzato
    df = pd.read_parquet(master_data_path)
    df['Date'] = pd.to_datetime(df['Date'])
    
    current_month = datetime.now().month
    prev_month = current_month - 1 if current_month > 1 else 12

    # 2. Calcoli Finanziari (KPIs)
    df['Margin_%'] = (df['Revenue'] - df['COGS']) / df['Revenue']
    
    # Creazione Pivot Table via Pandas
    business_unit_rpt = pd.pivot_table(
        df,
        values=['Revenue', 'Margin_%'],
        index=['Business_Unit'],
        columns=['Date_Month'],
        aggfunc={'Revenue': 'sum', 'Margin_%': 'mean'}
    )
    
    # 3. Formattazione Condizionale su File Excel originato da codice
    report_path = f"Board_Report_M{current_month}.xlsx"
    with pd.ExcelWriter(report_path, engine='xlsxwriter') as writer:
        business_unit_rpt.to_excel(writer, sheet_name='Executive Summary')
        workbook = writer.book
        worksheet = writer.sheets['Executive Summary']
        
        # Formato valuta e percentuale applicati a livello di cella
        currency_format = workbook.add_format({'num_format': '€#,##0.00'})
        percent_format = workbook.add_format({'num_format': '0.0%'})
        worksheet.set_column('B:C', 18, currency_format)
        worksheet.set_column('D:E', 12, percent_format)

    # 4. Email Pipeline: Dispatcher SMTP sicuro
    msg = EmailMessage()
    msg['Subject'] = f"Automated Finance Report - {datetime.now().strftime('%B %Y')}"
    msg['From'] = "bot_finance@corporate.com"
    msg['To'] = "board_of_directors@corporate.com"
    msg.set_content("In allegato il report finanziario elaborato dall'RPA.")
    
    with open(report_path, 'rb') as f:
        msg.add_attachment(f.read(), maintype='application', subtype='vnd.ms-excel', filename=report_path)
        
    with smtplib.SMTP_SSL('smtp.corporate_server.com', 465) as smtp:
        smtp.login('bot_apikey', 'secure_secret_token')
        smtp.send_message(msg)

    return "Report successfully generated and dispatched to the Board."