An inefficient but straightforward way of doing it would be to add a getMTD function to your ccp model that would calculate the MTD for that row of the table (obviously you’d have to hit the database once for each row you display). You could then reference it in the CGridView.
If you need more efficiency, you could switch over to using an SQLDataProvider to feed your CGridView and write sql to calculate the MTD and return it as one of your columns.
Thanks Jobla and Zilles for you help.much appreciation. I worked on zilles idea and am still looking at jobla’s. I created a class
<?php
Yii::import(‘zii.widgets.grid.CGridColumn’);
class getMTD extends CGridColumn {
private $_mtd = 0;
public function renderDataCellContent($row, $data) { // $row number is ignored
$this->_mtd += $data->sales;
echo $this->_mtd;
}
It calculates the monthly totals however I can’t figute out how to calculate the monthy totals in a given revenue centre per posting category. Please help. Thanks in advance
If you add getMTD() to your Ccp model, then you can just reference "MTD" in the column list.
I was thinking you could use MySql to calculate the mtd value.
Something like this: (untested code)
function getMTD()
{
$conn=Yii::app()->db;
$command=$conn->createCommand( 'SELECT SUM(sales) FROM ccp
WHERE MONTH(DATE)=:month AND YEAR(DATE)=:year AND
rvc_id=:rvc AND postingcat_id=:pc AND id<=:id');
$data = $command->queryScalar(
array(
'month'=>date('m',$this->date),
'year'=>date('Y',$this->date),
'rvc'=>$this->rvc_id,
'pc'=>$this->postingcat_id,
'id'=>$this->id
));
return $data;
}
This code assumes that orders are placed in the table in chronological order, so you will have to adjust it if that’s not a safe assumption.
Alternatively, you can work this sql into a query:
SELECT *, (SELECT SUM(sales) FROM ccp t2
WHERE MONTH(t1.date)=MONTH(t2.date) AND YEAR(t1.date)=YEAR(t2.date) AND t1.rvc_id=t2.rvc_id AND
t1.postingcat_id=t2.postingcat_id AND t2.id<=t1.id) AS mtd FROM ccp t1
And populate your CGridView with a SQLDataProvider
function getMTD()
{
$conn=Yii::app()->db;
$command=$conn->createCommand( 'SELECT SUM(sales) FROM ccp
WHERE MONTH(DATE)=:month AND YEAR(DATE)=:year AND
rvc_id=:rvc AND postingcat_id=:pc AND id<=:id');
$data = $command->queryScalar(
array(
'month'=>date('m',$this->date),
'year'=>date('Y',$this->date),
'rvc'=>$this->rvc_id,
'pc'=>$this->postingcat_id,
'id'=>$this->id
));
return $data;
}
When I run this query
SELECT *, (SELECT SUM(sales) FROM ccp t2
WHERE MONTH(t1.date)=MONTH(t2.date) AND YEAR(t1.date)=YEAR(t2.date) AND t1.rvc_id=t2.rvc_id AND
t1.postingcat_id=t2.postingcat_id AND t2.id<=t1.id) AS mtd FROM ccp t1
in mysql it gives me the right results but I still cant figure out how to populate those results in cgridview.
I did
function getMTD()
{
$sql= 'SELECT ( SELECT SUM( sales )FROM `ccp` `t`
WHERE MONTH( t1.date ) = MONTH( t.date )
AND YEAR( t1.date ) = YEAR( t.date )
AND t1.rvc_id = t.rvc_id
AND t1.postingcat_id = t.postingcat_id
AND t.id <= t1.id ) as mtd from ccp t1';
$conn=Yii::app()->db;
$command=$conn->createCommand($sql);
$data = $command->queryscalar();
return $data;
}
but its still populating the whole column with the first row results .Please help.Thank you
Yeah, I don’t have enough information to write code that’s definitely going to work for you. I was just trying to get you on the right track. It should be close though. You might try debugging this. I don’t know how you’re storing dates in the object. If they’re strings then you might just need to parse them before pulling out the components.
public $mtd;
public $id;
public function getMtd()
{
$sql = 'SELECT *,(SELECT SUM(sales) FROM tbl_ccp t2
WHERE MONTH(t1.date)=MONTH(t2.date) AND YEAR(t1.date)=YEAR(t2.date) AND t1.rvc_id=t2.rvc_id
AND t1.postingcat_id=t2.postingcat_id AND t2.id<=t1.id) AS mtd
FROM tbl_ccp t1, tbl_rvc, tbl_postingcat
WHERE rvc_id = tbl_rvc.id
AND postingcat_id = tbl_postingcat.id
GROUP BY DATE, rvc_id, postingcat_id';
$sql_count = 'SELECT COUNT(id) FROM tbl_ccp';
return new CSqlDataProvider($sql, array(
'totalItemCount' => Yii::app()->db->createCommand($sql_count)->queryScalar(),
'sort' => array(
'attributes' => array(
'date',
),
),
'pagination' => array(
'pageSize' => 15,
),
));
}
Then in your views
<?php $this->widget('zii.widgets.grid.CGridView', array(
'dataProvider' => $model->getMtd(),
'columns' => array(
array(
'name'=>'Category',
'value'=>'$data["postingcat_name"]', //if you declared in relationship it will show name instead of Id.
),
'mtd::M.T.D',
),
));
?>
If you put your function in the controller accress with $this->getMtd()
