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"],
"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",
"Konto_Bezeichnung",
last,
last + "_Nr",
last + "_Bez",
"Ebene" + str(i * 10 + 1),
"Ebene" + str(i * 10 + 2),
]
to_label = [
t_to,
t_to + "_Bez_ori",
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["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"])
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",
"Konto_Nr",
"Konto_Nr_Händler_mit_KST",
"SKR51_Bez_ori",
]
to_label = [
"Marke",
"Standort",
"Konto_Nr",
"Kostenstelle",
"Absatzkanal",
"Kostenträger",
"Kostenträger_Ebene",
"Konto_Nr_Händler",
"Konto_Nr_SKR51",
"Marke_HBV",
"Standort_HBV",
"Konto_Nr_Händler_kurz",
"Konto_Nr_Händler_mit_KST",
"Konto_Bezeichnung_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"] = df_source["Konto_Art"]
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"] = df_source["Acct_Nr"].str.slice_replace(3, 5, "00")
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 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()