from tkinter import *
from tkinter.ttk import Treeview,Combobox
from tkinter.dialog import *
from tkinter.messagebox import *
import random
import datetime
import sqlite3 as sql
import os.path as op

ivdIndexTimes=0
rsvIndexTimes=0
ivdIndexTimes1=0
rsvIndexTimes1=0
ivdIndexTimesb=0
rsvIndexTimesb=0
ratQIndexTimes=0
ratQIndexTimes1=0
ratQIndexTimesb=0
rmtQIndexTimes=0
rmtQIndexTimes1=0
stfIndexTimes=0
stfIndexTimes1=0
stfIndexTimesb=0
drsIndexTimes = 0
drsIndexTimes1 = 0
drsIndexTimesb= 0
drIndexTimes = 0
drIndexTimes1 = 0


#log in
def reg():
	global staffid,name,manager
	staffid=unEt.get()
	s2=pwEt.get()
	t1=len(staffid)
	t2=len(s2)
	conn = sql.connect('hotel.db')
	cursor=conn.cursor()
	cursor.execute('select passwd from staff where id=?', (staffid,))
	passwd=str(cursor.fetchone())
	passwd=passwd[1:-2]
	if s2==passwd:
		frm.pack_forget()
		cursor.execute('select name from staff where id=?', (staffid,))
		name = str(cursor.fetchone())
		name=name[2:-3]
		cursor.execute('select manager from staff where id=?', (staffid,))
		manager= str(cursor.fetchone())
		manager=manager[1:-2]
		showMenu()
	else:
		c["text"]="Error password or staff ID."
	unEt.delete(0,t1)
	pwEt.delete(0,t2)
	cursor.close()
	conn.close()

#main menu frame
def showMenu():
	global frmMenu
	frmMenu = Frame(root)
	Label(frmMenu, text='Welcome, %s!' % (name)).pack()
	indBtm = Button(frmMenu, text="Profile",command=showIdv).pack()
	resBtm = Button(frmMenu, text="Reservation",command=showRsv).pack()
	rqBtm = Button(frmMenu, text="Rate Query",command=showDR).pack()
	rtBtm=Button(frmMenu, text="Rate Type Query",command=showRatQ).pack()
	rmtBtm=Button(frmMenu, text="Room Type Query",command=showRmtQ).pack()
	rsBtm=Button(frmMenu, text="Daily Room Availability Query",command=showDRS).pack()
	if manager=="'True'":
		ssBtm=Button(frmMenu, text="Set Staff Info",command=showSatffInfo).pack()
	loBtm=Button(frmMenu, text="Log Out",command=logoff).pack()
	frmMenu.pack()

def logoff():
	frmMenu.pack_forget()
	ShowLogIn()

def IvdShowHis():
	global resultRsv, rsvIndexTimes,rsvIndexTimes1
	id=ivdID['text']
	if id:
		showRsv()
		srcRsvTree.delete(*srcRsvTree.get_children())
		conn = sql.connect('hotel.db')
		c = conn.cursor()
		resultRsv = []
		c.execute('select confirmID, name, surname, ciDate, coDate, status from Reservation inner join Individual on Reservation.guest1ID = Individual.ID or Reservation.guest2ID = Individual.ID where id=?', (id,))
		resultRsv=c.fetchall()
		for i in range(len(resultRsv)):
			srcRsvTree.insert('',i, values=resultRsv[i])
		sreRsvsultLabel['text'] = '%s result(s).' % (len(resultRsv))
		c.close()
		conn.close()
		rsvIndexTimes = rsvIndexTimes + rsvIndexTimes1
		rsvIndexTimes1 =len(resultRsv)
	else:
		showinfo(title="ERROR", message="Please select a profile to search history.")

def searchStf():
	global stfresult,stfIndexTimes1,stfIndexTimes
	srcStfTree.delete(*srcStfTree.get_children())
	sname=searchStfNEt.get()
	ssur=searchStfSEt.get()
	sid=searchStfIDEt.get()
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	stfresult=[]
	if (len(sname) != 0 and len(ssur) != 0):
		c.execute('select id,name,surname from Staff where name=? and surname=?', (sname, ssur))
		stfresult = c.fetchall()
		for i in range(len(stfresult)):
			srcStfTree.insert('', i, values=stfresult[i])
	elif (len(sname) != 0):
		c.execute('select id,name,surname from Staff where name=?', (sname,))
		stfresult = c.fetchall()
		for i in range(len(stfresult)):
			srcStfTree.insert('', i, values=stfresult[i])
	elif (len(ssur) != 0):
		c.execute('select id,name,surname from Staff where surname=?', (ssur,))
		stfresult = c.fetchall()
		for i in range(len(stfresult)):
			srcStfTree.insert('', i, values=stfresult[i])
	elif (len(sid) != 0):
		if sid =="ALL":
			c.execute('select id,name,surname from Staff ')
			stfresult = c.fetchall()
			for i in range(len(stfresult)):
				srcStfTree.insert('', i, values=stfresult[i])
		else:
			c.execute('select id,name,surname from Staff where id=?', (sid,))
			stfresult = c.fetchall()
			for i in range(len(stfresult)):
				srcStfTree.insert('', i, values=stfresult[i])
	stfResultL['text'] = '%s result(s).' % (len(stfresult))
	c.close()
	conn.close()
	stfIndexTimes = stfIndexTimes +stfIndexTimes1
	stfIndexTimes1 = len(stfresult)

def searchDS():
	global dsresult,drIndexTimes,drIndexTimes1
	srcDRTree.delete(*srcDRTree.get_children())
	sf=searchDRfromDateEt.get()
	st=searchDRToDateEt.get()
	rt=searchDRRTEt.get()
	rat=searchDRRatEt.get()
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	dsresult=[]
	if (len(sf) != 0 and len(st) != 0 and len(rt)!=0 and len(rat)!=0):
		c.execute('select fromDate,toDate,roomType,rateType,rate,id  from DailyRate where fromDate<? and toDate>? and roomType=? and rateType', (sf, st,rt,rat))
		dsresult = c.fetchall()
		for i in range(len(dsresult)):
			srcDRTree.insert('', i, values=dsresult[i])
	elif (len(sf) != 0 and len(st) != 0 and len(rt)!=0 ):
		c.execute('select fromDate,toDate,roomType,rateType,rate,id  from DailyRate where fromDate<? and toDate>? and roomType=?',(sf, st, rt))
		dsresult = c.fetchall()
		for i in range(len(dsresult)):
			srcDRTree.insert('', i, values=dsresult[i])
	elif (len(sf) != 0 and len(st) != 0 and len(rat)!=0):
		c.execute('select fromDate,toDate,roomType,rateType,rate,id  from DailyRate where fromDate<? and toDate>? and rateType=?',(sf, st, rat))
		dsresult = c.fetchall()
		for i in range(len(dsresult)):
			srcDRTree.insert('', i, values=dsresult[i])
	elif (len(sf) != 0 and len(st) != 0):
		c.execute('select fromDate,toDate,roomType,rateType,rate,id  from DailyRate where fromDate<? and toDate>?',(sf, st))
		dsresult = c.fetchall()
		for i in range(len(dsresult)):
			srcDRTree.insert('', i, values=dsresult[i])
	drResultL['text']='%s result(s).' % (len(dsresult))
	c.close()
	conn.close()
	drIndexTimes=drIndexTimes+drIndexTimes1
	drIndexTimes1=len(dsresult)


def searchIvd():
	global sresult,ivdIndexTimes,ivdIndexTimes1
	srcIdvTree.delete(*srcIdvTree.get_children())
	sname=searchNameEt.get()
	ssur=searchSurnameEt.get()
	sivdID=searchIDEt.get()
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	sresult=[]
	if(len(sname)!=0 and len(ssur)!=0 ):
		c.execute('select name,surname,id from individual where name=? and surname=?', (sname, ssur))
		sresult=c.fetchall()
		for i in range(len(sresult)):
			srcIdvTree.insert('',i,values=sresult[i])
	elif(len(sname)!=0):
		c.execute('select name,surname,id from individual where name=?', (sname,))
		sresult=c.fetchall()
		for i in range(len(sresult)):
			srcIdvTree.insert('',i,values=sresult[i])
	elif(len(ssur)!=0):
		c.execute('select name,surname,id from individual where surname=?', (ssur,))
		sresult=c.fetchall()
		for i in range(len(sresult)):
			srcIdvTree.insert('',i,values=sresult[i])
	elif(len(sivdID)!=0):
		c.execute('select name,surname,id from individual where id=?', (sivdID,))
		sresult=c.fetchall()
		for i in range(len(sresult)):
			srcIdvTree.insert('',i,values=sresult[i])
	sresultLabel['text']='%s result(s).' % (len(sresult))
	c.close()
	conn.close()
	ivdIndexTimes=ivdIndexTimes+ivdIndexTimes1
	ivdIndexTimes1=len(sresult)

