SugarCon presentation and webcasts: Why SugarCRM is a better choice compared to Salesforce.com

Today I am very excited to announce that I will present my last deck "CRM Made Simple: Why SugarCRM is a better choice compared to Salesforce.com" at SugarCon and will make three webcasts. 

Webcast description
In today's competition every company needs a CRM to grow and secure its business. But comparing CRM vendors solutions is a tough nut to crack if you are not a CRM expert. This webcast will help you understand why SugarCRM is a better choice than Salesforce. If you are choosing your first CRM solution, thinking about making a swap or just curious, you should attend this presentation.

SugarCon
http://sugarcon.sugarcrm.com/program/session-schedule
Date TBC. Tue 4/24 or Wed 4/25, during "Partner traning: Sales 101" (1:30-5:30pm)

Webcast Emea - in English
Date & Time:
Wed, May 9, 2012
6 am PT / 14:00 GMT / 15:00 CET
Duration: 1 hr
Register for this webcast  

Webcast Emea - in French
Date & Time:
Fri, May 11 2012
10:00 CET
Duration: 1 hr
Register for this webcast

Webcast North America - in English
Date & Time:
Wed, May 16 2012
9 am PT / 12 pm ET
Duration: 1 hr
Register for this webcast


SugarCon is going to be an amazing event! If you are attending SugarCon, please download the SugarCon app (iOS or Android devices - key word search SugarCon). The app will help you to build your agenda and brings a lot of nice social features. Check it out! And feel free to add me in your friends list - and win cool badges ;-)

TortugaCRM is now sailing to the turquoise waters of SugarCRM!

Yo ho a pirate's life for me! The blog TortugaCRM is now sailing to the turquoise waters of SugarCRM.

Let me tell you a story. When I started this blog one year ago, the name was [the no logo Software vendor]-expresso. Everybody in the team enjoyed the blog, except one Emea VP Marketing (who left the company in the meantime), he told me: "Olivier, we encourage employees running their own blog, but you are not allowed using the logo neither the name. Change this asap!". I was at a loss. If I were a partner, the same guy would have suggested raising me at the rank of MVP.
Then the blog name became TortugaCRM, a place of free speech where you can talk about any CRM  :-)
So I started learning SugarCRM and was so convinced by the product that I decided joining the company!

Now I am proud to announce that I am a pre-Sales Engineer and Evangelist working at SugarCRM.

I will present a competitive session at our next coming event SugarCon in April during the partner day. Then, I will make several webcasts for the North America and Emea regions in English and French as well.
The topic of this presentation will be brought to light in a next post...

Please join us at SugarCon if you can make it, you will meet our amazing speakers:
Guy Kawasaki (former Apple Chief Evangelist @GuyKawasaki), Paul Greenberg (author of the best seller "CRM at the speed of light" @pgreenbe), Dr. Natalie Petouhoff (Chief Strategist at Weber Shandwick @drnatalie) and many others.

Click on the banner to get to SugarCon web site:

[Salesforce/SugarCRM] Live data synchronization between Salesforce.com and SugarCRM

Introduction

This post will present how to set up a master/slave synchronization between two great CRM: Salesforce and SugarCRM.
I have been working at Salesforce.com as a Technical Architect the last three years, so I have a heavy practice of Salesforce but I am a SugarCRM newbie: coding in Sugar is a brand new experience. My code is probably not 100% best practices so feel free to share your advices in the post comments.
Disclaimer: the provided code samples are straight forward; they are not dedicated to run in a production environment. You might want to pick up some parts but use it at your own risks.
The code is available under the GPL license 3.0.

The scenario

The Salesforce organization is the master data. Each time an account record is created or updated, a REST call is sent to the SugarCRM instance. SugarCRM will create a new record or update an existing one, based on the Salesforce record Id.
This is a one way synchronization, the aim of this post is to keep it simple and providing clear code samples.

Salesforce side

Salesforce Developer Edition

I am working with a Salesforce Developer Edition (DE). This a free edition that brings with the same features as the Unlimited Edition. The main limitations are: 2 crm user licenses and the data storage (up to 5Mb). Sign-up for a DE.

The Apex trigger

An Apex trigger is fired each time a record is created or modified (on the after update event). This process will run when using the HCI (Salesforce in the web browser) and also with the Salesforce API (using the data loader e.g.). So we need to bulkify the code. This is a best practice that will let your trigger process 1 record (HCI) or 200 records as well (data loader, 3rd party tools using the API).
The Apex trigger will call an asynchronous method that will send the REST message to SugarCRM. As it is asynchronous, the end user is not disturbed by the synchronization process as it ran behind the scene.
The trigger code:

trigger AccountAfterUpdate on Account (after update) {
    Set AccountIds=new Set();
    for(SObject a : Trigger.New) AccountIds.Add(a.ID);
    SugarCRM.AccountUpsert(AccountIds);
}

Remark: the id double tags at the end of the code sample is a Syntax Highlighter bug.

Why REST rather than SOAP?
I highly recommend using REST rather than SOAP web services:
- it is quicker, easier, a lower maintenance (no need to import a new wsdl at each release)
- Salesforce meets some limitations when importing wsdl files. E.g, importing an external schema is not supported and provide the following error:
Error: Failed to parse wsdl: Found schema import from location http://schemas.xmlsoap.org/soap/encoding/. External schema import not supported

