cleanup_comments.py 3.3 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788
  1. import re
  2. import logging
  3. from datetime import date, timedelta
  4. from .db import SessionLocal
  5. from .logging_setup import run_cli
  6. from sqlalchemy import text
  7. logger = logging.getLogger(__name__)
  8. def main():
  9. with SessionLocal() as db:
  10. q = db.execute(text("SELECT * FROM dbo.Forderungen_Kommentar_Rohdaten"))
  11. for row in q.fetchall():
  12. for i, r in enumerate(row.Kommentar.split("\n"), 1):
  13. comment_date = (row.Timestamp + timedelta(minutes=i)).strftime("%Y-%m-%dT%H:%M:%S")
  14. comment_user = row.Benutzer_ID
  15. r = r.replace("'", "").replace("?", "").replace(" :", "").replace(" ", " ").strip()
  16. # d, u = get_date_and_user_from_comment(r)
  17. db.execute(
  18. text("INSERT INTO dbo.Forderungen_Kommentar (Client_DB, Beleg_Nr, [Timestamp], Benutzer_ID, Kommentar) "
  19. "VALUES (:Client_DB, :Beleg_Nr, :Timestamp, :Benutzer_ID, :Kommentar)")
  20. .execution_options(query_name='cleanup_comments.import_comments'),
  21. dict(Client_DB=row.Client_DB, Beleg_Nr=row.Beleg_Nr, Timestamp=comment_date, Benutzer_ID=comment_user, Kommentar=r),
  22. )
  23. db.commit()
  24. def get_details_from_comments():
  25. with SessionLocal() as db:
  26. q = db.execute(text("SELECT * FROM dbo.Forderungen_Kommentar_Rohdaten"))
  27. for row in q.fetchall():
  28. data = {}
  29. az = re.search(r"^AZ\:?\s(\d+\/\d+)", row.Kommentar)
  30. if az:
  31. data["Rechtsanwalt_Aktenzeichen"] = az[1]
  32. if "HP Schaden" in row.Kommentar:
  33. data["Versicherung_Typ"] = "Haftpflicht"
  34. if "Haftpflicht" in row.Kommentar:
  35. data["Versicherung_Typ"] = "Haftpflicht"
  36. if "VK Schaden" in row.Kommentar:
  37. data["Versicherung_Typ"] = "Vollkasko"
  38. if "VK-Schaden" in row.Kommentar:
  39. data["Versicherung_Typ"] = "Vollkasko"
  40. if "TK Schaden" in row.Kommentar:
  41. data["Versicherung_Typ"] = "Teilkasko"
  42. if "TK-Schaden" in row.Kommentar:
  43. data["Versicherung_Typ"] = "Teilkasko"
  44. s = re.search(r"Schaden (.*) bei (.*) eingereicht", row.Kommentar)
  45. if s:
  46. data["Schadennummer"] = s[1][:25]
  47. data["Vers_Adresse_ID"] = s[2].replace("'", "").lower()[:25]
  48. if data:
  49. db.execute(
  50. text("UPDATE dbo.Forderungen_Details SET " + ", ".join(f"{key} = :{key}" for key in data)
  51. + " WHERE Client_DB = :Client_DB AND Beleg_Nr = :Beleg_Nr")
  52. .execution_options(query_name='cleanup_comments.parse_details'),
  53. dict(data, Client_DB=row.Client_DB, Beleg_Nr=row.Beleg_Nr),
  54. )
  55. db.commit()
  56. def get_date_and_user_from_comment(r: str) -> tuple[date, str]:
  57. m = re.search(r"(\d\d\.\d\d?)\.?[\/\s](\w+)", r)
  58. if m:
  59. date_split = m[1].split(".")
  60. return date(2026, date_split[1], date_split[0]), m[2]
  61. if r.startswith("EWB"):
  62. return None, "mde"
  63. az = re.search(r"^AZ\:?\s(\d+\/\d+)", r)
  64. if az:
  65. return None, "mde"
  66. return None, "mde"
  67. if __name__ == "__main__":
  68. # main()
  69. run_cli(get_details_from_comments)