3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2019 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
10 | CiviCRM is free software; you can copy, modify, and distribute it |
11 | under the terms of the GNU Affero General Public License |
12 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
14 | CiviCRM is distributed in the hope that it will be useful, but |
15 | WITHOUT ANY WARRANTY; without even the implied warranty of |
16 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
17 | See the GNU Affero General Public License for more details. |
19 | You should have received a copy of the GNU Affero General Public |
20 | License and the CiviCRM Licensing Exception along |
21 | with this program; if not, contact CiviCRM LLC |
22 | at info[AT]civicrm[DOT]org. If you have questions about the |
23 | GNU Affero General Public License or the licensing of CiviCRM, |
24 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
25 +--------------------------------------------------------------------+
32 * @copyright CiviCRM LLC (c) 2004-2019
34 class CRM_Core_BAO_CustomQuery
{
35 const PREFIX
= 'custom_value_';
38 * The set of custom field ids.
52 * The name of the elements that are in the select clause.
53 * used to extract the values
60 * The tables involved in the query.
75 * The english language version of the query.
83 * No longer needed due to CRM-17646 refactoring, but still used in some places
90 * The custom fields information.
97 * Searching for contacts?
101 protected $_contactSearch;
103 protected $_locationSpecificCustomFields;
106 * This stores custom data group types and tables that it extends.
110 static $extendsMap = array(
111 'Contact' => 'civicrm_contact',
112 'Individual' => 'civicrm_contact',
113 'Household' => 'civicrm_contact',
114 'Organization' => 'civicrm_contact',
115 'Contribution' => 'civicrm_contribution',
116 'ContributionRecur' => 'civicrm_contribution_recur',
117 'Membership' => 'civicrm_membership',
118 'Participant' => 'civicrm_participant',
119 'Group' => 'civicrm_group',
120 'Relationship' => 'civicrm_relationship',
121 'Event' => 'civicrm_event',
122 'Case' => 'civicrm_case',
123 'Activity' => 'civicrm_activity',
124 'Pledge' => 'civicrm_pledge',
125 'Grant' => 'civicrm_grant',
126 'Address' => 'civicrm_address',
127 'Campaign' => 'civicrm_campaign',
128 'Survey' => 'civicrm_survey',
134 * Takes in a set of custom field ids andsets up the data structures to
138 * The set of custom field ids.
140 * @param bool $contactSearch
141 * @param array $locationSpecificFields
143 public function __construct($ids, $contactSearch = FALSE, $locationSpecificFields = array()) {
145 $this->_locationSpecificCustomFields
= $locationSpecificFields;
147 $this->_select
= array();
148 $this->_element
= array();
149 $this->_tables
= array();
150 $this->_whereTables
= array();
151 $this->_where
= array();
152 $this->_qill
= array();
153 $this->_options
= array();
155 $this->_fields
= array();
156 $this->_contactSearch
= $contactSearch;
158 if (empty($this->_ids
)) {
162 // initialize the field array
163 $tmpArray = array_keys($this->_ids
);
164 $idString = implode(',', $tmpArray);
166 SELECT f.id, f.label, f.data_type,
167 f.html_type, f.is_search_range,
168 f.option_group_id, f.custom_group_id,
169 f.column_name, g.table_name,
170 f.date_format,f.time_format
171 FROM civicrm_custom_field f,
172 civicrm_custom_group g
173 WHERE f.custom_group_id = g.id
176 AND f.id IN ( $idString )";
178 $dao = CRM_Core_DAO
::executeQuery($query);
179 while ($dao->fetch()) {
180 // get the group dao to figure which class this custom field extends
181 $extends = CRM_Core_DAO
::getFieldValue('CRM_Core_DAO_CustomGroup', $dao->custom_group_id
, 'extends');
183 if (array_key_exists($extends, self
::$extendsMap)) {
184 $extendsTable = self
::$extendsMap[$extends];
186 elseif (in_array($extends, CRM_Contact_BAO_ContactType
::subTypes())) {
187 // if $extends is a subtype, refer contact table
188 $extendsTable = self
::$extendsMap['Contact'];
190 $this->_fields
[$dao->id
] = array(
192 'label' => $dao->label
,
193 'extends' => $extendsTable,
194 'data_type' => $dao->data_type
,
195 'html_type' => $dao->html_type
,
196 'is_search_range' => $dao->is_search_range
,
197 'column_name' => $dao->column_name
,
198 'table_name' => $dao->table_name
,
199 'option_group_id' => $dao->option_group_id
,
202 // Deprecated (and poorly named) cache of field attributes
203 $this->_options
[$dao->id
] = array(
204 'attributes' => array(
205 'label' => $dao->label
,
206 'data_type' => $dao->data_type
,
207 'html_type' => $dao->html_type
,
211 $options = CRM_Core_PseudoConstant
::get('CRM_Core_BAO_CustomField', 'custom_' . $dao->id
, array(), 'search');
213 $this->_options
[$dao->id
] +
= $options;
216 if ($dao->html_type
== 'Select Date') {
217 $this->_options
[$dao->id
]['attributes']['date_format'] = $dao->date_format
;
218 $this->_options
[$dao->id
]['attributes']['time_format'] = $dao->time_format
;
224 * Generate the select clause and the associated tables.
226 public function select() {
227 if (empty($this->_fields
)) {
231 foreach ($this->_fields
as $id => $field) {
232 $name = $field['table_name'];
233 $fieldName = 'custom_' . $field['id'];
234 $this->_select
["{$name}_id"] = "{$name}.id as {$name}_id";
235 $this->_element
["{$name}_id"] = 1;
236 $this->_select
[$fieldName] = "{$field['table_name']}.{$field['column_name']} as $fieldName";
237 $this->_element
[$fieldName] = 1;
240 if ($field['extends'] == 'civicrm_group') {
243 elseif ($field['extends'] == 'civicrm_contact') {
244 $joinTable = 'contact_a';
246 elseif ($field['extends'] == 'civicrm_contribution') {
247 $joinTable = $field['extends'];
249 elseif (in_array($field['extends'], self
::$extendsMap)) {
250 $joinTable = $field['extends'];
256 $this->_tables
[$name] = "\nLEFT JOIN $name ON $name.entity_id = $joinTable.id";
258 if ($this->_ids
[$id]) {
259 $this->_whereTables
[$name] = $this->_tables
[$name];
264 $joinTableAlias = $joinTable;
265 // Set location-specific query
266 if (isset($this->_locationSpecificCustomFields
[$id])) {
267 list($locationType, $locationTypeId) = $this->_locationSpecificCustomFields
[$id];
268 $joinTableAlias = "$locationType-address";
269 $joinClause = "\nLEFT JOIN $joinTable `$locationType-address` ON (`$locationType-address`.contact_id = contact_a.id AND `$locationType-address`.location_type_id = $locationTypeId)";
271 $this->_tables
[$name] = "\nLEFT JOIN $name ON $name.entity_id = `$joinTableAlias`.id";
272 if ($this->_ids
[$id]) {
273 $this->_whereTables
[$name] = $this->_tables
[$name];
275 if ($joinTable != 'contact_a') {
276 $this->_whereTables
[$joinTableAlias] = $this->_tables
[$joinTableAlias] = $joinClause;
278 elseif ($this->_contactSearch
) {
279 CRM_Contact_BAO_Query
::$_openedPanes[ts('Custom Fields')] = TRUE;
286 * Generate the where clause and also the english language equivalent.
288 public function where() {
289 foreach ($this->_ids
as $id => $values) {
291 // Fixed for Issue CRM 607
292 if (CRM_Utils_Array
::value($id, $this->_fields
) === NULL ||
298 foreach ($values as $tuple) {
299 list($name, $op, $value, $grouping, $wildcard) = $tuple;
301 $field = $this->_fields
[$id];
303 $fieldName = "{$field['table_name']}.{$field['column_name']}";
305 $isSerialized = CRM_Core_BAO_CustomField
::isSerialized($field);
307 // fix $value here to escape sql injection attacks
309 if (!is_array($value)) {
310 $value = CRM_Core_DAO
::escapeString(trim($value));
311 $qillValue = CRM_Core_BAO_CustomField
::displayValue($value, $id);
313 elseif (count($value) && in_array(key($value), CRM_Core_DAO
::acceptedSQLOperators(), TRUE)) {
315 $qillValue = strstr($op, 'NULL') ?
NULL : CRM_Core_BAO_CustomField
::displayValue($value[$op], $id);
318 $op = strstr($op, 'IN') ?
$op : 'IN';
319 $qillValue = CRM_Core_BAO_CustomField
::displayValue($value, $id);
322 $qillOp = CRM_Utils_Array
::value($op, CRM_Core_SelectValues
::getSearchBuilderOperators(), $op);
324 switch ($field['data_type']) {
326 case 'StateProvince':
329 if ($field['is_search_range'] && is_array($value)) {
330 //didn't found any field under any of these three data-types as searchable by range
333 // fix $value here to escape sql injection attacks
334 if (!is_array($value)) {
335 if ($field['data_type'] == 'String') {
336 $value = CRM_Utils_Type
::escape($value, 'String');
339 $value = CRM_Utils_Type
::escape($value, 'Integer');
341 $value = str_replace(array('[', ']', ','), array('\[', '\]', '[:comma:]'), $value);
342 $value = str_replace('|', '[:separator:]', $value);
344 elseif ($isSerialized) {
345 if (in_array(key($value), CRM_Core_DAO
::acceptedSQLOperators(), TRUE)) {
347 $value = $value[$op];
349 // CRM-19006: escape characters like comma, | before building regex pattern
350 $value = (array) $value;
351 foreach ($value as $key => $val) {
352 $value[$key] = str_replace(array('[', ']', ','), array('\[', '\]', '[:comma:]'), $val);
353 $value[$key] = str_replace('|', '[:separator:]', $value[$key]);
355 $value = implode(',', $value);
358 // CRM-14563,CRM-16575 : Special handling of multi-select custom fields
359 if ($isSerialized && !CRM_Utils_System
::isNull($value) && !strstr($op, 'NULL') && !strstr($op, 'LIKE')) {
360 $sp = CRM_Core_DAO
::VALUE_SEPARATOR
;
361 $value = str_replace(",", "$sp|$sp", $value);
362 $value = str_replace(array('[:comma:]', '(', ')'), array(',', '[(]', '[)]'), $value);
364 $op = (strstr($op, '!') ||
strstr($op, 'NOT')) ?
'NOT RLIKE' : 'RLIKE';
365 $value = $sp . $value . $sp;
367 foreach (explode("|", $value) as $val) {
368 $val = str_replace('[:separator:]', '\|', $val);
369 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $val, 'String');
373 $value = str_replace('[:separator:]', '\|', $value);
374 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'String');
378 //FIX for custom data query fired against no value(NULL/NOT NULL)
379 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'String');
381 $this->_qill
[$grouping][] = $field['label'] . " $qillOp $qillValue";
385 case 'ContactReference':
386 $label = $value ? CRM_Core_DAO
::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name') : '';
387 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'String');
388 $this->_qill
[$grouping][] = $field['label'] . " $qillOp $label";
392 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'Integer');
393 $this->_qill
[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));;
397 if (!is_array($value)) {
398 if (strtolower($value) == 'yes' ||
strtolower($value) == strtolower(ts('Yes'))) {
402 $value = (int) $value;
404 $value = ($value == 1) ?
1 : 0;
405 $qillValue = $value ?
'Yes' : 'No';
407 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'Integer');
408 $this->_qill
[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
413 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'String');
414 $this->_qill
[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
418 $value = CRM_Utils_Array
::value($op, (array) $value, $value);
419 if (is_array($value)) {
420 foreach ($value as $key => $val) {
421 // @todo - this clean money should be in the form layer - it's highly likely to be doing more harm than good here
422 // Note the only place I can find that this code is reached by is searching a custom money field in advanced search.
423 // with euro style comma separators this doesn't work - with or without this cleanMoney.
424 // So this should be removed but is not increasing the brokeness IMHO
425 $value[$op][$key] = CRM_Utils_Rule
::cleanMoney($value[$key]);
429 // @todo - this clean money should be in the form layer - it's highly likely to be doing more harm than good here
430 // comments per above apply. cleanMoney
431 $value = CRM_Utils_Rule
::cleanMoney($value);
435 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'Float');
436 $this->_qill
[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
440 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'Date');
441 list($qillOp, $qillVal) = CRM_Contact_BAO_Query
::buildQillForFieldValue(NULL, $field['label'], $value, $op, array(), CRM_Utils_Type
::T_DATE
);
442 $this->_qill
[$grouping][] = "{$field['label']} $qillOp '$qillVal'";
446 if ($op == 'IS NULL' ||
$op == 'IS NOT NULL' ||
$op == 'IS EMPTY' ||
$op == 'IS NOT EMPTY') {
456 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op);
457 $this->_qill
[$grouping][] = $field['label'] . " {$qillOp} ";
466 * Function that does the actual query generation.
467 * basically ties all the above functions together
472 public function query() {
478 if (!empty($this->_where
)) {
480 foreach ($this->_where
as $grouping => $values) {
481 if (!empty($values)) {
482 $clauses[] = ' ( ' . implode(' AND ', $values) . ' ) ';
485 if (!empty($clauses)) {
486 $whereStr = ' ( ' . implode(' OR ', $clauses) . ' ) ';
491 implode(' , ', $this->_select
),
492 implode(' ', $this->_tables
),