PHP CRM_Core_SelectValues::getSearchBuilderOperators方法代码示例

 /**
  * Build qill for field.
  *
  * Qill refers to the query detail visible on the UI.
  *
  * @param string $daoName
  * @param string $fieldName
  * @param mixed $fieldValue
  * @param string $op
  * @param array $pseudoExtraParam
  * @param int $type
  *   Type of the field per CRM_Utils_Type
  *
  * @return array
  */
 public static function buildQillForFieldValue($daoName, $fieldName, $fieldValue, $op, $pseudoExtraParam = array(), $type = CRM_Utils_Type::T_STRING)
 {
     $qillOperators = CRM_Core_SelectValues::getSearchBuilderOperators();
     // if Operator chosen is NULL/EMPTY then
     if (strpos($op, 'NULL') !== FALSE || strpos($op, 'EMPTY') !== FALSE) {
         return array(CRM_Utils_Array::value($op, $qillOperators, $op), '');
     }
     if ($fieldName == 'activity_type_id') {
         $pseudoOptions = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
     } elseif ($fieldName == 'country_id') {
         $pseduoOptions = CRM_Core_PseudoConstant::country();
     } elseif ($fieldName == 'county_id') {
         $pseduoOptions = CRM_Core_PseudoConstant::county();
     } elseif ($fieldName == 'world_region') {
         $pseduoOptions = CRM_Core_PseudoConstant::worldRegion();
     } elseif ($daoName == 'CRM_Event_DAO_Event' && $fieldName == 'id') {
         $pseudoOptions = CRM_Event_BAO_Event::getEvents(0, $fieldValue, TRUE, TRUE, TRUE);
     } elseif ($fieldName == 'contribution_product_id') {
         $pseudoOptions = CRM_Contribute_PseudoConstant::products();
     } elseif ($daoName == 'CRM_Contact_DAO_Group' && $fieldName == 'id') {
         $pseudoOptions = CRM_Core_PseudoConstant::group();
     } elseif ($fieldName == 'country_id') {
         $pseudoOptions = CRM_Core_PseudoConstant::country();
     } elseif ($daoName) {
         $pseudoOptions = CRM_Core_PseudoConstant::get($daoName, $fieldName, $pseudoExtraParam);
     }
     //API usually have fieldValue format as array(operator => array(values)),
     //so we need to separate operator out of fieldValue param
     if (is_array($fieldValue) && in_array(key($fieldValue), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
         $op = key($fieldValue);
         $fieldValue = $fieldValue[$op];
     }
     if (is_array($fieldValue)) {
         $qillString = array();
         if (!empty($pseudoOptions)) {
             foreach ((array) $fieldValue as $val) {
                 $qillString[] = CRM_Utils_Array::value($val, $pseudoOptions, $val);
             }
             $fieldValue = implode(', ', $qillString);
         } else {
             if ($type == CRM_Utils_Type::T_DATE) {
                 foreach ($fieldValue as $index => $value) {
                     $fieldValue[$index] = CRM_Utils_Date::customFormat($value);
                 }
             }
             $separator = ', ';
             // @todo - this is a bit specific (one operator).
             // However it is covered by a unit test so can be altered later with
             // some confidence.
             if ($op == 'BETWEEN') {
                 $separator = ' AND ';
             }
             $fieldValue = implode($separator, $fieldValue);
         }
     } elseif (!empty($pseudoOptions) && array_key_exists($fieldValue, $pseudoOptions)) {
         $fieldValue = $pseudoOptions[$fieldValue];
     } elseif ($type === CRM_Utils_Type::T_DATE) {
         $fieldValue = CRM_Utils_Date::customFormat($fieldValue);
     }
     return array(CRM_Utils_Array::value($op, $qillOperators, $op), $fieldValue);
 }

 /**
  * Generate the where clause and also the english language.
  * equivalent
  *
  * @return void
  */
 public function where()
 {
     foreach ($this->_ids as $id => $values) {
         // Fixed for Isuue CRM 607
         if (CRM_Utils_Array::value($id, $this->_fields) === NULL || !$values) {
             continue;
         }
         $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
         foreach ($values as $tuple) {
             list($name, $op, $value, $grouping, $wildcard) = $tuple;
             $field = $this->_fields[$id];
             $fieldName = "{$field['table_name']}.{$field['column_name']}";
             // Autocomplete comes back as a string not an array
             if ($field['data_type'] == 'String' && $field['html_type'] == 'Autocomplete-Select' && $op == '=') {
                 $value = explode(',', $value);
             }
             $isSerialized = CRM_Core_BAO_CustomField::isSerialized($field);
             // fix $value here to escape sql injection attacks
             $qillValue = NULL;
             if (!is_array($value)) {
                 $value = CRM_Core_DAO::escapeString(trim($value));
                 $qillValue = CRM_Core_BAO_CustomField::getDisplayValue($value, $id, $this->_options);
             } elseif (count($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
                 $op = key($value);
                 $qillValue = CRM_Core_BAO_CustomField::getDisplayValue($value[$op], $id, $this->_options);
             } else {
                 $op = 'IN';
                 $qillValue = CRM_Core_BAO_CustomField::getDisplayValue($value, $id, $this->_options);
             }
             $qillOp = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op);
             switch ($field['data_type']) {
                 case 'String':
                 case 'StateProvince':
                 case 'Country':
                     if ($field['is_search_range'] && is_array($value)) {
                         $this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping);
                     } else {
                         // fix $value here to escape sql injection attacks
                         if (!is_array($value)) {
                             if ($field['data_type'] == 'String') {
                                 $value = CRM_Utils_Type::escape($strtolower($value), 'String');
                             } else {
                                 $value = CRM_Utils_Type::escape($value, 'Integer');
                             }
                         } elseif ($isSerialized) {
                             if (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
                                 $op = key($value);
                                 $value = $value[$op];
                             }
                             $value = implode(',', $value);
                         }
                         // CRM-14563,CRM-16575 : Special handling of multi-select custom fields
                         if ($isSerialized && !empty($value)) {
                             if (strstr($op, 'IN')) {
                                 $value = str_replace(",", "[[:cntrl:]]*|[[:cntrl:]]*", $value);
                                 $value = str_replace('(', '[[.left-parenthesis.]]', $value);
                                 $value = str_replace(')', '[[.right-parenthesis.]]', $value);
                             }
                             $op = strstr($op, '!') || strstr($op, 'NOT') ? 'NOT RLIKE' : 'RLIKE';
                             $value = "[[:cntrl:]]*" . $value . "[[:cntrl:]]*";
                             if (!$wildcard) {
                                 $value = str_replace("[[:cntrl:]]*|", '', $value);
                             }
                         }
                         //FIX for custom data query fired against no value(NULL/NOT NULL)
                         $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
                         $this->_qill[$grouping][] = "{$field['label']} {$qillOp} {$qillValue}";
                     }
                     break;
                 case 'ContactReference':
                     $label = $value ? CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name') : '';
                     $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
                     $this->_qill[$grouping][] = $field['label'] . " {$qillOp} {$label}";
                     break;
                 case 'Int':
                     if ($field['is_search_range'] && is_array($value)) {
                         $this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping);
                     } else {
                         $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer');
                         $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
                     }
                     break;
                 case 'Boolean':
                     if (!is_array($value)) {
                         if (strtolower($value) == 'yes' || strtolower($value) == strtolower(ts('Yes'))) {
                             $value = 1;
                         } else {
                             $value = (int) $value;
                         }
                         $value = $value == 1 ? 1 : 0;
                         $qillValue = $value ? 'Yes' : 'No';
                     }
                     $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer');
                     $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
//.........这里部分代码省略.........

 /**
  * Build qill for field.
  *
  * Qill refers to the query detail visible on the UI.
  *
  * @param $daoName
  * @param $fieldName
  * @param $fieldValue
  * @param $op
  * @param array $pseduoExtraParam
  *
  * @return array
  */
 public static function buildQillForFieldValue($daoName = NULL, $fieldName, $fieldValue, $op, $pseduoExtraParam = array())
 {
     $qillOperators = CRM_Core_SelectValues::getSearchBuilderOperators();
     if ($fieldName == 'activity_type_id') {
         $pseduoOptions = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
     } elseif ($daoName == 'CRM_Event_DAO_Event' && $fieldName == 'id') {
         $pseduoOptions = CRM_Event_BAO_Event::getEvents(0, $fieldValue, TRUE, TRUE, TRUE);
     } elseif ($fieldName == 'contribution_product_id') {
         $pseduoOptions = CRM_Contribute_PseudoConstant::products();
     } elseif ($daoName) {
         $pseduoOptions = CRM_Core_PseudoConstant::get($daoName, $fieldName, $pseduoExtraParam = array());
     }
     //API usually have fieldValue format as array(operator => array(values)),
     //so we need to separate operator out of fieldValue param
     if (is_array($fieldValue) && in_array(key($fieldValue), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
         $op = key($fieldValue);
         $fieldValue = $fieldValue[$op];
     }
     if (is_array($fieldValue)) {
         $qillString = array();
         if (!empty($pseduoOptions)) {
             foreach ((array) $fieldValue as $val) {
                 $qillString[] = $pseduoOptions[$val];
             }
             $fieldValue = implode(', ', $qillString);
         } else {
             $fieldValue = implode(', ', $fieldValue);
         }
     } elseif (!empty($pseduoOptions) && array_key_exists($fieldValue, $pseduoOptions)) {
         $fieldValue = $pseduoOptions[$fieldValue];
     }
     return array(CRM_Utils_Array::value($op, $qillOperators, $op), $fieldValue);
 }

//.........这里部分代码省略.........
             //Fix for Search Builder
             if ($mappingType == 'Export') {
                 $j = 7;
             } else {
                 $j = 4;
             }
             $formValues = $form->exportValues();
             if (!$jsSet) {
                 if (empty($formValues)) {
                     // Incremented length for third select box(relationship type)
                     for ($k = 1; $k < $j; $k++) {
                         $noneArray[] = array($x, $i, $k);
                     }
                 } else {
                     if (!empty($formValues['mapper'][$x])) {
                         foreach ($formValues['mapper'][$x] as $value) {
                             for ($k = 1; $k < $j; $k++) {
                                 if (!isset($formValues['mapper'][$x][$i][$k]) || !$formValues['mapper'][$x][$i][$k]) {
                                     $noneArray[] = array($x, $i, $k);
                                 } else {
                                     $nullArray[] = array($x, $i, $k);
                                 }
                             }
                         }
                     } else {
                         for ($k = 1; $k < $j; $k++) {
                             $noneArray[] = array($x, $i, $k);
                         }
                     }
                 }
             }
             //Fix for Search Builder
             if ($mappingType == 'Export') {
                 if (!isset($mappingId) || $i >= count(reset($mappingName))) {
                     if (isset($formValues['mapper']) && isset($formValues['mapper'][$x][$i][1]) && array_key_exists($formValues['mapper'][$x][$i][1], $relationshipTypes)) {
                         $sel->setOptions(array($sel1, $sel2, $sel5, $sel6, $sel7, $sel3, $sel4));
                     } else {
                         $sel->setOptions(array($sel1, $sel2, $sel3, $sel4, $sel5, $sel6, $sel7));
                     }
                 } else {
                     $sel->setOptions(array($sel1, $sel2, $sel3, $sel4, $sel5, $sel6, $sel7));
                 }
             } else {
                 $sel->setOptions(array($sel1, $sel2, $sel3, $sel4));
             }
             if ($mappingType == 'Search Builder') {
                 //CRM -2292, restricted array set
                 $operatorArray = array('' => ts('-operator-')) + CRM_Core_SelectValues::getSearchBuilderOperators();
                 $form->add('select', "operator[{$x}][{$i}]", '', $operatorArray);
                 $form->add('text', "value[{$x}][{$i}]", '');
             }
         }
         //end of columnCnt for
         if ($mappingType == 'Search Builder') {
             $title = ts('Another search field');
         } else {
             $title = ts('Select more fields');
         }
         $form->addElement('submit', "addMore[{$x}]", $title, array('class' => 'submit-link'));
     }
     //end of block for
     $js = "<script type='text/javascript'>\n";
     $formName = "document.{$name}";
     if (!empty($nullArray)) {
         $js .= "var nullArray = [";
         $elements = array();
         $seen = array();
         foreach ($nullArray as $element) {
             $key = "{$element[0]}, {$element[1]}, {$element[2]}";
             if (!isset($seen[$key])) {
                 $elements[] = "[{$key}]";
                 $seen[$key] = 1;
             }
         }
         $js .= implode(', ', $elements);
         $js .= "]";
         $js .= "\n                for (var i=0;i<nullArray.length;i++) {\n                    if ( {$formName}['mapper['+nullArray[i][0]+']['+nullArray[i][1]+']['+nullArray[i][2]+']'] ) {\n                        {$formName}['mapper['+nullArray[i][0]+']['+nullArray[i][1]+']['+nullArray[i][2]+']'].style.display = '';\n                    }\n                }\n";
     }
     if (!empty($noneArray)) {
         $js .= "var noneArray = [";
         $elements = array();
         $seen = array();
         foreach ($noneArray as $element) {
             $key = "{$element[0]}, {$element[1]}, {$element[2]}";
             if (!isset($seen[$key])) {
                 $elements[] = "[{$key}]";
                 $seen[$key] = 1;
             }
         }
         $js .= implode(', ', $elements);
         $js .= "]";
         $js .= "\n                for (var i=0;i<noneArray.length;i++) {\n                    if ( {$formName}['mapper['+noneArray[i][0]+']['+noneArray[i][1]+']['+noneArray[i][2]+']'] ) {\n  {$formName}['mapper['+noneArray[i][0]+']['+noneArray[i][1]+']['+noneArray[i][2]+']'].style.display = 'none';\n                    }\n                }\n";
     }
     $js .= "</script>\n";
     $form->assign('initHideBoxes', $js);
     $form->assign('columnCount', $columnCount);
     $form->assign('blockCount', $blockCount);
     $form->setDefaults($defaults);
     $form->setDefaultAction('refresh');
 }

 public function _testCustomValue($customField, $sqlOps, $type)
 {
     $isSerialized = CRM_Core_BAO_CustomField::isSerialized($customField);
     $customId = $customField['id'];
     $params = array('contact_type' => 'Individual', 'email' => substr(sha1(rand()), 0, 7) . 'man1@yahoo.com');
     $result = $this->callAPISuccess('Contact', 'create', $params);
     $contactId = $result['id'];
     $count = rand(1, 2);
     if ($isSerialized) {
         $selectedValue = $this->optionGroup[$type]['values'];
         $notselectedValue = $selectedValue[$count];
         unset($selectedValue[$count]);
     } elseif ($customField['html_type'] == 'Link') {
         $selectedValue = "http://" . substr(sha1(rand()), 0, 7) . ".com";
         $notselectedValue = "http://" . substr(sha1(rand()), 0, 7) . ".com";
     } elseif ($type == 'date') {
         $selectedValue = date('Ymd');
         $notselectedValue = $lesserSelectedValue = date('Ymd', strtotime('yesterday'));
         $greaterSelectedValue = date('Ymd', strtotime('+ 1 day'));
     } elseif ($type == 'contact') {
         $selectedValue = $this->optionGroup[$type]['values'][1];
         $notselectedValue = $this->optionGroup[$type]['values'][0];
     } elseif ($type == 'boolean') {
         $selectedValue = 1;
         $notselectedValue = 0;
     } else {
         $selectedValue = $this->optionGroup[$type]['values'][0];
         $notselectedValue = $this->optionGroup[$type]['values'][$count];
         if (in_array(">", $sqlOps)) {
             $greaterSelectedValue = $selectedValue + 1;
             $lesserSelectedValue = $selectedValue - 1;
         }
     }
     $params = array('entity_id' => $contactId, 'custom_' . $customId => $selectedValue);
     $this->callAPISuccess('CustomValue', 'create', $params);
     foreach ($sqlOps as $op) {
         $qillOp = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op);
         switch ($op) {
             case '=':
                 $result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => is_array($selectedValue) ? implode(CRM_Core_DAO::VALUE_SEPARATOR, $selectedValue) : $selectedValue));
                 $this->assertEquals($contactId, $result['id']);
                 break;
             case '!=':
                 $result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => $notselectedValue)));
                 $this->assertEquals(TRUE, array_key_exists($contactId, $result['values']));
                 break;
             case '>':
             case '<':
             case '>=':
             case '<=':
                 if ($isSerialized) {
                     continue;
                 }
                 // To be precise in for these operator we can't just rely on one contact,
                 // hence creating multiple contact with custom value less/more then $selectedValue respectively
                 $result = $this->callAPISuccess('Contact', 'create', array('contact_type' => 'Individual', 'email' => substr(sha1(rand()), 0, 7) . 'man2@yahoo.com'));
                 $contactId2 = $result['id'];
                 $this->callAPISuccess('CustomValue', 'create', array('entity_id' => $contactId2, 'custom_' . $customId => $lesserSelectedValue));
                 if ($op == '>') {
                     $result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => $lesserSelectedValue)));
                     $this->assertEquals($contactId, $result['id']);
                 } elseif ($op == '<') {
                     $result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => $selectedValue)));
                     $this->assertEquals($contactId2, $result['id']);
                 } else {
                     $result = $this->callAPISuccess('Contact', 'create', array('contact_type' => 'Individual', 'email' => substr(sha1(rand()), 0, 7) . 'man3@yahoo.com'));
                     $contactId3 = $result['id'];
                     $this->callAPISuccess('CustomValue', 'create', array('entity_id' => $contactId3, 'custom_' . $customId => $greaterSelectedValue));
                     $result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => $selectedValue)));
                     $this->assertEquals($contactId, $result['values'][$contactId]['id']);
                     if ($op == '>=') {
                         $this->assertEquals($contactId3, $result['values'][$contactId3]['id']);
                     } else {
                         $this->assertEquals($contactId2, $result['values'][$contactId2]['id']);
                     }
                     $this->callAPISuccess('contact', 'delete', array('id' => $contactId3));
                 }
                 $this->callAPISuccess('contact', 'delete', array('id' => $contactId2));
                 break;
             case 'IN':
                 $result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => (array) $selectedValue)));
                 $this->assertEquals($contactId, $result['id']);
                 break;
             case 'NOT IN':
                 $result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => (array) $notselectedValue)));
                 $this->assertEquals($contactId, $result['id']);
                 break;
             case 'LIKE':
                 $selectedValue = is_array($selectedValue) ? $selectedValue[0] : $selectedValue;
                 $result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => "%{$selectedValue}%")));
                 $this->assertEquals($contactId, $result['id']);
                 break;
             case 'NOT LIKE':
                 $result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => $notselectedValue)));
                 $this->assertEquals($contactId, $result['id']);
                 break;
             case 'IS NULL':
                 $result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => 1)));
                 $this->assertEquals(FALSE, array_key_exists($contactId, $result['values']));
                 break;
