planner/webservice/csv_accounts.py

import pandas as pd
import numpy as np
from re import match
import json
import plac
from pathlib import Path


@plac.pos('period', '', type=str)
def actuals(period):
    base_dir = Path(__file__).absolute().parent.parent
    print(base_dir)
    df1 = pd.read_csv(base_dir / 'data/Belege_Planung_Ist_FC_AHR.csv', sep=';', decimal=',',
                      dtype={0: str, 1: str, 2: str, 3: float})
    df2 = pd.read_csv(base_dir / 'data/Belege_Planung_Ist_FC_AHA.csv', sep=';', decimal=',',
                      dtype={0: str, 1: str, 2: str, 3: float})
    df12 = pd.concat([df1, df2])
    df3 = pd.read_csv(base_dir / 'data/NW_GW_Stk_Planung_AHR.csv', sep=';', decimal=',',
                      dtype={0: str, 1: str, 2: str, 3: float})
    df4 = pd.read_csv(base_dir / 'data/NW_GW_Stk_Planung_AHA.csv', sep=';', decimal=',',
                      dtype={0: str, 1: str, 2: str, 3: float})
    df34 = pd.concat([df3, df4])
    df = pd.merge(df12, df34, how='left', on=['Bookkeep_Period', 'Betrieb_Nr', 'Konto_Nr'])
    # df = pd.read_csv('Planung/Belege_Planung_Ist_FC_Dresen.csv', sep=';', decimal=',',
    #                  dtype={0: str, 1: str, 2: str, 3: str, 4: str, 5: float, 6: float})
    df['Jahr'] = df['Bookkeep_Period'].apply(lambda x: x[:4])
    current_year = period[:4]
    prev_year = str(int(current_year) - 1)
    next_year = str(int(current_year) + 1)
    month_no = int(period[4:])
    # df = df[df['Bookkeep_Period'] <= period]
    df['PY'] = np.where(df['Jahr'] == prev_year, df['Betrag'], 0)
    df['PYQ'] = np.where(df['Jahr'] == prev_year, df['Menge'], 0)
    df['CY'] = np.where(df['Jahr'] == current_year, df['Betrag'], 0)
    df['CYQ'] = np.where(df['Jahr'] == current_year, df['Menge'], 0)
    df['YTD'] = np.where(df['Bookkeep_Period'] <= period, df['CY'], 0)
    df['YTDQ'] = np.where(df['Bookkeep_Period'] <= period, df['CYQ'], 0)
    df['FC'] = df['YTD'] * 12 / month_no
    df['FCQ'] = df['YTDQ'] * 12 / month_no
    df.drop(columns=['Menge', 'Betrag'], inplace=True)

    # df2 = pd.pivot_table(df, values='Betrag', index=['Konto Nr', 'Betrieb Nr'], columns=['Jahr'], aggfunc=np.sum, fill_value=0.0)
    df = df.groupby(['Konto_Nr', 'Betrieb_Nr']).sum()
    print(df.head())

    res = {}
    for (acct, dept), values in df.to_dict(orient='index').items():
        if acct not in res:
            res[acct] = {}
        res[acct][dept] = [round(v, 2) for v in values.values()]

    data = {'values': res}
    json.dump(data, open(base_dir / f'export/accounts_{next_year}.json', 'w'), indent=2)


@plac.pos('year', '', type=str)
def planning_prev(year):
    base_dir = Path.cwd().parent
    print(base_dir)
    df1 = pd.read_csv(base_dir / 'data/Planner_2022_V1_Plan.csv',
                      sep=';', decimal=',', encoding='latin-1', dtype={'Betrieb Nr': str, 'Bereich': str, 'Zeile': str})
    df1['Wert'] = df1['Gesamt']
    df1 = df1[['Jahr', 'Betrieb Nr', 'Vstufe 1', 'Bereich', 'Zeile', 'Konto', 'Version', 'Wert']]

    df2 = pd.read_csv(base_dir / 'data/Planner_2022_V1_Stk.csv',
                      sep=';', decimal=',', encoding='latin-1', dtype={'Betrieb Nr': str, 'Bereich': str, 'Zeile': str})
    df2['Menge'] = df2['Gesamt']
    df2['Vstufe 1'] = 'Umsatzerlöse'
    df2 = df2[['Jahr', 'Betrieb Nr', 'Vstufe 1', 'Bereich', 'Zeile', 'Konto', 'Version', 'Menge']]
    df = pd.merge(df1, df2, how='left', on=['Jahr', 'Betrieb Nr', 'Vstufe 1', 'Bereich', 'Zeile', 'Konto', 'Version'])
    df['Menge'] = df['Menge'].fillna(0)
    df['Wert'] = df['Wert'].fillna(0)
    df['Wert'] = np.where(df['Vstufe 1'] == 'Umsatzerlöse', df['Wert'], df['Wert'] * -1)

    df['Bereich'] = df['Bereich'].fillna('NA').replace('VW (inkl. GF)', '?')
    df['regex'] = df['Vstufe 1'] + ";" + df['Bereich'] + ";.*" + df['Zeile'] + ' - [^;]*;;'
    df = df[df['Wert'] != 0]

    gcstruct = json.load(open(base_dir / 'export/gcstruct.json', 'r'))
    structure_ids = [s['id'] for s in gcstruct['flat']['Struktur_FB']]

    df['id'] = df['regex'].apply(lambda x: (list(filter(lambda y: match(x, y), structure_ids)) + [''])[0])
    df = df[df['id'] != '']

    res = {}
    for item in df.to_dict(orient='records'):
        if item['id'] not in res:
            res[item['id']] = {}
        res[item['id']][item['Betrieb Nr']] = [item['Wert'], item['Menge']]
    data = {'values': res}
    json.dump(data, open(base_dir / 'export/planning_2023.json', 'w'), indent=2)


def planning_new(filename):
    with open('planner/export/' + filename, 'r') as frh:
        structure = json.load(frh)
    year = str(int(filename[:4]) + 1)
    result = {}
    for s in structure:
        if len(s['accounts']) == 0:
            continue
        result[s['id']] = dict([(k, [v[10], v[5]]) for k, v in s['values2'].items()])

    with open(f"planner/export/planning_{year}.json", 'w') as fwh:
        json.dump({'values': result}, fwh, indent=2)


if __name__ == '__main__':
    actuals('202210')
    planning_prev('2022')
    # plac.call(actuals)
    # planning_new('2022_V2_20220407150009.json')