def add_message_to_db(msg: Message) -> bool:
message_id = None
if msg.receiver and msg.content.strip() != '':
mycursor = DBManager.getCursor()
sql = "INSERT INTO messages (sender_username, receiver_username, content, sending_date, meeting_proposal) VALUES (%s, %s, %s, %s, %s)"
val = (msg.sender, msg.receiver, msg.content, msg.date.strftime('%Y-%m-%d %H:%M:%S'), msg.meeting_proposal)
mycursor.execute('select * from messages where sender_username="******" and receiver_username="******" order by sending_date desc Limit 1')
last_massage= mycursor.fetchone()
print(last_massage)
# check if the last message was a meeting proposal and the owner replied yes
if last_massage is not None and msg.content.lower() == 'yes' and last_massage[5] == 'True':
mycursor.execute("select email from users where username='******'")
owner_email = mycursor.fetchone()
mycursor.execute("select email from users where username='******'")
username_email = mycursor.fetchone()
# extract name place and time from the meeting proposal message
matches = re.findall(r'Can I meet (\S+) in (\S+) in (\S+) at (.*?)\?\nYes/No', last_massage[3])[0]
#meeting_output = os.popen('python meeting//create_meeting.py "' + matches[0] +'" "' + matches[3] + '" ' + matches[1] + 'T' + matches[2] + ' ' + owner_email[0] + ' ' + username_email[0]).read()
#print(f'end creating meeting: {meeting_output}')
meeting.create_meeting(dog_name=matches[0], place=matches[3], time=f'{matches[1]}T{matches[2]}', owner_email=owner_email[0], client_email=username_email[0])
try:
mycursor.execute(sql, val)
DBManager.connection.commit()
message_id = mycursor.lastrowid
print(f'inserted message with id {message_id}')
except Exception as error:
print(f'error in add_message_to_db: {str(error)}')
return message_id
def get_all_messages(sender, receiver):
view = []
try:
mycursor = DBManager.getCursor()
mycursor.execute("SELECT * FROM messages WHERE (sender_username = %(sender)s AND receiver_username = %(receiver)s) " +
"OR (receiver_username = %(sender)s AND sender_username = %(receiver)s)" +
"ORDER BY sending_date ASC", { 'sender': sender,
'receiver': receiver})
view = mycursor.fetchall()
DBManager.closeConnection()
except Exception as error:
print(f'error in get_all_messages: {str(error)}')
return view
def get_all_chats(sender_username):
view = []
try:
mycursor = DBManager.getCursor()
mycursor.execute("SELECT d.username FROM dogs d " +
"INNER JOIN likes l ON l.dog_id = d.dog_id " +
"WHERE l.username=%(sender)s " +
"AND d.dog_id NOT IN (SELECT dog_id FROM adopted) " +
"AND d.username <> %(sender)s " +
"AND l.answer='yes'" +
"UNION " +
"SELECT l.username FROM dogs d " +
"INNER JOIN likes l ON l.dog_id = d.dog_id " +
"WHERE d.username=%(sender)s " +
"AND l.username <> %(sender)s " +
"AND l.answer='yes'" +
"AND d.dog_id NOT IN (SELECT dog_id FROM adopted) " +
"ORDER BY 1 ASC", { 'sender': sender_username, })
view = mycursor.fetchall()
DBManager.closeConnection()
except Exception as error:
print(f'error in get_all_chats: {str(error)}')
return view