def profile(username):
"""Show user's profile"""
# Get list containing dict with user info
users_list = db.execute(
"SELECT id, username, bday, pfp_url, gender, bday, date_joined, bio FROM users WHERE username=?",
username)
# Check if username in database. If not, return 404 error page
if not users_list:
return render_template("404.html")
# Dict containing info about user
user_info = users_list[0]
# Check if username belongs to user logged in
if user_info['id'] == session.get("user_id"):
isLoggedInUser = True
else:
isLoggedInUser = False
user_id = user_info['id']
# Alter dates so written Month Day, Year
user_info['bday'] = standard_date(user_info['bday'])
user_info['date_joined'] = standard_date(user_info['date_joined'])
# List of dicts with information about 5 of user's favorite anime
favorites = db.execute(
"SELECT title, image_url FROM anime JOIN favorites ON anime.id=favorites.anime_id WHERE user_id=? ORDER BY rank LIMIT 5",
user_id)
# List of dicts with information about 3 most recent shows a user has rated
show_history = db.execute(
"SELECT anime.title, ratings.rating, ratings.comment, ratings.time FROM ratings JOIN anime ON anime.id = ratings.anime_id WHERE user_id = ? ORDER BY time DESC LIMIT 3",
user_id)
# User-reached route via GET
return render_template("profile.html",
user_info=user_info,
favorites=favorites,
isLoggedInUser=isLoggedInUser,
show_history=show_history)
def community():
"""Show members of community"""
# List of dicts containing info about each user
users = db.execute(
"SELECT username, pfp_url, date_joined, substr(bio, 1, 200) AS bio FROM users ORDER BY LOWER(username)"
)
# Change date to Month Day, Year format
for user in users:
user['date_joined'] = standard_date(user['date_joined'])
# User-reached route via GET
return render_template("community.html", users=users)
def inbox():
"""User inbox"""
# User's id
user_id = session.get("user_id")
# List of users who have requested to become friends
friend_requests = db.execute(
"""SELECT users.username, users.pfp_url, users.id, friends.friend_date
FROM users JOIN friends ON users.id = friends.user_id
WHERE friends.friend_id = ? AND friends.pending = 1
ORDER BY friends.friend_date DESC""", user_id)
# List of pending watch party requests
watch_party_requests = db.execute(
"""SELECT users.username, watchparty.host, watchparty.watching, watchparty.date_created, watchparty.message,
watchparty.date, watchparty.time, users.pfp_url, watchparty.id
FROM users JOIN participants ON users.id = participants.participant JOIN watchparty ON participants.party_id = watchparty.id
WHERE participants.participant = ? AND participants.pending = 1
ORDER BY watchparty.date_created DESC""",
user_id)
# Update information corresponding to each watch party
for event in watch_party_requests:
host_info = db.execute(
"SELECT username, pfp_url FROM users WHERE id = ?",
event["host"])[0]
event["date"] = standard_date(event["date"])
event["host"] = host_info["username"]
event["pfp_url"] = host_info["pfp_url"]
# Messages to the user
messages = db.execute(
"""SELECT users.username, users.pfp_url, messages.message, messages.id, messages.subject, messages.date
FROM users JOIN messages ON users.id=messages.sender
WHERE messages.recipient=? AND messages.deleted_recipient = 0 ORDER BY date DESC""",
user_id)
return render_template("inbox.html",
friend_requests=friend_requests,
watch_party_requests=watch_party_requests,
messages=messages)
def friends():
"""Display friend list and add friends"""
user_id = session.get("user_id")
if request.method == "GET":
# List of friends
friends = db.execute(
"""SELECT users.username, users.pfp_url, users.id, friends.friend_date
FROM users JOIN friends ON users.id = friends.user_id
WHERE friends.friend_id = ? AND friends.pending = 0
ORDER BY friends.friend_date DESC""", user_id)
# Reformat Date
for friend in friends:
friend['friend_date'] = standard_date(
friend['friend_date'].split(" ")[0])
# Number of incoming friend requests
requests = db.execute(
"SELECT COUNT(user_id) FROM friends WHERE friend_id = ? AND pending = 1",
user_id)[0]['COUNT(user_id)']
return render_template("friends.html",
friends=friends,
requests=requests)
if request.method == "POST":
# List of friends
friend = db.execute(
"SELECT id, username FROM users WHERE LOWER(username) = ?",
request.form.get("friend").lower())
# Alert if user input does not exist
if not friend:
flash("User does not exist", 'warning')
return redirect("/friends")
friend_id = friend[0]['id']
# Alert if user input is the logged in user
if user_id == friend_id:
flash("Cannot add yourself", 'warning')
return redirect("/friends")
# Store info about input user if they have sent a request
incoming_request = db.execute(
"SELECT user_id, pending FROM friends WHERE user_id = ? AND friend_id = ?",
friend_id, user_id)
# Alert if user needs to respond to request from the input user
if incoming_request and incoming_request[0]['pending']:
flash("Respond to " + friend[0]['username'] + "'s friend request",
'warning')
return redirect("/friend-requests")
# Store info about input user if already a friend/sent a request already
potential_friend = db.execute(
"SELECT pending FROM friends WHERE user_id = ? AND friend_id = ?",
user_id, friend_id)
# Alert if friends already or the user sent a request already and has not received a response
if potential_friend:
if potential_friend[0]['pending']:
flash("Request to " + friend[0]['username'] + " still pending",
'warning')
return redirect("/friends")
flash(
"You are already friends with " + friend[0]['username'] + "!",
'warning')
return redirect("/friends")
# Add info about friend request into database
db.execute("INSERT INTO friends (user_id, friend_id) VALUES(?, ?)",
user_id, friend[0]['id'])
flash("Friend request sent to " + friend[0]['username'] + "!",
'success')
return redirect("/friends")
def watchPartyRecords():
"""Display watch parties user is hosting and attending"""
user_id = session["user_id"]
date = EST_CURRENT_DATETIME.split("T")[0]
# hosting table on the page will update when there is at least one participant that accepts the request
hosting = db.execute(
"""SELECT watchparty.id, watchparty.watching, watchparty.host, watchparty.date, watchparty.time, watchparty.message, GROUP_CONCAT(participants.participant) participants
FROM watchparty JOIN participants ON watchparty.id=participants.party_id
WHERE watchparty.host = ? AND participants.pending = 0 AND DATE(watchparty.date,'+1 day') >= ?
GROUP BY watchparty.id""", user_id, date)
# pending table on the page will update when there is at least one participant that is still pending for a watch party
allPending = db.execute(
"""SELECT watchparty.id, watchparty.watching, watchparty.host, watchparty.date, watchparty.time, GROUP_CONCAT(participants.participant) participants
FROM watchparty JOIN participants ON watchparty.id=participants.party_id
WHERE watchparty.host = ? AND participants.pending = 1 AND DATE(watchparty.date,'+1 day') >= ?
GROUP BY watchparty.id""", user_id, date)
# if null, unavailable participants table updates (all participants canceled or denied the watchparty)
available = db.execute(
"""SELECT watchparty.id
FROM watchparty JOIN participants ON watchparty.id=participants.party_id
WHERE watchparty.host = ? AND (participants.pending = 1 OR participants.pending = 0) AND DATE(watchparty.date,'+1 day') >= ?
GROUP BY watchparty.id""", user_id, date)
if not available:
noneAvailable = db.execute(
"""SELECT watchparty.id, watchparty.watching, watchparty.host, watchparty.date, watchparty.time
FROM watchparty JOIN participants ON watchparty.id=participants.party_id
WHERE watchparty.host = ? AND (participants.pending = 2 OR participants.pending = 3) AND DATE(watchparty.date,'+1 day') >= ?
GROUP BY watchparty.id""", user_id, date)
else:
noneAvailable = None
# Reformat date and add participant usernames for non-pending watch parties
for event in hosting:
event["date"] = standard_date(event["date"])
participants = event["participants"].split(",")
event["participants"] = ""
for participant in participants:
friend = db.execute("SELECT username FROM users WHERE id=?",
int(participant.replace("'",
"")))[0]["username"]
event["participants"] = event["participants"] + [
', ', ""
][event["participants"] == ""] + friend
# Reformat date and add participant usernames for pending
for event in allPending:
event["date"] = standard_date(event["date"])
participants = event["participants"].split(",")
event["participants"] = ""
for participant in participants:
friend = db.execute("SELECT username FROM users WHERE id=?",
int(participant.replace("'",
"")))[0]["username"]
event["participants"] = event["participants"] + [
', ', ""
][event["participants"] == ""] + friend
# participating table on the watch party main page will update with watch parties the user is attending
joining = db.execute(
"""SELECT watchparty.id, watchparty.host, watchparty.host host_name, watchparty.host host_pfp, watchparty.watching, watchparty.date, watchparty.time, watchparty.message, participants.participant
FROM watchparty JOIN participants ON watchparty.id=participants.party_id
WHERE participants.participant = ? AND DATE(watchparty.date,'+1 day') >= ? AND pending = 0""",
user_id, date)
# Reformat Date
for event in joining:
event["date"] = standard_date(
event["date"]) + " at " + event["time"] + " ET"
# change host info from number ids to text usernames + pfp_url
host_info = db.execute(
"SELECT username, pfp_url FROM users WHERE id = ?",
event["host"])[0]
event["host_name"] = host_info["username"]
event["host_pfp"] = host_info["pfp_url"]
return render_template("watchparty_main.html",
hosting=hosting,
allPending=allPending,
noneAvailable=noneAvailable,
joining=joining,
user_id=user_id)
def createWatchParty():
"""Create new watch party"""
# load friend list of the user
user_id = session.get("user_id")
friend_usernames = db.execute(
"SELECT username, id FROM users WHERE id IN (SELECT friend_id FROM friends WHERE user_id = ? AND pending = 0) ORDER BY username ASC",
user_id)
if request.method == "POST":
room_link = request.form.get("room-link")
# warning if no friend is selected
if not request.form.getlist("friend"):
flash("Enter a friend's username!", 'warning')
return redirect("/create-watch-party")
# check if the user already added friend(s) for the same watch party
# helpful source: https://stackoverflow.com/questions/13207697/how-to-remove-square-brackets-from-list-in-python
friends = (", ".join(
repr(e) for e in request.form.getlist("friend"))).replace("'", "")
if not request.form.get("title"):
flash("Add a title!", 'warning')
return redirect("/create-watch-party")
# Title entered by user in lowercase + remove english title if user used autocomplete
title = request.form.get("title").lower().split(" (english: ")[0]
# check if title exists (case insensitive)
check_title = db.execute(
"SELECT title, id FROM anime WHERE LOWER(title) = ? OR LOWER(title_english) = ?",
title, title)
if not check_title:
flash("Invalid title. Please try again.", 'warning')
return redirect("/create-watch-party")
# store the title as seen in the "title" column (not "title_english") of the anime table
real_title = check_title[0]["title"]
if "https://app.kosmi.io/room/" not in room_link:
flash("Enter a valid link!", 'warning')
return redirect("/create-watch-party")
if not request.form.get("meeting-time"):
flash("Fill out the event date & time!")
return redirect("/create-watch-party")
# check if the user is hosting another watch party at the same time
# (if the user forgot to add a friend to the invite, they can delete their watch party and create a new one)
date = request.form.get("meeting-time").split("T")[0]
time = request.form.get("meeting-time").split("T")[1]
alreadyHosting = db.execute(
"SELECT date, time FROM watchparty WHERE date = ? AND time = ? AND host = ?",
date, time, user_id)
if alreadyHosting:
flash("You are already hosting a watch party at this time!",
'warning')
return redirect("/create-watch-party")
message = request.form.get("message")
if not message:
message = "Hey! I'm planning on watching " + real_title + " on " + \
standard_date(date) + " at " + time + " ET!\nHere's the link if you want to join: " + room_link
if room_link not in message:
flash(
"At a minimum, your message must include the kosmi room link!",
'warning')
return redirect("/create-watch-party")
db.execute(
"INSERT INTO watchparty (host, watching, message, date, time) VALUES (?, ?, ?, ?, ?)",
user_id, real_title, message, date, time)
party_id = db.execute(
"SELECT id FROM watchparty WHERE host = ? AND date = ? AND time = ?",
user_id, date, time)[0]["id"]
# pending requests
for friend in request.form.getlist("friend"):
recipient = db.execute("SELECT id FROM users WHERE username = ?",
friend)[0]["id"]
db.execute(
"INSERT INTO participants (party_id, participant, pending) VALUES (?, ?, ?)",
party_id, recipient, 1)
# Alert user that invitation has been sent
flash("Invitation sent! Recipients: " + friends, 'success')
# User-reached route via POST
return redirect("/watch-party")
else:
# Redirect user to home page
return render_template("watchparty_request.html",
friend_usernames=friend_usernames,
current_date=EST_CURRENT_DATETIME)