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!')
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 agentweekly(): conn = sqlite3.connect('UMRF_SQL_Weekly.sqlite') cur = conn.cursor() mail = imaplib.IMAP4_SSL(imap_url) mail.login(user, password) mail.select('"Agent Weekly"') dfemp = pd.DataFrame(columns=['FirstName', 'LastName']) dfweek = pd.DataFrame() datelist = pd.read_sql_query( '''SELECT DISTINCT "WeekStart" FROM "AllData"''', conn)['WeekStart'].tolist() eml = search('Subject', 'UMRF Agent Stats for the week', mail)[0].split() for i in eml[-12:]: 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.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['FCR %'] = dfraw['FCR %'].mask((dfraw['CallsHandled'] == 0)) 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) dfweek = dfweek.append(dfraw) else: print(dt, 'already in') continue conn2 = sqlite3.connect('emplistmanual.sqlite') dfemp = pd.read_sql_query('''SELECT * FROM info''', conn2) dfemp = dfemp.filter(['fedex_id', 'position']) dfemp = dfemp[dfemp['position'] == 'Agent'] dfweek = dfweek.reset_index() dfweekly = pd.merge( dfweek, dfemp, left_on='Employee Number', right_on='fedex_id').drop(columns=['fedex_id', 'position']) dfweekly['FCR %'] = dfweekly['FCR %'].mask( (dfweekly['FCR %'] == 0) & ~(dfweekly['IncidentsCreated'] > 0)) dfweekly = dfweekly.sort_values(by=['LastName']) dfweekly.to_sql('AllData', conn, if_exists='append', index=False) conn.commit() cur.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!')