Last active
April 29, 2020 13:37
-
-
Save javierwilson/6fb564b6cda6bf32902fa75284f5529f to your computer and use it in GitHub Desktop.
Check ByTheNumbers records for strange things
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/usr/bin/env python | |
# -*- coding: utf-8 -*- | |
import codecs | |
import sys | |
from simple_salesforce import Salesforce | |
import secrets | |
UTF8Writer = codecs.getwriter('utf8') | |
sys.stdout = UTF8Writer(sys.stdout) | |
sf = Salesforce(username=secrets.username, password=secrets.password, security_token=secrets.token) | |
def getDate(s): | |
if s is None: | |
return None | |
clean = s.split(':', 1)[1]; | |
clean = clean.strip().replace(' ', 'T') | |
if len(clean) < 20: | |
clean = clean[:10] | |
return clean | |
indicators = ['Men_Direct_Actual__c', 'Men_Direct_Target__c', 'Men_Indirect_Actual__c', 'Men_Indirect_Target__c', 'Women_Direct_Actual__c', 'Women_Direct_Target__c', 'Women_Indirect_Actual__c', 'Women_Indirect_Target__c'] | |
indicators_string = ','.join(indicators) | |
targets = ['Men_Direct_Target__c', 'Men_Indirect_Target__c', 'Women_Direct_Target__c', 'Women_Indirect_Target__c'] | |
targets_match = {'Men_Direct_Target__c': 'Men_Direct_Actual__c', 'Men_Indirect_Target__c': 'Men_Indirect_Actual__c', 'Women_Direct_Target__c': 'Women_Direct_Actual__c', 'Women_Indirect_Target__c': 'Women_Indirect_Actual__c'} | |
subprojects = sf.query_all("SELECT \ | |
Id, \ | |
Project__r.Project_Type__c,\ | |
Project__r.LWR_Region__c,\ | |
Country__r.Name,\ | |
Project__r.Start_Date__c,\ | |
Project__r.End_Date__c,\ | |
CreatedBy.Name,\ | |
Project__r.Project_Identifier__c,\ | |
Sub_Project_Identifier__c,\ | |
Name\ | |
FROM Sub_Project__c WHERE Sub_Project__c.Project__r.RecordType.Name <> 'Non-Project' AND Sub_Project__c.Project__r.End_Date__c > 2019-03-31 AND Sub_Project__c.Project__r.Start_Date__c < 2019-04-01 AND Sub_Project__c.Project__r.Status__c <> 'Terminated'") | |
i = 1 | |
fy = 19 | |
#print "Id,Region,Name,TOOMANY,NODATA,ZERO,SAME,LOWER,HIGHER,INDIRECT,EXACT,TOOHIGH,SEX,Notes" | |
print("Id,Region,Country,Start,End,Owner,Project Id,Sub-Project Id,Type,Name,TOOMANY,NODATA,ZERO,SAME,LOWER,HIGHER,INDIRECT,EXACT,TOOHIGH,SEX,Notes") | |
for item in subprojects['records']: | |
toomany = 0 | |
nodata = 0 | |
zero = 0 | |
same = 0 | |
lower = 0 | |
higher = 0 | |
indirect = 0 | |
exact = 0 | |
toohigh = 0 | |
sex = 0 | |
error = "" | |
# check Zero beneficiaries listed for a project | |
result = sf.query_all("SELECT LastModifiedBy.Name, LastModifiedDate, Sub_Project__r.Id, Sub_Project__r.Name, Fiscal_Year__c, %s FROM Beneficiaries__c WHERE Sub_Project__r.Id='%s' AND Fiscal_Year__c=%s" % (indicators_string, item['Id'], fy)) | |
result_last = sf.query_all("SELECT LastModifiedBy.Name, LastModifiedDate, Sub_Project__r.Id, Sub_Project__r.Name, Fiscal_Year__c, %s FROM Beneficiaries__c WHERE Sub_Project__r.Id='%s' AND Fiscal_Year__c=%s" % (indicators_string, item['Id'], fy-1)) | |
i += 1 | |
if result['totalSize'] < 1: | |
error += "No beneficiary rows. " | |
nodata = 1 | |
else: | |
if result['totalSize'] > 1: | |
error += "Two beneficiary rows for this FY%s. " % (fy,) | |
toomany = 1 | |
for target in targets: | |
#print "Compare %s=%s to %s=%s" % (target, result['records'][0][target], targets_match[target], result['records'][0][targets_match[target]]) | |
if result['records'][0][target] == result['records'][0][targets_match[target]]: | |
error += "%s Beneficiary numbers match targets exactly. " % (targets_match[target],) | |
exact = 1 | |
if (result['records'][0][target]) and ((result['records'][0][target] * 1.5) < result['records'][0][targets_match[target]]): | |
error += "%s Beneficiary numbers high compared to target. " % (targets_match[target],) | |
toohigh = 1 | |
for indicator in indicators: | |
if result['records'][0][indicator] == 0 or result['records'][0][indicator] == None: | |
error += "%s is zero. " % (indicator,) | |
zero = 1 | |
if result_last['totalSize'] >= 1: | |
for indicator in indicators: | |
if result['records'][0][indicator] < result_last['records'][0][indicator]: | |
error += "%s Beneficiary numbers are lower previous FY. " % (indicator,) | |
lower = 1 | |
if (indicator not in targets) and (result['records'][0][indicator] == result_last['records'][0][indicator]): | |
error += "%s Beneficiary numbers are the same as the previous FY. " % (indicator,) | |
same = 1 | |
if error: | |
print('%s,"%s","%s",%s,%s,"%s","%s","%s","%s","%s","%s",%s,%s,%s,%s,%s,%s,%s,%s,%s,"%s"' % ( | |
item['Id'], | |
item['Project__r']['LWR_Region__c'], | |
item['Country__r']['Name'], | |
item['Project__r']['Start_Date__c'], | |
item['Project__r']['End_Date__c'], | |
item['CreatedBy']['Name'], | |
item['Project__r']['Project_Identifier__c'], | |
item['Sub_Project_Identifier__c'], | |
item['Project__r']['Project_Type__c'], | |
item['Name'], | |
toomany, nodata, zero, same, lower, higher, indirect, exact, toohigh, sex, error)) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment