ILIAS  release_5-2 Revision v5.2.25-18-g3f80b828510
Static Public Member Functions | Static Private Member Functions
PHPExcel_Calculation_Database Class Reference
+ Collaboration diagram for PHPExcel_Calculation_Database:

Static Public Member Functions

static DAVERAGE ($database, $field, $criteria)
 
static DCOUNT ($database, $field, $criteria)
 
static DCOUNTA ($database, $field, $criteria)
 
static DGET ($database, $field, $criteria)
 
static DMAX ($database, $field, $criteria)
 
static DMIN ($database, $field, $criteria)
 
static DPRODUCT ($database, $field, $criteria)
 
static DSTDEV ($database, $field, $criteria)
 
static DSTDEVP ($database, $field, $criteria)
 
static DSUM ($database, $field, $criteria)
 
static DVAR ($database, $field, $criteria)
 
static DVARP ($database, $field, $criteria)
 

Static Private Member Functions

static __fieldExtract ($database, $field)
 __fieldExtract More...
 
static __filter ($database, $criteria)
 __filter More...
 

Detailed Description

Definition at line 46 of file Database.php.

Member Function Documentation

◆ __fieldExtract()

static PHPExcel_Calculation_Database::__fieldExtract (   $database,
  $field 
)
staticprivate

__fieldExtract

Extracts the column ID to use for the data field.

private

Parameters
mixed[]$database The range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.
mixed$fieldIndicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as "Age" or "Yield," or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
Returns
string|NULL

Definition at line 67 of file Database.php.

References PHPExcel_Calculation_Functions\flattenSingleValue().

67  {
68  $field = strtoupper(PHPExcel_Calculation_Functions::flattenSingleValue($field));
69  $fieldNames = array_map('strtoupper',array_shift($database));
70 
71  if (is_numeric($field)) {
72  $keys = array_keys($fieldNames);
73  return $keys[$field-1];
74  }
75  $key = array_search($field,$fieldNames);
76  return ($key) ? $key : NULL;
77  }
PHPExcel_Calculation_Functions\flattenSingleValue
static flattenSingleValue($value='')
Convert an array to a single scalar value by extracting the first element.
Definition: Functions.php:662
+ Here is the call graph for this function:

◆ __filter()

static PHPExcel_Calculation_Database::__filter (   $database,
  $criteria 
)
staticprivate

__filter

Parses the selection criteria, extracts the database rows that match those criteria, and returns that subset of rows.

private

Parameters
mixed[]$database The range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.
mixed[]$criteria The range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.
Returns
array of mixed

Definition at line 98 of file Database.php.

References $result, $row, PHPExcel_Calculation_Functions\_ifCondition(), PHPExcel_Calculation\_wrapResult(), array, and PHPExcel_Calculation\getInstance().

