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