def searchRsv():
	global resultRsv,rsvIndexTimes,rsvIndexTimes1
	srcRsvTree.delete(*srcRsvTree.get_children())
	sname=searchResNameEt.get()
	ssurname=searchResSurnameEt.get()
	sciDate=searchResCIEt.get()
	sresID=searchResIDEt.get()
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	resultRsv = []
	if(len(sname)!=0 and len(ssurname)!=0 and len(sciDate)!=0):
		c.execute('select confirmID, name, surname, ciDate, coDate, status from Reservation inner join Individual on Reservation.guest1ID = Individual.ID or Reservation.guest2ID = Individual.ID where name=? and surname=? and ciDate=?', (sname,ssurname,sciDate))
		resultRsv=c.fetchall()
		for i in range(len(resultRsv)):
			srcRsvTree.insert('',i, values=resultRsv[i])
	elif (len(sname)!=0 and len(ssurname)!=0):
		c.execute('select confirmID, name, surname, ciDate, coDate, status from Reservation inner join Individual on Reservation.guest1ID = Individual.ID or Reservation.guest2ID = Individual.ID where name=? and surname=?',(sname, ssurname))
		resultRsv = c.fetchall()
		for i in range(len(resultRsv)):
			srcRsvTree.insert('', i, values=resultRsv[i])
	elif len(sciDate)!=0 :
		c.execute('select confirmID, name, surname, ciDate, coDate, status from Reservation inner join Individual on Reservation.guest1ID = Individual.ID or Reservation.guest2ID = Individual.ID where ciDate=?',(sciDate, ))
		resultRsv = c.fetchall()
		for i in range(len(resultRsv)):
			srcRsvTree.insert('', i, values=resultRsv[i])
	elif len(sresID)!=0 :
		c.execute('select confirmID, name, surname, ciDate, coDate, status from Reservation inner join Individual on Reservation.guest1ID = Individual.ID or Reservation.guest2ID = Individual.ID where confirmID=?',(sresID,))
		resultRsv = c.fetchall()
		for i in range(len(resultRsv)):
			srcRsvTree.insert('', i, values=resultRsv[i])
	sreRsvsultLabel['text']='%s result(s).' % (len(resultRsv))
	c.close()
	conn.close()
	rsvIndexTimes = rsvIndexTimes + rsvIndexTimes1
	rsvIndexTimes1=len(resultRsv)

def searchRmt():
	global resultRmt,rmtQIndexTimes,rmtQIndexTimes1
	srcRmtTree.delete(*srcRmtTree.get_children())
	scode=searchRmCodeEt.get()
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	resultRmt =[]
	if (len(scode)!=0):
		c.execute('select id,name,tier from RoomType where id=?',(scode,))
		resultRmt=c.fetchall()
		for i in range(len(resultRmt)):
			srcRmtTree.insert('', i, values=resultRmt[i])
	sRmtresultLabel['text']='%s result(s).' % (len(resultRmt))
	c.close()
	conn.close()
	rmtQIndexTimes=rmtQIndexTimes+rmtQIndexTimes1
	rmtQIndexTimes1=len(resultRmt)

def showAllRmt():
	global resultRmt,rmtQIndexTimes,rmtQIndexTimes1
	srcRmtTree.delete(*srcRmtTree.get_children())
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	resultRmt =[]
	c.execute('select id,name,tier from RoomType ')
	resultRmt=c.fetchall()
	for i in range(len(resultRmt)):
		srcRmtTree.insert('', i, values=resultRmt[i])
	sRmtresultLabel['text']='%s result(s).' % (len(resultRmt))
	c.close()
	conn.close()
	rmtQIndexTimes = rmtQIndexTimes +rmtQIndexTimes1
	rmtQIndexTimes1=len(resultRmt)

def searchDRS():
	global srcDRSTree,drsIndexTimes,drsIndexTimes1,resultDRS,drsResultL,ll
	srcDRSTree.delete(*srcDRSTree.get_children())
	fd=searchfromDateEt.get()
	td=searchToDateEt.get()
	rt=searchRTEt.get()
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	resultDRS = []
	if (len(rt) != 0):
		c.execute('select date,rmType,hadSold,maintain,id from DailyRoomStatus where rmType=? and date between ? and ?', (rt,fd,td))
		resultDRS = c.fetchall()
	else:
		c.execute('select date,rmType,hadSold,maintain,id from DailyRoomStatus where date between ? and ?',(fd, td))
		resultDRS = c.fetchall()
	print(resultDRS)
	ll = [['' for i in range(4)] for j in range(len(resultDRS))]
	for i in range(len(resultDRS)):
		c.execute('select quantity from RoomType where ID=?', (resultDRS[i][1],))
		value = int(c.fetchone()[0])
		a = value - int(resultDRS[i][2]) - int(resultDRS[i][3])
		ll[i][0] = resultDRS[i][0]
		ll[i][1] = resultDRS[i][1]
		ll[i][2] = a
		ll[i][3]=resultDRS[i][4]
		print(ll)
		srcDRSTree.insert('', i, values=ll[i])
	drsResultL['text'] = '%s result(s).' % (len(resultDRS))
	c.close()
	conn.close()
	print(ll)
	drsIndexTimes = drsIndexTimes + drsIndexTimes1
	drsIndexTimes1=len(resultDRS)

def searchRat():
	global resultRat,ratQIndexTimes,ratQIndexTimes1
	srcRatTree.delete(*srcRatTree.get_children())
	scode=searchCodeEt.get()
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	resultRat =[]
	if (len(scode)!=0):
		c.execute('select code,name from RateType where code=?',(scode,))
		resultRat=c.fetchall()
		for i in range(len(resultRat)):
			srcRatTree.insert('', i, values=resultRat[i])
	sRatresultLabel['text']='%s result(s).' % (len(resultRat))
	c.close()
	conn.close()
	ratQIndexTimes=ratQIndexTimes+ratQIndexTimes1
	ratQIndexTimes1=len(resultRat)


def showAllRat():
	global resultRat,ratQIndexTimes,ratQIndexTimes1
	srcRatTree.delete(*srcRatTree.get_children())
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	resultRat =[]
	c.execute('select code,name from RateType ')
	resultRat=c.fetchall()
	for i in range(len(resultRat)):
		srcRatTree.insert('', i, values=resultRat[i])
	sRatresultLabel['text']='%s result(s).' % (len(resultRat))
	c.close()
	conn.close()
	ratQIndexTimes = ratQIndexTimes + ratQIndexTimes1
	ratQIndexTimes1=len(resultRat)

def clearDR():
	drIDL['text']=''
	drRTEt.delete(0,END)
	drFDEt.delete(0,END)
	drTDEt.delete(0,END)
	drRatTEt.delete(0,END)
	drNoteTx.delete(0.0,END)
	drRatEt.delete(0,END)

def clearIvd():
	ivdNameEt.delete(0,END)
	ivdSurEt.delete(0,END)
	ivdEmailEt.delete(0,END)
	ivdPhoneEt.delete(0,END)
	ivdAddEt.delete(0,END)
	ivdCityEt.delete(0,END)
	ivdZipEt.delete(0,END)
	ivdCtryEt.delete(0,END)
	ivdNoteTx.delete(0.0,END)
	titleC.current(0)
	ivdID['text']=""
	searchNameEt.delete(0,END)
	searchSurnameEt.delete(0,END)
	searchIDEt.delete(0,END)
	titleC.current(0)

def clearRsv():
	resIDL['text']=""
	resMakeDateL['text']=""
	resCIDEt.delete(0,END)
	resCODEt.delete(0,END)
	resRNEt.delete(0,END)
	resRTEt.delete(0,END)
	resGst1NL['text']=""
	resGst1Et.delete(0,END)
	resGst2NL['text']=""
	resGst2Et.delete(0,END)
	resGst1SL['text']=""
	resGst2SL['text']=""
	resRatET.delete(0,END)
	resAmountL['text']=""
	resStaffL['text']=staffid
	resNCET.delete(0,END)
	resCNET.delete(0,END)
	resEpEt.delete(0,END)
	rsvNoteTx.delete(0.0,END)
	resCTC.current(0)
	resStatusC.current(0)
	resFromC.current(0)

