RSWQueueBot Take Control Messenger

This bot built in python will listen for new events added to the email_event table and then send them to the Trillian support chat.
Once an event is processed, the processed field in SQL will be updated to 1, ensuring that the event is not reprocessed again.

The objective is to notify everyone that there is a new live chat incoming, no matter what it is.

# -*- coding: utf-8 -*-
"""
Created on Sun May  5 07:28:50 2024
 
@author: jcorona
 
http://quesarito/doku.php?id=python:rswqueuebot_takecontrolmessenger
 
Required Sister Application: 
http://quesarito/doku.php?id=python:rswqueuebot_takecontrolemailprocessor
 
Required Database Name:
RSWQueueBotDB
 
CREATE DATABASE RSWQueueBotDB;
 
 
Required Tables:
 
CREATE TABLE Email_Event (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Subject NVARCHAR(255),
    Body NVARCHAR(MAX)
);
-----
 
ALTER TABLE Email_Event
ADD processed BIT DEFAULT 0 NOT NULL;
 
 
"""
import pyodbc
import time
import re
from pywinauto.application import Application
from pynput.keyboard import Controller, Key
 
def connect_to_database():
    conn = pyodbc.connect('DRIVER={SQL Server};'
                          'SERVER=localhost\\liberty;'
                          'DATABASE=RswQueueBotDB;'
                          'UID=Sa;'
                          'PWD=Rsw02p@ss')
    return conn
 
def fetch_emails(cursor):
    cursor.execute("SELECT id, subject, body FROM Email_event WHERE processed = 0")
    emails = cursor.fetchall()
    cleaned_emails = []
    for email in emails:
        email_id, subject, body = email
        # Clean the subject from new lines and other unwanted characters
        cleaned_subject = re.sub(r'[\r\n]+', ' ', subject).strip()
        cleaned_emails.append((email_id, cleaned_subject, body))
    return cleaned_emails
 
def extract_description(email_body):
    start = "Support request Description:"
    end = "Request Queue:"
    try:
        description = email_body.split(start)[1].split(end)[0].strip()
        return description if description else "None"
    except IndexError:
        return "None"
 
def update_processed_status(cursor, email_id):
    cursor.execute("UPDATE Email_event SET processed = 1 WHERE id = ?", email_id)
    cursor.commit()
 
def main():
    conn = connect_to_database()
    cursor = conn.cursor()
    keyboard = Controller()
    app = Application().connect(title="Trillian", visible_only=True)
    main_window = app.window(title='Trillian')
    main_window.set_focus()
    main_window.type_keys('Support Chat', with_spaces=True)
    keyboard.press(Key.enter)
    keyboard.release(Key.enter)
 
    while True:
        emails = fetch_emails(cursor)
        for email in emails:
            email_id, subject, body = email
            description = extract_description(body)
            subject = subject if subject else "None"  # Ensure subject is not None
            description = description if description else "None"  # Ensure description is not None
            # Automation to enter data into Trillian
            support_chat = app.window(title='Support Chat Trillian')
            support_chat.set_focus()
            message = f"Incoming Live Chat Subject: {subject}\n  Description: {description} @all"
            support_chat.type_keys(message, with_spaces=True)
            time.sleep(2)  # Added delay for reliability in typing
            keyboard.press(Key.enter)
            keyboard.release(Key.enter)
            keyboard.press(Key.enter)
            keyboard.release(Key.enter)
            update_processed_status(cursor, email_id)
 
        time.sleep(5)  # Check for new unprocessed sql entries every 5 seconds
 
if __name__ == '__main__':
    main()