def getMessages(conversationId):
try:
connection = connectToDB()
if(connection != False):
cursor = connection.cursor(buffered=True)
sql = f"SELECT p.firstname, m.content, m.timeStamp \
FROM Messages m, Profile p \
WHERE conversationId = {conversationId} AND m.fromUser = p.userId \
ORDER BY timeStamp"
cursor.execute(sql)
firstnames = []
messages = []
timeStamps = []
for pos in cursor.fetchall():
firstnames.append(pos[0])
messages.append(pos[1])
timeStamps.append(pos[2])
d = {"response": "Success", "fromNames": firstnames, "messages": messages, "timeStamps": timeStamps}
connection.commit()
cursor.close()
return d
except mysql.connector.Error as err:
return {"response": err.msg }
def swipeDecision(currentUserId, shownUserId, userDecision):
try:
connection = connectToDB()
if (connection != False):
userDecisionCast = userDecision == 'true'
cursor = connection.cursor(buffered=True)
updateSwipeDecisionQuery = "UPDATE PotentialMatch SET matchDecision = %s WHERE shownUser = %s and currentUser = %s"
insertRow = (
userDecisionCast,
shownUserId,
currentUserId,
)
cursor.execute(updateSwipeDecisionQuery, insertRow)
connection.commit()
cursor.close()
if userDecision == 'true':
insertConvo(currentUserId, shownUserId)
return {"response": "Success"}
except mysql.connector.Error as err:
return {"response": err.msg}
return {"response": "Something went wrong"}
def matchUser(userId):
try:
connection = connectToDB()
if (connection != False):
cursor = connection.cursor(buffered=True)
sql = "SELECT firstname from Profile where userId in \
(SELECT distinct userTwo from Conversation where userOne = %s)"
cursor.execute(sql, (int(userId), ))
result = [pos[0] for pos in cursor.fetchall()]
return {"response": "Success", "potentialUserIds": result}
except mysql.connector.Error as err:
return {"response": err.msg}
def sendMessage(convoId, friendConvoId, currentId, friendId, message):
try:
connection = connectToDB()
if(connection != False):
cursor = connection.cursor(buffered=True)
addToMessageTable(convoId, friendConvoId, currentId, friendId, message, cursor)
connection.commit()
cursor.close()
return {"response": "Success"}
except mysql.connector.Error as err:
return {"response": err.msg }
return{"response": "Something went wrong"}
def getPotentialMatchList(currentUserId):
try:
connection = connectToDB()
if (connection != False):
cursor = connection.cursor(buffered=True)
PotentialMatchQuery = "SELECT shownUser FROM PotentialMatch WHERE currentUser = %s and matchDecision IS NULL"
userID = (currentUserId, )
cursor.execute(PotentialMatchQuery, userID)
potentialListId = [i[0] for i in cursor.fetchall()]
connection.commit()
cursor.close()
return {"response": "Success", "potentialListId": potentialListId}
except mysql.connector.Error as err:
return {"response": err.msg}
return {"response": "Something went wrong"}
def getConversationIds(currentUser, friendUser):
try:
connection = connectToDB()
if(connection != False):
cursor = connection.cursor(buffered=True)
sql = f"SELECT conversationId FROM Conversation WHERE userOne = {currentUser} AND userTwo = {friendUser}"
cursor.execute(sql)
currentId = cursor.fetchone()[0]
sql = f"SELECT conversationId FROM Conversation WHERE userOne = {friendUser} AND userTwo = {currentUser}"
cursor.execute(sql)
friendId = cursor.fetchone()[0]
d = {"response": "Success", "currentConvoId": currentId, "friendConvoId": friendId}
return d
except mysql.connector.Error as err:
return {"response": err.msg }
def insertConvo(userOne, userTwo):
try:
connection = connectToDB()
if (connection != False):
cursor = connection.cursor(buffered=True)
insertConvoRowsQuery = "INSERT INTO Conversation (userOne, userTwo) \
SELECT p1.currentUser, p1.shownUser FROM PotentialMatch p1 \
WHERE (p1.currentUser = %s or p1.currentUser = %s) AND p1.matchDecision = 1 AND EXISTS \
(SELECT * FROM PotentialMatch p2 \
WHERE p2.shownUser = p1.currentUser AND p2.currentUser = p1.shownUser AND p2.matchDecision = 1)"
cursor.execute(insertConvoRowsQuery, (
userOne,
userTwo,
))
connection.commit()
cursor.close()
return
except mysql.connector.Error as err:
return {"response": err.msg}
def updateProfile(name, age, bio, gender, education, interests,
genderPreference, id):
try:
connection = connectToDB()
if (connection != False):
cursor = connection.cursor(buffered=True)
sql = '''INSERT INTO Profile (userId, firstname, age, description, gender, workplace, interests, genderPreference)
VALUES (%s, %s, %s,%s,%s,%s,%s, %s);'''
values = (id, name, age, bio, gender, education, interests,
genderPreference)
cursor.execute(sql, values)
connection.commit()
cursor.close()
return {"response": "Success"}
except mysql.connector.Error as err:
return {"response": err.msg}
return {"response": "Something went wrong creating profile"}
def registerUser(email, password):
try:
connection = connectToDB()
if (connection != False):
cursor = connection.cursor(buffered=True)
if (invalidEmail(email, cursor)):
return {"response": "Email address is already in use"}
sql = "INSERT INTO Users (location, email, password) VALUES ('', %s, %s);"
values = (email, password)
cursor.execute(sql, values)
id = cursor.lastrowid
connection.commit()
cursor.close()
return {"response": "Success", "id": id}
except mysql.connector.Error as err:
return {"response": err.msg}
return {"response": "Something went wrong"}
def matchUser(userId):
try:
connection = connectToDB()
if(connection != False):
cursor = connection.cursor(buffered=True)
sql = "SELECT distinct p.userId, p.firstname \
from Profile p where p.userId in \
(SELECT userOne FROM Conversation WHERE userTwo = %s)"
cursor.execute(sql, (userId,))
userIds = []
firstnames = []
for pos in cursor.fetchall():
if pos[0] not in userIds:
userIds.append(pos[0])
firstnames.append(pos[1])
currentName = currentUserName(userId, cursor)
d = {"response": "Success", "userIds": userIds, "firstnames": firstnames, "currentName": currentName}
return d
except mysql.connector.Error as err:
return {"response": err.msg }
def loginUser(email, password):
try:
connection = connectToDB()
if (connection != False):
cursor = connection.cursor(buffered=True)
if (invalidEmail(email, cursor, connection)):
return {"response": "No account with that Email"}
userId = getId(email, password, cursor, connection)
connection.commit()
cursor.close()
if (userId == -1):
return {"response": "Incorrect Password"}
return {"response": "Success", "id": userId}
# need to return ID
except mysql.connector.Error as err:
return {"response": err.msg}
return {"response": "Something went wrong"}
def showProfile(shownUserId):
try:
connection = connectToDB()
if (connection != False):
cursor = connection.cursor(buffered=True)
shownUserProfileQuery = "SELECT firstname, interests, description, age, gender, workPlace FROM Profile WHERE userId = %s"
userID = (shownUserId, )
cursor.execute(shownUserProfileQuery, userID)
if (cursor.rowcount < 1):
return {"response": "Error loading user profile"}
result = cursor.fetchall()
for row in result:
firstName = row[0]
interests = row[1]
description = row[2]
age = row[3]
gender = row[4]
workPlace = row[5]
connection.commit()
cursor.close()
return {
"response": "Success",
"firstName": firstName,
"interests": interests,
"description": description,
"age": age,
"gender": gender,
"workPlace": workPlace
}
except mysql.connector.Error as err:
return {"response": err.msg}
return {"response": "Something went wrong"}
def insertConvo(userOne, userTwo):
try:
connection = connectToDB()
if (connection != False):
cursor = connection.cursor(buffered=True)
sql = "IF EXISTS (SELECT * from PotentialMatch p1 where \
(currentUser = %s and matchDecision = 1 and potentialUser = %s) AND \
(SELECT * from PotentialMatch p2 where currentUser = %s and matchDecision = 1 and potentialUser = %s) \
THEN (INSERT INTO Conversation (userOne, userTwo) VALUES (%s, %s), INSERT INTO Conversation (userTwo, userOne) Values (%s, %s))"
cursor.execute(sql, (
userOne,
userTwo,
userTwo,
userOne,
userOne,
userTwo,
userTwo,
userOne,
))
return
except mysql.connector.Error as err:
return {"response": err.msg}
def updateQuestionnaire(response, id):
try:
connection = connectToDB()
if (connection != False):
cursor = connection.cursor(buffered=True)
sql = '''INSERT INTO Questionnaire(userId, response1, response2, response3, response4, response5, response6, response7, response8, response9, response10, response11, response12, response13, response14, response15, response16)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s);'''
values = (id, response[0], response[1], response[2], response[3],
response[4], response[5], response[6], response[7],
response[8], response[9], response[10], response[11],
response[12], response[13], response[14], response[15])
cursor.execute(sql, values)
connection.commit()
cursor.close()
return {"response": "Success"}
except mysql.connector.Error as err:
return {"response": err.msg}
return {
"response":
"Something went wrong with receiving the questionnaire responses"
}
def findPotentialMatches(id):
try:
connection = connectToDB()
if (connection != False):
cursor = connection.cursor(buffered=True)
genderPreference = getGenderPreference(id, cursor, connection)
if genderPreference == "":
return {
"response": "No Matches",
"id": id,
"Number of Matches": 0
}
potentialIds = getPotentialIds(id, genderPreference, cursor,
connection)
if len(potentialIds) == 0:
return {
"response": "No Matches",
"id": id,
"Number of Matches": 0
}
responses = getQuestionnaireResponses(potentialIds, cursor,
connection)
if len(responses) == 0:
return {
"response": "No Matches",
"id": id,
"Number of Matches": 0
}
matchList = getPotentialMatches(id, responses, cursor, connection,
0.8)
if len(matchList) == 0:
return {
"response": "No Matches",
"id": id,
"Number of Matches": 0
}
sql = "INSERT INTO PotentialMatch (currentUser, shownUser) VALUES (%s, %s)"
values = matchList
cursor.executemany(sql, values)
connection.commit()
cursor.close()
return {"response": "Success", "numOfMatches": len(matchList)}
except mysql.connector.Error as err:
return {"response": err.msg}
#error handling in case there are no questionnaire entries in the database for a user
except TypeError:
return {
"response": "Questionnaire was not filled out",
"id": id,
"Number of Matches": 0
}
return {"response": "Something went wrong"}