3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2016 |
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-2016
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');
182 if (array_key_exists($extends, self
::$extendsMap)) {
183 $extendsTable = self
::$extendsMap[$extends];
185 elseif (in_array($extends, CRM_Contact_BAO_ContactType
::subTypes())) {
186 // if $extends is a subtype, refer contact table
187 $extendsTable = self
::$extendsMap['Contact'];
189 $this->_fields
[$dao->id
] = array(
191 'label' => $dao->label
,
192 'extends' => $extendsTable,
193 'data_type' => $dao->data_type
,
194 'html_type' => $dao->html_type
,
195 'is_search_range' => $dao->is_search_range
,
196 'column_name' => $dao->column_name
,
197 'table_name' => $dao->table_name
,
198 'option_group_id' => $dao->option_group_id
,
201 // Deprecated (and poorly named) cache of field attributes
202 $this->_options
[$dao->id
] = array(
203 'attributes' => array(
204 'label' => $dao->label
,
205 'data_type' => $dao->data_type
,
206 'html_type' => $dao->html_type
,
210 $options = CRM_Core_PseudoConstant
::get('CRM_Core_BAO_CustomField', 'custom_' . $dao->id
, array(), 'search');
212 $this->_options
[$dao->id
] +
= $options;
215 if ($dao->html_type
== 'Select Date') {
216 $this->_options
[$dao->id
]['attributes']['date_format'] = $dao->date_format
;
217 $this->_options
[$dao->id
]['attributes']['time_format'] = $dao->time_format
;
223 * Generate the select clause and the associated tables.
225 public function select() {
226 if (empty($this->_fields
)) {
230 foreach ($this->_fields
as $id => $field) {
231 $name = $field['table_name'];
232 $fieldName = 'custom_' . $field['id'];
233 $this->_select
["{$name}_id"] = "{$name}.id as {$name}_id";
234 $this->_element
["{$name}_id"] = 1;
235 $this->_select
[$fieldName] = "{$field['table_name']}.{$field['column_name']} as $fieldName";
236 $this->_element
[$fieldName] = 1;
239 if ($field['extends'] == 'civicrm_group') {
242 elseif ($field['extends'] == 'civicrm_contact') {
243 $joinTable = 'contact_a';
245 elseif ($field['extends'] == 'civicrm_contribution') {
246 $joinTable = $field['extends'];
248 elseif (in_array($field['extends'], self
::$extendsMap)) {
249 $joinTable = $field['extends'];
255 $this->_tables
[$name] = "\nLEFT JOIN $name ON $name.entity_id = $joinTable.id";
257 if ($this->_ids
[$id]) {
258 $this->_whereTables
[$name] = $this->_tables
[$name];
263 $joinTableAlias = $joinTable;
264 // Set location-specific query
265 if (isset($this->_locationSpecificCustomFields
[$id])) {
266 list($locationType, $locationTypeId) = $this->_locationSpecificCustomFields
[$id];
267 $joinTableAlias = "$locationType-address";
268 $joinClause = "\nLEFT JOIN $joinTable `$locationType-address` ON (`$locationType-address`.contact_id = contact_a.id AND `$locationType-address`.location_type_id = $locationTypeId)";
270 $this->_tables
[$name] = "\nLEFT JOIN $name ON $name.entity_id = `$joinTableAlias`.id";
271 if ($this->_ids
[$id]) {
272 $this->_whereTables
[$name] = $this->_tables
[$name];
274 if ($joinTable != 'contact_a') {
275 $this->_whereTables
[$joinTableAlias] = $this->_tables
[$joinTableAlias] = $joinClause;
277 elseif ($this->_contactSearch
) {
278 CRM_Contact_BAO_Query
::$_openedPanes[ts('Custom Fields')] = TRUE;
285 * Generate the where clause and also the english language equivalent.
287 public function where() {
288 foreach ($this->_ids
as $id => $values) {
290 // Fixed for Issue CRM 607
291 if (CRM_Utils_Array
::value($id, $this->_fields
) === NULL ||
297 $strtolower = function_exists('mb_strtolower') ?
'mb_strtolower' : 'strtolower';
299 foreach ($values as $tuple) {
300 list($name, $op, $value, $grouping, $wildcard) = $tuple;
302 $field = $this->_fields
[$id];
304 $fieldName = "{$field['table_name']}.{$field['column_name']}";
306 $isSerialized = CRM_Core_BAO_CustomField
::isSerialized($field);
308 // fix $value here to escape sql injection attacks
310 if (!is_array($value)) {
311 $value = CRM_Core_DAO
::escapeString(trim($value));
312 $qillValue = CRM_Core_BAO_CustomField
::displayValue($value, $id);
314 elseif (count($value) && in_array(key($value), CRM_Core_DAO
::acceptedSQLOperators(), TRUE)) {
316 $qillValue = strstr($op, 'NULL') ?
NULL : CRM_Core_BAO_CustomField
::displayValue($value[$op], $id);
319 $op = strstr($op, 'IN') ?
$op : 'IN';
320 $qillValue = CRM_Core_BAO_CustomField
::displayValue($value, $id);
323 $qillOp = CRM_Utils_Array
::value($op, CRM_Core_SelectValues
::getSearchBuilderOperators(), $op);
325 switch ($field['data_type']) {
327 case 'StateProvince':
330 if ($field['is_search_range'] && is_array($value)) {
331 //didn't found any field under any of these three data-types as searchable by range
334 // fix $value here to escape sql injection attacks
335 if (!is_array($value)) {
336 if ($field['data_type'] == 'String') {
337 $value = CRM_Utils_Type
::escape($strtolower($value), 'String');
340 $value = CRM_Utils_Type
::escape($value, 'Integer');
343 elseif ($isSerialized) {
344 if (in_array(key($value), CRM_Core_DAO
::acceptedSQLOperators(), TRUE)) {
346 $value = $value[$op];
348 $value = implode(',', (array) $value);
351 // CRM-14563,CRM-16575 : Special handling of multi-select custom fields
352 if ($isSerialized && !CRM_Utils_System
::isNull($value) && !strstr($op, 'NULL') && !strstr($op, 'LIKE')) {
353 $sp = CRM_Core_DAO
::VALUE_SEPARATOR
;
354 if (strstr($op, 'IN')) {
355 $value = str_replace(",", "$sp|$sp", $value);
356 $value = str_replace('(', '[[.left-parenthesis.]]', $value);
357 $value = str_replace(')', '[[.right-parenthesis.]]', $value);
359 $op = (strstr($op, '!') ||
strstr($op, 'NOT')) ?
'NOT RLIKE' : 'RLIKE';
360 $value = $sp . $value . $sp;
362 foreach (explode("|", $value) as $val) {
363 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $val, 'String');
367 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'String');
371 //FIX for custom data query fired against no value(NULL/NOT NULL)
372 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'String');
374 $this->_qill
[$grouping][] = $field['label'] . " $qillOp $qillValue";
378 case 'ContactReference':
379 $label = $value ? CRM_Core_DAO
::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name') : '';
380 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'String');
381 $this->_qill
[$grouping][] = $field['label'] . " $qillOp $label";
385 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'Integer');
386 $this->_qill
[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));;
390 if (!is_array($value)) {
391 if (strtolower($value) == 'yes' ||
strtolower($value) == strtolower(ts('Yes'))) {
395 $value = (int) $value;
397 $value = ($value == 1) ?
1 : 0;
398 $qillValue = $value ?
'Yes' : 'No';
400 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'Integer');
401 $this->_qill
[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
406 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'String');
407 $this->_qill
[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
411 $value = CRM_Utils_Array
::value($op, (array) $value, $value);
412 if (is_array($value)) {
413 foreach ($value as $key => $val) {
414 $value[$key] = CRM_Utils_Rule
::cleanMoney($value[$key]);
418 $value = CRM_Utils_Rule
::cleanMoney($value);
422 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'Float');
423 $this->_qill
[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
427 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op, $value, 'String');
428 list($qillOp, $qillVal) = CRM_Contact_BAO_Query
::buildQillForFieldValue(NULL, $field['label'], $value, $op, array(), CRM_Utils_Type
::T_DATE
);
429 $this->_qill
[$grouping][] = "{$field['label']} $qillOp '$qillVal'";
433 if ($op == 'IS NULL' ||
$op == 'IS NOT NULL' ||
$op == 'IS EMPTY' ||
$op == 'IS NOT EMPTY') {
443 $this->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldName, $op);
444 $this->_qill
[$grouping][] = $field['label'] . " {$qillOp} ";
453 * Function that does the actual query generation.
454 * basically ties all the above functions together
459 public function query() {
465 if (!empty($this->_where
)) {
467 foreach ($this->_where
as $grouping => $values) {
468 if (!empty($values)) {
469 $clauses[] = ' ( ' . implode(' AND ', $values) . ' ) ';
472 if (!empty($clauses)) {
473 $whereStr = ' ( ' . implode(' OR ', $clauses) . ' ) ';
478 implode(' , ', $this->_select
),
479 implode(' ', $this->_tables
),