CRM/Core/BAO/CustomQuery.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.4                                                |
   6  +--------------------------------------------------------------------+
   7  | Copyright CiviCRM LLC (c) 2004-2013                                |
   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
  33  * @copyright CiviCRM LLC (c) 2004-2013
  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
 105   protected $_locationSpecificCustomFields;
 106
 107   /**
 108    * This stores custom data group types and tables that it extends
 109    *
 110    * @var array
 111    * @static
 112    */
 113   static $extendsMap = array(
 114     'Contact' => 'civicrm_contact',
 115     'Individual' => 'civicrm_contact',
 116     'Household' => 'civicrm_contact',
 117     'Organization' => 'civicrm_contact',
 118     'Contribution' => 'civicrm_contribution',
 119     'Membership' => 'civicrm_membership',
 120     'Participant' => 'civicrm_participant',
 121     'Group' => 'civicrm_group',
 122     'Relationship' => 'civicrm_relationship',
 123     'Event' => 'civicrm_event',
 124     'Case' => 'civicrm_case',
 125     'Activity' => 'civicrm_activity',
 126     'Pledge' => 'civicrm_pledge',
 127     'Grant' => 'civicrm_grant',
 128     'Address' => 'civicrm_address',
 129   );
 130
 131   /**
 132    * class constructor
 133    *
 134    * Takes in a set of custom field ids andsets up the data structures to
 135    * generate a query
 136    *
 137    * @param  array  $ids     the set of custom field ids
 138    *
 139    * @access public
 140    */
 141   function __construct($ids, $contactSearch = FALSE, $locationSpecificFields = array()) {
 142     $this->_ids = &$ids;
 143     $this->_locationSpecificCustomFields = $locationSpecificFields;
 144
 145     $this->_select      = array();
 146     $this->_element     = array();
 147     $this->_tables      = array();
 148     $this->_whereTables = array();
 149     $this->_where       = array();
 150     $this->_qill        = array();
 151     $this->_options     = array();
 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);
 163     $query    = "
 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   /**
 254    * generate the select clause and the associated tables
 255    * for the from clause
 256    *
 257    * @param  NULL
 258    *
 259    * @return void
 260    * @access public
 261    */
 262   function select() {
 263     if (empty($this->_fields)) {
 264       return;
 265     }
 266
 267     foreach ($this->_fields as $id => $field) {
 268       $name = $field['table_name'];
 269       $fieldName = 'custom_' . $field['id'];
 270       $this->_select["{$name}_id"] = "{$name}.id as {$name}_id";
 271       $this->_element["{$name}_id"] = 1;
 272       $this->_select[$fieldName] = "{$field['table_name']}.{$field['column_name']} as $fieldName";
 273       $this->_element[$fieldName] = 1;
 274       $joinTable = NULL;
 275       if ($field['extends'] == 'civicrm_contact') {
 276         $joinTable = 'contact_a';
 277       }
 278       elseif ($field['extends'] == 'civicrm_contribution') {
 279         $joinTable = 'civicrm_contribution';
 280       }
 281       elseif ($field['extends'] == 'civicrm_participant') {
 282         $joinTable = 'civicrm_participant';
 283       }
 284       elseif ($field['extends'] == 'civicrm_membership') {
 285         $joinTable = 'civicrm_membership';
 286       }
 287       elseif ($field['extends'] == 'civicrm_pledge') {
 288         $joinTable = 'civicrm_pledge';
 289       }
 290       elseif ($field['extends'] == 'civicrm_activity') {
 291         $joinTable = 'civicrm_activity';
 292       }
 293       elseif ($field['extends'] == 'civicrm_relationship') {
 294         $joinTable = 'civicrm_relationship';
 295       }
 296       elseif ($field['extends'] == 'civicrm_grant') {
 297         $joinTable = 'civicrm_grant';
 298       }
 299       elseif ($field['extends'] == 'civicrm_address') {
 300         $joinTable = 'civicrm_address';
 301       }
 302       elseif ($field['extends'] == 'civicrm_case') {
 303         $joinTable = 'civicrm_case';
 304       }
 305
 306       if ($joinTable) {
 307         $joinClause = 1;
 308         $joinTableAlias = $joinTable;
 309         // Set location-specific query
 310         if (isset($this->_locationSpecificCustomFields[$id])) {
 311           list($locationType, $locationTypeId) = $this->_locationSpecificCustomFields[$id];
 312           $joinTableAlias = "$locationType-address";
 313           $joinClause = "\nLEFT JOIN $joinTable `$locationType-address` ON (`$locationType-address`.contact_id = contact_a.id AND `$locationType-address`.location_type_id = $locationTypeId)";
 314         }
 315         $this->_tables[$name] = "\nLEFT JOIN $name ON $name.entity_id = `$joinTableAlias`.id";
 316         if ($this->_ids[$id]) {
 317           $this->_whereTables[$name] = $this->_tables[$name];
 318         }
 319         if ($joinTable != 'contact_a') {
 320           $this->_whereTables[$joinTableAlias] = $this->_tables[$joinTableAlias] = $joinClause;
 321         }
 322         elseif ($this->_contactSearch) {
 323           CRM_Contact_BAO_Query::$_openedPanes[ts('Custom Fields')] = TRUE;
 324         }
 325       }
 326     }
 327   }
 328
 329   /**
 330    * generate the where clause and also the english language
 331    * equivalent
 332    *
 333    * @param NULL
 334    *
 335    * @return void
 336    *
 337    * @access public
 338    */
 339   function where() {
 340     foreach ($this->_ids as $id => $values) {
 341
 342       // Fixed for Isuue CRM 607
 343       if (CRM_Utils_Array::value($id, $this->_fields) === NULL ||
 344         !$values
 345       ) {
 346         continue;
 347       }
 348
 349       $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
 350
 351       foreach ($values as $tuple) {
 352         list($name, $op, $value, $grouping, $wildcard) = $tuple;
 353
 354         // fix $value here to escape sql injection attacks
 355         $field = $this->_fields[$id];
 356         $qillValue = CRM_Core_BAO_CustomField::getDisplayValue($value, $id, $this->_options);
 357
 358         if (!is_array($value)) {
 359           $value = CRM_Core_DAO::escapeString(trim($value));
 360         }
 361
 362         $fieldName = "{$field['table_name']}.{$field['column_name']}";
 363         switch ($field['data_type']) {
 364           case 'String':
 365             $sql = "$fieldName";
 366             // if we are coming in from listings,
 367             // for checkboxes the value is already in the right format and is NOT an array
 368             if (is_array($value)) {
 369
 370               //ignoring $op value for checkbox and multi select
 371               $sqlValue   = array();
 372               $sqlOP      = ' AND ';
 373               $sqlOPlabel = ts('match ALL');
 374               if ($field['html_type'] == 'CheckBox') {
 375                 foreach ($value as $k => $v) {
 376                   if ($v) {
 377                     if ($k == 'CiviCRM_OP_OR') {
 378                       $sqlOP = ' OR ';
 379                       $sqlOPlabel = ts('match ANY');
 380                       continue;
 381                     }
 382
 383                     $sqlValue[] = "( $sql like '%" . CRM_Core_DAO::VALUE_SEPARATOR . $k . CRM_Core_DAO::VALUE_SEPARATOR . "%' ) ";
 384                   }
 385                 }
 386                 //if user check only 'CiviCRM_OP_OR' check box
 387                 //of custom checkbox field, then ignore this field.
 388                 if (!empty($sqlValue)) {
 389                   $this->_where[$grouping][] = ' ( ' . implode($sqlOP, $sqlValue) . ' ) ';
 390                   $this->_qill[$grouping][] = "{$field['label']} $op $qillValue ( $sqlOPlabel )";
 391                 }
 392                 // for multi select
 393               }
 394               else {
 395                 foreach ($value as $k => $v) {
 396                   if ($v == 'CiviCRM_OP_OR') {
 397                     $sqlOP = ' OR ';
 398                     $sqlOPlabel = ts('match ANY');
 399                     continue;
 400                   }
 401                   $v = CRM_Core_DAO::escapeString($v);
 402                   $sqlValue[] = "( $sql like '%" . CRM_Core_DAO::VALUE_SEPARATOR . $v . CRM_Core_DAO::VALUE_SEPARATOR . "%' ) ";
 403                 }
 404                 //if user select only 'CiviCRM_OP_OR' value
 405                 //of custom multi select field, then ignore this field.
 406                 if (!empty($sqlValue)) {
 407                   $this->_where[$grouping][] = ' ( ' . implode($sqlOP, $sqlValue) . ' ) ';
 408                   $this->_qill[$grouping][] = "$field[label] $op $qillValue ( $sqlOPlabel )";
 409                 }
 410               }
 411             }
 412             else {
 413               if ($field['is_search_range'] && is_array($value)) {
 414                 $this->searchRange($field['id'],
 415                   $field['label'],
 416                   $field['data_type'],
 417                   $fieldName,
 418                   $value,
 419                   $grouping
 420                 );
 421               }
 422               else {
 423                 if ($field['html_type'] == 'Autocomplete-Select') {
 424                   $wildcard = FALSE;
 425                   $val = array_search($value, $this->_options[$field['id']]);
 426                 }
 427                 elseif (in_array($field['html_type'], array(
 428                   'Select', 'Radio'))) {
 429                   $wildcard = FALSE;
 430                   $val = CRM_Utils_Type::escape($value, 'String');
 431                 }
 432                 else {
 433                   $val = CRM_Utils_Type::escape($strtolower(trim($value)), 'String');
 434                 }
 435
 436                 if ($wildcard) {
 437                   $val = $strtolower(CRM_Core_DAO::escapeString($val));
 438                   $val = "%$val%";
 439                   $op  = 'LIKE';
 440                 }
 441
 442                 //FIX for custom data query fired against no value(NULL/NOT NULL)
 443                 $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($sql, $op, $val, $field['data_type']);
 444                 $this->_qill[$grouping][] = "$field[label] $op $qillValue";
 445               }
 446             }
 447             continue;
 448
 449           case 'ContactReference':
 450             $label = $value ? CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name') : '';
 451             $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
 452             $this->_qill[$grouping][] = $field['label'] . " $op $label";
 453             continue;
 454
 455           case 'Int':
 456             if ($field['is_search_range'] && is_array($value)) {
 457               $this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping);
 458             }
 459             else {
 460               $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer');
 461               $this->_qill[$grouping][] = $field['label'] . " $op $value";
 462             }
 463             continue;
 464
 465           case 'Boolean':
 466             if (strtolower($value) == 'yes' || strtolower($value) == strtolower(ts('Yes'))) {
 467               $value = 1;
 468             }
 469             else {
 470               $value = (int) $value;
 471             }
 472             $value = ($value == 1) ? 1 : 0;
 473             $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer');
 474             $value = $value ? ts('Yes') : ts('No');
 475             $this->_qill[$grouping][] = $field['label'] . " {$op} {$value}";
 476             continue;
 477
 478           case 'Link':
 479             $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
 480             $this->_qill[$grouping][] = $field['label'] . " $op $value";
 481             continue;
 482
 483           case 'Float':
 484             if ($field['is_search_range'] && is_array($value)) {
 485               $this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping);
 486             }
 487             else {
 488               $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Float');
 489               $this->_qill[$grouping][] = $field['label'] . " {$op} {$value}";
 490             }
 491             continue;
 492
 493           case 'Money':
 494             if ($field['is_search_range'] && is_array($value)) {
 495               foreach ($value as $key => $val) {
 496                 $moneyFormat = CRM_Utils_Rule::cleanMoney($value[$key]);
 497                 $value[$key] = $moneyFormat;
 498               }
 499               $this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping);
 500             }
 501             else {
 502               $moneyFormat = CRM_Utils_Rule::cleanMoney($value);
 503               $value = $moneyFormat;
 504               $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Float');
 505               $this->_qill[$grouping][] = $field['label'] . " {$op} {$value}";
 506             }
 507             continue;
 508
 509           case 'Memo':
 510             $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
 511             $this->_qill[$grouping][] = "$field[label] $op $value";
 512             continue;
 513
 514           case 'Date':
 515             $fromValue = CRM_Utils_Array::value('from', $value);
 516             $toValue = CRM_Utils_Array::value('to', $value);
 517
 518             if (!$fromValue && !$toValue) {
 519               if (!CRM_Utils_Date::processDate($value) && $op != 'IS NULL' && $op != 'IS NOT NULL') {
 520                 continue;
 521               }
 522
 523               // hack to handle yy format during search
 524               if (is_numeric($value) && strlen($value) == 4) {
 525                 $value = "01-01-{$value}";
 526               }
 527
 528               $date = CRM_Utils_Date::processDate($value);
 529               $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $date, 'String');
 530               $this->_qill[$grouping][] = $field['label'] . " {$op} " . CRM_Utils_Date::customFormat($date);
 531             }
 532             else {
 533               if (is_numeric($fromValue) && strlen($fromValue) == 4) {
 534                 $fromValue = "01-01-{$fromValue}";
 535               }
 536
 537               if (is_numeric($toValue) && strlen($toValue) == 4) {
 538                 $toValue = "01-01-{$toValue}";
 539               }
 540
 541               // TO DO: add / remove time based on date parts
 542               $fromDate = CRM_Utils_Date::processDate($fromValue);
 543               $toDate = CRM_Utils_Date::processDate($toValue);
 544               if (!$fromDate && !$toDate) {
 545                 continue;
 546               }
 547               if ($fromDate) {
 548                 $this->_where[$grouping][] = "$fieldName >= $fromDate";
 549                 $this->_qill[$grouping][] = $field['label'] . ' >= ' . CRM_Utils_Date::customFormat($fromDate);
 550               }
 551               if ($toDate) {
 552                 $this->_where[$grouping][] = "$fieldName <= $toDate";
 553                 $this->_qill[$grouping][] = $field['label'] . ' <= ' . CRM_Utils_Date::customFormat($toDate);
 554               }
 555             }
 556             continue;
 557
 558           case 'StateProvince':
 559           case 'Country':
 560             if (!is_array($value)) {
 561               $this->_where[$grouping][] = "$fieldName {$op} " . CRM_Utils_Type::escape($value, 'Int');
 562               $this->_qill[$grouping][] = $field['label'] . " {$op} {$qillValue}";
 563             }
 564             else {
 565               $sqlOP = ' AND ';
 566               $sqlOPlabel = ts('match ALL');
 567               foreach ($value as $k => $v) {
 568                 if ($v == 'CiviCRM_OP_OR') {
 569                   $sqlOP = ' OR ';
 570                   $sqlOPlabel = ts('match ANY');
 571                   continue;
 572                 }
 573                 $sqlValue[] = "( $fieldName like '%" . CRM_Core_DAO::VALUE_SEPARATOR . $v . CRM_Core_DAO::VALUE_SEPARATOR . "%' ) ";
 574               }
 575
 576               //if user select only 'CiviCRM_OP_OR' value
 577               //of custom multi select field, then ignore this field.
 578               if (!empty($sqlValue)) {
 579                 $this->_where[$grouping][] = " ( " . implode($sqlOP, $sqlValue) . " ) ";
 580                 $this->_qill[$grouping][] = "$field[label] $op $qillValue ( $sqlOPlabel )";
 581               }
 582             }
 583             continue;
 584
 585           case 'File':
 586             if ( $op == 'IS NULL' || $op == 'IS NOT NULL' || $op == 'IS EMPTY' || $op == 'IS NOT EMPTY' ) {
 587               switch ($op) {
 588                 case 'IS EMPTY':
 589                   $op = 'IS NULL';
 590                   break;
 591                 case 'IS NOT EMPTY':
 592                   $op = 'IS NOT NULL';
 593                   break;
 594               }
 595               $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op);
 596               $this->_qill[$grouping][] = $field['label'] . " {$op} ";
 597             }
 598             continue;
 599         }
 600       }
 601     }
 602   }
 603
 604   /**
 605    * function that does the actual query generation
 606    * basically ties all the above functions together
 607    *
 608    * @param NULL
 609    *
 610    * @return  array   array of strings
 611    * @access public
 612    */
 613   function query() {
 614     $this->select();
 615
 616     $this->where();
 617
 618     $whereStr = NULL;
 619     if (!empty($this->_where)) {
 620       $clauses = array();
 621       foreach ($this->_where as $grouping => $values) {
 622         if (!empty($values)) {
 623           $clauses[] = ' ( ' . implode(' AND ', $values) . ' ) ';
 624         }
 625       }
 626       if (!empty($clauses)) {
 627         $whereStr = ' ( ' . implode(' OR ', $clauses) . ' ) ';
 628       }
 629     }
 630
 631     return array(implode(' , ', $this->_select),
 632       implode(' ', $this->_tables),
 633       $whereStr,
 634     );
 635   }
 636
 637   function searchRange(&$id, &$label, $type, $fieldName, &$value, &$grouping) {
 638     $qill = array();
 639
 640     if (isset($value['from'])) {
 641       $val = CRM_Utils_Type::escape($value['from'], $type);
 642
 643       if ($type == 'String') {
 644         $this->_where[$grouping][] = "$fieldName >= '$val'";
 645       }
 646       else {
 647         $this->_where[$grouping][] = "$fieldName >= $val";
 648       }
 649       $qill[] = ts('greater than or equal to \'%1\'', array(1 => $value['from']));
 650     }
 651
 652     if (isset($value['to'])) {
 653       $val = CRM_Utils_Type::escape($value['to'], $type);
 654       if ($type == 'String') {
 655         $this->_where[$grouping][] = "$fieldName <= '$val'";
 656       }
 657       else {
 658         $this->_where[$grouping][] = "$fieldName <= $val";
 659       }
 660       $qill[] = ts('less than or equal to \'%1\'', array(1 => $value['to']));
 661     }
 662
 663     if (!empty($qill)) {
 664       $this->_qill[$grouping][] = $label . ' - ' . implode(' ' . ts('and') . ' ', $qill);
 665     }
 666   }
 667 }