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