[Summary view]

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