Skip to content

This PHP package contains a HTML pivot table. You provide it with a data set and the list of columns and rows and it will display a nice pivot table.

License

Notifications You must be signed in to change notification settings

thecodingmachine/html.widgets.statsgrid

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

20 Commits
 
 
 
 
 
 
 
 
 
 

Repository files navigation

StatsGrid: a PHP pivot table

StatsGrid is a PHP library that let's you generate HTML pivot tables from any dataset. You give the data to be rendered as an array to StatsGrid and it will render the HTML. For instance, you can give this array:

$data = array(
	array("country"=>"US", "city"=>"Chicago", "year"=>2009, "month"=>"February", "sales"=>12, "profit"=>2),	
	array("country"=>"US", "city"=>"Chicago", "year"=>2009, "month"=>"April", "sales"=>12, "profit"=>2),	
	array("country"=>"US", "city"=>"NY", "year"=>2009, "month"=>"May", "sales"=>15, "profit"=>5),
	array("country"=>"US", "city"=>"Baltimore", "year"=>2009, "month"=>"April", "sales"=>42, "profit"=>3),
	array("country"=>"US", "city"=>"Baltimore", "year"=>2010, "month"=>"April", "sales"=>24, "profit"=>4),
	array("country"=>"FR", "city"=>"Paris", "year"=>2010, "month"=>"May", "sales"=>12, "profit"=>2),
	array("country"=>"FR", "city"=>"Paris", "year"=>2010, "month"=>"June", "sales"=>12, "profit"=>2),	
);

and StatsGrid can generate this kind of reports:

20092010
FebruaryAprilMayAprilMayJune
SalesProf.SalesProf.SalesProf.SalesProf.SalesProf.SalesProf.
USChicago122122
NY155
Baltimore423244
FRParis122122

Installing StatsGrid:

StatsGrid comes as a composer package (the name of the package is mouf/html.widgets.statsgrid)

Not used to Composer? The first step is installing Composer. This is essentially a one line process:

curl -s https://getcomposer.org/installer | php

