Python-Projekte/planner/hbv-export.py

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


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_2021_V3_Stk.csv'
plan_values = base_dir + '../export/Planner_2021_V3_Plan.csv'

hb_ignored = base_dir + 'ignoriert.csv'

current_year = '2021'
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(',', '.'))
    df_values = pd.read_csv(plan_values, decimal=',', sep=';', encoding='latin-1', converters=values_converter)
    df_values['type'] = '2'
    df_amount = pd.read_csv(plan_amount, decimal=',', sep=';', encoding='latin-1', converters=values_converter)
    df_amount['type'] = '1'
    df: pd.DataFrame = df_values.append(df_amount)
    df['column_no'] = np.where(df['Vstufe 1'].str.contains('Umsatz'), '3', '0')
    df['column_no'] = np.where(df['Vstufe 1'].isin(['Materialaufwand']), '4', df['column_no'])
    df['column_no'] = np.where(df['type'].isin(['1']), '1', df['column_no'])

    # 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', 'column_no'], right_on=['from', 'column_no_join'])
    # 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_y', '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"HB{g['BM_CODE']}{current_year}00{g['BV_NUMMER']}{g['FILIAL_NR']}0{current_date}.dat"
        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()