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.