【HAVENT原创】Salesforce (JSforce) 调用示例

注意事项：

soql 每次查询最大只能获取 2000 条记录

 

下面是使用 JSforce 对 Salesforce 进行相关操作的代码示例：

            var soql = "SELECT Id, Name, OwnerId, AccountId, RecordTypeId, Email, Go_People_Runner_Id__c, Account.Id, Account.RecordTypeId";
            soql += " FROM Contact";

            jsforceConn.query(soql, function(err, result) {
                if (err) {
                    console.error(err);
                    return logger.error(err);
                }


                console.log("total : " + result.totalSize);
                console.log("fetched : " + result.records.length);　　// 每次最大2000条记录

                console.log("API Limit: " + jsforceConn.limitInfo.apiUsage.limit);
                console.log("API Used: " + jsforceConn.limitInfo.apiUsage.used);
            });

 

var records = [];
conn.query("SELECT Id, Name FROM Account", function(err, result) {
  if (err) { return console.error(err); }
  console.log("total : " + result.totalSize);
  console.log("fetched : " + result.records.length);
  console.log("done ? : " + result.done);
  if (!result.done) {
    // you can use the locator to fetch next records set.
    // Connection#queryMore()
    console.log("next records URL : " + result.nextRecordsUrl);
  }
});

var records = [];
var query = conn.query("SELECT Id, Name FROM Account")
  .on("record", function(record) {
    records.push(record);
  })
  .on("end", function() {
    console.log("total in database : " + query.totalSize);
    console.log("total fetched : " + query.totalFetched);
  })
  .on("error", function(err) {
    console.error(err);
  })
  .run({ autoFetch : true, maxFetch : 4000 }); 

//
// Following query is equivalent to this SOQL
//
// "SELECT Id, Name, CreatedDate FROM Contact
//  WHERE LastName LIKE 'A%' AND CreatedDate >= YESTERDAY AND Account.Name = 'Sony, Inc.'
//  ORDER BY CreatedDate DESC, Name ASC
//  LIMIT 5 OFFSET 10"
//
conn.sobject("Contact")
  .find(
    // conditions in JSON object
    { LastName : { $like : 'A%' },
      CreatedDate: { $gte : jsforce.Date.YESTERDAY },
      'Account.Name' : 'Sony, Inc.' },
    // fields in JSON object
    { Id: 1,
      Name: 1,
      CreatedDate: 1 }
  )
  .sort({ CreatedDate: -1, Name : 1 })
  .limit(5)
  .skip(10)
  .execute(function(err, records) {
    if (err) { return console.error(err); }
    console.log("fetched : " + records.length);
  });

conn.sobject("Contact")
  .select('*, Account.*') // asterisk means all fields in specified level are targeted.
  .where("CreatedDate = TODAY") // conditions in raw SOQL where clause.
  .limit(10)
  .offset(20) // synonym of "skip"
  .execute(function(err, records) {
    for (var i=0; i<records.length; i++) {
      var record = records[i];
      console.log("First Name: " + record.FirstName);
      console.log("Last Name: " + record.LastName);
      // fields in Account relationship are fetched
      console.log("Account Name: " + record.Account.Name); 
    }
  });

//
// Following query is equivalent to this SOQL
//
// "SELECT Id, FirstName, LastName, ..., 
//         Account.Id, Acount.Name, ...,
//         (SELECT Id, Subject, … FROM Cases
//          WHERE Status = 'New' AND OwnerId = :conn.userInfo.id
//          ORDER BY CreatedDate DESC)
//  FROM Contact
//  WHERE CreatedDate = TODAY
//  LIMIT 10 OFFSET 20"
//
conn.sobject("Contact")
  .select('*, Account.*')
  .include("Cases") // include child relationship records in query result. 
     // after include() call, entering into the context of child query.
     .select("*")
     .where({
        Status: 'New',
        OwnerId : conn.userInfo.id,
     })
     .orderby("CreatedDate", "DESC")
     .end() // be sure to call end() to exit child query context
  .where("CreatedDate = TODAY")
  .limit(10)
  .offset(20)
  .execute(function(err, records) {
    if (err) { return console.error(err); }
    console.log('records length = ' + records.length);
    for (var i=0; i<records.length; i++) {
      var record = records[i];
      console.log("First Name: " + record.FirstName);
      console.log("Last Name: " + record.LastName);
      // fields in Account relationship are fetched
      console.log("Account Name: " + record.Account.Name); 
      // 
      if (record.Cases) {
        console.log("Cases total: " + record.Cases.totalSize);
        console.log("Cases fetched: " + record.Cases.records.length);
      }
    }
  });

 

 

