Coding Implementation and App Documentation

Libraries/Modules used: 1. customtkinter (highly-customized

tkinter library)
2. requests
3. CtkTable (for customized tkinter Table
widgets)
4. threading
5. mysql-connector-python
6. speedtest-cli (for speedtest)
7. time
8. datetime
File Structure:

Short Description of each file:

1. [Link] : It is the main file responsible for the app’s main functionality
as well as UI. It has over 989 lines of code.

2. [Link] : It contains several functions related to the dashboard

of the app. It has over 48 lines of code.

3. [Link] : It contains functions related to Employee tab. It has

over 21 lines of code.

4. [Link] : It returns financial reports. It has over 21 lines of

code.

5. [Link] : It is used to perform functions related to Hotel

Infrastructure tab. It has over 22 lines of code.

6. [Link] : It contains several useful functions related to

Dashboard, as well as functionality of adding Guests and Rooms. It has
over 71 lines of code.

App Design
Code along with Filenames:
1. [Link]
import requests
import [Link]
import speedtest
import time

# used for checking existence of given username input

def availability(username):

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"SELECT COUNT(*) FROM userinfo WHERE
usname='{username}';"
[Link](statement)
result=[Link]()[0]
[Link]()
return result

# fetches userdata to be displayed on dashboard

def getuserdata(username):

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"SELECT usname,emailid,hotelname FROM userinfo where
usname='{username}';"
[Link](statement)
result=[Link]()
[Link]()
return result

# fetches location from IP address

def getlocation(ip):
response=[Link](f"[Link]
data=[Link]()
return data

# fetches IP address
def getip():
publicip=[Link]("[Link]
return publicip

# returns internet speed using Ookla Speedtest

def getspeed():
st=[Link]()
 starttime=[Link]()
# get best server on based on ping
st.get_best_server()
download=round([Link]()/1_000_000)
upload=round([Link]()/1_000_000)
ping=round([Link])
endtime=[Link]()
totaltime=round(endtime-starttime,2)
result=f"Time taken:{totaltime}seconds\nDownload:{download}Mbps\
nUpload:{upload}Mbps\nPing:{ping}ms"
return result

2. [Link]
import [Link]

def getemployee(username):

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"SELECT first_name, last_name, position, department,
hire_date, salary FROM hotel_employees WHERE username='{username}';"
[Link](statement)
result=[Link]()
columns=[("first_name", "last_name", "position", "department",
"hire_date", "salary")]
finalresult=columns+result
[Link]()
return finalresult

def getempcount(username):

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"SELECT COUNT(*) FROM hotel_employees WHERE
username='{username}';"
[Link](statement)
result=[Link]()[0]
[Link]()
return result
3. [Link]
import [Link]

def getfinance(username):

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"SELECT yearly, total_revenue, total_expenses,
net_income, adr, occupancy_rate FROM financial WHERE
username='{username}';"
[Link](statement)
result=[Link]()
columns=[("yearly", "total_revenue", "total_expenses",
"net_income", "adr", "occupancy_rate")]
finalresult=columns+result
[Link]()
return finalresult

def financecount(username):

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"SELECT COUNT(*) FROM financial WHERE
username='{username}';"
[Link](statement)
result=[Link]()[0]
[Link]()
return result

4. [Link]
import [Link]

def gethotelinfra(username):

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"SELECT component_name,floor_number,quantity,purpose
FROM hotel_infrastructure WHERE username='{username}';"
[Link](statement)
result=[Link]()
columns=[("Component","Quantity","Floor","Purpose")]
 finalresult=columns+result
[Link]()
return finalresult

def counthotelinfra(username):

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"SELECT COUNT(*) FROM hotel_infrastructure WHERE
username='{username}';"
[Link](statement)
result=[Link]()[0]
[Link]()
return result

5. [Link]
import datetime
import [Link]

def gettime():
current_time=[Link]()
return current_time

def timearrangedata():
timenow=gettime()
year=str([Link])
day=str([Link])
monthnum=[Link]

dict1={1:"January",2:"February",3:"March",4:"Arpil",5:"May",6:"June",7:
"July",8:"August",9:"August",10:"October",11:"November",12:"December"}
month=dict1[monthnum]
return "Today's date:\n"+day + " " + month+", " + year

def addguests(name,addr,ph,stay,arr,depar,accomp,rooms,username):

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"INSERT INTO guestsinfo(Guestname, address ,phno,
stayduration,arrival_date,departure_date,accompanies,rooms_occupied,use
rname) VALUES('{name}','{addr}','{ph}',{stay},'{arr}','{depar}',
{accomp},{rooms},'{username}');"
[Link](statement)
 [Link]()
[Link]()

def retrievedata(username):

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"SELECT
Guestname,address,phno,stayduration,arrival_date,departure_date,accompa
nies,rooms_occupied FROM guestsinfo WHERE username='{username}';"
[Link](statement)
results=[Link]()
columns=[("Guest Name","address","Phone
No:","stayduration","arrival date","departure date","No of persons
accompanying","rooms occupied")]
results=columns+results
[Link]()
return results

def countrecord(username):

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"SELECT COUNT(*) FROM guestsinfo WHERE
username='{username}';"
[Link](statement)
result=[Link]()[0]
[Link]()
return result

def getrooms(username):

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"SELECT room,occupied FROM roomallocate WHERE
username='{username}';"
[Link](statement)
results=[Link]()
finalresult=[]
for i in results:
if i[1]==True:
l1=[i[0],"Occupied"]
else:
l1=[i[0],"Empty"]
[Link](l1)
 columns=[("Room No.","Status")]
finalresult=columns+finalresult
[Link]()
return finalresult

def getcount(username):

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"SELECT COUNT(*) FROM roomallocate WHERE
username='{username}';"
[Link](statement)
result=[Link]()[0]
[Link]()
return result

6. [Link]
import customtkinter as ctk
import requests
root=[Link]()
[Link]("600x500")
[Link]("Hosthub Hotel Management Software")
[Link](False,False)
# themes and colours
ctk.set_default_color_theme("dark-blue")
ctk.set_appearance_mode("dark")
# [Link]("[Link]")
# mysql connectivity
import [Link]

# separate custom imports

from hotel import *
from usefulfunctions import *
from genreports import *
from employee import *
from dashboard import *
from CTkTable import *
import threading

# used for globally accessible data

realusername=""

def maincontentlogin(username):
root2=[Link]()
[Link]("1400x600")
[Link]("Hosthub Hotel Management Software")
 # [Link]("[Link]")
[Link](True,True)
greetlabel=[Link](root2,text=f"Welcome back,
{username}!",font=('Microsoft YaHei UI',30,"bold"))
# notebook sidebar (tabview)
tabview1=[Link](root2,corner_radius=15,height=330)
[Link]("Dashboard")
[Link]("Guests Record")
[Link]("Room Allocation")
[Link]("Hotel Infrastructure")
[Link]("Employees")
[Link]("Financial")

# dashboard work
date=timearrangedata()

label1=[Link]([Link]("Dashboard"),text=date,font=("Montserr
at",15,"normal"),width=50)
userdata=getuserdata(username)
usname,emid,htname=userdata[0],userdata[1],userdata[2]
global label2
label2=[Link]([Link]("Dashboard"),text=f"Username:
{usname}\nEmail Id: {emid}\nHotel name: {htname}",font=('Microsoft
YaHei',15))
# slabs

# get location
ip=getip()
location=getlocation(ip)
label3=[Link]([Link]("Dashboard"),text=f"Location:
{location['city']},{location['region']},
{location['country']}",font=('Microsoft YaHei UI',15))
[Link](column=1,row=2)

# weather

label4=[Link]([Link]("Dashboard"),text="Weather:",font=('Mi
crosoft YaHei UI',15))
[Link](column=3,row=2)
coordinates=location['loc'].split(",")
lat=coordinates[0]
lon=coordinates[1]
# use open-meteo
respon=[Link](f"[Link]
latitude={lat}&longitude={lon}&current_weather=true")
data=[Link]()
dict1=data['current_weather']
temp1=f"Temperature:{dict1['temperature']}degrees celsius"
 wind1=f"Windspeed:{dict1['windspeed']}km/h"
finaldata=f"{temp1}\n{wind1}"

label10=[Link]([Link]("Dashboard"),text=finaldata,font=('Mi
crosoft YaHei Light',15))
[Link](column=3,row=3)

def editprofile():

def changehotel():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"UPDATE userinfo SET
hotelname='{[Link]()}' WHERE usname='{realusername}';"
[Link](statement)
[Link]()
[Link]()
successmsg=[Link](dashwindow,text="Modified
Successfully!",text_color="green")
[Link](column=3,row=2)
[Link](0,[Link])
def clearsuccessmsg():
[Link]()
[Link](3000,clearsuccessmsg)

def changepwd():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"UPDATE userinfo SET pwd='{[Link]()}' WHERE
usname='{realusername}';"
[Link](statement)
[Link]()
[Link]()
successmsg=[Link](dashwindow,text="Modified
Successfully!",text_color="green")
[Link](column=3,row=3)
[Link](0,[Link])
def clearsuccessmsg():
[Link]()
[Link](3000,clearsuccessmsg)

def changemail():
conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"UPDATE userinfo SET emailid='{[Link]()}'
WHERE usname='{realusername}';"
[Link](statement)
[Link]()
[Link]()
successmsg=[Link](dashwindow,text="Modified
Successfully!",text_color="green")
[Link](column=3,row=4)
[Link](0,[Link])
def clearsuccessmsg():
[Link]()
[Link](3000,clearsuccessmsg)

global dashwindow,newhname,newpwd,newmail
dashwindow=[Link]()
[Link]("Edit Profile")
[Link]("600x300")
[Link](False,False)
usernamelabel=[Link](dashwindow,text=f"Username:
{realusername}",font=('Raleway',15,"bold"))
newhname=[Link](dashwindow,placeholder_text="New Hotel
Name : ")
newpwd=[Link](dashwindow,placeholder_text="New
password:")
newmail=[Link](dashwindow,placeholder_text="New Email
ID:")

changehnamebtn=[Link](dashwindow,text="Change Hotel
name",command=changehotel)
changepwdbtn=[Link](dashwindow,text="Change
Password",command=changepwd)
changeemailbtn=[Link](dashwindow,text="Change Email
ID",command=changemail)

[Link](column=2,row=4)
[Link](column=2,row=2)
[Link](column=2,row=3)
[Link](column=2,row=1)
[Link](column=1,row=4)
[Link](column=1,row=2)
[Link](column=1,row=3)
[Link]((1,2,3,4),weight=1)
[Link]((1,2,3,4,5,6),weight=1)
[Link]()
 btn11=[Link]([Link]("Dashboard"),text="Edit
Profile",command=editprofile,fg_color="#3d84db",hover_color="#166fdb",t
ext_color="white")
[Link](column=3,row=4)

def getrefreshdashboard():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"SELECT usname,emailid,hotelname FROM userinfo where
usname='{realusername}';"
[Link](statement)
result=[Link]()
[Link]()
return result

def refreshdashboard():
global label2
[Link]()
data=getrefreshdashboard()
username,hotelname,emailid=data[0],data[2],data[1]
label2=[Link]([Link]("Dashboard"),text=f"Username:
{username}\nEmail Id: {emailid}\nHotel name:
{hotelname}",font=('Microsoft YaHei',15))
[Link](column=3,row=0,sticky=('ew'))

btn12=[Link]([Link]("Dashboard"),text="Refresh",fg_color="
#f2fafa",hover_color="#e1fafa",text_color="black",command=refreshdashbo
ard)
[Link](column=3,row=5)
# get internet speed
def activateinternet():
global label6,label5
label5=[Link]([Link]("Dashboard"),text="Please wait
for about 60 seconds:")
[Link](column=1,row=4)
def threadfunc():
global label6
info=getspeed()

label6=[Link]([Link]("Dashboard"),text=info,font=('Microsof
t YaHei UI',20))
[Link](column=1,row=5)
global clearbtn2
clearbtn2=[Link]([Link]("Dashboard"),text="Clear",command=
clear2)
[Link](column=1,row=6)
[Link](target=threadfunc).start()
def clear2():
[Link]()
[Link]()
[Link]()

speedbtn1=[Link]([Link]("Dashboard"),text="Check
Internet
Speed",fg_color="#fc4454",hover_color="#fc2638",command=activateinterne
t)
[Link](column=1,row=3)

[Link]("Dashboard").columnconfigure((1,2,3),weight=1)
[Link]("Dashboard").rowconfigure((1,2,3,4,5,6,7),weight=1)

[Link](column=1,row=1,sticky=('ew'))
[Link](column=3,row=0,sticky=('ew'))

# current guests work

scrollableframe2=[Link]([Link]("Guests
Record"),width=1350)
[Link](row=1)
value=retrievedata(username)

def addguestwindow():
win2=[Link]()
[Link]("Add Guests!")
[Link]("700x700")
global
gstnamedata,addrdata,phdata,durdata,Arrdata,deparrdata,accompdata,roomo
ccup

gstnamedata=[Link](win2,placeholder_text="Guest
name:",width=200)

addrdata=[Link](win2,placeholder_text="Address:",width=200)
phdata=[Link](win2,placeholder_text="Phone
No:",width=200)
durdata=[Link](win2,placeholder_text="Duration of
Stay:",width=200)
Arrdata=[Link](win2,placeholder_text="Arrival Date (YYYY-
MM-DD):",width=200)
 deparrdata=[Link](win2,placeholder_text="Departure Date
(YYYY-MM-DD):",width=200)
accompdata=[Link](win2,placeholder_text="No of persons
accompanying:",width=200)
roomoccup=[Link](win2,placeholder_text="No of rooms
occupied:",width=200)

def addactivate():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()

successmsg=[Link](win2,text="Successful!",text_color="green")
[Link](column=1,row=10)
global
gstnamedata,addrdata,phdata,durdata,Arrdata,deparrdata,accompdata,roomo
ccup
statement=f"INSERT INTO guestsinfo(Guestname,
address ,phno,
stayduration,arrival_date,departure_date,accompanies,rooms_occupied,use
rname) VALUES
('{[Link]()}','{[Link]()}','{[Link]()}',
{[Link]()},'{[Link]()}','{[Link]()}',
{[Link]()},{[Link]()},'{realusername}');"
[Link](statement)
[Link]()
[Link]()
[Link](0,[Link])
[Link](0,[Link])
[Link](0,[Link])
[Link](0,[Link])
[Link](0,[Link])
[Link](0,[Link])
[Link](0,[Link])
[Link](0,[Link])
def removemsg():
successmsg.grid_forget()
[Link](3000,removemsg)

addbtn=[Link](win2,text="Add Guest",command=addactivate)
greet1=[Link](win2,text="Fill up the details!")

[Link](row=0,column=1)
[Link](row=9,column=1)
[Link](row=1,column=1)
[Link](row=2,column=1)
[Link](row=3,column=1)
 [Link](row=4,column=1)
[Link](row=5,column=1)
[Link](row=6,column=1)
[Link](row=7,column=1)
[Link](row=8,column=1)
[Link]((0,1,2),weight=1)
[Link]((0,1,2,3,4,5,6,7,8,9,10,11),weight=1)
[Link]()
global table1
table1=CTkTable(scrollableframe2,column=8,row=countrecord(username)
+1,values=value,hover_color="grey")
[Link]("Guests Record").rowconfigure((0,1,2),weight=1)
[Link](row=1)
# refresh guests

def refreshguestdata():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"SELECT Guestname, address ,phno,
stayduration,arrival_date,departure_date,accompanies,rooms_occupied
FROM guestsinfo WHERE username='{realusername}';"
[Link](statement)
results=[Link]()
columns=[("Guest Name","address","Phone
No:","stayduration","arrival date","departure date","No of persons
accompanying","rooms occupied")]
results=columns+results
[Link]()
return results
def getrefreshguestcount():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"SELECT COUNT(*) FROM guestsinfo WHERE
username='{username}';"
[Link](statement)
result=[Link]()[0]
[Link]()
return result

def refreshguest():
global table1
[Link]()
value1=refreshguestdata()
table1=CTkTable(scrollableframe2,column=8,row=getrefreshguestcount()
+1,values=value1,hover_color="grey")
[Link](row=1)

refreshguestbtn=[Link]([Link]("Guests
Record"),text="Refresh",command=refreshguest)
[Link](row=2)

# add guests functionality

addbtn=[Link]([Link]("Guests Record"),text="Add
Guest",fg_color="#d14fcd",hover_color="#d115cb",command=addguestwindow)
[Link](row=0)

def addroom():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
global window,roomnum

successmsg=[Link](window,text="Success!",text_color="green")
[Link](row=5,column=1)
cur=[Link]()
statement=f"INSERT INTO roomallocate
(room,username,occupied)VALUES({[Link]()},'{realusername}',
{shared_value.get()});"
[Link](statement)
[Link]()
[Link]()
[Link](0,[Link])
def removemsg():
successmsg.grid_forget()
[Link](3000,removemsg)

def editroom():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()

successmsg=[Link](window3,text="Success!",text_color="green")
[Link](column=1,row=5)
statement=f"UPDATE roomallocate SET
occupied={shared_value2.get()} WHERE username='{realusername}' AND
room={[Link]()};"
[Link](statement)
[Link]()
 [Link]()
[Link](0,[Link])
def removemsg():
successmsg.grid_forget()
[Link](3000,removemsg)

def removeroom():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
global window2

successmsg=[Link](window2,text="Success!",text_color="green")
[Link](row=4,column=1)
statement=f"DELETE FROM roomallocate WHERE
username='{realusername}' AND room={[Link]()};"
[Link](statement)
[Link]()
[Link]()
[Link](0,[Link])

def removemsg():
successmsg.grid_forget()
[Link](3000,removemsg)

def editroomwindow():
global window3
window3=[Link]()
[Link]("Edit Room")
[Link]("400x400")
[Link](False,False)
global roomnum2,shared_value2

roomnum2=[Link](window3,placeholder_text="Room No:")
label2=[Link](window3,text="Occupied?")
shared_value2=[Link](value=False)

choiceyes=[Link](window3,text="Yes",variable=shared_value2,
value=True)

choiceno=[Link](window3,text="No",variable=shared_value2,va
lue=False)

donebtn=[Link](window3,text="Done",command=editroom)

#grid
[Link](column=1,row=1)
 [Link](column=1,row=2)
[Link](column=0,row=3)
[Link](column=2,row=3)
[Link](column=1,row=4)

[Link]((0,1,2),weight=1)
[Link]((1,2,3,4,5,6),weight=1)
[Link]()

def delroomwindow():
global window2
window2=[Link]()
[Link]("Delete Room")
[Link]("250x250")
[Link](False,False)

global roomnum3

roomnum3=[Link](window2,placeholder_text="Room No:")

delbtn=[Link](window2,text="Delete",command=removeroom,fg_color=
"#fc4454",hover_color="#fc2638")

[Link](column=1,row=1)
[Link](column=1,row=3)

[Link]((1,2,3,4,5),weight=1)
[Link]((0,1,2),weight=1)
[Link]()

def addroomwindow():
global window
window=[Link]()
[Link]("Add Room")
[Link]("500x500")
[Link](False,False)

global roomnum,shared_value

roomnum=[Link](window,placeholder_text="Room No:")
label1=[Link](window,text="Occupied?")
shared_value=[Link](value=False)

choiceyes=[Link](window,text="Yes",variable=shared_value,va
lue=True)

choiceno=[Link](window,text="No",variable=shared_value,valu
e=False)
 donebtn=[Link](window,text="Done",command=addroom)

#grid
[Link](column=1,row=1)
[Link](column=1,row=2)
[Link](column=0,row=3,sticky=ctk.N+ctk.E)
[Link](column=2,row=3,sticky=ctk.N+ctk.W)
[Link](column=1,row=4)

[Link]((1,2,3,4,5,6),weight=1)
[Link]((0,1,2),weight=1)

[Link]()
# room allocation
global scrollableframe1
scrollableframe1=[Link]([Link]("Room
Allocation"),width=300)

# room table
global table2,value2
value2=getrooms(realusername)

table2=CTkTable(scrollableframe1,column=2,row=getcount(realusername)
+1,values=value2,hover_color="#a3abb8")
[Link]()

def refreshgetrooms():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"SELECT room,occupied FROM roomallocate WHERE
username='{realusername}';"
[Link](statement)
results=[Link]()
[Link]()
finalresult=[]
for i in results:
if i[1]==True:
l1=[i[0],"Occupied"]
else:
l1=[i[0],"Empty"]
[Link](l1)
columns=[("Room No.","Status")]
finalresult=columns+finalresult
return finalresult
 def refreshgetcount():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"SELECT COUNT(*) FROM roomallocate WHERE
username='{realusername}';"
[Link](statement)
result=[Link]()[0]
[Link]()
return result

def refreshrooms():
global table2
[Link]()
value3=refreshgetrooms()

table2=CTkTable(scrollableframe1,column=2,hover_color="#a3abb8",row=ref
reshgetcount()+1,values=value3)
[Link]()

addroombtn=[Link]([Link]("Room Allocation"),text="Add
Room",command=addroomwindow,fg_color="#76ad74",hover_color="#51ad4e")
editroombtn=[Link]([Link]("Room
Allocation"),text="Edit Room",command=editroomwindow)
removeroombtn=[Link]([Link]("Room
Allocation"),text="Remove
Room",fg_color="#fc4454",hover_color="#fc2638",command=delroomwindow)
refreshbtn=[Link]([Link]("Room
Allocation"),text="Refresh",command=refreshrooms)
# config state buttons

[Link]("Room Allocation").rowconfigure((0,1,2),weight=1)
[Link]("Room Allocation").columnconfigure((0,1,2),weight=1)
[Link](row=0,column=0)
[Link](row=2,column=0)
[Link](row=0,column=2)
[Link](row=2,column=2)
[Link](row=1,column=1)

# hotel infra work

global table5
value5=gethotelinfra(username)
scrollableframe5=[Link]([Link]("Hotel
Infrastructure"),width=600)
 [Link](column=1,row=1)

table5=CTkTable(scrollableframe5,column=4,row=counthotelinfra(username)
+1,values=value5,hover_color="#3b3d3d")
[Link]()

def addcompwindow():
global window4
window4=[Link]()
[Link]("Add Component!")
[Link]("700x500")
global compentry,quantity,floor,menu

def addcomp():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
successmsg=[Link](window4,text="Successfully
added!",text_color="green")
[Link](row=7,column=2)
statement=f"INSERT INTO hotel_infrastructure
(component_name,floor_number,quantity,purpose,username)
VALUES('{[Link]()}','{[Link]()}',
{[Link]()},'{[Link]()}','{realusername}');"
[Link](statement)
[Link]()
[Link]()
[Link](0,[Link])
[Link](0,[Link])
[Link](0,[Link])
def removemsg():
successmsg.grid_forget()
[Link](3000,removemsg)

compentry=[Link](window4,placeholder_text="Component
Name:")
quantity=[Link](window4,placeholder_text="Quantity:")
floor=[Link](window4,placeholder_text="Floor:")

purposes=['Accommodation','Guest
Reception','Dining','Recreation','Relaxation','Transportation','Guest
Services','Business Services','Staff
Accommodation','Security','Safety','Facility Management']

purposelabel=[Link](window4,text="Purpose:")

menu=[Link](window4,values=purposes,dynamic_resizing=True)
done2=[Link](window4,text="Add",fg_color="#f2fafa",hover_color="
#e1fafa",command=addcomp,text_color="black")

[Link](column=2,row=2)
[Link](column=2,row=3)
[Link](column=2,row=4)
[Link](column=1,row=5,sticky="e")
[Link](column=2,row=5)
[Link](column=2,row=6)

[Link]((1,2,3),weight=1)
[Link]((1,2,3,4,5,6,7,8),weight=1)
[Link]()

def removecompwindow():
global window5,centry
window5=[Link]()
[Link]("Delete Components")
[Link]("500x500")

def search():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
global options
statement=f"SELECT component_name FROM hotel_infrastructure
WHERE component_name LIKE '%{[Link]()}%' AND
username='{realusername}';"
[Link](statement)
results=[Link]()
[Link]()
l1=[]
for i in results:
[Link](i[0])

options=[Link](window5,values=l1,dynamic_resizing=True)
[Link](column=2,row=4)
def clear():
[Link]()
clearbtn=[Link](window5,text="Clear",command=clear)
[Link](column=2,row=5,sticky="N")

def delete():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
 cur=[Link]()
statement=f"DELETE FROM hotel_infrastructure WHERE
component_name='{[Link]()}' AND username='{realusername}'"
[Link](statement)
[Link]()
[Link]()
successmsg=[Link](window5,text="Deleted
successfully!",text_color="green")
[Link](column=2,row=7)
[Link](0,[Link])
def removemsg():
successmsg.grid_forget()
[Link](3000,removemsg)

centry=[Link](window5,placeholder_text="Search
component:")
searchbtn=[Link](window5,text="Search",command=search)

delbtn=[Link](window5,text="Delete",fg_color="#fc4454",hover_col
or="#fc2638",text_color="white",command=delete)

[Link](column=2,row=6)
[Link](column=2,row=2)
[Link](column=2,row=3)
[Link]((1,2,3,4,5,6,7,8,9),weight=1)
[Link]((1,2,3),weight=1)
[Link]()
def getrefreshhotelinfra():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"SELECT component_name,floor_number,quantity,purpose
FROM hotel_infrastructure WHERE username='{realusername}';"
[Link](statement)
result=[Link]()
[Link]()
columns=[("Component","Quantity","Floor","Purpose")]
finalresult=columns+result
return finalresult

def countrefreshhotelinfra():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"SELECT COUNT(*) FROM hotel_infrastructure WHERE
username='{realusername}';"
 [Link](statement)
result=[Link]()[0]
[Link]()
return result

def refreshinfra():
global table5
[Link]()
value5=getrefreshhotelinfra()

table5=CTkTable(scrollableframe5,column=4,row=countrefreshhotelinfra()
+1,values=value5)
[Link]()
# add/remove buttons
addinfrabtn=[Link]([Link]("Hotel
Infrastructure"),text="Add Component",command=addcompwindow)
removeinfrabtn=[Link]([Link]("Hotel
Infrastructure"),text="Remove
Component",fg_color="#fc4454",hover_color="#fc2638",text_color="white",
command=removecompwindow)
refreshinfrabtn=[Link]([Link]("Hotel
Infrastructure"),text="Refresh",command=refreshinfra)
[Link](column=0,row=1)
[Link](column=2,row=1)
[Link](column=1,row=2)
# grids
[Link]("Hotel
Infrastructure").columnconfigure((0,1,2),weight=1)

# employee work
def addemployeewindow():

def addemployee():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"INSERT INTO
hotel_employees(first_name,last_name,position,department,hire_date,sala
ry,username)
VALUES('{[Link]()}','{[Link]()}','{[Link]()}','{[Link]()}','{
[Link]()}','{[Link]()}','{realusername}');"
[Link](statement)
[Link]()
[Link]()
successmsg=[Link](window6,text="Successfully
Added!",text_color="green")
 [Link](row=7,column=2)
[Link](0,[Link])
[Link](0,[Link])
[Link](0,[Link])
[Link](0,[Link])
[Link](0,[Link])
[Link](0,[Link])
def removemsg():
successmsg.grid_forget()
[Link](3000,removemsg)

window6=[Link]()
[Link]("Add Employee")
[Link]("700x700")
fname=[Link](window6,placeholder_text="First name:")
lname=[Link](window6,placeholder_text="Last name:")
posn=[Link](window6,placeholder_text="Position:")
dept=[Link](window6,placeholder_text="Department:")
hdate=[Link](window6,placeholder_text="Hire date:")
salary=[Link](window6,placeholder_text="Salary:")
addempbtn=[Link](window6,text="Add
Employee",command=addemployee)

[Link](column=2,row=6)
[Link](column=2,row=2)
[Link](column=2,row=3,sticky='N')
[Link](column=2,row=3,sticky='S')
[Link](column=2,row=4)
[Link](column=2,row=5,sticky='N')
[Link](column=2,row=5,sticky='S')

[Link]((1,2,3,4,5,6,7,8,9),weight=1)
[Link]((1,2,3),weight=1)

[Link]()

def delemployeewindow():

def searchemp():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
global options3
 statement=f"SELECT first_name,last_name FROM
hotel_employees WHERE first_name LIKE '%{[Link]()}%' OR last_name
LIKE '%{[Link]()}%';"
[Link](statement)
results=[Link]()
[Link]()
l1=[]
for i in results:
[Link](i[0]+" " +i[1])

options3=[Link](window7,values=l1,dynamic_resizing=True)
[Link](column=2,row=4)
def clear():
[Link]()
clearbtn=[Link](window7,text="Clear",command=clear)
[Link](column=2,row=5)

def delemp():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
fullname=[Link]().split(" ")
first=fullname[0]
last=fullname[1]
statement=f"DELETE FROM hotel_employees WHERE
first_name='{first}' AND last_name='{last}' AND
username='{realusername}';"
[Link](statement)
[Link]()
[Link]()
successmsg=[Link](window7,text="Successfully
Deleted!",text_color="green")
[Link](column=2,row=7)
[Link](0,[Link])
def clearmsg():
[Link]()
[Link](3000,clearmsg)

window7=[Link]()
[Link]("Remove Employee")
[Link]("400x400")

ementry=[Link](window7,placeholder_text="Enter employee
name:")

searchbtn2=[Link](window7,text="Search
employee",command=searchemp)
delbtn2=[Link](window7,text="Delete",fg_color="#fc4454",hover_co
lor="#fc2638",text_color="white",command=delemp)

[Link](column=2,row=6)
[Link](column=2,row=2)
[Link](column=2,row=3)
[Link]((1,2,3,4,5,6,7,8),weight=1)
[Link]((1,2,3),weight=1)

[Link]()

def getrefreshemployee():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"SELECT first_name, last_name, position, department,
hire_date, salary FROM hotel_employees WHERE
username='{realusername}';"
[Link](statement)
result=[Link]()
[Link]()
columns=[("first_name", "last_name", "position", "department",
"hire_date", "salary")]
finalresult=columns+result
return finalresult

def getrefreshempcount():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"SELECT COUNT(*) FROM hotel_employees WHERE
username='{realusername}';"
[Link](statement)
result=[Link]()[0]
[Link]()
return result

def refreshemployee():
global table4
[Link]()
value4=getrefreshemployee()

table4=CTkTable(scrollableframe4,column=6,row=getrefreshempcount()
+1,values=value4,hover_color="#121212")
[Link]()
scrollableframe4=[Link]([Link]("Employees"),width
=870)
addempbtn=[Link]([Link]("Employees"),text="Add
Employee",fg_color="#f2fafa",hover_color="#e1fafa",text_color="black",c
ommand=addemployeewindow)
removeempbtn=[Link]([Link]("Employees"),text="Remove
Employee",fg_color="#fc4454",hover_color="#fc2638",command=delemployeew
indow)

refreshempbtn=[Link]([Link]("Employees"),text="Refresh",co
mmand=refreshemployee)

[Link]("Employees").rowconfigure((0,1,2),weight=1)
[Link]("Employees").columnconfigure((0,1,2),weight=1)

[Link](row=1,column=1)
[Link](row=0,column=0)
[Link](row=0,column=2)
[Link](row=0,column=1)
# employee table
global table4
value4=getemployee(username)
table4=CTkTable(scrollableframe4,column=6,row=getempcount(username)
+1,values=value4,hover_color="#121212")
[Link]()

# financial work

scrollableframe3=[Link]([Link]("Financial"),width
=870)
[Link](row=1,sticky=('WE'),column=2)
value3=getfinance(username)

# finance function
def addfinance():
financewindow=[Link]()
[Link]("Add Financial Details")
[Link]("600x700")
[Link](False,False)

global yearentry,reventry,expentry,netentry,adrentry,occupentry
yearentry=[Link](financewindow,placeholder_text="Year:")
reventry=[Link](financewindow,placeholder_text="Total
revenue:")
expentry=[Link](financewindow,placeholder_text="Total
expenses:")
 netentry=[Link](financewindow,placeholder_text="Net
income")
adrentry=[Link](financewindow,placeholder_text="ADR:")

occupentry=[Link](financewindow,placeholder_text="Occupancy
rate:")

def savedatafin():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"INSERT INTO
financial(yearly,total_revenue,total_expenses,net_income,adr,occupancy_
rate,username) VALUES({[Link]()},{[Link]()},
{[Link]()},{[Link]()},{[Link]()},
{[Link]()},'{realusername}');"
[Link](statement)
[Link]()
[Link]()
[Link](0,[Link])
[Link](0,[Link])
[Link](0,[Link])
[Link](0,[Link])
[Link](0,[Link])
successmsg=[Link](financewindow,text="Saved
Successfully",text_color="green")
[Link](row=8,column=2)
def clearmsg():
[Link]()
[Link](3000,clearmsg)

savebtn=[Link](financewindow,text="Save",command=savedatafin)

[Link](row=7,column=2)
[Link](row=1,column=2)
[Link](row=2,column=2)
[Link](row=3,column=2)
[Link](row=4,column=2)
[Link](row=5,column=2)
[Link](row=6,column=2)

[Link]((1,2,3,4,5,6,7,8,9),weight=1)
[Link]((1,2,3),weight=1)
[Link]()
# add finance
 addfinancebtn=[Link]([Link]("Financial"),text="Add
Financial
Data",command=addfinance,fg_color="#f2fafa",hover_color="#e1fafa",text_
color="black")
[Link](row=0,column=1,sticky=('E'))
def removefinance():
removefinwindow=[Link]()
[Link]("Remove Financial Details")
[Link]("300x300")
[Link](False,False)

def removefin():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"DELETE FROM financial WHERE
yearly={[Link]()} AND username='{realusername}';"
[Link](statement)
[Link]()
[Link]()
successmsg=[Link](removefinwindow,text="Deletion
successful!",text_color="green")
[Link](row=3,column=2)
[Link](0,[Link])
def clearmsg2():
[Link]()
[Link](3000,clearmsg2)

global yearremove

yearremove=[Link](removefinwindow,placeholder_text="Year:")

removeconfirmbtn=[Link](removefinwindow,text="Remove",command=re
movefin)

[Link](row=1,column=2)
[Link](row=2,column=2)

[Link]((1,2,3),weight=1)
[Link]((1,2,3,4),weight=1)
[Link]()

removefinancebtn=[Link]([Link]("Financial"),text="Remove
Finance",command=removefinance,fg_color="#fc4454",hover_color="#fc2638"
)
[Link](row=0,column=3)
# refresh finance functions
 def getrefreshfinance():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"SELECT yearly, total_revenue, total_expenses,
net_income, adr, occupancy_rate FROM financial WHERE
username='{realusername}';"
[Link](statement)
result=[Link]()
[Link]()
columns=[("yearly", "total_revenue", "total_expenses",
"net_income", "adr", "occupancy_rate")]
finalresult=columns+result
return finalresult

def financerefreshcount():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
statement=f"SELECT COUNT(*) FROM financial WHERE
username='{realusername}';"
[Link](statement)
result=[Link]()[0]
[Link]()
return result

def refreshfinance():
global table3
[Link]()
value3=getrefreshfinance()

table3=CTkTable(scrollableframe3,column=6,row=financerefreshcount()
+1,values=value3,hover_color="#121212")
[Link]()

refreshfinancebtn=[Link]([Link]("Financial"),text="Refresh
",command=refreshfinance)
[Link](row=0,column=2)

global table3

table3=CTkTable(scrollableframe3,column=6,row=financecount(username)
+1,values=value3,hover_color="#121212")
[Link]()
[Link]("Financial").columnconfigure((1,2,3),weight=1)
 [Link]("Financial").rowconfigure((0,1,2),weight=1)

# grid work
[Link](row=0)
[Link](column=1,row=0)
[Link](column=1,row=1)
copyright2=[Link](root2,text="Copyright© by [Link],[Link] &
[Link]",text_color="grey",font=('Microsoft YaHei Light',10))
[Link](row=2,column=1,sticky=('SE'))
[Link]((0,1,2),weight=1)
[Link]()

# login page functions

def loginactivate():
login=[Link]()
[Link]("300x200")
[Link]("Login")
# [Link]("[Link]")
[Link](False,False)
def loginfinish():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
usnm=[Link]()
pwd=[Link]()
statement=f"SELECT pwd FROM userinfo WHERE usname='{usnm}';"
[Link](statement)
value=[Link]()[0]
[Link]()
truepwd=value
if truepwd==pwd:
global realusername
realusername=usnm
[Link]()
[Link]()
maincontentlogin(realusername)
else:
wrongmsg=[Link](login,text="Wrong password/username!
Please try again!",text_color="red")
[Link](row=3,column=1,pady=10)
# widgets
username=[Link](login,placeholder_text="Username:")
password=[Link](login,placeholder_text="Password")
loginbtn=[Link](login,command=loginfinish,text="Login")
[Link](0,weight=1)
[Link](1,weight=1)
[Link](2,weight=1)
 [Link](row=0,column=1,pady=10)
[Link](row=1,column=1,pady=10)
[Link](row=2,column=1,pady=10)
[Link]()
def signupactivate():
signup=[Link]()
[Link]("300x400")
[Link]("Sign Up")
[Link](False,False)
# [Link]("[Link]")
def signupfinish():

conn=[Link](user='root',host='localhost',password='ass
sr2003$',port=3306,database='hoteldb')
cur=[Link]()
# entry into database
usnm=[Link]()
pwd=[Link]()
emid=[Link]()
htname=[Link]()
available=availability(usnm)
if available==0:
# successmsg=[Link](signup,text="Registration
Successful!\nWindow closes in 5 seconds!",text_color="green")
# [Link](row=6,column=1)
sql=f"INSERT INTO userinfo(usname,pwd,emailid,hotelname)
VALUES('{usnm}','{pwd}','{emid}','{htname}');"
[Link](sql)
[Link]()
[Link]()
# destroy the window
[Link]()
else:
signuperrormsg=[Link](signup,text="Username already
exists!",text_color="red")
[Link](row=5,column=1)
# Entry widgets
username=[Link](signup,placeholder_text="Username:")
password=[Link](signup,placeholder_text="Password:")
emailid=[Link](signup,placeholder_text="Email address:")
hotelname=[Link](signup,placeholder_text="Hotel Name:")
donebtn=[Link](signup,command=signupfinish,text="Done")
# grid configurations
[Link](0,weight=1)
[Link](1,weight=1)
[Link](2,weight=1)
[Link](row=0,column=1,pady=5)
[Link](row=1,column=1,pady=5)
 [Link](row=2,column=1,pady=5)
[Link](row=3,column=1,pady=5)
[Link](row=4,column=1,pady=10)
[Link]()

# login page
login_label=[Link](root,text="Login/Sign
Up",font=('Helvetica',25,"bold"),fg_color="transparent")
greetings_label=[Link](root,text="Welcome to Hosthub Hotel
Management Software. Enter your details to sign in!",font=('Roboto
Condensed',15,"italic"))
loginbtn=[Link](root,text="Login",command=loginactivate,font=('M
icrosoft YaHei UI',20,"bold"))
signupbtn=[Link](root,text="Sign
Up",command=signupactivate,font=('Microsoft YaHei UI',20,"bold"))

# copyright statement
copyright=[Link](root,text="Copyright© by [Link],[Link] &
[Link]",text_color="grey",font=('Microsoft YaHei Light',10))
[Link](row=4,column=1,sticky=('SE'))
# grid configurations
[Link](0,weight=1)
[Link](1,weight=1)
[Link](2,weight=1)
login_label.grid(row=0,column=1,pady=50,padx=50,ipadx=30,ipady=10,)
greetings_label.grid(row=1,column=1)
[Link](row=2,column=1,pady=20)
[Link](row=3,column=1)
[Link](4,weight=2)

[Link]()

MySQL Database Design