File Source for Database.php

PHPExcel_Calculation

# Source for file Database.php

Documentation is available at Database.php

1. <?php
2. /**
3. * PHPExcel
4. *
5. * Copyright (c) 2006 - 2011 PHPExcel
6. *
7. * This library is free software; you can redistribute it and/or
8. * modify it under the terms of the GNU Lesser General Public
9. * License as published by the Free Software Foundation; either
10. * version 2.1 of the License, or (at your option) any later version.
11. *
12. * This library is distributed in the hope that it will be useful,
13. * but WITHOUT ANY WARRANTY; without even the implied warranty of
14. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15. * Lesser General Public License for more details.
16. *
17. * You should have received a copy of the GNU Lesser General Public
18. * License along with this library; if not, write to the Free Software
19. * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
20. *
21. * @category PHPExcel
22. * @package PHPExcel_Calculation
23. * @copyright Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
25. * @version 1.7.6, 2011-02-27
26. */
29. /** PHPExcel root directory */
30. if (! defined('PHPEXCEL_ROOT')) {
31. /**
32. * @ignore
33. */
34. define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35. require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36. }
39. /**
40. * PHPExcel_Calculation_Database
41. *
42. * @category PHPExcel
43. * @package PHPExcel_Calculation
44. * @copyright Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
45. */
46. class PHPExcel_Calculation_Database {
49. private static function __fieldExtract($database,$field) {
50. $field = strtoupper( PHPExcel_Calculation_Functions:: flattenSingleValue($field));
51. $fieldNames = array_map('strtoupper', array_shift($database));
53. if ( is_numeric($field)) {
54. $keys = array_keys($fieldNames);
55. return $keys[$field-1];
56. }
57. $key = array_search($field,$fieldNames);
58. return ($key) ? $key : null;
59. }
61. private static function __filter($database,$criteria) {
62. $fieldNames = array_shift($database);
63. $criteriaNames = array_shift($criteria);
65. // Convert the criteria into a set of AND/OR conditions with [:placeholders]
66. $testConditions = $testValues = array();
67. $testConditionsCount = 0;
68. foreach($criteriaNames as $key => $criteriaName) {
69. $testCondition = array();
70. $testConditionCount = 0;
71. foreach($criteria as $row => $criterion) {
72. if ($criterion[$key] > '') {
73. $testCondition[] = '[:'.$criteriaName.']'.PHPExcel_Calculation_Functions::_ifCondition($criterion[$key]);
74. $testConditionCount++;
75. }
76. }
77. if ($testConditionCount > 1) {
78. $testConditions[] = 'OR('.implode(',',$testCondition).')';
79. $testConditionsCount++;
80. } elseif($testConditionCount == 1) {
81. $testConditions[] = $testCondition[0];
82. $testConditionsCount++;
83. }
84. }
85. if ($testConditionsCount > 1) {
86. $testConditionSet = 'AND('.implode(',',$testConditions).')';
87. } elseif($testConditionsCount == 1) {
88. $testConditionSet = $testConditions[0];
89. }
91. // Loop through each row of the database
92. foreach($database as $dataRow => $dataValues) {
93. // Substitute actual values from the database row for our [:placeholders]
94. $testConditionList = $testConditionSet;
95. foreach($criteriaNames as $key => $criteriaName) {
96. $k = array_search($criteriaName,$fieldNames);
97. if (isset($dataValues[$k])) {
98. $dataValue = $dataValues[$k];
99. $dataValue = ( is_string($dataValue)) ? PHPExcel_Calculation::_wrapResult( strtoupper($dataValue)) : $dataValue;
100. $testConditionList = str_replace('[:'.$criteriaName.']',$dataValue,$testConditionList);
101. }
102. }
103. // evaluate the criteria against the row data
104. $result = PHPExcel_Calculation:: getInstance()->_calculateFormulaValue('='.$testConditionList);
105. // If the row failed to meet the criteria, remove it from the database
106. if (!$result) {
107. unset($database[$dataRow]);
108. }
109. }
111. return $database;
112. }
115. /**
116. * DAVERAGE
117. *
118. */
119. public static function DAVERAGE($database,$field,$criteria) {
120. $field = self::__fieldExtract($database,$field);
121. if ( is_null($field)) {
122. return NULL;
123. }
125. // reduce the database to a set of rows that match all the criteria
126. $database = self::__filter($database,$criteria);
127. // extract an array of values for the requested column
128. $colData = array();
129. foreach($database as $row) {
130. $colData[] = $row[$field];
131. }
133. // Return
134. return PHPExcel_Calculation_Statistical:: AVERAGE($colData);
135. } // function DAVERAGE()
137. /**
138. * DCOUNT
139. *
140. */
141. public static function DCOUNT($database,$field,$criteria) {
142. $field = self::__fieldExtract($database,$field);
143. if ( is_null($field)) {
144. return NULL;
145. }
147. // reduce the database to a set of rows that match all the criteria
148. $database = self::__filter($database,$criteria);
149. // extract an array of values for the requested column
150. $colData = array();
151. foreach($database as $row) {
152. $colData[] = $row[$field];
153. }
155. // Return
156. return PHPExcel_Calculation_Statistical:: COUNT($colData);
157. } // function DCOUNT()
159. /**
160. * DCOUNTA
161. *
162. */
163. public static function DCOUNTA($database,$field,$criteria) {
164. $field = self::__fieldExtract($database,$field);
165. if ( is_null($field)) {
166. return NULL;
167. }
169. // reduce the database to a set of rows that match all the criteria
170. $database = self::__filter($database,$criteria);
171. // extract an array of values for the requested column
172. $colData = array();
173. foreach($database as $row) {
174. $colData[] = $row[$field];
175. }
177. // Return
178. return PHPExcel_Calculation_Statistical:: COUNTA($colData);
179. } // function DCOUNTA()
181. /**
182. * DGET
183. *
184. */
185. public static function DGET($database,$field,$criteria) {
186. $field = self::__fieldExtract($database,$field);
187. if ( is_null($field)) {
188. return NULL;
189. }
191. // reduce the database to a set of rows that match all the criteria
192. $database = self::__filter($database,$criteria);
193. // extract an array of values for the requested column
194. $colData = array();
195. foreach($database as $row) {
196. $colData[] = $row[$field];
197. }
199. // Return
200. if ( count($colData) > 1) {
201. return PHPExcel_Calculation_Functions:: NaN();
202. }
204. return $colData[0];
205. } // function DGET()
207. /**
208. * DMAX
209. *
210. */
211. public static function DMAX($database,$field,$criteria) {
212. $field = self::__fieldExtract($database,$field);
213. if ( is_null($field)) {
214. return NULL;
215. }
217. // reduce the database to a set of rows that match all the criteria
218. $database = self::__filter($database,$criteria);
219. // extract an array of values for the requested column
220. $colData = array();
221. foreach($database as $row) {
222. $colData[] = $row[$field];
223. }
225. // Return
226. return PHPExcel_Calculation_Statistical:: MAX($colData);
227. } // function DMAX()
229. /**
230. * DMIN
231. *
232. */
233. public static function DMIN($database,$field,$criteria) {
234. $field = self::__fieldExtract($database,$field);
235. if ( is_null($field)) {
236. return NULL;
237. }
239. // reduce the database to a set of rows that match all the criteria
240. $database = self::__filter($database,$criteria);
241. // extract an array of values for the requested column
242. $colData = array();
243. foreach($database as $row) {
244. $colData[] = $row[$field];
245. }
247. // Return
248. return PHPExcel_Calculation_Statistical:: MIN($colData);
249. } // function DMIN()
251. /**
252. * DPRODUCT
253. *
254. */
255. public static function DPRODUCT($database,$field,$criteria) {
256. $field = self::__fieldExtract($database,$field);
257. if ( is_null($field)) {
258. return NULL;
259. }
261. // reduce the database to a set of rows that match all the criteria
262. $database = self::__filter($database,$criteria);
263. // extract an array of values for the requested column
264. $colData = array();
265. foreach($database as $row) {
266. $colData[] = $row[$field];
267. }
269. // Return
270. return PHPExcel_Calculation_MathTrig:: PRODUCT($colData);
271. } // function DPRODUCT()
273. /**
274. * DSTDEV
275. *
276. */
277. public static function DSTDEV($database,$field,$criteria) {
278. $field = self::__fieldExtract($database,$field);
279. if ( is_null($field)) {
280. return NULL;
281. }
283. // reduce the database to a set of rows that match all the criteria
284. $database = self::__filter($database,$criteria);
285. // extract an array of values for the requested column
286. $colData = array();
287. foreach($database as $row) {
288. $colData[] = $row[$field];
289. }
291. // Return
292. return PHPExcel_Calculation_Statistical:: STDEV($colData);
293. } // function DSTDEV()
295. /**
296. * DSTDEVP
297. *
298. */
299. public static function DSTDEVP($database,$field,$criteria) {
300. $field = self::__fieldExtract($database,$field);
301. if ( is_null($field)) {
302. return NULL;
303. }
305. // reduce the database to a set of rows that match all the criteria
306. $database = self::__filter($database,$criteria);
307. // extract an array of values for the requested column
308. $colData = array();
309. foreach($database as $row) {
310. $colData[] = $row[$field];
311. }
313. // Return
314. return PHPExcel_Calculation_Statistical:: STDEVP($colData);
315. } // function DSTDEVP()
317. /**
318. * DSUM
319. *
320. */
321. public static function DSUM($database,$field,$criteria) {
322. $field = self::__fieldExtract($database,$field);
323. if ( is_null($field)) {
324. return NULL;
325. }
327. // reduce the database to a set of rows that match all the criteria
328. $database = self::__filter($database,$criteria);
329. // extract an array of values for the requested column
330. $colData = array();
331. foreach($database as $row) {
332. $colData[] = $row[$field];
333. }
335. // Return
336. return PHPExcel_Calculation_MathTrig:: SUM($colData);
337. } // function DSUM()
339. /**
340. * DVAR
341. *
342. */
343. public static function DVAR($database,$field,$criteria) {
344. $field = self::__fieldExtract($database,$field);
345. if ( is_null($field)) {
346. return NULL;
347. }
349. // reduce the database to a set of rows that match all the criteria
350. $database = self::__filter($database,$criteria);
351. // extract an array of values for the requested column
352. $colData = array();
353. foreach($database as $row) {
354. $colData[] = $row[$field];
355. }
357. // Return
358. return PHPExcel_Calculation_Statistical:: VARFunc($colData);
359. } // function DVAR()
361. /**
362. * DVARP
363. *
364. */
365. public static function DVARP($database,$field,$criteria) {
366. $field = self::__fieldExtract($database,$field);
367. if ( is_null($field)) {
368. return NULL;
369. }
371. // reduce the database to a set of rows that match all the criteria
372. $database = self::__filter($database,$criteria);
373. // extract an array of values for the requested column
374. $colData = array();
375. foreach($database as $row) {
376. $colData[] = $row[$field];
377. }
379. // Return
380. return PHPExcel_Calculation_Statistical:: VARP($colData);
381. } // function DVARP()
384. } // class PHPExcel_Calculation_Database

Documentation generated on Sun, 27 Feb 2011 16:29:19 -0800 by phpDocumentor 1.4.3