# L-23 MCS 275 Fri 7 Mar 2008 : guidb1.py

# this is a simple GUI to brows OurPyFiles

from Tkinter import *
import MySQLdb

class GUIdb():
   """
   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")
         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():
   top = Tk()
   show = GUIdb(top)
   top.mainloop()

if __name__ == "__main__": main()