Python-Projekte/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'}

    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='ansi') 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')

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

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

    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='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(self.config['default']):
            with open(f"{self.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(self.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(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_file):
        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())

        df_source = pd.read_csv(accounts_combined_file, decimal=',', sep=';', encoding='ansi', converters={i: str for i in range(0, 200)})
        for t_from, t_to in self.translate.items():
            if t_to == 'SKR51':
                df_source['SKR51'] = df_source['Konto_Nr']
            elif t_from == 'Marke_HBV':
                df_source['Marke_HBV'] = df_source['Marke']
            else:
                df_source[t_to] = df_source[t_from].apply(lambda x: t_to + '_' + x)
            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='ansi', 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']
        to_label = ['Marke', 'Standort', 'Konto_Nr', 'Kostenstelle', 'Absatzkanal', 'Kostenträger', 'Kostenträger_Ebene', 'Konto_Nr_Händler', 'Konto_Nr_SKR51', 'Marke_HBV']
        df_combined = df_source[from_label].rename(columns=dict(zip(from_label, to_label)))
        df_combined.to_csv(f"{self.config['path2']}/Kontenrahmen_kombiniert.csv", sep=';', encoding='ansi', 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='ansi', index=False)

        df_source = pd.read_csv(accounts_combined_file, decimal=',', sep=';', encoding='ansi', converters={i: str for i in range(0, 200)})
        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 == 'Marke_HBV':
                continue
            if t_from != 'Konto_Nr':
                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[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_amount = df_source[df_source['Ebene1'] == 'Umsatzerlöse'].reset_index()
        df_amount['Ebene1'] = '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_source = pd.concat([df_source, df_amount])

        df_source = df_source[['Konto_neu', 'Konto_Nr_Händler', 'Konto_Bezeichnung', 'Konto_Art', 'Konto_KST', 'Konto_STK', 'Konto_1', 'Konto_2', 'Konto_3', 'Konto_4', 'Konto_5', 'Ebene1', 'Ebene2', 'Ebene3', 'Ebene4', 'Ebene5', 'Ebene6', 'Ebene7', 'Ebene8', 'Ebene9', 'Ebene10', 'Konto_Gruppe']]
        from_label = cols
        to_label = get_default_cols(9)

        df_source = df_source.rename(columns=dict(zip(from_label, to_label)))
        # 'Marke', 'Standort', 'Konto_Nr', 'Kostenstelle', 'Absatzkanal', 'Kostenträger',
        df_source.to_csv(f"{self.config['path2']}/SKR51_Uebersetzung.csv", sep=';', encoding='ansi', 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='ansi', 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='ansi', 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 = 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='ansi', index_label='Sortierung')


def luchtenberg():
    # base_dir = 'P:/SKR51_GCStruct/'
    base_dir = Path('.').absolute()
    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('V:/Kunden/Luchtenberg/1 Umstellung SKR51/')
        import_dir = base_dir

    struct = GCStruct(str(base_dir.joinpath('GCStruct_Aufbereitung')))
    struct.skr51_translate(str(import_dir.joinpath('Kontenrahmen_kombiniert.csv')))
    # 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_kombiniert.csv')))
    struct2.skr51_vars()


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


if __name__ == '__main__':
    # struct = GCStruct('c:/projekte/gcstruct_dresen')
    # struct = GCStruct('c:/projekte/python/gcstruct')
    # struct = GCStruct('c:/projekte/python/gcstruct_reisacher_planung')
    # struct = GCStruct('X:/Robert/Planung Reisacher/GCStruct_neue_Struktur_Planung')
    # print(struct.config['struct'])
    # struct.post_structure_and_tree()
    luchtenberg()
    # dresen()