Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | ||
3 | /* | |
4 | +--------------------------------------------------------------------+ | |
06b69b18 | 5 | | CiviCRM version 4.5 | |
6a488035 | 6 | +--------------------------------------------------------------------+ |
06b69b18 | 7 | | Copyright CiviCRM LLC (c) 2004-2014 | |
6a488035 TO |
8 | +--------------------------------------------------------------------+ |
9 | | This file is a part of CiviCRM. | | |
10 | | | | |
11 | | CiviCRM is free software; you can copy, modify, and distribute it | | |
12 | | under the terms of the GNU Affero General Public License | | |
13 | | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. | | |
14 | | | | |
15 | | CiviCRM is distributed in the hope that it will be useful, but | | |
16 | | WITHOUT ANY WARRANTY; without even the implied warranty of | | |
17 | | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. | | |
18 | | See the GNU Affero General Public License for more details. | | |
19 | | | | |
20 | | You should have received a copy of the GNU Affero General Public | | |
21 | | License and the CiviCRM Licensing Exception along | | |
22 | | with this program; if not, contact CiviCRM LLC | | |
23 | | at info[AT]civicrm[DOT]org. If you have questions about the | | |
24 | | GNU Affero General Public License or the licensing of CiviCRM, | | |
25 | | see the CiviCRM license FAQ at http://civicrm.org/licensing | | |
26 | +--------------------------------------------------------------------+ | |
27 | */ | |
28 | ||
29 | /** | |
30 | * | |
31 | * | |
32 | * @package CRM | |
06b69b18 | 33 | * @copyright CiviCRM LLC (c) 2004-2014 |
6a488035 TO |
34 | * $Id$ |
35 | * | |
36 | */ | |
37 | class CRM_Core_BAO_CustomQuery { | |
38 | CONST PREFIX = 'custom_value_'; | |
39 | ||
40 | /** | |
41 | * the set of custom field ids | |
42 | * | |
43 | * @var array | |
44 | */ | |
45 | protected $_ids; | |
46 | ||
47 | /** | |
48 | * the select clause | |
49 | * | |
50 | * @var array | |
51 | */ | |
52 | public $_select; | |
53 | ||
54 | /** | |
55 | * the name of the elements that are in the select clause | |
56 | * used to extract the values | |
57 | * | |
58 | * @var array | |
59 | */ | |
60 | public $_element; | |
61 | ||
62 | /** | |
63 | * the tables involved in the query | |
64 | * | |
65 | * @var array | |
66 | */ | |
67 | public $_tables; | |
68 | public $_whereTables; | |
69 | ||
70 | /** | |
71 | * the where clause | |
72 | * | |
73 | * @var array | |
74 | */ | |
75 | public $_where; | |
76 | ||
77 | /** | |
78 | * The english language version of the query | |
79 | * | |
80 | * @var array | |
81 | */ | |
82 | public $_qill; | |
83 | ||
84 | /** | |
85 | * The cache to translate the option values into labels | |
86 | * | |
87 | * @var array | |
88 | */ | |
89 | public $_options; | |
90 | ||
91 | /** | |
92 | * The custom fields information | |
93 | * | |
94 | * @var array | |
95 | */ | |
96 | public $_fields; | |
97 | ||
98 | /** | |
99 | * Searching for contacts? | |
100 | * | |
101 | * @var boolean | |
102 | */ | |
103 | protected $_contactSearch; | |
104 | ||
247ad911 | 105 | protected $_locationSpecificCustomFields; |
442df34b | 106 | |
6a488035 TO |
107 | /** |
108 | * This stores custom data group types and tables that it extends | |
109 | * | |
dcf0d348 | 110 | * @todo add comments explaining why survey & campaign are missing from this |
6a488035 TO |
111 | * @var array |
112 | * @static | |
113 | */ | |
114 | static $extendsMap = array( | |
115 | 'Contact' => 'civicrm_contact', | |
116 | 'Individual' => 'civicrm_contact', | |
117 | 'Household' => 'civicrm_contact', | |
118 | 'Organization' => 'civicrm_contact', | |
119 | 'Contribution' => 'civicrm_contribution', | |
120 | 'Membership' => 'civicrm_membership', | |
121 | 'Participant' => 'civicrm_participant', | |
122 | 'Group' => 'civicrm_group', | |
123 | 'Relationship' => 'civicrm_relationship', | |
124 | 'Event' => 'civicrm_event', | |
125 | 'Case' => 'civicrm_case', | |
126 | 'Activity' => 'civicrm_activity', | |
127 | 'Pledge' => 'civicrm_pledge', | |
128 | 'Grant' => 'civicrm_grant', | |
129 | 'Address' => 'civicrm_address', | |
130 | ); | |
131 | ||
132 | /** | |
133 | * class constructor | |
134 | * | |
135 | * Takes in a set of custom field ids andsets up the data structures to | |
136 | * generate a query | |
137 | * | |
fd31fa4c EM |
138 | * @param array $ids the set of custom field ids |
139 | * | |
140 | * @param bool $contactSearch | |
141 | * @param array $locationSpecificFields | |
6a488035 TO |
142 | * |
143 | * @access public | |
442df34b CW |
144 | */ |
145 | function __construct($ids, $contactSearch = FALSE, $locationSpecificFields = array()) { | |
6a488035 | 146 | $this->_ids = &$ids; |
247ad911 | 147 | $this->_locationSpecificCustomFields = $locationSpecificFields; |
6a488035 TO |
148 | |
149 | $this->_select = array(); | |
150 | $this->_element = array(); | |
151 | $this->_tables = array(); | |
152 | $this->_whereTables = array(); | |
153 | $this->_where = array(); | |
154 | $this->_qill = array(); | |
155 | $this->_options = array(); | |
156 | ||
157 | $this->_fields = array(); | |
158 | $this->_contactSearch = $contactSearch; | |
159 | ||
160 | if (empty($this->_ids)) { | |
161 | return; | |
162 | } | |
163 | ||
164 | // initialize the field array | |
165 | $tmpArray = array_keys($this->_ids); | |
166 | $idString = implode(',', $tmpArray); | |
167 | $query = " | |
168 | SELECT f.id, f.label, f.data_type, | |
169 | f.html_type, f.is_search_range, | |
170 | f.option_group_id, f.custom_group_id, | |
171 | f.column_name, g.table_name, | |
172 | f.date_format,f.time_format | |
173 | FROM civicrm_custom_field f, | |
174 | civicrm_custom_group g | |
175 | WHERE f.custom_group_id = g.id | |
176 | AND g.is_active = 1 | |
177 | AND f.is_active = 1 | |
178 | AND f.id IN ( $idString )"; | |
179 | ||
180 | $dao = CRM_Core_DAO::executeQuery($query); | |
181 | while ($dao->fetch()) { | |
182 | // get the group dao to figure which class this custom field extends | |
183 | $extends = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomGroup', $dao->custom_group_id, 'extends'); | |
184 | if (array_key_exists($extends, self::$extendsMap)) { | |
185 | $extendsTable = self::$extendsMap[$extends]; | |
186 | } | |
187 | elseif (in_array($extends, CRM_Contact_BAO_ContactType::subTypes())) { | |
188 | // if $extends is a subtype, refer contact table | |
189 | $extendsTable = self::$extendsMap['Contact']; | |
190 | } | |
191 | $this->_fields[$dao->id] = array( | |
192 | 'id' => $dao->id, | |
193 | 'label' => $dao->label, | |
194 | 'extends' => $extendsTable, | |
195 | 'data_type' => $dao->data_type, | |
196 | 'html_type' => $dao->html_type, | |
197 | 'is_search_range' => $dao->is_search_range, | |
198 | 'column_name' => $dao->column_name, | |
199 | 'table_name' => $dao->table_name, | |
200 | 'option_group_id' => $dao->option_group_id, | |
201 | ); | |
202 | ||
203 | // store it in the options cache to make things easier | |
204 | // during option lookup | |
205 | $this->_options[$dao->id] = array(); | |
206 | $this->_options[$dao->id]['attributes'] = array( | |
207 | 'label' => $dao->label, | |
208 | 'data_type' => $dao->data_type, | |
209 | 'html_type' => $dao->html_type, | |
210 | ); | |
211 | ||
212 | $optionGroupID = NULL; | |
213 | $htmlTypes = array('CheckBox', 'Radio', 'Select', 'Multi-Select', 'AdvMulti-Select', 'Autocomplete-Select'); | |
214 | if (in_array($dao->html_type, $htmlTypes) && $dao->data_type != 'ContactReference') { | |
215 | if ($dao->option_group_id) { | |
216 | $optionGroupID = $dao->option_group_id; | |
217 | } | |
218 | elseif ($dao->data_type != 'Boolean') { | |
219 | $errorMessage = ts("The custom field %1 is corrupt. Please delete and re-build the field", | |
220 | array(1 => $dao->label) | |
221 | ); | |
222 | CRM_Core_Error::fatal($errorMessage); | |
223 | } | |
224 | } | |
225 | elseif ($dao->html_type == 'Select Date') { | |
226 | $this->_options[$dao->id]['attributes']['date_format'] = $dao->date_format; | |
227 | $this->_options[$dao->id]['attributes']['time_format'] = $dao->time_format; | |
228 | } | |
229 | ||
230 | // build the cache for custom values with options (label => value) | |
231 | if ($optionGroupID != NULL) { | |
232 | $query = " | |
233 | SELECT label, value | |
234 | FROM civicrm_option_value | |
235 | WHERE option_group_id = $optionGroupID | |
236 | "; | |
237 | ||
238 | $option = CRM_Core_DAO::executeQuery($query); | |
239 | while ($option->fetch()) { | |
240 | $dataType = $this->_fields[$dao->id]['data_type']; | |
241 | if ($dataType == 'Int' || $dataType == 'Float') { | |
242 | $num = round($option->value, 2); | |
243 | $this->_options[$dao->id]["$num"] = $option->label; | |
244 | } | |
245 | else { | |
246 | $this->_options[$dao->id][$option->value] = $option->label; | |
247 | } | |
248 | } | |
249 | $options = $this->_options[$dao->id]; | |
250 | //unset attributes to avoid confussion | |
251 | unset($options['attributes']); | |
252 | CRM_Utils_Hook::customFieldOptions($dao->id, $options, FALSE); | |
253 | } | |
254 | } | |
255 | } | |
256 | ||
257 | /** | |
258 | * generate the select clause and the associated tables | |
259 | * for the from clause | |
260 | * | |
261 | * @param NULL | |
262 | * | |
263 | * @return void | |
264 | * @access public | |
265 | */ | |
266 | function select() { | |
267 | if (empty($this->_fields)) { | |
268 | return; | |
269 | } | |
270 | ||
271 | foreach ($this->_fields as $id => $field) { | |
272 | $name = $field['table_name']; | |
273 | $fieldName = 'custom_' . $field['id']; | |
274 | $this->_select["{$name}_id"] = "{$name}.id as {$name}_id"; | |
275 | $this->_element["{$name}_id"] = 1; | |
276 | $this->_select[$fieldName] = "{$field['table_name']}.{$field['column_name']} as $fieldName"; | |
277 | $this->_element[$fieldName] = 1; | |
278 | $joinTable = NULL; | |
dcf0d348 PJ |
279 | // CRM-14265 |
280 | if ($field['extends'] == 'civicrm_group') { | |
281 | return; | |
282 | } | |
283 | elseif ($field['extends'] == 'civicrm_contact') { | |
6a488035 TO |
284 | $joinTable = 'contact_a'; |
285 | } | |
286 | elseif ($field['extends'] == 'civicrm_contribution') { | |
dcf0d348 | 287 | $joinTable = $field['extends']; |
6a488035 | 288 | } |
dcf0d348 PJ |
289 | elseif (in_array($field['extends'], self::$extendsMap)) { |
290 | $joinTable = $field['extends']; | |
6a488035 | 291 | } |
dcf0d348 PJ |
292 | else { |
293 | return; | |
6a488035 | 294 | } |
dcf0d348 PJ |
295 | |
296 | $this->_tables[$name] = "\nLEFT JOIN $name ON $name.entity_id = $joinTable.id"; | |
297 | ||
298 | if ($this->_ids[$id]) { | |
299 | $this->_whereTables[$name] = $this->_tables[$name]; | |
6a488035 TO |
300 | } |
301 | ||
302 | if ($joinTable) { | |
442df34b CW |
303 | $joinClause = 1; |
304 | $joinTableAlias = $joinTable; | |
305 | // Set location-specific query | |
247ad911 | 306 | if (isset($this->_locationSpecificCustomFields[$id])) { |
307 | list($locationType, $locationTypeId) = $this->_locationSpecificCustomFields[$id]; | |
442df34b CW |
308 | $joinTableAlias = "$locationType-address"; |
309 | $joinClause = "\nLEFT JOIN $joinTable `$locationType-address` ON (`$locationType-address`.contact_id = contact_a.id AND `$locationType-address`.location_type_id = $locationTypeId)"; | |
310 | } | |
311 | $this->_tables[$name] = "\nLEFT JOIN $name ON $name.entity_id = `$joinTableAlias`.id"; | |
6a488035 TO |
312 | if ($this->_ids[$id]) { |
313 | $this->_whereTables[$name] = $this->_tables[$name]; | |
314 | } | |
315 | if ($joinTable != 'contact_a') { | |
442df34b | 316 | $this->_whereTables[$joinTableAlias] = $this->_tables[$joinTableAlias] = $joinClause; |
6a488035 TO |
317 | } |
318 | elseif ($this->_contactSearch) { | |
319 | CRM_Contact_BAO_Query::$_openedPanes[ts('Custom Fields')] = TRUE; | |
320 | } | |
321 | } | |
322 | } | |
323 | } | |
324 | ||
325 | /** | |
326 | * generate the where clause and also the english language | |
327 | * equivalent | |
328 | * | |
329 | * @param NULL | |
330 | * | |
331 | * @return void | |
332 | * | |
333 | * @access public | |
334 | */ | |
335 | function where() { | |
6a488035 TO |
336 | foreach ($this->_ids as $id => $values) { |
337 | ||
338 | // Fixed for Isuue CRM 607 | |
339 | if (CRM_Utils_Array::value($id, $this->_fields) === NULL || | |
340 | !$values | |
341 | ) { | |
342 | continue; | |
343 | } | |
344 | ||
345 | $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower'; | |
346 | ||
347 | foreach ($values as $tuple) { | |
348 | list($name, $op, $value, $grouping, $wildcard) = $tuple; | |
349 | ||
350 | // fix $value here to escape sql injection attacks | |
351 | $field = $this->_fields[$id]; | |
352 | $qillValue = CRM_Core_BAO_CustomField::getDisplayValue($value, $id, $this->_options); | |
353 | ||
354 | if (!is_array($value)) { | |
355 | $value = CRM_Core_DAO::escapeString(trim($value)); | |
356 | } | |
357 | ||
358 | $fieldName = "{$field['table_name']}.{$field['column_name']}"; | |
359 | switch ($field['data_type']) { | |
360 | case 'String': | |
361 | $sql = "$fieldName"; | |
362 | // if we are coming in from listings, | |
363 | // for checkboxes the value is already in the right format and is NOT an array | |
364 | if (is_array($value)) { | |
365 | ||
366 | //ignoring $op value for checkbox and multi select | |
367 | $sqlValue = array(); | |
368 | $sqlOP = ' AND '; | |
369 | $sqlOPlabel = ts('match ALL'); | |
370 | if ($field['html_type'] == 'CheckBox') { | |
371 | foreach ($value as $k => $v) { | |
372 | if ($v) { | |
373 | if ($k == 'CiviCRM_OP_OR') { | |
374 | $sqlOP = ' OR '; | |
375 | $sqlOPlabel = ts('match ANY'); | |
376 | continue; | |
377 | } | |
378 | ||
379 | $sqlValue[] = "( $sql like '%" . CRM_Core_DAO::VALUE_SEPARATOR . $k . CRM_Core_DAO::VALUE_SEPARATOR . "%' ) "; | |
380 | } | |
381 | } | |
382 | //if user check only 'CiviCRM_OP_OR' check box | |
383 | //of custom checkbox field, then ignore this field. | |
384 | if (!empty($sqlValue)) { | |
385 | $this->_where[$grouping][] = ' ( ' . implode($sqlOP, $sqlValue) . ' ) '; | |
386 | $this->_qill[$grouping][] = "{$field['label']} $op $qillValue ( $sqlOPlabel )"; | |
387 | } | |
388 | // for multi select | |
389 | } | |
390 | else { | |
391 | foreach ($value as $k => $v) { | |
392 | if ($v == 'CiviCRM_OP_OR') { | |
393 | $sqlOP = ' OR '; | |
394 | $sqlOPlabel = ts('match ANY'); | |
395 | continue; | |
396 | } | |
397 | $v = CRM_Core_DAO::escapeString($v); | |
398 | $sqlValue[] = "( $sql like '%" . CRM_Core_DAO::VALUE_SEPARATOR . $v . CRM_Core_DAO::VALUE_SEPARATOR . "%' ) "; | |
399 | } | |
400 | //if user select only 'CiviCRM_OP_OR' value | |
401 | //of custom multi select field, then ignore this field. | |
402 | if (!empty($sqlValue)) { | |
403 | $this->_where[$grouping][] = ' ( ' . implode($sqlOP, $sqlValue) . ' ) '; | |
404 | $this->_qill[$grouping][] = "$field[label] $op $qillValue ( $sqlOPlabel )"; | |
405 | } | |
406 | } | |
407 | } | |
408 | else { | |
409 | if ($field['is_search_range'] && is_array($value)) { | |
410 | $this->searchRange($field['id'], | |
411 | $field['label'], | |
412 | $field['data_type'], | |
413 | $fieldName, | |
414 | $value, | |
415 | $grouping | |
416 | ); | |
417 | } | |
418 | else { | |
1b4d9e39 | 419 | if (in_array($field['html_type'], array('Select', 'Radio', 'Autocomplete-Select'))) { |
6a488035 TO |
420 | $wildcard = FALSE; |
421 | $val = CRM_Utils_Type::escape($value, 'String'); | |
422 | } | |
423 | else { | |
424 | $val = CRM_Utils_Type::escape($strtolower(trim($value)), 'String'); | |
425 | } | |
426 | ||
427 | if ($wildcard) { | |
428 | $val = $strtolower(CRM_Core_DAO::escapeString($val)); | |
429 | $val = "%$val%"; | |
430 | $op = 'LIKE'; | |
431 | } | |
432 | ||
433 | //FIX for custom data query fired against no value(NULL/NOT NULL) | |
434 | $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($sql, $op, $val, $field['data_type']); | |
435 | $this->_qill[$grouping][] = "$field[label] $op $qillValue"; | |
436 | } | |
437 | } | |
438 | continue; | |
439 | ||
440 | case 'ContactReference': | |
441 | $label = $value ? CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name') : ''; | |
442 | $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String'); | |
443 | $this->_qill[$grouping][] = $field['label'] . " $op $label"; | |
444 | continue; | |
445 | ||
446 | case 'Int': | |
447 | if ($field['is_search_range'] && is_array($value)) { | |
448 | $this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping); | |
449 | } | |
450 | else { | |
451 | $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer'); | |
452 | $this->_qill[$grouping][] = $field['label'] . " $op $value"; | |
453 | } | |
454 | continue; | |
455 | ||
456 | case 'Boolean': | |
457 | if (strtolower($value) == 'yes' || strtolower($value) == strtolower(ts('Yes'))) { | |
458 | $value = 1; | |
459 | } | |
460 | else { | |
461 | $value = (int) $value; | |
462 | } | |
463 | $value = ($value == 1) ? 1 : 0; | |
464 | $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer'); | |
465 | $value = $value ? ts('Yes') : ts('No'); | |
466 | $this->_qill[$grouping][] = $field['label'] . " {$op} {$value}"; | |
467 | continue; | |
468 | ||
469 | case 'Link': | |
470 | $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String'); | |
471 | $this->_qill[$grouping][] = $field['label'] . " $op $value"; | |
472 | continue; | |
473 | ||
474 | case 'Float': | |
475 | if ($field['is_search_range'] && is_array($value)) { | |
476 | $this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping); | |
477 | } | |
478 | else { | |
479 | $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Float'); | |
480 | $this->_qill[$grouping][] = $field['label'] . " {$op} {$value}"; | |
481 | } | |
482 | continue; | |
483 | ||
484 | case 'Money': | |
485 | if ($field['is_search_range'] && is_array($value)) { | |
486 | foreach ($value as $key => $val) { | |
487 | $moneyFormat = CRM_Utils_Rule::cleanMoney($value[$key]); | |
488 | $value[$key] = $moneyFormat; | |
489 | } | |
490 | $this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping); | |
491 | } | |
492 | else { | |
493 | $moneyFormat = CRM_Utils_Rule::cleanMoney($value); | |
494 | $value = $moneyFormat; | |
495 | $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Float'); | |
496 | $this->_qill[$grouping][] = $field['label'] . " {$op} {$value}"; | |
497 | } | |
498 | continue; | |
499 | ||
500 | case 'Memo': | |
501 | $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String'); | |
502 | $this->_qill[$grouping][] = "$field[label] $op $value"; | |
503 | continue; | |
504 | ||
505 | case 'Date': | |
506 | $fromValue = CRM_Utils_Array::value('from', $value); | |
507 | $toValue = CRM_Utils_Array::value('to', $value); | |
508 | ||
509 | if (!$fromValue && !$toValue) { | |
510 | if (!CRM_Utils_Date::processDate($value) && $op != 'IS NULL' && $op != 'IS NOT NULL') { | |
511 | continue; | |
512 | } | |
513 | ||
514 | // hack to handle yy format during search | |
515 | if (is_numeric($value) && strlen($value) == 4) { | |
516 | $value = "01-01-{$value}"; | |
517 | } | |
518 | ||
519 | $date = CRM_Utils_Date::processDate($value); | |
520 | $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $date, 'String'); | |
521 | $this->_qill[$grouping][] = $field['label'] . " {$op} " . CRM_Utils_Date::customFormat($date); | |
522 | } | |
523 | else { | |
524 | if (is_numeric($fromValue) && strlen($fromValue) == 4) { | |
525 | $fromValue = "01-01-{$fromValue}"; | |
526 | } | |
527 | ||
528 | if (is_numeric($toValue) && strlen($toValue) == 4) { | |
529 | $toValue = "01-01-{$toValue}"; | |
530 | } | |
531 | ||
532 | // TO DO: add / remove time based on date parts | |
533 | $fromDate = CRM_Utils_Date::processDate($fromValue); | |
534 | $toDate = CRM_Utils_Date::processDate($toValue); | |
535 | if (!$fromDate && !$toDate) { | |
536 | continue; | |
537 | } | |
538 | if ($fromDate) { | |
539 | $this->_where[$grouping][] = "$fieldName >= $fromDate"; | |
540 | $this->_qill[$grouping][] = $field['label'] . ' >= ' . CRM_Utils_Date::customFormat($fromDate); | |
541 | } | |
542 | if ($toDate) { | |
543 | $this->_where[$grouping][] = "$fieldName <= $toDate"; | |
544 | $this->_qill[$grouping][] = $field['label'] . ' <= ' . CRM_Utils_Date::customFormat($toDate); | |
545 | } | |
546 | } | |
547 | continue; | |
548 | ||
549 | case 'StateProvince': | |
550 | case 'Country': | |
551 | if (!is_array($value)) { | |
552 | $this->_where[$grouping][] = "$fieldName {$op} " . CRM_Utils_Type::escape($value, 'Int'); | |
553 | $this->_qill[$grouping][] = $field['label'] . " {$op} {$qillValue}"; | |
554 | } | |
555 | else { | |
556 | $sqlOP = ' AND '; | |
557 | $sqlOPlabel = ts('match ALL'); | |
558 | foreach ($value as $k => $v) { | |
559 | if ($v == 'CiviCRM_OP_OR') { | |
560 | $sqlOP = ' OR '; | |
561 | $sqlOPlabel = ts('match ANY'); | |
562 | continue; | |
563 | } | |
564 | $sqlValue[] = "( $fieldName like '%" . CRM_Core_DAO::VALUE_SEPARATOR . $v . CRM_Core_DAO::VALUE_SEPARATOR . "%' ) "; | |
565 | } | |
566 | ||
567 | //if user select only 'CiviCRM_OP_OR' value | |
568 | //of custom multi select field, then ignore this field. | |
569 | if (!empty($sqlValue)) { | |
570 | $this->_where[$grouping][] = " ( " . implode($sqlOP, $sqlValue) . " ) "; | |
571 | $this->_qill[$grouping][] = "$field[label] $op $qillValue ( $sqlOPlabel )"; | |
572 | } | |
573 | } | |
574 | continue; | |
575 | ||
576 | case 'File': | |
577 | if ( $op == 'IS NULL' || $op == 'IS NOT NULL' || $op == 'IS EMPTY' || $op == 'IS NOT EMPTY' ) { | |
578 | switch ($op) { | |
579 | case 'IS EMPTY': | |
580 | $op = 'IS NULL'; | |
581 | break; | |
582 | case 'IS NOT EMPTY': | |
583 | $op = 'IS NOT NULL'; | |
584 | break; | |
585 | } | |
586 | $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op); | |
587 | $this->_qill[$grouping][] = $field['label'] . " {$op} "; | |
588 | } | |
589 | continue; | |
590 | } | |
591 | } | |
592 | } | |
593 | } | |
594 | ||
595 | /** | |
596 | * function that does the actual query generation | |
597 | * basically ties all the above functions together | |
598 | * | |
599 | * @param NULL | |
600 | * | |
601 | * @return array array of strings | |
602 | * @access public | |
603 | */ | |
604 | function query() { | |
605 | $this->select(); | |
606 | ||
607 | $this->where(); | |
608 | ||
609 | $whereStr = NULL; | |
610 | if (!empty($this->_where)) { | |
611 | $clauses = array(); | |
612 | foreach ($this->_where as $grouping => $values) { | |
613 | if (!empty($values)) { | |
614 | $clauses[] = ' ( ' . implode(' AND ', $values) . ' ) '; | |
615 | } | |
616 | } | |
617 | if (!empty($clauses)) { | |
618 | $whereStr = ' ( ' . implode(' OR ', $clauses) . ' ) '; | |
619 | } | |
620 | } | |
621 | ||
622 | return array(implode(' , ', $this->_select), | |
623 | implode(' ', $this->_tables), | |
624 | $whereStr, | |
625 | ); | |
626 | } | |
627 | ||
b5c2afd0 EM |
628 | /** |
629 | * @param $id | |
630 | * @param $label | |
631 | * @param $type | |
632 | * @param $fieldName | |
633 | * @param $value | |
634 | * @param $grouping | |
635 | */ | |
6a488035 TO |
636 | function searchRange(&$id, &$label, $type, $fieldName, &$value, &$grouping) { |
637 | $qill = array(); | |
638 | ||
639 | if (isset($value['from'])) { | |
640 | $val = CRM_Utils_Type::escape($value['from'], $type); | |
641 | ||
642 | if ($type == 'String') { | |
643 | $this->_where[$grouping][] = "$fieldName >= '$val'"; | |
644 | } | |
645 | else { | |
646 | $this->_where[$grouping][] = "$fieldName >= $val"; | |
647 | } | |
648 | $qill[] = ts('greater than or equal to \'%1\'', array(1 => $value['from'])); | |
649 | } | |
650 | ||
651 | if (isset($value['to'])) { | |
652 | $val = CRM_Utils_Type::escape($value['to'], $type); | |
653 | if ($type == 'String') { | |
654 | $this->_where[$grouping][] = "$fieldName <= '$val'"; | |
655 | } | |
656 | else { | |
657 | $this->_where[$grouping][] = "$fieldName <= $val"; | |
658 | } | |
659 | $qill[] = ts('less than or equal to \'%1\'', array(1 => $value['to'])); | |
660 | } | |
661 | ||
662 | if (!empty($qill)) { | |
663 | $this->_qill[$grouping][] = $label . ' - ' . implode(' ' . ts('and') . ' ', $qill); | |
664 | } | |
665 | } | |
666 | } |