[Summary view] [Print] [Text view]

   1  <?php
   2  /**
   3   * @version   v5.20.3  01-Jan-2016
   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  }