The Apex Class

Login method
Logs in SugarCRM using the user name and a MD5 hash of the password. The previous post is explaining how to generate the MD5 value.
AccountUpsert method
Sends the data. The method has the @future(call=true) annotation. It means that the execution is asynchronous, it does not impact the governor limits of the current transaction.
Objects cannot be passed as parameters, only primitives can: that is why I am using a set of record Ids.
The REST call is straight forward, the Json is hardcoded. If you want to improve the code and get something smooth, I recommend to built on the fly the Json syntax based on a structure declaration. Read this interesting post: http://www.tgerm.com/2011/10/winter12-jsonparser-serialize.html

The end point:
I recommend to declare the end point as a System Label. My demo is working on a virtual machine that changes its IP address and name at every reboot.
Setup | Administration Setup | Security Controls | Remote Site Settings

Apex Class code:

public class SugarCRM {

    private final static String endpointUrl = 'http://mc-178-20-142-224.ovh.net';

    @future (callout=true)
    public static void AccountUpsert(Set AccountIds) {
        String SessionId = login('admin','1e87fae4d9133d53124fedcfeca02adc');
        CallAccountUpsert(SessionId, AccountIds);
    }
    
    public static String login(String user, String password) {
        String sessionId='';
        Http h = new Http();
        HttpRequest req = new HttpRequest();
        String url=endpointUrl+'/sugar/service/v2/rest.php?';
        url=url+'method=login&input_type=json&response_type=json&rest_data={"user_auth":{"user_name":"'+
            EncodingUtil.urlEncode(user,'UTF-8')+'","password":"'+EncodingUtil.urlEncode(password,'UTF-8')+
            '","version":"0.1"},"application_name":""}';
        req.setEndpoint(url);
        req.setMethod('GET');
        // Send the request, and return a response
        HttpResponse res = h.send(req);
        String Body = res.getBody();
        //System.Debug('##Body='+Body);
        JSONParser parser = JSON.createParser(Body);
        while (parser.nextToken() != null) {
            //System.Debug('##getCurrentToken()='+parser.getCurrentToken()+',parser.getText()='+parser.getText());
            if ((parser.getCurrentToken() == JSONToken.FIELD_NAME) && (parser.getText() == 'id')) {
                parser.nextToken();
                sessionId = parser.gettext();
                break;
            }
        }
        System.Debug('##sessionId='+sessionId);
        return (sessionId);        
    }
    
    public static String CallAccountUpsert(String SessionId, Set AccountIds) {
        Http h = new Http();
        HttpRequest req = new HttpRequest();
        String url=endpointUrl+'/sugar/service/v2/rest.php?';
        String url1='method=set_entries&input_type=json&response_type=json&rest_data='+
          '{"session":"'+SessionId+'","module_name":"Accounts","name_value_lists":[';
        for(Account a : [Select Id, Name, Phone From Account Where Id IN :AccountIds]) {
            url1+='[{"name":"assigned_user_id","value":"1"},{"name":"name","value":"'+EncodingUtil.urlEncode(a.Name,'UTF-8')+'"},'+
              '{"name":"phone_office","value":"'+EncodingUtil.urlEncode(a.Phone,'UTF-8')+'"},'+
              '{"name":"salesforceid_c","value":"'+a.Id+'"}],';
        }
        url1=url1.replaceAll(',$','');
        url1+=']}';
        //url1=EncodingUtil.urlEncode(url1,'UTF-8');
        url+=url1;
        System.Debug('##url='+url);
        req.setEndpoint(url);
        req.setMethod('GET');
        // Send the request, and return a response
        HttpResponse res = h.send(req);
        String Body = res.getBody();
        System.Debug('##Body='+Body);
        return Body;
    }
}

What is an upsert?

Let 's start with the Wikipedia definition: http://en.wikipedia.org/wiki/Upsert.
<< The term "Upsert" refers to any database statement, or combination of statements, that inserts a record to a table in a database if the record does not exist or, if the record already exists, updates the existing record. The term upsert is a portmanteau of update and insert and is common slang among database developers. >>
The upsert is a common DML statement Salesforce. I did not find the equivalent in SugarCRM and coded my own Upsert behavior.

The test methods

Before deploying your code to the production environment, you must write some test methods and get a minimum code coverage of 75%. We will not write the test methods as they are not needed in a DE environment.

SugarCRM

Assumptions

You have a SugarCRM installation reachable by Salesforce. I deployed the open source version of SugarCRM: the community edition version 6.4.0 running on a virtual Ubuntu 10 64 bits hosted by OVH Cloud. I will not detail the installation walk through in this post in order to keep focus on the code.

change the data model

The Salesforce Account Id will match a foreign key called salesforce_id.
Create the foreign key
Go to Admin | Developer Tools | Studio. Click on the Accounts Icon.

 

 Click on the "Fields" icon, then the "Add Fields" button. Create a string field called salesforceid. The name will be salesforceid_c. Set the length to 18.

 Then click on the layouts icons. Edit the "View" page layout. Add a section that contains the new custom field. Do not add the field to the "Edit" page layout because we want only Salesforce to be able to change the value.

SugarCRM beginner Best practices

