[Sommaire] [Imprimer]

   1  <?php
   2  /** 
   3   * @version V4.65 22 July 2005 (c) 2000-2005 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      $iif = strpos($db->databaseType,'access') !== false; 
  41          // note - vfp still doesn' work even with IIF enabled || $db->databaseType == 'vfp';
  42      
  43      //$hidecnt = false;
  44      
  45       if ($where) $where = "\nWHERE $where";
  46      if (!is_array($colfield)) $colarr = $db->GetCol("select distinct $colfield from $tables $where order by 1");
  47      if (!$aggfield) $hidecnt = false;
  48      
  49      $sel = "$rowfields, ";
  50      if (is_array($colfield)) {
  51          foreach ($colfield as $k => $v) {
  52              $k = trim($k);
  53              if (!$hidecnt) {
  54                  $sel .= $iif ? 
  55                      "\n\t$aggfn(IIF($v,1,0)) AS \"$k\", "
  56                      :
  57                      "\n\t$aggfn(CASE WHEN $v THEN 1 ELSE 0 END) AS \"$k\", ";
  58              }
  59              if ($aggfield) {
  60                  $sel .= $iif ?
  61                      "\n\t$aggfn(IIF($v,$aggfield,0)) AS \"$sumlabel$k\", "
  62                      :
  63                      "\n\t$aggfn(CASE WHEN $v THEN $aggfield ELSE 0 END) AS \"$sumlabel$k\", ";
  64              }
  65          } 
  66      } else {
  67          foreach ($colarr as $v) {
  68              if (!is_numeric($v)) $vq = $db->qstr($v);
  69              else $vq = $v;
  70              $v = trim($v);
  71              if (strlen($v) == 0    ) $v = 'null';
  72              if (!$hidecnt) {
  73                  $sel .= $iif ?
  74                      "\n\t$aggfn(IIF($colfield=$vq,1,0)) AS \"$v\", "
  75                      :
  76                      "\n\t$aggfn(CASE WHEN $colfield=$vq THEN 1 ELSE 0 END) AS \"$v\", ";
  77              }
  78              if ($aggfield) {
  79                  if ($hidecnt) $label = $v;
  80                  else $label = "{$v}_$aggfield";
  81                  $sel .= $iif ?
  82                      "\n\t$aggfn(IIF($colfield=$vq,$aggfield,0)) AS \"$label\", "
  83                      :
  84                      "\n\t$aggfn(CASE WHEN $colfield=$vq THEN $aggfield ELSE 0 END) AS \"$label\", ";
  85              }
  86          }
  87      }
  88      if ($aggfield && $aggfield != '1'){
  89          $agg = "$aggfn($aggfield)";
  90          $sel .= "\n\t$agg as \"$sumlabel$aggfield\", ";        
  91      }
  92      
  93      if ($showcount)
  94          $sel .= "\n\tSUM(1) as Total";
  95      else
  96          $sel = substr($sel,0,strlen($sel)-2);
  97      
  98      $sql = "SELECT $sel \nFROM $tables $where \nGROUP BY $rowfields";
  99      return $sql;
 100   }
 101  
 102  /* EXAMPLES USING MS NORTHWIND DATABASE */
 103  if (0) {
 104  
 105  # example1
 106  #
 107  # Query the main "product" table
 108  # Set the rows to CompanyName and QuantityPerUnit
 109  # and the columns to the Categories
 110  # and define the joins to link to lookup tables 
 111  # "categories" and "suppliers"
 112  #
 113  
 114   $sql = PivotTableSQL(
 115       $gDB,                                              # adodb connection
 116       'products p ,categories c ,suppliers s',          # tables
 117      'CompanyName,QuantityPerUnit',                    # row fields
 118      'CategoryName',                                    # column fields 
 119      'p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID' # joins/where
 120  );
 121   print "<pre>$sql";
 122   $rs = $gDB->Execute($sql);
 123   rs2html($rs);
 124   
 125  /*
 126  Generated SQL:
 127  
 128  SELECT CompanyName,QuantityPerUnit, 
 129      SUM(CASE WHEN CategoryName='Beverages' THEN 1 ELSE 0 END) AS "Beverages", 
 130      SUM(CASE WHEN CategoryName='Condiments' THEN 1 ELSE 0 END) AS "Condiments", 
 131      SUM(CASE WHEN CategoryName='Confections' THEN 1 ELSE 0 END) AS "Confections", 
 132      SUM(CASE WHEN CategoryName='Dairy Products' THEN 1 ELSE 0 END) AS "Dairy Products", 
 133      SUM(CASE WHEN CategoryName='Grains/Cereals' THEN 1 ELSE 0 END) AS "Grains/Cereals", 
 134      SUM(CASE WHEN CategoryName='Meat/Poultry' THEN 1 ELSE 0 END) AS "Meat/Poultry", 
 135      SUM(CASE WHEN CategoryName='Produce' THEN 1 ELSE 0 END) AS "Produce", 
 136      SUM(CASE WHEN CategoryName='Seafood' THEN 1 ELSE 0 END) AS "Seafood", 
 137      SUM(1) as Total 
 138  FROM products p ,categories c ,suppliers s  WHERE p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID 
 139  GROUP BY CompanyName,QuantityPerUnit
 140  */
 141  //=====================================================================
 142  
 143  # example2
 144  #
 145  # Query the main "product" table
 146  # Set the rows to CompanyName and QuantityPerUnit
 147  # and the columns to the UnitsInStock for diiferent ranges
 148  # and define the joins to link to lookup tables 
 149  # "categories" and "suppliers"
 150  #
 151   $sql = PivotTableSQL(
 152       $gDB,                                        # adodb connection
 153       'products p ,categories c ,suppliers s',    # tables
 154      'CompanyName,QuantityPerUnit',                # row fields
 155                                                  # column ranges
 156  array(                                        
 157  ' 0 ' => 'UnitsInStock <= 0',
 158  "1 to 5" => '0 < UnitsInStock and UnitsInStock <= 5',
 159  "6 to 10" => '5 < UnitsInStock and UnitsInStock <= 10',
 160  "11 to 15"  => '10 < UnitsInStock and UnitsInStock <= 15',
 161  "16+" =>'15 < UnitsInStock'
 162  ),
 163      ' p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID', # joins/where
 164      'UnitsInStock',                             # sum this field
 165      'Sum'                                        # sum label prefix
 166  );
 167   print "<pre>$sql";
 168   $rs = $gDB->Execute($sql);
 169   rs2html($rs);
 170   /*
 171   Generated SQL:
 172   
 173  SELECT CompanyName,QuantityPerUnit, 
 174      SUM(CASE WHEN UnitsInStock <= 0 THEN UnitsInStock ELSE 0 END) AS "Sum  0 ", 
 175      SUM(CASE WHEN 0 < UnitsInStock and UnitsInStock <= 5 THEN UnitsInStock ELSE 0 END) AS "Sum 1 to 5", 
 176      SUM(CASE WHEN 5 < UnitsInStock and UnitsInStock <= 10 THEN UnitsInStock ELSE 0 END) AS "Sum 6 to 10", 
 177      SUM(CASE WHEN 10 < UnitsInStock and UnitsInStock <= 15 THEN UnitsInStock ELSE 0 END) AS "Sum 11 to 15", 
 178      SUM(CASE WHEN 15 < UnitsInStock THEN UnitsInStock ELSE 0 END) AS "Sum 16+",
 179      SUM(UnitsInStock) AS "Sum UnitsInStock", 
 180      SUM(1) as Total 
 181  FROM products p ,categories c ,suppliers s  WHERE  p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID 
 182  GROUP BY CompanyName,QuantityPerUnit
 183   */
 184  }
 185  ?>