Adding a Chart to Your Report
From Cerberus Helpdesk Wiki
Warning this code only works with Cerberus 4.2.3 and earlier. This example needs to be updated to the new format. See the forums for more info on the updated format. http://www.cerb4.com/forums/showthread.php?t=2438
Contents |
[edit] There are errors in this code and doesn't work out of the box.
In the previous tutorial, Creating a plugin, we went through all the steps necessary to create a simple report plugin, and a report that displayed a list of workers. In this tutorial we will expand on the previous example and create a chart for our report. The chart will be a bar chart that shows the number of open tickets assigned to each worker.
The charts library used by Cerberus is part of the Yahoo UI Library. You can read a lot more information about them at http://developer.yahoo.com/yui/charts/ .
In this implementation we are going to use an AJAX call to the server to download the data for our report. Then we'll pass the results to the charts library so they can be rendered client-side in the browser.
[edit] Changes to our Report Extension Class
Open up the App.php file for our plugin (located in plugins/wgm.reports.myreports/api/). First we'll write the function that returns the data that will be drawn in our chart. In the class for the report extension we created in the last tutorial (WGMReportWorkers), add the following function:
function getWorkerOpenTicketsAction() {
$db = DevblocksPlatform::getDatabaseService();
//initialize the template service and settings
$tpl = DevblocksPlatform::getTemplateService();
$tpl->cache_lifetime = "0";
$tpl->assign('path', $this->tpl_path);
//get our global list of workers for the helpdesk
$workers = DAO_Worker::getAll();
$tpl->assign('workers', $workers);
//create and run a query to find the number of tickets owned by each worker
$sql = "SELECT next_worker_id, count(*) as owned_tickets_count ".
"FROM ticket ".
"WHERE is_closed = 0 " .
"AND is_deleted = 0 ".
"GROUP BY next_worker_id ";
$rs_workers = $db->Execute($sql);
//echo worker id and ownership count in tab delimited format
while(!$rs_workers->EOF) {
$owned_tickets_count = intval($rs_workers->fields['owned_tickets_count']);
$worker_id = intval($rs_workers->fields['next_worker_id']);
if(isset($workers[$worker_id]))
echo $workers[$worker_id]->getName() , "\t" , $owned_tickets_count , "\n";
$rs_workers->MoveNext();
}
}
It's worth mentioning that Devblocks expects that any function that is meant to be called by AJAX should be named so that it includes the suffix "Action". This will be useful to remember especially when we get around to writing the URL for the request in our template later.
After some basic template system initialization we come to some notable lines:
//get our global list of workers for the helpdesk
$workers = DAO_Worker::getAll();
$tpl->assign('workers', $workers);
//create and run a query to find the number of tickets owned by each worker
$sql = "SELECT next_worker_id, count(*) as owned_tickets_count ".
"FROM ticket ".
"WHERE is_closed = 0 " .
"AND is_deleted = 0 ".
"GROUP BY next_worker_id ";
$rs_workers = $db->Execute($sql);
This section gets the data for our report. We could have just pulled the data for the workers directly from the SQL query, but instead I chose to use the Data Access Object (DAO) for workers. The list of the Workers obtained from the DAO is actually cached by the Devblocks platform Cerberus uses, so it is a reliably quick way to have access to most of the important fields about each worker. This means our SQL query only needs to pull worker ids, and totals from the ticket table.
The rest of the file just loops through our database result set and writes out our results in a tab delimited format. This tab delimited data will eventually be handled by JavaScript written into the report template file, and eventually feed into the chart library.
[edit] Template Changes
Open up the report template file report_workers.tpl.php from the previous example. We need to create a div tag for where we want our chart drawn. We want it under the title, but above the report data.
<h2>My Workers Report</h2>
<br/>
<div id="myChart" style="width:100%;height:400;"></div>
<br/>
{foreach from=$workers item=worker}
{$worker->first_name} {$worker->last_name}<br/>
{/foreach}
Next we will create a JavaScript function that will perform the AJAX necessary to request the data generated by our function above on the server, and generate a chart with it. Paste the following at the top of the same report template file:
<script language="javascript" type="text/javascript">
{literal}
YAHOO.util.Event.addListener(window,'load',function(e) {
drawChart();
});
function drawChart() {{/literal}
YAHOO.widget.Chart.SWFURL = "{devblocks_url}c=resource&p=cerberusweb.core&f=scripts/yui/charts/assets/charts.swf{/devblocks_url}?v={$smarty.const.APP_BUILD}";
{literal}
var cObj = YAHOO.util.Connect.asyncRequest('GET', "{/literal}{devblocks_url}ajax.php?c=reports&a=action&extid=report.workers.my_workers_list&extid_a=getWorkerOpenTickets{/devblocks_url}{literal}", {
success: function(o) {
var resultArr = new Array();
//parse the tab delimited file data from the server into an array of javascript objects
var trimmedText = o.responseText.replace(/^\s+|\s+$/g, '') ;
var rows = trimmedText.split("\n");
for(i=0; i < rows.length; i++) {
var cols = rows[i].split("\t");
resultArr[i] = new Object();
resultArr[i].worker = cols[0];
resultArr[i].ticketcount = cols[1];
}
//instance a yahoo charts datasource based on our result array
var myDataSource = new YAHOO.util.DataSource(resultArr, {responseType: YAHOO.util.DataSource.TYPE_JSARRAY});
//for the response schema, just specify the fields from the javascript object we will use for our chart
myDataSource.responseSchema =
{
fields: [ "worker","ticketcount" ]
};
//set the chart size based on the number of records we got from the server
myChart.style.cssText = 'width:100%;height:'+(30+30*resultArr.length);
//create the chart
var chart = new YAHOO.widget.BarChart( "myChart", myDataSource,
{
xField: "ticketcount",
yField: "worker",
wmode: "opaque"
//polling: 1000
});
},
failure: function(o) {},
argument:{caller:this}
}
);
}{/literal}
</script>
That's a lot to look at all at once so I'll go back over some of the details. First if you look at the very beginning:
<script language="javascript" type="text/javascript">
{literal}
YAHOO.util.Event.addListener(window,'load',function(e) {
drawChart();
});
The {literal} is a Smarty template tag that tells the the template system not to interpret curly braces which would otherwise interfere with JavaScript. The rest of this section uses Yahoo UI's Event api to make sure our new function is called when the page has loaded.
At the very top of the drawChart() function we see this:
YAHOO.widget.Chart.SWFURL = "{devblocks_url}c=resource&p=cerberusweb.core&f=scripts/yui/charts/assets/charts.swf{/devblocks_url}?v={$smarty.const.APP_BUILD}";
This is required when using Yahoo Charts so that it can find the flash File (charts.swf) it uses to render charts in Flash. The value of this variable is enclosed in {devblocks_url} Devblocks Smarty tags. Enclosing them in this special custom Smarty tag causes Devblocks, the framework Cerberus uses, to render a URL based on the properties inside the tag. Basically the c= tells it that it's a plugin resource (i.e. static) file so it should use the resource controller. The p= tells it the name of the plugin where the resource is located. The f= tells it the relative path from the plugin's resource directory where it will find the file. Finally, the v= is simply a cache killing tactic in case this file ever changes in the on a later build of Cerberus, and we want to make sure we see those changes.
Next in the file you should see a general structure of:
var cObj = YAHOO.util.Connect.asyncRequest('GET', "{/literal}{devblocks_url}ajax.php?c=reports&a=action&extid=report.workers.my_workers_list&extid_a=getWorkerOpenTickets{/devblocks_url}{literal}", {
success: function(o) {
...
},
failure: function(o) {},
argument:{caller:this}
}
);
This is the basic way to perform an AJAX GET request using Yahoo UI.
Again you'll notice a {devblocks_url} Smarty tag which generates the URL to access the server function we created earlier in this tutorial. The c= tells it to use the reports controller. The a= is always "action" for AJAX calls. The extid_a= specifies the extension action, which is the name of the function in our PHP extension class, except *without* the "Action" suffix.
The main idea to understand about this section is that if the AJAX call is successful, it will run whatever code is in the "success" function block. And the response text is available as o.responseText.
Inside the success block you'll see:
var resultArr = new Array();
//parse the tab delimited file data from the server into an array of javascript objects
var trimmedText = o.responseText.replace(/^\s+|\s+$/g, '') ;
var rows = trimmedText.split("\n");
for(i=0; i < rows.length; i++) {
var cols = rows[i].split("\t");
resultArr[i] = new Object();
resultArr[i].worker = cols[0];
resultArr[i].ticketcount = cols[1];
}
This section just parses the tab delimited results from the server and stores it into an array of JavaScript objects.
Then we can create a Yahoo UI datasource based on that array in this section:
var myDataSource = new YAHOO.util.DataSource(resultArr, {responseType: YAHOO.util.DataSource.TYPE_JSARRAY});
//for the response schema, just specify the fields from the javascript object we will use for our chart
myDataSource.responseSchema =
{
fields: [ "worker","ticketcount" ]
};
As the code comment indicates, the response schema just specifies which variables in our object will be used in the chart.
Then the size of the chart div is adjusted based on the number of records it needs to display in:
myChart.style.cssText = 'width:100%;height:'+(30+30*resultArr.length);
And finally, the chart is actually created in:
//create the chart
var chart = new YAHOO.widget.BarChart( "myChart", myDataSource,
{
xField: "ticketcount",
yField: "worker",
wmode: "opaque"
//polling: 1000
});
The xField and yField is where you pick which field is on which access. The wmode attribute set is useful to make sure that the chart doesn't force other elements of your template to render underneath your chart.
[edit] View your Chart
Refresh the report in your browser, and you should see a spiffy looking chart.
