Python-Projekte/gchr/gchr_bookings.py

import logging
import os
from datetime import datetime
from pathlib import Path

import pandas as pd


class GchrBookings:
    base_dir: str
    account_bookings: list[str]
    _df_bookings: pd.DataFrame
    booking_date: datetime

    def __init__(self, base_dir: list[str], first_month: str = None):
        self.base_dir = base_dir
        self.account_bookings = list(Path(self.base_dir).joinpath("data").glob("GuV_Bilanz_Salden*.csv"))
        self.first_month_of_financial_year = first_month or "01"

        self._df_bookings = self.load_bookings_from_file()

    def load_bookings_from_file(self) -> None:
        df_list: list[pd.DataFrame] = []
        timestamps: list[float] = []

        for csv_file in self.account_bookings:
            df_list.append(
                pd.read_csv(
                    csv_file,
                    decimal=",",
                    sep=";",
                    encoding="latin-1",
                    converters={0: str, 1: str},
                )
            )
            timestamps.append(Path(csv_file).stat().st_mtime)
        self.booking_date = datetime.fromtimestamp(max(timestamps))
        df = pd.concat(df_list)
        df["amount"] = (df["Debit Amount"] + df["Credit Amount"]).round(2)
        return df

    def set_bookkeep_period(self, year: str, month: str) -> None:
        self.current_year = year
        self.current_month = month
        self.period = f"{year}-{month}"
        prot_file = f"{self.export_info_dir}\\protokoll_{self.period}.log"
        logging.basicConfig(
            filename=prot_file,
            filemode="w",
            encoding="utf-8",
            level=logging.DEBUG,
            force=True,
        )

    @property
    def last_year(self) -> str:
        return str(int(self.current_year) - 1)

    @property
    def last_year2(self) -> str:
        return str(int(self.current_year) - 2)

    @property
    def next_year(self) -> str:
        return str(int(self.current_year) + 1)

    def filter_bookings(self, year: str, month: str) -> pd.DataFrame:
        self.set_bookkeep_period(year, month)

        # Kontensalden auf gegebenen Monat filtern
        filter_from = self.current_year + self.first_month_of_financial_year
        filter_prev = self.last_year + self.first_month_of_financial_year

        if self.first_month_of_financial_year > self.current_month:
            filter_from = self.last_year + self.first_month_of_financial_year
            filter_prev = self.last_year2 + self.first_month_of_financial_year
        filter_to = self.current_year + self.current_month
        filter_opening = self.current_year + "00"
        filter_prev_opening = self.last_year + "00"
        prev_year_closed = True

        df_opening_balance = self._df_bookings[(self._df_bookings["Bookkeep Period"] == filter_opening)]
        if df_opening_balance.shape[0] == 0:
            df_opening_balance = self._df_bookings[
                (self._df_bookings["Bookkeep Period"] == filter_prev_opening)
                | (
                    (self._df_bookings["Bookkeep Period"] >= filter_prev)
                    & (self._df_bookings["Bookkeep Period"] < filter_from)
                )
            ].copy()
            df_opening_balance["Bookkeep Period"] = filter_opening
            prev_year_closed = False

        df_opening_balance = df_opening_balance[(df_opening_balance["Konto_Nr_Händler"].str.contains(r"-[013]\d\d+-"))]
        opening_balance = df_opening_balance["amount"].aggregate("sum").round(2)
        logging.info("Gewinn/Verlustvortrag")
        logging.info(opening_balance)

        if not prev_year_closed:
            row = {
                "Konto_Nr_Händler": "01-01-0861-00-00-00",
                "Bookkeep Period": filter_opening,
                "Debit Amount": opening_balance * -1,
                "Credit Amount": 0,
                "Debit Quantity": 0,
                "Credit Quantity": 0,
                "amount": opening_balance * -1,
            }
            df_opening_balance = pd.concat([df_opening_balance, pd.DataFrame.from_records([row])])

        df_filtered = self._df_bookings[
            (self._df_bookings["Bookkeep Period"] >= filter_from) & (self._df_bookings["Bookkeep Period"] <= filter_to)
        ]

        # Buchungen kopieren und als Statistikkonten anhängen
        df_stats = df_filtered.copy()
        # df_stats = df_stats[df_stats['Konto_Nr_Händler'].str.match(r'-[24578]\d\d\d-')]
        df_stats["Konto_Nr_Händler"] = df_stats["Konto_Nr_Händler"].str.replace(r"-(\d\d\d+)-", r"-\1_STK-", regex=True)
        df_stats["amount"] = (df_filtered["Debit Quantity"] + df_filtered["Credit Quantity"]).round(2)

        df_combined = pd.concat([df_opening_balance, df_filtered, df_stats])

        # Spalten konvertieren
        df_combined["period"] = df_combined["Bookkeep Period"].apply(lambda x: self.bookkeep_filter[x])
        return df_combined[df_combined["amount"] != 0.00]

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

    @property
    def export_info_dir(self) -> str:
        info_dir = f"{self.base_dir}\\Export\\{self.current_year}\\info\\"
        os.makedirs(info_dir, exist_ok=True)
        return info_dir