"""summary_by_project (rpcs §2.5) + the month-picker math (summary.md B2–B4, lib/filters.ts heritage) — the / (Summary) page's data. FK grouping (rebuild delta #4): one row per project entity; unassigned rows group by their raw_* triple with NULL labels — the `(no operator)` placeholders are presentation-layer now (T-SUM-008). Semantics pinned by tests: - base scan excludes rows with NULL duration_seconds (B14); - three windows via CASE-inside-SUM on start_at, half-open (B16); - pivot filters are ILIKE contains on the display exprs (B17); - adjusted billout per window = raw * (1+pct/100) + amount, the fixed amount added IN FULL to every window (ADR #036 parity, B19); - totals: seconds/cost/billout summed in SQL; totals-of-ADJUSTED are the sum of the per-row rounded values (ADR #004: the footer must equal the sum of the displayed column to the cent — the one documented Python-side aggregation, ~62 group scalars). """ from __future__ import annotations import re import uuid from dataclasses import dataclass from datetime import date, datetime from decimal import Decimal from sqlalchemy import case, distinct, func from sqlalchemy.orm import Session from app.models import TimeEntry from app.services.authz.scope import scope from app.services.reporting.filters import EntryFilters, EntryJoins, apply_filters from app.services.reporting.money import MixedCurrencyError, Money, quantize2 _MONTH_RE = re.compile(r"^(\d{4})-(\d{1,2})$") @dataclass(frozen=True) class MonthWindow: year: int month: int @property def start(self) -> datetime: return datetime(self.year, self.month, 1) @property def end(self) -> datetime: y, m = (self.year + 1, 1) if self.month == 12 else (self.year, self.month + 1) return datetime(y, m, 1) def shift(self, delta: int) -> MonthWindow: index = self.year * 12 + (self.month - 1) + delta return MonthWindow(index // 12, index % 12 + 1) def as_param(self) -> str: return f"{self.year:04d}-{self.month:02d}" def parse_month(raw: str | None, *, today: date | None = None) -> MonthWindow: """B2 parity: `2026-7` and `2026-07` both parse; absent, malformed, or month∉1..12 falls back SILENTLY to the current month.""" today = today or date.today() if raw: m = _MONTH_RE.match(raw.strip()) if m: year, month = int(m.group(1)), int(m.group(2)) if 1 <= month <= 12: return MonthWindow(year, month) return MonthWindow(today.year, today.month) def _window_sum(column, start: datetime, end: datetime): return func.sum( case(((TimeEntry.start_at >= start) & (TimeEntry.start_at < end), column)) ) def _adjusted(billout: Decimal, pct: Decimal | None, amount: Decimal | None) -> Decimal: raw = billout * (1 + (pct or Decimal(0)) / 100) + (amount or Decimal(0)) return quantize2(raw) def summary_by_project( session: Session, actor, this: MonthWindow, *, operator: str | None = None, client: str | None = None, user: str | None = None, source_email: str | None = None, imported_by: uuid.UUID | None = None, zero_currency: str = "CAD", ) -> dict: prev = this.shift(-1) joins = EntryJoins() p = joins.project stmt = ( joins.select_from( p.id.label("project_id"), joins.operator_expr.label("operator"), joins.client_expr.label("client"), joins.project_expr.label("project"), p.billout_adjustment_pct.label("adj_pct"), p.billout_adjustment_amount.label("adj_amount"), _window_sum(TimeEntry.duration_seconds, this.start, this.end).label("this_secs"), _window_sum(TimeEntry.duration_seconds, prev.start, prev.end).label("prev_secs"), func.sum(TimeEntry.duration_seconds).label("all_secs"), _window_sum(TimeEntry.cost_amount, this.start, this.end).label("this_cost"), _window_sum(TimeEntry.cost_amount, prev.start, prev.end).label("prev_cost"), func.sum(TimeEntry.cost_amount).label("all_cost"), _window_sum(TimeEntry.billout_amount, this.start, this.end).label("this_billout"), _window_sum(TimeEntry.billout_amount, prev.start, prev.end).label("prev_billout"), func.sum(TimeEntry.billout_amount).label("all_billout"), func.count(distinct(TimeEntry.cost_currency)).label("cost_ccys"), func.min(TimeEntry.cost_currency).label("cost_ccy"), func.count(distinct(TimeEntry.billout_currency)).label("billout_ccys"), func.min(TimeEntry.billout_currency).label("billout_ccy"), ) .where(TimeEntry.duration_seconds.is_not(None)) # B14 .group_by( p.id, joins.operator_expr, joins.client_expr, joins.project_expr, p.billout_adjustment_pct, p.billout_adjustment_amount, ) ) stmt = scope(actor, "entries:read", stmt) pivots = EntryFilters( operator=operator, client=client, user=user, source_email=source_email, imported_by=imported_by, ) stmt = apply_filters(stmt, joins, pivots) can_see_billout = actor.allowed("can_see_income") rows: list[dict] = [] totals_adj = {"this": Decimal(0), "prev": Decimal(0), "all": Decimal(0)} totals_raw = { "this_seconds": 0, "prev_seconds": 0, "all_seconds": 0, "this_cost": Decimal(0), "prev_cost": Decimal(0), "all_cost": Decimal(0), "this_billout": Decimal(0), "prev_billout": Decimal(0), "all_billout": Decimal(0), } currency = zero_currency for r in session.execute(stmt).mappings(): if r["cost_ccys"] > 1 or r["billout_ccys"] > 1: raise MixedCurrencyError( "summary_by_project group", max(r["cost_ccys"], r["billout_ccys"]) ) currency = r["billout_ccy"] or r["cost_ccy"] or currency def dec(value) -> Decimal: return Decimal(value) if value is not None else Decimal(0) row: dict = { "project_id": str(r["project_id"]) if r["project_id"] else None, "operator": r["operator"], "client": r["client"], "project": r["project"], "this_seconds": int(r["this_secs"] or 0), "prev_seconds": int(r["prev_secs"] or 0), "all_seconds": int(r["all_secs"] or 0), "this_cost": _money(dec(r["this_cost"]), r["cost_ccy"] or currency), "prev_cost": _money(dec(r["prev_cost"]), r["cost_ccy"] or currency), "all_cost": _money(dec(r["all_cost"]), r["cost_ccy"] or currency), } totals_raw["this_seconds"] += row["this_seconds"] totals_raw["prev_seconds"] += row["prev_seconds"] totals_raw["all_seconds"] += row["all_seconds"] totals_raw["this_cost"] += quantize2(dec(r["this_cost"])) totals_raw["prev_cost"] += quantize2(dec(r["prev_cost"])) totals_raw["all_cost"] += quantize2(dec(r["all_cost"])) if can_see_billout: billout_ccy = r["billout_ccy"] or currency pct = Decimal(r["adj_pct"]) if r["adj_pct"] is not None else None amount = Decimal(r["adj_amount"]) if r["adj_amount"] is not None else None windows = ( ("this", "this_billout"), ("prev", "prev_billout"), ("all", "all_billout"), ) for win, key in windows: raw_sum = dec(r[key]) adjusted = _adjusted(raw_sum, pct, amount) row[key] = _money(raw_sum, billout_ccy) row[f"{win}_billout_adjusted"] = _money(adjusted, billout_ccy) totals_adj[win] += adjusted totals_raw[key] += quantize2(raw_sum) rows.append(row) totals = { "this_seconds": totals_raw["this_seconds"], "prev_seconds": totals_raw["prev_seconds"], "all_seconds": totals_raw["all_seconds"], "this_cost": _money(totals_raw["this_cost"], currency), "prev_cost": _money(totals_raw["prev_cost"], currency), "all_cost": _money(totals_raw["all_cost"], currency), } if can_see_billout: for win in ("this", "prev", "all"): totals[f"{win}_billout"] = _money(totals_raw[f"{win}_billout"], currency) totals[f"{win}_billout_adjusted"] = _money(totals_adj[win], currency) return { "month": { "this": {"year": this.year, "month": this.month}, "prev": {"year": prev.year, "month": prev.month}, }, "rows": rows, "totals": totals, } def _money(amount: Decimal, currency: str) -> dict: return Money(amount=str(quantize2(amount)), currency=currency).as_dict()