Running Total in Grid

Suite and Optimus Suite 5
#1

Hi Guys,

I have a little problem in migrating some code from an old system to one using DHTMLX grid.

I have a grid like this

suppLedgerGrid.setHeader(‘Date, Ref, Item, Debit, Credit, Balance’);

which pulls data from two tables, an invoice table and a payment table. I use the following SQL UNION statement to prepare the data on the server side :

$LedgerSQL = "select invoiceid as ItemID, invoicedate as ItemDate, invoiceref as ItemRef, ‘Invoice’ as ItemType, invtotal as ItemDebit, 0 as ItemCredit, ‘’ as ItemBalance from invoices where suppid=$_GET[SuppID] ";
$LedgerSQL .= "union select paymentid as ItemID, paymentdate as ItemDate, paymentref as ItemRef, ‘Payment’ as ItemType, 0 as ItemDebit, paymentamt as ItemCredit, ‘’ as ItemBalance from payments where suppid=$_GET[SuppID] ";
$LedgerSQL .= “order by ItemDate asc”;

$resconn->render_sql($LedgerSQL,“ItemID”, “ItemDate,ItemRef,ItemType,ItemDebit,ItemCredit,ItemBalance”);

In my old system when rendering the HTML table to display the results I iterated through the SQL results and built up the last column manually by adding/deleting a counter based on the results from each table.

I’d like the last column in the grid ‘ItemBalance’ to be previous row’s ‘ItemBalance’+(-ItemDebit)+ItemCredit

Maybe this is just not possible? I can’t figure out how to do it using render_sql…

#2

You can use beforeRender as

[code]$counter = 0;
function calck($action){
global $counter;

$counter = $counter - $action->get(“ItemDebit”) + $action->get(“itemCredit”);
$action->setValue(“ItemBalance”,$counter);
}
$grid->event->attach(“beforeRender”,“calck”);[/code]

#3

Hi Stanislav,

I can’t seem to get this to work… am I using it correctly?

	case "supplierledger":
	
		$LedgerSQL = "select invoiceid as ItemID, invoicedate as ItemDate, invoiceref as ItemRef, 'Invoice' as ItemType, invtotal as ItemDebit, 0 as ItemCredit, '' as ItemBalance from invoices where suppid=$_GET[SuppID] ";
		$LedgerSQL .= "union select paymentid as ItemID, paymentdate as ItemDate, paymentref as ItemRef, 'Payment' as ItemType, 	0 as ItemDebit, paymentamt as ItemCredit, '' as ItemBalance from payments where suppid=$_GET[SuppID] ";
		
		$LedgerSQL .= "order by ItemDate asc";
		$resconn->event->attach("beforeRender","calck");
		$resconn->render_sql($LedgerSQL,"ItemID", "ItemDate,ItemRef,ItemType,ItemDebit,ItemCredit,ItemBalance");	
                    break;



$counter = 0;
function calck($action)
{
global $counter;

$counter = $counter - $action->get("ItemDebit") + $action->get("ItemCredit");
$action->setValue("ItemBalance",$counter);
}

I am now always getting the error…

Fatal error: Call to undefined method GridDataItem::get() in C:\xampp\htdocs\shopnet\supplierdetail.php on line 188

#4

Sorry, it was a my typo, replace “get” with “get_value”

#5

Also, it is a good practice to have all configuration code BEFORE render_table or render_sql command, because script stops execution after them, so any further commands will not be processed at all.

#6

Perfect! thanks