def users():
db = get_db()
advance = True
try:
page = int(request.args.get("page", 0))
assert 0 <= page <= 100000000000000
except (ValueError, AssertionError):
page = 0
start = page * 14
userssql = db.execute(
"SELECT userid, username, isadmin FROM users LIMIT 14 OFFSET ?",
(start, )).fetchall()
if len(userssql) < 14:
advance = False
start = db.execute(
"SELECT COUNT(*) FROM users").fetchone()["COUNT(*)"] - 14
start = max(start, 0)
userssql = db.execute("SELECT * FROM users LIMIT 14 OFFSET ?",
(start, )).fetchall()
users = []
for u in userssql:
users.append({
"userid": u["userid"],
"username": u["username"],
"isadmin": u["isadmin"]
})
return render_template("admin/users.html",
users=users,
start=start,
advance=advance)
def load_logged_in_user():
sessionid = session.get("sessionid")
g.user = None
if sessionid is not None:
db = get_db()
cookie = db.execute("SELECT * FROM cookies WHERE sessionid = ?",
(sessionid, )).fetchone()
if cookie is None or cookie["expiration"] < time.time(
): # expired or not found
session.clear() # clear the bad cookie
db.execute("DELETE FROM cookies WHERE sessionid = ?",
(sessionid, ))
db.commit()
else:
usr = db.execute("SELECT * FROM users WHERE userid = ?",
(cookie["userid"], )).fetchone()
if usr is not None:
g.user = {}
for attr in ("userid", "username", "isadmin"):
g.user[attr] = usr[attr]
if g.user is None:
if request.endpoint in ("auth.changepw", "game.play") or checkep(
request.endpoint):
flash("You must be logged in to access this page!", "danger")
return redirect(url_for(
"auth.login")) # a valid logged in session is required!
elif checkep(request.endpoint) and not g.user["isadmin"]:
flash("You must be an admin to access this page!", "danger")
return redirect(url_for("index.index"))
def make():
if request.method == 'POST':
questions = []
opt = []
questions = request.form.getlist('question[]')
opt.append(request.form.getlist('stronglyAgreeX[]'))
opt.append(request.form.getlist('stronglyAgreeY[]'))
opt.append(request.form.getlist('agreeX[]'))
opt.append(request.form.getlist('agreeY[]'))
opt.append(request.form.getlist('neutralX[]'))
opt.append(request.form.getlist('neutralY[]'))
opt.append(request.form.getlist('disagreeX[]'))
opt.append(request.form.getlist('disagreeY[]'))
opt.append(request.form.getlist('stronglyDisagreeX[]'))
opt.append(request.form.getlist('stronglyDisagreeY[]'))
# sending to db
db = get_db()
cursor = db.cursor()
cursor.execute(
'insert into `questionary` (name, xAxis, yAxis, maxX, maxY) values (?, ?, ?, ?, ?)',
[
request.form['qName'], request.form['xAxis'],
request.form['yAxis'], request.form['maxX'],
request.form['maxY']
])
questionaryId = cursor.lastrowid
for a in range(0, len(questions)):
db.execute \
(
'insert into question (fk_id, qText, stronglyAgreeX, stronglyAgreeY, agreeX, agreeY, neutralX, neutralY, disagreeX, disagreeY, stronglyDisagreeX, stronglyDisagreeY) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
[questionaryId, questions[a], opt[0][a], opt[1][a], opt[2][a], opt[3][a], opt[4][a], opt[5][a],
opt[6][a], opt[7][a], opt[8][a], opt[9][a]])
db.commit()
return redirect('/quiz/' + str(questionaryId))
def logout():
if "sessionid" not in session:
return redirect(url_for("index.index"))
db = get_db()
db.execute("DELETE FROM cookies WHERE sessionid = ?",
(session["sessionid"], ))
db.commit()
session.clear()
return redirect(url_for("index.index"))
def showQuiz(quizId):
db = get_db()
data = db.execute('select qText from question where fk_id=?',
[str(quizId)])
data = data.fetchall()
quizName = db.execute('select name from questionary where id=?',
[str(quizId)])
quizName = quizName.fetchone()
return render_template('quiz.html',
questions=data,
name=quizName,
quizid=str(quizId))
def demoteuser():
user = request.args.get("user", 0)
db = get_db()
status = db.execute("SELECT isadmin FROM users WHERE userid = ?",
(user, )).fetchone()
if status is None:
flash("Unable to demote user.", "warning")
elif status["isadmin"] == 0:
flash("That user is already a standard user.", "warning")
else:
db.execute("UPDATE users SET isadmin = 0 WHERE userid = ?", (user, ))
db.commit()
flash("That admin has been demoted.", "success")
return redirect(url_for("admin.users", page=request.args.get("page", 0)))
def removesong():
song = request.args.get("song")
if song is not None:
db = get_db()
if db.execute("SELECT * FROM songs WHERE id = ?",
(song, )).fetchone() is not None:
db.execute("DELETE FROM songs WHERE id = ?", (song, ))
db.commit()
flash("Song removed.", "success")
return redirect(
url_for("admin.songs", page=request.args.get("page", 0)))
flash("Unable to remove song.", "warning")
return redirect(url_for("admin.songs", page=request.args.get("page", 0)))
def answerQuiz(quizId):
if request.method == 'POST':
db = get_db()
qNumber = db.execute('select count(qId) from question where fk_id=?',
[str(quizId)])
qNumber = qNumber.fetchone()
qName = db.execute('select name from questionary where id=?',
[str(quizId)])
qName = qName.fetchone()
xName = db.execute('select xAxis from questionary where id=?',
[str(quizId)])
xName = xName.fetchone()
yName = db.execute('select yAxis from questionary where id=?',
[str(quizId)])
yName = yName.fetchone()
maxX = db.execute('select maxX from questionary where id=?',
[str(quizId)])
maxY = db.execute('select maxY from questionary where id=?',
[str(quizId)])
maxX = maxX.fetchone()
maxY = maxY.fetchone()
data = []
xAxis = yAxis = 0.0
for a in range(1,
qNumber[0] + 1): # starts at 1 because loop.index does
data.append(request.form.get('opinion[' + str(a) + ']'))
for a in range(0, qNumber[0]):
x = db.execute(
'select ' + str(data[a]) +
'X from question where fk_id = ? limit 1 offset ' + str(a),
[str(quizId)])
y = db.execute(
'select ' + str(data[a]) +
'Y from question where fk_id = ? limit 1 offset ' + str(a),
[str(quizId)])
x = x.fetchone()
y = y.fetchone()
xAxis = xAxis + x[0]
yAxis = yAxis + y[0]
return (render_template('result.html',
x=xAxis,
y=yAxis,
name=qName[0],
maxX=maxX[0],
maxY=maxY[0],
xAxis=xName[0],
yAxis=yName[0]))
def removeuser():
user = request.args.get("user", 0)
if user == 0:
flash("Unable to remove user.", "warning")
return redirect(
url_for("admin.users", page=request.args.get("page", 0)))
db = get_db()
if db.execute("SELECT username FROM users WHERE userid = ?",
(user, )).fetchone() is not None:
db.execute("DELETE FROM users WHERE userid = ?", (user, ))
db.commit()
db.execute("DELETE FROM game WHERE userid = ?", (user, ))
db.commit()
flash("User removed.", "success")
return redirect(
url_for("admin.users", page=request.args.get("page", 0)))
flash("Unable to remove user.", "warning")
return redirect(url_for("admin.users", page=request.args.get("page", 0)))
def login():
if g.user:
flash("You are already logged in.", "info")
return redirect(url_for("index.index"))
if request.method == "POST":
username = request.form["username"]
passwd = request.form["passwd"]
remember = "remember-me" in request.form
db = get_db()
err = False
user = db.execute("SELECT * FROM users WHERE username = ?",
(username, )).fetchone()
if user is None:
err = True
elif not bcrypt.checkpw(passwd.encode("utf8"), user["passwd"]):
err = True
if not err: # user can now be signed in
token = token_hex()
session.clear()
session.permanent = True
session["sessionid"] = token
db.execute(
"DELETE FROM cookies WHERE userid = ?",
(user["userid"], ) # delete any previous
)
expire = 60 * 10
if remember:
expire = 60 * 60 * 24 * 7
db.execute(
"INSERT INTO cookies (sessionid, userid, expiration) VALUES (?, ?, ?)",
(token, user["userid"], round(time.time()) + expire))
db.commit()
flash("Successfully logged in.", "success")
return redirect(url_for("index.index"))
flash("Invalid username or password.", "danger")
return render_template("auth/login.html")
def register():
if g.user:
flash("You are already logged in.", "info")
return redirect(url_for("index.index"))
if request.method == "POST":
username = request.form["username"]
passwd1 = request.form["passwd1"]
passwd2 = request.form["passwd2"]
db = get_db()
errs = []
if not valid(username, string.ascii_letters + string.digits + "_"):
errs.append(
"Username is invalid - please use only letters, numbers and underscores"
)
if len(username) > 24:
errs.append("Username must be a maximum of 24 characters long.")
if len(passwd1) < 6 or len(passwd2) < 6:
errs.append("Password must be 6 characters or longer.")
if passwd1 != passwd2:
errs.append("Passwords do not match.")
if db.execute("SELECT * FROM users WHERE username = ?",
(username, )).fetchone() is not None:
errs.append("That user is already registered.")
if not errs: # we can now register the user, all data is valid
salt = bcrypt.gensalt()
hashed = bcrypt.hashpw(passwd1.encode("utf-8"), salt)
db.execute(
"INSERT INTO users (username, passwd, isadmin) VALUES (?, ?, ?)",
(username, hashed, 0))
db.commit()
return redirect(url_for("auth.login"))
for err in errs: # send errors to the template
flash(err, "danger")
return render_template("auth/register.html")
def changepw():
if request.method == "POST":
passwd1 = request.form["passwd1"]
passwd2 = request.form["passwd2"]
db = get_db()
errs = []
if len(passwd1) < 6 or len(passwd2) < 6:
errs.append("Password must be 6 characters or longer.")
if passwd1 != passwd2:
errs.append("Passwords do not match.")
if not errs: # we can now change passwd
salt = bcrypt.gensalt()
hashed = bcrypt.hashpw(passwd1.encode("utf-8"), salt)
db.execute("UPDATE users SET passwd = ? WHERE userid = ?",
(hashed, g.user["userid"]))
db.commit()
return redirect(url_for("auth.logout"))
for err in errs: # send errors to the template
flash(err, "danger")
return render_template("auth/changepw.html")
def songs():
db = get_db()
if request.method == "POST":
toadd = []
idtoindex = {}
for field in request.form:
numb = int(field[1:])
if numb not in idtoindex:
toadd.append(["", ""])
idtoindex[numb] = len(toadd) - 1
if field[0] == "t":
toadd[idtoindex[numb]][0] = request.form[field].strip()
else:
toadd[idtoindex[numb]][1] = request.form[field].strip()
added = 0
for song in toadd:
if song[0] or song[1]:
if not (song[0] and song[1]):
flash(
f'Failed to add song "{song[0]}" by "{song[1]}" to the database.',
"warning")
continue
db.execute("INSERT INTO songs (title, artist) VALUES (?, ?)",
tuple(song))
db.commit()
added += 1
if added == 0:
flash("Added 0 songs.", "warning")
elif added == 1:
flash("Added 1 song.", "success")
else:
flash(f"Added {added} songs.", "success")
advance = True
try:
page = int(request.args.get("page", 0))
assert 0 <= page <= 100000000000000
except (ValueError, AssertionError):
page = 0
start = page * 14
songssql = db.execute("SELECT * FROM songs LIMIT 14 OFFSET ?",
(start, )).fetchall()
if len(songssql) < 14:
advance = False
start = db.execute(
"SELECT COUNT(*) FROM songs").fetchone()["COUNT(*)"] - 14
start = max(start, 0)
songssql = db.execute("SELECT * FROM songs LIMIT 14 OFFSET ?",
(start, )).fetchall()
songs = []
for s in songssql:
songs.append({
"id": s["id"],
"title": s["title"],
"artist": s["artist"]
})
return render_template("admin/songs.html",
songs=songs,
start=start,
advance=advance)
def leaderboard():
db = get_db()
advance = True
try:
page = int(request.args.get("page", 0))
assert 0 <= page <= 1000000000000000
except (ValueError, AssertionError):
page = 0
start = page * 10
userssql = db.execute(
"SELECT userid, highscore FROM game ORDER BY highscore DESC LIMIT 10 OFFSET ?",
(start, )).fetchall()
if len(userssql) < 10:
advance = False
start = db.execute(
"SELECT COUNT(*) FROM game").fetchone()["COUNT(*)"] - 10
start = max(start, 0)
userssql = db.execute(
"SELECT userid, highscore FROM game ORDER BY highscore DESC LIMIT 10 OFFSET ?",
(start, )).fetchall()
users = []
gotuser = False
douser = True
if g.user is None:
douser = False
for u in userssql:
name = db.execute("SELECT username FROM users WHERE userid = ?",
(u["userid"], )).fetchone()
if douser:
if u["userid"] == g.user["userid"]:
gotuser = True
users.append({
"userid": u["userid"],
"name": name["username"],
"highscore": u["highscore"]
})
insert = None
if douser and not gotuser:
al = db.execute(
"SELECT userid, highscore FROM game ORDER BY highscore DESC"
).fetchall()
c = 0
found = False
hs = 0
for uid in al:
c += 1
if uid["userid"] == g.user["userid"]:
hs = uid["highscore"]
found = True
break
if found:
insert = (c, hs)
return render_template("leaderboard.html",
users=users,
start=start,
advance=advance,
insert=insert)
def play():
db = get_db()
uid = g.user["userid"]
songrecord = db.execute("SELECT currsong FROM game WHERE userid = ?",
(uid, )).fetchone()
songid = 0
if songrecord is None: # Add user to game database on first go
db.execute(
"INSERT INTO game (userid, currscore, currsong, attempts, used, highscore) VALUES (?, ?, ?, ?, ?, ?)",
(uid, 0, 0, 0, "", 0))
db.commit()
songid = new_song(db, uid, purge_used=True)[0]
else:
songid = songrecord["currsong"]
song = db.execute("SELECT title FROM songs WHERE id = ?",
(songid, )).fetchone()
if song is None: # song database was changed between requests, probably safe to start over
songid = new_song(db, uid, purge_used=True)[0]
song = db.execute("SELECT title FROM songs WHERE id = ?",
(songid, )).fetchone()
if request.method == "POST":
words = titleToArray(song["title"])
count = 0
for wid, word in request.form.items():
correct = words[int(wid[1:])][1:]
if filterChars(word) == filterChars(correct):
count += 1
attempts = db.execute("SELECT attempts FROM game WHERE userid = ?",
(uid, )).fetchone()["attempts"]
if count == len(request.form): # if song was correct
pts = 1
if attempts == 0:
pts = 3
db.execute(
"UPDATE game SET currscore = currscore + ? WHERE userid = ?",
(pts, uid))
db.commit()
songid, ranout = new_song(db, uid)
if ranout:
flash(
"You just correctly guessed all songs in the database!",
"primary")
final_score(db, uid)
return redirect(url_for("leaderboard.leaderboard"))
else:
flash("Good job, that's correct.", "primary")
elif attempts == 0: # have another go
db.execute(
"UPDATE game SET attempts = attempts + 1 WHERE userid = ?",
(uid, ))
db.commit()
flash(
f"You entered {count} of {len(request.form)} correctly. Have another go.",
"primary")
else: # game over
flash(f"The correct title was '{song['title']}'", "primary")
final_score(db, uid)
new_song(db, uid, purge_used=True)
return redirect(url_for("leaderboard.leaderboard"))
song = db.execute("SELECT * FROM songs WHERE id = ?",
(songid, )).fetchone()
return render_template("game.html",
words=titleToArray(song["title"]),
artist=song["artist"])