You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
331 lines
9.9 KiB
331 lines
9.9 KiB
import pathlib |
|
import re |
|
import sys |
|
import warnings |
|
from dataclasses import dataclass |
|
from datetime import datetime |
|
from typing import Iterable |
|
|
|
import click |
|
import pandas as pd |
|
from PyPDFForm import FormWrapper |
|
|
|
warnings.filterwarnings("ignore") |
|
|
|
Pathlike = pathlib.Path | str |
|
|
|
WINHOME = pathlib.Path("/mnt/c/Users/Holgi/") |
|
DESKTOP = WINHOME / "Desktop" |
|
|
|
TODAY = datetime.now().strftime("%Y-%m-%d") |
|
|
|
|
|
@dataclass |
|
class PrePaymentEntry: |
|
date: str |
|
amount: float |
|
superx_id: str |
|
|
|
@classmethod |
|
def from_series(cls, series: pd.Series) -> "PrePamentEntry": |
|
return cls( |
|
date=series["BuDat"], |
|
amount=series["Betrag"], |
|
superx_id=series["BelegNr"], |
|
) |
|
|
|
@property |
|
def amount_localized(self, *, decimal=",", thousands=".") -> str: |
|
non_local = f"{self.amount:_.2f}" |
|
return non_local.replace(".", decimal).replace("_", thousands) |
|
|
|
|
|
@dataclass |
|
class PrePayments: |
|
travel_nr: str |
|
cost_center: str |
|
fonds: str |
|
project: str |
|
payments: list[PrePaymentEntry] |
|
|
|
def __iter__(self) -> Iterable[PrePaymentEntry]: |
|
return iter(self.payments) |
|
|
|
@property |
|
def nice_number(self) -> str: |
|
if len(self.travel_nr) > 6: |
|
return " ".join(self.travel_nr[:5], self.travel_nr[5:]) |
|
|
|
def to_form_data(self) -> dict[str, str]: |
|
form_data = { |
|
"Projekt": self.project, |
|
"Kostenstelle": self.cost_center, |
|
"Mittelbindung": self.travel_nr, |
|
"Fonds": self.fonds, |
|
} |
|
for index, entry in enumerate(self, start=1): |
|
form_data.update( |
|
{ |
|
f"Datum{index}": entry.date, |
|
f"BelegNr aus SuberX{index}": entry.superx_id, |
|
# no field "Euro{index}" |
|
# the automatic form calculation would not work. |
|
} |
|
) |
|
return form_data |
|
|
|
|
|
@dataclass |
|
class TravelInfo: |
|
first_name: str |
|
last_name: str |
|
iso_date: str |
|
place: str |
|
uka_form: pathlib.Path |
|
|
|
@property |
|
def first_last(self) -> str: |
|
return f"{self.first_name} {self.last_name}" |
|
|
|
@property |
|
def last_first(self) -> str: |
|
return f"{self.last_name}, {self.first_name}" |
|
|
|
@property |
|
def travel_short(self) -> str: |
|
return f"{self.last_first}, {self.place}" |
|
|
|
@property |
|
def travel_name(self) -> str: |
|
return f"{self.iso_date} {self.travel_short}" |
|
|
|
@property |
|
def ger_date(self) -> str: |
|
return _iso_date_to_german(self.iso_date) |
|
|
|
|
|
def _iso_date_to_german(date: str) -> str: |
|
return ".".join(reversed(date.split("-"))) |
|
|
|
|
|
def _search_files( |
|
folder: Pathlike, partial_name: str, suffix: str |
|
) -> list[pathlib.Path]: |
|
parent = pathlib.Path(folder) |
|
if not suffix.startswith("."): |
|
suffix = f".{suffix}" |
|
files = (item for item in parent.iterdir() if item.is_file()) |
|
non_hidden = (item for item in files if not item.stem.startswith(".")) |
|
non_tempfile = (item for item in files if not item.stem.startswith("~")) |
|
types = ( |
|
item for item in non_tempfile if item.suffix.lower() == suffix.lower() |
|
) |
|
return [item for item in types if partial_name.lower() in item.stem.lower()] |
|
|
|
|
|
def _get_latest_file( |
|
folder: Pathlike, partial_name: str, suffix: str |
|
) -> pathlib.Path | None: |
|
results = _search_files(folder, partial_name, suffix) |
|
if not results: |
|
return None |
|
creation_times = [item.stat().st_ctime for item in results] |
|
by_creation_time = sorted(zip(creation_times, results)) |
|
newest_with_time = by_creation_time[-1] # latest entry |
|
return newest_with_time[1] # the path entry of the tuple |
|
|
|
|
|
def _get_form_path(partial_name: str) -> pathlib.Path: |
|
own_parent = pathlib.Path(__file__).parent |
|
matches = _search_files(own_parent, partial_name, ".pdf") |
|
if len(matches) == 1: |
|
return matches[0] |
|
counts = len(matches) |
|
msg = f"Found {counts} matching pdf forms for '{partial_name}'" |
|
raise OSError(msg) |
|
|
|
|
|
def _get_unique(data: pd.DataFrame, column: str) -> str | int | float: |
|
uniques = data[column].unique() |
|
if len(uniques) != 1: |
|
msg = f"Found multiple unique values for '{column}'" |
|
raise ValueError(msg) |
|
return uniques[0] |
|
|
|
|
|
def _extract_travel_number(data: pd.DataFrame, column: str) -> str: |
|
belege = data[column] |
|
travel_nr = belege.apply( |
|
lambda x: re.search(r"(5\d{9,10})", x.replace(" ", "")) |
|
) |
|
match_result = travel_nr[travel_nr.first_valid_index()] |
|
return match_result[0] |
|
|
|
|
|
def _read_pre_payments(file_path: Pathlike = None) -> PrePayments: |
|
if not file_path: |
|
downloads = WINHOME / "Downloads" |
|
|
|
file_path = _get_latest_file(downloads, "Buchungen_SAP", ".xlsx") |
|
if not file_path: |
|
sys.exit("Could not find an SuperX export file, aborting.") |
|
|
|
fields = ["BelegNr", "VorgängerBelegNr", "Kostenstelle", "Fonds", "Projekt"] |
|
converters = {field: str for field in fields} |
|
raw_data = pd.read_excel(file_path, skiprows=3, converters=converters) |
|
|
|
travel_nr = _extract_travel_number(raw_data, "BelegNr") |
|
try: |
|
cost_center = _get_unique(raw_data, "Kostenstelle") |
|
fonds = _get_unique(raw_data, "Fonds") |
|
project = _get_unique(raw_data, "Projekt") |
|
if not project or len(project) <= 5: |
|
project = "" |
|
except ValueError as e: |
|
sys.exit(str(e)) |
|
|
|
mask = raw_data["Werttyp"] == "Zahlung" |
|
raw_payments = raw_data[mask].copy() |
|
|
|
summary = ( |
|
raw_payments.groupby("BelegNr") |
|
.agg({"Betrag": "sum", "BuDat": "first"}) |
|
.reset_index() |
|
.sort_values("BelegNr") |
|
) |
|
|
|
paymments = [ |
|
PrePaymentEntry.from_series(row) for i, row in summary.iterrows() |
|
] |
|
|
|
return PrePayments( |
|
travel_nr=travel_nr, |
|
cost_center=cost_center, |
|
fonds=fonds, |
|
project=project, |
|
payments=paymments, |
|
) |
|
|
|
|
|
def _complete_travel_info( |
|
search_last_name: str, iso_date: str, place: str |
|
) -> TravelInfo: |
|
forms_path = DESKTOP / "Formulare" |
|
templates_path = forms_path / "vorbereitete UK-As" |
|
|
|
templates = _search_files(templates_path, f" {search_last_name}", ".pdf") |
|
if len(templates) == 0: |
|
sys.exit( |
|
f"Could not find a UK-A template for search '{search_last_name}'" |
|
) |
|
if len(templates) > 1: |
|
sys.exit( |
|
f"Found multiple UK-A templates for search '{search_last_name}'" |
|
) |
|
uka_form = templates[0] |
|
|
|
rest, first_name = uka_form.stem.rsplit(",", maxsplit=1) |
|
*_, last_name = rest.split() |
|
first_name = first_name.strip() |
|
last_name = last_name.strip() |
|
|
|
return TravelInfo( |
|
first_name=first_name, |
|
last_name=last_name, |
|
iso_date=iso_date, |
|
place=place, |
|
uka_form=uka_form, |
|
) |
|
|
|
|
|
def _create_text_stub(path: Pathlike, *, content: str = None) -> None: |
|
path = pathlib.Path(path) |
|
content = content or path.stem |
|
path.write_text(content) |
|
|
|
|
|
def _fill_pre_payments_form(destination_path: Pathlike) -> PrePayments: |
|
pre_payments = _read_pre_payments() |
|
|
|
print(f"Projekt: {pre_payments.project}") |
|
print(f"Kostenstelle: {pre_payments.cost_center}") |
|
print(f"Mittelbindung: {pre_payments.travel_nr}") |
|
print(f"Fonds: {pre_payments.fonds}") |
|
|
|
print() |
|
print(" Datum Betrag SuperX") |
|
print() |
|
|
|
for row in pre_payments.payments: |
|
print(f" {row.date} {row.amount_localized:>7} {row.superx_id}") |
|
|
|
forms = DESKTOP / "Formulare" |
|
source_path = forms / "Vorlage UK-Abschlag, v2023-01.pdf" |
|
form = FormWrapper(str(source_path)) |
|
filled = form.fill(pre_payments.to_form_data(), flatten=False) |
|
pathlib.Path(destination_path).write_bytes(filled.read()) |
|
|
|
return pre_payments |
|
|
|
|
|
@click.command() |
|
def fill_prepayment_form(): |
|
destination_path = DESKTOP / f"{TODAY} UK-Abschlag.pdf" |
|
_fill_pre_payments_form(destination_path) |
|
|
|
|
|
@click.command() |
|
@click.option("-l", "--search_last_name", prompt=True, required=True) |
|
@click.option("-d", "--iso_date", prompt=True, required=True) |
|
@click.option("-p", "--place", prompt=True, required=True) |
|
def final_payment(search_last_name: str, iso_date: str, place: str): |
|
print("---") |
|
info = _complete_travel_info(search_last_name, iso_date, place) |
|
print(f"Name: {info.first_last}") |
|
|
|
folder = DESKTOP / f"{info.travel_name} (abgerechnet)" |
|
folder.mkdir() |
|
|
|
destination_path = folder / f"{TODAY} {info.travel_short}, UK-Abschlag.pdf" |
|
payments = _fill_pre_payments_form(destination_path) |
|
|
|
rk_path = folder / f"DATE {info.travel_short}, Reisekostenabrechnung.txt" |
|
_create_text_stub(rk_path) |
|
|
|
uka_hint_path = ( |
|
folder |
|
/ f"HUEL UK-A {info.last_first}, Dienstreise {info.place}, Schlusszahlung.txt" |
|
) |
|
content = "\t".join( |
|
[ |
|
info.ger_date.replace(".", "/"), |
|
info.first_last, |
|
f"Schlusszahlung Dienstreise {info.place}, {info.ger_date}", |
|
payments.nice_number |
|
] |
|
) |
|
_create_text_stub(uka_hint_path, content=content) |
|
|
|
sz_path = ( |
|
folder |
|
/ f"DATE {info.travel_short}, Schlusszahlung an Unikasse, KONTO, HUELNR.txt" |
|
) |
|
_create_text_stub(sz_path, content="Schlusszahlung an Unikasse geschickt.") |
|
|
|
form = FormWrapper(str(info.uka_form)) |
|
form_data = { |
|
"Verwendungszweck": f"Schlusszahlung Dienstreise nach {info.place}", |
|
"Begründung": f"Schlusszahlung Dienstreise {info.first_last} nach {info.place} am {info.ger_date}", |
|
"Datum_Feststellung": _iso_date_to_german(TODAY), |
|
"Datum_Anordnung": _iso_date_to_german(TODAY), |
|
"KostenstelleKontierung": payments.cost_center, |
|
"FondsKontierung": payments.fonds, |
|
"ProjektKontierung": payments.project, |
|
"Bezug zur Mittelbindung": payments.travel_nr, |
|
"Schlusszahlung": True, |
|
"UK-Abschlag": True, |
|
} |
|
filled = form.fill(form_data, flatten=False) |
|
|
|
uka_path = folder / f"{TODAY} {info.travel_short}, UK-A Schlusszahlung.pdf" |
|
uka_path.write_bytes(filled.read())
|
|
|