CGridView Filter

itmagetan · February 28, 2011, 1:57am

hello all… I would like to know more about CGridView usage (or manipulation maybe). How to update a div ID in my view file depends on a dropdown filter operation in the CGridView ? I need to count a money column (integer data type) and display it in a div ID below the CGridView and change depends on user selection in dropdown filter. How to do it ? I already search the reference but no clue yet ?

mpluss · February 28, 2011, 10:16am

Hi,

To use a dropdownlist in cgridview filter :

if the field is a foreign key :




		array(

			'name'=>'RefInvoiceStatusID',

			'filter'=>CHtml::listData(InvoiceStatusTable::model()->findAll(), 'InvoiceStatusID', 'InvoiceStatus'),

		),

else




		array(

			'name'=>'InvoiceStatus',

			'filter'=>array('En Cours'=>'En Cours','Partiel'=>'Partiel','Soldee'=>'Soldée','Retard'=>'Retard'),

		),

for other needs, can you explain more ?

itmagetan · February 28, 2011, 9:14pm

Hi Mahdi, below is my view file code :




<?php $this->widget('zii.widgets.grid.CGridView', array(

	'id'=>'data-grid',

	'dataProvider'=>$model->search(),

	'filter'=>$model,

	'columns'=>array(

		'id',

		array(

			'name'=>'cat_id',

			'value'=>'$data->cat_id',

			'filter'=>LookupCat::items('1'),

		),

		'item',

		'description',

		'price',

		array(

			'class'=>'CButtonColumn',

		),

	),

)); 

?>


<div id="total"></div>


<?php

Yii::app()->clientScript->registerScript('search', "

$('select[name=\'Data[cat_id]\']').change(function(){

	var n = $('select[name=\'Data[cat_id]\']').val();

	$('#total').html(n);

	return false;

});

");

?>

I want my DIV (id=total) count total of the item’s price on filtered category selected by user.

I’ve added a jquery script to get category dropdown value, it works but then stop the CGridView filter script. I couldn’t find another way yet. Could you give me a working example?

mdomba · March 1, 2011, 9:04am

You can use the footer property of the CDataColumn… check this thread - http://www.yiiframework.com/forum/index.php?/topic/9636-cgridview-totals-or-summary-row/

itmagetan · March 2, 2011, 2:07am

Thanks mdomba, i have checked it out, but there was still a confusion at me on how to get all rows (displayed and undisplayed row) returned by $model->search(). Any working example would be much appreciated. Thanks.

mdomba · March 2, 2011, 7:43am

It will be simpler if you explain what have you done until now… post your current code… and explain what you need to accomplish… then me or anybody else can give you proper suggestions or ideas on how to solve it…

itmagetan · March 2, 2011, 9:17am

Ok…sorry for unclear question. Below i pasted all my codes and explain my objective.

This is my CGridView pictures with added information (Total) on the below (in Indonesian language) :

Page 1

Page 2

Here is my view files (admin.php) :




<?php $this->widget('zii.widgets.grid.CGridView', array(

	'id'=>'data-grid',

	'dataProvider'=>$model->search(),

	'filter'=>$model,

	'ajaxUpdate'=>false,

	'columns'=>array(

                array(

		'header'=>'No.',

		'value'=>'$this->grid->dataProvider->pagination->currentPage*

                          $this->grid->dataProvider->pagination->pageSize + $row+1',

		),

		array(

		'name'=>'id_unit',

		'value'=>'$data->unit->description',

		'filter'=>LookupUnit::items('1'),

		),

		array(

		'name'=>'id_unit_sub',

		'value'=>'$data->sub_unit->description',

		'filter'=>LookupUnitSub::itemsFilter('1'),

		),

		array(

		'name'=>'dari',

		'value'=>'date("d-m-Y",strtotime($data->dari))',

		'filter'=>FALSE,

		),

		array(

		'name'=>'sampai',

		'value'=>'date("d-m-Y",strtotime($data->sampai))',

		'filter'=>FALSE,

		),

		array(

		'name'=>'id_kategori_diklat',

		'type'=>'raw',

		'value'=>'$data->kategori_diklat->description',

		'filter'=>LookupKategoriDiklat::items('1'),

		),

		array(

		'name'=>'id_diklat',

		'value'=>'$data->diklat->nama',

		'filter'=>LookupDiklat::itemsFilter('1'),

		),

		array(

		'header'=>'Lama (hari)',

		'value'=>'(((strtotime($data->sampai))-(strtotime($data->dari)))/24/3600)+1',

		),

		'jumlah_peserta',

		array(

		'header'=>'HOP',

		'value'=>'((((strtotime($data->sampai))-(strtotime($data->dari)))/24/3600)+1)*($data->jumlah_peserta)',

		),

		array(

		'header'=>'Total Biaya',

                'value'=>'number_format(($data->perkakas+$data->alat_tulis+$data->barang_cetakan

                         +$data->honorarium+$data->perjalanan_dinas+$data->konsumsi),0,",",".")',

		'htmlOptions'=>array('style'=>'text-align:right'),

		'footer'=>$model->search()->itemCount===0 ? '' : $model->getTotal($model->search()),

		),

		array(

			'class'=>'CButtonColumn',

		),

	),

)); 

?>


<!--START COUNT DATA-->

<?php

$countData = $model->search();

$total=0;

$hop=0;

$totalFilteredData = $countData->getTotalItemCount();


foreach($countData->data as $attribute):

	$subtotal = $attribute->perkakas + $attribute->alat_tulis + $attribute->barang_cetakan + 

		$attribute->honorarium + $attribute->perjalanan_dinas + $attribute->konsumsi;

	$total += $subtotal;

	$hop += ((((strtotime($attribute->sampai))-(strtotime($attribute->dari)))/24/3600)+1) * $attribute->jumlah_peserta;

