Add Column Totals and Recalculate when Filter Applied

I am building an inventory system for a client where there is a form used to enter in new inventory (made with chronoforms) and all the data is stored in a table. Then using chrono connectivity, I made a page to display the rows in the inventory table and even included a filter to filter results by one of the fields (model).

The client now want me to total three columns on the resulting table in the joomla site. I was able to implement to total using MySQL statements however there are a few issues I am having with it. First, if the value in the database for a dollar amount is 27,000 it won't total anything beyond the comma. If it is imputted in the table as 27000 the it totals fine. How can I get it to total with the value entered as 27,000 since visually this looks better. I don't mind if the value in the database is without the comma and we just format as currency on the view. Also, I want to have the totals recalculate when a filter is applied. Right now, the filter changes the number of records shown on the page but does not effect the totals calculated. The code I entered in the footer box of chronoconnectivity is below along with a screen shot of my page.

Thanks for the assistance,

Michael

[attachment=0]bovet stock.PNG[/attachment]

<?
$db =& JFactory::getDBO();
$query = "
SELECT SUM(`worldprice`) AS `total_worldprice`
FROM `jos_chronoforms_bovetstock1`;
";
$db->setQuery($query);
$total_worldprice = $db->loadResult();
echo "Total World Price = $".$total_worldprice;
?>
<br/>
<?
$db =& JFactory::getDBO();
$query = "
SELECT SUM(`cost`) AS `total_cost`
FROM `jos_chronoforms_bovetstock1`;
";
$db->setQuery($query);
$total_cost = $db->loadResult();
echo "Total Cost = $".$total_cost;
?>
<br/>
<?
$db =& JFactory::getDBO();
$query = "
SELECT SUM(`invoice`) AS `total_invoice`
FROM `jos_chronoforms_bovetstock1`;
";
$db->setQuery($query);
$total_invoice = $db->loadResult();
echo "Total Invoice = $".$total_invoice;
?>

Hi Michael,

I did this last week.

First off save the amounts unformatted and format on display.

<?php echo number_format($MyRow->some_number); ?>

The simplest way to have the totals match the filter is to sum them in the Body box.

In the header add:

<?php
global $total_cost;
$total_cost = 0;
?>

The in the Body Box:

<?php
global $total_cost;
$total_cost += $MyRow->cost;
?>

and in the Footer box:

<?php
global $total_cost;
echo "<div>Total Cost: ".number_format($total_cost)."</div>;
?>

Bob

Bob - First off, thank you for the quick reply. I added the code you provided but two odd things are now happening. First, there is now a "0" displayed to the left of my filter box. Second, I receive a parse error on my page only when I added the code to the footer. I am including a screen shot of the error for you to see. Below is the code from my Where SQL box, header box, body box, and footer box.



WHERE SQL:

<?php
$model = JRequest::getString('model', '', 'post');
if ( $model ) {
echo " WHERE `model` LIKE '%$model%' ";
}
?>

HEADER:

<br>
<?php echo number_format($MyRow->some_number); ?>
<?php
$script = "";
$script .= "
$('clear').addEvent('click', function() {
$('title').value = '';
});
";
if ( $script ) {
$doc =& JFactory::getDocument();
$script = "window.addEvent('domready', function() { $script });";
$doc->addScriptDeclaration($script);
}
?>

<input type='text' name='model' id='model' value='' /> <input type='submit'
name='filter' id='filter' value='Filter' /> <input type='button' name='clear' id='clear' value='Clear' />

<?php
global $total_cost;
$total_cost = 0;
?>

<table>
   <thead>
       <tr>
          <th style='width:70px;'>GPAO</th>
          <th style='width:70px;'>MODEL</th>
          <th style='width:70px;'>METAL</th>
          <th style='width:125px;'>DIAL</th>
          <th style='width:80px;'>LOCATION</th>
          <th style='width:70px;'>CASE NO.</th>
          <th>WORLD PRICE</th>
          <th style='width:80px;'>COST</th>
          <th>INVOICE</th>
       </tr>
   </thead>

BODY:

<?php
global $total_cost;
$total_cost += $MyRow->cost;
?>

<tr><td>{gpao}</td><td>{model}</td> <td>{metal}</td><td>{dial}</td><td>{location}</td><td>{case}</td><td>{worldprice}</td><td>{cost}</td><td>{invoice}</td></tr>

FOOTER:

</table>
    
<?php
global $total_cost;
echo "<div>Total Cost: ".number_format($total_cost)."</div>;
?>

<?
$db =& JFactory::getDBO();
$query = "
    SELECT SUM(`worldprice`) AS `total_worldprice`
        FROM `jos_chronoforms_bovetstock1`;
";
$db->setQuery($query);
$total_worldprice = $db->loadResult();
echo "Total World Price = $".$total_worldprice;
?>
<br/>
<?
$db =& JFactory::getDBO();
$query = "
    SELECT SUM(`cost`) AS `total_cost`
        FROM `jos_chronoforms_bovetstock1`;
";
$db->setQuery($query);
$total_cost = $db->loadResult();
echo "Total Cost = $".$total_cost;
?>
<br/>
<?
$db =& JFactory::getDBO();
$query = "
    SELECT SUM(`invoice`) AS `total_invoice`
        FROM `jos_chronoforms_bovetstock1`;
";
$db->setQuery($query);
$total_invoice = $db->loadResult();
echo "Total Invoice = $".$total_invoice;
?>

Hi mkusens,

The stray 0 is from this line which was just given as a formatting example

<?php echo number_format($MyRow->some_number); ?>

The parse error is probably because I missed the final " from this line:

echo "<div>Total Cost: ".number_format($total_cost)."</div>";

Bob

Worked perfect. Thanks. Just bought you a beer so please enjoy.

Hi guys.
Sorry for digging this one up; however, I am trying to achieve something similar (or identical) with CCv4 and cannot make it work. I followed all steps from the thread, yet with no luck...
Could it be the case of a too old a code to suit the new CC 4.0 RC3.2?

Here's what I've got In header

<br>
<?php echo number_format($MyRow->some_number); ?>

<?php
global $total_k1;
$total_k1 = 0;
?>

<p>test raportu</p>
<table>
   <thead>
       <tr>
          <th style='width:70px;'>Kierowca</th>
          <th style='width:50px;'>Zleceniodawca</th>
          <th style='width:25px;'>K1</th>
          <th style='width:25px;'>KK</th>
          <th style='width:25px;'>WW</th>
        </tr>
   </thead>

Body:

<?php
global $total_k1;
$total_k1 += $MyRow->k1;
?>
<tr><td>{kierowca}</td>
<td>{zleceniodawca}</td> 
<td>{k1}</td>
<td>{kk}</td>
<td>{ww}</td>
</tr>

And Footer:

</table>
    
<?php
global $total_k1;
echo "<div>Suma K1: ".number_format($total_k1)."</div>";
?>

<?
$db =& JFactory::getDBO();
$query = "
    SELECT SUM(`k1`) AS `total_k1`
        FROM `jos_my_table`;
";
$db->setQuery($query);
$total_k1 = $db->loadResult();
echo "Total K1 = $".$total_k1;
?>
<br/>

Just to add... I'm using the inbuilt search form to filter the results based on the {zleceniodawca} field.

Hi Marcin,

I think so, please note that

$MyRow->k1

should be now

$row['k1']

please try fixing that and see if it helps ?

Regards,
Max

Max!

I cannot thank you enough!!!

Worked like a charm. THANK YOU. You have no idea how much it helped me and saved hours of work each month. :mrgreen: :mrgreen: