How to create SugarCRM graphs

Creating New Sugar CRM Graphs

I've recently been working on a project that required some custom chart additions to SugarCRM. At time of writing the project is using the Community Edition, which according to wikipedia has 85%~ of the code base that the Professional and Enterprise editions do.

I think I'll have to side with Dan Farber from CNET with concerns over how SugarCRM uses the term Open Source - it's not really. Especially considering that I'm working with the charts module, which is one of the main pieces which has been removed from the AGPLv3 license in Sugar 6 (the latest edition).

While being hindered by a lack of documentation and examples with regard to charting, i.e. the main way you view all the data in your system, I've had to reverse engineer the system that is being used. This would appear to be common in various areas of CE, i.e. it's entry point bait that gets you going on a project - then you realise you need Pro or Enterprise - to deliver on the project.

Make sure you have all the functionality asses before you start on a project with this, and if you can't find how to do something in less that two minuets, assume it can't be done.

I think a better route would of been to use something like CodeIgnighter and jpGraph and build a custom reporting interface. Opposed to using the limitations of the Flash graph player that is offered by default. Then again if your off down that route you're already - outside of the bounds of the system - you should be looking at the whole project and what fits best, as it might not be SugarCRM at all.

Though this blog is about explaining how the parts I understand interact, and then how to work with what is there.

Overview

SugarCRM uses the concept of modules, and modules can have "Dashlets" which is what you can see and interact with on your Dashboard. For this example we'll be in the directory :

/var/www/modules/Charts/Dashlets

Fairly self explanatory so far - the dashlets directory for the charts module.

There are several predefined ones in there.

Ultimately the Chart Dashlet is a Flash player that is given the URL to a PHP generated XML file, which then renders it as the graph you see.

