class Dashboard(object):
def __init__(self, active_users, query_string):
self.credentials = sfconf.credentials
self.active_users = active_users
self.query_string = query_string
self.sf = Salesforce(
username=self.credentials["username"],
password=self.credentials["password"],
security_token=self.credentials["security_token"],
)
def get_salesforce_data(self):
query_results = self.sf.query(self.query_string % self.active_users)
query_results = query_results["records"]
result = {}
users = self.sf.query("SELECT id, Name FROM User WHERE isActive = true")
users = users["records"]
user_names = {}
sales_names = []
print query_results
for user in users:
user_names[user["Id"]] = user["Name"]
for query_result in query_results:
if user_names.has_key(query_result["OwnerId"]):
result[user_names[query_result["OwnerId"]]] = query_result["expr0"]
sales_names.append(user_names[query_result["OwnerId"]])
return result, sales_names
def get_pricebooks():
sf = Salesforce(instance_url=settings.SALESFORCE_URL,
username=settings.SALESFORCE_USERNAME,
password=settings.SALESFORCE_PASSWORD,
security_token=settings.SALESFORCE_TOKEN)
pricebooks = PriceBook.objects.all()
for pb in pricebooks:
pricebook = sf.Pricebook2.get(pb.identifier)
query = ("SELECT Id,Name,ProductCode,UnitPrice,IsActive FROM PricebookEntry "
"WHERE Pricebook2Id = '{}'").format(pb.identifier)
remote_prices = sf.query(query)
price_list, created = PriceBook.objects.get_or_create(name=pricebook['Name'])
for item in remote_prices['records']:
price, created = Price.objects.get_or_create(code=item['ProductCode'],
defaults={
'name': item['Name'],
'price': item['UnitPrice'],
'identifier': item['Id']
})
price.name = item['Name']
price.price = item['UnitPrice']
price.identifier = item['Id']
price.save()
price_list.prices.add(price)
def index():
sf = Salesforce( username=u, password=p, security_token=k )
if request.vars.id == None:
redirect( URL(a='bih',c='default',f='index') )
users = sf.query(\
''.join( [ "Select ID, Date_of_First_Login__c ",
"FROM BIH_USER__C" ] ) )['records']
result = [ ]
try:
record = sf.query(\
''.join( [ "Select ID, Date_of_First_Login__c, First_Name__c, Last_Name__c, Email__c, ",
"(Select BIH_BUS__R.ID, BIH_BUS__R.NAME FROM TEAM_Members__r) ",
"FROM BIH_USER__C WHERE ID = '", request.vars.id, "'" ] ) )['records'][0]
if record['Date_of_First_Login__c'] is not None:
redirect( URL(a='bih',c='default',f='index') )
session.userId = record['Id']
if record['Team_Members__r'] is not None:
session.busId = record['Team_Members__r']['records'][0]['BIH_Bus__r']['Id']
return dict( user = dict(
id = record['Id'],
firstName = record['First_Name__c'],
lastName = record['Last_Name__c'],
emailAddress = record['Email__c'] ) )
except:
redirect( URL(a='bih',c='default',f='index') )
def main():
credentials = sfconf.credentials
sf = Salesforce(username = credentials['username'], password = credentials['password'],\
security_token = credentials['security_token'])
sales_managers_profile_id = '00eb0000000YXJL'
users = sf.query('SELECT Id, Name FROM User WHERE profileid = \'%s\' AND isActive = true ' % sales_managers_profile_id)
active_users = []
users = users['records']
for user in users:
active_users.append(str(user['Id']))
active_users = str(active_users).replace('[','(').replace(']',')')
funding_by_users_query = "SELECT AccountId__r.ownerid, sum(amount__c) FROM AForexEvent__c WHERE EventType__c = 'funding' \
AND DateTimeOfEvent__c = THIS_MONTH AND AccountId__r.ownerid in %s GROUP BY ROLLUP(AccountId__r.ownerid)"
withdrawal_by_users_query = "SELECT AccountId__r.ownerid, sum(amount__c) FROM AForexEvent__c WHERE EventType__c = 'Withdrawal' \
AND DateTimeOfEvent__c = THIS_MONTH AND AccountId__r.ownerid in %s GROUP BY ROLLUP(AccountId__r.ownerid)"
converted_leads_by_users_query = "SELECT Account__r.ownerid, count(Incentive_Deposit_Amount__c) FROM Wallet__c WHERE First_Funding_Date__c = THIS_MONTH \
AND Incentive_Deposit_Amount__c > 95 AND Account__r.ownerid in %s GROUP BY ROLLUP(Account__r.ownerid)"
sum_of_incentive_deposites_by_users_query = "SELECT Account__r.ownerid, sum(Incentive_Deposit_Amount__c) FROM Wallet__c \
WHERE First_Funding_Date__c = THIS_MONTH AND Account__r.ownerid in %s GROUP BY ROLLUP(Account__r.ownerid)"
outbount_calls_to_accounts_by_users_query = "SELECT OwnerId, Count(Id)From Task WHERE \
(Who.Type in ('Account', 'Contact') OR What.Type in ('Account', 'Contact')) \
AND type = 'Outbound Call' \
AND result__c in ('1 Talked - Substantial', '2 Talked - Brief', '3 Talked - Callback Requested', '4 Reached Associate') \
AND status = 'Завершено' \
AND ActivityDate = THIS_MONTH \
AND OwnerId in %s \
GROUP BY ROLLUP(OwnerId)"
outbount_calls_to_leads_by_users_query = "SELECT OwnerId, Count(Id)From Task WHERE \
(Who.Type in ('Lead') OR What.Type in ('Lead')) \
AND type = 'Outbound Call' \
AND result__c in ('1 Talked - Substantial', '2 Talked - Brief', '3 Talked - Callback Requested', '4 Reached Associate') \
AND status = 'Завершено' \
AND ActivityDate = THIS_MONTH \
AND OwnerId in %s \
GROUP BY ROLLUP(OwnerId)"
funding_by_users, funding_user_names = Dashboard(active_users, funding_by_users_query).get_salesforce_data()
withdrawal_by_users, withdrawal_user_names = Dashboard(active_users, withdrawal_by_users_query).get_salesforce_data()
converted_leads_by_users, converted_leads_user_names = Dashboard(active_users, converted_leads_by_users_query).get_salesforce_data()
sum_of_incentive_deposites_by_users, sum_of_incentive_user_names = Dashboard(active_users, sum_of_incentive_deposites_by_users_query).get_salesforce_data()
outbount_calls_to_accounts_by_users, outbount_calls_to_accounts_user_names = Dashboard(active_users, outbount_calls_to_accounts_by_users_query).get_salesforce_data()
outbount_calls_to_leads_by_users, outbount_calls_to_leads_user_names = Dashboard(active_users, outbount_calls_to_leads_by_users_query).get_salesforce_data()
return funding_by_users, funding_user_names, withdrawal_by_users, withdrawal_user_names, \
converted_leads_by_users, converted_leads_user_names, sum_of_incentive_deposites_by_users, sum_of_incentive_user_names, \
outbount_calls_to_accounts_by_users, outbount_calls_to_accounts_user_names, \
outbount_calls_to_leads_by_users, outbount_calls_to_leads_user_names
def count_records(program):
query = translate_program_to_soql(program, count_only=True)
# fetch count
program.source.token.refresh() # TODO: Source.count_records
conn = Salesforce(session_id=program.source.token.access_token,
instance_url=program.source.token.instance_url,
sandbox=program.source.token.is_sandbox)
try:
count = conn.query(query)['totalSize']
except SalesforceError as e:
raise Exception({
'type': 'salesforceerror',
'data': e.content[0],
})
except RequestException:
raise Exception({
'type': 'connectionerror',
})
except:
raise Exception({
'type': 'unknown',
})
else:
return count
def search_organizations(request):
sf = Salesforce(instance_url=request.session['sf_instance'], session_id=request.session['sf_session'])
orgname = u"%{orgname}%".format(orgname=clean(request.GET['orgname'])) if request.GET['orgname'] else settings.DEFAULT_ORG_NAME
sforg_query = u"SELECT Id, Name FROM Account WHERE Name LIKE '{orgname}'".format(orgname=orgname)
if 'orgid' in request.GET and request.GET['orgid'] != u'' and request.GET['orgid'] != settings.DEFAULT_ORG_ID:
print type(request.GET['orgid'])
sforg_query = sforg_query + u" OR Id = '{orgid}'".format(orgid=clean(request.GET['orgid']))
sforg_query = sforg_query + u" LIMIT 7"
hashkey = base64.b64encode(str(sforg_query.__hash__()))
sfresults = cache.get('orgsearch-%s' % hashkey)
if sfresults == None:
try:
sfresults = sf.query(sforg_query)
except SalesforceExpiredSession:
sf = refresh_token(request)
sfresults = sf.query(sforg_query)
cache.set('orgsearch-%s' % hashkey, sfresults, 30)
records = sfresults['records']
result = {}
for x in records:
result.update({x['Id']: x['Name']})
return HttpResponse(json.dumps(result), content_type="application/json")
def __init__(self, **kwargs):
#This is how the subject is created:
self.subject_components = kwargs['subject_components']
#This is the state of the Salesforce task:
self.task_status = kwargs["task_status"]
self.searched_records, self.saved_users = {}, {}
Salesforce.__init__(self, **kwargs)
self.owner_id = self.get_ownerid_from_assigned_to_name(kwargs["assigned_to"])
def setup():
#
# Declare global variables
#
global myRegId
global myUsername
global myPassword
global myToken
global contactid
global subject
global ws
global adxl
#
# setup Raspberry Pi ADXL345
# see http://shop.pimoroni.com/products/adafruit-triple-axis-accelerometer
#
if not simulation_mode:
adxl = adxl345.ADXL345()
#adxl.setRange(adxl345.RANGE_2G)
adxl.setRange(adxl345.RANGE_16G)
#
# Read configuration from file
#
config = ConfigParser.RawConfigParser()
config.read('salesforce_login.cfg')
#
# Establish Websockt connection for monitoring chat service
#
if chat_mode:
ws_url = config.get('Chat', 'ws_url')
ws = create_connection(ws_url)
#
# Lookup Salesforce demo org credentials and configuration
#
sf_lookup = Salesforce(username=config.get('Salesforce', 'username'), password=config.get('Salesforce', 'password'), security_token=config.get('Salesforce', 'security_token'))
result = sf_lookup.query("SELECT Id, Username__c, Password__c, Security_Token__c, Case_Contact_Id__c, Case_Subject__c FROM Raspberry_Pi_Demo__c WHERE Active__c = true AND Raspi_Hostname__c = " + config.get('Host', 'hostname'))
#
# Register new demo run
#
myRegId = result.get('records')[0].get('Id')
sf_lookup.Raspberry_Pi_Demo_Registration__c.create({'Raspberry_Pi_Demo__c':myRegId,'Status__c':'connected'})
myUsername = result.get('records')[0].get('Username__c')
myPassword = result.get('records')[0].get('Password__c')
myToken = result.get('records')[0].get('Security_Token__c')
contactid = result.get('records')[0].get('Case_Contact_Id__c')
subject = result.get('records')[0].get('Case_Subject__c')
if chat_mode:
chat("Sensor","Connection established.")
def post(self, request, format=None):
"""
Adds the necessary account and contact data to Salesforce if they do not exist.
"""
sf = Salesforce(instance_url=settings.SALESFORCE_URL,
username=settings.SALESFORCE_USERNAME,
password=settings.SALESFORCE_PASSWORD,
security_token=settings.SALESFORCE_TOKEN)
contact_id = ''
account_id = ''
contacts_query = ("SELECT Id,AccountId,FirstName,LastName,Email "
"FROM Contact WHERE Email = '{}'").format(request.data['email'])
contacts = sf.query(contacts_query)
if contacts['totalSize'] > 0:
contact_id = contacts['records'][0]['Id']
account_id = contacts['records'][0]['AccountId']
else:
account_query = "SELECT Id,Name FROM Account WHERE Name = '{}'".format(
request.data['institution_name'])
accounts = sf.query(account_query)
if accounts['totalSize'] == 0:
result = sf.Account.create({'Name': request.data['institution_name']})
account_id = result['id']
else:
account_id = accounts['records'][0]['Id']
country_codes = dict((value, key) for key, value in dict(countries).items())
contact = sf.Contact.create({
'FirstName': request.data['first_name'],
'LastName': request.data['last_name'],
'AccountId': account_id,
'Email': request.data['email'],
'MailingStreet':
request.data['address_1'] + '\n ' + request.data.get('address_2', ''),
'MailingCity': request.data['city'],
'MailingPostalCode': request.data['postcode'],
'MailingCountryCode': country_codes[request.data['country']],
})
contact_id = contact['id']
if contact_id and account_id:
user = User.objects.get(username=request.data['email'])
details = CRMAccount(contact_identifier=contact_id,
account_identifier=account_id,
user=user)
details.save()
user = User.objects.get(username=request.data['email'])
s = UserSerializer(user)
return Response(s.data)
return Response({'No CRM data added to account'})
def application(caseid):
if caseid in kvs:
url = sf_url + '/console#%2f' + kvs[caseid]
else:
sf = Salesforce(instance_url=sf_url, username=sf_usr, password=sf_pwd, security_token=sf_tkn)
for case in sf.query("SELECT Id from Case where CaseNumber = '%d'" % int(caseid))['records']:
kvs[caseid] = case['Id']
url = sf_url + '/console#%2f' + case['Id']
return redirect(url, code=301)
def prettyQuery(query):
"""prints JSON of SOQL query. For convenience console use."""
sf = Salesforce(username=config.username,
password=config.password,
security_token=config.security_token)
raw = sf.query_all(query)
pretty = jsonizer(raw)
print pretty
return pretty
def returnsObject(self):
sf = Salesforce(instance_url=self.instance_url, session_id=self.session_id)
object_list = []
for x in sf.describe()['sobjects']:
l1 = {}
l1['name']=x['name']
l1['custom']=x['custom']
l1['label']=x['label']
object_list.append(l1)
return object_list
def handler(event, context):
log.debug("Received event {}".format(json.dumps(event)))
sf = Salesforce(instance_url=event["instance_url"], session_id=event["session_id"])
objs = sf.describe()["sobjects"]
objects = map(describe(sf),objs)
client = boto3.client("dynamodb")
client.batch_write_item(RequestItems={'sobs-metadata-dev':map((lambda obj: {'PutRequest':{'Item':obj}}),objects)})
return {}
def main(argv):
from simple_salesforce import Salesforce
from simple_salesforce.util import date_to_iso8601 as iso
from datetime import datetime, timedelta
from secret import *
import pytz
print 'running...'
inputfile = 'C:\Users\mivinson\workspace\SFDC Data Connection\sfdcDataConnection\\accountSelect.sql'
# parameter = ''
startDate = datetime(2014,11,12,0,0,0, tzinfo=pytz.UTC)
endDate = datetime(2014,11,12,0,0,0, tzinfo = pytz.UTC)
print startDate
print endDate
delta = endDate - startDate
# desc = ''
# try:
# opts, args = getopt.getopt(argv,"hi:p:s:e:d:",["ifile=","param=","sDate=","eDate=","desc="])
# except getopt.GetoptError:
# print '-i <inputfile> -p whereParameter -s startDate -e endDate -d describe(Object)'
# sys.exit(2)
# for opt, arg in opts:
# if opt == '-h':
# print '-i <inputfile>\n-p where Parameter'
# sys.exit()
# elif opt in ("-i", "--ifile"):
# inputfile = arg
# elif opt in ("-s", "--sDate"):
# startDate = arg
# elif opt in ("-e", "--eDate"):
# endDate = arg
# elif opt in ("-p","--param"):
# parameter = arg
# elif opt in ("-d","--desc"):
# desc = arg
## elif opt in ("-o", "--ofile"):
## outputfile = arg
# print 'Input file is ', inputfile
# print 'Output file is "', outputfile
f = file(inputfile,'r')
select = f.read()
sf = Salesforce(username = USER, password = PASSWORD,security_token=HKEY)
#
for i in range(delta.days + 1):
iSelect = select.replace(':Start',(startDate + timedelta(days=i)).strftime('%Y-%m-%dT%H:%M:%SZ'))
iSelect = iSelect.replace( ':End',(startDate + timedelta(days=i+1) + timedelta(microseconds=-1)).strftime('%Y-%m-%dT%H:%M:%SZ'))
print iSelect
req = sf.query_all(iSelect)
print req
def get_oauth_user(oauth):
""" Fetches the user info from the org """
if not oauth or not oauth.get('access_token', None):
return 'Not connected'
sf = Salesforce(instance_url = oauth['instance_url'], session_id = oauth['access_token'], sandbox = oauth.get('sandbox',False))
# Parse user id from id which ends in /ORGID/USERID
user_id = oauth['id'].split('/')[-1]
#user = sf.User.get(user_id)
res = sf.query("SELECT Id, Username, Profile.PermissionsModifyAllData from User WHERE Id='%s'" % user_id)
user = res['records'][0];
return user
def sync_country_data(self, request, queryset):
for setting in queryset:
rows_updated = queryset.update(
sync_country_date = timezone.now(),
)
if setting.sf_active == True:
sfusername = setting.sf_username
sfpassword = setting.sf_password
sftoken = setting.sf_token
sftype = setting.sf_type
if sftype == 'Production':
sftypesetup = False
else:
sftypesetup = True
cursor = connection.cursor()
salesforce_login = {'username':sfusername, 'password':sfpassword, 'security_token':sftoken, 'sandbox':sftypesetup}
sf = Salesforce(**salesforce_login)
countries = sf.query_all("SELECT Id, Name, Programme_Launch__c FROM Country__c where Programme_Launch__c!=''")
for x in countries["records"]:
countryname = str(x["Name"])
sfrecordid = str(x["Id"])
launch = str(x["Programme_Launch__c"])
slug = str(x["Name"]).replace(" ","-").replace("/","-").replace(".","-").lower()
try:
add_objects = ("INSERT INTO data_country "
"(name,sfid,launch,slug) "
"VALUES (%(name)s,%(sfid)s,%(launch)s,%(slug)s)")
data_objects = {
'name' : countryname,
'sfid' : sfrecordid,
'launch' : launch,
'slug' : slug,
}
cursor.execute(add_objects,data_objects)
except:
add_objects = ("UPDATE data_country "
"SET name=%(name)s,sfid=%(sfid)s,launch=%(launch)s,slug=%(slug)s "
"WHERE sfid=%(sfid)s")
data_objects = {
'name' : countryname,
'sfid' : sfrecordid,
'launch' : launch,
'slug' : slug,
}
cursor.execute(add_objects,data_objects)
self.message_user(request, "Your Countries have been syncronised")
def application():
caseid = request.args.get('text', '')
if caseid in kvs:
url = sf_url + '/console#%2f' + kvs[caseid].get('id')
title = kvs[caseid].get('title')
else:
sf = Salesforce(instance_url=sf_url, username=sf_usr, password=sf_pwd, security_token=sf_tkn)
for case in sf.query("SELECT Id, Subject from Case where CaseNumber = '%d'" % int(caseid))['records']:
kvs[caseid] = {'id': case['Id'], 'title': prepare_json_data(case['Subject'])}
url = sf_url + '/console#%2f' + case['Id']
title = prepare_json_data(case['Subject'])
return '{"response_type": "in_channel", "attachments": [{"title": "'+title+'","title_link": "'+url+'",}]}', 200, {'Content-Type': 'application/json'}
def send_signup_info( name, email, address=''):
try:
res = None
payload = {'donorName': name, 'email': email, 'donorAddress': address}
sf = Salesforce(username=SFDC_ACCOUNT, password=SFDC_PASSWORD, security_token=SFDC_TOKEN)
logger.info('send sign-up to SFDC with data: %s', payload)
res = sf.apexecute(SFDC_REVOLV_SIGNUP, method='POST', data=payload)
if res.lower() != 'success':
raise SFDCException(res)
logger.info('SFDC sign-up: sucess.')
except Exception as e:
logger.error('SFDC sign-up: ERROR for name: %s and data: %s, res: %s', name, payload, res, exc_info=True)
send_signup_info.retry(args=[name, email, address], countdown=INTERVAL, exc=e, max_retries=MAX_RETRIES)
def user_id(self):
if not self.config.get('user_id'):
sf = Salesforce(
instance=self.instance_url.replace('https://', ''),
session_id=self.access_token,
version='38.0',
)
result = sf.query_all(
"SELECT Id FROM User WHERE UserName='******'".format(
self.username
)
)
self.config['user_id'] = result['records'][0]['Id']
return self.config['user_id']
def salesforceUpload(self,information):
# we are making safe connection with salesforce by passing account information
sf = Salesforce(username=information['salesforceUserName'], password=information['salesforcepassword'], security_token=information['salesforcesecurityToken'])
# we are getting session id here
sessionId = sf.session_id
flag = 0
# we are querying all folders in your salesforce account
documentQuery = sf.query("SELECT ID,Name FROM folder")
# we are traversing all folders and fetching the folderid for our target folder which is SpringCM
for f in documentQuery['records']:
if (information['targetFoldername'] == f['Name']):
folderid = f['Id']
flag = 1
print "folder found"
break
if flag ==0:
loggerFile.write("Module salesforceUpload:folder not found")
loggerFile.write("\n")
print "folder not found"
print "Leaving this program"
return 0
body = ""
# here we are opening our xml file and encoding into base4..this is required to send files to salesforce
with open(information['filename'], "r") as f:
body = base64.b64encode(f.read())
# Rest API post method call.. using requests post method
# here we are sending parameters:
#first one is url
#second one is header, with content type as json
#third one is our actual data
response = requests.post('https://%s.salesforce.com/services/data/v24.0/sobjects/Document/' % information['salesforceinstance'],
headers = { 'Content-Type': 'application/json', 'Authorization': 'Bearer %s' % sessionId },
data = json.dumps({
'FolderId':folderid,
'Name': information['filename'],
'body': body
})
)
#printing the response output
print response.text
json_data = json.loads(response.text)
if(json_data['success']== True):
return 1
else:
loggerFile.write("Module salesforceUpload:Upload Failed")
loggerFile.write("\n")
return 0
def statuscheck():
global done
uname = input(str("Enter Username: "******"Enter password: "******"Enter API key: "))
sf=Salesforce(username=uname, password=pword, security_token=key)
cid_ask = input(str("Enter Case Number to get Status: " ))
try:
a=sf.query("SELECT Status, Owner.Name FROM case where CaseNumber = '{0}'".format(cid_ask))
status = a['records'][0]['Status']
owner = a['records'][0]['Owner']['Name']
print("Case {0} is owned by {1} and is in status {2}".format(cid_ask, owner, status))
done = True
except:
print("The case number you have entered is invalid.")
def get_cases(self, environment='staging',
type='01250000000Hnex',
status='In Progress',
sub_status='In Development',
technician=''):
""" Get helpdesk tickets for the respective que 100 at a time.
:return: OrderedDict that contains totalsize, done, and records. Records in turn is also given as an
OrderedDict with the actualrecords data.
"""
user_name, pw, token = auth.sfdc_login(environment)
sf = Salesforce(username=user_name, password=pw, security_token=token)
result = sf.query_all(self.build_query())
print result
return result
def send_donation_info(name, amount, project, address=''):
if not settings.SFDC_ACCOUNT:
return
try:
res = None
payload = {'donorName': name, 'projectName': project, 'donationAmount': amount, 'donorAddress': address}
sf = Salesforce(username=SFDC_ACCOUNT, password=SFDC_PASSWORD, security_token=SFDC_TOKEN)
logger.info('send donation to SFDC with data: %s', payload)
res = sf.apexecute(SFDC_REVOLV_DONATION, method='POST', data=payload)
if res.lower() != 'success':
raise SFDCException(res)
logger.info('SFDC donation: success.')
except Exception as e:
logger.error('SFDC donation: ERROR for name: %s and data: %s, res: %s', name, payload, res, exc_info=True)
send_donation_info.retry(args=[name, amount, project, address], countdown=INTERVAL, exc=e,
max_retries=MAX_RETRIES)
def __init__(self):
self.sf = Salesforce(
username=os.environ.get('SF_USER'),
password=os.environ.get('SF_PASS'),
security_token=os.environ.get('SF_TOKEN'),
sandbox=True)
return
def get(self, request, format=None):
"""
Lists all projects on Salesforce.
"""
search = request.query_params.get('search', '')
sf = Salesforce(instance_url=settings.SALESFORCE_URL,
username=settings.SALESFORCE_USERNAME,
password=settings.SALESFORCE_PASSWORD,
security_token=settings.SALESFORCE_TOKEN)
projects_query = ("SELECT Id,Name,Description,CreatedDate FROM Opportunity "
"WHERE Name LIKE '%{}%'").format(search)
projects = sf.query(projects_query)
return Response(projects['records'])
def __create_session__(self, session_id):
'''
Creates a simple_salesforce session and checks to see if it is valid
'''
self.sf_session = Salesforce(instance_url='https://gus.salesforce.com', session_id=session_id)
self.sf_session.query("select Id from ADM_Work__c where Name='NOTHING'") #See if the token is good
self.sf_session_id = session_id
def query_salesforce(arg: str, salesforce: simple_salesforce.Salesforce, command: Dict[str, Any]) -> str:
arg = arg.strip()
qarg = arg.split(' -', 1)[0]
split_args = [] # type: List[str]
raw_arg = ''
if len(arg.split(' -', 1)) > 1:
raw_arg = ' -' + arg.split(' -', 1)[1]
split_args = raw_arg.split(' -')
limit_num = 5
re_limit = re.compile('-limit \d+')
limit = re_limit.search(raw_arg)
if limit:
limit_num = int(limit.group().rsplit(' ', 1)[1])
logging.info('Searching with limit {}'.format(limit_num))
query = default_query
if 'query' in command.keys():
query = command['query']
object_type = object_types[command['object']]
res = salesforce.query(query.format(
object_type['fields'], object_type['table'], qarg, limit_num))
exclude_keys = [] # type: List[str]
if 'exclude_keys' in command.keys():
exclude_keys = command['exclude_keys']
force_keys = [] # type: List[str]
if 'force_keys' in command.keys():
force_keys = command['force_keys']
rank_output = False
if 'rank_output' in command.keys():
rank_output = command['rank_output']
show_all_keys = 'show' in split_args
if 'show_all_keys' in command.keys():
show_all_keys = command['show_all_keys'] or 'show' in split_args
return format_result(res, exclude_keys=exclude_keys, force_keys=force_keys, rank_output=rank_output, show_all_keys=show_all_keys)
def login(self):
self.sf = Salesforce(username=self.sf_user,
password=self.sf_pswd,
security_token=self.sf_token)
# these can be stored and reused for faster authentication
self.instance = self.sf.sf_instance
self.session = self.sf.session_id
def post(self, request, format=None):
if settings.ENABLE_CRM:
crm_project_ids = request.data.get('crm_ids', None)
if crm_project_ids:
projects = CRMProject.objects.filter(id__in=crm_project_ids)
crm_identifiers = ["'" + p.project_identifier + "'" for p in projects.all()]
sf = Salesforce(instance_url=settings.SALESFORCE_URL,
username=settings.SALESFORCE_USERNAME,
password=settings.SALESFORCE_PASSWORD,
security_token=settings.SALESFORCE_TOKEN)
crm_project_query = ("SELECT o.Id,o.Name,o.Description,o.CreatedDate,"
"o.Project_Status__c "
"FROM Opportunity o "
"WHERE o.Id IN ({})").format(", ".join(crm_identifiers))
crm_project_data = sf.query(crm_project_query)
if crm_project_data['totalSize'] > 0:
records = crm_project_data['records']
for record in records:
try:
proj = CRMProject.objects.get(project_identifier=record['Id'])
except:
pass
else:
proj.name = record['Name']
proj.description = record['Description']
proj.status = record.get('Project_Status__c', '')
proj.save()
# Get project and update status
projects = proj.project_set.all()
for p in projects:
if record.get('Project_Status__c', '') != '':
ps = record.get('Project_Status__c')
try:
project_status = ProjectStatus.objects.get(name=ps)
except ObjectDoesNotExist:
project_status = ProjectStatus.objects.create(name=ps)
p.status = project_status
p.save()
return Response({'message': 'Projects updated'})
return Response({'message': 'No projects found on CRM system'}, status=404)
return Response({'message': 'Please provide a list of CRM project IDs'}, status=400)
return Response({'message': 'CRM is currently disabled'}, status=501)
def refresh(self, force=False):
"""
Refreshes this token if the session has expired.
:param force: Refresh this token even if the session hasn't expired
"""
if force:
auth.refresh_token(self)
return Salesforce(instance_url=self.instance_url, session_id=self.access_token, sandbox=self.is_sandbox)
try:
connection = Salesforce(instance_url=self.instance_url, session_id=self.access_token, sandbox=self.is_sandbox)
connection.describe()
except (SalesforceExpiredSession, SalesforceGeneralError):
auth.refresh_token(self)
return Salesforce(instance_url=self.instance_url, session_id=self.access_token, sandbox=self.is_sandbox)
else:
return connection
'''
jekyll2salesforce = dict(L.split() for L in fields.strip().splitlines())
salesforce2jekyll = {v: k for k, v in jekyll2salesforce.items()}
# Fetch records
# ////////////////////////////////////////////////////////////////////////////
# If the word "offline" is specified on the command line, use a cached query
if 'offline' in sys.argv:
try:
with open(CACHE_FILE, 'rb') as file:
records = pickle.load(file)
except IOError:
die("Can't work offline because no CACHE_FILE exists")
else:
sf = Salesforce(username=SALESFORCE_USERNAME,
password=SALESFORCE_PASSWORD,
security_token=SALESFORCE_SECURITY_TOKEN)
records = sf.query_all('''
SELECT {}
FROM Beautiful_Solution__c
WHERE on_website__c = true
'''.format(','.join(salesforce2jekyll)))['records']
with open(CACHE_FILE, 'wb') as file:
pickle.dump(records, file)
# Clean and process records
# ////////////////////////////////////////////////////////////////////////////
# Convert record keys from salesforce to jekyll names, replace None w/'' & remove carriage returns
records = [{
j: (r[s] or '').replace('\r\n', '\n')
for j, s in jekyll2salesforce.items()
def iterate_query(client: Salesforce,
query: str) -> typing.List[typing.Dict[str, typing.Any]]:
return client.query_all(query)["records"]
from simple_salesforce import Salesforce
sf = Salesforce(username='******',
password='******',
security_token='uEGXRLdynZdEtd8Kef2mZANC')
response = sf.query_all("SELECT Id, Name, Car_number__c FROM Car__c ")
cars = response['records']
for car in cars:
print("ID", car["Id"])
print("Name", car["Name"])
print("Number", car["Car_number__c"])
print('')
#https://towardsdatascience.com/using-python-to-get-salesforce-data-97bb5a7ef2cf
#https://www.oktana.com/python-and-salesforce/
#importing the libary
#!pip install simple_salesforce
import pandas as pd
from simple_salesforce import Salesforce
import json
#open json file
with open(
r'C:\Users\Madhuri\Documents\GitHub\ProgramData\Spyder\Credential.json'
) as Credential:
#load json file contains
creds = json.load(Credential)
#salesforce credentials
sf = Salesforce(username=creds['username'],
password=creds['password'],
security_token=creds['token'])
#soql query to extract data from child object
SOQL = "SELECT Date_of_Birth__c,First_Name__c,HSC_Gender__c,Last_Name__c,Test_number__c FROM Child__c"
#SOQL code is put into the method and extract it to a variable, resulting in json data
sf_data = sf.query(SOQL)
#converting json data to pandas dataframe
sf_df = pd.DataFrame(sf_data['records']).drop(columns='attributes')
#printing the child data
print("salesforce data for child object", sf_df)
def aptUpdate():
try:
today = str(datetime.today())[:10]+'T05:00:00.000+0000'
sf = Salesforce(username='******', password='******', security_token='yWlJG8lAKCq1pTBkbBMSVcKg')
response = sf.query("SELECT interaction__c.Contact__r.Accountnumber__c, ScheduledDate__c FROM interaction__c WHERE Subject__c = 'CAD Appointment' AND ScheduledDate__c >= today AND Canceled__c = false AND Outcome__c ='' ")
c,conn = connection()
listUpdate = []
listOut = []
for item in response['records']:
x = c.execute("SELECT * FROM cases WHERE caseid = (%s)",
[thwart(str(item['Contact__r']['Accountnumber__c']))])
row = c.fetchone()
if int(x) != 0 and str(row[11]).find('g2g') == -1:
dateDay = item['ScheduledDate__c'][8:10]
aptDate = datetime.strptime(item['ScheduledDate__c'][:-5], "%Y-%m-%dT%H:%M:%S.%f")
if int(item['ScheduledDate__c'][11:13]) < 5:
aptDate = str(aptDate+timedelta(days=-1))
else:
aptDate = str(aptDate)
if row[11] == None or row[11][:2] != aptDate[5:7] or row[11][3:5] != aptDate[8:10]:
date = aptDate[5:7]+'/'+aptDate[8:10]+'/'+item['ScheduledDate__c'][:4]
data = c.execute("UPDATE cases SET aptDate = (%s) WHERE caseid = (%s)",
[thwart(date),thwart(str(item['Contact__r']['Accountnumber__c']))])
listUpdate.append([item['Contact__r']['Accountnumber__c'],row[11],aptDate])
elif int(x) == 0:
url = 'https://levelsolar.secure.force.com/api/services/apexrest/v2/opportunities?account_number=' + item['Contact__r']['Accountnumber__c']
response_dict = requests.get(url).json()
try:
if response_dict[0]["array_design_completed_date"] == None:
listOut.append(item['Contact__r']['Accountnumber__c'])
except:
pass
if len(listOut) > 0:
subject = 'Please double check the following cases'
msg = Message(subject, sender='*****@*****.**', recipients=['*****@*****.**','*****@*****.**'])
msg.body = 'The following cases are not in AD Home, but will have a meeting soon: ' + str(listOut).replace('u','')
mail.send(msg)
if len(listUpdate) > 0:
subject = 'Following cases has been updated'
msg = Message(subject, sender='*****@*****.**', recipients=['*****@*****.**'])
msg.body = str(listUpdate).replace('u','')
mail.send(msg)
conn.commit()
c.close()
conn.close()
gc.collect()
c,conn = connection()
response = sf.query_all("SELECT Id, StageName, Latest_CAD_Outcome__c, Good_to_Go_Date__c FROM Opportunity WHERE (Latest_CAD_Outcome__c = 'goodtogo' OR Latest_CAD_Outcome__c = 'Good to Go') AND (StageName = 'Array Design' or StageName = 'Array Design Ready')")
OppowG2G = []
for item in response['records']:
try:
OppowG2G.append(item['Id'])
except:
pass
response = sf.query("SELECT Case.Contact.Accountnumber__c, Status FROM Case WHERE Record_Type_Bucket__c = 'design' AND (Status = 'New') AND Case.Opportunity__r.Id in " + str(OppowG2G).replace("u'","'").replace('[','(').replace(']',')').replace(' ',''))
G2GNew = []
for item in response['records']:
G2GNew.append(item['Contact']['Accountnumber__c'])
print "New Design Cases With A G2G: " + str(len(G2GNew))
print str(G2GNew).replace('u','')
response = sf.query("SELECT Case.Contact.Accountnumber__c, Status FROM Case WHERE Record_Type_Bucket__c = 'design' AND (Status = 'Soft Close' OR Status = 'Draft') AND Case.Opportunity__r.Id in " + str(OppowG2G).replace("u'","'").replace('[','(').replace(']',')').replace(' ',''))
G2GSoft = []
for item in response['records']:
G2GSoft.append(item['Contact']['Accountnumber__c'])
response = sf.query_all("SELECT AccountNumber__c, Pre_BMC_Status_Issue__c, Pre_BMC__c.Opportunity__r.Id FROM Pre_BMC__c WHERE AccountNumber__c !=null AND Pre_BMC_Resolved__c = false AND Pre_BMC_Status_Issue__c != 'Meter is not grounded' AND Pre_BMC__c.Opportunity__r.Id in " + str(OppowG2G).replace("u'","'").replace('[','(').replace(']',')').replace(' ',''))
G2GwBMC = []
for item in response['records']:
G2GwBMC.append(item['AccountNumber__c'])
G2GSoftBacklog = list(set(G2GSoft) - set(G2GwBMC))
G2GNoHome = []
for item in G2GSoftBacklog:
x = c.execute("SELECT * FROM cases WHERE caseid = (%s)",
[thwart(str(item))])
if int(x) == 0:
G2GNoHome.append(str(item))
else:
row = c.fetchone()
if int(x) != 0 and str(row[11]) != 'g2g-now':
data = c.execute("UPDATE cases SET aptDate = 'g2g-now' WHERE caseid = (%s)",
[thwart(str(item))])
if int(x) != 0 and float(row[3]) > 2:
url = urlHome+'casestatus/' + str(item) + '?api_key=Jo3y1SAW3S0M3'
payload = {'status' : '1'}
response = requests.post(url, data=payload)
if len(G2GNoHome) > 0:
subject = 'Following G2G cases not in AD Home but needs to be finalized'
msg = Message(subject, sender='*****@*****.**', recipients=['*****@*****.**'])
msg.body = str(G2GNoHome).replace('u','')
mail.send(msg)
conn.commit()
c.close()
conn.close()
gc.collect()
return 'Good'
except Exception as e:
subject = 'CAD Apt sync problem'
msg = Message(subject, sender='*****@*****.**', recipients=['*****@*****.**'])
msg.body = 'Syncing has some problem: ' + str(e) + str(item)
mail.send(msg)
return str(e)
def salesFolder():
try:
today = str(datetime.today())
subject = 'Sales Folder Update ' + today[:10]
msg = Message(subject, sender=('AD - Please Reply To All','*****@*****.**'), recipients=['*****@*****.**','*****@*****.**'])
# msg = Message(subject, sender=('AD - Please Reply To All','*****@*****.**'), recipients=['*****@*****.**'])
sf = Salesforce(username='******', password='******', security_token='yWlJG8lAKCq1pTBkbBMSVcKg')
today = str(datetime.today())[:10]+'T05:00:00.000+0000'
response = sf.query("SELECT Case.Contact.Accountnumber__c, Status FROM Case WHERE Record_Type_Bucket__c = 'design' AND (Status ='Closed' OR Status = 'Cancelled') AND LastModifiedDate >= today")
SFClosed = []
for item in response['records']:
SFClosed.append(item['Contact']['Accountnumber__c'])
base='https://api.box.com/2.0/folders/'
token = box.token_verify()
headers = {'Authorization': 'Bearer ' + token}
json_r = requests.get(base + '2658582399/items?limit=1000&offset=0', headers=headers).json()
json_r2 = requests.get(base + '2658582399/items?limit=1000&offset=1000', headers=headers).json()
folder = {}
for item in json_r['entries']:
try:
numbers = map(int, re.findall(r'\d+', item['name']))
for number in numbers:
folder[number]=item['id']
except Exception as e:
pass
for item in json_r2['entries']:
try:
numbers = map(int, re.findall(r'\d+', item['name']))
for number in numbers:
folder[number]=item['id']
except Exception as e:
pass
msg.html = 'Current Size of Shared Folder: ' + str(json_r['total_count']) + ' Subfolders <br>'
response = sf.query_all("SELECT AccountNumber__c, Pre_BMC_Status_Issue__c, Pre_BMC__c.Opportunity__r.Id FROM Pre_BMC__c WHERE AccountNumber__c !=null AND Pre_BMC_Resolved__c = false AND Pre_BMC_Status_Issue__c='Missing Sales Pictures' AND Opportunity_Stage__c != 'Closed Lost' AND Pre_BMC_Postponed__c = false")
PhotoBMC = []
BMCInShare = []
CaseGone = []
for item in response['records']:
PhotoBMC.append(item['AccountNumber__c'])
for item in folder:
try:
if str(item) in SFClosed:
requests.delete(base + folder[item] + '?recursive=true', headers=headers)
CaseGone.append(item)
if str(item) in PhotoBMC:
folder_response = requests.get(base + folder[item] + '?fields=modified_at,modified_by', headers=headers).json()
modifyedDate = datetime.strptime(folder_response['modified_at'][:-6], "%Y-%m-%dT%H:%M:%S")+timedelta(days=1)
today = datetime.strptime(str(datetime.today())[:10]+'T00:00:00', "%Y-%m-%dT%H:%M:%S")
if modifyedDate > today:
msg.html = msg.html + '<br><a href="https://levelsolar.app.box.com/files/0/f/'+folder[item]+'" >' + str(item) + '</a> modified by: ' + folder_response['modified_by']['name'] + ' at: ' + folder_response['modified_at'][:-6]
BMCInShare.append(str(item))
except Exception as e:
return str(e) + ' wrong here'
NotInBox = list(set(PhotoBMC) - set(BMCInShare))
if len(NotInBox) > 0:
msg.html = msg.html + '<br><br>' +'Following folders have been removed from Shared, but have a Pre-BMC of Missing Sales Pictures open. Sales will need a new one to upload.'
msg.html = msg.html + '<br>' + str(NotInBox).replace('u','')
if len(CaseGone) > 0:
msg.html = msg.html + '<br><br>' +'Following folders have been removed from Shared because Design case is closed.'
msg.html = msg.html + '<br>' + str(CaseGone).replace('u','')
mail.send(msg)
return 'good'
except Exception as e:
return str(e)
import csv from simple_salesforce import Salesforce from salesforce_bulk import SalesforceBulk from salesforce_bulk import CsvDictsAdapter from datetime import date from pathlib import Path from shutil import copyfile username = '******' password = '******' security_token = '3ojna9b4VIY7grIPp0ZmNhlD' sf = Salesforce(username=username,password=password, security_token=security_token) #Declare vaiables localCSVDir = 'c:/kenandy/python/localCSV/' sourceCSVDir = 'c:/kenandy/python/sourceCSV/' stageCSVDir = 'c:/kenandy/python/stageCSV/' configDir = 'c:/kenandy/python/Configuration/' myObject = "KSPRING17__Customer__c" #myObject = "KSPRING17__Supplier__c" salesforceObject = sf.__getattr__(myObject) fieldNames = [field['name'] for field in salesforceObject.describe()['fields']] print(fieldNames) #soqlQuery = "SELECT " + ", ".join(fieldNames) + " FROM " + myObject "WHERE RecordTypeId = '0121I0000009IBkQAM'"
class TimecardEntry:
def __init__(self, cfg="~/.pse.json"):
self.cfg_file = os.path.expanduser(cfg)
with open(self.cfg_file) as inf:
try:
self.cfg = AppConfig(**json.load(inf))
except json.decoder.JSONDecodeError:
sys.exit(f"Unable to decode JSON config at {self.cfg_file}")
self.sf = Salesforce(
username=self.cfg.username,
password=self.cfg.password,
security_token=self.cfg.token,
domain=self.cfg.domain,
client_id="FF",
)
self.contact_id = self.get_contact_id(self.cfg.username)
self.assignments = self.get_assignments_active()
self.global_project = self.get_global_project()
today = date.today()
day = today.strftime("%d-%m-%Y")
self.get_week(day)
def get_week(self, day):
dt = datetime.strptime(day, "%d-%m-%Y")
# FIXME does this need to be self?
self.start = dt - timedelta(days=dt.weekday())
self.end = self.start + timedelta(days=6)
def safe_sql(self, sql):
logger.debug(sql)
try:
return self.sf.query_all(sql)
except SalesforceError:
logger.error("error on query:{}".format(sql))
logger.error(sys.exc_info()[1])
sys.exit(1)
def list_timecard(self, details, start, end):
self.assignments = self.get_assignments_all()
fields = [
"Id",
"Name",
"pse__Project__c",
"pse__Assignment__c",
"pse__Monday_Hours__c",
"pse__Tuesday_Hours__c",
"pse__Wednesday_Hours__c",
"pse__Thursday_Hours__c",
"pse__Friday_Hours__c",
"pse__Status__c",
]
if details:
base = [
"OwnerId",
"PROJECT_ID__c",
"pse__Approved__c",
"pse__Start_Date__c",
"pse__End_Date__c",
]
fields = (base + fields + [
"CreatedById",
"CreatedDate",
"IsDeleted",
"LastModifiedById",
"LastModifiedDate",
"LastReferencedDate",
"LastViewedDate",
"pse__Audit_Notes__c",
"pse__Billable__c",
"pse__Resource__c",
"pse__Location_Mon__c",
"pse__Location_Tue__c",
"pse__Location_Wed__c",
"pse__Location_Thu__c",
"pse__Location_Fri__c",
"pse__Saturday_Hours__c",
"pse__Saturday_Notes__c",
"pse__Location_Sat__c",
"pse__Sunday_Hours__c",
"pse__Sunday_Notes__c",
"pse__Location_Sun__c",
"pse__Timecard_Notes__c",
"pse__Submitted__c",
"pse__Monday_Notes__c",
"pse__Tuesday_Notes__c",
"pse__Wednesday_Notes__c",
"pse__Thursday_Notes__c",
"pse__Friday_Notes__c",
])
sql_query = """
select
{}
from pse__Timecard_Header__c
where
pse__Start_Date__c = {} and pse__End_Date__c = {} and
pse__Resource__c = '{}' """.format(
", ".join(fields),
start,
end,
self.contact_id,
)
results = self.safe_sql(sql_query)
if len(results["records"]) > 0:
rs = []
for r in results["records"]:
r.pop("attributes", None)
# adding Project name
if r.get("pse__Assignment__c", "") in self.assignments.keys():
r["pse__Project_Name__c"] = self.assignments[
r["pse__Assignment__c"]]["assignment_name"]
if r.get("pse__Project__c", "") in self.global_project.keys():
r["pse__Project_Name__c"] = self.global_project[
r["pse__Project__c"]]["project_name"]
rs.append(r)
return rs
else:
logger.warning("No time card")
return []
def get_contact_id(self, email):
name_part = email.split("@")[0]
r = self.safe_sql(
("select Id, Name, Email from Contact "
f"where pse__Is_Resource__c = true and Email LIKE '{name_part}@%'"
))
return r["records"][0]["Id"]
def get_timecard_id(self, timecard_name):
r = self.safe_sql(
"select Id from pse__Timecard_Header__c where Name = '{}'".format(
timecard_name))
return r["records"][0]["Id"]
def get_assignments_all(self, contact_id=None):
if not contact_id:
contact_id = self.contact_id
sql_query = (
"select Id, Name, pse__Project__c, pse__Project__r.Name, "
"pse__Project__r.pse__Is_Billable__c from pse__Assignment__c "
f"where pse__Resource__c = '{contact_id}' and "
"Open_up_Assignment_for_Time_entry__c = false and "
"pse__Closed_for_Time_Entry__c = false")
return self.get_assignments(sql_query)
def get_assignments_active(self, contact_id=None):
if not contact_id:
contact_id = self.contact_id
sql_query = (
"select Id, Name, pse__Project__c, pse__Project__r.Name, "
"pse__Project__r.pse__Is_Billable__c from pse__Assignment__c "
f"where pse__Resource__c = '{contact_id}' and "
"Open_up_Assignment_for_Time_entry__c = false and "
"pse__Closed_for_Time_Entry__c = false and "
"pse__Exclude_from_Planners__c = false and "
f"pse__End_Date__c > {date.today().strftime('%Y-%m-%d')}")
return self.get_assignments(sql_query)
def get_assignments(self, sql_query):
results = self.safe_sql(sql_query)
assignments = {}
for r in results["records"]:
assignments[r["Id"]] = {
"assignment_id": r["Id"],
"assignment_name": r["Name"],
"project_id": r["pse__Project__c"],
"project_name": r["pse__Project__r"]["Name"],
"billable": r["pse__Project__r"]["pse__Is_Billable__c"],
}
return assignments
def get_global_project(self):
results = self.safe_sql(
("select Id, Name, pse__Is_Billable__c "
"from pse__Proj__c "
"where pse__Allow_Timecards_Without_Assignment__c = true "
"and pse__Is_Active__c = true"))
rs = {}
for r in results["records"]:
rs[r["Id"]] = {
"project_id": r["Id"],
"project_name": r["Name"],
"billable": r["pse__Is_Billable__c"],
}
return rs
def delete_time_entry(self, _id):
try:
self.sf.pse__Timecard_Header__c.delete_cmd(_id)
except SalesforceError:
logger.error("failed on deletion id:{}".format(_id))
logger.error(sys.exc_info()[1])
sys.exit(1)
def add_time_entry(self, assignment_id, day_n, hours, notes):
# FIXME assignment_id is used only here?
self.assignment_id = assignment_id
new_timecard = {
"pse__Start_Date__c": self.start.strftime("%Y-%m-%d"),
"pse__End_Date__c": self.end.strftime("%Y-%m-%d"),
"pse__Resource__c": self.contact_id,
"pse__Status__c": "Saved",
}
if self.assignment_id in self.assignments.keys():
new_timecard["pse__Assignment__c"] = self.assignment_id
new_timecard["pse__Project__c"] = self.assignments[
self.assignment_id]["project_id"]
new_timecard["pse__Billable__c"] = self.assignments[
self.assignment_id]["billable"]
sql_query = (
"select Id from pse__Timecard_Header__c "
"where "
f"pse__Start_Date__c = {self.start.strftime('%Y-%m-%d')} "
f"and pse__End_Date__c = {self.end.strftime('%Y-%m-%d')} and "
f"pse__Resource__c = '{self.contact_id}' and "
f"pse__Assignment__c = '{self.assignment_id}' and "
f"pse__Project__c = '{self.assignments[self.assignment_id]['project_id']}' and "
"pse__Status__c not in ('Submitted', 'Approved')")
else:
# most probably is a project without assigment
new_timecard["pse__Project__c"] = self.assignment_id
new_timecard["pse__Billable__c"] = self.global_project[
self.assignment_id]["billable"]
sql_query = (
"select Id from pse__Timecard_Header__c "
"where "
f"pse__Start_Date__c = {self.start.strftime('%Y-%m-%d')} "
f"and pse__End_Date__c = {self.end.strftime('%Y-%m-%d')} and "
f"pse__Resource__c = '{self.contact_id}' and "
f"pse__Project__c = '{self.assignment_id}' and "
"pse__Status__c not in ('Submitted', 'Approved') ")
new_timecard["pse__" + day_n + "_Hours__c"] = hours
new_timecard["pse__" + day_n + "_Notes__c"] = notes
results = self.safe_sql(sql_query)
logger.debug(json.dumps(new_timecard, indent=4))
if len(results["records"]) > 0:
logger.debug("required update")
try:
self.sf.pse__Timecard_Header__c.update(
results["records"][0]["Id"], new_timecard)
except SalesforceError:
logger.error("failed on update")
logger.error(sys.exc_info()[1])
sys.exit(1)
else:
try:
self.sf.pse__Timecard_Header__c.create(new_timecard)
except SalesforceError:
logger.error("failed on creation")
logger.error(sys.exc_info()[1])
sys.exit(1)
def submit_time_entry(self, _id):
data = {
"pse__Submitted__c": True,
"pse__Status__c": "Submitted",
}
try:
self.sf.pse__Timecard_Header__c.update(_id, data)
except SalesforceError:
logger.error("failed on update")
logger.error(sys.exc_info()[1])
sys.exit(1)
from db import upsert
from config import config
from datetime import datetime, timedelta
from pytz import timezone
from api import call_and_load_json
from simple_salesforce import Salesforce
sf = Salesforce(username=config.get('USERNAME'),
password=config.get('PASSWORD'),
security_token=config.get('TOKEN'))
def escape_email(email):
# SF doesn't allow certain characters unescaped in email searches.
# This isn't all of them, so may need to add more.
return email.replace('-', '\-').replace('+', '\+')
# change days to adjust date range
def create_filters(days=365):
filters = [('system', 'true')]
sync_date = datetime.today()
# get data from 12 hours before the last sync date until now
sync_date = sync_date - timedelta(days=int(days))
# Convert to the Wufoo API req.
# Lets datetime know that our date is central
localized = timezone('US/Central').localize(sync_date)
"""
Created on Fri Jun 14 12:04:38 2019
@author: DannySwift
"""
import pandas as pd
import yaml
from datetime import datetime
from docx import Document
from simple_salesforce import Salesforce
#%%
config = yaml.safe_load(open('config.yml'))
sf = Salesforce(username=config['username'],
password=config['password'],
security_token=config['security_token'])
df = pd.read_csv(r'Downloads/GalaThanks.csv')
date = datetime.today().strftime('%B %d')
#%%
def format_doc(name, amount, date, don_date, tix, email):
doc = Document(r'Downloads/Thanks.docx')
for p in doc.paragraphs:
if 'TODAYS DATE' in p.text:
p.text = p.text.replace('TODAYS DATE', date)
if 'NAME' in p.text:
p.text = p.text.replace('NAME', name)
if 'ADDRESS' in p.text:
from mwclient import Site
from PythonConfluenceAPI import ConfluenceAPI
import re
from simple_salesforce import Salesforce
api = ConfluenceAPI('admin', '123@qwe', 'http://127.0.0.1:8090')
FileOut = open('PagesBUSList.txt', 'w')
UserAgent = 'Wiki_parser/0.1 run by DremSama'
site = Site(('http', 'wiki.support.veeam.local'),
path='/',
clients_useragent=UserAgent)
sf = Salesforce(username='******',
password='******',
security_token='dNr44yHsFXaSuRmKXunWPlzS')
PagesList = []
PagesBUGSList = []
page = site.Pages[
'Bug 44540 - Download from EM FLR doesn\'t work with long paths more than 260 symbols']
print('----------------------------------')
BugID = re.match(r'[A-z,a-z]ug\b.(\d*)[-|\s]*(.*)', page.page_title)
if BugID:
BugID_NUM = BugID.group(1)
BugID_SUBJECT = BugID.group(2)
textALL = page.text(0)
if not textALL:
print('ERROR: Page "' + page.name + '" has no text')
elif textALL.startswith('#REDIRECT'):
print('Page "' + page.name + '" is only a redirect page, skipping')
else:
print(page.name)
BugCaseID = re.findall(r"'''Case ID: '''(\d*)", textALL)
BugToBeFixedIn = re.findall(r"'''To be fixed in: '''(.*)", textALL)
def caseValidator_v2(app):
with app.app_context():
try:
today = str(datetime.today())[:10]+'T05:00:00.000+0000'
# return today
sf = Salesforce(username='******', password='******', security_token='yWlJG8lAKCq1pTBkbBMSVcKg')
c,conn = connection()
#cases in AD Home but not g2g or change design
ADHome = []
data = c.execute("SELECT caseid FROM cases WHERE aptDate NOT LIKE '%g2g-%' AND note NOT LIKE '%#CHANGE DESIGN#%' OR aptDate IS NULL")
for row in c:
ADHome.append(str(row[0]))
#cases will have a CAD meeting
SFMeeting = []
response = sf.query_all("SELECT interaction__c.Contact__r.Accountnumber__c, ScheduledDate__c FROM interaction__c WHERE Subject__c = 'CAD Appointment' AND ScheduledDate__c >= today AND Canceled__c = false AND Outcome_Submitted__C ='' ")
for item in response['records']:
SFMeeting.append(item['Contact__r']['Accountnumber__c'])
#cases in AD Home will not have a meeting
leftOver = list(set(ADHome) - set(SFMeeting))
#cases in SF are canceled
SFCancel = []
response = sf.query_all("SELECT Account_Number__c FROM Opportunity WHERE StageName = 'Closed Lost' AND LastModifiedDate >= today")
for item in response['records']:
SFCancel.append(item['Account_Number__c'])
#cases in AD Home are actually canceled
needToCLean = []
needToCLean = list(set(SFCancel) & set(ADHome))
#removed canceled cases from AD Home
for sfdc in needToCLean:
url = urlHome + 'case/caseremove/'+str(sfdc)+'?api_key=Jo3y1SAW3S0M3'
requests.get(url)
#update no meeting case in AD Home
for sfdc in leftOver:
data = c.execute("UPDATE cases SET aptDate = 'No Meeting' WHERE caseid = (%s)",
[thwart(sfdc)])
# return str(needToCLean).replace('u','q')
# return str(len(needToCLean))
conn.commit()
c.close()
conn.close()
gc.collect()
if len(needToCLean) > 0:
subject = 'Following cases have been removed from AD Home'
msg = Message(subject, sender='*****@*****.**', recipients=['*****@*****.**'])
msg.body = str(needToCLean).replace('u','')
mail.send(msg)
return 'Good'
except Exception as e:
subject = 'Something wrong with case validator'
msg = Message(subject, sender='*****@*****.**', recipients=['*****@*****.**'])
msg.body = str(e)
mail.send(msg)
class SFConnectAPI:
"""
SALESFORCE API CLASS
"""
sf = None
sf_bulk = None
def __init__(self, sf_config=ConnectionString):
"""
:param sf_config:
"""
print("connecting with salesforce.")
# self.sf = Salesforce(username=sf_config.USERNAME,
# password=sf_config.PASSWORD,
# sandbox=True)
# security_token=sf_config.SF_AUTH_TOKEN)
try:
self.sf = Salesforce(instance=sf_config.SF_URL,
session_id=sf_config.ACCESS_TOKEN,
sandbox=True)
except:
sf_config.ACCESS_TOKEN = self.get_access_token()
self.sf = Salesforce(instance=sf_config.SF_URL,
session_id=sf_config.ACCESS_TOKEN,
sandbox=True)
self.sf_config = sf_config
print("connection success")
def get_header(self, session_id):
"""
:return:
"""
return {
'Content-Type': 'application/json',
'Authorization': 'Bearer ' + session_id,
'X-PrettyPrint': '1'
}
def get_access_token(self):
"""
:return:
"""
data = {
'client_secret': ConnectionString.CLIENT_SECRET,
'username': ConnectionString.USERNAME,
'password': ConnectionString.PASSWORD,
'client_id': ConnectionString.CONSUMER_KEY,
'redirect_uri': ConnectionString.REDIRECT_URI
}
import requests
service = requests.post(
"https://test.salesforce.com/services/oauth2/token?grant_type=password",
data=data)
print(service.json())
return service.json().get("access_token")
def execute_soql(self, query):
"""
:param sf_conn:
:param query:
:param host:
:return:
"""
result = self.sf.query(query)
return result
def create_record(self, object_name=None, data=None):
# session_id = self.get_login_connection()
session_id = ConnectionString.ACCESS_TOKEN
try:
sf_obj = SFType(object_name, session_id, self.sf_config.SF_URL)
result = sf_obj.create(data)
except Exception as ex:
result = ex
print(repr(ex))
return result
def update_record(self, record_id=None, object_name=None, data=None):
session_id = ConnectionString.ACCESS_TOKEN
try:
sf_obj = SFType(object_name, session_id, self.sf_config.SF_URL)
result = sf_obj.update(record_id, data)
except Exception as ex:
result = ex
print(repr(ex))
return result
# import datetime
# from datetime import timedelta
# # sf = SFConnectAPI()
# #
# # query = "select id from Contact limit 10"
# # print(str(sf.get_access_token()))
# # username = "******"
# # password = "******"
# sf_instance = SFConnectAPI()
# emp = sf_instance.execute_soql("select id,Name from Attendance__c where date__c >={start_date} " \
# "AND date__c <={end_date}".format(start_date=datetime.date.today(),
# end_date=datetime.date.today() + timedelta(days=5) ))
# print(repr(emp['records']))
# import requests
#
# params = {
# "grant_type": "password",
# "client_id": ConnectionString.CONSUMER_KEY, # Consumer Key
# "client_secret": ConnectionString.CLIENT_SECRET, # Consumer Secret
# # "username": ConnectionString.USERNAME, # The email you use to login
# # "password": ConnectionString.PASSWORD,
# "redirect_uri": "https://www.google.com"
# # Concat your password and your security token
# }
# r = requests.post("https://login.salesforce.com/services/oauth2/token", params=params)
# print(repr(r))
# access_token = r.json().get("access_token")
# instance_url = r.json().get("instance_url")
# print("Access Token:", access_token)
# print("Instance URL", instance_url)
# # access_token = '00D7F0000048jEa!ARcAQDqX99zhupkuv8RzVXYjEP7d_ZtcNQrMM2rK1O76mSvUt.UJF8ldlRzThVN3xXRmAjLzvC8yGZKUN7XBCK2aijl7t1ns'
#
# from simple_salesforce import Salesforce
# sf = Salesforce(instance='ap5.salesforce.com', session_id=access_token)
# result = sf.query("select id from Attendance__c")
# print(repr(result))<class 'tuple'>:
# sf_instance = SFConnectAPI()
# location = (77.2167, 28.6667)
# sf_instance.create_record( object_name='Resume_Google_Drive_Link__c',
# data={'Name__c': 'TEST RECORD 1',
# 'Contact_Number__c': '9936556447',
# 'Email__c': '*****@*****.**',
# 'Google_Drive_URL__c': 'https://drive.google.com/file/d/1Stu3UScJ6uy8V9zGa1rqVAGxNehlry6c/view',
# 'Position__c': 'Developer',
# 'Address__latitude__s': location[1],
# 'Address__longitude__s': location[0],
# 'City__c': 'Delhi',
# } )
# conn = Salesforce(username=ConnectionString.USERNAME,
# password=ConnectionString.PASSWORD,
# sandbox=True)
# print(repr(conn))
# import requests
#
# data = {'client_secret': ConnectionString.CLIENT_SECRET,
# 'client_id': ConnectionString.CONSUMER_KEY,
# 'redirect_uri': ConnectionString.REDIRECT_URI}
# result = requests.post(' https://test.salesforce.com/services/oauth2/authorize?response_type=code', data=data)
# print(result.json())
if 'code' in query:
code = query.getvalue('code')
data = {
'grant_type': 'authorization_code',
'redirect_uri': redirect_uri,
'code': code,
'client_id': consumer_key,
'client_secret': consumer_secret
}
headers = {'content-type': 'application/x-www-form-urlencoded'}
req = requests.post(access_token_url, data=data, headers=headers)
response = req.json()
print(response)
sf = Salesforce(instance_url=response['instance_url'],
session_id=response['access_token'])
records = sf.query("SELECT Id, Name, Email FROM Contact")
# records = records['records']
# print web page
print("Content-type: text/html")
print("<html><body>")
print("<h1>SELECT Id, Name, Email FROM Contact</h1>")
print("<table>")
print("<tr><td><b>Name</b></td><td><b>Email</b></td></tr>")
for record in records:
print("<tr><td>" + record['Name'] + "</td><td>" + record['Email'] +
"</td></tr>")
def run(self):
sf = Salesforce(username='******',
password='******',
security_token='3GKZny4jRWgmXNUmvFjzKhggQ')
print(
'NEW START.............................................................................................................'
)
for attemt in attemts:
# try:
# c.close();
# conn5.commit();
# conn5.close();
#
# except Exception as e:
# print(e)
# try:
# c.close();
# conn6.commit();
# conn6.close();
# except Exception as e:
# print(e)
# if attemt=='RHID': db_path=os.path.abspath("\\\\IXI-APPSVR02\\RHID Sustaining Production\\ttproj.db")
# if attemt=='RHIT': db_path=os.path.abspath("\\\\IXI-APPSVR02\\RapidHIT Sustaining\\ttproj.db")
#while True:
#time.sleep(60)
# print("After 5 sec")
date_S = datetime.now() - timedelta(days=1)
date_E = datetime.now() - timedelta(days=0)
print(str(date_S)[0:10])
print(attemt)
hastrack = False
solution = ""
try:
if attemt == 'RHID':
db_path = os.path.abspath(
"\\\\IXI-APPSVR02\\RHID Sustaining Production\\ttproj.db"
)
if attemt == 'RHIT':
db_path = os.path.abspath(
"\\\\IXI-APPSVR02\\RapidHIT Sustaining\\ttproj.db")
conn5 = sqlite3.connect(db_path)
c = conn5.cursor()
hourago = datetime.now() - timedelta(hours=1)
#s='select DefectNum,Summary,Status from DEFECTS inner join DEFECTEVTS on DEFECTEVTS.ParentID=DEFECTS.idRecord where dateEvent> '+ str(hourago).rpartition(':')[0]
# s='select idRecord,DefectNum,Summary,Status,dateModify from DEFECTS where dateModify >'+ repr(str(hourago).rpartition(':')[0])
print(str(date_S).rpartition(':')[0])
#print (str(date_E).rpartition(':')[4])
s = 'select idRecord,DefectNum,Summary,Status,dateModify from DEFECTS where dateModify >=' + repr(
str(date_S).rpartition(':')[0])
print(s)
#s='select idRecord,DefectNum,Summary,Status,dateModify from DEFECTS where dateModify >='+ repr(str(date_S).rpartition(':')[0]) + ' and dateModify <='+ repr(str(date_E).rpartition(':')[0])
#s='select dateModify,idRecord,DefectNum,Summary,Status,dateModify from DEFECTS where DefectNum =2976'
#'+ repr(str(hourago).rpartition(':')[0])
c.execute(s)
rows = c.fetchall()
defect_as_dict = []
#file = open("sync.txt","w")
for row in rows:
DefectNum = row[1]
print(DefectNum)
Summary = row[2]
Status = row[3]
if attemt == 'RHID':
s = 'select Custvalue from CUSTMVAL where idCustRec=681 and ParentID=' + repr(
str(row[0])) + ''
if attemt == 'RHIT':
s = 'select Custvalue from CUSTMVAL where idCustRec=1072 and ParentID=' + repr(
str(row[0])) + ''
c.execute(s)
rows1 = c.fetchall()
casenumber = 0
for row1 in rows1:
casenumber = row1[0]
print(casenumber)
#Synceddict=db.search(CN.casenumber == casenumber)
s = 'select Name,dateEvent,DEFECTEVTS.Notes ,OrderNum from DEFECTEVTS inner join EVENTS on DEFECTEVTS.EvtDefID=EVENTS.idRecord and ParentID=' + str(
row[0]) + ' order by OrderNum'
c.execute(s)
tracks_as_dict = []
tracktext = ""
tracks = c.fetchall()
for track in tracks:
hastrack = True
if (track[0] == "Verified"): solution = track[2]
track_as_dict = {
'Event': track[0],
'Date': track[1],
'Notes': track[2]
}
if (hastrack):
tracks_as_dict.append(track_as_dict)
tracktext = str(tracks_as_dict)
#UPDATE WORKFLOW
list = sf.query(
"SELECT Id,Defect_Ref__c FROM Case WHERE CaseNumber = "
+ repr(str(casenumber)))
dict = list["records"]
if len(dict) > 0:
cid = dict[0]['Id']
CurrentDefectNumber = dict[0]['Defect_Ref__c']
sf.Case.update(cid,
{'Workflow_from_ALM__c': tracktext})
if CurrentDefectNumber is None:
#sf.headers({'Sforce-Auto-Assign': 'FALSE'})
sf.Case.update(cid, {'Defect_Ref__c': DefectNum})
#Write the log
file = open("C:\Python34\ixi_TBOX34\sync.txt", "a")
logstr = "|" + str(casenumber) + "|" + str(
DefectNum) + "|" + attemt + "|" + repr(
str(datetime.now()))
file.write(logstr + "\n")
file.close()
#UPDATE SOLUTIONS
if Status == 10:
if (str(solution) != ""):
list = sf.query(
"SELECT Id FROM Case WHERE CaseNumber = " +
repr(str(casenumber)))
dict = list["records"]
if len(dict) > 0:
print(dict[0]['Id'])
cid = dict[0]['Id']
mydict = sf.Solution.create({
'SolutionNote':
solution,
'SolutionName':
'solution - ' + Summary
})
#db.insert({'casenumber': casenumber})
print(mydict['id'])
sid = mydict['id']
solution = sf.Solution.get(mydict['id'])
print(mydict['success'])
#orderdisc=solution('OrderDict')
print(solution['SolutionNumber'])
sn = solution['SolutionNumber']
mydict = sf.CaseSolution.create({
'CaseId':
cid,
'SolutionId':
sid
})
print(mydict)
#Update case
#file.close()
#sf.Case.Update
except Exception as e:
print(e)
class ExpenseEntry(object):
def __init__(self, cfg="~/.pse.json"):
self.cfg_file = os.path.expanduser(cfg)
with open(self.cfg_file) as f:
self.cfg = json.load(f)
credential_store = self.cfg.get('credential_store', 'default')
if credential_store == 'default':
password = base64.b64decode(self.cfg["password"]).decode()
security_token = self.cfg["token"]
elif credential_store == 'keyring':
password = keyring.get_password("salesforce_cli", f"{self.cfg['username']}_password")
security_token = keyring.get_password("salesforce_cli", f"{self.cfg['username']}_token")
self.sf = Salesforce(username=self.cfg["username"],
password=password,
security_token=security_token,
sandbox=self.cfg.get("sandbox", None),
client_id="FF"
)
self.contact_id = self.get_contact_id(self.cfg["username"])
self.assignments = self.get_assignments_active()
today = date.today()
day = today.strftime("%d-%m-%Y")
self.get_week(day)
def get_week(self, day):
dt = datetime.strptime(day, "%d-%m-%Y")
self.start = dt - timedelta(days=dt.weekday())
self.end = self.start + timedelta(days=6)
def safe_sql(self, sql):
logger.debug(sql)
try:
return self.sf.query_all(sql)
except:
logger.error("error on query:{}".format(sql))
logger.error(sys.exc_info()[1])
sys.exit(1)
def _upload_image(self, name, parent_id):
body = self._get_file_body(name)
upload_url = 'https://%s/services/data/v%s/sobjects/Attachment/' % (self.sf.sf_instance, self.sf.sf_version)
response = requests.post(upload_url,
headers={'Content-Type': 'application/json',
'Authorization': 'Bearer %s' % self.sf.session_id},
data=json.dumps({
'ParentId': parent_id,
'Name': name,
'body': body
})
)
r = json.loads(response.text)
if r['success']:
return r['id']
def _get_file_body(self, filename):
body = ""
with open(filename, "rb") as f:
body = base64.b64encode(f.read()).decode()
return body
def _upload_expense(self, data, dry_run=False):
if dry_run:
print(data)
else:
r = self.sf.pse__Expense__c.create(data)
if r['success']:
return r['id']
def get_contact_id(self, email):
name_part = email.split("@")[0]
r = self.safe_sql(
"select Id, Name, Email from Contact where pse__Is_Resource__c = true and Email LIKE '{}@%'".format(
name_part))
return r["records"][0]["Id"]
def get_assignments_active(self, contact_id = None):
if not contact_id:
contact_id = self.contact_id
SQL = '''select Id, Name, pse__Project__c, pse__Project__r.Name, pse__Project__r.pse__Is_Billable__c from pse__Assignment__c
where pse__Resource__c = '{}' and
Open_up_Assignment_for_Time_entry__c = false and
pse__Closed_for_Time_Entry__c = false and
pse__Exclude_from_Planners__c = false and
pse__End_Date__c > {}
'''.format(
contact_id,
date.today().strftime("%Y-%m-%d")
)
return self.get_assignments(SQL)
def get_assignments(self, SQL):
results = self.safe_sql(SQL)
assignments = {}
for r in results["records"]:
assignments[r["Id"]] = {"assignment_id": r["Id"],
"assignment_name": r["Name"],
"project_id": r["pse__Project__c"],
"project_name": r["pse__Project__r"]["Name"],
"billable": r["pse__Project__r"]["pse__Is_Billable__c"]}
return assignments
def connect_sf(self):
sf = Salesforce(username=self.username, password=self.password, security_token=self.security_token, sandbox=self.use_sandbox)
self.logger.info("connected to SFDC {}".format(self.sfurl))
return sf
from flask import Flask, render_template, jsonify, request, send_file
import json
import pytz
import datetime
import requests
from simple_salesforce import Salesforce
from flask_sqlalchemy import SQLAlchemy, sqlalchemy
sf = Salesforce(username='******',
password='******',
security_token='yWlJG8lAKCq1pTBkbBMSVcKg')
app = Flask(__name__)
app.config[
'SQLALCHEMY_DATABASE_URI'] = 'sqlite:////root/lboard/map-master/database.db'
db = SQLAlchemy(app)
class Assignment(db.Model):
id = db.Column(db.Integer, primary_key=True)
territory = db.Column(db.String)
amb = db.Column(db.String)
start = db.Column(db.DateTime)
end = db.Column(db.DateTime)
def __init__(self, territory, amb, start, end):
self.territory = territory
self.amb = amb
self.start = start
self.end = end
from cloudant.client import Cloudant
from cloudant.error import CloudantException
from simple_salesforce import Salesforce
sf = Salesforce(username='******', password='******', security_token='p59BlagIhfIvUYOMols0aktca')
records = sf.query("SELECT Id, Name, Content__c FROM Note__c")
records = records['records']
uploadData = []
for record in records:
data = [record["Name"], record["Content__c"]]
uploadData.append(data)
client = Cloudant("c8e8c***bluemix", "1469d4cb***", url="https://c8e8c***.cloudant.com")
client.connect()
databaseName = "note"
# Delete old
try :
client.delete_database(databaseName)
except CloudantException:
print "There was a problem deleting '{0}'.\n".format(databaseName)
else:
print "'{0}' successfully deleted.\n".format(databaseName)
myDatabase = client.create_database(databaseName)
if myDatabase.exists():
print "'{0}' successfully created.\n".format(databaseName)
# Update database
for document in uploadData:
# Retrieve the fields in each row.
def salesforce_client(self):
return Salesforce(
instance=self.instance_url.replace("https://", ""),
session_id=self.access_token,
version=self.latest_api_version,
)
from simple_salesforce import Salesforce
import pandas as pd
USER='******'
PWD='***'
token='***'
# to access UAT set the sandbox true option
sf = Salesforce(username=USER, password=PWD, security_token=token, sandbox=True)
# (1) Set data type mapping# (1) Se
mapping =\
{'id':'varchar',\
'boolean':'bool',\
'reference':'varchar',\
'string':'varchar',\
'picklist':'varchar',\
'textarea':'varchar',\
'double':'decimal',\
'phone':'varchar',\
'url':'varchar',\
'currency':'double',\
'int':'int',\
'datetime':'timestamp',\
'date':'timestamp',\
'email':'varchar',\
'multipicklist':'varchar',\
'percent':'decimal',\
# Excel SaveAs file path
year = datetime.datetime.today().year
FilePath = 'I:\\10-Sales\\Delphi\\Audit_Info\\Audits\\MPE\\' + str(year) + '\\Booking\\Booking Other Income\\'
# Salesforce Login info
Username = password.Username
Password = password.Password
securitytoken = password.securitytoken
# Change proxies and ports
proxies = {"http": "http://10.96.250.10:80", "https":"https://10.96.250.10:80"}
# Login to Salesforce
sf = Salesforce(instance='na1.salesforce.com', session_id='', proxies=proxies, username = Username, password= Password, security_token = securitytoken)
session = requests.Session()
# Date Range is from now til 60 days later
now = datetime.datetime.now()
StartDate = now + datetime.timedelta(days=60)
Current_Date = str(now.year) + '-' + str('%02d'% now.month) + '-' + str('%02d'% now.day)
Start_Date = str(StartDate.year) + '-' + str('%02d'% StartDate.month) + '-' + str('%02d'% StartDate.day)
File_Date = str(now.year) + str('%02d'% now.month) + str('%02d'% now.day)
# Run SOQL to get data
BKdata1 = sf.query("SELECT nihrm__Booking__r.Owner.Name, nihrm__Booking__r.Owner.Email, nihrm__Booking__r.nihrm__Account__r.Name, nihrm__Booking__r.nihrm__Agency__r.Name, nihrm__Booking__r.Name, nihrm__Booking__r.nihrm__BookingStatus__c, nihrm__Booking__r.nihrm__ArrivalDate__c, \
nihrm__Booking__r.nihrm__DepartureDate__c, Name, nihrm__BookedRevenueTotal__c, nihrm__Description__c \
FROM nihrm__BookingOtherIncome__c \
WHERE (nihrm__Booking__r.nihrm__ArrivalDate__c <= TODAY AND nihrm__Booking__r.nihrm__ArrivalDate__c >= " + str(Start_Date) + ") AND (nihrm__Booking__r.nihrm__BookingStatus__c IN ('Prospect', 'Tentative'))")
#WHERE (nihrm__Booking__r.nihrm__ArrivalDate__c <= TODAY AND nihrm__Booking__r.nihrm__ArrivalDate__c >= " + str(Start_Date) + ")")
def print_pe_users(client: Salesforce) -> None:
"""
Prints the users that can PE to stdout
"""
print("")
users = list(find_users_with_apex(client))
if not users:
print("No user can PE")
return
print("The following users can escalate privileges:")
for user in find_users_with_apex(client):
print(f"+ {user.name} ({user.user_id}) - profile {user.profile}")
if __name__ == "__main__":
import argparse
parser = argparse.ArgumentParser()
parser.add_argument("-u", "--username", help="Username", required=True)
parser.add_argument("-p", "--password", help="Password", required=True)
parser.add_argument("-t", "--token", help="Security Token", required=True)
args = parser.parse_args()
sfdc_client = Salesforce(username=args.username,
password=args.password,
security_token=args.token)
print_pe_users(sfdc_client)
def get_salesforce_conn():
sfdc = get_conn('SFDC_CONN_ID_HERE')
return Salesforce(username=sfdc.login,
password=sfdc.password,
security_token=sfdc.extra_dejson.get('sfdc_security_token'),
instance_url=sfdc.host)
def main():
import argparse
import configparser
parser = argparse.ArgumentParser(
description='Export Notes & Attachments from Salesforce')
parser.add_argument(
'-q',
'--query',
metavar='query',
required=True,
help=
'SOQL to select records from where Attachments should be downloaded. Must return the '
'Id(s) of parent objects.')
args = parser.parse_args()
# Get settings from config file
config = configparser.ConfigParser()
config.read('download.ini')
username = config['salesforce']['username']
password = config['salesforce']['password']
token = config['salesforce']['security_token']
is_sandbox = config['salesforce']['connect_to_sandbox']
download_attachments = config['salesforce'][
'download_attachments'] == 'True'
download_notes = config['salesforce']['download_notes'] == 'True'
batch_size = int(config['salesforce']['batch_size'])
loglevel = logging.getLevelName(config['salesforce']['loglevel'])
sharetype = logging.getLevelName(config['salesforce']['sharetype'])
visibility = logging.getLevelName(config['salesforce']['visibility'])
logging.basicConfig(format='%(asctime)s %(levelname)s %(message)s',
level=loglevel)
attachment_query = 'SELECT Id, ContentType, Description, Name, OwnerId, ParentId, CreatedById, CreatedDate, ' \
'LastModifiedDate FROM Attachment WHERE ParentId IN ({0})'.format(args.query)
notes_query = 'SELECT Id, Title, OwnerId, ParentId, CreatedById, CreatedDate, LastModifiedDate ' \
'FROM Note WHERE ParentId IN ({0})'.format(args.query)
output = config['salesforce']['output_dir']
attachment_query_string = "SELECT Id, ContentType, Description, Name, OwnerId, ParentId FROM Attachment"
note_query_string = "SELECT Id, Body, Title, OwnerId, ParentId FROM Note"
domain = None
if is_sandbox == 'True':
domain = 'test'
# Output
logging.info('Export Attachments from Salesforce')
logging.info('Username: '******'Output directory: ' + output)
# Connect
sf = Salesforce(username=username,
password=password,
security_token=token,
domain=domain)
logging.debug("Connected successfully to {0}".format(sf.sf_instance))
if attachment_query and download_attachments:
logging.info("Querying to get Attachment Ids...")
valid_record_ids = get_record_ids(sf=sf,
output_directory=output,
query=attachment_query,
object_type=ATTACHMENT)
logging.info("Found {0} total attachments".format(
len(valid_record_ids)))
fetch_files(sf=sf,
query_string=attachment_query_string,
valid_record_ids=valid_record_ids,
output_directory=output,
object_type=ATTACHMENT,
batch_size=batch_size)
if notes_query and download_notes:
logging.info("Querying to get Note Ids...")
valid_record_ids = get_record_ids(sf=sf,
output_directory=output,
query=notes_query,
object_type=NOTE,
sharetype=sharetype,
visibility=visibility)
logging.info("Found {0} total notes".format(len(valid_record_ids)))
fetch_files(sf=sf,
query_string=note_query_string,
valid_record_ids=valid_record_ids,
output_directory=output,
object_type=NOTE,
batch_size=batch_size)
class SalesForceImporter(models.Model):
_name = 'salesforce.connector'
sales_force = None
field_name = fields.Char('salesforce_connector')
# selection = fields.Selection([
# ('import_customers', 'Import Customers'),
# ('import_sale_orders', 'Import Sale Orders'),
# ("export_products", 'Export Product')
# ], string='Select Import/Export Option')
# history_id = fields.One2many('sync.history', 'order_id', copy=True)
history_line = fields.One2many('sync.history', 'sync_id', copy=True)
customers = fields.Boolean('Import Customers')
sales_orders = fields.Boolean('Import Sale Orders')
products = fields.Boolean('Export Products')
def sync_data(self):
"""
:return:
"""
if self.customers or self.products or self.sales_orders:
self.import_data()
else:
raise Warning(_("No Option Checked.", ))
# documents_link = self.env["sync.history"]
# documents_link.create({"no_of_products_sync": 2,
# "sync_id": 1})
# self.env.cr.commit()
def connect_to_salesforce(self):
"""
"""
try:
username = self.env.user.sf_username
password = self.env.user.sf_password
security_token = self.env.user.sf_security_token
self.sales_force = Salesforce(username=username,
password=password,
security_token=security_token)
except Exception as e:
Warning(_(str(e)))
def import_data(self):
"""
:return:
"""
success_message = "Customers Added: {} and {} updated.\n" \
"SalesOrders Added: {} and {} updated.\n" \
"Products Exported: {} and {} updated.\n"
data_dictionary = {}
# None if self.sales_force else self.connect_to_salesforce()
if self.sales_force is None:
raise Warning(
_("Kindly provide Salesforce credentails for odoo user", ))
if self.customers:
data_dictionary["customers"] = self.add_customers_from_sales_force(
)
if self.sales_orders:
data_dictionary["sales_orders"] = self.import_sale_orders()
if self.products:
data_dictionary["products"] = []
raise osv.except_osv(
_("Sync Details!"),
_(
success_message.format(len(data_dictionary["customers"]), 0,
len(data_dictionary["sales_orders"]), 0,
len(data_dictionary['products']), 0)),
)
def import_customers(self):
"""
:return:
"""
try:
return self.add_customers_from_sales_force()
except Exception as e:
raise Warning(_(str(e)))
def import_sale_orders(self):
"""
:return:
"""
try:
orders = self.sales_force.query(
"select id , AccountId,"
" EffectiveDate, orderNumber, status from Order")['records']
order_model = self.env["sale.order"]
order_name = [order.name for order in order_model.search([])]
order_data = []
for order in orders:
if order["OrderNumber"] in order_name:
continue
details = self.add_order_products_in_product_model(order["Id"])
customer = self.add_customers_from_sales_force(
order['AccountId'])[0]
temp_order = {
"name": order["OrderNumber"],
"partner_id": customer.id,
"state": "draft" if order['Status'] == 'Draft' else 'sale',
"invoice_status": "no",
"confirmation_date": order['EffectiveDate'],
"date_order": order['EffectiveDate']
}
order_data.append(temp_order)
sale_order = self.env["sale.order"].create(temp_order)
self.env.cr.commit()
for product_details, quantity in details:
self.env["sale.order.line"].create({
'product_uom':
1,
'product_id':
self.get_product_id(product_details.id),
'order_partner_id':
customer.id,
"order_id":
sale_order.id,
"product_uom_qty":
quantity
})
self.env.cr.commit()
return order_data
except Exception as e:
raise Warning(_(str(e)))
def add_order_products_in_product_model(self, order_id):
"""
:return:
"""
try:
order_products_data = []
order_lines = self.sales_force.query(
"select Pricebookentry.Product2Id , listprice, Quantity from "
"orderitem where orderid='%s'" % str(order_id))["records"]
product_model = self.env["product.template"]
old_products = product_model.search([])
old_products_default_code = [
product.default_code for product in old_products
]
for order_line in order_lines:
product_id = order_line["PricebookEntry"]["Product2Id"]
product_data = self.sales_force.query(
"select productCode, name, description from product2"
" where id='%s'" % str(product_id))["records"][0]
if product_data["ProductCode"] in old_products_default_code:
order_products_data.append((product_model.search([
('default_code', '=', product_data["ProductCode"])
]), order_line["Quantity"]))
continue
temp = dict()
temp["name"] = product_data["Name"]
temp["description"] = product_data["Description"]
temp["default_code"] = product_data["ProductCode"]
temp["list_price"] = order_line['ListPrice']
temp["invoice_policy"] = "delivery"
product_details = product_model.create(temp)
order_products_data.append(
(product_details, order_line["Quantity"]))
self.env.cr.commit()
return order_products_data
except Exception as e:
raise Warning(_(str(e)))
# def import_accounts(self):
# """
# Import Bank accounts from Sales force.
# """
# try:
# account_model = self.env['account.journal']
# old_accounts = account_model.search([])
# ac counts = self.sales_force.query("select id, accountnumber from account where accountnumber != ''")["records"]
# old_account_numbers = [account.name for account in old_accounts]
# for account in accounts:
# if account["AccountNumber"] in old_account_numbers:
# continue
# temp = dict()
# temp["name"] = account["AccountNumber"]
# temp["code"] = 'sale'
# temp["type"] = 'bank'
# account_model.create(temp)
# self.env.cr.commit()
# raise Warning(_("Accounts are imported from Salesforce"))
# except Exception as e:
# raise Warning(_(str(e)))
def get_product_id(self, tempalte_id):
"""
"""
product = self.env["product.product"].search([("product_tmpl_id", '=',
tempalte_id)])
return product.id
def add_customers_from_sales_force(self, customer_id=None):
"""
"""
query = "select id, name, shippingStreet, ShippingCity,Website, ShippingPostalCode, shippingCountry, fax, phone, Description from account %s"
extend_query = ''
customers_detail_list = []
if customer_id:
extend_query = "where id='%s'" % customer_id
contacts = self.sales_force.query(query % extend_query)["records"]
partner_model = self.env["res.partner"]
old_customers = partner_model.search([])
old_customers_name = [customer.name for customer in old_customers]
for customer in contacts:
if customer["Name"] in old_customers_name:
customers_detail_list.append(
partner_model.search([("name", "=", customer["Name"])]))
continue
customer_data = dict()
customer_data[
"name"] = customer["Name"] if customer["Name"] else ""
customer_data["street"] = customer["ShippingStreet"] if customer[
"ShippingStreet"] else ""
customer_data["city"] = customer["ShippingCity"] if customer[
"ShippingCity"] else ""
customer_data[
"phone"] = customer["Phone"] if customer["Phone"] else ""
customer_data["comment"] = customer['Description'] if customer[
'Description'] else ""
customer_data[
'website'] = customer["Website"] if customer["Website"] else ""
customer_data["fax"] = customer["Fax"] if customer["Fax"] else ""
customer_data["zip"] = customer["ShippingPostalCode"] if customer[
"ShippingPostalCode"] else ""
country = self.add_country(customer['ShippingCountry'])
customer_data["country_id"] = country[0].id if country else ''
customer_detail = partner_model.create(customer_data)
self.env.cr.commit()
self.add_child_customers(customer['Id'], customer_detail.id)
customers_detail_list.append(customer_detail)
self.env.cr.commit()
return customers_detail_list
@api.one
def add_country(self, country_name):
"""
:return:
"""
country_model = self.env["res.country"]
country = country_model.search([('name', 'ilike', country_name)],
limit=1)
return country
def add_child_customers(self, customer_id, parent_id):
"""
:return:
"""
query = "select name, mailingaddress, mailingpostalcode, mailingcountry, phone,email,fax,mobilephone," \
"description from Contact where accountid='%s'"
customers_detail_list = []
contacts = self.sales_force.query(query % customer_id)["records"]
partner_model = self.env["res.partner"]
old_customers = partner_model.search([])
old_customers_name = [customer.name for customer in old_customers]
for customer in contacts:
if customer["Name"] in old_customers_name:
continue
customer_data = dict()
customer_data[
"name"] = customer["Name"] if customer["Name"] else ""
customer_data["street"] = customer["MailingAddress"][
"street"] if customer["MailingAddress"] else ""
customer_data["city"] = customer["MailingAddress"][
"city"] if customer["MailingAddress"] else ""
customer_data[
"phone"] = customer["Phone"] if customer["Phone"] else ""
customer_data[
"email"] = customer["Email"] if customer["Email"] else ""
customer_data["fax"] = customer["Fax"] if customer["Fax"] else ""
customer_data["mobile"] = customer["MobilePhone"] if customer[
"MobilePhone"] else ""
customer_data["zip"] = customer["MailingPostalCode"] if customer[
"MailingPostalCode"] else ""
customer_data["parent_id"] = parent_id
customer_data["type"] = "invoice"
customer_data["comment"] = customer['Description'] if customer[
'Description'] else ""
country = self.add_country(customer['MailingCountry'])
customer_data["country_id"] = country[0].id if country else ''
customer_detail = partner_model.create(customer_data)
customers_detail_list.append(customer_detail)
self.env.cr.commit()
def export_products(self):
"""
"""
try:
products = self.get_products_not_in_salesforce()
product_data = [{
"Name":
product["name"],
"Description":
product["description"] if product["description"] else '',
"ProductCode":
product["default_code"] if product["default_code"] else '',
"IsActive":
True
} for product in products]
product_price = [product["list_price"] for product in products]
counter = 0
while 1:
buffer = product_data[counter * 200:(counter + 1) * 200]
price_buffer = product_price[counter * 200:(counter + 1) * 200]
price_book = []
if len(buffer) == 0:
break
product_buffer = self.sales_force.bulk.Product2.insert(buffer)
for product, price in zip(product_buffer, price_buffer):
if product["success"]:
price_book.append({
"Pricebook2Id":
self.get_standard_pricebook_id(),
"Product2Id":
product["id"],
"UnitPrice":
price,
"IsActive":
True
})
self.sales_force.bulk.PriceBookEntry.insert(price_book)
counter += 1
raise Warning(_("Products are exported to Salesforce from Odoo"))
except Exception as e:
raise Warning(_(str(e)))
def get_products_not_in_salesforce(self):
"""
:return:
"""
filtered_products = []
products = self.env["product.product"].search([])
old_products = self.sales_force.query(
"select name, productCode from product2")["records"]
p_filter = {
str(p["Name"]) + str(p["ProductCode"] if p["ProductCode"] else "")
for p in old_products
}
for product in products:
product_filter = str(product["name"]) + str(
product["default_code"] if product["default_code"] else "")
if product_filter not in p_filter:
filtered_products.append(product)
return filtered_products
def get_standard_pricebook_id(self):
"""
"""
pricebook_detail = self.sales_force.query(
"select id from pricebook2 where name = 'Standard Price Book'"
)["records"]
if pricebook_detail:
return pricebook_detail[0]["Id"]
pricebook_detail = self.sales_force.PriceBook2.create({
"name": 'Standard Price Book',
"IsActive": True
})
return pricebook_detail["id"]
def create_html_file(self, data_dictionary):
"""
import csv
from simple_salesforce import Salesforce
from salesforce_bulk import SalesforceBulk
from salesforce_bulk import CsvDictsAdapter
from datetime import date
from pathlib import Path
username = '******'
password = '******'
security_token = 'VJMrCkGcuEERf5thOsjZwPfIZ'
sf = Salesforce(username=username,
password=password,
security_token=security_token)
def get_records2CSV(namespace, sObject):
dir = "c:/kenandy/python/localCSV/"
object = namespace + sObject
print(object)
# datapath = Path(args.datadir) / date.today().isoformat()
# datapath = Path(dir) / date.today().isoformat()
datapath = dir + '/' + date.today().isoformat()
print(datapath)
sourceCSV = dir + sObject + '.csv'
print(sourceCSV)
salesforceObject = sf.__getattr__(object)
fieldNames = [
field['name'] for field in salesforceObject.describe()['fields']
]
import unittest
import os
import json
import conf
from simple_salesforce import Salesforce
SFDC_ACCT_ID_GOOGLE = '0010G00002CDZNZQA5'
SFDC_ACCT_NAME_GOOGLE = 'Google'
SFDC_OPP_ID_GOOGLE_AUSTIN = '0062I0000127wz4QAA'
SFDC_DEMAND_SIGNAL_ID_GOOGLE_SEATTLE = 'a3w2I0000006deYQAQ'
sfdc = Salesforce(username=conf.SFDC_USERNAME, password=conf.SFDC_PASSWORD, security_token=conf.SFDC_SECURITY_TOKEN)
def main():
all_demands = sfdc.query_all("SELECT Id, Geolocation__Latitude__s, Geolocation__Longitude__s, IsDeleted FROM Demand_Signal__c")
with open("data/sfdc_demand_signals_all.json", 'w') as fp:
json.dump(all_demands, fp, sort_keys=True, indent=4)
# all_demands = sfdc.query_all("SELECT Id, Geolocation__Latitude__s, Geolocation__Longitude__s FROM Demand_Signal__c WHERE IsDeleted = false")
# with open("data/sfdc_demand_signals.json", 'w') as fp:
# json.dump(all_demands, fp, sort_keys=True, indent=4)
# all_opps = sfdc.query_all("SELECT Id, AccountId FROM Opportunity WHERE IsDeleted = false")
# with open("data/sfdc_opportunities.json", 'w') as fp:
# json.dump(all_opps, fp, sort_keys=True, indent=4)
# all_accts = sfdc.query_all("SELECT Id, Name FROM Account WHERE IsDeleted = false")
# with open("data/sfdc_accounts.json", 'w') as fp:
# json.dump(all_accts, fp, sort_keys=True, indent=4)
def G2GWBMC():
try:
today = str(datetime.today())
subject = 'G2Gs are waiting! ' + today[:10]
msg = Message(subject, sender=('AD - Please Reply To All','*****@*****.**'), recipients=['*****@*****.**','*****@*****.**','*****@*****.**','*****@*****.**','*****@*****.**','*****@*****.**','*****@*****.**','*****@*****.**','*****@*****.**','*****@*****.**','*****@*****.**','*****@*****.**','*****@*****.**','*****@*****.**','*****@*****.**','*****@*****.**','*****@*****.**','*****@*****.**','*****@*****.**','*****@*****.**','*****@*****.**','*****@*****.**','*****@*****.**','*****@*****.**'])
c,conn = connection()
data = c.execute("select caseid, status, aptDate, info from cases where status = '1' and (aptDate != 'On Hold')")
backlog = 0
realBacklog = 0
realBacklog2d = 0
needconsumption2d = 0
realBacklog2dmore = 0
needconsumption2dmore = 0
nycBacklog = 0
pendingClose = 0
for row in c:
info = str(row[3])
au = 0
if info.find('AUS:') != -1:
au = info[info.find('AUS:')+4:info.find(';',info.find('AUS:'))]
if len(au) > 0 and row[2] != 'g2g-now':
realBacklog +=1
try:
CAD_date = str(row[2])
if CAD_date != 'g2g-now' and CAD_date != None:
month = int(CAD_date.split("/")[0])
day = int(CAD_date.split("/")[1].split("/",2)[0])
year = int('20'+CAD_date.split(str(day)+'/20')[1])
dateapt = datetime(year,month,day)
twod = datetime.now()+timedelta(days=3) > dateapt
onew = datetime.now()+timedelta(days=7) > dateapt
if twod:
realBacklog2d += 1
else:
realBacklog2dmore += 1
except Exception as e:
realBacklog2dmore += 1
else:
try:
CAD_date = str(row[2])
if CAD_date != 'g2g-now' and CAD_date != None:
month = int(CAD_date.split("/")[0])
day = int(CAD_date.split("/")[1].split("/",2)[0])
year = int('20'+CAD_date.split(str(day)+'/20')[1])
dateapt = datetime(year,month,day)
twod = datetime.now()+timedelta(days=3) > dateapt
onew = datetime.now()+timedelta(days=7) > dateapt
if twod:
needconsumption2d += 1
else:
needconsumption2dmore += 1
except Exception as e:
needconsumption2dmore += 1
if row[2] == 'g2g-now':
nycBacklog +=1
backlog +=1
data = c.execute("select caseid, status, aptDate, info from cases where (status = '2' or status = '1.5') and (aptDate != 'On Hold')")
for row in c:
pendingClose += 1
msg.html = '<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><style type="text/css" media="screen">table {border-collapse:collapse;width: 100%;}th, td {text-align:left;padding: 8px;}tr:nth-child(even){background-color: #f2f2f2}</style></head><body><table style="width:1200;border:1px solid black;margin-bottom:20px;padding-top:7px;padding-bottom:7px"><tr><th style="text-decoration:underline;width:12%;text-align:left">AD Backlog</th><th style="text-decoration:underline;width:22%;text-align:left">CAD In 2 Days</th><th style="text-decoration:underline;width:22%;text-align:left">CAD More Than 2 Days</th><th style="text-decoration:underline;width:22%;text-align:left">Design Pending To Be Closed</th><th style="text-decoration:underline;width:22%;text-align:left">G2G Finalization</th></tr>'
msg.html = msg.html + '<tr><td>#</td><td>' + str(realBacklog2d) + '</td><td>' + str(realBacklog2dmore) + '</td><td>' + str(pendingClose) + '</td><td>' + str(nycBacklog) + '</td></tr></table>'
sf = Salesforce(username='******', password='******', security_token='yWlJG8lAKCq1pTBkbBMSVcKg')
today = str(datetime.today())
response = sf.query_all("SELECT Id, StageName, Latest_CAD_Outcome__c FROM Opportunity WHERE (Latest_CAD_Outcome__c = 'goodtogo' OR Latest_CAD_Outcome__c = 'Good to Go') AND (StageName = 'Array Design' or StageName = 'Array Design Ready')")
OppowG2G = []
for item in response['records']:
try:
OppowG2G.append(item['Id'])
except:
pass
response = sf.query("SELECT Case.Contact.Accountnumber__c,Case.Opportunity__r.Id, Status FROM Case WHERE Record_Type_Bucket__c = 'design' AND (Status = 'Soft Close' OR Status = 'Draft') AND Case.Opportunity__r.Id in " + str(OppowG2G).replace("u'","'").replace('[','(').replace(']',')').replace(' ',''))
G2GSoftId = []
for item in response['records']:
G2GSoftId.append(item['Opportunity__r']['Id'])
response = sf.query_all("SELECT AccountNumber__c, Pre_BMC_Status_Issue__c, Pre_BMC__c.Opportunity__r.Id FROM Pre_BMC__c WHERE AccountNumber__c !=null AND Pre_BMC_Resolved__c = false AND Pre_BMC__c.Opportunity__r.Id in " + str(OppowG2G).replace("u'","'").replace('[','(').replace(']',')').replace(' ',''))
G2GwBMCId = []
for item in response['records']:
G2GwBMCId.append(item['Opportunity__r']['Id'])
G2GBMCBacklogID = list(set(G2GSoftId) & set(G2GwBMCId))
response = sf.query_all("SELECT AccountNumber__c, Pre_BMC_Status_Issue__c, Pre_BMC__c.Opportunity__r.Id, Pre_BMC__c.Opportunity__r.SalesRepE__c, Id, Pre_BMC__c.Contact__r.Name, Notes__c, Opportunity__r.SalesRepE__r.Name FROM Pre_BMC__c WHERE AccountNumber__c !=null AND Pre_BMC_Status_Issue__c != 'Meter is not grounded' AND Pre_BMC_Resolved__c = false AND Pre_BMC__c.Opportunity__r.Id in " + str(G2GBMCBacklogID).replace("u'","'").replace('[','(').replace(']',')').replace(' ',''))
msg.html = msg.html + '<table style="width:1200;border:1px solid black;margin-bottom:20px;padding-top:7px;padding-bottom:7px"><tr><th style="text-decoration:underline;width:12%;text-align:left">AD Waiting List</th><th style="text-decoration:underline;width:22%;text-align:left">CAD In 2 Days Need Consumption</th><th style="text-decoration:underline;width:44%;text-align:left">CAD More Than 2 Days Need Consumption</th><th style="text-decoration:underline;width:22%;text-align:left">G2G With Issues</th></tr>'
msg.html = msg.html + '<tr><td>#</td><td>' + str(needconsumption2d) + '</td><td>' + str(needconsumption2dmore) + '</td><td>' + str(len(response['records'])) + '</td></tr></table>'
# msg.html = msg.html + '<div>Current Size of Pre-BMC Backlog with G2G: ' + str(len(G2GwBMCId)) + '<br>'
g2gRespobsibleList = {'Michael Kaffka':0,
'Design Change':0,
'Array Design':0,
'Electrical':0,
'Ops':0}
for item in response['records']:
g2gRespobsibleList[designIssueSplit[item['Pre_BMC_Status_Issue__c']]] += 1
msg.html = msg.html + '<p>G2G With Issues Breakdown</p>'
msg.html = msg.html + '<table style="width:300px;border:1px solid black;margin-bottom:20px;padding-top:7px;padding-bottom:7px"><tr><th style="text-decoration:underline;width:80%;text-align:left">G2G With Issue</th><th style="text-decoration:underline;width:20%;text-align:left">#</th></tr>'
msg.html = msg.html + '<tr><td>Michael Kaffka Follow Up</td><td>' + str(g2gRespobsibleList['Michael Kaffka']) + '</td></tr>'
msg.html = msg.html + '<tr><td>CAD Coordinator Follow Up</td><td>' + str(g2gRespobsibleList['Design Change']) + '</td></tr>'
msg.html = msg.html + '<tr><td>Array Design Follow Up</td><td>' + str(g2gRespobsibleList['Array Design']) + '</td></tr>'
msg.html = msg.html + '<tr><td>Electrical Team Follow Up</td><td>' + str(g2gRespobsibleList['Electrical']) + '</td></tr>'
msg.html = msg.html + '<tr><td>Ops Team Follow Up</td><td>' + str(g2gRespobsibleList['Ops']) + '</td></tr></table>'
# msg.html = msg.html + '<table style="width:600;border:1px solid black;margin-bottom:20px;padding-top:7px;padding-bottom:7px"><tr><th style="text-decoration:underline;width:60%;text-align:left">AD Waiting List</th><th style="text-decoration:underline;width:40%;text-align:left">CAD In 2 Days Need Consumption</th></tr>'
msg.html = msg.html + '<p>Michael Kaffka:</p><table style="width:1200px;border:1px solid black;margin-bottom:20px;padding-top:7px;padding-bottom:7px"><tr><th style="text-decoration:underline;width:10%;text-align:left">SFDC</th><th style="text-decoration:underline;width:15%;text-align:left">Name</th><th style="text-decoration:underline;width:15%;text-align:left">Consultant</th><th style="text-decoration:underline;width:20%;text-align:left">Pre-BMC</th><th style="text-decoration:underline;width:40%;text-align:left">Pre-BMC Note</th></tr>'
for item in response['records']:
if designIssueSplit[item['Pre_BMC_Status_Issue__c']] == 'Michael Kaffka':
msg.html = msg.html + '<tr><td><a href="https://levelsolar.my.salesforce.com/'+item['Opportunity__r']['Id']+'" >' + item['AccountNumber__c'] + '</a></td><td>' + item['Contact__r']['Name'] + '</td><td>' + item['Opportunity__r']['SalesRepE__r']['Name'] + '</td><td><a href="https://levelsolar.my.salesforce.com/'+item['Id']+'" >' + item['Pre_BMC_Status_Issue__c'] + '</a></td>'
try:
msg.html = msg.html+'<td>'+item['Notes__c']+'</td></tr>'
except:
msg.html = msg.html+'<td>' +'</td></tr>'
msg.html = msg.html + '</table>'
msg.html = msg.html + '<p>CAD Coordinator:</p><table style="width:1200px;border:1px solid black;margin-bottom:20px;padding-top:7px;padding-bottom:7px"><tr><th style="text-decoration:underline;width:10%;text-align:left">SFDC</th><th style="text-decoration:underline;width:15%;text-align:left">Name</th><th style="text-decoration:underline;width:15%;text-align:left">Consultant</th><th style="text-decoration:underline;width:20%;text-align:left">Pre-BMC</th><th style="text-decoration:underline;width:40%;text-align:left">Pre-BMC Note</th></tr>'
for item in response['records']:
if designIssueSplit[item['Pre_BMC_Status_Issue__c']] == 'Design Change':
msg.html = msg.html + '<tr><td><a href="https://levelsolar.my.salesforce.com/'+item['Opportunity__r']['Id']+'" >' + item['AccountNumber__c'] + '</a></td><td>' + item['Contact__r']['Name'] + '</td><td>' + item['Opportunity__r']['SalesRepE__r']['Name'] + '</td><td><a href="https://levelsolar.my.salesforce.com/'+item['Id']+'" >' + item['Pre_BMC_Status_Issue__c'] + '</a></td>'
try:
msg.html = msg.html+'<td>'+item['Notes__c']+'</td></tr>'
except:
msg.html = msg.html+'<td>' +'</td></tr>'
msg.html = msg.html + '</table>'
msg.html = msg.html + '<p>Array Design:</p><table style="width:1200px;border:1px solid black;margin-bottom:20px;padding-top:7px;padding-bottom:7px"><tr><th style="text-decoration:underline;width:10%;text-align:left">SFDC</th><th style="text-decoration:underline;width:15%;text-align:left">Name</th><th style="text-decoration:underline;width:15%;text-align:left">Consultant</th><th style="text-decoration:underline;width:20%;text-align:left">Pre-BMC</th><th style="text-decoration:underline;width:40%;text-align:left">Pre-BMC Note</th></tr>'
for item in response['records']:
if designIssueSplit[item['Pre_BMC_Status_Issue__c']] == 'Array Design':
msg.html = msg.html + '<tr><td><a href="https://levelsolar.my.salesforce.com/'+item['Opportunity__r']['Id']+'" >' + item['AccountNumber__c'] + '</a></td><td>' + item['Contact__r']['Name'] + '</td><td>' + item['Opportunity__r']['SalesRepE__r']['Name'] + '</td><td><a href="https://levelsolar.my.salesforce.com/'+item['Id']+'" >' + item['Pre_BMC_Status_Issue__c'] + '</a></td>'
try:
msg.html = msg.html+'<td>'+item['Notes__c']+'</td></tr>'
except:
msg.html = msg.html+'<td>' +'</td></tr>'
msg.html = msg.html + '</table>'
msg.html = msg.html + '<p>Electrical Team:</p><table style="width:1200px;border:1px solid black;margin-bottom:20px;padding-top:7px;padding-bottom:7px"><tr><th style="text-decoration:underline;width:10%;text-align:left">SFDC</th><th style="text-decoration:underline;width:15%;text-align:left">Name</th><th style="text-decoration:underline;width:15%;text-align:left">Consultant</th><th style="text-decoration:underline;width:20%;text-align:left">Pre-BMC</th><th style="text-decoration:underline;width:40%;text-align:left">Pre-BMC Note</th></tr>'
for item in response['records']:
if designIssueSplit[item['Pre_BMC_Status_Issue__c']] == 'Electrical':
msg.html = msg.html + '<tr><td><a href="https://levelsolar.my.salesforce.com/'+item['Opportunity__r']['Id']+'" >' + item['AccountNumber__c'] + '</a></td><td>' + item['Contact__r']['Name'] + '</td><td>' + item['Opportunity__r']['SalesRepE__r']['Name'] + '</td><td><a href="https://levelsolar.my.salesforce.com/'+item['Id']+'" >' + item['Pre_BMC_Status_Issue__c'] + '</a></td>'
try:
msg.html = msg.html+'<td>'+item['Notes__c']+'</td></tr>'
except:
msg.html = msg.html+'<td>' +'</td></tr>'
msg.html = msg.html + '</table>'
msg.html = msg.html + '<p>Ops Team:</p><table style="width:1200px;border:1px solid black;margin-bottom:20px;padding-top:7px;padding-bottom:7px"><tr><th style="text-decoration:underline;width:10%;text-align:left">SFDC</th><th style="text-decoration:underline;width:15%;text-align:left">Name</th><th style="text-decoration:underline;width:15%;text-align:left">Consultant</th><th style="text-decoration:underline;width:20%;text-align:left">Pre-BMC</th><th style="text-decoration:underline;width:40%;text-align:left">Pre-BMC Note</th></tr>'
for item in response['records']:
if designIssueSplit[item['Pre_BMC_Status_Issue__c']] == 'Ops':
msg.html = msg.html + '<tr><td><a href="https://levelsolar.my.salesforce.com/'+item['Opportunity__r']['Id']+'" >' + item['AccountNumber__c'] + '</a></td><td>' + item['Contact__r']['Name'] + '</td><td>' + item['Opportunity__r']['SalesRepE__r']['Name'] + '</td><td><a href="https://levelsolar.my.salesforce.com/'+item['Id']+'" >' + item['Pre_BMC_Status_Issue__c'] + '</a></td>'
try:
msg.html = msg.html+'<td>'+item['Notes__c']+'</td></tr>'
except:
msg.html = msg.html+'<td>' +'</td></tr>'
msg.html = msg.html + '</table>'
msg.html = msg.html + '<h4>--------------------------------------------------</h4><p>Thank you!</p></body></html>'
mail.send(msg)
return 'good'
except Exception as e:
subject = 'G2G w BMC email didnot go out'
msg = Message(subject, sender='*****@*****.**', recipients=['*****@*****.**'])
msg.body = str(e)
mail.send(msg)
return str(e) + ' ' + str(item)
sf_url = parser.get('SalesForce', 'url')
sf_usr = parser.get('SalesForce', 'username')
sf_pwd = parser.get('SalesForce', 'password')
sf_tkn = parser.get('SalesForce', 'token')
slack_hook = parser.get('Slack', 'monitor_hook_url')
poll_rate = int(parser.get('misc', 'monitor_poll_minutes'))
monitor_group_name = parser.get('misc', 'monitor_group_name')
# Wait times in minutes for Sev 1, 2, 3 and 4
# respectively before notifying again
sev_wait = [5, 20, 40, 80]
ntickets = {}
# Opening session with SalesForce
sf = Salesforce(instance_url=sf_url, username=sf_usr, password=sf_pwd,
security_token=sf_tkn)
for group in sf.query("SELECT Id FROM Group WHERE Name = '%s'" %
monitor_group_name)['records']:
monitor_group_id = group['Id']
# A function for sending messages to Slack incoming hook
def slack_send(username, icon_emoji, text):
params = dumps({"username": username,
"icon_emoji": icon_emoji,
"text": text
})
conn = httplib.HTTPSConnection("hooks.slack.com")
conn.request("POST", slack_hook, params)
res = conn.getresponse()