98  {
99  $fieldNames = array_shift($database);
100  $criteriaNames = array_shift($criteria);
101 
102  // Convert the criteria into a set of AND/OR conditions with [:placeholders]
103  $testConditions = $testValues = array();
104  $testConditionsCount = 0;
105  foreach($criteriaNames as $key => $criteriaName) {
106  $testCondition = array();
107  $testConditionCount = 0;
108  foreach($criteria as $row => $criterion) {
109  if ($criterion[$key] > '') {
110  $testCondition[] = '[:'.$criteriaName.']'.PHPExcel_Calculation_Functions::_ifCondition($criterion[$key]);
111  $testConditionCount++;
112  }
113  }
114  if ($testConditionCount > 1) {
115  $testConditions[] = 'OR('.implode(',',$testCondition).')';
116  $testConditionsCount++;
117  } elseif($testConditionCount == 1) {
118  $testConditions[] = $testCondition[0];
119  $testConditionsCount++;
120  }
121  }
122 
123  if ($testConditionsCount > 1) {
124  $testConditionSet = 'AND('.implode(',',$testConditions).')';
125  } elseif($testConditionsCount == 1) {
126  $testConditionSet = $testConditions[0];
127  }
128 
129  // Loop through each row of the database
130  foreach($database as $dataRow => $dataValues) {
131  // Substitute actual values from the database row for our [:placeholders]
132  $testConditionList = $testConditionSet;
133  foreach($criteriaNames as $key => $criteriaName) {
134  $k = array_search($criteriaName,$fieldNames);
135  if (isset($dataValues[$k])) {
136  $dataValue = $dataValues[$k];
137  $dataValue = (is_string($dataValue)) ? PHPExcel_Calculation::_wrapResult(strtoupper($dataValue)) : $dataValue;
138  $testConditionList = str_replace('[:'.$criteriaName.']',$dataValue,$testConditionList);
139  }
140  }
141  // evaluate the criteria against the row data
142  $result = PHPExcel_Calculation::getInstance()->_calculateFormulaValue('='.$testConditionList);
143  // If the row failed to meet the criteria, remove it from the database
144  if (!$result) {
145  unset($database[$dataRow]);
146  }
147  }
148 
149  return $database;
150  }
$result
$result
Definition: CleanUpTest.php:407
PHPExcel_Calculation\_wrapResult
static _wrapResult($value)
Wrap string values in quotes.
Definition: Calculation.php:2163
PHPExcel_Calculation_Functions\_ifCondition
static _ifCondition($condition)
Definition: Functions.php:309
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
PHPExcel_Calculation\getInstance
static getInstance(PHPExcel $workbook=NULL)
Get an instance of this class.
Definition: Calculation.php:1765
+ Here is the call graph for this function:

◆ DAVERAGE()

static PHPExcel_Calculation_Database::DAVERAGE (   $database,
  $field,
  $criteria 
)
static

Definition at line 180 of file Database.php.

References $row, array, and PHPExcel_Calculation_Statistical\AVERAGE().

180  {
181  $field = self::__fieldExtract($database,$field);
182  if (is_null($field)) {
183  return NULL;
184  }
185  // reduce the database to a set of rows that match all the criteria
186  $database = self::__filter($database,$criteria);
187  // extract an array of values for the requested column
188  $colData = array();
189  foreach($database as $row) {
190  $colData[] = $row[$field];
191  }
192 
193  // Return
194  return PHPExcel_Calculation_Statistical::AVERAGE($colData);
195  } // function DAVERAGE()
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
PHPExcel_Calculation_Statistical\AVERAGE
static AVERAGE()
Definition: Statistical.php:748
+ Here is the call graph for this function:

◆ DCOUNT()

static PHPExcel_Calculation_Database::DCOUNT (   $database,
  $field,
  $criteria 
)
static

Definition at line 232 of file Database.php.

References $row, array, and PHPExcel_Calculation_Statistical\COUNT().

232  {
233  $field = self::__fieldExtract($database,$field);
234  if (is_null($field)) {
235  return NULL;
236  }
237 
238  // reduce the database to a set of rows that match all the criteria
239  $database = self::__filter($database,$criteria);
240  // extract an array of values for the requested column
241  $colData = array();
242  foreach($database as $row) {
243  $colData[] = $row[$field];
244  }
245 
246  // Return
247  return PHPExcel_Calculation_Statistical::COUNT($colData);
248  } // function DCOUNT()
PHPExcel_Calculation_Statistical\COUNT
static COUNT()
Definition: Statistical.php:1165
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
+ Here is the call graph for this function:

◆ DCOUNTA()

static PHPExcel_Calculation_Database::DCOUNTA (   $database,
  $field,
  $criteria 
)
static

Definition at line 281 of file Database.php.

References $row, array, and PHPExcel_Calculation_Statistical\COUNTA().

281  {
282  $field = self::__fieldExtract($database,$field);
283  if (is_null($field)) {
284  return NULL;
285  }
286 
287  // reduce the database to a set of rows that match all the criteria
288  $database = self::__filter($database,$criteria);
289  // extract an array of values for the requested column
290  $colData = array();
291  foreach($database as $row) {
292  $colData[] = $row[$field];
293  }
294 
295  // Return
296  return PHPExcel_Calculation_Statistical::COUNTA($colData);
297  } // function DCOUNTA()
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
PHPExcel_Calculation_Statistical\COUNTA
static COUNTA()
Definition: Statistical.php:1200
+ Here is the call graph for this function:

