def return_car(user_id, reservation_id, car_id):
# Updates the cars checked in/out status and sets the return date
query = "UPDATE reservations SET car_returned=2, return_time=%s WHERE (id=%s)"
# Gets the date returned
date_returned = datetime.datetime.now().date()
result = db_conn.query_db(query, [str(date_returned), reservation_id])
# Indicates that the car has been checked in
query = "UPDATE cars SET status=1 WHERE id=%s"
result = db_conn.query_db(query, [car_id])
# If we were able to return the car
if not result:
num_credits = 50 # Hard coded, 50 credits per rental
# Gets the date rented
query = "SELECT for_date FROM reservations WHERE id=%s"
date_rented = db_conn.query_db(query, [reservation_id])[0]['for_date']
# Calculate how many days it has been since they rented the car
num_days_difference = (date_returned - date_rented).days
# If they returned the car more than 1 day late, penalize them
# Else, give the user their 50 credits back
if num_days_difference > 1:
num_credits = -25 * (num_days_difference - 1)
# Updates the user's credits
query = "UPDATE users SET credits=credits+%s WHERE (id=%s)"
result = db_conn.query_db(query, [num_credits, user_id])
return result
def return_car(user_id, reservation_id, car_id):
# Updates the cars checked in/out status and sets the return date
query = "UPDATE reservations SET car_returned=2, return_time=%s WHERE (id=%s)"
# Gets the date returned
date_returned = datetime.datetime.now().date()
result = db_conn.query_db(query, [str(date_returned), reservation_id])
# Indicates that the car has been checked in
query = "UPDATE cars SET status=1 WHERE id=%s"
result = db_conn.query_db(query, [car_id])
# If we were able to return the car
if not result:
num_credits = 50 # Hard coded, 50 credits per rental
# Gets the date rented
query = "SELECT for_date FROM reservations WHERE id=%s"
date_rented = db_conn.query_db(query, [reservation_id])[0]['for_date']
# Calculate how many days it has been since they rented the car
num_days_difference = (date_returned - date_rented).days
# If they returned the car more than 1 day late, penalize them
# Else, give the user their 50 credits back
if num_days_difference > 1:
num_credits = -25 * (num_days_difference - 1)
# Updates the user's credits
query = "UPDATE users SET credits=credits+%s WHERE (id=%s)"
result = db_conn.query_db(query, [num_credits, user_id])
return result
def log_work(car_id, description, worker):
last_driver = get_last_driver(car_id)
query = "UPDATE cars SET status=3 WHERE id=%s"
result = db_conn.query_db(query, [car_id], select=False)
if last_driver is None:
last_driver = 1
query = "INSERT INTO maintenance_log (car, description, last_driver, maintenance_worker) " \
"VALUES (%s, %s, %s, %s)"
result = db_conn.query_db(query, [car_id, description, last_driver, worker], select=False)
return result
def log_work(car_id, description, worker):
last_driver = get_last_driver(car_id)
query = "UPDATE cars SET status=3 WHERE id=%s"
result = db_conn.query_db(query, [car_id], select=False)
if last_driver is None:
last_driver = 1
query = "INSERT INTO maintenance_log (car, description, last_driver, maintenance_worker) " \
"VALUES (%s, %s, %s, %s)"
result = db_conn.query_db(query,
[car_id, description, last_driver, worker],
select=False)
return result
def create_review(review_data, u_id):
query = "SELECT COUNT(id) AS count FROM reservations WHERE for_car=%s AND made_by=%s"
result = db_conn.query_db(query, [review_data["car_id"], u_id], one=True)
if result["count"] == 0:
return "You cannot review a car you never drove!"
query = "INSERT INTO reviews (date_created,num_stars, text, reviewer, car) VALUES " \
"(%s, %s, %s, %s, %s)"
result = db_conn.query_db(query, [
datetime.datetime.now(), review_data["carRating"],
review_data["carReview"], u_id, review_data["car_id"]
],
select=False)
return result
def join_waitlist(user_id):
# Check if the user is already on the waiting list
query = "SELECT id FROM waiting_queue WHERE user=%s"
result = db_conn.query_db(query, [user_id])
# If the user is not on the waitlist, add them
if not result:
query = "INSERT INTO waiting_queue (`user`) VALUES (%s)"
result = db_conn.query_db(query, [user_id])
return True
# Indicates that the user is already on the waitlist
return False
def create_review(review_data, u_id):
query = "SELECT COUNT(id) AS count FROM reservations WHERE for_car=%s AND made_by=%s"
result = db_conn.query_db(query, [review_data["car_id"], u_id], one=True)
if result["count"] == 0:
return "You cannot review a car you never drove!"
query = "INSERT INTO reviews (date_created,num_stars, text, reviewer, car) VALUES " \
"(%s, %s, %s, %s, %s)"
result = db_conn.query_db(query,
[datetime.datetime.now(),
review_data["carRating"],
review_data["carReview"],
u_id, review_data["car_id"]],
select=False)
return result
def join_waitlist(user_id):
# Check if the user is already on the waiting list
query = "SELECT id FROM waiting_queue WHERE user=%s"
result = db_conn.query_db(query, [user_id])
# If the user is not on the waitlist, add them
if not result:
query = "INSERT INTO waiting_queue (`user`) VALUES (%s)"
result = db_conn.query_db(query, [user_id])
return True
# Indicates that the user is already on the waitlist
return False
def check_waitlist(user_id):
# Check if the user is already on the waiting list
query = "SELECT id FROM waiting_queue WHERE user=%s"
result = db_conn.query_db(query, [user_id])
return len(result) > 0
def check_waitlist(user_id):
# Check if the user is already on the waiting list
query = "SELECT id FROM waiting_queue WHERE user=%s"
result = db_conn.query_db(query, [user_id])
return len(result) > 0
def validate_credentials(username, password):
"""
Takes a password and user name, runs the password through a hash and then compares that hash to the stored hash
to determine the authenticity of a user.
:param username: The user_id i.e. email address of the user
:param password: A string representing the entered password
:return: A complete instance of the User class if authenticated, None if not authenticated.
"""
user = db_conn.query_db('SELECT * FROM `users` WHERE `userid`=%s',
[username], True)
#print "Validating: ", username, ":", password
if user is None: # not a valid username
return None
stored_salted_password = user["hpass"]
end_salt_pos = stored_salted_password.find('==') + 2
salt = stored_salted_password[0:end_salt_pos]
# stored_password = stored_salted_password[end_salt_pos:] # this isnt needed I dont think...
if stored_salted_password == encode_password(password, salt):
userid = user["userid"]
return User(userid, user)
''' This is a simple test.
if username == "Xeno" and password == "cars":
userid = "testing"
return User(userid)
'''
return None
def validate_credentials(username, password):
"""
Takes a password and user name, runs the password through a hash and then compares that hash to the stored hash
to determine the authenticity of a user.
:param username: The user_id i.e. email address of the user
:param password: A string representing the entered password
:return: A complete instance of the User class if authenticated, None if not authenticated.
"""
user = db_conn.query_db('SELECT * FROM `xeno`.`users` WHERE `userid`=%s', [username], True)
#print "Validating: ", username, ":", password
if user is None: # not a valid username
return None
stored_salted_password = user["hpass"]
end_salt_pos = stored_salted_password.find('==') + 2
salt = stored_salted_password[0:end_salt_pos]
# stored_password = stored_salted_password[end_salt_pos:] # this isnt needed I dont think...
if stored_salted_password == encode_password(password, salt):
userid = user["userid"]
return User(userid, user)
''' This is a simple test.
if username == "Xeno" and password == "cars":
userid = "testing"
return User(userid)
'''
return None
def get_car_reviews(id):
query = "SELECT reviews.date_created AS date_created, reviews.num_stars AS num_stars, reviews.text AS text, " \
"users.first_name AS fname, users.last_name AS lname " \
"FROM reviews " \
"JOIN users ON reviews.reviewer=users.id " \
"WHERE car=%s " \
"ORDER BY date_created DESC"
result = db_conn.query_db(query, [id])
return result
def get_cars(page, howmany, get_new=False, get_featured=False, search_params=None, isAdmin=False):
"""
This function controls getting details for multiple cars at a time. It should be noted that setting get_new=True
and get_featured=True is the same as get_featured=True, so setting get_new=True is redundant.
Setting get_featured=True and get_new=False is the same as get_featured=True so setting get_new=False is redundant
:param page: An integer representing which section of data wanted
:param howmany: An integer representing how many cars should be retrieved. Used in calculating page data
:param get_new: Boolean that is True if cars should be ordered only by date_added. Default is False
:param get_featured: Boolean that is True if cars should be sorted by featured or not(featured first) and then
by date. Default is False.
:param search_params: A dictionary to be used the search functionality. Either contains 1 key named "general"
or 3 keys named "year", "make", and "model". Additional keys may be added later.
:return: A dictionary of cars, None if no cars found. Keys in the dictionary are: id, year, hp, torque, odo,
acceleration, max_speed, make, model, date_added, is_featured, make_id. Their values are representative of
their data type in the database.
"""
#print "getting cars!"
args = []
offset = 0
query = "SELECT cars.id AS id, cars.year AS year, cars.hp AS hp, cars.torque AS torque, cars.miles_driven AS odo, " \
"cars.acceleration AS acceleration, cars.max_speed AS max_speed, make.description AS make, " \
"model.description AS model, cars.date_added AS date_added, cars.is_featured AS is_featured, " \
"make.id AS make_id " \
"FROM cars " \
"JOIN make ON cars.make=make.id " \
"JOIN model ON cars.model=model.id "
if not isAdmin:
query += "WHERE cars.status<=3 "
else:
query += "WHERE 1=1 "
if search_params is not None:
where_clause, args = searching.create_search_query(search_params)
query += where_clause
# print("get_cars: " + where_clause)
if get_new is False and get_featured is False:
query += "ORDER BY make.id DESC, cars.date_added DESC "
elif get_new is True and get_featured is False:
query += "ORDER BY cars.date_added DESC "
howmany = 8
offset = 0
page = 1
elif get_featured is True:
# query += "WHERE cars.is_featured=1 "
# to force there to be some car that is featured(even if none is marked), do not use WHERE, use ORDER BY
query += "ORDER BY cars.is_featured DESC, cars.date_added DESC "
howmany = 4
offset = 0
page = 1
# query = "SELECT * FROM `xeno`.`cars` ORDER BY `make` ASC"
if howmany > 0:
offset = (page - 1) * howmany
query += "LIMIT " + str(offset) + ", " + str(howmany) # LIMIT offset,row_count
elif howmany == -1:
offset = 0
car_data = db_conn.query_db(query, args)
return car_data
def get_car_reviews(id):
query = "SELECT reviews.date_created AS date_created, reviews.num_stars AS num_stars, reviews.text AS text, " \
"users.first_name AS fname, users.last_name AS lname " \
"FROM reviews " \
"JOIN users ON reviews.reviewer=users.id " \
"WHERE car=%s " \
"ORDER BY date_created DESC"
result = db_conn.query_db(query, [id])
return result
def update_user(self, fields, values):
query = "UPDATE users SET "
for i in range(0, len(fields)):
query += fields[i] + "=%s"
if i < len(fields) - 1:
query += ","
query += " "
query += "WHERE id=%s"
result = db_conn.query_db(query, values + [self.db_id], select=False)
return result
def admin_only_user_update(user_id, fields, values):
query = "UPDATE users SET "
for i in range(0, len(fields)):
query += fields[i] + "=%s"
if i < len(fields) - 1:
query += ","
query += " "
query += "WHERE id=%s"
result = db_conn.query_db(query, values + [user_id], select=False)
return result
def update_user(self, fields, values):
query = "UPDATE users SET "
for i in range(0, len(fields)):
query += fields[i] + "=%s"
if i < len(fields) - 1:
query += ","
query += " "
query += "WHERE id=%s"
result = db_conn.query_db(query, values + [self.db_id], select=False)
return result
def find_model(term):
"""
Attempts to determine if this term is a model in the database.
:param term: A word that may or may not be the model of the car
:return: The id of the row in the database representing this model,
None if no model with description==term can be found.
"""
query = "SELECT id FROM model WHERE description " \
"RLIKE '([A-Za-z0-9]{0,3} )*[A-Za-z0-9]{0,3}%s[A-Za-z0-9]{0,3}( [A-Za-z0-9]{0,3})*'" % (term,)
return db_conn.query_db(query, one=True)
def get_reviews(self):
"""
Retrieves all the reviews a user has created.
:return: Dictionary with keys "date_created", "num_stars", "text", "car" where the value of "car" is an id
corresponding to an entry in the cars table. Returns None if no reviews found.
"""
query = "SELECT `reviews`.`date_created`, `reviews`.`num_stars`, `reviews`.`text`, `reviews`.`car` FROM `reviews` " \
"WHERE `reviews`.`reviewer`=%s"
result = db_conn.query_db(query, [self.db_id])
return result
def admin_only_user_update(user_id, fields, values):
query = "UPDATE users SET "
for i in range(0, len(fields)):
query += fields[i] + "=%s"
if i < len(fields) - 1:
query += ","
query += " "
query += "WHERE id=%s"
result = db_conn.query_db(query, values + [user_id], select=False)
return result
def get_reviews(self):
"""
Retrieves all the reviews a user has created.
:return: Dictionary with keys "date_created", "num_stars", "text", "car" where the value of "car" is an id
corresponding to an entry in the cars table. Returns None if no reviews found.
"""
query = "SELECT `reviews`.`date_created`, `reviews`.`num_stars`, `reviews`.`text`, `reviews`.`car` FROM `reviews` " \
"WHERE `reviews`.`reviewer`=%s"
result = db_conn.query_db(query, [self.db_id])
return result
def get_activity(user_id):
query = "SELECT for_car, for_date FROM reservations WHERE made_by=%s AND car_returned=2 ORDER BY for_date DESC"
rentals = db_conn.query_db(query, [user_id])
query = "SELECT car, date_created FROM reviews WHERE reviewer=%s ORDER BY id DESC"
reviews = db_conn.query_db(query, [user_id])
# Manual Inner Join because the car rentals
# use Dates and the reviews use Datetimes
activity = []
rental_i = 0
reviews_i = 0
for i in range(len(rentals) + len(reviews)):
# If the rental came first, append it
if reviews_i == len(reviews) or (
rental_i != len(rentals) and rentals[rental_i]['for_date'] <
reviews[reviews_i]['date_created'].date()):
activity.append({
'date': rentals[rental_i]['for_date'],
'car': rentals[rental_i]['for_car'],
'type': 'Rented'
})
# Increment the rental index
rental_i = rental_i + 1
else:
# Else, review came first, so add it
activity.append({
'date':
str(reviews[reviews_i]['date_created'].date()),
'car':
reviews[reviews_i]['car'],
'type':
'Reviewed'
})
# Increment the reviews index
reviews_i = reviews_i + 1
return activity
def get_broken_cars():
query = "SELECT cars.id AS id, cars.year AS year, cars.hp AS hp, cars.torque AS torque, cars.miles_driven AS miles, " \
"cars.acceleration AS acceleration, cars.max_speed AS max_speed, make.description AS make, maintenance_log.description AS issue, " \
"model.description AS model, cars.date_added AS date_added, cars.is_featured AS is_featured, " \
"make.id AS make_id " \
"FROM cars " \
"JOIN make ON cars.make=make.id " \
"JOIN model ON cars.model=model.id " \
"JOIN maintenance_log ON cars.id=maintenance_log.car " \
"WHERE cars.status=3"
result = db_conn.query_db(query)
return result
def get_broken_cars():
query = "SELECT cars.id AS id, cars.year AS year, cars.hp AS hp, cars.torque AS torque, cars.miles_driven AS miles, " \
"cars.acceleration AS acceleration, cars.max_speed AS max_speed, make.description AS make, maintenance_log.description AS issue, " \
"model.description AS model, cars.date_added AS date_added, cars.is_featured AS is_featured, " \
"make.id AS make_id " \
"FROM cars " \
"JOIN make ON cars.make=make.id " \
"JOIN model ON cars.model=model.id " \
"JOIN maintenance_log ON cars.id=maintenance_log.car " \
"WHERE cars.status=3"
result = db_conn.query_db(query)
return result
def make_reservation(car_id, text_date, user):
credits_required = 50 # hard coded
# check if user has enough credits
query = "SELECT credits FROM users WHERE id=%s"
result = db_conn.query_db(query, [user.db_id], one=True)
num_credits = 0
if result is not None:
num_credits = result["credits"]
if num_credits < credits_required:
return "You do not have enough credits."
# month, day, year
date_list = text_date.split("/")
# current_time = datetime.datetime.now() - datetime.timedelta(hours=5)
# if current_time > datetime.datetime(int(date_list[2]), int(date_list[0]), int(date_list[1])):
current_time = time.strftime("%Y-%m-%d")
requested_time = date_list[2] + "-" + date_list[0] + "-" + date_list[1]
# 2015-05-03 < 2015-05-04
if requested_time < current_time:
return "Invalid date. Reservation must be today, or in the future."
# check if car is already reserved or if user already has a car reserved that day
query = "SELECT COUNT(id) AS count FROM reservations WHERE (for_car=%s OR made_by=%s) AND for_date=%s AND car_returned <> 2"
sql_date = date_list[2] + "-" + date_list[0] + "-" + date_list[1]
result = db_conn.query_db(query, [car_id, user.db_id, sql_date], one=True)
if result is None or result["count"] == 0:
# can make reservation
query = "INSERT INTO reservations (made_by, for_car, for_date) VALUES (%s, %s, %s)"
args = [user.db_id, int(car_id), sql_date]
result = db_conn.query_db(query, args, select=False)
user.update_user(["credits"], [user.credits-50])
else:
return "You may not reserve this car today."
return True
def make_reservation(car_id, text_date, user):
credits_required = 50 # hard coded
# check if user has enough credits
query = "SELECT credits FROM users WHERE id=%s"
result = db_conn.query_db(query, [user.db_id], one=True)
num_credits = 0
if result is not None:
num_credits = result["credits"]
if num_credits < credits_required:
return "You do not have enough credits."
# month, day, year
date_list = text_date.split("/")
# current_time = datetime.datetime.now() - datetime.timedelta(hours=5)
# if current_time > datetime.datetime(int(date_list[2]), int(date_list[0]), int(date_list[1])):
current_time = time.strftime("%Y-%m-%d")
requested_time = date_list[2] + "-" + date_list[0] + "-" + date_list[1]
# 2015-05-03 < 2015-05-04
if requested_time < current_time:
return "Invalid date. Reservation must be today, or in the future."
# check if car is already reserved or if user already has a car reserved that day
query = "SELECT COUNT(id) AS count FROM reservations WHERE (for_car=%s OR made_by=%s) AND for_date=%s AND car_returned <> 2"
sql_date = date_list[2] + "-" + date_list[0] + "-" + date_list[1]
result = db_conn.query_db(query, [car_id, user.db_id, sql_date], one=True)
if result is None or result["count"] == 0:
# can make reservation
query = "INSERT INTO reservations (made_by, for_car, for_date) VALUES (%s, %s, %s)"
args = [user.db_id, int(car_id), sql_date]
result = db_conn.query_db(query, args, select=False)
user.update_user(["credits"], [user.credits - 50])
else:
return "You may not reserve this car today."
return True
def claim_car(user_id, car_id):
# Does not include the redundant check to see if this user
# has this car checked out because we already get all of
# the reservations for this user on this day in /dashboard
# Indicates that the car has been checked out
query = "UPDATE cars SET status=2 WHERE id=%s"
result = db_conn.query_db(query, [car_id])
print "Claim result: ", result
return result
def claim_car(user_id, car_id):
# Does not include the redundant check to see if this user
# has this car checked out because we already get all of
# the reservations for this user on this day in /dashboard
# Indicates that the car has been checked out
query = "UPDATE cars SET status=2 WHERE id=%s"
result = db_conn.query_db(query, [car_id])
print "Claim result: ", result
return result
def get_activity(user_id):
query = "SELECT for_car, for_date FROM reservations WHERE made_by=%s AND car_returned=2 ORDER BY for_date DESC"
rentals = db_conn.query_db(query, [user_id])
query = "SELECT car, date_created FROM reviews WHERE reviewer=%s ORDER BY id DESC"
reviews = db_conn.query_db(query, [user_id])
# Manual Inner Join because the car rentals
# use Dates and the reviews use Datetimes
activity = []
rental_i = 0
reviews_i = 0
for i in range(len(rentals) + len(reviews)):
# If the rental came first, append it
if reviews_i == len(reviews) or (rental_i != len(rentals) and rentals[rental_i]['for_date'] < reviews[reviews_i]['date_created'].date()):
activity.append({
'date':rentals[rental_i]['for_date'],
'car':rentals[rental_i]['for_car'],
'type':'Rented'})
# Increment the rental index
rental_i = rental_i + 1
else:
# Else, review came first, so add it
activity.append({
'date':str(reviews[reviews_i]['date_created'].date()),
'car':reviews[reviews_i]['car'],
'type':'Reviewed'})
# Increment the reviews index
reviews_i = reviews_i + 1
return activity
def get_all_users():
query = "SELECT id, CONCAT(first_name, ' ', last_name) AS name, userid, acct_type, suspended_until FROM users " \
"ORDER BY date_joined DESC, suspended_until DESC"
result = db_conn.query_db(query)
for user in result:
now = datetime.datetime.now()
if user["suspended_until"] is not None and user["suspended_until"] >= datetime.datetime(3000, 1, 1):
user["banned"] = True
else:
user["banned"] = False
if user["suspended_until"] is not None and now < user["suspended_until"]:
user["suspended"] = True
else:
user["suspended"] = False
return result
def get_reserved_dates(id):
# Reserved dates are where the car is not returned (0) or returned
# but not ready for rental (1)
query = "SELECT for_date FROM reservations WHERE for_car=%s AND (car_returned <> 2)"
result = db_conn.query_db(query, [id])
dates = []
for date in result:
# starts as year-month-day
# needs to be day-month-year
date = str(date["for_date"]).split("-")
temp = str(int(date[2]))
date[2] = str(int(date[0]))
date[0] = temp
date[1] = str(int(date[1]))
dates.append("-".join(date))
return dates
def get_reserved_dates(id):
# Reserved dates are where the car is not returned (0) or returned
# but not ready for rental (1)
query = "SELECT for_date FROM reservations WHERE for_car=%s AND (car_returned <> 2)"
result = db_conn.query_db(query, [id])
dates = []
for date in result:
# starts as year-month-day
# needs to be day-month-year
date = str(date["for_date"]).split("-")
temp = str(int(date[2]))
date[2] = str(int(date[0]))
date[0] = temp
date[1] = str(int(date[1]))
dates.append("-".join(date))
return dates
def init_user(u, user_id):
"""
Gets a user's information from the database and fills in a User object.
:param u: Instance of User class to fill
:param user_id: The user's u_id, a.k.a. email address
:return: A filled User object
"""
udata = db_conn.query_db(
'SELECT * FROM `xeno`.`users` WHERE `userid`=%s', [user_id],
one=True)
if udata is not None:
u.db_id = udata["id"]
u.fname = udata["first_name"]
u.lname = udata["last_name"]
u.date_joined = udata["date_joined"]
u.credits = udata["credits"]
u.acct_type = udata["acct_type"]
# http://stackoverflow.com/questions/14291636/what-is-the-proper-way-to-convert-between-mysql-datetime-and-python-timestamp
# time_format = '%Y-%m-%d %H:%M:%S'
u.suspended_til = udata[
"suspended_until"] # .strftime(time_format)
now = datetime.datetime.now()
if udata["suspended_until"] is not None and now < udata[
"suspended_until"]:
u.suspended = True
else:
u.suspended = False
if udata["suspended_until"] is not None and udata[
"suspended_until"] >= datetime.datetime(3000, 1, 1):
u.banned = True
else:
u.banned = False
u.id = user_id
u.active = True
u.authenticated = True
u.anonymous = True
u.exists = True
else:
u.active = False
u.authenticated = False
u.anonymous = False
u.exists = False
u.id = None
return u
def get_single_car(id, isAdmin):
query = "SELECT cars.id AS id, cars.year AS year, cars.hp AS hp, cars.torque AS torque, cars.miles_driven AS odo, " \
"cars.acceleration AS acceleration, cars.max_speed AS max_speed, make.description AS make, " \
"model.description AS model, cars.date_added AS date_added, cars.is_featured AS is_featured, " \
"make.id AS make_id, car_type.description AS ctype, country.description AS country, " \
"cars.status AS status " \
"FROM cars " \
"JOIN make ON cars.make=make.id " \
"JOIN model ON cars.model=model.id " \
"JOIN car_type ON cars.type=car_type.id " \
"JOIN country ON cars.country=country.id "
query += "WHERE cars.id=%s "
if not isAdmin:
query += "AND cars.status<=3 "
query += "LIMIT 1"
car_data = db_conn.query_db(query, [id], one=True)
return car_data
def get_single_car(id, isAdmin):
query = "SELECT cars.id AS id, cars.year AS year, cars.hp AS hp, cars.torque AS torque, cars.miles_driven AS odo, " \
"cars.acceleration AS acceleration, cars.max_speed AS max_speed, make.description AS make, " \
"model.description AS model, cars.date_added AS date_added, cars.is_featured AS is_featured, " \
"make.id AS make_id, car_type.description AS ctype, country.description AS country, " \
"cars.status AS status " \
"FROM cars " \
"JOIN make ON cars.make=make.id " \
"JOIN model ON cars.model=model.id " \
"JOIN car_type ON cars.type=car_type.id " \
"JOIN country ON cars.country=country.id "
query += "WHERE cars.id=%s "
if not isAdmin:
query += "AND cars.status<=3 "
query += "LIMIT 1"
car_data = db_conn.query_db(query, [id], one=True)
return car_data
def get_all_users():
query = "SELECT id, CONCAT(first_name, ' ', last_name) AS name, userid, acct_type, suspended_until FROM users " \
"ORDER BY date_joined DESC, suspended_until DESC"
result = db_conn.query_db(query)
for user in result:
now = datetime.datetime.now()
if user["suspended_until"] is not None and user[
"suspended_until"] >= datetime.datetime(3000, 1, 1):
user["banned"] = True
else:
user["banned"] = False
if user["suspended_until"] is not None and now < user[
"suspended_until"]:
user["suspended"] = True
else:
user["suspended"] = False
return result
def init_user(u, user_id):
"""
Gets a user's information from the database and fills in a User object.
:param u: Instance of User class to fill
:param user_id: The user's u_id, a.k.a. email address
:return: A filled User object
"""
udata = db_conn.query_db('SELECT * FROM `xeno`.`users` WHERE `userid`=%s', [user_id], one=True)
if udata is not None:
u.db_id = udata["id"]
u.fname = udata["first_name"]
u.lname = udata["last_name"]
u.date_joined = udata["date_joined"]
u.credits = udata["credits"]
u.acct_type = udata["acct_type"]
# http://stackoverflow.com/questions/14291636/what-is-the-proper-way-to-convert-between-mysql-datetime-and-python-timestamp
# time_format = '%Y-%m-%d %H:%M:%S'
u.suspended_til = udata["suspended_until"] # .strftime(time_format)
now = datetime.datetime.now()
if udata["suspended_until"] is not None and now < udata["suspended_until"]:
u.suspended = True
else:
u.suspended = False
if udata["suspended_until"] is not None and udata["suspended_until"] >= datetime.datetime(3000, 1, 1):
u.banned = True
else:
u.banned = False
u.id = user_id
u.active = True
u.authenticated = True
u.anonymous = True
u.exists = True
else:
u.active = False
u.authenticated = False
u.anonymous = False
u.exists = False
u.id = None
return u
def create_new_user(user_data, acct_type=3):
"""
Adds user data to the database if it can.
:param user_data: A dictionary that MUST have keys "full_name", "password", and "email"
:param acct_type: Value specifying type of account. 1=administrator 2=maintenance 3=regular user. Default is 3.
:return: A dictionary representing database fields for this user. None if an error occurred INSERTING into the
database.
"""
first_name_space = user_data["full_name"].find(" ")
fname = user_data["full_name"][0:first_name_space]
lname = user_data["full_name"][first_name_space + 1:]
hpass = encode_password(user_data["password"])
user_id = user_data["email"]
query = "INSERT INTO `users` " \
"(`first_name`, `last_name`, `acct_type`, `userid`, `hpass`)" \
"VALUES (%s, %s, 100, %s, %s, %s)"
args = [fname, lname, acct_type, user_id, hpass]
#print query, args
result = db_conn.query_db(query, args, select=False)
#print "Attempted create user: ", result
return result
def create_new_user(user_data, acct_type=3):
"""
Adds user data to the database if it can.
:param user_data: A dictionary that MUST have keys "full_name", "password", and "email"
:param acct_type: Value specifying type of account. 1=administrator 2=maintenance 3=regular user. Default is 3.
:return: A dictionary representing database fields for this user. None if an error occurred INSERTING into the
database.
"""
first_name_space = user_data["full_name"].find(" ")
fname = user_data["full_name"][0:first_name_space]
lname = user_data["full_name"][first_name_space + 1:]
hpass = encode_password(user_data["password"])
user_id = user_data["email"]
query = "INSERT INTO `xeno`.`users` " \
"(`first_name`, `last_name`, `credits`, `acct_type`, `userid`, `hpass`)" \
"VALUES (%s, %s, 100, %s, %s, %s)"
args = [fname, lname, acct_type, user_id, hpass]
#print query, args
result = db_conn.query_db(query, args, select=False)
#print "Attempted create user: ", result
return result
def get_favorite_car(self):
"""
Attempts to determine this user's most rented car. If there is a tie, it will return the most recently rented.
:return: a dictionary containing details about a singular car. None if no car is found.
"""
subquery = "SELECT `reservations`.`for_car` AS car_id, COUNT(*) AS num_rentals FROM `reservations` " \
"WHERE `reservations`.`made_by`='%s' GROUP BY `reservations`.`for_car` " \
"ORDER BY `reservations`.`for_date`"
max_query = "SELECT car_id, MAX(num_rentals) FROM (" + subquery + ") AS sub"
car_query = "SELECT `cars`.`id` FROM (" + max_query + ") AS max JOIN `cars` WHERE `cars`.`id=cars_id"
query = "SELECT cars.id AS id, cars.year AS year, cars.hp AS hp, cars.torque AS torque, cars.miles_driven AS odo, " \
"cars.acceleration AS acceleration, cars.max_speed AS max_speed, make.description AS make, " \
"model.description AS model, cars.date_added AS date_added, cars.is_featured AS is_featured, " \
"make.id " \
" FROM (" + max_query + ") AS final " \
"JOIN `cars` ON `cars`.`id`=car_id " \
"JOIN make ON cars.make=make.id " \
"JOIN model ON cars.model=model.id "
result = db_conn.query_db(query, [self.db_id], one=True)
return result
def get_favorite_car(self):
"""
Attempts to determine this user's most rented car. If there is a tie, it will return the most recently rented.
:return: a dictionary containing details about a singular car. None if no car is found.
"""
subquery = "SELECT `reservations`.`for_car` AS car_id, COUNT(*) AS num_rentals FROM `reservations` " \
"WHERE `reservations`.`made_by`='%s' GROUP BY `reservations`.`for_car` " \
"ORDER BY `reservations`.`for_date`"
max_query = "SELECT car_id, MAX(num_rentals) FROM (" + subquery + ") AS sub"
car_query = "SELECT `cars`.`id` FROM (" + max_query + ") AS max JOIN `cars` WHERE `cars`.`id=cars_id"
query = "SELECT cars.id AS id, cars.year AS year, cars.hp AS hp, cars.torque AS torque, cars.miles_driven AS odo, " \
"cars.acceleration AS acceleration, cars.max_speed AS max_speed, make.description AS make, " \
"model.description AS model, cars.date_added AS date_added, cars.is_featured AS is_featured, " \
"make.id " \
" FROM (" + max_query + ") AS final " \
"JOIN `cars` ON `cars`.`id`=car_id " \
"JOIN make ON cars.make=make.id " \
"JOIN model ON cars.model=model.id "
result = db_conn.query_db(query, [self.db_id], one=True)
return result
def check_avail_cars(sc):
with app.app_context():
print "Checking for available cars"
#Get list of cars where status is available for checkout (this is just for safety)
query_check_available_cars = ("SELECT id FROM cars WHERE status = 1")
cur.execute(query_check_available_cars)
list_of_available_cars = cur.fetchall()
TEXT = ""
for car in list_of_available_cars:
#Get the human readable info for cars (make, model)
query_get_car_name = (
"SELECT make, model, year FROM cars WHERE id = '%s'") % (str(
car[0]))
cur.execute(query_get_car_name)
car_make_and_model_year = cur.fetchall()
print "car: ", car_make_and_model_year
car_year = car_make_and_model_year[0][2]
car_make_id = car_make_and_model_year[0][0]
query_get_car_make = (
"SELECT description FROM make WHERE id = '%s'") % (
str(car_make_id))
cur.execute(query_get_car_make)
car_make = cur.fetchone()[0]
car_model_id = car_make_and_model_year[0][1]
query_get_car_model = (
"SELECT description FROM model WHERE id = '%s'") % (
str(car_model_id))
cur.execute(query_get_car_model)
car_model = cur.fetchone()[0]
#Get list of cars that are checked out/reserved today
query_check_car_reservation = (
"SELECT id FROM reservations WHERE for_car = '%s' AND for_date = '%s' AND car_returned='0'"
) % (str(car[0]), str(datetime.date.today()))
cur.execute(query_check_car_reservation)
reserved_car_list = cur.fetchall()
if (reserved_car_list):
print str(car_make) + " " + str(
car_model) + " is reserved today."
else:
TEXT = TEXT + str(car_year) + " " + str(car_make) + " " + str(
car_model) + " is available, reserve now!\n"
TEXT = TEXT + "http://xenocars.me"
print TEXT
SUBJECT = TEXT
message = "Subject: %s\n\n%s" % (SUBJECT, TEXT)
server = smtplib.SMTP("smtp.gmail.com:587")
server.starttls()
server.login(username, password)
#Get top # of user_id's in waiting_queue (where # = # of cars available)
query_get_user_email = ("SELECT user FROM waiting_queue LIMIT %s") % (
str(len(list_of_available_cars)))
cur.execute(query_get_user_email)
top_users_in_queue = cur.fetchall()
#send each user an email
for user in top_users_in_queue:
print "User: "******"SELECT userid FROM users WHERE id = '%s'") % (str(user[0]))
cur.execute(query_get_user_email)
user_email = cur.fetchone()[0]
server.sendmail(FROM, [user_email], message)
#remove user from queue
# query_remove_user_from_queue = ("DELETE FROM waiting_queue WHERE user=%s") % (str(user[0]))
# cur.execute(query_remove_user_from_queue)
# Remove user from queue
query = "DELETE FROM waiting_queue where user=%s"
result = db_conn.query_db(query, [user[0]])
print "Removed user ", user[0], "from queue."
server.quit()
#run this function every 3600 seconds (60 minutes)
sc.enter(3600, 1, check_avail_cars, (sc, ))
def get_last_driver(car_id):
query = "SELECT made_by FROM reservations WHERE for_car=%s AND for_date<CURRENT_DATE() " \
"ORDER BY for_date DESC LIMIT 1"
driver = db_conn.query_db(query, [car_id], one=True)
def get_reserved_car(user_id):
query = "SELECT * FROM reservations WHERE (made_by=%s) AND (for_date<=%s) AND (car_returned=0)"
current_date = time.strftime("%Y-%m-%d")
result = db_conn.query_db(query, [user_id, current_date])
return result
def update_car_status(car_id, car_status):
query = "UPDATE cars SET status=%s WHERE id=%s"
result = db_conn.query_db(query, [car_status, car_id], select=False)
return result
def hello():
import database_connection as db_conn
query = "select name from sqlite_master where type=\'table\'"
result = db_conn.query_db(query, [])
print result
return "Hello World!<br/>" + certificate_utils.get_user_certificate_str()
def leave_waitlist(user_id):
# Removes the user from the waiting list
query = "DELETE FROM waiting_queue where user=%s"
result = db_conn.query_db(query, [user_id])
return result
def get_last_driver(car_id):
query = "SELECT made_by FROM reservations WHERE for_car=%s AND for_date<CURRENT_DATE() " \
"ORDER BY for_date DESC LIMIT 1"
driver = db_conn.query_db(query, [car_id], one=True)
def add_new_car(cdata, current_user):
"""
Attempts to insert a new car into the database by first querying to see if things such as the make, model, country,
and type already have entries in their respective tables, creating them if need be, and then using those ids to
populate the cars table with related fields and details specific to the car.
:param cdata: A dictionary representing data from the form. Keys should be: make, model, ctype, country, year, hp,
torque, odo, acceleration, max_speed, and is_featured. Data type should be clear from the form.
:param current_user: The user currently logged into the site. Should be an instance of type User
:return: The id of the car just added, or None if error occurred.
"""
query = "SELECT id FROM make WHERE description=%s"
make_id = db_conn.query_db(query, [cdata["make"].lower()], one=True)
if make_id is None:
query = "INSERT INTO make (`description`) VALUES (%s)"
make_id = db_conn.query_db(query, [cdata["make"]], select=False)
#print "Make id" + str(make_id)
else:
make_id = int(make_id["id"])
#print "Make id already there as " + str(make_id)
query = "SELECT id FROM model WHERE description=%s"
model_id = db_conn.query_db(query, [cdata["model"].lower()], one=True)
if model_id is None:
query = "INSERT INTO model (`description`) VALUES (%s)"
model_id = db_conn.query_db(query, [cdata["model"]], select=False)
#print "Model id" + str(model_id)
else:
model_id = int(model_id["id"])
#print "Model id already there as " + str(model_id)
query = "SELECT id FROM car_type WHERE description=%s"
car_type = db_conn.query_db(query, [cdata["ctype"].lower()], one=True)
if car_type is None:
query = "INSERT INTO car_type (`description`) VALUES (%s)"
car_type = db_conn.query_db(query, [cdata["ctype"]], select=False)
#print "Car Type id" + str(car_type)
else:
car_type = int(car_type["id"])
#print "Car Type already there as " + str(car_type)
query = "SELECT id FROM country WHERE description=%s"
country_id = db_conn.query_db(query, [cdata["country"].lower()], one=True)
if country_id is None:
query = "INSERT INTO country (`description`) VALUES (%s)"
country_id = db_conn.query_db(query, [cdata["country"]], select=False)
#print "Country id" + str(country_id)
else:
country_id = int(country_id["id"])
#print "Country id already there as " + str(country_id)
is_featured = 0
if cdata["is_featured"] == "on":
is_featured = 1
query = "INSERT INTO cars (make, model, year, country, type, hp, torque, miles_driven, acceleration, max_speed, added_by, is_featured)" \
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
result = False
try:
result = db_conn.query_db(query, [make_id, model_id, int(cdata["year"]), country_id, car_type,
int(cdata["hp"]), int(cdata["torque"]), int(cdata["odo"]),
float(cdata["acceleration"]), int(cdata["max_speed"]), current_user.db_id, is_featured],
select=False)
except Exception, e:
result = False
print repr(e)
def update_car_status(car_id, car_status):
query = "UPDATE cars SET status=%s WHERE id=%s"
result = db_conn.query_db(query, [car_status, car_id], select=False)
return result
def hello():
import database_connection as db_conn
query = "select name from sqlite_master where type=\'table\'"
result = db_conn.query_db(query, [])
print result
return "Hello World!<br/>" + certificate_utils.get_user_certificate_str()
def get_cars(page,
howmany,
get_new=False,
get_featured=False,
search_params=None,
isAdmin=False):
"""
This function controls getting details for multiple cars at a time. It should be noted that setting get_new=True
and get_featured=True is the same as get_featured=True, so setting get_new=True is redundant.
Setting get_featured=True and get_new=False is the same as get_featured=True so setting get_new=False is redundant
:param page: An integer representing which section of data wanted
:param howmany: An integer representing how many cars should be retrieved. Used in calculating page data
:param get_new: Boolean that is True if cars should be ordered only by date_added. Default is False
:param get_featured: Boolean that is True if cars should be sorted by featured or not(featured first) and then
by date. Default is False.
:param search_params: A dictionary to be used the search functionality. Either contains 1 key named "general"
or 3 keys named "year", "make", and "model". Additional keys may be added later.
:return: A dictionary of cars, None if no cars found. Keys in the dictionary are: id, year, hp, torque, odo,
acceleration, max_speed, make, model, date_added, is_featured, make_id. Their values are representative of
their data type in the database.
"""
#print "getting cars!"
args = []
offset = 0
query = "SELECT cars.id AS id, cars.year AS year, cars.hp AS hp, cars.torque AS torque, cars.miles_driven AS odo, " \
"cars.acceleration AS acceleration, cars.max_speed AS max_speed, make.description AS make, " \
"model.description AS model, cars.date_added AS date_added, cars.is_featured AS is_featured, " \
"make.id AS make_id " \
"FROM cars " \
"JOIN make ON cars.make=make.id " \
"JOIN model ON cars.model=model.id "
if not isAdmin:
query += "WHERE cars.status<=3 "
else:
query += "WHERE 1=1 "
if search_params is not None:
where_clause, args = searching.create_search_query(search_params)
query += where_clause
# print("get_cars: " + where_clause)
if get_new is False and get_featured is False:
query += "ORDER BY make.id DESC, cars.date_added DESC "
elif get_new is True and get_featured is False:
query += "ORDER BY cars.date_added DESC "
howmany = 8
offset = 0
page = 1
elif get_featured is True:
# query += "WHERE cars.is_featured=1 "
# to force there to be some car that is featured(even if none is marked), do not use WHERE, use ORDER BY
query += "ORDER BY cars.is_featured DESC, cars.date_added DESC "
howmany = 4
offset = 0
page = 1
# query = "SELECT * FROM `xeno`.`cars` ORDER BY `make` ASC"
if howmany > 0:
offset = (page - 1) * howmany
query += "LIMIT " + str(offset) + ", " + str(
howmany) # LIMIT offset,row_count
elif howmany == -1:
offset = 0
car_data = db_conn.query_db(query, args)
return car_data
def add_new_car(cdata, current_user):
"""
Attempts to insert a new car into the database by first querying to see if things such as the make, model, country,
and type already have entries in their respective tables, creating them if need be, and then using those ids to
populate the cars table with related fields and details specific to the car.
:param cdata: A dictionary representing data from the form. Keys should be: make, model, ctype, country, year, hp,
torque, odo, acceleration, max_speed, and is_featured. Data type should be clear from the form.
:param current_user: The user currently logged into the site. Should be an instance of type User
:return: The id of the car just added, or None if error occurred.
"""
query = "SELECT id FROM make WHERE description=%s"
make_id = db_conn.query_db(query, [cdata["make"].lower()], one=True)
if make_id is None:
query = "INSERT INTO make (`description`) VALUES (%s)"
make_id = db_conn.query_db(query, [cdata["make"]], select=False)
#print "Make id" + str(make_id)
else:
make_id = int(make_id["id"])
#print "Make id already there as " + str(make_id)
query = "SELECT id FROM model WHERE description=%s"
model_id = db_conn.query_db(query, [cdata["model"].lower()], one=True)
if model_id is None:
query = "INSERT INTO model (`description`) VALUES (%s)"
model_id = db_conn.query_db(query, [cdata["model"]], select=False)
#print "Model id" + str(model_id)
else:
model_id = int(model_id["id"])
#print "Model id already there as " + str(model_id)
query = "SELECT id FROM car_type WHERE description=%s"
car_type = db_conn.query_db(query, [cdata["ctype"].lower()], one=True)
if car_type is None:
query = "INSERT INTO car_type (`description`) VALUES (%s)"
car_type = db_conn.query_db(query, [cdata["ctype"]], select=False)
#print "Car Type id" + str(car_type)
else:
car_type = int(car_type["id"])
#print "Car Type already there as " + str(car_type)
query = "SELECT id FROM country WHERE description=%s"
country_id = db_conn.query_db(query, [cdata["country"].lower()], one=True)
if country_id is None:
query = "INSERT INTO country (`description`) VALUES (%s)"
country_id = db_conn.query_db(query, [cdata["country"]], select=False)
#print "Country id" + str(country_id)
else:
country_id = int(country_id["id"])
#print "Country id already there as " + str(country_id)
is_featured = 0
if cdata["is_featured"] == "on":
is_featured = 1
query = "INSERT INTO cars (make, model, year, country, type, hp, torque, miles_driven, acceleration, max_speed, added_by, is_featured)" \
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
result = False
try:
result = db_conn.query_db(query, [
make_id, model_id,
int(cdata["year"]), country_id, car_type,
int(cdata["hp"]),
int(cdata["torque"]),
int(cdata["odo"]),
float(cdata["acceleration"]),
int(cdata["max_speed"]), current_user.db_id, is_featured
],
select=False)
except Exception, e:
result = False
print repr(e)
def leave_waitlist(user_id):
# Removes the user from the waiting list
query = "DELETE FROM waiting_queue where user=%s"
result = db_conn.query_db(query, [user_id])
return result
def get_reserved_car(user_id):
query = "SELECT * FROM reservations WHERE (made_by=%s) AND (for_date<=%s) AND (car_returned=0)"
current_date = time.strftime("%Y-%m-%d")
result = db_conn.query_db(query, [user_id, current_date])
return result