TinyButStrong - the PHP Template Engine
Categories > Your tips & tricks >

Cross Tab Report Generator

The forum is closed. Please use Stack Overflow for submitting new questions. Use tags: tinybutstrong , opentbs

By: tonier
Date: 2011-05-09
Time: 05:46

Cross Tab Report Generator

Here for you, TBS user that would like to know how to create cross-tab report and then combine with Dynamic Columns of our powerfull TBS Templating system ...

<?php
/**
* Yan Friskantoni @ 2011
* version super alpha 0.01 ;)
* Generate Cross Tab data of array
* Example:
*
* // $r would be the source of data, you can supplied the data from query like $r = $db->GetArray('select bar,count(bar) from foo group by bar');
* $r = array();
* $r[]= array('user_code' => '123456', 'TestName' => 'Uji Coba', 'TheoryName'=> 'Dasar Komputer', 'AnswerRight' => 0, 'nQuestions' => 2 );
* $r[]= array( 'user_code' => '123456', 'TestName' => 'Uji Coba', 'TheoryName'=> 'Bahasa Inggris', 'AnswerRight' => 0, 'nQuestions' => 20 );
* $r[]= array( 'user_code' => '1234567890', 'TestName' => 'Uji Coba', 'TheoryName'=> 'Dasar Komputer', 'AnswerRight' => 5, 'nQuestions' => 2 );
* $r[]= array( 'user_code' => '1234567890', 'TestName' => 'Uji Coba', 'TheoryName'=> 'Bahasa Inggris', 'AnswerRight' => 0, 'nQuestions' => 20 );
*
* // call back function
* $nRightAnswer = 0;
* function onBeforeRowSaved($drowSrc, &$dcurr_row)
* {
*     global $nRightAnswer;
*     $dcurr_row['TotalRightAnswer'] = $nRightAnswer; // add new column ...
*     $nRightAnswer = 0;
* }
* // watch for by reference paramaters
* function afterCTSet($drowSrc, &$dcurr_row, $theField, $theValue)
* {
*     global $nRightAnswer;
*     $nRightAnswer += $theValue;
*     //echo "AFTER CTSET $nRightAnswer $theField - $theValue";
* }
*
* $columns = array(); // columns will hold the cross tab column of field that you want to be the source of columns field ...
* $dt = &generateCrossTab($r, 'TheoryName', 'AnswerRight', & $columns
*         , array('nQuestions') // skip or hide the field
*         , array('onBeforeRowSaved' => 'onBeforeRowSaved', 'afterCTSet' => 'afterCTSet') // call back function
*         );
* // if succed then arrange how our data shown ... see how we combine $columns and the other column
* $columns = array_merge(array('user_code', 'TestName'), $columns, array('TotalRightAnswer'));
* // START TEST ... used show source for more idea
* var_dump($dt, $columns);
* echo "\n";
* foreach($columns as $c)
* {
*     printf("[%26s]",$c);
* }
* echo "\n";
* foreach($dt as $i)
* {
*     foreach($columns as $c)
*     {
*         printf("[%26s]",$i[$c]);
*     }
*     echo "\n";
* }
* @param type $srcData source of data
* @param type $ctFieldCol cross tab field
* @param type $ctFieldValue cross tab value
* @param type $ctColumns special Columns of CrossTab
* @param type $fieldToSkip what field to skip when it saved into data?
* @param type $callbackArray call back function .. see example and code :D to know how to used it ;)
* @return type arranged data in cross tab
*/
function & generateCrossTab(&$srcData,
            $ctFieldCol, $ctFieldValue, &$ctColumns, $fieldToSkip = null, $callbackArray = null )
    {
        $data = array();
        $the_row = array();
        $startCrossValue = '';
        $isSaveCtColumns = true;
        $ctColumns = array(); // columns of crosstab
        foreach($srcData as $rowSrc)
        {
            if(isset($startCrossValue[0]) &&
                    $startCrossValue != $rowSrc[$ctFieldCol])
            {
                $the_row[$rowSrc[$ctFieldCol]] = $rowSrc[$ctFieldValue]; // add to row, value of ct become field name
                if($isSaveCtColumns) $ctColumns[] = $rowSrc[$ctFieldCol];
                if(isset($callbackArray['afterCTSet'])) $callbackArray['afterCTSet']($rowSrc, &$the_row, $rowSrc[$ctFieldCol], $rowSrc[$ctFieldValue]);
            }
            else // a new row has been reached then save to data
            {
                if(!isset($startCrossValue[0]))
                {
                    $startCrossValue = $rowSrc[$ctFieldCol];
                }
                else
                {
                    if(isset($callbackArray['onBeforeRowSaved'])) $callbackArray['onBeforeRowSaved']($rowSrc, &$the_row);
                    $data[] = $the_row;
                    $isSaveCtColumns = false;
                }
                $the_row = array();
                $the_row[$rowSrc[$ctFieldCol]] = $rowSrc[$ctFieldValue]; // add to row, value of ct become field name
                if($isSaveCtColumns) $ctColumns[] = $rowSrc[$ctFieldCol];
                if(isset($callbackArray['afterCTSet'])) $callbackArray['afterCTSet']($rowSrc, &$the_row, $rowSrc[$ctFieldCol], $rowSrc[$ctFieldValue]);
                // -----------------------
                // save grouped value
                // -----------------------
                // first, dont save crosstab field and value
                $skipper = array($ctFieldCol, $ctFieldValue);
                if(isset($fieldToSkip[0]))
                { // skip field to show?
                    $skipper = array_merge($skipper, $fieldToSkip);
                }
                foreach($rowSrc as $key => $val)
                {
                    if(array_key_exists($key, $skipper))
                        continue;
                    $the_row[$key] = $val;
                }
            }
        }
        if(count($the_row)>0) // for the last row ...
        {
            if(isset($callbackArray['onBeforeRowSaved'])) $callbackArray['onBeforeRowSaved']($rowSrc, &$the_row);
            $data[] = $the_row;
            unset($the_row);
        }
        return $data;
    }

?>