def clearRat():
	ratRCEt.delete(0,END)
	ratNameEt.delete(0,END)
	ratPPC.current(0)
	ratCPTx.delete(0.0,END)
	ratDesTx.delete(0.0,END)

def clearRmt():
	rmtRCEt.delete(0,END)
	rmtNameEt.delete(0,END)
	rmtTierEt.delete(0,END)
	rmtQEt.delete(0,END)
	rmtDesTx.delete(0.0,END)

def clearStf():
	stfIDL['text']=""
	stfNameEt.delete(0,END)
	stfSNameEt.delete(0,END)
	stfPwEt.delete(0,END)
	stfMngC.current(0)
	stfNoteTx.delete(0.0,END)

def clearDRS():
	drsIDL['text']=""
	drsDateEt.delete(0,END)
	drsRtEt.delete(0,END)
	drsHsEt['text']=""
	drsMtEt.delete(0,END)
	desNoteTx.delete(0.0,END)

#search >>show rate type
def showRatInfo(code):
	clearRat()
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	c.execute('select * from RateType where code=?', (code,))
	result=c.fetchone()
	ratRCEt.insert(0,result[0])
	ratNameEt.insert(0,result[1])
	if result[2]=='True':
		ratPPC.current(1)
	else:
		ratPPC.current(2)
	ratCPTx.insert(1.0,result[3])
	if result[4]:
		ratDesTx.insert(1.0,result[4])
	c.close()
	conn.close()

#search >>show room type
def showRmtInfo(code):
	clearRmt()
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	c.execute('select * from RoomType where id=?', (code,))
	result=c.fetchone()
	rmtRCEt.insert(0,result[0])
	rmtNameEt.insert(0,result[1])
	rmtTierEt.insert(0,result[2])
	rmtQEt.insert(0,result[3])
	if result[4]:
		rmtDesTx.insert(1.0,result[4])
	c.close()
	conn.close()

#search >>show result profile
def showIvdPro(id):
	clearIvd()
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	c.execute('select * from individual where id=?', (id,))
	result = c.fetchone()
	ivdID['text']=id
	ivdNameEt.insert(0,result[2])
	ivdSurEt.insert(0,result[3])
	if result[4]:
		ivdEmailEt.insert(0,result[4])
	if result[5]:
		ivdPhoneEt.insert(0,result[5])
	if result[6]:
		ivdAddEt.insert(0,result[6])
	if result[7]:
		ivdCityEt.insert(0,result[7])
	if result[8]:
		ivdZipEt.insert(0,result[8])
	if result[9]:
		ivdCtryEt.insert(0,result[9])
	if result[10]:
		ivdNoteTx.insert(1.0,result[10])
	if result[1]=='MS':
		titleC.current(2)
	elif result[1]=='MR':
		titleC.current(1)
	c.close()
	conn.close()

def showStfInfo(sid):
	clearStf()
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	c.execute('select * from Staff where id=?', (sid,))
	result = c.fetchone()
	stfIDL['text']=result[0]
	stfNameEt.insert(0,result[1])
	stfSNameEt.insert(0,result[2])
	stfPwEt.insert(0,result[3])
	if result[4]=='True':
		stfMngC.current(1)
	else:
		stfMngC.current(2)
	if result[5]:
		stfNoteTx.insert(1.0,result[5])
	c.close()
	conn.close()

def showDRInfo(id):
	clearDR()
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	c.execute('select * from DailyRate where id=?', (id,))
	result = c.fetchone()
	drIDL['text']=id
	drRTEt.insert(0,result[1])
	drFDEt.insert(0,result[3])
	drTDEt.insert(0,result[4])
	drRatTEt.insert(0,result[2])
	drRatEt.insert(0,result[5])
	if result[6]:
		drNoteTx.insert(1.0,result[6])
	c.close()
	conn.close()

def showDRSInfo(id):
	clearDRS()
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	c.execute('select * from DailyRoomStatus where id=?', (id,))
	result=c.fetchone()
	drsIDL['text'] = result[0]
	drsDateEt.insert(0, result[1])
	drsRtEt.insert(0, result[2])
	drsHsEt['text'] = result[3]
	drsMtEt.insert(0, result[4])
	if result[5]:
		desNoteTx.insert(1.0, result[5])
	c.close()
	conn.close()

#search >>show reservation
def showRsvInfo(rid):
	clearRsv()
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	c.execute('select * from Reservation inner join Individual on Reservation.guest1ID = Individual.ID or Reservation.guest2ID=Individual.ID   where confirmID=?', (rid,))
	result = c.fetchone()
	resIDL['text']=rid
	resMakeDateL['text']=result[1]
	resCIDEt.insert(0,result[2])
	resCODEt.insert(0,result[3])
	resRNEt.insert(0,result[6])
	resRTEt.insert(0,result[8])
	resRatET.insert(0,result[9])
	resAmountL['text'] = result[10]
	if result[14]:
		resNCET.insert(0, result[14])
	if result[15]:
		resCNET.insert(0, result[15])
	if result[16]:
		resEpEt.insert(0, result[15])
	if result[17]:
		rsvNoteTx.insert(1.0, result[17])
	resGst1NL['text']=result[20]
	resGst1Et.insert(0,result[18])
	resGst1SL['text']=result[21]
	if result[11]:
		resStaffL['text'] = result[11]
	if result[7]=='Phone':
		resFromC.current(1)
	elif result[7]=='FAX':
		resFromC.current(2)
	elif result[7]=='Email':
		resFromC.current(3)
	elif result[7]=='Web':
		resFromC.current(4)
	elif result[7]=='OTA':
		resFromC.current(5)
	if result[12]=='Confirm':
		resStatusC.current(1)
	elif result[12]=='Cancel':
		resStatusC.current(2)
	elif result[12]=='C/I':
		resStatusC.current(3)
	elif result[12]=='C/O':
		resStatusC.current(4)
	elif result[12]=='No show':
		resStatusC.current(5)
	if result[13]=='UP':
		resCTC.current(1)
	elif result[13]=='VISA':
		resCTC.current(2)
	elif result[13]=='Master':
		resCTC.current(3)
	elif result[13]=='AE':
		resCTC.current(4)
	elif result[13]=='JCB':
		resCTC.current(5)
	elif result[13]=='Transfer':
		resCTC.current(6)
	elif result[13]=='Cash':
		resCTC.current(7)
	result = c.fetchone()
	if result:
		resGst2NL['text']=result[20]
		resGst2Et.insert(0,result[18])
		resGst2SL['text']=result[21]
	c.close()
	conn.close()

def selectDR(event):
	global drIndexTimes
	item = srcDRTree.identify_row(event.y)
	item = item[1:]
	item = int(item, 16) - drIndexTimes - 1
	showDRInfo(dsresult[item][5])


def selectDRS(event):
	global drsIndexTimes
	item = srcDRSTree.identify_row(event.y)
	item = item[1:]
	item = int(item, 16) - drsIndexTimes - 1
	showDRSInfo(ll[item][3])

def selectStf(event):
	global stfIndexTimes
	item=srcStfTree.identify_row(event.y)
	item=item[1:]
	item = int(item, 16) - stfIndexTimes - 1
	showStfInfo(stfresult[item][0])

def selectRsv(event):
	global rsvIndexTimes
	item=srcRsvTree.identify_row(event.y)
	item=item[1:]
	item=int(item,16)-rsvIndexTimes-1
	showRsvInfo(resultRsv[item][0])

def selectIvd(event):
	global ivdIndexTimes
	item=srcIdvTree.identify_row(event.y)
	item=item[1:]
	item=int(item,16)-ivdIndexTimes-1
	showIvdPro(sresult[item][2])

def selectRat(event):
	global ratQIndexTimes,ratQIndexTimes1
	item = srcRatTree.identify_row(event.y)
	item = item[1:]
	item = int(item,16) - ratQIndexTimes-1
	showRatInfo(resultRat[item][0])

