def get_user_details_by_email(email):
"""
Gets all details for a single user
Args:
email: the users email address
Returns:
details: array of [userId, firstname, surname, location]
"""
sql = "SELECT Users.userId, Users.first_name, Users.surname, Locations.location_id FROM Users\
INNER JOIN Locations ON Locations.location_id = Users.location_id\
WHERE Users.email = %s;"
conn = None
details = None
try:
conn = connect.db_connection()
c = conn.cursor()
c.execute(sql, (email, ))
details = c.fetchall()
c.close()
except Exception as error:
print(error)
finally:
if conn is not None:
conn.close()
return details
def create_booking(bDict):
"""
Create a new booking for a user to rent/borrow a service
"""
sql = "INSERT INTO Bookings (start_date, end_date, user_id, service_id)\
VALUES (%s, %s, %s, %s) RETURNING booking_id;"
booking_id = None
conn = None
try:
conn = connect.db_connection()
c = conn.cursor()
c.execute(sql, (
bDict['startdate'],
bDict['enddate'],
bDict['userId'],
bDict['serviceId'],
))
booking_id = c.fetchone()[0]
services.update_availabilty(bDict['serviceId'], False)
conn.commit()
c.close()
except Exception as error:
print(error)
finally:
if conn is not None:
conn.close()
return booking_id
def add_location(locationsDict):
""" args {name: string,
address: string
city: string,
user_id: int,
postcode: string}
returns location id """
conn = None
try:
conn = connect.db_connection()
print(conn)
c = conn.cursor()
c.execute(
"INSERT INTO locations(name, address, city, user_id,postcode)\
VALUES(%s,%s,%s,%s,%s)\
RETURNING location_id;",
(locationsDict['name'], locationsDict['address'],
locationsDict['city'], locationsDict['user_id'],
locationsDict['postcode']))
conn.commit()
location_id = c.fetchall()[0]
c.close()
conn.close()
return location_id
except Exception as error:
print(error)
finally:
if conn is not None:
conn.close()
def update_location(locId, userId):
"""
Update the location for a single user
Args:
userId: the user ID to update
locId: the location ID
Returns:
0 - failure (no rows updated)
1 - success (row updated)
"""
sql = "UPDATE Users SET location_id = %s WHERE user_id = %s;"
conn = None
updatedRows = 0
try:
conn = connect.db_connection()
c = conn.cursor()
c.execute(sql, (
locId,
userId,
))
updatedRows = c.rowcount
conn.commit()
c.close()
except Exception as error:
print(error)
finally:
if conn is not None:
conn.close()
return updatedRows
def get_user_details(userId):
"""
Gets all details for a single user
Args:
userId: the user id to get info for
Returns:
details: array of [firstname, surname, email, location]
"""
sql = "SELECT Users.first_name, Users.surname, Users.email, Locations.location_id FROM Users\
INNER JOIN Locations ON Locations.location_id = Users.location_id\
WHERE user_id = %s;"
conn = None
details = None
try:
conn = connect.db_connection()
c = conn.cursor()
c.execute(sql, (userId, ))
details = c.fetchall()
c.close()
except Exception as error:
print(error)
finally:
if conn is not None:
conn.close()
return details
def update_membership(memberBool, userId):
"""
Update the membership status for a single user
Args:
memberBool: the membership status true/false
userId: the user ID to update
Returns:
0 - failure (no rows updated)
1 - success (row updated)
"""
sql = "UPDATE Users SET membership = %s WHERE user_id = %s;"
conn = None
updatedRows = 0
try:
conn = connect.db_connection()
c = conn.cursor()
c.execute(sql, (
memberBool,
userId,
))
updatedRows = c.rowcount
conn.commit()
c.close()
except Exception as error:
print(error)
finally:
if conn is not None:
conn.close()
return updatedRows
def add_user(userDict):
"""
Add a new user to user table
Args:
userDict: A dictionary in the form
{'firstname': [string],
'surname': [string],
'email': [string],
'password': [string],
'membership': [boolean],
'locId':[int]
}
Returns:
user_id
"""
sql = "INSERT INTO Users(first_name, surname, email, password, membership, location_id) VALUES (%s, %s, %s, %s, %s, %s) RETURNING user_id;"
user_id = None
conn = None
try:
conn = connect.db_connection()
c = conn.cursor()
c.execute(sql, (
userDict['firstname'],
userDict['surname'],
userDict['email'],
bcrypt.hash(userDict['password']),
userDict['membership'],
userDict['locId'],
))
user_id = c.fetchone()[0]
conn.commit()
c.close()
except Exception as error:
print(error)
finally:
if conn is not None:
conn.close()
return user_id
def add_services(servicesDict):
"""
Add a new service to user table
Args:
userDict: A dictionary in the form
{'name': [string],
'available': [boolean],
'location': [string],
'serviceType': [string],
'points': [int],
'price' :[float],
'user_id':[int]
}
Returns:
service_id if successful
"""
conn = None
try:
conn = connect.db_connection()
print(conn)
c = conn.cursor()
c.execute(
"INSERT INTO services(name, available, type, points,location_id,price,user_id)\
VALUES(%s,%s,%s,%s,%s,%s,%s)\
RETURNING service_id;",
(servicesDict['name'], servicesDict['available'],
servicesDict['serviceType'], servicesDict['points'],
servicesDict['location'], servicesDict['price'],
servicesDict['user_id']))
conn.commit()
service_id = c.fetchall()[0]
c.close()
conn.close()
return service_id
except Exception as error:
print(error)
finally:
if conn is not None:
conn.close()
def get_servicelist():
"""returns a list with tuples in off all the sevices available
in the format, user_is, name, available, type, points, location_id, price, user_id """
details = None
conn = None
try:
conn = connect.db_connection()
c = conn.cursor()
c.execute("SELECT * FROM services;")
conn.commit()
details = c.fetchall()
c.close()
conn.close()
except Exception as error:
print(error)
finally:
if conn is not None:
conn.close()
return details
def select_availabilty(service_id):
""" gets the availability of the product
returns available [boolean] """
details = None
conn = None
try:
conn = connect.db_connection()
c = conn.cursor()
c.execute("SELECT available FROM services WHERE service_id =%s;",
(service_id))
conn.commit()
details = c.fetchall()[0]
c.close()
conn.close()
except Exception as error:
print(error)
finally:
if conn is not None:
conn.close()
return details
def delete_service(service_id):
""" Delete a service from the system
args: service_id int
returns 0 if successful
1 if failed"""
conn = None
try:
conn = connect.db_connection()
c = conn.cursor()
c.execute("DELETE FROM services WHERE service_id =%s;", (service_id))
conn.commit()
c.close()
conn.close()
except Exception as error:
print(error)
return 0
finally:
if conn is not None:
conn.close()
return 1
def update_availabilty(service_id, available):
"""Updates the services availbility
returns 1 if true
"""
conn = None
try:
conn = connect.db_connection()
c = conn.cursor()
c.execute("UPDATE services SET available =%s WHERE service_id =%s;",
(available, service_id))
conn.commit()
c.close()
conn.close()
except Exception as error:
print(error)
finally:
if conn is not None:
conn.close()
return 1
def get_service(service_id):
""" Gets the information about a service
sends it back as an array [name,available,location,type,points]"""
details = None
conn = None
try:
conn = connect.db_connection()
c = conn.cursor()
c.execute("SELECT * FROM services WHERE service_id =%s;", (service_id))
conn.commit()
details = c.fetchall()
c.close()
conn.close()
except Exception as error:
print(error)
finally:
if conn is not None:
conn.close()
return details
def get_loc_id(name):
"""
Returns locations id for a location name
"""
sql = "SELECT location_id FROM Locations WHERE name=%s;"
conn = None
locId = None
try:
conn = connect.db_connection()
c = conn.cursor()
c.execute(sql, (name, ))
locId = c.fetchone()[0]
c.close()
except Exception as error:
print(error)
finally:
if conn is not None:
conn.close()
return locId
def get_loc_name(locId):
"""
Returns locations name for a single id
"""
sql = "SELECT name FROM Locations WHERE location_id=%s;"
conn = None
location = None
try:
conn = connect.db_connection()
c = conn.cursor()
c.execute(sql, (locId, ))
location = c.fetchone()[0]
c.close()
except Exception as error:
print(error)
finally:
if conn is not None:
conn.close()
return location
def get_loc_list():
"""
Returns entire list of location names
"""
sql = "SELECT name FROM Locations;"
conn = None
locations = None
try:
conn = connect.db_connection()
c = conn.cursor()
c.execute(sql, ())
locations = c.fetchall()
c.close()
except Exception as error:
print(error)
finally:
if conn is not None:
conn.close()
return locations
def get_service_id_from_name(name, user_id):
""" FINISH"""
details = None
conn = None
try:
conn = connect.db_connection()
c = conn.cursor()
c.execute(
"SELECT service_id FROM services WHERE name =%s AND user_id = %s;",
(name, user_id))
conn.commit()
details = c.fetchall()[0]
c.close()
conn.close()
except Exception as error:
print(error)
finally:
if conn is not None:
conn.close()
return details
