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 Logical
   6  {
   7      /**
   8       * TRUE.
   9       *
  10       * Returns the boolean TRUE.
  11       *
  12       * Excel Function:
  13       *        =TRUE()
  14       *
  15       * @return bool True
  16       */
  17      public static function true()
  18      {
  19          return true;
  20      }
  21  
  22      /**
  23       * FALSE.
  24       *
  25       * Returns the boolean FALSE.
  26       *
  27       * Excel Function:
  28       *        =FALSE()
  29       *
  30       * @return bool False
  31       */
  32      public static function false()
  33      {
  34          return false;
  35      }
  36  
  37      private static function countTrueValues(array $args)
  38      {
  39          $returnValue = 0;
  40  
  41          foreach ($args as $arg) {
  42              // Is it a boolean value?
  43              if (is_bool($arg)) {
  44                  $returnValue += $arg;
  45              } elseif ((is_numeric($arg)) && (!is_string($arg))) {
  46                  $returnValue += ((int) $arg != 0);
  47              } elseif (is_string($arg)) {
  48                  $arg = strtoupper($arg);
  49                  if (($arg == 'TRUE') || ($arg == Calculation::getTRUE())) {
  50                      $arg = true;
  51                  } elseif (($arg == 'FALSE') || ($arg == Calculation::getFALSE())) {
  52                      $arg = false;
  53                  } else {
  54                      return Functions::VALUE();
  55                  }
  56                  $returnValue += ($arg != 0);
  57              }
  58          }
  59  
  60          return $returnValue;
  61      }
  62  
  63      /**
  64       * LOGICAL_AND.
  65       *
  66       * Returns boolean TRUE if all its arguments are TRUE; returns FALSE if one or more argument is FALSE.
  67       *
  68       * Excel Function:
  69       *        =AND(logical1[,logical2[, ...]])
  70       *
  71       *        The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be arrays
  72       *            or references that contain logical values.
  73       *
  74       *        Boolean arguments are treated as True or False as appropriate
  75       *        Integer or floating point arguments are treated as True, except for 0 or 0.0 which are False
  76       *        If any argument value is a string, or a Null, the function returns a #VALUE! error, unless the string holds
  77       *            the value TRUE or FALSE, in which case it is evaluated as the corresponding boolean value
  78       *
  79       * @param mixed ...$args Data values
  80       *
  81       * @return bool|string the logical AND of the arguments
  82       */
  83      public static function logicalAnd(...$args)
  84      {
  85          $args = Functions::flattenArray($args);
  86  
  87          if (count($args) == 0) {
  88              return Functions::VALUE();
  89          }
  90  
  91          $args = array_filter($args, function ($value) {
  92              return $value !== null || (is_string($value) && trim($value) == '');
  93          });
  94          $argCount = count($args);
  95  
  96          $returnValue = self::countTrueValues($args);
  97          if (is_string($returnValue)) {
  98              return $returnValue;
  99          }
 100  
 101          return ($returnValue > 0) && ($returnValue == $argCount);
 102      }
 103  
 104      /**
 105       * LOGICAL_OR.
 106       *
 107       * Returns boolean TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
 108       *
 109       * Excel Function:
 110       *        =OR(logical1[,logical2[, ...]])
 111       *
 112       *        The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be arrays
 113       *            or references that contain logical values.
 114       *
 115       *        Boolean arguments are treated as True or False as appropriate
 116       *        Integer or floating point arguments are treated as True, except for 0 or 0.0 which are False
 117       *        If any argument value is a string, or a Null, the function returns a #VALUE! error, unless the string holds
 118       *            the value TRUE or FALSE, in which case it is evaluated as the corresponding boolean value
 119       *
 120       * @param mixed $args Data values
 121       *
 122       * @return bool|string the logical OR of the arguments
 123       */
 124      public static function logicalOr(...$args)
 125      {
 126          $args = Functions::flattenArray($args);
 127  
 128          if (count($args) == 0) {
 129              return Functions::VALUE();
 130          }
 131  
 132          $args = array_filter($args, function ($value) {
 133              return $value !== null || (is_string($value) && trim($value) == '');
 134          });
 135  
 136          $returnValue = self::countTrueValues($args);
 137          if (is_string($returnValue)) {
 138              return $returnValue;
 139          }
 140  
 141          return $returnValue > 0;
 142      }
 143  
 144      /**
 145       * LOGICAL_XOR.
 146       *
 147       * Returns the Exclusive Or logical operation for one or more supplied conditions.
 148       * i.e. the Xor function returns TRUE if an odd number of the supplied conditions evaluate to TRUE, and FALSE otherwise.
 149       *
 150       * Excel Function:
 151       *        =XOR(logical1[,logical2[, ...]])
 152       *
 153       *        The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be arrays
 154       *            or references that contain logical values.
 155       *
 156       *        Boolean arguments are treated as True or False as appropriate
 157       *        Integer or floating point arguments are treated as True, except for 0 or 0.0 which are False
 158       *        If any argument value is a string, or a Null, the function returns a #VALUE! error, unless the string holds
 159       *            the value TRUE or FALSE, in which case it is evaluated as the corresponding boolean value
 160       *
 161       * @param mixed $args Data values
 162       *
 163       * @return bool|string the logical XOR of the arguments
 164       */
 165      public static function logicalXor(...$args)
 166      {
 167          $args = Functions::flattenArray($args);
 168  
 169          if (count($args) == 0) {
 170              return Functions::VALUE();
 171          }
 172  
 173          $args = array_filter($args, function ($value) {
 174              return $value !== null || (is_string($value) && trim($value) == '');
 175          });
 176  
 177          $returnValue = self::countTrueValues($args);
 178          if (is_string($returnValue)) {
 179              return $returnValue;
 180          }
 181  
 182          return $returnValue % 2 == 1;
 183      }
 184  
 185      /**
 186       * NOT.
 187       *
 188       * Returns the boolean inverse of the argument.
 189       *
 190       * Excel Function:
 191       *        =NOT(logical)
 192       *
 193       *        The argument must evaluate to a logical value such as TRUE or FALSE
 194       *
 195       *        Boolean arguments are treated as True or False as appropriate
 196       *        Integer or floating point arguments are treated as True, except for 0 or 0.0 which are False
 197       *        If any argument value is a string, or a Null, the function returns a #VALUE! error, unless the string holds
 198       *            the value TRUE or FALSE, in which case it is evaluated as the corresponding boolean value
 199       *
 200       * @param mixed $logical A value or expression that can be evaluated to TRUE or FALSE
 201       *
 202       * @return bool|string the boolean inverse of the argument
 203       */
 204      public static function NOT($logical = false)
 205      {
 206          $logical = Functions::flattenSingleValue($logical);
 207  
 208          if (is_string($logical)) {
 209              $logical = strtoupper($logical);
 210              if (($logical == 'TRUE') || ($logical == Calculation::getTRUE())) {
 211                  return false;
 212              } elseif (($logical == 'FALSE') || ($logical == Calculation::getFALSE())) {
 213                  return true;
 214              }
 215  
 216              return Functions::VALUE();
 217          }
 218  
 219          return !$logical;
 220      }
 221  
 222      /**
 223       * STATEMENT_IF.
 224       *
 225       * Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.
 226       *
 227       * Excel Function:
 228       *        =IF(condition[,returnIfTrue[,returnIfFalse]])
 229       *
 230       *        Condition is any value or expression that can be evaluated to TRUE or FALSE.
 231       *            For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100,
 232       *            the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE.
 233       *            This argument can use any comparison calculation operator.
 234       *        ReturnIfTrue is the value that is returned if condition evaluates to TRUE.
 235       *            For example, if this argument is the text string "Within budget" and the condition argument evaluates to TRUE,
 236       *            then the IF function returns the text "Within budget"
 237       *            If condition is TRUE and ReturnIfTrue is blank, this argument returns 0 (zero). To display the word TRUE, use
 238       *            the logical value TRUE for this argument.
 239       *            ReturnIfTrue can be another formula.
 240       *        ReturnIfFalse is the value that is returned if condition evaluates to FALSE.
 241       *            For example, if this argument is the text string "Over budget" and the condition argument evaluates to FALSE,
 242       *            then the IF function returns the text "Over budget".
 243       *            If condition is FALSE and ReturnIfFalse is omitted, then the logical value FALSE is returned.
 244       *            If condition is FALSE and ReturnIfFalse is blank, then the value 0 (zero) is returned.
 245       *            ReturnIfFalse can be another formula.
 246       *
 247       * @param mixed $condition Condition to evaluate
 248       * @param mixed $returnIfTrue Value to return when condition is true
 249       * @param mixed $returnIfFalse Optional value to return when condition is false
 250       *
 251       * @return mixed The value of returnIfTrue or returnIfFalse determined by condition
 252       */
 253      public static function statementIf($condition = true, $returnIfTrue = 0, $returnIfFalse = false)
 254      {
 255          if (Functions::isError($condition)) {
 256              return $condition;
 257          }
 258  
 259          $condition = ($condition === null) ? true : (bool) Functions::flattenSingleValue($condition);
 260          $returnIfTrue = ($returnIfTrue === null) ? 0 : Functions::flattenSingleValue($returnIfTrue);
 261          $returnIfFalse = ($returnIfFalse === null) ? false : Functions::flattenSingleValue($returnIfFalse);
 262  
 263          return ($condition) ? $returnIfTrue : $returnIfFalse;
 264      }
 265  
 266      /**
 267       * STATEMENT_SWITCH.
 268       *
 269       * Returns corresponding with first match (any data type such as a string, numeric, date, etc).
 270       *
 271       * Excel Function:
 272       *        =SWITCH (expression, value1, result1, value2, result2, ... value_n, result_n [, default])
 273       *
 274       *        Expression
 275       *              The expression to compare to a list of values.
 276       *        value1, value2, ... value_n
 277       *              A list of values that are compared to expression. The SWITCH function is looking for the first value that matches the expression.
 278       *        result1, result2, ... result_n
 279       *              A list of results. The SWITCH function returns the corresponding result when a value matches expression.
 280       *         default
 281       *              Optional. It is the default to return if expression does not match any of the values (value1, value2, ... value_n).
 282       *
 283       * @param mixed $arguments Statement arguments
 284       *
 285       * @return mixed The value of matched expression
 286       */
 287      public static function statementSwitch(...$arguments)
 288      {
 289          $result = Functions::VALUE();
 290  
 291          if (count($arguments) > 0) {
 292              $targetValue = Functions::flattenSingleValue($arguments[0]);
 293              $argc = count($arguments) - 1;
 294              $switchCount = floor($argc / 2);
 295              $switchSatisfied = false;
 296              $hasDefaultClause = $argc % 2 !== 0;
 297              $defaultClause = $argc % 2 === 0 ? null : $arguments[count($arguments) - 1];
 298  
 299              if ($switchCount) {
 300                  for ($index = 0; $index < $switchCount; ++$index) {
 301                      if ($targetValue == $arguments[$index * 2 + 1]) {
 302                          $result = $arguments[$index * 2 + 2];
 303                          $switchSatisfied = true;
 304  
 305                          break;
 306                      }
 307                  }
 308              }
 309  
 310              if (!$switchSatisfied) {
 311                  $result = $hasDefaultClause ? $defaultClause : Functions::NA();
 312              }
 313          }
 314  
 315          return $result;
 316      }
 317  
 318      /**
 319       * IFERROR.
 320       *
 321       * Excel Function:
 322       *        =IFERROR(testValue,errorpart)
 323       *
 324       * @param mixed $testValue Value to check, is also the value returned when no error
 325       * @param mixed $errorpart Value to return when testValue is an error condition
 326       *
 327       * @return mixed The value of errorpart or testValue determined by error condition
 328       */
 329      public static function IFERROR($testValue = '', $errorpart = '')
 330      {
 331          $testValue = ($testValue === null) ? '' : Functions::flattenSingleValue($testValue);
 332          $errorpart = ($errorpart === null) ? '' : Functions::flattenSingleValue($errorpart);
 333  
 334          return self::statementIf(Functions::isError($testValue), $errorpart, $testValue);
 335      }
 336  
 337      /**
 338       * IFNA.
 339       *
 340       * Excel Function:
 341       *        =IFNA(testValue,napart)
 342       *
 343       * @param mixed $testValue Value to check, is also the value returned when not an NA
 344       * @param mixed $napart Value to return when testValue is an NA condition
 345       *
 346       * @return mixed The value of errorpart or testValue determined by error condition
 347       */
 348      public static function IFNA($testValue = '', $napart = '')
 349      {
 350          $testValue = ($testValue === null) ? '' : Functions::flattenSingleValue($testValue);
 351          $napart = ($napart === null) ? '' : Functions::flattenSingleValue($napart);
 352  
 353          return self::statementIf(Functions::isNa($testValue), $napart, $testValue);
 354      }
 355  
 356      /**
 357       * IFS.
 358       *
 359       * Excel Function:
 360       *         =IFS(testValue1;returnIfTrue1;testValue2;returnIfTrue2;...;testValue_n;returnIfTrue_n)
 361       *
 362       *         testValue1 ... testValue_n
 363       *             Conditions to Evaluate
 364       *         returnIfTrue1 ... returnIfTrue_n
 365       *             Value returned if corresponding testValue (nth) was true
 366       *
 367       * @param mixed ...$arguments Statement arguments
 368       *
 369       * @return mixed|string The value of returnIfTrue_n, if testValue_n was true. #N/A if none of testValues was true
 370       */
 371      public static function IFS(...$arguments)
 372      {
 373          if (count($arguments) % 2 != 0) {
 374              return Functions::NA();
 375          }
 376          // We use instance of Exception as a falseValue in order to prevent string collision with value in cell
 377          $falseValueException = new Exception();
 378          for ($i = 0; $i < count($arguments); $i += 2) {
 379              $testValue = ($arguments[$i] === null) ? '' : Functions::flattenSingleValue($arguments[$i]);
 380              $returnIfTrue = ($arguments[$i + 1] === null) ? '' : Functions::flattenSingleValue($arguments[$i + 1]);
 381              $result = self::statementIf($testValue, $returnIfTrue, $falseValueException);
 382  
 383              if ($result !== $falseValueException) {
 384                  return $result;
 385              }
 386          }
 387  
 388          return Functions::NA();
 389      }
 390  }