def getDialogLastMessages(current_uid, other_uid, number):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = """
SELECT * FROM messages WHERE
(
(receiver_id = %s AND sender_id = %s AND isDeletedByReceiver = 0)
OR
(receiver_id = %s AND sender_id = %s AND isDeletedBySender = 0)
)
ORDER BY mid DESC LIMIT %s"""
cursor.execute(
query,
(current_uid, other_uid, other_uid, current_uid, number))
result = cursor.fetchall()
#Marks as read
query = """
UPDATE messages SET isRead = 1
WHERE
receiver_id = %s AND sender_id = %s
"""
cursor.execute(query, (current_uid, other_uid))
connection.commit()
except Exception as e:
print(e)
return None
finally:
cursor.close()
connection.close()
return result
def addUserSkill(uid, skill):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
if SkillModel.isThereThisSkill(skill):
skid = SkillModel.getSkillByName(skill)["skid"]
else:
#Adding skill to the skills table
query = "INSERT INTO skills(name) VALUES(%s)"
cursor.execute(query, (skill,) )
#Getting new added skill id
skid = cursor.lastrowid
#Adding user skill
query = "INSERT INTO userSkills(uid, skid) VALUES(%s, %s)"
cursor.execute(query, (uid, skid) )
connection.commit()
except Exception as e:
print(e)
return None
finally:
cursor.close()
connection.close()
return skid
def updateFullDescription(pid, fullDescription):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
query = "UPDATE projects SET full_description = %s WHERE pid = %s"
cursor.execute(query, (fullDescription, pid))
connection.commit()
cursor.close()
connection.close()
def rejectSeaterAspiration(uid, sid):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "UPDATE seaterAspirations SET isRejected = 1 WHERE uid = %s AND sid = %s"
cursor.execute(query, (uid, sid))
connection.commit()
except Exception as e:
print(e)
cursor.close()
connection.close()
def updateProjectPostComment(ppcid, comment):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "UPDATE projectPostComments SET comment = %s WHERE ppcid = %s"
cursor.execute(query, (comment, ppcid))
connection.commit()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def cancelAspirationToTheSeater(uid, sid):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "DELETE FROM seaterAspirations WHERE uid = %s AND sid = %s"
cursor.execute(query, (uid, sid))
connection.commit()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def aspireSeater(uid, sid):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "INSERT INTO seaterAspirations(sid, uid) VALUES(%s, %s)"
cursor.execute(query, (sid, uid))
connection.commit()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def assignUser(uid, sid):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "UPDATE seaters SET uid = %s WHERE sid = %s"
cursor.execute(query, (uid, sid))
connection.commit()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def removeSeater(sid):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "DELETE FROM seaters WHERE sid = %s"
cursor.execute(query, (sid, ))
connection.commit()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def follow(followerId, followedId):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "INSERT INTO followers(flwrid, flwdid) VALUES(%s, %s)"
cursor.execute(query, (followerId, followedId))
connection.commit()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def unFollow(followerId, followedId):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "DELETE FROM followers WHERE flwrid = %s AND flwdid = %s"
cursor.execute(query, (followerId, followedId))
connection.commit()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def verifyEmail(email):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "UPDATE users SET isEmailVerified = 1 WHERE email = %s"
cursor.execute(query, (email,) )
connection.commit()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def updateBio(uid, bio):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "UPDATE users SET bio = %s WHERE uid = %s"
cursor.execute(query, (bio, uid))
connection.commit()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def updateEmail(uid, email):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "UPDATE users SET email = %s, isEmailVerified = 0 WHERE uid = %s"
cursor.execute(query, (email, uid))
connection.commit()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def addProjectPost(uid, pid, post):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "INSERT INTO projectPosts(uid, pid, post) VALUES(%s, %s, %s)"
cursor.execute(query, (uid, pid, post))
connection.commit()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def unlikeProjectPostComment(uid, ppcid):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "DELETE FROM projectPostCommentLikes WHERE uid = %s AND ppcid = %s"
cursor.execute(query, (uid, ppcid))
connection.commit()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def updateFullname(uid, fullname):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "UPDATE users SET full_name = %s WHERE uid = %s"
cursor.execute(query, (fullname, uid))
connection.commit()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def updateProjectLink(plid, name, link):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "UPDATE projectLinks SET name = %s, link = %s WHERE plid = %s"
cursor.execute(query, (name, link, plid))
connection.commit()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def removeUserSkill(uid, skid):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "DELETE FROM userSkills WHERE uid = %s AND skid = %s"
cursor.execute(query, (uid, skid) )
connection.commit()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def updateShortDescription(pid, shortDescription):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "UPDATE projects SET short_description = %s WHERE pid = %s"
cursor.execute(query, (shortDescription, pid))
connection.commit()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def removeProjectLink(plid):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "DELETE FROM projectLinks WHERE plid = %s"
cursor.execute(query, (plid, ))
connection.commit()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def updateProjectName(pid, name):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "UPDATE projects SET project_name = %s WHERE pid = %s"
cursor.execute(query, (name, pid))
connection.commit()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def removeContactMessage(cmid):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "DELETE FROM contactMessages WHERE cmid = %s"
cursor.execute(query, (cmid, ))
connection.commit()
except Exception as e:
print(e)
return
finally:
cursor.close()
connection.close()
def addUser(user):
user["password"] = sha256_crypt.encrypt(user["password"])
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "INSERT INTO users(email, username, password, full_name) VALUES(%s, %s, %s, %s)"
cursor.execute(query, (user["email"], user["username"], user["password"], user["full_name"]) )
connection.commit()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def removeSeaterSkill(sid, skillName):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = """DELETE FROM seaterSkills WHERE sid = %s AND skid =
(SELECT skid FROM skills WHERE name = %s)"""
cursor.execute(query, (sid, skillName) )
connection.commit()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def isThereThisProjectName(name):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "SELECT * FROM projects WHERE project_name = %s)"
result = cursor.execute(query, (name, ))
result = cursor.fetchone()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
return (result != None)
def isProjectAdmin(uid, pid):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "SELECT * FROM projectAdmins WHERE uid = %s AND pid = %s"
result = cursor.execute(query, (uid, pid))
result = cursor.fetchone()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
return (result != None)
def getProjectPostCommentNumber(ppid):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "SELECT COUNT(*) AS number FROM projectPostComments WHERE ppid = %s"
cursor.execute(query, (ppid, ))
result = cursor.fetchone()["number"]
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
return result
def updatePassword(uid, password):
password = sha256_crypt.encrypt(password)
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "UPDATE users SET password = %s WHERE uid = %s"
cursor.execute(query, (password, uid))
connection.commit()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def getSkillByName(name):
connection = Database.getConnection()
cursor = connection.cursor(dictionary=True)
try:
query = "SELECT * FROM skills WHERE name = %s"
result = cursor.execute(query, (name,) )
result = cursor.fetchone()
except Exception as e:
print(e)
return None
finally:
cursor.close()
connection.close()
return result