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)
