# L-26 MCS 275 Fri 10 Mar 2017 : guidb1.py
"""
this is a simple GUI to browse OurPyFiles
"""

from tkinter import Tk, StringVar, IntVar, END
from tkinter import Button, Label, Listbox, Radiobutton
# import MySQLdb
import pymysql

class GUIdb(object):
    """
    a GUI to see records in our database
    """
    def __init__(self, wdw):
        """
        The records are displayed in listboxes.
        Buttons allow to connect, retrieve, and
        browse forward and backward.
        Radiobuttons set sorting preferences.
        A message label displays status info.
        """
        wdw.title("GUI to OurPyFiles db")
        self.message = StringVar()
        self.message.set("welcome to our database")
        self.messageLabel = Label(wdw, \
           textvariable=self.message)
        self.messageLabel.grid(row=0, column=0, columnspan=4)
        self.bc = Button(wdw, text='connect', \
           command=self.connect)
        self.bc.grid(row=1, column=0)
        self.br = Button(wdw, text='retrieve', \
           command=self.retrieve)
        self.br.grid(row=1, column=1)
        self.bn = Button(wdw, text='next 10', \
           command=self.next10)
        self.bn.grid(row=1, column=2)
        self.bp = Button(wdw, text='previous 10', \
           command=self.prev10)
        self.bp.grid(row=1, column=3)
        self.tt = Label(wdw, text='type')
        self.tt.grid(row=2, column=0)
        self.dd = Label(wdw, text='date')
        self.dd.grid(row=2, column=1)
        self.ff = Label(wdw, text='file')
        self.ff.grid(row=2, column=2, columnspan=2)
        self.Lt = Listbox(wdw, width=4, height=10)
        self.Lt.grid(row=3, column=0)
        self.Ld = Listbox(wdw, width=10, height=10)
        self.Ld.grid(row=3, column=1)
        self.Ln = Listbox(wdw, width=15, height=10)
        self.Ln.grid(row=3, column=2, columnspan=2)
        self.sL = Label(wdw, text='sort by')
        self.sL.grid(row=4, column=0)
        self.RadioSort = IntVar()
        self.st = Radiobutton(wdw, text='type', \
            variable=self.RadioSort, value=1)
        self.st.grid(row=4, column=1)
        self.sd = Radiobutton(wdw, text='date', \
            variable=self.RadioSort, value=2)
        self.sd.grid(row=4, column=2)
        self.sf = Radiobutton(wdw, text='file', \
            variable=self.RadioSort, value=3)
        self.sf.grid(row=4, column=3)
        self.RadioSort.set(1)
        self.RadioOrder = IntVar()
        self.asc = Radiobutton(wdw, text='ascending order', \
            variable=self.RadioOrder, value=1)
        self.asc.grid(row=5, column=0, columnspan=2)
        self.dsc = Radiobutton(wdw, text='descending order', \
            variable=self.RadioOrder, value=2)
        self.dsc.grid(row=5, column=2, columnspan=2)
        self.RadioOrder.set(1)
        self.cursor = 0
        self.records = 0
        self.pos = 0

    def connect(self):
        """
        Connects to the database OurPyFiles.
        """
        try:
            # db = MySQLdb.connect(db="OurPyFiles")
            db = pymysql.connect(db="OurPyFiles")
            self.message.set("connected to \"OurPyFiles\"")
            self.cursor = db.cursor()
        except:
            self.message.set("failed to connect to \"OurPyFiles\"")

    def clear(self):
        """
        Clears all listboxes.
        """
        self.Lt.delete(0, END)
        self.Ld.delete(0, END)
        self.Ln.delete(0, END)

    def insert(self, item):
        """
        Inserts one record item to the listboxes.
        """
        t = item[0] + '-' + str(int(item[1]))
        self.Lt.insert(END, t)
        self.Ld.insert(END, str(item[2]))
        self.Ln.insert(END, item[3])

    def next10(self):
        """
        Displays the next 10 records in listboxes.
        """
        if self.records == 0:
            self.message.set("no records to show")
        else:
            self.clear()
            for i in range(self.pos, self.pos+10):
                if i >= len(self.records):
                    break
                self.insert(self.records[i])
            self.pos = self.pos + 10
            if self.pos >= len(self.records):
                self.pos = len(self.records) - 1

    def prev10(self):
        """
        Displays the previous 10 records in listboxes.
        """
        if self.records == 0:
            self.message.set("no records to show")
        else:
            self.clear()
            self.pos = self.pos - 20
            if self.pos < 0:
                self.pos = 0
            for i in range(self.pos, self.pos+10):
                if i >= len(self.records):
                    break
                self.insert(self.records[i])
            self.pos = self.pos + 10

    def query(self):
        """
        Returns the query to select all records,
        taking input from the radio buttons.
        """
        q = 'select * from scripts'
        ord = ''
        if self.RadioOrder.get() == 1:
            ord = 'asc'
        elif self.RadioOrder.get() == 2:
            ord = 'desc'
        if self.RadioSort.get() == 1:
            q = q + ' order by t, n ' + ord
        elif self.RadioSort.get() == 2:
            q = q + ' order by d ' + ord
        elif self.RadioSort.get() == 2:
            q = q + ' order by f ' + ord
        return q

    def retrieve(self):
        """
        Retrieves all records from the scripts table.
        """
        if self.cursor == 0:
            self.message.set("please connect first")
        else:
            q = self.query()
            lc = self.cursor.execute(q)
            m = 'retrieved %d records' % int(lc)
            self.message.set(m)
            R = self.cursor.fetchall()
            self.clear()
            for i in range(0, len(R)):
                if i >= 10:
                    break
                self.insert(R[i])
            self.records = R
            self.pos = 10

def main():
    """
    Launches the event loop of the GUI.
    """
    top = Tk()
    GUIdb(top)
    top.mainloop()

if __name__ == "__main__":
    main()