"""Per-department finance rollups. Departments have no direct financial records of their own. Money reaches a department through one of four FK paths, all of which resolve without any string matching: ProjectFinance -> project -> project.departments ProjectFund -> project -> project.departments JournalEntry -> source_project_fund -> project -> project.departments Requisition -> requesting_department (direct) `JournalEntry.project_code` is deliberately NOT used: it holds an unrelated free-text taxonomy (e.g. "PRJ-HEALTH", "OPS-GENERAL") that matches no project number or name, so joining on it would attribute nothing or attribute wrongly. Attribution rule: a project co-owned by N departments contributes its FULL amount to each of them. Per-department figures are therefore each honest on their own, but summing across departments double-counts co-owned projects. The `co_owned_project_count` field reports how much of that is in play so the UI can say so. """ from collections import defaultdict from decimal import Decimal from django.db.models import Count, Sum from django.db.models.functions import TruncMonth, TruncYear from rest_framework import viewsets from rest_framework.decorators import action from rest_framework.response import Response from drf_spectacular.utils import extend_schema, OpenApiParameter from apps.core.tenancy import get_active_organization from .models import Department def _dec(value) -> Decimal: return value if isinstance(value, Decimal) else Decimal(str(value or 0)) def _project_department_map(org): """{project_id: [department_id, ...]} for every project in the org. One query over the M2M through-table; no per-project lookups. """ mapping = defaultdict(list) rows = ( Department.objects.filter(organization=org) .values_list("id", "projects__id") ) for dept_id, project_id in rows: if project_id is not None: mapping[project_id].append(dept_id) return mapping def _period_key(value, granularity: str) -> str: """Bucket label: 'YYYY' yearly, 'YYYY-MM' monthly.""" if value is None: return "unknown" return value.strftime("%Y") if granularity == "yearly" else value.strftime("%Y-%m") def _parse_year(raw): try: return int(raw) except (TypeError, ValueError): return None class DepartmentFinanceViewSet(viewsets.ViewSet): """Read-only finance rollups grouped by department.""" rbac_domain = "finance" # ---- shared row builders ------------------------------------------------- def _finance_rows(self, org, year): from apps.projects.models import ProjectFinance qs = ProjectFinance.objects.filter(project__organization=org) if year: qs = qs.filter(date__year=year) return qs.values_list( "project_id", "type", "amount_idr", "amount", "currency", "date", ) def _fund_rows(self, org, year): from apps.projects.models import ProjectFund qs = ProjectFund.objects.filter(project__organization=org) if year: qs = qs.filter(agreement_date__year=year) return qs.values_list( "project_id", "amount_idr", "amount", "currency", "agreement_date", "status", ) def _journal_rows(self, org, year): """Journal entries reachable via a project fund. Amount comes from the entry's debit lines, which is the entry's own total.""" from apps.accounting.models import JournalEntry qs = JournalEntry.objects.filter( organization=org, source_project_fund__isnull=False, ) if year: qs = qs.filter(date__year=year) return qs.values_list( "source_project_fund__project_id", "date", ).annotate(total=Sum("lines__debit_idr")) def _requisition_rows(self, org, year): from apps.procurements.models import Requisition qs = Requisition.objects.filter( organization=org, requesting_department__isnull=False, ) if year: qs = qs.filter(created_at__year=year) return qs.values_list( "requesting_department_id", "total_amount", "created_at", "status", ) # ---- endpoints ----------------------------------------------------------- @extend_schema( parameters=[ OpenApiParameter("year", int, description="Restrict to a single calendar year."), ], responses={200: {"type": "object"}}, ) def list(self, request): """One row per department: income, spend, net, and source counts.""" org = get_active_organization(request) year = _parse_year(request.query_params.get("year")) proj_depts = _project_department_map(org) blank = { "income": Decimal("0"), "expense": Decimal("0"), "fund_pledged": Decimal("0"), "journal_total": Decimal("0"), "requisition_total": Decimal("0"), "finance_entry_count": 0, "fund_count": 0, "journal_count": 0, "requisition_count": 0, "project_ids": set(), } buckets = {d.id: {**blank, "project_ids": set()} for d in Department.objects.filter(organization=org)} co_owned = set() def fan_out(project_id): """Departments owning a project, tracking co-ownership as we go.""" depts = proj_depts.get(project_id, []) if len(depts) > 1: co_owned.add(project_id) return depts for project_id, entry_type, amount_idr, amount, currency, _d in self._finance_rows(org, year): value = _dec(amount_idr if amount_idr is not None else amount) for dept_id in fan_out(project_id): b = buckets.get(dept_id) if b is None: continue if entry_type in ("income", "expense"): b[entry_type] += value b["finance_entry_count"] += 1 b["project_ids"].add(project_id) for project_id, amount_idr, amount, currency, _d, _s in self._fund_rows(org, year): value = _dec(amount_idr if amount_idr is not None else amount) for dept_id in fan_out(project_id): b = buckets.get(dept_id) if b is None: continue b["fund_pledged"] += value b["fund_count"] += 1 b["project_ids"].add(project_id) for project_id, _d, total in self._journal_rows(org, year): value = _dec(total) for dept_id in fan_out(project_id): b = buckets.get(dept_id) if b is None: continue b["journal_total"] += value b["journal_count"] += 1 for dept_id, total, _c, _s in self._requisition_rows(org, year): b = buckets.get(dept_id) if b is None: continue b["requisition_total"] += _dec(total) b["requisition_count"] += 1 departments = ( Department.objects.filter(organization=org) .values("id", "name", "code", "is_research") .order_by("name") ) rows = [] for d in departments: b = buckets[d["id"]] rows.append({ "id": str(d["id"]), "name": d["name"], "code": d["code"], "is_research": d["is_research"], "income": str(b["income"]), "expense": str(b["expense"]), "net": str(b["income"] - b["expense"]), "fund_pledged": str(b["fund_pledged"]), "journal_total": str(b["journal_total"]), "requisition_total": str(b["requisition_total"]), "finance_entry_count": b["finance_entry_count"], "fund_count": b["fund_count"], "journal_count": b["journal_count"], "requisition_count": b["requisition_count"], "project_count": len(b["project_ids"]), }) return Response({ "currency": "IDR", "year": year, "departments": rows, # Co-owned projects are counted in full under each of their # departments, so per-department figures don't sum to the org total. "co_owned_project_count": len(co_owned), }) @extend_schema( parameters=[ OpenApiParameter("year", int, description="Restrict to a single calendar year."), OpenApiParameter("granularity", str, enum=["monthly", "yearly"]), ], responses={200: {"type": "object"}}, ) @action(detail=True, methods=["get"], url_path="trend") def trend(self, request, pk=None): """Income/expense per period for one department.""" org = get_active_organization(request) year = _parse_year(request.query_params.get("year")) granularity = request.query_params.get("granularity") or "monthly" if granularity not in ("monthly", "yearly"): granularity = "monthly" dept = Department.objects.filter(organization=org, pk=pk).first() if dept is None: return Response({"detail": "Not found."}, status=404) project_ids = list(dept.projects.values_list("id", flat=True)) from apps.projects.models import ProjectFinance trunc = TruncYear if granularity == "yearly" else TruncMonth qs = ProjectFinance.objects.filter(project_id__in=project_ids, date__isnull=False) if year: qs = qs.filter(date__year=year) grouped = ( qs.annotate(period=trunc("date")) .values("period", "type") .annotate(total=Sum("amount_idr"), raw=Sum("amount"), n=Count("id")) .order_by("period") ) periods = {} for row in grouped: key = _period_key(row["period"], granularity) slot = periods.setdefault(key, {"period": key, "income": Decimal("0"), "expense": Decimal("0"), "entry_count": 0}) value = _dec(row["total"] if row["total"] is not None else row["raw"]) if row["type"] in ("income", "expense"): slot[row["type"]] += value slot["entry_count"] += row["n"] series = [ { "period": k, "income": str(v["income"]), "expense": str(v["expense"]), "net": str(v["income"] - v["expense"]), "entry_count": v["entry_count"], } for k, v in sorted(periods.items()) ] return Response({ "department": {"id": str(dept.id), "name": dept.name, "code": dept.code}, "currency": "IDR", "granularity": granularity, "year": year, "series": series, }) @extend_schema( parameters=[ OpenApiParameter("year", int, description="Restrict to a single calendar year."), ], responses={200: {"type": "object"}}, ) @action(detail=True, methods=["get"], url_path="transactions") def transactions(self, request, pk=None): """Every transaction attributable to one department, newest first. Sources are merged in Python because they're four unrelated tables with no shared parent — the same approach the project activity feed uses. """ org = get_active_organization(request) year = _parse_year(request.query_params.get("year")) dept = Department.objects.filter(organization=org, pk=pk).first() if dept is None: return Response({"detail": "Not found."}, status=404) project_rows = dict(dept.projects.values_list("id", "name")) project_ids = list(project_rows) items = [] from apps.projects.models import ProjectFinance, ProjectFund finance_qs = ProjectFinance.objects.filter(project_id__in=project_ids) if year: finance_qs = finance_qs.filter(date__year=year) for row in finance_qs.values( "id", "project_id", "description", "amount", "amount_idr", "currency", "type", "date", "approval_status", ): items.append({ "id": str(row["id"]), "source": "project_finance", "date": row["date"].isoformat() if row["date"] else None, "description": row["description"], "direction": row["type"], "amount": str(row["amount"]), "currency": row["currency"], "amount_idr": str(row["amount_idr"]) if row["amount_idr"] is not None else None, "status": row["approval_status"], "project_id": str(row["project_id"]), "project_name": project_rows.get(row["project_id"]), }) fund_qs = ProjectFund.objects.filter(project_id__in=project_ids) if year: fund_qs = fund_qs.filter(agreement_date__year=year) for row in fund_qs.values( "id", "project_id", "source", "amount", "amount_idr", "currency", "agreement_date", "status", "grant_number", ): items.append({ "id": str(row["id"]), "source": "project_fund", "date": row["agreement_date"].isoformat() if row["agreement_date"] else None, "description": row["source"] or row["grant_number"] or "Fund", "direction": "income", "amount": str(row["amount"]), "currency": row["currency"], "amount_idr": str(row["amount_idr"]) if row["amount_idr"] is not None else None, "status": row["status"], "project_id": str(row["project_id"]), "project_name": project_rows.get(row["project_id"]), }) from apps.accounting.models import JournalEntry journal_qs = JournalEntry.objects.filter( organization=org, source_project_fund__project_id__in=project_ids, ) if year: journal_qs = journal_qs.filter(date__year=year) for row in journal_qs.values( "id", "journal_no", "description", "date", "status", "source_project_fund__project_id", ).annotate(total=Sum("lines__debit_idr")): pid = row["source_project_fund__project_id"] items.append({ "id": str(row["id"]), "source": "journal_entry", "date": row["date"].isoformat() if row["date"] else None, "description": row["description"] or row["journal_no"], "direction": "expense", "amount": str(_dec(row["total"])), "currency": "IDR", "amount_idr": str(_dec(row["total"])), "status": row["status"], "project_id": str(pid) if pid else None, "project_name": project_rows.get(pid), }) from apps.procurements.models import Requisition req_qs = Requisition.objects.filter(organization=org, requesting_department_id=dept.id) if year: req_qs = req_qs.filter(created_at__year=year) for row in req_qs.values( "id", "title", "total_amount", "created_at", "status", "project_id", ): items.append({ "id": str(row["id"]), "source": "requisition", "date": row["created_at"].date().isoformat() if row["created_at"] else None, "description": row["title"], "direction": "expense", "amount": str(_dec(row["total_amount"])), "currency": "IDR", "amount_idr": str(_dec(row["total_amount"])), "status": row["status"], "project_id": str(row["project_id"]) if row["project_id"] else None, "project_name": project_rows.get(row["project_id"]), }) # Undated rows sort last rather than crashing the comparison. items.sort(key=lambda i: (i["date"] or "", i["id"]), reverse=True) return Response({ "department": {"id": str(dept.id), "name": dept.name, "code": dept.code}, "year": year, "count": len(items), "results": items, }) @extend_schema( parameters=[ OpenApiParameter("year", int, description="Restrict to a single calendar year."), ], responses={200: {"type": "object"}}, ) @action(detail=True, methods=["get"], url_path="projects") def projects(self, request, pk=None): """Per-project income/expense breakdown within one department.""" org = get_active_organization(request) year = _parse_year(request.query_params.get("year")) dept = Department.objects.filter(organization=org, pk=pk).first() if dept is None: return Response({"detail": "Not found."}, status=404) from apps.projects.models import ProjectFinance project_rows = list( dept.projects.values("id", "name", "project_number", "status") ) project_ids = [p["id"] for p in project_rows] qs = ProjectFinance.objects.filter(project_id__in=project_ids) if year: qs = qs.filter(date__year=year) agg = defaultdict(lambda: {"income": Decimal("0"), "expense": Decimal("0"), "count": 0}) for row in qs.values("project_id", "type").annotate( total=Sum("amount_idr"), raw=Sum("amount"), n=Count("id"), ): slot = agg[row["project_id"]] value = _dec(row["total"] if row["total"] is not None else row["raw"]) if row["type"] in ("income", "expense"): slot[row["type"]] += value slot["count"] += row["n"] results = [] for p in project_rows: slot = agg[p["id"]] results.append({ "id": str(p["id"]), "name": p["name"], "project_number": p["project_number"], "status": p["status"], "income": str(slot["income"]), "expense": str(slot["expense"]), "net": str(slot["income"] - slot["expense"]), "entry_count": slot["count"], }) results.sort(key=lambda r: Decimal(r["expense"]), reverse=True) return Response({ "department": {"id": str(dept.id), "name": dept.name, "code": dept.code}, "currency": "IDR", "year": year, "results": results, })