Hi Bob,
thank you for your answer. Somehow (you never know how these processes go) it led me to a solution I think is correct (at least it works perfectly in my situation!), which is shown in this post, as a kind of tutorial for whoever wants to use it.
When thinking about your answer, I realised, that recalculation has to be part of the solution, but when you only do it when the subtotal isn't set, the result will certainly be incorrect. Not only can the user switch to the next page, but also to the previous one, even to a randomly selected page, including the current one.
Therefore you need to (re)calculate the subtotal whenever the first record of the new page does not immediately follow the last record of the previous page.
Implementing this strategy was a nice programming exercise! I did it by defining two session variables: subtotal and lastrec, maintaining over the session the following relation true: "subtotal is the sum of the values in records 1..lastrec" (a socalled invariant). Note that the record numbers in my application will ever be consecutive (cf_id is the record number) - no records will be deleted. Recalculation is required when the first record of a new page has a number != lastrec+1.
Another problem (at least for me) was your suggestion to do the recalculation in the header box. I couldn't find a simple method to determine the first record number of the new page there. In the body box each row can be accessed via $MyRow, but in the header box someting like $MyRows does not seem to exist (compare connection.php functions evalPart() and getBody()). Probably it can be found somewhere in $pageNav, but I didn't want to start a research project on that.
Instead I found a simple way to determine whether we were handling the first record in the body box by realising that PHP variables defined for the first record keep their values handling subsequent records. So when such a variable does not exist, we're handling the first record! Luckily when starting a next page, the variable is undefined again.
Following is the detailed solution, with lots of explaining inline comment. The field to be added in the subtotal only will contain small integers.
The following code goes into the header box. If the session variables do not exist yet they are defined and the invariant is established, otherwise we trust our abilities to maintain the invariant.
<?php
// We define two session variables: subtotal and lastrec
// We will maintain an invariant during the complete session:
// subtotal contains the sum of the elementary fields in records 1..lastrec
// We first check whether subtotal exists. If not we establish the invariant.
$session =& JFactory::getSession();
if ($session->get('subtotal', -1) == -1) {
$session->set('subtotal', 0);
$session->set('lastrec', 0);
}
?>
The following code comes at the start of the body box.
<?php
// At this place the invariant will hold.
// A new calculation of subtotal is only required for the first record on the
// page, and only when its record number is not lastrec+1
// First check whether we are handling the first record on the page.
// This is done by checking for the (non-)existence of a variable $nextrec,
// that is set for every record (after this test!).
// We abuse the fact that such a variable is undefined upon the start of each
// execution of this connection, and maintains its value over the body records.
$session =& JFactory::getSession(); // Required for all records
if (!$nextrec) {
$nextrec = 1;
// Here is the code for handling the first record on the page.
// Now we check whether recalculation is required.
$firstrec = $MyRow->cf_id;
$lastrec = $session->get('lastrec', 0);
if ($firstrec != $lastrec + 1) {
// Yes, recalculation is required, read all previous records
$db =& JFactory::getDBO();
$query = "
SELECT `cf_id`,`element_value`
FROM `jos_chronoforms_mytable`
WHERE `cf_id`<'".$firstrec."'
ORDER BY `cf_id`";
$db->setQuery($query);
$myrows = $db->loadObjectList();
// Recalculate the subtotal
$subtotal = 0;
foreach ($myrows as $myrow) {
$subtotal += $myrow->element_value;
}
// and reestablish the invariant
$session->set('subtotal', $subtotal);
$session->set('lastrec', $firstrec - 1);
}
}
// The following code is executed for all records in the current page
// It calculates the subtotal for each record and maintains the invariant.
$subtotal = $session->get('subtotal', 0) + $MyRow->element_value;
$session->set('subtotal', $subtotal);
$session->set('lastrec', $MyRow->cf_id);
?>
In subsequent HTML code (the usual <tr> and <td> stuff) the value of $subtotal can be used.
Please let me know when you find any shortcomings or improvements.
Regards,
Peter