qualifier / backend /app /dashboard_export.py
Sethu0330's picture
Add FTL RFQ Qualifier agent + full SALES CRM app for HF Spaces testing
005df22 unverified
Raw History Blame Contribute Delete
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