Download backend/app/dashboard_export.py from Seth0330/qualifier: direct link, hf CLI and curl.
- Browser
- Download file 14.8 kB
-
https://huggingface.co/spaces/Seth0330/qualifier/resolve/main/backend/app/dashboard_export.py
- Command line
-
hf download hf://spaces/Seth0330/qualifier/backend/app/dashboard_export.py
-
curl -L -o dashboard_export.py https://huggingface.co/spaces/Seth0330/qualifier/resolve/main/backend/app/dashboard_export.py
14.8 kB
| """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 | |