beep boop

from tkinter import *
from tkinter import messagebox
import tkinter, tkinter.ttk as ttk
import tkinter.scrolledtext as stxt
import wckToolTips as tt
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
import os, sys, re, copy, time, getpass, pymssql, _mssql, decimal

def resource_path(relative_path):
    """ Get absolute path to resource, works for dev and for PyInstaller """
    try:
        # PyInstaller creates a temp folder and stores path in _MEIPASS
        base_path = sys._MEIPASS
    except Exception:
        base_path = os.path.abspath(".")

    return os.path.join(base_path, relative_path)

class Ticketing():
    # mail config
    mailConf = {'server': "192.168.100.254", 'sender': "alerts@excon.be", 'receiver': "helpdesk@excon.be"}

    # database config
    dbConf = {'server': "192.168.0.4", 'user': "kaseyasupport", 'password': "Kas123eya", 'database': "ksubscribers"}

    # message variables
    ticketVar = {}

    #3CX supplied variables
    try:
        ticketVar['callnr'] = sys.argv[1]
        internalnrReg = re.compile(r'7\d{2}')
        if internalnrReg.search(ticketVar['callnr']) and len(ticketVar['callnr'])==3:
            print("Detected internal number, quitting.")
            sys.exit()
    except IndexError:
        ticketVar['callnr'] = ""
    try:
        ticketVar['callname'] = sys.argv[2]
    except IndexError:
        ticketVar['callname'] = ""


    ticketVar['assignee'] = str(os.environ['userdnsdomain']).lower() + '\\' + getpass.getuser()

    def sendTicket():
        global mailConf

        message = """
        %(description)s

        <span style="display:none;">

        ---------------AutoGeneratedFields---------------<br>
        ~organization='%(org)s'<br>
        assignee='%(assignee)s'<br>
        ~username='%(username)s'<br>
        ~useremail='%(useremail)s'<br>
        ~machineid=’%(machineid)s’<br>
        ~machinegroup=’%(machinegr)s’<br>
        Caller Name: %(callname)s<br>
        Caller Number: %(callnr)s<br>
        ---------------------------------------------------------------<br>
        Generated by SMAC Aide.</span>
        """ % Ticketing.ticketVar
        print(message)

        try:
            ticketMail = MIMEMultipart()
            ticketMail['Subject'] = Ticketing.ticketVar['subject']
            ticketMail['From'] = Ticketing.mailConf['sender']
            ticketMail['To'] = Ticketing.mailConf['receiver']
            body = MIMEText(message, 'html')
            ticketMail.attach(body)

            smtpObj = smtplib.SMTP(Ticketing.mailConf['server'], timeout=5)
            smtpObj.sendmail(Ticketing.mailConf['sender'], Ticketing.mailConf['receiver'], ticketMail.as_string())
            print ("Successfully sent email")
            messagebox.showinfo("Mail Sent", "Mail was successfully sent.")
            time.sleep(2)
            sys.exit(0)
        except:
            print ("Error: unable to send email")
            messagebox.showerror("Error", "Mail error: Couldn't send out mail.")

    def getAllOrg():
        try:
            conn = pymssql.connect(host='192.168.0.4', user='kaseyasupport', password='Kas123eya',
                                   database='ksubscribers', login_timeout=5)
            cursor = conn.cursor()
            cursor.execute("""SELECT REF
            FROM kasadmin.vbo_Organizations_List
            WHERE REF <> 'unnamed' AND REF <> 'excon' AND REF <> 'excon-be' AND REF <> 'kserver' AND REF <> 'excon-fr' AND REF <> 'prosense-be' AND REF <> 'prosense-fr' AND REF <> 'kaseyatestaa' AND REF <> 'kaseyatestdd'
            ORDER BY REF""")
            Ticketing.ticketVar['eORG'] = [item[0] for item in cursor.fetchall()]
        except:
            print("Couldn't connect to DB.")
            Ticketing.ticketVar['eORG'] = ""
            messagebox.showerror("Error", "Database error: Couldn't connect to database1.")

    def getInitialSetup():
        try:
            if any(word in Ticketing.ticketVar['callname'] for word in ("HISALG", "ZNAPAT")):
                Ticketing.ticketVar['eORG'] = "excon.be-" + Ticketing.ticketVar['callname'].split('.')[0].lower()
            else:
                Ticketing.getAllOrg()
            #attempt a connection
            conn = pymssql.connect(host='192.168.0.4', user='kaseyasupport', password='Kas123eya',
                                       database='ksubscribers', login_timeout=5)
            cursor = conn.cursor()
            cursor.execute("SELECT @@VERSION")
        except:
            print("Couldn't connect to DB.")
            Ticketing.ticketVar['eORG'] = ""
            Ticketing.ticketVar['RC'] = ""
            messagebox.showerror("Error", "Database error: Couldn't connect to database2.\nCheck your connection.")
            Ticketing.getAllOrg()
            Ticketing.getRC()

    def getRC():
        try:
            conn = pymssql.connect(host='192.168.0.4', user='kaseyasupport', password='Kas123eya',
                                   database='ksubscribers', login_timeout=5)
            cursor = conn.cursor()
            cursor.execute("""SELECT DISTINCT(Machine_GroupID) as machine
            FROM KaseyaRemoteControl.Log
            join dbo.DenormalizedOrgToMach on KaseyaRemoteControl.Log.agentGuid = dbo.DenormalizedOrgToMach.AgentGuid
            join vAgentLabel st on st.agentGuid = KaseyaRemoteControl.Log.agentGuid
            WHERE startTime > DATEADD(mi,-120,getdate()) and datediff(mi,startTime,lastActiveTime) > 0 AND adminName LIKE %s
            """, str(Ticketing.ticketVar['assignee']).replace("\\", "_"))
            Ticketing.ticketVar['RC'] = [item[0] for item in cursor.fetchall()]
        except:
            print("Couldn't connect to DB.")
            messagebox.showerror("Error", "Database error: Couldn't connect to database3.")

    def getSubmitter():
        if Ticketing.ticketVar['useremail']:
            try:
                conn = pymssql.connect(host='192.168.0.4', user='kaseyasupport', password='Kas123eya',
                                       database='ksubscribers', login_timeout=5)
                cursor = conn.cursor()
                cursor.execute("""SELECT staffName
                FROM dbo.vSystemOrgStaff
                WHERE staffEmail LIKE %s""", '%' + str(Ticketing.ticketVar['useremail']) + '%')
                Ticketing.ticketVar['username'] = [item[0] for item in cursor.fetchall()]
            except:
                print("Couldn't connect to DB.")
                messagebox.showerror("Error", "Database error: Couldn't connect to database.")
        else: messagebox.showinfo("Warning", "No value found for submitter mail.")

    def getSubmitterMail():
        try:
            conn = pymssql.connect(host='192.168.0.4', user='kaseyasupport', password='Kas123eya', database='ksubscribers', login_timeout=5)
            cursor = conn.cursor()
            cursor.execute("""SELECT staffName
            FROM dbo.vSystemOrgStaff
            WHERE staffEmail LIKE %s""", '%' + str(Ticketing.ticketVar['username']) + '%')
            #Ticketing.ticketVar['useremail'] = [item[0] for item in cursor.fetchall()]
            return [item[0] for item in cursor.fetchall()]
        except:
            print("Couldn't connect to DB.")
            messagebox.showerror("Error", "Database error: Couldn't connect to database.")

