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