"""Sales Dashboard Excel export — pure aggregation/formatting on already-fetched, already RBAC-scoped, already period-filtered data. No DB or route access here by design, so this module has zero risk of bypassing the security scoping that main.py applies before calling it. compute_dashboard_metrics mirrors frontend/src/pages/SalesDashboard.jsx's client-side useMemo chain exactly (same "open deals by created_at, won deals by won_at" period rule, same forecast/ actual/avg-won formulas) so the export never disagrees with what's on screen. """ from __future__ import annotations import io from datetime import datetime, date from typing import Any, Dict, List, Optional from openpyxl import Workbook from openpyxl.styles import Alignment, Font, PatternFill from openpyxl.utils import get_column_letter STAGE_ORDER = ["new", "discovery", "proposal", "negotiation", "won", "lost"] STAGE_LABELS = { "new": "New", "discovery": "Discovery", "proposal": "Proposal", "negotiation": "Negotiation", "won": "Won", "lost": "Lost", } FUNNEL_STAGES = ["new", "discovery", "proposal", "negotiation", "won"] HEADER_FILL = PatternFill(start_color="1E293B", end_color="1E293B", fill_type="solid") HEADER_FONT = Font(bold=True, color="FFFFFF") TITLE_FONT = Font(bold=True, size=14) SUBTLE_FONT = Font(color="64748B", italic=True) KPI_LABEL_FONT = Font(bold=True, color="475569", size=10) KPI_VALUE_FONT = Font(bold=True, size=16, color="0F172A") SECTION_FONT = Font(bold=True, size=12, color="0F172A") CURRENCY_FORMAT = "$#,##0" def _normalize_stage(s: Optional[str]) -> str: v = (s or "new").strip().lower() return v if v in STAGE_ORDER else "new" def _is_open_stage(s: Optional[str]) -> bool: v = _normalize_stage(s) return v not in ("won", "lost") def _month_key(iso: Optional[str]) -> Optional[str]: if not iso or not isinstance(iso, str) or len(iso) < 7: return None mk = iso[:7] return mk if len(mk) == 7 and mk[4] == "-" else None def _deal_period_date(d: dict) -> Optional[str]: """Same rule as dealInDashboardPeriod in SalesDashboard.jsx: won deals use won_at, else created_at is used for both open and won deals.""" if _normalize_stage(d.get("stage")) == "won" and d.get("won_at"): return d["won_at"] return d.get("created_at") def filter_deals_in_period(deals: List[dict], bounds: Optional[tuple]) -> List[dict]: if bounds is None: return list(deals) start, end = bounds out = [] for d in deals: iso = _deal_period_date(d) if not iso: continue try: dt = datetime.fromisoformat(iso.replace("Z", "")) except (ValueError, TypeError): continue if start <= dt <= end: out.append(d) return out def _iso_in_bounds(iso: Optional[str], bounds: Optional[tuple]) -> bool: if bounds is None: return True if not iso: return False try: dt = datetime.fromisoformat(iso.replace("Z", "")) except (ValueError, TypeError): return False start, end = bounds return start <= dt <= end def compute_dashboard_metrics( deals: List[dict], leads: List[dict], activities: List[dict], bounds: Optional[tuple], ) -> Dict[str, Any]: """`deals`/`leads`/`activities` are already scope-filtered (RBAC) but NOT yet date-filtered — this function applies the exact same date-in-period rules the dashboard UI uses, per record type, since "deals created in period" and "deals scoped to period for revenue/closings" are two different filters of the same list (mirrors SalesDashboard.jsx's dealsCreatedCount vs. dashboardScopedDeals distinction).""" scoped_deals = filter_deals_in_period(deals, bounds) open_deals = [d for d in scoped_deals if _is_open_stage(d.get("stage"))] won_deals = [d for d in scoped_deals if _normalize_stage(d.get("stage")) == "won"] forecast_total = sum((d.get("forecast_value") or 0) for d in scoped_deals) actual_total = sum( (d.get("deal_value") or 0) for d in won_deals if d.get("deal_value") is not None ) won_values = [d["deal_value"] for d in won_deals if d.get("deal_value") is not None] avg_won = (sum(won_values) / len(won_values)) if won_values else None calls = sum(1 for a in activities if _iso_in_bounds(a.get("created_at"), bounds) and a.get("activity_type") == "call") visits = sum(1 for a in activities if _iso_in_bounds(a.get("created_at"), bounds) and a.get("activity_type") == "visit") new_leads_count = sum(1 for l in leads if _iso_in_bounds(l.get("created_at"), bounds)) deals_created_count = sum(1 for d in deals if _iso_in_bounds(d.get("created_at"), bounds)) closings_count = len(won_deals) stage_counts = {s: 0 for s in STAGE_ORDER} for d in scoped_deals: stage_counts[_normalize_stage(d.get("stage"))] += 1 forecast_by_stage = [ { "stage": STAGE_LABELS[s], "forecast": sum( (d.get("forecast_value") or 0) for d in open_deals if _normalize_stage(d.get("stage")) == s ), } for s in STAGE_ORDER if s not in ("won", "lost") ] forecast_month_map: Dict[str, float] = {} undated_forecast = 0.0 for d in open_deals: mk = _month_key(d.get("expected_close_date")) fv = d.get("forecast_value") or 0 if not mk: undated_forecast += fv else: forecast_month_map[mk] = forecast_month_map.get(mk, 0) + fv forecast_by_month = [{"month": mk, "forecast": v} for mk, v in sorted(forecast_month_map.items())] if undated_forecast > 0: forecast_by_month.append({"month": "Undated", "forecast": undated_forecast}) actual_month_map: Dict[str, float] = {} for d in won_deals: mk = _month_key(d.get("expected_close_date")) or _month_key(d.get("created_at")) if not mk or d.get("deal_value") is None: continue actual_month_map[mk] = actual_month_map.get(mk, 0) + d["deal_value"] actual_by_month = [{"month": mk, "actual": v} for mk, v in sorted(actual_month_map.items())] rep_map: Dict[str, dict] = {} for d in scoped_deals: oid = d.get("owner_user_id") key = "none" if oid is None else str(oid) if key not in rep_map: label = ( d.get("owner_display_name") or d.get("owner_initials") or ("Unassigned" if key == "none" else f"User {oid}") ).strip() rep_map[key] = {"rep": label, **{s: 0 for s in STAGE_ORDER}} rep_map[key][_normalize_stage(d.get("stage"))] += 1 deals_by_rep = sorted( rep_map.values(), key=lambda r: (r["rep"] == "Unassigned", r["rep"].lower()) ) return { "forecast_total": forecast_total, "actual_total": actual_total, "avg_won": avg_won, "calls": calls, "visits": visits, "new_leads_count": new_leads_count, "deals_created_count": deals_created_count, "closings_count": closings_count, "stage_counts": stage_counts, "forecast_by_stage": forecast_by_stage, "forecast_by_month": forecast_by_month, "actual_by_month": actual_by_month, "deals_by_rep": deals_by_rep, } def _autosize(ws, max_width: int = 42) -> None: for col_cells in ws.columns: length = max((len(str(c.value)) if c.value is not None else 0) for c in col_cells) col_letter = get_column_letter(col_cells[0].column) ws.column_dimensions[col_letter].width = min(max(length + 2, 10), max_width) def _write_header_row(ws, row: int, headers: List[str]) -> None: for ci, h in enumerate(headers, start=1): cell = ws.cell(row=row, column=ci, value=h) cell.font = HEADER_FONT cell.fill = HEADER_FILL cell.alignment = Alignment(horizontal="left", vertical="center") def build_dashboard_workbook( *, generated_at: datetime, scope_label: str, period_label: str, metrics: Dict[str, Any], deals: List[dict], leads: List[dict], activities: List[dict], ) -> io.BytesIO: wb = Workbook() summary = wb.active summary.title = "Summary" summary["A1"] = "Sales Dashboard Export" summary["A1"].font = TITLE_FONT summary["A2"] = f"Scope: {scope_label} Period: {period_label} Generated: {generated_at.strftime('%Y-%m-%d %H:%M UTC')}" summary["A2"].font = SUBTLE_FONT row = 4 summary.cell(row=row, column=1, value="KPI").font = KPI_LABEL_FONT summary.cell(row=row, column=2, value="Value").font = KPI_LABEL_FONT row += 1 kpi_rows = [ ("Forecasted revenue", metrics["forecast_total"], CURRENCY_FORMAT), ("Actual revenue", metrics["actual_total"], CURRENCY_FORMAT), ("Avg. value of won deals", metrics["avg_won"], CURRENCY_FORMAT), ("Calls", metrics["calls"], None), ("Visits", metrics["visits"], None), ("New leads", metrics["new_leads_count"], None), ("Deals", metrics["deals_created_count"], None), ("Closings", metrics["closings_count"], None), ] for label, value, fmt in kpi_rows: c1 = summary.cell(row=row, column=1, value=label) c1.font = Font(bold=True) c2 = summary.cell(row=row, column=2, value=value if value is not None else "—") if fmt and value is not None: c2.number_format = fmt row += 1 row += 2 summary.cell(row=row, column=1, value="Pipeline by stage").font = SECTION_FONT row += 1 _write_header_row(summary, row, ["Stage", "Count"]) row += 1 for s in STAGE_ORDER: summary.cell(row=row, column=1, value=STAGE_LABELS[s]) summary.cell(row=row, column=2, value=metrics["stage_counts"].get(s, 0)) row += 1 row += 2 summary.cell(row=row, column=1, value="Forecasted revenue by stage").font = SECTION_FONT row += 1 _write_header_row(summary, row, ["Stage", "Forecast"]) row += 1 for r in metrics["forecast_by_stage"]: summary.cell(row=row, column=1, value=r["stage"]) c = summary.cell(row=row, column=2, value=r["forecast"]) c.number_format = CURRENCY_FORMAT row += 1 row += 2 summary.cell(row=row, column=1, value="Actual revenue by month (won)").font = SECTION_FONT row += 1 _write_header_row(summary, row, ["Month", "Actual revenue"]) row += 1 for r in metrics["actual_by_month"]: summary.cell(row=row, column=1, value=r["month"]) c = summary.cell(row=row, column=2, value=r["actual"]) c.number_format = CURRENCY_FORMAT row += 1 row += 2 summary.cell(row=row, column=1, value="Forecasted revenue by month").font = SECTION_FONT row += 1 _write_header_row(summary, row, ["Month", "Forecast"]) row += 1 for r in metrics["forecast_by_month"]: summary.cell(row=row, column=1, value=r["month"]) c = summary.cell(row=row, column=2, value=r["forecast"]) c.number_format = CURRENCY_FORMAT row += 1 row += 2 summary.cell(row=row, column=1, value="Deal stages by rep").font = SECTION_FONT row += 1 rep_headers = ["Rep"] + [STAGE_LABELS[s] for s in STAGE_ORDER] _write_header_row(summary, row, rep_headers) row += 1 for r in metrics["deals_by_rep"]: summary.cell(row=row, column=1, value=r["rep"]) for ci, s in enumerate(STAGE_ORDER, start=2): summary.cell(row=row, column=ci, value=r.get(s, 0)) row += 1 summary.freeze_panes = "A5" _autosize(summary) # --- Raw data sheets --- deals_ws = wb.create_sheet("Deals") deal_headers = [ "ID", "Name", "Stage", "Owner", "Deal value", "Forecast value", "Close probability %", "Expected close date", "Won at", "Created at", "Account", "Contact", ] _write_header_row(deals_ws, 1, deal_headers) for ri, d in enumerate(deals, start=2): deals_ws.cell(row=ri, column=1, value=d.get("id")) deals_ws.cell(row=ri, column=2, value=d.get("name") or "") deals_ws.cell(row=ri, column=3, value=STAGE_LABELS.get(_normalize_stage(d.get("stage")), d.get("stage"))) deals_ws.cell(row=ri, column=4, value=d.get("owner_display_name") or d.get("owner_initials") or "Unassigned") c = deals_ws.cell(row=ri, column=5, value=d.get("deal_value")) c.number_format = CURRENCY_FORMAT c = deals_ws.cell(row=ri, column=6, value=d.get("forecast_value")) c.number_format = CURRENCY_FORMAT deals_ws.cell(row=ri, column=7, value=d.get("close_probability")) deals_ws.cell(row=ri, column=8, value=d.get("expected_close_date") or "") deals_ws.cell(row=ri, column=9, value=(d.get("won_at") or "")[:19]) deals_ws.cell(row=ri, column=10, value=(d.get("created_at") or "")[:19]) deals_ws.cell(row=ri, column=11, value=d.get("account_name") or "") deals_ws.cell(row=ri, column=12, value=d.get("contact_display") or "") deals_ws.freeze_panes = "A2" _autosize(deals_ws) leads_ws = wb.create_sheet("Leads") lead_headers = ["ID", "Name", "Email", "Company", "Status", "Owner", "Created at"] _write_header_row(leads_ws, 1, lead_headers) for ri, l in enumerate(leads, start=2): name = f"{l.get('first_name') or ''} {l.get('last_name') or ''}".strip() leads_ws.cell(row=ri, column=1, value=l.get("id")) leads_ws.cell(row=ri, column=2, value=name) leads_ws.cell(row=ri, column=3, value=l.get("email") or "") leads_ws.cell(row=ri, column=4, value=l.get("company_name") or "") leads_ws.cell(row=ri, column=5, value=l.get("crm_status") or "") leads_ws.cell(row=ri, column=6, value=l.get("owner_display_name") or l.get("owner_initials") or "Unassigned") leads_ws.cell(row=ri, column=7, value=(l.get("created_at") or "")[:19]) leads_ws.freeze_panes = "A2" _autosize(leads_ws) activities_ws = wb.create_sheet("Activities") act_headers = ["ID", "Type", "By", "Notes", "Created at"] _write_header_row(activities_ws, 1, act_headers) for ri, a in enumerate(activities, start=2): activities_ws.cell(row=ri, column=1, value=a.get("id")) activities_ws.cell(row=ri, column=2, value=(a.get("activity_type") or "").capitalize()) activities_ws.cell(row=ri, column=3, value=a.get("user_display_name") or "") activities_ws.cell(row=ri, column=4, value=a.get("notes") or "") activities_ws.cell(row=ri, column=5, value=(a.get("created_at") or "")[:19]) activities_ws.freeze_panes = "A2" _autosize(activities_ws) buf = io.BytesIO() wb.save(buf) buf.seek(0) return buf