Windows users can download the phar file here: [http://getcomposer.org/download/](install composer). Then create a composer.json file at the root of your project:

{
    "require": {
        "mouf/html.widgets.statsgrid": "~1.0"
    }
}

and finally, run

php composer.phar install

Usage sample:

To generate a statsgrid, you need several things:

  • obviously, a dataset (the raw data that you will render)
  • you will also need a set of row and a set of column descriptors (describing what should be in row and what should be in column)
  • finally, you need to decide what values are to be displayed in the grid

StatsGrid is compatible with Mouf so you can completely define a grid using Mouf's graphical interface.

Not using Mouf? You should! But here is a sample code base anyway:

// Let's load the autoloader
require 'vendor/autoload.php';

// Let's import all required classes
use Mouf\Html\Widgets\StatsGrid\StatsGrid;
use Mouf\Html\Widgets\StatsGrid\StatsColumnDescriptor;
use Mouf\Html\Widgets\StatsGrid\StatsValueDescriptor;
use Mouf\Html\Widgets\StatsGrid\Aggregate\SumAggregator;

// Let's define the data to be displayed (usually, you will get this from a database using GROUP BY statements)
$data = array(
	array("country"=>"US", "city"=>"Chicago", "year"=>2009, "month"=>"February", "sales"=>12, "profit"=>2),	
	array("country"=>"US", "city"=>"Chicago", "year"=>2009, "month"=>"April", "sales"=>12, "profit"=>2),	
	array("country"=>"US", "city"=>"NY", "year"=>2009, "month"=>"May", "sales"=>15, "profit"=>5),
	array("country"=>"US", "city"=>"Baltimore", "year"=>2009, "month"=>"April", "sales"=>42, "profit"=>3),
	array("country"=>"US", "city"=>"Baltimore", "year"=>2010, "month"=>"April", "sales"=>24, "profit"=>4),
	array("country"=>"FR", "city"=>"Paris", "year"=>2010, "month"=>"May", "sales"=>12, "profit"=>2),
	array("country"=>"FR", "city"=>"Paris", "year"=>2010, "month"=>"June", "sales"=>12, "profit"=>2),	
);

// The StatsGrid object is the main object used to display the grid
$grid = new StatsGrid();

// Let's associate the data to the grid
$grid->setData($data);

// Now, let's define 2 row descriptors: COUNTRY and CITY
$countryRow = new StatsColumnDescriptor("country");
$cityRow = new StatsColumnDescriptor("city");
// We associate these column descriptors to the grid
$grid->addRow($countryRow);
$grid->addRow($cityRow);


// Now, let's define 2 column descriptors: YEAR and MONTH
$yearColumn = new StatsColumnDescriptor("year");
$monthColumn = new StatsColumnDescriptor("month");
// Do not sort the month column alphabetically (see sorting paragraph below)
$monthColumn->setSort(false);
// We associate these column descriptors to the grid
$grid->addColumn($yearColumn);
$grid->addColumn($monthColumn);

// Let's define the values to be displayed in the table
$salesValue = new StatsValueDescriptor("sales", "Sales");
$grid->addValue($salesValue);

// Finally, let's print the table
$grid->toHtml();

Adding aggregation (sums/means...):

Presenting data in a pivot table is nice, but often, you will find out you want to display sums or means of the data at the bottom of the table. StatsGrid let's you aggregate data (performing sums/means...) on any column or any row. This way, you can perform sums / subsums, etc... the way you want.

In order to aggregate data, you just need to call the addAggregator method and pass a valid aggregator object.

// Let's add 4 aggregators (sub and subsums on columns and on rows)
$grid->addAggregator(new SumAggregator($countryRow, $salesValue, "Total Sales"));
$grid->addAggregator(new SumAggregator($cityRow, $salesValue, "Total city"));
$grid->addAggregator(new SumAggregator($monthColumn, $salesValue, "Total month"));
$grid->addAggregator(new SumAggregator($yearColumn, $salesValue, "Total year"));

In the sample above we decide we want to sum data by country/city and year/month. Therefore, we create 4 SumAggregator objects. For each aggregator we need:

  • The column/row we will be summing upon
  • The object representing the value we will be summing
  • The title of the aggregator

By just adding those 4 lines, we will get this:

20092010Total year
FebruaryAprilMayTotal monthAprilMayJuneTotal month
USChicago121224024
NY1515015
Baltimore4242242466
Total city12541581240024105
FRParis012122424
Total city0000012122424
Total Sales1254158124121248129

Displaying several values:

If you look at the dataset we have been working on, we have 2 sets of values: "sales" and "profit". So far, we have been displaying only the "sales" value. However, stats grid can perfectly handle displaying 2 values next to each other.

For instance, you could write:

// Let's define the values to be displayed in the table
$salesValue = new StatsValueDescriptor("sales", "Sales");
$profitValue = new StatsValueDescriptor("profit", "Prof.");
$grid->addValue($salesValue);
$grid->addValue($profitValue);

This will displays The 2 values in 2 columns. If you want values to be displayed in rows rather in column, just add:

// There are several value descriptor, let's put those in rows.
$grid->setValuesDisplayMode(StatsGrid::VALUES_DISPLAY_VERTICAL);

Sorting columns/rows headers

In the exemple above, the years are sorted numerically (2009, 2010...) However, we might very well want to alter this order. Maybe we want to store the result in reverse order. Or maybe we want the result not to be sorted at all (and be displayed in the order of the origin dataset).

For these use case, there is the setSort method. This function takes a boolean (true|false), or a callable function that will be called to compare 2 items (just like the usort PHP function).

Here is an example to sort the columns in reverse order:

// Let's sort the YEAR columns in reverse order (2010, 2009...)
$yearColumn->setSort(function($a, $b) { return  $b-$a; });

Styling statsgrids:

Statsgrid comes with a default CSS stylesheet. You can find it in css/dist/statsgrid.css.

Using composer? Add this to your theme:

<link rel="stylesheet" type="text/css" href="[myproject]/vendor/mouf/html.widgets.statsgrid/css/dist/statsgrid.css" />

By default the grid is in blueish tones.

You can change the main CSS class of the grid using:

// Changes the design of the grid
$grid->setCssClass("redstatsgrid");

Those are valid CSS classes defined in the statsgrid package:

  • bluestatsgrid
  • redstatsgrid
  • greenstatsgrid
  • greystatsgrid
  • yellowstatsgrid
  • purplestatsgrid
  • cyanstatsgrid

You can of course provide your own class to suit your needs. The css/src/statsgrid.scss file contains the SASS file that has been used to generate those themes easily.

About

This PHP package contains a HTML pivot table. You provide it with a data set and the list of columns and rows and it will display a nice pivot table.

Resources

License

Stars

Watchers

Forks

Packages

No packages published