From 665f98684086085c7d22bbd281e73bfc4840acbe Mon Sep 17 00:00:00 2001
From: David Kaufmann <astra@ionic.at>
Date: Tue, 3 Jun 2025 19:10:38 +0200
Subject: update script

---
 get_zammad_ham_spam.py | 58 ++++++++++++++++++++++++++++++++------------------
 1 file changed, 37 insertions(+), 21 deletions(-)

diff --git a/get_zammad_ham_spam.py b/get_zammad_ham_spam.py
index 6decd12..1690fff 100755
--- a/get_zammad_ham_spam.py
+++ b/get_zammad_ham_spam.py
@@ -6,7 +6,19 @@ import subprocess
 import datetime
 import psycopg2
 
-valid_destinations = [
+# DB settings
+DB_HOST = "DBHOST"
+DB_USER = "rspamd"
+DB_PASS = "DBPASSWORD"
+DB_NAME = "zammad"
+
+# log file for spam subject logging
+SUBJECT_LOG = None
+
+# consider tickets closed in the last X days for learning
+NEW_MAIL_TIME_PERIOD = 7
+
+VALID_DESTINATIONS = [
     b'ticketsystem@hostname'
 ]
 
@@ -23,10 +35,10 @@ def send_to_rspamd(ticketid, title, spamflag, maildata):
 
 # Send mails from zammad to rspamc to learn already processed mails
 if __name__ == '__main__':
-    conn = psycopg2.connect("dbname=zammad host=DBHOST user=rspamd password=DBPASSWORD")
+    conn = psycopg2.connect(f"dbname={DB_NAME} host={DB_HOST} user={DB_USER} password={DB_PASS}")
     cur = conn.cursor()
     # get starting position, one week ago
-    last_week = (datetime.datetime.now() - datetime.timedelta(days=7)).date()
+    last_week = (datetime.datetime.now() - datetime.timedelta(days=NEW_MAIL_TIME_PERIOD)).date()
 
     # fetch all current tickets, which are in state closed and take its' first article
     cur.execute("SELECT tickets.id AS ticket, tickets.title AS title, tags.tag_item_id AS flag, MIN(ticket_articles.id) AS article \
@@ -48,25 +60,29 @@ if __name__ == '__main__':
         mail = cur.fetchall()
         if len(mail) != 1:
             print(f"ERROR: did find more than one mail in ticket {ticket}")
+            continue
+
+        filename, data = mail[0]
+        mailbytes = data.tobytes()
+
+        # safety check
+        has_been_delivered_to_me = False
+        for dest in VALID_DESTINATIONS:
+            if b'Delivered-To: ' + dest in mailbytes:
+                has_been_delivered_to_me = True
+                break
+
+        if not has_been_delivered_to_me:
+            print(f"ERROR: mail for ticket {ticket} does not contain Delivered-To header field")
+        elif flag == 1:
+            send_to_rspamd(ticket, title, 'spam', mailbytes)
+            if SUBJECT_LOG is not None and len(SUBJECT_LOG) > 0:
+                with open(SUBJECT_LOG, 'a+') as subject_log:
+                    subject_log.write(f"{title}\n")
+        elif flag is None:
+            send_to_rspamd(ticket, title, 'ham', mailbytes)
         else:
-            filename, data = mail[0]
-            mailbytes = data.tobytes()
-
-            # safety check
-            has_been_delivered_to_me = False
-            for dest in valid_destinations:
-                if b'Delivered-To: ' + dest in mailbytes:
-                    has_been_delivered_to_me = True
-                    break
-
-            if not has_been_delivered_to_me:
-                print(f"ERROR: mail for ticket {ticket} does not contain Delivered-To header field")
-            elif flag == 1:
-                send_to_rspamd(ticket, title, 'spam', mailbytes)
-            elif flag is None:
-                send_to_rspamd(ticket, title, 'ham', mailbytes)
-            else:
-                print(f"ERROR: mail flag in ticket {ticket} undefined: {flag}")
+            print(f"ERROR: mail flag in ticket {ticket} undefined: {flag}")
 
     cur.close()
     conn.close()
-- 
cgit v1.2.3