Source for file pivottable.inc.php

Documentation is available at pivottable.inc.php

  1. <?php
  2. /** 
  3.  * @version V4.93 10 Oct 2006 (c) 2000-2008 John Lim (jlim#natsoft.com.my). All rights reserved.
  4.  *  Released under both BSD license and Lesser GPL library license.
  5.  *  Whenever there is any discrepancy between the two licenses,
  6.  *  the BSD license will take precedence.
  7.  *
  8.  *  Set tabs to 4 for best viewing.
  9.  * 
  10. */
  11.  
  12. /*
  13.  * Concept from daniel.lucazeau@ajornet.com. 
  14.  *
  15.  * @param db        Adodb database connection
  16.  * @param tables    List of tables to join
  17.  * @rowfields        List of fields to display on each row
  18.  * @colfield        Pivot field to slice and display in columns, if we want to calculate
  19.  *                        ranges, we pass in an array (see example2)
  20.  * @where            Where clause. Optional.
  21.  * @aggfield        This is the field to sum. Optional. 
  22.  *                        Since 2.3.1, if you can use your own aggregate function 
  23.  *                        instead of SUM, eg. $aggfield = 'fieldname'; $aggfn = 'AVG';
  24.  * @sumlabel        Prefix to display in sum columns. Optional.
  25.  * @aggfn            Aggregate function to use (could be AVG, SUM, COUNT)
  26.  * @showcount        Show count of records
  27.  *
  28.  * @returns            Sql generated
  29.  */
  30.  
  31.  function PivotTableSQL(&$db,$tables,$rowfields,$colfield, $where=false,
  32.      $aggfield = false,$sumlabel='Sum ',$aggfn ='SUM', $showcount = true)
  33.  {
  34.     if ($aggfield) $hidecnt = true;
  35.     else $hidecnt = false;
  36.     
  37.     $iif = strpos($db->databaseType,'access') !== false; 
  38.         // note - vfp 6 still doesn' work even with IIF enabled || $db->databaseType == 'vfp';
  39.     
  40.     //$hidecnt = false;
  41.     
  42.      if ($where) $where = "\nWHERE $where";
  43.     if (!is_array($colfield)) $colarr = $db->GetCol("select distinct $colfield from $tables $where order by 1");
  44.     if (!$aggfield) $hidecnt = false;
  45.     
  46.     $sel = "$rowfields, ";
  47.     if (is_array($colfield)) {
  48.         foreach ($colfield as $k => $v) {
  49.             $k = trim($k);
  50.             if (!$hidecnt) {
  51.                 $sel .= $iif ? 
  52.                     "\n\t$aggfn(IIF($v,1,0)) AS \"$k\", "
  53.                     :
  54.                     "\n\t$aggfn(CASE WHEN $v THEN 1 ELSE 0 END) AS \"$k\", ";
  55.             }
  56.             if ($aggfield) {
  57.                 $sel .= $iif ?
  58.                     "\n\t$aggfn(IIF($v,$aggfield,0)) AS \"$sumlabel$k\", "
  59.                     :
  60.                     "\n\t$aggfn(CASE WHEN $v THEN $aggfield ELSE 0 END) AS \"$sumlabel$k\", ";
  61.             }
  62.         } 
  63.     } else {
  64.         foreach ($colarr as $v) {
  65.             if (!is_numeric($v)) $vq = $db->qstr($v);
  66.             else $vq = $v;
  67.             $v = trim($v);
  68.             if (strlen($v) == 0    ) $v = 'null';
  69.             if (!$hidecnt) {
  70.                 $sel .= $iif ?
  71.                     "\n\t$aggfn(IIF($colfield=$vq,1,0)) AS \"$v\", "
  72.                     :
  73.                     "\n\t$aggfn(CASE WHEN $colfield=$vq THEN 1 ELSE 0 END) AS \"$v\", ";
  74.             }
  75.             if ($aggfield) {
  76.                 if ($hidecnt) $label = $v;
  77.                 else $label = "{$v}_$aggfield";
  78.                 $sel .= $iif ?
  79.                     "\n\t$aggfn(IIF($colfield=$vq,$aggfield,0)) AS \"$label\", "
  80.                     :
  81.                     "\n\t$aggfn(CASE WHEN $colfield=$vq THEN $aggfield ELSE 0 END) AS \"$label\", ";
  82.             }
  83.         }
  84.     }
  85.     if ($aggfield && $aggfield != '1'){
  86.         $agg = "$aggfn($aggfield)";
  87.         $sel .= "\n\t$agg as \"$sumlabel$aggfield\", ";        
  88.     }
  89.     
  90.     if ($showcount)
  91.         $sel .= "\n\tSUM(1) as Total";
  92.     else
  93.         $sel = substr($sel,0,strlen($sel)-2);
  94.     
  95.     
  96.     // Strip aliases
  97.     $rowfields = preg_replace('/ AS (\w+)/i', '', $rowfields);
  98.     
  99.     $sql = "SELECT $sel \nFROM $tables $where \nGROUP BY $rowfields";
  100.     
  101.     return $sql;
  102.  }
  103.  
  104. /* EXAMPLES USING MS NORTHWIND DATABASE */
  105. if (0) {
  106.  
  107. # example1
  108. #
  109. # Query the main "product" table
  110. # Set the rows to CompanyName and QuantityPerUnit
  111. # and the columns to the Categories
  112. # and define the joins to link to lookup tables 
  113. # "categories" and "suppliers"
  114. #
  115.  
  116.  $sql = PivotTableSQL(
  117.      $gDB,                                              # adodb connection
  118.      'products p ,categories c ,suppliers s',          # tables
  119.     'CompanyName,QuantityPerUnit',                    # row fields
  120.     'CategoryName',                                    # column fields 
  121.     'p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID' # joins/where
  122. );
  123.  print "<pre>$sql";
  124.  $rs = $gDB->Execute($sql);
  125.  rs2html($rs);
  126.  
  127. /*
  128. Generated SQL:
  129.  
  130. SELECT CompanyName,QuantityPerUnit, 
  131.     SUM(CASE WHEN CategoryName='Beverages' THEN 1 ELSE 0 END) AS "Beverages", 
  132.     SUM(CASE WHEN CategoryName='Condiments' THEN 1 ELSE 0 END) AS "Condiments", 
  133.     SUM(CASE WHEN CategoryName='Confections' THEN 1 ELSE 0 END) AS "Confections", 
  134.     SUM(CASE WHEN CategoryName='Dairy Products' THEN 1 ELSE 0 END) AS "Dairy Products", 
  135.     SUM(CASE WHEN CategoryName='Grains/Cereals' THEN 1 ELSE 0 END) AS "Grains/Cereals", 
  136.     SUM(CASE WHEN CategoryName='Meat/Poultry' THEN 1 ELSE 0 END) AS "Meat/Poultry", 
  137.     SUM(CASE WHEN CategoryName='Produce' THEN 1 ELSE 0 END) AS "Produce", 
  138.     SUM(CASE WHEN CategoryName='Seafood' THEN 1 ELSE 0 END) AS "Seafood", 
  139.     SUM(1) as Total 
  140. FROM products p ,categories c ,suppliers s  WHERE p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID 
  141. GROUP BY CompanyName,QuantityPerUnit
  142. */
  143. //=====================================================================
  144.  
  145. # example2
  146. #
  147. # Query the main "product" table
  148. # Set the rows to CompanyName and QuantityPerUnit
  149. # and the columns to the UnitsInStock for diiferent ranges
  150. # and define the joins to link to lookup tables 
  151. # "categories" and "suppliers"
  152. #
  153.  $sql = PivotTableSQL(
  154.      $gDB,                                        # adodb connection
  155.      'products p ,categories c ,suppliers s',    # tables
  156.     'CompanyName,QuantityPerUnit',                # row fields
  157.                                                 # column ranges
  158. array(                                        
  159. ' 0 ' => 'UnitsInStock <= 0',
  160. "1 to 5" => '0 < UnitsInStock and UnitsInStock <= 5',
  161. "6 to 10" => '5 < UnitsInStock and UnitsInStock <= 10',
  162. "11 to 15"  => '10 < UnitsInStock and UnitsInStock <= 15',
  163. "16+" =>'15 < UnitsInStock'
  164. ),
  165.     ' p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID', # joins/where
  166.     'UnitsInStock',                             # sum this field
  167.     'Sum'                                        # sum label prefix
  168. );
  169.  print "<pre>$sql";
  170.  $rs = $gDB->Execute($sql);
  171.  rs2html($rs);
  172.  /*
  173.  Generated SQL:
  174.  
  175. SELECT CompanyName,QuantityPerUnit, 
  176.     SUM(CASE WHEN UnitsInStock <= 0 THEN UnitsInStock ELSE 0 END) AS "Sum  0 ", 
  177.     SUM(CASE WHEN 0 < UnitsInStock and UnitsInStock <= 5 THEN UnitsInStock ELSE 0 END) AS "Sum 1 to 5", 
  178.     SUM(CASE WHEN 5 < UnitsInStock and UnitsInStock <= 10 THEN UnitsInStock ELSE 0 END) AS "Sum 6 to 10", 
  179.     SUM(CASE WHEN 10 < UnitsInStock and UnitsInStock <= 15 THEN UnitsInStock ELSE 0 END) AS "Sum 11 to 15", 
  180.     SUM(CASE WHEN 15 < UnitsInStock THEN UnitsInStock ELSE 0 END) AS "Sum 16+",
  181.     SUM(UnitsInStock) AS "Sum UnitsInStock", 
  182.     SUM(1) as Total 
  183. FROM products p ,categories c ,suppliers s  WHERE  p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID 
  184. GROUP BY CompanyName,QuantityPerUnit
  185.  */
  186. }
  187. ?>

Documentation generated on Sun, 09 Mar 2008 23:52:54 -0300 by phpDocumentor 1.4.0

SourceForge.net Logo Support This Project