def lost_account():
cursor, conn = connect()
email = request.form.get("email")
check_existing_users_stmt = "SELECT user_id FROM users WHERE email=%s"
cursor.execute(check_existing_users_stmt, (email))
result = cursor.fetchall()
if len(result) is not 1:
return error_with_message("user does not exist")
user_id = result[0][0]
create_recovery_stmt = "INSERT INTO password_recovery (user_id, recovery_token) VALUES (%s, %s)"
recovery_token = ''.join(
random.choice(string.ascii_letters + string.digits) for _ in range(32))
cursor.execute(create_recovery_stmt, (user_id, recovery_token))
if cursor.rowcount is not 1:
return error_with_message("account recovery failed")
conn.commit()
# Send confirmation email
FROM = "*****@*****.**"
TO = [email]
SUBJECT = "Recover your StudyBuddy Account"
MSG = (
"Hello " + name +
",\nYou have requested to recovery your account. Please change your password by visiting this link: "
+ "http://34.214.169.181:5000/account_recovery?recovery_token=" +
recovery_token + "\n" + "\nThank you,\nThe StudyBuddies Team")
message = 'Subject: {}\n\n{}'.format(SUBJECT, MSG)
server = smtplib.SMTP('localhost')
server.sendmail(FROM, TO, message)
server.quit()
print(message)
return success_with_data({"confirmation_token": recovery_token})
def list_user_classes(**kwargs):
user_id = kwargs["user_id"]
user_classes_stmt = """SELECT
classes.id, course_title, course_name
FROM classes LEFT JOIN user_classes ON
classes.id = user_classes.class_id
WHERE user_classes.user_id = %s"""
cursor, conn = connect()
cursor.execute(user_classes_stmt, (user_id, ))
classes = cursor.fetchall()
resultsDict = []
for the_class in classes:
groups = []
get_group_stmt = """SELECT id, leader_id, start_time, end_time,
category, description, location_lat, location_lon, location_description,
chat_id FROM groups WHERE class_id=%s AND end_time > CURRENT_TIMESTAMP"""
cursor.execute(get_group_stmt, (the_class[0], ))
group_list = cursor.fetchall()
for the_group in group_list:
group_id = the_group[0]
leader_id = the_group[1]
start_time = the_group[2]
end_time = the_group[3]
category = the_group[4]
description = the_group[5]
location_lat = the_group[6]
location_lon = the_group[7]
location_description = the_group[8]
chat_id = the_group[9]
get_size_stmt = "SELECT COUNT(*) FROM users WHERE group_id=%s"
cursor.execute(get_size_stmt, (group_id, ))
size = cursor.fetchone()[0]
group_result = {
"id": group_id,
"leader_id": leader_id,
"start_time": str(start_time),
"end_time": str(end_time),
"category": category,
"description": description,
"location_lat": location_lat,
"location_lon": location_lon,
"location_description": location_description,
"chat_id": chat_id,
"size": size
}
groups.append(group_result)
id = the_class[0]
course_title = the_class[1]
course_name = the_class[2]
result = {
"id": id,
"course_title": course_title,
"course_name": course_name,
"active_groups": groups
}
resultsDict.append(result)
return success_with_data({"classes": resultsDict})
def create_group(**kwargs):
userID = kwargs["user_id"]
cursor, conn = connect()
class_id = request.form.get("class_id")
end_time = request.form.get("end_time")
category = request.form.get("category")
description = request.form.get("description")
location_lat = request.form.get("location_lat")
location_lon = request.form.get("location_lon")
location_des = request.form.get("location_description")
check_user_group_id_stmt = "SELECT group_id FROM users WHERE id=%s"
cursor.execute(check_user_group_id_stmt, (userID, ))
results = cursor.fetchall()
if results[0][0] == 1:
return error_with_message("User already in group")
create_group_stmt = "INSERT INTO groups (class_id, leader_id, end_time, category, description, location_lat, location_lon, location_description) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
cursor.execute(create_group_stmt,
(class_id, userID, end_time, category, description,
location_lat, location_lon, location_des))
if cursor.rowcount is not 1:
return error_with_message("msg_creating_group_failed")
group_id = cursor.lastrowid
update_user_group_id_stmt = "UPDATE users SET group_id=%s WHERE id=%s"
cursor.execute(update_user_group_id_stmt, (group_id, userID))
conn.commit()
return success_with_data({"group_id": group_id})
def delete_user(**kwargs):
cursor, conn = connect()
password = request.form.get("password")
user_id = kwargs["user_id"]
del_user_stmt = "DELETE from users where id=%s AND password=%s"
cursor.execute(del_user_stmt, (user_id, password))
if cursor.rowcount is not 1:
return error_with_message("user does not exist")
del_user_class_stmt = "DELETE from user_classes where user_id=%s"
cursor.execute(del_user_class_stmt, (user_id, ))
del_user_sess_stmt = "DELETE from sessions where user_id=%s"
cursor.execute(del_user_sess_stmt, (user_id, ))
find_group_stmt = "SELECT id FROM groups WHERE leader_id=%s"
cursor.execute(find_group_stmt, (user_id, ))
results = cursor.fetchall()
if len(results) != 0:
group_id = results[0][0]
find_member_stmt = "SELECT id FROM users WHERE group_id=%s"
cursor.execute(find_member_stmt, (group_id, ))
results = cursor.fetchall()
if len(results) == 0:
del_group_stmt = "DELETE from groups where leader_id=%s"
cursor.execute(del_group_stmt, (user_id, ))
else:
newlead = results[0][0]
change_leader_stmt = "UPDATE groups SET leader_id=%s where leader_id=%s"
cursor.execute(change_leader_stmt, (newlead, user_id))
conn.commit()
return success_with_data({})
def logout():
cursor, conn = connect()
token = request.form.get("session_token")
logout_stmt = "DELETE FROM sessions where token=%s"
cursor.execute(logout_stmt, (token, ))
if cursor.rowcount is not 1:
return error_with_message("session_does_not_exist")
conn.commit()
return success_with_data({})
def create_user():
cursor, conn = connect()
email = request.form.get("email")
password = request.form.get("password")
name = request.form.get("name")
class_year = request.form.get("class_year")
split_email = email.split("@")
school = split_email[1]
find_school_stmt = "SELECT id FROM schools where email=%s"
cursor.execute(find_school_stmt, (school, ))
result = cursor.fetchall()
if len(result) == 0:
return error_with_message("must use school email")
school_id = result[0][0]
check_existing_users_stmt = "SELECT COUNT(*) FROM users WHERE email=%s"
cursor.execute(check_existing_users_stmt, (email, ))
count = cursor.fetchone()[0]
if count is not 0:
return error_with_message("user already exists")
salt = ''.join(
random.choice(string.ascii_letters + string.digits) for _ in range(32))
h = bcrypt_sha256.hash(password + salt)
create_user_stmt = "INSERT INTO users (email, password, name, class_year, school_id, salt) VALUES (%s, %s, %s, %s, %s)"
cursor.execute(create_user_stmt,
(email, h, name, class_year, school_id, salt))
if cursor.rowcount is not 1:
return error_with_message("creating user failed")
conn.commit()
user_id = cursor.lastrowid
confirmation_token = ''.join(
random.choice(string.ascii_letters + string.digits) for _ in range(32))
create_confirmation_stmt = "INSERT INTO email_confirmations (user_id, token) VALUES (%s, %s)"
cursor.execute(create_confirmation_stmt, (user_id, confirmation_token))
conn.commit()
# Send confirmation email
FROM = "*****@*****.**"
TO = [email]
SUBJECT = "Confirm your StudyBuddy Account"
MSG = ("Hello " + name +
",\nPlease confirm your account by visiting this link: " +
"http://34.214.169.181:5000/confirm_email/" + confirmation_token +
"\n" + "\nThank you,\nThe StudyBuddies Team")
message = 'Subject: {}\n\n{}'.format(SUBJECT, MSG)
server = smtplib.SMTP('localhost')
server.sendmail(FROM, TO, message)
server.quit()
print(message)
return success_with_data({"confirmation_token": confirmation_token})
def delete_class(**kwargs):
cursor, conn = connect()
user_id = kwargs["user_id"]
class_id = int(request.form.get("class_id"))
del_class_stmt = "DELETE FROM user_classes where user_id=%s AND class_id=%s"
cursor.execute(del_class_stmt, (user_id, class_id))
if cursor.rowcount is not 1:
return error_with_message("msg_failed_to_delete_class_for_user")
conn.commit()
return success_with_data({"deleted_class_id": class_id})
def change_leader(**kwargs):
cursor, conn = connect()
new_leader_id = request.form.get("new_leader_id")
user_id = kwargs["user_id"]
change_leader_stmt = "UPDATE groups SET leader_id=%s where leader_id=%s"
cursor.execute(change_leader_stmt, (new_leader_id, user_id))
if cursor.rowcount == 0:
return error_with_message("user was not a group leader")
conn.commit()
return success_with_data({})
def join_group(**kwargs):
cursor, conn = connect()
user_id = kwargs["user_id"]
group_id = request.form.get("group_id")
check_group_id_stmt = "SELECT COUNT(*) FROM groups WHERE id=%s"
cursor.execute(check_group_id_stmt, (group_id, ))
results = cursor.fetchall()
if len(results) == 0:
return error_with_message("no_matching_group_id")
update_stmt = "UPDATE users SET group_id=%s WHERE id=%s"
cursor.execute(update_stmt, (group_id, user_id))
conn.commit()
return success_with_data({})
def list_class_groups(**kwargs):
cursor, conn = connect()
user_id = kwargs["user_id"]
class_groups_stmt = """SELECT
groups.id, leader_id, start_time,
end_time, category, groups.description,
location_lat, location_lon, location_description,
classes.id, course_title, course_name
FROM groups
LEFT JOIN classes ON
groups.class_id = classes.id
LEFT JOIN user_classes ON
classes.id = user_classes.class_id
WHERE user_classes.user_id = %s"""
cursor.execute(class_groups_stmt, (user_id, ))
results = cursor.fetchall()
resultsDict = []
for result in results:
group_id = result[0]
leader_id = result[1]
start_time = result[2]
end_time = result[3]
category = result[4]
group_description = result[5]
location_lat = result[6]
location_lon = result[7]
location_description = result[8]
class_id = result[9]
course_title = result[10]
course_name = result[11]
result = {
"group_id": group_id,
"leader_id": leader_id,
"start_time": start_time,
"end_time": end_time,
"category": category,
"group_description": group_description,
"location_lat": location_lat,
"location_lon": location_lon,
"location_description": location_description,
"class_id": class_id,
"course_title": course_title,
"course_name": course_name
}
resultsDict.append(result)
return success_with_data({"groups": resultsDict})
def update_user(**kwargs):
cursor, conn = connect()
userID = kwargs["user_id"]
check_user_id_stmt = "SELECT id FROM users WHERE id=%s"
cursor.execute(check_user_id_stmt, (userID,))
results = cursor.fetchall()
if len(results) == 0:
return error_with_message("user_does_not_exist")
elif len(results) > 1:
return error_with_message("more_than_one_user")
check_password_stmt = "SELECT id FROM users WHERE id=%s AND password=%s"
cursor.execute(check_user_id_stmt, (userID,))
results = cursor.fetchall()
if len(results) == 0:
return error_with_message("incorrect password")
choose_defaults_stmt = "SELECT push_notifications_enabled, Apple_APN_Key, Android_APN_Key, group_id, name, class_year, password FROM users WHERE id=%s"
cursor.execute(choose_defaults_stmt, (userID,))
result = cursor.fetchone()
default_push = result[0]
default_apple = result[1]
default_android = result[2]
default_group_id = result[3]
default_name = result[4]
default_class_year = result[5]
default_password = result[6]
push_notif_enable = request.form.get("push_notifications_enabled", default_push)
Apple_APN_Key = request.form.get("Apple_APN_Key", default_apple)
Android_APN_Key = request.form.get("Android_APN_Key", default_android)
group_id = request.form.get("group_id", default_group_id)
name = request.form.get("name", default_name)
class_year = request.form.get("class_year", default_class_year)
password = request.form.get("password", default_password)
if int(push_notif_enable) != 0 and int(push_notif_enable) != 1:
return error_with_message("Push notifications is not 0 or 1.")
if int(group_id) < -1:
return error_with_message("Group id too negative")
update_user_stmt = "UPDATE users SET push_notifications_enabled=%s, Apple_APN_Key=%s, Android_APN_Key=%s, group_id=%s, name=%s, class_year=%s, password=%s WHERE id=%s"
cursor.execute(update_user_stmt, (push_notif_enable, Apple_APN_Key, Android_APN_Key, group_id, name, class_year, password, userID))
if cursor.rowcount is not 1:
return error_with_message("updating user failed")
conn.commit()
return success_with_data({})
def leave_group(**kwargs):
cursor, conn = connect()
user_id = kwargs["user_id"]
get_group_stmt = "SELECT group_id FROM users WHERE id=%s"
cursor.execute(get_group_stmt, (user_id, ))
results = cursor.fetchall()
if len(results) == 0:
return error_with_message("msg_user_does_not_exist")
group_id = results[0][0]
get_leader_stmt = "SELECT leader_id FROM groups WHERE id=%s"
cursor.execute(get_leader_stmt, (group_id, ))
results = cursor.fetchall()
if len(results) == 0:
return error_with_message("msg_group_does_not_exist")
leader_id = results[0][0]
if leader_id == user_id:
find_members_stmt = "SELECT id FROM users WHERE group_id=%s"
cursor.execute(find_members_stmt, (group_id, ))
results = cursor.fetchall()
if len(results) != 0:
new_leader_id = results[0][0]
change_leader_stmt = "UPDATE groups SET leader_id=%s WHERE id=%s"
cursor.execute(change_leader_stmt, (new_leader_id, group_id))
else:
del_group_stmt = "DELETE FROM groups WHERE id=%s"
cursor.execute(del_group_stmt, (group_id))
leave_group_stmt = "UPDATE users SET group_id=-1 WHERE id=%s"
cursor.execute(leave_group_stmt, (user_id, ))
count_stmt = "SELECT COUNT(*) FROM users WHERE group_id=%s"
cursor.execute(count_stmt, (group_id, ))
count = cursor.fetchone()[0]
if count == 0:
delete_group_stmt = "DELETE FROM groups WHERE id=%s"
cursor.execute(delete_group_stmt, (group_id, ))
conn.commit()
return success_with_data({})
def classes_list(**kwargs):
cursor, conn = connect()
search_string = request.form.get("search_string","(.*?)")
user_id = kwargs["user_id"]
get_school_stmt = "SELECT school_id FROM users WHERE id=%s"
cursor.execute(get_school_stmt, (user_id,))
results = cursor.fetchall()
if len(results) == 0:
return error_with_message("msg_nonexistant_school")
school_id = results[0][0]
get_classes_stmt = "SELECT id, course_title, course_name FROM classes WHERE school_id=%s"
cursor.execute(get_classes_stmt, (school_id,))
results = cursor.fetchall()
search_results = []
final_results = []
for course in results:
if re.search(search_string.lower(), course[1].lower()) is not None:
search_results.append(course)
elif re.search(search_string.lower(), course[2].lower()) is not None:
search_results.append(course)
else:
listings = course[2].split(",")
for listing in listings:
if re.search(search_string.lower(), listing.lower()) is not None:
search_results.append(course)
elif re.search(search_string.lower(), listing.replace(" ", "").lower()) is not None:
search_results.append(course)
for match in search_results:
result = {
"id": match[0],
"course_title": match[1],
"course_name": match[2]
}
final_results.append(result)
return success_with_data({"classes" : final_results})
def account_recovery():
cursor, conn = connect()
recovery_token = request.form.get("recovery_token")
new_password = request.form.get("new_password")
find_token_stmt = "SELECT user_id FROM password_recovery WHERE token=%s"
cursor.execute(find_token_stmt, (recovery_token, ))
results = cursor.fetchall()
if len(results) == 0:
return error_with_message("msg_invalid_recovery_code")
user_id = results[0][0]
change_password_stmt = "UPDATE users SET password=%s where id=%s"
cursor.execute(change_password_stmt, (new_password, user_id))
if cursor.rowcount == 0:
return error_with_message("msg_user_does_not_exist")
conn.commit()
return success_with_data({})
def delete_group(**kwargs):
cursor, conn = connect()
user_id = kwargs["user_id"]
get_group_stmt = "SELECT id FROM groups WHERE leader_id=%s"
cursor.execute(get_group_stmt, (user_id, ))
results = cursor.fetchall()
if len(results) == 0:
return error_with_message("msg_user_not_leader_of_group")
group_id = results[0][0]
del_group_stmt = "DELETE FROM groups WHERE leader_id=%s"
cursor.execute(del_group_stmt, (user_id, ))
if cursor.rowcount is not 1:
return error_with_message("msg_failed_to_delete_class")
leave_group_stmt = "UPDATE users SET group_id=%s WHERE group_id=%s"
cursor.execute(leave_group_stmt, (-1, group_id))
conn.commit()
return success_with_data({})
def update_group(**kwargs):
userID = kwargs["user_id"]
cursor, conn = connect()
group_id = request.form.get("group_id")
choose_defaults_stmt = "SELECT end_time, category, description, location_lat, location_lon, location_description FROM groups WHERE id=%s"
cursor.execute(choose_defaults_stmt, (group_id,))
results = cursor.fetchall()
if len(results) == 0:
return error_with_message("msg_group_does_not_exist")
group_info = results[0]
default_end_time = group_info[0]
default_category = group_info[1]
default_description = group_info[2]
default_location_lat = group_info[3]
default_location_lon = group_info[4]
default_location_des = group_info[5]
end_time = request.form.get("end_time", default_end_time)
category = request.form.get("category", default_category)
description = request.form.get("description", default_description)
location_lat = request.form.get("location_lat", default_location_lat)
location_lon = request.form.get("location_lon", default_location_lon)
location_des = request.form.get("location_description", default_location_des)
check_leader_id_stmt = "SELECT COUNT(*) FROM groups WHERE leader_id=%s"
cursor.execute(check_leader_id_stmt, (userID,))
results = cursor.fetchall()
if len(results) == 0:
return error_with_message("msg_no_matching_leader_id")
elif len(results) > 1:
return error_with_message("msg_multiple_leader_id_matches")
update_group_stmt = "UPDATE groups SET end_time=%s, category=%s, description=%s, location_lat=%s, location_lon=%s, location_des=%s WHERE id=%s AND leader_id=%s"
cursor.execute(update_group_stmt, (end_time, category, description, location_lat, location_lon, location_des, group_id, userID))
if cursor.rowcount is not 1:
return error_with_message("msg_updating_group_failed")
conn.commit()
return success_with_data({})
def add_class():
cursor, conn = connect()
class_id = int(request.form.get("class_id"))
session_token = request.form.get("session_token")
#Check: Check if entered class id exists
check_class_stmt = "SELECT COUNT(*) FROM classes WHERE id=%s"
cursor.execute(check_class_stmt, (class_id, ))
count = cursor.fetchone()[0]
if count is 0:
return error_with_message("invalid_class_id")
elif count is not 1:
return error_with_message(
"multiple_class_id (This message should not pop up)")
# Choose the id associated with the session token
choose_user_id_stmt = "SELECT user_id FROM sessions WHERE token=%s"
cursor.execute(choose_user_id_stmt, (session_token, ))
user_id = cursor.fetchall()
if len(user_id) == 0:
return error_with_message("invalid_session_token")
userID = user_id[0][0]
# Check: Check if this user already has the class added
check_class_list_stmt = "SELECT COUNT(*) FROM user_classes WHERE user_id=%s AND class_id=%s"
cursor.execute(check_class_list_stmt, (userID, class_id))
count2 = cursor.fetchone()[0]
if count2 is not 0:
return error_with_message("user_has_this_class")
# Add class to user's classes
add_class_stmt = "INSERT INTO user_classes (user_id, class_id) VALUES (%s, %s)"
cursor.execute(add_class_stmt, (userID, class_id))
if cursor.rowcount is not 1:
return error_with_message("adding class failed")
conn.commit()
return success_with_data({})
def login():
cursor, conn = connect()
email = request.form.get("email")
passwd = request.form.get("password")
login_stmt = "SELECT id, email, name, class_year, salt, password, account_confirmed FROM users WHERE email=%s"
cursor.execute(login_stmt, (email, ))
results = cursor.fetchall()
if len(results) == 0:
return error_with_message("account_not_created")
if results[0][6] == 0:
return error_with_message("email_not_confirmed")
salted_password = passwd + results[0][4]
if not bcrypt_sha256.verify(salted_password, results[0][5]):
return error_with_message("bad_login")
user = results[0]
user_id = user[0]
delete_duplicates_stmt = "DELETE FROM sessions WHERE user_id=%s"
cursor.execute(delete_duplicates_stmt, (user_id, ))
session_token = ''.join(
random.choice(string.ascii_letters + string.digits) for _ in range(32))
session_create_stmt = "INSERT INTO sessions (user_id, token) VALUES (%s, %s)"
cursor.execute(session_create_stmt, (user_id, session_token))
if cursor.rowcount is not 1:
return error_with_message("bad_session_creation")
conn.commit()
return success_with_data({
"token": session_token,
"user_info": {
"id": user_id,
"email": user[1],
"name": user[2],
"class_year": user[3]
}
})
def get_current_group(**kwargs):
user_id = kwargs["user_id"]
current_group_stmt = """SELECT
group_id
FROM users
WHERE id = %s"""
cursor, conn = connect()
cursor.execute(current_group_stmt, (user_id, ))
group_id = cursor.fetchone()[0]
if group_id == -1:
return success_with_data({"group": {"id": -1}})
group_exists_stmt = "SELECT COUNT(*) FROM groups WHERE id=%s"
cursor.execute(group_exists_stmt, (group_id, ))
size = cursor.fetchone()[0]
if size == 0:
return success_with_data({"group": {"id": -1}})
get_group_stmt = """SELECT id, leader_id, start_time, end_time,
category, description, location_lat, location_lon, location_description,
chat_id, class_id FROM groups WHERE id = %s"""
cursor.execute(get_group_stmt, (group_id, ))
the_group = cursor.fetchone()
group_id = the_group[0]
leader_id = the_group[1]
start_time = the_group[2]
end_time = the_group[3]
category = the_group[4]
description = the_group[5]
location_lat = the_group[6]
location_lon = the_group[7]
location_description = the_group[8]
chat_id = the_group[9]
course_id = the_group[10]
get_size_stmt = "SELECT COUNT(*) FROM users WHERE group_id=%s"
cursor.execute(get_size_stmt, (group_id, ))
size = cursor.fetchone()[0]
get_class_stmt = "SELECT id, course_title, course_name, school_id FROM classes WHERE id=%s"
cursor.execute(get_class_stmt, (course_id, ))
course_data = cursor.fetchone()
id = course_data[0]
course_title = course_data[1]
course_name = course_data[2]
school_id = course_data[3]
group_result = {
"id": group_id,
"leader_id": leader_id,
"start_time": str(start_time),
"end_time": str(end_time),
"category": category,
"description": description,
"location_lat": location_lat,
"location_lon": location_lon,
"location_description": location_description,
"chat_id": chat_id,
"course": {
"id": id,
"course_title": course_title,
"course_name": course_name,
"school_id": school_id
},
"size": size
}
return success_with_data({"group": group_result})