Search moodle.org's
Developer Documentation
Developer Documentation
- Bug fixes for general core bugs in 3.10.x will end 8 November 2021 (12 months).
- Bug fixes for security issues in 3.10.x will end 9 May 2022 (18 months).
- PHP version: minimum PHP 7.2.0 Note: minimum PHP version has increased since Moodle 3.8. PHP 7.3.x and 7.4.x are supported too.
Differences Between: [Versions 310 and 311] [Versions 310 and 400] [Versions 310 and 401] [Versions 310 and 402] [Versions 310 and 403]
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 * @category Database Functions 134 * 135 * @param mixed[] $database The range of cells that makes up the list or database. 136 * A database is a list of related data in which rows of related 137 * information are records, and columns of data are fields. The 138 * first row of the list contains labels for each column. 139 * @param int|string $field Indicates which column is used in the function. Enter the 140 * column label enclosed between double quotation marks, such as 141 * "Age" or "Yield," or a number (without quotation marks) that 142 * represents the position of the column within the list: 1 for 143 * the first column, 2 for the second column, and so on. 144 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 145 * You can use any range for the criteria argument, as long as it 146 * includes at least one column label and at least one cell below 147 * the column label in which you specify a condition for the 148 * column. 149 * 150 * @return float|string 151 */ 152 public static function DAVERAGE($database, $field, $criteria) 153 { 154 $field = self::fieldExtract($database, $field); 155 if ($field === null) { 156 return null; 157 } 158 159 // Return 160 return Statistical::AVERAGE( 161 self::getFilteredColumn($database, $field, $criteria) 162 ); 163 } 164 165 /** 166 * DCOUNT. 167 * 168 * Counts the cells that contain numbers in a column of a list or database that match conditions 169 * that you specify. 170 * 171 * Excel Function: 172 * DCOUNT(database,[field],criteria) 173 * 174 * Excel Function: 175 * DAVERAGE(database,field,criteria) 176 * 177 * @category Database Functions 178 * 179 * @param mixed[] $database The range of cells that makes up the list or database. 180 * A database is a list of related data in which rows of related 181 * information are records, and columns of data are fields. The 182 * first row of the list contains labels for each column. 183 * @param int|string $field Indicates which column is used in the function. Enter the 184 * column label enclosed between double quotation marks, such as 185 * "Age" or "Yield," or a number (without quotation marks) that 186 * represents the position of the column within the list: 1 for 187 * the first column, 2 for the second column, and so on. 188 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 189 * You can use any range for the criteria argument, as long as it 190 * includes at least one column label and at least one cell below 191 * the column label in which you specify a condition for the 192 * column. 193 * 194 * @return int 195 * 196 * @TODO The field argument is optional. If field is omitted, DCOUNT counts all records in the 197 * database that match the criteria. 198 */ 199 public static function DCOUNT($database, $field, $criteria) 200 { 201 $field = self::fieldExtract($database, $field); 202 if ($field === null) { 203 return null; 204 } 205 206 // Return 207 return Statistical::COUNT( 208 self::getFilteredColumn($database, $field, $criteria) 209 ); 210 } 211 212 /** 213 * DCOUNTA. 214 * 215 * Counts the nonblank cells in a column of a list or database that match conditions that you specify. 216 * 217 * Excel Function: 218 * DCOUNTA(database,[field],criteria) 219 * 220 * @category Database Functions 221 * 222 * @param mixed[] $database The range of cells that makes up the list or database. 223 * A database is a list of related data in which rows of related 224 * information are records, and columns of data are fields. The 225 * first row of the list contains labels for each column. 226 * @param int|string $field Indicates which column is used in the function. Enter the 227 * column label enclosed between double quotation marks, such as 228 * "Age" or "Yield," or a number (without quotation marks) that 229 * represents the position of the column within the list: 1 for 230 * the first column, 2 for the second column, and so on. 231 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 232 * You can use any range for the criteria argument, as long as it 233 * includes at least one column label and at least one cell below 234 * the column label in which you specify a condition for the 235 * column. 236 * 237 * @return int 238 * 239 * @TODO The field argument is optional. If field is omitted, DCOUNTA counts all records in the 240 * database that match the criteria. 241 */ 242 public static function DCOUNTA($database, $field, $criteria) 243 { 244 $field = self::fieldExtract($database, $field); 245 if ($field === null) { 246 return null; 247 } 248 249 // reduce the database to a set of rows that match all the criteria 250 $database = self::filter($database, $criteria); 251 // extract an array of values for the requested column 252 $colData = []; 253 foreach ($database as $row) { 254 $colData[] = $row[$field]; 255 } 256 257 // Return 258 return Statistical::COUNTA( 259 self::getFilteredColumn($database, $field, $criteria) 260 ); 261 } 262 263 /** 264 * DGET. 265 * 266 * Extracts a single value from a column of a list or database that matches conditions that you 267 * specify. 268 * 269 * Excel Function: 270 * DGET(database,field,criteria) 271 * 272 * @category Database Functions 273 * 274 * @param mixed[] $database The range of cells that makes up the list or database. 275 * A database is a list of related data in which rows of related 276 * information are records, and columns of data are fields. The 277 * first row of the list contains labels for each column. 278 * @param int|string $field Indicates which column is used in the function. Enter the 279 * column label enclosed between double quotation marks, such as 280 * "Age" or "Yield," or a number (without quotation marks) that 281 * represents the position of the column within the list: 1 for 282 * the first column, 2 for the second column, and so on. 283 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 284 * You can use any range for the criteria argument, as long as it 285 * includes at least one column label and at least one cell below 286 * the column label in which you specify a condition for the 287 * column. 288 * 289 * @return mixed 290 */ 291 public static function DGET($database, $field, $criteria) 292 { 293 $field = self::fieldExtract($database, $field); 294 if ($field === null) { 295 return null; 296 } 297 298 // Return 299 $colData = self::getFilteredColumn($database, $field, $criteria); 300 if (count($colData) > 1) { 301 return Functions::NAN(); 302 } 303 304 return $colData[0]; 305 } 306 307 /** 308 * DMAX. 309 * 310 * Returns the largest number in a column of a list or database that matches conditions you that 311 * specify. 312 * 313 * Excel Function: 314 * DMAX(database,field,criteria) 315 * 316 * @category Database Functions 317 * 318 * @param mixed[] $database The range of cells that makes up the list or database. 319 * A database is a list of related data in which rows of related 320 * information are records, and columns of data are fields. The 321 * first row of the list contains labels for each column. 322 * @param int|string $field Indicates which column is used in the function. Enter the 323 * column label enclosed between double quotation marks, such as 324 * "Age" or "Yield," or a number (without quotation marks) that 325 * represents the position of the column within the list: 1 for 326 * the first column, 2 for the second column, and so on. 327 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 328 * You can use any range for the criteria argument, as long as it 329 * includes at least one column label and at least one cell below 330 * the column label in which you specify a condition for the 331 * column. 332 * 333 * @return float 334 */ 335 public static function DMAX($database, $field, $criteria) 336 { 337 $field = self::fieldExtract($database, $field); 338 if ($field === null) { 339 return null; 340 } 341 342 // Return 343 return Statistical::MAX( 344 self::getFilteredColumn($database, $field, $criteria) 345 ); 346 } 347 348 /** 349 * DMIN. 350 * 351 * Returns the smallest number in a column of a list or database that matches conditions you that 352 * specify. 353 * 354 * Excel Function: 355 * DMIN(database,field,criteria) 356 * 357 * @category Database Functions 358 * 359 * @param mixed[] $database The range of cells that makes up the list or database. 360 * A database is a list of related data in which rows of related 361 * information are records, and columns of data are fields. The 362 * first row of the list contains labels for each column. 363 * @param int|string $field Indicates which column is used in the function. Enter the 364 * column label enclosed between double quotation marks, such as 365 * "Age" or "Yield," or a number (without quotation marks) that 366 * represents the position of the column within the list: 1 for 367 * the first column, 2 for the second column, and so on. 368 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 369 * You can use any range for the criteria argument, as long as it 370 * includes at least one column label and at least one cell below 371 * the column label in which you specify a condition for the 372 * column. 373 * 374 * @return float 375 */ 376 public static function DMIN($database, $field, $criteria) 377 { 378 $field = self::fieldExtract($database, $field); 379 if ($field === null) { 380 return null; 381 } 382 383 // Return 384 return Statistical::MIN( 385 self::getFilteredColumn($database, $field, $criteria) 386 ); 387 } 388 389 /** 390 * DPRODUCT. 391 * 392 * Multiplies the values in a column of a list or database that match conditions that you specify. 393 * 394 * Excel Function: 395 * DPRODUCT(database,field,criteria) 396 * 397 * @category Database Functions 398 * 399 * @param mixed[] $database The range of cells that makes up the list or database. 400 * A database is a list of related data in which rows of related 401 * information are records, and columns of data are fields. The 402 * first row of the list contains labels for each column. 403 * @param int|string $field Indicates which column is used in the function. Enter the 404 * column label enclosed between double quotation marks, such as 405 * "Age" or "Yield," or a number (without quotation marks) that 406 * represents the position of the column within the list: 1 for 407 * the first column, 2 for the second column, and so on. 408 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 409 * You can use any range for the criteria argument, as long as it 410 * includes at least one column label and at least one cell below 411 * the column label in which you specify a condition for the 412 * column. 413 * 414 * @return float 415 */ 416 public static function DPRODUCT($database, $field, $criteria) 417 { 418 $field = self::fieldExtract($database, $field); 419 if ($field === null) { 420 return null; 421 } 422 423 // Return 424 return MathTrig::PRODUCT( 425 self::getFilteredColumn($database, $field, $criteria) 426 ); 427 } 428 429 /** 430 * DSTDEV. 431 * 432 * Estimates the standard deviation of a population based on a sample by using the numbers in a 433 * column of a list or database that match conditions that you specify. 434 * 435 * Excel Function: 436 * DSTDEV(database,field,criteria) 437 * 438 * @category Database Functions 439 * 440 * @param mixed[] $database The range of cells that makes up the list or database. 441 * A database is a list of related data in which rows of related 442 * information are records, and columns of data are fields. The 443 * first row of the list contains labels for each column. 444 * @param int|string $field Indicates which column is used in the function. Enter the 445 * column label enclosed between double quotation marks, such as 446 * "Age" or "Yield," or a number (without quotation marks) that 447 * represents the position of the column within the list: 1 for 448 * the first column, 2 for the second column, and so on. 449 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 450 * You can use any range for the criteria argument, as long as it 451 * includes at least one column label and at least one cell below 452 * the column label in which you specify a condition for the 453 * column. 454 * 455 * @return float|string 456 */ 457 public static function DSTDEV($database, $field, $criteria) 458 { 459 $field = self::fieldExtract($database, $field); 460 if ($field === null) { 461 return null; 462 } 463 464 // Return 465 return Statistical::STDEV( 466 self::getFilteredColumn($database, $field, $criteria) 467 ); 468 } 469 470 /** 471 * DSTDEVP. 472 * 473 * Calculates the standard deviation of a population based on the entire population by using the 474 * numbers in a column of a list or database that match conditions that you specify. 475 * 476 * Excel Function: 477 * DSTDEVP(database,field,criteria) 478 * 479 * @category Database Functions 480 * 481 * @param mixed[] $database The range of cells that makes up the list or database. 482 * A database is a list of related data in which rows of related 483 * information are records, and columns of data are fields. The 484 * first row of the list contains labels for each column. 485 * @param int|string $field Indicates which column is used in the function. Enter the 486 * column label enclosed between double quotation marks, such as 487 * "Age" or "Yield," or a number (without quotation marks) that 488 * represents the position of the column within the list: 1 for 489 * the first column, 2 for the second column, and so on. 490 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 491 * You can use any range for the criteria argument, as long as it 492 * includes at least one column label and at least one cell below 493 * the column label in which you specify a condition for the 494 * column. 495 * 496 * @return float|string 497 */ 498 public static function DSTDEVP($database, $field, $criteria) 499 { 500 $field = self::fieldExtract($database, $field); 501 if ($field === null) { 502 return null; 503 } 504 505 // Return 506 return Statistical::STDEVP( 507 self::getFilteredColumn($database, $field, $criteria) 508 ); 509 } 510 511 /** 512 * DSUM. 513 * 514 * Adds the numbers in a column of a list or database that match conditions that you specify. 515 * 516 * Excel Function: 517 * DSUM(database,field,criteria) 518 * 519 * @category Database Functions 520 * 521 * @param mixed[] $database The range of cells that makes up the list or database. 522 * A database is a list of related data in which rows of related 523 * information are records, and columns of data are fields. The 524 * first row of the list contains labels for each column. 525 * @param int|string $field Indicates which column is used in the function. Enter the 526 * column label enclosed between double quotation marks, such as 527 * "Age" or "Yield," or a number (without quotation marks) that 528 * represents the position of the column within the list: 1 for 529 * the first column, 2 for the second column, and so on. 530 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 531 * You can use any range for the criteria argument, as long as it 532 * includes at least one column label and at least one cell below 533 * the column label in which you specify a condition for the 534 * column. 535 * 536 * @return float 537 */ 538 public static function DSUM($database, $field, $criteria) 539 { 540 $field = self::fieldExtract($database, $field); 541 if ($field === null) { 542 return null; 543 } 544 545 // Return 546 return MathTrig::SUM( 547 self::getFilteredColumn($database, $field, $criteria) 548 ); 549 } 550 551 /** 552 * DVAR. 553 * 554 * Estimates the variance of a population based on a sample by using the numbers in a column 555 * of a list or database that match conditions that you specify. 556 * 557 * Excel Function: 558 * DVAR(database,field,criteria) 559 * 560 * @category Database Functions 561 * 562 * @param mixed[] $database The range of cells that makes up the list or database. 563 * A database is a list of related data in which rows of related 564 * information are records, and columns of data are fields. The 565 * first row of the list contains labels for each column. 566 * @param int|string $field Indicates which column is used in the function. Enter the 567 * column label enclosed between double quotation marks, such as 568 * "Age" or "Yield," or a number (without quotation marks) that 569 * represents the position of the column within the list: 1 for 570 * the first column, 2 for the second column, and so on. 571 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 572 * You can use any range for the criteria argument, as long as it 573 * includes at least one column label and at least one cell below 574 * the column label in which you specify a condition for the 575 * column. 576 * 577 * @return float 578 */ 579 public static function DVAR($database, $field, $criteria) 580 { 581 $field = self::fieldExtract($database, $field); 582 if ($field === null) { 583 return null; 584 } 585 586 // Return 587 return Statistical::VARFunc( 588 self::getFilteredColumn($database, $field, $criteria) 589 ); 590 } 591 592 /** 593 * DVARP. 594 * 595 * Calculates the variance of a population based on the entire population by using the numbers 596 * in a column of a list or database that match conditions that you specify. 597 * 598 * Excel Function: 599 * DVARP(database,field,criteria) 600 * 601 * @category Database Functions 602 * 603 * @param mixed[] $database The range of cells that makes up the list or database. 604 * A database is a list of related data in which rows of related 605 * information are records, and columns of data are fields. The 606 * first row of the list contains labels for each column. 607 * @param int|string $field Indicates which column is used in the function. Enter the 608 * column label enclosed between double quotation marks, such as 609 * "Age" or "Yield," or a number (without quotation marks) that 610 * represents the position of the column within the list: 1 for 611 * the first column, 2 for the second column, and so on. 612 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 613 * You can use any range for the criteria argument, as long as it 614 * includes at least one column label and at least one cell below 615 * the column label in which you specify a condition for the 616 * column. 617 * 618 * @return float 619 */ 620 public static function DVARP($database, $field, $criteria) 621 { 622 $field = self::fieldExtract($database, $field); 623 if ($field === null) { 624 return null; 625 } 626 627 // Return 628 return Statistical::VARP( 629 self::getFilteredColumn($database, $field, $criteria) 630 ); 631 } 632 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
