[SOLVED] CSqldataprovider Date Range Filter

Hi,

first of all, sorry for my english.

My problem is that I want to filter a SQL query between dates given by the user

This is my controller


public function actionAdmin()

	{


		$first = date('Y-m-d',strtotime($_POST['date_first']));

                $last = date('Y-m-d',strtotime($_POST['date_last']));

        

		$model=new PagosCarterosGeneral('search');

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

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

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


		  $sql = "SELECT CA.rut AS MAIN_ID, 

	            CA.nombre AS nom, 

	            CA.apellido_paterno AS ap, 

	            CA.apellido_materno AS am, 

	            DA.fecha_devolucion AS fecha,

	            SUM(RE.cantidad_cartas) AS totalC, 

	            SUM(DA.cantidad_devoluciones) AS totalD

				FROM cartero AS CA

				INNER JOIN 

				carga_diaria AS CD

				ON (CD.rut_cartero = CA.rut)

				INNER JOIN registro_acuse AS RE

				ON (CD.codigo_acuse_recibo = RE.codigo_acuse_recibo)

				INNER JOIN devolucion_acuse AS DA

				ON (RE.codigo_acuse_recibo = DA.codigo_acuse_recibo)

				WHERE DA.fecha_devolucion Between '".$first."' AND '".$last."' 

				GROUP BY MAIN_ID, nom, ap, am


				";

        

        $rawData = Yii::app()->db->createCommand($sql);

		$count = Yii::app()->db->createCommand(

                                         'SELECT COUNT(*) FROM (' . $sql . ') as count_alias')->queryScalar();

 

        $model = new CSqlDataProvider($rawData, array( 

                    'keyField' => 'MAIN_ID', 

                    'totalItemCount' => $count,

                    'sort' => array(

                        'attributes' => array(

                            'MAIN_ID','nom','ap','am'

                        ),

                        'defaultOrder' => array(

                            'ap' => CSort::SORT_ASC,

                        ),

                    ),

                    'pagination' => array(

                        'pageSize' => 11,

                    ),

                ));

 

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

            'model' => $model,

        ));

	}

and my admin view




  

                <b>from:</b>

                <?php $this->widget('zii.widgets.jui.CJuiDatePicker', array(

                                        'name'=>'date_first',                                                                  

                                        'language' => 'es',                                     

                                        'htmlOptions'=>array(                                           

                                        'readonly'=>"readonly",

                                        ),

                                        'options'=>array(                                               

                                                'dateFormat'=>'yy-mm-dd',                                                       

                                        ),

                )); ?>

              

                <b>to:</b>

                <?php $this->widget('zii.widgets.jui.CJuiDatePicker', array(

                                        'name'=>'date_last',                                                                     

                                        'language' => 'es',                                     

                                        'htmlOptions'=>array(                                           

                                        'readonly'=>"readonly",

                                        ),

                                        'options'=>array(                                               

                                                'dateFormat'=>'yy-mm-dd',                                                       

                                        ),

                )); ?>


                 <?php echo CHtml::submitButton('Search'); ?>


<?php $this->widget('bootstrap.widgets.TbGridView', array(

    'id' => 'a-grid-id',

    'dataProvider' => $model,

    'ajaxUpdate' => true, 

    'filter' => null, 

    'columns' => array(

        array(

            'header' => 'R.U.T',

            'name' => 'MAIN_ID',

           

        ),

        array(

            'header' => 'Name',

            'name' => 'nom',

            'value' => '$data[nom]." ".$data[ap]." ".$data[am]',

           

        ),

        array(

            'header' => 'Cartas',

            'name' => 'totalC',

            //'value'=>'$data["title"]', //in the case we want something custom

        ),

        array(

            'header' => 'Devoluciones',

            'name' => 'totalD',

           

        ),

 

        ),

    )

);?>           

I know I have to send from the admin view the parameters $first and $last but I don’t know how.

Thank you.

You have to put the two CJuiDatePicker in a form or you can call a javascript function when one of the two input changes value.

Hi bro, what Fabrizio told you is correct. I also suggest you this way to do what you´re needing. In your model put this code changing and adding the name of your fields, etc…

