Like any database app, users of Force.com often want to see the updated statistics for their initiatives on a regular basis, be it a sales pipeline, marketing campaign or other activity. Email provides a convenient and easy way to deliver that data, keeping key metrics and goals in front of the right users, and on their mind.

Using the Force.com API and PHP, it's straightforward to create these kinds of email reports - and with SOQL-Relationships new in the 8.0 API, it's easier than ever. As a use case, email reports requires using some of the most common aspects of the API and associated style sheets / presentation tools, so this task a good starting place for those looking to start out using PHP with the Force.com API.

This article provides of an overview of creating a scheduled email report based on your choice of data, and covers some key PHP and Apex basics, including connecting the API, working with queries and query results, and skinning your page (and email) to carry the salesforce.com look and feel.

Contents

Prerequisites

For this sample, you'll need to be up and running with the PHP Toolkit, which provides access to most - but not all - of the Force.com API from PHP. The bad news is that getting an environment set up with the right version of PHP and the related libraries is typically the hardest part of using PHP with Apex. (The good news is that this is the hardest part, and the coding is straightforward!) To start off, you'll need to make sure you have PHP 5.1.2 or later installed, and are using a build of PHP with support for SOAP/XML (so your application can talk to the Force.com API) as well as curl (so your application can do so using SSL). The full details on getting setting up are available on the wiki page for the PHP_1.0.x_Toolkit.

Connection and Query

For this use case, which will involve a PHP script being invoked every night automatically and generating an email, we'll be running PHP via the command line, and not in a Web browser as is typically the case. So we'll need to establish a new connection to the API with each invocation.


require_once ('SforcePartnerClient.php');
$mySforceConnection = login("user@domain.com","password");


function login($username, $password) {
        global $mySforceConnection;
        $wsdl = 'wsdl.xml';
        $mylogin = null;
        try {
                $mySforceConnection = new SforcePartnerClient();
                $mySforceConnection->createConnection($wsdl);
                $mylogin = $mySforceConnection->login($username, $password);

        } catch (Exception $e) {
                global $errors;
                $errors = $e->faultstring;
                echo $errors;
        }

        return $mySforceConnection;
}

This handy block of code returns as what we really want - a connection to the API - which we'll use for all the subsequent API requests. And if there was an problem (bad password, etc) we should get that error back in the fault string. Since this script will be running at the command line there is probably a better technique to capture and report the error than writing it to the screen, but we'll leave that for extra credit (or an enterprising wiki editor).

The magic happens with the query, and specifically the new SOQL-Relationships support introduced with the Winter 07 (8.0) API. With this new functionality, it is now possible to retrieve a full set of related data in a single query, as opposed to manually traversing related objects in multiple requests. Hopefully you'll quickly find how powerful this can be in answering complex questions, such as "what percentage of the people who participated in this marketing campaign were from customer accounts versus prospect accounts." Unlike SQL Joins, SOQL-R is an object query language, meaning your result set will be a graph of objects (related objects pointing to one another), rather than a single column/row result.

For this example, we want to get a list of all of the new members of a campaign for the current day. Turns out that this is a particularly good example for SOQL-R as it involves working with the Campaign Member object, which is part of a many to many relationship with Contacts and Leads, and as such allows involves data from multiple objects. Its also a good use case for email reports, as it will let us create an email that contains key marketing data - all of the people that have signed up for a particular web form in the past day. For this example, we are assuming that the leads and contacts from the web form have been associated with a campaign.

Select Id, CampaignId, FirstRespondedDate, CreatedDate, Lead.Name, Lead.Title, Lead.Company, Lead.Email, Lead.Id, Contact.Name, Contact.Account.Currentlicenses__c, Contact.Email, Contact.Account.Name From CampaignMember Where (CampaignId = '70130000000CGhzAAG' AND CreatedDate = TODAY) Order By CreatedDate Asc

Working with the Results

