ILIAS  release_5-4 Revision v5.4.26-12-gabc799a52e6
ExcelMatch.php
Go to the documentation of this file.
1<?php
2
4
9
11{
12 public const MATCHTYPE_SMALLEST_VALUE = -1;
13 public const MATCHTYPE_FIRST_VALUE = 0;
14 public const MATCHTYPE_LARGEST_VALUE = 1;
15
31 public static function MATCH($lookupValue, $lookupArray, $matchType = self::MATCHTYPE_LARGEST_VALUE)
32 {
33 $lookupArray = Functions::flattenArray($lookupArray);
34 $lookupValue = Functions::flattenSingleValue($lookupValue);
35 $matchType = ($matchType === null)
36 ? self::MATCHTYPE_LARGEST_VALUE
37 : (int) Functions::flattenSingleValue($matchType);
38
39 try {
40 // Input validation
41 self::validateLookupValue($lookupValue);
42 self::validateMatchType($matchType);
43 self::validateLookupArray($lookupArray);
44
45 $keySet = array_keys($lookupArray);
46 if ($matchType == self::MATCHTYPE_LARGEST_VALUE) {
47 // If match_type is 1 the list has to be processed from last to first
48 $lookupArray = array_reverse($lookupArray);
49 $keySet = array_reverse($keySet);
50 }
51
52 $lookupArray = self::prepareLookupArray($lookupArray, $matchType);
53 } catch (Exception $e) {
54 return $e->getMessage();
55 }
56
57 // MATCH() is not case sensitive, so we convert lookup value to be lower cased if it's a string type.
58 if (is_string($lookupValue)) {
59 $lookupValue = StringHelper::strToLower($lookupValue);
60 }
61
62 $valueKey = null;
63 switch ($matchType) {
65 $valueKey = self::matchLargestValue($lookupArray, $lookupValue, $keySet);
66
67 break;
69 $valueKey = self::matchFirstValue($lookupArray, $lookupValue);
70
71 break;
73 default:
74 $valueKey = self::matchSmallestValue($lookupArray, $lookupValue);
75 }
76
77 if ($valueKey !== null) {
78 return ++$valueKey;
79 }
80
81 // Unsuccessful in finding a match, return #N/A error value
82 return Functions::NA();
83 }
84
85 private static function matchFirstValue($lookupArray, $lookupValue)
86 {
87 $wildcardLookup = ((bool) preg_match('/([\?\*])/', $lookupValue));
88 $wildcard = WildcardMatch::wildcard($lookupValue);
89
90 foreach ($lookupArray as $i => $lookupArrayValue) {
91 $typeMatch = ((gettype($lookupValue) === gettype($lookupArrayValue)) ||
92 (is_numeric($lookupValue) && is_numeric($lookupArrayValue)));
93
94 if (
95 $typeMatch && is_string($lookupValue) &&
96 $wildcardLookup && WildcardMatch::compare($lookupArrayValue, $wildcard)
97 ) {
98 // wildcard match
99 return $i;
100 } elseif ($lookupArrayValue === $lookupValue) {
101 // exact match
102 return $i;
103 }
104 }
105
106 return null;
107 }
108
109 private static function matchLargestValue($lookupArray, $lookupValue, $keySet)
110 {
111 foreach ($lookupArray as $i => $lookupArrayValue) {
112 $typeMatch = ((gettype($lookupValue) === gettype($lookupArrayValue)) ||
113 (is_numeric($lookupValue) && is_numeric($lookupArrayValue)));
114
115 if ($typeMatch && ($lookupArrayValue <= $lookupValue)) {
116 return array_search($i, $keySet);
117 }
118 }
119
120 return null;
121 }
122
123 private static function matchSmallestValue($lookupArray, $lookupValue)
124 {
125 $valueKey = null;
126
127 // The basic algorithm is:
128 // Iterate and keep the highest match until the next element is smaller than the searched value.
129 // Return immediately if perfect match is found
130 foreach ($lookupArray as $i => $lookupArrayValue) {
131 $typeMatch = gettype($lookupValue) === gettype($lookupArrayValue);
132
133 if ($lookupArrayValue === $lookupValue) {
134 // Another "special" case. If a perfect match is found,
135 // the algorithm gives up immediately
136 return $i;
137 } elseif ($typeMatch && $lookupArrayValue >= $lookupValue) {
138 $valueKey = $i;
139 } elseif ($typeMatch && $lookupArrayValue < $lookupValue) {
140 //Excel algorithm gives up immediately if the first element is smaller than the searched value
141 break;
142 }
143 }
144
145 return $valueKey;
146 }
147
148 private static function validateLookupValue($lookupValue): void
149 {
150 // Lookup_value type has to be number, text, or logical values
151 if ((!is_numeric($lookupValue)) && (!is_string($lookupValue)) && (!is_bool($lookupValue))) {
152 throw new Exception(Functions::NA());
153 }
154 }
155
156 private static function validateMatchType($matchType): void
157 {
158 // Match_type is 0, 1 or -1
159 if (
160 ($matchType !== self::MATCHTYPE_FIRST_VALUE) &&
161 ($matchType !== self::MATCHTYPE_LARGEST_VALUE) && ($matchType !== self::MATCHTYPE_SMALLEST_VALUE)
162 ) {
163 throw new Exception(Functions::NA());
164 }
165 }
166
167 private static function validateLookupArray($lookupArray): void
168 {
169 // Lookup_array should not be empty
170 $lookupArraySize = count($lookupArray);
171 if ($lookupArraySize <= 0) {
172 throw new Exception(Functions::NA());
173 }
174 }
175
176 private static function prepareLookupArray($lookupArray, $matchType)
177 {
178 // Lookup_array should contain only number, text, or logical values, or empty (null) cells
179 foreach ($lookupArray as $i => $value) {
180 // check the type of the value
181 if ((!is_numeric($value)) && (!is_string($value)) && (!is_bool($value)) && ($value !== null)) {
182 throw new Exception(Functions::NA());
183 }
184 // Convert strings to lowercase for case-insensitive testing
185 if (is_string($value)) {
186 $lookupArray[$i] = StringHelper::strToLower($value);
187 }
188 if (
189 ($value === null) &&
190 (($matchType == self::MATCHTYPE_LARGEST_VALUE) || ($matchType == self::MATCHTYPE_SMALLEST_VALUE))
191 ) {
192 unset($lookupArray[$i]);
193 }
194 }
195
196 return $lookupArray;
197 }
198}
An exception for terminatinating execution or to throw for unit testing.
static flattenArray($array)
Convert a multi-dimensional array to a simple 1-dimensional array.
Definition: Functions.php:583
static flattenSingleValue($value='')
Convert an array to a single scalar value by extracting the first element.
Definition: Functions.php:649
static prepareLookupArray($lookupArray, $matchType)
Definition: ExcelMatch.php:176
static matchSmallestValue($lookupArray, $lookupValue)
Definition: ExcelMatch.php:123
static MATCH($lookupValue, $lookupArray, $matchType=self::MATCHTYPE_LARGEST_VALUE)
MATCH.
Definition: ExcelMatch.php:31
static matchLargestValue($lookupArray, $lookupValue, $keySet)
Definition: ExcelMatch.php:109
static matchFirstValue($lookupArray, $lookupValue)
Definition: ExcelMatch.php:85
static strToLower($pValue)
Convert a UTF-8 encoded string to lower case.
$i
Definition: disco.tpl.php:19