use the sugar logs to trace the execution. Go to Admin | System Settings | View Log. Set the log level to "Debug". This spot is really useful when it comes to trace all the application behavior, especially when you are dealing with hooks.
Use a MySQL Gui or command line client, I am using "emma" (for Extendable Mysql Managing Assistant) on Ubuntu, a fancy editor.

Add a Hook

A hook is a piece of code that will be triggered during an event.It may be compared as a data base trigger or a Salesforce trigger. More on SugarCRM events in the developer documentation.
As the upsert action does not exist in SugarCRM, I coded my own. This is my first hook and I had some hard time to dig the web, read the docs, parse the code, check the data base and make my own stuff. All critics are welcome.
I set the log level to "debug" to highlight the unexpected behaviors: Sugar was executing the hook several times (because I am saving a new account record inside the hook). To prevent multiple executions, I added a semaphore behavior inside the hook class: an array that contains the ID of the recorded already processed (cf private static array $canTrigger)

First of all, you must declare the event:
$SUGARHOME/custom/modules/Accounts/logic_hooks.php

// Do not store anything in this file that is not part of the array or the hook version.  This file will 
// be automatically rebuilt in the future. 
$hook_version = 1; 
$hook_array = Array(); 
// position, file, function 
$hook_array['after_save'] = Array(); 
$hook_array['after_save'][] = Array(1, 'Account From SFDC', 'custom/modules/Accounts/AccountFromSFDC-AfterUpdate.php','AccountFromSFDC', 'AccountUpsert'); 

Then, the Hook:
I am tracking when entering/exiting the class with the logs. The semaphore prevents the save method from re-executing the hook code. I do not know if this is a best practice as I was not able to find on Google a sample of code to perform an Upsert.
$SUGARHOME/custom/modules/Accounts/AccountFromSFDC-AfterUpdate.php

//AccountsTestHook.php
//AccountsTestHook.php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
class AccountFromSFDC{

 static private $canTrigger = array();

 function AccountUpsert(&$bean, $event, $arguments) {
  $GLOBALS['log']->warn("######## AccountFromSFDC-AfterUpdate.AccountFromSFDC - START");
  $GLOBALS['log']->warn("#### self::\$canTrigger=".implode(',',self::$canTrigger));
  if (array_search($bean->id,self::$canTrigger)===false) {
   $GLOBALS['log']->warn("##### continue: entering in the hook Account After Save");
   array_push(self::$canTrigger, $bean->id);
   if (empty($bean->fetched_row['id']) && ($bean->salesforceid_c!='')) { // on after insert
    $query = "select id_c from accounts_cstm where salesforceid_c='".$bean->salesforceid_c."' and id_c!='".$bean->id."' limit 1";
    $GLOBALS['log']->warn("##### query=$query");
    $res=$bean->db->query($query);
    $row=$bean->db->fetchByAssoc($res); 
    $GLOBALS['log']->warn("##### count(\$row)=".count($row).', $row[\'id_c\']='.$row['id_c']);
    if ($row['id_c'] != '') {// foreign key match
     $GLOBALS['log']->warn("##### \$bean->id=".$bean->id);
     $b = clone $bean;
     $b->id = $row['id_c'];
     array_push(self::$canTrigger, $b->id);
     $result = $bean->db->query("delete from accounts where id='".$bean->id."'");
     $result = $bean->db->query("delete from accounts_cstm where id_c='".$bean->id."'");
     $b->save();
     
     /* this process should only happen through the API as the salesforceid_c is not on the Edit page layout
      * otherwise you should redirect to the new page:
              $site = $sugar_config['site_url'];
     $GLOBALS['log']->warn("##### {$site}/index.php?module=Accounts&action=DetailView&record={$b->id}");
     $GLOBALS['log']->warn("######## AccountFromSFDC-AfterUpdate.AccountFromSFDC - END (return)");
     SugarApplication::redirect('index.php?module=Accounts&action=DetailView&record='.$b->id);
              */
    }
   }
  } 
  else {
   $GLOBALS['log']->warn("######## exit: do not enter in the hook Account After Save");
   return;
  }

 }
}

The home made upsert process:

Entering condition: new record ($bean->fetched_row['id'] is empty), but a new record had been created.
Case 1: no Salesforceid_c. Do nothing.
Case 2: the record does not match a Salesforceid_c. Do nothing.
Case 3: the record matches with a Salesforceid_c: delete the previous record in the data base, set the previous record id to the new record.

Synchronization: mapping

Salesforce	SugarCRM
Id	Salesforceid_c
Name	Name
Phone	Phone_office

Now let's test!

To improve your tests, I recommend starting by executing Anonymous Apex code, and following the results using the "System Logs" or the "Debug Logs". The "Debug Logs" will trace all the code execution, including the @Future call (asynchronous call).

Salesforce: with the browser

Update 1 account
Salesforce: go to the Account tab, choose an account. Make any update you like on the account in order to fire the trigger.

Then go to Sugar, make a search filtered on the account name, woohoo, it works!

Update again the account
In Salesforce, change the Phone value and click on the save button:

In Sugar, refresh the detail page of the account:

Salesforce: bulk load

