routes.py 18 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458
  1. import io
  2. import logging
  3. import re
  4. import secrets
  5. from datetime import datetime, timedelta
  6. from pathlib import Path
  7. from typing import Annotated
  8. from urllib.parse import unquote, urlencode
  9. from docxtpl import DocxTemplate
  10. from fastapi import APIRouter, Depends, Form, HTTPException, Request, status
  11. from fastapi.responses import (
  12. FileResponse,
  13. HTMLResponse,
  14. RedirectResponse,
  15. StreamingResponse,
  16. )
  17. from sqlalchemy import text
  18. from sqlalchemy.orm import Session
  19. from app.file_storage import get_file, get_files
  20. from app.query_filter import import_filter_config
  21. from .auth import clear_session_cookie, safe_next_url, set_session_cookie
  22. from .config import settings
  23. from .db import get_session
  24. from .docuware_search import get_invoice_pdf_by_document_no
  25. from .forderung_history import HistoryScope, history_context, section_context
  26. from .forderung_summary import get_forderung_summary
  27. from .jinja_templates import templates
  28. from .ldap import DirectoryUnavailable, DirectoryUser, ldap_authenticate, normalize_username
  29. from .oauth_token import create_access_token
  30. from .permissions import (
  31. current_principal,
  32. ensure_forderung_access,
  33. highest_role,
  34. has_forderung_access,
  35. load_entitlements,
  36. )
  37. router = APIRouter()
  38. logger = logging.getLogger(__name__)
  39. @router.get("/", response_class=HTMLResponse)
  40. def index(request: Request):
  41. return RedirectResponse(url="/select")
  42. @router.get("/health")
  43. def health():
  44. return {"status": "ok"}
  45. def _login_response(request: Request, next: str | None, error: str | None = None, status_code: int = 200):
  46. # Keep open login forms valid when another tab loads the login page.
  47. csrf = request.cookies.get(settings.LOGIN_CSRF_COOKIE_NAME, "")
  48. if not re.fullmatch(r"[A-Za-z0-9_-]{43}", csrf):
  49. csrf = secrets.token_urlsafe(32)
  50. response = templates.TemplateResponse(
  51. request,
  52. "base/login.html",
  53. {"request": request, "csrf": csrf, "next": safe_next_url(next), "error": error},
  54. status_code=status_code,
  55. headers={"Cache-Control": "no-store"},
  56. )
  57. response.set_cookie(
  58. settings.LOGIN_CSRF_COOKIE_NAME,
  59. csrf,
  60. secure=settings.COOKIE_SECURE,
  61. httponly=True,
  62. samesite="lax",
  63. path="/login",
  64. )
  65. return response
  66. @router.get("/login", response_class=HTMLResponse)
  67. def login_get(request: Request, next: str | None = None):
  68. if getattr(request.state, "principal", None):
  69. return RedirectResponse(safe_next_url(next), status_code=303)
  70. return _login_response(request, next)
  71. @router.post("/login")
  72. def login_post(
  73. request: Request,
  74. username: Annotated[str, Form()],
  75. password: Annotated[str, Form()],
  76. csrf: Annotated[str, Form(alias="_csrf")] = "",
  77. next: Annotated[str | None, Form()] = None,
  78. db: Session = Depends(get_session),
  79. ):
  80. if getattr(request.state, "principal", None):
  81. return RedirectResponse(safe_next_url(next), status_code=303)
  82. expected_csrf = request.cookies.get(settings.LOGIN_CSRF_COOKIE_NAME, "")
  83. if not expected_csrf or not secrets.compare_digest(expected_csrf, csrf):
  84. return _login_response(
  85. request,
  86. next,
  87. error="Das Anmeldeformular ist nicht mehr gültig. Bitte melden Sie sich erneut an. "
  88. "Falls der Fehler erneut auftritt, prüfen Sie, ob Cookies für diese Website erlaubt sind.",
  89. status_code=status.HTTP_403_FORBIDDEN,
  90. )
  91. try:
  92. if settings.debug_auth_enabled:
  93. user = None
  94. if username and secrets.compare_digest(username.encode("utf-8"), password.encode("utf-8")):
  95. normalized = normalize_username(username)
  96. user = DirectoryUser(
  97. username=normalized,
  98. distinguished_name="",
  99. name=normalized,
  100. email="",
  101. )
  102. else:
  103. user = ldap_authenticate(username, password)
  104. except DirectoryUnavailable:
  105. logger.exception("LDAP-Anmeldedienst nicht verfügbar")
  106. return _login_response(request, next, error="Anmeldedienst ist derzeit nicht verfügbar", status_code=503)
  107. except ValueError:
  108. user = None
  109. if not user:
  110. return _login_response(request, next, error="Anmeldung fehlgeschlagen", status_code=401)
  111. entitlements = load_entitlements(db, user.username)
  112. role = highest_role(entitlements)
  113. token = create_access_token(
  114. username=user.username,
  115. name=user.name,
  116. email=user.email,
  117. role=role,
  118. auth_source="debug" if settings.debug_auth_enabled else "ldap",
  119. )
  120. response = RedirectResponse(url=safe_next_url(next), status_code=303)
  121. set_session_cookie(response, token)
  122. response.delete_cookie(settings.LOGIN_CSRF_COOKIE_NAME, path="/login")
  123. return response
  124. @router.post("/logout")
  125. def logout(request: Request):
  126. request.state.suppress_session_refresh = True
  127. response = templates.TemplateResponse(request, "base/logout.html", {"request": request})
  128. clear_session_cookie(response)
  129. return response
  130. @router.get("/select", response_class=HTMLResponse)
  131. def select_get(request: Request):
  132. return templates.TemplateResponse(request, "base/select.html", {"request": request})
  133. @router.get("/app/forderungen/liste", response_class=HTMLResponse)
  134. def forderungen_liste(request: Request, db: Session = Depends(get_session), limit: int = 100, page: int = 0):
  135. principal = current_principal(request)
  136. forderung_filter_config = import_filter_config("forderungen")
  137. params = {
  138. f.name: request.query_params[f.name]
  139. for f in forderung_filter_config
  140. if request.query_params.get(f.name, "") != ""
  141. }
  142. if page == 0 and len(params) < len(request.query_params):
  143. return RedirectResponse(url="/app/forderungen/liste?" + urlencode(params))
  144. query_clauses = []
  145. query_values = {"auth_username": principal.username}
  146. for f in forderung_filter_config:
  147. f.current_value = unquote(params.get(f.name, f.default_value))
  148. if f.filter_type == "hidden" or not f.current_value:
  149. continue
  150. parameter = f"filter_{f.name.lower()}"
  151. if f.filter_type == "date":
  152. operator = "<=" if "bis" in f.name.lower() else ">="
  153. query_clauses.append(f"F.[{f.key_column}] {operator} :{parameter}")
  154. query_values[parameter] = f.current_value
  155. else:
  156. query_clauses.append(f"F.[{f.key_column}] LIKE :{parameter}")
  157. query_values[parameter] = f"%{f.current_value}%"
  158. query = templates.TemplateResponse(request, "forderungen/liste/queries/forderungen_liste.sql", {}).body.decode(
  159. "utf-8"
  160. )
  161. if query_clauses:
  162. query = query.replace("AND 1 = 1", "AND 1 = 1 AND " + " AND ".join(query_clauses))
  163. q_res = db.execute(text(query), query_values)
  164. col_names = list(q_res.keys())
  165. q = q_res.fetchall()
  166. filters = {}
  167. for f in forderung_filter_config:
  168. if f.key_column not in col_names:
  169. continue
  170. filter_temp = [
  171. (
  172. row[col_names.index(f.sort_column)] or "",
  173. row[col_names.index(f.key_column)],
  174. row[col_names.index(f.value_column)],
  175. )
  176. for row in q
  177. ]
  178. if filter_temp:
  179. filters[f.name] = {item[1]: item[2] for item in sorted(list(set(filter_temp))) if item[1]}
  180. else:
  181. filters[f.name] = {}
  182. f.options = filters[f.name]
  183. summary = {
  184. "Beleg_offen": sum([r[col_names.index("Beleg_offen")] for r in q]),
  185. "Anzahl": len(q),
  186. }
  187. q_limit = q[page * limit : (page + 1) * limit]
  188. defaults = {}
  189. for f in forderung_filter_config:
  190. defaults[f.name] = unquote(request.query_params.get(f.name, f.default_value))
  191. f.current_value = defaults[f.name]
  192. if len(q_limit) == 0:
  193. return None
  194. template = "base/liste.html"
  195. if page > 0:
  196. template = "base/liste_tabelle.html"
  197. return templates.TemplateResponse(
  198. request,
  199. template,
  200. {
  201. "request": request,
  202. "forderungen_liste": q_limit,
  203. "filter": filters,
  204. "defaults": defaults,
  205. "summary": summary,
  206. "page": page,
  207. "filter_config": forderung_filter_config,
  208. },
  209. )
  210. @router.get("/app/forderungen/details/{primary_key}", response_class=HTMLResponse)
  211. def forderungen_details(request: Request, primary_key: str, db: Session = Depends(get_session)):
  212. principal = current_principal(request)
  213. pkey = convert_primary_key("forderungen", primary_key)
  214. if not has_forderung_access(db, principal, pkey["Client_DB"], pkey["Beleg_Nr"]):
  215. context = get_forderung_summary(db, pkey["Client_DB"], pkey["Beleg_Nr"])
  216. return templates.TemplateResponse(request, "forderungen/details/notice.html", context)
  217. template_context = get_template_context(request, pkey, db)
  218. return templates.TemplateResponse(request, "forderungen/details/details.html", template_context)
  219. def get_template_context(request: Request, pkey: dict[str, str], db: Session) -> dict:
  220. template_context = {}
  221. for filename in [
  222. "auftraege_positionen",
  223. "forderungen_details",
  224. "forderungen_mahnungen",
  225. "versicherungen",
  226. ]:
  227. query = templates.TemplateResponse(request, f"forderungen/details/queries/{filename}.sql", {}).body.decode(
  228. "utf-8"
  229. )
  230. values = {"client_db": pkey["Client_DB"], "beleg_nr": pkey["Beleg_Nr"]}
  231. template_context[filename] = db.execute(text(query), values).fetchall()
  232. for section in ("kommentare", "buchungen"):
  233. template_context.update(section_context(db, pkey, template_context["forderungen_details"][0], section))
  234. template_context["files"] = get_files(pkey)
  235. template_context["add7days"] = (datetime.now() + timedelta(days=7)).strftime("%Y-%m-%d")
  236. return template_context
  237. @router.get("/app/forderungen/details/{primary_key}/kommentare", response_class=HTMLResponse)
  238. def forderungen_kommentare(
  239. request: Request, primary_key: str, scope: HistoryScope = "invoice", db: Session = Depends(get_session)
  240. ):
  241. pkey = convert_primary_key("forderungen", primary_key)
  242. ensure_forderung_access(db, current_principal(request), pkey["Client_DB"], pkey["Beleg_Nr"])
  243. context = history_context(db, pkey, "kommentare", scope)
  244. return templates.TemplateResponse(request, "forderungen/details/details_kommentare.html", context)
  245. @router.get("/app/forderungen/details/{primary_key}/buchungen", response_class=HTMLResponse)
  246. def forderungen_buchungen(
  247. request: Request, primary_key: str, scope: HistoryScope = "invoice", db: Session = Depends(get_session)
  248. ):
  249. pkey = convert_primary_key("forderungen", primary_key)
  250. ensure_forderung_access(db, current_principal(request), pkey["Client_DB"], pkey["Beleg_Nr"])
  251. context = history_context(db, pkey, "buchungen", scope)
  252. return templates.TemplateResponse(request, "forderungen/details/details_buchungen.html", context)
  253. @router.post("/app/forderungen/details/{primary_key}", response_class=HTMLResponse)
  254. async def post_forderungen_details(
  255. request: Request,
  256. primary_key: str,
  257. source: Annotated[str, Form()],
  258. redirect: Annotated[str | None, Form()] = None,
  259. comment_scope: Annotated[HistoryScope, Form()] = "invoice",
  260. db: Session = Depends(get_session),
  261. ):
  262. principal = current_principal(request)
  263. pkey = convert_primary_key("forderungen", primary_key)
  264. ensure_forderung_access(db, principal, pkey["Client_DB"], pkey["Beleg_Nr"])
  265. if source == "comments":
  266. data = {
  267. "Client_DB": "",
  268. "Beleg_Nr": "",
  269. "Timestamp": datetime.now().strftime("%Y-%m-%dT%H:%M:%S"),
  270. "Benutzer_ID": principal.username,
  271. "Kommentar": "",
  272. }
  273. else:
  274. data = {
  275. "Client_DB": "",
  276. "Beleg_Nr": "",
  277. "Bearbeitet": "J",
  278. "Versicherung": "N",
  279. "Rechtsanwalt": "N",
  280. "Reklamation": "N",
  281. "Mahnen_aussetzen": "N",
  282. "Selbstbeteiligung": "",
  283. "Selbstbeteiligung_Betrag": "0.0",
  284. "Mwst": "",
  285. "Mwst_Betrag": "0.0",
  286. "Wiedervorlage": "",
  287. "Vers_Adresse_ID": "",
  288. "Versicherung_Typ": "",
  289. "Reklamation_Begruendung": "",
  290. "Rechtsanwalt_Begruendung": "",
  291. "Rechtsanwalt_Aktenzeichen": "",
  292. "Rechtsanwalt_Stand": "",
  293. "Mahnen_Begruendung": "",
  294. }
  295. data.update(pkey)
  296. async with request.form() as form:
  297. for k, v in form.items():
  298. if k in data and k not in {"Client_DB", "Beleg_Nr", "Benutzer_ID", "Timestamp"}:
  299. if v == "on":
  300. v = "J"
  301. if re.match(r"\d+,?\d*", v):
  302. v = v.replace(",", ".")
  303. data[k] = v
  304. if source not in {"comments", "details"}:
  305. raise HTTPException(status_code=400, detail="Ungültige Datenquelle")
  306. query = templates.TemplateResponse(
  307. request, f"forderungen/details/queries/post/post_{source}.sql", context={"data": data}
  308. ).body.decode("utf-8")
  309. for subquery in query.split(";"):
  310. if subquery.strip():
  311. db.execute(text(subquery), data)
  312. db.commit()
  313. if redirect:
  314. return RedirectResponse(url=redirect, status_code=302)
  315. if source == "comments":
  316. context = history_context(db, pkey, "kommentare", comment_scope)
  317. return templates.TemplateResponse(request, "forderungen/details/details_kommentare.html", context)
  318. template_context = get_template_context(request, pkey, db)
  319. return templates.TemplateResponse(request, "forderungen/details/details_formular.html", template_context)
  320. def convert_primary_key(area: str, primary_key: str) -> dict[str, str]:
  321. if area != "forderungen" or "_" not in primary_key:
  322. raise HTTPException(status_code=400, detail="Ungültiger Primärschlüssel")
  323. client_db, beleg_nr = primary_key.split("_", 1)
  324. if not client_db or not beleg_nr or len(client_db) > 20 or len(beleg_nr) > 100:
  325. raise HTTPException(status_code=400, detail="Ungültiger Primärschlüssel")
  326. return {"Client_DB": client_db, "Beleg_Nr": beleg_nr}
  327. @router.get("/app/forderungen/dashboard", response_class=HTMLResponse)
  328. def forderungen_dashboard(request: Request, db: Session = Depends(get_session)):
  329. current_principal(request)
  330. context = {"summary": False}
  331. return templates.TemplateResponse(request, "forderungen/dashboard/dashboard.html", context)
  332. # TODO
  333. @router.get("/app/forderungen/export/{client_db}_{document_no}/")
  334. def export_docx2(request: Request, client_db: str, document_no: str, db: Session = Depends(get_session)):
  335. principal = current_principal(request)
  336. ensure_forderung_access(db, principal, client_db, document_no)
  337. q = (
  338. db.execute(
  339. text("""SELECT TOP 1 F.*, K.Kunde_Name, K.Kunde_Adresse, K.Kunde_PLZ, K.Kunde_Ort
  340. FROM dbo.Forderungen_Kopf AS F
  341. LEFT JOIN dbo.Kunden AS K ON K.Client_DB = F.Client_DB AND K.Kunde_Nr = F.Kunde_Nr
  342. WHERE F.Client_DB = :client_db AND F.Beleg_Nr = :beleg_nr"""),
  343. {"client_db": client_db, "beleg_nr": document_no},
  344. )
  345. .mappings()
  346. .first()
  347. )
  348. if not q or not settings.LOCAL_STORAGE:
  349. raise HTTPException(status_code=503, detail="Dokumentablage ist nicht konfiguriert")
  350. doc = DocxTemplate("templates\\forderungen\\details\\docs\\Mahnung_AHR.docx")
  351. storage_root = Path(settings.LOCAL_STORAGE).resolve()
  352. filename = (storage_root / client_db / document_no / f"Mahnung_{client_db}_{document_no}.docx").resolve()
  353. if storage_root not in filename.parents:
  354. raise HTTPException(status_code=400, detail="Ungültiger Dokumentpfad")
  355. filename.parent.mkdir(parents=True, exist_ok=True)
  356. context = {
  357. "Kunde_Name": q["Kunde_Name"] or "",
  358. "Kunde_Adresse": q["Kunde_Adresse"] or "",
  359. "Kunde_PLZ": q["Kunde_PLZ"] or "",
  360. "Kunde_Ort": q["Kunde_Ort"] or "",
  361. "Datum_heute": datetime.now().strftime("%d.%m.%Y"),
  362. "Kunde_Nr": q["Kunde_Nr"] or "",
  363. "Fahrzeug_Kennzeichen": q.get("Fahrzeug_Kennzeichen", "") or "",
  364. "Rechnung_Nr": q["Beleg_Nr"],
  365. "Betrag_SB": q.get("Selbstbeteiligung_Betrag", "") or "",
  366. "Betrag_USt": q.get("Mwst_Betrag", "") or "",
  367. }
  368. doc.render(context)
  369. doc.save(filename)
  370. return FileResponse(filename, media_type="application/octet-stream", filename=filename.name)
  371. @router.get("/files/")
  372. def get_files2(request: Request, db: Session = Depends(get_session)):
  373. principal = current_principal(request)
  374. name = request.query_params.get("name", "")
  375. parts = Path(unquote(name)).parts
  376. if len(parts) < 3:
  377. raise HTTPException(status_code=404, detail="Datei nicht gefunden")
  378. ensure_forderung_access(db, principal, parts[0], parts[1])
  379. try:
  380. filename = get_file(name)
  381. except FileNotFoundError:
  382. raise HTTPException(status_code=404, detail="Datei nicht gefunden")
  383. return FileResponse(filename, media_type="application/octet-stream", filename=filename.name)
  384. @router.get("/app/forderungen/files/{primary_key}", response_class=StreamingResponse)
  385. def forderungen_files(request: Request, primary_key: str, db: Session = Depends(get_session)):
  386. principal = current_principal(request)
  387. pkey = convert_primary_key("forderungen", primary_key)
  388. ensure_forderung_access(db, principal, pkey["Client_DB"], pkey["Beleg_Nr"])
  389. try:
  390. file_content = get_invoice_pdf_by_document_no(client_db=pkey["Client_DB"], document_no=pkey["Beleg_Nr"])
  391. filestream = io.BytesIO(file_content)
  392. except Exception:
  393. logger.exception("Rechnung konnte nicht aus DocuWare geladen werden")
  394. filestream = io.BytesIO(Path("static/assets/pdf/invoice.pdf").read_bytes())
  395. return StreamingResponse(
  396. filestream,
  397. media_type="application/pdf",
  398. )