Python-Projekte/gcstruct/gcstruct.py

import pandas as pd
import numpy as np
import xml.etree.ElementTree as ET
import json
import csv
import re
import chevron

# from shutil import copyfile
from bs4 import BeautifulSoup
from functools import reduce
from pathlib import Path


def get_flat(node):
    result = [
        {
            "id": node["id"],
            "text": node["text"],
            "children": [x["id"] for x in node["children"]],
            "children2": [],
            "parents": node["parents"],
            "accounts": node["accounts"],
            "costcenter": "",
            "level": node["level"],
            "drilldown": node["level"] < 2,  # (node['level'] != 2 and len(node['accounts']) == 0),
            "form": node["form"],
            "accountlevel": False,
            "absolute": True,
            "seasonal": True,
            "status": "0",
            "values": [],
            "values2": {},
        }
    ]
    for child in node["children"]:
        result += get_flat(child)
    return result


def get_parents_list(p_list):
    id = ";".join(p_list) + ";" * (10 - len(p_list))
    if len(p_list) > 0:
        return [id] + get_parents_list(p_list[:-1])
    return [";" * 9]


def structure_from_tree(node):
    result = []
    result.append(node["id"])
    for child in node["children"]:
        result.extend(structure_from_tree(child))
    return result


def xml_from_tree(xml_node, tree_node):
    for child in tree_node["children"]:
        element = ET.SubElement(xml_node, "Ebene")
        element.set("Name", child["text"])
        xml_from_tree(element, child)


def split_it(text, index):
    try:
        return re.findall(r"([^;]+) - ([^;]*);;", text)[0][index]
    except Exception:
        return ""


def last_layer(text):
    try:
        return re.findall(r"([^;]+);;", text)[0]
    except Exception:
        return ""


def get_default_cols(i):
    return ["Ebene" + str(i) for i in range(i * 10 + 1, (i + 1) * 10 + 1)]


def get_structure_exports(s):
    result = {
        "files": {},
        "format": {
            "KontoFormat": "{0} - {1}",
            "HerstellerkontoFormat": "{{Herstellerkonto_Nr}}",
            "HerstellerBezeichnungFormat": "{{Herstellerkonto_Bez}}",
            "NeueHerstellerkontenAnlegen": False,
        },
    }
    export_files = [
        "ExportStk",
        "ExportStrukturenStk",
        "ExportAdjazenz",
        "ExportUebersetzung",
        "ExportUebersetzungStk",
        "ExportHerstellerKontenrahmen",
    ]
    export_format = [
        "KontoFormat",
        "HerstellerkontoFormat",
        "HerstellerBezeichnungFormat",
        "NeueHerstellerkontenAnlegen",
    ]
    for e in export_files:
        if s.find(e) is not None and s.find(e).text is not None and s.find(e).text[-4:] == ".csv":
            result["files"][e] = s.find(e).text
    for e in export_format:
        if s.find(e) is not None and s.find(e).text != "":
            result["format"][e] = s.find(e).text

    result["format"]["NeueHerstellerkontenAnlegen"] = result["format"]["NeueHerstellerkontenAnlegen"] == "true"
    return result


