rene
9b0c286aa3
KATEGORIE_META als Dict mit id → {label, color} (preserved Order).
KATEGORIEN bleibt als Set für Validierung. Neuer Endpunkt liefert
[{id, label, color}, …] als Single Source of Truth für PWA und mobile
Clients — bisher war die Liste in JS und Python dupliziert.
Mobile-Client (banyaro-ios) holt die Liste jetzt dynamisch.
416 lines
16 KiB
Python
416 lines
16 KiB
Python
"""BAN YARO — Ausgaben-Tracker Routes"""
|
|
|
|
import logging
|
|
from datetime import date, timedelta
|
|
from dateutil.relativedelta import relativedelta
|
|
from fastapi import APIRouter, Depends, HTTPException, Query
|
|
from pydantic import BaseModel, Field
|
|
from typing import Optional
|
|
from database import db
|
|
from auth import get_current_user
|
|
|
|
router = APIRouter()
|
|
logger = logging.getLogger(__name__)
|
|
|
|
KATEGORIE_META = {
|
|
"futter": {"label": "Futter", "color": "#f59e0b"},
|
|
"tierarzt": {"label": "Tierarzt", "color": "#ef4444"},
|
|
"zubehoer": {"label": "Zubehör", "color": "#8b5cf6"},
|
|
"versicherung": {"label": "Versicherung", "color": "#3b82f6"},
|
|
"sitter": {"label": "Sitter", "color": "#10b981"},
|
|
"sonstiges": {"label": "Sonstiges", "color": "#6b7280"},
|
|
}
|
|
KATEGORIEN = set(KATEGORIE_META.keys())
|
|
|
|
|
|
# ------------------------------------------------------------------
|
|
# Schemas
|
|
# ------------------------------------------------------------------
|
|
class ExpenseCreate(BaseModel):
|
|
dog_id: Optional[int] = None
|
|
kategorie: str = Field(..., max_length=50)
|
|
betrag: float
|
|
datum: str = Field(..., max_length=32)
|
|
notiz: Optional[str] = Field(None, max_length=1000)
|
|
|
|
|
|
class ExpenseUpdate(BaseModel):
|
|
dog_id: Optional[int] = None
|
|
kategorie: Optional[str] = Field(None, max_length=50)
|
|
betrag: Optional[float] = None
|
|
datum: Optional[str] = Field(None, max_length=32)
|
|
notiz: Optional[str] = Field(None, max_length=1000)
|
|
|
|
|
|
class RecurringCreate(BaseModel):
|
|
dog_id: Optional[int] = None
|
|
kategorie: str = Field(..., max_length=50)
|
|
betrag: float
|
|
haeufigkeit: str = Field(..., max_length=30) # monatlich | quartalsweise | jaehrlich
|
|
startdatum: str = Field(..., max_length=32) # ISO date
|
|
notiz: Optional[str] = Field(None, max_length=1000)
|
|
|
|
class RecurringUpdate(BaseModel):
|
|
dog_id: Optional[int] = None
|
|
kategorie: Optional[str] = Field(None, max_length=50)
|
|
betrag: Optional[float] = None
|
|
haeufigkeit: Optional[str] = Field(None, max_length=30)
|
|
startdatum: Optional[str] = Field(None, max_length=32)
|
|
notiz: Optional[str] = Field(None, max_length=1000)
|
|
aktiv: Optional[bool] = None
|
|
|
|
|
|
HAEUFIGKEITEN = {"monatlich", "quartalsweise", "jaehrlich"}
|
|
|
|
|
|
def _next_due(startdatum: str, haeufigkeit: str, after: date) -> date:
|
|
"""Berechnet das nächste Fälligkeitsdatum nach `after`."""
|
|
d = date.fromisoformat(startdatum)
|
|
if d > after:
|
|
return d
|
|
if haeufigkeit == "monatlich":
|
|
delta = relativedelta(months=1)
|
|
elif haeufigkeit == "quartalsweise":
|
|
delta = relativedelta(months=3)
|
|
else:
|
|
delta = relativedelta(years=1)
|
|
while d <= after:
|
|
d += delta
|
|
return d
|
|
|
|
|
|
def _serialize(row) -> dict:
|
|
return dict(row)
|
|
|
|
|
|
# ------------------------------------------------------------------
|
|
# GET /api/expenses/categories — gültige Kategorien (id + label + color)
|
|
# Single source of truth für PWA und mobile Clients.
|
|
# ------------------------------------------------------------------
|
|
@router.get("/categories")
|
|
async def list_categories():
|
|
return [
|
|
{"id": key, **meta}
|
|
for key, meta in KATEGORIE_META.items()
|
|
]
|
|
|
|
|
|
# ------------------------------------------------------------------
|
|
# GET /api/expenses/summary — Monats- und Jahressummen
|
|
# WICHTIG: Diese Route muss VOR /{id} stehen!
|
|
# ------------------------------------------------------------------
|
|
@router.get("/summary")
|
|
async def get_summary(
|
|
dog_id: Optional[int] = Query(default=None),
|
|
user=Depends(get_current_user),
|
|
):
|
|
today = date.today()
|
|
monat_prefix = today.strftime("%Y-%m")
|
|
jahr_prefix = today.strftime("%Y")
|
|
|
|
extra_cond = ""
|
|
extra_params: list = []
|
|
if dog_id is not None:
|
|
extra_cond = " AND dog_id=?"
|
|
extra_params = [dog_id]
|
|
|
|
with db() as conn:
|
|
# Monats-Summen pro Kategorie
|
|
rows_monat = conn.execute(
|
|
f"""SELECT kategorie, COALESCE(SUM(betrag),0) AS summe
|
|
FROM expenses
|
|
WHERE user_id=? AND datum LIKE ?{extra_cond}
|
|
GROUP BY kategorie""",
|
|
[user["id"], f"{monat_prefix}%"] + extra_params,
|
|
).fetchall()
|
|
|
|
# Jahres-Summen pro Kategorie
|
|
rows_jahr = conn.execute(
|
|
f"""SELECT kategorie, COALESCE(SUM(betrag),0) AS summe
|
|
FROM expenses
|
|
WHERE user_id=? AND datum LIKE ?{extra_cond}
|
|
GROUP BY kategorie""",
|
|
[user["id"], f"{jahr_prefix}%"] + extra_params,
|
|
).fetchall()
|
|
|
|
monat = {r["kategorie"]: round(r["summe"], 2) for r in rows_monat}
|
|
jahr = {r["kategorie"]: round(r["summe"], 2) for r in rows_jahr}
|
|
|
|
gesamt_monat = round(sum(monat.values()), 2)
|
|
gesamt_jahr = round(sum(jahr.values()), 2)
|
|
|
|
return {
|
|
"monat": monat,
|
|
"jahr": jahr,
|
|
"gesamt_monat": gesamt_monat,
|
|
"gesamt_jahr": gesamt_jahr,
|
|
}
|
|
|
|
|
|
# ------------------------------------------------------------------
|
|
# GET /api/expenses — Liste mit optionalen Filtern
|
|
# ------------------------------------------------------------------
|
|
@router.get("")
|
|
async def list_expenses(
|
|
dog_id: Optional[int] = Query(default=None),
|
|
von: Optional[str] = Query(default=None),
|
|
bis: Optional[str] = Query(default=None),
|
|
limit: int = Query(default=100, le=500),
|
|
offset: int = Query(default=0),
|
|
user=Depends(get_current_user),
|
|
):
|
|
conditions = ["e.user_id=?"]
|
|
params: list = [user["id"]]
|
|
|
|
if dog_id is not None:
|
|
conditions.append("e.dog_id=?")
|
|
params.append(dog_id)
|
|
if von:
|
|
conditions.append("e.datum >= ?")
|
|
params.append(von)
|
|
if bis:
|
|
conditions.append("e.datum <= ?")
|
|
params.append(bis)
|
|
|
|
where = " AND ".join(conditions)
|
|
params += [limit, offset]
|
|
|
|
with db() as conn:
|
|
rows = conn.execute(
|
|
f"""SELECT e.*, d.name AS dog_name
|
|
FROM expenses e
|
|
LEFT JOIN dogs d ON d.id = e.dog_id
|
|
WHERE {where}
|
|
ORDER BY e.datum DESC, e.id DESC
|
|
LIMIT ? OFFSET ?""",
|
|
params,
|
|
).fetchall()
|
|
|
|
return [_serialize(r) for r in rows]
|
|
|
|
|
|
# ------------------------------------------------------------------
|
|
# POST /api/expenses — neuer Eintrag
|
|
# ------------------------------------------------------------------
|
|
@router.post("", status_code=201)
|
|
async def create_expense(data: ExpenseCreate, user=Depends(get_current_user)):
|
|
if data.kategorie not in KATEGORIEN:
|
|
raise HTTPException(400, f"Ungültige Kategorie: {data.kategorie}")
|
|
if data.betrag <= 0:
|
|
raise HTTPException(400, "Betrag muss größer als 0 sein.")
|
|
|
|
with db() as conn:
|
|
# dog_id prüfen — muss dem User gehören
|
|
if data.dog_id is not None:
|
|
dog = conn.execute(
|
|
"SELECT id FROM dogs WHERE id=? AND user_id=?",
|
|
(data.dog_id, user["id"]),
|
|
).fetchone()
|
|
if not dog:
|
|
raise HTTPException(404, "Hund nicht gefunden.")
|
|
|
|
conn.execute(
|
|
"""INSERT INTO expenses (user_id, dog_id, kategorie, betrag, datum, notiz)
|
|
VALUES (?, ?, ?, ?, ?, ?)""",
|
|
(user["id"], data.dog_id, data.kategorie, data.betrag, data.datum, data.notiz),
|
|
)
|
|
row = conn.execute(
|
|
"SELECT * FROM expenses WHERE user_id=? ORDER BY id DESC LIMIT 1",
|
|
(user["id"],),
|
|
).fetchone()
|
|
|
|
return _serialize(row)
|
|
|
|
|
|
# ------------------------------------------------------------------
|
|
# PATCH /api/expenses/{id} — bearbeiten
|
|
# ------------------------------------------------------------------
|
|
@router.patch("/{expense_id}")
|
|
async def update_expense(
|
|
expense_id: int, data: ExpenseUpdate, user=Depends(get_current_user)
|
|
):
|
|
with db() as conn:
|
|
row = conn.execute(
|
|
"SELECT * FROM expenses WHERE id=? AND user_id=?",
|
|
(expense_id, user["id"]),
|
|
).fetchone()
|
|
if not row:
|
|
raise HTTPException(404, "Eintrag nicht gefunden.")
|
|
|
|
updates = {}
|
|
if data.kategorie is not None:
|
|
if data.kategorie not in KATEGORIEN:
|
|
raise HTTPException(400, f"Ungültige Kategorie: {data.kategorie}")
|
|
updates["kategorie"] = data.kategorie
|
|
if data.betrag is not None:
|
|
if data.betrag <= 0:
|
|
raise HTTPException(400, "Betrag muss größer als 0 sein.")
|
|
updates["betrag"] = data.betrag
|
|
if data.datum is not None:
|
|
updates["datum"] = data.datum
|
|
if data.notiz is not None:
|
|
updates["notiz"] = data.notiz
|
|
if data.dog_id is not None:
|
|
dog = conn.execute(
|
|
"SELECT id FROM dogs WHERE id=? AND user_id=?",
|
|
(data.dog_id, user["id"]),
|
|
).fetchone()
|
|
if not dog:
|
|
raise HTTPException(404, "Hund nicht gefunden.")
|
|
updates["dog_id"] = data.dog_id
|
|
|
|
if not updates:
|
|
return _serialize(row)
|
|
|
|
set_clause = ", ".join(f"{k}=?" for k in updates)
|
|
values = list(updates.values()) + [expense_id]
|
|
conn.execute(f"UPDATE expenses SET {set_clause} WHERE id=?", values)
|
|
row = conn.execute("SELECT * FROM expenses WHERE id=?", (expense_id,)).fetchone()
|
|
|
|
return _serialize(row)
|
|
|
|
|
|
# ------------------------------------------------------------------
|
|
# DELETE /api/expenses/{id} — löschen
|
|
# ------------------------------------------------------------------
|
|
@router.delete("/{expense_id}", status_code=204)
|
|
async def delete_expense(expense_id: int, user=Depends(get_current_user)):
|
|
with db() as conn:
|
|
row = conn.execute(
|
|
"SELECT id FROM expenses WHERE id=? AND user_id=?",
|
|
(expense_id, user["id"]),
|
|
).fetchone()
|
|
if not row:
|
|
raise HTTPException(404, "Eintrag nicht gefunden.")
|
|
conn.execute("DELETE FROM expenses WHERE id=?", (expense_id,))
|
|
return None
|
|
|
|
|
|
# ------------------------------------------------------------------
|
|
# Wiederkehrende Ausgaben
|
|
# ------------------------------------------------------------------
|
|
@router.get("/recurring")
|
|
async def list_recurring(user=Depends(get_current_user)):
|
|
with db() as conn:
|
|
rows = conn.execute(
|
|
"""SELECT r.*, d.name AS dog_name
|
|
FROM recurring_expenses r
|
|
LEFT JOIN dogs d ON d.id = r.dog_id
|
|
WHERE r.user_id=? ORDER BY r.startdatum DESC""",
|
|
(user["id"],),
|
|
).fetchall()
|
|
return [dict(r) for r in rows]
|
|
|
|
|
|
@router.post("/recurring", status_code=201)
|
|
async def create_recurring(data: RecurringCreate, user=Depends(get_current_user)):
|
|
if data.kategorie not in KATEGORIEN:
|
|
raise HTTPException(400, f"Ungültige Kategorie: {data.kategorie}")
|
|
if data.haeufigkeit not in HAEUFIGKEITEN:
|
|
raise HTTPException(400, f"Ungültige Häufigkeit: {data.haeufigkeit}")
|
|
if data.betrag <= 0:
|
|
raise HTTPException(400, "Betrag muss größer als 0 sein.")
|
|
|
|
today = date.today()
|
|
naechste = _next_due(data.startdatum, data.haeufigkeit, today - timedelta(days=1))
|
|
|
|
with db() as conn:
|
|
if data.dog_id:
|
|
if not conn.execute("SELECT 1 FROM dogs WHERE id=? AND user_id=?",
|
|
(data.dog_id, user["id"])).fetchone():
|
|
raise HTTPException(404, "Hund nicht gefunden.")
|
|
conn.execute(
|
|
"""INSERT INTO recurring_expenses
|
|
(user_id, dog_id, kategorie, betrag, haeufigkeit, startdatum, naechste_faelligkeit, notiz)
|
|
VALUES (?,?,?,?,?,?,?,?)""",
|
|
(user["id"], data.dog_id, data.kategorie, data.betrag,
|
|
data.haeufigkeit, data.startdatum, str(naechste), data.notiz),
|
|
)
|
|
row = conn.execute(
|
|
"SELECT * FROM recurring_expenses WHERE user_id=? ORDER BY id DESC LIMIT 1",
|
|
(user["id"],),
|
|
).fetchone()
|
|
return dict(row)
|
|
|
|
|
|
@router.patch("/recurring/{rid}")
|
|
async def update_recurring(rid: int, data: RecurringUpdate, user=Depends(get_current_user)):
|
|
with db() as conn:
|
|
row = conn.execute(
|
|
"SELECT * FROM recurring_expenses WHERE id=? AND user_id=?", (rid, user["id"])
|
|
).fetchone()
|
|
if not row:
|
|
raise HTTPException(404, "Dauerauftrag nicht gefunden.")
|
|
updates: dict = {}
|
|
if data.kategorie is not None:
|
|
if data.kategorie not in KATEGORIEN:
|
|
raise HTTPException(400, f"Ungültige Kategorie.")
|
|
updates["kategorie"] = data.kategorie
|
|
if data.betrag is not None:
|
|
updates["betrag"] = data.betrag
|
|
if data.haeufigkeit is not None:
|
|
if data.haeufigkeit not in HAEUFIGKEITEN:
|
|
raise HTTPException(400, "Ungültige Häufigkeit.")
|
|
updates["haeufigkeit"] = data.haeufigkeit
|
|
if data.startdatum is not None:
|
|
updates["startdatum"] = data.startdatum
|
|
if data.notiz is not None:
|
|
updates["notiz"] = data.notiz
|
|
if data.aktiv is not None:
|
|
updates["aktiv"] = 1 if data.aktiv else 0
|
|
if updates:
|
|
# naechste_faelligkeit neu berechnen wenn relevante Felder geändert
|
|
startdatum = updates.get("startdatum", row["startdatum"])
|
|
haeufigkeit = updates.get("haeufigkeit", row["haeufigkeit"])
|
|
today = date.today()
|
|
updates["naechste_faelligkeit"] = str(
|
|
_next_due(startdatum, haeufigkeit, today - timedelta(days=1))
|
|
)
|
|
set_clause = ", ".join(f"{k}=?" for k in updates)
|
|
conn.execute(f"UPDATE recurring_expenses SET {set_clause} WHERE id=?",
|
|
[*updates.values(), rid])
|
|
row = conn.execute("SELECT * FROM recurring_expenses WHERE id=?", (rid,)).fetchone()
|
|
return dict(row)
|
|
|
|
|
|
@router.delete("/recurring/{rid}", status_code=204)
|
|
async def delete_recurring(rid: int, user=Depends(get_current_user)):
|
|
with db() as conn:
|
|
if not conn.execute("SELECT 1 FROM recurring_expenses WHERE id=? AND user_id=?",
|
|
(rid, user["id"])).fetchone():
|
|
raise HTTPException(404, "Dauerauftrag nicht gefunden.")
|
|
conn.execute("DELETE FROM recurring_expenses WHERE id=?", (rid,))
|
|
return None
|
|
|
|
|
|
def process_due_recurring(user_id: int | None = None):
|
|
"""Legt fällige Daueraufträge als Einträge an. Wird vom Scheduler aufgerufen."""
|
|
today = date.today()
|
|
today_str = str(today)
|
|
with db() as conn:
|
|
where = "aktiv=1 AND naechste_faelligkeit <= ?"
|
|
params: list = [today_str]
|
|
if user_id:
|
|
where += " AND user_id=?"
|
|
params.append(user_id)
|
|
rows = conn.execute(
|
|
f"SELECT * FROM recurring_expenses WHERE {where}", params
|
|
).fetchall()
|
|
|
|
for r in rows:
|
|
# Eintrag anlegen
|
|
conn.execute(
|
|
"""INSERT INTO expenses (user_id, dog_id, kategorie, betrag, datum, notiz)
|
|
VALUES (?,?,?,?,?,?)""",
|
|
(r["user_id"], r["dog_id"], r["kategorie"], r["betrag"],
|
|
r["naechste_faelligkeit"],
|
|
f"[Dauerauftrag] {r['notiz'] or r['kategorie']}"),
|
|
)
|
|
# Nächste Fälligkeit berechnen
|
|
naechste = _next_due(r["startdatum"], r["haeufigkeit"],
|
|
date.fromisoformat(r["naechste_faelligkeit"]))
|
|
conn.execute(
|
|
"UPDATE recurring_expenses SET naechste_faelligkeit=? WHERE id=?",
|
|
(str(naechste), r["id"]),
|
|
)
|
|
return len(rows) if rows else 0
|