Yii Cgridview Footer With Sum Of Column Values

In the cgridview add following lines


'columns'=>array(

		................

		array(

		'name'=>'column name',

		'type'=>'text',

		'footer'=>$model->getTotals($model->search()->getKeys()),

		),

		..................

		),



we are passing currently displayed primary keys as parameter to getTotals function [in array format]

kindly create a function in model like below


public function getTotals($ids)

	{

		$ids = implode(",",$ids);

		

		$connection=Yii::app()->db;

		$command=$connection->createCommand("SELECT SUM(columnname)

											FROM `tablename` where id in ($ids)");

		return "Total Rs: ".$amount = $command->queryScalar();

	}

nice

Dear Friend

Let us have a model :Wage.

And we are having a field days.

admin.php




.......other columns......

array(

	'name'=>'days',

	'footer'=>"Total: ".$model->fetchTotalDays($model->search()->getKeys()),

		),

........other columns......



The model function in Wage.php




public function fetchTotalDays($keys)

{

	$wages=self::model()->findAllByPk($keys);

	$days=0;

	foreach($wages as $wage)

		$days+=$wage->days;

	return $days;

}	



Actually CActiveDataProvider::getKeys is wrapper around protected function CActiveDataProvider::fetchKeys.

CActiveDataProvider::fetchKeys get primaryKeys in array from CActiveDataProvider::getData.

Instead of using CActiveDataProvider::getKeys,we can use CActiveDataProvider::getData.

the column declaration in admin.php.




array(

	'name'=>'days',

	'footer'=>"Total: ".$model->fetchTotalDays($model->search()->getData()),

		),



The utility function in the model.




public function fetchTotalDays($records)

{

	$days=0;

	foreach($records as $record)

		$days+=$record->days;

	return $days;

}	



Regards.

[color="#006400"]/* Moved from "General Discussion for Yii 1.1.x" to "Tips, Snippets and Tutorials" */[/color]

Good stuff. Thanks.

You can also use YiiBooster’s Extended Gridview as well to perform this function and many more.

Perfect and simple, also could be extended to more functionalities.

Thanks, great tutorial!!!

Easy e usefull tutorial!!! Congrats!

If anyone need to get totals from multiple columns you can use code like this:

this chunk of code goes to your view file


'columns'=>array(

                ..........

		array(

			'name'=>'age',

			'footer'=>'Total: ' . $model->getTotal($model->search()->getData(), 'age'),

		),

		array(

			'name'=>'weight',

			'footer'=>'Total: ' . $model->getTotal($model->search()->getData(), 'weight'),

		),

		..........

	),

and this one goes to your model file


	public function getTotal($records, $column)

	{

		$total = 0;

		foreach ($records as $record) {

			$total += $record->$column;

		}

		return $total;

	}

If anyone need to get totals from multiple columns and it should be having more performance if your data is very large :


 

$Totals=$model->getTotal($model->search()->getData(), array('age','weight'));


$columns=array(

                ..........

                array(

                        'name'=>'age',

                        'footer'=>'Total: ' . $Totals['age'],

                ),

                array(

                        'name'=>'weight',

                        'footer'=>'Total: ' .$Totals['weight'],

                ),

                ..........

        );

// in CGridView 

'columns' =>$columns




and this one goes to your model file




 public function getTotal($records, $columns)

        {

           if(!is_array($columns){

              $columns=array($columns);

           }              

                $total = array();

                foreach ($records as $record) {

                        foreach ($columns as $column) {

                                  if(!isset($total[$column]))$total[$column]=0;

                                  $total[$column] += $record->$column;

                        }

                }

                return $total;

        }




thank it’s work!