get_zammad_ham_spam.py

1 files changed, 72 insertions, 0 deletions

diff --git a/get_zammad_ham_spam.py b/get_zammad_ham_spam.py
new file mode 100755
index 0000000..410845d
--- /dev/null
+++ b/get_zammad_ham_spam.py
@@ -0,0 +1,72 @@
+#!/usr/bin/env python3
+
+# apt install python3-psycopg2
+
+import subprocess
+import datetime
+import psycopg2
+
+valid_destinations = [
+    b'ticketsystem@hostname'
+]
+
+
+def send_to_rspamd(ticketid, spamflag, maildata):
+    # send the mail to rspamc to process as ham/spam
+    with subprocess.Popen(['/usr/bin/rspamc', f"learn_{spamflag}"], stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE) as rspamd:
+        stdout, stderr = rspamd.communicate(input=maildata)
+        if len(stdout) > 0:
+            print(f"Ticket {ticketid} stdout: {stdout.decode('utf-8')}")
+        if len(stderr) > 0:
+            print(f"Ticket {ticketid} stderr: {stderr.decode('utf-8')}")
+
+
+# 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")
+    cur = conn.cursor()
+    # get starting position, one week ago
+    last_week = (datetime.datetime.now() - datetime.timedelta(days=7)).date()
+
+    # fetch all current tickets, which are in state closed and take its' first article
+    cur.execute("SELECT tickets.id AS ticket, tags.tag_item_id AS flag, MIN(ticket_articles.id) AS article \
+            FROM tickets LEFT JOIN tags ON tags.o_id=tickets.id \
+            LEFT JOIN ticket_articles ON tickets.id=ticket_articles.ticket_id \
+            WHERE tickets.state_id=4 \
+            AND (tags.tag_item_id=1 OR tags.tag_item_id IS NULL) \
+            AND tickets.close_at>=%s \
+            GROUP BY tickets.id, tags.tag_item_id", (last_week.strftime('%Y-%m-%d'),))
+    tickets = cur.fetchall()
+
+    for ticketrow in tickets:
+        ticket, flag, article = ticketrow
+        # fetch the associated mail file from db, there should only be one with store_object_id=2 (Ticket::Article::Mail)
+        cur.execute("SELECT stores.filename AS filename, store_provider_dbs.data AS data \
+                FROM stores LEFT JOIN store_provider_dbs ON store_provider_dbs.id=store_file_id \
+                WHERE stores.store_object_id=2 \
+                AND stores.o_id=%s", (str(article),))
+        mail = cur.fetchall()
+        if len(mail) != 1:
+            print(f"ERROR: did find more than one mail in ticket {ticket}")
+        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, 'spam', mailbytes)
+            elif flag is None:
+                send_to_rspamd(ticket, 'ham', mailbytes)
+            else:
+                print(f"ERROR: mail flag in ticket {ticket} undefined: {flag}")
+
+    cur.close()
+    conn.close()