[Summary view]

Differences Between: [Versions 400 and 403] [Versions 401 and 403]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Calculation\Database;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Functions;
   7  use PhpOffice\PhpSpreadsheet\Calculation\Internal\WildcardMatch;
   8  
   9  abstract class DatabaseAbstract
  10  {
  11      /**
  12       * @param array $database
  13       * @param int|string $field
  14       * @param array $criteria
  15       *
  16       * @return null|float|int|string
  17       */
  18      abstract public static function evaluate($database, $field, $criteria);
  19  
  20      /**
  21       * fieldExtract.
  22       *
  23       * Extracts the column ID to use for the data field.
  24       *
  25       * @param mixed[] $database The range of cells that makes up the list or database.
  26       *                                        A database is a list of related data in which rows of related
  27       *                                        information are records, and columns of data are fields. The
  28       *                                        first row of the list contains labels for each column.
  29       * @param mixed $field Indicates which column is used in the function. Enter the
  30       *                                        column label enclosed between double quotation marks, such as
  31       *                                        "Age" or "Yield," or a number (without quotation marks) that
  32       *                                        represents the position of the column within the list: 1 for
  33       *                                        the first column, 2 for the second column, and so on.
  34       */
  35      protected static function fieldExtract(array $database, $field): ?int
  36      {
  37          $field = strtoupper(Functions::flattenSingleValue($field) ?? '');
  38          if ($field === '') {
  39              return null;
  40          }
  41  
  42          $fieldNames = array_map('strtoupper', array_shift($database));
  43          if (is_numeric($field)) {
  44              $field = (int) $field - 1;
  45              if ($field < 0 || $field >= count($fieldNames)) {
  46                  return null;
  47              }
  48  
  49              return $field;
  50          }
  51          $key = array_search($field, array_values($fieldNames), true);
  52  
  53          return ($key !== false) ? (int) $key : null;
  54      }
  55  
  56      /**
  57       * filter.
  58       *
  59       * Parses the selection criteria, extracts the database rows that match those criteria, and
  60       * returns that subset of rows.
  61       *
  62       * @param mixed[] $database The range of cells that makes up the list or database.
  63       *                                        A database is a list of related data in which rows of related
  64       *                                        information are records, and columns of data are fields. The
  65       *                                        first row of the list contains labels for each column.
  66       * @param mixed[] $criteria The range of cells that contains the conditions you specify.
  67       *                                        You can use any range for the criteria argument, as long as it
  68       *                                        includes at least one column label and at least one cell below
  69       *                                        the column label in which you specify a condition for the
  70       *                                        column.
  71       *
  72       * @return mixed[]
  73       */
  74      protected static function filter(array $database, array $criteria): array
  75      {
  76          $fieldNames = array_shift($database);
  77          $criteriaNames = array_shift($criteria);
  78  
  79          //    Convert the criteria into a set of AND/OR conditions with [:placeholders]
  80          $query = self::buildQuery($criteriaNames, $criteria);
  81  
  82          //    Loop through each row of the database
  83          return self::executeQuery($database, $query, $criteriaNames, $fieldNames);
  84      }
  85  
  86      protected static function getFilteredColumn(array $database, ?int $field, array $criteria): array
  87      {
  88          //    reduce the database to a set of rows that match all the criteria
  89          $database = self::filter($database, $criteria);
  90          $defaultReturnColumnValue = ($field === null) ? 1 : null;
  91  
  92          //    extract an array of values for the requested column
  93          $columnData = [];
  94          foreach ($database as $rowKey => $row) {
  95              $keys = array_keys($row);
  96              $key = $keys[$field] ?? null;
  97              $columnKey = $key ?? 'A';
  98              $columnData[$rowKey][$columnKey] = $row[$key] ?? $defaultReturnColumnValue;
  99          }
 100  
 101          return $columnData;
 102      }
 103  
 104      private static function buildQuery(array $criteriaNames, array $criteria): string
 105      {
 106          $baseQuery = [];
 107          foreach ($criteria as $key => $criterion) {
 108              foreach ($criterion as $field => $value) {
 109                  $criterionName = $criteriaNames[$field];
 110                  if ($value !== null) {
 111                      $condition = self::buildCondition($value, $criterionName);
 112                      $baseQuery[$key][] = $condition;
 113                  }
 114              }
 115          }
 116  
 117          $rowQuery = array_map(
 118              function ($rowValue) {
 119                  return (count($rowValue) > 1) ? 'AND(' . implode(',', $rowValue) . ')' : ($rowValue[0] ?? '');
 120              },
 121              $baseQuery
 122          );
 123  
 124          return (count($rowQuery) > 1) ? 'OR(' . implode(',', $rowQuery) . ')' : ($rowQuery[0] ?? '');
 125      }
 126  
 127      /**
 128       * @param mixed $criterion
 129       */
 130      private static function buildCondition($criterion, string $criterionName): string
 131      {
 132          $ifCondition = Functions::ifCondition($criterion);
 133  
 134          // Check for wildcard characters used in the condition
 135          $result = preg_match('/(?<operator>[^"]*)(?<operand>".*[*?].*")/ui', $ifCondition, $matches);
 136          if ($result !== 1) {
 137              return "[:{$criterionName}]{$ifCondition}";
 138          }
 139  
 140          $trueFalse = ($matches['operator'] !== '<>');
 141          $wildcard = WildcardMatch::wildcard($matches['operand']);
 142          $condition = "WILDCARDMATCH([:{$criterionName}],{$wildcard})";
 143          if ($trueFalse === false) {
 144              $condition = "NOT({$condition})";
 145          }
 146  
 147          return $condition;
 148      }
 149  
 150      private static function executeQuery(array $database, string $query, array $criteria, array $fields): array
 151      {
 152          foreach ($database as $dataRow => $dataValues) {
 153              //    Substitute actual values from the database row for our [:placeholders]
 154              $conditions = $query;
 155              foreach ($criteria as $criterion) {
 156                  $conditions = self::processCondition($criterion, $fields, $dataValues, $conditions);
 157              }
 158  
 159              //    evaluate the criteria against the row data
 160              $result = Calculation::getInstance()->_calculateFormulaValue('=' . $conditions);
 161  
 162              //    If the row failed to meet the criteria, remove it from the database
 163              if ($result !== true) {
 164                  unset($database[$dataRow]);
 165              }
 166          }
 167  
 168          return $database;
 169      }
 170  
 171      /**
 172       * @return mixed
 173       */
 174      private static function processCondition(string $criterion, array $fields, array $dataValues, string $conditions)
 175      {
 176          $key = array_search($criterion, $fields, true);
 177  
 178          $dataValue = 'NULL';
 179          if (is_bool($dataValues[$key])) {
 180              $dataValue = ($dataValues[$key]) ? 'TRUE' : 'FALSE';
 181          } elseif ($dataValues[$key] !== null) {
 182              $dataValue = $dataValues[$key];
 183              // escape quotes if we have a string containing quotes
 184              if (is_string($dataValue) && strpos($dataValue, '"') !== false) {
 185                  $dataValue = str_replace('"', '""', $dataValue);
 186              }
 187              $dataValue = (is_string($dataValue)) ? Calculation::wrapResult(strtoupper($dataValue)) : $dataValue;
 188          }
 189  
 190          return str_replace('[:' . $criterion . ']', $dataValue, $conditions);
 191      }
 192  }