[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Phpgroupware-cvs] phpgwapi/inc/adodb/pivottable.inc.php, 1.1.2.1
From: |
nomail |
Subject: |
[Phpgroupware-cvs] phpgwapi/inc/adodb/pivottable.inc.php, 1.1.2.1 |
Date: |
Thu, 30 Dec 2004 05:51:48 +0100 |
Update of /phpgwapi/inc/adodb
Added Files:
Branch: proposed-0_9_18-branch
pivottable.inc.php
date: 2004/12/30 04:51:48; author: skwashd; state: Exp; lines: +164 -0
Log Message:
switch to ADOdb
=====================================================================
<?php
/**
* @version V4.50 6 July 2004 (c) 2000-2004 John Lim (address@hidden). 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 address@hidden
*
* @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. $sumfield =
'AVG(fieldname)';
* @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;
//$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) {
if (!$hidecnt) $sel .= "\n\t$aggfn(CASE WHEN $v THEN 1
ELSE 0 END) AS \"$k\", ";
if ($aggfield)
$sel .= "\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;
if (strlen($v) == 0 ) $v = 'null';
if (!$hidecnt) $sel .= "\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 .= "\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 different 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
*/
}
?>
[Prev in Thread] |
Current Thread |
[Next in Thread] |
- [Phpgroupware-cvs] phpgwapi/inc/adodb/pivottable.inc.php, 1.1.2.1,
nomail <=