The bulk load is a common behavior when using the API: instead of pushing the records 1 by 1, we are pushing them by 200. This makes the overall process quicker as all the user validation rules, workflow, triggers, assignation, case escalation, roll up summary fields are updated during the load.
Better than providing a ton of data loader screenshots, I will make the demonstration using a an Apex anonymous code that will send two records in the bulk.

anonymous Apex:

upserting 2 existing Accounts.

Set AccountIds=new Set();
for(Account a : [Select ID From account Where ID IN('0018000000US5Jk','0018000000sjhVI')]) AccountIds.Add(a.ID);
SugarCRM.AccountUpsert(AccountIds);

The two records with those Ids are sent to Sugar. Here is the REST message (from the Salesforce debug logs):

04:06:38.780 (780110000)|CALLOUT_REQUEST|[57]|System.HttpRequest[Endpoint=http://mc-178-20-142-224.ovh.net/sugar/service/v2/rest.php?method=set_entries&input_type=json&response_type=json&rest_data={"session":"64c916f1b6e369543d40b0b39a3d0b18","module_name":"Accounts","name_value_lists":[[{"name":"assigned_user_id","value":"1"},{"name":"name","value":"United+Oil+%26+Gas%2C+UK"},{"name":"phone_office","value":"%2B44+191+4956300"},{"name":"salesforceid_c","value":"0018000000US5JkAAL"}],[{"name":"assigned_user_id","value":"1"},{"name":"name","value":"United+Oil%2C+Manchester"},{"name":"phone_office","value":"01020304066"},{"name":"salesforceid_c","value":"0018000000sjhVIAAY"}]]}, Method=GET]

And Sugar's answer:

04:06:39.128 (1128089000)|USER_DEBUG|[59]|DEBUG|##Body={"ids":["5d122a1a-1a6f-fa15-7f32-4ecf0626cc95","617f4722-7121-2719-523b-4ecf068caf6d"]}

The End

I hope you enjoyed this post, please feel free to share your views in the post comments.

[SugarCRM] SOAP and REST calls using PHP

Today I would like to share with you a recipe to connect two great CRM: Salesforce and SugarCRM. As you might know, I am self training on SugarCRM. However, it is pretty hard to find a straight forward tutorial with good samples of code. So I decided to write my own demo. This post will present two ways to create an account record in SugarCRM:
- with a web service (soap)
- with a REST call

Requirements

I am using the open source version of SugarCRM: the community edition version 6.4.0 running on my Ubuntu 10 laptop.

Versions
Apache: 2.2.21
PHP: 5.3.8
PHP Configure file:

./configure --with-apxs2=/usr/local/apache2/bin/apxs --enable-ftp --enable-bcmath --enable-calendar --with-jpeg-dir --with-png-dir --with-gd --enable-gd-native-ttf --with-freetype-dir --with-gettext --with-mysql --with-zlib-dir --with-ldap --with-openssl --enable-mbstring --enable-exif --enable-soap --enable-zip --with-curl

I am doing my tests on a local instance, the domain name is: sugarcrm1.ubuntu1, so the wdsl is reachable at: http://sugarcrm1.ubuntu1/soap.php?wsdl

Use case

I want to create this account:
- name: "Account Test 003"
- phone: "0102031003"

You need to get the MD5 hash of your SugarCRM password. Run from the command line:

php -r "echo md5('yourPassword').\"\\n\";"
ff85305ab86ceff0f59877358928d81d

SOAP

Here is a sample of code to:
- log in SugarCRM
- create an account record

$client = new SoapClient("http://sugarcrm1.ubuntu1/soap.php?wsdl", array("trace" => 1, "exception" => 0)); 

// LOGIN
$response = $client->__soapCall("login", 
  array(
    "user_auth" =>
     array(
  'user_name' => "admin",
  'password' => "0192023a7bbd73220a16f06cdf18b532",
  'version' => "0.1"
  ),
 "application_name" => ''
  )
);
$session_id = $response->id;
echo "session_id=$session_id\n";

// CREATE ACCOUNT
$response = $client->set_entry($session_id, 'Accounts', array(
 array('name' => 'name', 'value' => "Account Test 003"), 
 array('name' => 'phone', 'value' => "0102031003")
));
$account_id = $response->id;
echo "account_id=$account_id\n";

The "trace" parameter when creating the SoapClient instance let you play with the PHP Soap debug trace. Try to add these lines at the end of the code:

echo "LastRequest\n";
echo $client->__getLastRequest();
echo "LastResponse\n";
echo $client->__getLastResponse();

LastRequest:

cbbd39b6cb2774bc3db950ea095aa900
Accounts


name
Account Test 003


phone
0102031003

LastResponse:

5d0c048b-fda9-73c5-bb1b-4ecade8bd41a

0
No Error
No Error

REST

Here is a sample of code to:
- log in SugarCRM
- create an account record

// LOGIN
$url = 'http://sugarcrm1.ubuntu1/service/v2/rest.php'; 
$curl = curl_init($url); 
curl_setopt($curl, CURLOPT_POST, true); 
curl_setopt($curl, CURLOPT_HEADER, false); 
curl_setopt($curl, CURLOPT_RETURNTRANSFER, true); 
$parameters = 
  array(
 "user_auth" =>
  array(
  'user_name' => "admin",
  'password' => "0192023a7bbd73220a16f06cdf18b532",
  'version' => "0.1"
  ),
 "application_name" => ''
  );
$json = json_encode($parameters); 
$postArgs = 'method=login&input_type=json&response_type=json&rest_data=' . $json; 
echo "postArgs=$postArgs\n";
curl_setopt($curl, CURLOPT_POSTFIELDS, $postArgs); 
$response = curl_exec($curl); 
$result = json_decode($response); 
if(!is_object($result)) { die("Connection error\n"); } 
$sessionid = $result->id;
$userid=$result->name_value_list->user_id->value;

// CREATE ACCOUNT
$parameters = array(
 'session' => $sessionid,
 'module_name' => 'Accounts',
 'name_value_list' => array(
  array('name' => 'assigned_user_id', 'value' => $userid), 
  array('name' => 'name', 'value' => "Account Test 005"), 
  array('name' => 'phone', 'value' => "0102031005")
 )
);
$json = json_encode($parameters); 
$postArgs = 'method=set_entry&input_type=json&response_type=json&rest_data=' . $json; 
echo "postArgs=$postArgs\n";
curl_setopt($curl, CURLOPT_POSTFIELDS, $postArgs); 
$response = curl_exec($curl); 
$result = json_decode($response); 
print_r($result);
curl_close($curl);  

Here is the parameters sent:

method=set_entry&input_type=json&response_type=json&rest_data={"session":"3f339d60c1050ed57e44976a135e156a","module_name":"Accounts","name_value_list":[{"name":"assigned_user_id","value":"1"},{"name":"name","value":"Account Test 005"},{"name":"phone","value":"0102031005"}]}

And the result (deserialized):

stdClass Object
(
    [id] => bb7fd58d-fd85-045f-c7a2-4ecad4d4aba2
)

In the next post I will show an easy way to synchronize data between Salesforce and SugarCRM!

[Salesforce] The one minute Salesforce Workbook!

Many times during my assignments, customers and partners ask: "Olivier, we need a workbook, but it will require at least 2 days to build one manually because we have so many objects. Could you help?". So I wrote this very small piece of PHP code that automatically builds an Excel workbook. And today, I am sharing it with you! Under the License GPL v2

Requirements

First of all, you need PHPExel: http://phpexcel.codeplex.com/

Then, you need the Salesforce.com PHP Toolkit: http://wiki.developerforce.com/index.php/Web_Services_API#PHP
Download the partner wsdl from your org, go to:
Setup | App Setup | Develop | API > Partner WSDL > Generate Partner WSDL
Do not forget that the partner wsdl can be shared with any org, but the endpoint is different in sandbox and in the production.

And last, check that you have a PHP environment to execute the script from the command line.
Here is a sample of a PHP configure (the step before the make):

./configure --with-apxs2=/usr/local/apache2/bin/apxs --enable-ftp --enable-bcmath --enable-calendar --with-jpeg-dir --with-png-dir --with-gd --enable-gd-native-ttf --with-freetype-dir --with-gettext --with-mysql --with-zlib-dir --with-ldap --with-openssl --enable-mbstring --enable-exif --enable-soap --enable-zip

If your execution environment is Windows, make sure that the correct DLLs are selected in your ini file.

The file system

Create a directory that will host the code.
Unzip PHPExcel in a folder called PHPExcel.
Unzip in the folder called phptoolkit the PHP Tool kit.
Copy the wdsl to phptoolkit/soapclient
Copy/paste the PHP source to a file called workbook.php

Your directory should look like (3 first levels, dir only):

olivier@Ubuntu1:~/SFDC/blog/Tortuga-crm/Post_workbook$ tree -d -L 3
.
|-- PHPExcel
|   |-- Classes
|   |   `-- PHPExcel
|   |-- Documentation
|   |   |-- API
|   |   `-- Examples
|   `-- Tests
|       |-- images
|       `-- templates
`-- phptoolkit
    `-- soapclient

The workbook.php code :

// replace with your user & password + token
$user='username@domain.com';
$password='yourPassword';
$token='yourToken';
$objectNames = array('Account', 'Contact', 'Opportunity');
$objectFields = array('name', 'label', 'type', 'length', 'nameField', 'namePointing', 'byteLength', 'calculated', 'caseSensitive', 'createable', 'updateable', 'sortable', 'custom', 'defaultedOnCreate', 'deprecatedAndHidden', 'digits', 'filterable', 'groupable', 'idLookup', 'nillable', 'precision', 'restrictedPicklist', 'scale', 'unique');
date_default_timezone_set('Europe/Paris');

define("BASEDIR1", "./phptoolkit/soapclient");
define("BASEDIR2", "./PHPExcel/Classes/");
require_once (BASEDIR1.'/SforcePartnerClient.php');
set_include_path(BASEDIR2);
require_once('PHPExcel.php');
require_once('PHPExcel/IOFactory.php');

echo "Connecting to Salesforce... ";
$conn = new SforcePartnerClient();
$conn->createConnection(BASEDIR1.'/partner23-prod.wsdl');
$mylogin = $conn->login($user, $password.$token);
echo "connected\n";

$excel = new PHPExcel();
$excel->removeSheetByIndex(0);  // remove 1st worksheet
$spreadsheetNb=0;
$no=1;
foreach($objectNames as $objectName) {//sheets: one per object
 echo "Sheet #".$no++.": $objectName\n";
 $objWorksheet = $excel->createSheet();
 $objWorksheet->setTitle(substr($objectName,0,30)); 
 $row=1; $col=0;
 foreach($objectFields as $objectField) $objWorksheet->setCellValueByColumnAndRow($col++,$row,$objectField); //headers
 $response = $conn->describeSObject($objectName);
 $row++;
 foreach($response->fields as $field) {//fields
  $col=0;
  foreach($objectFields as $objectField)
   $objWorksheet->setCellValueByColumnAndRow($col++,$row,$field->$objectField);
  $row++;
 }
}
echo "Saving to Excel\n";
$excelWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$excelWriter->save('workbook.xlsx');

Running the script

olivier@Ubuntu1:~/SFDC/blog/Tortuga-crm/Post_workbook$ php workbook.php 
Connecting to Salesforce... connected
Sheet #1: Account
Sheet #2: Contact
Sheet #3: Opportunity
Saving to Excel

One sheet has been generated per object:

Enjoy your 1 minute workbook!

[Security] Protect your personal data (gnu/Linux users)

1) What?