endforeach;


$HOP = $total/(($hop<>0)?$hop:1);

?>


<br>

<div style="border-top:1px solid silver; border-bottom:1px solid silver; background: #EEEEEE; padding:10px;">

	<?php

		echo '<table border=0>';

		echo '<tr><td width=110>Total Diklat</td><td width=5>:</td><td><b>'.$totalFilteredData.'</b></td></tr>';

		echo '<tr><td width=110>Total Biaya Diklat</td><td width=5>:</td><td>

                      <b>Rp. '.number_format($total,0,',','.').',-</b></td></tr>';

		echo '<tr><td width=110>Total HOP</td><td width=5>:</td><td><b>'.$hop.'</b></td></tr>';

		echo '<tr><td width=110>Biaya per HOP</td><td width=5>:</td><td>

                      <b>Rp. '.number_format($HOP,0,',','.').',-</b></td></tr>';

		echo '</table>';

?>

</div>

The model:




	public function search()

	{

		// Warning: Please modify the following code to remove attributes that

		// should not be searched.


		$criteria=new CDbCriteria;


		$criteria->compare('id',$this->id);

		$criteria->compare('id_unit',$this->id_unit);

		$criteria->compare('id_unit_sub',$this->id_unit_sub);

		$criteria->compare('dari',$this->dari);

		$criteria->compare('sampai',$this->sampai);

		$criteria->compare('id_kategori_diklat',$this->id_kategori_diklat);

		$criteria->compare('id_diklat',$this->id_diklat);

		$criteria->compare('jumlah_peserta',$this->jumlah_peserta);

		$criteria->compare('is_active',$this->is_active,true);


		return new CActiveDataProvider(get_class($this), array(

			'criteria'=>$criteria,

		));

	}


	public static function getTotal($provider)

	{

		$total=0;

		foreach($provider->data as $data)

		{

			$t = $data->perkakas+$data->alat_tulis+$data->barang_cetakan+

				$data->honorarium+$data->perjalanan_dinas+$data->konsumsi;

			$total += $t;

		}

		return $total;

	}

The controller:




	public function actionAdmin()

	{

		$model=new Data('search');

		$model->unsetAttributes();  // clear any default values

		if(isset($_GET['Data']))

		{

			$model->attributes=$_GET['Data'];

		}


		$this->render('admin',array(

			'model'=>$model,

		));

	}

Explanation

All files were generated by Gii. Max record per page is 10 by default. I entered 12 records. I use getTotalItemCount() function to get total record and change depends on dropdown filter.

The problem is on the first page, Total Biaya Diklat ($total) gives wrong information, only return 1.000.000 that count from 10 records only, not all record (12), even the second page have the same behavior, only return 50.000 from 2 records.

What i want to achieve is : Total Biaya Diklat should return all records, no matter the pagination is set to any number. And when the user click on the dropdown filter, it should count all record on the filtered rows, once again no matter how the pagination set.

If i give a large pageSize, Total Biaya Diklat always give right information. But it’s impossible to implement if it has thousands of record.

I feel the solution may go in simple way, but i already stuck with it, couldn’t find solution. Please help.

mdomba · March 2, 2011, 1:46pm

You are passing the CActiveProvider to getTotal(), as $model->search() returns it… you need there only the criteria ( $model->search()->criteria )… and than make a findAll() using that criteria…

itmagetan · March 2, 2011, 3:13pm

Of course… Big thanks mdomba. I knew it that it’ll be as simple as creating webapp with yiic .

Wow…it’s amazing to know how the way you are thinking. I believe there are still a lot of thing i have to comprehend.

So, this is my final working codes, the model :




	public static function getTotal($criteria)

	{

		$total=0;

		$hopNum=0;


		$provider = Data::model()->findAll($criteria);

		foreach($provider as $data)

		{

			$subTotal = $data->perkakas+$data->alat_tulis+$data->barang_cetakan+

				$data->honorarium+$data->perjalanan_dinas+$data->konsumsi;

			$total += $subTotal;

			$hopNum += ((((strtotime($data->sampai))-(strtotime($data->dari)))/24/3600)+1) * $data->jumlah_peserta;

		}

		return $total.'_'.$hopNum;

	}

View file:




<?php

$total=0;

$hop=0;

$data = explode('_', $model->getTotal($model->search()->criteria));

?>

<br>

<div style="border-top:1px solid silver; border-bottom:1px solid silver; background: #EEEEEE; padding:10px;">

	<?php

		echo '<table border=0>';

		echo '<tr><td width=110>Total Diklat</td><td width=5>:</td><td><b>'.$model->search()->getTotalItemCount().'</b></td></tr>';

		echo '<tr><td width=110>Total Biaya Diklat</td><td width=5>:</td><td><b>Rp. '.number_format($data[0],0,',','.').',-</b></td></tr>';

		echo '<tr><td width=110>Total HOP</td><td width=5>:</td><td><b>'.$data[1].'</b></td></tr>';

		echo '<tr><td width=110>Biaya per HOP</td><td width=5>:</td><td><b>Rp. '.number_format(($data[0]/(($data[1]<>0)?$data[1]:1)),0,',','.').',-</b></td></tr>';

		echo '</table>';

	?>

</div>

Once again, thank you.

itmagetan · March 2, 2011, 3:22pm

Oh…one thing i forgot. That works on configuration ‘ajaxUpdate’=>false in CGridView property. How if i want to use ajax? i’m sure we need to play with JQuery, but how? i’ve been trying but no luck yet.