Starting to program vtiger CRM

This article applies to vtiger 5.1.0 - some of the advice/code may not be accurate for later versions.

We've been investigating the vtiger CRM as a component in an open source set of tools that a business or organisation might need. A crucial idea is to link various tools together, so I have been looking at how to get information into and out of vtiger.

As a start, we're looking at an Outlook add-in to add an email address/name to the vtiger Contacts list, and how to export to an accounts package. There is already an Outlook vtiger add-in, but it seems pretty flaky and only does synchronisation; we want it to check a new email for existing matches and only add it if need be.

About vtiger

vtiger is a Customer Relationship Manager (CRM). vtiger deals with Leads who might turn into Contacts, with a Contact associated with an Account. You can set up Products with stock levels (and Services as well) and then generate Quotes and Invoices which can be downloaded as PDFs or sent by email. All that sort of thing. It is important to know that vtiger is designed for use only by a business' staff - it is not intended to be used by customers, although vtiger can check incoming support emails.

vtiger is extensible and there is some basic documentation to get you started making your own modules. The instructions effectively describe how to create a new object type (eg a time sheet), represented as a table or two in the vtiger database. You can link your module and its new object type into vtiger, so that eg menu [Tools][Time sheet] shows a list of time sheets in the standard vtiger style, clicking on a record shows the detail, with an Edit option available.

For the rest of this piece, I'm looking at adding functionality to an existing module. This will hopefully help you even if you are starting a complete module. Be careful if you alter existing code - it may well be overwritten if you upgrade vtiger.

The vtiger database

You will almost certainly want to have access to the database that vtiger uses, partly so you can see what's in there, but also so you see any changes as they really are. For MySQL, using phpMyAdmin will probably be the available tool - make sure you can get in to your database.

If there aren't any already, make some test Contacts within vtiger. In phpMyAdmin have a look at these tables:

• vtiger_contactdetails
• vtiger_contactscf
• vtiger_contactaddress
• vtiger_contactsubdetails

Each of these tables has a column named 'contactid' or similar as a primary or foreign key. Note that the "contact_no" column is what is displayed is vtiger as the "Contact Id", eg 'CON1'.

The table vtiger_modentity_num is used when generating the next "contact_no" value, for the appropriate module, so 2 might be next free Contact number to make 'CON2'. Later, we'll use vtiger CRMEntity class setModuleSeqNumber() function to get this value.

The 'contactid' numbers identifying the contact don't start at 0 or 1. In fact, vtiger assigns a unique id number across all objects, so object 792 might be a Contact and 793 might be an Invoice. These details numbers are stored in the vtiger_crmentity table - a deleted flag in there is set when items are first deleted - so they can recovered from the recycle bin. Later, we'll use the insertIntoCrmEntity() function to get a new object id.

vtiger command structure

All vtiger accesses come through one root file index.php. The parameters determine which module operation is carried out. For example, consider this URL

/index.php?action=DetailView&module=Invoice&record=800&parenttab=Inventory

It runs the code DetailView.php in the directory /modules/Invoice/. The code that runs in there, finds the "record" value and shows the relevant invoice. So, you can add to the "Detail View" functionality by adding code to this file.

You can create your own actions by making a new file in the relevant directory. You then need to provide a means of invoking that action - more later.

Smarty templates

vtiger uses the Smarty template system to generate its output. By and large, the vtiger code doesn't generate any HTML - instead, it loads up a template from TPL files in the /Smarty/templates/ directory. vtiger PHP code sets various values that are combined into the template to form the final output. Smarty has an extensive control language available for use in a template, so it can cope with complicated structures, eg it can iterate through arrays to generate multiple table rows. One Smarty template can invoke another, so finding where something is generated can be tricky.

Adding a tool option to an Invoice

If you view an Invoice there are Tools listed on the right hand side: "Export to PDF" and "Send Email with PDF". To add another option here, you have to drill down through the tpl files: DetailView.php invokes template Inventory/InventoryDetailView.tpl which includes template Inventory/InventoryActions.tpl.

In InventoryActions.tpl, after this line:

<!-- To display the Export To PDF link for PO, SO, Quotes and Invoice - ends -->

add in this code:

{if $MODULE eq 'Invoice'}
{assign var=send_accounts_action value="SendAccounts"}
<tr><td align="left" style="padding-left:10px;">
<a href="index.php?module={$MODULE}&action={$send_accounts_action}&return_module={$MODULE}&return_action=DetailView&record={$ID}&return_id={$ID}" class="webMnu"><img src="{'actionGenerateInvoice.gif'@vtiger_imageurl:$THEME}" hspace="5" align="absmiddle" border="0"/></a>
<a href="index.php?module={$MODULE}&action={$send_accounts_action}&return_module={$MODULE}&return_action=DetailView2&record={$ID}&return_id={$ID}" class="webMnu">Send to Accounts</a>
</td>
</tr>
{/if}

Upload InventoryActions.tpl and view an Invoice - check that "Send to Accounts" is visible on the right.

Adding to accounts

Now, create a file SendAccounts.php - probably copying an existing file such as DetailView.php makes sense.

OK - this bit is skimpy for now, but it might get you started. Near the end, add in the following code to query the vtiger database and display a little info from each record found. To get the list of products ordered you will also have to query the vtiger_inventoryproductrel table.

