Python-Projekte/planner/hbv_export.py

import pandas as pd
import numpy as np
from datetime import datetime
from gnupg_encrypt import encrypt
import os


base_dir = '/home/robert/projekte/python/planner/HBV/'
hb_format = base_dir + 'hb_format.csv'
hb_department = base_dir + 'hb_department.csv'
hb_translation = base_dir + 'hb_translation.csv'
plan_amount = base_dir + '../export/Planner_2022_V2_Stk.csv'
plan_values = base_dir + '../export/Planner_2022_V2_Plan.csv'

hb_ignored = base_dir + 'ignoriert.csv'

current_year = '2022'
current_date = datetime.now().strftime('%d%m%Y%H%M%S')
# current_date = '24032021112656'


def main():
    # Übersetzungstabelle importieren
    df_translation = pd.read_csv(hb_translation, decimal=',', sep=';', encoding='latin-1', converters={i: str for i in range(0, 200)})
    # df_translation['column_no_join'] = np.where(df_translation['column_no'].isin(['1', '3', '4']), df_translation['column_no'], '0')
    # Department-Zuordnung importieren
    df_department = pd.read_csv(hb_department, decimal=',', sep=';', encoding='latin-1', converters={i: str for i in range(0, 200)})

    # Planwerte importieren
    values_converter = {i: str for i in range(0, 200)}
    values_converter[4] = lambda x: np.float64(x.replace(',', '.') if x != '' else 0.0)
    values_converter[5] = values_converter[4]
    df_values = pd.read_csv(plan_values, decimal=',', sep=';', encoding='latin-1', converters=values_converter)   # encoding='latin-1',
    df_values['Gesamt'] = df_values['Gesamt'] + df_values['Periode13']
    df_values['type'] = '2'
    df_values['type'] = np.where(df_values['Vstufe 1'].isin(['Materialaufwand']), '3', df_values['type'])
    df_amount = pd.read_csv(plan_amount, decimal=',', sep=';', encoding='latin-1', converters=values_converter)   # , encoding='latin-1'
    df_amount['type'] = '1'
    df: pd.DataFrame = df_values.append(df_amount)

    # Planwerte alle positiv
    df['Minus1'] = np.where(df['Vstufe 1'].isin(['Umsatzerlöse', 'Verk. Stückzahlen']) | df['Zeile'].isin(['7410', '7440']), 1, -1)
    df['Gesamt'] = df['Gesamt'] * df['Minus1']

    # Planwerte übersetzen
    df = df.merge(df_department, how='inner', left_on='Betrieb Nr', right_on='department_id')
    df = df.merge(df_translation, how='left', left_on=['Zeile', 'type'], right_on=['from', 'type'])
    # fehlende Übersetzung
    df_ignored = df[(df['to'].isna()) & (df['Gesamt'] != 0)]
    df_ignored.to_csv(hb_ignored, decimal=',', sep=';', encoding='latin-1', index=False)

    # Planwerte formatieren und exportieren
    rename_from = ['bm_code', 'BV_NUMMER', 'FILIAL_NR', 'to', 'column_no', 'Jahr', 'Gesamt']
    rename_to = ['BM_CODE', 'BV_NUMMER', 'FILIAL_NR', 'ZEILE', 'SPALTE', 'JAHR', 'WERT']
    df_valid = df[df['to'].notna()].rename(columns=dict(zip(rename_from, rename_to)))
    df_valid['SPALTE'] = df_valid['SPALTE'].str.zfill(3)
    group_by = ['BM_CODE', 'BV_NUMMER', 'FILIAL_NR']
    df_valid = df_valid[rename_to].groupby(group_by)

    for group in df_valid.groups:
        g = dict(zip(group_by, group))
        filename = base_dir + f"{current_year}/{g['BV_NUMMER']}_{g['FILIAL_NR']}/HB{g['BM_CODE']}{current_year}00{g['BV_NUMMER']}{g['FILIAL_NR']}0{current_date}.dat"
        os.makedirs(os.path.dirname(filename), exist_ok=True)
        df_group = df_valid.get_group(group).groupby(rename_to[:-1]).sum().reset_index()
        with open(filename, 'w') as fwh:
            for row in df_group.to_dict(orient='records'):
                fwh.write("I0155{BV_NUMMER}{FILIAL_NR}0{ZEILE}{SPALTE}00{JAHR}{WERT:16.2f}03\n".format(**row))
        encrypt(filename)


if __name__ == '__main__':
    main()