Python-Projekte/gcstruct/gchr.py

import pandas as pd
import numpy as np
import xml.etree.ElementTree as ET
from xml.dom import minidom

base_dir = 'C:/Projekte/Python/gcstruct/'
account_translation = base_dir + 'Kontenrahmen_kombiniert.csv'
account_bookings = base_dir + 'GuV_Salden.csv'
first_month_of_financial_year = '07'
current_year = '2021'
current_month = '04'

last_year = str(int(current_year) - 1)
next_year = str(int(current_year) + 1)

header = {
    'Country': 'DE',
    'MainBmCode': '8024',
    'Month': '07',
    'Year': '2015',
    'Currency': 'EUR',
    'NumberOfMakes': 2,
    'NumberOfSites': 2,
    'ExtractionDate': '26.08.2015',
    'ExtractionTime': '13:51:47',
    'BeginFiscalYear': '01'
}


def bookkeep_filter():
    period = [current_year + str(i).zfill(2) for i in range(1, 13)]
    if first_month_of_financial_year != '01':
        if first_month_of_financial_year > current_month:
            period = [last_year + str(i).zfill(2) for i in range(1, 13)] + period
        else:
            period = period + [next_year + str(i).zfill(2) for i in range(1, 13)]
        fm = int(first_month_of_financial_year)
        period = period[fm - 1:fm + 12]
    rename_to = ['Period' + str(i).zfill(2) for i in range(1, 13)]
    return dict(zip(period, rename_to))


def main():
    # Übersetzungstabelle laden
    df_translate = pd.read_csv(account_translation, decimal=',', sep=';', encoding='ansi', converters={i: str for i in range(0, 200)})
    # Kontensalden laden
    df_bookings = pd.read_csv(account_bookings, decimal=',', sep=';', encoding='ansi', converters={0: str, 1: str})
    # Kontensalden auf gegebenen Monat filtern
    filter_from = current_year + first_month_of_financial_year
    if first_month_of_financial_year > current_month:
        filter_from = last_year + first_month_of_financial_year
    filter_to = current_year + current_month
    df_bookings = df_bookings[(df_bookings['Bookkeep Period'] >= filter_from) & (df_bookings['Bookkeep Period'] <= filter_to)]
    bk_filter = bookkeep_filter()
    # Spalten konvertieren
    df_bookings['period'] = df_bookings['Bookkeep Period'].apply(lambda x: bk_filter[x])
    df_bookings['amount'] = df_bookings['Debit Amount'] + df_bookings['Credit Amount']
    df_bookings['quantity'] = df_bookings['Debit Quantity'] + df_bookings['Credit Quantity']
    # Join auf Übersetzung
    df_combined = df_bookings.merge(df_translate, how='inner', on='Konto_Nr_Händler')
    # Gruppieren
    # df_grouped = df_combined.groupby(['Konto_Nr_SKR51', 'period']).sum()
    df_pivot = df_combined.pivot_table(index=['Konto_Nr_SKR51'], columns=['period'], values='amount', aggfunc=np.sum, margins=True, margins_name='CumulatedYear')
    # Infos ergänzen
    df_pivot['Decimals'] = 2
    df_pivot['OpeningBalance'] = 0.0
    df_recombined = df_translate.drop(columns=['Konto_Nr_Händler', 'Kostenträger_Ebene']).drop_duplicates()
    df_recombined = df_recombined.merge(df_pivot, how='inner', on='Konto_Nr_SKR51')
    from_label = ['Marke', 'Standort', 'Konto_Nr', 'Kostenstelle', 'Absatzkanal', 'Kostenträger']
    to_label = ['Make', 'Site', 'Account', 'Origin', 'SalesChannel', 'CostCarrier']
    df_recombined = df_recombined.rename(columns=dict(zip(from_label, to_label)))
    df_recombined['CostAccountingString'] = df_recombined['Make'] + df_recombined['Site'] + df_recombined['Origin'] + \
        df_recombined['SalesChannel'] + df_recombined['CostCarrier']

    makes = df_recombined[['Make', 'Marke_HBV']].drop_duplicates().to_dict(orient='records')
    sites = df_recombined[['Make', 'Site', 'Marke_HBV']].drop_duplicates().to_dict(orient='records')
    # als xml exportieren
    res = df_recombined.head().to_dict(orient='records')
    record_elements = ['Account', 'Make', 'Site', 'Origin', 'SalesChannel', 'CostCarrier', 'CostAccountingString',
                       'Decimals', 'OpeningBalance'] + list(bk_filter.values()) + ['CumulatedYear']
    root = ET.Element('HbvData')
    h = ET.SubElement(root, 'Header')
    for k, v in header.items():
        ET.SubElement(h, k).text = str(v)

    make_list = ET.SubElement(root, 'MakeList')
    for m in makes:
        e = ET.SubElement(make_list, 'MakeListEntry')
        ET.SubElement(e, 'Make').text = m['Make']
        ET.SubElement(e, 'MakeCode').text = m['Marke_HBV']

    bm_code_list = ET.SubElement(root, 'BmCodeList')
    for s in sites:
        e = ET.SubElement(bm_code_list, 'BmCodeListEntry')
        ET.SubElement(e, 'Make').text = m['Make']
        ET.SubElement(e, 'Site').text = m['Site']
        ET.SubElement(e, 'BmCode').text = m['Marke_HBV']

    record_list = ET.SubElement(root, 'RecordList')

    for row in res:
        record = ET.SubElement(record_list, 'Record')
        for e in record_elements:
            child = ET.SubElement(record, e)
            child.text = str(row.get(e, 0.0))
    with open(base_dir + 'export_2021-04.xml', 'w', encoding='utf-8') as f:
        f.write(minidom.parseString(ET.tostring(root)).toprettyxml(indent='  '))


if __name__ == '__main__':
    print(bookkeep_filter())
    main()