Reisacher-Forderung/app/routes.py

import io
import re
from datetime import datetime, timedelta
from pathlib import Path
from typing import Annotated
from urllib.parse import unquote, urlencode

import pandas as pd
import requests
from docx import Document
from docxtpl import DocxTemplate
from fastapi import APIRouter, Depends, Form, HTTPException, Request, Response
from fastapi.responses import (
    FileResponse,
    HTMLResponse,
    RedirectResponse,
    StreamingResponse,
)
from sqlalchemy import text
from sqlalchemy.orm import Session

from app.file_storage import get_file, get_files
from app.query_filter import filter_type_option, import_filter_config

from .db import get_session
from .docuware_search import get_invoice_pdf_by_document_no
from .jinja_templates import templates
from .ldap import ldap_authenticate
from .models import Forderung

router = APIRouter()


@router.get("/", response_class=HTMLResponse)
def index(request: Request):
    return RedirectResponse(url="/login")


@router.get("/login", response_class=HTMLResponse)
def login_get(request: Request):
    return templates.TemplateResponse(request, "base/login.html", {"request": request})


@router.get("/select", response_class=HTMLResponse)
def select_get(request: Request):
    return templates.TemplateResponse(request, "base/select.html", {"request": request})


@router.post("/login")
def login_post(username: str = Form(...), password: str = Form(...)):
    user = ldap_authenticate(username, password)
    if not user:
        raise HTTPException(status_code=401, detail="Invalid credentials")
    # For a simple demo we set a cookie (not secure) -- replace with real session in production
    response = RedirectResponse(url="/forderungen", status_code=302)
    response.set_cookie("user", user["username"])
    return response


def single_quote(text: str):
    return "'" + unquote(text) + "'"


@router.get("/app/forderungen/liste", response_class=HTMLResponse)
def forderungen_liste(request: Request, db: Session = Depends(get_session), limit: int = 100, page: int = 0):
    forderung_filter_config = import_filter_config("forderungen")

    params = {
        f.name: request.query_params[f.name]
        for f in forderung_filter_config
        if request.query_params.get(f.name, "") != ""
    }
    if page == 0 and len(params) < len(request.query_params):
        return RedirectResponse(url="/app/forderungen/liste?" + urlencode(params))

    context = {}
    for f in forderung_filter_config:
        context[f.name] = {filter_type_option[f.filter_type]: single_quote(params.get(f.name, f.default_value))}
        if f.name in params:
            f.current_value = params[f.name]

    query = templates.TemplateResponse(request, "forderungen/liste/queries/forderungen_liste.sql", context).body.decode(
        "utf-8"
    )
    query_params = [
        f.query_param for f in forderung_filter_config if f.query_param is not None and f.filter_type != "hidden"
    ]
    if query_params:
        query = query.replace("1 = 1", " AND ".join(query_params))
    print(query)
    print(query_params)
    # q = db.execute(text("SELECT * FROM [dbo].[Forderungen]"))
    q_res = db.execute(text(query))
    col_names = list(q_res.keys())
    q = q_res.fetchall()

    filters = {}
    for f in forderung_filter_config:
        if f.key_column not in col_names:
            continue
        filter_temp = [
            (
                row[col_names.index(f.sort_column)] or "",
                row[col_names.index(f.key_column)],
                row[col_names.index(f.value_column)],
            )
            for row in q
        ]
        if filter_temp:
            print(filter_temp[0])
            filters[f.name] = {item[1]: item[2] for item in sorted(list(set(filter_temp)))}
        else:
            print(f"Filter {f.name} has no options, skipping")
            filters[f.name] = {}
        f.options = filters[f.name]

    summary = {
        "Beleg_offen": sum([r[col_names.index("Beleg_offen")] for r in q]),
        "Anzahl": len(q),
    }

    q_limit = q[page * limit : (page + 1) * limit]

    defaults = {}
    for f in forderung_filter_config:
        defaults[f.name] = unquote(request.query_params.get(f.name, f.default_value))
        f.current_value = defaults[f.name]

    if len(q_limit) == 0:
        return None

    template = "base/liste.html"
    if page > 0:
        template = "base/liste_tabelle.html"

    return templates.TemplateResponse(
        request,
        template,
        {
            "request": request,
            "forderungen_liste": q_limit,
            "filter": filters,
            "defaults": defaults,
            "summary": summary,
            "page": page,
            "filter_config": forderung_filter_config,
        },
    )


@router.get("/app/forderungen/details/{primary_key}", response_class=HTMLResponse)
def forderungen_details(request: Request, primary_key: str, db: Session = Depends(get_session)):

    pkey = convert_primary_key("forderungen", primary_key)

    template_context = get_template_context(request, pkey, db)
    return templates.TemplateResponse(request, "forderungen/details/details.html", template_context)