The files in a Dashlet folder are named as per the folder and class name (as that's how Sugar finds and loads the relevant classes).

So for our example we'll have :

/var/www/modules/Charts/Dashlets/AccountsTotal

A chart to show the total accounts we have per month.

Inside that directory there are 5 files (yes 5 .....) that make up the data/functions for the chart :

1. {name}.data.php
2. {name}.en_us.lang.php
3. {name}.meta.php
4. {name}.php 
5. {name}.tpl

So for our chart we'll have :

1. AccountsTotal.data.php
2. AccountsTotal.en_us.lang.php
3. AccountsTotal.meta.php
4. AccountsTotal.tpl
5. AccountsTotal.php

Just copy one of the existing directories and files, and then rename them.

AccountsTotal.data.php

This will contain the search fields for the chart, the popup when you click on edit to choose a date etc. For now just rename the first element of the Array to the class name so Sugar can load it .

$dashletData['AccountsTotal']['searchFields'] = array(
...
...
);

AccountsTotal.en_us.lang.php

Same again here, it's just a tokened language file, so rename the key to the class name

$dashletStrings['AccountsTotal'] = array(
...
...
);

AccountsTotal.meta.php

The meta file is used (for what we're concerned with) by the Dashlet selector - the pop up where you select what Dashlets you want to add to your Dashboard. If you want to check the options the images are stored in :

/var/www/themes/default/images/

i.e. icon_Charts_GroupBy_32.gif

AccountsTotal.tpl

The template file you can leave for now, it's the HTML for the configure popup, i.e. where you choose the dates to feed to the graph logic.

AccountsTotal.php

This is the main file for the graphs, the C of the MVC world.

This is a class file that extends the Sugar class of - DashletGenericChart - so rename accordingly.

class AccountsTotal extends DashletGenericChart

The constructor and displayOptions can be left, and just call the parent methods. The $options that are passed to the constructor are the details that you set in the search fields.


public function __construct($id, array $options = null)
{
    parent::__construct($id,$options);
}
   
public function displayOptions()
{
  return parent::displayOptions();
}


The final two methods are the constructQuery() and display(), the query function is just an abstraction for generating the SQL, applying variables from globals or passed in options etc.

The display method is the main area you need to be concerned with, overriding the parent and adding in all the XML generation, saving then loading of flash player and passing of XML URL.

There is an include to get the chartDefs, the the actual sugarchart is created and properties set :


require_once('include/SugarCharts/SugarChart.php');

$sugarChart = new SugarChart();

$sugarChart->setProperties(
    /* Title */    "My charts title",
    /* Subtitle */ "Some relevant sub title",
    /* Type */     $chartDef['chartType']
);


The signature is :

setProperties($title, $subtitle, $type, $legend = 'on', $labels = 'value', $print = 'on')

There are some defaults of the chat class set in the original, that I've just left :


$sugarChart->base_url = $chartDef['base_url'];
$sugarChart->group_by = $chartDef['groupBy'];
$sugarChart->url_params = array();


Now to get the data & construct the XML I have deviated from Sugar, as after decompling the SWF file and going though the XML generation (that is string building opposed to any use of native PHP XML) I found a much easier and quicker route to getting the job done.

There are also various bugs and inconsistencies around :

$sugarChart->sortData();

So I didn't use it, I wrote my own XML handler available (here), it constructs the XML using native PHP functions to do so.

All that remains is to get the data, and sort it, getData() seems to work, so we can use that.

$sugarChart->getData($this->constructQuery());

This places the data into an array :

$sugarChart->data_set

All that remains to sort the query data into rawData then use the new XML class to build the desired outcome and then pass it to the usual Sugar methods for saving the XML and returning the HTML to display the Dashlet.

The final 3 lines of the method do all of that :


 $sugarChart->getXMLFileName(...
 $sugarChart->saveXMLFile(...
 return $this->getTitle(...



As per SugarCRM community killing conventions I have omitted  any comments and anything that could be helpful from the source ;)

And here is the display method :

public function display()
{
    $currency_symbol = $GLOBALS['sugar_config']['default_currency_symbol'];

    if ($GLOBALS['current_user']->getPreference('currency'))
    {
        require_once('modules/Currencies/Currency.php');
        $currency = new Currency();
        $currency->retrieve($GLOBALS['current_user']->getPreference('currency'));
        $currency_symbol = $currency->symbol;
    }

    require("modules/Charts/chartdefs.php");

    $chartDef = $chartDefs['outcome_by_month'];

    require_once('include/SugarCharts/SugarChart.php');

    $sugarChart = new SugarChart();

    $sugarChart->setProperties(
        /* Title */    "Total GLA By Month :: {$this->startDate} to  {$this->endDate}",
        /* Subtitle */ "Month view",
        /* Type */     $chartDef['chartType']
    );

    $sugarChart->base_url = $chartDef['base_url'];
    $sugarChart->group_by = $chartDef['groupBy'];
    $sugarChart->url_params = array();

    $sugarChart->getData($this->constructQuery());
       
    $rawData = array();
    $month = array (
        1 => 'Jan', 2 => 'Feb', 3 => 'Mar', 4 => 'Apr', 5 => 'May', 6 => 'Jun',
        7 => 'Jul', 8 => 'Aug', 9 => 'Sep', 10 => 'Oct', 11 => 'Nov', 12 => 'Dec'
    );
       
    for($i = 1; $i <= 12; $i++)
    {
        $rawData[$i] = array('title' => $month[$i], 'value' => 0, 'label' => 0);
    }
       
    $value = 0;
       
    foreach ($sugarChart->data_set AS $id => $details)
    {
        $value = ($details['total'] > 0 ? $details['total'] : 0);
               
        $rawData[$details['month']]['value'] = $details['total'];
        $rawData[$details['month']]['label'] = $details['total'];
    }
       
    $options['properties'] = array (
        'title'    => "Accounts By Month",
        'subtitle' => 'Months',
        'type'     => 'bar chart',
        'legend'   => 'off',
        'labels'   => 'value'
    );

    $options['ystep']= 100;
    
    $x = new XMLGraph($options);

    if ($value)
    {
        $total = $this->previousAccountTotal();
       
        foreach ($rawData AS $monthId => $monthData)
        {
            $total += $monthData['value'];
                       
            if ($monthData['value'] AND $total)
            {
                $monthData['value'] = $total;
                $monthData['label'] = $total;
            }      
                       
            $x->addData($monthData);
        }

        $testXML = $x->asXML();
    }
    else
    {
        $testXML = $x->emptyXML();
    }
       
    $sugarChart->getXMLFileName($this->id);

    $sugarChart->saveXMLFile($xmlFile, $testXML);

    return $this->getTitle('

') . '

' . $sugarChart->display($this->id, $xmlFile, '100%', '460', false) . '

<br />';
}

Conclusion

A messy work around for sure, though gets the job done for the various graph types I've deciphered so far. I'll add more as I do and likely create a whole repo for a default graph.

As for calling SugarCRM open source ....... it might be stepping on or right up to the line in legal terms - though I don't think it is wholly OSS in the spirit of the movement.

I hope it decides what camp it is in and announces that sooner rather than later.