$query="select * from vtiger_invoice where invoiceid=?";
$invoice_id = $focus->id;
$result = $adb->pquery($query, array($invoice_id));
$noofrows = $adb->num_rows($result);
$final_output = "";
while ($inv_info = $adb->fetch_array($result))
{
$invoice_no = $inv_info['invoice_no'];
$subject = $inv_info['subject'];
$invoicedate = $inv_info['invoicedate'];
$accountid = $inv_info['accountid'];
$total = $inv_info['total'];
$final_output .= "$invoice_no: $subject $invoicedate $accountid $total<br/>";
}
$smarty->assign("SEND_DETAILS", $final_output);
$smarty->display("Inventory/SendToAccounts.tpl");

At the end of our new code, the Smarty variable "SEND_DETAILS" is set to the string we want to display. Then the Smarty template SendToAccounts.tpl is run.

You had best create the template file SendToAccounts.tpl initially as a copy of an existing template, eg InventoryDetailView.tpl. You will need to pare it down - and eventually add in a line like the following to display your output:

<p><b>Storing data</b>: {$SEND_DETAILS}</p>

OK - all we've done so far is display the invoice details. Actually talking to an accounts system is beyond the scope of this article.

Adding a new contact

As I said earlier, I want to have an Outlook add-in that can be used to add a contact to the vtiger database, after checking to see whether the contact already exists. As the first step to getting this done, I set up a small web form that has First name, Last name and Email fields. The Go button is set up to go to this vtiger URL eg:

/index.php?action=AddOutlookContact&module=Contacts&FirstName=Chris&LastName=Cant&Email=sales@phdcc.com

As per usual, vtiger uses its standard rules so that this script is called:
/modules/Contacts/AddOutlookContact.php

I made AddOutlookContact.php in a similar way to that described above, using the title "Import from Outlook" for the main content tab. The code generates the output HTML in the PHP script - a neater final approach would put the raw HTML within the template. Anyway, the PHP code generates its HTML in $Output and then invokes my new template AddOutlookContact.tpl:

$smarty->assign("IMPORT_DETAILS", $Output);
$smarty->display("AddOutlookContact.tpl");

In the guts of AddOutlookContact.tpl, the output is reproduced:

<div>{$IMPORT_DETAILS}</div>

The tasks within AddOutlookContact.php are:

• Get passed parameters
• Check parameters
• Find any matching names or emails
• Show matches to user
• Show Add Contact button

So: the user can see any existing matches, and can decide whether or not to press the Add Contact button, which goes to another script AddOutlookContact2.php.

The SQL script to query the existing contacts is as follows:

SELECT c.*, a.accountname, crm.deleted FROM vtiger_contactdetails AS c
LEFT JOIN vtiger_account AS a USING (accountid)
INNER JOIN vtiger_crmentity AS crm ON crm.crmid=c.contactid
WHERE lastname LIKE '%$QuotedLastName%' OR email LIKE '%$QuotedEmail%'";

Note that I also use the function mysql_real_escape_string() to ensure that the passed strings cope with single and double quotes etc.

The SQL links vtiger_contactdetails with the vtiger_crmentity table so the Deleted column can be picked up. The vtiger_account table is also included so any associated account name can be found.

The code goes through all the found matches and displays the results to the user. I provided a link to each contact - this is the code for the link:

$MatchLink = "index.php?action=DetailView&module=Contacts&record=$Match_ContactId&parenttab=Support";

The following form shows a suitable "Add new contact" button:

<form action='index.php' method='post' onsubmit='VtigerJS_DialogBox.block();'>
<input type='hidden' value='AddOutlookContact2' name='action' />
<input type='hidden' value='Contacts' name='module' />
<input type='hidden' value='$htmlLastName' name='LastName' />
<input type='hidden' value='$htmlFirstName' name='FirstName' />
<input type='hidden' value='$htmlEmail' name='Email' />
<input type='submit' value='Add new contact' />
</form>

AddOutlookContact2.php has a similar core as before, except it generates HTML that shows what it has done. Here's what I did to add a new contact. This is probably not the recommended vtiger method, but it seems to get the job done.

$adb->startTransaction();
$NewContactNo = $focus->setModuleSeqNumber("increment",'Contacts'); // updates vtiger_modentity_num
$focus->insertIntoCrmEntity('Contacts'); // updates vtiger_crmentity and sets $focus->id

$query = "insert into vtiger_contactdetails (contactid,contact_no,firstname,lastname,email) values(?,?,?,?,?)";
$qparams = array($focus->id, $NewContactNo, $FirstName, $LastName, $Email);
$adb->pquery($query, $qparams);

$query = "insert into vtiger_contactscf (contactid) values(?)";
$qparams = array($focus->id);
$adb->pquery($query, $qparams);

$query = "insert into vtiger_contactaddress (contactaddressid) values(?)";
$qparams = array($focus->id);
$adb->pquery($query, $qparams);

$query = "insert into vtiger_contactsubdetails (contactsubscriptionid) values(?)";
$qparams = array($focus->id);
$adb->pquery($query, $qparams);

$adb->completeTransaction();

Here are the steps:

• Call setModuleSeqNumber() to get the next contact no eg CON2
• Call insertIntoCrmEntity() to get the next contact id, eg 794
• Insert the basic details into vtiger_contactdetails
• Insert the contact id into vtiger_contactscf
• Insert basic rows into vtiger_contactaddress and vtiger_contactsubdetails

The output provides a suitable link to the new Contact.

Later, I made a real Outlook 2007 addin to call this vtiger code. An extra button is shown on the ribbon when you read an email. When you click the button, it picks up the sender's name and email address and shows the correct URL with parameters in the user's default browser.