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