This blog presents a secure way to protect your personal data. The solution is designed to run on "Unix like" operating systems, e.g.: gnu/Linux, Mac OSX.
If you are looking for a solution to secure your professional data, please referrer to your corporate security policy.

2) Problem

You are still wondering how to keep your personal data secure. What about?
1) A USB key with plain text files
Con's
- the key is stolen, all your data are compromised.
2) A Google Spreadsheet
Con's
- if Google is hacked
- when you are off line
3) An encrypted zip file
Con's
- if your file is stolen, it might be opened using a brutal force attack
- when you are opening a text file, it is temporally copied to your system temp directory
4) A third party tool
Con's
- why will you pay for a closed source tool? This is a matter of confidence
5) An encrypted partition on your hard drive
Pro's
- yeah, smart idea
Con's
- the data can only be accessed from your laptop, and you might need your personal password when you are far from your personal computer
6) An encrypted file partition stored on an USB Key
Pro's
- Definitely YES!

3) Solution Set-up

You will find similar solutions on the Internet. This one worked pretty well on Ubuntu 10.

I recommend that you run all these steps log in as the root user.

Step 1:
Create a 256MB disk file (zero-filled) called file01. I choose to create it under my home directory:

dd if=/dev/zero of=/home/olivier/virtualfs/file01 bs=1024 count=262144 

