visualizer / vendor / phpoffice / phpexcel / Classes / PHPExcel / Calculation / Database.php

Database.php in Visualizer – Tables & Charts Manager with Built-in AI Generator 3.1.2, at vendor/phpoffice/phpexcel/Classes/PHPExcel/Calculation/Database.php

677 lines 32.5 KB

Raw Download Zip

1	<?php
2
3	/** PHPExcel root directory */
4	if (!defined('PHPEXCEL_ROOT')) {
5	/**
6	* @ignore
7	*/
8	define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
9	require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
10	}
11
12	/**
13	* PHPExcel_Calculation_Database
14	*
15	* Copyright (c) 2006 - 2015 PHPExcel
16	*
17	* This library is free software; you can redistribute it and/or
18	* modify it under the terms of the GNU Lesser General Public
19	* License as published by the Free Software Foundation; either
20	* version 2.1 of the License, or (at your option) any later version.
21	*
22	* This library is distributed in the hope that it will be useful,
23	* but WITHOUT ANY WARRANTY; without even the implied warranty of
24	* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
25	* Lesser General Public License for more details.
26	*
27	* You should have received a copy of the GNU Lesser General Public
28	* License along with this library; if not, write to the Free Software
29	* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
30	*
31	* @category PHPExcel
32	* @package PHPExcel_Calculation
33	* @copyright Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel)
34	* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
35	* @version ##VERSION##, ##DATE##
36	*/
37	class PHPExcel_Calculation_Database
38	{
39	/**
40	* fieldExtract
41	*
42	* Extracts the column ID to use for the data field.
43	*
44	* @access private
45	* @param mixed[] $database The range of cells that makes up the list or database.
46	* A database is a list of related data in which rows of related
47	* information are records, and columns of data are fields. The
48	* first row of the list contains labels for each column.
49	* @param mixed $field Indicates which column is used in the function. Enter the
50	* column label enclosed between double quotation marks, such as
51	* "Age" or "Yield," or a number (without quotation marks) that
52	* represents the position of the column within the list: 1 for
53	* the first column, 2 for the second column, and so on.
54	* @return string\|NULL
55	*
56	*/
57	private static function fieldExtract($database, $field)
58	{
59	$field = strtoupper(PHPExcel_Calculation_Functions::flattenSingleValue($field));
60	$fieldNames = array_map('strtoupper', array_shift($database));
61
62	if (is_numeric($field)) {
63	$keys = array_keys($fieldNames);
64	return $keys[$field-1];
65	}
66	$key = array_search($field, $fieldNames);
67	return ($key) ? $key : null;
68	}
69
70	/**
71	* filter
72	*
73	* Parses the selection criteria, extracts the database rows that match those criteria, and
74	* returns that subset of rows.
75	*
76	* @access private
77	* @param mixed[] $database The range of cells that makes up the list or database.
78	* A database is a list of related data in which rows of related
79	* information are records, and columns of data are fields. The
80	* first row of the list contains labels for each column.
81	* @param mixed[] $criteria The range of cells that contains the conditions you specify.
82	* You can use any range for the criteria argument, as long as it
83	* includes at least one column label and at least one cell below
84	* the column label in which you specify a condition for the
85	* column.
86	* @return array of mixed
87	*
88	*/
89	private static function filter($database, $criteria)
90	{
91	$fieldNames = array_shift($database);
92	$criteriaNames = array_shift($criteria);
93
94	// Convert the criteria into a set of AND/OR conditions with [:placeholders]
95	$testConditions = $testValues = array();
96	$testConditionsCount = 0;
97	foreach ($criteriaNames as $key => $criteriaName) {
98	$testCondition = array();
99	$testConditionCount = 0;
100	foreach ($criteria as $row => $criterion) {
101	if ($criterion[$key] > '') {
102	$testCondition[] = '[:'.$criteriaName.']'.PHPExcel_Calculation_Functions::ifCondition($criterion[$key]);
103	$testConditionCount++;
104	}
105	}
106	if ($testConditionCount > 1) {
107	$testConditions[] = 'OR(' . implode(',', $testCondition) . ')';
108	$testConditionsCount++;
109	} elseif ($testConditionCount == 1) {
110	$testConditions[] = $testCondition[0];
111	$testConditionsCount++;
112	}
113	}
114
115	if ($testConditionsCount > 1) {
116	$testConditionSet = 'AND(' . implode(',', $testConditions) . ')';
117	} elseif ($testConditionsCount == 1) {
118	$testConditionSet = $testConditions[0];
119	}
120
121	// Loop through each row of the database
122	foreach ($database as $dataRow => $dataValues) {
123	// Substitute actual values from the database row for our [:placeholders]
124	$testConditionList = $testConditionSet;
125	foreach ($criteriaNames as $key => $criteriaName) {
126	$k = array_search($criteriaName, $fieldNames);
127	if (isset($dataValues[$k])) {
128	$dataValue = $dataValues[$k];
129	$dataValue = (is_string($dataValue)) ? PHPExcel_Calculation::wrapResult(strtoupper($dataValue)) : $dataValue;
130	$testConditionList = str_replace('[:' . $criteriaName . ']', $dataValue, $testConditionList);
131	}
132	}
133	// evaluate the criteria against the row data
134	$result = PHPExcel_Calculation::getInstance()->_calculateFormulaValue('='.$testConditionList);
135	// If the row failed to meet the criteria, remove it from the database
136	if (!$result) {
137	unset($database[$dataRow]);
138	}
139	}
140
141	return $database;
142	}
143
144
145	private static function getFilteredColumn($database, $field, $criteria)
146	{
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	}
154
155	return $colData;
156	}
157
158	/**
159	* DAVERAGE
160	*
161	* Averages the values in a column of a list or database that match conditions you specify.
162	*
163	* Excel Function:
164	* DAVERAGE(database,field,criteria)
165	*
166	* @access public
167	* @category Database Functions
168	* @param mixed[] $database The range of cells that makes up the list or database.
169	* A database is a list of related data in which rows of related
170	* information are records, and columns of data are fields. The
171	* first row of the list contains labels for each column.
172	* @param string\|integer $field Indicates which column is used in the function. Enter the
173	* column label enclosed between double quotation marks, such as
174	* "Age" or "Yield," or a number (without quotation marks) that
175	* represents the position of the column within the list: 1 for
176	* the first column, 2 for the second column, and so on.
177	* @param mixed[] $criteria The range of cells that contains the conditions you specify.
178	* You can use any range for the criteria argument, as long as it
179	* includes at least one column label and at least one cell below
180	* the column label in which you specify a condition for the
181	* column.
182	* @return float
183	*
184	*/
185	public static function DAVERAGE($database, $field, $criteria)
186	{
187	$field = self::fieldExtract($database, $field);
188	if (is_null($field)) {
189	return null;
190	}
191
192	// Return
193	return PHPExcel_Calculation_Statistical::AVERAGE(
194	self::getFilteredColumn($database, $field, $criteria)
195	);
196	}
197
198
199	/**
200	* DCOUNT
201	*
202	* Counts the cells that contain numbers in a column of a list or database that match conditions
203	* that you specify.
204	*
205	* Excel Function:
206	* DCOUNT(database,[field],criteria)
207	*
208	* Excel Function:
209	* DAVERAGE(database,field,criteria)
210	*
211	* @access public
212	* @category Database Functions
213	* @param mixed[] $database The range of cells that makes up the list or database.
214	* A database is a list of related data in which rows of related
215	* information are records, and columns of data are fields. The
216	* first row of the list contains labels for each column.
217	* @param string\|integer $field Indicates which column is used in the function. Enter the
218	* column label enclosed between double quotation marks, such as
219	* "Age" or "Yield," or a number (without quotation marks) that
220	* represents the position of the column within the list: 1 for
221	* the first column, 2 for the second column, and so on.
222	* @param mixed[] $criteria The range of cells that contains the conditions you specify.
223	* You can use any range for the criteria argument, as long as it
224	* includes at least one column label and at least one cell below
225	* the column label in which you specify a condition for the
226	* column.
227	* @return integer
228	*
229	* @TODO The field argument is optional. If field is omitted, DCOUNT counts all records in the
230	* database that match the criteria.
231	*
232	*/
233	public static function DCOUNT($database, $field, $criteria)
234	{
235	$field = self::fieldExtract($database, $field);
236	if (is_null($field)) {
237	return null;
238	}
239
240	// Return
241	return PHPExcel_Calculation_Statistical::COUNT(
242	self::getFilteredColumn($database, $field, $criteria)
243	);
244	}
245
246
247	/**
248	* DCOUNTA
249	*
250	* Counts the nonblank cells in a column of a list or database that match conditions that you specify.
251	*
252	* Excel Function:
253	* DCOUNTA(database,[field],criteria)
254	*
255	* @access public
256	* @category Database Functions
257	* @param mixed[] $database The range of cells that makes up the list or database.
258	* A database is a list of related data in which rows of related
259	* information are records, and columns of data are fields. The
260	* first row of the list contains labels for each column.
261	* @param string\|integer $field Indicates which column is used in the function. Enter the
262	* column label enclosed between double quotation marks, such as
263	* "Age" or "Yield," or a number (without quotation marks) that
264	* represents the position of the column within the list: 1 for
265	* the first column, 2 for the second column, and so on.
266	* @param mixed[] $criteria The range of cells that contains the conditions you specify.
267	* You can use any range for the criteria argument, as long as it
268	* includes at least one column label and at least one cell below
269	* the column label in which you specify a condition for the
270	* column.
271	* @return integer
272	*
273	* @TODO The field argument is optional. If field is omitted, DCOUNTA counts all records in the
274	* database that match the criteria.
275	*
276	*/
277	public static function DCOUNTA($database, $field, $criteria)
278	{
279	$field = self::fieldExtract($database, $field);
280	if (is_null($field)) {
281	return null;
282	}
283
284	// reduce the database to a set of rows that match all the criteria
285	$database = self::filter($database, $criteria);
286	// extract an array of values for the requested column
287	$colData = array();
288	foreach ($database as $row) {
289	$colData[] = $row[$field];
290	}
291
292	// Return
293	return PHPExcel_Calculation_Statistical::COUNTA(
294	self::getFilteredColumn($database, $field, $criteria)
295	);
296	}
297
298
299	/**
300	* DGET
301	*
302	* Extracts a single value from a column of a list or database that matches conditions that you
303	* specify.
304	*
305	* Excel Function:
306	* DGET(database,field,criteria)
307	*
308	* @access public
309	* @category Database Functions
310	* @param mixed[] $database The range of cells that makes up the list or database.
311	* A database is a list of related data in which rows of related
312	* information are records, and columns of data are fields. The
313	* first row of the list contains labels for each column.
314	* @param string\|integer $field Indicates which column is used in the function. Enter the
315	* column label enclosed between double quotation marks, such as
316	* "Age" or "Yield," or a number (without quotation marks) that
317	* represents the position of the column within the list: 1 for
318	* the first column, 2 for the second column, and so on.
319	* @param mixed[] $criteria The range of cells that contains the conditions you specify.
320	* You can use any range for the criteria argument, as long as it
321	* includes at least one column label and at least one cell below
322	* the column label in which you specify a condition for the
323	* column.
324	* @return mixed
325	*
326	*/
327	public static function DGET($database, $field, $criteria)
328	{
329	$field = self::fieldExtract($database, $field);
330	if (is_null($field)) {
331	return null;
332	}
333
334	// Return
335	$colData = self::getFilteredColumn($database, $field, $criteria);
336	if (count($colData) > 1) {
337	return PHPExcel_Calculation_Functions::NaN();
338	}
339
340	return $colData[0];
341	}
342
343
344	/**
345	* DMAX
346	*
347	* Returns the largest number in a column of a list or database that matches conditions you that
348	* specify.
349	*
350	* Excel Function:
351	* DMAX(database,field,criteria)
352	*
353	* @access public
354	* @category Database Functions
355	* @param mixed[] $database The range of cells that makes up the list or database.
356	* A database is a list of related data in which rows of related
357	* information are records, and columns of data are fields. The
358	* first row of the list contains labels for each column.
359	* @param string\|integer $field Indicates which column is used in the function. Enter the
360	* column label enclosed between double quotation marks, such as
361	* "Age" or "Yield," or a number (without quotation marks) that
362	* represents the position of the column within the list: 1 for
363	* the first column, 2 for the second column, and so on.
364	* @param mixed[] $criteria The range of cells that contains the conditions you specify.
365	* You can use any range for the criteria argument, as long as it
366	* includes at least one column label and at least one cell below
367	* the column label in which you specify a condition for the
368	* column.
369	* @return float
370	*
371	*/
372	public static function DMAX($database, $field, $criteria)
373	{
374	$field = self::fieldExtract($database, $field);
375	if (is_null($field)) {
376	return null;
377	}
378
379	// Return
380	return PHPExcel_Calculation_Statistical::MAX(
381	self::getFilteredColumn($database, $field, $criteria)
382	);
383	}
384
385
386	/**
387	* DMIN
388	*
389	* Returns the smallest number in a column of a list or database that matches conditions you that
390	* specify.
391	*
392	* Excel Function:
393	* DMIN(database,field,criteria)
394	*
395	* @access public
396	* @category Database Functions
397	* @param mixed[] $database The range of cells that makes up the list or database.
398	* A database is a list of related data in which rows of related
399	* information are records, and columns of data are fields. The
400	* first row of the list contains labels for each column.
401	* @param string\|integer $field Indicates which column is used in the function. Enter the
402	* column label enclosed between double quotation marks, such as
403	* "Age" or "Yield," or a number (without quotation marks) that
404	* represents the position of the column within the list: 1 for
405	* the first column, 2 for the second column, and so on.
406	* @param mixed[] $criteria The range of cells that contains the conditions you specify.
407	* You can use any range for the criteria argument, as long as it
408	* includes at least one column label and at least one cell below
409	* the column label in which you specify a condition for the
410	* column.
411	* @return float
412	*
413	*/
414	public static function DMIN($database, $field, $criteria)
415	{
416	$field = self::fieldExtract($database, $field);
417	if (is_null($field)) {
418	return null;
419	}
420
421	// Return
422	return PHPExcel_Calculation_Statistical::MIN(
423	self::getFilteredColumn($database, $field, $criteria)
424	);
425	}
426
427
428	/**
429	* DPRODUCT
430	*
431	* Multiplies the values in a column of a list or database that match conditions that you specify.
432	*
433	* Excel Function:
434	* DPRODUCT(database,field,criteria)
435	*
436	* @access public
437	* @category Database Functions
438	* @param mixed[] $database The range of cells that makes up the list or database.
439	* A database is a list of related data in which rows of related
440	* information are records, and columns of data are fields. The
441	* first row of the list contains labels for each column.
442	* @param string\|integer $field Indicates which column is used in the function. Enter the
443	* column label enclosed between double quotation marks, such as
444	* "Age" or "Yield," or a number (without quotation marks) that
445	* represents the position of the column within the list: 1 for
446	* the first column, 2 for the second column, and so on.
447	* @param mixed[] $criteria The range of cells that contains the conditions you specify.
448	* You can use any range for the criteria argument, as long as it
449	* includes at least one column label and at least one cell below
450	* the column label in which you specify a condition for the
451	* column.
452	* @return float
453	*
454	*/
455	public static function DPRODUCT($database, $field, $criteria)
456	{
457	$field = self::fieldExtract($database, $field);
458	if (is_null($field)) {
459	return null;
460	}
461
462	// Return
463	return PHPExcel_Calculation_MathTrig::PRODUCT(
464	self::getFilteredColumn($database, $field, $criteria)
465	);
466	}
467
468
469	/**
470	* DSTDEV
471	*
472	* Estimates the standard deviation of a population based on a sample by using the numbers in a
473	* column of a list or database that match conditions that you specify.
474	*
475	* Excel Function:
476	* DSTDEV(database,field,criteria)
477	*
478	* @access public
479	* @category Database Functions
480	* @param mixed[] $database The range of cells that makes up the list or database.
481	* A database is a list of related data in which rows of related
482	* information are records, and columns of data are fields. The
483	* first row of the list contains labels for each column.
484	* @param string\|integer $field Indicates which column is used in the function. Enter the
485	* column label enclosed between double quotation marks, such as
486	* "Age" or "Yield," or a number (without quotation marks) that
487	* represents the position of the column within the list: 1 for
488	* the first column, 2 for the second column, and so on.
489	* @param mixed[] $criteria The range of cells that contains the conditions you specify.
490	* You can use any range for the criteria argument, as long as it
491	* includes at least one column label and at least one cell below
492	* the column label in which you specify a condition for the
493	* column.
494	* @return float
495	*
496	*/
497	public static function DSTDEV($database, $field, $criteria)
498	{
499	$field = self::fieldExtract($database, $field);
500	if (is_null($field)) {
501	return null;
502	}
503
504	// Return
505	return PHPExcel_Calculation_Statistical::STDEV(
506	self::getFilteredColumn($database, $field, $criteria)
507	);
508	}
509
510
511	/**
512	* DSTDEVP
513	*
514	* Calculates the standard deviation of a population based on the entire population by using the
515	* numbers in a column of a list or database that match conditions that you specify.
516	*
517	* Excel Function:
518	* DSTDEVP(database,field,criteria)
519	*
520	* @access public
521	* @category Database Functions
522	* @param mixed[] $database The range of cells that makes up the list or database.
523	* A database is a list of related data in which rows of related
524	* information are records, and columns of data are fields. The
525	* first row of the list contains labels for each column.
526	* @param string\|integer $field Indicates which column is used in the function. Enter the
527	* column label enclosed between double quotation marks, such as
528	* "Age" or "Yield," or a number (without quotation marks) that
529	* represents the position of the column within the list: 1 for
530	* the first column, 2 for the second column, and so on.
531	* @param mixed[] $criteria The range of cells that contains the conditions you specify.
532	* You can use any range for the criteria argument, as long as it
533	* includes at least one column label and at least one cell below
534	* the column label in which you specify a condition for the
535	* column.
536	* @return float
537	*
538	*/
539	public static function DSTDEVP($database, $field, $criteria)
540	{
541	$field = self::fieldExtract($database, $field);
542	if (is_null($field)) {
543	return null;
544	}
545
546	// Return
547	return PHPExcel_Calculation_Statistical::STDEVP(
548	self::getFilteredColumn($database, $field, $criteria)
549	);
550	}
551
552
553	/**
554	* DSUM
555	*
556	* Adds the numbers in a column of a list or database that match conditions that you specify.
557	*
558	* Excel Function:
559	* DSUM(database,field,criteria)
560	*
561	* @access public
562	* @category Database Functions
563	* @param mixed[] $database The range of cells that makes up the list or database.
564	* A database is a list of related data in which rows of related
565	* information are records, and columns of data are fields. The
566	* first row of the list contains labels for each column.
567	* @param string\|integer $field Indicates which column is used in the function. Enter the
568	* column label enclosed between double quotation marks, such as
569	* "Age" or "Yield," or a number (without quotation marks) that
570	* represents the position of the column within the list: 1 for
571	* the first column, 2 for the second column, and so on.
572	* @param mixed[] $criteria The range of cells that contains the conditions you specify.
573	* You can use any range for the criteria argument, as long as it
574	* includes at least one column label and at least one cell below
575	* the column label in which you specify a condition for the
576	* column.
577	* @return float
578	*
579	*/
580	public static function DSUM($database, $field, $criteria)
581	{
582	$field = self::fieldExtract($database, $field);
583	if (is_null($field)) {
584	return null;
585	}
586
587	// Return
588	return PHPExcel_Calculation_MathTrig::SUM(
589	self::getFilteredColumn($database, $field, $criteria)
590	);
591	}
592
593
594	/**
595	* DVAR
596	*
597	* Estimates the variance of a population based on a sample by using the numbers in a column
598	* of a list or database that match conditions that you specify.
599	*
600	* Excel Function:
601	* DVAR(database,field,criteria)
602	*
603	* @access public
604	* @category Database Functions
605	* @param mixed[] $database The range of cells that makes up the list or database.
606	* A database is a list of related data in which rows of related
607	* information are records, and columns of data are fields. The
608	* first row of the list contains labels for each column.
609	* @param string\|integer $field Indicates which column is used in the function. Enter the
610	* column label enclosed between double quotation marks, such as
611	* "Age" or "Yield," or a number (without quotation marks) that
612	* represents the position of the column within the list: 1 for
613	* the first column, 2 for the second column, and so on.
614	* @param mixed[] $criteria The range of cells that contains the conditions you specify.
615	* You can use any range for the criteria argument, as long as it
616	* includes at least one column label and at least one cell below
617	* the column label in which you specify a condition for the
618	* column.
619	* @return float
620	*
621	*/
622	public static function DVAR($database, $field, $criteria)
623	{
624	$field = self::fieldExtract($database, $field);
625	if (is_null($field)) {
626	return null;
627	}
628
629	// Return
630	return PHPExcel_Calculation_Statistical::VARFunc(
631	self::getFilteredColumn($database, $field, $criteria)
632	);
633	}
634
635
636	/**
637	* DVARP
638	*
639	* Calculates the variance of a population based on the entire population by using the numbers
640	* in a column of a list or database that match conditions that you specify.
641	*
642	* Excel Function:
643	* DVARP(database,field,criteria)
644	*
645	* @access public
646	* @category Database Functions
647	* @param mixed[] $database The range of cells that makes up the list or database.
648	* A database is a list of related data in which rows of related
649	* information are records, and columns of data are fields. The
650	* first row of the list contains labels for each column.
651	* @param string\|integer $field Indicates which column is used in the function. Enter the
652	* column label enclosed between double quotation marks, such as
653	* "Age" or "Yield," or a number (without quotation marks) that
654	* represents the position of the column within the list: 1 for
655	* the first column, 2 for the second column, and so on.
656	* @param mixed[] $criteria The range of cells that contains the conditions you specify.
657	* You can use any range for the criteria argument, as long as it
658	* includes at least one column label and at least one cell below
659	* the column label in which you specify a condition for the
660	* column.
661	* @return float
662	*
663	*/
664	public static function DVARP($database, $field, $criteria)
665	{
666	$field = self::fieldExtract($database, $field);
667	if (is_null($field)) {
668	return null;
669	}
670
671	// Return
672	return PHPExcel_Calculation_Statistical::VARP(
673	self::getFilteredColumn($database, $field, $criteria)
674	);
675	}
676	}
677