planner/webservice/csv_accounts.py

import pandas as pd
import numpy as np
from re import match
import json
import plac
from pathlib import Path


@plac.pos("period", "", type=str)
def actuals(period):
    base_dir = Path(__file__).parent.parent.resolve()
    print(base_dir)
    df1 = pd.read_csv(
        base_dir / "data/Belege_Planung_Ist_FC_AHR.csv", sep=";", decimal=",", dtype={0: str, 1: str, 2: str, 3: float}
    )
    df2 = pd.read_csv(
        base_dir / "data/Belege_Planung_Ist_FC_AHA.csv", sep=";", decimal=",", dtype={0: str, 1: str, 2: str, 3: float}
    )
    df12 = pd.concat([df1, df2])
    df3 = pd.read_csv(
        base_dir / "data/NW_GW_Stk_Planung_AHR.csv", sep=";", decimal=",", dtype={0: str, 1: str, 2: str, 3: float}
    )
    df4 = pd.read_csv(
        base_dir / "data/NW_GW_Stk_Planung_AHA.csv", sep=";", decimal=",", dtype={0: str, 1: str, 2: str, 3: float}
    )
    df34 = pd.concat([df3, df4])
    df = pd.merge(df12, df34, how="left", on=["Bookkeep_Period", "Betrieb_Nr", "Konto_Nr"])
    # df = pd.read_csv('Planung/Belege_Planung_Ist_FC_Dresen.csv', sep=';', decimal=',',
    #                  dtype={0: str, 1: str, 2: str, 3: str, 4: str, 5: float, 6: float})
    df["Jahr"] = df["Bookkeep_Period"].apply(lambda x: x[:4])
    current_year = period[:4]
    prev_year = str(int(current_year) - 1)
    next_year = str(int(current_year) + 1)
    month_no = int(period[4:])
    # df = df[df['Bookkeep_Period'] <= period]
    df["PY"] = np.where(df["Jahr"] == prev_year, df["Betrag"], 0)
    df["PYQ"] = np.where(df["Jahr"] == prev_year, df["Menge"], 0)
    df["CY"] = np.where(df["Jahr"] == current_year, df["Betrag"], 0)
    df["CYQ"] = np.where(df["Jahr"] == current_year, df["Menge"], 0)
    df["YTD"] = np.where(df["Bookkeep_Period"] <= period, df["CY"], 0)
    df["YTDQ"] = np.where(df["Bookkeep_Period"] <= period, df["CYQ"], 0)
    df["FC"] = df["YTD"] * 12 / month_no
    df["FCQ"] = df["YTDQ"] * 12 / month_no
    df.drop(columns=["Menge", "Betrag", "Jahr", "Bookkeep_Period"], inplace=True)

    # df2 = pd.pivot_table(df, values='Betrag', index=['Konto Nr', 'Betrieb Nr'],
    # columns=['Jahr'], aggfunc=np.sum, fill_value=0.0)
    df = df.groupby(["Konto_Nr", "Betrieb_Nr"]).sum()
    print(df.head())

    res = {}
    for (acct, dept), values in df.to_dict(orient="index").items():
        if acct not in res:
            res[acct] = {}
        res[acct][dept] = [round(v, 2) for v in values.values()]

    data = {"values": res}
    json.dump(data, open(base_dir / f"export/accounts_{next_year}.json", "w"), indent=2)


@plac.pos("year", "", type=str)
def planning_prev(year):
    base_dir = Path(__file__).parent.parent.resolve()
    print(base_dir)
    df1 = pd.read_csv(
        base_dir / f"data/Planner_{year}_V1_Plan.csv",
        sep=";",
        decimal=",",
        encoding="latin-1",
        dtype={"Betrieb Nr": str, "Bereich": str, "Zeile": str},
    )
    df1["Wert"] = df1["Gesamt"] + df1["Periode13"]
    df1["Wert"] = np.where(df1["Vstufe 1"] == "Umsatzerlöse", df1["Wert"], df1["Wert"] * -1)
    df1["Menge"] = 0
    df1 = df1[["Jahr", "Betrieb Nr", "Vstufe 1", "Bereich", "Zeile", "Konto", "Version", "Wert", "Menge"]]

    df2 = pd.read_csv(
        base_dir / f"data/Planner_{year}_V1_Stk.csv",
        sep=";",
        decimal=",",
        encoding="latin-1",
        dtype={"Betrieb Nr": str, "Bereich": str, "Zeile": str},
    )
    df2.rename(columns={"Gesamt": "Menge"}, inplace=True)
    df2["Wert"] = 0
    df2["Vstufe 1"] = "Umsatzerlöse"
    df2 = df2[["Jahr", "Betrieb Nr", "Vstufe 1", "Bereich", "Zeile", "Konto", "Version", "Wert", "Menge"]]

    df = pd.concat([df1, df2])

    df["Bereich"] = df["Bereich"].fillna("NA").replace("VW (inkl. GF)", "?")
    df["regex"] = df["Vstufe 1"] + ";" + df["Bereich"] + ";.*" + df["Zeile"] + " - [^;]*;;"

    gcstruct = json.load(open(base_dir / "export/gcstruct.json", "r"))
    structure_ids = [s["id"] for s in gcstruct["flat"]["Struktur_FB"]]

    df["id"] = df["regex"].apply(lambda x: (list(filter(lambda y: match(x, y), structure_ids)) + [""])[0])
    df["order_by"] = df.index
    df = df[df["id"] != ""]

    df_grouped = df.groupby(by=["id", "Betrieb Nr"], sort=False).aggregate("sum").reset_index()
    df_grouped = df_grouped[df_grouped["Wert"] != 0]  # .sort_values(by="order_by")

    res = {}
    for item in df_grouped.to_dict(orient="records"):
        if item["id"] not in res:
            res[item["id"]] = {}
        res[item["id"]][item["Betrieb Nr"]] = [item["Wert"], item["Menge"]]
    data = {"values": res}
    next_year = str(int(year) + 1)
    json.dump(data, open(base_dir / f"export/planning_{next_year}.json", "w"), indent=2)


def planning_new(filename):
    with open("planner/export/" + filename, "r") as frh:
        structure = json.load(frh)
    year = str(int(filename[:4]) + 1)
    result = {}
    for s in structure:
        if len(s["accounts"]) == 0:
            continue
        result[s["id"]] = dict([(k, [v[10], v[5]]) for k, v in s["values2"].items()])

    with open(f"planner/export/planning_{year}.json", "w") as fwh:
        json.dump({"values": result}, fwh, indent=2)


if __name__ == "__main__":
    actuals("202310")
    planning_prev("2023")
    # plac.call(actuals)
    # planning_new('2022_V2_20220407150009.json')