Fehlerbericht/app/db.py

import pandas as pd
from flask import request, Flask, json
from sqlalchemy import create_engine

app = Flask(__name__)

engine = create_engine("mysql+mysqldb://python:sqlalchemy@GC-SERVER1/tasks")


@app.route("/tickets", methods=["POST", "GET"])
def tickets():
    data = request.args
    if not data.get("id") is None:
        if data.get("id") == "":
            query = (
                f"INSERT INTO tickets (datum, benutzer, kunde, kontakt, beschreibung, termin, dauer, status, prioritaet, "
                f"fortschritt, kategorie, programm, kommentar) "
                f"VALUES ('{data['datum']}', '{data['benutzer']}', '{data['kunde']}', '{data['kontakt']}', '{data['beschreibung']}', "
                f"'{data['termin']}', '{data['dauer']}', "
                f"'{data['status']}', '{data['prioritaet']}', '{data['fortschritt']}', "
                f"'{data['kategorie']}', '{data['programm']}', '{data['kommentar']}')"
            )
        else:
            query = (
                f"UPDATE tickets SET datum = '{data['datum']}', benutzer = '{data['benutzer']}', kunde = '{data['kunde']}', "
                f"kontakt = '{data['kontakt']}', beschreibung = '{data['beschreibung']}', termin = '{data['termin']}', "
                f"dauer = '{data['dauer']}',	status = '{data['status']}', "
                f"prioritaet = '{data['prioritaet']}', fortschritt = '{data['fortschritt']}', "
                f"kategorie = '{data['kategorie']}', programm = '{data['programm']}', "
                f"kommentar = '{data['kommentar']}'  WHERE id = '{data['id']}' "
            )
        with engine.connect() as con:
            con.execute(query)

    df = pd.read_sql("SELECT * FROM tickets", engine)
    return df.to_json()


@app.route("/benutzer", methods=["POST", "GET"])
def benutzer():
    df = pd.read_sql("SELECT * FROM benutzer", engine)
    return df.to_json()


@app.route("/kunden", methods=["POST", "GET"])
def kunden():
    data = request.args
    kunde = request.args.items()
    if not data.get("id") is None:
        for kunde in data:
            if kunde.get("kunde") != "":
                query = (
                    f"UPDATE kunden SET system = '{kunde['system']}', start_soll = '{kunde['start_soll']}', "
                    f"ende_soll = '{kunde['ende_soll']}', "
                    f"erster_status = '{kunde['erster_status']}', aktiv = '{kunde['aktiv']}', woche = '{kunde['woche']}', "
                    f"bundesland = '{kunde['bundesland']}', plz = '{kunde['plz']}', ort = '{kunde['ort']}' "
                    f"WHERE kunde = '{kunde['kunde']}' "
                )

            with engine.connect() as con:
                con.execute(query)

    df = pd.read_sql("SELECT * FROM kunden", engine)
    return df.to_json()


@app.route("/fehlerbericht", methods=["POST", "GET"])
def fehlerbericht():
    data = request.args
    kunde = request.args.get("kunde")
    datum = request.args.get("datum")
    start = request.args.get("start")
    if data.get("id") is None:
        query = (
            f"SELECT sm.*, k.whitelist FROM status_meldung sm INNER JOIN kunden k USING (kunde) "
            f"WHERE sm.datum = '{datum}' AND sm.kunde = '{kunde}' AND sm.start = '{start}' LIMIT 1"
        )
        df = pd.read_sql(query, engine)
        df["fehlerbericht"] = df["fehlerbericht"].apply(json.loads)
        # df["whitelist"] = df["whitelist"].apply(json.loads)
        if df.get("kommentar_id") != "":
            query = f"SELECT sk.* FROM status_kommentar sk WHERE sk.id = '{df['kommentar_id']}' "
        else:
            query = (
                f"SELECT sk.* FROM status_kommentar sk "
                f"WHERE sk.datum = '{datum}' AND sk.kunde = '{kunde}' AND sk.start = '{start}' "
                "ORDER BY cdate DESC LIMIT 1"
            )
        with engine.connect() as con:
            con.execute(query)

    response = app.response_class(
        response=df.to_json(indent=2), status=200, mimetype="application/json"
    )

    return response


