Difference between #5 and #3 of Keeping a running total in a CGridView

This tip created in conjunction with IRC #yii channel users **emilsedgh** and
**tydeas** - thanks!

When using [CGridView] to display rows of data, each one is independent of the
other - this is normally what we want. But a cases requiring a running total
(say, the current balance in a bank account register or a running inventory
count) are more tricky because of no shared context.

This How-to introduces the [CGridColumn] component and how to extend it to
provide this running total (which may inspire other uses as well).

The sample table
----------------
Our example will use a simple inventory table (we'll omit the Model and
Controller code, as they are straightforward):
~~~
[sql]
CREATE TABLE inventory (
    id          INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name        VARCHAR(64),
    quantity    INTEGER
);

insert into inventory values ( 1, 'Beer',  4 );
insert into inventory values ( 2, 'Wine',  2 );
insert into inventory values ( 3, 'Vodka', 7 );
~~~
Our goal is to show this in a table with all the product detail, plus a running
total of the quantities in a fourth column.

Using CGridColumn
-----------------
To do this, we define that fourth column with our own helper class,
`TotalColumn`. This extends the grid's column class that does the actual
rendering of cell data, and this class has a private variable with that running
total.

Our view code is:
~~~
[php]
<?php
// protected/views/inventory/index.php

Yii::import('zii.widgets.grid.CGridColumn');

class TotalColumn extends CGridColumn {

    private $_total = 0;

    public function renderDataCellContent($row, $data) { // $row number is
ignored

        $this->_total += $data->quantity;

        echo $this->_total;
    }
}

$this->widget('zii.widgets.grid.CGridView', array(
    'dataProvider' => $dp,   // provided by the controller
    'columns' => array(
        'id',
        'name',
        'quantity',
        array(
            'header' => 'Total',
            'class'  => 'TotalColumn'
        )
)));
~~~
This produces a grid with four columns, the last being the running total: just
like we wanted.

Some points on this approach:

* The `class` name is a __PHP__ class, not a __CSS__ class, and this creates a
new instance of `TotalColumn`, one time per `CGridView`, not one time per row.
* Because `CGridColumn` is part of the Zii hierarchy and not in the usual import
path, we must import the class explicitly so Yii can use it.
* It's perfectly acceptable to define this helper class directly in the view.
Because the attribute name (`quantity`) is coded directly into the class, this
limits its reuse. The next section talks about making this a more general
extension.
* If the `CGridView` is in some kind of loop within the view, a new
`TotalColumn` object is created each time around, which resets the running
total. No static/global variables to get in the way.
* All the _other_ columns defined are of the type `CGridColumn` too, created
automatically by `CGridView`; they can each be extended in their own way.

Generalizing it
---------------
Because the original version hardcodes the attribute name
(`$data->quantity`), we can't reuse this generally, but we can expand the
class a bit to allow assignment of the attribute name as part of the object's
creation.

In the same `array('class'=>'TotalColumn')` found in the `CGridView`, we can
add other attribute assignments, including get/set functions we define in the
helper class. By adding `setAttribute()` and `getAttribute()` methods, we can
make our column totalizer more general and move it into the
`protected/components/` folder (and out of the view code).
~~~
[php]
<?php

// protected/components/TotalColumn.php

Yii::import('zii.widgets.grid.CGridColumn');

class TotalColumn extends CGridColumn {

    private $_total = 0;
    private $_attr  = null;

    public function getAttribute()
    {
        return $this->_attr;
    }
    public function setAttribute($value)
    {
        $this->_attr = $value;
    }

    public function renderDataCellContent($row, $data) {
        $this->_total += $data->{$this->attribute};

        echo $this->_total;
    }
}
~~~
This done, we can remove the helper class from the view code and add the
attribute name as one of the initializers:
~~~
[php]
<?php

$this->widget('zii.widgets.grid.CGridView', array(
    'dataProvider' => $dp,
    'columns' => array(
        'id',
        'name',
        'quantity',
        array(
            'header'    => 'Total',
            'class'     => 'TotalColumn',
            'attribute' => 'quantity',         // THIS IS NEW
        )
    )));
