class Codex:
"""
Acts as a conduit to a Salesforce instance, as well as a central
repository for metadata on the tables in the instance.
"""
def __init__(self):
self.client = Salesforce(username=SFCONFIG.username,
password=SFCONFIG.password,
security_token=SFCONFIG.security_token)
self._tables = dict()
for o in self.client.describe()['sobjects']:
n = o['name']
if o['createable'] and (n in SFCONFIG.tables
or len(SFCONFIG.tables) == 0):
self.get_table(n)
def get_table(self, table_api_name: str):
"""
Adds a table from the attached Salesforce instance as a new
Table object attribute on the Codex.
Args:
table_api_name: A string, the API name of the table in the
attached Salesforce instance.
Returns: The generated Table object.
"""
t = Table(getattr(self.client, table_api_name), self)
self._tables[table_api_name] = t
setattr(self, table_api_name, t)
return t
def query(self, q: str):
"""
Queries the Salesforce REST API using the passed SOQL query
string.
Args:
q: A valid SOQL query string.
Returns: A list of OrderedDicts, the records resulting from
the bulk query.
"""
r = self.client.query(q)
results = r['records']
while not r['done']:
r = self.client.query_more(r['nextRecordsUrl'], True)
results += r['records']
return results
def queryb(self, q: str, table_api_name: str) -> list:
"""
Queries the Salesforce Bulk API using the passed SOQL query
string and table_api_name.
Args:
q: A valid SOQL query string.
table_api_name: A table's api_name.
Returns: A list of OrderedDicts, the records resulting from
the bulk query.
"""
return getattr(self.client.bulk, table_api_name).query(q)
class SalesforceFetcher(object):
"""
Class that encapsulates all the fetching logic for SalesForce.
"""
def __init__(self, config_path):
"""
Bootstrap a fetcher class
:param config_path: Path to the configuration file to use for this instance
"""
# Get settings
with open(config_path, 'r') as f:
self.settings = yaml.safe_load(f)
# Configure the logger
log_level = (logging.WARN, logging.DEBUG)[self.settings['debug']]
LOG_FORMAT = logging.Formatter(
'%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger("salesforce-fetcher")
logger.setLevel(log_level)
ch = logging.StreamHandler()
ch.setFormatter(LOG_FORMAT)
logger.addHandler(ch)
logger.debug("Logging is set to DEBUG level")
# let's not output the password
#logger.debug("Settings: %s" % self.settings)
self.logger = logger
self.salesforce = Salesforce(**self.settings['salesforce']['auth'])
self.salesforce_bulk = SalesforceBulk(**self.settings['salesforce']
['auth'],
API_version='46.0')
# Make sure output dir is created
output_directory = self.settings['output_dir']
if not os.path.exists(output_directory):
os.makedirs(output_directory)
def fetch_all(self, fetch_only, airflow_date, fetch_method, days_lookback):
"""
Fetch any reports or queries, writing them out as files in the output_dir
"""
queries = self.load_queries()
for name, query in queries.items():
if fetch_only and name != fetch_only:
self.logger.debug(
"'--fetch-only %s' specified. Skipping fetch of %s" %
(fetch_only, name))
continue
#if name == 'contacts' or name == 'opportunity':
if fetch_method and fetch_method == 'bulk':
self.fetch_soql_query_bulk(name, query, airflow_date)
else:
self.fetch_soql_query(name, query, airflow_date)
reports = self.settings['salesforce']['reports']
for name, report_url in reports.items():
if fetch_only and name != fetch_only:
self.logger.debug(
"'--fetch-only %s' specified. Skipping fetch of %s" %
(fetch_only, name))
continue
self.fetch_report(name, report_url, airflow_date)
if fetch_only:
if fetch_only == 'contact_deletes':
self.fetch_contact_deletes(days=days_lookback,
airflow_date=airflow_date)
else:
self.fetch_contact_deletes(days=days_lookback,
airflow_date=airflow_date)
self.logger.info("Job Completed")
def fetch_contact_deletes(self, days=29, airflow_date=None):
"""
Fetches all deletes from Contact for X days
:param days: Fetch deletes from this number of days to present
:return:
"""
path = self.create_output_path('contact_deletes',
airflow_date=airflow_date)
end = datetime.datetime.now(
pytz.UTC) # we need to use UTC as salesforce API requires this!
records = self.salesforce.Contact.deleted(
end - datetime.timedelta(days=days), end)
data_list = records['deletedRecords']
if len(data_list) > 0:
fieldnames = list(data_list[0].keys())
with open(path, 'w') as f:
writer = DictWriter(f,
fieldnames=fieldnames,
quoting=QUOTE_ALL)
writer.writeheader()
for delta_record in data_list:
writer.writerow(delta_record)
def fetch_report(self, name, report_url, airflow_date=None):
"""
Fetches a single prebuilt Salesforce report via an HTTP request
:param name: Name of the report to fetch
:param report_url: Base URL for the report
:return:
"""
self.logger.info("Fetching report - %s" % name)
sf_host = self.settings['salesforce']['host']
url = "%s%s?view=d&snip&export=1&enc=UTF-8&xf=csv" % (sf_host,
report_url)
resp = requests.get(url,
headers=self.salesforce.headers,
cookies={'sid': self.salesforce.session_id},
stream=True)
path = self.create_output_path(name, airflow_date=airflow_date)
with open(path, 'w+') as f:
# Write the full contents
f.write(resp.text.replace("\"", ""))
# Remove the Salesforce footer (last 7 lines)
f.seek(0, os.SEEK_END)
pos = f.tell() - 1
count = 0
while pos > 0 and count < 7:
pos -= 1
f.seek(pos, os.SEEK_SET)
if f.read(1) == "\n":
count += 1
# So long as we're not at the start of the file, delete all the characters ahead of this position
if pos > 0:
# preserve the last newline then truncate the file
pos += 1
f.seek(pos, os.SEEK_SET)
f.truncate()
def fetch_soql_query_bulk(self, name, query, airflow_date=None):
self.logger.info("BULK Executing %s" % name)
self.logger.info("BULK Query is: %s" % query)
if name == 'contacts' or name == 'contact_updates':
table_name = 'Contact'
elif name == 'opportunity' or name == 'opportunity_updates':
table_name = 'Opportunity'
job = self.salesforce_bulk.create_query_job(table_name,
contentType='CSV',
pk_chunking=True,
concurrency='Parallel')
self.logger.info("job: %s" % job)
batch = self.salesforce_bulk.query(job, query)
# job = '7504O00000LUxuCQAT'
# batch = '7514O00000TvapeQAB'
self.logger.info("Bulk batch created: %s" % batch)
while True:
batch_state = self.salesforce_bulk.batch_state(
batch, job_id=job, reload=True).lower()
if batch_state == 'notprocessed':
self.logger.info("master batch is done")
break
elif batch_state == 'aborted' or batch_state == 'failed':
self.logger.error("master batch failed")
self.logger.error(
self.salesforce_bulk.batch_status(batch_id=batch,
job_id=job,
reload=True))
raise Exception("master batch failed")
self.logger.info("waiting for batch to be done. status=%s" %
batch_state)
time.sleep(10)
count = 0
downloaded = {}
pool = mp.Pool(5)
while True:
stats = {}
batch_count = 0
all_batches = self.salesforce_bulk.get_batch_list(job)
for batch_info in all_batches:
batch_count += 1
batch_state = batch_info['state'].lower()
if batch_state in stats:
stats[batch_state] += 1
else:
stats[batch_state] = 1
if batch_info['id'] == batch:
#self.logger.debug("skipping the master batch id")
continue
elif batch_info['id'] in downloaded:
#self.logger.debug("batch %s already downloaded" % batch_info['id'])
continue
if batch_state == 'completed':
self.logger.debug(
"batch %s (%s of %s)" %
(batch_info['id'], batch_count, len(all_batches)))
for result_id in self.salesforce_bulk.get_query_batch_result_ids(
batch_info['id'], job_id=job):
self.logger.debug("result_id: %s" % result_id)
path = self.create_output_path(
name, result_id, airflow_date=airflow_date)
pool.apply_async(
get_and_write_bulk_results,
args=(batch_info['id'], result_id, job,
self.salesforce_bulk.endpoint,
self.salesforce_bulk.headers(), path))
downloaded[batch_info['id']] = 1
elif batch_state == 'failed':
downloaded[batch_info['id']] = 1
self.logger.error("batch %s failed!" % batch_info['id'])
self.logger.error(
self.salesforce_bulk.batch_status(
batch_id=batch_info['id'], job_id=job,
reload=True))
if 'completed' in stats and stats['completed'] + 1 == batch_count:
self.logger.info("all batches retrieved")
break
elif 'failed' in stats and stats['failed'] + 1 == batch_count:
self.logger.error("NO batches retrieved")
self.logger.error(
self.salesforce_bulk.batch_status(batch_id=batch,
job_id=job,
reload=True))
raise Exception("NO batches retrieved")
elif 'failed' in stats and stats['failed'] + stats[
'completed'] == batch_count:
self.logger.warning("all batches WITH SOME FAILURES")
break
else:
self.logger.info(stats)
time.sleep(5)
try:
self.salesforce_bulk.close_job(job)
except:
pass
pool.close()
pool.join()
def fetch_soql_query(self, name, query, airflow_date=None):
self.logger.info("Executing %s" % name)
self.logger.info("Query is: %s" % query)
path = self.create_output_path(name, airflow_date=airflow_date)
result = self.salesforce.query(query)
self.logger.info("First result set received")
batch = 0
count = 0
if result['records']:
fieldnames = list(result['records'][0].keys())
fieldnames.pop(0) # get rid of attributes
with open(path, 'w') as f:
writer = DictWriter(f,
fieldnames=fieldnames,
quoting=QUOTE_ALL)
writer.writeheader()
while True:
batch += 1
for row in result['records']:
# each row has a strange attributes key we don't want
row.pop('attributes', None)
out_dict = {}
for key, value in row.items():
if type(value) is collections.OrderedDict:
out_dict[key] = json.dumps(value)
else:
out_dict[key] = value
writer.writerow(out_dict)
count += 1
if count % 100000 == 0:
self.logger.debug("%s rows fetched" % count)
# fetch next batch if we're not done else break out of loop
if not result['done']:
result = self.salesforce.query_more(
result['nextRecordsUrl'], True)
else:
break
else:
self.logger.warn("No results returned for %s" % name)
def create_output_path(self, name, filename='output', airflow_date=None):
output_dir = self.settings['output_dir']
if airflow_date:
date = airflow_date
else:
date = time.strftime("%Y-%m-%d")
child_dir = os.path.join(output_dir, name, date)
if not os.path.exists(child_dir):
os.makedirs(child_dir)
filename = filename + ".csv"
file_path = os.path.join(child_dir, filename)
self.logger.info("Writing output to %s" % file_path)
return file_path
def create_custom_query(self,
table_name='Contact',
dir='/usr/local/salesforce_fetcher/queries',
updates_only=False):
"""
The intention is to have Travis upload the "contact_fields.yaml" file
to a bucket where it can be pulled down dynamically by this script
and others (instead of having to rebuild the image on each change)
"""
fields_file_name = table_name.lower() + '_fields.yaml'
fields_file = os.path.join(dir, fields_file_name)
if not os.path.exists(fields_file):
return
with open(fields_file, 'r') as stream:
columns = yaml.safe_load(stream)
query = "SELECT "
for field in columns['fields']:
query += next(iter(field)) + ', '
query = query[:-2] + " FROM " + table_name
if updates_only:
query += " WHERE LastModifiedDate >= LAST_N_DAYS:3"
return query
def load_queries(self):
"""
load queries from an external directory
:return: a dict containing all the SOQL queries to be executed
"""
queries = {}
query_dir = self.settings['salesforce']['query_dir']
for file in os.listdir(query_dir):
if file.endswith(".soql"):
name, ext = os.path.splitext(file)
query_file = os.path.join(query_dir, file)
with open(query_file, 'r') as f:
queries[name] = f.read().strip().replace('\n', ' ')
# explicitly add the non-file queries
queries['contacts'] = self.create_custom_query(table_name='Contact',
dir=query_dir)
queries['contact_updates'] = self.create_custom_query(
table_name='Contact', dir=query_dir, updates_only=True)
queries['opportunity'] = self.create_custom_query(
table_name='Opportunity', dir=query_dir)
queries['opportunity_updates'] = self.create_custom_query(
table_name='Opportunity', dir=query_dir, updates_only=True)
return queries
class SalesforceFetcher(object):
"""
Class that encapsulates all the fetching logic for SalesForce.
"""
def __init__(self, config_path):
"""
Bootstrap a fetcher class
:param config_path: Path to the configuration file to use for this instance
"""
# Get settings
with open(config_path, 'r') as f:
self.settings = yaml.load(f)
# Configure the logger
log_level = (logging.WARN, logging.DEBUG)[self.settings['debug']]
LOG_FORMAT = logging.Formatter(
'%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger("salesforce-fetcher")
logger.setLevel(log_level)
ch = logging.StreamHandler()
ch.setFormatter(LOG_FORMAT)
logger.addHandler(ch)
logger.debug("Logging is set to DEBUG level")
logger.debug("Settings: %s" % self.settings)
self.logger = logger
self.salesforce = Salesforce(**self.settings['salesforce']['auth'])
# Make sure output dir is created
output_directory = self.settings['output_dir']
if not os.path.exists(output_directory):
os.makedirs(output_directory)
def fetch_all(self):
"""
Fetch any reports or queries, writing them out as files in the output_dir
"""
queries = self.load_queries()
for name, query in queries.items():
self.fetch_soql_query(name, query)
reports = self.settings['salesforce']['reports']
for name, report_url in reports.items():
self.fetch_report(name, report_url)
self.logger.info("Job Completed")
def fetch_report(self, name, report_url):
"""
Fetches a single prebuilt Salesforce report via an HTTP request
:param name: Name of the report to fetch
:param report_url: Base URL for the report
:return:
"""
self.logger.info("Fetching report - %s" % name)
sf_host = self.settings['salesforce']['host']
url = "%s%s?view=d&snip&export=1&enc=UTF-8&xf=csv" % (sf_host,
report_url)
resp = requests.get(url,
headers=self.salesforce.headers,
cookies={'sid': self.salesforce.session_id},
stream=True)
path = self.create_output_path(name)
with open(path, 'w+') as f:
# Write the full contents
f.write(resp.text.replace("\"", ""))
# Remove the Salesforce footer (last 7 lines)
f.seek(0, os.SEEK_END)
pos = f.tell() - 1
count = 0
while pos > 0 and count < 7:
pos -= 1
f.seek(pos, os.SEEK_SET)
if f.read(1) == "\n":
count += 1
# So long as we're not at the start of the file, delete all the characters ahead of this position
if pos > 0:
# preserve the last newline then truncate the file
pos += 1
f.seek(pos, os.SEEK_SET)
f.truncate()
def fetch_soql_query(self, name, query):
self.logger.info("Executing %s" % name)
path = self.create_output_path(name)
result = self.salesforce.query(query)
self.logger.info("First result set received")
batch = 0
count = 0
if result['records']:
fieldnames = list(result['records'][0].keys())
fieldnames.pop(0) # get rid of attributes
with open(path, 'w') as f:
writer = DictWriter(f,
fieldnames=fieldnames,
quoting=QUOTE_ALL)
writer.writeheader()
while True:
batch += 1
for row in result['records']:
# each row has a strange attributes key we don't want
row.pop('attributes', None)
writer.writerow(row)
count += 1
if count % 100000 == 0:
self.logger.debug("%s rows fetched" % count)
# fetch next batch if we're not done else break out of loop
if not result['done']:
result = self.salesforce.query_more(
result['nextRecordsUrl'], True)
else:
break
else:
self.logger.warn("No results returned for %s" % name)
def create_output_path(self, name):
output_dir = self.settings['output_dir']
date = time.strftime("%Y-%m-%d")
child_dir = os.path.join(output_dir, name, date)
if not os.path.exists(child_dir):
os.makedirs(child_dir)
filename = "output.csv"
file_path = os.path.join(child_dir, filename)
self.logger.info("Writing output to %s" % file_path)
return file_path
def load_queries(self):
"""
load queries from an external directory
:return: a dict containing all the SOQL queries to be executed
"""
queries = {}
query_dir = self.settings['salesforce']['query_dir']
for file in os.listdir(query_dir):
if file.endswith(".soql"):
name, ext = os.path.splitext(file)
query_file = os.path.join(query_dir, file)
with open(query_file, 'r') as f:
queries[name] = f.read().strip().replace('\n', ' ')
return queries
#'query_all()' will return all archive and non archive records
recordaccounts = sf2.query(
querySOQL
) #this results in records which have less then 200, if record count is more then we need to create batches
#recordaccounts.keys()
# To get records in batches ## Query Records Method ##
recordaccounts2 = sf2.query(querySOQL)
lstrecords = recordaccounts2.get('records')
nextRecordsUrl = recordaccounts2.get(
'nextRecordsUrl'
) #this key appears when recordaccounts2.get('done') is False
while not recordaccounts2.get('done'):
recordaccounts2 = sf2.query_more(nextRecordsUrl, identifier_is_url=True)
lstrecords.extend(recordaccounts2.get('records'))
nextRecordsUrl = recordaccounts2.get('nextRecordsUrl')
# Therefore lstrecords will give older records
df_records = pd.DataFrame(lstrecords)
# Accounts and Oppurtunity query records
querySOQL = "SELECT Id, Name,StageName,Account.Name,Account.Type ,Account.Industry FROM Opportunity"
recordaccounts2 = sf2.query(querySOQL)
lstrecords = recordaccounts2.get('records')
nextRecordsUrl = recordaccounts2.get(
'nextRecordsUrl'
) #this key appears when recordaccounts2.get('done') is False
class ScheduleBot:
"""
slackbot class
"""
def __init__(self):
# instantiate Salesforce instance
self.sf = None
self.project_table_name = 'pse__Proj__c'
# instantiate Slack client
self.slack_client = SlackClient(os.environ.get("SLACK_BOT_TOKEN"))
# bot's user ID in Slack: value is assigned after the bot starts up
self.slack_client_id = None
# variable to count updated tasks
self.number_of_success = 0
def create_salesforce_instance(self, session_id):
self.session_id = session_id
self.sf = Salesforce(instance=SALESFORCE_URL, session_id=session_id)
def set_project_table_name(self):
sobjects = self.sf.query_more("/services/data/v37.0/sobjects/", True)
for sobject in sobjects["sobjects"]:
if sobject["labelPlural"] == "Projects":
self.project_table_name = sobject.name
def parse_bot_commands(self, slack_events):
"""
Parses a list of events coming from the Slack RTM API to find bot commands.
If a bot command is found, this function returns a tuple of command and channel.
If its not found, then this function returns None, None.
"""
for event in slack_events:
if event["type"] == "message" and not "subtype" in event:
user_id, message = self.parse_direct_mention(event["text"])
if user_id == self.slack_client_id:
return message, event["channel"]
return None, None
def parse_direct_mention(self, message_text):
"""
Finds a direct mention (a mention that is at the beginning) in message text
and returns the user ID which was mentioned. If there is no direct mention, returns None
"""
matches = re.search(MENTION_REGEX, message_text)
# the first group contains the username, the second group contains the remaining message
return (matches.group(1), matches.group(2).strip()) if matches else (None, None)
def handle_command(self, command, channel):
"""
Executes bot command if the command is known
"""
# Default response is help text for the user
default_response = "Not sure what you mean. Try *{}*.".format("sync or report")
# Finds and executes the given command, filling in response
response = None
is_command_syntax_correct = False
for example_command in EXAMPLE_COMMANDs:
if command.startswith(example_command):
is_command_syntax_correct = True
if is_command_syntax_correct == False:
self.slack_client.api_call(
"chat.postMessage",
channel=channel,
text=default_response
)
else:
# This is where you start to implement more commands!
command_args = command.split(" ")
if command_args[0] == u'report':
self.get_tasks_by_weeks(channel)
self.slack_client.api_call(
"chat.postMessage",
channel=channel,
text=response or 'Upload Finished!'
)
else:
session_id = command_args[1]
self.create_salesforce_instance(session_id)
is_session_valid = True
try:
self.sf.query_more("/services/data/v38.0/sobjects/", True)
except:
is_session_valid = False
if is_session_valid == False:
response = 'Session is incorrect or expired!'
# Sends the response back to the channel
self.slack_client.api_call(
"chat.postMessage",
channel=channel,
text=response
)
return True
else:
if command_args[0] == u'sync':
self.sync_tasks(channel)
if command_args[0] == u'projectplan':
modified_start = command_args[2]
self.download_attachments(channel, modified_start)
def run(self):
if self.slack_client.rtm_connect(with_team_state=False):
print("Starter Bot connected and running!")
# Read bot's user ID by calling Web API method `auth.test`
self.slack_client_id = self.slack_client.api_call("auth.test")["user_id"]
while True:
try:
command, channel = self.parse_bot_commands(self.slack_client.rtm_read())
if command:
self.handle_command(command, channel)
time.sleep(RTM_READ_DELAY)
except:
self.slack_client.rtm_connect(with_team_state=False)
self.slack_client_id = self.slack_client.api_call("auth.test")["user_id"]
else:
print("Connection failed. Exception traceback printed above.")
def sync_tasks(self, channel):
self.slack_client.api_call(
"chat.postMessage",
channel=channel,
text='Please wait a moment...'
)
is_session_valid = True
try:
self.sf.query_more("/services/data/v38.0/sobjects/", True)
except:
is_session_valid = False
test_limit = 0
if is_session_valid and test_limit < 10:
try:
sf_tasks = []
sf_project_task = SFType('pse__Project_Task__c', self.session_id, SALESFORCE_URL)
sf_project_task_assign = SFType('pse__Project_Task_Assignment__c', self.session_id, SALESFORCE_URL)
float_api = FloatAPI()
projects = float_api.get_projects()
for project in projects:
m = re.search(r'(?<=-)\d+', project["name"])
if m is not None:
sf_project_id = m.group(0)
# float_tasks = float_api.test()
tmp_float_tasks = float_api.get_tasks_by_params(
'project_id={}'.format(project["project_id"])
)
float_tasks = []
float_task_hash = {}
for tmp_task in tmp_float_tasks:
tmp_user = float_api.get_person_by_id(tmp_task["people_id"])
task_name = tmp_task["task_id"]
if tmp_user['active'] == 1:
tmp_task["users"] = self.format_username(tmp_user["name"])
if task_name not in float_task_hash:
float_task_hash[task_name] = tmp_task
float_tasks.append(tmp_task)
# else:
# first_start_date = datetime.strptime(
# float_task_hash[task_name]["start_date"],
# '%Y-%m-%d'
# ).strftime("%V")
# second_start_date = datetime.strptime(
# tmp_task["start_date"], '%Y-%m-%d'
# ).strftime("%V")
# if first_start_date == second_start_date:
# float_task_hash[task_name]["users"] = self.format_username(float_task_hash[task_name]["users"]) + ', ' + self.format_username(tmp_user["name"])
# else:
# tmp_task["is_duplicate"] = True
# float_task_hash[task_name] = tmp_task
# float_tasks.append(tmp_task)
# if len(float_tasks) > 0:
# if 'PR-207534' in project["name"]:
# import pdb
# pdb.set_trace()
if len(float_tasks) > 0:
# tags = float_api.get_project_by_id(float_tasks[0]["project_id"])["tags"]
sf_tasks = self.get_tasks_by_project_id('PR-'+sf_project_id)
for float_task_key in float_task_hash.keys():
# fl_user = float_api.get_person_by_id(float_task["people_id"])
float_task = float_task_hash[float_task_key]
if 'is_duplicate' in float_task:
project_name = 'No name'
if project and 'name' in project:
project_name = project["name"]
self.slack_client.api_call(
"chat.postMessage",
channel=channel,
text="Project: {} has two tasks. "\
"Please manually sync the second in Salesforce, "\
"or use a different task name".format(project["name"])
)
else:
# if 'PR-207534' in project["name"]:
for sf_task in sf_tasks:
if float_task["name"] == sf_task["Name"]:
start_datetime = datetime.strptime(float_task['start_date'], '%Y-%m-%d') + timedelta(days=1)
end_datetime = datetime.strptime(float_task['end_date'], '%Y-%m-%d') + timedelta(days=1)
start_datetime_obj = eastern.localize(start_datetime).strftime("%Y-%m-%dT%H:%M:%S")
end_datetime_obj = eastern.localize(end_datetime).strftime("%Y-%m-%dT%H:%M:%S")
float_names = float_task["users"].replace('*', '').split(',')
contacts_num = len(float_names)
for username in float_names:
float_username = username.strip()
msg = ''
params = {}
# if sf_task['pse__Assigned_Resources__c'] != float_task["users"]:
params["pse__Assigned_Resources__c"] = float_username
params["pse__Assigned_Resources_Long__c"] = float_username
msg = 'assigned resources '
# if self.remove_delta(sf_task['pse__Start_Date_Time__c']) != start_datetime_obj.decode() or self.remove_delta(sf_task['pse__End_Date_Time__c']) != end_datetime_obj.decode():
params['pse__Start_Date_Time__c'] = start_datetime_obj
params['pse__End_Date_Time__c'] = end_datetime_obj
msg = 'start & end time '
contact_info = self.get_contact_id(float_username)
d_project_task_asssign = {}
if contact_info is not None:
if contact_info['is_active']:
d_project_task_asssign['pse__Resource__c'] = contact_info['Id']
d_project_task_asssign['resource_lookup__c'] = contact_info['Id']
else:
d_project_task_asssign['pse__External_Resource__c'] = contact_info['Id']
try:
result = sf_project_task.update(sf_task["Id"], params, False)
te_status = self.task_exist_in_assignment(sf_task["Id"])
ta_result = None
if te_status['is_exist']:
if contact_info['is_active']:
resource_id = contact_info['Id']
else:
resource_id = d_project_task_asssign['pse__External_Resource__c']
if resource_id != te_status['resource_id']:
# pdb.set_trace()
try:
ta_result = sf_project_task_assign.update(te_status['Id'], d_project_task_asssign, False)
except Exception as e:
print(e, project['name'], float_username, "##########")
task_status_response = "{}: {} | {} | project {}".format(
float_username,
'User with same role is already assgined',
float_task["name"],
project["name"])
self.slack_client.api_call(
"chat.postMessage",
channel=channel,
text=task_status_response
)
else:
# pdb.set_trace()
d_project_task_asssign['pse__Project_Task__c'] = sf_task['Id']
# d_project_task_asssign['pse__Project_ID__c'] = sf_task['Project_ID__c']
ta_result = sf_project_task_assign.create(d_project_task_asssign, False)
test_limit = test_limit + 1
task_status_response = ''
if result < 400 and ta_result is not None:
self.number_of_success = self.number_of_success + 1
task_status_response = "{} | {} | project {}".format(
msg,
float_task["name"],
project["name"])
self.slack_client.api_call(
"chat.postMessage",
channel=channel,
text=task_status_response
)
except Exception as e:
print(e)
continue
else:
self.slack_client.api_call(
"chat.postMessage",
channel=channel,
text='Contact: {} doesn\'t exist'.format(float_username)
)
except Exception as e:
self.slack_client.api_call(
"chat.postMessage",
channel=channel,
text=e.message
)
else:
response = 'Session is incorrect or expired!'
# Sends the response back to the channel
self.slack_client.api_call(
"chat.postMessage",
channel=channel,
text=response or 'Finished!'
)
def download_attachments(self, channel, modified_time):
self.slack_client.api_call(
"chat.postMessage",
channel=channel,
text='Please wait a moment...'
)
OWNERS = ['Ashley Tuley', 'Brian DeHetre', 'Carlos Rojas',
'Chad Ready', 'Jacci Oglesby', 'Jessica Berry',
'Leslie Lyle', 'Liam Perigo', 'Lori Foster',
'Marie Alberal', 'Michelle Lee', 'Scott Badger',
'Susan Fulmer', 'Tiffany Vance-Huffman']
# Get projects that contains "ATLAS"
try:
search_key = "ATLAS"
query = "select Id, Name, Assigned_Owner__c from pse__Proj__c where Name like '%{}%'".format(search_key)
projects = self.sf.query(query)
csv_data = []
if projects["totalSize"] > 0:
for project in projects["records"]:
# Get owner
if project['Assigned_Owner__c']:
contact = self.get_contact_by_id(project['Assigned_Owner__c'])
print('Contact: ', contact)
if contact and contact in OWNERS:
start_datetime = datetime.strptime(modified_time, '%Y-%m-%d') + timedelta(days=1)
start_datetime_obj = eastern.localize(start_datetime).strftime("%Y-%m-%dT%H:%M:%S.000+0000")
attachment_query = "select Id, Name, LastModifiedDate from Attachment where (ParentId = '{}' and \
Name like '%.xls%' and LastModifiedDate > {})".format(project['Id'], start_datetime_obj)
attachments = self.sf.query(attachment_query)
if attachments["totalSize"] > 0:
for attachment in attachments["records"]:
csv_data.append({
'resource_name': contact,
'project_name': project['Name'],
'doc_id': attachment['Id'],
'attachment_name': attachment['Name'],
'last_modiled_date': attachment['LastModifiedDate'],
'attachment_url': DOWNLOAD_LINK+attachment['Id']})
fieldnames = ['resource_name', 'project_name', 'attachment_id',
'attachment_name', 'attachment_url', 'last_modiled_date']
report_str = './reports/' + uuid.uuid4().hex + '.csv'
with open(report_str, 'a') as csv_file:
writer = csv.DictWriter(csv_file, fieldnames=fieldnames)
for cdata in csv_data:
writer.writerow({
'resource_name': self.validate_text(cdata['resource_name']),
'project_name': self.validate_text(cdata['project_name']),
'attachment_id': self.validate_text(cdata['doc_id']),
'attachment_name': self.validate_text(cdata['attachment_name']),
'last_modiled_date': self.validate_text(cdata['last_modiled_date'].replace('.000+0000', '')),
'attachment_url': self.validate_text(cdata['attachment_url'])})
doc_id = cdata['doc_id']
doc_name = self.sf.Attachment.get(doc_id)['Name']
download_url = 'https://{base_url}/services/data/v47.0/sobjects/Attachment/{doc_id}/body'.format(
doc_id=doc_id, base_url=SALESFORCE_URL)
result = self.sf.session.get(download_url,
headers=self.sf.headers,stream=True)
with open('./excels/'+self.validate_text(cdata['attachment_name']),'wb+') as file:
#retrieve the bytes from the resources incrementally
for chunk in result.iter_content(1000):
file.write(chunk)
file.close()
csv_file.close()
self.upload(report_str, channel)
self.slack_client.api_call(
"chat.postMessage",
channel=channel,
text='Upload Finished! {} items found'.format(len(csv_data))
)
except Exception as e:
self.slack_client.api_call(
"chat.postMessage",
channel=channel,
text=e.message
)
def validate_text(self, text):
return unicode(text).encode('utf-8')
def get_tasks_by_project_id(self, project_id):
sobject = self.sf.query_more("/services/data/v38.0/sobjects/pse__Proj__c", True)
projects = sobject["recentItems"]
tasks = []
milestone_obj = self.get_milestone_id(project_id)
if milestone_obj is not None:
sf_tasks = self.get_task_by_milestone_and_product(
milestone_obj['project_id'],
milestone_obj['milestone_id']
)
for task in sf_tasks:
formatted_task = self.get_detail_task(task["attributes"]["url"])
tasks.append(formatted_task)
return tasks
def get_detail_task(self, task_url):
sobject = self.sf.query_more(task_url, True)
return sobject
def get_milestone_id(self, project_id, milestone_name='Implementation and Training'):
query = "select Id, Name from pse__Proj__c where pse__Project_ID__c='{}'".format(project_id)
sobject = self.sf.query(query)
if sobject["totalSize"] == 0:
return None
global_project_id = sobject["records"][0]['Id']
query = "select Id, Name from pse__Milestone__c \
where pse__Project__c='{}' and Name='{}'".format(global_project_id, milestone_name)
sobject = self.sf.query(query)
if sobject["totalSize"] == 0:
return None
return {
'milestone_id': sobject["records"][0]['Id'],
'project_id': global_project_id
}
def get_task_by_milestone_and_product(self, project_id, milestone_id):
query = "select Id, pse__Project__c from pse__Project_Task__c \
where (pse__Project__c='{}' and pse__Milestone__c='{}')".format(project_id, milestone_id)
sobject = self.sf.query(query)
if sobject["totalSize"] == 0:
return []
return sobject["records"]
def get_contact_by_id(self, id):
query = "select Id, Name, pse__Is_Resource__c, pse__Is_Resource_Active__c from Contact where Id='{}'".format(id)
result = self.sf.query(query)
if result['totalSize'] == 0:
return None
if result['records'][0]['pse__Is_Resource__c'] == True and result['records'][0]['pse__Is_Resource_Active__c']:
return result['records'][0]['Name']
def get_contact_id(self, username):
query = "select Id, Name, pse__Is_Resource__c, pse__Is_Resource_Active__c from Contact where name='{}'".format(username)
result = self.sf.query(query)
if result['totalSize'] == 0:
return None
idx = 0
is_active = False
for record in result['records']:
if record['pse__Is_Resource__c'] == True and record['pse__Is_Resource_Active__c']:
is_active = True
break
idx = idx + 1
if is_active == True:
return {'is_active': is_active, 'Id': result['records'][idx]['Id'],
'Name': result['records'][idx]['Name']}
return {'is_active': is_active, 'Id': username}
def task_exist_in_assignment(self, task_id):
result = self.sf.query("select Id, Name, pse__Resource__c from pse__Project_Task_Assignment__c \
where pse__Project_Task__c='{}'".format(task_id))
if result['totalSize'] == 0:
return { 'is_exist': False }
return {'is_exist': True,
'Id': result['records'][0]['Id'],
'resource_id': result['records'][0]['pse__Resource__c']}
def format_time(self, time_val):
if time_val is None:
return time_val
return time_val.replace(' ', 'T')
def remove_delta(self, time_val):
if time_val is None:
return time_val
return time_val.replace('+0000', '').replace('.000', '')
def format_username(self, val):
if val is None:
return None
return val.split("-")[0].strip()
def upload(self, file, channel):
try:
with open(file) as file_content:
res = self.slack_client.api_call(
"files.upload",
channels=channel,
file=file_content,
title="Test upload"
)
file_content.close()
except Exception as e:
self.slack_client.api_call(
"chat.postMessage",
channel=channel,
text=e.message
)
return True
def get_tasks_by_weeks(self, channel):
float_api = FloatAPI()
report_schedules = []
fieldnames = [
"start_date",
"on_vocation",
"in_training_for_teaching",
"in_training_for_learning",
"onsite_go_live",
"onsite_setup",
"remote_training"
]
# create csv file with header
with open('report.csv', 'w') as csv_file:
writer = csv.DictWriter(csv_file, fieldnames=fieldnames)
writer.writeheader()
year = datetime.now().strftime("%Y")
month = datetime.now().strftime("%m")
next_month = datetime.now() + dateutil.relativedelta.relativedelta(months=2)
before_month = datetime.now() - dateutil.relativedelta.relativedelta(months=1)
start_next_month = datetime.strptime(
'{}-{}-1'.format(year, next_month.strftime("%m")),
'%Y-%m-%d'
)
start_date_of_month = datetime.strptime(
'{}-{}-1'.format(year, before_month.strftime("%m")),
'%Y-%m-%d'
)
last_date_of_month = start_next_month - timedelta(days=1)
start_weeknum = start_date_of_month.strftime("%V")
end_weeknum = last_date_of_month.strftime("%V")
for week_num in range(int(start_weeknum), int(end_weeknum)):
date_obj = get_start_end_dates(2019, week_num)
start_date = date_obj["start_datetime"].strftime("%Y-%m-%d")
end_date = date_obj["end_datetime"].strftime("%Y-%m-%d")
schedule_tasks = float_api.get_tasks_by_params(
'start_date={}&end_date={}'.format(start_date, end_date)
)
if len(schedule_tasks) > 0:
with open('report.csv', 'a') as csv_file:
writer = csv.DictWriter(csv_file, fieldnames=fieldnames)
report_schedule = {
"start_date": start_date,
"on_vocation": 0,
"in_training_for_teaching": 0,
"in_training_for_learning": 0,
"onsite_go_live": 0,
"onsite_setup": 0,
"remote_training": 0
}
for schedule_task in schedule_tasks:
if "paid time off" in schedule_task["name"].lower():
report_schedule["on_vocation"] = report_schedule["on_vocation"] + 1
if "one on one" in schedule_task["name"].lower():
project_item = float_api.get_project_by_id(schedule_task["project_id"])
if project_item["name"] is not None:
if "trainer" in project_item["name"].lower():
report_schedule["in_training_for_teaching"] = report_schedule["in_training_for_teaching"] + 1
if "trainee" in project_item["name"].lower():
report_schedule["in_training_for_learning"] = report_schedule["in_training_for_learning"] + 1
if "remote enduser" in schedule_task["name"].lower():
report_schedule["remote_training"] = report_schedule["remote_training"] + 1
if "enduser" in schedule_task["name"].lower():
report_schedule["onsite_setup"] = report_schedule["onsite_setup"] + 1
if "go live" in schedule_task["name"].lower():
report_schedule["onsite_go_live"] = report_schedule["onsite_go_live"] + 1
self.slack_client.api_call(
"chat.postMessage",
channel=channel,
text='Get tasks: {} ~ {}'.format(start_date, end_date)
)
writer.writerow({
"start_date": report_schedule["start_date"],
"on_vocation": report_schedule["on_vocation"],
"in_training_for_teaching": report_schedule["in_training_for_teaching"],
"in_training_for_learning": report_schedule["in_training_for_learning"],
"onsite_go_live": report_schedule["onsite_go_live"],
"onsite_setup": report_schedule["onsite_setup"],
"remote_training": report_schedule["remote_training"],
})
csv_file.close()
self.upload('report.csv', channel)
with open("failures.log", "w") as fail_log:
sf = Salesforce(username=username, password=password, security_token=security_token, sandbox=False)
result = sf.query(soql % (comma_separated_column_names, table))
done = False
while not done:
for r in result["records"]:
line_number = line_number + 1
# Here's where we actually print...
try:
row = [dig_out_value(c, r, table) for c in columns]
writer.writerow(row)
printed_lines = printed_lines + 1
except TypeError:
fail_log.write("# %d\n" % line_number)
fail_log.write(str(r))
fail_log.write("\n")
skipped_lines = skipped_lines + 1
if result['done']:
done = True
else:
result = sf.query_more(result['nextRecordsUrl'], identifier_is_url=True)
if skipped_lines > 0:
sys.stderr.write("WARNING: Skipped %d lines.\n" % skipped_lines)
sys.exit(0)
else:
sys.stderr.write("OK: wrote all lines successfully.\n")
sys.exit(0)
security_token=security_token,
sandbox=False)
result = sf.query(soql % (comma_separated_column_names, table))
done = False
while not done:
for r in result["records"]:
line_number = line_number + 1
# Here's where we actually print...
try:
row = [dig_out_value(c, r, table) for c in columns]
writer.writerow(row)
printed_lines = printed_lines + 1
except TypeError:
fail_log.write("# %d\n" % line_number)
fail_log.write(str(r))
fail_log.write("\n")
skipped_lines = skipped_lines + 1
if result['done']:
done = True
else:
result = sf.query_more(result['nextRecordsUrl'],
identifier_is_url=True)
if skipped_lines > 0:
sys.stderr.write("WARNING: Skipped %d lines.\n" % skipped_lines)
sys.exit(0)
else:
sys.stderr.write("OK: wrote all lines successfully.\n")
sys.exit(0)
class SF2MySQLCopy:
def __init__(self):
v = mysql.connector.version.VERSION
if v[0] < 2 or v[1] < 1 or v[2] < 3:
raise Exception('Minimum required MySQL driver version is 2.1.3')
self.salesforce = Salesforce(username=sf_credentials.SF_USER, password=sf_credentials.SF_PASSWD, security_token=sf_credentials.SF_SEC_TOKEN, sandbox=False, sf_version='34.0')
self.salesforce.headers['Accept-Encoding'] = 'gzip'
# https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/headers_queryoptions.htm
self.salesforce.headers['Sforce-Query-Options'] = 'batchSize=2000'
self.mysql_connection = mysql.connector.connect(user='******', password='******',
host=mysql_config.MYSQL_HOST,
port=mysql_config.MYSQL_PORT,
database=mysql_config.MYSQL_SCHEMA,
charset=mysql_config.MYSQL_CHARSET,
use_unicode=True)
def copy_sobjects(self, sobjects_names):
for sobject in sobjects_names:
self._actually_copy_sobject(sobject);
def copy_sobject(self, sobject_name):
self._actually_copy_sobject(self.mysql_connection, sobject_name)
def _actually_copy_sobject(self, mysql_connection, sobject_name):
# Step 1: Obtain SObject's metadata and instantiate helper objects
stype = self.salesforce.__getattr__(sobject_name)
print 'Retrieving metadata for SObject ' + sobject_name + '...'
sobject_desc = stype.describe()
sobject_fields_map = self._sobject_fields_map(sobject_desc['fields'])
table_creator = MySQLTableCreator(sobject_desc)
soql_helper = SOQLHelper(sobject_desc)
cursor = mysql_connection.cursor()
# Step 2: Drop table if table already exists
drop_table_statement = table_creator.generate_drop_table()
try:
cursor.execute(drop_table_statement)
print 'Dropped MySQL table: ' + table_creator.mysql_table_name
except mysql.connector.Error as err:
print 'MySQL Error when executing: ' + drop_table_statement
# Step 3: Create table
create_table_statement = table_creator.generate_create_table()
create_index_statements = table_creator.generate_indexes_for_references()
table_name = table_creator.mysql_table_name
try:
cursor.execute(create_table_statement)
for create_index_statement in create_index_statements:
cursor.execute(create_index_statement)
print 'Created MySQL table: ' + table_name
except mysql.connector.Error as err:
print 'MySQL Error when executing: ' + create_table_statement
print 'Skipping SObject copy ...'
return
# Step 4: Start to pump data from Salesforce
soql_query = soql_helper.generate_soql_select()
print 'Querying SObject ' + sobject_desc['name']
timer = Timer()
timer.start()
results = self.salesforce.query(soql_query)
total_size = results['totalSize'] # Int
print 'Salesforce query retrieved ' + str(total_size) + ' records for SObject ' + sobject_desc['name']
done = False
paged = False
page_number = 0
pages_count = 0
insert_statement = table_creator.insert_statement()
while not done:
done = results['done']
if not done and not paged:
paged = True
sf_records = results['records']
page_size = len(sf_records)
if pages_count == 0:
pages_count = total_size / page_size
pages_count = int(math.ceil(pages_count))
if not done:
print 'Query result is paged'
if paged:
page_number = page_number + 1
print 'Page #' + str(page_number) + ': ' + str(page_size) + ' records'
mysql_records = []
for sf_record in sf_records:
mysql_record = table_creator.sf_record2mysql_record(sf_record)
mysql_records.append(mysql_record)
# Step 5: Insert records into MySQL
print 'Inserting ' + str(len(mysql_records)) + ' records into the ' + table_name + ' table'
try:
cursor.executemany(insert_statement, mysql_records)
mysql_connection.commit()
except mysql.connector.Error as err:
print 'MySQL Error when executing: ' + insert_statement
print 'Aborting ...'
return
if not done:
if 'nextRecordsUrl' in results:
next_records = results['nextRecordsUrl']
print 'Retrieving page ' + str(page_number + 1) + ' of ' + str(pages_count) + ' ...'
print 'ETC: ' + timer.formatted_etc(page_number, pages_count)
results = self.salesforce.query_more(next_records, True)
else:
done = True
cursor.close()
def _sobject_fields_map(self, sobject_fields):
map = {}
for field in sobject_fields:
map[field['name']] = field
return map
