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