Connect XERO to VTiger CRM

Connect XERO to VTiger CRM

In my last article on XERO accounting software, I mentioned all the possibilities of using XERO for your account management. I had also said that you could connect XERO to VTiger CRM if necessary. In this tutorial, I’ll show you how to do it. Whether you run a business or you are an entrepreneur; you can use XERO to manage receipts, invoices, sales list product list, contacts and much more.

XERO accounting software is a cloud based software application service. So, you can access your account from anywhere around the world. You can in fact carry your accounts along with you on your mobile device while on the go.

Now, since the software is very useful; you can actually integrate it in your website CRM as well. CRM solutions are especially built to manage customer or lead information of a company, manage invoices, manage products, sales and so on. Integrating it with XERO can provide some very useful features added on to your CRM. Here is a list of points that I have jotted down as some of the best added on features when you connect XERO to VTiger.

The idea is to integrate or connect XERO to VTiger CRM such that all updates in both the applications are interchanged.

XERO provides a good API connectivity for developers to build and connect XERO to other applications. From the link above, you can browse throughout their API documents and guide. You will find that there is a special section named – “connect to CRM”. When you click on it, you will be able to see several CRM options for which some code or plugin has already been built. Unfortunately, these codes are built privately by third parties (not XERO) and I could not find one for VTiger CRM when I needed.

When I started looking up for documentation on how to connect XERO to VTiger, I came across their built in code samples for developer help.

Note: These code samples are available according to the language of your preference. They are hosted on GitHub and uploaded by XERO officials. But, the PHP codes help only in the authentication part and connection part. Retrieving data or updating to your XERO account might not work without changes to the codes.

They use a complex code flow while connecting and authenticating. What I did was, I broke up the whole thing and rearranged it according to my convenience.

Understanding the API to connect XERO to VTiger

The API flow goes as follows –

–          First, the authentication request is sent to XERO.

–          XERO checks the consumer key and secret

–          XERO provides an access token for a certain time interval.

–          You can now access the API with this token

XERO requires you to create an app before accessing their API. There are two types of apps that can be created – public and private.

Public apps remain active for accessing the API up to a certain time interval (30 minutes). So, if you require accessing the API more than 30 minutes of time you might need to authenticate again and again.

Private apps, on the other hand, have long time validity. These apps require digital certificates for authentication and the time interval is mentioned in the certificate itself. Normally it is 365 days (according to the XERO documentation for creating certs), but you can extend that by entering the number of days you wish to.

We deal with private apps in this tutorial. For private apps, you need not authenticate multiple times; just run the authentication once and you’re done.

Steps to connect XERO to VTiger

The very first step is to create a XERO account. Start off with creating a trial account and upgrade as soon you are happy using it. Remember, trial accounts freeze after a certain number of transactions. So, it is better to use a full version as soon as possible.

Once created, go to the developer section. From the tabs on the header region, select “My Applications”. Click on the “Add application” button. Select “private” and provide a name for the app. Next, select the organization that you created while creating the XERO account.

After this, you need to upload your own X509 public certificate. To create this certificate, you need to install OpenSSL according to your operating system configuration.

After installation, follow the instructions to creating a certificate from the XERO website. Just follow and run the commands and your certificates shall be created in the OpenSSL folder itself. There shall be two cerificates – a private and a public. You need to upload the public (.cer) file while creating the application.

Your app is now ready. Make a note of the “consumer key” and the consumer secret” that appears after you save.

Now, we check out the “code samples” provided on the XERO website. To connect XERO to VTiger, we must refer to the procedure from their PHP code samples. After opening the PHP code sample page, you will see that there is a code in GitHub by David Pitman for private applications. My procedure is based as a derivation from his code samples.

We will work with this code. What we do is rearrange some codes according to our convenience, thus keeping the original concept and code metrics same.

The authentication code sample is as follows –

require 'lib/XeroOAuth.php'; // do not forget to keep these files as supplied from the GitHub code