◆ DGET()

static PHPExcel_Calculation_Database::DGET (   $database,
  $field,
  $criteria 
)
static

Definition at line 328 of file Database.php.

References $row, array, and PHPExcel_Calculation_Functions\NaN().

328  {
329  $field = self::__fieldExtract($database,$field);
330  if (is_null($field)) {
331  return NULL;
332  }
333 
334  // reduce the database to a set of rows that match all the criteria
335  $database = self::__filter($database,$criteria);
336  // extract an array of values for the requested column
337  $colData = array();
338  foreach($database as $row) {
339  $colData[] = $row[$field];
340  }
341 
342  // Return
343  if (count($colData) > 1) {
344  return PHPExcel_Calculation_Functions::NaN();
345  }
346 
347  return $colData[0];
348  } // function DGET()
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
PHPExcel_Calculation_Functions\NaN
static NaN()
Definition: Functions.php:233
+ Here is the call graph for this function:

◆ DMAX()

static PHPExcel_Calculation_Database::DMAX (   $database,
  $field,
  $criteria 
)
static

Definition at line 379 of file Database.php.

References $row, array, and PHPExcel_Calculation_Statistical\MAX().

379  {
380  $field = self::__fieldExtract($database,$field);
381  if (is_null($field)) {
382  return NULL;
383  }
384 
385  // reduce the database to a set of rows that match all the criteria
386  $database = self::__filter($database,$criteria);
387  // extract an array of values for the requested column
388  $colData = array();
389  foreach($database as $row) {
390  $colData[] = $row[$field];
391  }
392 
393  // Return
394  return PHPExcel_Calculation_Statistical::MAX($colData);
395  } // function DMAX()
PHPExcel_Calculation_Statistical\MAX
static MAX()
Definition: Statistical.php:2129
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
+ Here is the call graph for this function:

◆ DMIN()

static PHPExcel_Calculation_Database::DMIN (   $database,
  $field,
  $criteria 
)
static

Definition at line 426 of file Database.php.

References $row, array, and PHPExcel_Calculation_Statistical\MIN().

426  {
427  $field = self::__fieldExtract($database,$field);
428  if (is_null($field)) {
429  return NULL;
430  }
431 
432  // reduce the database to a set of rows that match all the criteria
433  $database = self::__filter($database,$criteria);
434  // extract an array of values for the requested column
435  $colData = array();
436  foreach($database as $row) {
437  $colData[] = $row[$field];
438  }
439 
440  // Return
441  return PHPExcel_Calculation_Statistical::MIN($colData);
442  } // function DMIN()
PHPExcel_Calculation_Statistical\MIN
static MIN()
Definition: Statistical.php:2291
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
+ Here is the call graph for this function:

◆ DPRODUCT()

static PHPExcel_Calculation_Database::DPRODUCT (   $database,
  $field,
  $criteria 
)
static

Definition at line 472 of file Database.php.

References $row, array, and PHPExcel_Calculation_MathTrig\PRODUCT().

472  {
473  $field = self::__fieldExtract($database,$field);
474  if (is_null($field)) {
475  return NULL;
476  }
477 
478  // reduce the database to a set of rows that match all the criteria
479  $database = self::__filter($database,$criteria);
480  // extract an array of values for the requested column
481  $colData = array();
482  foreach($database as $row) {
483  $colData[] = $row[$field];
484  }
485 
486  // Return
487  return PHPExcel_Calculation_MathTrig::PRODUCT($colData);
488  } // function DPRODUCT()
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
PHPExcel_Calculation_MathTrig\PRODUCT
static PRODUCT()
Definition: MathTrig.php:811
+ Here is the call graph for this function:

◆ DSTDEV()

static PHPExcel_Calculation_Database::DSTDEV (   $database,
  $field,
  $criteria 
)
static

Definition at line 519 of file Database.php.

References $row, array, and PHPExcel_Calculation_Statistical\STDEV().

