import logging import re from datetime import date, timedelta from sqlalchemy import text from .db import SessionLocal from .logging_setup import run_cli logger = logging.getLogger(__name__) def main(): with SessionLocal() as db: q = db.execute(text("SELECT * FROM dbo.Forderungen_Kommentar_Rohdaten")) for row in q.fetchall(): for i, r in enumerate(row.Kommentar.split("\n"), 1): comment_date = (row.Timestamp + timedelta(minutes=i)).strftime("%Y-%m-%dT%H:%M:%S") comment_user = row.Benutzer_ID r = r.replace("'", "").replace("?", "").replace(" :", "").replace(" ", " ").strip() # d, u = get_date_and_user_from_comment(r) db.execute( text( "INSERT INTO dbo.Forderungen_Kommentar (Client_DB, Beleg_Nr, [Timestamp], Benutzer_ID, Kommentar) " "VALUES (:Client_DB, :Beleg_Nr, :Timestamp, :Benutzer_ID, :Kommentar)" ).execution_options(query_name="cleanup_comments.import_comments"), dict( Client_DB=row.Client_DB, Beleg_Nr=row.Beleg_Nr, Timestamp=comment_date, Benutzer_ID=comment_user, Kommentar=r, ), ) db.commit() def get_details_from_comments(): with SessionLocal() as db: q = db.execute(text("SELECT * FROM dbo.Forderungen_Kommentar_Rohdaten")) for row in q.fetchall(): data = {} az = re.search(r"^AZ\:?\s(\d+\/\d+)", row.Kommentar) if az: data["Rechtsanwalt_Aktenzeichen"] = az[1] if "HP Schaden" in row.Kommentar: data["Versicherung_Typ"] = "Haftpflicht" if "Haftpflicht" in row.Kommentar: data["Versicherung_Typ"] = "Haftpflicht" if "VK Schaden" in row.Kommentar: data["Versicherung_Typ"] = "Vollkasko" if "VK-Schaden" in row.Kommentar: data["Versicherung_Typ"] = "Vollkasko" if "TK Schaden" in row.Kommentar: data["Versicherung_Typ"] = "Teilkasko" if "TK-Schaden" in row.Kommentar: data["Versicherung_Typ"] = "Teilkasko" s = re.search(r"Schaden (.*) bei (.*) eingereicht", row.Kommentar) if s: data["Schadennummer"] = s[1][:25] data["Vers_Adresse_ID"] = s[2].replace("'", "").lower()[:25] if data: db.execute( text( "UPDATE dbo.Forderungen_Details SET " + ", ".join(f"{key} = :{key}" for key in data) + " WHERE Client_DB = :Client_DB AND Beleg_Nr = :Beleg_Nr" ).execution_options(query_name="cleanup_comments.parse_details"), dict(data, Client_DB=row.Client_DB, Beleg_Nr=row.Beleg_Nr), ) db.commit() def get_date_and_user_from_comment(r: str) -> tuple[date, str]: m = re.search(r"(\d\d\.\d\d?)\.?[\/\s](\w+)", r) if m: date_split = m[1].split(".") return date(2026, date_split[1], date_split[0]), m[2] if r.startswith("EWB"): return None, "mde" az = re.search(r"^AZ\:?\s(\d+\/\d+)", r) if az: return None, "mde" return None, "mde" if __name__ == "__main__": # main() run_cli(get_details_from_comments)