def remove_reaction(message_ts, reaction):
write_log(f'Remove {reaction} reaction for {message_ts}...')
url = f'https://slack.com/api/reactions.remove?token={get_token()}&name={reaction}&channel={get_channel()}×tamp={message_ts}&pretty=1'
response = requests.post(url)
write_log('Success' if response.json()['ok'] else 'Fail',
is_new_line=False)
return response.json()['ok']
def get_permalink(message_ts):
write_log(f'Getting permalink for {message_ts}...')
url = f'https://slack.com/api/chat.getPermalink?token={get_token()}&channel={get_channel()}&message_ts={message_ts}&unfurl_links=true&pretty=1'
response = requests.get(url)
write_log('Success' if response.status_code == 200
and response.json()['ok'] else 'Fail',
is_new_line=False)
return response.json()['permalink']
def get_internal_ticket_data(url):
write_log(f'Get internal ticket data from {url}')
headers = {
'accept': 'application/json, text/javascript, */*; q=0.01',
'cookie': f'{get_cookies()}'
}
response = requests.get(url=url, headers=headers).text
return response
def send_message(message):
write_log('Sending message to slack channel...')
url = f'https://slack.com/api/chat.postMessage?token={get_token()}&channel={get_channel()}&text={message}&pretty=1'
response = requests.post(url)
write_log(
'Success' if response.status_code == 200 and response.json()['ok'] else
f'Fail ({response.json()["error"]})',
is_new_line=False)
return response
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_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 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_reactions(message_ts):
write_log(f'Getting reactions for {message_ts}...')
url = f'https://slack.com/api/reactions.get?token={get_token()}&channel={get_channel()}×tamp={message_ts}&pretty=1'
response = requests.post(url)
try:
write_log(
'Success' if response.json()['message']['reactions'] else 'Fail',
is_new_line=False)
return response.json()['message']['reactions']
except KeyError:
return []
def get_inbox_data():
url = 'https://sm.ink/filter?draw=1&columns%5B0%5D%5Bdata%5D=0&columns%5B0%5D%5Bname%5D=&columns%5B0%5D%5Bsearchable%5D=true&columns%5B0%5D%5Borderable%5D=true&columns%5B0%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B0%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B1%5D%5Bdata%5D=1&columns%5B1%5D%5Bname%5D=&columns%5B1%5D%5Bsearchable%5D=true&columns%5B1%5D%5Borderable%5D=true&columns%5B1%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B1%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B2%5D%5Bdata%5D=2&columns%5B2%5D%5Bname%5D=&columns%5B2%5D%5Bsearchable%5D=true&columns%5B2%5D%5Borderable%5D=true&columns%5B2%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B2%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B3%5D%5Bdata%5D=3&columns%5B3%5D%5Bname%5D=&columns%5B3%5D%5Bsearchable%5D=true&columns%5B3%5D%5Borderable%5D=true&columns%5B3%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B3%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B4%5D%5Bdata%5D=4&columns%5B4%5D%5Bname%5D=&columns%5B4%5D%5Bsearchable%5D=true&columns%5B4%5D%5Borderable%5D=true&columns%5B4%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B4%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B5%5D%5Bdata%5D=5&columns%5B5%5D%5Bname%5D=&columns%5B5%5D%5Bsearchable%5D=true&columns%5B5%5D%5Borderable%5D=true&columns%5B5%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B5%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B6%5D%5Bdata%5D=6&columns%5B6%5D%5Bname%5D=&columns%5B6%5D%5Bsearchable%5D=false&columns%5B6%5D%5Borderable%5D=true&columns%5B6%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B6%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B7%5D%5Bdata%5D=7&columns%5B7%5D%5Bname%5D=&columns%5B7%5D%5Bsearchable%5D=false&columns%5B7%5D%5Borderable%5D=true&columns%5B7%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B7%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B8%5D%5Bdata%5D=8&columns%5B8%5D%5Bname%5D=&columns%5B8%5D%5Bsearchable%5D=true&columns%5B8%5D%5Borderable%5D=true&columns%5B8%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B8%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B9%5D%5Bdata%5D=9&columns%5B9%5D%5Bname%5D=&columns%5B9%5D%5Bsearchable%5D=true&columns%5B9%5D%5Borderable%5D=true&columns%5B9%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B9%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B10%5D%5Bdata%5D=10&columns%5B10%5D%5Bname%5D=&columns%5B10%5D%5Bsearchable%5D=true&columns%5B10%5D%5Borderable%5D=true&columns%5B10%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B10%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B11%5D%5Bdata%5D=11&columns%5B11%5D%5Bname%5D=&columns%5B11%5D%5Bsearchable%5D=true&columns%5B11%5D%5Borderable%5D=true&columns%5B11%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B11%5D%5Bsearch%5D%5Bregex%5D=false&order%5B0%5D%5Bcolumn%5D=11&order%5B0%5D%5Bdir%5D=desc&start=0&length=10&search%5Bvalue%5D=&search%5Bregex%5D=false&segment=%2Fticket%2Finbox&_=1588710041019'
headers = {
'accept': 'application/json, text/javascript, */*; q=0.01',
'cookie': f'{get_cookies()}'
}
print('Connecting to ticket-system...')
write_log('Connecting to ticket-system...')
response = requests.get(url=url, headers=headers).text
if response:
write_log('Data from ticket-system received')
return response
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 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 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 how_much_time_passed(timestamp):
write_log('Starting calculating how much time passed...')
time_passed = {}
timestamp_difference = int(time.time()) - int(timestamp)
days = timestamp_difference // 86400
hours = (timestamp_difference % 86400) // 3600
minutes = (timestamp_difference % 3600) // 60
seconds = (timestamp_difference % 60)
if days:
time_passed['days'] = days
time_passed['hours'] = hours
elif hours:
time_passed['hours'] = hours
time_passed['minutes'] = minutes
elif minutes:
time_passed['minutes'] = minutes
time_passed['seconds'] = seconds
else:
time_passed['seconds'] = seconds
write_log('Time calculating is done. Result is {}'.format(time_passed))
return time_passed
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 get_tickets_from_inbox_data(inbox_data):
write_log('Starting parsing new received data...')
tickets = []
dict_inbox_data = json.loads(inbox_data)
for i, ticket_source in enumerate(dict_inbox_data['data']):
ticket = Ticket()
ticket.subject = splitting(source=ticket_source[1],
from_phrase='\'>',
count_from=1,
to_phrase=' ')
ticket.ticket_id = splitting(source=ticket_source[2],
from_phrase='title=\'',
to_phrase='\'>')
ticket.link = splitting(source=ticket_source[2],
from_phrase='<a href=\'',
to_phrase='\' title=')
ticket.assignee_name = splitting(source=ticket_source[9],
from_phrase='>',
count_from=2,
to_phrase='</span>')
ticket.reporter = splitting(source=ticket_source[6],
from_phrase='\'>',
count_from=2,
to_phrase='<span')
ticket.last_activity = splitting(source=ticket_source[10],
from_phrase='">',
to_phrase='</span>')
ticket.topic = ticket_source[3]
ticket.department = ticket_source[4]
ticket.ib_name = ticket_source[7]
ticket.brands = ticket_source[8]
ticket.status, ticket.last_message = get_status_and_last_message(
ticket.link)
tickets.append(ticket)
write_log('Parsing data is done')
return tickets
def check_if_changed(source):
write_log('Checking incoming hash sum')
hashed_source = sha256(source.encode('utf-8')).hexdigest()
print(hashed_source)
if hashed_source != get_last_checksum():
message = f'Detected some changing in inbox data, save new checksum {hashed_source} in DB'
write_log(message)
print(message)
insert_checksum(hashed_source)
return True
else:
message = 'Nothing changed in inbox'
print(message)
write_log(message)
return False
def handle_incoming(incoming_tickets):
for incoming_ticket in incoming_tickets:
incoming_ticket_id = incoming_ticket.ticket_id
if check_if_ticket_exist(
incoming_ticket_id
): # Checking if the ticket already exist in DB
write_log(
f'Ticket {incoming_ticket.ticket_id} already exist in DB. Loading current...'
)
stored_ticket = get_ticket(incoming_ticket_id)
if incoming_ticket.last_message.lower(
) != stored_ticket.last_message.lower(
): # Checking if the ticket has new reply
write_log(
f'Detected new answering by {incoming_ticket.last_message} in ticket {incoming_ticket.ticket_id}: '
)
if incoming_ticket.last_message.lower(
) not in get_channel_names_list(
): # Checking if the person who replied exist in assignee table
write_log(
f'{incoming_ticket.last_message} is absent in assignee table, need to send notification',
is_new_line=False)
initialize_notification(get_answered, incoming_ticket,
stored_ticket)
else:
write_log(
f'{incoming_ticket.last_message} exist in assignee table, not needed to send notification',
is_new_line=False)
update_ticket(incoming_ticket)
elif incoming_ticket.assignee_name.lower(
) != stored_ticket.assignee_name.lower(
): # Checking if assignee was changed
write_log(
f'Detected changing assignee from {stored_ticket.assignee_name} to {incoming_ticket.assignee_name} in ticket {incoming_ticket.ticket_id}. Need to send notification about it'
)
update_assignee(incoming_ticket)
initialize_notification(get_reassigned, incoming_ticket,
stored_ticket)
elif incoming_ticket.status != stored_ticket.status:
write_log(
f'Detected status changing for {incoming_ticket.ticket_id} from {stored_ticket.status} to {incoming_ticket.status}'
)
for message_ts in get_message_ts_list(stored_ticket):
remove_reaction(message_ts,
get_reaction(stored_ticket.status))
update_ticket(incoming_ticket)
else:
insert_ticket(incoming_ticket)
initialize_notification(get_initial, incoming_ticket,
get_ticket(incoming_ticket.ticket_id))