~~~
Running totals in SQL
---------------------
It **is** possible to do this in SQL directly, but it's very computationally
expensive and doesn't fit in well with the Yii way of doing things. But because
it's interesting - and people inevitably ask about it - the same query can be
performed this way:
~~~
[php]
mysql> SELECT a.id,
    ->         a.name,
    ->         a.quantity,
    ->         ( SELECT SUM( quantity )
    ->           FROM inventory b
    ->           WHERE b.id <= a.id) AS running_qty
    ->    FROM inventory a
    ->    ORDER BY id;
+----+-------+----------+-------------+
| id | name  | quantity | running_qty |
+----+-------+----------+-------------+
|  1 | Beer  |        4 |           4 | 
|  2 | Vodka |        2 |           6 | 
|  3 | Milk  |        7 |          13 | 
+----+-------+----------+-------------+
3 rows in set (0.00 sec)
~~~
This method gets exponentially slower as the number of rows increases. This is
**not** recommended.

A little bit more
-----------------

### Transaction example

In the above example refers we had an inventory table, instead of it if we may
have a transaction table like:

~~~
[mysql]
CREATE TABLE transaction (
    id          INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
    quantity    INTEGER,
    date        TIMESTAMP,
);
~~~

That would look like:

~~~

+----+----------+------------+
| id | quantity | date       |
+----+----------+------------+
|  1 | 10       | 2011-03-01 |
|  2 | 10       | 2011-03-10 |
|  3 | -1       | 2011-03-19 |
|  4 | -2       | 2011-03-25 |
|  5 | -9       | 2011-04-01 |
+----+----------+------------+

~~~

The above simple example will work here as well with no problem. But what will
happen if the user wants to add a from date to date condition in the criteria
that init the dataprovider for the view?
Because the TotalColumn will always start with $_total = 0; the result will be
false. What we can do is alter the TotalColumn code and add setter and getter
for the total value like:

~~~
[php]

<?php
 
// protected/components/TotalColumn.php
 
Yii::import('zii.widgets.grid.CGridColumn');
 
class TotalColumn extends CGridColumn {
 
    private $_total = 0;
    private $_attr  = null;

    public function getTotal()
    {
        $this->_total;
    }
    public function setTotal($value)
    {
        $this->_total = $value;
    }
 
    public function getAttribute()
    {
        return $this->_attr;
    }
    public function setAttribute($value)
    {
        $this->_attr = $value;
    }
 
    public function renderDataCellContent($row, $data) {
        $this->_total += $data->{$this->attribute};
 
        echo $this->_total;
    }
}

~~~

And doing the following will result in a proper transaction grid:

~~~
[php]

$criteria = new CDbCriteria();
$criteria->addCondition('date > 2011-03-20');

$dp = new CActiveDataProvider('Transaction', array('criteria'=>$criteria));

$this->widget('zii.widgets.grid.CGridView', array(
    'dataProvider' => $dp,
    'columns' => array(
        'id',
        'name',
        'quantity',
        array(
            'header'    => 'Total',
            'class'     => 'TotalColumn',
            'attribute' => 'quantity',         // THIS IS NEW
            'total'     => 19, // The true total for date < 2011-03-20
        )
    )));
~~~

Extra
-----

Its a common case that you will need a numeric format for this "running
total" field. Because we extend <code>CGridColumn</code> we
don't have access to the
<code>[CDataColumn::$type](http://www.yiiframework.com/doc/api/1.1/CDataColumn#type-detail).
</code>
So, if you want to be able to use <code>'type'=>'number'</code>
so the running total has a numeric format you must alter the <code>class
TotalColumn</code> like this 
~~~
[php]
class TotalColumn extends CGridColumn
{
    ...
    public $type = 'number'; //If you want to be able to access it.
    ...
    public function renderDataCellContent($row, $data) {
        $this->_total += $data->{$this->attribute};
 
        echo (isset($this->type)) ?  $
$this->grid->getFormatter()->format($this->_total, $this->type) :
this->_total;
    }
}
~~~