tbsForum - Topic - tbs_plugin_aggregate and minimal grouping support

By: Fernando Fernandez

Date: 2012-03-14

Time: 10:06

tbs_plugin_aggregate and minimal grouping support

I've modified this plugin to get a minimal support for subtotals. Only works for one level of grouping and placing computed value in the footer.

By: Skrol29

Date: 2012-03-15

Time: 00:05

Re: tbs_plugin_aggregate and minimal grouping support

Hi Fernando,

Thank you very much for sharing this.
I'll have a look to it and probably integrate it.

Regards

By: Fernando Fernandez

Date: 2012-03-15

Time: 11:25

Re: tbs_plugin_aggregate and minimal grouping support

Hi, I've just finished another version with multiple group support:
I've had to use a new parameter aggregategrp to set the columns groups. I don't know how to extract that info from parameters of events BeforeMegeBlock or OnMergeSection.
There is a sample of usage in the comments.

<?php

/*
********************************************************
TinyButStrong plug-in: Aggregate
Version 1.00, on 2006-07-21, by Skrol29
********************************************************
*
2012-03-14. Version 1.01, by Fernando Fernandez
Modified for initial group (first level) support.
*
2012-03-15. Version 1.02, by Fernando Fernandez
Now allow any level of gruoping
It's necessary set parameter aggregategrp with the grouped columns separated by commas
Each subtotal is accessible by name_column_group:name_aggregate:function
Example with 1 column group:
<table>
<tr><td colspan=2>Year: [resultado.year;block=tr;headergrp=year;aggregate=price:sum;aggregategrp=year]</td></tr>
<tr><td>[resultado.product;block=tr]</td><td>[resultado.price]</td></tr>
<tr><td colspan=2>Year subtotal: [resultado.year:price:sum;block=tr;footergrp=year]</td></tr>
<tr><td colspan=2>Total: [resultado.price:sum]</td></tr>
</table>

Example whith 2 column group:
<table>
<tr><td colspan=2>Year: [resultado.year;block=tr;headergrp=year;aggregate=price:sum;aggregategrp=year,month]</td></tr>
<tr><td colspan=2>Month: [resultado.month;block=tr;headergrp=month]</td></tr>
<tr><td>[resultado.product;block=tr]</td><td>[resultado.price]</td></tr>
<tr><td colspan=2>Month subtotal: [resultado.month:price:sum;block=tr;footergrp=month]</td></tr>
<tr><td colspan=2>Year subtotal: [resultado.year:price:sum;block=tr;footergrp=year]</td></tr>
<tr><td colspan=2>Total: [resultado.price:sum]</td></tr>
</table>
*/

// Name of the class is a keyword used for Plug-In authentication. So i'ts better to save it into a constant.
define('TBS_AGGREGATE','clsTbsAggregate');

$GLOBALS['_TBS_AutoInstallPlugIns'][] = TBS_AGGREGATE;