def selectRmt(event):
	global rmtQIndexTimes,rmtQIndexTimes1
	item = srcRmtTree.identify_row(event.y)
	item = item[1:]
	print(item)
	print(rmtQIndexTimes)
	item = int(item,16) - rmtQIndexTimes-1
	print(item)
	showRmtInfo(resultRmt[item][0])

def deleteIvd():
	if ivdID["text"]:
		if askquestion(title="Deletion Comfirmation", message="Do you sure to delete the profile?")=='yes':
			conn = sql.connect('hotel.db')
			c = conn.cursor()
			c.execute('delete  from individual where id=?', (ivdID['text'],))
			clearIvd()
			c.close()
			conn.commit()
			conn.close()
			srcIdvTree.delete(*srcIdvTree.get_children())
	else:
		showinfo(title="ERROR",message="Please select a profile to delete it.")

def deleteStf():
	code = stfIDL['text']
	if code:
		if askquestion(title="Deletion Comfirmation", message="Do you sure to delete the profile?")=='yes':
			conn = sql.connect('hotel.db')
			c = conn.cursor()
			c.execute('delete  from Staff where id=?', (stfIDL['text'],))
			clearStf()
			c.close()
			conn.commit()
			conn.close()
			srcStfTree.delete(*srcStfTree.get_children())
	else:
		showinfo(title="ERROR", message="Please select a profile to delete it.")


def initDRS():
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	newid = int(random.uniform(100000000000, 999999999999))
	n = 1
	while n == 1:
		c.execute('select * from DailyRoomStatus where id=?', (newid,))
		if c.fetchone():
			newid = int(random.uniform(100000000000, 999999999999))
			continue
		else:
			break
	drsIDL['text']=newid
	c.execute('insert into DailyRoomStatus values (?,?,?,0,0,null)',(drsIDL['text'],drsDateEt.get(),drsRtEt.get()))
	drsHsEt['text']=0
	drsMtEt.insert(0,0)
	c.close()
	conn.commit()
	conn.close()

def saveDR():
	code = drIDL['text']
	if code:
		conn = sql.connect('hotel.db')
		c = conn.cursor()
		c.execute('select * from DailyRate where id=?', (code,))
		if c.fetchone():
			c.execute('update DailyRate set roomType=?, rateType=?,fromDate=?, toDate=? , rate=? , notes=? where id=?',
					  (drRTEt.get(), drRatTEt.get(), drFDEt.get(), drTDEt.get(),drRatEt.get(), drNoteTx.get(1.0, END), code))
		else:
			c.execute('insert into DailyRate values(?,?,?,?,?,?,?)',
					  (code, drRTEt.get(), drRatTEt.get(), drFDEt.get(), drTDEt.get(),drRatEt.get(), drNoteTx.get(1.0, END)))
		c.close()
		conn.commit()
		conn.close()
	else:
		showinfo(title="ERROR", message="Please select a Daily Rate to save it.")

def saveDRS():
	code = drsIDL['text']
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	c.execute('update DailyRoomStatus set maintain=?,Notes=? where id=?',(drsMtEt.get(),desNoteTx.get(1.0,END),code ))
	c.close()
	conn.commit()
	conn.close()

def saveStf():
	code = stfIDL['text']
	if code:
		conn = sql.connect('hotel.db')
		c = conn.cursor()
		c.execute('select * from Staff where id=?', (code,))
		if c.fetchone():
			c.execute('update Staff set name=?, surname=?,passwd=?, manager=? , Notes=? where id=?',(stfNameEt.get(),stfSNameEt.get(),stfPwEt.get(),stfMngC.get(),stfNoteTx.get(1.0,END), code))
		else:
			c.execute('insert into Staff values(?,?,?,?,?,?)',(code,stfNameEt.get(),stfSNameEt.get(),stfPwEt.get(),stfMngC.get(),stfNoteTx.get(1.0,END)))
		c.close()
		conn.commit()
		conn.close()
	else:
		showinfo(title="ERROR", message="Please select a profile to save it.")

def saveIvd():
	newid=ivdID['text']
	if newid:
		conn = sql.connect('hotel.db')
		c = conn.cursor()
		c.execute('select name,surname,id from individual where id=?', (newid,))
		if c.fetchone():
			c.execute('update individual set title=?, name=?, surname=?, email=?, phone=?, address=?, city=?, zipCode=?, country=?,Notes=? where id=?',(titleC.get(),ivdNameEt.get(),ivdSurEt.get(),ivdEmailEt.get(),ivdPhoneEt.get(),ivdAddEt.get(),ivdCityEt.get(),ivdZipEt.get(),ivdCtryEt.get(),ivdNoteTx.get(1.0,END),newid))
		else:
			c.execute('insert into individual values (?,?,?,?,?,?,?,?,?,?,?)',(newid,titleC.get(),ivdNameEt.get(),ivdSurEt.get(),ivdEmailEt.get(),ivdPhoneEt.get(),ivdAddEt.get(),ivdCityEt.get(),ivdZipEt.get(),ivdCtryEt.get(),ivdNoteTx.get(1.0,END)))
		c.close()
		conn.commit()
		conn.close()
	else:
		showinfo(title="ERROR", message="Please select a profile to edit or create a new profile.")

def saveRsv():
	id=resIDL['text']
	if id:
		conn = sql.connect('hotel.db')
		c = conn.cursor()
		c.execute('select * from Reservation where confirmID=?',(id,))
		if c.fetchone():
			c.execute('delete from Reservation where confirmID=?',(id,))
			makeDate=resMakeDateL['text']
		else:
			c.execute('select date("now")')
			value=c.fetchone()
			makeDate=value[1]
		c.execute('insert into Reservation values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',(id,makeDate,resCIDEt.get(),resCODEt.get(),resGst1Et.get(),resGst2Et.get(),resRNEt.get(),resFromC.get(),resRTEt.get(),resRatET.get(),resAmountL['text'],resStaffL['text'],resStatusC.get(),resCTC.get(),resNCET.get(),resCNET.get(),resEpEt.get(),rsvNoteTx.get(1.0,END)))
		c.execute('select * from Reservation where confirmID=?',(id,))
		if c.fetchone():
			conn.commit()
		c.close()
		conn.close()
	else:
		showinfo(title="ERROR", message="Please select a Rate Type to edit or create a new Rate Type.")

def saveRat():
	code=ratRCEt.get()
	conn = sql.connect('hotel.db')
	if ratPPC.get()=='Y':
		prepaid='True'
	else:
		prepaid='False'
	c = conn.cursor()
	c.execute('select * from RateType where code=?', (code,))
	if c.fetchone():
		c.execute('update RateType set name=?, prepaid=?,cancellation=?, description=? where code=?',(ratNameEt.get(),prepaid,ratCPTx.get(1.0,END),ratDesTx.get(1.0,END),ratRCEt.get() ))
	else:
		c.execute('insert into RateType values(?,?,?,?,?)',(ratRCEt.get(),ratNameEt.get(),prepaid,ratCPTx.get(1.0,END),ratDesTx.get(1.0,END)))
	c.close()
	conn.commit()
	conn.close()

def ivdBookNow():
	if ivdID['text']:
		showRsv()
		resGst1Et.insert(0,ivdID['text'])
		checkGst1()
		srcRsvTree.delete(*srcRsvTree.get_children())
		conn = sql.connect('hotel.db')
		c = conn.cursor()
		newid = int(random.uniform(100000000, 999999999))
		n = 1
		while n == 1:
			c.execute('select * from Reservation where confirmID=?', (newid,))
			if c.fetchone():
				newid = int(random.uniform(100000000, 999999999))
				continue
			else:
				break
		print(newid)
		resIDL['text'] = newid
		c.close()
		conn.close()
		resStatusC.current(0)
	else:
		showinfo(title="ERROR", message="Please enter a correct ID.")


def saveRmt():
	code=rmtRCEt.get()
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	c.execute('select * from RoomType where id=?', (code,))
	if c.fetchone():
		c.execute('update RoomType set  name=?, tier=?,quantity=?, description=? where id=?',(rmtNameEt.get(),rmtTierEt.get(),rmtQEt.get(),rmtDesTx.get(1.0,END),rmtRCEt.get() ))
	else:
		c.execute('insert into RoomType values(?,?,?,?,?)',(rmtRCEt.get(),rmtNameEt.get(),rmtTierEt.get(),rmtQEt.get(),rmtDesTx.get(1.0,END)))
	c.close()
	conn.commit()
	conn.close()