class GCStruct:
    config = {
        "path": "c:/projekte/python/gcstruct",
        "path2": "c:/projekte/python/gcstruct",
        "file": "c:/projekte/python/gcstruct/config/config.xml",
        "output": "gcstruct.json",
        "default": [],
        "special": {},
        "special2": {
            "Planner": ["Kostenstelle", "Ebene1", "Ebene2"],
            "Test": ["Ebene1", "Ebene2"],
        },
        "columns": [
            "Konto_Nr",
            "Konto_Bezeichnung",
            "Konto_Art",
            "Konto_KST",
            "Konto_STK",
            "Konto_1",
            "Konto_2",
            "Konto_3",
            "Konto_4",
            "Konto_5",
        ],
        "struct": {},
        "export": {},
    }

    json_result = {
        "accounts": {},
        "tree": {},
        "flat": {},
        "struct_export": {},
        "skr51_vars": {},
    }
    structure_ids = []

    translate = {
        "Konto_Nr": "SKR51",
        "Kostenstelle": "KST",
        "Absatzkanal": "ABS",
        "Kostenträger": "KTR",
        "Marke": "MAR",
        "Standort": "STA",
        "Marke_HBV": "MAR",
        "Standort_HBV": "BMC",
    }

    def __init__(self, struct_dir, export_dir=None):
        self.config["path"] = struct_dir
        self.config["path2"] = struct_dir + "/export" if export_dir is None else export_dir

        self.config["file"] = f"{self.config['path']}/config/gcstruct.xml"
        if not Path(self.config["file"]).exists():
            self.config["file"] = f"{self.config['path']}/config/config.xml"

        cfg = ET.parse(self.config["file"])
        self.config["default"] = [
            s.find("Name").text for s in cfg.getroot().find("Strukturdefinitionen").findall("Struktur")
        ]
        self.config["export"] = dict(
            [
                (s.find("Name").text, get_structure_exports(s))
                for s in cfg.getroot().find("Strukturdefinitionen").findall("Struktur")
            ]
        )

        struct = dict([(x, get_default_cols(i)) for (i, x) in enumerate(self.config["default"])])
        struct.update(self.config["special"])
        self.config["struct"] = struct
        # print(self.config['struct'])

    def export_header(self, filetype):
        return {
            "ExportStk": [],
            "ExportStrukturenStk": [],
            "ExportAdjazenz": [],
            "ExportUebersetzung": [
                "Konto_Nr_Hersteller",
                "Konto_Nr_Split",
                "Konto_Nr_Haendler",
                "Info",
            ],
            "ExportUebersetzungStk": [
                "Konto_Nr_Hersteller",
                "Konto_Nr_Split",
                "Konto_Nr_Haendler",
                "Info",
            ],
            "ExportHerstellerKontenrahmen": [
                "Konto_Nr",
                "Konto_Bezeichnung",
                "Case",
                "Info",
            ],
        }[filetype]

    def accounts_from_csv(self, struct):
        max_rows = (len(self.config["default"]) + 1) * 10
        with open(
            f"{self.config['path']}/Kontenrahmen/Kontenrahmen.csv",
            "r",
            encoding="latin-1",
        ) as f:
            csv_reader = csv.reader(f, delimiter=";")
            imported_csv = [row[:max_rows] for row in csv_reader]

        df = pd.DataFrame.from_records(np.array(imported_csv[1:], dtype="object"), columns=imported_csv[0]).fillna(
            value=""
        )
        df = df.rename(columns={"Kostenstelle": "Konto_KST", "STK": "Konto_STK"})

        for i, (s, cols) in enumerate(struct.items()):
            df[s] = reduce(lambda x, y: x + ";" + df[y], cols, "")
            df[s] = df[s].apply(lambda x: x[1:])
            df["LetzteEbene" + str(i + 1)] = df[s].apply(lambda x: last_layer(x))
            df["LetzteEbene" + str(i + 1) + "_Nr"] = df[s].apply(lambda x: split_it(x, 0))
            df["LetzteEbene" + str(i + 1) + "_Bez"] = df[s].apply(lambda x: split_it(x, 1))
        df["Herstellerkonto_Nr"] = df["LetzteEbene1_Nr"]
        df["Herstellerkonto_Bez"] = df["LetzteEbene1_Bez"]
        return df

    def tree_from_xml(self, struct, df):
        result = {}
        for s, cols in struct.items():
            try:
                tree = ET.parse(f"{self.config['path']}/Xml/{s}.xml")
                result[s] = self.get_tree_root(tree.getroot(), s)

            except FileNotFoundError:
                print("XML-Datei fehlt")
                used_entries = [x.split(";")[1:] for x in set(df[s].to_numpy())]
                print(used_entries)
                root = ET.Element("Ebene")
                root.set("Name", s)
                result[s] = self.get_tree_root(root, s)
                # self.json_result["tree"][s] = get_tree_from_accounts(cols, [])
        return result

    def get_structure_and_tree(self):
        df = self.accounts_from_csv(self.config["struct"])
        self.json_result["accounts"] = df.to_dict("records")
        self.structure_ids = df.melt(
            id_vars=["Konto_Nr"],
            value_vars=self.config["struct"].keys(),
            var_name="Struktur",
            value_name="id",
        ).groupby(by=["Struktur", "id"])
        self.json_result["tree"] = self.tree_from_xml(self.config["struct"], df)

        for s, cols in self.config["struct"].items():
            self.json_result["flat"][s] = get_flat(self.json_result["tree"][s])

        for s, entries in self.json_result["flat"].items():
            cols = self.config["struct"][s]
            df_temp = pd.DataFrame([x["id"].split(";") for x in entries], columns=cols)
            self.json_result["struct_export"][s] = df_temp.to_dict(orient="records")

        # {'accounts': {}, 'tree': {}, 'flat': {}, 'struct_export': {}, 'skr51_vars': {}}
        return self.json_result

    def export_structure_and_tree(self):
        json.dump(
            self.json_result,
            open(f"{self.config['path2']}/{self.config['output']}", "w"),
            indent=2,
        )

    def get_accounts(self, structure, id):
        return [x["Konto_Nr"] for x in self.json_result["accounts"] if x[structure] == id]
        # return []
        # res = self.structure_ids.groups.get((structure, id))
        # if res is None:
        #     return []
        # return res.values

    def export(self):
        for s in self.config["export"].keys():
            for filetype, filename in self.config["export"][s]["files"].items():
                with open(self.config["path2"] + "/" + filename, "w") as fwh:
                    fwh.write("Konto_Nr_Hersteller;Konto_Nr_Split;Konto_Nr_Haendler;Info\n")
                    # 'Hersteller'Konto_Nr;Konto_Bezeichnung;Case;Info'
                    for a in self.json_result["accounts"]:
                        if a["Herstellerkonto_Nr"] != "":
                            account = chevron.render(
                                self.config["export"]["SKR51"]["format"]["HerstellerkontoFormat"],
                                a,
                            )
                            fwh.write(
                                account + ";" + account + ";" + a["Konto_Nr"] + ";" + "\n"
                            )  # a['Herstellerkonto_Bez']

    def get_tree(self, node, parents, structure):
        result = []
        for child in node:
            p = get_parents_list(parents)
            parents.append(child.attrib["Name"])
            id = ";".join(parents) + ";" * (10 - len(parents))
            result.append(
                {
                    "id": id,
                    "text": child.attrib["Name"],
                    "children": self.get_tree(child, parents, structure),
                    "parents": p,
                    "accounts": self.get_accounts(structure, id),
                    "level": len(parents),
                    "form": child.attrib.get("Split", ""),
                }
            )
            parents.pop()
        return result

    def get_tree_root(self, node, structure):
        id = ";" * 9
        return {
            "id": id,
            "text": node.attrib["Name"],
            "children": self.get_tree(node, [], structure),
            "parents": [],
            "accounts": [],
            "level": 0,
            "form": "",
        }

    def post_structure_and_tree(self):
        json_post = json.load(open(f"{self.config['path']}/{self.config['output']}", "r"))

        # Kontenrahmen.csv
        ebenen = ["Ebene" + str(i) for i in range(1, len(self.config["default"]) * 10 + 1)]
        header = ";".join(self.config["columns"] + ebenen)
        cols = self.config["columns"] + self.config["default"]
        with open(
            self.config["path"] + "/Kontenrahmen/Kontenrahmen_out.csv",
            "w",
            encoding="latin-1",
        ) as f:
            f.write(header + "\n")
            for row in json_post["Kontenrahmen"]:
                f.write(";".join([row[e] for e in cols]) + "\n")
        # print(header)
        # xml und evtl. Struktur.csv
        for i, s in enumerate(self.config["default"]):
            with open(
                f"{self.config['path']}/Strukturen/Kontenrahmen.csv/{s}_out.csv",
                "w",
                encoding="latin-1",
            ) as f:
                f.write(";".join(["Ebene" + str(i * 10 + j) for j in range(1, 11)]) + "\n")
                rows = structure_from_tree({"id": ";" * 9, "children": json_post[s]})
                f.write("\n".join(rows))

            # with open(self.config['path'] + "/Strukturen/Kontenrahmen.csv/" + structure + "_2.csv", "w", encoding="latin-1") as f:
            root = ET.Element("Ebene")
            root.set("Name", s)
            xml_from_tree(root, {"id": ";" * 9, "children": json_post[s]})

            with open(f"{self.config['path']}/Xml/{s}_out.xml", "w", encoding="utf-8") as f:
                f.write(BeautifulSoup(ET.tostring(root), "xml").prettify())

    def skr51_translate(self, accounts_combined_files):
        df = self.accounts_from_csv(self.config["struct"])

        df_translate = {}
        for i, (t_from, t_to) in enumerate(self.translate.items()):
            last = "LetzteEbene" + str(i + 1)
            from_label = [
                "Konto_Nr",
                last,
                last + "_Nr",
                last + "_Bez",
                "Ebene" + str(i * 10 + 1),
                "Ebene" + str(i * 10 + 2),
            ]
            to_label = [
                t_to,
                t_to + "_Ebene",
                t_to + "_Nr",
                t_to + "_Bez",
                "Ebene1",
                "Ebene2",
            ]
            df_translate[t_from] = df[df[last + "_Nr"] != ""][from_label].rename(
                columns=dict(zip(from_label, to_label))
            )
            # print(df_translate[t_to].head())

        df2 = []
        for ac_file in accounts_combined_files:
            df2.append(
                pd.read_csv(
                    ac_file,
                    decimal=",",
                    sep=";",
                    encoding="latin-1",
                    converters={i: str for i in range(0, 200)},
                )
            )
        df_source = pd.concat(df2)
        df3 = df_source.copy()
        df3["Konto_Nr"] = df3["Konto_Nr"] + "_STK"
        df_source = pd.concat([df_source, df3])

        for t_from, t_to in self.translate.items():
            if t_to == "SKR51":
                df_source["SKR51"] = df_source["Konto_Nr"]
            elif t_from in ["Marke_HBV"]:
                df_source["Marke_HBV"] = df_source["Marke"]
            elif t_from in ["Standort_HBV"]:
                df_source["Standort_HBV"] = df_source["Standort"] + "_" + df_source["Marke"]
                df_source["BMC"] = "BMC_" + df_source["Standort_HBV"]
            elif t_to == "KTR":
                df_source["KTR"] = np.where(
                    df_source["Kostenträger_Quelle"] == "TZ",
                    "KTR_TZ_" + df_source["Kostenträger"],
                    "KTR_00",
                )
                df_source["KTR"] = np.where(
                    df_source["Kostenträger_Quelle"].isin(["NW", "SC"]),
                    "KTR_"
                    + df_source["Kostenträger_Quelle"]
                    + "_"
                    + df_source["Marke"]
                    + "_"
                    + df_source["Kostenträger"],
                    df_source["KTR"],
                )
            else:
                df_source[t_to] = t_to + "_" + df_source[t_from]
            df_source = df_source.merge(df_translate[t_from], how="left", on=[t_to], suffixes=(None, "_" + t_to))
            df_source[t_to + "_Nr"] = np.where(
                df_source[t_to + "_Nr"].isna(),
                df_source[t_from],
                df_source[t_to + "_Nr"],
            )

        df_source["Konto_Nr_SKR51"] = (
            df_source["MAR_Nr"]
            + "-"
            + df_source["STA_Nr"]
            + "-"
            + df_source["SKR51_Nr"]
            + "-"
            + df_source["KST_Nr"]
            + "-"
            + df_source["ABS_Nr"]
            + "-"
            + df_source["KTR_Nr"]
        )
        df_source["Konto_Nr_Händler"] = (
            df_source["Marke"]
            + "-"
            + df_source["Standort"]
            + "-"
            + df_source["Konto_Nr"]
            + "-"
            + df_source["Kostenstelle"]
            + "-"
            + df_source["Absatzkanal"]
            + "-"
            + df_source["Kostenträger"]
        )
        # df_source.to_csv(f"{self.config['path2']}/SKR51_Uebersetzung.csv", sep=';', encoding='latin-1', index=False)
        df_source["MAR_Nr_MAR"] = np.where(df_source["MAR_Nr_MAR"].isna(), "0000", df_source["MAR_Nr_MAR"])
        from_label = [
            "MAR_Nr",
            "STA_Nr",
            "SKR51_Nr",
            "KST_Nr",
            "ABS_Nr",
            "KTR_Nr",
            "KTR_Ebene",
            "Konto_Nr_Händler",
            "Konto_Nr_SKR51",
            "MAR_Nr_MAR",
            "BMC_Nr",
        ]
        to_label = [
            "Marke",
            "Standort",
            "Konto_Nr",
            "Kostenstelle",
            "Absatzkanal",
            "Kostenträger",
            "Kostenträger_Ebene",
            "Konto_Nr_Händler",
            "Konto_Nr_SKR51",
            "Marke_HBV",
            "Standort_HBV",
        ]
        df_combined = df_source[from_label].rename(columns=dict(zip(from_label, to_label)))
        df_combined.to_csv(
            f"{self.config['path2']}/Kontenrahmen_uebersetzt.csv",
            sep=";",
            encoding="latin-1",
            index=False,
        )

    def skr51_translate2(self, accounts_combined_file):
        df = self.accounts_from_csv(self.config["struct"])

        df_list = []
        for i, s in enumerate(self.config["struct"].keys()):
            from_label = [
                "Konto_Nr",
                "Ebene" + str(i * 10 + 1),
                "Ebene" + str(i * 10 + 2),
                "Ebene" + str(i * 10 + 3),
            ]
            to_label = ["Konto_Nr", "key", "value", "value2"]
            df_temp = df[from_label].rename(columns=dict(zip(from_label, to_label)))
            df_temp["key"] = "{" + s + "/" + df_temp["key"] + "}"
            df_list.append(df_temp[df_temp["value"] != ""])
        df_translate = pd.concat(df_list)

        # df_translate.to_csv(f"{self.config['path2']}/SKR51_Variablen.csv", sep=';', encoding='latin-1', index=False)

        df_source = pd.read_csv(
            accounts_combined_file,
            decimal=",",
            sep=";",
            encoding="latin-1",
            converters={i: str for i in range(0, 200)},
        )
        df_source = df_source[df_source["Konto_Nr"].str.contains("_STK") == False]
        df_source["Konto_Nr_Gesamt"] = df_source["Konto_Nr"]
        df_source["Konto_Nr"] = np.where(
            df_source["Konto_Nr"].str.contains(r"^[4578]"),
            df_source["Konto_Nr"] + "_" + df_source["Kostenstelle"].str.slice(stop=1),
            df_source["Konto_Nr"],
        )
        df_source["Konto_Nr"] = np.where(
            df_source["Konto_Nr"].str.contains(r"^5\d+_4"),
            df_source["Konto_Nr"] + df_source["Kostenstelle"].str.slice(start=1, stop=2),
            df_source["Konto_Nr"],
        )

        df_source = df_source.merge(df, how="left", on=["Konto_Nr"])
        # rows = df_source.shape[0]
        df_source["value"] = ""

        cols = get_default_cols(0)
        for t_from, t_to in self.translate.items():
            if t_from in ["Marke_HBV", "Standort_HBV"]:
                continue
            if t_from == "Konto_Nr":
                df_source[t_to] = df_source[t_from]
            else:
                df_source[t_to] = t_to + "_" + df_source[t_from]
            for e in cols:
                df_source = df_source.merge(
                    df_translate,
                    how="left",
                    left_on=[t_to, e],
                    right_on=["Konto_Nr", "key"],
                    suffixes=(None, "_" + t_to + "_" + e),
                )
                df_source[e] = np.where(
                    df_source["value_" + t_to + "_" + e].notna(),
                    df_source["value_" + t_to + "_" + e],
                    df_source[e],
                )
                # if df_source.shape[0] > rows:
                #     print(t_to + '_' + e + ': ' + str(df_source.shape[0]))
        # df_source.to_csv(f"{self.config['path2']}/SKR51_Variablen2.csv", sep=';', encoding='latin-1', index=False)
        # df_source[t_to + '_Nr'] = np.where(df_source[t_to + '_Nr'].isna(), df_source[t_from], df_source[t_to + '_Nr'])
        for e in cols:
            df_source[e] = np.where(
                df_source[e].str.startswith("{"),
                df_source[e].str.extract(r"\/(.*)}", expand=False) + " falsch",
                df_source[e],
            )  # df_source[e].str.extract(r'/(.*)}') +
            df_source[e] = np.where(df_source[e] == "[KTR]", df_source["Kostenträger_Ebene"], df_source[e])
        # df_all[df_all['Ebene1'] == ]
        # print(df_source.head())
        df_source["Konto_neu"] = (
            df_source["Marke"]
            + "-"
            + df_source["Standort"]
            + "-"
            + df_source["Konto_Nr"]
            + "-"
            + df_source["Kostenstelle"]
            + "-"
            + df_source["Absatzkanal"]
            + "-"
            + df_source["Kostenträger"]
            + " - "
            + df_source["Konto_Bezeichnung"]
        )
        df_source["Ebene1_empty"] = df_source["Ebene1"].isna()  # , df_source['Ebene1'].map(lambda x: x == ''))
        df_source["Konto_neu"] = np.where(df_source["Ebene1_empty"], "keine Zuordnung", df_source["Konto_neu"])
        df_source["Ebene1"] = np.where(df_source["Ebene1_empty"], "keine Zuordnung", df_source["Ebene1"])
        df_source["Konto_Gruppe"] = df_source["Konto_Nr"] + " - " + df_source["Konto_Bezeichnung"]
        df_source["Konto_Gruppe"] = np.where(df_source["Ebene1_empty"], "keine Zuordnung", df_source["Konto_Gruppe"])
        df_source["Konto_Gesamt"] = df_source["Konto_Nr_Gesamt"] + " - " + df_source["Konto_Bezeichnung"]

        df_amount = df_source[df_source["Ebene1"] == "Umsatzerlöse"].reset_index()
        df_amount["Ebene1"] = "verkaufte Stückzahlen"
        df_amount["Ebene72"] = "verkaufte Stückzahlen"
        df_amount["Konto_neu"] = "STK " + df_amount["Konto_neu"]
        df_amount["Konto_Nr_Händler"] = df_amount["Konto_Nr_Händler"] + "_STK"
        df_amount["Konto_Gruppe"] = "STK " + df_amount["Konto_Gruppe"]
        df_amount["Konto_Gesamt"] = "STK " + df_amount["Konto_Gesamt"]

        df_source = pd.concat([df_source, df_amount])

        df_source["BWA"] = "Ebene81" in df_source.columns
        df_source["Ebene91"] = np.where(df_source["BWA"], df_source["Ebene81"], "")
        df_source["Ebene92"] = np.where(df_source["BWA"], df_source["Ebene82"], "")
        df_source["Ebene93"] = np.where(df_source["BWA"], df_source["Ebene83"], "")
        df_source["Ebene94"] = np.where(df_source["BWA"], df_source["Ebene84"], "")
        df_source["Ebene95"] = np.where(df_source["BWA"], df_source["Ebene85"], "")
        df_source["Ebene96"] = np.where(df_source["BWA"], df_source["Ebene86"], "")
        df_source["Ebene97"] = np.where(df_source["BWA"], df_source["Ebene87"], "")
        df_source["Ebene98"] = np.where(df_source["BWA"], df_source["Ebene88"], "")
        df_source["Ebene99"] = np.where(df_source["BWA"], df_source["Ebene89"], "")
        df_source["Ebene100"] = np.where(df_source["BWA"], df_source["Ebene90"], "")

        df_source["GuV"] = df_source["Ebene71"] == "GuV"
        df_source["Ebene81"] = np.where(df_source["GuV"], df_source["Ebene72"], "Bilanz")
        df_source["Ebene82"] = np.where(df_source["GuV"], df_source["Ebene73"], "")
        df_source["Ebene83"] = np.where(df_source["GuV"], df_source["Ebene74"], "")
        df_source["Ebene84"] = np.where(df_source["GuV"], df_source["Ebene75"], "")
        df_source["Ebene85"] = np.where(df_source["GuV"], df_source["Ebene76"], "")
        df_source["Ebene86"] = np.where(df_source["GuV"], df_source["Ebene77"], "")
        df_source["Ebene87"] = np.where(df_source["GuV"], df_source["Ebene78"], "")
        df_source["Ebene88"] = np.where(df_source["GuV"], df_source["Ebene79"], "")
        df_source["Ebene89"] = np.where(df_source["GuV"], df_source["Ebene80"], "")
        df_source["Ebene90"] = ""
        df_source["Ebene71"] = np.where(df_source["GuV"], "GuV", df_source["Ebene72"])
        df_source["Ebene72"] = np.where(df_source["GuV"], "", df_source["Ebene73"])
        df_source["Ebene73"] = np.where(df_source["GuV"], "", df_source["Ebene74"])
        df_source["Ebene74"] = np.where(df_source["GuV"], "", df_source["Ebene75"])
        df_source["Ebene75"] = np.where(df_source["GuV"], "", df_source["Ebene76"])
        df_source["Ebene76"] = np.where(df_source["GuV"], "", df_source["Ebene77"])
        df_source["Ebene77"] = np.where(df_source["GuV"], "", df_source["Ebene78"])
        df_source["Ebene78"] = np.where(df_source["GuV"], "", df_source["Ebene79"])
        df_source["Ebene79"] = np.where(df_source["GuV"], "", df_source["Ebene80"])
        df_source["Ebene80"] = ""
        df_source["Susa"] = df_source["Konto_Gruppe"].str.slice(stop=1)
        df_source["Konto_KST"] = ""
        df_source["GuV_Bilanz"] = df_source["Konto_Art"]

        from_label = ["Konto_neu", "Konto_Nr_Händler"]
        to_label = ["Konto", "Acct_Nr"]

        df_source = df_source.rename(columns=dict(zip(from_label, to_label)))

        df_source = df_source[
            [
                "Konto",
                "Acct_Nr",
                "Konto_Bezeichnung",
                "GuV_Bilanz",
                "Konto_KST",
                "Konto_STK",
                "Konto_1",
                "Konto_2",
                "Konto_3",
                "Konto_4",
                "Konto_5",
            ]
            + get_default_cols(0)
            + get_default_cols(7)
            + get_default_cols(8)
            + get_default_cols(9)
            + ["Konto_Gruppe", "Konto_Nr_Gesamt", "Konto_Gesamt", "Susa"]
        ]
        df_source.to_csv(
            f"{self.config['path2']}/SKR51_Uebersetzung.csv",
            sep=";",
            encoding="latin-1",
            index=False,
        )

    def skr51_vars(self):
        self.get_structure_and_tree()
        cols = get_default_cols(0)

        df_temp = pd.read_csv(
            f"{self.config['path']}/Export/Kostentraeger.csv",
            decimal=",",
            sep=";",
            encoding="latin-1",
            converters={i: str for i in range(0, 200)},
        )
        df_temp["value"] = df_temp["Ebene33"]
        df_temp["key"] = "[KTR]"
        df_temp = df_temp[df_temp["value"].str.contains(" - ")]

        df_list = [df_temp[["key", "value"]]]

        for s, entries in self.json_result["flat"].items():
            df = pd.DataFrame([x["id"].split(";") for x in entries], columns=cols)
            df["key"] = df[cols[0]].apply(lambda x: "{" + s + "/" + x + "}")
            df["value"] = df[cols[1]]
            df_list.append(df[["key", "value"]])
        df = pd.concat(df_list)
        df_vars = df[df["value"] != ""]
        # df_vars.to_csv(f"{self.config['path2']}/SKR51_Variablen2.csv", sep=';', encoding='latin-1', index=False)

        df_main = pd.DataFrame(
            [x["id"].split(";") for x in self.json_result["flat"]["SKR51"]],
            columns=cols,
        )
        df_main["value"] = ""
        for c in cols:
            df_main = df_main.merge(df_vars, how="left", left_on=c, right_on="key", suffixes=(None, "_" + c))
            df_main[c] = np.where(df_main["value_" + c].isna(), df_main[c], df_main["value_" + c])

        df_amount = df_main[df_main["Ebene1"] == "Umsatzerlöse"].reset_index()
        df_amount["Ebene1"] = "verkaufte Stückzahlen"
        df_main = pd.concat([df_main, df_amount])

        # from_label = cols
        to_label = cols  # get_default_cols(9)
        # df_main = df_main.rename(columns=dict(zip(from_label, to_label)))
        df_main[to_label].to_csv(
            f"{self.config['path2']}/SKR51_Struktur.csv",
            sep=";",
            encoding="latin-1",
            index_label="Sortierung",
        )


