routes.py 18 KB

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