/* FOR CHANGE IN XERO ACCOUNT...MAKE CHANGES IN THE FOLLOWING DATA...START------>>>> */
define('BASE_PATH','.'); 
define("XRO_APP_TYPE",     "Private");
define("OAUTH_CALLBACK",     "oob");
$useragent = "YOURAPPNAME";                                                       // app name goes here

$signatures = array( 'consumer_key'     => 'YOURCONSUMERKEY',    // consumer key given during private app creation
              	     'shared_secret'    => 'YOURCONSUMERSECRET',    // consumer secret given during private app creation
	           	'core_version'	=> '2.0',
	        	'payroll_version' => '1.0');
/* FOR CHANGE IN XERO ACCOUNT...MAKE CHANGES IN THE ABOVE DATA...END------>>>> */

if(XRO_APP_TYPE=="Private"||XRO_APP_TYPE=="Partner"){
	$signatures['rsa_private_key']	= BASE_PATH . '/certs/privatekey.pem';
	$signatures['rsa_public_key']	= BASE_PATH . '/certs/publickey.cer';
}

$XeroOAuth = new XeroOAuth(array_merge(array(
						  	'application_type' => XRO_APP_TYPE,
						  	'oauth_callback'	 => OAUTH_CALLBACK,
						  	'user_agent'		 => $useragent,
							), $signatures));

	$initialCheck = $XeroOAuth->diagnostics();
 	$checkErrors = count($initialCheck);
	 if($checkErrors>0){
	 	// you could handle any config errors here, or keep on truckin if you like to live dangerously
	 	foreach($initialCheck as $check){ echo 'Error: ' . $check . PHP_EOL; }
	 }else{

$session  = persistSession(array('oauth_token' => $XeroOAuth->config['consumer_key'], 'oauth_token_secret' => $XeroOAuth->config['shared_secret'], 'oauth_session_handle' => ''));
$oauthSession = retrieveSession();

if ( isset($oauthSession['oauth_token']) ) {

 	$XeroOAuth->config['access_token']  = $oauthSession['oauth_token'];
  	$XeroOAuth->config['access_token_secret'] = $oauthSession['oauth_token_secret'];

        if ( isset($oauthSession['oauth_token']) && isset($_REQUEST) ) {

	$XeroOAuth->config['access_token']  = $oauthSession['oauth_token'];
  	$XeroOAuth->config['access_token_secret'] = $oauthSession['oauth_token_secret'];
  	$XeroOAuth->config['session_handle'] = $oauthSession['oauth_session_handle'];

/* -------- Make all requests here ------- */

}
}
}

Just include the above code at the beginning of a PHP file. After authentication, from within the loop (while the authentication is valid); access the API according to the XERO manual.

Here’s an example of getting multiple invoices from XERO and adding them to VTiger database –

