def rate():
user_email = session['userEmail']
cursor = conn.cursor()
page = 'index'
item = list(request.form.keys())[0]
# if the user is rating on the shared page, make page = sharedPosts
# otherwise, it is already initialized to index page
if 'share' in item:
page = 'sharedPosts'
cursor.rownumber = 0
# retrieve the item_id of what is being rated
rate_id = int(item.split('te')[-1])
# determine whether the rating already exists
query = "SELECT * FROM Rate WHERE item_id = %s AND email = %s"
cursor.execute(query, (rate_id, user_email))
rate_exist = cursor.fetchone()
cursor.rownumber = 0
# if rating already exists, uopdate the row
if rate_exist:
query = "UPDATE Rate SET rate_time = CURRENT_TIMESTAMP, emoji = %s WHERE item_id = %s AND email = %s"
cursor.execute(query, (request.form[item], rate_id, user_email))
# otherwise, insert a new row for the rating
else:
query = "INSERT INTO Rate (email, item_id, rate_time, emoji) VALUES (%s, %s, CURRENT_TIMESTAMP, %s)"
cursor.execute(query, (user_email, rate_id, request.form[item]))
conn.commit()
cursor.close()
return redirect(url_for(page))
def shareAction(owner_email, fg_name, postid):
query = "INSERT INTO share VALUES (%s, %s, %s)"
cursor = conn.cursor()
cursor.execute(query, (owner_email, fg_name, postid))
conn.commit()
cursor.close()
return redirect('/')
def post():
user_email = session['userEmail']
cursor = conn.cursor()
blog = request.form['content']
pub = request.form.get('pub')
file = request.files.getlist('image')
destination = None
if file:
contentType = "image"
# the three lines below are to create the path if it doesn't exist already
target = app.config['UPLOAD_FOLDER'] + '/images'
if not os.path.isdir(target):
os.makedirs(target)
# loop through submitted images
for file in request.files.getlist('image'):
filename = file.filename
destination = "/".join([target, filename])
else:
contentType = "text"
pub = True if pub else False
# This query just throws the post info we got into the contentitem table
query = 'INSERT INTO ContentItem(email_post, file_path, content_type, item_name, is_pub) VALUES(%s, %s, %s, %s, %s)'
cursor.execute(query, (user_email, destination, contentType, blog, pub))
# if there was a photo, make sure we saved it
if file:
file.save(destination)
# commit our changes
conn.commit()
cursor.close()
return redirect(url_for('index'))
def registerAuth():
# grabs information from the forms
fname = request.form['fname']
lname = request.form['lname']
user_email = request.form['userEmail']
password = request.form['password']
# cursor used to send queries
cursor = conn.cursor()
# executes query
query = 'SELECT * FROM PERSON WHERE email = %s'
cursor.execute(query, user_email)
data = cursor.fetchone()
if data:
error = "This user already exists"
cursor.close()
return render_template('signup.html', error=error)
else:
ins = 'INSERT INTO PERSON VALUES(%s, SHA2(%s, 256), %s, %s)'
cursor.execute(ins, (user_email, password, fname, lname))
conn.commit()
cursor.close()
return render_template('index.html')
def commentsubmit(postid):
if 'userEmail' in session:
comment_content = request.form['content']
query = 'INSERT INTO comments (content, commentor_email, item_id) VALUES (%s, %s, %s)'
cursor = conn.cursor()
cursor.execute(query, (comment_content, session['userEmail'], postid))
conn.commit()
cursor.close()
return redirect(url_for('comments', postid=postid))
def tag_auth():
user_email = session['userEmail']
cursor = conn.cursor()
item = list(request.form.keys())[0]
lst = item.split('@nyu.edu')
tagger, item_id = lst[0], int(lst[1])
status = request.form[item]
# if user accepted tag, update row in table to be true
if status == "Accept":
query = "UPDATE Tag SET status = 'True' WHERE email_tagger = %s" \
" AND email_tagged = %s AND item_id = %s"
# otherwise, delete the row from the table
else:
query = "DELETE FROM Tag WHERE email_tagger = %s" \
"AND email_tagged = %s AND item_id = %s"
cursor.execute(query, (tagger + '@nyu.edu', user_email, item_id))
conn.commit()
cursor.close()
return redirect(url_for('pending_tag'))
def createNewGroup():
user_email = session['userEmail']
groupName = request.form['groupName']
groupDesc = request.form['groupDesc']
cursor = conn.cursor()
# the query checks is the user already owns a group with the inputted name
checkQuery = 'SELECT fg_name FROM FriendGroup WHERE owner_email = %s AND fg_name = %s'
cursor.execute(checkQuery, (user_email, groupName))
groupData = cursor.fetchone()
# if group exits, send error
if groupData:
cursor.rownumber = 0
error = "You have already created a group with this name"
cursor.close()
return render_template('newGroup.html',
displayNewGroup="true",
error=error)
# the group doesn't exist so the group is created with the user as the owner
# the owner is automatically put into the Belong table so they are a member of the group they own
else:
newGroupQuery = 'INSERT INTO FriendGroup(owner_email, fg_name, description) VALUES (%s,%s,%s)'
cursor.execute(newGroupQuery, (user_email, groupName, groupDesc))
conn.commit()
cursor.rownumber = 0
addSelf = 'INSERT INTO Belong(email,owner_email,fg_name) VALUES (%s,%s,%s)'
cursor.execute(addSelf, (user_email, user_email, groupName))
conn.commit()
cursor.close()
# allows the user to add members to the group they just created
if request.form.get('AddMember') == 'AddMember':
return render_template('newGroup.html',
displayAddMember="true",
dispGroupName=groupName)
cursor.close()
return redirect(url_for('index'))
def removeMember(nameGroup):
if 'userEmail' in session:
useremail = session['userEmail']
cursor = conn.cursor()
# selects the members of the people who belong in the user's group
showMemQuery = 'SELECT * FROM Belong WHERE fg_name = %s AND email != %s AND owner_email=%s'
cursor.execute(showMemQuery, (nameGroup, useremail, useremail))
memNames = cursor.fetchall()
# if there are members in the group
# nameData is an array with the first and last names of people in the group
if memNames:
nameData = []
for x in range(len(memNames)):
searchEmail = memNames[x]['email']
# query to select first and last name of associated email
nameQuery = 'SELECT * FROM Person WHERE email = (%s)'
cursor.execute(nameQuery, searchEmail)
nameData.extend(cursor.fetchall())
cursor.close()
return render_template('removeMember.html',
memNames=memNames,
nameGroup=nameGroup,
nameData=nameData,
user=useremail)
else:
# if there are no members the group will be deleted
cursor = conn.cursor()
# delete everyone from belong
deleteQuery4 = 'DELETE FROM Belong WHERE owner_email = %s AND fg_name = %s'
cursor.execute(deleteQuery4, (useremail, nameGroup))
conn.commit()
conn.rownumber = 0
# delete the shared content
deleteQuery5 = 'DELETE FROM Share WHERE owner_email = %s AND fg_name = %s'
cursor.execute(deleteQuery5, (useremail, nameGroup))
conn.commit()
conn.rownumber = 0
# delete the group
deleteQuery6 = 'DELETE FROM FriendGroup WHERE owner_email = %s AND fg_name = %s'
cursor.execute(deleteQuery6, (useremail, nameGroup))
conn.commit()
cursor.close()
# error = "There is no one in this group. Return home to add people"
return redirect('/')
else:
return redirect('/')
def tag():
user_email = session['userEmail']
cursor = conn.cursor()
item = list(request.form.keys())[0]
taggee = request.form[item]
cursor.rownumber = 0
tag_id = int(item.split('d')[-1])
# check to see if email to be tagged exist
query = "SELECT * FROM Person WHERE email = %s"
cursor.execute(query, taggee)
tag_email_exist = cursor.fetchone()
# generate error if email does not exist on home page
if not tag_email_exist:
error = "This email has not been registered."
flash(error)
return redirect(url_for('index'))
# check to see if the tag already exists
query = "SELECT * FROM Tag WHERE item_id = %s AND email_tagger = %s AND email_tagged = %s"
cursor.execute(query, (tag_id, user_email, taggee))
tag_exist = cursor.fetchone()
cursor.rownumber = 0
# if tag does not exist
if not tag_exist:
# check to see if the content is public
query = "SELECT is_pub FROM ContentItem WHERE item_id = %s"
cursor.execute(query, tag_id)
is_public = cursor.fetchone()
cursor.rownumber = 0
if is_public['is_pub']:
status = "False"
# if tagging yourself, automatically accept the tag
if user_email == taggee:
status = "True"
# insert the pending/accepted tag into the tag table
query = "INSERT INTO Tag(email_tagged, email_tagger, item_id, status) VALUES (%s, %s, %s, %s)"
cursor.execute(query, (taggee, user_email, tag_id, status))
# if it's a private post, confirm that you are tagging someone who you already shared the post with
else:
query = "SELECT * FROM Belong NATURAL JOIN Share WHERE email = %s AND item_id = %s"
cursor.execute(query, (taggee, tag_id))
is_shared = cursor.fetchone()
# if post is shared, insert row into table
if is_shared:
query = "INSERT INTO Tag(email_tagged, email_tagger, item_id, status) VALUES (%s, %s, %s, %s)"
cursor.execute(query, (taggee, user_email, tag_id, "False"))
# otherwise, all cases failed
# generate the error to the home page
else:
error = "Tag request cannot be done."
flash(error)
return redirect(url_for('index'))
# generate error saying that email is already tagged
else:
error = "You already tagged " + taggee + " for this post!"
flash(error)
return redirect(url_for('index'))
conn.commit()
cursor.close()
return redirect(url_for('index'))
def addNewMember():
user_email = session['userEmail']
groupName = request.form['groupName']
newMemberF = request.form['newMemFname']
newMemberL = request.form['newMemLname']
duplicateTest = request.form['duplicateTest']
cursor = conn.cursor()
# check that the member you're adding exists
checkExist = 'SELECT * FROM Person WHERE fname = %s AND lname = %s'
cursor.execute(checkExist, (newMemberF, newMemberL))
memExist = cursor.fetchall()
# if there is more than one user, an email is required
if duplicateTest == "True":
cursor.rownumber = 0
newMemEmail = request.form['newMemEmail']
# check if they're already in your group
checkInQuery = 'SELECT * FROM Belong WHERE owner_email = %s AND fg_name = %s AND email = %s'
cursor.execute(checkInQuery, (user_email, groupName, newMemEmail))
memExistData2 = cursor.fetchall()
# if the member already in user's group, the user retrieves an error message
if memExistData2:
cursor.close()
error = "This person is already in your group"
return render_template('newGroup.html',
displayAddMember="true",
dispGroupName=groupName,
error=error)
# if the user exists and isn't in the group, they are added
else:
cursor.rownumber = 0
addMemQuery2 = 'INSERT INTO Belong (email, owner_email, fg_name) VALUES (%s, %s, %s)'
cursor.execute(addMemQuery2, (newMemEmail, user_email, groupName))
message = "you successfully added a member"
conn.commit()
cursor.close()
return render_template('newGroup.html',
displayAddMember="true",
dispGroupName=groupName,
message=message)
#if we don't know if there are duplicates, we have to check the how many people have the same name
else:
# if there is only one person with the inputted name
if len(memExist) == 1:
cursor.rownumber = 0
# if the member exists - check if they're already in your group
newMember = memExist[0]['email']
checkMemQuery = 'SELECT * FROM Belong WHERE owner_email = %s AND fg_name = %s AND email = %s'
cursor.execute(checkMemQuery, (user_email, groupName, newMember))
memExistData = cursor.fetchone()
# if they're already in your group send an error message
if memExistData:
error = "This person is already in your group"
cursor.close()
return render_template('newGroup.html',
displayAddMember="true",
dispGroupName=groupName,
error=error)
else:
# if the member exists and is not in group, add them to your group
cursor.rownumber = 0
addMemberQuery = 'INSERT INTO Belong (email, owner_email, fg_name) VALUES (%s, %s, %s)'
cursor.execute(addMemberQuery,
(newMember, user_email, groupName))
message = "You successfully added a member"
conn.commit()
cursor.close()
return render_template('newGroup.html',
displayAddMember="true",
dispGroupName=groupName,
message=message)
# if the user doesn't exist, an error message is sent
elif len(memExist) == 0:
error = "This person does not exist, try another name"
cursor.close()
return render_template('newGroup.html',
displayAddMember="true",
dispGroupName=groupName,
error=error)
# if the query returns something longer than one, that means there are multiple people with the same name
# will render the page with an error message and a list of the emails associated with that name
# the user must enter the correct email to add the person
else:
error = "There are multiple people with the same name. Enter the correct email and to move on"
return render_template('newGroup.html',
displayAddMember="true",
dispGroupName=groupName,
error=error,
duplicate="true",
memExist=memExist)
def deleteMember():
if 'userEmail' in session:
useremail = session['userEmail']
deletePerson = request.form['memberEmail']
fromGroup = request.form['deleteGroup']
# remove deletes a person from the selected group
if request.form.get('Remove') == 'Remove':
cursor = conn.cursor()
# delete a person from your group
deleteQuery1 = 'DELETE FROM Belong WHERE email = %s AND fg_name = %s AND owner_email = %s'
cursor.execute(deleteQuery1, (deletePerson, fromGroup, useremail))
conn.commit()
cursor.close()
return removeMember(fromGroup)
# if you want to completely sever your relationship with the person
elif request.form.get('Sever') == 'Sever':
cursor = conn.cursor()
# removes the person from all their groups
deleteQuery2 = 'DELETE FROM Belong WHERE email = %s AND owner_email = %s'
cursor.execute(deleteQuery2, (deletePerson, useremail))
conn.commit()
conn.rownumber = 0
# deletes the user from all of deleted persons group to sever friendship
deleteQuery3 = 'DELETE FROM Belong WHERE email = %s AND owner_email = %s'
cursor.execute(deleteQuery3, (useremail, deletePerson))
conn.commit()
cursor.close()
return removeMember(fromGroup)
# deletes the whole group which also deletes the shared content
elif request.form.get('Delete') == 'Delete':
cursor = conn.cursor()
# query delete everyone from belong table
deleteQuery4 = 'DELETE FROM Belong WHERE owner_email = %s AND fg_name = %s'
cursor.execute(deleteQuery4, (useremail, fromGroup))
conn.commit()
conn.rownumber = 0
# query deletes the shared content associated with the user's group
deleteQuery5 = 'DELETE FROM Share WHERE owner_email = %s AND fg_name = %s'
cursor.execute(deleteQuery5, (useremail, fromGroup))
conn.commit()
conn.rownumber = 0
# deletes the group from the database
deleteQuery6 = 'DELETE FROM FriendGroup WHERE owner_email = %s AND fg_name = %s'
cursor.execute(deleteQuery6, (useremail, fromGroup))
conn.commit()
cursor.close()
return redirect('/')
else:
return removeMember(fromGroup)
else:
return redirect('/')