TYPO3 CMS  TYPO3_7-6
pivottable.inc.php
Go to the documentation of this file.
1 <?php
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 
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 #
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 }
if(isset($_REQUEST['nrows'])) else $rs
Definition: server.php:94
PivotTableSQL(&$db, $tables, $rowfields, $colfield, $where=false, $aggfield=false, $sumlabel='Sum ', $aggfn='SUM', $showcount=true)
rs2html(&$rs, $ztabhtml=false, $zheaderarray=false, $htmlspecialchars=true, $echo=true)
Definition: tohtml.inc.php:43
$sql
Definition: server.php:84