Totals in CGridView

Yii 1.1 General Discussion
1

I have a bunch of invoice records displayed in gridview. Each invoice has a HAS_MANY relation to line items. Each line item has an amount among other fields. What I would like to do is display the total* of those line items in the invoice gridview.

Grid Example

ID | Invoice Title | Invoice Status | Invoice Date | Invoice Total*

I’ve done a bit of searching through the forums but can’t quite find what I’m looking for.

Thanks in advance to anyone who can assist.

2

Ok what do you want is the count of total items in a row ?

3

No that’s not it.

For example, if one invoice has 2 line items. First one is say $100 the second is $200. The total would be $300.

4

Is there a requirement for filtering/sorting the grid by invoice total or is it enough to display it?

5

Hi Huss,

What I am usually doing is like this:

I have invoice and invoice_line tables. On the invoice.php model, I add one variable $invoiceTotal in addition to afterFind method which will read the values on its invoice_line and set the $invoiceTotal value. It should be working but I just do not know whether this is the correct yii way and also not quite sure with the performance impact.

Sorting and searching, I never check this since usually I am not doing any sorting and searching on the calculated columns.

Any better ideas?

Cheers,

Daniel

6

See my solution of customizing grid datacells: Merge GridView Cells

It should be easy to generate a controller method, that returns your total invoice sum.




  

class GridController extends Controller

{       

      

        protected function getInvoiceTotal($data,$row)

        {

             $invoiceTotal = 0;

             ....

             $invoiceId = $data->ID;


             //do db-queries here ...

  

             ....

             return $invoiceTotal;     

        }







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

   ....

 'columns' => array(

      .... 

      array(

                'name' => 'InvoiceTotal',

                //call the method 'getInvoiceTotal' of the controller

                //the params extracted to the method are $data (=the current rowdata) and $row (the row index)

                'value' => array($this, 'getInvoiceTotal')

        ),
7

Where I’m struggling is how do I execute the query to return the total.

8

Righto, I think I’ve done it.




	protected function invoiceTotal($data,$row)

	{

		$invoiceID = $data->inv_ID;

		$invoiceItems = Yii::app()->db->createCommand(array(

			'select' => array('SUM(ini_Sale)'),

			'from' => 'br5_InvItems',

			'where' => 'ini_IDInvoice=:id',

			'params' => array(':id'=>$invoiceID),

		))->queryRow();


		$invoiceTotal = current($invoiceItems);


		return $invoiceTotal;   

	}

There’s only two things I’m unsure about.

I already have a relation in the Invoice model to ‘br5_InvItems’ is the above code making an unnecessary call to the db. Can the existing relation be used to tidy the code?

Also the query returns an array, I’ve just grabbed the first value to return a string, looks kinda messy - any suggestions?

…Thanks Joblo for pointing me in the right direction.

9

For anyone else who’s watching.

I found a better way of doing this here.