def newIvd():
	clearIvd()
	srcIdvTree.delete(*srcIdvTree.get_children())
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	newid = int(random.uniform(1000000000, 9999999999))
	n=1
	while n==1:
		c.execute('select name,surname,id from individual where id=?', (newid,))
		if c.fetchone():
			newid = int(random.uniform(1000000000, 9999999999))
			print("Hello")
			continue
		else:
			break
	print(newid)
	ivdID['text']=newid
	c.close()
	conn.close()

def newStf():
	clearStf()
	srcStfTree.delete(*srcStfTree.get_children())
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	newid = int(random.uniform(10000, 99999))
	n = 1
	while n == 1:
		c.execute('select * from Staff where id=?', (newid,))
		if c.fetchone():
			newid = int(random.uniform(10000, 99999))
			continue
		else:
			break
	stfIDL['text'] = newid
	c.close()
	conn.close()

def newDR():
	clearDR()
	srcDRTree.delete(*srcDRTree.get_children())
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	newid = int(random.uniform(10000000000, 99999999999))
	n = 1
	while n == 1:
		c.execute('select * from DailyRate where id=?', (newid,))
		if c.fetchone():
			newid = int(random.uniform(10000000000, 99999999999))
			continue
		else:
			break
	drIDL['text']=newid
	c.close()
	conn.close()

def newRsv():
	clearRsv()
	srcRsvTree.delete(*srcRsvTree.get_children())
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	newid = int(random.uniform(100000000, 999999999))
	n=1
	while n==1:
		c.execute('select * from Reservation where confirmID=?', (newid,))
		if c.fetchone():
			newid = int(random.uniform(100000000, 999999999))
			continue
		else:
			break
	resIDL['text']=newid
	c.close()
	conn.close()

def checkGst1():
	id=resGst1Et.get()
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	c.execute('select name,surname from individual where id=?', (id,))
	value=c.fetchone()
	if value:
		resGst1NL['text']=value[0]
		resGst1SL['text']=value[1]
	else:
		showinfo(title="ERROR", message="Please enter a correct ID.")
	c.close()
	conn.close()

def checkGst2():
	id=resGst2Et.get()
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	c.execute('select name,surname from individual where id=?', (id,))
	value=c.fetchone()
	if value:
		resGst2NL['text']=value[0]
		resGst2SL['text']=value[1]
	else:
		showinfo(title="ERROR", message="Please enter a correct ID.")
	c.close()
	conn.close()

def checkRate():
	cRmT=resRTEt.get()
	cRaT=resRatET.get()
	cci=resCIDEt.get()
	cco=resCODEt.get()
	ciy=int(cci[0:4])
	cim=int(cci[5:7])
	cid=int(cci[8:10])
	coy = int(cco[0:4])
	com = int(cco[5:7])
	cod = int(cco[8:10])
	conn = sql.connect('hotel.db')
	c = conn.cursor()
	d1 = datetime.datetime(ciy, cim, cid)
	d2 = datetime.datetime(coy, com, cod)
	d=(d2-d1).days
	print(cci,cco)
	c.execute('select rate from DailyRate where roomType=? and rateType=? and fromDate<? and toDate>?', (cRmT,cRaT,cci,cco))
	value=c.fetchone()
	if value:
		amount=value[0]*(int(resRNEt.get()))
		resAmountL['text']=amount
	else:
		showinfo(title="ERROR", message="Rate is not available.")
	c.close()
	conn.close()


#show individual frame
def showIdv():
	global searchNameEt,searchSurnameEt,searchIDEt,srcIdvTree,sresultLabel,ivdID,ivdNameEt,ivdSurEt,ivdEmailEt,ivdPhoneEt,ivdAddEt,ivdCityEt,ivdZipEt
	global ivdNoteTx,ivdCtryEt,titleC,ivdIndexTimes,ivdIndexTimes1
	tlIdv=Toplevel(root)
	ivdIndexTimes=0
	ivdIndexTimes1=0
	tlIdv.title("Individual Profile")
	Label(tlIdv, text="Search", fg='red').grid(row=0, sticky=W)
	Label(tlIdv, text="Name").grid(row=1, column=0, sticky=W)
	searchNameEt=Entry(tlIdv)
	searchNameEt.grid(row=1, column=1, sticky=W)
	Label(tlIdv, text="Surname").grid(row=1, column=2, sticky=W)
	searchSurnameEt=Entry(tlIdv)
	searchSurnameEt.grid(row=1, column=3, sticky=W)
	Label(tlIdv, text="ID").grid(row=1, column=4, sticky=W)
	searchIDEt=Entry(tlIdv)
	searchIDEt.grid(row=1, column=5, sticky=W)
	Button(tlIdv, text="Search",command=searchIvd).grid(row=1,column=6,sticky=W)
	srcIdvTree=Treeview(tlIdv, column=('Name','Surname','ID'), show="headings")
	srcIdvTree.heading('Name',text='Name')
	srcIdvTree.heading('Surname',text='Surname')
	srcIdvTree.heading('ID', text='ID')
	srcIdvTree.grid(row=2,rowspan=3,columnspan=6,sticky=W)
	srcIdvTree.bind("<Double-1>", selectIvd)
	sresultLabel=Label(tlIdv, text="Please input the guest's name/ surname or ID to search the profile.")
	sresultLabel.grid(row=5,columnspan=6,sticky=W)
	Label(tlIdv, text="Information", fg='red').grid(row=6, sticky=W)
	Label(tlIdv, text="ID").grid(row=7, column=0, sticky=W)
	Label(tlIdv, text="Title").grid(row=8, column=0, sticky=W)
	Label(tlIdv, text="Name").grid(row=9, column=0, sticky=W)
	Label(tlIdv, text="Surname").grid(row=10, column=0, sticky=W)
	Label(tlIdv, text="Email").grid(row=11, column=0, sticky=W)
	Label(tlIdv, text="Phone").grid(row=7, column=2, sticky=W)
	Label(tlIdv, text="Address").grid(row=8, column=2, sticky=W)
	Label(tlIdv, text="City").grid(row=9, column=2, sticky=W)
	Label(tlIdv, text="Zip Code").grid(row=10, column=2, sticky=W)
	Label(tlIdv, text="Country").grid(row=11, column=2, sticky=W)
	ivdID=Label(tlIdv, text="")
	ivdID.grid(row=7, column=1, sticky=W)
	titleC=Combobox(tlIdv)
	titleC["values"]=("","MR","MS")
	titleC["state"]="readonly"
	titleC.grid(row=8,column=1,sticky=W)
	ivdNameEt = Entry(tlIdv)
	ivdNameEt.grid(row=9, column=1, sticky=W)
	ivdSurEt = Entry(tlIdv)
	ivdSurEt.grid(row=10, column=1, sticky=W)
	ivdEmailEt = Entry(tlIdv)
	ivdEmailEt.grid(row=11, column=1, sticky=W)
	ivdPhoneEt = Entry(tlIdv)
	ivdPhoneEt.grid(row=7, column=3, sticky=W)
	ivdAddEt = Entry(tlIdv)
	ivdAddEt.grid(row=8, column=3, sticky=W)
	ivdCityEt = Entry(tlIdv)
	ivdCityEt.grid(row=9, column=3, sticky=W)
	ivdZipEt = Entry(tlIdv)
	ivdZipEt.grid(row=10, column=3, sticky=W)
	ivdCtryEt = Entry(tlIdv)
	ivdCtryEt.grid(row=11, column=3, sticky=W)
	Label(tlIdv, text="Notes").grid(row=7, column=4, sticky=W)
	ivdNoteTx=Text(tlIdv,height=6,width=47)
	ivdNoteTx.grid(row=8,column=4,rowspan=4,columnspan=3)
	Button(tlIdv, text="Save",command=saveIvd).grid(row=12,column=0,sticky=W)
	Button(tlIdv, text="Delete",command=deleteIvd).grid(row=12,column=1,sticky=W)
	Button(tlIdv, text="New",command=newIvd).grid(row=12,column=2,sticky=W)
	Button(tlIdv, text="Book Now",command=ivdBookNow).grid(row=12,column=3,sticky=W)
	Button(tlIdv, text="History",command=IvdShowHis).grid(row=12,column=4,sticky=W)
	Button(tlIdv, text="Clear",command=clearIvd).grid(row=12,column=5,sticky=W)

