def get_data_in_batches( self, salesforce_object: str, columns: Optional[list] = None, data_from: Optional[datetime] = None, data_until: Optional[datetime] = None, batch_size: int = 10000, ) -> List[Dict[str, Any]]: """Generator to return all data of a salesforce object""" # Figure out what field to use for date comparison fields = self.get_fields_of_object(salesforce_object) incrementer = None if "SystemModstamp" in fields: incrementer = "SystemModstamp" elif "LastModifiedDate" in fields: incrementer = "LastModifiedDate" elif "CreatedDate" in fields: incrementer = "CreatedDate" # Create SOQL query query = "SELECT\n\t{0}\nFROM {1}".format( ",\n\t".join(columns or fields), salesforce_object) where_clauses = [] if data_from and incrementer: where_clauses.append(f"{incrementer} >= " + format_soql("{0}", data_from)) if data_until and incrementer: where_clauses.append(f"{incrementer} < " + format_soql("{0}", data_until)) if where_clauses: query += "\nWHERE " + "\n AND ".join(where_clauses) self.log.info(f"Querying data with this SOQL query:\n\n{query}") # Yield results result = self.sf_conn.query(query, include_deleted=True) data = [] while (result.get("done") and result.get("rescords")) or result.get("nextRecordsUrl"): result_data = result.pop("records") for datum in result_data: del datum["attributes"] datum = dict(datum) data += result_data next_page = result.get("nextRecordsUrl") if next_page: result = self.sf_conn.query_more(next_page, True) if len(data) >= batch_size: yield data data = [] if data: yield data # Make sure there was no error on the way if not result.get("done"): raise Exception("SOQL query error! Response: {0}".format(result))
def verify(): # secret to check if the client is valid secret = request.args.get('secret') correct_secret = os.environ.get('VERIFY_SIGNUP_SECRET') if (not secret or secret != correct_secret): response = jsonify({ "code": "invalid_secret", "description": "Access denied." }) response.status_code = 401 return response # parses arguments that user sent via query string email = request.args.get('email') firstname = request.args.get('firstname') lastname = request.args.get('lastname') name = firstname + " " + lastname print("name: ", name) # salesforce query based on the email, firstname & lastname result = sf.query( format_soql( "SELECT Id, Email FROM Contact WHERE (Has_Youth_App_Account__c = true AND email = {email_value} AND name={full_name})", email_value=email, full_name=name)) if (result["totalSize"] == 1): return {"verified": bool(1)} # true return {"verified": bool(0)} # false
def processCheckin(api_url): sf = getSalesforce() # Find CM record in SF, mark as checked in. registration = requests.get(api_url, headers=AUTH_HEADER_EB, params={ "expand": "category", "expand": "promotional_code", "expand": "promo_code" }) registration = registration.json() print("Processing checkin...") print(registration) campaignMemberQuery = sf.query( format_soql( "SELECT Id FROM CampaignMember WHERE Eventbrite_Attendee_ID__c = '{attendeeID}'" .format(attendeeID=registration['id'].strip().replace( '"', '\\"').replace("'", "\\'")))) if campaignMemberQuery['totalSize'] >= 1: print("Campaign Member found, updating status to Checked In") campaignMemberID = campaignMemberQuery['records'][0]['Id'] result = sf.CampaignMember.update(campaignMemberID, {'Attendee_Status__c': 'Checked In'}) print("Done.") else: print("Campaign member not found, skipping...")
def test_datetime(self): """ Datetime literals are inserted """ query = "select foo from bar where date = {}" expected = "select foo from bar where date = 1987-02-01T01:02:03+00:00" quoted = format_soql( query, datetime(1987, 2, 1, 1, 2, 3, tzinfo=timezone.utc)) self.assertEqual(quoted, expected)
def test_lists(self): """ Conversion of lists to parentheses groups """ query = "select foo from bar where x in {} and y in {named}" expected = "select foo from bar where" " x in ('value1','val\\'ue1\\n')" " and y in ('value2','val\\'ue2\\n')" quoted = format_soql(query, ["value1", "val'ue1\n"], named=["value2", "val'ue2\n"]) self.assertEqual(quoted, expected)
def test_escaped_chars(self): """ Quotes and escape special chars """ query = "select foo from bar where x = {} and y = {named}" expected = ("select foo from bar where" " x = 'val\\'ue1\\n' and y = 'val\\'ue2\\n'") quoted = format_soql(query, 'val\'ue1\n', named='val\'ue2\n') self.assertEqual(quoted, expected)
def podOutcomes(user): # parses arguments that user sent via query string user_id = user.get('id') # Extract current pod to update from request arguments pod = request.args.get('pod') pod_map_name = pod + '_POD_Map__c' # salesforce query for all the field names and labels in the trainee pod desc = getattr(sf, pod_map_name).describe() field_names_and_labels = [(field['name'], field['label']) for field in desc['fields']] # filter to get only the trainee outcome field names pod_field_names = [ field[0][:-12] for field in field_names_and_labels if "Completed__c" in field[0] ] all_fields_to_fetch = [ field[0] for field in field_names_and_labels if "Completed__c" in field[0] or "Checked__c" in field[0] ] # salesforce query of each *completed* outcome # in trainee pod, based on the email and name soql = ("SELECT {} FROM " + pod_map_name).format( ','.join(all_fields_to_fetch)) sf_result = sf.query( format_soql((soql + " WHERE Contact__r.auth0_user_id__c={user_id}"), user_id=user_id)) # organizing and putting data into dictionary outcome_dict outcome_dict = {} for field in pod_field_names: field_type = field[3:6].upper() outcome_dict[field_type] = {} # count the *completed* outcomes for each field: if sf_result['records']: outcome_dict[field_type]['completed_outcomes'] = sf_result[ 'records'][0][field + 'Completed__c'] outcome_dict[field_type]['checked_outcomes'] = sf_result[ 'records'][0].get(field + 'Checked__c') else: outcome_dict[field_type]['completed_outcomes'] = 0 outcome_dict[field_type]['checked_outcomes'] = 0 # count the *total* outcomes for each field: outcome_dict[field_type]['total_outcomes'] = 0 for name_and_label in field_names_and_labels: if "_Outcome_" + field_type in name_and_label[0]: outcome_dict[field_type]['total_outcomes'] += 1 #putting in the name of each field into dictionary if field in name_and_label[0]: name = name_and_label[1].partition("Outcomes")[ 0] #only grab part in label up to the word "Outcomes" outcome_dict[field_type]['name'] = name return outcome_dict
def findValid(user): user_id = user.get('id') global pod_names total_dict = {} for pod_num, pod_map_name in enumerate(pod_names): desc = getattr(sf, pod_map_name).describe() field_names_and_labels = [(field['name'], field['label']) for field in desc['fields']] field_names = [field['name'] for field in desc['fields']] # Query for all fields for this user soql = ("SELECT {} FROM " + pod_map_name).format(','.join(field_names)) sf_result = sf.query( format_soql( (soql + " WHERE (Contact__r.auth0_user_id__c={user_id})"), user_id=user_id)) if len(sf_result["records"]) == 0: total_dict[pod_map_name] = { 'status': 'does not exist', 'completed': False } continue tot_outcomes = 0 tot_completed = 0 for field in field_names: if 'Outcome' in field: tot_outcomes += 1 for name, value in sf_result["records"][0].items(): if 'Outcome' in name and value == True: tot_completed += 1 if pod_num == 0: status = 'allowed' else: while pod_num > 0: if total_dict[pod_names[pod_num - 1]]['completed'] == True: pod_num -= 1 status = 'allowed' else: status = 'no access' break total_dict[pod_map_name] = { 'status': status, 'completed': True if tot_completed == tot_outcomes else False, 'current': False } # marks current sections for display prev_completed = False for _, item in total_dict.items(): if prev_completed and not item['completed']: item['current'] = True break elif item['completed']: prev_completed = True return total_dict
def amo_check_user_for_deletion(user_id): """If a user has no addons their AMO info should be removed""" addons = sfdc.sf.query( sfapi.format_soql( "SELECT Id FROM DevAddOn__c WHERE AMO_Contact_ID__c = {contact_id} LIMIT 1", contact_id=user_id, ), ) if not addons["records"]: sfdc.update({"id": user_id}, {"amo_id": None, "amo_user": False})
def get_salesforce_opportunity(opportunity_id): opportunity_info = {} if redis_connection: opportunity_json = redis_connection.get(opportunity_id) if opportunity_json: opportunity_info = json.loads(opportunity_json) if not opportunity_info: salesforce_api = salesforce_connection() opportunity_query = format_soql( "SELECT " " Id, Name, AccountId, IsClosed, " " CloseDate, StageName, OpportunityNumber__c " "FROM Opportunity " "WHERE OpportunityNumber__c = {}", str(opportunity_id).strip() ) try: opp_results = salesforce_api.query(opportunity_query) opportunity_info['totalSize'] = opp_results.get('totalSize', 0) for i in opp_results['records']: if 'attributes' in i: del i['attributes'] opportunity_info.update(i) # Rename custom field to be readable opportunity_info['Number'] = opportunity_info.pop('OpportunityNumber__c') except SalesforceMalformedRequest: flask.abort(404, description='Invalid SalesForce Request') opportunity_valid = opportunity_info.get('totalSize', 0) if redis_connection: redis_connection.setex(opportunity_id, session_lifetime, json.dumps(opportunity_info)) # If the opportunity not found in SFDC that means its invalid opportunity number if opportunity_valid == 0: return False else: # Business rules for invalid opportunity: # If the opportunity is Closed # If the current date is more than the CloseDate # If opportunity's stage in Closed Booked, Closed Lost or Closed On current_date = datetime.utcnow().strftime("%Y-%m-%d") is_closed = opportunity_info.get('IsClosed', False) close_date = opportunity_info.get('CloseDate') stage_name = opportunity_info.get('StageName') if is_closed or current_date > close_date or \ stage_name in ('Closed Booked', 'Closed Lost', 'Closed Won'): return False return True
def HomeScreenoutcomes(pod_map_name): user_id = user.get('id') desc = getattr(sf, pod_map_name).describe() field_names_and_labels = [(field['name'], field['label']) for field in desc['fields']] filtered_field_names = [ field for field in field_names_and_labels if "Completed__c" in field[0] or field[0] == "Total_Checked__c" ] Pod_field_names = [field[0] for field in filtered_field_names] # salesforce query of each completed outcome # in trainee pod, based on the email and name query_from = "SELECT {} FROM " + pod_map_name soql = query_from.format(','.join(Pod_field_names)) Pod_sf_result = sf.query( format_soql( (soql + " WHERE Contact__r.auth0_user_id__c={user_id}"), user_id=user_id)) if len(Pod_sf_result["records"]) == 0: Pod_outcome_sum = 0 Pod_total_count = 0 else: # calculate Trainee total Pod_total_count = 0 #create new value in sf_result dict that will store field's total outcomes for field in Pod_field_names: field_type = field[3:6].upper() for name_and_label in field_names_and_labels: if "_Outcome_" + field_type in name_and_label[0]: Pod_total_count += 1 # transform into a python dictionary vars(Pod_sf_result) # calculate *Trainee* outcomes based on all related fields Pod_outcome_sum = 0 Pod_checked_sum = 0 for outcome in Pod_field_names: if Pod_sf_result['records'] and outcome != "Total_Checked__c": Pod_outcome_sum += Pod_sf_result['records'][0][outcome] if "Total_Checked__c" in Pod_sf_result['records'][0]: Pod_checked_sum = Pod_sf_result['records'][0]['Total_Checked__c'] pod_outcome = { 'progress': Pod_outcome_sum, 'checked': Pod_checked_sum, 'total': Pod_total_count, } return pod_outcome
def getStarredTasksInPod(pod_map_name): """ a sub-function that does the process for each user. """ res = [] fields = {} display_name = pod_map_name[:-11] # Obtain all field names for the query desc = getattr(sf, pod_map_name).describe() field_names = [field['name'] for field in desc['fields']] for field in desc['fields']: fields[field['name']] = field['label'] # Query for all fields for this user soql = ("SELECT {} FROM " + pod_map_name).format(','.join(field_names)) query = format_soql( (soql + " WHERE (Contact__r.auth0_user_id__c={user_id})"), user_id=user_id) sf_result = sf.query(query) for name, value in sf_result["records"][0].items(): if "_Youth_" in name and "_BOOL_" not in name: # BOOL are the stars parts = name.split("_") parts[2] = "BOOL" api_bool_key = '_'.join(parts) ydm_key = name.replace("_Youth_", "_YDM_") # show in starred if: 1. is starred, 2. not approved by YDM if sf_result["records"][0].get(api_bool_key) == True and \ sf_result["records"][0].get(ydm_key) == False: res.append({ "api_key": name, "api_bool_key": api_bool_key, "id": parts[-3].lower(), "key": fields.get(name), "ydmApproved": sf_result["records"][0].get(ydm_key), "checked": value, "starIsFilled": True, "pod": display_name, "accessible": pod_data[pod_map_name].get('status') == "allowed" }) return res
def get_user_id(self, username): """ Given a username, returns the user id for the User with that username or None if no user is found Used to get a the user id of the user we will assign the retirement task to """ id_query = self._sf.query( format_soql("SELECT Id FROM User WHERE Username = {username}", username=username)) total_size = int(id_query['totalSize']) if total_size == 0: return None else: return id_query['records'][0]['Id']
def get_lead_ids_by_email(self, email): """ Given an id, query for a Lead with that email Returns a list of ids tht have that email or None if none are found """ id_query = self._sf.query( format_soql("SELECT Id FROM Lead WHERE Email = {email}", email=email)) total_size = int(id_query['totalSize']) if total_size == 0: return None else: ids = [record['Id'] for record in id_query['records']] if len(ids) > 1: LOG.warning( "Multiple Ids returned for Lead with email {}".format( email)) return ids
def userInfo(user): soql = format_soql(""" SELECT Id, Email, Name FROM Contact WHERE (Has_Youth_App_Account__c = true AND auth0_user_id__c = {user_id}) """, user_id=user['id']) result = sf.query(soql) if (result["totalSize"] == 1): user_info = result['records'][0] res = {"email": user_info['Email'], "name": user_info['Name']} return jsonify(res) else: return "{}"
def finishSignup(): """ After sign up, to be called by auth0 to checkoff the box in salesforce to indicate that the account has been registered. """ secret = request.headers.get('Authorization') email = request.json.get('email') auth0id = request.json.get('id') correct_secret = os.environ.get('VERIFY_SIGNUP_SECRET') # exit when the wrong secret is provided if (not secret or secret != "Secret " + correct_secret): response = jsonify({ "code": "invalid_secret", "description": "Access denied." }) response.status_code = 401 return response # get the object response = sf.query( format_soql( "SELECT Id, Has_Youth_App_Account__c FROM Contact WHERE (email = {email})", email=email)) if response.get("totalSize") is None or response.get("totalSize") < 0: response = jsonify({ "code": "user_not_found", "description": "The User is not found in the database." }) response.status_code = 401 return response # update the contact sf.Contact.update(response["records"][0]["Id"], { "Has_Youth_App_Account__c": True, "auth0_user_id__c": auth0id }) return jsonify({"result": "success"})
def updateCheckbox(user): # Extract user details from the user object user_id = user.get('id') # Extract current pod to update from JSON body data pod = request.json.get('pod') pod_map_name = pod + '_POD_Map__c' # Query for this user in Salesforce soql = "SELECT Contact__c FROM " + pod_map_name sf_result = sf.query( format_soql((soql + " WHERE (Contact__r.auth0_user_id__c={user_id})"), user_id=user_id)) # Obtain pod ID tr_pod_id = sf_result['records'][0]['attributes']['url'].split('/')[-1] task_title = request.json.get('task_title') new_value = request.json.get('new_value') print(request.json) # Update value of specific task in Salesforce getattr(sf, pod_map_name).update(tr_pod_id, {task_title: new_value}) return {}
# sf.Contact.create({'LastName':'Smith','Email':'*****@*****.**'}) # sf.Contact.create({'LastName':'Smith','Email':'*****@*****.**'}) # contact = sf.Contact.get('0038A00000YfgVEQAZ') # contact = sf.Contact.get_by_custom_id('My_Custom_ID__c', '22') # sfresult = sf.Contact.update('0038A00000YfgVEQAZ',{'LastName': 'Jones', 'FirstName': 'John'}) # sfresult = 204 (succeded, no content) # sf.Contact.delete('0038A00000YfgVEQAZ') # result = sf.Contact.create( # {'Email':attendee['profile']['email']+"3", # 'FirstName':attendee['profile']['first_name']+"3", # 'LastName':attendee['profile']['last_name']+"3", # 'Title':attendee['profile']['job_title']+"3"}) # contactID = result['id'] # result = sf.Opportunity.create({'AccountId':"", 'npsp__Primary_Contact__c':contactID, 'EventbriteSync__Buyer__c':contactID, 'amount':attendee['costs']['gross']['major_value'], 'StageName':'posted', 'CloseDate':attendee['created'], 'CampaignId':campaignID, 'Order_Number__c':attendee['order_id'], 'Ticket_Type__c':attendee['ticket_class_name'], 'RecordTypeId':'012f4000000JdASAA0', 'Name':'tempName'}) # print(result) # sf.Payment.create({}) # result = sf.Opportunity.create({'AccountId':'0017h00000XpNWPAA3', 'npsp__Primary_Contact__c':'0037h00000RkN8TAAV', 'EventbriteSync__Buyer__c':'0037h00000RkN8TAAV', 'StageName':'posted', 'CloseDate':"2021-03-25T19:23:04Z", 'CampaignId':'7017h000000xOe6AAE', 'Order_Number__c':'1686870425', 'Ticket_Type__c':"Nonmember Ticket", 'RecordTypeId':'012f4000000JdASAA0', 'Name':'tempName', 'amount':'20.00'}) # print(result) # opID = result['id'] # result = sf.npe01__OppPayment__c.create({'npe01__Opportunity__c':opID, 'npe01__Paid__c':True, 'Payment_Contact__c':'0038A00000ZVhpbQAD', 'Ready_for_Invoice__c':True, 'npe01__Payment_Amount__c':'50.00'}) #print(result) # sfCM = sf.query("SELECT Id FROM CampaignMember WHERE Eventbrite_Attendee_ID__c = '{ebAttendee}'".format(ebEventID=attendee['event_id'])) searchterm = "Mobile Loaves & Fishes, Inc." qs = sf.quick_search(format_soql("{{Mobile Loaves & Fishes, Inc.}}")) print(qs)
def kic_rpt(): '''Run the current kids in custody with and without placement report''' def column_size(sheet): '''Dynamically adjust the column sizes in excel sheet''' column_widths = [] for row in sheet: for i, cell in enumerate(row): if len(column_widths) > i: if len(str(cell.value)) > column_widths[i]: column_widths[i] = len(str(cell.value)) + 5 else: column_widths += [len(str(cell.value)) + 5] for i, column_width in enumerate(column_widths): sheet.column_dimensions[get_column_letter(i + 1)].width = column_width def soql_df(soql_query): '''Create a DF using SOQL query and normalizing JSON''' info = sf.query_all(soql_query) df = pd.json_normalize(info['records']) cols = [c for c in df.columns if 'attribute' not in c] cols = [c for c in cols if not c.endswith('__r')] df = df[cols].copy() return df def cus_rank(x): '''Rank anything that is not DSCYF/DFS, DFS, DSCYF as 1 for sorting''' if x in ('DSCYF/DFS', 'DFS', 'DSCYF'): return 2 else: return 1 def rank_null(x): '''Rank null dates above a normal date for sorting''' if x == None: return 1 else: return 2 #Sign into Salesforce. username = stuff.username #password = p.password('Enter your password', title='Salesforce Password') password = stuff.password orgid = '' url = '' sf = Salesforce(username=username, password=password, instance_url=url, organizationId=orgid) #Create the initial Kids in Custody query. q = f''' SELECT Custodian_Name__c,Start_Date__c,End_Date__c,Client_Name__r.DEL_PID__c,Client_Name__r.Name,Client_Name__r.Birthdate, Client_Name__r.DEL_Age__c, Client_Name__r.DEL_Age_Years__c,Client_Name__r.DEL_Custody_Start_Date__c, Client_Name__r.DEL_Runaway_Alert_Flag__c, Client_Name__r.DEL_MCI__c FROM DEL_Custody__c ORDER BY Client_Name__r.DEL_PID__c, Start_Date__c DESC ''' df = soql_df(q) #Rank the custodian and null dates df['Custodian_Rank'] = df['Custodian_Name__c'].apply(cus_rank) df['Date_Rank'] = df['End_Date__c'].apply(rank_null) #Sort the data to get the most recent start date with a non DFS custodian first and open end date first as well. df = df.sort_values(by=[ 'Client_Name__r.DEL_PID__c', 'Start_Date__c', 'Custodian_Rank', 'Date_Rank' ], ascending=[True, False, True, True]).copy() #Drop duplicate by PID and then keep the ones that are DFS related with an open end date. df = df.drop_duplicates('Client_Name__r.DEL_PID__c').copy() df = df[df['Custodian_Name__c'].isin(['DSCYF/DFS', 'DFS', 'DSCYF'])].copy() df = df[df['End_Date__c'].isnull()].copy() #Drop the rank columns and date columns df = df.drop( ['Start_Date__c', 'End_Date__c', 'Custodian_Rank', 'Date_Rank'], axis='columns').copy() #Change Age_Years into a numeric. df = df.astype({'Client_Name__r.DEL_Age_Years__c': 'int32'}).copy() #Make a list of all the PIDs pids = list(df['Client_Name__r.DEL_PID__c']) #Query for Eligibility Information q1 = format_soql(''' SELECT Person_LkID__r.DEL_PID__c,Start_Date__c, End_Date__c, Aid_Category_Description__c FROM DEL_Income_Eligibility__c WHERE Person_LkID__r.DEL_PID__c IN {pids} AND Eligibility_Income_Type__c = 'Medicaid Eligibility' ORDER BY End_Date__c DESC ''', pids=pids) #Create Dataframe #JSON Normalize will remove the nested dictionaries #and then we will keep the columns that do not have the word 'attribute in it'. Then remove columns that end with __r. df1 = soql_df(q1) #Sort data by End_Date__c and remove duplicates by PID df1 = df1.sort_values(by=['Person_LkID__r.DEL_PID__c', 'End_Date__c'], ascending=[True, False]).copy() df1 = df1.drop_duplicates('Person_LkID__r.DEL_PID__c').copy() #Merge df1 and df and then drop Person_LkID__r.DEL_PID__c. df1 = df.merge(df1, how='left', left_on='Client_Name__r.DEL_PID__c', right_on='Person_LkID__r.DEL_PID__c').copy() df1 = df1.drop(columns=['Person_LkID__r.DEL_PID__c']).copy() #Create the SOQL query for all the Placements for those PIDs. q2 = format_soql(''' SELECT PID__c, Name,Division__c, Service_Name__c, Case_Number__r.Assigned_Worker__r.Name , Case_Number__r.Assigned_Worker__r.DEL_Service_Area__c, Case_Number__r.Assigned_Supervisor__r.Name, State__c, Placement_Start_Date_Division_Wide__c, Case_Number__r.Name, Case_Type__c, Placement_Type_Formula__c FROM DEL_Placement__c WHERE PID__c IN {pids} AND Service_Name__c != null AND Placement_Start_Date_Division_Wide__c != null AND Placement_End_Date_Division_Wide__c = null AND Latest_Version_Placement__c = True ''', pids=pids) #Run the query and put it into the Pandas DataFrame. df2 = soql_df(q2) #Merge df1 with df2 and rename columns and order them correctly. df3 = df1.merge(df2, how='left', left_on='Client_Name__r.DEL_PID__c', right_on='PID__c').copy() df3 = df3.drop(columns=['PID__c']).copy() columns = [ 'Who Has Custody', 'PID', 'Name', 'Birth Date', 'Age', 'Age (Years)', 'Custody Start Date', 'Runaway', 'MCI', 'MA Start Date', 'MA End Date', 'MA Code', 'Placement ID', 'Division', 'Service', 'State', 'Placement Start Date', 'Case Type', 'Placement Type', 'Case Owner', 'Service Area', 'Supervisor', 'Case Number' ] df3.columns = columns #Rename columns ord_columns = [ 'PID', 'Name', 'Birth Date', 'Age', 'Age (Years)', 'Custody Start Date', 'Division', 'Service', 'Case Number', 'Case Type', 'Case Owner', 'Supervisor', 'Service Area', 'State', 'Placement ID', 'Placement Start Date', 'Who Has Custody', 'Runaway', 'MCI', 'MA Start Date', 'MA End Date', 'MA Code', 'Placement Type' ] df3 = df3[ord_columns].copy() #Order the columns #Kids in Custody with Active Placement wplacement = df3.loc[~df3['Placement ID'].isnull()].copy() wplacement.loc[wplacement['MA End Date'] == '2299-12-31', 'MA End Date'] = '2050-12-31' wplacement.loc[:, [ 'Birth Date', 'Custody Start Date', 'Placement Start Date', 'MA Start Date', 'MA End Date' ]] = wplacement.loc[:, [ 'Birth Date', 'Custody Start Date', 'Placement Start Date', 'MA Start Date', 'MA End Date' ]].apply(pd.to_datetime) wplacement1 = wplacement.copy() #For KIC with current DFS Placement Report #Drop additional column of Placement Type not needed for the report but needed for the other report mentioned above. wplacement = wplacement.drop('Placement Type', axis='columns').copy() #Kids in Custody without Active Placement woplacement = df3.loc[df3['Placement ID'].isnull()].copy() woppids = list(woplacement['PID']) #get pids for without placement. woq = format_soql(''' SELECT PID__c, Name,Division__c, Service_Name__c, End_Reason__c, Case_Number__r.Assigned_Worker__r.Name , Case_Number__r.Assigned_Worker__r.DEL_Service_Area__c, Case_Number__r.Assigned_Supervisor__r.Name, State__c, Placement_Start_Date_Division_Wide__c, Placement_End_Date_Division_Wide__c, Case_Number__r.Name, Case_Type__c FROM DEL_Placement__c WHERE PID__c IN {woppids} AND Service_Name__c != null AND Placement_Start_Date_Division_Wide__c != null AND Latest_Version_Placement__c = True ORDER BY Placement_Start_Date_Division_Wide__c DESC ''', woppids=woppids) #Query for last placement info. wodf = soql_df(woq) #Create DF wodf = wodf.sort_values( by=['PID__c', 'Placement_Start_Date_Division_Wide__c'], ascending=[True, False]).copy() wodf = wodf.drop_duplicates( 'PID__c').copy() #Sort by Placement Start Date and remove duplicates c = [ 'PID', 'Last Placement ID', 'Last Placing Division', 'Service', 'Last Placement Removal Reason', 'State', 'Placement Start Date', 'Placement End Date', 'Case Type', 'Case Owner', 'Service Area', 'Supervisor', 'Case Number' ] wodf.columns = c #Change column names old_cols = [ c for c in woplacement.columns if (c not in wodf.columns and c not in ('Placement ID', 'Division')) or c == 'PID' ] # get columns not in new placement table. oldwoplacement = woplacement[old_cols].copy( ) #remove old placement columns. woplacement1 = oldwoplacement.merge(wodf, how='left', left_on='PID', right_on='PID') ord_columns = [ 'PID', 'Name', 'Birth Date', 'Age', 'Age (Years)', 'Custody Start Date', 'Last Placing Division', 'Service', 'Case Number', 'Case Type', 'Case Owner', 'Supervisor', 'Service Area', 'State', 'Last Placement ID', 'Placement Start Date', 'Placement End Date', 'Who Has Custody', 'Runaway', 'MCI', 'MA Start Date', 'MA End Date', 'MA Code', 'Last Placement Removal Reason' ] woplacement1 = woplacement1[ord_columns].copy( ) # Create new KIC without placement with last placement information. woplacement1.loc[woplacement1['MA End Date'] == '2299-12-31', ['MA End Date']] = '2050-12-31' woplacement1.loc[:, [ 'Birth Date', 'Custody Start Date', 'Placement Start Date', 'Placement End Date', 'MA Start Date', 'MA End Date' ]] = woplacement1.loc[:, [ 'Birth Date', 'Custody Start Date', 'Placement Start Date', 'Placement End Date', 'MA Start Date', 'MA End Date' ]].apply(pd.to_datetime) # Export to excel date_ = dt.date.today() month = date_.month day = date_.day year = date_.year location = fr'H:\Python_Programs\SF\Weekly\Results\KIC_report{month}.{day}.{year}.xlsx' writer = pd.ExcelWriter(location, engine='xlsxwriter', datetime_format='mm/dd/yyyy') wplacement.to_excel(writer, sheet_name='KIC W Active Placement', index=False) woplacement1.to_excel(writer, sheet_name='KIC WO Active Placement', index=False) writer.save() #Adjust column sizes book = load_workbook(location) for sheet in book.sheetnames: worksheet = book[sheet] column_size(worksheet) book.save(location) #Print Results r_loc = fr'H:\Python_Programs\SF\Weekly\Results\KIC_results{month}.{day}.{year}.txt' with open(r_loc, 'w') as file: kidswp = len(wplacement['PID'].unique()) kidswop = len(woplacement1['PID'].unique()) print(f'''Results: Kids in Custody with Active Placement: {kidswp} Kids in Custody without Active Placement: {kidswop} Total Kids in Custody: {kidswp + kidswop}''', file=file) return wplacement1
def processOrder(api_url): sf = getSalesforce() r = requests.get(api_url + "/attendees", headers=AUTH_HEADER_EB, params={"expand": ["category", "promotional_code"]}) attendees = r.json() print("Raw attendees data: ") print(attendees) for attendee in attendees['attendees']: sfCampaign = sf.query( format_soql( "SELECT Id FROM Campaign WHERE EventbriteSync__EventbriteId__c = '{ebEventID}'" .format(ebEventID=attendee['event_id']))) campaignID = sfCampaign['records'][0]['Id'] print("Checking for an email match for " + attendee['profile']['name']) # Search SF for contact with attendee email queryResult = sf.query( "SELECT Id, Email, npsp__Primary_Affiliation__c, Primary_Affiliation_text__c FROM Contact WHERE Email = '{attendeeEmail}'" .format(attendeeEmail=attendee['profile']['email'].strip().replace( '"', '\\"').replace("'", "\\'"))) # Check for edge case: Facebook Registration / Company Not Collected. if 'company' not in attendee['profile'].keys(): print( "Company Name missing from record! Processing alternatively..." ) if queryResult['totalSize'] >= 1: print( "NO COMPANY- Email match found, updating contact FName/LName, making campaign member, making opportunity" ) accountID = queryResult['records'][0][ 'npsp__Primary_Affiliation__c'] contactID = queryResult['records'][0]['Id'] sf.Contact.update( contactID, { 'FirstName': attendee['profile']['first_name'], 'LastName': attendee['profile']['last_name'], }) createCampaignMember(sf, attendee, campaignID, contactID) createOpportunity(sf, attendee, contactID, accountID, campaignID, api_url) elif queryResult['totalSize'] == 0: print( "NO COMPANY- Contact not found in salesforce. Creating new contact, campaign member, opportunity and proceeding with limited information..." ) result = sf.Contact.create({ 'Email': attendee['profile']['email'], 'FirstName': attendee['profile']['first_name'], 'LastName': attendee['profile']['last_name'] }) newContactID = result['id'] createCampaignMember(sf, attendee, campaignID, newContactID) createOpportunity(sf, attendee, newContactID, "", campaignID, api_url) # If contact with an email IS found. elif queryResult['totalSize'] >= 1: print("Email match(es) found!") contactID = queryResult['records'][0]['Id'] print("Checking if primary affiliation is an exact match....") # If Primary Affilation matches: Update contact FName/LName/Title, add campaign member. if queryResult['records'][0][ 'Primary_Affiliation_text__c'] == None: queryResult['records'][0]['Primary_Affiliation_text__c'] = "" elif queryResult['records'][0][ 'Primary_Affiliation_text__c'].lower( ) == attendee['profile']['company'].lower(): accountID = queryResult['records'][0][ 'npsp__Primary_Affiliation__c'] print("Matches Primary Affiliation! Updating records...") updateContactNormal(sf, attendee, contactID, accountID) createCampaignMember(sf, attendee, campaignID, contactID) createOpportunity(sf, attendee, contactID, accountID, campaignID, api_url) print("Done!") # If primary affiliation doesn't match else: print("Primary Affiliation does not match") accountQuery = sf.query( format_soql( "SELECT Id, Name from Account WHERE Name LIKE '{ebOrg}'" .format(ebOrg=attendee['profile']['company'].strip(). replace('"', '\\"').replace("'", "\\'")))) # If primary affiliaiton doesn't match, but it exists in salesforce update the contact and create the campaign member if accountQuery['totalSize'] >= 1: print( "Matching account found. Starting update contact, create campaign member, create opportunity." ) accountID = accountQuery['records'][0]['Id'] updateContactNormal(sf, attendee, contactID, accountID) createCampaignMember(sf, attendee, campaignID, queryResult['records'][0]['Id']) createOpportunity(sf, attendee, contactID, accountID, campaignID, api_url) print("Done!") # If primary affiliation doesn't match and doesn't exist in salesforce, create new account, update the contact, and create campaign member else: print( "No matching account found. Starting Create Account, Contact, CampaignMember" ) newAccountID = createNewAccount(sf, attendee) updateContactNormal(sf, attendee, contactID, newAccountID) createCampaignMember(sf, attendee, campaignID, contactID) createOpportunity(sf, attendee, contactID, newAccountID, campaignID, api_url) print("Done!") # If contact with email is not found elif queryResult['totalSize'] == 0: print( "Email match failed. Checking if Account exists in Salesforce..." ) accountQueryResult = sf.query( format_soql( ("SELECT Id, Name from Account WHERE Name LIKE '{ebOrg}'". format( ebOrg=attendee['profile']['company'].strip().replace( '"', '\\"').replace("'", "\\'"))))) # If the account doesn't exist in Salesforce, make the account, contact, campaign record if accountQueryResult['totalSize'] == 0: print( "No matching Account. Starting create Account, Contact, and Campaign Member" ) newAccountID = createNewAccount(sf, attendee) newContactID = createNewContact(sf, attendee, newAccountID) createCampaignMember(sf, attendee, campaignID, newContactID) createOpportunity(sf, attendee, newContactID, newAccountID, campaignID, api_url) print("Done!") # Create new Account, new Contact, and the event affiliation. # if the account does exist, search by name else: print("Matching Account Found, searching by name...") accountID = accountQueryResult['records'][0]['Id'] personQueryResult = sf.query( format_soql( "SELECT Id, Name, Primary_Affiliation_text__c FROM Contact WHERE Name='{ebName}' AND Primary_Affiliation_text__c LIKE '{ebCompany}'" .format( ebName=attendee['profile']['name'].strip().replace( '"', '\\"').replace("'", "\\'"), ebCompany=attendee['profile'] ['company'].strip().replace('"', '\\"').replace( "'", "\\'")))) # If there's an exact name match, update the contact, create the campaign member. if personQueryResult['totalSize'] == 1: print( "Match found by Name and Company. Starting update contact, create campaign member, create opportunity." ) contactID = personQueryResult['records'][0]['Id'] updateContactNormal(sf, attendee, contactID, accountID) createCampaignMember(sf, attendee, campaignID, contactID) createOpportunity(sf, attendee, contactID, accountID, campaignID, api_url) print("Done!") else: print( "Person not found. Starting create new contact, campaign member, opportunity." ) # SF create new contact in account matched in "accountQueryResult" newContactID = createNewContact(sf, attendee, accountID) # SF create new Campaign Member record with new ContactID and the Campaign ID from sfCampaign. createCampaignMember(sf, attendee, campaignID, newContactID) createOpportunity(sf, attendee, newContactID, accountID, campaignID, api_url) print("Done!")
def test_booleans(self): """ Boolean literals are inserted """ query = "select foo from bar where truth = {} and lies = {}" expected = "select foo from bar where truth = false and lies = true" quoted = format_soql(query, False, True) self.assertEqual(quoted, expected)
def amo_sync_addon(data): if not settings.SFDC_ENABLED: return data = deepcopy(data) if data["status"] == "deleted": try: addon_record = sfdc.addon.get_by_custom_id("AMO_AddOn_Id__c", data["id"]) except sfapi.SalesforceResourceNotFound: return # if deleted, go ahead and delete the author associations and addon addon_users = sfdc.sf.query( sfapi.format_soql( "SELECT Id, AMO_Contact_ID__c FROM DevAddOn__c WHERE AMO_AddOn_ID__c = {addon_id}", addon_id=addon_record["Id"], ), ) for record in addon_users["records"]: sfdc.dev_addon.delete(record["Id"]) amo_check_user_for_deletion(record["AMO_Contact_ID__c"]) sfdc.addon.delete(addon_record["Id"]) return users = [upsert_amo_user_data(author) for author in data["authors"]] # filter out the users that couldn't be found users = [user for user in users if user] addon_data = { "AMO_Category__c": amo_compress_categories(data["categories"]), "AMO_Default_Language__c": data["default_locale"], "AMO_GUID__c": data["guid"], "AMO_Rating__c": data["ratings"]["average"], "AMO_Slug__c": data["slug"], "AMO_Status__c": data["status"], "AMO_Type__c": data["type"], "AMO_Update__c": data["last_updated"], "Average_Daily_Users__c": data["average_daily_users"], "Dev_Disabled__c": "Yes" if data["is_disabled"] else "No", "AMO_Recommended__c": data["is_recommended"], } # check for possible None or empty values if data["name"]: addon_data["Name"] = data["name"] # versions can be removed, so they should be removed if they are null if data["current_version"]: addon_data["AMO_Current_Version__c"] = data["current_version"]["version"] else: addon_data["AMO_Current_Version__c"] = "" if data["latest_unlisted_version"]: addon_data["AMO_Current_Version_Unlisted__c"] = data["latest_unlisted_version"][ "version" ] else: addon_data["AMO_Current_Version_Unlisted__c"] = "" sfdc.addon.upsert(f'AMO_AddOn_Id__c/{data["id"]}', addon_data) addon_record = sfdc.addon.get_by_custom_id("AMO_AddOn_Id__c", data["id"]) # delete users no longer associated with the addon existing_users = sfdc.sf.query( sfapi.format_soql( "SELECT Id, AMO_Contact_ID__c FROM DevAddOn__c WHERE AMO_AddOn_ID__c = {addon_id}", addon_id=addon_record["Id"], ), ) user_ids_to_records = { i["AMO_Contact_ID__c"]: i["Id"] for i in existing_users["records"] } existing_user_ids = set(user_ids_to_records.keys()) new_user_ids = {user["id"] for user in users} if new_user_ids == existing_user_ids: # no need to continue as no users have been added or removed return to_delete = existing_user_ids - new_user_ids for delete_user_id in to_delete: sfdc.dev_addon.delete(user_ids_to_records[delete_user_id]) amo_check_user_for_deletion(delete_user_id) to_add = new_user_ids - existing_user_ids if not to_add: # no new users to add return for user in users: if user["id"] not in to_add: # record exists continue try: sfdc.dev_addon.upsert( f'ConcatenateAMOID__c/{user["amo_id"]}-{data["id"]}', { "AMO_AddOn_ID__c": addon_record["Id"], "AMO_Contact_ID__c": user["id"], }, ) except sfapi.SalesforceMalformedRequest as e: if e.content[0]["errorCode"] == "DUPLICATE_VALUE": # dupe error, so we don't need to do this again pass else: raise e
def createOpportunity(sf, attendee, contactID, accountID, campaignID, api_url): r = requests.get(api_url, headers=AUTH_HEADER_EB, params={"expand": ["category", "promotional_code"]}) order = r.json() buyerQuery = sf.query( format_soql( "SELECT Id, Email, npsp__Primary_Affiliation__c, Primary_Affiliation_text__c FROM Contact WHERE Email = '{buyerEmail}'" .format(buyerEmail=order['email'].strip().replace( '"', '\\"').replace("'", "\\'")))) # if salesforce returns one primary affiliation for the attendee, it will select the first primary affiliation returned if buyerQuery['totalSize'] == 1: buyerID = buyerQuery['records'][0]['Id'] sf.Contact.update(buyerID, { 'FirstName': order['first_name'], 'LastName': order['last_name'], }) # if not, it will create a new contact else: createResponse = sf.Contact.create({ 'FirstName': order['first_name'], 'LastName': order['last_name'], 'Email': order['email'] }) buyerID = createResponse['id'] if "promotional_code" in attendee.keys(): sf.Opportunity.create({ 'AccountId': accountID, 'npsp__Primary_Contact__c': contactID, 'EventbriteSync__Buyer__c': buyerID, 'amount': attendee['costs']['gross']['major_value'], 'StageName': 'posted', 'CloseDate': attendee['created'], 'CampaignId': campaignID, 'Order_Number__c': attendee['order_id'], 'Ticket_Type__c': attendee['ticket_class_name'], 'RecordTypeId': '012f4000000JdASAA0', 'Name': 'tempName', 'Coupon_Code__c': attendee['promotional_code']['code'] }) # Name is by Salesforce when processed by NPSP else: sf.Opportunity.create({ 'AccountId': accountID, 'npsp__Primary_Contact__c': contactID, 'EventbriteSync__Buyer__c': buyerID, 'amount': attendee['costs']['gross']['major_value'], 'StageName': 'posted', 'CloseDate': attendee['created'], 'CampaignId': campaignID, 'Order_Number__c': attendee['order_id'], 'Ticket_Type__c': attendee['ticket_class_name'], 'RecordTypeId': '012f4000000JdASAA0', 'Name': 'tempName' }) # Name is by Salesforce when processed by NPSP print("Opportunity created for " + attendee['profile']['first_name'])
def test_null(self): """ Null literals are inserted """ query = "select foo from bar where name != {}" expected = "select foo from bar where name != null" quoted = format_soql(query, None) self.assertEqual(quoted, expected)
def youthCheck(user): # Extract user details from the user object user_id = user.get('id') # Extract current pod to update from request arguments pod = request.args.get('pod') focus_area = request.args.get('focus_area') pod_map_name = pod + '_POD_Map__c' # Obtain all field names for the query desc = getattr(sf, pod_map_name).describe() field_names_and_labels = [(field['name'], field['label']) for field in desc['fields']] field_names = [field['name'] for field in desc['fields']] # Query for all fields for this user soql = ("SELECT {} FROM " + pod_map_name).format(','.join(field_names)) sf_result = sf.query( format_soql((soql + " WHERE (Contact__r.auth0_user_id__c={user_id})"), user_id=user_id)) # Format response response = {} for name_and_label in field_names_and_labels: response[name_and_label[0]] = { "name": name_and_label[1], "value": None } for name, value in sf_result["records"][0].items(): if name in response.keys(): response[name]["value"] = value def clean_response(response): new_data = [] # Helper function to find and update in new_data based on api_name def find_and_update(new_data, api_name, index, field_name, get_key=False): words_in_api_name = api_name.split("_") curr_id = words_in_api_name[len(words_in_api_name) - 3].lower() content_index = -1 try: content_index = [x['id'] for x in new_data[index]['content'] ].index(curr_id) except ValueError: content_index = -1 finally: if content_index >= 0: if get_key: new_data[index]['content'][content_index][ field_name] = api_name else: new_data[index]['content'][content_index][ field_name] = response[api_name]["value"] return new_data # Extract all outcome titles for later use for api_name in response.keys(): if "Outcome" in api_name and focus_area in api_name and "Outcomes" not in api_name: new_data.append({ 'id': api_name[0:3], 'title': response[api_name]["name"], 'content': [] }) # Adds task objects to content array of outcome in new_data for api_name in response.keys(): api_name_id = api_name[0:3] index = -1 try: index = [x['id'] for x in new_data].index(api_name_id) except ValueError: index = -1 finally: if "Youth" in api_name and index >= 0 and not "BOOL" in api_name: words_in_api_name = api_name.split("_") new_data[index]['content'].append({ 'api_key': api_name, 'api_bool_key': "", 'id': words_in_api_name[len(words_in_api_name) - 3].lower(), 'key': response[api_name]["name"], 'ydmApproved': True, # Change later based on salesforce data 'checked': response[api_name]["value"], 'starIsFilled': False, # Change later based on salesforce data 'pod': pod }) # Updates values of starIsFilled/ydmApproved in content array for api_name in response.keys(): api_name_id = api_name[0:3] index = -1 try: index = [x['id'] for x in new_data].index(api_name_id) except ValueError: index = -1 finally: if "YDM" in api_name and index >= 0: new_data = find_and_update(new_data, api_name, index, "ydmApproved") if "BOOL" in api_name and index >= 0: new_data = find_and_update(new_data, api_name, index, "starIsFilled") new_data = find_and_update(new_data, api_name, index, "api_bool_key", get_key=True) return {'response': new_data} return clean_response(response)
def test_date(self): """ Date literals are inserted """ query = "select foo from bar where date = {}" expected = "select foo from bar where date = 1987-02-01" quoted = format_soql(query, date(1987, 2, 1)) self.assertEqual(quoted, expected)
def test_literal(self): """ :literal format spec """ query = "select foo from bar where income > {amt:literal}" expected = "select foo from bar where income > USD100" quoted = format_soql(query, amt="USD100") self.assertEqual(quoted, expected)
def test_like(self): """ :like format spec """ query = "select foo from bar where name like '%{:like}%'" expected = "select foo from bar where name like '%foo\\'\\%bar\\_%'" quoted = format_soql(query, "foo'%bar_") self.assertEqual(quoted, expected)
def test_invalid(self): """ Unexpected value type """ with self.assertRaises(ValueError): format_soql("select foo from bar where x = {}", {"x": "y"})
def test_plain_string(self): """ Case where there is no quoting """ query = "select foo from bar where x = 'y'" quoted = format_soql(query) self.assertEqual(quoted, query)