129 lines
6.6 KiB
Python
129 lines
6.6 KiB
Python
from datetime import date
|
|
from fastapi import HTTPException
|
|
from database import connect
|
|
from models import CATEGORIES, MONTHS, cents, valid_date, percent, decimal_string
|
|
from services.asset_service import list_assets as assets
|
|
|
|
|
|
def _get_entry(db, entry_id):
|
|
if not 1 <= entry_id <= 9223372036854775807:
|
|
raise HTTPException(404, 'Zahlung nicht gefunden.')
|
|
row = db.execute('SELECT i.*, a.name FROM income_entries i JOIN assets a ON a.id=i.asset_id WHERE i.id=?', (entry_id,)).fetchone()
|
|
if row is None:
|
|
raise HTTPException(404, 'Zahlung nicht gefunden.')
|
|
return dict(row)
|
|
|
|
|
|
def get_entry(entry_id):
|
|
with connect() as db:
|
|
return _get_entry(db, entry_id)
|
|
|
|
|
|
def _validated_values(data, db, existing):
|
|
day = valid_date(data.get('date', ''))
|
|
amount = cents(data.get('amount', ''))
|
|
category = data.get('category', '')
|
|
if category not in CATEGORIES:
|
|
raise ValueError('Bitte eine gültige Kategorie auswählen.')
|
|
try:
|
|
asset_id = int(data.get('asset_id', ''))
|
|
if not 1 <= asset_id <= 9223372036854775807:
|
|
raise ValueError
|
|
except (TypeError, ValueError):
|
|
raise ValueError('Bitte eine Position auswählen.') from None
|
|
note = (data.get('note') or '').strip()
|
|
if len(note) > 2000:
|
|
raise ValueError('Notiz darf maximal 2000 Zeichen enthalten.')
|
|
expected = int(data.get('expected') in (True, '1'))
|
|
received = int(data.get('received') in (True, '1'))
|
|
asset = db.execute('SELECT * FROM assets WHERE id=?', (asset_id,)).fetchone()
|
|
if asset is None or (not asset['active'] and (existing is None or existing['asset_id'] != asset_id)):
|
|
raise ValueError('Diese Position ist nicht mehr verfügbar.')
|
|
return day, asset_id, category, amount, note or None, expected, received
|
|
|
|
|
|
def write_entry(data, entry_id=None, partial=False):
|
|
"""Validate and write atomically; PATCH merges inside the write transaction."""
|
|
with connect() as db:
|
|
db.execute('BEGIN IMMEDIATE')
|
|
existing = _get_entry(db, entry_id) if entry_id is not None else None
|
|
if partial:
|
|
merged = dict(existing)
|
|
merged['amount'] = decimal_string(existing['amount'])
|
|
merged.update(data)
|
|
data = merged
|
|
values = _validated_values(data, db, existing)
|
|
if entry_id is not None:
|
|
db.execute("UPDATE income_entries SET date=?, asset_id=?, category=?, amount=?, note=?, expected=?, received=?, updated_at=strftime('%Y-%m-%dT%H:%M:%fZ','now') WHERE id=?", (*values, entry_id))
|
|
else:
|
|
entry_id = db.execute('INSERT INTO income_entries (date,asset_id,category,amount,note,expected,received) VALUES (?,?,?,?,?,?,?)', values).lastrowid
|
|
return _get_entry(db, entry_id)
|
|
|
|
|
|
def save_entry(data, entry_id=None):
|
|
"""Existing web/import-facing return contract."""
|
|
return write_entry(data, entry_id)['id']
|
|
|
|
|
|
def delete_entry(entry_id):
|
|
with connect() as db:
|
|
db.execute('BEGIN IMMEDIATE')
|
|
_get_entry(db, entry_id)
|
|
db.execute('DELETE FROM income_entries WHERE id=?', (entry_id,))
|
|
|
|
|
|
def list_entries(year=None, month=None, asset_id=None, category=None, limit=None, offset=0, received=None, expected=None):
|
|
# SQL fragments are constants. All filter values remain bound parameters.
|
|
clauses, args = [], []
|
|
for sql, value in [("substr(i.date,1,4) = ?", str(year) if year else None),
|
|
("substr(i.date,6,2) = ?", f'{month:02}' if month else None),
|
|
('i.asset_id = ?', asset_id), ('i.category = ?', category),
|
|
('i.received = ?', received), ('i.expected = ?', expected)]:
|
|
if value is not None:
|
|
clauses.append(sql)
|
|
args.append(value)
|
|
query = 'SELECT i.*, a.name FROM income_entries i JOIN assets a ON a.id=i.asset_id'
|
|
if clauses:
|
|
query += ' WHERE ' + ' AND '.join(clauses)
|
|
query += ' ORDER BY i.date DESC, i.id DESC'
|
|
if limit is not None:
|
|
query += ' LIMIT ? OFFSET ?'
|
|
args.extend([limit, offset])
|
|
with connect() as db:
|
|
return db.execute(query, args).fetchall()
|
|
|
|
|
|
def available_years():
|
|
with connect() as db:
|
|
return [int(row[0]) for row in db.execute('SELECT DISTINCT substr(date,1,4) FROM income_entries ORDER BY 1')]
|
|
|
|
|
|
def dashboard(today=None):
|
|
today = today or date.today()
|
|
with connect() as db:
|
|
db.execute('BEGIN') # One read snapshot for all dashboard/statistics aggregates.
|
|
years_found = [int(r[0]) for r in db.execute('SELECT DISTINCT substr(date,1,4) FROM income_entries')]
|
|
grouped = db.execute("SELECT substr(date,1,4) year, substr(date,6,2) month, SUM(amount) amount, COUNT(*) count FROM income_entries WHERE received=1 GROUP BY year, month").fetchall()
|
|
shares = [dict(r) for r in db.execute('SELECT a.id asset_id, a.name, SUM(i.amount) amount FROM income_entries i JOIN assets a ON a.id=i.asset_id WHERE received=1 GROUP BY a.id ORDER BY amount DESC')]
|
|
kinds = {r['category']: r['amount'] for r in db.execute('SELECT category, SUM(amount) amount FROM income_entries WHERE received=1 GROUP BY category')}
|
|
pending = db.execute('SELECT COUNT(*) count, COALESCE(SUM(amount),0) amount FROM income_entries WHERE expected=1 AND received=0').fetchone()
|
|
years = list(range(min(years_found + [today.year]), max(years_found + [today.year + 1]) + 1))
|
|
monthly = {year: [0] * 12 for year in years}
|
|
count = 0
|
|
for row in grouped:
|
|
year = int(row['year'])
|
|
monthly[year][int(row['month']) - 1] = row['amount']
|
|
if year == today.year:
|
|
count += row['count']
|
|
totals = {year: sum(values) for year, values in monthly.items()}
|
|
current_month = monthly[today.year][today.month - 1]
|
|
prior_month = monthly.get(today.year - 1, [0] * 12)[today.month - 1]
|
|
prior_year = totals.get(today.year - 1, 0)
|
|
return dict(years=years, monthly=monthly, totals=totals, month=current_month, prior_month=prior_month,
|
|
month_change=percent(current_month, prior_month), year=totals[today.year], prior_year=prior_year,
|
|
year_change=percent(totals[today.year], prior_year), all_time=sum(totals.values()), count=count,
|
|
pending=dict(pending), today=today, shares=shares, categories=kinds,
|
|
chart={'months': MONTHS, 'years': [{'label': str(y), 'data': monthly[y]} for y in years],
|
|
'shares': shares, 'kinds': [{'name': 'Dividenden / Ausschüttungen', 'amount': kinds.get('dividend',0)+kinds.get('distribution',0)},
|
|
{'name': 'Zinsen', 'amount': kinds.get('interest',0)}, {'name': 'Sonstiges', 'amount': kinds.get('other',0)}]})
|