[Sommaire] [Imprimer]

   1  <?php
   2  /** 

   3   * @version V4.93 10 Oct 2006 (c) 2000-2007 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  ?>