#reservation frame
def showRsv():
	global rsvIndexTimes,searchResNameEt,searchResSurnameEt,searchResIDEt,searchResCIEt,sreRsvsultLabel,resIDL,resMakeDateL
	global resCIDEt,resCODEt,resRNEt,resRTEt,resFromC,resGst1NL,resGst1Et,resGst2NL,resGst2Et,resGst1SL,resGst2SL,resRatET
	global resAmountL,resStaffL,resStatusC,resCTC,resNCET,resCNET,resEpEt,srcRsvTree,rsvNoteTx,rsvIndexTimes1
	tlRsv = Toplevel(root)
	rsvIndexTimes = 0
	rsvIndexTimes1 = 0
	tlRsv.title("Reservation")
	Label(tlRsv, text="Search", fg='red').grid(row=0, sticky=W)
	Label(tlRsv, text="Name").grid(row=1, column=0, sticky=W)
	searchResNameEt = Entry(tlRsv)
	searchResNameEt.grid(row=1, column=1, sticky=W)
	Label(tlRsv, text="Surname").grid(row=1, column=2, sticky=W)
	searchResSurnameEt = Entry(tlRsv)
	searchResSurnameEt.grid(row=1, column=3, sticky=W)
	Label(tlRsv, text="C/I Date").grid(row=1, column=4, sticky=W)
	searchResCIEt = Entry(tlRsv)
	searchResCIEt.grid(row=1, column=5, sticky=W)
	Button(tlRsv, text="Search",command=searchRsv).grid(row=2, column=5, sticky=W)
	Label(tlRsv, text="Confirmation No.").grid(row=2, column=0,sticky=W)
	searchResIDEt = Entry(tlRsv)
	searchResIDEt.grid(row=2, column=1, sticky=W)
	srcRsvTree = Treeview(tlRsv, column=('Confirmation No.', 'Name', 'Surname','C/I Date','C/O Date', 'Status'), show="headings")
	srcRsvTree.heading('Confirmation No.', text='Confirmation No.')
	srcRsvTree.heading('Surname', text='Surname')
	srcRsvTree.heading('Name', text='Name')
	srcRsvTree.heading('C/I Date', text='C/I Date')
	srcRsvTree.heading('C/O Date', text='C/O Date')
	srcRsvTree.heading('Status', text='Status')
	srcRsvTree.grid(row=3, rowspan=3, columnspan=6, sticky=W)
	srcRsvTree.bind("<Double-1>", selectRsv)
	sreRsvsultLabel = Label(tlRsv, text="Please input the guest's name & surname & C/I Date or ID to search the profile.")
	sreRsvsultLabel.grid(row=6, columnspan=6, sticky=W)
	Label(tlRsv, text="Information", fg='red').grid(row=7, sticky=W)
	Label(tlRsv, text="Confirmation No.").grid(row=8, column=0, sticky=W)
	Label(tlRsv, text="Book on").grid(row=9, column=0, sticky=W)
	Label(tlRsv, text="C/I Date").grid(row=10, column=0, sticky=W)
	Label(tlRsv, text="C/O Date").grid(row=11, column=0, sticky=W)
	Label(tlRsv, text="Room Num").grid(row=12, column=0, sticky=W)
	Label(tlRsv, text="From").grid(row=13, column=0, sticky=W)
	Label(tlRsv, text="Room Type").grid(row=14, column=0, sticky=W)
	Label(tlRsv, text="Guest ID").grid(row=8, column=2, sticky=W)
	Label(tlRsv, text="Name").grid(row=9, column=2, sticky=W)
	Label(tlRsv, text="Surname").grid(row=10, column=2, sticky=W)
	Label(tlRsv, text="Shared Guest ID").grid(row=11, column=2, sticky=W)
	Label(tlRsv, text="Name").grid(row=12, column=2, sticky=W)
	Label(tlRsv, text="Surname").grid(row=13, column=2, sticky=W)
	Label(tlRsv, text="Rate Type").grid(row=14, column=2, sticky=W)
	Label(tlRsv, text="Amount").grid(row=8, column=4, sticky=W)
	Label(tlRsv, text="Made by").grid(row=9, column=4, sticky=W)
	Label(tlRsv, text="status").grid(row=10, column=4, sticky=W)
	Label(tlRsv, text="Card Type").grid(row=11, column=4, sticky=W)
	Label(tlRsv, text="Name On Card").grid(row=12, column=4, sticky=W)
	Label(tlRsv, text="Card Num").grid(row=13, column=4, sticky=W)
	Label(tlRsv, text="Expires").grid(row=14, column=4, sticky=W)
	Label(tlRsv, text="Notes").grid(row=15,sticky=W)
	rsvNoteTx = Text(tlRsv,height=5,width=150)
	rsvNoteTx.grid(row=16,columnspan=6,sticky=W)
	resIDL=Label(tlRsv, text="")
	resIDL.grid(row=8, column=1, sticky=W)
	resMakeDateL = Label(tlRsv, text="")
	resMakeDateL.grid(row=9, column=1, sticky=W)
	resCIDEt=Entry(tlRsv)
	resCIDEt.grid(row=10,column=1,sticky=W)
	resCODEt = Entry(tlRsv)
	resCODEt.grid(row=11, column=1, sticky=W)
	resRNEt = Entry(tlRsv)
	resRNEt.grid(row=12, column=1, sticky=W)
	resFromC = Combobox(tlRsv)
	resFromC["values"] = ( "","Phone", "FAX", "Email", "Web", "OTA")
	resFromC.grid(row=13, column=1, sticky=W)
	resRTEt = Entry(tlRsv)
	resRTEt.grid(row=14, column=1, sticky=W)
	resGst1Et=Entry(tlRsv)
	resGst1Et.grid(row=8, column=3, sticky=W)
	Button(tlRsv, text="Ck",command=checkGst1).grid(row=8, column=2, sticky=E)
	resGst1NL=Label(tlRsv, text="")
	resGst1NL.grid(row=9, column=3, sticky=W)
	resGst1SL=Label(tlRsv)
	resGst1SL.grid(row=10, column=3, sticky=W)
	resGst2Et=Entry(tlRsv)
	resGst2Et.grid(row=11, column=3, sticky=W)
	Button(tlRsv, text="Ck",command=checkGst2).grid(row=11, column=2, sticky=E)
	resGst2NL=Label(tlRsv, text="")
	resGst2NL.grid(row=12, column=3, sticky=W)
	resGst2SL=Label(tlRsv)
	resGst2SL.grid(row=13, column=3, sticky=W)
	resRatET=Entry(tlRsv)
	resRatET.grid(row=14, column=3, sticky=W)
	resAmountL=Label(tlRsv)
	resAmountL.grid(row=8,column=5,sticky=W)
	Button(tlRsv, text="Ck",command=checkRate).grid(row=8, column=4, sticky=E)
	resStaffL=Label(tlRsv)
	resStaffL.grid(row=9,column=5,sticky=W)
	resStatusC = Combobox(tlRsv)
	resStatusC["values"] = ("Pending","Confirm", "Cancel", "C/I", "C/O", "No show")
	resStatusC.grid(row=10, column=5, sticky=W)
	resCTC = Combobox(tlRsv)
	resCTC["values"] = ("","UP", "VISA", "Master", "AE", "JCB","Transfer","Cash")
	resCTC.grid(row=11, column=5, sticky=W)
	resNCET=Entry(tlRsv)
	resNCET.grid(row=12, column=5, sticky=W)
	resCNET=Entry(tlRsv)
	resCNET.grid(row=13, column=5, sticky=W)
	resEpEt=Entry(tlRsv)
	resEpEt.grid(row=14, column=5, sticky=W)
	Button(tlRsv, text="Save",command=saveRsv).grid(row=17, column=0, sticky=W)
	Button(tlRsv, text="New",command=newRsv).grid(row=17, column=0, sticky=E)
	Button(tlRsv, text="Clear", command=clearRsv).grid(row=17, column=1, sticky=W)

