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))