def get_assignee(assignee_name):
with PSQLConnection('ticket_system_bot') as connection:
cursor = connection.get_cursor()
cursor.execute(
f"SELECT * FROM assignee WHERE assignee_name = '{assignee_name}'")
response = cursor.fetchall()
return Assignee(response[0]) if len(response) > 0 else None
def get_ticket(ticket_id):
write_log(f'Getting {ticket_id} ticket from DB...')
with PSQLConnection('ticket_system_bot') as connection:
cursor = connection.get_cursor()
cursor.execute(f"SELECT * FROM ticket WHERE ticket_id = '{ticket_id}'")
response = cursor.fetchall()
write_log(' done' if len(response) > 0 else ' nothing to get',
is_new_line=False)
return Ticket(response[0]) if len(response) > 0 else None
def get_all_assignee():
with PSQLConnection('ticket_system_bot') as connection:
cursor = connection.get_cursor()
cursor.execute(f"SELECT * FROM assignee")
response = cursor.fetchall()
assignee_list = list()
for assignee_data in response:
assignee_list.append(Assignee(assignee_data))
return assignee_list
def get_message_ts_list(stored_ticket):
write_log(
f'Getting notifications list for {stored_ticket.ticket_id} in DB...')
with PSQLConnection('ticket_system_bot') as connection:
cursor = connection.get_cursor()
cursor.execute(f"""select slack_ts from notification where
ticket_id = {stored_ticket.id}
""")
result = cursor.fetchall()
write_log(f'{" done" if result else " fail"}', is_new_line=False)
return result
def get_last_checksum():
print('Trying to DB connect...')
with PSQLConnection('ticket_system_bot') as connection:
cursor = connection.get_cursor()
cursor.execute(
f"SELECT checksum FROM inbox_checksum ORDER BY date DESC LIMIT 1")
response = cursor.fetchall()
write_log(
f'Last checksum in database {response[0]["checksum"] if response else None}'
)
return response[0]['checksum'] if response else None
def insert_notification(notification):
write_log(f'Saving {notification.slack_ts} into DB...')
with PSQLConnection('ticket_system_bot') as connection:
cursor = connection.get_cursor()
cursor.execute(f"""insert into notification values (
(select nextval('notification_sequence')),
'{notification.ticket_id}',
{notification.initial},
'{notification.slack_ts}',
'{notification.team_id}',
'{notification.channel}')""")
write_log(' done', is_new_line=False)
def update_assignee(incoming_ticket):
write_log(
f'Update assignee for ticket {incoming_ticket.ticket_id} in DB: ')
assignee = get_assignee(incoming_ticket.assignee_name)
with PSQLConnection('ticket_system_bot') as connection:
cursor = connection.get_cursor()
cursor.execute(f"""update ticket set
assignee_name = '{incoming_ticket.assignee_name}',
assignee_id = {assignee.id if assignee else 'Null'}
where ticket_id = '{incoming_ticket.ticket_id}'
""")
write_log('done', is_new_line=False)
def update_answered_ticket(ticket):
write_log(
f'Update status, last_activity and last_message for ticket {ticket.ticket_id} in DB'
)
with PSQLConnection('ticket_system_bot') as connection:
cursor = connection.get_cursor()
cursor.execute(f"""update ticket set
status = '{ticket.status}',
last_activity = '{ticket.last_activity}',
last_message = '{ticket.last_message}'
where ticket_id = '{ticket.ticket_id}'
""")
def get_opened():
write_log('Loading all opened ticket from DB...')
with PSQLConnection('ticket_system_bot') as connection:
cursor = connection.get_cursor()
cursor.execute(f"SELECT * FROM ticket WHERE status = 'open'")
response = cursor.fetchall()
ticket_list = list()
for ticket in response:
ticket_list.append(Ticket(ticket))
write_log(
f'found {len(ticket_list)} opened ticket in DB at the moment',
is_new_line=False)
return ticket_list
def get_initial(stored_ticket):
write_log(
f'Getting initial notification for {stored_ticket.ticket_id} from DB...'
)
with PSQLConnection('ticket_system_bot') as connection:
cursor = connection.get_cursor()
cursor.execute(f"""select * from notification where
ticket_id = {stored_ticket.id} and
initial = True
""")
response = cursor.fetchall()
result_notification = Notification(response[0])
write_log(f' fail' if not response else
f' done (found {result_notification.slack_ts})',
is_new_line=False)
return result_notification
def insert_ticket(ticket):
write_log(f'Inserting {ticket.ticket_id} ticket into DB')
assignee_obj = get_assignee(ticket.assignee_name)
ticket.subject = ticket.subject.replace('\'', '"')
with PSQLConnection('ticket_system_bot') as connection:
cursor = connection.get_cursor()
cursor.execute(f"""insert into ticket values ((
select nextval('tickets_sequence')),
'{ticket.ticket_id}',
'{ticket.subject}',
'{ticket.topic}',
'{ticket.department}',
'{ticket.reporter}',
'{ticket.ib_name}',
'{ticket.brands}',
'{ticket.assignee_name}',
{assignee_obj.id if assignee_obj else 'Null'},
'{ticket.status}',
'{ticket.link}',
'{ticket.last_activity}',
'{ticket.last_message}')""")
def insert_checksum(checksum_string):
with PSQLConnection('ticket_system_bot') as connection:
cursor = connection.get_cursor()
cursor.execute(
f"insert into inbox_checksum values ('{datetime.now()}', '{checksum_string}')"
)
def check_if_ticket_exist(ticket_id):
with PSQLConnection('ticket_system_bot') as connection:
cursor = connection.get_cursor()
cursor.execute(f"SELECT * FROM ticket WHERE ticket_id = '{ticket_id}'")
result = cursor.fetchall()
return bool(result)