35b9fe41af6b02830c98e5462ff38a9b051414cf
[Packages/TYPO3.CMS.git] / typo3 / sysext / adodb / adodb / pivottable.inc.php
1 <?php
2 /**
3 * @version V4.81 3 May 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 * Latest version is available at http://php.weblogs.com
11 *
12 * Requires PHP4.01pl2 or later because it uses include_once
13 */
14
15 /*
16 * Concept from daniel.lucazeau@ajornet.com.
17 *
18 * @param db Adodb database connection
19 * @param tables List of tables to join
20 * @rowfields List of fields to display on each row
21 * @colfield Pivot field to slice and display in columns, if we want to calculate
22 * ranges, we pass in an array (see example2)
23 * @where Where clause. Optional.
24 * @aggfield This is the field to sum. Optional.
25 * Since 2.3.1, if you can use your own aggregate function
26 * instead of SUM, eg. $aggfield = 'fieldname'; $aggfn = 'AVG';
27 * @sumlabel Prefix to display in sum columns. Optional.
28 * @aggfn Aggregate function to use (could be AVG, SUM, COUNT)
29 * @showcount Show count of records
30 *
31 * @returns Sql generated
32 */
33
34 function PivotTableSQL(&$db,$tables,$rowfields,$colfield, $where=false,
35 $aggfield = false,$sumlabel='Sum ',$aggfn ='SUM', $showcount = true)
36 {
37 if ($aggfield) $hidecnt = true;
38 else $hidecnt = false;
39
40 $iif = strpos($db->databaseType,'access') !== false;
41 // note - vfp still doesn' work even with IIF enabled || $db->databaseType == 'vfp';
42
43 //$hidecnt = false;
44
45 if ($where) $where = "\nWHERE $where";
46 if (!is_array($colfield)) $colarr = $db->GetCol("select distinct $colfield from $tables $where order by 1");
47 if (!$aggfield) $hidecnt = false;
48
49 $sel = "$rowfields, ";
50 if (is_array($colfield)) {
51 foreach ($colfield as $k => $v) {
52 $k = trim($k);
53 if (!$hidecnt) {
54 $sel .= $iif ?
55 "\n\t$aggfn(IIF($v,1,0)) AS \"$k\", "
56 :
57 "\n\t$aggfn(CASE WHEN $v THEN 1 ELSE 0 END) AS \"$k\", ";
58 }
59 if ($aggfield) {
60 $sel .= $iif ?
61 "\n\t$aggfn(IIF($v,$aggfield,0)) AS \"$sumlabel$k\", "
62 :
63 "\n\t$aggfn(CASE WHEN $v THEN $aggfield ELSE 0 END) AS \"$sumlabel$k\", ";
64 }
65 }
66 } else {
67 foreach ($colarr as $v) {
68 if (!is_numeric($v)) $vq = $db->qstr($v);
69 else $vq = $v;
70 $v = trim($v);
71 if (strlen($v) == 0 ) $v = 'null';
72 if (!$hidecnt) {
73 $sel .= $iif ?
74 "\n\t$aggfn(IIF($colfield=$vq,1,0)) AS \"$v\", "
75 :
76 "\n\t$aggfn(CASE WHEN $colfield=$vq THEN 1 ELSE 0 END) AS \"$v\", ";
77 }
78 if ($aggfield) {
79 if ($hidecnt) $label = $v;
80 else $label = "{$v}_$aggfield";
81 $sel .= $iif ?
82 "\n\t$aggfn(IIF($colfield=$vq,$aggfield,0)) AS \"$label\", "
83 :
84 "\n\t$aggfn(CASE WHEN $colfield=$vq THEN $aggfield ELSE 0 END) AS \"$label\", ";
85 }
86 }
87 }
88 if ($aggfield && $aggfield != '1'){
89 $agg = "$aggfn($aggfield)";
90 $sel .= "\n\t$agg as \"$sumlabel$aggfield\", ";
91 }
92
93 if ($showcount)
94 $sel .= "\n\tSUM(1) as Total";
95 else
96 $sel = substr($sel,0,strlen($sel)-2);
97
98 $sql = "SELECT $sel \nFROM $tables $where \nGROUP BY $rowfields";
99 return $sql;
100 }
101
102 /* EXAMPLES USING MS NORTHWIND DATABASE */
103 if (0) {
104
105 # example1
106 #
107 # Query the main "product" table
108 # Set the rows to CompanyName and QuantityPerUnit
109 # and the columns to the Categories
110 # and define the joins to link to lookup tables
111 # "categories" and "suppliers"
112 #
113
114 $sql = PivotTableSQL(
115 $gDB, # adodb connection
116 'products p ,categories c ,suppliers s', # tables
117 'CompanyName,QuantityPerUnit', # row fields
118 'CategoryName', # column fields
119 'p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID' # joins/where
120 );
121 print "<pre>$sql";
122 $rs = $gDB->Execute($sql);
123 rs2html($rs);
124
125 /*
126 Generated SQL:
127
128 SELECT CompanyName,QuantityPerUnit,
129 SUM(CASE WHEN CategoryName='Beverages' THEN 1 ELSE 0 END) AS "Beverages",
130 SUM(CASE WHEN CategoryName='Condiments' THEN 1 ELSE 0 END) AS "Condiments",
131 SUM(CASE WHEN CategoryName='Confections' THEN 1 ELSE 0 END) AS "Confections",
132 SUM(CASE WHEN CategoryName='Dairy Products' THEN 1 ELSE 0 END) AS "Dairy Products",
133 SUM(CASE WHEN CategoryName='Grains/Cereals' THEN 1 ELSE 0 END) AS "Grains/Cereals",
134 SUM(CASE WHEN CategoryName='Meat/Poultry' THEN 1 ELSE 0 END) AS "Meat/Poultry",
135 SUM(CASE WHEN CategoryName='Produce' THEN 1 ELSE 0 END) AS "Produce",
136 SUM(CASE WHEN CategoryName='Seafood' THEN 1 ELSE 0 END) AS "Seafood",
137 SUM(1) as Total
138 FROM products p ,categories c ,suppliers s WHERE p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID
139 GROUP BY CompanyName,QuantityPerUnit
140 */
141 //=====================================================================
142
143 # example2
144 #
145 # Query the main "product" table
146 # Set the rows to CompanyName and QuantityPerUnit
147 # and the columns to the UnitsInStock for diiferent ranges
148 # and define the joins to link to lookup tables
149 # "categories" and "suppliers"
150 #
151 $sql = PivotTableSQL(
152 $gDB, # adodb connection
153 'products p ,categories c ,suppliers s', # tables
154 'CompanyName,QuantityPerUnit', # row fields
155 # column ranges
156 array(
157 ' 0 ' => 'UnitsInStock <= 0',
158 "1 to 5" => '0 < UnitsInStock and UnitsInStock <= 5',
159 "6 to 10" => '5 < UnitsInStock and UnitsInStock <= 10',
160 "11 to 15" => '10 < UnitsInStock and UnitsInStock <= 15',
161 "16+" =>'15 < UnitsInStock'
162 ),
163 ' p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID', # joins/where
164 'UnitsInStock', # sum this field
165 'Sum' # sum label prefix
166 );
167 print "<pre>$sql";
168 $rs = $gDB->Execute($sql);
169 rs2html($rs);
170 /*
171 Generated SQL:
172
173 SELECT CompanyName,QuantityPerUnit,
174 SUM(CASE WHEN UnitsInStock <= 0 THEN UnitsInStock ELSE 0 END) AS "Sum 0 ",
175 SUM(CASE WHEN 0 < UnitsInStock and UnitsInStock <= 5 THEN UnitsInStock ELSE 0 END) AS "Sum 1 to 5",
176 SUM(CASE WHEN 5 < UnitsInStock and UnitsInStock <= 10 THEN UnitsInStock ELSE 0 END) AS "Sum 6 to 10",
177 SUM(CASE WHEN 10 < UnitsInStock and UnitsInStock <= 15 THEN UnitsInStock ELSE 0 END) AS "Sum 11 to 15",
178 SUM(CASE WHEN 15 < UnitsInStock THEN UnitsInStock ELSE 0 END) AS "Sum 16+",
179 SUM(UnitsInStock) AS "Sum UnitsInStock",
180 SUM(1) as Total
181 FROM products p ,categories c ,suppliers s WHERE p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID
182 GROUP BY CompanyName,QuantityPerUnit
183 */
184 }
185 ?>