def gcstruct_uebersetzung(base_dir=None):
    # base_dir = 'P:/SKR51_GCStruct/'
    if base_dir is None:
        base_dir = Path(".").absolute()
    else:
        base_dir = Path(base_dir)
    import_dir = base_dir
    if base_dir.name == "scripts":
        if base_dir.parent.parent.name == "Portal":
            base_dir = base_dir.parent.parent.parent
            import_dir = base_dir.joinpath("Portal/System/IQD/Belege/Kontenrahmen")
        else:
            base_dir = base_dir.parent.parent
            import_dir = base_dir.joinpath("System/OPTIMA/Export")
    elif not base_dir.joinpath("GCStruct_Aufbereitung").exists():
        base_dir = Path("//192.168.2.21/verwaltung/Kunden/Luchtenberg/1 Umstellung SKR51/")
        if not base_dir.exists():
            base_dir = Path("//media/fileserver1/verwaltung/Kunden/Luchtenberg/1 Umstellung SKR51/")
        import_dir = base_dir

    struct = GCStruct(str(base_dir.joinpath("GCStruct_Aufbereitung")))
    struct.skr51_translate(import_dir.glob("Kontenrahmen_kombiniert*.csv"))
    print("Kontenrahmen_uebersetzt.csv erstellt.")
    # copyfile('c:/Projekte/Python/Gcstruct/Kontenrahmen_kombiniert.csv', base_dir + 'GCStruct_Modell/Export/Kontenrahmen_kombiniert.csv')

    struct2 = GCStruct(str(base_dir.joinpath("GCStruct_Modell")))
    struct2.skr51_translate2(str(base_dir.joinpath("GCStruct_Aufbereitung/Export/Kontenrahmen_uebersetzt.csv")))
    print("SKR51_Uebersetzung.csv erstellt.")
    struct2.skr51_vars()
    print("SKR51_Struktur.csv erstellt.")


def dresen():
    struct = GCStruct("c:/projekte/GCHRStruct_Hyundai_Export")
    struct.get_structure_and_tree()
    struct.export_structure_and_tree()
    struct.export()


def reisacher():
    base_dir = "X:/Robert/Planung Reisacher/GCStruct_neue_Struktur_Planung"
    if not Path(base_dir).exists():
        base_dir = "/media/fileserver1/austausch/Robert/Planung Reisacher/GCStruct_neue_Struktur_Planung"
    struct = GCStruct(base_dir)
    struct.get_structure_and_tree()
    struct.export_structure_and_tree()
    # json.dump(res['flat'], open(f"{self.config['path2']}/{self.config['output']}", 'w'), indent=2)


if __name__ == "__main__":
    # struct = GCStruct('c:/projekte/gcstruct_dresen')
    # struct = GCStruct('c:/projekte/python/gcstruct')
    # struct = GCStruct('c:/projekte/python/gcstruct_reisacher_planung')
    reisacher()
    # dresen()