| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788 |
- import re
- import logging
- from datetime import date, timedelta
- from .db import SessionLocal
- from .logging_setup import run_cli
- from sqlalchemy import text
- 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)
|