def agentweekly(): user, password, imap_url = emailcredentials() mail = imaplib.IMAP4_SSL(imap_url) mail.login(user, password) mail.select('"Agent Weekly"') eml = search('Subject', 'UMRF Agent Stats for the week', mail)[0].split() for i in eml[-1:]: result, data = mail.fetch(i, '(RFC822)') emlhtml = get_body(email.message_from_bytes(data[0][1])) soup = BeautifulSoup(emlhtml, 'lxml') dt = re.findall('from ([0-9-]+)', soup.text)[0] #prep/clean data dfraw = pd.read_html(emlhtml, header=0)[0] #dfraw.insert(8, 'Ticket %',((dfraw['IncidentsCreated']/dfraw['CallsHandled'])*100).replace([np.inf,-np.inf], np.nan).round(2)) dfraw['WeekStart'] = dt dfraw['FCR %'] = dfraw['FCR %'].replace({ '%': '' }, regex=True).astype('float') dfraw['LoggedOnTime'] = dfraw['LoggedOnTime'].apply( lambda x: float(convtime(x)[0])) dfraw['AvailTime'] = dfraw['AvailTime'].apply( lambda x: float(convtime(x)[0])) dfraw['NotReadyTime'] = dfraw['NotReadyTime'].apply( lambda x: float(convtime(x)[0])) dfraw['ASA'] = dfraw['ASA'].apply(lambda x: float(convtime(x)[1])) dfraw['AHT'] = dfraw['AHT'].apply(lambda x: float(convtime(x)[1])) dfraw.rename(columns={ 'LoggedOnTime': 'LoggedOnTime (hrs)', 'AvailTime': 'AvailTime (hrs)', 'NotReadyTime': 'NotReadyTime (hrs)', 'ASA': 'ASA (min)', 'AHT': 'AHT (min)', }, inplace=True) dfraw['IncidentsCreated'] = dfraw['IncidentsCreated'].mask( (dfraw['IncidentsCreated'] == 0) & (dfraw['CallsHandled'] > 0)) dfweeklyeml = dfraw return dfweeklyeml, dt
def rejectedincidents() : today = datetime.strftime(datetime.today(), '%Y-%m-%d') yesterday = datetime.strftime(datetime.strptime(today,'%Y-%m-%d') - timedelta(1), '%d-%b-%Y') print(yesterday) conn = sqlite3.connect('UMRF_Incidents_SQL.sqlite') conn2 = sqlite3.connect('emplistmanual.sqlite') dflist = [] user, password, imap_url = emailcredentials() mail = imaplib.IMAP4_SSL(imap_url) mail.login(user,password) mail.select('"Rejected Incidents"') eml = search('ON', "{}".format(yesterday), mail)[0].split() for i in eml[:] : result, data = mail.fetch(i,'(RFC822)') emlbytes = get_body(email.message_from_bytes(data[0][1])) try : s = str(emlbytes,'windows-1252') incidentNum = re.findall('Incident # (INC[0-9]+)', s)[0] except : print('multipart email: trying base64 decoding') try : b = email.message_from_bytes(data[0][1]) if b.is_multipart(): for part in b.walk(): ctype = part.get_content_type() cdispo = str(part.get('Content-Disposition')) # skip any text/plain (txt) attachments if ctype == 'text/plain' and 'attachment' not in cdispo: s = part.get_payload(decode=True) # decode break else : s = b.get_payload(decode=True) except : print('could not read, fetching incident number from subject...') s = str(data[0][1])[2:-1] s = str(s,'windows-1252') incidentNum = re.findall('Incident (INC[0-9]+)', s)[0] sql = '''SELECT * FROM AllData WHERE number IS "{}"'''.format(incidentNum) dfraw = pd.read_sql_query(sql,conn) df = dfraw.replace({'\r\n':'. ','\n':' '}, regex=True) df = df.filter(['number','sys_created_by','opened_by','opened_at','company','assignment_group','reassignment_count','u_resolving_level','resolved_by','short_description','description','comments_and_work_notes','close_notes']) df = df.rename(columns={'sys_created_by':'Emp_ID','reassignment_count':'reassigned','u_resolving_level':'resolving_lvl'}) #df = df.drop(columns=['comments_and_work_notes','comments']) #print(df) dflist.append(df) if len(dflist) > 1 : df = pd.concat(dflist,axis=0) df = df.apply(pd.to_numeric, errors='ignore') elif len(dflist) == 1 : df = df.apply(pd.to_numeric, errors='ignore') elif len(dflist) == 0 : print('No Rejected Incidents!') try : df.to_html('rejected_incidents_yest.html',index=False) dfempcount = pd.read_sql_query('''SELECT * FROM "EmpCount"''', conn, index_col=None) dfcount = df.filter(['number','Emp_ID']) dfempcount = dfempcount.append(dfcount,sort=False) dfempcount = dfempcount.drop_duplicates(subset=['number']) dfempcount.to_sql('EmpCount', conn, if_exists='replace',index=False) df = df.drop(columns=['Emp_ID']) dfemp = pd.read_sql_query('''SELECT * FROM info''', conn2) df_emp = dfemp.filter(['email2','position']) df_emp = df_emp[~(df_emp['position'] == 'Agent')] eml = df_emp['email2'].values.tolist() html = StringIO() df.to_html(buf=html) dfhtml = pd.read_html(html,index_col=0)[0] print(eml) html = dfhtml.to_html(border='border',index=False) text = '''This is an automated email. If you have any questions, concerns, or feedback please email Kyle Lynch at [email protected]''' #send_mail(user, eml,'Rejected Incidents Report for {}'.format(yesterday), text=text, html=html) except : print('No Rejected Incidents!') print('UMRF_Rejected_Incidents.py completed successfully!')
# -*- coding: utf-8 -*- import imaplib, email import sqlite3 from bs4 import BeautifulSoup import pandas as pd import numpy as np import os import re from datetime import date, datetime from sensitive import emailcredentials pd.set_option('display.max_columns', 100) user, password, imap_url = emailcredentials() def get_body(msg): if msg.is_multipart(): return get_body(msg.get_payload(1)) else: return msg.get_payload(None, True) def search(key, value, conn): result, data = conn.search(None, key, '"{}"'.format(value)) return data def get_emails(result_bytes): msgs = [] for num in result_bytes[0].split():
def agentdailycsv(): conn = sqlite3.connect('UMRF_Incidents_SQL.sqlite') conn2 = sqlite3.connect('UMRF_SQL_Daily.sqlite') conn3 = sqlite3.connect('emplistmanual.sqlite') user, password, imap_url = emailcredentials() mail = imaplib.IMAP4_SSL(imap_url) mail.login(user, password) mail.select('"Agent Daily"') dfemp = pd.DataFrame(columns=['FirstName', 'LastName']) dfdaily = pd.DataFrame() datelist = pd.read_sql_query('''SELECT DISTINCT "Date" FROM "AllData"''', conn2)['Date'].tolist() eml = search('Subject', 'FW: UMRF Agent Stats', mail)[0].split() for i in eml[-5:]: result, data = mail.fetch(i, '(RFC822)') emlhtml = get_body(email.message_from_bytes(data[0][1])) soup = BeautifulSoup(emlhtml, 'lxml') dt = re.findall('from ([0-9-]+)', soup.text)[0] if not dt in datelist: print(dt, 'not in') datelist.append(dt) #prep/clean data dfraw = pd.read_html(emlhtml, header=0)[0].set_index(['Employee Number']) dfraw['IncidentsCreated'] = dfraw['IncidentsCreated'].mask( (dfraw['IncidentsCreated'] == 0) & (dfraw['CallsHandled'] > 0)) dfraw['Date'] = dt dfraw['FCR %'] = dfraw['FCR %'].replace({ '%': '' }, regex=True).astype('float') dfraw['LoggedOnTime'] = dfraw['LoggedOnTime'].apply( lambda x: float(convtime(x)[0])) dfraw['AvailTime'] = dfraw['AvailTime'].apply( lambda x: float(convtime(x)[0])) dfraw['NotReadyTime'] = dfraw['NotReadyTime'].apply( lambda x: float(convtime(x)[0])) dfraw['ASA'] = dfraw['ASA'].apply(lambda x: float(convtime(x)[1])) dfraw['AHT'] = dfraw['AHT'].apply(lambda x: float(convtime(x)[1])) dfraw.rename(columns={ 'LoggedOnTime': 'LoggedOnTime (hrs)', 'AvailTime': 'AvailTime (hrs)', 'NotReadyTime': 'NotReadyTime (hrs)', 'ASA': 'ASA (min)', 'AHT': 'AHT (min)', }, inplace=True) dftemp = dfraw.filter(items=['FirstName', 'LastName']) dfemp = dfemp.append(dftemp) dfemp.drop_duplicates(inplace=True) dfemp.index.rename('Employee Number', inplace=True) yesterday = (datetime.strptime(dt, '%Y-%m-%d')).strftime('%Y-%m-%d') dfraw2 = pd.read_sql_query( '''SELECT * FROM AllData WHERE Date IS "{0}"'''.format( yesterday), conn) df_inc = dfraw2.filter( ['sys_created_by', 'opened_by', 'resolved_by']) s_open = df_inc['opened_by'].value_counts() s_res = df_inc['resolved_by'].value_counts() dfemp = pd.read_sql_query('''SELECT * FROM info''', conn3) df_relate = dfemp.filter(['fedex_id', 'first_name', 'last_name']) df_relate['opened_by'] = df_relate['first_name'] + ' ' + df_relate[ 'last_name'] df_relate = df_relate.drop( columns=['first_name', 'last_name']).set_index('opened_by') df = pd.concat([df_relate, s_open, s_res], axis=1, sort=True) df['opened_by'] = df['opened_by'].fillna(0) df['resolved_by'] = df['resolved_by'].fillna(0) df = df.dropna().astype(int) df['FCR %'] = (df['resolved_by'] / df['opened_by']).round(4) * 100 df = df.rename( columns={ 'fedex_id': 'Employee Number', 'opened_by': 'IncidentsCreated', 'resolved_by': 'IncidentsCreatedAndResolved' }).reset_index().set_index('Employee Number') df_call = dfraw.drop(columns=[ 'IncidentsCreated', 'IncidentsCreatedAndResolved', 'FCR %' ]) df_day = pd.concat([df, df_call], axis=1, sort=True).dropna().reset_index(drop=False) df_day['FirstName'] = df_day['index'].str.split().str[0] df_day['LastName'] = df_day['index'].str.split().str[-1] #df_week['FirstName'] = df_week['index'].str.extract('([a-zA-Z-]+)\s[\sa-zA-Z-]+') #df_week['LastName'] = df_week['index'].str.extract('[a-zA-Z-]+\s([\sa-zA-Z-]+)') df_day = df_day.drop(columns=['index']) df_day['Ticket %'] = ( (df_day['IncidentsCreated'] / df_day['CallsHandled']) * 100).replace([np.inf, -np.inf], np.nan).round(2) df_day = df_day.filter([ 'Employee Number', 'LastName', 'FirstName', 'Number of Surveys', 'Avg. Survey Score (100)', 'Avg. Survey Score (5)', 'IncidentsCreated', 'IncidentsCreatedAndResolved', 'FCR %', 'Ticket %', 'IncidentsResolved', 'LoggedOnTime (hrs)', 'AvailTime (hrs)', 'NotReadyTime (hrs)', 'NR%', 'CallsAnswered', 'ACH%', 'ASA (min)', 'CallsHandled', 'AHT (min)', 'AbandonRingCalls', 'AbandonHoldCalls', 'AbandonHoldOutCalls', 'ShortCalls', 'Date' ]) df_day = df_day.sort_values(by=['LastName']).reset_index(drop=True) dfemp = dfemp.filter(['fedex_id', 'position']) dfemp = dfemp[dfemp['position'] == 'Agent'] df_daily = pd.merge( df_day, dfemp, left_on='Employee Number', right_on='fedex_id', sort=False).drop(columns=['fedex_id', 'position']) df_daily['FCR %'] = df_daily['FCR %'].mask( (df_daily['FCR %'] == 0) & ~(df_daily['IncidentsCreated'] > 0)) dfdaily = dfdaily.append(df_daily) #print(dfdaily) else: print(dt, 'already in') continue dfdaily.to_sql('AllData', conn2, if_exists='append', index=False) print('UMRF_Agent_Daily Completed Successfully!')
def callpatternsql(): conn = sqlite3.connect('UMRF_Call_Pattern.sqlite') dflist = [] days = { 'Monday': int(0), 'Tuesday': int(1), 'Wednesday': int(2), 'Thursday': int(3), 'Friday': int(4), 'Saturday': int(5), 'Sunday': int(6) } try: datelist = pd.read_sql_query( '''SELECT DISTINCT "Date" FROM "AllData"''', conn)['Date'].tolist() except: datelist = [] user, password, imap_url = emailcredentials() mail = imaplib.IMAP4_SSL(imap_url) mail.login(user, password) mail.select('"Call Pattern"') eml = search('Subject', 'FW: UMRF Calls Arrival Pattern for', mail)[0].split() for i in eml[-5:]: #for i in eml : result, data = mail.fetch(i, '(RFC822)') emlhtml = get_body(email.message_from_bytes(data[0][1])) #extract data soup = BeautifulSoup(emlhtml, 'lxml') dt = re.findall('for ([0-9-]+)', soup.text)[0] year, month, day = dt.split('-') day = calendar.day_name[calendar.weekday(int(year), int(month), int(day))] if dt not in datelist: print(dt, 'not in') datelist.append(dt) #prep/clean data dfraw = pd.read_html(emlhtml, header=0)[0] dfraw = dfraw.iloc[14:40].reset_index(drop=True) dfraw.replace( { '! ': '', '!': '', '< /td>': '', '<tr>': '', '< td>': '', '< tr>': '', 'A\sM': 'AM', '\s': '', '[0-9]+\s': '' }, regex=True, inplace=True) if not 'Overflow Calls' in dfraw: dfraw['Overflow Calls'] = dfraw['Calls Offered'].astype( 'float') - dfraw['ACD Calls'].astype('float') if dfraw['Overflow Calls'].isnull().values.any(): nanindex = dfraw[dfraw.isnull().any(axis=1)] for i in nanindex.index.values: dfraw1 = dfraw[['Time Interval']] dfraw2 = dfraw[[ 'SL Abandoned', 'Abandoned Calls', 'Calls Offered', 'ACD Calls', 'Overflow Calls' ]] dfraw2.iloc[i] = dfraw2.iloc[i].shift(1) dfraw = pd.concat([dfraw1, dfraw2], axis=1, sort=False) dfraw['SL Abandoned'] = dfraw['SL Abandoned'].fillna(0) dfraw['Day'] = day dfraw = dfraw.reset_index() dfraw = dfraw.rename(columns={'Time Interval': 'Time Int'}) dfraw['Time Interval'] = pd.date_range( start='{} 07:30:00'.format(dt), end='{} 20:00:00'.format(dt), freq='30T').astype('str') df = dfraw.set_index(['Time Interval']) df = df.drop(columns=['index']) df['Date'] = dt df['rank'] = df['Day'].map(days) dflist.append(df) else: print(dt, 'already in') continue if len(dflist) > 1: dfall = pd.concat(dflist, axis=0).sort_values(by=['Date'], kind='mergesort') dfall = dfall.apply(pd.to_numeric, errors='ignore') dfall.to_sql('AllData', conn, if_exists='append') elif len(dflist) == 1: df = df.apply(pd.to_numeric, errors='ignore') df.to_sql('AllData', conn, if_exists='append') elif len(dflist) == 0: print('Already up to date!') conn.commit() conn.close()
def agentdailycsv(): conn = sqlite3.connect('UMRF_Incidents_SQL.sqlite') dflist = [] days = { 'Monday': int(0), 'Tuesday': int(1), 'Wednesday': int(2), 'Thursday': int(3), 'Friday': int(4), 'Saturday': int(5), 'Sunday': int(6) } try: datelist = pd.read_sql_query( '''SELECT DISTINCT "Date" FROM "AllData"''', conn)['Date'].tolist() except: datelist = [] user, password, imap_url = emailcredentials() mail = imaplib.IMAP4_SSL(imap_url) mail.login(user, password) mail.select('"Incidents Daily"') eml = search('Subject', 'FW: UMRF_Incidents', mail)[0].split() for i in eml[-5:]: result, data = mail.fetch(i, '(RFC822)') emlbytes = get_body(email.message_from_bytes(data[0][1])) #print(emlbytes) s = str(emlbytes, 'windows-1252') #print(s) data = StringIO(s) df = pd.read_csv(data) df = df.filter([ 'number', 'opened_at', 'u_affected_user', 'short_description', 'state', 'assignment_group', 'assigned_to', 'caller_id.user_name', 'category', 'business_service', 'work_notes', 'urgency', 'severity', 'resolved_at', 'close_notes', 'reopen_count', 'reassignment_count', 'priority', 'parent_incident', 'incident_state', 'impact', 'calendar_duration', 'description', 'sys_created_by', 'sys_created_on', 'correlation_display', 'location', 'comments_and_work_notes', 'closed_at', 'comments', 'active', 'resolved_by', 'opened_by', 'cmdb_ci', 'company', 'u_resolving_level', 'u_process_feedback', 'u_process_feedback_notes' ]) dt = df.at[2, 'opened_at'][:10] year, month, day = dt.split('-') day = calendar.day_name[calendar.weekday(int(year), int(month), int(day))] if dt not in datelist: print(dt, 'not in') datelist.append(dt) df['Date'] = dt dflist.append(df) else: print(dt, 'already in') continue if len(dflist) > 1: dfall = pd.concat(dflist, axis=0).sort_values(by=['Date'], kind='mergesort') dfall = dfall.apply(pd.to_numeric, errors='ignore') dfall.to_sql('AllData', conn, if_exists='append') elif len(dflist) == 1: df = df.apply(pd.to_numeric, errors='ignore') df.to_sql('AllData', conn, if_exists='append') elif len(dflist) == 0: print('Already up to date!') print('UMRF_Incident_Daily_SQL.py completed successfully!')