When you put the code listed above together with the connection, you can see that its easy to issue a query and be ready to work with the results.

$query = "Select Id, CampaignId, FirstRespondedDate, CreatedDate, Lead.Name, Lead.Title, Lead.Company, Lead.Email, Contact.Name, Contact.Account.Currentlicenses__c, Contact.Email, Contact.Account.Name From CampaignMember Where (CampaignId = '7013000000005wb' AND CreatedDate = TODAY) Order By CreatedDate Asc";

$response = $mySforceConnection->query($query);
$queryResult = new QueryResult($response);

So now that we have a connection, and a query, the next step is the (fairly) simple matter of iterating through the results. One thing to keep in mind is that a CampaignMember can be either a lead or a contact, so as we traverse the result set we’ll need to check to the type of data we are working with. Specifically, for this report, if the Campaign Member is a Contact, I want to display the number of licenses (a custom field) that the Contact represents. That value stored on the Account record that is the parent of the Contact, and since its defined in my original query, the value is available to me via the queryResult variable. However, since that value is only present for Contacts, and not Leads, I’ll need to check the type of each row as I iterate through the results.

foreach ($queryResult->records as $CampaignMember) {
	 
 // get fields that are common to both Lead and Contact

$_id = CampaignMember->sobjects[0]->Id;
$_name = $CampaignMember->sobjects[0]->fields->Name;

		$_email = CampaignMember->sobjects[0]->fields->Email;
		$_title = CampaignMember->sobjects[0]->fields->Title;
		
// get fields that are dependant on the Campaign Member type

		if ($CampaignMember->sobjects[0]->type == 'Lead') {
			$_type = "Lead";
			$_company = $CampaignMember->sobjects[0]->fields->Company;
		}

		if ($CampaignMember->sobjects[0]->type == 'Contact') {
			$_type = "Contact";
			$_company = $CampaignMember->sobjects[0]->sobjects[0]->fields->Name." - ".$CampaignMember->sobjects[0]->sobjects[0]->fields->Currentlicenses__c;
		}
 echo “Id:“.$_id.” Name:”.$_name.”Email: “.$_email”;
}

So now we have iterated through the query result, and have the fields we’ll want to use in our email available as a handy set of local variables. (While there are more terse ways of writing the code, this approach was taken for readability.) You can also see we've added a line at the end of the code block to echo the results to output.

Output to Email

The common way of interacting with a PHP page is to request it as a URL from a Web server, with the echo and other output being sent back to the Web browser. Of course, PHP can also be run from the command line, which creates an interesting opportunity – what if you could redirect the output from STDOUT (the command line) or the browser and instead have it be used to create the contents of an email? Fortunately, PHP provides a convenient, if slightly obscure, way. The magic starts with ob_start() – simply place that line in your PHP script, and all subsequent output is redirected to a buffer. When you are done, extract its contents with $content = ob_get_contents(). Here is an example:

<?php
ob_start();
echo  “hello world”;
//do all of your query and HTML outputting here
$content = ob_get_contents();
?>


Everything you do between ob_start and ob_get_contents will be captured in the $content variable, making it easy to add lots of query code, and complex formatting. Better yet, you can comment out ob_start during development, test and build your page interactively, and only enable it when you are ready for deployment. So the last task is to actually send $content as an email, which is done as follows.

$base64contents = rtrim(chunk_split(base64_encode($content)));
mail(‘fromperson1@email.com, 'My Subject Line’,
    $base64contents,
    "To: person1@salesforce.com,person2@salesforce.com\n" .
    "From: Your PHP Script <fromperson1@email.com>\n" .
    "MIME-Version: 1.0\n" .
     "Content-type: text/html; charset=iso-8859-1\n".
     "Content-Transfer-Encoding: base64");


We will leave it to the reader to put together the components into a single script, and add some style with CSS (for best results, create an HTML email). Add a cron job to schedule nightly execution, and sit back and enjoy the complements!