def edit(id):
if request.method == "GET":
db = database.get_db()
with db.cursor() as cursor:
get_event_info = "SELECT name, start, end, description, max_participants, cost, paid_members_only, opener, " \
"tournament_result_unit, tournament_result_ordering FROM Event WHERE eventid=%s;"
cursor.execute(get_event_info, id)
entries = cursor.fetchall()
get_opener = "SELECT userid, student_name from UserDataWithRole where roles LIKE '%opener%'"
cursor.execute(get_opener)
openers = cursor.fetchall()
try:
return render_template('event_edit.html', entries=entries, openers=openers)
except TemplateNotFound:
abort(500)
else:
try:
db = database.get_db()
with db.cursor() as cursor:
update_event = "UPDATE Event SET name=%s, description=%s, start=%s, end=%s, max_participants=%s, cost=%s, paid_members_only=%s, " + \
"opener=%s, tournament_result_unit=%s, tournament_result_ordering=%s WHERE eventid=%s;"
opener = None if (request.form['opener'] == "None") else request.form['opener']
if request.form['TRO'] == "None":
if request.form['TRU'] == '':
print(11111)
cursor.execute(update_event, (request.form['event_name'], request.form['description'],
request.form['start'], request.form['end'],
request.form['max_participants'],
request.form['cost'], request.form['PMO_Options'],
request.form['opener'], None, None, id))
else:
cursor.execute(update_event, (request.form['event_name'], request.form['description'],
request.form['start'], request.form['end'],
request.form['max_participants'],
request.form['cost'], request.form['PMO_Options'],
request.form['opener'], request.form['TRU'], None, id))
else:
if request.form['TRU'] == '':
cursor.execute(update_event, (request.form['event_name'], request.form['description'],
request.form['start'], request.form['end'],
request.form['max_participants'],
request.form['cost'], request.form['PMO_Options'],
request.form['opener'],
None, request.form['TRO'], id))
else:
cursor.execute(update_event, (request.form['event_name'], request.form['description'],
request.form['start'], request.form['end'], request.form['max_participants'],
request.form['cost'], request.form['PMO_Options'], request.form['opener'],
request.form['TRU'], request.form['TRO'], id))
if (cursor.rowcount == 1):
db.commit()
flash('Event infomation has been changed! ', 'success')
return redirect(url_for('events.show_info', id=id, **request.args))
else:
flash('Check if you insert the correct information or change the information', 'danger')
return redirect(url_for('events.show_info', id=id, **request.args))
except pymysql.InternalError as e:
flash(e.args[1], 'danger')
return redirect(url_for('events.show_info', id=id, **request.args))
def create():
db = database.get_db()
with db.cursor() as cursor:
get_opener = "SELECT userid, student_name from UserDataWithRole where roles LIKE '%opener%';"
cursor.execute(get_opener)
entries = cursor.fetchall()
if request.method == "POST":
try:
db = database.get_db()
with db.cursor() as cursor:
add_event_query = "INSERT INTO Event(name, description, start, end, max_participants, cost, " \
"paid_members_only, opener, tournament_result_unit, tournament_result_ordering) VALUES" + \
"(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"
if request.form['TRO'] == "None":
if request.form['TRU'] == '':
cursor.execute(add_event_query, (request.form['event_name'], request.form['description'],
request.form['start'], request.form['end'],
request.form['max_participants'],
request.form['cost'], request.form['PMO_Options'],
request.form['opener'],
None, None))
else:
cursor.execute(add_event_query, (request.form['event_name'], request.form['description'],
request.form['start'], request.form['end'],
request.form['max_participants'],
request.form['cost'], request.form['PMO_Options'],
request.form['opener'],
request.form['TRU'], None))
else:
if request.form['TRU'] == '':
cursor.execute(add_event_query, (request.form['event_name'], request.form['description'],
request.form['start'], request.form['end'],
request.form['max_participants'],
request.form['cost'], request.form['PMO_Options'],
request.form['opener'],
None, request.form['TRO']))
else:
cursor.execute(add_event_query, (request.form['event_name'], request.form['description'],
request.form['start'], request.form['end'], request.form['max_participants'],
request.form['cost'], request.form['PMO_Options'], request.form['opener'],
request.form['TRU'], request.form['TRO']))
if (cursor.rowcount == 1):
db.commit()
flash('Created event', 'success')
return redirect(url_for('events.show', **request.args))
else:
flash('Check if you insert the correct information', 'danger')
return redirect(url_for('events.show', **request.args))
except pymysql.InternalError as e:
flash(e.args[1], 'danger')
return redirect(url_for('events.show', **request.args))
else:
try:
return render_template('create.html', entries=entries)
except TemplateNotFound:
abort(500)
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 home():
d = db.get_db()
with d.cursor() as cursor:
next_7_days = "SELECT * FROM event WHERE end >= CURRENT_TIMESTAMP AND end < DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 7 day) ORDER BY start LIMIT 4;"
cursor.execute(next_7_days)
events = cursor.fetchall()
return render_template('home.html', events=events)
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 PE():
flash(
'Please enter the start date of the current semester/term in order to see the PE student information!',
'danger')
db = database.get_db()
result = [{'Name': None, 'TimeSpent': None}]
if request.method == "POST":
start_date = request.form['Start Date']
if start_date == "":
return render_template('PE.html', records=result)
else:
with db.cursor() as cursor:
get_pe_query = "SELECT Name, SUM(total_time) AS TimeSpent " + \
"FROM PE " +\
"WHERE start >= %s " +\
"GROUP BY Name " +\
"ORDER BY Name"
#get_pe_query = "SELECT student_name AS Name, SUM(total_time) AS TimeSpent " + \
# "FROM User U left join (SELECT * from timeentry where start >= %s) AS T " + \
# "ON U.userid = T.userid " + \
# "WHERE U.pe_credit = 1 " + \
# "GROUP BY student_name " + \
# "ORDER BY student_name"
cursor.execute(get_pe_query, start_date)
result = cursor.fetchall()
return render_template('PE.html', records=result)
else:
return render_template('PE.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 show_info(id):
db = database.get_db()
with db.cursor() as cursor:
get_event_info = "SELECT name, start, end, description, max_participants, cost, paid_members_only, " \
"tournament_result_unit, tournament_result_ordering FROM Event WHERE eventid=%s;"
get_result_asc = "SELECT U.student_name as name, T.score as score FROM tournamentparticipants as T, " \
"user as U WHERE U.userid=T.userid and T.eventid=%s ORDER BY score;"
get_result_desc = "SELECT U.student_name as name, T.score as score FROM tournamentparticipants as T, " \
"user as U WHERE U.userid=T.userid and T.eventid=%s ORDER BY score DESC;"
cursor.execute(get_event_info, id)
entries = cursor.fetchall()
for entry in entries:
if entry['tournament_result_ordering'] is None:
entry['result'] = None
elif entry['tournament_result_ordering'] == 1:
cursor.execute(get_result_asc, id)
results = cursor.fetchall()
entry['result'] = results
else:
cursor.execute(get_result_desc, id)
results = cursor.fetchall()
entry['result'] = results
get_opener = "SELECT student_name FROM User AS u, Event AS e WHERE u.userid = e.opener and e.eventid=%s;"
cursor.execute(get_opener, id)
openers = cursor.fetchall()
return render_template('event_info.html', entries=entries, openers=openers)
def invalidate_token(token):
db = database.get_db()
with db.cursor() as cursor:
delete_session_query = "DELETE FROM LoginSession where LoginSession.token=%s"
cursor.execute(delete_session_query, token)
if (cursor.rowcount == 1):
db.commit()
return (cursor.rowcount == 1)
def get_result(eventid, userid):
db = database.get_db()
with db.cursor() as cursor:
get_result = 'SELECT score FROM tournamentparticipants WHERE userid=%s AND eventid=%s;'
cursor.execute(get_result, (userid, eventid))
result = cursor.fetchone()
if result is None:
return None
return result['score']
def resetPayment():
if (request.method == "POST"):
db = database.get_db()
with db.cursor() as cursor:
clearpayment = "UPDATE UserData set paid=0;"
cursor.execute(clearpayment)
db.commit()
flash("Cleared all membership payments", "success")
return redirect(url_for('accounts.admin'))
else:
return render_template('resetconfirm.html', type="membership payments")
def resetWaiver():
db = database.get_db()
if (request.method == "POST"):
with db.cursor() as cursor:
clearwaiver = "UPDATE UserData set waiver=0;"
cursor.execute(clearwaiver)
db.commit()
flash("Cleared all waiver signatures", "success")
return redirect(url_for('accounts.admin'))
else:
return render_template('resetconfirm.html', type="waiver signatures")
def signup():
if request.method == "POST":
## Post method, process data
for _, v in request.form.items():
if v is None or v.strip() == "":
flash('Make sure that you fill in every field', 'danger')
return redirect(url_for('accounts.signup', **request.args))
if (request.form["password"] != request.form["confirmpassword"]):
flash('Make sure that your passwords match', 'danger')
return redirect(url_for('accounts.signup', **request.args))
db = database.get_db()
with db.cursor() as cursor:
salt = bcrypt.gensalt()
studentIDHash = hashlib.sha512(
request.form["studentID"].encode('utf-8')).hexdigest()
add_user_query = "INSERT INTO User (student_id, student_name, join_date, password_hash) VALUES " + \
"(%s, %s, CURDATE(), %s);"
cursor.execute(
add_user_query,
(studentIDHash, request.form["name"],
bcrypt.hashpw(request.form["password"].encode('utf-8'),
salt)))
if (cursor.rowcount == 1):
db.commit()
check_if_first_user_query = "SELECT COUNT(*) as ct FROM User;"
cursor.execute(check_if_first_user_query)
if (int(cursor.fetchone()["ct"]) == 1):
check_if_first_user_query = "INSERT INTO UserRoles VALUES ((SELECT userid FROM User LIMIT 1), 'admin');"
cursor.execute(check_if_first_user_query)
db.commit()
flash('Created first user account as admin', 'success')
else:
flash('Created user account', 'success')
return redirect(url_for('accounts.signup', **request.args))
else:
flash('Error creating user. Does one already exist?', 'danger')
return redirect(url_for('accounts.signup', **request.args))
else:
## Get method, show the form
try:
# Pre-fill id if supplied.
id = ''
if ('id' in request.args.keys()):
id = request.args["id"]
return render_template('register.html', id=id)
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 Route():
db = database.get_db()
with db.cursor() as cursor:
get_routes_query = "SELECT routeid, set_by, difficulty, picture, student_name from Route, User WHERE Route.set_by = User.userid"
cursor.execute(get_routes_query)
result = cursor.fetchall()
if request.method == "POST":
if ("delete" in request.form):
with db.cursor() as cursor:
routedel = "DELETE FROM Route WHERE routeid=%s"
cursor.execute(routedel, (request.form["delete"]))
db.commit()
return redirect(url_for('routes.Route', **request.args))
return render_template('Route.html', routes=result)
def current_user():
if flask.has_request_context(
) and CS542_TOKEN_COOKIE in flask.request.cookies:
db = database.get_db()
if 'current_user' not in flask.g:
with db.cursor() as cursor:
get_user_from_token_query = "SELECT U.*, (NOW()- L.start_time) as session_duration_seconds," + \
"L.token as session_token FROM UserDataWithRole U, LoginSession L WHERE L.token = %s AND U.userid = L.userid;"
cursor.execute(get_user_from_token_query,
flask.request.cookies[CS542_TOKEN_COOKIE])
result = cursor.fetchone()
flask.g.current_user = result
return result
else:
return flask.g.current_user
else:
return None
def signin():
if (request.method == "GET"):
return render_template('login.html')
elif (request.method == "POST"):
db = database.get_db()
with db.cursor() as cursor:
studentIDHash = hashlib.sha512(
request.form["studentID"].encode('utf-8')).hexdigest()
get_user_login = "******"
cursor.execute(get_user_login, studentIDHash)
result = cursor.fetchone()
if (cursor.rowcount == 1 and bcrypt.checkpw(
request.form["password"].encode('utf-8'),
result['password_hash'].encode('utf-8'))):
## User successfuly authenticated, make the session for the user.
# Delte any old session that exists
delete_old_session_query = "DELETE FROM LoginSession WHERE userid=%s;"
cursor.execute(delete_old_session_query, result['userid'])
# Make a new session
session_token = secrets.token_hex(30)
make_user_session = "INSERT INTO LoginSession (userid, token) VALUES (%s, %s);"
cursor.execute(make_user_session,
(result['userid'], session_token))
if (cursor.rowcount == 1):
db.commit()
resp = make_response(
redirect('/' if "redirect" not in
request.args else request.args["redirect"]))
resp.set_cookie(CS542_TOKEN_COOKIE, session_token)
flash('Welcome, %s.' % result['student_name'], 'success')
return resp
else:
flash('Login Failed.', 'danger')
return render_template('login.html')
else:
flash('Login Failed.', 'danger')
return render_template('login.html')
return ""
def checkinout(id):
db = database.get_db()
tournament_info = "SELECT tournament_result_unit AS unit FROM event WHERE eventid=%s"
with db.cursor() as cursor:
cursor.execute(tournament_info, id)
tour_infos = cursor.fetchall()
get_view_query_in = "SELECT Name, userid from checkin where eventid = %s"
get_view_query_out = "SELECT Name, total_time AS Time, userid from checkout where eventid = %s"
if request.method == "POST":
with db.cursor() as cursor:
if 'eventid' in request.form:
select = "SELECT userid as id from TimeEntry WHERE eventid=%s"
cursor.execute(select, id)
userids = cursor.fetchall()
checkout_condition = []
if userids:
for userid in userids:
checkout_condition.append("userid=%s" %
db.escape(userid['id']))
checkout = "UPDATE TimeEntry SET end=CURRENT_TIMESTAMP() WHERE eventid=%s AND " + (
" OR ".join(checkout_condition))
cursor.execute(checkout, id)
close_event = "UPDATE Event SET actual_end=CURRENT_TIMESTAMP() WHERE eventid=%s;"
cursor.execute(close_event, request.form['eventid'])
if cursor.rowcount == 1:
db.commit()
flash('Event has been closed!', 'success')
return redirect(url_for('events.show', **request.args))
else:
flash('Event has not been closed successfully!', 'danger')
return redirect(url_for('events.show', **request.args))
elif 'StudentID' in request.form:
studentIDshash = hashlib.sha512(
request.form['StudentID'].encode('utf-8')).hexdigest()
find_userid_query = "SELECT userid FROM User WHERE student_id = %s"
cursor.execute(find_userid_query, studentIDshash)
userid = cursor.fetchall()
if userid == ():
flash('No account with this studentID, please sign up!',
'danger')
#get_view_query = "SELECT student_name AS Name " + \
# "FROM TimeEntry T, User U " + \
# "WHERE T.userid = U.userid AND eventid = %s AND T.end is null"
cursor.execute(get_view_query_in, id)
result = cursor.fetchall()
#get_view_query = "SELECT student_name AS Name, total_time AS Time " + \
# "FROM TimeEntry T, User U " + \
# "WHERE T.userid = U.userid AND eventid = %s AND T.end is not null"
cursor.execute(get_view_query_out, id)
check_out = cursor.fetchall()
return render_template('checkinout.html',
id=id,
records=result,
check_outs=check_out,
tour_infos=tour_infos)
else:
userid = userid[0]['userid']
check_userid_query = "SELECT count(*) AS c FROM TimeEntry " +\
"WHERE eventid= %s " +\
"AND userid = %s"
cursor.execute(check_userid_query, (id, userid))
count = cursor.fetchall()
count = count[0]['c']
if count == 0:
cursor.execute(get_view_query_out, id)
check_out = cursor.fetchall()
try:
add_start_query = "INSERT INTO TimeEntry (eventid, userid, start) VALUES(%s, %s, CURRENT_TIMESTAMP());"
cursor.execute(add_start_query, (id, userid))
if (cursor.rowcount == 1):
db.commit()
flash('Successfully checked in', 'success')
cursor.execute(get_view_query_in, id)
result = cursor.fetchall()
return render_template('checkinout.html',
id=id,
records=result,
check_outs=check_out,
tour_infos=tour_infos)
else:
flash('Check in error', 'danger')
cursor.execute(get_view_query_in, id)
result = cursor.fetchall()
return render_template('checkinout.html',
id=id,
records=result,
check_outs=check_out,
tour_infos=tour_infos)
except pymysql.InternalError as e:
flash(e.args[1], 'danger')
cursor.execute(get_view_query_in, id)
result = cursor.fetchall()
return render_template('checkinout.html',
id=id,
records=result,
check_outs=check_out,
tour_infos=tour_infos)
if count != 0:
check_checkout_query = "SELECT count(*) AS c FROM TimeEntry " +\
"WHERE eventid =%s AND userid = %s AND end is not null"
cursor.execute(check_checkout_query, (id, userid))
cc = cursor.fetchall()
if cc[0]['c'] == 0:
add_end_query = "UPDATE TimeEntry SET end = CURRENT_TIMESTAMP() " +\
"WHERE eventid = %s AND userid = %s"
cursor.execute(add_end_query, (id, userid))
if (cursor.rowcount == 1):
db.commit()
flash('Successfully checked out', 'success')
cursor.execute(get_view_query_in, id)
result = cursor.fetchall()
cursor.execute(get_view_query_out, id)
check_out = cursor.fetchall()
return render_template('checkinout.html',
id=id,
records=result,
check_outs=check_out,
tour_infos=tour_infos)
else:
flash('Something missing in the account', 'danger')
cursor.execute(get_view_query_in, id)
result = cursor.fetchall()
cursor.execute(get_view_query_out, id)
check_out = cursor.fetchall()
return render_template('checkinout.html',
id=id,
records=result,
check_outs=check_out,
tour_infos=tour_infos)
elif cc[0]['c'] == 1:
flash("The student has already checked out", "success")
cursor.execute(get_view_query_in, id)
result = cursor.fetchall()
cursor.execute(get_view_query_out, id)
check_out = cursor.fetchall()
return render_template('checkinout.html',
id=id,
records=result,
check_outs=check_out,
tour_infos=tour_infos)
else:
cursor.execute(get_view_query_in, id)
result = cursor.fetchall()
cursor.execute(get_view_query_out, id)
check_out = cursor.fetchall()
current_result = get_result(request.form['result_eventid'],
request.form['result_stuid'])
if current_result is None:
insert_result = 'INSERT INTO tournamentparticipants VALUES(%s, %s, %s);'
cursor.execute(
insert_result,
(request.form['result_eventid'],
request.form['result_stuid'], request.form['result']))
if cursor.rowcount == 1:
db.commit()
flash('Result has been inserted', 'success')
return render_template('checkinout.html',
id=id,
records=result,
check_outs=check_out,
tour_infos=tour_infos,
**request.args)
else:
flash('Result has not been inserted successfully',
'danger')
return render_template('checkinout.html',
id=id,
records=result,
check_outs=check_out,
tour_infos=tour_infos,
**request.args)
else:
insert_result = 'UPDATE tournamentparticipants SET score=%s WHERE eventid=%s AND userid=%s;'
cursor.execute(insert_result,
(request.form['result'],
request.form['result_eventid'],
request.form['result_stuid']))
if cursor.rowcount == 1:
db.commit()
flash('Result has been changed', 'success')
return render_template('checkinout.html',
id=id,
records=result,
check_outs=check_out,
tour_infos=tour_infos,
**request.args)
else:
flash('Result has not been changed successfully',
'danger')
return render_template('checkinout.html',
id=id,
records=result,
check_outs=check_out,
tour_infos=tour_infos,
**request.args)
else:
with db.cursor() as cursor:
cursor.execute(get_view_query_in, id)
result = cursor.fetchall()
cursor.execute(get_view_query_out, id)
check_out = cursor.fetchall()
return render_template('checkinout.html',
id=id,
records=result,
check_outs=check_out,
tour_infos=tour_infos)
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 participation():
# Page Limit
LIMIT = 10
db = database.get_db()
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 U.userid, U.student_name, IFNULL(SUM(total_time),0.0) as hours FROM UserDataWithRole U LEFT JOIN TimeEntry ON U.userid=TimeEntry.userid "
if (len(query_conditions) > 0):
query += " WHERE " + (" AND ".join(query_conditions))
query += "GROUP BY U.userid ORDER BY hours DESC LIMIT %s OFFSET %s" % (
db.escape(LIMIT), db.escape(offset))
cursor.execute(query)
result = cursor.fetchall()
return render_template(
'participation.html',
userlist=result,
pages=pages,
page=page,
maxpage=maxpage,
limit=LIMIT,
count=count,
search_name=request.args["name"] if "name" in request.args else "")
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)
def show():
if request.method == "GET":
try:
db = database.get_db()
with db.cursor() as cursor:
# Filtration criteria
query_conditions = []
for arg, val in request.args.items():
if (arg == "free" and val is not "" and int(val) == 1):
query_conditions.append("paid_members_only=0 AND cost=0")
elif (arg == "free" and val is not "" and int(val) == 0):
query_conditions.append("paid_members_only=1 AND cost=0")
elif (arg == "tournament" and val is not "" and int(val) == 1):
query_conditions.append("tournament_result_unit IS NOT NULL and tournament_result_ordering IS NOT NULL")
elif (arg == "tournament" and val is not "" and int(val) == 0):
query_conditions.append("tournament_result_unit IS NULL and tournament_result_ordering IS NULL")
elif (arg == "maxcost" and val is not "" and val.isnumeric()):
query_conditions.append("cost <= %s" % db.escape(val))
elif (arg == "start" and val is not ""):
query_conditions.append("start >= %s" % db.escape(val))
elif (arg == "end" and val is not ""):
query_conditions.append("end <= %s" % db.escape(val))
query = "SELECT COUNT(*) as ct FROM Event"
if (len(query_conditions) > 0):
query += " WHERE " + (" AND ".join(query_conditions))
print(query)
cursor.execute(query)
count = cursor.fetchone()["ct"]
# Pagination calculations
LIMIT = 9
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)
get_event = "SELECT e.eventid, e.name, e.start, e.end, e.actual_end, e.description, e.max_participants, " \
"e.cost, e.paid_members_only, e.opener, e.display, e.tournament_result_unit, " \
"e.tournament_result_ordering, e.display, u.student_name FROM event AS e LEFT JOIN user AS u ON e.opener=u.userid"
if (len(query_conditions) > 0):
get_event += " WHERE " + (" AND ".join(query_conditions))
get_event += " ORDER BY e.start DESC LIMIT %s OFFSET %s" % (db.escape(LIMIT), db.escape(offset))
cursor.execute(get_event)
entries = cursor.fetchall()
get_result_asc = "SELECT U.student_name as name, T.score as score FROM tournamentparticipants as T, " \
"user as U WHERE U.userid=T.userid and T.eventid=%s ORDER BY score;"
get_result_desc = "SELECT U.student_name as name, T.score as score FROM tournamentparticipants as T, " \
"user as U WHERE U.userid=T.userid and T.eventid=%s ORDER BY score DESC;"
for entry in entries:
if entry['tournament_result_ordering'] is None:
entry['result'] = None
elif entry['tournament_result_ordering'] == 1:
cursor.execute(get_result_asc, entry['eventid'])
results = cursor.fetchall()
entry['result'] = results
else:
cursor.execute(get_result_desc, entry['eventid'])
results = cursor.fetchall()
entry['result'] = results
return render_template('events.html',
entries=entries,
pages=pages,
page=page,
maxpage=maxpage,
limit=LIMIT,
count=count,
start = request.args["start"] if "start" in request.args else "",
end = request.args["end"] if "end" in request.args else "",
maxcost = int(request.args["maxcost"]) if "maxcost" in request.args and request.args["maxcost"] and request.args["maxcost"].isnumeric() else "",
free = int(request.args["free"]) if "free" in request.args and request.args["free"] else 2,
tournament = int(request.args["tournament"]) if "tournament" in request.args and request.args["tournament"] else 2)
except TemplateNotFound:
abort(500)
else:
db = database.get_db()
with db.cursor() as cursor:
if 'delete' in request.form:
delete_event = "UPDATE event SET display=0 WHERE eventid=%s;"
delete_timeentry = "DELETE FROM timeentry WHERE eventid=%s;"
cursor.execute(delete_event, request.form['delete'])
if cursor.rowcount == 1:
flash('Event has been deleted', 'success')
cursor.execute(delete_timeentry, request.form['delete'])
db.commit()
return redirect(url_for('events.show', **request.args))
else:
flash('Event has not been deleted successfully', 'danger')
return redirect(url_for('events.show', **request.args))
else:
close_event = "UPDATE event SET actual_start=CURRENT_TIMESTAMP() WHERE eventid=%s;"
cursor.execute(close_event, request.form['eventid'])
if cursor.rowcount == 1:
db.commit()
flash('Event has been opened!', 'success')
return redirect(url_for('checkin.checkinout', id=request.form['eventid'], **request.args))
else:
flash('Event has not been opened successfully!', 'danger')
return redirect(url_for('events.show', **request.args))