从 Salesforce 数据库中下载 accounts 数据同步到本地数据库：

var config = require('../config.json');
var utility = require('./utility');
var moment = require('moment');

var logger = null;
var pool = null;
var jsforceConn = null;

var getSenderAccounts = function(callback){
    var soql = "SELECT Id, Name, Phone, Email__c";
    soql += " FROM Account";
    soql += " WHERE Account_Type__c = 'Customer'";

    jsforceConn.query(soql, function(err, result) {
        if (err) {
            return logger.error(err);
        }

        if(callback) {
            callback(err, result);
        }
    });
};

function releaseConnection(senders, index, connection) {
    if(senders.length - 1 == index){
        connection.release();
    }
}

function downloadSender() {
    getSenderAccounts(function(err, result){
        if(!result.done){
            logger.error('Accounts fetching failed');
        }

        pool.getConnection(function (err, connection) {
            if(err){
                logger.error(err);
            }
            var senders = result.records;

            senders.forEach(function(sender, index){
                var salesforceId = sender.Id;
                var email = sender.Email__c;
                var phone = sender.Phone;

                if(email != '' && email != null){
                    connection.query('SELECT * FROM user WHERE email = ? AND is_deleted = 0 AND role = 1', [email], function (err, rows) {
                        if(err){
                            logger.error(err);
                        }

                        if(rows.length == 0){
                            logger.error('Sender ' + sender.Name + ' is not found');

                            releaseConnection(senders, index, connection);
                        }
                        else{
                            if(rows[0].salesforce_account_id != '' && rows[0].salesforce_account_id != null){
                                releaseConnection(senders, index, connection);
                            }
                            else{
                                connection.query('UPDATE user SET salesforce_account_id = ? WHERE email = ? AND is_deleted = 0', [salesforceId, email], function (err, rows) {
                                    if(err){
                                        logger.error(err);
                                    }
                                    logger.info('Sender ' + sender.Name + ' has been synced');

                                    releaseConnection(senders, index, connection);
                                });
                            }
                        }
                    });
                }
                else if(phone != '' && phone != null){
                    phone = phone.replace(/[^A-Z0-9]/ig, "");
                    connection.query('SELECT * FROM user WHERE mobile = ? AND is_deleted = 0 AND role = 1', [phone], function (err, rows) {
                        if(err){
                            logger.error(err);
                        }

                        if(rows.length == 0){
                            logger.error('Sender ' + sender.Name + ' is not found');

                            releaseConnection(senders, index, connection);
                        }
                        else{
                            if(rows[0].salesforce_account_id != '' && rows[0].salesforce_account_id != null){
                                releaseConnection(senders, index, connection);
                            }
                            else{
                                connection.query('UPDATE user SET salesforce_account_id = ? WHERE mobile = ? AND is_deleted = 0', [salesforceId, phone], function (err, rows) {
                                    if(err){
                                        logger.error(err);
                                    }
                                    logger.info('Sender ' + sender.Name + ' has been synced');

                                    releaseConnection(senders, index, connection);
                                });
                            }
                        }
                    });
                }
                else{
                    logger.error('Sender ' + sender.Name + ' is Not found');
                    releaseConnection(senders, index, connection);
                }
            });
        });
    })
}

module.exports = {
    downloadSender: function (l, p, j) {
        logger = l;
        pool = p;
        jsforceConn = j;
        return downloadSender();
    }
}