Python-Projekte/gcstruct/gcstruct.py

import csv
import json
import re
import xml.etree.ElementTree as ET
from functools import reduce
from pathlib import Path

import chevron
import numpy as np
import pandas as pd

# from shutil import copyfile
from bs4 import BeautifulSoup


def get_flat(node):
    result = [
        {
            "id": node["id"],
            "text": node["text"],
            "amount_text": node["amount_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


def extract_acct_info(df: pd.DataFrame, account_col: str) -> pd.DataFrame:
    acct_info = [
        "Marke",
        "Standort",
        "Konto_Nr",
        "Kostenstelle",
        "Absatzkanal",
        "Kostenträger",
    ]
    has_five_dashes = df[account_col].str.count("-") == 5
    invalid = "XX-XX-XXXX-XX-XX-XX"

    df[account_col] = np.where(
        has_five_dashes,
        df[account_col],
        invalid,
    )
    df[acct_info] = df[account_col].str.split(pat="-", n=6, expand=True)
    return df


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",
            ],
        }.get(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():
            if len(self.config["export"][s]["files"]) == 0:
                continue
            struct_info = {e["id"]: e for e in self.json_result["flat"][s]}
            for filetype, filename in self.config["export"][s]["files"].items():
                with open(
                    self.config["path2"] + "/" + filename,
                    "w",
                    encoding="latin-1",
                    errors="ignore",
                ) as fwh:
                    fwh.write(";".join(self.export_header(filetype)) + "\n")
                    unique_accounts = []
                    for a in self.json_result["accounts"]:
                        if a["Herstellerkonto_Nr"] == "":
                            continue

                        account = chevron.render(
                            self.config["export"]["SKR51"]["format"]["HerstellerkontoFormat"],
                            a,
                        )

                        amount_text = struct_info.get(a[s], {}).get("amount_text", "")
                        if amount_text == "" or " - " not in amount_text:
                            amount_text += " - "
                        account_amount_nr, account_amount_text = amount_text.split(" - ")
                        a2 = a.copy()
                        a2["Herstellerkonto_Nr"] = account_amount_nr
                        a2["Herstellerkonto_Bez"] = account_amount_text
                        account2 = chevron.render(
                            self.config["export"]["SKR51"]["format"]["HerstellerkontoFormat"],
                            a2,
                        )
                        if filetype == "ExportUebersetzungStk" and a["Konto_STK"] != "1":
                            continue

                        if filetype == "ExportHerstellerKontenrahmen":
                            if account not in unique_accounts:
                                unique_accounts.append(account)
                                account_text = chevron.render(
                                    self.config["export"]["SKR51"]["format"]["HerstellerBezeichnungFormat"],
                                    a,
                                )
                                fwh.write(account + ";" + account_text + ";2;" + a["Herstellerkonto_Bez"] + "\n")
                            if account_amount_nr != "" and account2 not in unique_accounts:
                                unique_accounts.append(account2)
                                account_text = chevron.render(
                                    self.config["export"]["SKR51"]["format"]["HerstellerBezeichnungFormat"],
                                    a2,
                                )
                                fwh.write(account2 + ";" + account_text + ";2;" + a["Herstellerkonto_Bez"] + "\n")
                        elif filetype == "ExportUebersetzungStk":
                            fwh.write(account2 + ";" + account2 + ";" + a["Konto_Nr"] + ";" + "\n")
                        else:
                            fwh.write(account + ";" + account + ";" + a["Konto_Nr"] + ";" + "\n")

    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", ""),
                    "amount_text": child.attrib.get("NameStk", ""),
                }
            )
            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": "",
            "amount_text": "",
        }

    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):
        # self.get_structure_and_tree()
        df = self.accounts_from_csv(self.config["struct"])
        df["KRM_Nr"] = df["Konto_Nr"].apply(lambda x: x.split("_")[-1])

        # structure_index = {}
        # for i, s in enumerate(self.config["struct"].keys()):
        #     structure_index[s] = dict((e["id"], j) for j, e in enumerate(self.json_result["flat"][s]))

        df_translate = {}
        for i, (t_from, t_to) in enumerate(self.translate.items()):
            last = "LetzteEbene" + str(i + 1)

            from_label = [
                "Konto_Nr",
                "Konto_Bezeichnung",
                last,
                last + "_Nr",
                last + "_Bez",
                "Ebene" + str(i * 10 + 1),
                "Ebene" + str(i * 10 + 2),
                "KRM_Nr",
            ]
            to_label = [
                t_to,
                t_to + "_Bez_Händler",
                t_to + "_Ebene",
                t_to + "_Nr",
                t_to + "_Bez",
                t_to + "_Ebene1",
                t_to + "_Ebene2",
                t_to + "_Nr_Händler",
            ]
            df_translate[t_from] = df[df[last + "_Nr"] != ""][from_label].rename(
                columns=dict(zip(from_label, to_label))
            )

        # for (t_from, t_to), (s, idx) in zip(self.translate.items(), structure_index.items()):
        #     df_translate[t_from][t_to + "_Sortierung"] = df_translate[t_from]

        df_t4 = df_translate["Konto_Nr"].copy()
        df_t4 = df_t4[df_t4["SKR51_Nr"].str.match(r"^[01]")]
        df_t5 = df_t4.copy()
        df_t4["SKR51_Nr"] = df_t4["SKR51_Nr"] + "_A"
        df_t4["SKR51"] = df_t4["SKR51"] + "_A"
        df_t5["SKR51_Nr"] = df_t5["SKR51_Nr"] + "_P"
        df_t5["SKR51"] = df_t5["SKR51"] + "_P"
        df_translate["Konto_Nr"] = pd.concat([df_translate["Konto_Nr"], df_t4, df_t5])
        # print(df_translate[t_to].head())

        df2 = []
        for ac_file in accounts_combined_files:
            df_temp = pd.read_csv(
                ac_file,
                decimal=",",
                sep=";",
                encoding="latin-1",
                converters={i: str for i in range(0, 200)},
            )
            if "Konto_Nr_Händler" in df_temp.columns:
                df_temp = extract_acct_info(df_temp, "Konto_Nr_Händler")
                df_temp.drop(columns=["Konto_Nr_Händler"], inplace=True)

            if "Kostenträger_Marke" not in df_temp.columns:
                df_temp["Kostenträger_Marke"] = df_temp["Marke"]

            df2.append(df_temp)
        df_source = pd.concat(df2)

        df3 = df_source.copy()
        df3["Konto_Nr"] = df3["Konto_Nr"] + "_STK"
        df4 = df_source.copy()
        df4 = df4[df4["Konto_Nr"].str.match(r"^[01]")]
        df5 = df4.copy()
        df4["Konto_Nr"] = df4["Konto_Nr"] + "_A"
        df5["Konto_Nr"] = df5["Konto_Nr"] + "_P"
        df_source = pd.concat([df_source, df3, df4, df5])

        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["Kostenträger_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["Konto_Nr_Händler_mit_KST"] = df_source["Konto_Nr"] + "_" + df_source["Kostenstelle"]
        # 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"])
        df_source["SKR51_Sortierung"] = df_source["SKR51_Nr"]
        df_source["KST_Sortierung"] = df_source["KST_Nr"]
        df_source["ABS_Sortierung"] = df_source["ABS_Nr"]
        df_source["KTR_Sortierung"] = df_source["KTR_Nr"]
        df_source["MAR_Sortierung"] = df_source["MAR_Nr"]
        df_source["STA_Sortierung"] = df_source["STA_Nr"]
        df_source["MAR2_Sortierung"] = df_source["MAR_Nr_MAR"]
        df_source["BMC_Sortierung"] = df_source["BMC_Nr"]
        df_source["Kostenträger_Ebene"] = df_source["KTR_Ebene"]
        from_label = [
            "Konto_Nr_Händler",
            "Konto_Nr_SKR51",
            "MAR_Nr",
            "STA_Nr",
            "SKR51_Nr",
            "KST_Nr",
            "ABS_Nr",
            "KTR_Nr",
            "Kostenträger_Ebene",
            "MAR_Nr_MAR",
            "BMC_Nr",
            "Konto_Nr",
            "Konto_Nr_Händler_mit_KST",
            "SKR51_Bez_Händler",
            "SKR51_Ebene1",
            "SKR51_Ebene2",
            "SKR51_Sortierung",
            "KST_Ebene1",
            "KST_Ebene2",
            "KST_Ebene",
            "KST_Sortierung",
            "ABS_Ebene1",
            "ABS_Ebene2",
            "ABS_Ebene",
            "ABS_Sortierung",
            "KTR_Ebene1",
            "KTR_Ebene2",
            "KTR_Ebene",
            "KTR_Sortierung",
            "MAR_Ebene1",
            "MAR_Ebene2",
            "MAR_Ebene",
            "MAR_Sortierung",
            "STA_Ebene1",
            "STA_Ebene2",
            "STA_Ebene",
            "STA_Sortierung",
            "MAR_Ebene1_MAR",
            "MAR_Ebene2_MAR",
            "MAR_Ebene_MAR",
            "MAR2_Sortierung",
            "BMC_Ebene1",
            "BMC_Ebene2",
            "BMC_Ebene",
            "BMC_Sortierung",
            "KST_Nr_Händler",
            "KST_Bez_Händler",
            "ABS_Nr_Händler",
            "ABS_Bez_Händler",
            "KTR_Nr_Händler",
            "KTR_Bez_Händler",
            "MAR_Nr_Händler",
            "MAR_Bez_Händler",
        ]
        to_label = [
            "Konto_Nr_Händler",
            "Konto_Nr_SKR51",
            "Marke",
            "Standort",
            "Konto_Nr",
            "Kostenstelle",
            "Absatzkanal",
            "Kostenträger",
            "Kostenträger_Ebene",
            "Marke_HBV",
            "Standort_HBV",
            "Konto_Nr_Händler_kurz",
            "Konto_Nr_Händler_mit_KST",
            "Konto_Bezeichnung_Händler",
            "SKR51_Ebene1",
            "SKR51_Ebene2",
            "SKR51_Sortierung",
            "KST_Ebene1",
            "KST_Ebene2",
            "KST_Text",
            "KST_Sortierung",
            "ABS_Ebene1",
            "ABS_Ebene2",
            "ABS_Text",
            "ABS_Sortierung",
            "KTR_Ebene1",
            "KTR_Ebene2",
            "KTR_Text",
            "KTR_Sortierung",
            "MAR_Ebene1",
            "MAR_Ebene2",
            "MAR_Text",
            "MAR_Sortierung",
            "STA_Ebene1",
            "STA_Ebene2",
            "STA_Text",
            "STA_Sortierung",
            "MAR2_Ebene1",
            "MAR2_Ebene2",
            "MAR2_Text",
            "MAR2_Sortierung",
            "BMCode_Ebene1",
            "BMCode_Ebene2",
            "BMCode_Text",
            "BMCode_Sortierung",
            "KST_Nr_Händler",
            "KST_Bez_Händler",
            "ABS_Nr_Händler",
            "ABS_Bez_Händler",
            "KTR_Nr_Händler",
            "KTR_Bez_Händler",
            "MAR_Nr_Händler",
            "MAR_Bez_Händler",
        ]
        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_source["Konto_Händler"] = df_source["Konto_Nr_Händler_kurz"] + " - " + df_source["Konto_Bezeichnung_Händler"]
        df_source["Konto_mit_KST_Händler"] = (
            df_source["Konto_Nr_Händler_mit_KST"] + " - " + df_source["Konto_Bezeichnung_Händler"]
        )

        kto_amount = df_source["Ebene63"] == "mit STK"
        if kto_amount.sum() > 0:
            df_amount = df_source[kto_amount].reset_index()
        else:
            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_amount["Konto_Händler"] = "STK " + df_amount["Konto_Händler"]
        df_amount["Konto_mit_KST_Händler"] = "STK " + df_amount["Konto_mit_KST_Händler"]

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

        if "Ebene81" in df_source.columns:
            df_source["Ebene91"] = df_source["Ebene81"]
            df_source["Ebene92"] = df_source["Ebene82"]
            df_source["Ebene93"] = df_source["Ebene83"]
            df_source["Ebene94"] = df_source["Ebene84"]
            df_source["Ebene95"] = df_source["Ebene85"]
            df_source["Ebene96"] = df_source["Ebene86"]
            df_source["Ebene97"] = df_source["Ebene87"]
            df_source["Ebene98"] = df_source["Ebene88"]
            df_source["Ebene99"] = df_source["Ebene89"]
            df_source["Ebene100"] = df_source["Ebene90"]
        else:
            df_source["Ebene91"] = ""
            df_source["Ebene92"] = ""
            df_source["Ebene93"] = ""
            df_source["Ebene94"] = ""
            df_source["Ebene95"] = ""
            df_source["Ebene96"] = ""
            df_source["Ebene97"] = ""
            df_source["Ebene98"] = ""
            df_source["Ebene99"] = ""
            df_source["Ebene100"] = ""

        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"] = np.where(df_source["GuV"], "2", "1")

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

        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",
                "Konto_Nr_Händler",
                "Konto_Händler",
                "Konto_mit_KST_Händler",
            ]
        ]
        df_source.to_csv(
            f"{self.config['path2']}/SKR51_Uebersetzung.csv",
            sep=";",
            encoding="latin-1",
            index=False,
        )
        is_quantity = df_source["Konto"].str.startswith("STK")
        df_source["Konto"] = np.where(
            is_quantity,
            df_source["Konto"].str.slice_replace(7, 9, "00"),
            df_source["Konto"].str.slice_replace(3, 5, "00"),
        )
        df_source["Acct_Nr"] = np.where(
            df_source["Acct_Nr"].str.contains("-"),
            df_source["Acct_Nr"].str.slice_replace(3, 5, "00"),
            df_source["Acct_Nr"],
        )
        df_department = df_source.drop_duplicates()
        df_department.to_csv(
            f"{self.config['path2']}/SKR51_Uebersetzung_ohne_Standort.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 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()