home *** CD-ROM | disk | FTP | other *** search
/ Enter 2004 June / ENTER.ISO / files / xampp-win32-1.4.5-installer.exe / xampp / pivottable.inc.php < prev    next >
Encoding:
PHP Script  |  2004-03-20  |  5.8 KB  |  164 lines
  1. <?php
  2. /** 
  3.  * @version V4.21 20 Mar 2004 (c) 2000-2004 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.  * Latest version is available at http://php.weblogs.com
  11.  *
  12.  * Requires PHP4.01pl2 or later because it uses include_once
  13. */
  14.  
  15. /*
  16.  * Concept from daniel.lucazeau@ajornet.com. 
  17.  *
  18.  * @param db        Adodb database connection
  19.  * @param tables    List of tables to join
  20.  * @rowfields        List of fields to display on each row
  21.  * @colfield        Pivot field to slice and display in columns, if we want to calculate
  22.  *                        ranges, we pass in an array (see example2)
  23.  * @where            Where clause. Optional.
  24.  * @aggfield        This is the field to sum. Optional. 
  25.  *                        Since 2.3.1, if you can use your own aggregate function 
  26.  *                        instead of SUM, eg. $sumfield = 'AVG(fieldname)';
  27.  * @sumlabel        Prefix to display in sum columns. Optional.
  28.  * @aggfn            Aggregate function to use (could be AVG, SUM, COUNT)
  29.  * @showcount        Show count of records
  30.  *
  31.  * @returns            Sql generated
  32.  */
  33.  
  34.  function PivotTableSQL($db,$tables,$rowfields,$colfield, $where=false,
  35.      $aggfield = false,$sumlabel='Sum ',$aggfn ='SUM', $showcount = true)
  36.  {
  37.     if ($aggfield) $hidecnt = true;
  38.     else $hidecnt = false;
  39.     
  40.     
  41.     //$hidecnt = false;
  42.     
  43.      if ($where) $where = "\nWHERE $where";
  44.     if (!is_array($colfield)) $colarr = $db->GetCol("select distinct $colfield from $tables $where order by 1");
  45.     if (!$aggfield) $hidecnt = false;
  46.     
  47.     $sel = "$rowfields, ";
  48.     if (is_array($colfield)) {
  49.         foreach ($colfield as $k => $v) {
  50.             if (!$hidecnt) $sel .= "\n\t$aggfn(CASE WHEN $v THEN 1 ELSE 0 END) AS \"$k\", ";
  51.             if ($aggfield)
  52.                 $sel .= "\n\t$aggfn(CASE WHEN $v THEN $aggfield ELSE 0 END) AS \"$sumlabel$k\", ";
  53.         } 
  54.     } else {
  55.         foreach ($colarr as $v) {
  56.             if (!is_numeric($v)) $vq = $db->qstr($v);
  57.             else $vq = $v;
  58.             if (strlen($v) == 0    ) $v = 'null';
  59.             if (!$hidecnt) $sel .= "\n\t$aggfn(CASE WHEN $colfield=$vq THEN 1 ELSE 0 END) AS \"$v\", ";
  60.             if ($aggfield) {
  61.                 if ($hidecnt) $label = $v;
  62.                 else $label = "{$v}_$aggfield";
  63.                 $sel .= "\n\t$aggfn(CASE WHEN $colfield=$vq THEN $aggfield ELSE 0 END) AS \"$label\", ";
  64.             }
  65.         }
  66.     }
  67.     if ($aggfield && $aggfield != '1'){
  68.         $agg = "$aggfn($aggfield)";
  69.         $sel .= "\n\t$agg as \"$sumlabel$aggfield\", ";        
  70.     }
  71.     
  72.     if ($showcount)
  73.         $sel .= "\n\tSUM(1) as Total";
  74.     else
  75.         $sel = substr($sel,0,strlen($sel)-2);
  76.     
  77.     $sql = "SELECT $sel \nFROM $tables $where \nGROUP BY $rowfields";
  78.     return $sql;
  79.  }
  80.  
  81. /* EXAMPLES USING MS NORTHWIND DATABASE */
  82. if (0) {
  83.  
  84. # example1
  85. #
  86. # Query the main "product" table
  87. # Set the rows to CompanyName and QuantityPerUnit
  88. # and the columns to the Categories
  89. # and define the joins to link to lookup tables 
  90. # "categories" and "suppliers"
  91. #
  92.  
  93.  $sql = PivotTableSQL(
  94.      $gDB,                                              # adodb connection
  95.      'products p ,categories c ,suppliers s',          # tables
  96.     'CompanyName,QuantityPerUnit',                    # row fields
  97.     'CategoryName',                                    # column fields 
  98.     'p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID' # joins/where
  99. );
  100.  print "<pre>$sql";
  101.  $rs = $gDB->Execute($sql);
  102.  rs2html($rs);
  103.  
  104. /*
  105. Generated SQL:
  106.  
  107. SELECT CompanyName,QuantityPerUnit, 
  108.     SUM(CASE WHEN CategoryName='Beverages' THEN 1 ELSE 0 END) AS "Beverages", 
  109.     SUM(CASE WHEN CategoryName='Condiments' THEN 1 ELSE 0 END) AS "Condiments", 
  110.     SUM(CASE WHEN CategoryName='Confections' THEN 1 ELSE 0 END) AS "Confections", 
  111.     SUM(CASE WHEN CategoryName='Dairy Products' THEN 1 ELSE 0 END) AS "Dairy Products", 
  112.     SUM(CASE WHEN CategoryName='Grains/Cereals' THEN 1 ELSE 0 END) AS "Grains/Cereals", 
  113.     SUM(CASE WHEN CategoryName='Meat/Poultry' THEN 1 ELSE 0 END) AS "Meat/Poultry", 
  114.     SUM(CASE WHEN CategoryName='Produce' THEN 1 ELSE 0 END) AS "Produce", 
  115.     SUM(CASE WHEN CategoryName='Seafood' THEN 1 ELSE 0 END) AS "Seafood", 
  116.     SUM(1) as Total 
  117. FROM products p ,categories c ,suppliers s  WHERE p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID 
  118. GROUP BY CompanyName,QuantityPerUnit
  119. */
  120. //=====================================================================
  121.  
  122. # example2
  123. #
  124. # Query the main "product" table
  125. # Set the rows to CompanyName and QuantityPerUnit
  126. # and the columns to the UnitsInStock for different ranges
  127. # and define the joins to link to lookup tables 
  128. # "categories" and "suppliers"
  129. #
  130.  $sql = PivotTableSQL(
  131.      $gDB,                                        # adodb connection
  132.      'products p ,categories c ,suppliers s',    # tables
  133.     'CompanyName,QuantityPerUnit',                # row fields
  134.                                                 # column ranges
  135. array(                                        
  136. ' 0 ' => 'UnitsInStock <= 0',
  137. "1 to 5" => '0 < UnitsInStock and UnitsInStock <= 5',
  138. "6 to 10" => '5 < UnitsInStock and UnitsInStock <= 10',
  139. "11 to 15"  => '10 < UnitsInStock and UnitsInStock <= 15',
  140. "16+" =>'15 < UnitsInStock'
  141. ),
  142.     ' p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID', # joins/where
  143.     'UnitsInStock',                             # sum this field
  144.     'Sum'                                        # sum label prefix
  145. );
  146.  print "<pre>$sql";
  147.  $rs = $gDB->Execute($sql);
  148.  rs2html($rs);
  149.  /*
  150.  Generated SQL:
  151.  
  152. SELECT CompanyName,QuantityPerUnit, 
  153.     SUM(CASE WHEN UnitsInStock <= 0 THEN UnitsInStock ELSE 0 END) AS "Sum  0 ", 
  154.     SUM(CASE WHEN 0 < UnitsInStock and UnitsInStock <= 5 THEN UnitsInStock ELSE 0 END) AS "Sum 1 to 5", 
  155.     SUM(CASE WHEN 5 < UnitsInStock and UnitsInStock <= 10 THEN UnitsInStock ELSE 0 END) AS "Sum 6 to 10", 
  156.     SUM(CASE WHEN 10 < UnitsInStock and UnitsInStock <= 15 THEN UnitsInStock ELSE 0 END) AS "Sum 11 to 15", 
  157.     SUM(CASE WHEN 15 < UnitsInStock THEN UnitsInStock ELSE 0 END) AS "Sum 16+",
  158.     SUM(UnitsInStock) AS "Sum UnitsInStock", 
  159.     SUM(1) as Total 
  160. FROM products p ,categories c ,suppliers s  WHERE  p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID 
  161. GROUP BY CompanyName,QuantityPerUnit
  162.  */
  163. }
  164. ?>