| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798 |
- 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)
|