Then you might want to change the file owner, e.g.:

chmod olivier.olivier /home/olivier/virtualfs/file01

Step 2:
Get the first free loopback devices:

losetup -f

The command losetup -a lists all the loopback devices already in use.

Step 3:
Attach the first loopback device to your disk file:

losetup /dev/loop0 /home/olivier/virtualfs/file01

Step 4:
Crypt the disk:

cryptsetup luksFormat -c aes -h sha256 /dev/loop0

You will be ask for a pass phrase. Choose it carefully. It must not be equal to another of your passwords. Remember the Play Station Network hacking. Re using several times the same password is highly insecure.

Step 5:
Mount the disk in the system:

cryptsetup luksOpen /dev/loop0 secure01

Step 6:
Format the file system, choose your flavor:

mkfs.ext3 /dev/mapper/secure01

Step 7:
Mount the file system:

mk /media/secure01
mount -t ext3 /dev/mapper/secure01 /media/secure01
chmod 777 /media/secure01

Unmount:

umount /media/secure01
cryptsetup luksClose secure01
losetup -d /dev/loop0

4) Automate the Solution

I wrote these two scripts in order to easily mount/unmount the file disk.
This is a generic script that enable to handle multiple file disks without bothering the mounting order: the first loop device is auto detect and the unmount is based on the path.
If the script is not executed by the root user, it is ran again using a sudo command (assuming the running user is in the sudoers list).

mount-secure01.sh

#!/bin/sh

if [ "`whoami`" != "root" ]; then
  sudo $0
  exit
fi 

DEVICE_FS_PATH="/home/olivier/virtualfs/file01"
# check if already mounted
if [ -n "`losetup -a | grep $DEVICE_FS_PATH`" ]; then
  echo "Device already mounted."
  exit
fi

DEVICE=`losetup -f`
losetup $DEVICE $DEVICE_FS_PATH
cryptsetup luksOpen $DEVICE secure01
mount -t ext3 /dev/mapper/secure01 /media/secure01

unmount-secure01.sh

#!/bin/sh

if [ "`whoami`" != "root" ]; then
  sudo $0
  exit
fi 

DEVICE_FS_PATH="/home/olivier/virtualfs/file01"
DEVICE=`losetup -a | grep $DEVICE_FS_PATH | awk -F":" '{print $1}'`

umount /media/secure01
cryptsetup luksClose secure01
losetup -d $DEVICE

You might want to copy the file to a usb key and keep a backup copy on your laptop. If you lost the key, the data will not be compromised. The robber will find a key with an unknown file system, Windows will probably suggest to format it in FAT-32...

Enjoy!

[Salesforce] Scheduling an Apex call from the command line

1) Problem

This post presents a way to execute an Apex method or an Apex batch from the command line, regardless your platform (gnu/Linux, Unix, Windows) using Perl and cURL.

User Cases

• Customer IT rules compliance: all the scheduled batches should be centralized.
• Integration: customer needs to execute an Apex batch (e.g. after a daily data load.)
• Customer IT does not have resources with the required skills to write and maintain an application that will deal with Salesforce API.

2) Solution big picture

This is a very simple solution that might be easily integrated in a shell script. The advantages are: no sweat for customer IT and the new capability of launching heavy processes running on the Salesforce.com side.

Technical solution in a nutshell

A shell script is calling a cURL command that deals with the Salesforce.com API. cURL posts a SOAP login message to Salesforce and opens a session. Then, cURL is calling your Apex method exposed as a web service.

The user case we will walk through

Let's assume that your customer has a nightly batch that imports new leads (with an ETL e.g.). But the phone numbers format does not meet his CTI requirements; so the system cannot match the inbound calls. We need to apply a filter on the phone numbers. The process stands in a simple string replacement using a regular expression: we will suppress all the none numerical characters with the pattern [^\d]*.

3) Solution deep dive

Why Perl?

Why Perl rather than Ruby or PHP? Perl is a popular interpreted script language that is usually pre installed on any Unix/Linux systems. Perl provides powerful text processing facilities without the arbitrary data length limits, facilitating easy manipulation of text files. (see Perl on Wikipedia )

Which Perl for your system?

• Unix/Linux: pre installed (most of the times)
• Windows: Strawberry Perl (open source), Active State Perl (commercial with a free community edition)

on Windows, the installation is straight forward and should not upset the system administrator. Choose your flavor: Strawberry or Active State.

Why cURL?

cURL is open source and stands for “Client for URLs”. It is a command line tool for transferring data with URL syntax, supporting HTTP, HTTPS, FTP, SMTP, LDAP, etc. cURL handles cookies, HTTP headers, forms, all you need to mimic an internet browser.
cURL main site,
cURL download page.

• Unix/Linux: build cURL from the sources (compile with openssl), download a package for your distribution (or use apt-get)
• Windows: Get a version that support the HTTPS protocole

In action

The Perl script is performing the following actions:

• send a login SOAP call to Salesforce.com
• parse the response and get the session id
• send a SOAP call that will execute the Apex command

Your Salesforce.com org data model. Add to the Lead object the custom field:

• Name: phone cti
• API Name: phone_cti__c
• type: text(50)
• Read: all profiles
• Write: only the System Administrator

Apex Batch

This batch is making a copy of the Lead phone field to the phone_cti__c field and apply a filter that removes all the none numeric characters.
This is just some dummy code written for this recipe and not a CTI best practice!
Here is the Apex code, test method is nested in the class:

/*
BatchLeadPhones Blp = new BatchLeadPhones();
ID batchprocessid = Database.executeBatch(Blp);
System.Debug('####batchprocessid='+batchprocessid);
*/
global class BatchLeadPhones implements Database.Batchable{
    public String query;
    global database.querylocator start(Database.BatchableContext BC){
        if ((query==null) || (query=='')) query='Select phone_cti__c From Lead';
        return Database.getQueryLocator(query);
    }

    global void execute(Database.BatchableContext BC, List scope){
        List Leads = new List();
        for(sObject s : scope){
                Lead l = (Lead)s;
                String ph = l.phone_cti__c.replaceAll('[^\\d]*','');
                if (ph != l.phone_cti__c) {
                    l.phone_cti__c = ph;
                    Leads.Add(l);
                }
        }
        if (Leads.Size()>0) update Leads; 
    }
    
    global void finish(Database.BatchableContext BC){
    }
    
    // test method
    static testMethod void test_BatchLeadPhones() {
        Lead l = new lead(LastName='lead test 123', phone_cti__c='+1 (555)123-4567', company='test company 456');
        insert l;
        Test.StartTest();
        BatchLeadPhones Blp = new BatchLeadPhones();
        Blp.query = 'Select phone_cti__c From lead Where ID=\''+l.Id+'\'';
        ID batchprocessid = Database.executeBatch(Blp);
        Test.StopTest(); 
    }
}

Warning, there is a bug in the SyntaxHighlighter module that forces tags to be closed. Do not pay attention to the last line.

Calling the Batch from the System Log window, run on all leads:

BatchLeadPhones Blp = new BatchLeadPhones();
ID batchprocessid = Database.executeBatch(Blp);
System.Debug('####batchprocessid='+batchprocessid);

Run on a single lead:

BatchLeadPhones Blp = new BatchLeadPhones();
Blp.query = 'Select phone, phone_cti__c From lead Where ID=\'00QA000000GCVof\'';
ID batchprocessid = Database.executeBatch(Blp);
System.Debug('####batchprocessid='+batchprocessid);

Calling the Batch from a web service:
This is the Apex class exposed as a webservice:

global class callBatches {
    WebService static String CallBatchLeadPhones() {
        BatchLeadPhones Blp = new BatchLeadPhones();
        ID batchprocessid = Database.executeBatch(Blp);
        return batchprocessid;
    }

    // Test Method
    TestMethod static void test_CallBatchLeadPhones() {
        Lead l = new lead(LastName='lead test 123', phone_cti__c='+1 (555)123-4567', company='test company 456');
        insert l;
        String ID = callBatches.CallBatchLeadPhones();
        System.assert(ID != null);
    }
}

Now you need the WSDL file.

Go to Setup | App Setup | Develop | Apex Classes. On the row of the class callBatches, click on the link “WSDL” to download the WSDL. This XML file describes how to call the web service.

As I guess you are not fluent in WSDL, I recommend that you install SOAPUI on your system. SOAPUI is available as a free edition for gnu/Linux, Windows and Mac OSX; binary and source code.
This recipe is not a SOAPUI tutorial; I will assume that you are familiar with this fantastic tool.
You might want to provide a partner WSDL to SOAPUI and get the SOAP message to open a session in Salesforce.com. Here is the body:

login.xml

user@domain.com
password+token

Replace the username, password and token by your own credentials.
Salesforce will return a SOAP message containing informations regarding the user settings and the organization. The session id appears here:

00DA0000000AXPZ!AQYAQC1_3X1zuSc47y75CU5a4omSypSox6Bg.j.hIsGDBv9hnc7b9ZAD.98ZST3jYxwqoY5TyF4VR7YDUxfWn.ZmeDnoY1Nv

Try this call using cURL. Open a shell:

curl --insecure --silent https://login.salesforce.com/services/Soap/u/21.0 -H "Content-Type: text/xml;charset=UTF-8" -H "SOAPAction: login" -d @login.xml > loginresponse.xml

The SOAP response is saved to loginresponse.xml.
Remark: the --insecure parameter tells cURL not to check the peer. By default, cURL is always checking. See this page to understand how to work with certificates:


Create a new SOAPUI project with your Apex class WSDL, fill the “?” parameters. The set of values is documented in the WSDL file.

The SOAP message should look like:

   
      
         true
      
      
         
         
            Apex_code
            Debug
         
         Debugonly
      
      
         
      
      
         00DA0000000AXPZ!AQYAQC1_3X1zuSc47y75CU5a4omSypSox6Bg.j.hIsGDBv9hnc7b9ZAD.98ZST3jYxwqoY5TyF4VR7YDUxfWn.ZmeDnoY1Nv
      
   
   
      
   

Salesforce.com will answer:

   
      
         21.0 APEX_CODE,DEBUG
23:04:07.035|EXECUTION_STARTED
23:04:07.035|CODE_UNIT_STARTED|[EXTERNAL]|01pA0000002mr28|callBatches.CallBatchLeadPhones
23:04:07.035|METHOD_ENTRY|[1]|01pA0000002mr28|callBatches.callBatches()
23:04:07.035|METHOD_EXIT|[1]|callBatches
23:04:07.042|METHOD_ENTRY|[6]|01pA0000002meNJ|BatchLeadPhones.BatchLeadPhones()
23:04:07.042|METHOD_EXIT|[6]|BatchLeadPhones
23:04:07.042|CONSTRUCTOR_ENTRY|[4]|01pA0000002meNJ|<init>()
23:04:07.042|CONSTRUCTOR_EXIT|[4]|<init>()
23:04:07.042|METHOD_ENTRY|[5]|Database.executeBatch(APEX_OBJECT)
23:04:07.085|METHOD_EXIT|[5]|Database.executeBatch(APEX_OBJECT)
23:04:07.090|CODE_UNIT_FINISHED|callBatches.CallBatchLeadPhones
23:04:07.090|EXECUTION_FINISHED
      
   
   
      
         707A000000Cj9dKIAR
      
   

4) Solution Perl Script

The Perl script is making the same actions.

• login call (login.xml)
• get the session id
• insert the session id in the Apex method web service call
• call the Apex method (request1_tpl.xml)

request1_tpl.xml is a template SOAP call. The session Id is represented by:

#ID#

Perl is replacing #ID# by the value of the session ID and saves the file to request1.xml.
Remember to update the instance name on line 16 (na1, na2, eu1, etc.)

#!/usr/bin/perl -w
use strict;

system('> loginresponse.xml');
system('curl --insecure --silent https://login.salesforce.com/services/Soap/u/21.0 -H "Content-Type: text/xml;charset=UTF-8" -H "SOAPAction: login" -d @login.xml > loginresponse.xml');
open (FILE, 'loginresponse.xml') or die ('cannot read loginresponse.xml');
my $xml=''; while() { $xml.=$_; } close FILE;
if ($xml=~m|([0-9a-z!_\.-]+)|i) {
 my $sessionid=$1;
 open (FILER, 'request1_tpl.xml') or die ('cannot read request1_tpl.xml');
 $xml=''; while() { $xml.=$_; } close FILER;
 $xml=~s/#ID#/$sessionid/;
 open (FILEW, '> request1.xml') or die ('cannot write to request1.xml');
 print FILEW $xml;
 close FILEW;
 system('curl --insecure --silent https://eu1-api.salesforce.com/services/Soap/class/callBatches -H "Content-Type: text/xml;charset=UTF-8" -H "SOAPAction: SessionHeader" -d @request1.xml > response1.xml');
}
exit;

Warning, there is a bug in the SyntaxHighlighter module that forces tags to be closed. Do not pay attention to the last line.

Remark: the command "system('> loginresponse.xml');" is not working on a Windows system. Replace it by "system('echo . 2> loginresponse.xml');"

5) Source code

source code (zip)