"""Pure analytics aggregation for the cases reporting endpoints. The functions here take a base `Case` queryset (already filtered by org + visibility + any caller-supplied filters) plus a [from_dt, to_dt) window and return JSON-friendly dicts that the views serialize directly. Why pull values into Python rather than use DB percentiles? SQLite (used in the test suite) has no `percentile_cont`. The volumes here (cases per org per window) sit well within the millions-of-rows-into-Python range; if a deployment ever pushes past that, switch to a Postgres-only path or the optional `CaseMetricsDaily` cache table called out in the spec. """ from __future__ import annotations import math from datetime import date, datetime, timedelta from typing import Iterable, Optional from uuid import UUID from django.db.models import Q, QuerySet from django.utils import timezone from cases.workflow import ( TERMINAL_STATUSES, resolve_next_response_targets, resolve_sla_targets, ) # --------------------------------------------------------------------------- # Helpers def _percentile(sorted_values: list[float], pct: float) -> Optional[float]: """Linear-interpolation percentile (matches numpy.percentile default). Returns None for empty input. ``pct`` is in [0, 100]. ``sorted_values`` must already be sorted ascending. """ if not sorted_values: return None if pct <= 0: return sorted_values[0] if pct >= 100: return sorted_values[-1] rank = (pct / 100.0) * (len(sorted_values) - 1) lo = math.floor(rank) hi = math.ceil(rank) if lo == hi: return sorted_values[lo] frac = rank - lo return sorted_values[lo] + (sorted_values[hi] - sorted_values[lo]) * frac def _hours_between(later: datetime, earlier: datetime) -> float: return (later - earlier).total_seconds() / 3600.0 def _day_start(d: date) -> datetime: """The instant the org's day ``d`` begins. Every bucket here is labelled with a calendar date, so its edges have to be the same calendar's midnights. In Asia/Kolkata a ticket opened at 04:00 belongs to that day; measured against a UTC boundary it lands in the one before, and the chart shows a quiet morning followed by a spike that never happened. ``get_current_timezone`` returns whatever ``activate_org_timezone`` put there for this request, so this is the org's midnight, not the server's. """ return datetime.combine( d, datetime.min.time(), tzinfo=timezone.get_current_timezone() ) def _bucket_dates(from_dt: datetime, to_dt: datetime) -> list[date]: """Inclusive list of org-local dates from from_dt to to_dt-1 day.""" if to_dt <= from_dt: return [] start = timezone.localdate(from_dt) end = timezone.localdate(to_dt - timedelta(seconds=1)) out: list[date] = [] cur = start while cur <= end: out.append(cur) cur += timedelta(days=1) return out def _coerce_window( from_dt: Optional[datetime], to_dt: Optional[datetime] ) -> tuple[datetime, datetime]: """Defaults to last 30 days; ensures both ends are tz-aware. A naive end is read as the org's wall clock, matching how the client sent it. Reading it as UTC would shift an Indian org's window by five and a half hours against the days it is asking about. """ now = timezone.now() if to_dt is None: to_dt = now if from_dt is None: from_dt = to_dt - timedelta(days=30) if timezone.is_naive(from_dt): from_dt = timezone.make_aware(from_dt) if timezone.is_naive(to_dt): to_dt = timezone.make_aware(to_dt) return from_dt, to_dt # --------------------------------------------------------------------------- # Metric computations def compute_frt( qs: QuerySet, from_dt: Optional[datetime] = None, to_dt: Optional[datetime] = None, ) -> dict: """First Response Time aggregation. Window scopes by `Case.created_at`. For each case with `first_response_at` set, FRT = first_response_at - created_at. Breach counts cases where FRT > sla_first_response_hours, OR where first_response_at is null and `now - created_at > sla` (an in-flight breach). """ from_dt, to_dt = _coerce_window(from_dt, to_dt) in_window = qs.filter(created_at__gte=from_dt, created_at__lt=to_dt) rows = list( in_window.values_list( "id", "created_at", "first_response_at", "sla_first_response_hours", ) ) now = timezone.now() deltas: list[float] = [] breach_count = 0 case_ids: list[UUID] = [] breach_ids: list[UUID] = [] for case_id, created_at, first_response_at, sla_hours in rows: case_ids.append(case_id) if first_response_at is not None: hours = _hours_between(first_response_at, created_at) deltas.append(hours) if hours > (sla_hours or 0): breach_count += 1 breach_ids.append(case_id) else: # Open-but-unresponded: count as breach if SLA already exceeded. if _hours_between(now, created_at) > (sla_hours or 0): breach_count += 1 breach_ids.append(case_id) deltas.sort() median_h = _percentile(deltas, 50) p90_h = _percentile(deltas, 90) # Daily series of medians (created_at bucket). series: list[dict] = [] by_day: dict[date, list[float]] = {} for _id, created_at, first_response_at, _sla in rows: if first_response_at is None: continue bucket = timezone.localdate(created_at) by_day.setdefault(bucket, []).append( _hours_between(first_response_at, created_at) ) for d in _bucket_dates(from_dt, to_dt): values = sorted(by_day.get(d, [])) series.append( { "bucket": d.isoformat(), "median": _percentile(values, 50) if values else None, "count": len(values), } ) return { "median_hours": median_h, "p90_hours": p90_h, "count": len(deltas), "breach_count": breach_count, "series": series, "case_ids": [str(cid) for cid in case_ids], "breach_case_ids": [str(cid) for cid in breach_ids], } def _next_response_waits( case_ids: Iterable[UUID], ) -> dict[UUID, list[tuple[datetime, Optional[datetime]]]]: """Every wait for a next reply on the given cases, as ``(start, answered_at)``. A customer message is a public comment with no ``commented_by`` (written in the portal, or by nobody on our side) or an inbound email on the ticket. An agent reply is a public comment with a ``commented_by``: the same test ``cases.signals._maybe_stamp_first_response`` uses, so the first reply this finds is the one ``first_response_at`` records. Internal notes are neither. Only waits after the first agent reply count. Everything before it is the first-response wait, which FRT already measures from ``created_at``; the email that opened an inbound ticket always falls there. A run of customer messages is one wait, timed from the first one nobody has answered yet, and an agent reply with nothing to answer ends no wait. ``answered_at`` is None for a wait still open. """ from django.contrib.contenttypes.models import ContentType from cases.models import Case, EmailMessage from common.models import Comment case_ids = list(case_ids) if not case_ids: return {} # (case, when, is_agent). A customer message sorts before an agent reply # stamped the same instant, so that reply answers it. events: list[tuple[UUID, datetime, bool]] = [] for case_id, created_at, author_id in Comment.objects.filter( content_type=ContentType.objects.get_for_model(Case), object_id__in=case_ids, is_internal=False, ).values_list("object_id", "created_at", "commented_by_id"): events.append((case_id, created_at, author_id is not None)) for case_id, received_at in EmailMessage.objects.filter( case_id__in=case_ids, direction="inbound", drop_reason="" ).values_list("case_id", "received_at"): events.append((case_id, received_at, False)) events.sort(key=lambda e: (e[1], e[2])) answered_once: set[UUID] = set() waiting_since: dict[UUID, datetime] = {} waits: dict[UUID, list[tuple[datetime, Optional[datetime]]]] = {} for case_id, at, is_agent in events: if is_agent: start = waiting_since.pop(case_id, None) if start is not None: waits.setdefault(case_id, []).append((start, at)) answered_once.add(case_id) elif case_id in answered_once and case_id not in waiting_since: waiting_since[case_id] = at for case_id, start in waiting_since.items(): waits.setdefault(case_id, []).append((start, None)) return waits def _wait_breached(start, answered_at, target_hours, is_open, now) -> bool: """Late if answered past the target; also late if still waiting past it on an open ticket. A closed ticket's unanswered last word (the customer's "thanks") is not somebody we owe a reply.""" if answered_at is not None: return _hours_between(answered_at, start) > target_hours return is_open and _hours_between(now, start) > target_hours def compute_nrt( qs: QuerySet, from_dt: Optional[datetime] = None, to_dt: Optional[datetime] = None, *, org_id, ) -> dict: """Next Response Time aggregation: how long a customer who wrote back after the first reply waited for the next one. See `_next_response_waits`. Same shape as `compute_frt`, plus ``by_priority``: every priority in worst-first order, with its target, median and met/missed counts (an unanswered wait not yet past its target is neither). The window scopes by when the wait began (the customer's message), since one ticket can wait many times over its life. Only cases in ``qs`` are read, so a non-admin is scored on the tickets they may see. Breaches are scored against the org's next-response target for the case's priority (`cases.workflow.resolve_next_response_targets`): a wait answered late, or still unanswered past it on an open ticket. """ from django.contrib.contenttypes.models import ContentType from cases.models import Case, EmailMessage from common.models import Comment from_dt, to_dt = _coerce_window(from_dt, to_dt) # Only a case with a customer message inside the window can have a wait # starting there. The wait itself is found from the case's whole history, # because whether a message opens a wait depends on what came before it. visible_ids = qs.values("id") started = set( Comment.objects.filter( content_type=ContentType.objects.get_for_model(Case), object_id__in=visible_ids, is_internal=False, commented_by__isnull=True, created_at__gte=from_dt, created_at__lt=to_dt, ).values_list("object_id", flat=True) ) | set( EmailMessage.objects.filter( case_id__in=visible_ids, direction="inbound", drop_reason="", received_at__gte=from_dt, received_at__lt=to_dt, ).values_list("case_id", flat=True) ) rows = list(qs.filter(id__in=started).values_list("id", "priority", "status")) waits = _next_response_waits(r[0] for r in rows) targets = resolve_next_response_targets(org_id) now = timezone.now() deltas: list[float] = [] by_day: dict[date, list[float]] = {} per_priority: dict[str, dict] = { prio: {"hours": [], "met": 0, "missed": 0} for prio in _SERVICE_PRIORITY_ORDER } case_ids: list[UUID] = [] breach_ids: list[UUID] = [] breach_count = 0 for case_id, priority, status in rows: target = targets.get(priority, 4) # An unknown priority string still counts in the totals; it just has # no row of its own, as in `_first_response_by_priority`. bucket = per_priority.get(priority, {"hours": [], "met": 0, "missed": 0}) in_window = [ (start, answered) for start, answered in waits.get(case_id, []) if from_dt <= start < to_dt ] if not in_window: continue case_ids.append(case_id) case_breached = False for start, answered in in_window: if answered is not None: hours = _hours_between(answered, start) deltas.append(hours) bucket["hours"].append(hours) by_day.setdefault(timezone.localdate(start), []).append(hours) breached = _wait_breached( start, answered, target, status not in TERMINAL_STATUSES, now ) if breached: breach_count += 1 bucket["missed"] += 1 case_breached = True elif answered is not None: bucket["met"] += 1 if case_breached: breach_ids.append(case_id) deltas.sort() series = [] for d in _bucket_dates(from_dt, to_dt): values = sorted(by_day.get(d, [])) series.append( { "bucket": d.isoformat(), "median": _percentile(values, 50) if values else None, "count": len(values), } ) by_priority = [ { "priority": prio, "target_hours": targets.get(prio, 4), "median_hours": _percentile(sorted(b["hours"]), 50), "met": b["met"], "missed": b["missed"], } for prio, b in per_priority.items() ] return { "median_hours": _percentile(deltas, 50), "p90_hours": _percentile(deltas, 90), "count": len(deltas), "breach_count": breach_count, "series": series, "by_priority": by_priority, "case_ids": [str(cid) for cid in case_ids], "breach_case_ids": [str(cid) for cid in breach_ids], } def compute_mttr( qs: QuerySet, from_dt: Optional[datetime] = None, to_dt: Optional[datetime] = None, ) -> dict: """Mean Time To Resolution. Window scopes by `Case.resolved_at`.""" from_dt, to_dt = _coerce_window(from_dt, to_dt) rows = list( qs.filter( resolved_at__isnull=False, resolved_at__gte=from_dt, resolved_at__lt=to_dt, ).values_list("id", "created_at", "resolved_at", "priority") ) deltas: list[float] = [] by_priority_lists: dict[str, list[float]] = {} case_ids: list[UUID] = [] for case_id, created_at, resolved_at, priority in rows: h = _hours_between(resolved_at, created_at) deltas.append(h) case_ids.append(case_id) by_priority_lists.setdefault(priority, []).append(h) deltas_sorted = sorted(deltas) by_priority: dict[str, dict] = {} for prio, values in by_priority_lists.items(): values.sort() by_priority[prio] = { "mean_hours": (sum(values) / len(values)) if values else None, "median_hours": _percentile(values, 50), "p90_hours": _percentile(values, 90), "count": len(values), } return { "mean_hours": (sum(deltas) / len(deltas)) if deltas else None, "median_hours": _percentile(deltas_sorted, 50), "p90_hours": _percentile(deltas_sorted, 90), "count": len(deltas), "by_priority": by_priority, "case_ids": [str(cid) for cid in case_ids], } def compute_backlog( qs: QuerySet, from_dt: Optional[datetime] = None, to_dt: Optional[datetime] = None, ) -> dict: """Open + urgent counts at end of each day in window. Open at day D = created_at <= end_of_D AND (resolved_at IS NULL OR resolved_at > end_of_D). Cheap with the new (org, resolved_at) index since we never scan more than once per case row to materialize the window. """ from_dt, to_dt = _coerce_window(from_dt, to_dt) rows = list( qs.filter( Q(created_at__lt=to_dt) & (Q(resolved_at__isnull=True) | Q(resolved_at__gte=from_dt)) ).values_list("created_at", "resolved_at", "priority") ) series: list[dict] = [] for d in _bucket_dates(from_dt, to_dt): end_of_day = _day_start(d + timedelta(days=1)) open_count = 0 urgent_count = 0 for created_at, resolved_at, priority in rows: if created_at >= end_of_day: continue if resolved_at is not None and resolved_at <= end_of_day: continue open_count += 1 if priority == "Urgent": urgent_count += 1 series.append( { "date": d.isoformat(), "open_count": open_count, "urgent_count": urgent_count, } ) return {"series": series} def compute_agents( qs: QuerySet, from_dt: Optional[datetime] = None, to_dt: Optional[datetime] = None, ) -> list[dict]: """Per-agent rollup. A case with N assignees counts once per agent. Returns list of `{profile_id, name, email, handled, avg_frt_hours, csat_avg, breach_rate}`. `csat_avg` is computed from `CsatSurvey` rows (responded, in-window) tied to cases this agent was assigned to. """ from_dt, to_dt = _coerce_window(from_dt, to_dt) in_window = qs.filter(created_at__gte=from_dt, created_at__lt=to_dt) rows = list( in_window.values_list( "id", "created_at", "first_response_at", "resolved_at", "sla_first_response_hours", "sla_resolution_hours", "assigned_to", "assigned_to__user__email", ) ) now = timezone.now() # Per-agent CSAT averages, scoped to the same case set + window. Pulled # in one query keyed by profile_id so we don't N+1 inside the loop. from cases.models import CsatSurvey case_ids = {row[0] for row in rows} csat_pairs = list( CsatSurvey.objects.filter( case_id__in=case_ids, rating__isnull=False ).values_list("case_id", "rating") ) rating_by_case = {cid: rating for cid, rating in csat_pairs} agg: dict[UUID, dict] = {} for ( case_id, created_at, first_response_at, resolved_at, sla_frt, sla_res, profile_id, email, ) in rows: if profile_id is None: continue bucket = agg.setdefault( profile_id, { "profile_id": str(profile_id), "name": email or "", "email": email or "", "handled": 0, "frt_values": [], "csat_values": [], "breach_count": 0, }, ) bucket["handled"] += 1 if case_id in rating_by_case: bucket["csat_values"].append(rating_by_case[case_id]) if first_response_at is not None: h = _hours_between(first_response_at, created_at) bucket["frt_values"].append(h) if h > (sla_frt or 0): bucket["breach_count"] += 1 elif _hours_between(now, created_at) > (sla_frt or 0): bucket["breach_count"] += 1 # Resolution breach: closed past SLA, OR open past SLA. if resolved_at is not None: if _hours_between(resolved_at, created_at) > (sla_res or 0): bucket["breach_count"] += 1 elif _hours_between(now, created_at) > (sla_res or 0): bucket["breach_count"] += 1 out: list[dict] = [] for bucket in agg.values(): frt_values = bucket.pop("frt_values") avg_frt = (sum(frt_values) / len(frt_values)) if frt_values else None csat_values = bucket.pop("csat_values") avg_csat = (sum(csat_values) / len(csat_values)) if csat_values else None breach_count = bucket.pop("breach_count") # breach_rate denominator is "events that could breach": one FRT-check # plus one resolution-check per case, so 2 * handled. denom = max(1, 2 * bucket["handled"]) bucket["avg_frt_hours"] = avg_frt bucket["csat_avg"] = avg_csat bucket["breach_rate"] = breach_count / denom out.append(bucket) out.sort(key=lambda r: (-r["handled"], r["email"])) return out def compute_sla( qs: QuerySet, from_dt: Optional[datetime] = None, to_dt: Optional[datetime] = None, ) -> dict: """Org-wide SLA breach rates. Window scopes by `Case.created_at`. A case breached next response when any of its waits for a reply after the first was answered late, or is still open past the target (see `compute_nrt`); the rate is over every case in the window, like the others. """ from_dt, to_dt = _coerce_window(from_dt, to_dt) rows = list( qs.filter(created_at__gte=from_dt, created_at__lt=to_dt).values_list( "id", "created_at", "first_response_at", "resolved_at", "sla_first_response_hours", "sla_resolution_hours", "priority", "status", "org_id", ) ) now = timezone.now() waits = _next_response_waits(row[0] for row in rows) nrt_targets = resolve_next_response_targets(rows[0][8]) if rows else {} total = len(rows) frt_breach = 0 res_breach = 0 nrt_breach = 0 breach_ids_frt: list[UUID] = [] breach_ids_res: list[UUID] = [] breach_ids_nrt: list[UUID] = [] by_priority: dict[str, dict] = {} for ( case_id, created_at, first_response_at, resolved_at, sla_frt, sla_res, priority, status, _org_id, ) in rows: prio_bucket = by_priority.setdefault( priority, {"total": 0, "frt_breach": 0, "resolution_breach": 0, "nrt_breach": 0}, ) prio_bucket["total"] += 1 is_frt_breach = False if first_response_at is not None: if _hours_between(first_response_at, created_at) > (sla_frt or 0): is_frt_breach = True elif _hours_between(now, created_at) > (sla_frt or 0): is_frt_breach = True if is_frt_breach: frt_breach += 1 breach_ids_frt.append(case_id) prio_bucket["frt_breach"] += 1 is_res_breach = False if resolved_at is not None: if _hours_between(resolved_at, created_at) > (sla_res or 0): is_res_breach = True elif _hours_between(now, created_at) > (sla_res or 0): is_res_breach = True if is_res_breach: res_breach += 1 breach_ids_res.append(case_id) prio_bucket["resolution_breach"] += 1 nrt_target = nrt_targets.get(priority, 4) is_open = status not in TERMINAL_STATUSES if any( _wait_breached(start, answered, nrt_target, is_open, now) for start, answered in waits.get(case_id, []) ): nrt_breach += 1 breach_ids_nrt.append(case_id) prio_bucket["nrt_breach"] += 1 def _rate(num: int, denom: int) -> Optional[float]: return (num / denom) if denom else None by_priority_out: dict[str, dict] = {} for prio, b in by_priority.items(): by_priority_out[prio] = { "total": b["total"], "frt_breach_rate": _rate(b["frt_breach"], b["total"]), "resolution_breach_rate": _rate(b["resolution_breach"], b["total"]), "nrt_breach_rate": _rate(b["nrt_breach"], b["total"]), } return { "total": total, "frt_breach_count": frt_breach, "resolution_breach_count": res_breach, "nrt_breach_count": nrt_breach, "frt_breach_rate": _rate(frt_breach, total), "resolution_breach_rate": _rate(res_breach, total), "nrt_breach_rate": _rate(nrt_breach, total), "by_priority": by_priority_out, "frt_breach_case_ids": [str(cid) for cid in breach_ids_frt], "resolution_breach_case_ids": [str(cid) for cid in breach_ids_res], "nrt_breach_case_ids": [str(cid) for cid in breach_ids_nrt], } # --------------------------------------------------------------------------- # Drilldown helper def case_ids_for_metric( metric: str, qs: QuerySet, from_dt: Optional[datetime], to_dt: Optional[datetime], bucket: Optional[str] = None, ) -> Iterable[UUID]: """Return the set of case ids that the named metric/bucket pulls in. Used by both the drilldown JSON endpoint and the CSV export. Bucket is metric-specific: e.g. "breach" for frt/sla, a date string for backlog. """ from_dt, to_dt = _coerce_window(from_dt, to_dt) if metric == "frt": in_window = qs.filter(created_at__gte=from_dt, created_at__lt=to_dt) if bucket == "breach": data = compute_frt(qs, from_dt, to_dt) return [UUID(s) for s in data["breach_case_ids"]] if bucket: raise ValueError(f"unknown frt bucket: {bucket}") return in_window.values_list("id", flat=True) if metric == "mttr": return qs.filter( resolved_at__isnull=False, resolved_at__gte=from_dt, resolved_at__lt=to_dt, ).values_list("id", flat=True) if metric == "sla": if bucket == "frt_breach": return [ UUID(s) for s in compute_sla(qs, from_dt, to_dt)["frt_breach_case_ids"] ] if bucket == "resolution_breach": return [ UUID(s) for s in compute_sla(qs, from_dt, to_dt)["resolution_breach_case_ids"] ] if bucket == "nrt_breach": return [ UUID(s) for s in compute_sla(qs, from_dt, to_dt)["nrt_breach_case_ids"] ] if bucket: raise ValueError(f"unknown sla bucket: {bucket}") return qs.filter(created_at__gte=from_dt, created_at__lt=to_dt).values_list( "id", flat=True ) if metric == "backlog": # Bucket is a YYYY-MM-DD date. Return cases open at end of that day. if not bucket: raise ValueError("backlog drilldown requires a bucket=YYYY-MM-DD") target = date.fromisoformat(bucket) end_of_day = _day_start(target + timedelta(days=1)) return qs.filter( Q(created_at__lt=end_of_day) & (Q(resolved_at__isnull=True) | Q(resolved_at__gt=end_of_day)) ).values_list("id", flat=True) if metric == "agents": # Bucket = profile_id (uuid string). if not bucket: raise ValueError("agents drilldown requires a bucket=") profile_id = UUID(bucket) return ( qs.filter( created_at__gte=from_dt, created_at__lt=to_dt, assigned_to=profile_id, ) .values_list("id", flat=True) .distinct() ) raise ValueError(f"unknown metric: {metric}") # --------------------------------------------------------------------------- # Service-desk overview (single-call dashboard for /v2/tickets/analytics) # # The per-metric endpoints above answer one question each; this assembles the # whole "service health" page in one admin-only call so the frontend does not # fan out five requests. Elapsed times are wall-clock, consistent with the # compute_* functions above (business hours inform SLA *deadlines*, not the # elapsed math here, the same caveat the rest of this module already lives # with). The caller admin-gates this, so `qs` is the full org queryset with no # per-user visibility narrowing. # Worst-priority first, so the card leads with what hurts most. Every priority # is emitted even with no cases, so the shape is stable window to window. _SERVICE_PRIORITY_ORDER = ("Urgent", "High", "Normal", "Low") # How long the service window is when the caller does not say. The view reads # this too, so "no `?days=`" and "the function's default" cannot drift apart. DEFAULT_SERVICE_DAYS = 14 def _business_hours_state(org_id) -> tuple[Optional[str], bool]: """(calendar_name, business_hours_applied) for the org's default calendar. Mirrors the SLA engine's own fallback (`business_hours.calendar`): no calendar, or a calendar with no open weekday window, means 24/7 wall-clock, reported here as not-applied with a null name. """ from business_hours.calendar import get_default_calendar cal = get_default_calendar(org_id) if cal is None: return None, False applied = any( o is not None and c is not None and c > o for (o, c) in cal.windows_by_weekday() ) return cal.name, applied def _accumulate_frt( bucket: dict, created_at, first_response_at, sla_hours, now ) -> None: """Fold one case's first-response outcome into a per-{priority,agent} bucket. A responded case contributes its FRT (minutes) to the median list and, if over its own SLA, a breach. An unresponded case only counts as a breach once it is already overdue; while still in-flight it counts toward neither met nor missed, so attainment describes only decided cases. """ sla = sla_hours or 0 if first_response_at is not None: hours = _hours_between(first_response_at, created_at) bucket["frt_minutes"].append(hours * 60.0) if hours > sla: bucket["breached"] += 1 else: bucket["met"] += 1 elif _hours_between(now, created_at) > sla: bucket["breached"] += 1 def _first_response_by_priority(created_rows, now, org_id) -> list[dict]: """Per-priority first-response attainment, worst-priority first. `target_minutes` is the org's promise for that priority today: its active `EscalationPolicy` target, or the built-in default where it has none (`cases.workflow.resolve_sla_targets`), in minutes. `met`/`missed` are scored against each case's own stored SLA, the target in force when it was opened. Priorities with no activity are still emitted with zero counts and a null median so the card never collapses. """ per: dict[str, dict] = { prio: {"met": 0, "breached": 0, "frt_minutes": []} for prio in _SERVICE_PRIORITY_ORDER } for _id, created_at, first_response_at, sla_hours, priority, _ctype in created_rows: bucket = per.get(priority) if bucket is None: # Unknown priority string: skip rather than invent a column. continue _accumulate_frt(bucket, created_at, first_response_at, sla_hours, now) out: list[dict] = [] for prio in _SERVICE_PRIORITY_ORDER: bucket = per[prio] median = _percentile(sorted(bucket["frt_minutes"]), 50) out.append( { "priority": prio, "target_minutes": resolve_sla_targets(org_id, prio)[0] * 60, "median_minutes": round(median) if median is not None else None, "met": bucket["met"], "missed": bucket["breached"], } ) return out def _agent_table(qs, from_dt, to_dt, now) -> list[dict]: """Per-agent table: currently-open, closed-this-week, median FRT, breaches. Each column has its own natural time basis: `open` is point-in-time (not in a terminal status), `closed_this_week` is the trailing 7 days, and the FRT/breach figures are over the [from, to) window. A case with N assignees counts once per agent (M2M fan-out). The unassigned bucket is computed with explicit `assigned_to__isnull=True` filters rather than a NULL row from the M2M join, so it does not depend on whether Django emits an inner or outer join for the multi-valued relation. """ week_from = now - timedelta(days=7) open_qs = qs.exclude(status__in=TERMINAL_STATUSES) closed_week_qs = qs.filter( resolved_at__isnull=False, resolved_at__gte=week_from, resolved_at__lt=to_dt ) window_qs = qs.filter(created_at__gte=from_dt, created_at__lt=to_dt) agents: dict = {} def bucket(pid): return agents.setdefault( pid, { "open": 0, "closed_this_week": 0, "frt_minutes": [], "breached": 0, "met": 0, }, ) for _cid, pid in open_qs.values_list("id", "assigned_to"): if pid is not None: bucket(pid)["open"] += 1 for _cid, pid in closed_week_qs.values_list("id", "assigned_to"): if pid is not None: bucket(pid)["closed_this_week"] += 1 for _cid, created_at, fra, sla_hours, pid in window_qs.values_list( "id", "created_at", "first_response_at", "sla_first_response_hours", "assigned_to", ): if pid is not None: _accumulate_frt(bucket(pid), created_at, fra, sla_hours, now) # Unassigned bucket: explicit isnull filters (see docstring). un = {"open": 0, "closed_this_week": 0, "frt_minutes": [], "breached": 0, "met": 0} un["open"] = open_qs.filter(assigned_to__isnull=True).count() un["closed_this_week"] = closed_week_qs.filter(assigned_to__isnull=True).count() for _cid, created_at, fra, sla_hours in window_qs.filter( assigned_to__isnull=True ).values_list("id", "created_at", "first_response_at", "sla_first_response_hours"): _accumulate_frt(un, created_at, fra, sla_hours, now) from common.models import Profile label_by_pid = { pid: (name or email or str(pid)) for pid, name, email in Profile.objects.filter( id__in=list(agents.keys()) ).values_list("id", "user__name", "user__email") } def _row(pid, name, b): median = _percentile(sorted(b["frt_minutes"]), 50) return { "id": None if pid is None else str(pid), "name": name, "open": b["open"], "closed_this_week": b["closed_this_week"], "median_first_response_minutes": ( round(median) if median is not None else None ), "breached": b["breached"], } rows = [_row(pid, label_by_pid.get(pid, str(pid)), b) for pid, b in agents.items()] rows.sort(key=lambda r: (-r["open"], r["name"].lower())) # Only show the unassigned line when it carries signal, so it does not # dangle as an all-zero row on a fully-triaged queue. if un["open"] or un["closed_this_week"] or un["frt_minutes"] or un["breached"]: rows.append(_row(None, "Unassigned", un)) return rows def compute_service_overview( qs: QuerySet, org_id, days: int = DEFAULT_SERVICE_DAYS ) -> dict: """Assemble the full /v2/tickets/analytics payload in one call. Returns `{totals, volume, first_response, by_type, by_agent}`. The window is the last `days` whole days (default 14, clamped to [1, 90]), anchored to the org's day boundaries so `volume` has exactly `days` buckets and the last one is the org's today. `qs` must be the org-scoped Case queryset; this function does no visibility narrowing (the endpoint is admin-only). """ days = max(1, min(days, 90)) now = timezone.now() to_dt = _day_start(timezone.localdate(now) + timedelta(days=1)) from_dt = to_dt - timedelta(days=days) created_rows = list( qs.filter(created_at__gte=from_dt, created_at__lt=to_dt).values_list( "id", "created_at", "first_response_at", "sla_first_response_hours", "priority", "case_type", ) ) resolved_rows = list( qs.filter( resolved_at__isnull=False, resolved_at__gte=from_dt, resolved_at__lt=to_dt, ).values_list("id", "created_at", "resolved_at") ) # ---- totals ---- mttr_hours = sorted( _hours_between(resolved_at, created_at) for _id, created_at, resolved_at in resolved_rows ) median_res = _percentile(mttr_hours, 50) calendar_name, business_hours_applied = _business_hours_state(org_id) totals = { "opened": len(created_rows), "closed": len(resolved_rows), "open_now": qs.exclude(status__in=TERMINAL_STATUSES).count(), "median_resolution_hours": round(median_res) if median_res is not None else 0, "window_days": days, "business_hours_applied": business_hours_applied, "calendar_name": calendar_name, } # ---- volume (opened/closed per day) ---- opened_by_day: dict[date, int] = {} for _id, created_at, _fra, _sla, _prio, _ctype in created_rows: d = timezone.localdate(created_at) opened_by_day[d] = opened_by_day.get(d, 0) + 1 closed_by_day: dict[date, int] = {} for _id, _created, resolved_at in resolved_rows: d = timezone.localdate(resolved_at) closed_by_day[d] = closed_by_day.get(d, 0) + 1 volume = [ { "date": d.isoformat(), "opened": opened_by_day.get(d, 0), "closed": closed_by_day.get(d, 0), } for d in _bucket_dates(from_dt, to_dt) ] # ---- case-type mix (None → "Uncategorized") ---- type_counts: dict[str, int] = {} for _id, _created, _fra, _sla, _prio, case_type in created_rows: label = case_type or "Uncategorized" type_counts[label] = type_counts.get(label, 0) + 1 by_type = [ {"case_type": label, "count": n} for label, n in sorted(type_counts.items(), key=lambda kv: (-kv[1], kv[0])) ] return { "totals": totals, "volume": volume, "first_response": _first_response_by_priority(created_rows, now, org_id), "by_type": by_type, "by_agent": _agent_table(qs, from_dt, to_dt, now), }