# GUI part
class AideWindow(Frame):
    def setTicketVar(self):
        Ticketing.ticketVar['username'] = self.eSubmitter_val.get()
        Ticketing.ticketVar['useremail'] = self.eSubmail_val.get()
        Ticketing.ticketVar['org'] = self.eORG_val.get()
        Ticketing.ticketVar['subject'] = self.eSubject_val.get()
        try: Ticketing.ticketVar['description'] = self.eDesc.get("1.0", END)
        except: Ticketing.ticketVar['description'] = ""
        Ticketing.ticketVar['machineid'] = self.eRC_val.get().split('.')[0]
        try: Ticketing.ticketVar['machinegr'] = self.eRC_val.get().split('.', 1)[1]
        except: Ticketing.ticketVar['machinegr'] = ""
        Ticketing.ticketVar['callname'] = self.eCallN_val.get()
        Ticketing.ticketVar['callnr'] = self.eCallNr_val.get()

    def resetTicketVar(self):
        Ticketing.ticketVar.clear()
        #clear all entries

    def getSubmitterInfo(self, watisdit):
        print(watisdit)
        #if not (self.eSubmail_val.get()): Ticketing.ticketVar['useremail'] = Ticketing.getSubmitterMail()
        Ticketing.ticketVar['useremail'] = Ticketing.getSubmitterMail()
        print(Ticketing.ticketVar['useremail'])
        #elif not (self.eSubmitter_val.get()): Ticketing.getSubmmitterMail()
        #else: messagebox.showinfo("Warning", "Not enough submitter information.\nplease supply either Name or Email.")

    def setRC(self):
        self.setTicketVar()
        Ticketing.getRC()

    def Submit(self):
        self.setTicketVar()
        Ticketing.sendTicket()

    def __init__(self, parent=None):
        Frame.__init__(self, parent)
        # default config
        Frame.grid(self)
        root.geometry("340x360")
        root.resizable(width=False, height=False)
        root.wm_title("SMAC Aide")
        root.iconbitmap(r'images/smacaide256.ico')
        self.grid(padx=5, pady=5)
        self.columnconfigure(0, weight=1)
        self.rowconfigure(0, weight=1)
        # menu config
        menubar = Menu(root)
        root.config(menu=menubar)

        if any(word in Ticketing.ticketVar['callname'] for word in ("HISALG", "ZNAPAT")):
            Ticketing.ticketVar['eORG'] = "excon.be-" + Ticketing.ticketVar['callname'].split('.')[0].lower()
        else:
            Ticketing.getAllOrg()
        Ticketing.getRC()

        #some initial variables
        #Ticketing.getInitialSetup()

        # Create a menu button labeled "File" that brings up a menu
        filemenu = Menu(menubar, tearoff=0)
        menubar.add_cascade(label='Menu', menu=filemenu)
        filemenu.add_command(label='Settings', command='')
        filemenu.add_command(label='Reset', command=self.resetTicketVar)
        filemenu.add_separator()
        filemenu.add_command(label='Quit', command=root.destroy)

        # labels
        Label(self, text='Submitter Name: ').grid(row=1, pady=1, sticky=W)
        Label(self, text='Submitter Email: ').grid(row=2, pady=1, sticky=W)
        Label(self, text='Organisation: ').grid(row=3, pady=1, sticky=W)
        Label(self, text='Subject: ').grid(row=4, pady=1, sticky=W)
        Label(self, text='Description: ').grid(row=5, pady=1, sticky=W)
        Label(self, text='Assignee: ').grid(row=7, pady=1, sticky=W)
        Label(self, text='Asset: ').grid(row=8, pady=1, sticky=W)
        Label(self, text='Caller Display Name: ').grid(row=9, pady=1, sticky=W)
        Label(self, text='Caller Number: ').grid(row=10, pady=1, sticky=W)

        #icons
        self.refresh = PhotoImage(file=resource_path("images/refresh.png"))
        self.get = PhotoImage(file=resource_path("images/get.png"))
        self.mail = PhotoImage(file=resource_path("images/mail.png"))
        self.exit = PhotoImage(file=resource_path("images/exit.png"))

        # filled in entries variables
        self.eSubmitter_val = StringVar(root)
        self.eSubmail_val = StringVar(root)
        self.eSubject_val = StringVar(root)
        self.eORG_val = StringVar(root)
        self.eAssignee_val = StringVar(root)
        self.eAssignee_val.set(Ticketing.ticketVar['assignee'])
        self.eRC_val = StringVar(root)
        self.eCallN_val = StringVar(root)
        self.eCallN_val.set(Ticketing.ticketVar['callname'])
        self.eCallNr_val = StringVar(root)
        self.eCallNr_val.set(Ticketing.ticketVar['callnr'])
        self.setTicketVar()

        # entry boxes
        #submitter
        eSubmitter = ttk.Combobox(self, width=27, textvariable=self.eSubmitter_val, height=4)
        eSubmitter.bind("<<ComboboxSelected>>", self.setTicketVar)
        eSubmitter.bind("<Return>", self.getSubmitterInfo)
        eSubmitter['values'] = Ticketing.ticketVar['username']
        eSubmitter.grid(row=1, column=1)
        bSubmitter = Button(self, width=16, image=self.get, relief=GROOVE, takefocus=0, command=self.getSubmitterInfo)
        bSubmitter.grid(row=1, pady=1, padx=2, column=2)
        tt.register(bSubmitter, "Get submitter information")

        #Submit mail
        eSubmail = ttk.Combobox(self, width=27, textvariable=self.eSubmail_val, height=4)
        eSubmail.bind("<<ComboboxSelected>>", self.setTicketVar)
        eSubmail['values'] = Ticketing.ticketVar['useremail']
        eSubmail.grid(row=2, column=1)

        # organization
        eORG = ttk.Combobox(self, width=27, textvariable=self.eORG_val, height=4)
        eORG.bind("<<ComboboxSelected>>", self.setTicketVar)
        eORG['values'] = Ticketing.ticketVar['eORG']
        eORG.grid(row=3, column=1)

        #subject and description
        eSubject = Entry(self, textvariable=self.eSubject_val, width=30).grid(row=4, column=1, sticky='we')
        self.eDesc = stxt.ScrolledText(self, wrap=tkinter.WORD, width=25, height=5)
        self.eDesc.grid(row=6, columnspan=2, sticky='we')
        self.eDesc.bind("Tab", self.tk_focusNext())

        #assignee and rc
        eAssignee = Entry(self, width=30, textvariable=self.eAssignee_val, state=DISABLED).grid(row=7, column=1)
        eRC = ttk.Combobox(self, width=27, textvariable=self.eRC_val, height=4)
        eRC['values'] = Ticketing.ticketVar['RC']
        eRC.grid(row=8, column=1)
        bRC = Button(self, width=16, image=self.refresh, relief=GROOVE, takefocus=0, command=self.setRC)
        bRC.grid(row=8, pady=1, column=2)
        tt.register(bRC, "Refresh recent remote controls")

        #caller name en number
        eCallerName = Entry(self, textvariable=self.eCallN_val, width=30).grid(row=9, column=1)
        eCaller = Entry(self, textvariable=self.eCallNr_val, width=30).grid(row=10, column=1)

        #submit and exit buttons
        bSubmit = Button(self, width=100, height=20, image=self.mail, compound="left", text="Submit", command=self.Submit)
        bSubmit.grid(row=11, sticky='ws', pady=10)
        tt.register(bSubmit, "Send mail to the service desk")
        bExit = Button(self, width=100, height=20, image=self.exit, compound="left", text="Exit", command=root.destroy)
        bExit.grid(row=11, column=1, columnspan=2, sticky='es', pady=10)
        tt.register(bExit, "Exit SMAC Aide")

if __name__ == '__main__':
    root = Tk()
    AideWindow(root)
    root.mainloop()