def home():
db = database.get_db()
with db.cursor() as cur:
total = database.get_total(cur)
multiplier = get_multipliers(date=datetime.date.today())
if multiplier:
multiplier = multiplier["factor"]
return render_template(
"index.html",
announcements=get_announcements(),
multiplier=multiplier,
alltimeleaderboard=[[
i[0] if len(i[0].split()) == 1 else ' '.join(
(i[0].split()[0], i[0].split()[-1])), i[1], i[2], i[3], i[4]
] for i in get_all_time_leaderboard()],
yesterdayleaderboard=[[
i[0] if len(i[0].split()) == 1 else ' '.join(
(i[0].split()[0], i[0].split()[-1])), i[1], i[2]
] for i in get_day_leaderboard(datetime.date.today())],
alltimeteamleaderboard=get_all_time_team_leaderboard(),
yesterdayteamleaderboard=get_day_team_leaderboard(
datetime.date.today()),
total=fancy_float(total),
)
def get_all_time_team_leaderboard():
db = database.get_db()
with db.cursor() as cur:
cur.execute("SELECT teamname, distance, id FROM teams;")
teamdistances = cur.fetchall()
teamdistances.sort(key=lambda team: team[1], reverse=True)
return [[i[0], fancy_float(i[1]), i[2]] for i in teamdistances]
def haspayed(email):
db = database.get_db()
with db.cursor() as cur:
cur.execute(
"SELECT * FROM payed WHERE email=%s;", (email,)
)
return cur.fetchone() != None
def get_all_time_leaderboard():
db = database.get_db()
with db.cursor() as cur:
cur.execute("SELECT username, distance, wrdsbusername, position, id FROM users;")
userdistances = cur.fetchall()
userdistances.sort(key=lambda user: user[1], reverse=True)
return [[i[0], fancy_float(i[1]), i[2], i[3], i[4]] for i in userdistances]
def create_team(userid):
if getteamname(userid) is not None:
return # Prevent creating a team if the user is on a team
db = database.get_db()
with db.cursor() as cur:
teamname = generate_team_name(cur)
# Create the team
cur.execute(
"INSERT INTO teams (teamname) VALUES (%s)", (teamname,)
)
# Get the team id
cur.execute(
"SELECT id FROM teams WHERE teamname=%s LIMIT 1", (teamname,)
)
teamid = cur.fetchone()[0]
# Add user into team
cur.execute(
"INSERT INTO team_members (id, memberid) VALUES (%s, %s)",
(teamid, userid)
)
cur.execute(
"UPDATE users SET teamid=%s WHERE id=%s",
(teamid, userid)
)
# Initialize the team distance
update_team_total(teamid=teamid)
db.commit()
def get_wrdsbusername_from_id(userid):
db = database.get_db()
with db.cursor() as cur:
cur.execute(
"SELECT wrdsbusername FROM users WHERE id=%s LIMIT 1;",
(userid,)
)
return cur.fetchone()[0]
def isadmin(userid):
db = database.get_db()
with db.cursor() as cur:
cur.execute("SELECT wrdsbusername, valid FROM admins WHERE id=%s;",
(userid, ))
row = cur.fetchone()
return (row[0] == get_wrdsbusername_from_id(userid) and row[1]
if row is not None else False)
def click():
if "email" not in session:
return "Fail"
db = get_db()
with db.cursor() as cur:
cur.execute("UPDATE test_users SET clicks=clicks+1 WHERE email=%s",
(str(session["email"])))
db.commit()
return "Success"
def autoload_day_all(date): # Autoload all users with google fit connected
db = database.get_db()
with db.cursor() as cur:
cur.execute("SELECT id, username FROM users WHERE googlefit=True;")
users = cur.fetchall()
for userid, username in users:
dist = autoload_day(userid, username, date, cur)
db.commit()
def getteamname_from_id(teamid):
db = database.get_db()
with db.cursor() as cur:
cur.execute(
"SELECT teamname, distance, joincode FROM teams WHERE id=%s LIMIT 1",
(teamid,)
)
res = cur.fetchone()
return (res[0], round(float(res[1]), 1), res[2]) if res is not None else None
def get_multipliers(date=None):
db = database.get_db()
with db.cursor() as cur:
if date:
cur.execute("SELECT multiplydate, factor FROM multipliers WHERE multiplydate=%s", (date,))
return cur.fetchone()
else:
cur.execute("SELECT multiplydate, factor FROM multipliers;")
return cur.fetchall()
def multiply_by_factor(date=date.today()):
multiplier = get_multipliers(date)
if multiplier:
db = database.get_db()
with db.cursor() as cur:
cur.execute(
"UPDATE walks SET distance=distance*%s WHERE walkdate=%s;",
(multiplier["factor"], date)
)
db.commit()
def get_edit_distance_data(wrdsbusername):
userid, username = get_credentials_from_wrdsbusername(wrdsbusername)
db = database.get_db()
with db.cursor() as cur:
cur.execute(
"SELECT walkdate, distance, trackedwithfit FROM walks WHERE id=%s;", (userid,)
)
allwalks = cur.fetchall()
allwalks.sort(key=lambda row: row[0])
return allwalks
def db_get_total():
global total
db = database.get_db()
with db.cursor() as cur:
total = database.get_total(cur)
if total:
total = total["distance"]
else:
total = 0
return total
def getteamid(userid):
db = database.get_db()
with db.cursor() as cur:
cur.execute(
"SELECT teamid FROM users WHERE id=%s LIMIT 1",
(userid,)
)
teamid = cur.fetchone()
if teamid is None:
return None
return teamid[0]
def get_day_leaderboard(date):
db = database.get_db()
with db.cursor() as cur:
cur.execute(
"SELECT username, distance, id FROM walks WHERE walkdate=%s;",
(date,)
)
userdistances = cur.fetchall()
userdistances.sort(key=lambda user: user[1], reverse=True)
userdistances = list(map(_convert_id_to_wrdsbusername, userdistances))
return [[i[0], fancy_float(i[1]), i[2]] for i in userdistances]
def profile():
if "email" not in session:
return redirect("/users/login")
db = get_db()
with db.cursor() as cur:
cur.execute(
"SELECT username, clicks FROM test_users WHERE email=%s LIMIT 1",
(session["email"]))
res = cur.fetchone()
return render_template("users.html", username=res[0], clicks=res[1])
def update_leaderboard_positions():
db = database.get_db()
leaderboard = get_all_time_leaderboard()
with db.cursor() as cur:
for i in range(len(leaderboard)):
if leaderboard[i][3] != i + 1 and leaderboard[i][1] > 0:
cur.execute("UPDATE users SET position=%s WHERE id=%s;",
(i + 1, leaderboard[i][4]))
elif leaderboard[i][3] != None and leaderboard[i][1] <= 0:
cur.execute("UPDATE users SET position=null WHERE id=%s;",
(leaderboard[i][4], ))
db.commit()
def walkapi_get_access(userid):
db = database.get_db()
with db.cursor() as cur:
cur.execute(
"SELECT walkapi_accesstoken, walkapi_refreshtoken, walkapi_expiresat FROM users WHERE id=%s",
(userid, ))
result = cur.fetchone()
access, refresh, expiresat = result[0], result[1], result[2]
if int(datetime.datetime.now().timestamp()) > expiresat - 300:
access = walkapi_refresh_access_token(refresh, cur)
db.commit()
return access
def home():
db = database.get_db()
with db.cursor() as cur:
total = database.get_total(cur)[0]
return render_template(
"index.html",
alltimeleaderboard=get_all_time_leaderboard(),
yesterdayleaderboard=get_day_leaderboard(
datetime.date.today() - datetime.timedelta(days=1)
),
total=fancy_float(total),
)
def isblacklisted(userid, email):
db = database.get_db()
with db.cursor() as cur:
cur.execute("SELECT wrdsbusername, valid FROM blacklist WHERE id=%s;",
(userid, ))
result = cur.fetchone()
if result is None:
cur.execute(
"SELECT id, valid FROM blacklist WHERE wrdsbusername=%s;",
(email.split("@")[0], ),
)
result = cur.fetchone()
return (result[0] in [userid, email.split("@")[0]] and result[1]
if result is not None else False)
def replace_walk_distances(distances, dates, olddistances, user, id):
db = database.get_db()
with db.cursor() as cur:
for i in range(len(dates)):
if distances[i] != olddistances[i]:
user.update_walk(distances[i],
dates[i],
None,
cur,
replace=True,
id=id)
print("Updated", user.id, "walk on", dates[i], "to be",
distances[i])
db.commit()
def autoload_day_all(date): # Autoload all users with Strava connected
db = database.get_db()
print("Autoloading all")
with db.cursor() as cur:
cur.execute("SELECT id, username, email FROM users WHERE googlefit=True ORDER BY id;")
users = cur.fetchall()
for userid, username, email in users:
try:
autoload_day(userid, username, email, date, cur)
print("ok")
except:
print("Something went wrong. Possibly revoked access token.")
db.commit()
print("Done autoloading all")
def get_credentials_from_wrdsbusername(wrdsbusername, cur=None):
if cur is None:
closecur = True
db = database.get_db()
cur = db.cursor()
else:
closecur = False
cur.execute(
"SELECT id, username FROM users WHERE wrdsbusername=%s LIMIT 1;",
(wrdsbusername,),
)
user = cur.fetchone()
if closecur:
cur.close()
return user[0], user[1]
def delete_team(teamid):
db = database.get_db()
with db.cursor() as cur:
# Remove team members
cur.execute(
"DELETE FROM team_members WHERE id=%s", (teamid,)
)
cur.execute(
"UPDATE users SET teamid=NULL where teamid=%s", (teamid,)
)
# Remove team
cur.execute(
"DELETE FROM teams WHERE id=%s", (teamid,)
)
db.commit()
def confirmlogin():
if "email" in session:
return redirect("/users")
db = get_db()
with db.cursor() as cur:
cur.execute(
"SELECT * FROM test_users WHERE email=%s AND password=%s",
(str(request.form["email"]), str(request.form["password"])))
res = cur.fetchone()
if not res:
flash("Either account doesn't exist or incorrect password")
return redirect("/users/login")
session["email"] = request.form["email"]
return redirect("/users")
def edit_distance_update(distance, date, wrdsbusername):
userid, username = get_credentials_from_wrdsbusername(wrdsbusername)
db = database.get_db()
with db.cursor() as cur:
cur.execute("SELECT distance FROM walks WHERE id=%s AND walkdate=%s;",
(userid, date))
olddistance = float(cur.fetchone()[0])
if olddistance != distance:
distancechange = distance - olddistance
cur.execute(
"UPDATE walks SET distance=%s, trackedwithfit=False WHERE id=%s AND walkdate=%s;",
(distance, userid, date))
cur.execute("UPDATE users SET distance=distance+%s WHERE id=%s;",
(distancechange, userid))
add_to_total(distancechange, cur)
db.commit()
def _get_walk_distance(id):
db = database.get_db()
walkdate = date.today()
with db.cursor() as cur:
cur.execute(
"SELECT distance FROM walks WHERE walkdate=%s AND id=%s LIMIT 1;",
(
walkdate,
id,
),
)
walk = cur.fetchone()
if walk is not None:
return int(walk[0])
else:
return 0
def getteamname(userid, joincode=False):
db = database.get_db()
with db.cursor() as cur:
cur.execute(
"SELECT teamid FROM users WHERE id=%s LIMIT 1",
(userid,)
)
teamid = cur.fetchone()
if teamid is None:
return None
teamid = teamid[0]
cur.execute(
"SELECT teamname, distance, joincode FROM teams WHERE id=%s LIMIT 1",
(teamid,)
)
res = cur.fetchone()
return ((res[0], round(float(res[1]), 1)) if not joincode else (res[:3])) if res is not None else None
def join_team(userid, joincode=None):
# If joincode is None, we need to leave the team we're on, otherwise, join the team
# Check that we're on a team/not on a team
teamname = getteamname(userid)
if (teamname is None) == (joincode is None):
return False
db = database.get_db()
with db.cursor() as cur:
if joincode is None: # If we're leaving a team
# Remove user from member table
cur.execute(
"DELETE FROM team_members WHERE memberid=%s", (userid,)
)
# Get user's teamid
teamid = getteamid(userid)
# Set user's teamid to NULL
cur.execute(
"UPDATE users SET teamid=NULL where id=%s", (userid,)
)
else: # If we're joining a team
# Get team id of joincode
cur.execute(
"SELECT id FROM teams WHERE joincode=%s LIMIT 1", (joincode,)
)
teamid = cur.fetchone()
if teamid is None:
return False # Fail if team doesn't exist
teamid = teamid[0]
# Add user to member table
cur.execute(
"INSERT INTO team_members (id, memberid) VALUES (%s, %s)",
(teamid, userid)
)
# Set user's teamid
cur.execute(
"UPDATE users SET teamid=%s WHERE id=%s",
(teamid, userid)
)
# Either way, update team distance
update_team_total(teamid=teamid)
db.commit()
return True
