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