summaryrefslogtreecommitdiff
path: root/framework/DataAccess/adodb/pivottable.inc.php
diff options
context:
space:
mode:
Diffstat (limited to 'framework/DataAccess/adodb/pivottable.inc.php')
-rw-r--r--framework/DataAccess/adodb/pivottable.inc.php185
1 files changed, 185 insertions, 0 deletions
diff --git a/framework/DataAccess/adodb/pivottable.inc.php b/framework/DataAccess/adodb/pivottable.inc.php
new file mode 100644
index 00000000..4fa1875b
--- /dev/null
+++ b/framework/DataAccess/adodb/pivottable.inc.php
@@ -0,0 +1,185 @@
+<?php
+/**
+ * @version V4.72 21 Feb 2006 (c) 2000-2006 John Lim (jlim@natsoft.com.my). All rights reserved.
+ * Released under both BSD license and Lesser GPL library license.
+ * Whenever there is any discrepancy between the two licenses,
+ * the BSD license will take precedence.
+ *
+ * Set tabs to 4 for best viewing.
+ *
+ * Latest version is available at http://php.weblogs.com
+ *
+ * Requires PHP4.01pl2 or later because it uses include_once
+*/
+
+/*
+ * Concept from daniel.lucazeau@ajornet.com.
+ *
+ * @param db Adodb database connection
+ * @param tables List of tables to join
+ * @rowfields List of fields to display on each row
+ * @colfield Pivot field to slice and display in columns, if we want to calculate
+ * ranges, we pass in an array (see example2)
+ * @where Where clause. Optional.
+ * @aggfield This is the field to sum. Optional.
+ * Since 2.3.1, if you can use your own aggregate function
+ * instead of SUM, eg. $aggfield = 'fieldname'; $aggfn = 'AVG';
+ * @sumlabel Prefix to display in sum columns. Optional.
+ * @aggfn Aggregate function to use (could be AVG, SUM, COUNT)
+ * @showcount Show count of records
+ *
+ * @returns Sql generated
+ */
+
+ function PivotTableSQL(&$db,$tables,$rowfields,$colfield, $where=false,
+ $aggfield = false,$sumlabel='Sum ',$aggfn ='SUM', $showcount = true)
+ {
+ if ($aggfield) $hidecnt = true;
+ else $hidecnt = false;
+
+ $iif = strpos($db->databaseType,'access') !== false;
+ // note - vfp still doesn' work even with IIF enabled || $db->databaseType == 'vfp';
+
+ //$hidecnt = false;
+
+ if ($where) $where = "\nWHERE $where";
+ if (!is_array($colfield)) $colarr = $db->GetCol("select distinct $colfield from $tables $where order by 1");
+ if (!$aggfield) $hidecnt = false;
+
+ $sel = "$rowfields, ";
+ if (is_array($colfield)) {
+ foreach ($colfield as $k => $v) {
+ $k = trim($k);
+ if (!$hidecnt) {
+ $sel .= $iif ?
+ "\n\t$aggfn(IIF($v,1,0)) AS \"$k\", "
+ :
+ "\n\t$aggfn(CASE WHEN $v THEN 1 ELSE 0 END) AS \"$k\", ";
+ }
+ if ($aggfield) {
+ $sel .= $iif ?
+ "\n\t$aggfn(IIF($v,$aggfield,0)) AS \"$sumlabel$k\", "
+ :
+ "\n\t$aggfn(CASE WHEN $v THEN $aggfield ELSE 0 END) AS \"$sumlabel$k\", ";
+ }
+ }
+ } else {
+ foreach ($colarr as $v) {
+ if (!is_numeric($v)) $vq = $db->qstr($v);
+ else $vq = $v;
+ $v = trim($v);
+ if (strlen($v) == 0 ) $v = 'null';
+ if (!$hidecnt) {
+ $sel .= $iif ?
+ "\n\t$aggfn(IIF($colfield=$vq,1,0)) AS \"$v\", "
+ :
+ "\n\t$aggfn(CASE WHEN $colfield=$vq THEN 1 ELSE 0 END) AS \"$v\", ";
+ }
+ if ($aggfield) {
+ if ($hidecnt) $label = $v;
+ else $label = "{$v}_$aggfield";
+ $sel .= $iif ?
+ "\n\t$aggfn(IIF($colfield=$vq,$aggfield,0)) AS \"$label\", "
+ :
+ "\n\t$aggfn(CASE WHEN $colfield=$vq THEN $aggfield ELSE 0 END) AS \"$label\", ";
+ }
+ }
+ }
+ if ($aggfield && $aggfield != '1'){
+ $agg = "$aggfn($aggfield)";
+ $sel .= "\n\t$agg as \"$sumlabel$aggfield\", ";
+ }
+
+ if ($showcount)
+ $sel .= "\n\tSUM(1) as Total";
+ else
+ $sel = substr($sel,0,strlen($sel)-2);
+
+ $sql = "SELECT $sel \nFROM $tables $where \nGROUP BY $rowfields";
+ return $sql;
+ }
+
+/* EXAMPLES USING MS NORTHWIND DATABASE */
+if (0) {
+
+# example1
+#
+# Query the main "product" table
+# Set the rows to CompanyName and QuantityPerUnit
+# and the columns to the Categories
+# and define the joins to link to lookup tables
+# "categories" and "suppliers"
+#
+
+ $sql = PivotTableSQL(
+ $gDB, # adodb connection
+ 'products p ,categories c ,suppliers s', # tables
+ 'CompanyName,QuantityPerUnit', # row fields
+ 'CategoryName', # column fields
+ 'p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID' # joins/where
+);
+ print "<pre>$sql";
+ $rs = $gDB->Execute($sql);
+ rs2html($rs);
+
+/*
+Generated SQL:
+
+SELECT CompanyName,QuantityPerUnit,
+ SUM(CASE WHEN CategoryName='Beverages' THEN 1 ELSE 0 END) AS "Beverages",
+ SUM(CASE WHEN CategoryName='Condiments' THEN 1 ELSE 0 END) AS "Condiments",
+ SUM(CASE WHEN CategoryName='Confections' THEN 1 ELSE 0 END) AS "Confections",
+ SUM(CASE WHEN CategoryName='Dairy Products' THEN 1 ELSE 0 END) AS "Dairy Products",
+ SUM(CASE WHEN CategoryName='Grains/Cereals' THEN 1 ELSE 0 END) AS "Grains/Cereals",
+ SUM(CASE WHEN CategoryName='Meat/Poultry' THEN 1 ELSE 0 END) AS "Meat/Poultry",
+ SUM(CASE WHEN CategoryName='Produce' THEN 1 ELSE 0 END) AS "Produce",
+ SUM(CASE WHEN CategoryName='Seafood' THEN 1 ELSE 0 END) AS "Seafood",
+ SUM(1) as Total
+FROM products p ,categories c ,suppliers s WHERE p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID
+GROUP BY CompanyName,QuantityPerUnit
+*/
+//=====================================================================
+
+# example2
+#
+# Query the main "product" table
+# Set the rows to CompanyName and QuantityPerUnit
+# and the columns to the UnitsInStock for diiferent ranges
+# and define the joins to link to lookup tables
+# "categories" and "suppliers"
+#
+ $sql = PivotTableSQL(
+ $gDB, # adodb connection
+ 'products p ,categories c ,suppliers s', # tables
+ 'CompanyName,QuantityPerUnit', # row fields
+ # column ranges
+array(
+' 0 ' => 'UnitsInStock <= 0',
+"1 to 5" => '0 < UnitsInStock and UnitsInStock <= 5',
+"6 to 10" => '5 < UnitsInStock and UnitsInStock <= 10',
+"11 to 15" => '10 < UnitsInStock and UnitsInStock <= 15',
+"16+" =>'15 < UnitsInStock'
+),
+ ' p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID', # joins/where
+ 'UnitsInStock', # sum this field
+ 'Sum' # sum label prefix
+);
+ print "<pre>$sql";
+ $rs = $gDB->Execute($sql);
+ rs2html($rs);
+ /*
+ Generated SQL:
+
+SELECT CompanyName,QuantityPerUnit,
+ SUM(CASE WHEN UnitsInStock <= 0 THEN UnitsInStock ELSE 0 END) AS "Sum 0 ",
+ SUM(CASE WHEN 0 < UnitsInStock and UnitsInStock <= 5 THEN UnitsInStock ELSE 0 END) AS "Sum 1 to 5",
+ SUM(CASE WHEN 5 < UnitsInStock and UnitsInStock <= 10 THEN UnitsInStock ELSE 0 END) AS "Sum 6 to 10",
+ SUM(CASE WHEN 10 < UnitsInStock and UnitsInStock <= 15 THEN UnitsInStock ELSE 0 END) AS "Sum 11 to 15",
+ SUM(CASE WHEN 15 < UnitsInStock THEN UnitsInStock ELSE 0 END) AS "Sum 16+",
+ SUM(UnitsInStock) AS "Sum UnitsInStock",
+ SUM(1) as Total
+FROM products p ,categories c ,suppliers s WHERE p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID
+GROUP BY CompanyName,QuantityPerUnit
+ */
+}
+?> \ No newline at end of file