def getComments(self, username):
try:
self.lastCommentId = 0
self.comments = []
commentTableConn = getConnection()
commentTableCursor = commentTableConn.cursor()
commentTableCursor.execute(
"""SELECT * FROM COMMENTTABLE WHERE friendUsername=%s;""",
(username, ))
commentTableConn.commit()
dbData = commentTableCursor.fetchall()
if dbData != None:
for comment in dbData:
myComment = Comment()
myComment.commentId = comment[0]
myComment.userId = comment[1]
myComment.userName = comment[2]
myComment.friendUsername = comment[3]
myComment.content = comment[4]
self.comments.append(myComment)
self.lastCommentId += 1
commentTableCursor.close()
commentTableConn.close()
except commentTableConn.Error as Error:
print(Error)
return self
def getLocations(self,username):
try:
self.lastLocationId = 0
self.myLocations = []
userMapConnection = getConnection();
userMapCursor = userMapConnection.cursor()
userMapCursor.execute("""SELECT * FROM USERMAPTABLE WHERE user_id=%s;""",(username,))
userMapConnection.commit()
dbData = userMapCursor.fetchall()
if dbData != None:
for locations in dbData:
userLocations = UserLocation()
userLocations.userName = locations[1]
userLocations.mapInfo = locations[2]
userLocations.locationLabel = locations[3]
userLocations.lat = locations[4]
userLocations.lng = locations[5]
userLocations.locationId=locations[0]
self.myLocations.append(userLocations)
location = self.myLocations[0]
self.lastLocationId += 1
userMapCursor.close()
userMapConnection.close()
except userMapConnection.Error as userMapError:
print(userMapError)
return self
def search(username, password):
conn = getConnection()
cursor = conn.cursor()
try:
cursor.execute(""" SELECT * FROM USERTABLE WHERE username= %s;""",
(username, ))
conn.commit()
dbData = cursor.fetchone()
if dbData is None:
status = 'There is no user with this username '
else:
if password == dbData[2]:
status = 'Success'
else:
status = 'Password is invalid'
except conn.Error as error:
print(error)
status = 'Password or Username is invalid'
cursor.close()
conn.close()
return status
def GetLocation(self, location_id):
connection = getConnection()
cursor = connection.cursor()
query = """ SELECT location_id,user_id,lat,long,photo,video,document FROM EVENTTABLE WHERE location_id= %s;"""
try:
cursor.execute(query, (location_id))
fetched_data = cursor.fetchone()
connection.commit()
if fetched_data is None:
status = 'Invalid or no location data'
connection.close()
return None
else:
location_id = fetched_data[0]
lat = fetched_data[1]
long = fetched_data[2]
photo = fetched_data[3]
video = fetched_data[4]
document = fetched_data[5]
location = Location(location_id, lat, long, photo, video,
document)
except connection.Error as error:
print(error)
connection.close()
return location
def GetLocations(self):
connection = getConnection()
cursor = connection.cursor()
query = """ SELECT * FROM EVENTTABLE ORDER BY event_id;"""
try:
cursor.execute(query)
fetched_data = cursor.fetchone()
if fetched_data is None:
status = 'No location data '
connection.close()
return None
lat = fetched_data[0]
long = fetched_data[1]
photo = fetched_data[2]
video = fetched_data[3]
document = fetched_data[4]
locations = [(Location(lat, long, photo, video, document))]
for row in cursor:
location_id, lat, long, photo, video, document = row
locations_row = [(Location(location_id, lat, long, photo,
video, document))]
locations += locations_row
connection.commit()
except connection.Error as error:
print(error)
connection.close()
return locations
def getNotifications(self, username):
try:
self.lastNotificationId = 0
self.myNotifications = []
notificationTableConn = getConnection()
notificationCursor = notificationTableConn.cursor()
notificationCursor.execute(
"""SELECT * FROM NOTIFICATIONTABLE WHERE friendUsername = %s;""",
(username, ))
notificationTableConn.commit()
dataFromDb = notificationCursor.fetchall()
if dataFromDb != None:
for notifications in dataFromDb:
notification = Notification()
notification.notificationId = notifications[0]
notification.requester = notifications[1]
notification.requested = notifications[2]
messageId = notifications[3]
if messageId:
notification.type = 'message'
notification.typeId = messageId
commentId = notifications[4]
if commentId:
notification.type = 'comment'
notification.typeId = commentId
self.myNotifications.append(notification)
self.lastNotificationId += 1
notificationCursor.close()
notificationTableConn.close()
except notificationTableConn.Error as error:
print(error)
return self
def getUsers(self):
conn = getConnection()
cursor = conn.cursor()
self.userTable = []
self.lastUserCounter = 0
try:
cursor.execute(""" SELECT * FROM USERTABLE;""")
conn.commit()
dbData = cursor.fetchall()
if dbData != None:
for users in dbData:
user = User()
user.userId = users[0]
user.username = users[1]
user.password = users[2]
user.email = users[3]
user.name = users[4]
user.surname = users[5]
self.userTable.append(user)
self.lastUserCounter += 1
cursor.close()
conn.close()
return User
except conn.Error as error:
print(error)
return 'Error'
return self
def deleteLocation(self, locationId, newAddress):
try:
userMapConnection = getConnection();
userMapCursor = userMapConnection.cursor()
userMapcursor.execute("""DELETE FROM USERMAPTABLE WHERE userMap_id=%d;""",(locationId,))
userMapConnection.commit()
except userMapConnection.Error as userMapError:
print(userMapError)
userMapConnection.close()
def updateLocationAddress(self, locationId, newAddress):
try:
userMapConnection = getConnection();
userMapCursor = userMapConnection.cursor()
userMapcursor.execute("""UPDATE USERMAPTABLE SET address=%s WHERE userMap_id=%d;""",(newaddress,locationId))
userMapConnection.commit()
userMapCursor.close()
userMapConnection.close()
except userMapConnection.Error as userMapError:
print(userMapError)
def updateLocationInformation(self, locationId, newInfo):
try:
userMapConnection = getConnection();
userMapCursor = userMapConnection.cursor()
userMapcursor.execute("""UPDATE USERMAPTABLE SET mapInformation=%s WHERE userMap_id=%d;""",(newInfo,locationId))
userMapConnection.commit()
except userMapConnection.Error as userMapError:
print(userMapError)
userMapConnection.close()
def updateFriends(self,friendId,newStatus):
try:
friendTableConnection = getConnection();
friendCursor = friendTableConnection.cursor()
friendCursor.execute("""UPDATE FRIENDSTABLE SET status=%s WHERE friendRecordId=%s;""",(newStatus,friendId))
friendTableConnection.commit()
except friendTableConnection.Error as Error:
print(Error)
friendTableConnection.close()
def DeleteLocation(self, location_id):
connection = getConnection()
cursor = connection.cursor()
query = """DELETE FROM MMAPTABLE WHERE location_id = %s"""
try:
cursor.execute(query, (location_id))
connection.commit()
except connection.Error as error:
print(error)
connection.close()
def UpdateLocation(self, event, event_id):
connection = getConnection()
cursor = connection.cursor()
query = """UPDATE MMAPTABLE SET lat = %s, long = %s, photo = %s, video = %s, document = %s WHERE location_id = %s"""
try:
cursor.execute(query, (location.lat, location.long, location.photo,
location.video, location.document))
connection.commit()
except connection.Error as error:
print(error)
connection.close()
def deleteUser(username):
connection = getConnection()
cursor = connection.cursor()
try:
cursor.execute("""DELETE FROM USERTABLE WHERE username=%s;""",
(username, ))
connection.commit()
connection.close()
except connection.Error as error:
print(error)
def deleteRelation(self, friendId ):
try:
friendTableConnection = getConnection();
friendCursor = friendTableConnection.cursor()
friendCursor.execute("""DELETE FROM FRIENDSTABLE WHERE friendRecordId=%s;""",(friendId,))
friendTableConnection.commit()
self.lastFriendId = 0
self.myFriends = []
except friendTableConnection.Error as Error:
print(Error)
friendTableConnection.close()
def addFriend(self, Friend):
self.lastFriendId+= 1
Friend.friendStatus = 'casualFriend'
self.myFriends.append(Friend)
try:
friendTableConnection = getConnection();
friendCursor = friendTableConnection.cursor()
friendCursor.execute("""INSERT INTO FRIENDSTABLE (user_id,firends_id,status) VALUES(%s,%s,%s);""", (Friend.userName,Friend.friendUsername,Friend.friendStatus))
friendTableConnection.commit()
friendCursor.close()
friendTableConnection.close()
except friendTableConnection.Error as Error:
print(Error)
def deleteNotification(self, notificationId):
try:
notificationTableConn = getConnection()
notificationCursor = notificationTableConn.cursor()
notificationCursor.execute(
"""DELETE FROM NOTIFICATIONTABLE WHERE notificationId =%s;""",
(notificationId, ))
notificationTableConn.commit()
notificationCursor.close()
notificationTableConn.close()
except notificationTableConn.Error as error:
print(error)
def sendMessageNotification(self, notification):
try:
notificationTableConn = getConnection()
notificationCursor = notificationTableConn.cursor()
notificationCursor.execute(
"""INSERT INTO NOTIFICATIONTABLE(user_name, friendUsername,messageId) VALUES(%s,%s,%s);""",
(notification.requester, notification.requested,
notification.typeId))
notificationTableConn.commit()
notificationCursor.close()
notificationTableConn.close()
except notificationTableConn.Error as error:
print(error)
def deleteComment(self, commentId):
try:
commentTableConn = getConnection()
commentTableCursor = commentTableConn.cursor()
commentTableCursor.execute(
"""DELETE FROM COMMENTTABLE WHERE commentId=%s;""",
(commentId, ))
commentTableConn.commit()
self.lastFriendId = 0
self.myFriends = []
except commentTableConn.Error as Error:
print(Error)
commentTableConn.close()
def sendMessage(self, message):
newMessage = Message()
newMessage.sender = message.sender
newMessage.receiver = message.receiver
newMessage.content = message.content
newMessage.status = 'normal'
try:
messageTableConnection = getConnection();
messageCursor = messageTableConnection.cursor()
messageCursor.execute("""INSERT INTO MESSAGETABLE (user_id,firends_id,content,status) VALUES(%s,%s,%s,%s);""", (message.sender,message.receiver,message.content,message.status))
messageTableConnection.commit()
messageCursor.close()
messageTableConnection.close()
except messageTableConnection.Error as Error:
print(Error)
def addComment(self, Comment):
self.lastCommentId += 1
self.comments.append(Comment)
try:
commentTableConn = getConnection()
commentTableCursor = commentTableConn.cursor()
commentTableCursor.execute(
"""INSERT INTO COMMENTTABLE (userId,user_name,friendUsername,content) VALUES(%s,%s,%s,%s);""",
(Comment.userId, Comment.userName, Comment.friendUsername,
Comment.content))
commentTableConn.commit()
commentTableCursor.close()
commentTableConn.close()
except commentTableConn.Error as Error:
print(Error)
def blockFriend(self,friendId,username):
try:
friendTableConnection = getConnection();
friendCursor = friendTableConnection.cursor()
friendCursor.execute("""SELECT * FROM FRIENDSTABLE WHERE friendRecordId=%s;""",(friendId,))
dbData = friendCursor.fetchone()
if dbData[1] == username:
friendCursor.execute("""UPDATE FRIENDSTABLE SET status=%s WHERE friendRecordId=%s;""",('blocked1',friendId))
if dbData[2] == username:
friendCursor.execute("""UPDATE FRIENDSTABLE SET status=%s WHERE friendRecordId=%s;""",('blocked2',friendId))
friendTableConnection.commit()
except friendTableConnection.Error as Error:
print(Error)
friendTableConnection.close()
def AddLocation(self, location):
self.lastid += 1
self.locations[self.lastid] = location
location._id = self.lastid
connection = getConnection()
cursor = connection.cursor()
query = """INSERT INTO MMAPTABLE (lat, long, photo, video, document) VALUES (%s, %s, %s, %s, %s)"""
try:
cursor.execute(query, (location.lat, location.long, location.photo,
location.video, location.document))
self.last_key = cursor.lastrowid
connection.commit()
except connection.Error as error:
print(error)
connection.close()
def setUserToDb(User):
connection = getConnection()
cursor = connection.cursor()
username = User.username
password = User.password
email = User.email
name = User.name
surname = User.surname
try:
cursor.execute(
"""INSERT INTO USERTABLE (username, password, email, name, surname) VALUES(%s,%s,%s,%s,%s);""",
(username, password, email, name, surname))
connection.commit()
connection.close()
except connection.Error as error:
print(error)
def updateUser(User):
connection = getConnection()
cursor = connection.cursor()
username = User.username
password = User.password
email = User.email
name = User.name
surname = User.surname
try:
cursor.execute(
"""UPDATE USERTABLE SET username=%s, password=%s,email=%s, name=%s, surname=%s WHERE username=%s;""",
(username, password, email, name, surname, username))
connection.commit()
connection.close()
except connection.Error as error:
print(error)
def getMessages(self,username):
try:
self.lastConversationId = 0
self.conversations = []
messageTableConnection = getConnection();
messageCursor = messageTableConnection.cursor()
messageCursor.execute("""SELECT * FROM MESSAGETABLE WHERE firends_id=%s OR user_id=%s;""",(username,username))
messageTableConnection.commit()
dbData = messageCursor.fetchall()
if dbData != None:
for messages in dbData:
myMessage = Message()
myMessage.messageId = messages[0]
myMessage.sender = messages[1]
myMessage.receiver = messages[2]
myMessage.content = messages[3]
myMessage.status = messages[4]
found = 'false'
if myMessage.status == 'deleted':
if username == myMessage.receiver:
continue
if self.conversations != None:
i = 0
while i < self.lastConversationId:
if self.conversations[i].sender == messages[1] or self.conversations[i].sender == messages[2]:
self.conversations[i].addMessages(myMessage)
found = 'true'
self.conversations[i].lastMessageId += 1
i += 1
if found == 'false':
newConversation = Conversation()
newConversation.addMessages(myMessage)
if myMessage.sender == username:
newConversation.sender = myMessage.receiver
else:
newConversation.sender = myMessage.sender
self.conversations.append(newConversation)
self.lastConversationId += 1
messageCursor.close()
messageTableConnection.close()
except messageTableConnection.Error as Error:
print(Error)
return self
def updateAndDeleteMessages(self,messageId,username):
try:
messageTableConnection = getConnection();
messageCursor = messageTableConnection.cursor()
messageCursor.execute("""SELECT * FROM MESSAGETABLE WHERE messageId=%s;""",(messageId,))
dbData = messageCursor.fetchone()
if dbData[1] == username:
messageCursor.execute("""DELETE FROM MESSAGETABLE WHERE messageId=%s;""",(messageId,))
else:
newContent = 'deleted'
messageCursor.execute("""UPDATE MESSAGETABLE SET status=%s WHERE messageId=%s;""",(newContent,messageId))
messageTableConnection.commit()
except messageTableConnection.Error as Error:
print(Error)
messageTableConnection.close()
def getFriends(self,username):
try:
self.lastFriendId = 0
self.myFriends = []
friendTableConnection = getConnection();
friendCursor = friendTableConnection.cursor()
friendCursor.execute("""SELECT * FROM FRIENDSTABLE WHERE user_id=%s;""",(username,))
friendTableConnection.commit()
dbData = friendCursor.fetchall()
if dbData != None:
for friends in dbData:
myFriend = Friend()
myFriend.friendId = friends[0]
myFriend.userName = friends[1]
myFriend.friendUsername = friends[2]
if friends[3] != 'blocked2':
if friends[3] == 'blocked1':
myFriend.friendStatus = 'blockedByMe'
else:
myFriend.friendStatus = friends[3]
self.myFriends.append(myFriend)
self.lastFriendId += 1
friendCursor.execute("""SELECT * FROM FRIENDSTABLE WHERE firends_id=%s;""",(username,))
friendTableConnection.commit()
dbData = friendCursor.fetchall()
if dbData != None:
for friends in dbData:
myFriend = Friend()
myFriend.friendId = friends[0]
myFriend.userName = friends[2]
myFriend.friendUsername = friends[1]
if friends[3] != 'blocked1':
if friends[3] == 'blocked2':
myFriend.friendStatus = 'blockedByMe'
else:
myFriend.friendStatus = friends[3]
self.myFriends.append(myFriend)
self.lastFriendId += 1
friendCursor.close()
friendTableConnection.close()
except friendTableConnection.Error as Error:
print(Error)
return self
def getUserFromDb(username):
conn = getConnection()
cursor = conn.cursor()
try:
cursor.execute(""" SELECT * FROM USERTABLE WHERE username= %s;""",
(username, ))
conn.commit()
dbData = cursor.fetchone()
User.userId = dbData[0]
User.username = dbData[1]
User.password = dbData[2]
User.email = dbData[3]
User.name = dbData[4]
User.surname = dbData[5]
cursor.close()
conn.close()
return User
except conn.Error as error:
print(error)
return 'Error'
def addLocation(self, userLocation):
newUserLocation = UserLocation()
self.lastLocationId+= 1
newUserLocation.locationId = self.lastLocationId
userLocation.locationId=self.lastLocationId
newUserLocation.userName = userLocation.userName
newUserLocation.mapInfo = userLocation.mapInfo
newUserLocation.locationLabel = userLocation.locationLabel
newUserLocation.lat = userLocation.lat
newUserLocation.lng = userLocation.lng
self.myLocations.append(newUserLocation)
try:
userMapConnection = getConnection();
userMapCursor = userMapConnection.cursor()
userMapCursor.execute("""INSERT INTO USERMAPTABLE (userMap_id,user_id,mapInformation,locationLabel,lat,lng) VALUES(%s,%s,%s,%s,%s,%s);""", (userLocation.locationId, userLocation.userName, userLocation.mapInfo,userLocation.locationLabel,userLocation.lat, userLocation.lng ))
userMapConnection.commit()
userMapCursor.close()
userMapConnection.close()
except userMapConnection.Error as userMapError:
print(userMapError)
