[Summary view]

Differences Between: [Versions 310 and 402] [Versions 311 and 402] [Versions 39 and 402]

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