/* GETTING INVOICES FROM XERO TO OUR SERVER ----------------------------START-------- */
$response = $XeroOAuth->request('GET', $XeroOAuth->url('Invoices', 'core'), array('Where' => $_REQUEST['where']));
if ($XeroOAuth->response['code'] == 200) {
    $invoices = $XeroOAuth->parseResponse($XeroOAuth->response['response'], $XeroOAuth->response['format']);
    $invoices_in_array = json_decode(json_encode((array) $invoices), 1);    
    foreach ($invoices_in_array as $key => $value){        
        if(is_array(current($value))){              
          foreach ($value as $key1 => $value1) {
                if($key1 == 'Invoice'){
                    foreach ($value1 as $key2 => $value2){
                        //Setting the variables for database update                        
                        foreach ($value2 as $attribute => $data) {
                            switch ($attribute) {
                                case 'Status': $status = $data;
                                    break;
                                case 'Date': $date = $data;
                                    break;
                                case 'DueDate': $duedate = $data;
                                    break;
                                case 'SubTotal': $subtotal = $data;
                                    break;
                                case 'Total': $total = $data;
                                    break;
                                case 'Contact': $accountid = getCustId($data['Name']); // this is a custom function
                                    break;
                                case 'InvoiceNumber': $invoicenumber = $data;
                                    break;
                                case 'Reference': $reference = $data;
                                    break;
                            }                    
                        }                       
                    }   

                    // Setting the status of the invoice
                    switch ($status){
                        case 'SUBMITTED' : $statforupdate = 'Awaiting Approval in XERO';
                            break;
                        case 'AUTHORIZED' : $statforupdate = 'Awaiting payment';
                            break;
                        case 'PAID' : $statforupdate = 'Paid';
                            break;
                        case 'VOIDED' : $statforupdate = 'Voided';
                            break;
                        default : $statforupdate = 'AutoCreated';
                            break;
                    }

                        // Getting last product id..
                             $q = "SELECT id FROM vtiger_crmentity_seq";
                            $r = mysql_query($q) 
                                or die();
                            $id = mysql_fetch_row($r);
                            $lastid = $id[0];
                            $lastid = intval($lastid);
                            //increasing $lastid by one for new entry
                            $lastid += 1;

                        $query = "SELECT * FROM vtiger_invoice WHERE invoice_no='".$invoicenumber."'";
                        $result = mysql_query($query) 
                        or die();
                        // insert only when the record is not there
                        if(mysql_num_rows($result)==0) 
                        {
                        // Inserting into vtiger_crmentity table - always first step

                        //making a datetime
                        $timenow = date("Y-m-d h:i:s", time());

                        $q1 = "INSERT INTO vtiger_crmentity(crmid, smcreatorid, smownerid, modifiedby, setype, description, createdtime, modifiedtime, viewedtime, status, version, presence, deleted) 
                            VALUES('".$lastid."', '1', '1', '1', 'Invoice', '', '".$timenow."', '".$timenow."', '".$timenow."', '', '0', '1', '0')";
                        $r1 = mysql_query($q1) 
                            or die();

                        // Inserting into vtiger_crmentity_seq table - always second step
                        // When we add something from vTiger's interface..it fetches next applicable id from this table
                        // for entry in vtiger_crmentity

                        $q2 = "UPDATE vtiger_crmentity_seq SET id=".$lastid;
                        $r2 = mysql_query($q2) 
                            or die();

                        // Inserting into vtiger_invoice table

                        $q3 = "INSERT INTO 
                            vtiger_invoice(invoiceid, subject, salesorderid, customerno, 
                            contactid, notes, invoicedate, duedate, invoiceterms, type, 
                            adjustment, salescommission, exciseduty, subtotal, total, 
                            taxtype, discount_percent, discount_amount, s_h_amount, shipping,
                            accountid,terms_conditions, purchaseorder, invoicestatus, invoice_no,
                            currency_id, conversion_rate, exported_or_imported) 
                            VALUES('".$lastid."','".$reference."', NULL, '', '', '', 
                                '".$date."', '".$duedate."', '', '', '0.000', '0.000', '0.000',
                                '".$subtotal."', '".$total."', 'individual', '0.000', '0.000',
                                '0.000', '', '".$accountid."', 'Unless otherwise agreed in writing by the suppli',
                                '', '".$statforupdate."', '".$invoicenumber."', '1', '1.000', 'Yes')";
                        $r3 = mysql_query($q3) 
                            or die();

                        // Inserting into vtiger_invoicecf table                           

                        $q4 = "INSERT INTO vtiger_invoicecf(invoiceid, cf_640) 
                            VALUES('".$lastid."', 'No')";
                        $r4 = mysql_query($q4) 
                            or die();
                        }// end of if 
                   }
            }
        }        
    }
  }
/* GETTING INVOICES FROM XERO TO OUR SERVER ----------------------------END--------- */

Here’s an example of adding VTiger contacts to XERO –

/* PUTTING CONTACTS TO XERO FROM OUR SERVER ----------------------------START-------- */
      
$query = "SELECT * FROM vtiger_contactdetails WHERE exported_or_imported='No'";
$result = mysql_query($query) 
	or die();

