C:/lib/adodb/pivottable.inc.php Quellcode

pivottable.inc.php
gehe zur Dokumentation dieser Datei
1 <?php
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 ?>




Korrekturen, Hinweise und Ergänzungen

Bitte scheuen Sie sich nicht und melden Sie, was auf dieser Seite sachlich falsch oder irreführend ist, was ergänzt werden sollte, was fehlt usw. Dazu bitte oben aus dem Menü Seite den Eintrag Support Forum wählen. Es ist eine kostenlose Anmeldung erforderlich, um Anmerkungen zu posten. Unpassende Postings, Spam usw. werden kommentarlos entfernt.