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()