while($row = mysql_fetch_row($result)){    
    // Getting 'description' from crmentity table
    $query2 = "SELECT * FROM vtiger_contactaddress WHERE contactaddressid='$row[0]'";
$result2 = mysql_query($query2) 
	or die();
$row2 = mysql_fetch_row($result2);
    
		$xml = "<Contact>
  <ContactNumber>".$row[1]."</ContactNumber>
  <Name>".$row[4]." ".$row[5]."</Name>
  <FirstName>".$row[4]."</FirstName>
  <LastName>".$row[5]."</LastName>
  <EmailAddress>".$row[6]."</EmailAddress>
  <Addresses> 
      <Address> 
        <AddressType>POBOX</AddressType> 
        <AddressLine1>".$row2[2]."</AddressLine1> 
        <City>".$row2[1]."</City> 
        <PostalCode>".$row2[9]."</PostalCode>
      </Address>
      <Address> 
        <AddressType>STREET</AddressType> 
        <AddressLine1>".$row2[11]."</AddressLine1> 
        <City>".$row2[7]."</City> 
        <PostalCode>".$row2[12]."</PostalCode>
      </Address>
  </Addresses>  
  <Phones>
    <Phone>
       <PhoneType>DEFAULT</PhoneType>
       <PhoneNumber>".getNumber($row[7])."</PhoneNumber>
       <PhoneAreaCode>".getAreaCode($row[7])."</PhoneAreaCode>
    </Phone>
    <Phone>
       <PhoneType>MOBILE</PhoneType>
       <PhoneNumber>".getNumber($row[8])."</PhoneNumber>
       <PhoneAreaCode>".getAreaCode($row[8])."</PhoneAreaCode>
    </Phone>    
  </Phones>
</Contact>";
                
		$response = $XeroOAuth->request('PUT', $XeroOAuth->url('Contacts', 'core'), array(), $xml);
		  if ($XeroOAuth->response['code'] == 200) {
		    $contact = $XeroOAuth->parseResponse($XeroOAuth->response['response'], $XeroOAuth->response['format']);
                    
                    // Success. Change status in database                                  
                    
	}else {
    outputError($XeroOAuth); 
  }
        }
 
/* PUTTING CONTACTS TO XERO FROM OUR SERVER ----------------------------END--------- */

When we connect XERO to VTiger and send data, it is passed as an XML object. The XML tags are based on the respective XML tags mentioned in XERO. Make sure you do not add custom tags. Otherwise it might prompt an error.

For checking errors that commonly appear, you can check out the common errors listed in XERO.

Now, the most important part is that this code is not periodic or automatic. You must run the file manually in order to check for changes in database and update the necessary changes. So, one thing you could do is run the file as a CRON task from the server. This way, your file will keep running silently in the background without any manual interference.

For any further queries, you can refer to this technical documentation

That’s all. You can now connect XERO to VTiger CRM quite easily enough. Tell me your experience in the comments below.

  • Taylor Hamling

    does this work with vtiger 6? I am unsure about how to integrate with the latest version of vtiger

    • Hi Taylor, thanks for stopping by my blog.
      Well, the method mentioned in my article is an API sync using oAuth techniques and simple PHP coding. So, version is not a problem. Go ahead! Best of luck :)

  • Hi Kaustav, I use Vtiger On Demand, can this code be used with that version of Vtiger? I keep seeing reference to version 6 but nobody ever mentions if it’s on-demand or dedicated server.

    • Hi Jason, this code is based on oAuth and PHP. It simply connects vTiger database externally from a PHP file, using XERO’s API documentation measures. The syncing is done totally from an external PHP – connecting the XERO database and the vTiger database.
      So,no version issues involved, enjoy!

      I recommend you browse some more articles and do follow me on twitter @kb020189 / @a100websolution

  • Hey I know this is off topic but I was wondering if
    you knew of any widgets I could add to my blog that automatically tweet my
    newest twitter updates. I’ve been looking for a plug-in like this for
    quite some time and was hoping maybe you would have some experience
    with something like this. Please let me know if you run into anything.

    I truly enjoy reading your blog and I look forward to your new
    updates.

  • Someone essentially assist to make critically articles I would state.
    This is the first time I frequented your web page and up to now?
    I surprised with the research you made to create this
    particular submit extraordinary. Fantastic process!

  • Pingback: URL()