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

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 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 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 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 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 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 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 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 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 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 statuscheck():
    global done
    uname = input(str("Enter Username: "))
    pword = input(str("Enter password: "))
    key = input(str("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.")