@app.route("/whitelist", methods=["POST", "GET"])
def whitelist():
    kunde = request.args.get("kunde")
    pd.read_sql(f"SELECT * FROM kunden WHERE kunde = '{kunde}' ", engine)


@app.route("/zeit", methods=["POST", "GET"])
def zeit():
    start_soll = request.args.get("start_soll")
    ende_soll = request.args.get("ende_soll")
    kunde = request.args.get("kunde")
    with engine.connect() as con:
        con.execute(
            f"UPDATE kunden SET start_soll = '{start_soll}', ende_soll = '{ende_soll}' WHERE kunde = '{kunde}'"
        )

    return 1


@app.route("/kommentar", methods=["POST", "GET"])
def kommentar():
    pass


@app.route("/config", methods=["POST", "GET"])
def config():
    df = pd.read_sql(
        "SELECT kunde, datum FROM kunden_config ORDER BY 1, 2 DESC", engine
    )
    return df.to_json


@app.route("/liste", methods=["POST", "GET"])
def liste():
    datum_req = request.args.get("datum")
    if not datum_req or datum_req == "":
        datum = "a.datum > date_add(now(), INTERVAL -32 DAY)"
    else:
        datum = f"a.datum >= '{datum_req}' "
    datum_bis_req = request.args.get("datum_bis")
    if not datum_bis_req or datum_bis_req == "":
        datum_bis = "a.datum > date_add(now(), INTERVAL -32 DAY)"
    else:
        datum_bis = f"a.datum >= '{datum_bis_req}' "
    kunde_req = request.args.get("kunde")
    if not kunde_req or kunde_req == "":
        kunde = "1"
    else:
        kunde = f"k.kunde LIKE '%{kunde_req}%'"

    query = (
        f"SELECT DISTINCT a.datum, k.kunde, sm.aufgabe, k.system, a.bundesland, a.feiertag, k.start_soll, k.ende_soll, "
        f"sm.start, sm.ende, sm.anzahl, if(sk2.id IS NULL, sm.bearbeitet, '1') as bearbeitet,"
        f" case"
        f" when woche = 'Mo-Fr' then mofr"
        f" when woche = 'Mo-Sa' then mosa"
        f" when woche = 'Mo-So' then moso"
        f" when woche = 'Di-So' then diso"
        f" else 1"
        f" end * if(sk.status <= 1, 0, 1) * if(sm.anzahl > 0 or (sm.anzahl is null and now() > date_add(concat(a.datum, ' ', k.ende_soll), "
        f"INTERVAL 1 HOUR)), 1, 0) as fehler,"
        f" if(sm.anzahl is null and now() > date_add(concat(a.datum, ' ', k.ende_soll), INTERVAL 1 HOUR), 1, 0) as fehlend,"
        f" ifnull(sk.benutzer, '') as benutzer"
        f" FROM arbeitstage a"
        f" INNER JOIN kunden k USING (bundesland)"
        f" LEFT JOIN status_meldung sm USING (datum, kunde)"
        f" LEFT JOIN status_kommentar sk ON sm.kommentar_id = sk.id"
        f" LEFT JOIN status_kommentar as sk2 ON a.datum = sk2.datum AND k.kunde = sk2.kunde"
        f" WHERE {datum} AND {datum_bis} AND {kunde} AND a.datum <= now() AND k.erster_status <= a.datum"
        f" AND k.aktiv = 1"
        f" ORDER BY a.datum, k.ende_soll"
    )

    df = pd.read_sql(query, engine)
    return df.to_json()


@app.route("/events", methods=["POST", "GET"])
def events():
    pass


if __name__ == "__main__":
    app()