Column Totals?

[Fike]

I have tried the code and works great. Thanks a lot Steven!

Is there a way to catch the sql query that is created when a user filters the results from the browse form in order to get the column totals based on this query?

I mean, when the user filters the 'Invoices due' from a certain client, typing the clients name or code on the search box, is there a way to get the total of invoices due from that client with some code on the 'Before browse' or 'After browse' tab?

Thanks in advance!

Kind regrads,

Fike

[Fike]

I finally got the way to catch the search string that is used in the sql query when the search box is used to filter the results

Here is the php code (before browse)

Code: Select all

$javascript = "window.nuPoll = false;";   //disable polling (otherwise this code won't work)
nuAddJavascript($javascript);


//building the browse table

$sql_tbl = " SELECT *, CONCAT(tipact_plan_cta,' ',tb_cod,' ',act_consec) AS num_inv FROM activo
LEFT JOIN tipobien ON tipobien_id = act_tipobien_id
LEFT JOIN tipoactivo ON tipoactivo_id = tb_tipoactivo_id
ORDER BY tipact_secuencial, tb_cod, act_consec";

$sql_cre = "CREATE TABLE #TABLE_ID# ".$sql_tbl;

nuRunQuery($sql_cre);



//code taken from: function nuGetBrowseRecords($f, $p, $hashData) of nuapi.php file 


 
$f = nuV('form_id');
$p = nuV('page_number');
$p = 100000000000000;   //to get the sum of all pages

$S = "SELECT * FROM zzzsys_browse WHERE sbr_zzzsys_form_id = ? ORDER BY sbr_order";

 
    $T = nuRunQuery($S, array($f));
    if (nuErrorFound()) 
	{
        return;
    }

    $searchFields     = array();
    $searchColumns    = explode(',', nuV('search_columns'));                     //-- create an array of searchable columns
    $columnCount      = 1;
    while ($R = db_fetch_object($T)) 
	{                                           //-- create columns


        if (count($searchColumns) == 1) {
            nuV('search_columns', nuV('search_columns') . ",$columnCount");      //--  create a comma delimited string of searchable columns
            $searchFields[] = $R->sbr_display;
        } else {
            if (in_array($columnCount -1, $searchColumns)) {
                $searchFields[] = $R->sbr_display;                               //-- searchable column
            }
        }

        $columnCount++;
    }

    if (nuV('edit_browse') == 'true') 
	{
        $hashData = array_merge($hashData, nuGetCurrentData());                  //-- use values on current page for hash data
    }

    $hashedSQL = $r->sfo_sql;
    $SQL       = new nuSqlString($hashedSQL);
    $SQL->removeAllFields();
    $SQL->addField($r->sfo_primary_key);
    $width     = 0;
    



    if (nuV('search') != '' or nuV('filter') != '') 
	{
        if ($SQL->getWhere() == '') {
            $searchString = ' WHERE ' . nuBrowseWhereClause($searchFields, nuV('search') . ' ' . nuV('filter'));
        } else {
            $searchString = $SQL->getWhere() . ' AND ' . nuBrowseWhereClause($searchFields, nuV('search') . ' ' . nuV('filter'));
        }
        $SQL->setWhere($searchString);
    }
	



if ($searchString == " WHERE  () ")
{
   $searchString = "";
   
}


//use the $searchString to get the total sum from filtered results



$sql = "SELECT SUM(act_importe) AS totales FROM #TABLE_ID# ".$searchString;

$qry = nuRunQuery($sql);
$obj = db_fetch_object($qry);
$due = number_format($obj->totales,2);


//send the total to zzzsys_debug table

nuDebug($due);



//display the sum in the browse form

$javascript = "
   function showTotals() {
      
      
      $('#nuStatusHolder').append('<div class=\"edittable\" id=\"due\" style=\"top:2px; left:1250px; position:absolute;\"><b>Importe total: $due</b></div>');      
   }
   showTotals();
";

nuAddJavascript($javascript);

One thing I am not happy with, is that I had to disable polling in order to get the total sum from the filtered results. Otherwise I get the sum of all the records from the table.

If there is a way to make it work without disabling polling, please feel free to make any corrections to the code.

KInd regards,

Fike

[admin]

Fike,

We have removed polling and replaced it by checking only as a record is being saved.

https://www.nubuilder.net/documentation ... ac238edf55 (See Save Button.)

Steven

[Fike]

Thanks a lot Steven!

Kind regrads,

Fike

[admin]

.