def petsitter_signup_data(user, data):
try:
with DBcm.UseDatabase(connection) as cursor:
SQL = """SELECT id FROM customer WHERE email = %s"""
cursor.execute(
SQL,
(user, ),
)
return_data = cursor.fetchall()
customerid = return_data[0][0]
#customerid = ''.join(str(return_data[0]))
if data.getlist('service[]'):
petservice = format(data.getlist('service[]'))
else:
petservice = "[]"
if data.getlist('type[]'):
pettype = format(data.getlist('type[]'))
pettype = pettype.replace(", ''", "")
else:
pettype = "[]"
if data.getlist('size[]'): petsize = format(data.getlist('size[]'))
else: petsize = "[]"
with DBcm.UseDatabase(connection) as cursor:
SQL = """ INSERT INTO petsitter (
customerid,
petsittername,
email,
phone,
country,
city,
town,
address,
description,
price,
petservice,
petnums,
pettype,
petsize,
housetype,
outdoor,
transport,
deleteflag
) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 0) """
cursor.execute(
SQL,
(customerid, data['petsittername'], data["email"],
data["phone"], data['country'], data['city'], data['town'],
data['street'], data['description'], data['price'],
petservice, data['numofpet'], pettype, petsize, data['house'],
data['outdoor'], data['transport']),
)
except:
print("Error")
def api_connect_irc():
try:
parser = reqparse.RequestParser()
parser.add_argument('host', help="IRC Server host")
parser.add_argument('port', help='User login name for Authentication')
parser.add_argument('username', help="IRC User Name")
args = parser.parse_args()
_userName = args['username']
_host = args['host']
_port = args['port']
data = None
with DBcm.UseDatabase(app.config["DB_CONFIG"]) as cursor:
cursor.execute(
""" SELECT idIrcServers
FROM IrcServers
WHERE host = %s AND port = %s""", (
_host,
_port,
))
# if server exist
data = cursor.fetchone()
if data is not None:
session["server_id"] = data[0]
# if server does not exist, then create it
else:
with DBcm.UseDatabase(app.config["DB_CONFIG"]) as cursor:
cursor.execute(
""" INSERT INTO IrcServers (host, port) VALUES (%s, %s) """,
(
_host,
_port,
))
session["server_id"] = cursor.lastrowid
# update username for user
with DBcm.UseDatabase(app.config["DB_CONFIG"]) as cursor:
cursor.execute(
""" UPDATE Users
SET userName = %s
WHERE login = %s """, (_userName, session["user"]))
msg = {
'Id': session["server_id"],
'Host': _host,
'Port': _port,
'Username': _userName
}
return jsonify(session=msg), 200
except Exception as e:
return jsonify(error=str(e)), 500
def add_scores(name: str, score: float, sourceword: str) -> None:
_SQL = """Insert into leaderboard
(name, score, sourceword)
values
(%s, %s, %s)"""
with DBcm.UseDatabase(config) as cursor:
cursor.execute(_SQL, (name, score, sourceword))
def get_all_modes():
"""
simply reads all json files in the current directory, any with the type
param of "gameMode" will be read in and stored in a list, this is needed
for the user to choose which "gameMode" later
"""
game_modes = []
with DBcm.UseDatabase(config) as cursor:
_SQL = """select * from game_modes"""
cursor.execute(_SQL)
data = cursor.fetchall()
game_modes = []
for row in data:
game_mode = {}
game_mode["id"] = row[0]
game_mode["name"] = row[2]
game_mode["max_players"] = row[3]
game_mode["number_questions"] = row[4]
game_mode["time"] = row[1]
game_modes.append(game_mode)
return game_modes
def check_password(username: str):
"""Check the passwords."""
with DBcm.UseDatabase(DBconfig) as cursor:
_SQL = "select * from user where (username = '******')" % username
cursor.execute(_SQL)
return cursor.fetchone()
def gameDetails():
session["gameID"] = request.form["gameID"]
with DBcm.UseDatabase(config) as cursor:
SQL = "select * from review where review.game_id = " + session["gameID"]
cursor.execute(SQL)
data = cursor.fetchall()
return render_template("gameDetails.html", the_data=data)
def customer_signup_data(data):
try:
newstring = "'" + data['email'] + "','" + data['password'] + "'"
SQL = """ INSERT INTO customer (email,password"""
if data['fname'] != '':
SQL += ",fname"
newstring += ",'" + data['fname'] + "'"
if data['sname'] != '':
SQL += ",sname"
newstring += ",'" + data['sname'] + "'"
if data['title'] != '':
SQL += ",title"
newstring += ",'" + data['title'] + "'"
if data['dob'] != '':
SQL += ",dateOfBirth"
newstring += ",'" + data['dob'] + "'"
if data['address'] != '':
SQL += ",address"
newstring += ",'" + data['address'] + "'"
if data['phone'] != '':
SQL += ",contactNumber"
newstring += ",'" + data['phone'] + "'"
SQL += ") VALUES (" + newstring + ")"
print(SQL)
with DBcm.UseDatabase(connection) as cursor:
cursor.execute(SQL)
except:
print("Error")
def list_pettype_data():
try:
with DBcm.UseDatabase(connection) as cursor:
SQL = """SELECT DISTINCT pettype FROM petsitter"""
cursor.execute(SQL)
data = cursor.fetchall()
pettype = ['dog', 'cat']
lastEle = ['other']
newlist = []
while len(data) > 0:
temp = data.pop()
templist = str_to_list(temp[0])
while len(templist) > 0:
tempElement = templist.pop()
if tempElement.lower(
) not in newlist and tempElement.lower(
) not in pettype and tempElement.lower(
) not in lastEle and tempElement != "":
newlist.append(tempElement.lower())
newlist.sort()
pettype.extend(newlist)
pettype.extend(lastEle)
return pettype
except:
print("Error")
def update_account_data(data, email):
try:
title = data['title']
fname = data['fname']
sname = data['sname']
dateOfBirth = str(data['dob'])
contactNumber = data['phone']
address = data['address']
with DBcm.UseDatabase(connection) as cursor:
SQL = """UPDATE customer
SET title = %s, fname = %s, sname = %s, dateOfBirth = %s, contactNumber = %s, address = %s
WHERE email = %s"""
cursor.execute(
SQL,
(
title,
fname,
sname,
dateOfBirth,
contactNumber,
address,
email,
),
)
except:
print("Error")
def retrieve_sorted_leaderboard() -> list:
_SQL = """Select score, name, sourceword From leaderboard
order By score"""
with DBcm.UseDatabase(config) as cursor:
cursor.execute(_SQL)
data = cursor.fetchall()
return [(float(row[0]), row[1], row[2]) for row in data]
def api_login():
try:
# Parse the arguments
parser = reqparse.RequestParser()
parser.add_argument('login', help='User login name for Authentication')
parser.add_argument('password', help='Password for Authentication')
args = parser.parse_args()
_userLogin = args['login']
_userPassword = args['password']
with DBcm.UseDatabase(app.config["DB_CONFIG"]) as cursor:
cursor.execute(
"""SELECT idUsers
FROM Users
WHERE login=%s
AND password=%s """, (_userLogin, _userPassword))
data = cursor.fetchone() # data is a tuple
if data is not None:
session["logged_in"] = True
session["user"] = _userLogin
session["id"] = data[0]
return jsonify(logged_in=session["logged_in"],
user=session["user"],
id=session["id"]), 200
else:
return jsonify(info="Bad credentials"), 400
except Exception as e:
return jsonify(error=str(e)), 500
def get_towns(self):
with DBcm.UseDatabase(config) as cursor:
cursor.execute(
"""SELECT storeTown FROM store WHERE storeCounty = countyValue"""
)
rows = cursor.fetchall()
self.label_lang.values = map(str, rows)
def view_booking_data(user):
try:
with DBcm.UseDatabase(connection) as cursor:
SQL = """SELECT id FROM customer WHERE email = %s"""
cursor.execute(
SQL,
(user, ),
)
return_data = cursor.fetchall()
customerid = int(return_data[0][0])
with DBcm.UseDatabase(connection) as cursor:
SQL = """SELECT
booking.bookingRef,
booking.petsitterID,
booking.status,
booking.title,
booking.fname,
booking.sname,
booking.phone,
booking.email,
booking.startDate,
booking.endDate,
booking.totalPrice,
booking.petQunatity,
booking.typeOfPet,
booking.breed,
booking.note,
booking.createdBy,
petsitter.petsittername,
petsitter.country,
petsitter.city,
petsitter.town,
petsitter.address
FROM booking
INNER JOIN petsitter ON (booking.petsitterID = petsitter.id)
WHERE booking.customerID = %s
ORDER BY booking.createdBy DESC"""
cursor.execute(
SQL,
(customerid, ),
)
data = cursor.fetchall()
return data
except:
print("Error")
def getHomepageNews():
sql = """SELECT headline, paragraph FROM news
ORDER BY newsID DESC LIMIT 3"""
stories = []
with DBcm.UseDatabase(config) as database:
database.execute(sql)
stories = database.fetchall()
return stories
def getHeadline(id):
sql = """SELECT headline, FROM news
WHERE newsID = %s"""
with DBcm.UseDatabase(config) as database:
database.execute(sql, (id))
rows = database.fetchall()
return rows[0][0]
def addAdmin(admin):
for i in admin:
admin[i] = encrypt.encrypt(admin[i])
_SQL = """INSERT INTO admin
(adminUsername, adminPassword)
values
(%s, %s)"""
with DBcm.UseDatabase(config) as database:
database.execute(_SQL, (admin['username'], admin['password']))
def get_sorted_leaderboard() -> list:
"""Return a sorted list of tuples - this is the leaderboard."""
with DBcm.UseDatabase(config) as cursor:
_SQL = """
select timetaken, name, sourceword from leaderboard
order by timetaken asc
"""
cursor.execute(_SQL)
return cursor.fetchall()
def check_email_exists_data():
try:
with DBcm.UseDatabase(connection) as cursor:
SQL = """SELECT email FROM customer"""
cursor.execute(SQL)
data = cursor.fetchall()
return data
except:
print("Error")
def api_join_channel(channel: int):
with DBcm.UseDatabase(app.config["DB_CONFIG"]) as cursor:
cursor.execute(
""" UPDATE Users
SET channel = %s, idIrcServers = %s
WHERE login = %s """,
(channel, session["server_id"], session["user"]))
session["channel"] = channel
return jsonify(info="Channel joined"), 200
def insertNewGame():
createGame(request.form["GameName"], request.form["ReleaseYear"],
request.form["GameStudio"], request.form["Genre"],
request.form["Platform"])
with DBcm.UseDatabase(config) as cursor:
SQL = "select titles.id, titles.name, titles.game_studio, titles.release_year, genre.name, platform.name,titles.likes, titles.dislikes from titles, genre, platform where titles.genre = genre.id and titles.platform = platform.id "
cursor.execute(SQL)
data = cursor.fetchall()
return render_template("viewTitles.html", the_data=data)
def add_to_scores(name, score, word) -> None:
"""Add the name and its associated score to the pickle."""
with DBcm.UseDatabase(config) as cursor:
_SQL = """
insert into leaderboard
(name, sourceword, timetaken)
values
(%s, %s, %s)
"""
cursor.execute(_SQL, (name, word, score))
def getPrice(name):
decPrice = []
name = encrypt.encrypt(name)
_SQL = """SELECT price FROM cardtypes WHERE name = %s;"""
with DBcm.UseDatabase(config) as database:
database.execute(_SQL, (name, ))
price = database.fetchall()
decPrice.append(encrypt.decrypt(price[0][0]))
return decPrice
def checkUsername(username):
username = encrypt.encrypt(username)
_SQL = """SELECT username FROM users WHERE username = %s;"""
with DBcm.UseDatabase(config) as database:
database.execute(_SQL, (username, ))
user = database.fetchall()
if len(user):
return True
return False
def checkAdmin(user):
user = encrypt.encrypt(user)
_SQL = """SELECT adminUsername FROM admin WHERE adminUsername = %s"""
with DBcm.UseDatabase(config) as database:
database.execute(_SQL, (user, ))
row = database.fetchone()
if row:
return True
return False
def getBalance(username
): #used to display the users balance at the top of each screen
username = encrypt.encrypt(username)
_SQL = """SELECT balance FROM users WHERE username = %s;"""
with DBcm.UseDatabase(config) as database:
database.execute(_SQL, (username, ))
balance = database.fetchall()
decBalance = encrypt.decrypt(balance[0][0])
numBal = '{:.2f}'.format(float(decBalance))
return numBal
def getNewsID():
sql = """SELECT id WHERE news
ORDER BY newsID DESC LIMIT 1"""
stories = []
with DBcm.UseDatabase(config) as database:
database.execute(sql)
stories = database.fetchall()
id = stories[0][0]
return id
def put_alert():
_id = request.form['id']
alert = request.form['alert']
with DBcm.UseDatabase(config) as cursor:
_SQL = """insert into alerts
(alert_id, alert_text)
values
(%s, %s)"""
cursor.execute(_SQL, (_id, alert))
return "Thanks for the alert!"
def view_petsitter_data(petsittername):
with DBcm.UseDatabase(connection) as cursor:
SQL = """SELECT * FROM petsitter
WHERE petsittername = %s
"""
cursor.execute(
SQL,
(petsittername, ),
)
data = cursor.fetchall()
return data
def addPlayer(player):
sql = """INSERT INTO players
(firstname,lastname,email,phonenumber,club,username,password,isboardmember,approved)
values
(%s,%s,%s,%s,%s,%s,%s,0,0)"""
with DBcm.UseDatabase(config) as database:
database.execute(
sql, (player['firstname'], player['lastname'], player['email'],
player['phonenumber'], player['club'], player['username'],
player['password']))
def submitReview():
createReview(session["gameID"], 0, request.form["Like/Dislike"],
request.form["played"], request.form["ownership"],
request.form["Rating"], request.form["comment"])
#updates titles likes
with DBcm.UseDatabase(config) as cursor:
SQL = "update titles set likes = (select count(*) from review where review.game_id = 1 && review.liked = 1) where id =" + session[
"gameID"]
cursor.execute(SQL)
#updates titles dislikes
with DBcm.UseDatabase(config) as cursor:
SQL = "update titles set dislikes = (select count(*) from review where review.game_id = 1 && review.liked = 2 ) where id =" + session[
"gameID"]
cursor.execute(SQL)
with DBcm.UseDatabase(config) as cursor:
SQL = "select titles.id, titles.name, titles.game_studio, titles.release_year, genre.name, platform.name,titles.likes, titles.dislikes from titles, genre, platform where titles.genre = genre.id and titles.platform = platform.id "
cursor.execute(SQL)
data = cursor.fetchall()
return render_template("viewTitles.html", the_data=data)