# L-26 MCS 275 Fri 12 Mar 2010 : guidb2.py

# this GUI is to modify records to OurPyFiles

from Tkinter import *
import MySQLdb

class GUIdb():
   """
   a GUI to change 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.cursor = 0
      self.lb = Label(wdw, text='give key :')
      self.lb.grid(row=1,column=1)
      self.ky = Entry(wdw,width=5)
      self.ky.grid(row=1,column=2)
      self.br = Button(wdw, text='retrieve', \
         command = self.retrieve)
      self.br.grid(row=1,column=3)
      self.tp = Entry(wdw,width=4)
      self.tp.grid(row=2,column=0)
      self.dt = Entry(wdw,width=10)
      self.dt.grid(row=2,column=1)
      self.nm = Entry(wdw,width=15)
      self.nm.grid(row=2,column=2,columnspan=2)
      self.bi = Button(wdw, text='insert',\
         command = self.InsertData)
      self.bi.grid(row=3,column=0)
      self.startinsert = False
      self.bd = Button(wdw, text='delete',\
         command = self.DeleteData)
      self.startdelete = False
      self.bd.grid(row=3,column=1)
      self.bu = Button(wdw, text='update',\
         command = self.UpdateData)
      self.bu.grid(row=3,column=2)
      self.startupdate = False

   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 query(self,key):
      """
      Returns the query for all the information
      of the script with the given key.
      """ 
      q = 'select typedate.t, n, d, f ' + \
          'from typedate, filedata ' + \
          'where filedata.t = typedate.i ' + \
          'and filedata.i = %d' % key
      return q

   def display(self,R):
      """
      Displays the result of the query.
      """
      tp = R[0] + '-' + str(int(R[1]))
      self.tp.delete(0,END)
      self.tp.insert(INSERT,tp)
      self.dt.delete(0,END)
      self.dt.insert(INSERT,R[2])
      self.nm.delete(0,END)
      self.nm.insert(INSERT,R[3])

   def retrieve(self):
      """
      Retrieves all records from the scripts table.
      """
      if self.cursor == 0:
         self.message.set("please connect first")
      else:
         key = int(self.ky.get())
         m = 'retrieving key %d' % key
         self.message.set(m)
         q = self.query(key)
         lc = self.cursor.execute(q)
         m = 'retrieved record %d' % key
         self.message.set(m)
         R = self.cursor.fetchall()
         self.display(R[0])

   def CountFiles(self):
      """
      Returns the number of files.
      """
      q = 'select count(*) from filedata'
      r = self.cursor.execute(q)
      n = self.cursor.fetchone()
      return int(n[0])

   def CountTypes(self):
      """
      Returns the number of files.
      """
      q = 'select count(*) from typedate'
      r = self.cursor.execute(q)
      n = self.cursor.fetchone()
      return int(n[0])

   def InsertFileValues(self,v):
      """
      Inserts values in filedata table.
      """
      q = 'insert into filedata values '
      q = q + v
      self.cursor.execute(q)

   def InsertRecord(self,data):
      """
      Prepares data to insert a record.
      """
      L = data[0].split('-')
      t = L[0]; n = L[1]
      d = data[1]; f = data[2]
      nf = self.CountFiles()
      nt = self.CountTypes()
      v = '(' + '\"' + str(nf) + '\"' + \
          ',' + '\"' + f + '\"' + ',' + \
          '\"' + str(nt) + '\"' + ')'
      self.InsertFileValues(v)
      m = 'inserted %s' % v
      self.message.set(m)

   def InsertData(self):
      """
      Inserts a new record into the database.
      """
      t = self.tp.get()
      d = self.dt.get()
      f = self.nm.get()
      r = (t,d,f)
      if not self.startinsert:
         m = 'inserting %s,' % str(r)
         m = m + 'press to confirm'
         self.message.set(m)
         self.startinsert = True
      else:
         self.InsertRecord(r)
         m = 'inserted %s' % str(r)
         self.startinsert = False 

   def DeleteData(self):
      """
      Interface to remove a record.
      """
      key = int(self.ky.get())
      if not self.startdelete:
         m = 'removing record %d,' % key
         m = m + ' press again to confirm' 
         self.message.set(m)
         self.startdelete = True
      else:
         m = 'deleted record %d' % key
         self.message.set(m)
         self.startdelete = False

   def UpdateData(self):
      """
      Updates a record in the database.
      """
      key = int(self.ky.get())
      if not self.startupdate:
         m = 'updating record %d,' % key
         m = m + ' press again to confirm'
         self.message.set(m)
         self.startupdate = True
      else:
         m = 'updated record %d' % key
         self.message.set(m)
         self.startupdate = False

def main():
   top = Tk()
   show = GUIdb(top)
   top.mainloop()

if __name__ == "__main__": main()