def signout():
if (current_user() is not None):
invalidate_token(current_user()['session_token'])
resp = make_response(redirect('/'))
resp.set_cookie(CS542_TOKEN_COOKIE, '', 0)
flash('Logged Out', 'success')
return resp
else:
return redirect('/')
def SetRoutes():
db = database.get_db()
with db.cursor() as cursor:
get_name_query = "SELECT U.userid, role, student_name from User U, UserRoles R WHERE R.role = %s AND U.userid=R.userid"
cursor.execute(get_name_query, "setter")
entries = cursor.fetchall()
if request.method == "POST":
file = request.files['file']
picture = ""
if file and allowed_file(file.filename):
strs = file.filename.split(".")[-1]
picture = str(uuid.uuid1()) + "." + strs
file.save(os.path.join(app.config['UPLOAD_FOLDER'], picture))
with db.cursor() as cursor:
difficulty = request.form["Difficulty"]
if ("admin" not in current_user_roles()):
set_by = current_user()["userid"]
else:
set_by = request.form["SetBy"]
if (int(difficulty) < 10):
add_route_query = "INSERT INTO Route (set_by, difficulty, picture) VALUES " + \
"(%s, %s, %s);"
cursor.execute(add_route_query, (set_by, difficulty, picture))
db.commit()
flash('Created a route', 'success')
else:
flash('Difficulty should in 1-10', 'danger')
return render_template('SetRoute.html', entries=entries)
else:
return render_template('SetRoute.html', entries=entries)
def Scores():
db = database.get_db()
with db.cursor() as cursor:
get_scores_query = "SELECT E.name, T.score, E.tournament_result_unit AS unit from TournamentParticipants T, Event E WHERE T.eventid=E.eventid AND userid=%s"
cursor.execute(get_scores_query, current_user()["userid"])
result = cursor.fetchall()
return render_template('TournamentParticipants.html', records=result)
def show():
try:
user = current_user()
if (user is not None):
d = database.get_db()
with d.cursor() as cursor:
tournament_participation = """SELECT SUM(total_time) AS hours, COUNT(total_time) AS count, "tournament" as type FROM TimeEntry T, Event E WHERE userid=%s AND E.eventid=T.eventid AND E.tournament_result_ordering is not NULL
UNION
SELECT SUM(total_time) AS hours, COUNT(total_time) AS count, "non-tournament" as type FROM TimeEntry T, Event E WHERE userid=%s AND E.eventid=T.eventid AND E.tournament_result_ordering is NULL;
"""
cursor.execute(tournament_participation,
(user["userid"], user["userid"]))
t_participation = {
v["type"]: {k: b
for k, b in v.items() if k != "type"}
for v in cursor.fetchall()
}
op_events_query = "SELECT * FROM Event WHERE opener=%s AND end >= CURRENT_TIMESTAMP AND end < DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 7 day) ORDER BY start;"
cursor.execute(op_events_query, user["userid"])
opener_events = cursor.fetchall()
return render_template('user.html',
user=user,
partitipation=t_participation,
opener_events=opener_events)
else:
return redirect(url_for('accounts.signin'))
except TemplateNotFound:
abort(500)
def EditRoutes(id):
db = database.get_db()
with db.cursor() as cursor:
get_name_query = "SELECT U.userid, role,student_name from User U, UserRoles R WHERE R.role = %s AND U.userid=R.userid"
cursor.execute(get_name_query, "setter")
setters = cursor.fetchall()
get_old_data = "SELECT * FROM Route WHERE routeid=%s"
cursor.execute(get_old_data, id)
old_data = cursor.fetchone()
if request.method == "POST":
file = request.files['file']
if file and allowed_file(file.filename):
filename = secure_filename(file.filename)
strs = filename.split(".")[-1]
picture = str(uuid.uuid1()) + "." + strs
file.save(os.path.join(app.config['UPLOAD_FOLDER'], picture))
with db.cursor() as cursor:
difficulty = request.form["Difficulty"]
if ("admin" not in current_user_roles()):
set_by = current_user()["userid"]
else:
set_by = request.form["SetBy"]
if file:
add_route_query = "UPDATE Route SET set_by=%s, difficulty=%s, picture= %s WHERE routeid = %s;"
cursor.execute(add_route_query,
(set_by, difficulty, picture, id))
else:
add_route_query = "UPDATE Route SET set_by=%s, difficulty=%s WHERE routeid = %s;"
cursor.execute(add_route_query, (set_by, difficulty, id))
if (int(difficulty) < 10):
db.commit()
flash('Updated a route', 'success')
return redirect(url_for('routes.Route'))
else:
flash('Difficulty should in 1-10', 'danger')
return render_template('EditRoute.html',
setters=setters,
old_data=old_data)
else:
return render_template('EditRoute.html',
setters=setters,
old_data=old_data)
def show_my_events():
try:
user = current_user()
if (user is not None):
db = database.get_db()
with db.cursor() as cursor:
get_event = "SELECT e.name AS name, t.start AS start, t.end AS end, t.total_time AS total_time FROM " + \
"TimeEntry AS t, Event AS e WHERE e.eventid=t.eventid and t.userid=%s;"
cursor.execute(get_event, user['userid'])
entries = cursor.fetchall()
return render_template('my_events.html', entries=entries)
else:
return redirect(url_for('accounts.signup'))
except TemplateNotFound:
abort(500)
def edit(id):
current_u = current_user()
if ((int(current_u["userid"]) != int(id)
and ('admin' not in current_user_roles()
and 'opener' not in current_user_roles()))):
abort(403)
mode = ""
if (int(current_u["userid"]) == int(id)):
mode = "self"
elif ('admin' in current_user_roles()):
mode = "admin"
elif ('opener' in current_user_roles()):
mode = "opener"
db = database.get_db()
with db.cursor() as cursor:
usersel = "SELECT * FROM UserDataWithRole WHERE userid=%s"
cursor.execute(usersel, id)
userdata = cursor.fetchone()
if ("admin" in userdata["roles"].split(", ")
and "admin" not in current_user_roles()):
flash("You cannot edit that user", "warning")
return redirect(url_for('accounts.admin'))
if (request.method == "GET"):
return render_template('edit.html', user=userdata, mode=mode)
elif (request.method == "POST"):
if ("delete" in request.form):
if (mode == "opener"):
abort(403)
with db.cursor() as cursor:
userdel = "DELETE FROM User WHERE userid=%s"
cursor.execute(userdel, (id))
db.commit()
if ("admin" in current_user_roles()):
return redirect(url_for('accounts.admin', **request.args))
else:
return redirect(url_for('/', **request.args))
elif ("password" in request.form and "oldpassword" in request.form
and "confirmpassword" in request.form):
if (request.form["password"] == ""
or request.form["confirmpassword"] == ""
or request.form["confirmpassword"] !=
request.form["password"]):
flash(
"Please provide a new password, and make sure you've entered the same password twice",
"danger")
return redirect(url_for('accounts.edit', id=id,
**request.args))
with db.cursor() as cursor:
usersel = "SELECT password_hash FROM User WHERE userid=%s"
cursor.execute(usersel, id)
# Admins can change other user's passwords, or the user when providing the right password can change other user's passwords
if (mode == "admin" or mode == "opener" or bcrypt.checkpw(
request.form["oldpassword"].encode('utf-8'),
cursor.fetchone()['password_hash'].encode('utf-8'))):
with db.cursor() as cursor:
changepw = "UPDATE User SET password_hash=%s WHERE userid=%s"
cursor.execute(changepw, (bcrypt.hashpw(
request.form["password"].encode('utf-8'),
bcrypt.gensalt()), id))
db.commit()
if "admin" in current_user_roles(
) and current_u["userid"] != int(id):
flash("Password Changed Successfully", "success")
else:
flash(
"Password Changed Successfully. Please sign in with your new password.",
"success")
return redirect(
url_for('accounts.edit', id=id, **request.args))
else:
flash("Incorrect Password or Unauthorized", "danger")
return redirect(
url_for('accounts.edit', id=id, **request.args))
elif ("name" in request.form):
with db.cursor() as cursor:
userdel = "UPDATE UserData set student_name=%s WHERE userid=%s"
cursor.execute(userdel, (request.form["name"], id))
db.commit()
flash("Name changed successfully", "success")
return redirect(url_for('accounts.edit', id=id, **request.args))
else:
abort(400)
def admin(template="admin.html"):
# Page Limit
LIMIT = 12
db = database.get_db()
if request.method == "GET":
query_conditions = []
for arg, val in request.args.items():
if (arg == "paid"):
query_conditions.append("paid=%s" % db.escape(val))
elif (arg == "waiver"):
query_conditions.append("waiver=%s" % db.escape(val))
elif (arg == "cpr"):
query_conditions.append("cpr_certified=%s" % db.escape(val))
elif (arg == "PE"):
query_conditions.append("pe_credit=%s" % db.escape(val))
elif (arg == "name"):
query_conditions.append("student_name LIKE %s" %
db.escape("%" + val + "%"))
elif (arg == "setter"):
query_conditions.append("roles " +
("NOT " if val == "0" else "") +
"LIKE '%setter%'")
elif (arg == "opener"):
query_conditions.append("roles " +
("NOT " if val == "0" else "") +
"LIKE '%opener%'")
elif (arg == "admin"):
query_conditions.append("roles " +
("NOT " if val == "0" else "") +
"LIKE '%admin%'")
with db.cursor() as cursor:
query = "SELECT COUNT(*) as ct FROM UserDataWithRole"
if (len(query_conditions) > 0):
query += " WHERE " + (" AND ".join(query_conditions))
cursor.execute(query)
count = cursor.fetchone()["ct"]
# Pagination calculations
page = int(request.args["page"]) if "page" in request.args else 0
offset = page * LIMIT
maxpage = math.ceil(count / LIMIT) - 1
pages = []
if (maxpage >= 2):
if (page == 0):
pages.append(0)
pages.append(1)
pages.append(2)
elif (page == maxpage):
pages.append(maxpage - 2)
pages.append(maxpage - 1)
pages.append(maxpage)
else:
pages.append(page - 1)
pages.append(page)
pages.append(page + 1)
elif (maxpage == 1):
pages.append(0)
pages.append(1)
else:
pages.append(0)
with db.cursor() as cursor:
query = "SELECT * FROM UserDataWithRole"
if (len(query_conditions) > 0):
query += " WHERE " + (" AND ".join(query_conditions))
query += " LIMIT %s OFFSET %s" % (db.escape(LIMIT),
db.escape(offset))
cursor.execute(query)
result = cursor.fetchall()
return render_template(
template,
userlist=result,
pages=pages,
page=page,
maxpage=maxpage,
limit=LIMIT,
count=count,
search_name=request.args["name"] if "name" in request.args else "")
elif request.method == "POST":
param = None
val = None
for attr in ["paid", "waiver", "cpr_certified", "pe_credit"]:
if attr in request.form:
param = attr
val = request.form[attr]
break
if not param == None:
if ('admin' in current_user_roles() or
((param == "paid" or param == "waiver") and int(val) == 1)):
try:
with db.cursor() as cursor:
userupdate = "UPDATE UserData SET " + param + "=%s WHERE userid=%s"
cursor.execute(userupdate,
(val, request.form["userid"]))
db.commit()
except pymysql.InternalError as e:
### Trigger could create an error. Pass it thorugh here.
flash(e.args[1], 'danger')
return redirect(url_for('accounts.admin', **request.args))
else:
abort(403)
if ('admin' in current_user_roles()):
for attr in ["setter", "opener", "admin"]:
if attr in request.form:
param = attr
val = request.form[attr]
break
if (current_user()["userid"] == int(request.form["userid"])
and param == "admin" and int(val) == 0):
flash("You cannot demote yourself", "danger")
return redirect(url_for('accounts.admin', **request.args))
if not param == None:
try:
with db.cursor() as cursor:
userupdate = None
if (int(val) == 1):
userupdate = "INSERT INTO UserRoles VALUES(%s,%s)"
else:
userupdate = "DELETE FROM UserRoles WHERE userid=%s AND role=%s"
cursor.execute(userupdate,
(request.form["userid"], param))
db.commit()
except pymysql.InternalError as e:
### Trigger could create an error. Pass it thorugh here.
flash(e.args[1], 'danger')
return redirect(url_for('accounts.admin', **request.args))
if ("delete" in request.form):
with db.cursor() as cursor:
userdel = "DELETE FROM User WHERE userid=%s"
cursor.execute(userdel, (request.form["delete"]))
db.commit()
return redirect(url_for('accounts.admin', **request.args))
abort(400)
else:
abort(403)
