gctools/misc/datev_status.py

import json
from datetime import datetime
from pathlib import Path

import pyodbc

pyodbc.lowercase = False


def run_query(cur: pyodbc.Cursor, query: str) -> list[dict[str, str]]:
    cur.execute(query)

    col_names = [x[0] for x in cur.description]
    res = []
    while True:
        row = cur.fetchone()
        if row is None:
            return res
        r = {}
        for i, col in enumerate(row):
            r[col_names[i]] = str(col).strip()
        res.append(r)


def print_mdb_details(mdb_file: str):
    print("--")
    print("[" + mdb_file + "]")
    ts = datetime.fromtimestamp(Path(mdb_file).stat().st_mtime)
    res = {"Datum_MDB_Datei": ts.strftime("%Y-%m-%d %H:%M:%S")}

    try:
        conn = pyodbc.connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=" + mdb_file + ";")
        cur = conn.cursor()
    except pyodbc.Error:
        print("Error connecting to file")
        return

    date_range = "SELECT min(dtDatum) as Datum_min, max(dtDatum) as Datum_max, min(dtWJ) as Wirtschaftsjahr FROM tblKNW"
    res1 = run_query(cur, date_range)
    res.update(res1[0])

    main_site = (
        "SELECT FIRMAI as Firma_Nr, FIRMAS as Firma_Name, BERATER as Berater_Nr, "
        "MANDANT as Mandant_Nr, dtWJAnzeige as WJ_Anzeige FROM ORDDAT"
    )
    res2 = run_query(cur, main_site)
    res.update(res2[0])

    sites = "SELECT KSTESA_NUMMER as Standort_Nr, KSTESA_BEZEICHNUNG as Standort_Name FROM tblKS_KSTESA WHERE KSTESA_OWN_ID = 2"
    res3 = run_query(cur, sites)
    res["Standorte"] = res3

    print(json.dumps(res, indent=2))
    client_db = res.get("Mandant_Nr", "1")
    year_id = res.get("Wirtschaftsjahr", "00000101")
    new_filename = str(Path(mdb_file).parent) + f"\\KostSt_{client_db}_{year_id}.mdb"

    with open(new_filename + ".json", "w") as fwh:
        json.dump(res, fwh, indent=2)
    cur.close()
    conn.close()


def print_all_mdb_files(folder: str):
    for mdb_file in Path(folder).resolve().rglob("KostSt.mdb"):
        print_mdb_details(str(mdb_file))


if __name__ == "__main__":
    print_all_mdb_files("C:\\Projekte\\Datev")