Files
bin/finance/app/routers/gui.py
T

541 lines
22 KiB
Python
Raw Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
import logging
import re
from datetime import date, timedelta
from decimal import Decimal
from typing import Callable
from urllib.parse import urlencode
from fastapi import APIRouter, Depends, HTTPException, Request
from fastapi.templating import Jinja2Templates
from sqlalchemy import func, select
from sqlalchemy.orm import Session
from app.auth import COOKIE, session_valid
from app.config import get_settings
from app.db import get_session
from app.engine.loans import add_months
from app.engine.recurrence import occurrences
from app.engine.breakdown import FREI, BreakdownRow, Filters, category_rows, share
from app.engine.pie import UEBRIGE, pie_slices
from app.formats import de_label, eur, pct
from app.models.tables import (Account, Category, PlannedItem,
ProjectionResult, RecurringItem,
Scenario, ScenarioLoan, ScenarioModifier,
ScenarioPlannedItem, ScenarioRecurringItem,
Transaction)
from app.routers.imports import list_imports as _list_imports
from app.routers.imports import preview as _preview_import
from app.routers.planning import list_loans as _list_loans
from app.routers.planning import list_planned as _list_planned
from app.routers.planning import list_recurring as _list_recurring
from app.routers.planning import recurring_suggestions as _recurring_suggestions
from app.routers.scenarios import list_scenarios as _list_scenarios
from app.routers.transactions import count_transactions, list_transactions, normalize_sort
from app.services.balances import account_balance, total_balance
from app.services.breakdown import PLAN_NAME, compute_breakdown
from app.services.double_count import all_conflicts
from app.services.planning_chart import refresh_planning_chart
from app.services.projection_service import refresh_stale_projections
from app.version import get_version
PAGE_SIZE = 50
templates = Jinja2Templates(directory="app/templates")
templates.env.globals["app_version"] = get_version()
def grafana_public_base(request: Request) -> str:
"""Öffentliche Grafana-Basis-URL für Browser-Links/iframes. Ist
FB_GRAFANA_PUBLIC_URL gesetzt (Reverse-Proxy-Betrieb, z.B.
'https://fb.example.de/grafana/'), wird sie ohne abschließenden
Slash zurückgegeben; sonst der lokale Fallback http://<host>:8097
(Direktbetrieb ohne Proxy). Templates hängen bei Bedarf '/d/...' an."""
public = get_settings().grafana_public_url
if public:
return public.rstrip("/")
host = request.url.hostname or "127.0.0.1"
return f"http://{host}:8097"
templates.env.globals["grafana_public_base"] = grafana_public_base
templates.env.filters["eur"] = eur
templates.env.filters["de_label"] = de_label
templates.env.filters["pct"] = pct
router = APIRouter()
log = logging.getLogger(__name__)
def _safe_refresh(session: Session, refresh: Callable[[Session, date], object],
today: date) -> None:
"""Frischt abgeleitete Daten (Projektionen, Planungs-Diagramm) vor dem
Rendern auf. Ein Fehler darf die Seite nie brechen: rollback, Warnung ins
Log, die Seite rendert mit dem vorhandenen Stand."""
try:
refresh(session, today)
except Exception:
session.rollback()
log.warning("Auffrischen fehlgeschlagen: %s",
getattr(refresh, "__name__", refresh), exc_info=True)
def gui_session(request: Request):
if not session_valid(request.cookies.get(COOKIE)):
raise _redirect()
def _redirect():
exc = HTTPException(302)
exc.headers = {"Location": "/login"}
return exc
def _upcoming_items(session: Session, today: date, days: int = 30, limit: int = 10) -> list[dict]:
"""Nächste Posten aus RecurringItems (Termine) und PlannedItems der nächsten `days` Tage."""
end = today + timedelta(days=days)
items: list[dict] = []
for r in session.execute(select(RecurringItem)).scalars():
for d in occurrences(r.rhythm, r.due_day, today, end, r.start_date, r.end_date):
items.append({"date": d, "name": r.name, "amount": Decimal(r.amount)})
for p in session.execute(
select(PlannedItem).where(PlannedItem.due >= today, PlannedItem.due <= end)
).scalars():
items.append({"date": p.due, "name": p.name, "amount": Decimal(p.amount)})
items.sort(key=lambda i: i["date"])
return items[:limit]
def _latest_projection(session: Session) -> ProjectionResult | None:
return session.execute(
select(ProjectionResult).order_by(ProjectionResult.computed_at.desc())
).scalars().first()
def _opt_int(value: str | None) -> int | None:
"""Wandelt einen GUI-Query-Parameter tolerant in int|None um. HTML-Formulare
senden bei nicht ausgefüllten Feldern leere Strings statt gar keinen
Parameter - das darf nie zu einem 422 fuehren, sondern degradiert zu None."""
if value is None or not value.strip():
return None
try:
return int(value)
except ValueError:
return None
def _opt_date(value: str | None) -> date | None:
"""Wie _opt_int, nur fuer date-Felder (ISO-Format aus <input type=date>)."""
if value is None or not value.strip():
return None
try:
return date.fromisoformat(value)
except ValueError:
return None
def _month_starts_desc(session: Session, today: date) -> list[date]:
"""Liefert die Monatsersten von "heute" rueckwaerts bis zum Monat der
fruehesten bestaetigten Buchung, absteigend sortiert (heute zuerst).
Ohne bestaetigte Buchungen: leere Liste."""
earliest = session.execute(
select(func.min(Transaction.booking_date)).where(Transaction.status == "confirmed")
).scalar_one_or_none()
if earliest is None:
return []
start = date(earliest.year, earliest.month, 1)
current = date(today.year, today.month, 1)
months = []
m = current
while m >= start:
months.append(m)
m = add_months(m, -1)
return months
@router.get("/", dependencies=[Depends(gui_session)])
def index(request: Request, session: Session = Depends(get_session)):
_safe_refresh(session, refresh_stale_projections, date.today())
_safe_refresh(session, refresh_planning_chart, date.today())
accounts = session.execute(select(Account)).scalars().all()
balances = [(a, account_balance(session, a)) for a in accounts]
return templates.TemplateResponse(request, "index.html", {
"balances": balances,
"total": total_balance(session),
"upcoming": _upcoming_items(session, date.today()),
"projection": _latest_projection(session),
})
@router.get("/buchungen", dependencies=[Depends(gui_session)])
def buchungen(
request: Request,
account_id: str | None = None,
date_from: str | None = None,
date_to: str | None = None,
category_id: str | None = None,
q: str | None = None,
status: str = "confirmed",
page: int = 1,
sort: str = "datum",
dir: str = "asc",
session: Session = Depends(get_session),
):
# HTML-Filterformular sendet leere Strings fuer nicht ausgefuellte Felder -
# daher hier bewusst str|None statt int|None/date|None und tolerante
# Konvertierung, statt FastAPI vor dem Handler mit 422 abbrechen zu lassen.
# Die API-Routen (/api/transactions) bleiben strikt typisiert.
account_id = _opt_int(account_id)
date_from = _opt_date(date_from)
date_to = _opt_date(date_to)
category_id = _opt_int(category_id)
page = max(1, page)
sort, dir = normalize_sort(sort, dir)
total = count_transactions(
account_id=account_id,
date_from=date_from,
date_to=date_to,
category_id=category_id,
q=q,
status=status,
session=session,
)
total_pages = max(1, -(-total // PAGE_SIZE)) # ceil division
page = min(page, total_pages)
txs = list_transactions(
account_id=account_id,
date_from=date_from,
date_to=date_to,
category_id=category_id,
q=q,
status=status,
sort=sort,
dir=dir,
limit=PAGE_SIZE,
offset=(page - 1) * PAGE_SIZE,
session=session,
)
accounts = session.execute(select(Account)).scalars().all()
return templates.TemplateResponse(request, "transactions.html", {
"transactions": txs,
"accounts": accounts,
"account_names": {a.id: a.name for a in accounts},
"categories": session.execute(select(Category)).scalars().all(),
"filters": {
"account_id": account_id,
"date_from": date_from,
"date_to": date_to,
"category_id": category_id,
"q": q or "",
"status": status,
},
"page": page,
"total_pages": total_pages,
"sort": sort,
"dir": dir,
})
@router.get("/salden", dependencies=[Depends(gui_session)])
def salden_page(
request: Request,
stichtag: str | None = None,
session: Session = Depends(get_session),
):
# Lenient wie die Filter auf /buchungen: ein leerer oder kaputter
# stichtag-Parameter fuehrt nie zu 422, sondern faellt auf "heute" zurueck.
today = date.today()
parsed_stichtag = _opt_date(stichtag) or today
accounts = session.execute(select(Account)).scalars().all()
balances = [(a, account_balance(session, a, at=parsed_stichtag)) for a in accounts]
total = sum((b for _, b in balances), Decimal("0"))
month_rows = []
for month in _month_starts_desc(session, today):
# Saldo am Monatsanfang = Stand am ENDE DES VORTAGS (letzter Tag des
# Vormonats), NICHT account_balance(at=month) - sonst wuerden
# Buchungen AM 1. bereits in den "Anfangs"-Saldo einfliessen und den
# Ausweis verzerren. Siehe Fussnote im Template ("Stand: Ende des
# Vortags").
vortag = month - timedelta(days=1)
values = [(a, account_balance(session, a, at=vortag)) for a in accounts]
month_rows.append({
"month": month,
"values": values,
"total": sum((v for _, v in values), Decimal("0")),
})
accounts_without_anchor = [
a for a in accounts if a.anchor_date is None or a.anchor_balance is None
]
return templates.TemplateResponse(request, "salden.html", {
"stichtag": parsed_stichtag,
"balances": balances,
"total": total,
"accounts": accounts,
"months": month_rows,
"accounts_without_anchor": accounts_without_anchor,
})
@router.get("/import", dependencies=[Depends(gui_session)])
def import_page(request: Request, session: Session = Depends(get_session)):
return templates.TemplateResponse(request, "import.html", {
"statements": _list_imports(session=session),
})
@router.get("/import/list", dependencies=[Depends(gui_session)])
def import_list_fragment(request: Request, session: Session = Depends(get_session)):
return templates.TemplateResponse(request, "_import_list.html", {
"statements": _list_imports(session=session),
})
@router.get("/import/{statement_id}/preview-fragment", dependencies=[Depends(gui_session)])
def import_preview_fragment(statement_id: int, request: Request,
session: Session = Depends(get_session)):
result = _preview_import(statement_id, session=session)
return templates.TemplateResponse(request, "_preview_table.html", {
"statement": result.statement,
"transactions": result.transactions,
"balance_ok": result.balance_ok,
"duplicates": result.duplicates,
})
def _scenario_rows(session: Session, scenarios: list, loans: list) -> list[dict]:
"""Baut je Szenario den Kontext fürs Template: zugeordnete Kredite,
Modifikatoren und (falls vorhanden) das letzte Projektionsergebnis."""
rows = []
for sc in scenarios:
assigned_loan_ids = {
sl.loan_id for sl in session.execute(
select(ScenarioLoan).where(ScenarioLoan.scenario_id == sc.id)
).scalars()
}
modifiers = session.execute(
select(ScenarioModifier).where(ScenarioModifier.scenario_id == sc.id)
.order_by(ScenarioModifier.id)
).scalars().all()
planned_items = session.execute(
select(ScenarioPlannedItem)
.where(ScenarioPlannedItem.scenario_id == sc.id)
.order_by(ScenarioPlannedItem.due)).scalars().all()
recurring_items = session.execute(
select(ScenarioRecurringItem)
.where(ScenarioRecurringItem.scenario_id == sc.id)
.order_by(ScenarioRecurringItem.name, ScenarioRecurringItem.id)).scalars().all()
rows.append({
"scenario": sc,
"assigned_loan_ids": assigned_loan_ids,
"modifiers": modifiers,
"planned_items": planned_items,
"recurring_items": recurring_items,
"result": session.get(ProjectionResult, sc.id),
})
return rows
@router.get("/hilfe", dependencies=[Depends(gui_session)])
def hilfe_page(request: Request):
return templates.TemplateResponse(request, "hilfe.html", {})
@router.get("/planung", dependencies=[Depends(gui_session)])
def planung_page(request: Request, session: Session = Depends(get_session)):
_safe_refresh(session, refresh_planning_chart, date.today())
categories = session.execute(select(Category)).scalars().all()
recurring = _list_recurring(session=session)
planned = _list_planned(session=session)
loans = _list_loans(session=session)
doppelt_posten: dict[int, list[str]] = {}
doppelt_kredit: dict[int, list[str]] = {}
for k in all_conflicts(session):
doppelt_posten.setdefault(k.posten_id, []).append(k.kredit_name)
doppelt_kredit.setdefault(k.kredit_id, []).append(k.posten_name)
return templates.TemplateResponse(request, "planning.html", {
"categories": categories,
"category_names": {c.id: c.name for c in categories},
"recurring": recurring,
"recurring_names": {r.id: r.name for r in recurring},
"suggestions": _recurring_suggestions(session=session),
"planned": planned,
"loans": loans,
"rhythms": ["monthly", "quarterly", "yearly"],
"repayment_types": ["annuity", "bullet"],
"doppelt_posten": doppelt_posten,
"doppelt_kredit": doppelt_kredit,
})
@router.get("/szenarien", dependencies=[Depends(gui_session)])
def szenarien_page(request: Request, session: Session = Depends(get_session)):
_safe_refresh(session, refresh_stale_projections, date.today())
categories = session.execute(select(Category)).scalars().all()
recurring = _list_recurring(session=session)
loans = _list_loans(session=session)
scenarios = _list_scenarios(session=session)
return templates.TemplateResponse(request, "szenarien.html", {
"categories": categories,
"category_names": {c.id: c.name for c in categories},
"recurring": recurring,
"recurring_names": {r.id: r.name for r in recurring},
"loans": loans,
"scenario_rows": _scenario_rows(session, scenarios, loans),
"modifier_kinds": ["percent", "absolute", "set", "remove", "ende"],
"rhythms": ["monthly", "quarterly", "yearly"],
})
# --- Aufteilung (Ausbaustufe 13) ---
FEHLER_SZENARIO_VERGANGENHEIT = ("Szenarien sind erst ab dem laufenden Monat wählbar. "
"Für vergangene Monate „System-Plan“ wählen.")
def _parse_monat(value: str | None, today: date) -> date | None:
"""„JJJJ-MM" -> Monatserster; leer -> laufender Monat; ungültig -> None."""
if not value:
return today.replace(day=1)
m = re.fullmatch(r"(\d{4})-(\d{2})", value.strip())
if not m or not 1 <= int(m.group(2)) <= 12 or int(m.group(1)) < 1:
return None
return date(int(m.group(1)), int(m.group(2)), 1)
def _art(row: BreakdownRow) -> str:
if row.kind == "recurring":
return f"Fixposten ({de_label(row.rhythm)})"
return "Einmalposten" if row.kind == "planned" else "Kredit"
def _segmente(slices) -> list[dict]:
return [{"s": s, "q": None, "gewaehlt": False, "zusatz": None} for s in slices]
def _aufteilung_context(session: Session, today: date, monat: str | None,
szenario: str | None, fixposten: str | None,
einmal: str | None, kredite: str | None,
gesendet: str | None) -> dict:
# Erstaufruf (ohne gesendet): alle Filter an. Nach Absenden zählt nur,
# was angehakt ist - der Browser sendet leere Checkboxen gar nicht.
filters = (Filters(recurring=fixposten == "1", planned=einmal == "1",
loans=kredite == "1") if gesendet else Filters())
ctx: dict = {
"scenarios": session.execute(select(Scenario).order_by(Scenario.name)).scalars().all(),
"filters": filters, "plan_name": PLAN_NAME,
"monat_wert": monat or today.strftime("%Y-%m"), "monat_anzeige": None,
"szenario_id": None, "szenario_name": PLAN_NAME, "error": None, "b": None,
}
month = _parse_monat(monat, today)
if month is None:
ctx["error"] = f"Ungültiger Monat „{monat}“ – bitte im Format JJJJ-MM wählen."
return ctx
ctx["monat_wert"] = f"{month.year:04d}-{month.month:02d}"
ctx["monat_anzeige"] = f"{month.month:02d}.{month.year:04d}"
scenario = None
if szenario:
valid_id = re.fullmatch(r"[0-9]{1,9}", szenario) # ASCII, kein Integer-Überlauf
scenario = session.get(Scenario, int(szenario)) if valid_id else None
if scenario is None:
ctx["error"] = "Unbekanntes Szenario – bitte neu wählen."
return ctx
ctx["szenario_id"], ctx["szenario_name"] = scenario.id, scenario.name
if month < today.replace(day=1):
ctx["error"] = FEHLER_SZENARIO_VERGANGENHEIT
return ctx
b = compute_breakdown(session, month, scenario, filters)
s = b.summary
ctx.update({
"b": b, "s": s,
"zeilen": [{"r": r, "art": _art(r), "einnahme": r.amount > 0,
"anteil": share(r.amount, s.income) if r.amount < 0 else None}
for r in b.rows],
"frei_anteil": share(s.free, s.income),
"haupt": _segmente(pie_slices(s.category_costs, rest=s.pie_rest, rest_label=FREI)),
"haupt_titel": ("Anteile an den Einkünften" if s.pie_on_income
else "Anteile an den Gesamtkosten"),
})
return ctx
def _basis_query(ctx: dict) -> dict:
q = {"monat": ctx["monat_wert"], "szenario": ctx["szenario_id"] or "", "gesendet": "1"}
f = ctx["filters"]
for key, on in (("fixposten", f.recurring), ("einmal", f.planned), ("kredite", f.loans)):
if on:
q[key] = "1"
return q
def _drilldown(ctx: dict, kategorie: str | None, sammel: str | None) -> None:
"""Links der Haupttorte + ggf. zweite Torte. Die Sammelgruppe „Übrige"
läuft über sammel=1, damit eine echte Kategorie „Übrige" eindeutig bleibt."""
ctx["detail"] = None
ctx["detail_hinweis"] = None
if ctx["b"] is None:
return
sammel_gewaehlt = sammel == "1"
basis = _basis_query(ctx)
for seg in ctx["haupt"]:
s = seg["s"]
if s.color == "frei":
continue
extra = {"sammel": "1"} if s.merged else {"kategorie": s.label}
seg["q"] = urlencode({**basis, **extra})
seg["gewaehlt"] = s.merged if sammel_gewaehlt else (kategorie == s.label and not s.merged)
if sammel_gewaehlt:
merged = next((seg["s"] for seg in ctx["haupt"] if seg["s"].merged), None)
titel = UEBRIGE
enthaelt = list(merged.members) if merged else []
elif kategorie:
titel, enthaelt = kategorie, []
else:
return
rows = category_rows(ctx["b"].rows, set(enthaelt) if sammel_gewaehlt else {kategorie})
if not rows:
ctx["detail_hinweis"] = f"Kategorie „{titel}“ hat in dieser Auswahl keine Kosten."
return
income = ctx["s"].income
segmente = _segmente(pie_slices([(r.name, -r.amount) for r in rows]))
for seg in segmente:
anteil = share(seg["s"].value, income)
seg["zusatz"] = f"{pct(anteil)} der Einkünfte" if anteil is not None else None
ctx["detail"] = {"titel": titel, "enthaelt": enthaelt, "segmente": segmente,
"summe": sum((-r.amount for r in rows), Decimal("0"))}
@router.get("/aufteilung", dependencies=[Depends(gui_session)])
def aufteilung_page(request: Request, monat: str | None = None,
szenario: str | None = None, fixposten: str | None = None,
einmal: str | None = None, kredite: str | None = None,
gesendet: str | None = None, kategorie: str | None = None,
sammel: str | None = None,
session: Session = Depends(get_session)):
ctx = _aufteilung_context(session, date.today(), monat, szenario, fixposten,
einmal, kredite, gesendet)
_drilldown(ctx, kategorie, sammel)
return templates.TemplateResponse(request, "aufteilung.html", ctx)
@router.get("/aufteilung/kategorie", dependencies=[Depends(gui_session)])
def aufteilung_kategorie_fragment(request: Request, monat: str | None = None,
szenario: str | None = None,
fixposten: str | None = None,
einmal: str | None = None,
kredite: str | None = None,
gesendet: str | None = None,
kategorie: str | None = None,
sammel: str | None = None,
session: Session = Depends(get_session)):
"""htmx-Fragment: zweite Torte + Haupttorte out-of-band (Markierung)."""
ctx = _aufteilung_context(session, date.today(), monat, szenario, fixposten,
einmal, kredite, gesendet)
_drilldown(ctx, kategorie, sammel)
if ctx["error"]:
ctx["detail_hinweis"] = ctx["error"]
ctx["oob"] = ctx["b"] is not None
return templates.TemplateResponse(request, "_aufteilung_kategorie.html", ctx)