In the view put ‘dataProvider’ => $model->search(),

And take off the ajax to run your test…

Here´s the code…

public &#036;date_first;


public &#036;date_last;

public function search()


{


	&#036;criteria=new CDbCriteria;





	if((isset(&#036;this-&gt;date_first) &amp;&amp; trim(&#036;this-&gt;date_first) &#33;= &quot;&quot;) &amp;&amp; (isset(&#036;this-&gt;date_last) &amp;&amp; trim(&#036;this-&gt;date_last) &#33;= &quot;&quot;))


 		&#036;criteria-&gt;addBetweenCondition('Date_Begin', ''.&#036;this-&gt;date_first.'', ''.&#036;this-&gt;date_last.'');


 		&#036;criteria-&gt;addBetweenCondition('Date_End', ''.&#036;this-&gt;date_first.'', ''.&#036;this-&gt;date_last.'');


	





	&#036;criteria-&gt;compare('ID',&#036;this-&gt;ID);





	&#036;criteria-&gt;compare('Date_Begin',&#036;this-&gt;Date_Begin,true);


	&#036;criteria-&gt;compare('Date_End',&#036;this-&gt;Date_End,true);





	return new CActiveDataProvider(&#036;this, array(


		'criteria'=&gt;&#036;criteria,


	));


} 

Try this and tell me about it…

Good luck, keep comunication…

In the view use CHtml::button instead of CHtml::submitButton




<?php 

Yii::app()->clientScript->registerScript("searchdate","

    function searchdate()

                 {

                    $('#a-grid-id').yiiGridView('update', {data: {'PagosCarterosGeneral[date_first]': $('#".CHtml::activeId($model,'date_first')."').val(),'PagosCarterosGeneral[date_last]': $('#".CHtml::activeId($model,'date_last')."').val()}});

                 }",CClientScript::POS_END);




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

?>



Place the dataprovider in the model as search function




public function search()

    {

if(empty($this->date_first))

$this->date_first=null;

if(empty($this->date_last))

$this->date_last=null;


$sql = "SELECT CA.rut AS MAIN_ID, 

                    CA.nombre AS nom, 

                    CA.apellido_paterno AS ap, 

                    CA.apellido_materno AS am, 

                    DA.fecha_devolucion AS fecha,

                    SUM(RE.cantidad_cartas) AS totalC, 

                    SUM(DA.cantidad_devoluciones) AS totalD

                                FROM cartero AS CA

                                INNER JOIN 

                                carga_diaria AS CD

                                ON (CD.rut_cartero = CA.rut)

                                INNER JOIN registro_acuse AS RE

                                ON (CD.codigo_acuse_recibo = RE.codigo_acuse_recibo)

                                INNER JOIN devolucion_acuse AS DA

                                ON (RE.codigo_acuse_recibo = DA.codigo_acuse_recibo)

                                WHERE DA.fecha_devolucion Between '".$this->date_first."' AND '".$this->date_last."' 

                                GROUP BY MAIN_ID, nom, ap, am


                                ";

        

        

                $count = Yii::app()->db->createCommand(

                                         'SELECT COUNT(*) FROM (' . $sql . ') as count_alias')->queryScalar();

 

        $dataProvider = new CSqlDataProvider($sql, array( 

                    'keyField' => 'MAIN_ID', 

                    'totalItemCount' => $count,

                    'sort' => array(

                        'attributes' => array(

                            'MAIN_ID','nom','ap','am'

                        ),

                        'defaultOrder' => array(

                            'ap' => CSort::SORT_ASC,

                        ),

                    ),

                    'pagination' => array(

                        'pageSize' => 11,

                    ),

                ));


return $dataProvider;


}



Remove the code




$first = date('Y-m-d',strtotime($_POST['date_first']));

$last = date('Y-m-d',strtotime($_POST['date_last']));



Use $this->date_first and $this->date_last in the query

In the view file put the below code in the gridview




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



Thank you!! I made it some small modifications to your code and it’s works

if someone wants to see the answer, here is http://www.yiiframework.com/forum/index.php/topic/68367-solucionado-filtrar-por-fechas-csqldataprovider/page__p__290907__fromsearch__1#entry290907