Gryff Priest DSDB

from tkinter import * #import the entire tkinter module
import imaplib #import IMAP based email utilities
import email #other useful universal email utilities
import email.header #a specific email utility pertaining to the subject
import pymysql #Python mysql connector/support, the official one didn't install properly
import datetime #Date and time utilities


connection = pymysql.connect(host="localhost", #All this is the configuration for connecting to the MySQL Database
                             port=3306,
                             user="root",
                             passwd="",
                             db="dollarsolardb")

cursor = connection.cursor() #Allows the insertion of SQL commands


INBOX = "INBOX" #Simply creates INBOX in the form of a string as the function doesn't accept it

name = [] #Creates a list for the names generated later
phone = [] #Creates a list for the phones generated later
postcode = [] #Creates a list for the postcodes generated later
email_address = [] #Creates a list for the email_addresses generated later
message = [] #Creates a list for the messages generated later

def AddData(name,postcode,phone,email_address,message): #creates the function that the Add to DB button runs
    command = "INSERT INTO customerinfo(name,postcode,phone,email_address,message) ""VALUES(%s,%s,%s,%s,%s)" #This is the SQL command
    data = (name,postcode,phone,email_address,message) #An easy way of encapsulating all the data from the lists
    cursor.execute(command, data) #Tells the cursor to run the SQL command
    connection.commit() #Saves the changes to the databases


def LoginValidate(): #The first command that is run by a button, it logs in to the IMAP system
    success = 1 #Stores whether the login attemp was successful
    try: #This try command basically gives room for error when running something, the application will continue after doing a special set of commands
        short.login(entryUsername.get(), entryPassword.get()) #Runs the IMAP login command
    except short.error: #If the login command returns an error, do:
        success = 0 #Success = 0 because logging in didn't work
    if success == 1:
        MainWindow()#Open the next window
    else:
        entryUsername.delete(0,END) #Clear the email entry
        entryUsername.insert(0,"ERROR, CAN'T LOG IN") #fill it with an error message

def Logout(): #hypothetically the last command, it closes and logs out of everything, email, sql etc.
    connection.close() #close the mysql connection
    try:
        short.close() #try the close the email inbox
    except short.error:
        labText.set("logouterror, please close the app conventially") #This should occur if the user tries to logout without opening an inbox, nothing really wrong
    short.logout() #logs out of the IMAP system
    mWindow.destroy() #closes the mainwindow
    root.destroy() #closes the login window

def LoginShort(event): #This function only exists due to quirky tkinter syntax, if a function is given an argument (event) tkinter will run it in the config and break
    LoginValidate() #this is the Logincommand, thats all this does


def ButtonCommand(): #Similary to the other command, as the function takes an argument, it needs a command to run it
    EmailGet(short) #runs the emailget command


def EmailGet(short): #command that fetches the specific emails
    check, blank = short.select(INBOX) #The IMAP select command selects the INBOX letterbox
    if check != "OK": #it returns a status value, if everything is good it returns "OK", this checks if it is was OK
        labText.set("No inbox!") #Gives an error status

    if  boxTime.get() == "One Day": #Finds the selected time range, creates a variable for each range in day
        dayrange = 1
    elif boxTime.get() == "One Week":
        dayrange = 7
    elif boxTime.get() == "30 Days":
        dayrange = 30
    elif boxTime.get() == "One Year":
        dayrange = 365


    check, emails = short.search(None, '(SUBJECT "Dollar Solar Website Contact Form Submission")') #IMAP SEARCH command, selects emails with a specific value, in this case, those with a certain subject
    if check != "OK":
        labText.set("No emails!") #Shows an error message
    email_list = emails[0].split() #creates a list from the returned values of the SEARCH command
    for i in email_list: #iterates through the emails in email_list
        check, email_info, = short.fetch(i, '(RFC822)') #IMAP FETCH command, fetches the specific info of the email, the meat if you will
        if check != "OK":
            labText.set("Cannot extract email data")
        else: #If the command returns  an all good
            details = email.message_from_bytes(email_info[0][1]) #the internal data in the email
            details_date = email.utils.parsedate_tz(details['Date']) #the date of email arrival

            datelist = [] #its an empty list of the dates
            datelist.append(details_date[0:3]) #adds the Year Month and Day to the list

            date_year = (int(details_date[0])) #splits up the year month and day
            date_month = (int(details_date[1]))
            date_day = (int(details_date[2]))

            datedays = datetime.date(date_year,date_month,date_day) #Changes all those in to a datetime format
            todaydate = datetime.date.today() #todays date in datetime format

            if abs(todaydate-datedays).days >= dayrange: #if the absolute value of the two dates is greater than the specified date range, then it is too old
                within_daterange = False
            else:
                within_daterange = True
                for part in details.walk(): #runs through the email and puts each part on a new line
                    if part.get_content_type() == 'text/plain': #if the each part is normal text
                        emailDisplay.insert(END, part.get_payload()) #Insert each part of the email in to the textbox, line by line
    buttonAdd.config(state="normal") #Allows the button that adds the text to the database to be pressed, considering there is actually text now
    labText.set("Everything ok!, please edit the text if necassary. When completed click the 'Add to DB'") #further instruction to the user
def DB_ADD(): #function that adds whats in the text box to the database
    for line in emailDisplay.get(1.0,'end-1c').splitlines(): #iterate through each line in the textbox (1.0 = first character, 'end-1c' = last
                            if line[0:10] == "Your Name:": #if the first 10 characters are "Your Name:" add the rest of the line to the list
                                name.append(line[11:])
                            elif line[0:12] == "> Your Name:": #These lines are more or less the same, with different character lengths and words...
                                print(line[13:])
                                name.append(line[13:])
                            elif line[0:12] == "*Your Name*:":
                                print(line[13:])
                                name.append(line[13:])

                            if line[0:6] == "Phone:":
                                print(line[7:])
                                phone.append(line[7:])

                            elif line[0:8] == "> Phone:":
                                print(line[9:])
                                phone.append(line[9:])

                            elif line[0:8] == "*Phone*:":
                                print(line[9:])
                                phone.append(str(line[9:]))


                            if line[0:9] == "Postcode:":
                                print(line[10:])
                                postcode.append(line[10:])

                            elif line[0:11] == "> Postcode:":
                                print(line[12:])
                                postcode.append(line[12:])

                            elif line[0:11] == "*Postcode*:":
                                print(line[12:])
                                postcode.append(line[12:])

                            if line[0:19] == "Your Email Address:":
                                print(line[20:])
                                email_address.append(line[20:])

                            elif line[0:21] == "> Your Email Address:":
                                print(line[22:])
                                email_address.append(line[22:])

                            elif line[0:21] == "*Your Email Address*:":
                                print(line[22:])
                                email_address.append(line[22:])


                            if line[0:8] == "Message:":
                                print(line[9:])
                                message.append(line[9:])

                            elif line[0:10] == "> Message:":
                                print(line[11:])
                                message.append(line[11:])

                            elif line[0:10] == "*Message*:":
                                print(line[11:])
                                message.append(line[11:])



    print(name) #these are pretty much diagnostic just print the lists to make sure everything has worked
    print(phone)
    print(postcode)
    print(email_address)
    print(message)

    iterate = 0 #A helper for the next line just a value that counts how many times it has looped

    for number in name: #for each value in name (and hypothetically every other list),
        AddData(name[iterate], postcode[iterate],phone[iterate], email_address[iterate], message[iterate]) #run the add data function with the values as arguments
        iterate = iterate + 1 #add 1 to iterate so we don't add the same data again
    labText.set("Added "+str(iterate)+" emails to DB") #confirmation to the user
def MainWindow(): #this funcion is the window that opens after logging in
    global mWindow #global makes variables callable in all functions, otherwise it would error if it was referenced
    mWindow = Toplevel(root) #creates a new window
    mWindow.minsize(width=600, height=400) #defines the size of the window
    mWindow.title("Dollar Solar Database Manager") #names the window

    global labStatus
    global labText
    labText = StringVar()
    labText.set("Logged in successfully, awaiting processing...")
    labStatus = Label(mWindow, textvariable = labText, fg=("green")) #creates text that explains to the user what is going on
    labStatus.grid(row=0, sticky=W) #positions it

    labGuidance = Label(mWindow, text="Please select a time range, and click the button") #tells the user what to do
    labGuidance.grid(row=1, sticky=W)

    global boxTime
    boxTime = Spinbox(mWindow,values=("One Day", "One Week", "30 Days", "One Year"), state="readonly") #creates the menu where the user can select a time range
    boxTime.grid(row=2, column=0, sticky=W)

    buttonFetch = Button(mWindow, text = ("Fetch Emails"), command=ButtonCommand) #creates a button that executes the email fetch function
    buttonFetch.place(x=135,y=40) #an alternate and more exact, if not more finnicky form of placement

    global buttonAdd
    buttonAdd = Button(mWindow, text = ("Add to DB"), command=DB_ADD, state=DISABLED) #creates a button that executes the add to DB function, initially disabled
    buttonAdd.place(x=220, y=40)

    buttonQuit = Button(mWindow, text = "Logout/Quit", command=Logout) #button that executes the logout function
    buttonQuit.place(x=565, y=40)

    global emailDisplay
    emailDisplay = Text(mWindow, fg="gray", wrap=WORD) #this creates the textbox that holds the email data, it is editable for changes
    emailDisplay.grid(row=3, column=0, sticky=W)

    scroller = Scrollbar(mWindow) #this creates the scrollbar for the text display, why it has to be its own widget, well I'm not sure
    scroller.grid(row=3,column=0,sticky=N+E+S)

    emailDisplay.config(yscrollcommand=scroller) #tells the textbox to listen to the scrollbar
    scroller.config(command=emailDisplay.yview) #tells the scrollbar where it belongs


short = imaplib.IMAP4_SSL("imap.gmail.com") #This variable is simply an abbreviation of the IMAPLIB commands, so I don't have to type it every time I want to use it
#note the SSL for safe travel of the data

root = Tk() #creates the login window
root.title("Dollar Solar Database Manager") #names the window
root.maxsize(width=600, height=350) #sets the size

loginmessage = Label(root, text = "Please Log In", font=("Cambria",28)) #creates the original instruction
loginmessage.grid(row=0, sticky=N)

labUsername = Label(root, text = ("EMAIL:")) #tells the user where to put their details
labUsername.grid(row=1, sticky =W)

global entryUsername
entryUsername = Entry(root, text = ("")) #creates the entry where the user enters their details
entryUsername.grid(row=1, column =0, sticky=E)
entryUsername.bind("<Return>",LoginShort) #the bind function enables certain keys to be able to perform actions in certain widgets, in this case it does the same thing as hitting the button

labPassword = Label(root, text = ("PASSWORD:")) #tells the user where to put their details
labPassword.grid(row=2, sticky=W)

entryPassword = Entry(root, text = (""),show="*") #where the user puts their password, it shows asterisks (*) so no cheeky people look over their shoulder"
entryPassword.grid(row=2, column =0, sticky=E)
entryPassword.bind("<Return>",LoginShort)

buttonConfirm = Button(root, text = ("LOGIN"), command=LoginValidate) #Attempts to log the user in using the LoginValidate function
buttonConfirm.grid(row =1,column =1, sticky=E)


root.mainloop() #Creates the loop that maintains the tkinter windows, necassary for a working application