Geckoboard & Magento custom push widgets (1)

Over the past few days I've been helping a client set up a Geckoboard (www.geckoboard.com) - if you haven't comes across Geckoboard yet you should take a look, as it's a fantastic solution for compiling information from dozens of different sources and displaying it on a number of dashboards using a number of different widgets. Whilst it does come with out of the box integration for a load of different services, hopefully I'll be forgiven for saying that its integration with Magento is a little...well...weak. Whilst there are a number of other posts out there on how to create pull widgets for Geckoboard, I've only come across a small handful which deal with Magento, and they're not without their problems.

The first issue was that a number of the widgets simply didn't work, and were pretty damned clunky when they did work. The second (and more important) issue was security - the widgets were, by necessary, publicly accessible php files, so that Geckoboard could call upon them for data, so if someone were to know the location of the scripts and the right syntax, they could get the scripts to reply with your headline sales information. Whilst the logical answer would be to put the widgets in their own folder and then use .htaccess to lock down access to that folder to Geckoboard's IP address, for some reason that didn't work - whilst the syntax being used was right, any attempts to do so meant that the widget failed on the Geckoboard dashboard.

The solution was to move away from pull widgets, and instead use Geckoboard's custom widgets to push information from Magento to Geckoboard - the relevant files could then be safely locked down (they would only output data to Geckoboard anyway, but would be locked down to ensure that repeated calls to them wouldn't overwhelm the server by getting it to repeatedly answer requests that involved parsing large amounts of data).

This solution has a number of other benefits, chief amongst them that it can be applied to pretty much any service where an API is available. Zendesk, for example, is another key consideration for this client, but again Geckoboard's integration out of the box is pretty weak, despite Zendesk having a pretty serviceable API. By using custom push widgets, you can do the dog-work of getting the information out of Magento, Zendesk or wherever yourself, then parse that information into the format the Geckoboard requires and push to it.

The below script is one example - the end result of which is information pushed to a pair of widgets to generate sales figures from within Magento for a specific date range, along with a comparison against the relevant preceding period, and output them to two custom Geckoboard widgets. I've explained the bits and bobs along the way, with a download link for the full version at the bottom.

There are two steps - the first is to set up the custom push widgets in our Geckoboard dashboard, the second is to then set up the script which gets the information from Magento and pushes it to the widgets in the required format.

In Geckoboard, set up two new widgets, both of the 'Number and Secondary Stat' type from the 'Custom Widgets' section - one of these will show the number of orders, the other will show the value of orders.

Then change the widget type to 'push' and make a note of the widget Push URL. You'll also need to get your API key from the settings section of your Geckoboard account.

Then put together a PHP script to go in a sub-directory of your Magento installation. Within that script, first up, let's set ourselves up with access to Magento.

<?php
require_once("../app/Mage.php"); // amend this path as appropriate
$app = Mage::app('');

Then let's set the time periods that we're looking at (cs / ce for current period start and current end, ps / pe for previous period start and end) formatted into the format that Magento requires. In this instance we're looking at sales today (ie. from midnight to where we're at now) versus sales in the same time period yesterday, but this can be whatever you want, really.

$midnight = strtotime('midnight');
$cs = date("Y-m-d H:i:s", $midnight);
$ce = date("Y-m-d H:i:s"); 
$midnightyesterday = strtotime('midnight yesterday');
$ps = date("Y-m-d H:i:s", $midnightyesterday);
$nowyesterday = strtotime('-1 day');
$pe = date("Y-m-d H:i:s", $nowyesterday);

Then let's load the two collections containing the current number of orders - one for the current period and one for the previous period against which we're going to compare it.

$sales = Mage::getModel('sales/order')->getCollection()->addAttributeToFilter('created_at', array('from' => $cs,'to' => $ce))->addAttributeToSelect('grand_total');
$sales2 = Mage::getModel('sales/order')->getCollection()->addAttributeToFilter('created_at', array('from' => $ps,'to' => $pe))->addAttributeToSelect('grand_total');

Then let's count the number of sales in each period for one widget

$currentSales =  $sales->count();
$previousSales = $sales2->count();

And then loop through both collections' orders totalling the grand total, adding as we go, to get the turnover in each period.

$currentTurnover = 0;
foreach($sales as $order) { $currentTurnover += $order->getGrand_total(); }
$previousTurnover = 0;
foreach($sales2 as $order2) { $previousTurnover += $order2->getGrand_total();}

Now that's done, we can push the relevant data to the widgets. The first widget contains the current day's sales against sales the previous day to the same point in the day. We're pushing a JSON array via CURL, but you could do it with XML - Geckoboard allows for both.

Note that you'll need your API key from Geckoboard (for authentication purposes) as well as the widget ID of the widget(s) you set up at the beginning of the process.

$values = '{"api_key":"YOUR API KEY HERE","data":{"item":[{"text":"","value":'.$currentSales.'},{"text":"","value":'.$previousSales.'}]}}';
curl_setopt_array($ch = curl_init(), array(
  CURLOPT_URL => "https://push.geckoboard.com/v1/send/YOUR WIDGET ID HERE",
  CURLOPT_POSTFIELDS => $values,
  )
);
curl_exec($ch);
curl_close($ch);

Then repeat the task with the second widget, to which we're pushing the current day's turnover against the previous day's turnover to the same point in the day.

$values = '{"api_key":"YOUR API KEY HERE","data":{"item":[{"text":"","value":'.$currentTurnover.'},{"text":"","value":'.$previousTurnover.'}]}}';

curl_setopt_array($ch = curl_init(), array(
  CURLOPT_URL => "https://push.geckoboard.com/v1/send/YOUR WIDGET ID HERE",
  CURLOPT_POSTFIELDS => $values,
  )
);
curl_exec($ch);
curl_close($ch);

And that's it, so finish off the PHP.

?>

The result? Well, if you tweak the script into three different variants, one for daily, one for weekly and one for monthly figures, you'll get the reporting you need.

The possibilities are pretty much endless - with 10 or so widgets available, and pretty much any information you can get out of Magento at your disposal, it's just the start. Over the next few weeks I hope to be blogging a few more custom widgets that we're putting together on Geckoboard.