def deleteuser():
if "username" in session:
userId = session["userId"]
dbresponse = dbSelectCallEsc(
mysql,
"SELECT EXISTS(SELECT * FROM administrators WHERE userId = %s);",
[userId])[0][0]
if dbresponse == 1:
userIdToDel = request.form["userId"]
cur = mysql.connection.cursor()
cur.execute("DELETE FROM users WHERE userid = %s;", [userIdToDel])
cur.execute("DELETE FROM favourites WHERE userid = %s;",
[userIdToDel])
cur.execute("DELETE FROM administrators WHERE userid = %s;",
[userIdToDel])
cur.execute(
f"INSERT INTO logs (author, message) VALUES ('Admin {userId}', 'Deleted user {userIdToDel}.');"
)
mysql.connection.commit()
cur.close()
return ""
else:
abort(404)
else:
abort(404)
def profile():
if "username" in session:
username = session["username"]
userId = session["userId"]
dbresponse = dbSelectCallEsc(
mysql,
"""SELECT items.heading, items.imgurl, items.itemid, items.url, items.bazar
FROM items INNER JOIN favourites ON items.itemid=favourites.itemid WHERE userid=%s;""",
[userId])
dbresponseAdmin = dbSelectCallEsc(
mysql,
"SELECT EXISTS(SELECT * FROM administrators WHERE userId = %s);",
[userId])[0][0]
adminAccount = False
if dbresponseAdmin == 1:
adminAccount = True
return render_template("profile.html",
items=dbresponse,
username=username,
loggedIn=True,
adminAccount=adminAccount)
return redirect(url_for('auth'))
def admin():
if "username" in session:
userId = session["userId"]
dbresponse = dbSelectCallEsc(
mysql,
"SELECT EXISTS(SELECT * FROM administrators WHERE userId = %s);",
[userId])[0][0]
if dbresponse == 1:
dbresponseItems = dbSelectCall(
mysql,
"SELECT heading, imgurl, itemid, url, bazar, dateadded FROM items ORDER BY dateadded DESC;"
)
dbresponseUnpItems = dbSelectCall(
mysql, "SELECT itemid FROM items WHERE public = 0;")
dbresponseUsers = dbSelectCall(
mysql, "SELECT userid, username FROM users;")
dbresponseReports = dbSelectCall(
mysql,
"""SELECT items.heading, items.imgurl, items.itemid, items.url, COUNT(reports.itemid) AS reports
FROM items LEFT JOIN reports ON items.itemid = reports.itemid GROUP BY items.itemid HAVING reports > 0 ORDER BY reports DESC;"""
)
dbresponseAdmins = dbSelectCall(
mysql, "SELECT userid FROM administrators;")
dbresponseLogs = dbSelectCall(
mysql,
"SELECT logid, author, message, datecreated FROM logs ORDER BY datecreated DESC;"
)
unpItems = []
for i in dbresponseUnpItems:
unpItems.append(i[0])
admins = []
for i in dbresponseAdmins:
admins.append(i[0])
return render_template("admin.html",
items=dbresponseItems,
unpItems=unpItems,
users=dbresponseUsers,
reports=dbresponseReports,
admins=admins,
logs=dbresponseLogs)
else:
abort(404)
else:
abort(404)
def grant():
if "username" in session:
userId = session["userId"]
dbresponse = dbSelectCallEsc(
mysql,
"SELECT EXISTS(SELECT * FROM administrators WHERE userId = %s);",
[userId])[0][0]
if dbresponse == 1:
userIdToGrant = request.form["userId"]
cur = mysql.connection.cursor()
cur.execute("INSERT INTO administrators (userid) VALUES (%s);",
[userIdToGrant])
cur.execute(
f"INSERT INTO logs (author, message) VALUES ('Admin {userId}', 'Granted administrator to user {userIdToGrant}.');"
)
mysql.connection.commit()
cur.close()
return ""
else:
abort(404)
else:
abort(404)
def publish():
if "username" in session:
userId = session["userId"]
dbresponse = dbSelectCallEsc(
mysql,
"SELECT EXISTS(SELECT * FROM administrators WHERE userId = %s);",
[userId])[0][0]
if dbresponse == 1:
itemId = request.form["itemId"]
cur = mysql.connection.cursor()
cur.execute("UPDATE items SET public = 1 WHERE itemid = %s;",
[itemId])
cur.execute(
f"INSERT INTO logs (author, message) VALUES ('Admin {userId}', 'Published item {itemId}.');"
)
mysql.connection.commit()
cur.close()
return ""
else:
abort(404)
else:
abort(404)
def browse():
searchQuery = request.args.get("search")
pageQuery = request.args.get("page")
sortQuery = request.args.get("sort")
bazarQuery = request.args.get("bazar")
itemsPerPage = 15
try:
pageQuery = int(pageQuery)
if pageQuery < 1:
pageQuery = 1
except:
pageQuery = 1
if sortQuery == "newest" or sortQuery == "" or sortQuery == None:
sortCond = "dateadded DESC"
elif sortQuery == "oldest":
sortCond = "dateadded ASC"
elif sortQuery == "alphasc":
sortCond = "heading ASC"
else:
sortCond = "heading DESC"
if bazarQuery is None:
bazarQuery = "any"
if searchQuery == None or searchQuery == "":
if bazarQuery != "any":
dbresponseItems = dbSelectCall(
mysql,
f"""SELECT heading, imgurl, itemid, url, bazar FROM items
WHERE bazar = '{bazarQuery}' AND public=1 ORDER BY {sortCond}
LIMIT {(pageQuery-1)*itemsPerPage}, {itemsPerPage};""")
dbresponseItemCount = dbSelectCall(
mysql, f"""SELECT COUNT(*) FROM items
WHERE bazar = '{bazarQuery}' AND public=1;""")
else:
dbresponseItems = dbSelectCall(
mysql,
f"""SELECT heading, imgurl, itemid, url, bazar FROM items WHERE public=1
ORDER BY {sortCond} LIMIT {(pageQuery-1)*itemsPerPage}, {itemsPerPage};"""
)
dbresponseItemCount = dbSelectCall(
mysql, "SELECT COUNT(*) FROM items WHERE public=1;")
pageData = [
"", pageQuery, dbresponseItemCount[0][0], itemsPerPage, sortQuery,
bazarQuery
]
else:
searchQuery = searchQuery.replace("'", "")
if bazarQuery != "any":
dbresponseItems = dbSelectCall(
mysql,
f"""SELECT heading, imgurl, itemid, url, bazar FROM items
WHERE heading LIKE '%{searchQuery}%' AND bazar = '{bazarQuery}' AND public=1 ORDER BY {sortCond}
LIMIT {(pageQuery-1)*itemsPerPage}, {itemsPerPage};""")
dbresponseItemCount = dbSelectCall(
mysql, f"""SELECT COUNT(*) FROM items
WHERE heading LIKE '%{searchQuery}%' AND bazar = '{bazarQuery}' AND public=1;"""
)
else:
dbresponseItems = dbSelectCall(
mysql,
f"""SELECT heading, imgurl, itemid, url, bazar FROM items
WHERE heading LIKE '%{searchQuery}%' AND public=1 ORDER BY {sortCond}
LIMIT {(pageQuery-1)*itemsPerPage}, {itemsPerPage};""")
dbresponseItemCount = dbSelectCall(
mysql, f"""SELECT COUNT(*) FROM items
WHERE heading LIKE '%{searchQuery}%' AND public=1;""")
pageData = [
searchQuery, pageQuery, dbresponseItemCount[0][0], itemsPerPage,
sortQuery, bazarQuery
]
if "username" in session:
userId = session["userId"]
dbresponse = dbSelectCallEsc(
mysql, "SELECT itemid FROM favourites WHERE userid = %s", [userId])
favItems = []
for i in dbresponse:
favItems.append(i[0])
return render_template("browse.html",
items=dbresponseItems,
loggedIn=True,
favItems=favItems,
pageData=pageData)
else:
return render_template("browse.html",
items=dbresponseItems,
loggedIn=False,
pageData=pageData)
def auth():
if "username" in session:
return redirect(url_for('profile'))
if request.method == "POST":
if "loginForm" in request.form:
name = request.form["user"]
passw = request.form["pass"]
dbresponse = dbSelectCallEsc(
mysql,
"SELECT password, userid, username FROM users WHERE username=%s;",
[name])
if len(dbresponse) == 1:
respo = check_password_hash(dbresponse[0][0], passw)
if respo == True:
session["username"] = dbresponse[0][2]
session["userId"] = dbresponse[0][1]
cur = mysql.connection.cursor()
cur.execute(
f"INSERT INTO logs (author, message) VALUES ('User {dbresponse[0][1]}', 'Logged in.');"
)
mysql.connection.commit()
cur.close()
return redirect(url_for('profile'))
else:
return render_template("login.html", authResult="BadLogin")
else:
return render_template("login.html", authResult="BadLogin")
elif "registerForm" in request.form:
name = request.form["user"]
passw = request.form["pass"]
if len(name) < 4:
return render_template("login.html",
authResult="UsernameTooShort")
if len(passw) < 5:
return render_template("login.html", authResult="PassTooShort")
cur = mysql.connection.cursor()
cur.execute(
"SELECT EXISTS(SELECT * FROM users WHERE username = %s);",
[name])
dbresponse = cur.fetchall()
if dbresponse[0][0] == 0:
hashPass = generate_password_hash(passw,
method="sha256",
salt_length=10)
cur.execute(
"INSERT INTO users (username, password) VALUES (%s, %s);",
[name, hashPass])
mysql.connection.commit()
cur.close()
dbresponse = dbSelectCallEsc(
mysql,
"SELECT userid, username FROM users WHERE username=%s;",
[name])
session["username"] = dbresponse[0][1]
session["userId"] = dbresponse[0][0]
cur = mysql.connection.cursor()
cur.execute(
f"INSERT INTO logs (author, message) VALUES ('User {dbresponse[0][0]}', 'Registered.');"
)
mysql.connection.commit()
cur.close()
return redirect(url_for('profile'))
else:
return render_template("login.html",
authResult="UsernameExists")
return render_template("login.html")
