3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
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 |
9 +--------------------------------------------------------------------+
16 * @copyright CiviCRM LLC https://civicrm.org/licensing
18 class CRM_Core_BAO_CustomQuery
{
19 const PREFIX
= 'custom_value_';
22 * The set of custom field ids.
36 * The name of the elements that are in the select clause.
37 * used to extract the values
44 * The tables involved in the query.
59 * The english language version of the query.
66 * The custom fields information.
75 public function getFields() {
76 return $this->_fields
;
80 * Searching for contacts?
84 protected $_contactSearch;
86 protected $_locationSpecificCustomFields;
89 * This stores custom data group types and tables that it extends.
93 public static $extendsMap = [
94 'Contact' => 'civicrm_contact',
95 'Individual' => 'civicrm_contact',
96 'Household' => 'civicrm_contact',
97 'Organization' => 'civicrm_contact',
98 'Contribution' => 'civicrm_contribution',
99 'ContributionRecur' => 'civicrm_contribution_recur',
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',
110 'Campaign' => 'civicrm_campaign',
111 'Survey' => 'civicrm_survey',
117 * Takes in a set of custom field ids andsets up the data structures to
121 * The set of custom field ids.
123 * @param bool $contactSearch
124 * @param array $locationSpecificFields
126 public function __construct($ids, $contactSearch = FALSE, $locationSpecificFields = []) {
128 $this->_locationSpecificCustomFields
= $locationSpecificFields;
131 $this->_element
= [];
133 $this->_whereTables
= [];
137 $this->_contactSearch
= $contactSearch;
138 $this->_fields
= CRM_Core_BAO_CustomField
::getFields('ANY', FALSE, FALSE, NULL, NULL, FALSE, FALSE, FALSE);
142 * Generate the select clause and the associated tables.
144 public function select() {
145 if (empty($this->_fields
)) {
149 foreach (array_keys($this->_ids
) as $id) {
150 // Ignore any custom field ids within the ids array that are not present in the fields array.
151 if (empty($this->_fields
[$id])) {
154 $field = $this->_fields
[$id];
156 if ($this->_contactSearch
&& $field['search_table'] === 'contact_a') {
157 CRM_Contact_BAO_Query
::$_openedPanes[ts('Custom Fields')] = TRUE;
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;
167 $this->joinCustomTableForField($field);
172 * Generate the where clause and also the english language equivalent.
174 * @throws \CRM_Core_Exception
176 public function where() {
177 foreach ($this->_ids
as $id => $values) {
179 // Fixed for Issue CRM 607
180 if (CRM_Utils_Array
::value($id, $this->_fields
) === NULL ||
186 foreach ($values as $tuple) {
187 list($name, $op, $value, $grouping, $wildcard) = $tuple;
189 $field = $this->_fields
[$id];
191 $fieldName = "{$field['table_name']}.{$field['column_name']}";
193 $isSerialized = CRM_Core_BAO_CustomField
::isSerialized($field);
195 // fix $value here to escape sql injection attacks
197 if (!is_array($value)) {
198 $value = CRM_Core_DAO
::escapeString(trim($value));
199 $qillValue = CRM_Core_BAO_CustomField
::displayValue($value, $id);
201 elseif (count($value) && in_array(key($value), CRM_Core_DAO
::acceptedSQLOperators(), TRUE)) {
203 $qillValue = strstr($op, 'NULL') ?
NULL : CRM_Core_BAO_CustomField
::displayValue($value[$op], $id);
206 $op = strstr($op, 'IN') ?
$op : 'IN';
207 $qillValue = CRM_Core_BAO_CustomField
::displayValue($value, $id);
210 $qillOp = CRM_Utils_Array
::value($op, CRM_Core_SelectValues
::getSearchBuilderOperators(), $op);
212 // Ensure the table is joined in (eg if in where but not select).
213 $this->joinCustomTableForField($field);
214 switch ($field['data_type']) {
216 case 'StateProvince':
219 if ($field['is_search_range'] && is_array($value)) {
220 //didn't found any field under any of these three data-types as searchable by range
223 // fix $value here to escape sql injection attacks
224 if (!is_array($value)) {
225 if ($field['data_type'] == 'String') {
226 $value = CRM_Utils_Type
::escape($value, 'String');
229 $value = CRM_Utils_Type
::escape($value, 'Integer');
231 $value = str_replace(['[', ']', ','], ['\[', '\]', '[:comma:]'], $value);
232 $value = str_replace('|', '[:separator:]', $value);
234 elseif ($isSerialized) {
235 if (in_array(key($value), CRM_Core_DAO
::acceptedSQLOperators(), TRUE)) {
237 $value = $value[$op];
239 // CRM-19006: escape characters like comma, | before building regex pattern
240 $value = (array) $value;
241 foreach ($value as $key => $val) {
242 $value[$key] = str_replace(['[', ']', ','], ['\[', '\]', '[:comma:]'], $val);
243 $value[$key] = str_replace('|', '[:separator:]', $value[$key]);
244 if ($field['data_type'] == 'String') {
245 $value[$key] = CRM_Utils_Type
::escape($value[$key], 'String');
248 $value[$key] = CRM_Utils_Type
::escape($value[$key], 'Integer');
251 $value = implode(',', $value);
254 // CRM-14563,CRM-16575 : Special handling of multi-select custom fields
255 if ($isSerialized && !CRM_Utils_System
::isNull($value) && !strstr($op, 'NULL') && !strstr($op, 'LIKE')) {
256 $sp = CRM_Core_DAO
::VALUE_SEPARATOR
;
257 $value = str_replace(",", "$sp|$sp", $value);
258 $value = str_replace(['[:comma:]', '(', ')'], [',', '[(]', '[)]'], $value);
260 $op = (strstr($op, '!') ||
strstr($op, 'NOT')) ?
'NOT RLIKE' : 'RLIKE';
261 $value = $sp . $value . $sp;
263 foreach (explode("|", $value) as $val) {
264 $val = str_replace('[:separator:]', '\|', $val);
265 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $val, 'String');
269 $value = str_replace('[:separator:]', '\|', $value);
270 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'String');
274 //FIX for custom data query fired against no value(NULL/NOT NULL)
275 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'String');
277 $this->_qill
[$grouping][] = $field['label'] . " $qillOp $qillValue";
281 case 'ContactReference':
282 $label = $value ? CRM_Core_DAO
::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name') : '';
283 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'String');
284 $this->_qill
[$grouping][] = $field['label'] . " $qillOp $label";
288 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'Integer');
289 $this->_qill
[$grouping][] = ts("%1 %2 %3", [1 => $field['label'], 2 => $qillOp, 3 => $qillValue]);
293 if (!is_array($value)) {
294 if (strtolower($value) == 'yes' ||
strtolower($value) == strtolower(ts('Yes'))) {
298 $value = (int) $value;
300 $value = ($value == 1) ?
1 : 0;
301 $qillValue = $value ?
'Yes' : 'No';
303 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'Integer');
304 $this->_qill
[$grouping][] = ts("%1 %2 %3", [1 => $field['label'], 2 => $qillOp, 3 => $qillValue]);
309 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'String');
310 $this->_qill
[$grouping][] = ts("%1 %2 %3", [1 => $field['label'], 2 => $qillOp, 3 => $qillValue]);
314 $value = CRM_Utils_Array
::value($op, (array) $value, $value);
315 if (is_array($value)) {
316 foreach ($value as $key => $val) {
317 // @todo - this clean money should be in the form layer - it's highly likely to be doing more harm than good here
318 // Note the only place I can find that this code is reached by is searching a custom money field in advanced search.
319 // with euro style comma separators this doesn't work - with or without this cleanMoney.
320 // So this should be removed but is not increasing the brokeness IMHO
321 $value[$op][$key] = CRM_Utils_Rule
::cleanMoney($value[$key]);
325 // @todo - this clean money should be in the form layer - it's highly likely to be doing more harm than good here
326 // comments per above apply. cleanMoney
327 $value = CRM_Utils_Rule
::cleanMoney($value);
331 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'Float');
332 $this->_qill
[$grouping][] = ts("%1 %2 %3", [1 => $field['label'], 2 => $qillOp, 3 => $qillValue]);
336 if (substr($name, -9, 9) !== '_relative'
337 && substr($name, -4, 4) !== '_low'
338 && substr($name, -5, 5) !== '_high') {
339 // Relative dates are handled in the buildRelativeDateQuery function.
340 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'Date');
341 list($qillOp, $qillVal) = CRM_Contact_BAO_Query
::buildQillForFieldValue(NULL, $field['label'], $value, $op, [], CRM_Utils_Type
::T_DATE
);
342 $this->_qill
[$grouping][] = "{$field['label']} $qillOp '$qillVal'";
347 if ($op == 'IS NULL' ||
$op == 'IS NOT NULL' ||
$op == 'IS EMPTY' ||
$op == 'IS NOT EMPTY') {
357 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op);
358 $this->_qill
[$grouping][] = $field['label'] . " {$qillOp} ";
367 * Function that does the actual query generation.
368 * basically ties all the above functions together
373 public function query() {
379 if (!empty($this->_where
)) {
381 foreach ($this->_where
as $grouping => $values) {
382 if (!empty($values)) {
383 $clauses[] = ' ( ' . implode(' AND ', $values) . ' ) ';
386 if (!empty($clauses)) {
387 $whereStr = ' ( ' . implode(' OR ', $clauses) . ' ) ';
392 implode(' , ', $this->_select
),
393 implode(' ', $this->_tables
),
399 * Join the custom table for the field in (if not already in the query).
401 * @param array $field
403 protected function joinCustomTableForField($field) {
404 $name = $field['table_name'];
405 $join = "\nLEFT JOIN $name ON $name.entity_id = {$field['search_table']}.id";
406 $this->_tables
[$name] = $this->_tables
[$name] ??
$join;
407 $this->_whereTables
[$name] = $this->_whereTables
[$name] ??
$join;
409 $joinTable = $field['search_table'];
412 $joinTableAlias = $joinTable;
413 // Set location-specific query
414 if (isset($this->_locationSpecificCustomFields
[$field['id']])) {
415 list($locationType, $locationTypeId) = $this->_locationSpecificCustomFields
[$field['id']];
416 $joinTableAlias = "$locationType-address";
417 $joinClause = "\nLEFT JOIN $joinTable `$locationType-address` ON (`$locationType-address`.contact_id = contact_a.id AND `$locationType-address`.location_type_id = $locationTypeId)";
419 $this->_tables
[$name] = "\nLEFT JOIN $name ON $name.entity_id = `$joinTableAlias`.id";
420 if (!empty($this->_ids
[$field['id']])) {
421 $this->_whereTables
[$name] = $this->_tables
[$name];
423 if ($joinTable !== 'contact_a') {
424 $this->_whereTables
[$joinTableAlias] = $this->_tables
[$joinTableAlias] = $joinClause;