def main():
try:
# Get tickets for BizApps queues
tickets = TicketList(version=3, query=["BizApps - Triage", "BizApps - Integrations", "BizApps - Technical"],
count_only=False)
tickets = tickets.reformat_as_dataframe(tickets)
try:
tickets.drop('ATTACHMENTS', axis=1, inplace=True)
except:
print 'No Attachments column.'
tickets = tickets[['CATEGORY', 'COMPLETEDTIME', 'CREATEDBY', 'CREATEDTIME',
'DELETED_TIME', 'DEPARTMENT', 'DUEBYTIME', 'GROUP', 'HASATTACHMENTS',
'HASCONVERSATION', 'HASNOTES', 'ISPENDING', 'ITEM', 'LEVEL',
'LONG_REQUESTID', 'MODE', 'PRIORITY', 'REQUESTER', 'REQUESTEREMAIL',
'REQUESTTEMPLATE', 'RESOLUTION', 'RESOLUTIONLASTUPDATEDTIME', 'RESOLVER',
'RESPONDEDTIME', 'SHORTDESCRIPTION',
'SITE', 'SLA', 'STATUS', 'STOPTIMER', 'SUBCATEGORY', 'SUBJECT', 'TECHNICIAN',
'TEMPLATEID', 'TIMESPENTONREQ', 'Department_Group', 'System Component',
'System', 'WORKORDERID']].copy()
# Package in to a hyper file
data_file = HyperAssembler(data_frame=tickets, extract_name=extract_name, file_path=file_path)
# Set values for publishing the data.
file_name = str(data_file)
tableau_server = Tableau(server_url='https://tableau.bazaarvoice.com/', site_id='BizTech')
tableau_server.publish_datasource(project=project,
file_path=file_name,
mode='Append', name=data_source_name)
remove(file_name)
outlook().send_email(to='*****@*****.**',
subject='HDT-Data update complete', body='HDT-Data update complete')
except:
error_result = "Unexpected AttributeError: %s, %s"\
% (sys.exc_info()[0], sys.exc_info()[1])
subject = 'Error with Tableau refresh script, %s on Server %s' % (basename(__file__), socket.gethostname())
error_result += subject
print error_result
try:
data = {'REQUESTEREMAIL': '*****@*****.**',
'REQUESTER': 'Martin Valenzuela',
'DESCRIPTION': error_result,
'SUBJECT': subject}
Ticket().create_ticket(data)
except:
outlook().send_email('*****@*****.**', cc='*****@*****.**', subject=subject,
body=error_result)
give_notice = Notifier()
give_notice.set_red()
give_notice.wait(30)
give_notice.flow_the_light()
def main():
try:
# Get Users from Okta for Workday
workday = Okta_Application(app_name='workday')
employees = workday.app_users
# Package in to a hyper file
data_file = HyperAssembler(data_frame=employees,
extract_name=extract_name,
file_path=file_path)
# Set values for publishing the data.
file_name = str(data_file)
tableau_server = Tableau(server_url='https://tableau.bazaarvoice.com/',
site_id='BizTech')
tableau_server.publish_datasource(project=project,
file_path=file_name,
mode='Append',
name=data_source_name)
remove(file_name)
outlook().send_email(to='*****@*****.**',
subject='Workday_User_data compiling complete',
body='Workday_User_data compiling complete')
except KeyboardInterrupt:
pass
except:
error_result = 'Unexpected AttributeError: %s, %s' \
% (sys.exc_info()[0], sys.exc_info()[1])
subject = 'Error with Tableau refresh script, %s on Server %s' % (
basename(__file__), socket.gethostname())
error_result += subject
print error_result
try:
data = {
'REQUESTEREMAIL': '*****@*****.**',
'REQUESTER': 'Martin Valenzuela',
'DESCRIPTION': error_result,
'SUBJECT': subject
}
Ticket().create_ticket(data)
except:
outlook().send_email('*****@*****.**',
cc='*****@*****.**',
subject=subject,
body=error_result)
give_notice = Notifier()
give_notice.set_red()
give_notice.wait(30)
give_notice.flow_the_light()
def main():
try:
# Get tickets for BizApps queues
tickets = TicketList(version=3, query=["BizApps - Triage", "BizApps - Integrations", "BizApps - Technical"],
count_only=False, open_only=True)
backlog_levels(tickets.total_count)
tickets = tickets.reformat_as_dataframe(tickets)
try:
tickets.drop('ATTACHMENTS', axis=1, inplace=True)
except:
print 'No Attachments column.'
# Package in to a hyper file
data_file = HyperAssembler(data_frame=tickets, extract_name=extract_name, file_path=file_path)
# Set values for publishing the data.
file_name = str(data_file)
tableau_server = Tableau(server_url='https://tableau.bazaarvoice.com/', site_id='BizTech')
tableau_server.publish_datasource(project=project,
file_path=file_name,
mode='Append', name=data_source_name)
outlook().send_email(to='*****@*****.**',
subject='HDT-Open ticket refresh to Tableau complete',
body='HDT-Open ticket refresh to Tableau complete')
remove(file_name)
except KeyboardInterrupt:
pass
except:
error_result = "Unexpected AttributeError: %s, %s" \
% (sys.exc_info()[0], sys.exc_info()[1])
subject = 'Error with Tableau refresh script, %s on Server %s' % (basename(__file__), socket.gethostname())
error_result += subject
print error_result
try:
data = {'REQUESTEREMAIL': '*****@*****.**',
'REQUESTER': 'Martin Valenzuela',
'DESCRIPTION': error_result,
'SUBJECT': subject}
Ticket().create_ticket(data)
except:
outlook().send_email('*****@*****.**', cc='*****@*****.**', subject=subject,
body=error_result)
give_notice = Notifier()
give_notice.set_red()
give_notice.wait(30)
give_notice.flow_the_light()
def mfa_notify(user_info=('*****@*****.**', 'Martin')):
try:
user_email = user_info[0]
user_first_name = user_info[1]
# Connect as Helpdesk
email = outlook(account='*****@*****.**')
instructions_path = os.path.join(os.path.dirname(__file__),
'mfa_instructions/')
html_file_name = instructions_path + 'instruction_email_html.txt'
with open(html_file_name, 'r') as html_file:
html = html_file.read()
# Create list for attaching images to the html for the email
html = html % user_first_name
file_names = [
'%simage%03d.jpg' % (instructions_path, number)
for number in range(1, 11)
]
email.send_email(to=user_email,
subject='Added to Multi Factor Authentication',
body=html,
html=html,
files=file_names)
except Exception, e:
error_result = "Unexpected Error: %s, %s, %s" \
% (sys.exc_info()[0], sys.exc_info()[1], e)
subject = 'Error with MFA true up script. Not able to send Email. %s' % basename(
__file__)
print error_result
try:
data = {
'REQUESTEREMAIL': '*****@*****.**',
'REQUESTER': 'Martin Valenzuela',
'DESCRIPTION': error_result,
'SUBJECT': subject
}
Ticket().create_ticket(data)
except:
outlook().send_email('*****@*****.**',
cc='*****@*****.**',
subject=subject,
body=error_result)
give_notice = Notifier()
give_notice.set_red()
give_notice.wait(30)
give_notice.flow_the_light()
from os import remove
import socket
if environ['MY_ENVIRONMENT'] == 'prod':
file_path = '/var/shared_folder/BizApps/Tableau_data/'
project = 'Business Applications'
else:
file_path = '/Users/%s/Downloads/' % environ['USER']
project = 'Testing'
# file_path = 'Testing/BizApps/Tableau_data/'
extract_name = 'BizApps_Open_HDT'
data_source_name = 'BizApps_Open_HDT'
give_notice = Notifier()
def backlog_levels(backlog_number):
give_notice.alert_the_light()
if backlog_number < 75:
give_notice.set_green()
elif 75 <= backlog_number < 85:
give_notice.set_yellow()
elif backlog_number >= 85:
give_notice.set_red()
else:
raise ValueError(backlog_number)
def main():
def main():
hired_and_offer_stage_ids = ['44015e45-bbf3-447c-8517-55fe4540acdc', 'offer']
today = datetime.today()
current_month = today.replace(day=1, hour=0, minute=0, second=0, microsecond=0)
beginning_of_three_months_ago = current_month - pd.offsets.MonthBegin(3)
beginning_of_three_months_ago = beginning_of_three_months_ago.value / 1000000
try:
# Get stages from Lever
stages = Lever_Stages()
stages = stages.stages
stages = stages.append([{'stage_id': '4ddfa41f-8cf2-4648-9780-21923247c3f3',
'text': 'Recruiter Qualified'},
{'stage_id': '0798c37a-2a2f-4978-88ac-8af174a5a2e8',
'text': 'Hiring Manager Qualified'},
{'stage_id': '32c7f46d-8921-4752-8091-a207199d60c0',
'text': 'Contact Candidate'},
{'stage_id': '44015e45-bbf3-447c-8517-55fe4540acdc',
'text': 'Background Check'}
])
stages.set_index(['stage_id'], inplace=True)
stages.loc['-'] = '-'
# Get archive_reasons from Lever
archive_reasons = Archive_Reasons()
archive_reasons = archive_reasons.archive_reasons
archive_reasons.set_index(['archive_reason_id'], inplace=True)
archive_reasons.loc['-'] = '-'
# Combine Stage and Archive reasons
stage_and_archive_reasons = pd.merge(left=stages, right=archive_reasons, how='outer', on='text',
left_index=True, right_index=True)
# Get requisition_fields from Lever
requisition_fields = Requisition_Fields()
requisition_fields = requisition_fields.requisition_fields
# Get user from Lever
users = Lever_Users()
users = users.users
# Get posts from Lever
posts = Postings()
postings = posts.full_postings[['categories', 'content', 'createdAt', 'followers', 'hiringManager', 'post_id',
'owner', 'reqCode', 'state', 'tags', 'text', 'updatedAt', 'urls', 'user',
'commitment', 'department', 'level', 'location', 'team', 'closing',
'closingHtml', 'customQuestions', 'description', 'descriptionHtml', 'lists']]
final_posts = postings.copy(deep=True)
except:
exc_type, exc_value, exc_traceback = sys.exc_info()
traceback.print_exception(exc_type, exc_value, exc_traceback)
error_result = "Unexpected Error: %s, %s, %s" \
% (exc_type, exc_value, traceback.format_exc())
subject = 'Error with Tableau refresh script, Failed to gather supporting tables %s' % basename(__file__)
print error_result
# TODO- Log errors in to table then send only one email to HD
outlook().create_helpdesk_ticket(cc='*****@*****.**',
subject=subject, body=error_result)
give_notice = Notifier()
give_notice.set_red()
give_notice.wait(3)
give_notice.set_error_light()
give_notice.flow_the_light()
try:
# Get candidates from Lever
candidates = Candidates(date_limit=beginning_of_three_months_ago)
candidate_stages_ids = candidates.stages[['candidate_id', 'toStageId']]
candidates_full_columns = candidates.full_candidates[['applications', 'archived', 'createdAt',
'emails', 'followers', 'headline', 'candidate_id',
'lastAdvancedAt', 'lastInteractionAt', 'links',
'location',
'name', 'origin', 'owner', 'phones', 'snoozedUntil',
'sources', 'stage', 'stageChanges', 'tags', 'urls',
'archivedAt', 'reason', 'applications_as_feature_column',
'toStageId', 'toStageIndex', 'updatedAt', 'userId']]
candidates_full = candidates_full_columns.copy()
# Swap out if values with Label
candidates_full['reason'] = candidates_full['reason'].apply(lambda x: stage_and_archive_reasons.loc[x])
candidates_full['stage'] = candidates_full['stage'].apply(lambda x: stage_and_archive_reasons.loc[x])
# Narrow candidates to only those that reach stage_ids at some point for applications and offers
candidates_with_offers = candidate_stages_ids[candidate_stages_ids['toStageId']
.isin(hired_and_offer_stage_ids)]['candidate_id']
candidates_with_offers = candidates_with_offers.copy()
candidates_with_offers.drop_duplicates(inplace=True)
# Update stage tds with labels
candidates_full['toStageId'] = candidates_full['toStageId'].apply(lambda x: stage_and_archive_reasons.loc[x])
# Split out a list for applications and offers
candidates_with_offers = candidates_with_offers.tolist()
except:
exc_type, exc_value, exc_traceback = sys.exc_info()
traceback.print_exception(exc_type, exc_value, exc_traceback)
error_result = "Unexpected Error: %s, %s, %s" \
% (exc_type, exc_value, traceback.format_exc())
subject = 'Error with Tableau refresh script, Failed to gather candidates %s' % basename(__file__)
print error_result
outlook().create_helpdesk_ticket(cc='*****@*****.**',
subject=subject, body=error_result)
give_notice = Notifier()
give_notice.set_red()
give_notice.wait(3)
give_notice.set_error_light()
give_notice.flow_the_light()
try:
# Get offers from Lever based on candidates with Stages in hired_and_offer_stage_ids
offers = Offers(candidate_id=candidates_with_offers)
offers_full = offers.full_offer
offers_full = correct_date_dtype(offers_full, date_time_format='%Y-%m-%d %H:%M:%S',
date_time_columns={'createdAt', 'approvedAt', 'sentAt'})
print offers_full.columns
except:
exc_type, exc_value, exc_traceback = sys.exc_info()
traceback.print_exception(exc_type, exc_value, exc_traceback)
error_result = "Unexpected Error: %s, %s, %s" \
% (exc_type, exc_value, traceback.format_exc())
subject = 'Error with Tableau refresh script, Failed to gather offers %s' % basename(__file__)
print error_result
outlook().create_helpdesk_ticket(cc='*****@*****.**',
subject=subject, body=error_result)
give_notice = Notifier()
give_notice.set_red()
give_notice.wait(3)
give_notice.set_error_light()
give_notice.flow_the_light()
try:
# Get requisitions from Lever
requisitions = Requisitions()
requisitions_full = requisitions.full_requisitions
except:
exc_type, exc_value, exc_traceback = sys.exc_info()
traceback.print_exception(exc_type, exc_value, exc_traceback)
error_result = "Unexpected Error: %s, %s, %s" \
% (exc_type, exc_value, traceback.format_exc())
subject = 'Error with Tableau refresh script, Failed to gather requisitions %s' % basename(__file__)
print error_result
outlook().create_helpdesk_ticket(cc='*****@*****.**',
subject=subject, body=error_result)
give_notice = Notifier()
give_notice.set_red()
give_notice.wait(3)
give_notice.set_error_light()
give_notice.flow_the_light()
try:
# Left join candidates with offers
candidates_and_offers = pd.merge(left=candidates_full,
right=offers_full[['candidate_id', 'offer_id',
'posting', 'Type', 'Commission amount']],
how='left', on='candidate_id')
candidates_with_offers = pd.merge(left=candidates_and_offers,
right=postings[['post_id', 'team', 'location', 'owner', 'reqCode']],
how='outer', left_on='posting', right_on='post_id',
suffixes=('_candidate', '_posting'))
candidates_for_offers = correct_date_dtype(candidates_with_offers, date_time_format='%Y-%m-%d %H:%M:%S',
date_time_columns={'archivedAt', 'createdAt', 'updatedAt_posts',
'createdAt_posts', 'lastAdvancedAt',
'snoozedUntil', 'updatedAt',
'lastInteractionAt'})
except:
exc_type, exc_value, exc_traceback = sys.exc_info()
traceback.print_exception(exc_type, exc_value, exc_traceback)
error_result = "Unexpected Error: %s, %s, %s" \
% (exc_type, exc_value, traceback.format_exc())
subject = 'Error with Tableau refresh script, Failed to gather candidates wiht offers %s' % basename(__file__)
print error_result
outlook().create_helpdesk_ticket(cc='*****@*****.**',
subject=subject, body=error_result)
give_notice = Notifier()
give_notice.set_red()
give_notice.wait(3)
give_notice.set_error_light()
give_notice.flow_the_light()
try:
""" Left join requisition data with candidates"""
# Gather limited data on Candidates
candidates_for_heatmap = candidates.candidates[['candidate_id', 'archivedAt', 'reason']]
# Left join Offers data with Candidates
candidates_for_heatmap_with_offers = pd.merge(left=candidates_for_heatmap,
right=offers_full[['candidate_id', 'offer_id', 'posting',
'Type']],
how='left', on='candidate_id', suffixes=('_candidate', '_offer'))
candidates_for_heatmap_with_offers.rename(columns={'posting': 'post_id'}, inplace=True)
# Left join Posting data with Candidates
candidates_for_heatmap_with_offers_posts = pd.merge(left=candidates_for_heatmap_with_offers,
right=postings[['post_id', 'tags', 'owner', 'updatedAt',
'createdAt']],
how='outer', on='post_id',
suffixes=('_candidate', '_posts'))
candidates_for_heatmap_with_offers_posts.fillna(value='-', inplace=True)
candidates_for_heatmap_with_offers_posts = candidates_for_heatmap_with_offers_posts.copy(deep=True)
candidates_for_heatmap_with_offers_posts.rename(columns={'owner': 'owner_posting',
'updatedAt': 'updatedAt_posts',
'createdAt': 'createdAt_posts'}, inplace=True)
# Reduce row count
candidates_for_heatmap_with_offers_posts.sort_values(by=['post_id', 'tags', 'Type'], inplace=True)
candidates_for_heatmap_with_offers_posts.drop_duplicates(subset=['post_id'], inplace=True)
# Gather requisitions for joining with the candidates
requisitions_prep_for_heatmap = requisitions.requisitions[['team',
'postings_as_feature_column',
'requisition_id',
'location',
'offerIds',
'createdAt',
'name',
'creator',
'owner',
'status',
'hiringManager',
'requisitionCode']].copy(deep=True)
requisitions_prep_for_heatmap.rename(columns={'postings_as_feature_column': 'post_id'}, inplace=True)
requisitions_prep_for_heatmap_candidates_offers_posts = pd.merge(left=requisitions_prep_for_heatmap,
right=candidates_for_heatmap_with_offers_posts,
how='left', on='post_id',
suffixes=('_requisition', '_candidate'))
requisitions_for_heatmap = requisitions_prep_for_heatmap_candidates_offers_posts.copy(deep=True)
requisitions_for_heatmap.sort_values(by=['updatedAt_posts', 'tags', 'Type'], inplace=True)
requisitions_for_heatmap.drop_duplicates(subset=['requisition_id'], inplace=True)
# Clean up date columns
requisitions_for_heatmap = correct_date_dtype(requisitions_for_heatmap, date_time_format='%Y-%m-%d %H:%M:%S',
date_time_columns={'archivedAt', 'createdAt', 'updatedAt_posts',
'createdAt_posts'})
except:
exc_type, exc_value, exc_traceback = sys.exc_info()
traceback.print_exception(exc_type, exc_value, exc_traceback)
error_result = "Unexpected Error: %s, %s, %s" \
% (exc_type, exc_value, traceback.format_exc())
subject = 'Error with Tableau refresh script, Failed to gather Requisitions for Heatmaps %s' \
% basename(__file__)
print error_result
outlook().create_helpdesk_ticket(cc='*****@*****.**',
subject=subject, body=error_result)
give_notice = Notifier()
give_notice.set_red()
give_notice.wait(3)
give_notice.set_error_light()
give_notice.flow_the_light()
try:
offers_full.drop(columns=[''], axis=1, inplace=True)
# Create table array to iterate through for creation and publishing.
extract_name = [[offers_full, 'Lever_Offers'],
[users, 'Lever_Users'],
[final_posts, 'Lever_Posts'],
[requisitions_full, 'Lever_Requisitions'],
[candidates_full, 'Lever_Candidates'],
[stages, 'Lever_Stages'],
[requisition_fields, 'Lever_Req_Fields'],
[archive_reasons, 'Lever_Archieve_Reasons'],
[candidates_for_offers, 'Lever_Candidates_with_Offers_Posts'],
[requisitions_for_heatmap, 'Lever_Requisitions_with_Candidates_data_for_Heatmap']]
file_names_to_publish = {}
for table in extract_name:
# Package in to a tde file
data_file = HyperAssembler(data_frame=table[0], extract_name=table[1], file_path=file_path)
# Set values for publishing the data.
file_names_to_publish[table[1]] = data_file.file_name
print table[1], "\n", str(data_file), "\n-------------\n\n"
except:
exc_type, exc_value, exc_traceback = sys.exc_info()
traceback.print_exception(exc_type, exc_value, exc_traceback)
error_result = "Unexpected Error: %s, %s, %s" \
% (exc_type, exc_value, traceback.format_exc())
subject = 'Error creating Extracts in Tableau refresh script, %s on Server %s' % (basename(__file__),
socket.gethostname())
error_result += subject
print error_result
try:
data = {'REQUESTEREMAIL': '*****@*****.**',
'REQUESTER': 'Martin Valenzuela',
'DESCRIPTION': error_result,
'SUBJECT': subject}
Ticket().create_ticket(data)
except:
outlook().send_email('*****@*****.**', cc='*****@*****.**', subject=subject,
body=error_result)
give_notice = Notifier()
give_notice.set_red()
give_notice.wait(3)
give_notice.set_error_light()
give_notice.flow_the_light()
try:
tableau_server = Tableau(server_url='https://tableau.bazaarvoice.com/', site_id='PeopleandTalent')
for table_name in extract_name:
tableau_server.publish_datasource(project=project,
file_path=file_names_to_publish[table_name[1]],
mode='Append', name=table_name[1])
remove(file_names_to_publish[table_name[1]])
outlook().send_email(to='*****@*****.**',
subject='Lever-Data update complete', body='Lever-Data update complete')
except:
error_result = "Unexpected AttributeError: %s, %s" \
% (sys.exc_info()[0], sys.exc_info()[1])
subject = 'Error with Tableau refresh script, %s on Server %s' % (basename(__file__), socket.gethostname())
error_result += subject
print error_result
try:
data = {'REQUESTEREMAIL': '*****@*****.**',
'REQUESTER': 'Martin Valenzuela',
'DESCRIPTION': error_result,
'SUBJECT': subject}
Ticket().create_ticket(data)
except:
outlook().send_email('*****@*****.**', cc='*****@*****.**', subject=subject,
body=error_result)
give_notice = Notifier()
give_notice.set_red()
give_notice.wait(3)
give_notice.set_error_light()
give_notice.flow_the_light()
def main():
try:
all_wd_employees = []
for each in work_day_groups.groups['id'].tolist():
# Get Users from Okta that are in the list of Workday groups
mfa_users = Okta_Group_Members(group_id=each)
mfa_employees = mfa_users.group_members
all_wd_employees.append(mfa_employees)
# Union all data frames
all_wd_employees = pd.concat(all_wd_employees,
ignore_index=True,
sort=False)
# Get Users from Okta that are in mfa-everywhere group
mfa_users = Okta_Group_Members(group_id=m_f_group)
mfa_employees = mfa_users.group_members
# Do an left outer join so all Workday employees and their MFA profiles are matched up
left_wd_join = pd.merge(left=all_wd_employees,
right=mfa_employees[['email', 'login']],
how='left',
on='email',
suffixes=('_wd', '_mfa'))
# Any Null in login_mfa column are not in MFA group.
employees_without_mfa = left_wd_join[
left_wd_join['login_mfa'].isnull()].copy() # type: pd.DataFrame
employees_without_mfa['result'] = ''
# If employees_without_mfa is not Empty then apply mfa group to user missing the group and publish full list.
if not employees_without_mfa.empty:
# Apply mfa group to each user without mfa
employees_without_mfa['result'] = employees_without_mfa[
'email'].apply(mfa_users.add_user_to_group)
# Reduce to Success list
employees_success = employees_without_mfa[
employees_without_mfa['result'].str.contains('Success')][[
'email', 'firstName'
]]
# Send Email notifiying user of the change
for each in employees_success.values.tolist():
mfa_notify(each)
wait(1)
left_wd_join = pd.merge(
left=left_wd_join,
right=employees_without_mfa[['email', 'result']],
how='left',
on='email',
suffixes=('_wd', '_results'))
try:
# Package in to a hyper file
data_file = HyperAssembler(data_frame=left_wd_join,
extract_name=extract_name,
file_path=file_path)
# Set values for publishing the data.
file_name = str(data_file)
tableau_server = Tableau(
server_url='https://tableau.bazaarvoice.com/',
site_id='BizTech')
tableau_server.publish_datasource(project=project,
file_path=file_name,
mode='Append',
name=extract_name)
remove(file_name)
except Exception, e:
error_result = "Unexpected Error: %s, %s, %s" \
% (sys.exc_info()[0], sys.exc_info()[1], e)
subject = 'Error with MFA true up script. Could not publish user list to Tableau. %s on %s' % \
(basename(__file__), socket.gethostname())
error_result += subject
print error_result
try:
data = {
'REQUESTEREMAIL': '*****@*****.**',
'REQUESTER': 'Martin Valenzuela',
'DESCRIPTION': error_result,
'SUBJECT': subject
}
Ticket().create_ticket(data)
except:
outlook().send_email(
'*****@*****.**',
cc='*****@*****.**',
subject=subject,
body=error_result)
give_notice = Notifier()
give_notice.set_red()
give_notice.wait(30)
give_notice.flow_the_light()
# Get Style sheet for the email.
html = HTMLParser()
f = open(get_static_file('styleTags.html'), 'r')
style = f.readlines()
style = ' '.join(style)
style = html.unescape(style)
employees_without_mfa = employees_without_mfa[[
'id', 'email', 'result'
]].to_html(index=False, show_dimensions=True)
body = '<html><head>%s</head><body>' % style + '<h2>MFA_User_application compiling complete</h2><br><br>' \
'<h2>List of Users Below</h2>' + html.unescape(employees_without_mfa) + \
'<br><br></body></html>'
outlook().send_email(to='*****@*****.**',
subject='MFA_User_application Complete',
body=body,
html=body)
% (sys.exc_info()[0], sys.exc_info()[1])
subject = 'Error with MFA true up script. Major error. ' \
'Might have prevented mfa Provisioning. %s on %s' % (basename(__file__), socket.gethostname())
error_result += subject
print error_result
try:
data = {
'REQUESTEREMAIL': '*****@*****.**',
'REQUESTER': 'Martin Valenzuela',
'DESCRIPTION': error_result,
'SUBJECT': subject
}
Ticket().create_ticket(data)
except:
outlook().send_email('*****@*****.**',
cc='*****@*****.**',
subject=subject,
body=error_result)
give_notice = Notifier()
give_notice.set_red()
give_notice.wait(30)
give_notice.flow_the_light()
if __name__ == '__main__':
start = time()
main()
end = time()
print(end - start) / 60
print datetime.now()
def main():
hired_and_offer_stage_ids = [
'44015e45-bbf3-447c-8517-55fe4540acdc', 'offer'
]
funnel_stage_ids = [
'applicant-new', 'lead-responded',
'5cd295db-095a-438b-8ebb-02be04c5d219',
'1b38aea1-5a7c-44e2-87cd-7058b6400cb0',
'5e860f8b-acc1-40df-966e-748318285dc2',
'944e0470-7698-49be-85ec-3051c7e860ca',
'f46f4de3-aea1-41c0-9a2c-9c31ab0c5b59',
'44015e45-bbf3-447c-8517-55fe4540acdc', 'offer'
]
today = datetime.today()
current_month = today.replace(day=1,
hour=0,
minute=0,
second=0,
microsecond=0)
beginning_of_three_months_ago = current_month - pd.offsets.MonthBegin(3)
print beginning_of_three_months_ago
try:
# Get stages from Lever
stages = Lever_Stages()
stages = stages.stages
stages = stages.append([{
'stage_id': '4ddfa41f-8cf2-4648-9780-21923247c3f3',
'text': 'Recruiter Qualified'
}, {
'stage_id': '0798c37a-2a2f-4978-88ac-8af174a5a2e8',
'text': 'Hiring Manager Qualified'
}, {
'stage_id': '32c7f46d-8921-4752-8091-a207199d60c0',
'text': 'Contact Candidate'
}, {
'stage_id': '44015e45-bbf3-447c-8517-55fe4540acdc',
'text': 'Background Check'
}])
stages.set_index(['stage_id'], inplace=True)
stages.loc['-'] = '-'
# Get archive_reasons from Lever
archive_reasons = Archive_Reasons()
archive_reasons = archive_reasons.archive_reasons
archive_reasons.set_index(['archive_reason_id'], inplace=True)
archive_reasons.loc['-'] = '-'
# Combine Stage and Archive reasons
stage_and_archive_reasons = pd.merge(left=stages,
right=archive_reasons,
how='outer',
on='text',
left_index=True,
right_index=True)
# Get posts from Lever
posts = Postings()
postings = posts.full_postings
final_posts = postings.copy(deep=True)
except:
exc_type, exc_value, exc_traceback = sys.exc_info()
traceback.print_exception(exc_type, exc_value, exc_traceback)
error_result = "Unexpected Error: %s, %s, %s"\
% (exc_type, exc_value, traceback.format_exc())
subject = 'Error with Tableau refresh script, Failed to gather supporting tables %s' % path.basename(
__file__)
print error_result
# TODO- Log errors in to table then send only one email to HD
outlook().send_email('*****@*****.**',
cc='*****@*****.**',
subject=subject,
body=error_result)
give_notice = Notifier()
give_notice.set_red()
give_notice.wait(3)
give_notice.set_error_light()
give_notice.flow_the_light()
try:
# Get candidates from Lever
candidates = Candidates(date_limit=beginning_of_three_months_ago)
candidate_stages_ids = candidates.stages[[
'candidate_id', 'toStageId', 'updatedAt'
]]
candidates_full = candidates.full_candidates
# Swap out if values with Label
candidates_full['reason'] = candidates_full['reason'].apply(
lambda x: stage_and_archive_reasons.loc[x])
candidates_full['stage'] = candidates_full['stage'].apply(
lambda x: stage_and_archive_reasons.loc[x])
# Narrow candidates to only those that reach hired_and_offer_stage_ids at some point for offers
candidates_for_offers = candidate_stages_ids[candidate_stages_ids[
'toStageId'].isin(hired_and_offer_stage_ids)]['candidate_id']
candidates_for_offers = candidates_for_offers.copy()
candidates_for_offers.drop_duplicates(inplace=True)
# Narrow candidates to only those that reach funnel stages at some point from applications to hired
candidates_for_applications = candidate_stages_ids[
candidate_stages_ids['toStageId'].isin(funnel_stage_ids)]
candidates_for_applications = candidates_for_applications.copy(
deep=True)
candidates_for_applications.drop_duplicates(inplace=True)
# Update stage ids with labels
candidates_full['toStageId'] = candidates_full['toStageId'].apply(
lambda x: stage_and_archive_reasons.loc[x])
# Split out a list for applications and offers
candidates_with_applications = candidates_for_applications[
'candidate_id'].tolist()
candidates_with_offers = candidates_for_offers.tolist()
except:
exc_type, exc_value, exc_traceback = sys.exc_info()
traceback.print_exception(exc_type, exc_value, exc_traceback)
error_result = "Unexpected Error: %s, %s, %s"\
% (exc_type, exc_value, traceback.format_exc())
subject = 'Error with Tableau refresh script, Failed to gather candidates %s' % path.basename(
__file__)
print error_result
outlook().send_email('*****@*****.**',
cc='*****@*****.**',
subject=subject,
body=error_result)
give_notice = Notifier()
give_notice.set_red()
give_notice.wait(3)
give_notice.set_error_light()
give_notice.flow_the_light()
try:
# Get offers from Lever based on candidates with Stages in hired_and_offer_stage_ids
offers = Offers(candidate_id=candidates_with_offers)
offers_full = offers.full_offer
offers_full = correct_date_dtype(
offers_full,
date_time_format='%Y-%m-%d %H:%M:%S',
date_time_columns={'createdAt', 'approvedAt', 'sentAt'})
except:
exc_type, exc_value, exc_traceback = sys.exc_info()
traceback.print_exception(exc_type, exc_value, exc_traceback)
error_result = "Unexpected Error: %s, %s, %s"\
% (exc_type, exc_value, traceback.format_exc())
subject = 'Error with Tableau refresh script, Failed to gather offers %s' % path.basename(
__file__)
print error_result
outlook().send_email('*****@*****.**',
cc='*****@*****.**',
subject=subject,
body=error_result)
give_notice = Notifier()
give_notice.set_red()
give_notice.wait(3)
give_notice.set_error_light()
give_notice.flow_the_light()
try:
# Get applications from Lever based on candidates having been in stages that are in hired_and_offer_stage_ids
applications = Applications(candidate_id=candidates_with_applications)
applications = applications.full_application
applications = correct_date_dtype(
applications,
date_time_format='%Y-%m-%d %H:%M:%S',
date_time_columns={'createdAt_application', 'createdAt__fields'})
except:
exc_type, exc_value, exc_traceback = sys.exc_info()
traceback.print_exception(exc_type, exc_value, exc_traceback)
error_result = "Unexpected Error: %s, %s, %s"\
% (exc_type, exc_value, traceback.format_exc())
subject = 'Error with Tableau refresh script, Failed to gather applications%s' % path.basename(
__file__)
print error_result
outlook().send_email('*****@*****.**',
cc='*****@*****.**',
subject=subject,
body=error_result)
give_notice = Notifier()
give_notice.set_red()
give_notice.wait(3)
give_notice.set_error_light()
give_notice.flow_the_light()
try:
# Left join candidates with applications
applications_and_candidates = pd.merge(left=applications,
right=candidates_full,
how='outer',
left_on='candidateId',
right_on='candidate_id',
suffixes=('_application',
'_candidate'))
applications_and_candidates_and_offers = pd.merge(
left=applications_and_candidates,
right=offers_full[[
'candidate_id', 'offer_id', 'posting', 'Type',
'Commission amount'
]],
how='left',
on='candidate_id',
suffixes=('_a_c', '_offer'))
candidates_for_funnel = pd.merge(
left=applications_and_candidates_and_offers,
right=postings[['post_id', 'team', 'location', 'owner',
'reqCode']],
how='outer',
left_on='posting_a_c',
right_on='post_id',
suffixes=('_a_c_o', '_posting'))
candidates_for_funnel = correct_date_dtype(
candidates_for_funnel,
date_time_format='%Y-%m-%d %H:%M:%S',
date_time_columns={
'archivedAt', 'createdAt', 'updatedAt_posts',
'createdAt_posts', 'lastAdvancedAt', 'snoozedUntil',
'updatedAt', 'lastInteractionAt'
})
except:
exc_type, exc_value, exc_traceback = sys.exc_info()
traceback.print_exception(exc_type, exc_value, exc_traceback)
error_result = "Unexpected Error: %s, %s, %s" \
% (exc_type, exc_value, traceback.format_exc())
subject = 'Error with Tableau refresh script, Failed to gather candidates wiht offers %s' % path.basename(
__file__)
print error_result
outlook().send_email('*****@*****.**',
cc='*****@*****.**',
subject=subject,
body=error_result)
give_notice = Notifier()
give_notice.set_red()
give_notice.wait(3)
give_notice.set_error_light()
give_notice.flow_the_light()
try:
# Create table array to iterate through for creation and publishing.
extract_name = [[applications, 'Lever_Applications'],
[candidates_for_funnel, 'Lever_Funnel_Data']]
file_names_to_publish = {}
for table in extract_name:
# Package in to a tde file
data_file = TDEAssembler(data_frame=table[0],
extract_name=table[1])
# Set values for publishing the data.
file_names_to_publish[table[1]] = str(data_file)
print table[1], "\n", str(data_file), "\n-------------\n\n"
except:
exc_type, exc_value, exc_traceback = sys.exc_info()
traceback.print_exception(exc_type, exc_value, exc_traceback)
error_result = "Unexpected Error: %s, %s, %s"\
% (exc_type, exc_value, traceback.format_exc())
subject = 'Error with Tableau refresh script, Failed to create tableau extract, %s' % path.basename(
__file__)
print error_result
outlook().send_email('*****@*****.**',
cc='*****@*****.**',
subject=subject,
body=error_result)
give_notice = Notifier()
give_notice.set_red()
give_notice.wait(3)
give_notice.set_error_light()
give_notice.flow_the_light()
try:
tableau_server = Tableau(server_url='https://tableau.bazaarvoice.com/',
site_id='PeopleandTalent')
for table_name in extract_name:
tableau_server.publish_datasource(
project=project,
file_path=file_names_to_publish[table_name[1]],
mode='Append',
name=table_name[1])
outlook().send_email(to='*****@*****.**',
subject='Lever-Data update complete',
body='Lever-Data update complete')
except:
error_result = "Error publishing tableau extracts. Unexpected Error: %s, %s"\
% (sys.exc_info()[0], sys.exc_info()[1])
subject = 'Error with Tableau refresh script, %s' % path.basename(
__file__)
print error_result
outlook().send_email('*****@*****.**',
cc='*****@*****.**',
subject=subject,
body=error_result)
give_notice = Notifier()
give_notice.set_red()
give_notice.wait(3)
give_notice.set_error_light()
give_notice.flow_the_light()
def get_user_vector(self):
replace_with = {False: 0, True: 1, 'na': 0, 'False': 0, 'True': 1}
no_touch_columns = ['Email', 'Employee_ID__c', 'UserId', 'UserName']
# Create the initial SFDC user list with permissions
sfdc_list = SFDC_Users(include_licenses=True, include_permissions=True)
sfdc_users = sfdc_list.users_with_licenses_permissions().copy()
sfdc_users['Email'] = sfdc_users['Email'].map(lambda x: x.lower())
# Vectorize the profiles
# print '---------------new DF---------------\n'
# print sfdc_users[['UserId', 'Profile_Name']]
profiles_vectorized = create_feature_vector_dataframe(
sfdc_users[['UserId', 'Profile_Name']],
feature_index_column='UserId',
feature_column='Profile_Name',
suffix='_profile')
profiles_vectorized.fillna(value=0, inplace=True)
profile_clean = pd.merge(left=sfdc_users,
right=profiles_vectorized,
on='UserId')
# Vectorize the roles
# print '---------------new DF---------------\n'
# print sfdc_users[['UserId', 'Role__c']]
profiles_vectorized = create_feature_vector_dataframe(
sfdc_users[['UserId', 'Role__c']],
feature_index_column='UserId',
feature_column='Role__c',
suffix='_role')
profiles_vectorized.fillna(value=0, inplace=True)
almost_clean = pd.merge(left=profile_clean,
right=profiles_vectorized,
on='UserId')
# print '---------------new DF---------------\n'
almost_clean.drop(['Profile_Name', 'Role__c'], axis=1, inplace=True)
# print almost_clean
# print '---------------new DF---------------\n'
# Save none binary columns
user_values = almost_clean[no_touch_columns]
# print user_values
# Replace nulls and nots with 0's
almost_clean = almost_clean.applymap(self.replacement_method)
# print '---------------new DF---------------\n'
cleaned_df = user_values.join(almost_clean, how='left', rsuffix='_ac')
# print cleaned_df
# print '---------------new DF---------------\n'
# Continue to clean up Data frame to onlye necessary data
duplicate_columns = [element + '_ac' for element in no_touch_columns]
cleaned_df.drop(duplicate_columns, axis=1, inplace=True)
# print cleaned_df
# print '---------------new DF---------------\n'
# Gather Workday data
workday = Okta_Application(app_name='workday')
work_day = workday.app_users[[
'email', 'businessTitle', 'managerUserName', 'accountType'
]].copy()
work_day['email'] = work_day['email'].map(lambda x: x.lower())
# print '---------------new DF---------------\n'
# Merge the workday title and manager data with the SFDC permissions
full_clean = cleaned_df.merge(work_day,
how='left',
left_on='Email',
right_on='email')
# print full_clean['managerUserName']
full_clean = full_clean[~pd.isnull(full_clean['businessTitle'])]
notify = Notifier()
notify.alert_the_light()
notify.alert_homer()
return full_clean