#Rate Type Query Frame
def showRatQ():
	global searchCodeEt,srcRatTree,sRatresultLabel,ratRCEt,ratNameEt,ratPPC,ratCPTx,ratDesTx,ratQIndexTimes,ratQIndexTimes1
	tlRatQ=Toplevel(root)
	ratQIndexTimes=0
	ratQIndexTimes1=0
	tlRatQ.title("Rate Type Query")
	Label(tlRatQ, text="Search", fg='red').grid(row=0, sticky=W)
	Label(tlRatQ, text="Rate Code").grid(row=1, column=0, sticky=W)
	searchCodeEt=Entry(tlRatQ)
	searchCodeEt.grid(row=1, column=1, sticky=W)
	Button(tlRatQ, text="Search",command=searchRat).grid(row=1, column=2, sticky=W)
	Button(tlRatQ, text="Show All",command=showAllRat).grid(row=1, column=3, sticky=W)
	srcRatTree = Treeview(tlRatQ, column=('Code', 'Name'),show="headings")
	srcRatTree.heading('Code', text='Code')
	srcRatTree.heading('Name', text='Name')
	srcRatTree.grid(row=2, rowspan=2, columnspan=4, sticky=W)
	srcRatTree.bind("<Double-1>", selectRat)
	sRatresultLabel = Label(tlRatQ, text="Please input the rate code or click the SHOW ALL to check the rate type.")
	sRatresultLabel.grid(row=4, columnspan=4, sticky=W)
	Label(tlRatQ, text="Information", fg='red').grid(row=5, sticky=W)
	Label(tlRatQ, text="Rate Code").grid(row=6, column=0, sticky=W)
	Label(tlRatQ, text="Name").grid(row=7, column=0, sticky=W)
	Label(tlRatQ, text="Prepaid").grid(row=8, column=0, sticky=W)
	Label(tlRatQ, text="cancellantion Policy").grid(row=9, column=0, columnspan=2,sticky=W)
	Label(tlRatQ, text="Description").grid(row=6, column=2, sticky=W)
	ratRCEt=Entry(tlRatQ)
	ratRCEt.grid(row=6, column=1, sticky=W)
	ratNameEt=Entry(tlRatQ)
	ratNameEt.grid(row=7, column=1, sticky=W)
	ratPPC=Combobox(tlRatQ)
	ratPPC['values']=("","Y","N")
	ratPPC.grid(row=8, column=1, sticky=W)
	ratCPTx=Text(tlRatQ,height=5,width=40)
	ratCPTx.grid(row=10, column=0, columnspan=2,sticky=W)
	ratDesTx=Text(tlRatQ,height=20,width=20)
	ratDesTx.grid(row=7, column=2, columnspan=2,rowspan=4,sticky=W)
	if manager=="'True'":
		Button(tlRatQ, text="Save",command=saveRat).grid(row=11, column=0, sticky=W)
		Button(tlRatQ, text="New",command=clearRat).grid(row=11, column=1, sticky=W)

def showRmtQ():
	global rmtQIndexTimes,rmtQIndexTimes1,rmtQIndexTimes1,searchRmCodeEt,rmtRCEt,rmtNameEt,rmtTierEt,rmtQEt,rmtDesTx,srcRmtTree,sRmtresultLabel
	tlRmtQ=Toplevel(root)
	rmtQIndexTimes=0
	rmtQIndexTimes1=0
	tlRmtQ.title("Room Type Query")
	Label(tlRmtQ, text="Search", fg='red').grid(row=0, sticky=W)
	Label(tlRmtQ, text="Room Code").grid(row=1, column=0, sticky=W)
	searchRmCodeEt=Entry(tlRmtQ)
	searchRmCodeEt.grid(row=1, column=1, sticky=W)
	Button(tlRmtQ, text="Search",command=searchRmt).grid(row=1, column=2, sticky=W)
	Button(tlRmtQ, text="Show All",command=showAllRmt).grid(row=1, column=3, sticky=W)
	srcRmtTree = Treeview(tlRmtQ, column=('Code', 'Name','Tier'),show="headings")
	srcRmtTree.heading('Code', text='Code')
	srcRmtTree.heading('Name', text='Name')
	srcRmtTree.heading('Tier', text='Tier')
	srcRmtTree.grid(row=2, rowspan=2, columnspan=4, sticky=W)
	srcRmtTree.bind("<Double-1>", selectRmt)
	sRmtresultLabel = Label(tlRmtQ, text="Please input the room code or click the SHOW ALL to check the room type.")
	sRmtresultLabel.grid(row=4, columnspan=4, sticky=W)
	Label(tlRmtQ, text="Information", fg='red').grid(row=5, sticky=W)
	Label(tlRmtQ, text="Room Code").grid(row=6, column=0, sticky=W)
	Label(tlRmtQ, text="Name").grid(row=7, column=0, sticky=W)
	Label(tlRmtQ, text="Tier").grid(row=8, column=0, sticky=W)
	Label(tlRmtQ, text="Quantity").grid(row=9, column=0, columnspan=2,sticky=W)
	Label(tlRmtQ, text="Description").grid(row=6, column=2, sticky=W)
	rmtRCEt=Entry(tlRmtQ)
	rmtRCEt.grid(row=6, column=1, sticky=W)
	rmtNameEt=Entry(tlRmtQ)
	rmtNameEt.grid(row=7, column=1, sticky=W)
	rmtTierEt=Entry(tlRmtQ)
	rmtTierEt.grid(row=8, column=1, sticky=W)
	rmtQEt=Entry(tlRmtQ)
	rmtQEt.grid(row=9, column=1, sticky=W)
	rmtDesTx=Text(tlRmtQ,height=15,width=30)
	rmtDesTx.grid(row=7, column=2, columnspan=2,rowspan=3,sticky=W)
	if manager=="'True'":
		Button(tlRmtQ, text="Save",command=saveRmt).grid(row=11, column=0, sticky=W)
		Button(tlRmtQ, text="New",command=clearRmt).grid(row=11, column=1, sticky=W)

def showSatffInfo():
	global tlStf,stfIndexTimes,stfIndexTimes1,stfResultL,searchStfSEt,searchStfNEt,searchStfIDEt,stfNoteTx,srcStfTree,stfIDL,stfNameEt,stfSNameEt,stfPwEt,stfMngC
	tlStf=Toplevel(root)
	stfIndexTimes = 0
	stfIndexTimes1 = 0
	tlStf.title("Staff Information")
	Label(tlStf, text="Search", fg='red').grid(row=0, sticky=W)
	Label(tlStf, text="Staff ID").grid(row=1, column=0, sticky=W)
	searchStfIDEt=Entry(tlStf)
	searchStfIDEt.grid(row=1, column=1, sticky=W)
	Label(tlStf, text="Name").grid(row=1, column=2,sticky=W)
	searchStfNEt = Entry(tlStf)
	searchStfNEt.grid(row=1, column=3, sticky=W)
	Label(tlStf, text="Surname").grid(row=1, column=4, sticky=W)
	searchStfSEt=Entry(tlStf)
	searchStfSEt.grid(row=1, column=5, sticky=W)
	Button(tlStf, text="Search", command=searchStf).grid(row=1, column=6, sticky=W)
	srcStfTree = Treeview(tlStf, column=('ID', 'Name', 'Surname'), show="headings")
	srcStfTree.heading('ID', text='ID')
	srcStfTree.heading('Name', text='Name')
	srcStfTree.heading('Surname', text='Surname')
	srcStfTree.grid(row=2, rowspan=2, columnspan=6, sticky=W)
	srcStfTree.bind("<Double-1>", selectStf)
	stfResultL = Label(tlStf, text="Please input the Staff ID or Name & Surname to check the Staff Info.")
	stfResultL.grid(row=4, columnspan=6, sticky=W)
	Label(tlStf, text="Information", fg='red').grid(row=5, sticky=W)
	Label(tlStf, text="ID").grid(row=6, column=0, sticky=W)
	Label(tlStf, text="Name").grid(row=7, column=0, sticky=W)
	Label(tlStf, text="Surname").grid(row=8, column=0, sticky=W)
	Label(tlStf, text="Password").grid(row=9, column=0, sticky=W)
	Label(tlStf, text="manager").grid(row=10, column=0, sticky=W)
	Label(tlStf,text="Notes").grid(row=6, column=2, sticky=W)
	stfIDL=Label(tlStf, text="")
	stfIDL.grid(row=6, column=1, sticky=W)
	stfNameEt=Entry(tlStf)
	stfNameEt.grid(row=7, column=1, sticky=W)
	stfSNameEt = Entry(tlStf)
	stfSNameEt.grid(row=8, column=1, sticky=W)
	stfPwEt=Entry(tlStf)
	stfPwEt.grid(row=9, column=1, sticky=W)
	stfMngC=Combobox(tlStf)
	stfMngC["values"]=("","True","False")
	stfMngC.grid(row=10, column=1, sticky=W)
	stfNoteTx=Text(tlStf,height=20,width=50)
	stfNoteTx.grid(row=7, column=2,rowspan=3,columnspan=5,sticky=W)
	Button(tlStf, text="Save",command=saveStf).grid(row=11, column=0, sticky=W)
	Button(tlStf, text="New",command=newStf).grid(row=11, column=1, sticky=W)
	Button(tlStf, text="Clear",command=clearStf).grid(row=11,column=2,sticky=W)
	Button(tlStf, text="Delete",command=deleteStf).grid(row=11,column=3,sticky=W)

