Coverage for apps/core_los/dashboard_api.py: 90%
135 statements
« prev ^ index » next coverage.py v7.17.0, created at 2026-11-04 14:06 +0000
1"""GET /api/dashboard — cross-loan aggregate reporting for the executive dashboard.
3Each widget runs its own query against the scoped loan set (design.md D1): simpler to
4read and change than one mega-query, at the cost of N+1-at-the-widget-level, which is the
5right trade-off for a human-page-load-cadence endpoint.
6"""
8from __future__ import annotations
10from datetime import date, timedelta
11from decimal import Decimal
12from typing import Any
14from django.db.models import Count, Prefetch, Sum
15from django.utils import timezone
16from ninja import Router
18from apps.accounts.auth import requires, staff_loan_queryset
19from apps.application_1003.models import Borrower
20from apps.client_needs.models import ClientNeed
21from apps.core_los.dashboard_schemas import DashboardResponseOut
22from apps.core_los.schemas import _safe_transaction
23from apps.underwriting.models import Condition
24from apps.workflow.models import KeyDates, LoanTask, LoanTracker
25from domain.conditions import OPEN_CONDITION_STATUSES
26from domain.enums import ClientNeedStatus, LoanStage, TrackerStatus
28router = Router(tags=["dashboard"])
30# Pipeline-health buckets (dashboard spec "Pipeline-stage buckets").
31_PROSPECT_STAGES = [
32 LoanStage.APPLICATION_INTAKE.value, LoanStage.PREAPPROVED.value, LoanStage.QUALIFICATION.value,
33]
34_PROCESSING_STAGES = [
35 LoanStage.LOAN_SETUP.value, LoanStage.DISCLOSURE_SENT.value,
36 LoanStage.UNDERWRITING_SUBMITTED.value, LoanStage.APPROVED_WITH_CONDITION.value,
37 LoanStage.RE_SUBMITTAL.value,
38]
39_CLOSING_STAGES = [
40 LoanStage.CLEAR_TO_CLOSE.value, LoanStage.DOCS_OUT.value, LoanStage.DOCS_SIGNED.value,
41]
42_FUNDED_STAGES = [LoanStage.LOAN_FUNDED.value]
44_OPEN_TRACKER_STATUSES = [
45 s.value for s in TrackerStatus if s not in (TrackerStatus.COMPLETED, TrackerStatus.NA)
46]
47_ACTIVE_LOCK_STATUSES = ["LOCKED", "RELOCKED"]
50def _with_primary_borrower(qs):
51 return qs.prefetch_related(
52 Prefetch(
53 "borrowers",
54 queryset=Borrower.objects.filter(is_primary=True),
55 to_attr="_primary_borrowers",
56 )
57 )
60def _safe_key_dates(loan) -> KeyDates | None:
61 try:
62 return loan.key_dates
63 except KeyDates.DoesNotExist:
64 return None
67def _loan_ref(loan) -> dict:
68 primary = next(iter(getattr(loan, "_primary_borrowers", [])), None)
69 return {
70 "loan_guid": str(loan.guid),
71 "loan_number": loan.arive_loan_no,
72 "borrower_name": f"{primary.first_name} {primary.last_name}" if primary else "—",
73 }
76def _next_occurrence(anniversary: date, today: date) -> date:
77 """The next calendar occurrence of `anniversary`'s month/day on or after `today`."""
78 try:
79 this_year = anniversary.replace(year=today.year)
80 except ValueError:
81 # Feb 29 anniversary in a non-leap year: observe on Mar 1.
82 this_year = date(today.year, 3, 1)
83 if this_year >= today:
84 return this_year
85 try:
86 return anniversary.replace(year=today.year + 1)
87 except ValueError:
88 return date(today.year + 1, 3, 1)
91def _kpis(qs, today: date) -> dict:
92 total = qs.aggregate(total=Sum("transaction__base_loan_amt"))["total"]
93 pipeline_volume = total or Decimal("0.00")
94 funded = qs.filter(stage__in=_FUNDED_STAGES).count()
95 tasks_due = LoanTask.objects.filter(
96 loan__in=qs, completed=False, due_date__lte=today
97 ).count()
98 return {"pipeline_volume": pipeline_volume, "funded": funded, "tasks_due": tasks_due}
101def _by_pipeline(qs) -> dict:
102 return {
103 "prospect": qs.filter(stage__in=_PROSPECT_STAGES).count(),
104 "processing": qs.filter(stage__in=_PROCESSING_STAGES).count(),
105 "closing": qs.filter(stage__in=_CLOSING_STAGES).count(),
106 "funded": qs.filter(stage__in=_FUNDED_STAGES).count(),
107 }
110def _rate_lock_watchlists(qs) -> tuple[list[dict], list[dict]]:
111 now = timezone.now()
112 locked = _with_primary_borrower(
113 qs.exclude(stage__in=_FUNDED_STAGES).filter(
114 rate_lock__status__in=_ACTIVE_LOCK_STATUSES
115 )
116 ).select_related("rate_lock")
118 def _lock_row(loan):
119 return {
120 **_loan_ref(loan),
121 "expires_at": loan.rate_lock.expires_at,
122 "rate": loan.rate_lock.locked_rate,
123 }
125 expiring_window = locked.filter(
126 rate_lock__expires_at__gte=now, rate_lock__expires_at__lte=now + timedelta(days=14)
127 )
128 expiring = [_lock_row(loan) for loan in expiring_window]
129 expired = [_lock_row(loan) for loan in locked.filter(rate_lock__expires_at__lt=now)]
130 expiring.sort(key=lambda row: row["expires_at"])
131 expired.sort(key=lambda row: row["expires_at"])
132 return expiring, expired
135def _rate_alerts(qs) -> list[dict]:
136 loans = _with_primary_borrower(
137 qs.filter(rate_lock__status__in=_ACTIVE_LOCK_STATUSES, rate_lock__locked_rate__gt=7)
138 ).select_related("rate_lock")
139 return [{**_loan_ref(loan), "rate": loan.rate_lock.locked_rate} for loan in loans]
142def _funded_ytd(qs, today: date) -> dict:
143 result: dict[str, Any] = {
144 "purchase_count": 0, "purchase_volume": Decimal("0.00"),
145 "refinance_count": 0, "refinance_volume": Decimal("0.00"),
146 }
147 funded = qs.filter(stage__in=_FUNDED_STAGES).select_related("transaction", "key_dates")
148 for loan in funded:
149 txn = _safe_transaction(loan)
150 if txn is None: 150 ↛ 151line 150 didn't jump to line 151 because the condition on line 150 was never true
151 continue
152 key_dates = _safe_key_dates(loan)
153 effective_date = (key_dates.funding_date if key_dates else None) or loan.updated_at.date()
154 if effective_date.year != today.year:
155 continue
156 if loan.purpose == "Purchase": 156 ↛ 159line 156 didn't jump to line 159 because the condition on line 156 was always true
157 result["purchase_count"] += 1
158 result["purchase_volume"] += txn.base_loan_amt
159 elif loan.purpose == "Refinance":
160 result["refinance_count"] += 1
161 result["refinance_volume"] += txn.base_loan_amt
162 return result
165def _lender_spread(qs) -> list[dict]:
166 rows = (
167 qs.filter(rate_lock__isnull=False)
168 .values("rate_lock__product__investor")
169 .annotate(count=Count("id"), volume=Sum("transaction__base_loan_amt"))
170 .order_by("-volume")
171 )
172 return [
173 {
174 "lender_id": row["rate_lock__product__investor"],
175 "count": row["count"],
176 "volume": row["volume"] or Decimal("0.00"),
177 }
178 for row in rows
179 ]
182def _upcoming_events(qs, today: date) -> dict:
183 with_borrower = _with_primary_borrower(
184 qs.filter(borrowers__is_primary=True, borrowers__birth_date__isnull=False).distinct()
185 )
186 birthdays = []
187 for loan in with_borrower:
188 primary = next(iter(getattr(loan, "_primary_borrowers", [])), None)
189 if primary is None or primary.birth_date is None: 189 ↛ 190line 189 didn't jump to line 190 because the condition on line 189 was never true
190 continue
191 next_bday = _next_occurrence(primary.birth_date, today)
192 if (next_bday - today).days <= 30:
193 birthdays.append({**_loan_ref(loan), "date": next_bday})
195 funded = _with_primary_borrower(qs.filter(stage__in=_FUNDED_STAGES)).select_related(
196 "transaction", "key_dates"
197 )
198 anniversaries = []
199 early_payoffs = []
200 mi_drop_eligible = []
201 for loan in funded:
202 key_dates = _safe_key_dates(loan)
203 funding_date = key_dates.funding_date if key_dates else None
204 if funding_date is not None:
205 next_anniv = _next_occurrence(funding_date, today)
206 if (next_anniv - today).days <= 30: 206 ↛ 209line 206 didn't jump to line 209 because the condition on line 206 was always true
207 anniversaries.append({**_loan_ref(loan), "date": next_anniv})
209 txn = _safe_transaction(loan)
210 if txn is None: 210 ↛ 211line 210 didn't jump to line 211 because the condition on line 210 was never true
211 continue
212 if txn.note_rate_percent is not None and txn.note_rate_percent >= Decimal("7.5"): 212 ↛ 213line 212 didn't jump to line 213 because the condition on line 212 was never true
213 early_payoffs.append({**_loan_ref(loan), "rate": txn.note_rate_percent})
214 if (
215 txn.mortgage_type == "Conventional"
216 and txn.ltv_ratio_percent is not None
217 and txn.ltv_ratio_percent > Decimal("80")
218 ):
219 mi_drop_eligible.append({**_loan_ref(loan), "ltv": txn.ltv_ratio_percent})
221 return {
222 "birthdays": birthdays,
223 "loan_anniversaries": anniversaries,
224 "early_payoffs": early_payoffs,
225 "mi_drop_eligible": mi_drop_eligible,
226 }
229def _recent_loans(qs) -> list[dict]:
230 loans = _with_primary_borrower(qs.order_by("-updated_at"))[:5]
231 return [
232 {**_loan_ref(loan), "stage": loan.stage, "updated_at": loan.updated_at} for loan in loans
233 ]
236def _tracker_counts(qs) -> dict:
237 by_key = dict(
238 LoanTracker.objects.filter(
239 loan__in=qs, key__in=["trid", "appraisal"], status__in=_OPEN_TRACKER_STATUSES
240 )
241 .values("key")
242 .annotate(c=Count("id"))
243 .values_list("key", "c")
244 )
245 closing = LoanTracker.objects.filter(
246 loan__in=qs, category="Closing", status__in=_OPEN_TRACKER_STATUSES
247 ).count()
248 return {
249 "loan": by_key.get("trid", 0),
250 "appraisal": by_key.get("appraisal", 0),
251 "closing": closing,
252 }
255def _conditions_pending(qs) -> int:
256 return Condition.objects.filter(
257 loan__in=qs, status__in=[s.value for s in OPEN_CONDITION_STATUSES]
258 ).count()
261def _needs_pending(qs) -> int:
262 return ClientNeed.objects.filter(loan__in=qs, status=ClientNeedStatus.REQUESTED.value).count()
265@router.get("/dashboard", response=DashboardResponseOut, auth=requires("LOAN_VIEW"))
266def get_dashboard(request, scope: str = "all"):
267 qs = staff_loan_queryset(request.principal, scope)
268 today = timezone.now().date()
270 kpis = _kpis(qs, today)
271 by_pipeline = _by_pipeline(qs)
272 locks_expiring, locks_expired = _rate_lock_watchlists(qs)
274 return {
275 "scope": scope,
276 "pipeline_volume": kpis["pipeline_volume"],
277 "in_process": by_pipeline["processing"],
278 "funded": kpis["funded"],
279 "tasks_due": kpis["tasks_due"],
280 "by_pipeline": by_pipeline,
281 "widgets": {
282 "locks_expiring": locks_expiring,
283 "locks_expired": locks_expired,
284 "conditions_pending": _conditions_pending(qs),
285 "needs_pending": _needs_pending(qs),
286 "leads_new": 0, # No Lead/CRM model yet (design.md D4).
287 "funded_ytd": _funded_ytd(qs, today),
288 "lender_spread": _lender_spread(qs),
289 "upcoming_events": _upcoming_events(qs, today),
290 "recent_loans": _recent_loans(qs),
291 "counts": _tracker_counts(qs),
292 "contingencies": 0, # No contingency-tracking concept exists (design.md D3).
293 "rate_alerts": _rate_alerts(qs),
294 },
295 }