def get_template_context(request: Request, pkey: dict[str, str], db: Session) -> dict:
    context = {
        "appsmith": {
            "URL": {
                "queryParams": {
                    "Client_DB": "'" + pkey["Client_DB"] + "'",
                    "Beleg_Nr": "'" + pkey["Beleg_Nr"] + "'",
                }
            }
        },
        "BelegeFilter": {"selectedOptionValue": "D"},
        "BenutzerSelect": {"selectedOptionValue": "winter"},
    }

    template_context = {}
    for filename in [
        "auftraege_positionen",
        "forderungen_buchungen",
        "forderungen_kommentare",
        "forderungen_details",
        "forderungen_mahnungen",
        "versicherungen",
    ]:
        query = templates.TemplateResponse(request, f"forderungen/details/queries/{filename}.sql", context).body.decode(
            "utf-8"
        )
        template_context[filename] = db.execute(text(query)).fetchall()
    template_context["forderung"] = {
        "id": "12345",
        "rechnungsnummer": "WERE123445",
        "kunde": "Burghard",
        "betrag": "120,00",
        "faelligkeit": "23.12.1983",
    }
    template_context["files"] = get_files(pkey)
    template_context["add7days"] = (datetime.now() + timedelta(days=7)).strftime("%Y-%m-%d")
    return template_context


@router.post("/app/forderungen/details/{primary_key}", response_class=HTMLResponse)
async def post_forderungen_details(
    request: Request,
    primary_key: str,
    source: Annotated[str, Form()],
    redirect: Annotated[str | None, Form()] = None,
    db: Session = Depends(get_session),
):
    pkey = convert_primary_key("forderungen", primary_key)

    if source == "comments":
        data = {
            "Client_DB": "",
            "Beleg_Nr": "",
            "Timestamp": datetime.now().strftime("%Y-%m-%dT%H:%M:%S"),
            "Benutzer_ID": "winter",
            "Kommentar": "",
        }
    else:
        data = {
            "Client_DB": "",
            "Beleg_Nr": "",
            "Bearbeitet": "J",
            "Versicherung": "N",
            "Rechtsanwalt": "N",
            "Reklamation": "N",
            "Mahnen_aussetzen": "N",
            "Selbstbeteiligung": "",
            "Selbstbeteiligung_Betrag": "0.0",
            "Mwst": "",
            "Mwst_Betrag": "0.0",
            "Wiedervorlage": "",
            "Vers_Adresse_ID": "",
            "Versicherung_Typ": "",
            "Reklamation_Begruendung": "",
            "Rechtsanwalt_Begruendung": "",
            "Rechtsanwalt_Aktenzeichen": "",
            "Rechtsanwalt_Stand": "",
            "Mahnen_Begruendung": "",
        }
    data.update(pkey)

    async with request.form() as form:
        for k, v in form.items():
            if k in data:
                if v == "on":
                    v = "J"
                if re.match(r"\d+,?\d*", v):
                    v = v.replace(",", ".")
                data[k] = v

    query = templates.TemplateResponse(
        request, f"forderungen/details/queries/post/post_{source}.sql", context={"data": data}
    ).body.decode("utf-8")
    print(query)
    for subquery in query.split(";"):
        q = db.execute(text(subquery))
        print(q.rowcount)
    db.commit()
    template_context = get_template_context(request, pkey, db)

    if redirect:
        return RedirectResponse(url=redirect, status_code=302)
    if source == "comments":
        return templates.TemplateResponse(request, "base/chat_container.html", template_context)
    return templates.TemplateResponse(request, "forderungen/details/details_formular.html", template_context)


def convert_primary_key(area: str, primary_key: str) -> dict[str, str]:
    if area == "forderungen":
        pkey = ["Client_DB", "Beleg_Nr"]
    pkey_values = primary_key.split("_")
    return dict(zip(pkey, pkey_values))


@router.get("/app/forderungen/dashboard", response_class=HTMLResponse)
def forderungen_dashboard(request: Request, db: Session = Depends(get_session)):
    context = {"summary": False}
    return templates.TemplateResponse(request, "forderungen/dashboard/dashboard.html", context)


