cleanup_comments.py 3.5 KB

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