519  {
520  $field = self::__fieldExtract($database,$field);
521  if (is_null($field)) {
522  return NULL;
523  }
524 
525  // reduce the database to a set of rows that match all the criteria
526  $database = self::__filter($database,$criteria);
527  // extract an array of values for the requested column
528  $colData = array();
529  foreach($database as $row) {
530  $colData[] = $row[$field];
531  }
532 
533  // Return
534  return PHPExcel_Calculation_Statistical::STDEV($colData);
535  } // function DSTDEV()
PHPExcel_Calculation_Statistical\STDEV
static STDEV()
Definition: Statistical.php:2997
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
+ Here is the call graph for this function:

◆ DSTDEVP()

static PHPExcel_Calculation_Database::DSTDEVP (   $database,
  $field,
  $criteria 
)
static

Definition at line 566 of file Database.php.

References $row, array, and PHPExcel_Calculation_Statistical\STDEVP().

566  {
567  $field = self::__fieldExtract($database,$field);
568  if (is_null($field)) {
569  return NULL;
570  }
571 
572  // reduce the database to a set of rows that match all the criteria
573  $database = self::__filter($database,$criteria);
574  // extract an array of values for the requested column
575  $colData = array();
576  foreach($database as $row) {
577  $colData[] = $row[$field];
578  }
579 
580  // Return
581  return PHPExcel_Calculation_Statistical::STDEVP($colData);
582  } // function DSTDEVP()
PHPExcel_Calculation_Statistical\STDEVP
static STDEVP()
Definition: Statistical.php:3096
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
+ Here is the call graph for this function:

◆ DSUM()

static PHPExcel_Calculation_Database::DSUM (   $database,
  $field,
  $criteria 
)
static

Definition at line 612 of file Database.php.

References $row, array, and PHPExcel_Calculation_MathTrig\SUM().

612  {
613  $field = self::__fieldExtract($database,$field);
614  if (is_null($field)) {
615  return NULL;
616  }
617 
618  // reduce the database to a set of rows that match all the criteria
619  $database = self::__filter($database,$criteria);
620  // extract an array of values for the requested column
621  $colData = array();
622  foreach($database as $row) {
623  $colData[] = $row[$field];
624  }
625 
626  // Return
627  return PHPExcel_Calculation_MathTrig::SUM($colData);
628  } // function DSUM()
PHPExcel_Calculation_MathTrig\SUM
static SUM()
Definition: MathTrig.php:1127
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
+ Here is the call graph for this function:

◆ DVAR()

static PHPExcel_Calculation_Database::DVAR (   $database,
  $field,
  $criteria 
)
static

Definition at line 659 of file Database.php.

References $row, array, and PHPExcel_Calculation_Statistical\VARFunc().

659  {
660  $field = self::__fieldExtract($database,$field);
661  if (is_null($field)) {
662  return NULL;
663  }
664 
665  // reduce the database to a set of rows that match all the criteria
666  $database = self::__filter($database,$criteria);
667  // extract an array of values for the requested column
668  $colData = array();
669  foreach($database as $row) {
670  $colData[] = $row[$field];
671  }
672 
673  // Return
674  return PHPExcel_Calculation_Statistical::VARFunc($colData);
675  } // function DVAR()
PHPExcel_Calculation_Statistical\VARFunc
static VARFunc()
Definition: Statistical.php:3414
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
+ Here is the call graph for this function:

◆ DVARP()

static PHPExcel_Calculation_Database::DVARP (   $database,
  $field,
  $criteria 
)
static

Definition at line 706 of file Database.php.

References $row, array, and PHPExcel_Calculation_Statistical\VARP().

706  {
707  $field = self::__fieldExtract($database,$field);
708  if (is_null($field)) {
709  return NULL;
710  }
711 
712  // reduce the database to a set of rows that match all the criteria
713  $database = self::__filter($database,$criteria);
714  // extract an array of values for the requested column
715  $colData = array();
716  foreach($database as $row) {
717  $colData[] = $row[$field];
718  }
719 
720  // Return
721  return PHPExcel_Calculation_Statistical::VARP($colData);
722  } // function DVARP()
PHPExcel_Calculation_Statistical\VARP
static VARP()
Definition: Statistical.php:3509
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
+ Here is the call graph for this function:
The documentation for this class was generated from the following file: