feat: Buchungen serverseitig nach Spalte sortierbar (A11-T5)

Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>

finance/app/routers/gui.py

@@ -25,7 +25,7 @@ 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
+from app.routers.transactions import count_transactions, list_transactions, normalize_sort
 from app.services.balances import account_balance, total_balance
 from app.services.planning_chart import refresh_planning_chart
 from app.services.projection_service import refresh_stale_projections
@@ -168,6 +168,8 @@ def buchungen(
     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 -
@@ -179,6 +181,7 @@ def buchungen(
     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,
@@ -197,6 +200,8 @@ def buchungen(
         category_id=category_id,
         q=q,
         status=status,
+        sort=sort,
+        dir=dir,
         limit=PAGE_SIZE,
         offset=(page - 1) * PAGE_SIZE,
         session=session,
@@ -217,6 +222,8 @@ def buchungen(
         },
         "page": page,
         "total_pages": total_pages,
+        "sort": sort,
+        "dir": dir,
     })
 
 

finance/app/routers/transactions.py

@@ -75,6 +75,38 @@ def _apply_transaction_filters(
     return stmt
 
 
+# Serverseitige Sortierung der Buchungsliste (Ausbaustufe 11): Whitelist
+# URL-Schlüssel -> Sortierausdruck. Textspalten über lower(), damit SQLite
+# (Tests) und Postgres gleich (case-insensitiv) sortieren.
+SORT_COLUMNS = {
+    "datum": Transaction.booking_date,
+    "konto": func.lower(Account.name),
+    "zweck": func.lower(Transaction.purpose),
+    "empfaenger": func.lower(Transaction.counterparty),
+    "betrag": Transaction.amount,
+    "kategorie": func.lower(Category.name),
+}
+
+
+def normalize_sort(sort: str | None, dir: str | None) -> tuple[str, str]:
+    """Unbekannte Werte fallen still auf den Standard (datum/asc) zurück -
+    kein 422 für manipulierte oder veraltete Links."""
+    return (sort if sort in SORT_COLUMNS else "datum",
+            dir if dir in ("asc", "desc") else "asc")
+
+
+def _apply_sort(stmt, sort: str | None, dir: str | None):
+    sort, dir = normalize_sort(sort, dir)
+    if sort == "konto":
+        stmt = stmt.outerjoin(Account, Account.id == Transaction.account_id)
+    elif sort == "kategorie":
+        stmt = stmt.outerjoin(Category, Category.id == Transaction.category_id)
+    col = SORT_COLUMNS[sort]
+    if dir == "desc":
+        return stmt.order_by(col.desc().nulls_last(), Transaction.id.desc())
+    return stmt.order_by(col.asc().nulls_last(), Transaction.id.asc())
+
+
 @router.get("", response_model=list[TransactionOut])
 def list_transactions(
     account_id: int | None = None,
@@ -83,6 +115,8 @@ def list_transactions(
     category_id: int | None = None,
     q: str | None = None,
     status: str = "confirmed",
+    sort: str = "datum",
+    dir: str = "asc",
     limit: int = 200,
     offset: int = 0,
     session: Session = Depends(get_session),
@@ -91,7 +125,7 @@ def list_transactions(
         select(Transaction), account_id=account_id, date_from=date_from,
         date_to=date_to, category_id=category_id, q=q, status=status,
     )
-    stmt = stmt.order_by(Transaction.booking_date, Transaction.id).offset(offset).limit(limit)
+    stmt = _apply_sort(stmt, sort, dir).offset(offset).limit(limit)
     return [TransactionOut.model_validate(t) for t in session.execute(stmt).scalars()]
 
 

finance/app/templates/transactions.html

@@ -39,11 +39,25 @@
   <button type="submit">Filtern</button>
 </form>
 
+{% set filter_params = {
+  'account_id': filters.account_id,
+  'date_from': filters.date_from.isoformat() if filters.date_from else None,
+  'date_to': filters.date_to.isoformat() if filters.date_to else None,
+  'category_id': filters.category_id,
+  'q': filters.q,
+  'status': filters.status,
+} %}
+{% macro sort_th(key, label, params, cur_sort, cur_dir) -%}
+  {%- set active = cur_sort == key -%}
+  <th>{{ label }} <a class="sort-btn{{ ' active' if active else '' }}" aria-label="Nach {{ label }} sortieren"
+     href="/buchungen?{% for k, v in params.items() %}{% if v %}{{ k }}={{ v|urlencode }}&{% endif %}{% endfor %}sort={{ key }}&dir={{ 'desc' if active and cur_dir == 'asc' else 'asc' }}">{{ ('▲' if cur_dir == 'asc' else '▼') if active else '⇅' }}</a></th>
+{%- endmacro %}
 <table data-sort-mode="server">
   <thead>
     <tr>
-      <th>Datum</th><th>Konto</th><th>Verwendungszweck</th><th>Empfänger/Zahler</th>
-      <th>Betrag (€)</th><th>Kategorie</th><th></th>
+      {{ sort_th('datum', 'Datum', filter_params, sort, dir) }}{{ sort_th('konto', 'Konto', filter_params, sort, dir) }}
+      {{ sort_th('zweck', 'Verwendungszweck', filter_params, sort, dir) }}{{ sort_th('empfaenger', 'Empfänger/Zahler', filter_params, sort, dir) }}
+      {{ sort_th('betrag', 'Betrag (€)', filter_params, sort, dir) }}{{ sort_th('kategorie', 'Kategorie', filter_params, sort, dir) }}<th></th>
     </tr>
   </thead>
   <tbody>
@@ -88,20 +102,12 @@
 </table>
 
 <p class="pager">
-  {% set pager_params = {
-    'account_id': filters.account_id,
-    'date_from': filters.date_from.isoformat() if filters.date_from else None,
-    'date_to': filters.date_to.isoformat() if filters.date_to else None,
-    'category_id': filters.category_id,
-    'q': filters.q,
-    'status': filters.status,
-  } %}
   {% if page > 1 %}
-  <a href="/buchungen?{% for k, v in pager_params.items() %}{% if v %}{{ k }}={{ v|urlencode }}&{% endif %}{% endfor %}page={{ page - 1 }}">Zurück</a>
+  <a href="/buchungen?{% for k, v in filter_params.items() %}{% if v %}{{ k }}={{ v|urlencode }}&{% endif %}{% endfor %}sort={{ sort }}&dir={{ dir }}&page={{ page - 1 }}">Zurück</a>
   {% endif %}
   Seite {{ page }} von {{ total_pages }}
   {% if page < total_pages %}
-  <a href="/buchungen?{% for k, v in pager_params.items() %}{% if v %}{{ k }}={{ v|urlencode }}&{% endif %}{% endfor %}page={{ page + 1 }}">Weiter</a>
+  <a href="/buchungen?{% for k, v in filter_params.items() %}{% if v %}{{ k }}={{ v|urlencode }}&{% endif %}{% endfor %}sort={{ sort }}&dir={{ dir }}&page={{ page + 1 }}">Weiter</a>
   {% endif %}
 </p>
 

finance/tests/test_transactions_sort.py

@@ -0,0 +1,89 @@
+from datetime import date, timedelta
+from decimal import Decimal
+
+import pytest
+
+from app.models.tables import Account, Category, Transaction
+
+H = {"Authorization": "Bearer test-key"}
+
+
+def _tx(db, acc, day, amount, purpose="Zweck", counterparty="X", category=None, n=[0]):
+    n[0] += 1
+    db.add(Transaction(account_id=acc.id, booking_date=day, amount=Decimal(amount),
+                       purpose=purpose, counterparty=counterparty, status="confirmed",
+                       category_id=category.id if category else None,
+                       dedup_hash=f"sort-{n[0]}"))
+
+
+@pytest.fixture
+def small(db):
+    zeta = Account(bank="t", iban="DE-SO-1", name="Zeta", type="giro")
+    alpha = Account(bank="t", iban="DE-SO-2", name="alpha", type="giro")
+    kat = Category(name="Miete")
+    db.add_all([zeta, alpha, kat])
+    db.flush()
+    _tx(db, zeta, date(2026, 1, 3), "-5.00", "b-Zweck", "Bert", kat)
+    _tx(db, alpha, date(2026, 1, 1), "20.00", "A-Zweck", "anna", None)
+    _tx(db, zeta, date(2026, 1, 2), "-30.00", "c-Zweck", "Carl", kat)
+    db.commit()
+
+
+def _amounts(client, qs):
+    r = client.get(f"/api/transactions?{qs}", headers=H)
+    assert r.status_code == 200
+    return [Decimal(t["amount"]) for t in r.json()]
+
+
+def test_standard_bleibt_datum_aufsteigend(client, small):
+    assert _amounts(client, "") == [Decimal("20.00"), Decimal("-30.00"), Decimal("-5.00")]
+
+
+@pytest.mark.parametrize("sort,dir,expected", [
+    ("betrag", "asc", ["-30.00", "-5.00", "20.00"]),
+    ("betrag", "desc", ["20.00", "-5.00", "-30.00"]),
+    ("datum", "desc", ["-5.00", "-30.00", "20.00"]),
+    ("konto", "asc", ["20.00", "-5.00", "-30.00"]),       # alpha vor Zeta (case-insensitiv), Gleichstand -> id
+    ("zweck", "asc", ["20.00", "-5.00", "-30.00"]),       # A-, b-, c-Zweck
+    ("empfaenger", "desc", ["-30.00", "-5.00", "20.00"]),  # Carl, Bert, anna
+    ("kategorie", "asc", ["-5.00", "-30.00", "20.00"]),   # Miete (id-Reihenfolge), unkategorisiert zuletzt
+    ("kategorie", "desc", ["-30.00", "-5.00", "20.00"]),  # unkategorisiert auch absteigend zuletzt
+])
+def test_sortierung_je_spalte(client, small, sort, dir, expected):
+    assert _amounts(client, f"sort={sort}&dir={dir}") == [Decimal(e) for e in expected]
+
+
+def test_ungueltige_werte_fallen_auf_standard(client, small):
+    assert _amounts(client, "sort=foo;drop&dir=hoch") == _amounts(client, "")
+    client.post("/login", data={"username": "admin", "password": "geheim"})
+    assert client.get("/buchungen?sort=foo&dir=bar").status_code == 200
+
+
+@pytest.fixture
+def many(db):
+    acc = Account(bank="t", iban="DE-SO-3", name="Viel", type="giro")
+    db.add(acc)
+    db.flush()
+    for i in range(1, 61):  # Beträge i,37; Datum verwürfelt
+        _tx(db, acc, date(2026, 1, 1) + timedelta(days=(i * 7) % 60), f"{i}.37", f"Miete {i}")
+    db.commit()
+
+
+def test_sortierung_gilt_ueber_alle_seiten(client, many):
+    client.post("/login", data={"username": "admin", "password": "geheim"})
+    p1 = client.get("/buchungen?sort=betrag&dir=desc").text
+    p2 = client.get("/buchungen?sort=betrag&dir=desc&page=2").text
+    assert ">60,37</td>" in p1 and ">1,37</td>" not in p1
+    assert ">1,37</td>" in p2 and ">60,37</td>" not in p2
+
+
+def test_links_erhalten_filter_und_sortierung(client, many):
+    client.post("/login", data={"username": "admin", "password": "geheim"})
+    html = client.get("/buchungen?q=Miete&sort=betrag&dir=asc").text
+    # Aktiver Spaltenkopf kehrt die Richtung um, übernimmt den Filter, ohne page.
+    assert 'href="/buchungen?q=Miete&status=confirmed&sort=betrag&dir=desc"' in html
+    # Inaktive Spalte startet aufsteigend.
+    assert 'href="/buchungen?q=Miete&status=confirmed&sort=datum&dir=asc"' in html
+    # Pager behält Filter + Sortierung.
+    assert "q=Miete&status=confirmed&sort=betrag&dir=asc&page=2" in html
+    assert "▲" in html