rene
be9f263e0d
- landing.html: neues Stats-Band-Element 'Tagebuch-Einträge' (#big-diary) mit Wert aus diary_entries (war bereits im API-Response vorhanden) - stats.py km-Query: explizit WHERE is_valid=1 (kein is_public-Filter — private Routen werden mitgezählt, nur ungültige Aufzeichnungen ausgeschlossen) - SW by-v998, APP_VER 998
79 lines
2.7 KiB
Python
79 lines
2.7 KiB
Python
import time
|
|
from fastapi import APIRouter, Depends
|
|
from database import db
|
|
from auth import get_current_user, get_current_user_optional
|
|
|
|
router = APIRouter()
|
|
|
|
_STATS_SQL = """
|
|
SELECT u.id, u.name, u.avatar_url,
|
|
ROUND(COALESCE(SUM(r.distanz_km), 0), 1) AS total_km,
|
|
COUNT(DISTINCT r.id) AS routen,
|
|
COUNT(DISTINCT p.id) AS pois,
|
|
ROUND(COALESCE(SUM(r.distanz_km), 0), 1) * 1
|
|
+ COUNT(DISTINCT p.id) * 5
|
|
+ COUNT(DISTINCT r.id) * 10 AS punkte
|
|
FROM users u
|
|
LEFT JOIN routes r ON r.user_id = u.id AND r.is_valid = 1
|
|
LEFT JOIN user_map_pois p ON p.user_id = u.id
|
|
GROUP BY u.id
|
|
"""
|
|
|
|
|
|
_pub_cache: dict = {"data": None, "ts": 0.0}
|
|
_PUB_TTL = 300 # 5 Minuten
|
|
|
|
|
|
@router.get("/public")
|
|
async def public_stats():
|
|
now = time.time()
|
|
if _pub_cache["data"] and now - _pub_cache["ts"] < _PUB_TTL:
|
|
return _pub_cache["data"]
|
|
with db() as conn:
|
|
users = conn.execute("SELECT COUNT(*) FROM users").fetchone()[0]
|
|
dogs = conn.execute("SELECT COUNT(*) FROM dogs").fetchone()[0]
|
|
km = conn.execute(
|
|
# Alle Routen (öffentlich + privat), nur valide Aufzeichnungen
|
|
"SELECT ROUND(COALESCE(SUM(distanz_km),0),0) FROM routes WHERE is_valid=1"
|
|
).fetchone()[0]
|
|
posts = conn.execute("SELECT COUNT(*) FROM forum_posts").fetchone()[0]
|
|
diary = conn.execute("SELECT COUNT(*) FROM diary").fetchone()[0]
|
|
kotbeutel = conn.execute(
|
|
"SELECT COUNT(*) FROM osm_pois WHERE type='waste_basket'"
|
|
).fetchone()[0]
|
|
data = {
|
|
"users": users,
|
|
"dogs": dogs,
|
|
"km": int(km or 0),
|
|
"forum_posts": posts,
|
|
"diary_entries": diary,
|
|
"kotbeutel": kotbeutel,
|
|
}
|
|
_pub_cache["data"] = data
|
|
_pub_cache["ts"] = now
|
|
return data
|
|
|
|
|
|
@router.get("/leaderboard")
|
|
async def leaderboard(_user=Depends(get_current_user_optional)):
|
|
with db() as conn:
|
|
rows = conn.execute(f"""
|
|
SELECT * FROM ({_STATS_SQL})
|
|
ORDER BY punkte DESC, total_km DESC
|
|
LIMIT 20
|
|
""").fetchall()
|
|
return [dict(r) for r in rows]
|
|
|
|
|
|
@router.get("/me")
|
|
async def my_stats(user=Depends(get_current_user)):
|
|
with db() as conn:
|
|
row = conn.execute(f"""
|
|
SELECT s.*, rank_tbl.rang FROM ({_STATS_SQL}) s
|
|
JOIN (
|
|
SELECT id, ROW_NUMBER() OVER (ORDER BY punkte DESC, total_km DESC) AS rang
|
|
FROM ({_STATS_SQL})
|
|
) rank_tbl ON rank_tbl.id = s.id
|
|
WHERE s.id = ?
|
|
""", (user["id"],)).fetchone()
|
|
return dict(row) if row else {}
|