CRM/Core/BAO/CustomQuery.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.5                                                |
   6  +--------------------------------------------------------------------+
   7  | Copyright CiviCRM LLC (c) 2004-2014                                |
   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-2014
  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    * @todo add comments explaining why survey & campaign are missing from this
 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    *
 138    * @param  array $ids the set of custom field ids
 139    *
 140    * @param bool $contactSearch
 141    * @param array $locationSpecificFields
 142    *
 143    * @access public
 144    */
 145   function __construct($ids, $contactSearch = FALSE, $locationSpecificFields = array()) {
 146     $this->_ids = &$ids;
 147     $this->_locationSpecificCustomFields = $locationSpecificFields;
 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;
 279       // CRM-14265
 280       if ($field['extends'] == 'civicrm_group') {
 281         return;
 282       }
 283       elseif ($field['extends'] == 'civicrm_contact') {
 284         $joinTable = 'contact_a';
 285       }
 286       elseif ($field['extends'] == 'civicrm_contribution') {
 287         $joinTable = $field['extends'];
 288       }
 289       elseif (in_array($field['extends'], self::$extendsMap)) {
 290         $joinTable = $field['extends'];
 291       }
 292       else {
 293         return;
 294       }
 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];
 300       }
 301
 302       if ($joinTable) {
 303         $joinClause = 1;
 304         $joinTableAlias = $joinTable;
 305         // Set location-specific query
 306         if (isset($this->_locationSpecificCustomFields[$id])) {
 307           list($locationType, $locationTypeId) = $this->_locationSpecificCustomFields[$id];
 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";
 312         if ($this->_ids[$id]) {
 313           $this->_whereTables[$name] = $this->_tables[$name];
 314         }
 315         if ($joinTable != 'contact_a') {
 316           $this->_whereTables[$joinTableAlias] = $this->_tables[$joinTableAlias] = $joinClause;
 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() {
 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 {
 419                 if (in_array($field['html_type'], array('Select', 'Radio', 'Autocomplete-Select'))) {
 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
 628   /**
 629    * @param $id
 630    * @param $label
 631    * @param $type
 632    * @param $fieldName
 633    * @param $value
 634    * @param $grouping
 635    */
 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 }