Commit | Line | Data |
---|---|---|
6a488035 | 1 | <?php |
6a488035 TO |
2 | /* |
3 | +--------------------------------------------------------------------+ | |
bc77d7c0 | 4 | | Copyright CiviCRM LLC. All rights reserved. | |
6a488035 | 5 | | | |
bc77d7c0 TO |
6 | | This work is published under the GNU AGPLv3 license with some | |
7 | | permitted exceptions and without any warranty. For full license | | |
8 | | and copyright information, see https://civicrm.org/licensing | | |
6a488035 | 9 | +--------------------------------------------------------------------+ |
d25dd0ee | 10 | */ |
6a488035 TO |
11 | |
12 | /** | |
13 | * | |
14 | * | |
15 | * @package CRM | |
ca5cec67 | 16 | * @copyright CiviCRM LLC https://civicrm.org/licensing |
6a488035 TO |
17 | */ |
18 | class CRM_Core_BAO_CustomQuery { | |
7da04cde | 19 | const PREFIX = 'custom_value_'; |
6a488035 TO |
20 | |
21 | /** | |
d2e5d2ce | 22 | * The set of custom field ids. |
6a488035 TO |
23 | * |
24 | * @var array | |
25 | */ | |
26 | protected $_ids; | |
27 | ||
28 | /** | |
d2e5d2ce | 29 | * The select clause. |
6a488035 TO |
30 | * |
31 | * @var array | |
32 | */ | |
33 | public $_select; | |
34 | ||
35 | /** | |
d2e5d2ce | 36 | * The name of the elements that are in the select clause. |
6a488035 TO |
37 | * used to extract the values |
38 | * | |
39 | * @var array | |
40 | */ | |
41 | public $_element; | |
42 | ||
43 | /** | |
d2e5d2ce | 44 | * The tables involved in the query. |
6a488035 TO |
45 | * |
46 | * @var array | |
47 | */ | |
48 | public $_tables; | |
49 | public $_whereTables; | |
50 | ||
51 | /** | |
d2e5d2ce | 52 | * The where clause. |
6a488035 TO |
53 | * |
54 | * @var array | |
55 | */ | |
56 | public $_where; | |
57 | ||
58 | /** | |
d2e5d2ce | 59 | * The english language version of the query. |
6a488035 TO |
60 | * |
61 | * @var array | |
62 | */ | |
63 | public $_qill; | |
64 | ||
6a488035 | 65 | /** |
d2e5d2ce | 66 | * The custom fields information. |
6a488035 TO |
67 | * |
68 | * @var array | |
69 | */ | |
70 | public $_fields; | |
71 | ||
acd6c6ab | 72 | /** |
73 | * @return array | |
74 | */ | |
75 | public function getFields() { | |
76 | return $this->_fields; | |
77 | } | |
78 | ||
6a488035 TO |
79 | /** |
80 | * Searching for contacts? | |
81 | * | |
d51c6add | 82 | * @var bool |
6a488035 TO |
83 | */ |
84 | protected $_contactSearch; | |
85 | ||
247ad911 | 86 | protected $_locationSpecificCustomFields; |
442df34b | 87 | |
6a488035 | 88 | /** |
d2e5d2ce | 89 | * This stores custom data group types and tables that it extends. |
6a488035 TO |
90 | * |
91 | * @var array | |
6a488035 | 92 | */ |
518fa0ee | 93 | public static $extendsMap = [ |
6a488035 TO |
94 | 'Contact' => 'civicrm_contact', |
95 | 'Individual' => 'civicrm_contact', | |
96 | 'Household' => 'civicrm_contact', | |
97 | 'Organization' => 'civicrm_contact', | |
98 | 'Contribution' => 'civicrm_contribution', | |
95974e8e | 99 | 'ContributionRecur' => 'civicrm_contribution_recur', |
6a488035 TO |
100 | 'Membership' => 'civicrm_membership', |
101 | 'Participant' => 'civicrm_participant', | |
102 | 'Group' => 'civicrm_group', | |
103 | 'Relationship' => 'civicrm_relationship', | |
104 | 'Event' => 'civicrm_event', | |
105 | 'Case' => 'civicrm_case', | |
106 | 'Activity' => 'civicrm_activity', | |
107 | 'Pledge' => 'civicrm_pledge', | |
108 | 'Grant' => 'civicrm_grant', | |
109 | 'Address' => 'civicrm_address', | |
5b37ca7b EM |
110 | 'Campaign' => 'civicrm_campaign', |
111 | 'Survey' => 'civicrm_survey', | |
be2fb01f | 112 | ]; |
6a488035 TO |
113 | |
114 | /** | |
d2e5d2ce | 115 | * Class constructor. |
6a488035 TO |
116 | * |
117 | * Takes in a set of custom field ids andsets up the data structures to | |
118 | * generate a query | |
119 | * | |
6a0b768e TO |
120 | * @param array $ids |
121 | * The set of custom field ids. | |
fd31fa4c EM |
122 | * |
123 | * @param bool $contactSearch | |
124 | * @param array $locationSpecificFields | |
442df34b | 125 | */ |
be2fb01f | 126 | public function __construct($ids, $contactSearch = FALSE, $locationSpecificFields = []) { |
688e7a0d | 127 | $this->_ids = $ids; |
247ad911 | 128 | $this->_locationSpecificCustomFields = $locationSpecificFields; |
6a488035 | 129 | |
be2fb01f CW |
130 | $this->_select = []; |
131 | $this->_element = []; | |
132 | $this->_tables = []; | |
133 | $this->_whereTables = []; | |
134 | $this->_where = []; | |
135 | $this->_qill = []; | |
6a488035 | 136 | |
6a488035 | 137 | $this->_contactSearch = $contactSearch; |
1f61a7b1 | 138 | $this->_fields = CRM_Core_BAO_CustomField::getFields('ANY', FALSE, FALSE, NULL, NULL, FALSE, FALSE, FALSE); |
6a488035 TO |
139 | } |
140 | ||
141 | /** | |
d2e5d2ce | 142 | * Generate the select clause and the associated tables. |
6a488035 | 143 | */ |
00be9182 | 144 | public function select() { |
6a488035 TO |
145 | if (empty($this->_fields)) { |
146 | return; | |
147 | } | |
148 | ||
697aad03 | 149 | foreach (array_keys($this->_ids) as $id) { |
494d81e9 SL |
150 | // Ignore any custom field ids within the ids array that are not present in the fields array. |
151 | if (empty($this->_fields[$id])) { | |
152 | continue; | |
153 | } | |
697aad03 | 154 | $field = $this->_fields[$id]; |
f2d54c7b | 155 | |
156 | if ($this->_contactSearch && $field['search_table'] === 'contact_a') { | |
157 | CRM_Contact_BAO_Query::$_openedPanes[ts('Custom Fields')] = TRUE; | |
158 | } | |
159 | ||
6a488035 TO |
160 | $name = $field['table_name']; |
161 | $fieldName = 'custom_' . $field['id']; | |
162 | $this->_select["{$name}_id"] = "{$name}.id as {$name}_id"; | |
163 | $this->_element["{$name}_id"] = 1; | |
164 | $this->_select[$fieldName] = "{$field['table_name']}.{$field['column_name']} as $fieldName"; | |
165 | $this->_element[$fieldName] = 1; | |
dcf0d348 | 166 | |
7f175707 | 167 | $this->joinCustomTableForField($field); |
6a488035 TO |
168 | } |
169 | } | |
170 | ||
171 | /** | |
192d36c5 | 172 | * Generate the where clause and also the english language equivalent. |
410e3761 | 173 | * |
174 | * @throws \CRM_Core_Exception | |
6a488035 | 175 | */ |
00be9182 | 176 | public function where() { |
6a488035 TO |
177 | foreach ($this->_ids as $id => $values) { |
178 | ||
192d36c5 | 179 | // Fixed for Issue CRM 607 |
6a488035 TO |
180 | if (CRM_Utils_Array::value($id, $this->_fields) === NULL || |
181 | !$values | |
182 | ) { | |
183 | continue; | |
184 | } | |
185 | ||
6a488035 TO |
186 | foreach ($values as $tuple) { |
187 | list($name, $op, $value, $grouping, $wildcard) = $tuple; | |
188 | ||
6a488035 | 189 | $field = $this->_fields[$id]; |
6a488035 | 190 | |
3130209f CW |
191 | $fieldName = "{$field['table_name']}.{$field['column_name']}"; |
192 | ||
05c5cbc8 | 193 | $isSerialized = CRM_Core_BAO_CustomField::isSerialized($field); |
194 | ||
3130209f | 195 | // fix $value here to escape sql injection attacks |
7ecb613a | 196 | $qillValue = NULL; |
9f388466 CW |
197 | if (!is_array($value)) { |
198 | $value = CRM_Core_DAO::escapeString(trim($value)); | |
8cee0c70 | 199 | $qillValue = CRM_Core_BAO_CustomField::displayValue($value, $id); |
7ecb613a | 200 | } |
201 | elseif (count($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) { | |
202 | $op = key($value); | |
8cee0c70 | 203 | $qillValue = strstr($op, 'NULL') ? NULL : CRM_Core_BAO_CustomField::displayValue($value[$op], $id); |
9f388466 | 204 | } |
c94d39fd | 205 | else { |
ec28b24d | 206 | $op = strstr($op, 'IN') ? $op : 'IN'; |
8cee0c70 | 207 | $qillValue = CRM_Core_BAO_CustomField::displayValue($value, $id); |
c94d39fd | 208 | } |
3130209f | 209 | |
2c261619 | 210 | $qillOp = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op); |
3130209f | 211 | |
7f175707 | 212 | // Ensure the table is joined in (eg if in where but not select). |
213 | $this->joinCustomTableForField($field); | |
6a488035 TO |
214 | switch ($field['data_type']) { |
215 | case 'String': | |
16b6d3d4 | 216 | case 'StateProvince': |
217 | case 'Country': | |
a61d2ab7 | 218 | case 'ContactReference': |
3130209f CW |
219 | |
220 | if ($field['is_search_range'] && is_array($value)) { | |
4c2fe77b | 221 | //didn't found any field under any of these three data-types as searchable by range |
6a488035 TO |
222 | } |
223 | else { | |
05c5cbc8 | 224 | // fix $value here to escape sql injection attacks |
225 | if (!is_array($value)) { | |
16b6d3d4 | 226 | if ($field['data_type'] == 'String') { |
d215c0e1 | 227 | $value = CRM_Utils_Type::escape($value, 'String'); |
16b6d3d4 | 228 | } |
bb0300b9 | 229 | elseif ($value) { |
16b6d3d4 | 230 | $value = CRM_Utils_Type::escape($value, 'Integer'); |
231 | } | |
be2fb01f | 232 | $value = str_replace(['[', ']', ','], ['\[', '\]', '[:comma:]'], $value); |
2f32a341 | 233 | $value = str_replace('|', '[:separator:]', $value); |
05c5cbc8 | 234 | } |
c94d39fd | 235 | elseif ($isSerialized) { |
236 | if (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) { | |
237 | $op = key($value); | |
238 | $value = $value[$op]; | |
239 | } | |
7bcee799 | 240 | // CRM-19006: escape characters like comma, | before building regex pattern |
241 | $value = (array) $value; | |
242 | foreach ($value as $key => $val) { | |
be2fb01f | 243 | $value[$key] = str_replace(['[', ']', ','], ['\[', '\]', '[:comma:]'], $val); |
894223d8 | 244 | $value[$key] = str_replace('|', '[:separator:]', $value[$key]); |
804f8b8e JM |
245 | if ($field['data_type'] == 'String') { |
246 | $value[$key] = CRM_Utils_Type::escape($value[$key], 'String'); | |
247 | } | |
248 | elseif ($value) { | |
249 | $value[$key] = CRM_Utils_Type::escape($value[$key], 'Integer'); | |
250 | } | |
7bcee799 | 251 | } |
252 | $value = implode(',', $value); | |
05c5cbc8 | 253 | } |
3130209f | 254 | |
2c261619 | 255 | // CRM-14563,CRM-16575 : Special handling of multi-select custom fields |
78fe145d | 256 | if ($isSerialized && !CRM_Utils_System::isNull($value) && !strstr($op, 'NULL') && !strstr($op, 'LIKE')) { |
528f09a1 | 257 | $sp = CRM_Core_DAO::VALUE_SEPARATOR; |
2f32a341 | 258 | $value = str_replace(",", "$sp|$sp", $value); |
be2fb01f | 259 | $value = str_replace(['[:comma:]', '(', ')'], [',', '[(]', '[)]'], $value); |
2f32a341 | 260 | |
c94d39fd | 261 | $op = (strstr($op, '!') || strstr($op, 'NOT')) ? 'NOT RLIKE' : 'RLIKE'; |
528f09a1 | 262 | $value = $sp . $value . $sp; |
c94d39fd | 263 | if (!$wildcard) { |
528f09a1 | 264 | foreach (explode("|", $value) as $val) { |
e5ccf3d9 | 265 | $val = str_replace('[:separator:]', '\|', $val); |
528f09a1 | 266 | $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $val, 'String'); |
267 | } | |
268 | } | |
269 | else { | |
e5ccf3d9 | 270 | $value = str_replace('[:separator:]', '\|', $value); |
528f09a1 | 271 | $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String'); |
2c261619 | 272 | } |
6a488035 | 273 | } |
528f09a1 | 274 | else { |
275 | //FIX for custom data query fired against no value(NULL/NOT NULL) | |
276 | $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String'); | |
277 | } | |
b4fb2d23 | 278 | $this->_qill[$grouping][] = $field['label'] . " $qillOp $qillValue"; |
6a488035 | 279 | } |
3130209f | 280 | break; |
6a488035 | 281 | |
6a488035 | 282 | case 'Int': |
4c2fe77b | 283 | $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer'); |
fe7f4414 | 284 | $this->_qill[$grouping][] = ts("%1 %2 %3", [1 => $field['label'], 2 => $qillOp, 3 => $qillValue]); |
3130209f | 285 | break; |
6a488035 TO |
286 | |
287 | case 'Boolean': | |
16b6d3d4 | 288 | if (!is_array($value)) { |
289 | if (strtolower($value) == 'yes' || strtolower($value) == strtolower(ts('Yes'))) { | |
290 | $value = 1; | |
291 | } | |
292 | else { | |
293 | $value = (int) $value; | |
294 | } | |
295 | $value = ($value == 1) ? 1 : 0; | |
296 | $qillValue = $value ? 'Yes' : 'No'; | |
6a488035 | 297 | } |
6a488035 | 298 | $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer'); |
be2fb01f | 299 | $this->_qill[$grouping][] = ts("%1 %2 %3", [1 => $field['label'], 2 => $qillOp, 3 => $qillValue]); |
3130209f | 300 | break; |
6a488035 TO |
301 | |
302 | case 'Link': | |
16b6d3d4 | 303 | case 'Memo': |
6a488035 | 304 | $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String'); |
be2fb01f | 305 | $this->_qill[$grouping][] = ts("%1 %2 %3", [1 => $field['label'], 2 => $qillOp, 3 => $qillValue]); |
3130209f | 306 | break; |
6a488035 TO |
307 | |
308 | case 'Money': | |
ec28b24d | 309 | $value = CRM_Utils_Array::value($op, (array) $value, $value); |
16b6d3d4 | 310 | if (is_array($value)) { |
6a488035 | 311 | foreach ($value as $key => $val) { |
e45c5e68 | 312 | // @todo - this clean money should be in the form layer - it's highly likely to be doing more harm than good here |
313 | // Note the only place I can find that this code is reached by is searching a custom money field in advanced search. | |
314 | // with euro style comma separators this doesn't work - with or without this cleanMoney. | |
315 | // So this should be removed but is not increasing the brokeness IMHO | |
316 | $value[$op][$key] = CRM_Utils_Rule::cleanMoney($value[$key]); | |
6a488035 | 317 | } |
16b6d3d4 | 318 | } |
319 | else { | |
e45c5e68 | 320 | // @todo - this clean money should be in the form layer - it's highly likely to be doing more harm than good here |
321 | // comments per above apply. cleanMoney | |
16b6d3d4 | 322 | $value = CRM_Utils_Rule::cleanMoney($value); |
323 | } | |
324 | ||
325 | case 'Float': | |
4c2fe77b | 326 | $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Float'); |
be2fb01f | 327 | $this->_qill[$grouping][] = ts("%1 %2 %3", [1 => $field['label'], 2 => $qillOp, 3 => $qillValue]); |
3130209f | 328 | break; |
6a488035 | 329 | |
6a488035 | 330 | case 'Date': |
2206409b | 331 | if (substr($name, -9, 9) !== '_relative' |
332 | && substr($name, -4, 4) !== '_low' | |
333 | && substr($name, -5, 5) !== '_high') { | |
7f175707 | 334 | // Relative dates are handled in the buildRelativeDateQuery function. |
335 | $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Date'); | |
336 | list($qillOp, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, $field['label'], $value, $op, [], CRM_Utils_Type::T_DATE); | |
337 | $this->_qill[$grouping][] = "{$field['label']} $qillOp '$qillVal'"; | |
338 | } | |
3130209f | 339 | break; |
6a488035 | 340 | |
6a488035 | 341 | case 'File': |
481a74f4 | 342 | if ($op == 'IS NULL' || $op == 'IS NOT NULL' || $op == 'IS EMPTY' || $op == 'IS NOT EMPTY') { |
6a488035 TO |
343 | switch ($op) { |
344 | case 'IS EMPTY': | |
345 | $op = 'IS NULL'; | |
346 | break; | |
2aa397bc | 347 | |
6a488035 TO |
348 | case 'IS NOT EMPTY': |
349 | $op = 'IS NOT NULL'; | |
350 | break; | |
351 | } | |
352 | $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op); | |
2c261619 | 353 | $this->_qill[$grouping][] = $field['label'] . " {$qillOp} "; |
6a488035 | 354 | } |
3130209f | 355 | break; |
6a488035 TO |
356 | } |
357 | } | |
358 | } | |
359 | } | |
360 | ||
361 | /** | |
d2e5d2ce | 362 | * Function that does the actual query generation. |
6a488035 TO |
363 | * basically ties all the above functions together |
364 | * | |
a6c01b45 CW |
365 | * @return array |
366 | * array of strings | |
6a488035 | 367 | */ |
00be9182 | 368 | public function query() { |
6a488035 TO |
369 | $this->select(); |
370 | ||
371 | $this->where(); | |
372 | ||
373 | $whereStr = NULL; | |
374 | if (!empty($this->_where)) { | |
be2fb01f | 375 | $clauses = []; |
6a488035 TO |
376 | foreach ($this->_where as $grouping => $values) { |
377 | if (!empty($values)) { | |
378 | $clauses[] = ' ( ' . implode(' AND ', $values) . ' ) '; | |
379 | } | |
380 | } | |
381 | if (!empty($clauses)) { | |
382 | $whereStr = ' ( ' . implode(' OR ', $clauses) . ' ) '; | |
383 | } | |
384 | } | |
385 | ||
be2fb01f | 386 | return [ |
353ffa53 | 387 | implode(' , ', $this->_select), |
6a488035 TO |
388 | implode(' ', $this->_tables), |
389 | $whereStr, | |
be2fb01f | 390 | ]; |
6a488035 TO |
391 | } |
392 | ||
7f175707 | 393 | /** |
394 | * Join the custom table for the field in (if not already in the query). | |
395 | * | |
396 | * @param array $field | |
397 | */ | |
398 | protected function joinCustomTableForField($field) { | |
399 | $name = $field['table_name']; | |
400 | $join = "\nLEFT JOIN $name ON $name.entity_id = {$field['search_table']}.id"; | |
401 | $this->_tables[$name] = $this->_tables[$name] ?? $join; | |
402 | $this->_whereTables[$name] = $this->_whereTables[$name] ?? $join; | |
410e3761 | 403 | |
404 | $joinTable = $field['search_table']; | |
405 | if ($joinTable) { | |
406 | $joinClause = 1; | |
407 | $joinTableAlias = $joinTable; | |
408 | // Set location-specific query | |
409 | if (isset($this->_locationSpecificCustomFields[$field['id']])) { | |
410 | list($locationType, $locationTypeId) = $this->_locationSpecificCustomFields[$field['id']]; | |
411 | $joinTableAlias = "$locationType-address"; | |
412 | $joinClause = "\nLEFT JOIN $joinTable `$locationType-address` ON (`$locationType-address`.contact_id = contact_a.id AND `$locationType-address`.location_type_id = $locationTypeId)"; | |
413 | } | |
414 | $this->_tables[$name] = "\nLEFT JOIN $name ON $name.entity_id = `$joinTableAlias`.id"; | |
415 | if (!empty($this->_ids[$field['id']])) { | |
416 | $this->_whereTables[$name] = $this->_tables[$name]; | |
417 | } | |
418 | if ($joinTable !== 'contact_a') { | |
419 | $this->_whereTables[$joinTableAlias] = $this->_tables[$joinTableAlias] = $joinClause; | |
420 | } | |
421 | } | |
7f175707 | 422 | } |
423 | ||
6a488035 | 424 | } |