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)