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