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