def get_follow(self, follow):
user_m = UserManager()
connection = MySQLConn()
query = """SELECT * FROM tbl_follow WHERE user_id = '%s' and target_id = '%s'; """ % (follow.get("user_id"),
follow.get("target_id"))
connection.open()
connection.get_session().execute(query)
follow_data = connection.get_session().fetchone()
desc = connection.get_session().description
print(desc)
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
follow = Follow()
for data, col in zip(range(0, len(desc)), desc):
print("> > > %s = %s" % (col[0], follow_data[data]))
if isinstance(follow_data[data], datetime.date):
follow.set(col[0], str(follow_data[data]))
elif col[0] == "user_id" or col[0] == "target_id":
user = user_m.get_user_by_id(int(follow_data[data]))
follow.set(col[0], user.__dict__)
else:
follow.set(col[0], follow_data[data])
connection.get_connection().commit()
connection.close()
return follow
class NoticeManager():
def __init__(self):
self._create_table()
self.user_m = UserManager()
def _create_table(self):
connection = MySQLConn()
query = """CREATE TABLE IF NOT EXISTS tbl_notice(
id_notice INT NOT NULL AUTO_INCREMENT,
user_id INT NOT NULL,
text VARCHAR(250) NOT NULL,
written_at DATETIME NOT NULL,
PRIMARY KEY (id_notice),
INDEX user_id_ind (user_id),
FOREIGN KEY (user_id)
REFERENCES tbl_user(id_user));"""
connection.open();
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
connection.get_session().execute(query)
connection.get_connection().commit()
connection.close()
# add notice to table---test done
def insert(self, notice):
connection = MySQLConn()
query = """INSERT INTO tbl_notice (user_id, text, written_at)
VALUES ( '%s' , '%s', '%s');""" % (
notice.get("user_id"), notice.get("text"), notice.get("written_at"))
connection.open()
connection.get_session().execute(query)
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
connection.get_connection().commit()
connection.close()
# return List of all notice written by current user, order by time---test done
def get_notice_by_user(self, notice):
connection = MySQLConn()
query = """SELECT * FROM tbl_notice WHERE user_id = '%s' ORDER BY written_at DESC; """ % (notice.get("user_id"))
connection.open()
connection.get_session().execute(query)
notices_data = connection.get_session().fetchall()
desc = connection.get_session().description
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
connection.get_connection().commit()
notices = []
for notice_data in notices_data:
notice = Notice()
for data, col in zip(range(0, len(desc)), desc):
print("> > > %s = %s" % (col[0], notice_data[data]))
if isinstance(notice_data[data], datetime.datetime):
notice.set(col[0], str(notice_data[data]))
elif col[0] == "sender_id" or col[0] == "receiver_id":
user = self.user_m.get_user_by_id(int(notice_data[data]))
notice.set(col[0], user.__dict__)
else:
notice.set(col[0], notice_data[data])
notices.append(notice.__dict__)
connection.close()
return notices
class MessageManager():
def __init__(self):
self._create_table()
self.user_m = UserManager()
def _create_table(self):
connection = MySQLConn()
query = """CREATE TABLE IF NOT EXISTS tbl_message(
id_message INT NOT NULL AUTO_INCREMENT,
sender_id INT NOT NULL,
receiver_id INT NOT NULL,
text VARCHAR(250) NOT NULL,
sendet_at DATETIME NOT NULL,
isread TINYINT NOT NULL,
PRIMARY KEY (id_message),
INDEX sender_id_ind (sender_id),
INDEX receiver_id_ind (receiver_id),
FOREIGN KEY (sender_id)
REFERENCES tbl_user(id_user),
FOREIGN KEY (receiver_id)
REFERENCES tbl_user(id_user));"""
connection.open();
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
connection.get_session().execute(query)
connection.get_connection().commit()
connection.close()
# test done
def insert(self, message):
connection = MySQLConn()
query = """INSERT INTO tbl_message (sender_id, receiver_id, text, sendet_at, isread)
VALUES ( '%s' , '%s', '%s', '%s', '%s');""" % (message.get("sender_id"), message.get("receiver_id"),
message.get("text"), message.get("sendet_at"),
message.get("isread"))
connection.open()
connection.get_session().execute(query)
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
connection.get_connection().commit()
connection.close()
# Set a message as read----#test done
def set_read(self, message):
connection = MySQLConn()
query = """ update tbl_message set isread = 1 where sender_id = %s and receiver_id = '%s';""" % (
message.get("sender_id"), message.get("receiver_id"))
connection.open()
connection.get_session().execute(query)
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
connection.get_connection().commit()
connection.close()
# Get message receiverd or sent by current user----#test done
def get_message_by_user(self, message):
connection = MySQLConn()
query = """select * from tbl_message where sender_id = '%s' or receiver_id = '%s' ; """ % (
message.get('sender_id'),
message.get('sender_id'))
connection.open()
connection.get_session().execute(query)
messages_data = connection.get_session().fetchall()
desc = connection.get_session().description
connection.get_connection().commit()
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
messages = []
for message_data in messages_data:
message = Message()
for data, col in zip(range(0, len(desc)), desc):
print("> > > %s = %s" % (col[0], message_data[data]))
if isinstance(message_data[data], datetime.datetime):
message.set(col[0], str(message_data[data]))
elif col[0] == "sender_id" or col[0] == "receiver_id":
user = self.user_m.get_user_by_id(int(message_data[data]))
message.set(col[0], user.__dict__)
else:
message.set(col[0], message_data[data])
messages.append(message.__dict__)
connection.close()
return messages
# Get conversation beetwen 2 users---------#test done
def get_message_between_two_user(self, message):
connection = MySQLConn()
query = """select * from tbl_message where (sender_id = '%s' and receiver_id = '%s') or
(sender_id = '%s' and receiver_id = '%s') order by sendet_at DESC;""" % (
message.get('sender_id'), message.get('receiver_id'),
message.get('receiver_id'), message.get('sender_id'))
connection.open()
connection.get_session().execute(query)
messages_data = connection.get_session().fetchall()
desc = connection.get_session().description
connection.get_connection().commit()
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
messages = []
for message_data in messages_data:
message = Message()
for data, col in zip(range(0, len(desc)), desc):
print("> > > %s = %s" % (col[0], message_data[data]))
if isinstance(message_data[data], datetime.date):
message.set(col[0], str(message_data[data]))
elif col[0] == "sender_id" or col[0] == "receiver_id":
user = self.user_m.get_user_by_id(int(message_data[data]))
message.set(col[0], user.__dict__)
else:
message.set(col[0], message_data[data])
messages.append(message.__dict__)
connection.close()
return messages
# Return number of messsages not reab by current user----#test done
def get_number_messages_not_read(self, message):
connection = MySQLConn()
query = """SELECT count(DISTINCT sender_id) FROM tbl_message WHERE receiver_id = '%s' and isread = 0; """ % \
(message.get("receiver_id"))
connection.open()
connection.get_session().execute(query)
message_data = connection.get_session().fetchone()[0]
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
connection.get_connection().commit()
connection.close()
return message_data
#TODO: ??? :(
def get_users_intouch(self, message):
connection = MySQLConn()
query = """select tu.* ,tud.bday, tud.role, tud.city,tud.rate, tud.active, tud.description, tud.avatar
from (select * from tbl_user_dettail where role is not null) as tud
join (select * from tbl_user where email != '%s' order by rand() limit 7) as tu
on tu.id_user = tud.id_user""" % ()
connection.open()
connection.get_session().execute(query)
users_data = connection.get_session().fetchall()
desc = connection.get_session().description
connection.get_connection().commit()
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
users = []
for user_data in users_data:
user = User()
for data, col in zip(range(0, len(desc)), desc):
print("> > > %s = %s" % (col[0], user_data[data]))
if isinstance(user_data[data], datetime.date):
user.set(col[0], str(user_data[data]))
else:
user.set(col[0], user_data[data])
users.append(user.__dict__)
connection.close()
return users
