import pandas as pd import xml.etree.ElementTree as ET import json import re from bs4 import BeautifulSoup from functools import reduce reisacher = True reisacher_remote = 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"] } } 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): df = pd.read_csv(f"{config['path']}/Kontenrahmen/Kontenrahmen.csv", sep=";", encoding="ansi", decimal=",", converters={i: str for i in range(0, 200)}) # , index_col="Konto_Nr") 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']) get_structure_and_tree(struct) # post_structure_and_tree()