[Summary view]

Differences Between: [Versions 310 and 311] [Versions 310 and 400] [Versions 310 and 401] [Versions 310 and 402] [Versions 310 and 403]

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