Thursday, March 3, 2011

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.

12 comments:

  1. Hi Jeremy, I am reviewing this step by step new SugarCRM chart guide but I am lost in the XML data creation:
    1.I have to delete the construct function?
    2. I have to replace the display function?
    3. Where has to placed the XMLGraph.class.php?

    ReplyDelete
  2. It's on gitHub here :

    https://github.com/JeremyHutchings/SugarCRM-Chart-XML-generator

    I linked to it in the post, albeit not very clearly.

    ReplyDelete
  3. I followed your approach and I was able to create a few charts. Thank you very much for this.

    ReplyDelete
  4. Hallo Jeremy!
    Sugar 6.2.1 gives up at $total = $this->previousAccountTotal(); {because it can't find previousAccountTotal()}. I made all the usual checks but can't find anything referring to it either. A hint would be appreciated.

    Kind regards, and many thanks to you for publishing this stuff
    PFH

    ReplyDelete
  5. Hallo Jeremy!
    Stepping through in Eclipse showed this change got things working in Sugar 6.2.1: $xmlFile = $sugarChart->getXMLFileName($this->id); // JH: $sugarChart->getXMLFileName($this->id);

    Any hints about where to find previousAccountTotal()? (My query from yesterday)
    Many thanks,
    PFH

    ReplyDelete
  6. previousAccountTotal() is a local data access function for getting the data you actually want to graph, so just have a function that returns an array, i.e. :

    previousAccountTotal()
    {
    return array( 1=> 10, 2 => 20, 3 => 30);
    }

    For testing & dev and once you have it all going, put some SQL in there to get what you actually want.

    ReplyDelete
  7. Hi please using the community 6.5 version , am able to follow through till you get to the accountTotal.php page which seems different from what you have there , can you please help walk me through it because i really need help

    ReplyDelete
  8. Any Chance you could post each of the 5 files:

    AccountsTotal.data.php
    AccountsTotal.en_us.lang.php
    AccountsTotal.meta.php
    AccountsTotal.tpl
    AccountsTotal.php

    Or put them in the Git Repo?

    We could then push back a 6.5 version?

    ReplyDelete
    Replies
    1. I don't have the original AccountsTotal* files as they are with the client.

      Though I've included the originals that I based the ones I create from.

      Delete
  9. Hi Jeremy,

    Thanks for sharing these scripts. I have dowloaded all scripts from https://github.com/JeremyHutchings/SugarCRM-Chart-XML-generator. But its seems like not working with SugarCRM 6.5.12. Pl suggest the code for custom graph like you have shared on version 6.5.12.

    Thanks in advance.

    ReplyDelete
    Replies
    1. I was contemplating keeping up a version of a truly open source graphing plugin for SugarCRM. Though given the direction they have taken, which I believe to be very much against the spirit of open source development, I don't want to commit my time to supporting their bottom line.

      Personally I wouldn't use SugarCRM again, and would look to another truely open source project that A) understands OSS and B) actually shares and engages with the community.

      As for graphing, I've had a lot of success with http://jpgraph.net and am now investigating http://pchart.sourceforge.net and options for replacements to the functionality I was doing on this project a few years ago.

      I can see why they removed the charts on purpose :

      http://en.wikipedia.org/wiki/SugarCRM#License

      Delete