def _create_table_user(self):
connection = MySQLConn()
query = """CREATE TABLE IF NOT EXISTS %s (
%s INT NOT NULL AUTO_INCREMENT,
%s VARCHAR(55) NOT NULL,
%s VARCHAR(55) NOT NULL,
%s VARCHAR(55) NOT NULL,
%s VARCHAR(55) NOT NULL,
PRIMARY KEY (%s),
UNIQUE KEY %s_UNIQUE (%s));""" % (
self.__tbl_user,
self.__id_user,
self.__name,
self.__surname,
self.__email,
self.__password,
self.__id_user,
self.__email,
self.__email,
)
connection.open()
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
connection.get_session().execute(query)
connection.get_connection().commit()
connection.close()
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
def login(self, email, password):
connection = MySQLConn()
query = """ SELECT count(*) FROM tbl_user where email='%s' and password='******'; """ % (email, password)
connection.open()
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
connection.get_session().execute(query)
result = connection.get_session().fetchone()[0] == 1
connection.get_connection().commit()
connection.close()
print("> > > login result: %s \n" % result)
return result
def login(self, token):
connection = MySQLConn()
query = """ SELECT count(*) FROM tbl_refresh_token where token='%s' and expair_at > now();""" % (token)
connection.open()
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
connection.get_session().execute(query)
result = connection.get_session().fetchone()[0] == 1
connection.get_connection().commit()
connection.close()
print("> > > login result: %s \n" % result)
return result
def ceck_feedback(self, feedback):
connection = MySQLConn()
query = """ SELECT count(*) FROM tbl_feedback where user_id='%s' and target_id='%s' and
Datediff(now(), date_feedback) = 0; """ % (feedback.get("user_id"), feedback.get("target_id"))
connection.open()
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
connection.get_session().execute(query)
result = connection.get_session().fetchone()[0] == 0
connection.get_connection().commit()
connection.close()
print("> > > login result: %s \n" % result)
return result
def login(self, random_id, secret_id, grant_types):
connection = MySQLConn()
query = """ SELECT count(*) FROM tbl_client where random_id='%s' and secret_id='%s' and grant_types='%s'; """ % \
(random_id, secret_id, grant_types)
connection.open()
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
connection.get_session().execute(query)
result = connection.get_session().fetchone()[0] == 1
connection.get_connection().commit()
connection.close()
print("> > > login result: %s \n" % result)
return result
def is_followed(self, follow):
connection = MySQLConn()
query = """SELECT count(user_id) 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)
result = connection.get_session().fetchone()[0] == 1
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
connection.get_connection().commit()
connection.close()
return result
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()
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()
def remove_follower(self, follow):
connection = MySQLConn()
query = """DELETE FROM tbl_follow WHERE user_id = '%s' and target_id = '%s';""" % (follow.get("user_id"),
follow.get("target_id"))
connection.open()
print("#DB# %s \n" % (query))
connection.get_session().execute(query)
print("#DB# -execute-\n")
connection.get_connection().commit()
connection.close()
def insert(self, follow):
connection = MySQLConn()
query = """INSERT INTO tbl_follow (user_id, target_id, date_follow)
VALUES ( '%s' , '%s', '%s');""" % (follow.get("user_id"), follow.get("target_id"),
follow.get("date_follow"))
connection.open()
connection.get_session().execute(query)
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
connection.get_connection().commit()
connection.close()
def update_status(self, user):
connection = MySQLConn()
query = """ update tbl_user_dettail set active = '%s' where id_user = %s;""" % (
user.get("active"),
user.get("id_user"),
)
connection.open()
connection.get_session().execute(query)
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
connection.get_connection().commit()
connection.close()
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()
def get_tokens_by_user(self, user):
connection = MySQLConn()
query = """ SELECT tbl_refresh_token.token, tbl_access_token.token FROM tbl_refresh_token, tbl_access_token
WHERE tbl_refresh_token.user_id = '%s' and tbl_access_token.user_id ='%s' ;""" % (
user.get("id_user"),
user.get("id_user"),
)
connection.open()
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
connection.get_session().execute(query)
result = connection.get_session().fetchone()
connection.get_connection().commit()
connection.close()
print("> > > refresh token result: %s \n> > > access token result: %s \n" % (result[0], result[1]))
return result[0], result[1]
def _update_access(self, user):
connection = MySQLConn()
new_date = datetime.datetime.now() + datetime.timedelta(days=60)
query = """ update tbl_access_token set expair_at = '%s' where user_id = %s;""" % (
new_date,
user.get("id_user"),
)
connection.open()
connection.get_session().execute(query)
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
connection.get_connection().commit()
connection.close()
def _update_user(self, user):
connection = MySQLConn()
query = """ update tbl_user set name = '%s', surname = '%s', password = '******' where id_user = %s;""" % (
user.get("name"),
user.get("surname"),
user.get("password"),
user.get("id_user"),
)
connection.open()
connection.get_session().execute(query)
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
connection.get_connection().commit()
connection.close()
def _create_table_client(self):
connection = MySQLConn()
query = """CREATE TABLE IF NOT EXISTS %s (
%s INT NOT NULL AUTO_INCREMENT,
%s VARCHAR(55) NOT NULL,
%s VARCHAR(55) NOT NULL,
%s VARCHAR(55) NOT NULL,
PRIMARY KEY (%s));""" % (self.__tbl_name, self.__id_client, self.__random_id, self.__secret_id,
self.__grant_types, self.__id_client)
connection.open()
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
connection.get_session().execute(query)
connection.get_connection().commit()
connection.close()
def insert_access_token(self, token):
connection = MySQLConn()
query = """INSERT INTO tbl_access_token ( expair_at , token, client_id, user_id)
VALUES ( '%s' , '%s', '%s', '%s' );""" % (
token.get("expair_at"),
token.get("token"),
token.get("client_id"),
token.get("user_id"),
)
connection.open()
connection.get_session().execute(query)
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
connection.get_connection().commit()
connection.close()
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()
def _update_dettail(self, user):
connection = MySQLConn()
query = """ update tbl_user_dettail set bday = '%s', role = '%s', city = '%s', rate = '%s', active = '%s',
description = '%s', avatar = '%s' where id_user = %s;""" % (
user.get("bday"),
user.get("role"),
user.get("city"),
user.get("rate"),
user.get("active"),
user.get("description"),
user.get("avatar"),
user.get("id_user"),
)
connection.open()
connection.get_session().execute(query)
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
connection.get_connection().commit()
connection.close()
def _insert_dettails(self, user):
connection = MySQLConn()
query = """INSERT INTO %s VALUES ( '%d', '%s', '%s', '%s', '%f', '%d', '%s', '%d' );""" % (
self.__tbl_user_dettail,
user.get("id_user"),
user.get("bday"),
user.get("role"),
user.get("city"),
user.get("rate"),
user.get("active"),
user.get("description"),
user.get("avatar"),
)
connection.open()
connection.get_session().execute(query)
connection.get_connection().commit()
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
connection.close()
def _create_table(self):
connection = MySQLConn()
query = """CREATE TABLE IF NOT EXISTS tbl_follow(
id_follow INT NOT NULL AUTO_INCREMENT,
user_id INT NOT NULL,
target_id INT NOT NULL,
date_follow DATE NOT NULL,
PRIMARY KEY (id_follow),
INDEX user_id_ind (user_id),
INDEX target_id_ind (target_id),
FOREIGN KEY (user_id)
REFERENCES tbl_user(id_user),
FOREIGN KEY (target_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()
def _create_table_refresh(self):
connection = MySQLConn()
query = """CREATE TABLE IF NOT EXISTS tbl_refresh_token(
id_token INT NOT NULL AUTO_INCREMENT,
expair_at DATETIME NOT NULL,
token VARCHAR(55) NOT NULL,
client_id INT NOT NULL,
user_id INT NOT NULL,
PRIMARY KEY (id_token),
INDEX client_id_ind (client_id),
INDEX user_id_ind (user_id),
FOREIGN KEY (client_id)
REFERENCES tbl_client(id_client),
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()
def _create_table_user_dettail(self):
connection = MySQLConn()
query = """CREATE TABLE IF NOT EXISTS %s (
%s INT NOT NULL,
%s DATE NOT NULL,#Date
%s VARCHAR(55) NOT NULL,#Role
%s VARCHAR(55) NOT NULL,#City
%s DOUBLE NOT NULL,#Rate
%s TINYINT NOT NULL, #Active
%s VARCHAR(200) NOT NULL, #Descript
%s INT NOT NULL,#Avatar
PRIMARY KEY (%s),#id
INDEX %s_ind (%s),#id
FOREIGN KEY (%s)# id_user
REFERENCES %s(%s)# tbl_user, id_user
ON DELETE CASCADE
);""" % (
self.__tbl_user_dettail,
self.__id_user,
self.__bday,
self.__role,
self.__city,
self.__rate,
self.__active,
self.__description,
self.__avatar,
self.__id_user,
self.__id_user,
self.__id_user,
self.__id_user,
self.__tbl_user,
self.__id_user,
)
connection.open()
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
connection.get_session().execute(query)
connection.get_connection().commit()
connection.close()
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()
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
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
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
def get_user_by_id(self, id):
connection = MySQLConn()
query = """select tu.* , tbl_user_dettail.bday, tbl_user_dettail.role, tbl_user_dettail.city,
tbl_user_dettail.rate, tbl_user_dettail.active, tbl_user_dettail.description,
tbl_user_dettail.avatar
from(select * from tbl_user where id_user = '******') as tu
left join tbl_user_dettail on tu.id_user = tbl_user_dettail.id_user """ % (
id
)
connection.open()
connection.get_session().execute(query)
user_data = connection.get_session().fetchone()
desc = connection.get_session().description
connection.get_connection().commit()
print("#DB# -execute-\n")
print("#DB# %s \n" % (query))
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])
connection.close()
return user
def get_filtered_user(self, user):
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 like '%s%%' and city like '%s%%' and rate > '%s') as tud
join (select * from tbl_user where name like '%s%%') as tu
on tu.id_user = tud.id_user""" % (
user.get("role"),
user.get("city"),
user.get("rate"),
user.get("name"),
)
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