@router.get("/export/csv")
def export_csv(db: Session = Depends(get_session)):
    q = db.query(Forderung).all()
    rows = []
    for f in q:
        rows.append(
            {
                "id": f.id,
                "rechnungsnummer": f.rechnungsnummer,
                "kunde": f.kunde.name if f.kunde else "",
                "betrag": float(f.betrag),
                "faelligkeit": f.faelligkeit.isoformat() if f.faelligkeit else "",
            }
        )
    df = pd.DataFrame(rows)
    buf = io.StringIO()
    df.to_csv(buf, index=False)
    buf.seek(0)
    return Response(
        content=buf.getvalue(),
        media_type="text/csv",
        headers={"Content-Disposition": "attachment; filename=forderungen.csv"},
    )


@router.get("/export/xlsx")
def export_xlsx(db: Session = Depends(get_session)):
    q = db.query(Forderung).all()
    rows = []
    for f in q:
        rows.append(
            {
                "id": f.id,
                "rechnungsnummer": f.rechnungsnummer,
                "kunde": f.kunde.name if f.kunde else "",
                "betrag": float(f.betrag),
                "faelligkeit": f.faelligkeit.isoformat() if f.faelligkeit else "",
            }
        )
    df = pd.DataFrame(rows)
    buf = io.BytesIO()
    with pd.ExcelWriter(buf, engine="openpyxl") as writer:
        df.to_excel(writer, index=False, sheet_name="Forderungen")
    buf.seek(0)
    return StreamingResponse(
        buf,
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers={"Content-Disposition": "attachment; filename=forderungen.xlsx"},
    )


@router.get("/detail/{id}/export/docx")
def export_docx(id: int, db: Session = Depends(get_session)):
    f = db.query(Forderung).filter(Forderung.id == id).first()
    if not f:
        raise HTTPException(status_code=404)
    doc = Document()
    doc.add_heading(f"Rechnung {f.rechnungsnummer}", level=1)
    doc.add_paragraph(f'Kunde: {f.kunde.name if f.kunde else ""}')
    doc.add_paragraph(f"Betrag: {float(f.betrag)}")
    doc.add_paragraph(f"Fälligkeit: {f.faelligkeit}")
    buf = io.BytesIO()
    doc.save(buf)
    buf.seek(0)
    return StreamingResponse(
        buf,
        media_type="application/vnd.openxmlformats-officedocument.wordprocessingml.document",
        headers={"Content-Disposition": f"attachment; filename=rechnung_{f.rechnungsnummer}.docx"},
    )


# TODO
@router.get("/app/forderungen/export/{client_db}_{document_no}/")
def export_docx2(request: Request, client_db: str, document_no: str, db: Session = Depends(get_session)):
    context = {
        "appsmith": {
            "URL": {
                "queryParams": {
                    "Client_DB": "'" + client_db + "'",
                    "Document_No": "'" + document_no + "'",
                }
            }
        },
    }
    query = templates.TemplateResponse(request, "forderungen/details/queries/forderung_kopf.sql", context).body.decode(
        "utf-8"
    )
    q = db.execute(text(query)).fetchone()

    doc = DocxTemplate("templates\\forderungen\\docs\\Mahnung_AHR.docx")
    filename = Path(
        f"C:\\Projekte\\Reisacher-Fileserver\\{client_db}\\{document_no}\\Mahnung_{client_db}_{document_no}.docx"
    )
    filename.parent.mkdir(parents=True, exist_ok=True)

    context = {
        "Kunde_Name": q.Kunde,
        "Kunde_Adresse": "Im Waldhof 14a",
        "Kunde_PLZ": "61476",
        "Kunde_Ort": "Kronberg",
        "Datum_heute": datetime.now().strftime("%d.%m.%Y"),
        "Kunde_Nr": q.Kunde.split("-")[-1].strip(),
        "Fahrzeug_Kennzeichen": "OF-RB 512",
        "Rechnung_Nr": q.Document_No,
        "Betrag_SB": "250,00",
        "Betrag_USt": "380,00",
    }
    doc.render(context)
    doc.save(filename)

    return FileResponse(filename, media_type="application/octet-stream", filename=filename.name)


@router.get("/files/")
def get_files2(request: Request):
    filename = get_file(request.query_params.get("name"))
    return FileResponse(filename, media_type="application/octet-stream", filename=filename.name)


@router.get("/app/forderungen/files/{primary_key}", response_class=HTMLResponse)
def forderungen_files(request: Request, primary_key: str, db: Session = Depends(get_session)):
    pkey = convert_primary_key("forderungen", primary_key)
    try:
        filename = get_invoice_pdf_by_document_no(client_db=pkey["Client_DB"], document_no=pkey["Beleg_Nr"])
        # filename = "http://localhost:8093/static/assets/pdf/invoice.pdf"
        return StreamingResponse(
            requests.get(filename, stream=True).raw,
            media_type="application/pdf",
        )
    except ValueError as e:
        raise HTTPException(status_code=404, detail=str(e))