def showDR():
	global drIndexTimes,drIndexTimes1,searchDRfromDateEt,searchDRToDateEt,searchDRRTEt,searchDRRatEt,drResultL,srcDRTree,drIDL,drRTEt,drFDEt,drTDEt,drRatTEt,drNoteTx,drRatEt
	tlDR=Toplevel(root)
	drIndexTimes = 0
	drIndexTimes1 = 0
	tlDR.title("Daily Rate")
	Label(tlDR, text="Search", fg='red').grid(row=0, sticky=W)
	Label(tlDR, text="From").grid(row=1, column=0, sticky=W)
	searchDRfromDateEt=Entry(tlDR)
	searchDRfromDateEt.grid(row=1, column=1, sticky=W)
	Label(tlDR, text="To").grid(row=1, column=2, sticky=W)
	searchDRToDateEt = Entry(tlDR)
	searchDRToDateEt.grid(row=1, column=3, sticky=W)
	Label(tlDR, text="Room Type").grid(row=1, column=4, sticky=W)
	searchDRRTEt = Entry(tlDR)
	searchDRRTEt.grid(row=1, column=5, sticky=W)
	Label(tlDR, text="Rate Type").grid(row=1, column=6, sticky=W)
	searchDRRatEt = Entry(tlDR)
	searchDRRatEt.grid(row=1, column=7, sticky=W)
	Button(tlDR, text="Search", command=searchDS).grid(row=1, column=8, sticky=W)
	srcDRTree = Treeview(tlDR, column=('From','To', 'Room Type', 'Rate Type','Rate'), show="headings")
	srcDRTree.heading('From', text='From')
	srcDRTree.heading('To', text='To')
	srcDRTree.heading('Room Type', text='Room Type')
	srcDRTree.heading('Rate Type', text='Rate Type')
	srcDRTree.heading('Rate', text='Rate')
	srcDRTree.grid(row=2, rowspan=2, columnspan=8, sticky=W)
	srcDRTree.bind("<Double-1>", selectDR)
	drResultL = Label(tlDR, text="Please input the DATE / ROOM TYPE / RATE TYPE to check the avalability.")
	drResultL.grid(row=4, columnspan=6, sticky=W)
	Label(tlDR, text="Information", fg='red').grid(row=5, sticky=W)
	Label(tlDR, text="Room Type").grid(row=6, column=0, sticky=W)
	Label(tlDR, text="From").grid(row=7, column=0, sticky=W)
	Label(tlDR, text="To").grid(row=8, column=0, sticky=W)
	Label(tlDR, text="Rate Type").grid(row=9, column=0, sticky=W)
	Label(tlDR, text="Rate").grid(row=10, column=0, sticky=W)
	Label(tlDR, text="Notes").grid(row=6, column=2, sticky=W)
	drIDL=Label(tlDR,text="")
	drRTEt=Entry(tlDR)
	drRTEt.grid(row=6, column=1, sticky=W)
	drFDEt = Entry(tlDR)
	drFDEt.grid(row=7, column=1, sticky=W)
	drTDEt = Entry(tlDR)
	drTDEt.grid(row=8, column=1, sticky=W)
	drRatTEt = Entry(tlDR)
	drRatTEt.grid(row=9, column=1, sticky=W)
	drRatEt = Entry(tlDR)
	drRatEt.grid(row=10, column=1, sticky=W)
	drNoteTx=Text(tlDR,height=10,width=80)
	drNoteTx.grid(row=7,column=2,rowspan=4,columnspan=4,sticky=W)
	if manager=="'True'":
		Button(tlDR, text="Save",comman=saveDR).grid(row=11, column=0, sticky=W)
		Button(tlDR, text="New",command=newDR).grid(row=11, column=1, sticky=W)
		Button(tlDR, text="Clear",command=clearDR).grid(row=11, column=2, sticky=W)


def showDRS():
	global searchfromDateEt,drsIndexTimes,drsIndexTimes1,searchfromDateEt,searchToDateEt,searchRTEt,srcDRSTree,drsResultL,drsDateEt,drsRtEt,drsHsEt,drsMtEt,desNoteTx,drsIDL
	tlDRS=Toplevel(root)
	drsIndexTimes = 0
	drsIndexTimes1 = 0
	tlDRS.title("Daily Room Availability")
	Label(tlDRS, text="Search", fg='red').grid(row=0, sticky=W)
	Label(tlDRS, text="From").grid(row=1, column=0, sticky=W)
	searchfromDateEt=Entry(tlDRS)
	searchfromDateEt.grid(row=1, column=1, sticky=W)
	Label(tlDRS, text="To").grid(row=1, column=2, sticky=W)
	searchToDateEt = Entry(tlDRS)
	searchToDateEt.grid(row=1, column=3, sticky=W)
	Label(tlDRS, text="Room Type").grid(row=1, column=4, sticky=W)
	searchRTEt = Entry(tlDRS)
	searchRTEt.grid(row=1, column=5, sticky=W)
	Button(tlDRS, text="Search", command=searchDRS).grid(row=1, column=6, sticky=W)
	srcDRSTree = Treeview(tlDRS, column=('Date', 'Room Type', 'Availability'), show="headings")
	srcDRSTree.heading('Date', text='Date')
	srcDRSTree.heading('Room Type', text='Room Type')
	srcDRSTree.heading('Availability', text='Availability')
	srcDRSTree.grid(row=2, rowspan=2, columnspan=6, sticky=W)
	srcDRSTree.bind("<Double-1>", selectDRS)
	drsResultL = Label(tlDRS, text="Please input the DATE and ROOM TYPE to check the avalability.")
	drsResultL.grid(row=4, columnspan=6, sticky=W)
	Label(tlDRS, text="Information", fg='red').grid(row=5, sticky=W)
	Label(tlDRS, text="Date").grid(row=6, column=0,sticky=W)
	Label(tlDRS, text="Room Type").grid(row=7, column=0,sticky=W)
	Label(tlDRS, text="Had Sold").grid(row=8, column=0,sticky=W)
	Label(tlDRS, text="Maintain").grid(row=9, column=0,sticky=W)
	Label(tlDRS, text="Notes").grid(row=6, column=2,sticky=W)
	drsIDL=Label(tlDRS,text="")
	drsDateEt=Entry(tlDRS)
	drsDateEt.grid(row=6,column=1,sticky=W)
	drsRtEt = Entry(tlDRS)
	drsRtEt.grid(row=7, column=1, sticky=W)
	drsHsEt = Label(tlDRS,text="")
	drsHsEt.grid(row=8, column=1, sticky=W)
	drsMtEt=Entry(tlDRS)
	drsMtEt.grid(row=9, column=1, sticky=W)
	desNoteTx=Text(tlDRS,height=10,width=60)
	desNoteTx.grid(row=7,column=2,rowspan=3,columnspan=4,sticky=W)
	if manager=="'True'":
		Button(tlDRS, text="Save", command=saveDRS).grid(row=10, column=0, sticky=W)
		Button(tlDRS, text="Initial", command=initDRS).grid(row=10, column=1, sticky=W)
		Button(tlDRS, text="Clear", command=clearDRS).grid(row=10, column=2, sticky=W)


#log in frame
def ShowLogIn():
	global unEt,pwEt,c,frm
	frm = Frame(root)
	frm.pack()
	Label(frm, text="HRS Login").pack()
	Label(frm, text="Staff ID:").pack()
	unEt = Entry(frm)
	unEt.pack()
	Label(frm, text="password").pack()
	pwEt = Entry(frm)
	pwEt['show'] = '*'
	pwEt.pack()
	Button(frm, text="Log In", command=reg).pack()
	c = Label(frm, text="")
	c.pack()

root=Tk()
root.wm_title("Hotel Reservation System")
ShowLogIn()


root.mainloop()