class clsTbsAggregate {
protected $depthGrupos;
protected $nombreGrupos;
protected $valorGrupoActual;
protected $opeSubtotales;

function OnInstall() {
    $this->Disabled = true;
    $this->TBS->Aggregate = array();
    return array('OnData','BeforeMergeBlock','AfterMergeBlock');
}

function BeforeMergeBlock(&$TplSource,&$BlockBeg,&$BlockEnd,$PrmLst,&$Src,&$LocR) {
    if (!isset($PrmLst['aggregate'])) return;

    if (isset($PrmLst['aggregategrp'])){
      $grupos = explode(',', $PrmLst['aggregategrp']);
      $this->opeSubtotales = array();
      $this->nombreGrupos = array();
      $this->valorActualGrupos = array();
      $this->depthGrupos=count($grupos);
      $i=0;
      foreach($grupos as $nombreGrupo){
        $this->nombreGrupos[$i] = $nombreGrupo;
        $this->valorActualGrupos[$i] = '';
        $this->opeSubtotales[$i] = array();
        $i++;
      }
    }
    else{
      $this->depthGrupos = 0;
    }
    $this->Disabled = false;
    $this->Src =& $Src;
    $this->OpeLst = array();
    $this->OpeNbr = 0;

    $Lst = $PrmLst['aggregate'];
    $Lst = str_replace(chr(10),' ',$Lst);
    $Lst = str_replace(chr(13),' ',$Lst);
    $Lst = explode(',',$Lst);
    foreach ($Lst as $item) {
      // Prepare info
      $item = trim($item);
      $p = strpos($item,':');
      if ($p===false) {
        $this->TBS->meth_Misc_Alert('Aggregate plug-in','\''.$item.'\' is an invalide name for a computed column.');
        continue;
      }
      $field = substr($item,0,$p);
      $ope_type = strtolower(substr($item,$p+1));
      if (!in_array($ope_type,array('sum','min','max','avg','count','acc','chg'))) {
        $this->TBS->meth_Misc_Alert('Aggregate plug-in','Type \''.$ope_type.'\' is an invalide type of operation.');
        continue;
      }

      // Create object
      $Ope = (object) null;
      $Ope->Type = $ope_type;
      if (($ope_type=='sum') or ($ope_type=='acc')) {
        $Ope->Value = 0;
      } else {
        $Ope->Value = null;
      }
      $Ope->OrigCol = $field;
      $Ope->Name = $field.':'.$ope_type; // $ope_type is lowercase
      $Ope->Nbr = 0;
      $Ope->Fct = array(&$this,'f_Ope_'.$ope_type);
      $this->OpeNbr++;
      // Save and clean
      $this->OpeLst[$this->OpeNbr] =& $Ope;
      unset($Ope);

      // Para sub-bloques
      if ($this->depthGrupos > 0){
        for($d = 0; $d < $this->depthGrupos; $d++){
          $OpeSub = (object) null;
          $OpeSub->Type = $ope_type;
          if (($ope_type=='sum') or ($ope_type=='acc')) {
            $OpeSub->Value = 0;
          } else {
            $OpeSub->Value = null;
          }
          $OpeSub->OrigCol = $field;
          $OpeSub->Name = $this->nombreGrupos[$d] . ':' . $field.':'.$ope_type; // $ope_type is lowercase
          $OpeSub->Nbr = 0;
          $OpeSub->Fct = array(&$this,'f_Ope_'.$ope_type);
          $this->opeSubtotales[$d][$this->OpeNbr] =& $OpeSub;
          unset($OpeSub);
        }
      }
    }
}

function resetOpeSub($initialDepth){
    for ($d = $initialDepth; $d < $this->depthGrupos; $d++){
      for ($i = 1; $i <= $this->OpeNbr; $i++) {
        if ($this->opeSubtotales[$d][$i]->Type == 'sum' || $this->opeSubtotales[$d][$i]->Type == 'acc')
          $this->opeSubtotales[$d][$i]->Value = 0;
        else
          $this->OpeSubtotales[$d][$i]->Value = null;
      }
    }
}

function actualizaValorActual($initialDepth, &$CurrRec){
    for ($d = $initialDepth; $d < $this->depthGrupos; $d++){
      $this->valorActualGrupos[$d] = $CurrRec[$this->nombreGrupos[$d]];
    }
}


function OnData($BlockName,&$CurrRec,$RecNum,&$TBS) {
    if ($this->Disabled) return;

    // Calculations
    for ($i=1;$i<=$this->OpeNbr;$i++) {
      $Ope =& $this->OpeLst[$i];
      call_user_func_array($Ope->Fct,array(&$Ope,&$CurrRec));
    }

    if ($this->depthGrupos > 0){
      // Comprobar si hay que resetear algun subtotal
      $d = 0;
      $cambio = false;
      while ($d < $this->depthGrupos && !$cambio){
        $grupo = $CurrRec[$this->nombreGrupos[$d]];
        if ($grupo != $this->valorActualGrupos[$d]){
          $this->resetOpeSub($d);
          $this->actualizaValorActual($d, $CurrRec);
          $cambio = true;
        }
        $d++;
      }


      for($d=0; $d < $this->depthGrupos; $d++){
        for ($i=1;$i<=$this->OpeNbr;$i++) {
          $OpeSub =& $this->opeSubtotales[$d][$i];
          call_user_func_array($OpeSub->Fct,array(&$OpeSub,&$CurrRec));
          if (($OpeSub->Type==='avg') and ($OpeSub->Nbr>0)) $OpeSub->Value = ($OpeSub->Value / $OpeSub->Nbr);
          $CurrRec[$OpeSub->Name] = $OpeSub->Value;
        }
      }
    }
}

function AfterMergeBlock(&$Buffer,&$DataSrc,&$LocR) {
        if ($this->Disabled) return;

        // Save info in last record for fields outside the block
        $LastRec =& $this->Src->CurrRec;
        if (!is_array($LastRec)) $LastRec = array();
        for ($i=1;$i<=$this->OpeNbr;$i++) {
            $Ope =& $this->OpeLst[$i];
            if (($Ope->Type==='avg') and ($Ope->Nbr>0)) $Ope->Value = ($Ope->Value / $Ope->Nbr);
            $LastRec[$Ope->Name] = $Ope->Value;
        }

        // Clear all prepared variables;
        unset($this->Src);
        unset($this->OpeLst);
        $this->Disabled = true;

        // Save data
        $this->TBS->Aggregate = $LastRec;

    }

    function f_Ope_Sum(&$Ope,&$CurrRec) {
        $Ope->Value += $CurrRec[$Ope->OrigCol];
    }

    function f_Ope_Min(&$Ope,&$CurrRec) {
        // Don't use PHP function min(), it has a bad behavior with NULL.
        $x =& $CurrRec[$Ope->OrigCol];
        if (is_null($Ope->Value)) {
            $Ope->Value = $x;
        } elseif (!is_null($x)) {
            if ($x<$Ope->Value) $Ope->Value = $x;
        }
    }

    function f_Ope_Max(&$Ope,&$CurrRec) {
        $Ope->Value = max($Ope->Value,$CurrRec[$Ope->OrigCol]);
    }

    function f_Ope_Avg(&$Ope,&$CurrRec) {
        $x =& $CurrRec[$Ope->OrigCol];
        if (!is_null($x) and ($x!=='')) {
            $Ope->Value += $x;
            $Ope->Nbr++;
        }
    }

    function f_Ope_Count(&$Ope,&$CurrRec) {
        $x =& $CurrRec[$Ope->OrigCol];
        if (!is_null($x) and ($x!=='')) $Ope->Value++;
    }

    function f_Ope_Acc(&$Ope,&$CurrRec) {
        // Same as Sum but same intermediary values
        $Ope->Value += $CurrRec[$Ope->OrigCol];
        $CurrRec[$Ope->Name] = $Ope->Value;
    }

    function f_Ope_Chg(&$Ope,&$CurrRec) {
        $x =& $CurrRec[$Ope->OrigCol];
        if ($Ope->Value==$x) {
            $CurrRec[$Ope->Name] = '';
        } else {
            $CurrRec[$Ope->Name] = $x;
            $Ope->Value = $x;
        }
    }

}
?>

Regards.

tbs_plugin_aggregate and minimal grouping support

tbs_plugin_aggregate and minimal grouping support

Re: tbs_plugin_aggregate and minimal grouping support

Re: tbs_plugin_aggregate and minimal grouping support

New message