from tkinter import *
from tkinter.ttk import *
import pymysql.cursors

class DBTest:
    #Initiates thingies we'll use
    def __init__(self, root):
        self._create_tabs()
        self._layout()

    def _create_tabs(self):
        self.frame = Frame(root) # Main Frame
        self.nb = Notebook(self.frame) # The 'tab-thingy'

        # Create main tabs
        customer = Frame(self.nb)
        order = Frame(self.nb)
        product = Frame(self.nb)
        # Create customer NB
        self.customer = Notebook(customer)
        # Create Customer-tabs
        customer.insert = Frame(self.customer)
        customer.select = Frame(self.customer)
        customer.selAll = Frame(self.customer)
        customer.delete = Frame(self.customer)
        self._customer_insert_frame(customer.insert)
        self._customer_select_frame(customer.select)
        self._customer_selAll_frame(customer.selAll)
        self._customer_delete_frame(customer.delete)

        # Create order NB
        self.order = Notebook(order)
        # Create order-tabs
        order.insert = Frame(self.order)
        order.select = Frame(self.order)
        order.selAll = Frame(self.order)
        #self._order_insert_frame(order.insert)
        #self._order_select_frame(order.select)
        #self._order_selAll_frame(order.selAll)

        # Adds the tabs to the notebook
        self.nb.add(customer, text="Customer")
        self.nb.add(order, text="Order")
        self.nb.add(product, text="Product")
        self.customer.add(customer.insert, text="Insert")
        self.customer.add(customer.select, text="Select")
        self.customer.add(customer.selAll, text="Select ALL")
        self.customer.add(customer.delete, text="Delete Cust.")
        self.order.add(order.insert, text="Insert")
        self.order.add(order.select, text="Select")
        self.order.add(order.selAll, text="Select ALL")

    def _layout(self):
        # Layout
        self.frame.grid(row=0, column=0, sticky=(N,S,W,E))
        self.nb.grid(row=0, column=0, sticky=(N,S,W,E))
        self.customer.grid(row=0, column=0, columnspan=2, sticky=(N,S,W,E), pady=5)
        self.order.grid(row=0, column=0, columnspan=2, sticky=(N,S,W,E), pady=5)
        # Resize rules
        root.columnconfigure(0, weight=1)
        root.rowconfigure(0, weight=1)
        self.frame.columnconfigure(0, weight=2)
        self.frame.rowconfigure(0, weight=2)

    def _customer_insert_frame(self, insert):
        # Creates the Entry boxes to get data
        insert.name = Entry(insert)
        insert.address = Entry(insert)
        insert.phone = Entry(insert)
        # Makes a label and puts things out on the screen.
        Label(insert, text="Name: ").grid(row=0, column=0, sticky=E)
        insert.name.grid(row=0, column=1, sticky=W)
        Label(insert, text="Address: ").grid(row=1, column=0, sticky=E)
        insert.address.grid(row=1, column=1, sticky=W)
        Label(insert, text="Phone: ").grid(row=2, column=0, sticky=E)
        insert.phone.grid(row=2, column=1, sticky=W)
        # Using 'lambda: ' to prevent prepare_insert from running before we want it to.
        insert.submit_button = Button(insert, text="Submit", command=lambda: self.customer_prepare_insert(insert))
        insert.submit_button.grid(row=4, column=0, columnspan=2)
        insert.text = Text(insert, width=30, height=4, wrap=WORD)
        insert.text.grid(row=5,column=0,columnspan=2)

    def customer_prepare_insert(self, insert):
        # Gets values from the Entry boxes above.
        name = insert.name.get()
        address = insert.address.get()
        phone = insert.phone.get()
        # Checks if every field has something in them. (Does not check if only a space)
        if not all((name, address, phone)):
            insert.text.config(state=NORMAL)
            insert.text.delete(0.0, END)
            insert.text.insert(0.0, "Need to enter something in all boxes.")
            insert.text.config(state=DISABLED)
        # If every field is not None, do the query
        else:
            # Returns values entered if successful
            # Else it returns the error message.
            inserted = self.add_to_DB(name, address, phone)
            insert.text.config(state=NORMAL)
            insert.text.delete(0.0, END)
            insert.text.insert(0.0, inserted)
            insert.text.config(state=DISABLED)

    def _customer_select_frame(self, select):
        # Create the Entry boxes
        select.sID = Entry(select)
        select.name = Entry(select)
        select.address = Entry(select)
        select.phone = Entry(select)

        # Puts them out on the frame
        Label(select, text="Search for Databasedata\nenter at least 1 searchfield").grid(row=0, column=0, columnspan=2)
        Label(select, text="Search ID:").grid(row=1, column=0, sticky=E)
        select.sID.grid(row=1, column=1, sticky=W)
        Label(select, text="Search Name:").grid(row=2, column=0, sticky=E)
        select.name.grid(row=2, column=1, sticky=W)
        Label(select, text="Search address").grid(row=3, column=0, sticky=E)
        select.address.grid(row=3, column=1, sticky=W)
        Label(select, text="Search phone").grid(row=4, column=0, sticky=E)
        select.phone.grid(row=4, column=1, sticky=W)
        # Using lambda again on the button to not make it do the
        # prepare before we actually click the button
        select.submit_button = Button(select, text="Submit", command=lambda: self.customer_prepare_select(select))
        select.submit_button.grid(row=5, column=0, columnspan=2)
        select.text = Text(select, width=30, height=10, wrap=WORD)
        select.text.grid(row=6,column=0,columnspan=2)

    def customer_prepare_select(self, select):
        # Get value from the search fields.
        sID = select.sID.get()
        name = select.name.get()
        address = select.address.get()
        phone = select.phone.get()
        args = {}
        stmt = ""
        # Checks at least one have data. (Does not check if only a space)
        if sID != "" or name != "" or address != "" or phone != "":
            # If at least one have data we check which
            # one and adds the string for the query
            if sID != "":
                args['ID'] = "id='"+sID+"' AND "
            if name != "":
                args['name'] = "name='"+name+"' AND "
            if address != "":
                args['address'] = "address='"+address+"' AND "
            if phone != "":
                args['phone'] = "phone='"+phone+"'"
            for key, value in args.items():
                stmt += value
            # Removes the last ' AND '
            stmt = stmt[:-5]
            sql = "SELECT * FROM CUSTOMER WHERE {val}".format(val=stmt)
            db_result = self.get_from_DB(sql)
            textBox = ""
            # If we get rows back
            if db_result:
                # Iterates for all rows gotten
                for key in db_result:
                    textBox += "ID: {id} Name: {name}\nAddress: {address}\nPhone: {phone}\n\n".format(id=key['id'], name=key['name'], address=key['address'], phone=key['phone'])
            # If we searched for an entry that didnt exist
            else:
                textBox = "Could not find what you searched for."
        # If we enter no value in any box.
        else:
            textBox = "Must insert at least one value."

        # Updates the textfield
        select.text.config(state=NORMAL)
        select.text.delete(0.0, END)
        select.text.insert(0.0, textBox)
        select.text.config(state=DISABLED)

    def _customer_selAll_frame(self, selAll):
        #Create the button to show everything
        Label(selAll, text="Fetch all entries?").grid(row=0, column=0, sticky=E)
        selAll.submit_button = Button(selAll, text="Fetch!", command=lambda: self.customer_select_all_DB(selAll))
        selAll.submit_button.grid(row=0, column=1, sticky=W)
        selAll.text = Text(selAll, width=30, height=18, wrap=WORD)
        selAll.text.grid(row=1, column=0, columnspan=2)

    def customer_select_all_DB(self, selAll):
        sql = "SELECT * from CUSTOMER"
        # Gets all entries from the DB
        db_result = self.get_from_DB(sql)
        textBox = ""
        # If we get result returned
        if db_result:
            # Iterates for every Row
            for key in db_result:
                textBox += "ID: {id} Name: {name}\nAddress: {address}\nPhone: {phone}\n\n".format(id=key['id'], name=key['name'], address=key['address'], phone=key['phone'])
        # If no entries was found in the DB
        else:
            textBox = "There's nothing in the Database or some error n shit."
        # Updates the textfield
        selAll.text.config(state=NORMAL)
        selAll.text.delete(0.0, END)
        selAll.text.insert(0.0, textBox)
        selAll.text.config(state=DISABLED)

    def _customer_delete_frame(self, delete):
        Label(delete, text="Delete a user with the dropdown menu").grid(row=0, column=0, columnspan=2, sticky=E)
        sql = "SELECT name FROM customer"
        users = self.get_from_DB(sql)
        delete.result = []
        if users:
            for key in users:
                delete.result.append(key['name'])
        delete.result.sort()
        delete.result.insert(0, "None")
        delete.var = StringVar()
        delete.var.set("None")
        delete.menu = OptionMenu(delete, delete.var, *delete.result)
        delete.menu.grid(row=1, column=0)
        delete.submit_button = Button(delete, text="DELETE!", command=lambda: self.customer_delete_from_DB(delete))
        delete.submit_button.grid(row=2)
        delete.update_button = Button(delete, text="Update List", command=lambda: self.rebuild_window())
        delete.update_button.grid(row=4)
        delete.text = Text(delete, width=30, height=15, wrap=WORD)
        delete.text.grid(row=3, column=0, columnspan=2)

    def customer_delete_from_DB(self, delete):
        name = delete.var.get()
        column = "name"
        tab = "customer"
        x = self.delete_from_DB(column, tab, name)
        delete.text.delete(0.0, END)
        delete.text.insert(0.0, x)


    def delete_from_DB(self, column, tab, value):
        # Making the query
        # Takes the tab (order, customer, products) and which column (name, id, whatever)
        # and lastly the value of that column. This is to re-use this delete code for other tabs.
        sql = "DELETE FROM {tab} WHERE {column}='{value}'".format(tab=tab, column=column, value=value)
        # Connection config
        connection = self.db_settings()
        # Try to do the thing
        try:
            with connection.cursor() as cursor:
                # Executes the delete-query
                cursor.execute(sql)
                #Commit the changes
                connection.commit()
                # Checks how many rows that was affected
                result = cursor.rowcount
                # If 1 (row deleted) then show message.
                if result == 1:
                    result = "{name} from {tab}-tab has been deleted from the Database.".format(name=value, tab=tab)
                # If 0, no rows affected, thus nothing deleted.
                elif result == 0:
                    result = "Nothing deleted."
                else:
                    result = "Something weird happened. More than 1 row"

        except Exception as e:
            if e.args[0] == 1451:
                result = "The entry you try to remove is tied to an order."
            else:
                result = e
        finally:
            connection.close()

        # Return value
        return result

    def add_to_DB(self, *args): # args are name, address, phone
        # Connection config
        connection = self.db_settings()
        #Inserting the data
        try:
            # Adds it to DB
            with connection.cursor() as cursor:
                # Makes it into an SQL query
                sql = "INSERT INTO CUSTOMER (name, address, phone) VALUES ('{name}', '{address}', '{phone}')".format(name=args[0],address=args[1],phone=args[2])
                cursor.execute(sql)
                # Comit the query
                connection.commit()
                result = "Data added\nName: {name}\nAddress: {address}\nPhone: {phone}".format(name=args[0],address=args[1],phone=args[2])

        except Exception as error:
            code, message = error.args
            if code == 1062:
                result = "A person with the name '" +args[0]+ "' already exists in the Database."
            else:
                result = error
        finally:
            connection.close()
        # Returns whatever result we got
        return result

    def get_from_DB(self, sql):
        # Connection config
        connection = self.db_settings()
        # Try to get data
        try:
            with connection.cursor() as cursor:
                # The query from prepare_select()
                cursor.execute(sql)
                result = cursor.fetchall()
        except Exception as e:
            result = e
        finally:
            connection.close()
        return result

    # Destroys all windows, and rebuilds.
    # Got tired of trying other ways.
    def rebuild_window():
        self.frame.destroy()
        self._create_tabs()
        self._layout()

    ##########
    #The settings for the database, change this.
    ##########
    def db_settings(self):
        connection = pymysql.connect(host='',
                                    user='',
                                    password='',
                                    db='',
                                    charset='utf8',
                                    cursorclass=pymysql.cursors.DictCursor)
        return connection


root = Tk()
# Makes window not resizeable.
root.resizable(0,0)
# Window size
root.geometry("260x390")
# Title on the window
root.title("GUI Test :>")
# "activates"(words pls) the mainClass
app = DBTest(root)
# Main loop for the tkinder
root.mainloop()