Python-Projekte/gcstruct.py

import pandas as pd
import numpy as np
import xml.etree.ElementTree as ET
import json
import csv
import re
from bs4 import BeautifulSoup
from functools import reduce

reisacher = False
reisacher_remote = False
dresen = True


config = {
    'path': "c:/projekte/python/gcstruct",
    'path2': "c:/projekte/python/gcstruct",
    'output': "gcstruct.json",
    'default': ["SKR51", "Herkunft_KST", "Absatzkanal", "Kostentraeger", "Marke", "Standort", "Manuelle_Konten"],
    'special': {
        'Planner': ["Kostenstelle", "Ebene1", "Ebene2"],
        'Test': ["Ebene1", "Ebene2"]
    }
}

if reisacher:
    config = {
        'path': "c:/projekte/python/gcstruct_reisacher_planung",
        'path2': "c:/projekte/python/gcstruct_reisacher_planung",
        'output': "gcstruct_reisacher.json",
        'default': ["Struktur_FB", "Struktur_TEK", "Struktur_GuV", "Struktur_Bilanz", "Struktur_HBV", "Bruttoertrag"],
        'special': {
            'Planner': ["Kostenstelle", "Ebene1", "Ebene2"],
            'Test': ["Ebene1", "Ebene2"]
        }
    }

if reisacher_remote:
    config = {
        'path': "X:/Robert/Planung Reisacher/GCStruct_neue_Struktur_Planung",
        'path2': "C:/Projekte/Angular/gc-form/src/assets/data",
        'output': "gcstruct_reisacher.json",
        'default': ["Struktur_FB", "Struktur_TEK", "Struktur_GuV", "Struktur_Bilanz", "Struktur_HBV", "Bruttoertrag"],
        'special': {
            'Planner': ["Kostenstelle", "Ebene1", "Ebene2"],
            'Test': ["Ebene1", "Ebene2"]
        }
    }

if dresen:
    config = {
        'path': "c:/projekte/gcstruct_dresen",
        'path2': "c:/projekte/gcstruct_dresen",
        'output': "gcstruct.json",
        'default': ["Struktur_FB", "Struktur_TEK", "Struktur_GuV", "Struktur_3"],
        'special': {}
    }


columns = ["Konto_Nr", "Konto_Bezeichnung", "Konto_Art", "Kostenstelle", "STK", "Konto_1", "Konto_2", "Konto_3", "Konto_4", "Konto_5"]

json_result = {"accounts": {}, "tree": {}, "flat": {}}


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


def get_tree(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": get_tree(child, parents, structure),
            "parents": p,
            "accounts": get_accounts(structure, id),
            "level": len(parents),
            "form": child.attrib.get('Split', '')
        })
        parents.pop()
    return result


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_accounts(structure, id):
    return [x['Konto_Nr'] for x in json_result['accounts'] if x[structure] == id]


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 get_default_cols(i):
    return ["Ebene" + str(i) for i in range(i * 10 + 1, (i + 1) * 10 + 1)]


def get_structure_and_tree(struct):
    with open(f"{config['path']}/Kontenrahmen/Kontenrahmen.csv", 'r', encoding='ansi') as f:
        csv_reader = csv.reader(f, delimiter=';')
        imported_csv = [row[:50] for row in csv_reader]

    # df = pd.read_csv(f"{config['path']}/Kontenrahmen/Kontenrahmen.csv", sep=";", encoding="ansi", quoting=3, converters={i: str for i in range(200)})    # , index_col="Konto_Nr")
    df = pd.DataFrame.from_records(np.array(imported_csv[1:], dtype='object'), columns=imported_csv[0]).fillna(value='')
    print(df.head())

    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) + '_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))

    len_items = len(struct)
    df = df[columns + [*struct] + ['LetzteEbene' + str(i + 1) + '_Nr' for i in range(len_items)] + ['LetzteEbene' + str(i + 1) + '_Bez' for i in range(len_items)]]
    json_result["accounts"] = df.to_dict("records")

    # df2 = pd.read_csv("config['path'] + "/Strukturen/Kontenrahmen.csv/SKR51.csv", sep=";", encoding="ansi", decimal=",", converters={i: str for i in range(0, 200)})
    # print(df2.head())

    for i, (s, cols) in enumerate(struct.items()):
        try:
            tree = ET.parse(f"{config['path']}/Xml/{s}.xml")
            json_result["tree"][s] = 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)
            json_result["tree"][s] = get_tree_root(root, s)
            # json_result["tree"][s] = get_tree_from_accounts(cols, [])

        json_result["flat"][s] = get_flat(json_result["tree"][s])

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


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

    # Kontenrahmen.csv
    ebenen = ["Ebene" + str(i) for i in range(1, len(config['default']) * 10 + 1)]
    header = ";".join(columns + ebenen)
    cols = columns + config['default']
    with open(config['path'] + "/Kontenrahmen/Kontenrahmen_out.csv", "w", encoding="ansi") 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(config['default']):
        with open(config['path'] + "/Strukturen/Kontenrahmen.csv/" + s + "_out.csv", "w", encoding="ansi") 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(config['path'] + "/Strukturen/Kontenrahmen.csv/" + structure + "_2.csv", "w", encoding="ansi") as f:
        root = ET.Element("Ebene")
        root.set("Name", s)
        xml_from_tree(root, {"id": ";" * 9, "children": json_post[s]})

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


if __name__ == '__main__':
    struct = dict([(x, get_default_cols(i)) for (i, x) in enumerate(config['default'])])
    struct.update(config['special'])
    print(struct)
    get_structure_and_tree(struct)
    # post_structure_and_tree()