//.........这里部分代码省略.........

 /**
  * @param $value
  * @param $grouping
  * @param string $op
  */
 public function includeContactSubTypes($value, $grouping, $op = 'LIKE', $contact)
 {
     $clause = array();
     $alias = "{$contact}.contact_sub_type";
     $qillOperators = array('NOT LIKE' => ts('Not Like')) + CRM_Core_SelectValues::getSearchBuilderOperators();
     $op = str_replace('IN', 'LIKE', $op);
     $op = str_replace('=', 'LIKE', $op);
     $op = str_replace('!', 'NOT ', $op);
     if (strpos($op, 'NULL') !== FALSE || strpos($op, 'EMPTY') !== FALSE) {
         $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($alias, $op, $value, 'String');
     } elseif (is_array($value)) {
         foreach ($value as $k => $v) {
             if (!empty($k)) {
                 $clause[$k] = "({$alias} {$op} '%" . CRM_Core_DAO::VALUE_SEPARATOR . CRM_Utils_Type::escape($k, 'String') . CRM_Core_DAO::VALUE_SEPARATOR . "%')";
             }
         }
     } else {
         $clause[$value] = "({$alias} {$op} '%" . CRM_Core_DAO::VALUE_SEPARATOR . CRM_Utils_Type::escape($value, 'String') . CRM_Core_DAO::VALUE_SEPARATOR . "%')";
     }
     if (!empty($clause)) {
         $this->_where[$grouping][] = "( " . implode(' OR ', $clause) . " )";
     }
     $this->_qill[$grouping][] = ts('Contact Subtype %1 ', array(1 => $qillOperators[$op])) . implode(' ' . ts('or') . ' ', array_keys($clause));
 }

 /**
  * Generate the where clause and also the english language.
  * equivalent
  *
  * @return void
  */
 public function where()
 {
     foreach ($this->_ids as $id => $values) {
         // Fixed for Isuue CRM 607
         if (CRM_Utils_Array::value($id, $this->_fields) === NULL || !$values) {
             continue;
         }
         $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
         foreach ($values as $tuple) {
             list($name, $op, $value, $grouping, $wildcard) = $tuple;
             $field = $this->_fields[$id];
             $fieldName = "{$field['table_name']}.{$field['column_name']}";
             // Autocomplete comes back as a string not an array
             if ($field['data_type'] == 'String' && $field['html_type'] == 'Autocomplete-Select' && $op == '=') {
                 $value = explode(',', $value);
             }
             $isSerialized = CRM_Core_BAO_CustomField::isSerialized($field);
             // Handle multi-select search for any data type
             if (is_array($value) && !$field['is_search_range'] && $field['data_type'] != 'String') {
                 $wildcard = $isSerialized ? $wildcard : TRUE;
                 $options = CRM_Utils_Array::value('values', civicrm_api3('contact', 'getoptions', array('field' => $name, 'context' => 'search'), array()));
                 $qillValue = '';
                 $sqlOP = $wildcard ? ' OR ' : ' AND ';
                 $sqlValue = array();
                 foreach ($value as $num => &$v) {
                     $sep = count($value) > 1 + $num ? ', ' : ' ' . ($wildcard ? ts('OR') : ts('AND')) . ' ';
                     $qillValue .= ($num ? $sep : '') . $options[$v];
                     $v = CRM_Core_DAO::escapeString($v);
                     if ($isSerialized) {
                         $sqlValue[] = "( {$fieldName} like '%" . CRM_Core_DAO::VALUE_SEPARATOR . $v . CRM_Core_DAO::VALUE_SEPARATOR . "%' ) ";
                     } else {
                         $v = "'{$v}'";
                     }
                 }
                 if (!$isSerialized) {
                     $sqlValue = array("{$fieldName} IN (" . implode(',', $value) . ")");
                 }
                 $this->_where[$grouping][] = ' ( ' . implode($sqlOP, $sqlValue) . ' ) ';
                 $this->_qill[$grouping][] = "{$field['label']} {$qillOp} {$qillValue}";
                 continue;
             }
             // fix $value here to escape sql injection attacks
             if (!is_array($value)) {
                 $value = CRM_Core_DAO::escapeString(trim($value));
             }
             $qillValue = CRM_Core_BAO_CustomField::getDisplayValue($value, $id, $this->_options);
             $qillOp = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op);
             switch ($field['data_type']) {
                 case 'String':
                     $sql = "{$fieldName}";
                     if ($field['is_search_range'] && is_array($value)) {
                         $this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping);
                     } else {
                         // fix $value here to escape sql injection attacks
                         if (!is_array($value)) {
                             $value = CRM_Utils_Type::escape($strtolower($value), 'String');
                         } elseif ($isSerialized && strstr($op, 'IN')) {
                             $value = implode(',', $value);
                         }
                         // CRM-14563,CRM-16575 : Special handling of multi-select custom fields
                         if (!empty($value)) {
                             if ($isSerialized) {
                                 if (strstr($op, 'IN')) {
                                     $value = str_replace(array('(', ')'), '', str_replace(",", "[[:cntrl:]]|[[:cntrl:]]", $value));
                                 }
                                 $op = strstr($op, '!') || strstr($op, 'NOT') ? 'NOT RLIKE' : 'RLIKE';
                                 $value = "[[:cntrl:]]" . $value . "[[:cntrl:]]";
                             } elseif ($wildcard) {
                                 $value = "[[:cntrl:]]%{$value}%[[:cntrl:]]";
                                 $op = 'LIKE';
                             }
                         }
                         //FIX for custom data query fired against no value(NULL/NOT NULL)
                         $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($sql, $op, $value, $field['data_type']);
                         $this->_qill[$grouping][] = "{$field['label']} {$qillOp} {$qillValue}";
                     }
                     break;
                 case 'ContactReference':
                     $label = $value ? CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name') : '';
                     $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
                     $this->_qill[$grouping][] = $field['label'] . " {$qillOp} {$label}";
                     break;
                 case 'Int':
                     if ($field['is_search_range'] && is_array($value)) {
                         $this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping);
                     } else {
                         $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer');
                         $this->_qill[$grouping][] = $field['label'] . " {$qillOp} {$value}";
                     }
                     break;
                 case 'Boolean':
                     if (strtolower($value) == 'yes' || strtolower($value) == strtolower(ts('Yes'))) {
                         $value = 1;
                     } else {
//.........这里部分代码省略.........

 /**
  * Generate the where clause and also the english language equivalent.
  */
 public function where()
 {
     foreach ($this->_ids as $id => $values) {
         // Fixed for Issue CRM 607
         if (CRM_Utils_Array::value($id, $this->_fields) === NULL || !$values) {
             continue;
         }
         $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
         foreach ($values as $tuple) {
             list($name, $op, $value, $grouping, $wildcard) = $tuple;
             $field = $this->_fields[$id];
             $fieldName = "{$field['table_name']}.{$field['column_name']}";
             $isSerialized = CRM_Core_BAO_CustomField::isSerialized($field);
             // fix $value here to escape sql injection attacks
             $qillValue = NULL;
             if (!is_array($value)) {
                 $value = CRM_Core_DAO::escapeString(trim($value));
                 $qillValue = CRM_Core_BAO_CustomField::displayValue($value, $id);
             } elseif (count($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
                 $op = key($value);
                 $qillValue = strstr($op, 'NULL') ? NULL : CRM_Core_BAO_CustomField::displayValue($value[$op], $id);
             } else {
                 $op = strstr($op, 'IN') ? $op : 'IN';
                 $qillValue = CRM_Core_BAO_CustomField::displayValue($value, $id);
             }
             $qillOp = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op);
             switch ($field['data_type']) {
                 case 'String':
                 case 'StateProvince':
                 case 'Country':
                     if ($field['is_search_range'] && is_array($value)) {
                         //didn't found any field under any of these three data-types as searchable by range
                     } else {
                         // fix $value here to escape sql injection attacks
                         if (!is_array($value)) {
                             if ($field['data_type'] == 'String') {
                                 $value = CRM_Utils_Type::escape($strtolower($value), 'String');
                             } else {
                                 $value = CRM_Utils_Type::escape($value, 'Integer');
                             }
                         } elseif ($isSerialized) {
                             if (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
                                 $op = key($value);
                                 $value = $value[$op];
                             }
                             $value = implode(',', (array) $value);
                         }
                         // CRM-14563,CRM-16575 : Special handling of multi-select custom fields
                         if ($isSerialized && !empty($value) && !strstr($op, 'NULL') && !strstr($op, 'LIKE')) {
                             $sp = CRM_Core_DAO::VALUE_SEPARATOR;
                             if (strstr($op, 'IN')) {
                                 $value = str_replace(",", "{$sp}|{$sp}", $value);
                                 $value = str_replace('(', '[[.left-parenthesis.]]', $value);
                                 $value = str_replace(')', '[[.right-parenthesis.]]', $value);
                             }
                             $op = strstr($op, '!') || strstr($op, 'NOT') ? 'NOT RLIKE' : 'RLIKE';
                             $value = $sp . $value . $sp;
                             if (!$wildcard) {
                                 foreach (explode("|", $value) as $val) {
                                     $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $val, 'String');
                                 }
                             } else {
                                 $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
                             }
                         } else {
                             //FIX for custom data query fired against no value(NULL/NOT NULL)
                             $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
                         }
                         $this->_qill[$grouping][] = $field['label'] . " {$qillOp} {$qillValue}";
                     }
                     break;
                 case 'ContactReference':
                     $label = $value ? CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name') : '';
                     $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
                     $this->_qill[$grouping][] = $field['label'] . " {$qillOp} {$label}";
                     break;
                 case 'Int':
                     $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer');
                     $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
                     break;
                 case 'Boolean':
                     if (!is_array($value)) {
                         if (strtolower($value) == 'yes' || strtolower($value) == strtolower(ts('Yes'))) {
                             $value = 1;
                         } else {
                             $value = (int) $value;
                         }
                         $value = $value == 1 ? 1 : 0;
                         $qillValue = $value ? 'Yes' : 'No';
                     }
                     $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer');
                     $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
                     break;
                 case 'Link':
                 case 'Memo':
                     $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
                     $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
//.........这里部分代码省略.........

 /**
  * Build qill for field.
  *
  * Qill refers to the query detail visible on the UI.
  *
  * @param $daoName
  * @param $fieldName
  * @param $fieldValue
  * @param $op
  * @param array $pseduoExtraParam
  *
  * @return array
  */
 public static function buildQillForFieldValue($daoName, $fieldName, $fieldValue, $op, $pseduoExtraParam = array())
 {
     $qillOperators = CRM_Core_SelectValues::getSearchBuilderOperators();
     if ($fieldName == 'activity_type_id') {
         $pseduoOptions = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
     } elseif ($daoName == 'CRM_Event_DAO_Event' && $fieldName == 'id') {
         $pseduoOptions = CRM_Event_BAO_Event::getEvents(0, $fieldValue, TRUE, TRUE, TRUE);
     } else {
         $pseduoOptions = CRM_Core_PseudoConstant::get($daoName, $fieldName, $pseduoExtraParam = array());
     }
     //For those $fieldName which don't have any associated pseudoconstant defined
     if (empty($pseduoOptions)) {
         if (is_array($fieldValue)) {
             $op = key($fieldValue);
             $fieldValue = $fieldValue[$op];
             if (is_array($fieldValue)) {
                 $fieldValue = implode(', ', $fieldValue);
             }
         }
     } elseif (is_array($fieldValue)) {
         $qillString = array();
         if (in_array(key($fieldValue), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
             $op = key($fieldValue);
             $fieldValue = $fieldValue[$op];
         }
         foreach ((array) $fieldValue as $val) {
             $qillString[] = $pseduoOptions[$val];
         }
         $fieldValue = implode(', ', $qillString);
     } else {
         if (array_key_exists($fieldValue, $pseduoOptions)) {
             $fieldValue = $pseduoOptions[$fieldValue];
         }
     }
     return array(CRM_Utils_Array::value($op, $qillOperators, $op), $fieldValue);
 }

 /**
  * Build quick form.
  */
 public function buildQuickForm()
 {
     $fields = self::fields();
     // Get fields of type date
     // FIXME: This is a hack until our fields contain this meta-data
     $dateFields = array();
     $stringFields = array();
     $searchByLabelFields = array();
     foreach ($fields as $name => $field) {
         if (strpos($name, '_date') || CRM_Utils_Array::value('data_type', $field) == 'Date') {
             $dateFields[] = $name;
         }
         // it's necessary to know which of the fields are from string data type
         if (isset($field['type']) && $field['type'] === CRM_Utils_Type::T_STRING) {
             $stringFields[] = $name;
         }
         // it's necessary to know which of the fields are searchable by label
         if (isset($field['searchByLabel']) && $field['searchByLabel']) {
             $searchByLabelFields[] = $name;
         }
     }
     // Add javascript
     CRM_Core_Resources::singleton()->addScriptFile('civicrm', 'templates/CRM/Contact/Form/Search/Builder.js', 1, 'html-header')->addSetting(array('searchBuilder' => array('newBlock' => $this->get('newBlock'), 'dateFields' => $dateFields, 'fieldOptions' => self::fieldOptions(), 'stringFields' => $stringFields, 'searchByLabelFields' => $searchByLabelFields, 'generalOperators' => array('' => ts('-operator-')) + CRM_Core_SelectValues::getSearchBuilderOperators(), 'stringOperators' => array('' => ts('-operator-')) + CRM_Core_SelectValues::getSearchBuilderOperators(CRM_Utils_Type::T_STRING))));
     //get the saved search mapping id
     $mappingId = NULL;
     if ($this->_ssID) {
         $mappingId = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_SavedSearch', $this->_ssID, 'mapping_id');
     }
     CRM_Core_BAO_Mapping::buildMappingForm($this, 'Search Builder', $mappingId, $this->_columnCount, $this->_blockCount);
     parent::buildQuickForm();
 }