from Tkinter import *
import sqlite3
#Witten by Steve Atchison 11/16/2013
def cleargui():
recdate.delete(0,END)
bookpage.delete(0,END)
pin.delete(0,END)
qref.delete(0,END)
problemnotes.delete(0,END)
textwindow.delete("1.0",END)
def connectdb():
connect = sqlite3.connect("ProblemParcels.db")
cur = connect.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS problemtable(recorddate text, deednumber text,pin text, quickref text, notes text)")
def addtodatabase():
connectdb()
#Get the data from the GUI
record_date=recdate.get()
deednum=bookpage.get()
parcelnum=pin.get()
quick_ref=qref.get()
deed_notes=problemnotes.get()
#add data to the data base
connect = sqlite3.connect("ProblemParcels.db")
cur=connect.cursor()
cur.execute("INSERT INTO problemtable VALUES(?,?,?,?,?)",([record_date,deednum,parcelnum,quick_ref,deed_notes]))
connect.commit()
cleargui()
getdata()
def getdata():#Display data in test window
cleargui()
addtoTextwin()
connect = sqlite3.connect("ProblemParcels.db")
cur=connect.cursor()
thedata = cur.execute("SELECT * FROM problemtable")
for row in thedata:
textwindow.insert("100.0",row[0]+" - "+row[1]+" - "+row[2]+" - "+row[3]+" - "+row[4]+"\n")
addlines()
def deleterow():#Delete a row from database using quick ref
deleterow=qref.get()
connect = sqlite3.connect("ProblemParcels.db")
cur=connect.cursor()
cur.execute("DELETE FROM problemtable WHERE quickref = ?",([deleterow]))
connect.commit()
cleargui()
getdata()
def findqref():#Find data using quick ref
findquickref=qref.get()
connect = sqlite3.connect("ProblemParcels.db")
cur=connect.cursor()
therow=cur.execute("SELECT * FROM problemtable WHERE quickref = ?",([findquickref]))
addtoTextwin()
for row in therow:
textwindow.insert("100.0",row[0]+" - "+row[1]+" - "+row[2]+" - "+row[3]+" - "+row[4]+"\n")
def addtoTextwin():
textwindow.insert("100.0","REC DATE DEED PIN QUICK REF NOTES \n\n")
def addlines():
textwindow.insert("100.0","------------------------------------------------------------------------------------------------------\n")
root=Tk()
root.title("Problem Parcels")
root.config(bg='lightblue')
#create frames
frame1=Frame(root)
frame1.pack()
frame2=Frame(root)
frame2.pack()
frame3=Frame(root)
frame3.pack()
frame4=Frame(root)
frame4.pack()
frame5=Frame(root)
frame5.pack()
recordlabel=Label(frame1,text="Recorded date:")
recordlabel.pack(side=LEFT)
recdate=Entry(frame1,width=10,text="Record date:")
recdate.pack(side=LEFT,padx=5,pady=2)
bookpagelabel=Label(frame1,text='Deed number:')
bookpagelabel.pack(side=LEFT,padx=5,pady=2)
bookpage=Entry(frame1,width=10,text="Deed Number:")
bookpage.pack(side=LEFT,padx=5,pady=2)
pinlabel=Label(frame1,text='Parcel number:')
pinlabel.pack(side=LEFT,padx=5,pady=2)
pin=Entry(frame1,text="Parcel number:")
pin.pack(side=LEFT,padx=5,pady=2)
qreflabel=Label(frame1,text='Quick Ref:')
qreflabel.pack(side=LEFT,padx=5,pady=2)
qref=Entry(frame1,text="Quick Ref:")
qref.pack(side=LEFT,padx=5,pady=2)
problemlabel=Label(frame3,text="Problem Notes:")
problemlabel.pack(side=LEFT,padx=5,pady=2)
problemnotes=Entry(frame3,text="Problem notes",width='100')
problemnotes.pack(side=LEFT,padx=5,pady=2)
textwindow=Text(root,width='140',height=35,background='white')
textwindow.pack()
aframe=Frame(root)
aframe.pack()
btn=Button(aframe,text='Add to Database ',command=addtodatabase)
btn.pack(padx=5,pady=5,side='left')
btn2=Button(aframe,text="LIST DATA",command=getdata)
btn2.pack(padx=5,pady=5,side='left')
btn3=Button(aframe,text="Clear window",command=cleargui)
btn3.pack(padx=5,pady=5,side='left')
btn4=Button(aframe,text='Delete line',command=deleterow)
btn4.pack(padx=5,pady=5,side='left')
findbtn=Button(aframe,text='Find using Quick ref',command=findqref)
findbtn.pack(padx=5,pady=5,side='left')
root.mainloop()
