Yii cgridview filter date range

How to make a date filter for Cgridview in Yii. First we will create a table ‘project’ that has columns id (int),name (varchar) and created_on  (date). We will create the table in the database ‘report’ which we created for the example in this post Create Report using Jaspersoft Studio.

CREATE TABLE IF NOT EXISTS `project` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`created_on` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Next insert values into the ‘project’ table.

INSERT INTO `report`.`project` (`id`, `name`, `created_on`) VALUES (NULL, 'Project A', '2015-01-01'), (NULL, 'Project B', '2015-02-01'), (NULL, 'Project C', '2015-03-01'), (NULL, 'Project D', '2015-04-01'), (NULL, 'Project E', '2015-05-01');

Next we will create the model for this table at ‘myapp\protected\models\Project.php’. I will use the application I created in the  Yii ajax form submission example.

 

<?php

class Project extends CActiveRecord
{
public $project_name;
public $start_date;
public $end_date;

/**
* Declares the validation rules.
*/
public function rules()
{
return array(
array('id,name,created_on,project_name,start_date,end_date', 'safe'),
);
}

public function tableName()
{
return 'project';
}

public function search()
{

if(empty($this->project_name))
$this->project_name=null;
if(empty($this->start_date))
$this->start_date=null;
if(empty($this->end_date))
$this->end_date=null;

$command =Yii::app()->db->createCommand("SELECT COUNT(*) FROM project where :start_date is null or :end_date is null or created_on between :start_date and :end_date");
$command->bindParam(":start_date",  $this->start_date,PDO::PARAM_STR);
$command->bindParam(":end_date",  $this->end_date,PDO::PARAM_STR);
$count=$command->queryScalar();

$sql="SELECT id,name FROM project where  :start_date is null or :end_date is null or created_on between :start_date and :end_date";

$dataProvider=new CSqlDataProvider($sql, array(
'params' => array(':start_date'=>$this->start_date,':end_date'=>$this->end_date),
'totalItemCount'=>$count,
'db'=>Yii::app()->db,
'sort'=>array(
'attributes'=>array(
'name'
),
),
'pagination'=>array(
'pageSize'=>10,
),
));
return $dataProvider;
}

}

Next we will create the view file at ‘myapp\protected\views\site\project.php’

 

<?php
Yii::app()->clientScript->registerScript("searchdate","
function searchdate()
{
$('#project-grid').yiiGridView('update', {data: {'Project[start_date]': $('#start_date').val(),'Project[end_date]': $('#end_date').val()}});
}",CClientScript::POS_END);
?>
<strong>From:</strong>
<?php $this->widget('zii.widgets.jui.CJuiDatePicker', array(
'name'=>'start_date',
'htmlOptions'=>array(
'readonly'=>"readonly",
),
'options'=>array(
'dateFormat'=>'yy-mm-dd',
),
)); ?>

<strong>To:</strong>
<?php $this->widget('zii.widgets.jui.CJuiDatePicker', array(
'name'=>'end_date',
'htmlOptions'=>array(
'readonly'=>"readonly",
),
'options'=>array(
'dateFormat'=>'yy-mm-dd',
),
)); ?>

<?php echo CHtml::button('Search',array('onclick'=>'searchdate()')); ?>

<?php
;
$this->widget('zii.widgets.grid.CGridView', array(
'id'=>'project-grid',
'itemsCssClass'=>'table table-bordered table-condensed table-hover table-striped dataTable',
'dataProvider'=>$model->search(),
'enablePagination' => true,
'enableHistory'=>true,
'pagerCssClass'=>'dataTables_paginate paging_bootstrap table-pagination',
'pager' => array('header'=>'','htmlOptions'=>array('class'=>'pagination')),
'columns' => array(
array('name'=>'project_name','value'=>'$data["name"]','header'=>'Name'),
),
'htmlOptions'=>array('class'=>'grid-view table-responsive hide-x-scroll'),
))
?>

 

 

Next we can create the action in the ‘myapp\protected\controllers\SiteController.php’

 

public function actionProject()
{
$model=new Project;
if(isset($_GET['Project']))
{
$model->unsetAttributes();
$model->attributes = $_GET['Project'];
}
$this->render('project',array('model'=>$model));
}

Now add the below codein the menu section  in ‘myapp\protected\views\layouts\main.php’

array('label'=>'Project', 'url'=>array('/site/project')),

 

 

Now click on the menu and you can see the below screen with ajax date filtering.

 

 

 yii cgridview filter date range