Hi,
I think I would choose to do the totals with the Aggregate plug-in. But it doesn't work with rupture (headergrp or footergrp). So I would use subblocks instead of headergrp / footergrp.
Let put the problem of columns and global total in parentheses for a moment.
The template could be something like this:
Company/Resource
<tr> -------------------------------------------
<tr> Company [c.comp_name;block=tr+tr+tr]
<tr> [r.ress;block=tr;aggregate=amount:sum;p1=[c.comp_id]]
<tr> TOTALS
<tr> -------------------------------------------
|
Now for dynamic columns :
You have to rebuild a new data array to feet with the TBS template with the same philosophy of the Example about Dynamic columns.
Here is what it can be (please only take the idea, the code is not tested)
$qry = mysql_query('SELECT MONTH(date) AS month , SUM(price) AS amount , comp_id, comp_name , ress FROM table1 GROUP BY comp_id, comp_name , ress_name ');
$month_lst = array();
$comp_lst = array();
$big_total = array();
while ($rec = mysql_fetch_array($qry)) {
$m = $rec['month'];
$c = $rec['comp_id'];
// update list of month
if (!isset($month_lst[$m])) $month_lst[$m] = $m;
// update list of company
if (!isset($comp_lst[$c])) $month_lst[$c] = array('comp_id'=>$c , 'comp_name'=> $rec['comp_name']; , 'row'=>array() );
// save the cell information
$row =& $comp_lst[$c]['row'] // take car of the assignation by reference with &
$row['a_'.$m] = $rec['amount'];
$row['ress'] = $rec['ress'];
// big total
if (!isset($big_total[$m)) $big_total[$m = 0.0;
$big_total[$m] = $big_total[$m] + $rec['amount'];
}
$TBS->MergeBlock('m1,m2,m3,m4',$month_lst); // Dynamic columns, us as many m1,m2... as they are rows in the table before the merge
$TBS->MergeBlock('c',$comp_lst); // Merge company as main block
$TBS->MergeBlock('r','array','comp_lst[%p1%][row]'); // Merge cells as subblock
// Merge total
...
|
In this case, some of your row may have not any amount for each month. So the columns may be missing for each celle. Use parameter "noerr" to avoid this problem.
I suggest that you first test this solution without activating the subtotals and the Aggregate plug-in. It may not works with missing cells, but you can arrange that in the plug-in source code.