library/adodb/pivottable.inc.php

   1 <?php
   2 /**
   3  * @version V4.20 22 Feb 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 ?>