Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 3.11.x will end 14 Nov 2022 (12 months plus 6 months extension).
  • Bug fixes for security issues in 3.11.x will end 13 Nov 2023 (18 months plus 12 months extension).
  • PHP version: minimum PHP 7.3.0 Note: minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is supported too.

Differences Between: [Versions 310 and 311] [Versions 311 and 400] [Versions 311 and 401] [Versions 311 and 402] [Versions 311 and 403] [Versions 39 and 311]

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