[Summary view]

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