PHP CRM_Contact_BAO_Query::buildQillForFieldValue方法代码示例

 /**
  * @param $values
  * @param $query
  */
 public static function whereClauseSingle(&$values, &$query)
 {
     list($name, $op, $value, $grouping, $wildcard) = $values;
     $quoteValue = NULL;
     $fields = array_merge(CRM_Contribute_BAO_Contribution::fields(), self::getFields());
     if (!empty($value) && !is_array($value)) {
         $quoteValue = "\"{$value}\"";
     }
     $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
     foreach (self::getRecurringFields() as $dateField => $dateFieldTitle) {
         if (self::buildDateWhere($values, $query, $name, $dateField, $dateFieldTitle)) {
             return;
         }
     }
     switch ($name) {
         case 'contribution_date':
         case 'contribution_date_low':
         case 'contribution_date_low_time':
         case 'contribution_date_high':
         case 'contribution_date_high_time':
             // process to / from date
             $query->dateQueryBuilder($values, 'civicrm_contribution', 'contribution_date', 'receive_date', 'Contribution Date');
             return;
         case 'contribution_amount':
         case 'contribution_amount_low':
         case 'contribution_amount_high':
             // process min/max amount
             $query->numberRangeBuilder($values, 'civicrm_contribution', 'contribution_amount', 'total_amount', 'Contribution Amount', NULL);
             return;
         case 'contribution_thankyou_date_is_not_null':
             if ($value) {
                 $op = "IS NOT NULL";
                 $query->_qill[$grouping][] = ts('Contribution Thank-you Sent');
             } else {
                 $op = "IS NULL";
                 $query->_qill[$grouping][] = ts('Contribution Thank-you Not Sent');
             }
             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.thankyou_date", $op);
             $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
             return;
         case 'contribution_receipt_date_is_not_null':
             if ($value) {
                 $op = "IS NOT NULL";
                 $query->_qill[$grouping][] = ts('Contribution Receipt Sent');
             } else {
                 $op = "IS NULL";
                 $query->_qill[$grouping][] = ts('Contribution Receipt Not Sent');
             }
             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.receipt_date", $op);
             $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
             return;
         case 'financial_type':
         case 'contribution_page':
         case 'payment_instrument':
         case 'contribution_payment_instrument':
         case 'contribution_status':
             $name .= '_id';
         case 'financial_type_id':
         case 'payment_instrument_id':
         case 'contribution_payment_instrument_id':
         case 'contribution_page_id':
         case 'contribution_status_id':
         case 'contribution_id':
         case 'contribution_currency_type':
         case 'contribution_currency':
         case 'contribution_source':
         case 'contribution_trxn_id':
         case 'contribution_check_number':
         case 'contribution_contact_id':
         case strpos($name, '_amount') !== FALSE:
         case strpos($name, '_date') !== FALSE:
             $qillName = $name;
             $pseudoExtraParam = NULL;
             if (strpos($name, '_amount') !== FALSE || strpos($name, '_date') !== FALSE || in_array($name, array('contribution_id', 'contribution_currency', 'contribution_source', 'contribution_trxn_id', 'contribution_check_number', 'contribution_payment_instrument_id', 'contribution_contact_id'))) {
                 $name = str_replace('contribution_', '', $name);
                 if (!in_array($name, array('source', 'id', 'contact_id'))) {
                     $qillName = str_replace('contribution_', '', $qillName);
                 }
             }
             if (in_array($name, array('contribution_currency', 'contribution_currency_type'))) {
                 $qillName = $name = 'currency';
                 $pseudoExtraParam = array('labelColumn' => 'name');
             }
             $dataType = !empty($fields[$qillName]['type']) ? CRM_Utils_Type::typeToString($fields[$qillName]['type']) : 'String';
             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.{$name}", $op, $value, $dataType);
             list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Contribute_DAO_Contribution', $name, $value, $op, $pseudoExtraParam);
             $query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
             $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
             return;
         case 'contribution_pcp_made_through_id':
         case 'contribution_soft_credit_type_id':
             $qillName = $name;
             if ($name == 'contribution_pcp_made_through_id') {
                 $qillName = $name = 'pcp_id';
                 $fields[$name] = array('title' => ts('Personal Campaign Page'), 'type' => 2);
             }
//.........这里部分代码省略.........

//.........这里部分代码省略.........
         case 'mailing_id':
             $selectedMailings = array_flip($value);
             $value = "(" . implode(',', $value) . ")";
             $op = 'IN';
             $query->_where[$grouping][] = "civicrm_mailing.id {$op} {$value}";
             $mailings = CRM_Mailing_BAO_Mailing::getMailingsList();
             foreach ($selectedMailings as $id => $dnc) {
                 $selectedMailings[$id] = $mailings[$id];
             }
             $selectedMailings = implode(' or ', $selectedMailings);
             $query->_qill[$grouping][] = "Mailing Name {$op} \"{$selectedMailings}\"";
             $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
             $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
             return;
         case 'mailing_name':
             $value = strtolower(addslashes($value));
             if ($wildcard) {
                 $value = "%{$value}%";
                 $op = 'LIKE';
             }
             $query->_where[$grouping][] = "LOWER(civicrm_mailing.name) {$op} '{$value}'";
             $query->_qill[$grouping][] = "Mailing Namename {$op} \"{$value}\"";
             $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
             $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
             return;
         case 'mailing_date':
         case 'mailing_date_low':
         case 'mailing_date_high':
             // process to / from date
             $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
             $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1;
             $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1;
             $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
             $query->dateQueryBuilder($values, 'civicrm_mailing_job', 'mailing_date', 'start_date', 'Mailing Delivery Date');
             return;
         case 'mailing_delivery_status':
             $options = CRM_Mailing_PseudoConstant::yesNoOptions('delivered');
             list($name, $op, $value, $grouping, $wildcard) = $values;
             if ($value == 'Y') {
                 self::mailingEventQueryBuilder($query, $values, 'civicrm_mailing_event_delivered', 'mailing_delivery_status', ts('Mailing Delivery'), $options);
             } elseif ($value == 'N') {
                 $options['Y'] = $options['N'];
                 $values = array($name, $op, 'Y', $grouping, $wildcard);
                 self::mailingEventQueryBuilder($query, $values, 'civicrm_mailing_event_bounce', 'mailing_delivery_status', ts('Mailing Delivery'), $options);
             }
             return;
         case 'mailing_bounce_types':
             $op = 'IN';
             $values = array($name, $op, $value, $grouping, $wildcard);
             self::mailingEventQueryBuilder($query, $values, 'civicrm_mailing_event_bounce', 'bounce_type_id', ts('Bounce type(s)'), CRM_Core_PseudoConstant::get('CRM_Mailing_Event_DAO_Bounce', 'bounce_type_id', array('keyColumn' => 'id', 'labelColumn' => 'name')));
             return;
         case 'mailing_open_status':
             self::mailingEventQueryBuilder($query, $values, 'civicrm_mailing_event_opened', 'mailing_open_status', ts('Mailing: Trackable Opens'), CRM_Mailing_PseudoConstant::yesNoOptions('open'));
             return;
         case 'mailing_click_status':
             self::mailingEventQueryBuilder($query, $values, 'civicrm_mailing_event_trackable_url_open', 'mailing_click_status', ts('Mailing: Trackable URL Clicks'), CRM_Mailing_PseudoConstant::yesNoOptions('click'));
             return;
         case 'mailing_reply_status':
             self::mailingEventQueryBuilder($query, $values, 'civicrm_mailing_event_reply', 'mailing_reply_status', ts('Mailing: Trackable Replies'), CRM_Mailing_PseudoConstant::yesNoOptions('reply'));
             return;
         case 'mailing_optout':
             $valueTitle = array(1 => ts('Opt-out Requests'));
             // include opt-out events only
             $query->_where[$grouping][] = "civicrm_mailing_event_unsubscribe.org_unsubscribe = 1";
             self::mailingEventQueryBuilder($query, $values, 'civicrm_mailing_event_unsubscribe', 'mailing_unsubscribe', ts('Mailing: '), $valueTitle);
             return;
         case 'mailing_unsubscribe':
             $valueTitle = array(1 => ts('Unsubscribe Requests'));
             // exclude opt-out events
             $query->_where[$grouping][] = "civicrm_mailing_event_unsubscribe.org_unsubscribe = 0";
             self::mailingEventQueryBuilder($query, $values, 'civicrm_mailing_event_unsubscribe', 'mailing_unsubscribe', ts('Mailing: '), $valueTitle);
             return;
         case 'mailing_forward':
             $valueTitle = array('Y' => ts('Forwards'));
             // since its a checkbox
             $values[2] = 'Y';
             self::mailingEventQueryBuilder($query, $values, 'civicrm_mailing_event_forward', 'mailing_forward', ts('Mailing: '), $valueTitle);
             return;
         case 'mailing_job_status':
             if (!empty($value)) {
                 if ($value != 'Scheduled' && $value != 'Canceled') {
                     $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1;
                 }
                 $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
                 $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1;
                 $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
                 $query->_where[$grouping][] = " civicrm_mailing_job.status = '{$value}' ";
                 $query->_qill[$grouping][] = "Mailing Job Status IS \"{$value}\"";
             }
             return;
         case 'mailing_campaign_id':
             $name = 'campaign_id';
             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_mailing.{$name}", $op, $value, 'Integer');
             list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Mailing_DAO_Mailing', $name, $value, $op);
             $query->_qill[$grouping][] = ts('Campaign %1 %2', array(1 => $op, 2 => $value));
             $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
             $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
             return;
     }
 }

 /**
  * @param $values
  * @param $query
  */
 public static function whereClauseSingle(&$values, &$query)
 {
     list($name, $op, $value, $grouping, $wildcard) = $values;
     switch ($name) {
         case 'pledge_create_date_low':
         case 'pledge_create_date_high':
             // process to / from date
             $query->dateQueryBuilder($values, 'civicrm_pledge', 'pledge_create_date', 'create_date', 'Pledge Made');
         case 'pledge_start_date_low':
         case 'pledge_start_date_high':
             // process to / from date
             $query->dateQueryBuilder($values, 'civicrm_pledge', 'pledge_start_date', 'start_date', 'Pledge Start Date');
             return;
         case 'pledge_end_date_low':
         case 'pledge_end_date_high':
             // process to / from date
             $query->dateQueryBuilder($values, 'civicrm_pledge', 'pledge_end_date', 'end_date', 'Pledge End Date');
             return;
         case 'pledge_payment_date_low':
         case 'pledge_payment_date_high':
             // process to / from date
             $query->dateQueryBuilder($values, 'civicrm_pledge_payment', 'pledge_payment_date', 'scheduled_date', 'Payment Scheduled');
             return;
         case 'pledge_amount':
         case 'pledge_amount_low':
         case 'pledge_amount_high':
             // process min/max amount
             $query->numberRangeBuilder($values, 'civicrm_pledge', 'pledge_amount', 'amount', 'Pledge Amount');
             return;
         case 'pledge_payment_status_id':
         case 'pledge_status_id':
             if ($name == 'pledge_status_id') {
                 $tableName = 'civicrm_pledge';
                 $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
                 $label = "Pledge Status";
             } else {
                 $tableName = 'civicrm_pledge_payment';
                 $query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
                 $label = "Pledge Payment Status";
             }
             $name = 'status_id';
             if (!empty($value) && is_array($value) && !in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
                 $value = array('IN' => $value);
             }
             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("{$tableName}.{$name}", $op, $value, 'Integer');
             list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Contribute_DAO_Contribution', 'contribution_status_id', $value, $op);
             $query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $label, 2 => $qillop, 3 => $qillVal));
             return;
         case 'pledge_test':
         case 'pledge_is_test':
             // We dont want to include all tests for sql OR CRM-7827
             if (!$value || $query->getOperator() != 'OR') {
                 $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_pledge.is_test', $op, $value, 'Boolean');
                 if ($value) {
                     $query->_qill[$grouping][] = ts('Pledge is a Test');
                 }
                 $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
             }
             return;
         case 'pledge_financial_type_id':
             $type = CRM_Contribute_PseudoConstant::financialType($value);
             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_pledge.financial_type_id', $op, $value, 'Integer');
             $query->_qill[$grouping][] = ts('Financial Type - %1', array(1 => $type));
             $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
             return;
         case 'pledge_contribution_page_id':
             $page = CRM_Contribute_PseudoConstant::contributionPage($value);
             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_pledge.contribution_page_id', $op, $value, 'Integer');
             $query->_qill[$grouping][] = ts('Financial Page - %1', array(1 => $page));
             $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
             return;
         case 'pledge_id':
             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_pledge.id", $op, $value, "Integer");
             $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
             return;
         case 'pledge_frequency_interval':
             $query->_where[$grouping][] = "civicrm_pledge.frequency_interval {$op} {$value}";
             $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
             return;
         case 'pledge_frequency_unit':
             $query->_where[$grouping][] = "civicrm_pledge.frequency_unit {$op} {$value}";
             $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
             return;
         case 'pledge_campaign_id':
             $campParams = array('op' => $op, 'campaign' => $value, 'grouping' => $grouping, 'tableName' => 'civicrm_pledge');
             CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query);
             return;
         case 'pledge_contact_id':
             $name = str_replace('pledge_', '', $name);
             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_pledge.{$name}", $op, $value, 'Integer');
             list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Pledge_DAO_Pledge', $name, $value, $op);
             $query->_qill[$grouping][] = ts('Contact ID %1 %2', array(1 => $op, 2 => $value));
             $query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
             return;
     }
 }

 /**
  * Builds the necessary structures for all fields that are similar to option value look-ups.
  *
  * @param string $name
  *   the name of the field.
  * @param string $op
  *   the sql operator, this function should handle ALL SQL operators.
  * @param string $value
  *   depends on the operator and who's calling the query builder.
  * @param int $grouping
  *   the index where to place the where clause.
  * @param $selectValues
  *   The key value pairs for this element. This allows us to use this function for things besides option-value pairs.
  * @param array $field
  *   an array that contains various properties of the field identified by $name.
  * @param string $label
  *   The label for this field element.
  * @param string $dataType
  *   The data type for this element.
  * @param bool $useIDsOnly
  */
 public function optionValueQuery($name, $op, $value, $grouping, $daoName = NULL, $field, $label, $dataType = 'String', $useIDsOnly = FALSE)
 {
     $pseudoFields = array('email_greeting', 'postal_greeting', 'addressee', 'gender_id', 'prefix_id', 'suffix_id', 'communication_style_id');
     if ($useIDsOnly) {
         list($tableName, $fieldName) = explode('.', $field['where'], 2);
         if ($tableName == 'civicrm_contact') {
             $wc = "contact_a.{$fieldName}";
         } else {
             $wc = "{$tableName}.id";
         }
     } else {
         $wc = self::caseImportant($op) ? "LOWER({$field['where']})" : "{$field['where']}";
     }
     if (in_array($name, $pseudoFields)) {
         if (!in_array($name, array('gender_id', 'prefix_id', 'suffix_id', 'communication_style_id'))) {
             $wc = "contact_a.{$name}_id";
         }
         $dataType = 'Positive';
         $value = !$value ? 0 : $value;
     }
     if ($name == "world_region") {
         $field['name'] = $name;
     }
     list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue($daoName, $field['name'], $value, $op);
     $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $label, 2 => $qillop, 3 => $qillVal));
     $this->_where[$grouping][] = self::buildClause($wc, $op, $value, $dataType);
 }

 /**
  * @param $values
  * @param $query
  */
 public static function whereClauseSingle(&$values, &$query)
 {
     list($name, $op, $value, $grouping, $wildcard) = $values;
     switch ($name) {
         case 'member_join_date_low':
         case 'member_join_date_high':
             $query->dateQueryBuilder($values, 'civicrm_membership', 'member_join_date', 'join_date', 'Member Since');
             return;
         case 'member_start_date_low':
         case 'member_start_date_high':
             $query->dateQueryBuilder($values, 'civicrm_membership', 'member_start_date', 'start_date', 'Start Date');
             return;
         case 'member_end_date_low':
         case 'member_end_date_high':
             $query->dateQueryBuilder($values, 'civicrm_membership', 'member_end_date', 'end_date', 'End Date');
             return;
         case 'member_join_date':
             $op = '>=';
             $date = CRM_Utils_Date::format($value);
             if ($date) {
                 $query->_where[$grouping][] = "civicrm_membership.join_date {$op} {$date}";
                 $date = CRM_Utils_Date::customFormat($value);
                 $format = CRM_Utils_Date::customFormat(CRM_Utils_Date::format(array_reverse($value), '-'));
                 $query->_qill[$grouping][] = ts('Member Since %2 %1', array(1 => $format, 2 => $op));
             }
             return;
         case 'member_source':
             $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
             $value = $strtolower(CRM_Core_DAO::escapeString(trim($value)));
             $query->_where[$grouping][] = "civicrm_membership.source {$op} '{$value}'";
             $query->_qill[$grouping][] = ts('Source %2 %1', array(1 => $value, 2 => $op));
             $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
             return;
             // CRM-17011 These 2 variants appear in some smart groups saved at some time prior to 4.6.6.
         // CRM-17011 These 2 variants appear in some smart groups saved at some time prior to 4.6.6.
         case 'member_status_id':
         case 'member_membership_type_id':
             if (is_array($value)) {
                 $op = 'IN';
                 $value = array_keys($value);
             }
         case 'membership_status':
         case 'membership_status_id':
         case 'membership_type':
         case 'membership_type_id':
         case 'member_id':
             if (strpos($name, 'status') !== FALSE) {
                 $name = 'status_id';
                 $qillName = 'Membership Status(s)';
             } elseif ($name == 'member_id') {
                 $name = 'id';
                 $qillName = 'Membership ID';
             } else {
                 $name = 'membership_type_id';
                 $qillName = 'Membership Type(s)';
             }
             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.{$name}", $op, $value, "Integer");
             list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Member_DAO_Membership', $name, $value, $op);
             $query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $qillName, 2 => $op, 3 => $value));
             $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
             return;
         case 'member_test':
             // We dont want to include all tests for sql OR CRM-7827
             if (!$value || $query->getOperator() != 'OR') {
                 $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_test", $op, $value, "Boolean");
                 if ($value) {
                     $query->_qill[$grouping][] = ts('Membership is a Test');
                 }
             }
             $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
             return;
         case 'member_auto_renew':
             $op = "!=";
             if ($value) {
                 $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NOT NULL";
                 $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("ccr.contribution_status_id", $op, array_search('Cancelled', CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name')), "Integer");
                 $query->_qill[$grouping][] = ts("Membership is Auto-Renew");
             } else {
                 $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NULL";
                 $query->_qill[$grouping][] = ts("Membership is NOT Auto-Renew");
             }
             $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
             return;
         case 'member_pay_later':
             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_pay_later", $op, $value, "Integer");
             if ($value) {
                 $query->_qill[$grouping][] = ts("Membership is Pay Later");
             } else {
                 $query->_qill[$grouping][] = ts("Membership is NOT Pay Later");
             }
             $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
             return;
         case 'member_is_primary':
             if ($value) {
                 $query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NULL";
                 $query->_qill[$grouping][] = ts("Primary Members Only");
//.........这里部分代码省略.........

 /**
  * Where clause for a single field.
  *
  * @param $values
  * @param $query
  *
  * @return void
  */
 public static function whereClauseSingle(&$values, &$query)
 {
     list($name, $op, $value, $grouping, $wildcard) = $values;
     $val = $names = array();
     switch ($name) {
         case 'case_type_id':
         case 'case_type':
         case 'case_status':
         case 'case_status_id':
         case 'case_id':
             if (strpos($name, 'type')) {
                 $name = 'case_type_id';
                 $label = 'Case Type(s)';
             } elseif (strpos($name, 'status')) {
                 $name = 'status_id';
                 $label = 'Case Status(s)';
             } else {
                 $name = 'id';
                 $label = 'Case ID';
             }
             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.{$name}", $op, $value, "Integer");
             list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Case_DAO_Case', $name, $value, $op);
             $query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $label, 2 => $op, 3 => $value));
             $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
             return;
         case 'case_owner':
         case 'case_mycases':
             if (!empty($value)) {
                 if ($value == 2) {
                     $session = CRM_Core_Session::singleton();
                     $userID = $session->get('userID');
                     $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_relationship.contact_id_b", $op, $userID, 'Int');
                     $query->_qill[$grouping][] = ts('Case %1 My Cases', array(1 => $op));
                     $query->_tables['case_relationship'] = $query->_whereTables['case_relationship'] = 1;
                 } elseif ($value == 1) {
                     $query->_qill[$grouping][] = ts('Case %1 All Cases', array(1 => $op));
                     $query->_where[$grouping][] = "civicrm_case_contact.contact_id = contact_a.id";
                 }
                 $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
                 $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
             }
             return;
         case 'case_deleted':
             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.is_deleted", $op, $value, 'Boolean');
             if ($value) {
                 $query->_qill[$grouping][] = ts("Find Deleted Cases");
             }
             $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
             return;
         case 'case_activity_subject':
             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.subject", $op, $value, 'String');
             $query->_qill[$grouping][] = ts("Activity Subject %1 '%2'", array(1 => $op, 2 => $value));
             $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
             $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
             $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
             return;
         case 'case_subject':
             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.subject", $op, $value, 'String');
             $query->_qill[$grouping][] = ts("Case Subject %1 '%2'", array(1 => $op, 2 => $value));
             $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
             $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
             return;
         case 'case_source_contact_id':
             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case_reporter.sort_name", $op, $value, 'String');
             $query->_qill[$grouping][] = ts("Activity Reporter %1 '%2'", array(1 => $op, 2 => $value));
             $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
             $query->_tables['civicrm_case_reporter'] = $query->_whereTables['civicrm_case_reporter'] = 1;
             $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
             $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
             return;
         case 'case_recent_activity_date':
             $date = CRM_Utils_Date::format($value);
             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.activity_date_time", $op, $date, 'Date');
             if ($date) {
                 $date = CRM_Utils_Date::customFormat($date);
                 $query->_qill[$grouping][] = ts("Activity Actual Date %1 %2", array(1 => $op, 2 => $date));
             }
             $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
             $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
             $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
             return;
         case 'case_scheduled_activity_date':
             $date = CRM_Utils_Date::format($value);
             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.activity_date_time", $op, $date, 'Date');
             if ($date) {
                 $date = CRM_Utils_Date::customFormat($date);
                 $query->_qill[$grouping][] = ts("Activity Schedule Date %1 %2", array(1 => $op, 2 => $date));
             }
             $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
             $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
             $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
             return;
//.........这里部分代码省略.........

 /**
  * Where clause for a single field.
  *
  * @param $values
  * @param $query
  * @return void
  */
 public static function whereClauseSingle(&$values, &$query)
 {
     list($name, $op, $value, $grouping) = $values;
     $fields = CRM_Activity_BAO_Activity::exportableFields();
     $query->_tables['civicrm_activity'] = $query->_whereTables['civicrm_activity'] = 1;
     if ($query->_mode & CRM_Contact_BAO_Query::MODE_ACTIVITY) {
         $query->_skipDeleteClause = TRUE;
     }
     switch ($name) {
         case 'activity_type_id':
         case 'activity_status_id':
         case 'activity_engagement_level':
         case 'activity_subject':
         case 'activity_id':
             $qillName = $name;
             if (in_array($name, array('activity_engagement_level', 'activity_id'))) {
                 $name = $qillName = str_replace('activity_', '', $name);
             }
             if (in_array($name, array('activity_status_id', 'activity_subject'))) {
                 $name = str_replace('activity_', '', $name);
                 $qillName = str_replace('_id', '', $qillName);
             }
             $dataType = !empty($fields[$qillName]['type']) ? CRM_Utils_Type::typeToString($fields[$qillName]['type']) : 'String';
             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_activity.{$name}", $op, $value, $dataType);
             list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Activity_DAO_Activity', $name, $value, $op);
             $query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
             break;
         case 'activity_type':
         case 'activity_status':
             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("{$name}.label", $op, $value, 'String');
             list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Activity_DAO_Activity', $name, $value, $op);
             $query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$name]['title'], 2 => $op, 3 => $value));
             $query->_tables[$name] = $query->_whereTables[$name] = 1;
             break;
         case 'activity_survey_id':
             if (!$value) {
                 break;
             }
             $value = CRM_Utils_Type::escape($value, 'Integer');
             $query->_where[$grouping][] = " civicrm_activity.source_record_id = {$value}";
             $query->_qill[$grouping][] = ts('Survey') . ' - ' . CRM_Core_DAO::getFieldValue('CRM_Campaign_DAO_Survey', $value, 'title');
             break;
         case 'activity_role':
             CRM_Contact_BAO_Query::$_activityRole = $values[2];
             $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
             $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
             $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
             $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
             if ($values[2]) {
                 $query->_tables['civicrm_activity_contact'] = $query->_whereTables['civicrm_activity_contact'] = 1;
                 if ($values[2] == 1) {
                     $query->_where[$grouping][] = " civicrm_activity_contact.record_type_id = {$sourceID}";
                     $query->_qill[$grouping][] = ts('Activity created by');
                 } elseif ($values[2] == 2) {
                     $query->_where[$grouping][] = " civicrm_activity_contact.record_type_id = {$assigneeID}";
                     $query->_qill[$grouping][] = ts('Activity assigned to');
                 } elseif ($values[2] == 3) {
                     $query->_where[$grouping][] = " civicrm_activity_contact.record_type_id = {$targetID}";
                     $query->_qill[$grouping][] = ts('Activity targeted to');
                 }
             }
             break;
         case 'activity_test':
             // We don't want to include all tests for sql OR CRM-7827
             if (!$value || $query->getOperator() != 'OR') {
                 $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_activity.is_test", $op, $value, "Boolean");
                 if ($value) {
                     $query->_qill[$grouping][] = ts('Activity is a Test');
                 }
             }
             break;
         case 'activity_date':
         case 'activity_date_low':
         case 'activity_date_high':
             $query->dateQueryBuilder($values, 'civicrm_activity', 'activity_date', 'activity_date_time', ts('Activity Date'));
             break;
         case 'activity_taglist':
             $taglist = $value;
             $value = array();
             foreach ($taglist as $val) {
                 if ($val) {
                     $val = explode(',', $val);
                     foreach ($val as $tId) {
                         if (is_numeric($tId)) {
                             $value[$tId] = 1;
                         }
                     }
                 }
             }
         case 'activity_tags':
             $value = array_keys($value);
             $activityTags = CRM_Core_PseudoConstant::get('CRM_Core_DAO_EntityTag', 'tag_id', array('onlyActive' => FALSE));
             $names = array();
//.........这里部分代码省略.........

//.........这里部分代码省略.........
                 }
                 if (!empty($allEventIds)) {
                     $op = "IN";
                     $value = "(" . implode(",", $allEventIds) . ")";
                 }
             }
             $query->_where[$grouping][] = "civicrm_event.id {$op} {$value}";
             $query->_qill[$grouping][] = ts('Include Repeating Events');
             $query->_tables['civicrm_event'] = $query->_whereTables['civicrm_event'] = 1;
             return;
         case 'participant_is_test':
             $key = array_search('civicrm_participant.is_test = 0', $query->_where[$grouping]);
             if (!empty($key)) {
                 unset($query->_where[$grouping][$key]);
             }
         case 'participant_test':
             // We dont want to include all tests for sql OR CRM-7827
             if (!$value || $query->getOperator() != 'OR') {
                 $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_participant.is_test", $op, $value, "Boolean");
                 if ($value) {
                     $query->_qill[$grouping][] = ts("Participant is a Test");
                 }
                 $query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
             }
             return;
         case 'participant_fee_id':
             $feeLabel = CRM_Core_DAO::getFieldValue('CRM_Price_DAO_PriceFieldValue', $value, 'label');
             $feeLabel = CRM_Core_DAO::escapeString(trim($feeLabel));
             if ($value) {
                 $query->_where[$grouping][] = "civicrm_participant.fee_level LIKE '%{$feeLabel}%'";
                 $query->_qill[$grouping][] = ts("Fee level") . " contains {$feeLabel}";
             }
             $query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
             return;
         case 'participant_fee_amount_high':
         case 'participant_fee_amount_low':
             $query->numberRangeBuilder($values, 'civicrm_participant', 'participant_fee_amount', 'fee_amount', 'Fee Amount');
             return;
         case 'participant_status_id':
         case 'participant_role_id':
             if ($value && is_array($value) && strpos($op, 'IN') === FALSE) {
                 $op = 'IN';
             }
         case 'participant_status':
         case 'participant_role':
         case 'participant_source':
         case 'participant_id':
         case 'participant_contact_id':
         case 'participant_is_pay_later':
         case 'participant_fee_amount':
         case 'participant_fee_level':
             $qillName = $name;
             if (in_array($name, array('participant_status_id', 'participant_role_id', 'participant_source', 'participant_id', 'participant_contact_id', 'participant_fee_amount', 'participant_fee_level', 'participant_is_pay_later'))) {
                 $name = str_replace('participant_', '', $name);
                 if ($name == 'is_pay_later') {
                     $qillName = $name;
                 }
                 if ($name == 'role_id') {
                     $qillName = 'participant_role';
                     $query->_where[$grouping][] = " civicrm_participant.{$name} REGEXP '[[:<:]]" . implode('[[:>:]]|[[:<:]]', (array) $value) . "[[:>:]]' ";
                 }
             }
             $dataType = !empty($fields[$qillName]['type']) ? CRM_Utils_Type::typeToString($fields[$qillName]['type']) : 'String';
             if (in_array($name, array('participant_status', 'participant_role'))) {
                 $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("{$name}.label", $op, $value, $dataType);
             } elseif ($name != 'role_id') {
                 $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_participant.{$name}", $op, $value, $dataType);
             }
             list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Event_DAO_Participant', $name, $value, $op);
             $query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
             $query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
             return;
         case 'participant_register_date':
             $query->dateQueryBuilder($values, 'civicrm_participant', 'participant_register_date', 'register_date', 'Register Date');
             return;
         case 'event_id':
         case 'participant_event_id':
             $name = str_replace('participant_', '', $name);
         case 'event_is_public':
         case 'event_type_id':
         case 'event_title':
             $qillName = $name;
             if (in_array($name, array('event_id', 'event_title', 'event_is_public'))) {
                 $name = str_replace('event_', '', $name);
             }
             $dataType = !empty($fields[$qillName]['type']) ? CRM_Utils_Type::typeToString($fields[$qillName]['type']) : 'String';
             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_event.{$name}", $op, $value, $dataType);
             $query->_tables['civicrm_event'] = $query->_whereTables['civicrm_event'] = 1;
             if (!array_key_exists($qillName, $fields)) {
                 break;
             }
             list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Event_DAO_Event', $name, $value, $op);
             $query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
             return;
         case 'participant_campaign_id':
             $campParams = array('op' => $op, 'campaign' => $value, 'grouping' => $grouping, 'tableName' => 'civicrm_participant');
             CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query);
             return;
     }
 }

 /**
  * Set the qill and where properties for a field.
  *
  * This function is intended as a short-term function to encourage refactoring
  * & re-use - but really we should just have less special-casing.
  *
  * @param string $name
  * @param string $op
  * @param string|array $value
  * @param string $grouping
  * @param string $field
  */
 public function setQillAndWhere($name, $op, $value, $grouping, $field)
 {
     $this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $value);
     list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, $name, $value, $op);
     $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['title'], 2 => $qillop, 3 => $qillVal));
 }

//.........这里部分代码省略.........
                             $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));
                     break;
                 case 'Money':
                     $value = CRM_Utils_Array::value($op, (array) $value, $value);
                     if (is_array($value)) {
                         foreach ($value as $key => $val) {
                             $value[$key] = CRM_Utils_Rule::cleanMoney($value[$key]);
                         }
                     } else {
                         $value = CRM_Utils_Rule::cleanMoney($value);
                     }
                 case 'Float':
                     if ($field['is_search_range']) {
                         $this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping);
                     } else {
                         $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Float');
                         $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
                     }
                     break;
                 case 'Date':
                     if (in_array($op, CRM_Core_DAO::acceptedSQLOperators())) {
                         $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
                         list($qillOp, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, $field['label'], $value, $op, array(), CRM_Utils_Type::T_DATE);
                         $this->_qill[$grouping][] = "{$field['label']} {$qillOp} '{$qillVal}'";
                         break;
                     }
                     $fromValue = CRM_Utils_Array::value('from', $value);
                     $toValue = CRM_Utils_Array::value('to', $value);
                     $value = CRM_Utils_Array::value($op, $value, $value);
                     if (!$fromValue && !$toValue) {
                         if (!is_array($value) && !CRM_Utils_Date::processDate($value) && !in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
                             continue;
                         }
                         // hack to handle yy format during search
                         if (is_numeric($value) && strlen($value) == 4) {
                             $value = "01-01-{$value}";
                         }
                         if (is_array($value)) {
                             $date = $qillValue = array();
                             foreach ($value as $key => $val) {
                                 $date[$key] = CRM_Utils_Date::processDate($val);
                                 $qillValue[$key] = CRM_Utils_Date::customFormat($date[$key]);
                             }
                         } else {
                             $date = CRM_Utils_Date::processDate($value);
                             $qillValue = CRM_Utils_Date::customFormat($date);
                         }
                         $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $date, 'String');
                         $this->_qill[$grouping][] = $field['label'] . " {$qillOp} " . implode(', ', (array) $qillValue);
                     } else {
                         if (is_numeric($fromValue) && strlen($fromValue) == 4) {
                             $fromValue = "01-01-{$fromValue}";
                         }
                         if (is_numeric($toValue) && strlen($toValue) == 4) {
                             $toValue = "01-01-{$toValue}";

 /**
  * Where / qill clause for groups
  *
  * @param $values
  *
  * @return void
  */
 public function group(&$values)
 {
     list($name, $op, $value, $grouping, $wildcard) = $values;
     // Replace pseudo operators from search builder
     $op = str_replace('EMPTY', 'NULL', $op);
     if (count($value) > 1) {
         if (strpos($op, 'IN') === FALSE && strpos($op, 'NULL') === FALSE) {
             CRM_Core_Error::fatal(ts("%1 is not a valid operator", array(1 => $op)));
         }
         $this->_useDistinct = TRUE;
     }
     $groupIds = NULL;
     $names = array();
     $isSmart = FALSE;
     $isNotOp = $op == 'NOT IN' || $op == '!=';
     if ($value) {
         if (strpos($op, 'IN') === FALSE) {
             $value = key($value);
         } else {
             $value = array_keys($value);
         }
     }
     $statii = array();
     $gcsValues =& $this->getWhereValues('group_contact_status', $grouping);
     if ($gcsValues && is_array($gcsValues[2])) {
         foreach ($gcsValues[2] as $k => $v) {
             if ($v) {
                 $statii[] = "'" . CRM_Utils_Type::escape($k, 'String') . "'";
             }
         }
     } else {
         $statii[] = '"Added"';
     }
     $skipGroup = FALSE;
     if (!is_array($value) && count($statii) == 1 && $statii[0] == '"Added"' && !$isNotOp) {
         if (!empty($value) && CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Group', $value, 'saved_search_id')) {
             $isSmart = TRUE;
         }
     }
     $ssClause = $this->addGroupContactCache($value, NULL, "contact_a", $op);
     $isSmart = !$ssClause ? FALSE : $isSmart;
     $groupClause = NULL;
     if (!$isSmart) {
         $groupIds = implode(',', (array) $value);
         $gcTable = "`civicrm_group_contact-{$groupIds}`";
         $joinClause = array("contact_a.id = {$gcTable}.contact_id");
         if ($statii) {
             $joinClause[] = "{$gcTable}.status IN (" . implode(', ', $statii) . ")";
         }
         $this->_tables[$gcTable] = $this->_whereTables[$gcTable] = " LEFT JOIN civicrm_group_contact {$gcTable} ON (" . implode(' AND ', $joinClause) . ")";
         $groupClause = "{$gcTable}.group_id {$op} {$groupIds}";
         if (strpos($op, 'IN') !== FALSE) {
             $groupClause = "{$gcTable}.group_id {$op} ( {$groupIds} )";
         }
     }
     if ($ssClause) {
         $and = $op == 'IS NULL' ? 'AND' : 'OR';
         if ($groupClause) {
             $groupClause = "( ( {$groupClause} ) {$and} ( {$ssClause} ) )";
         } else {
             $groupClause = $ssClause;
         }
     }
     list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Contact_DAO_Group', 'id', $value, $op);
     $this->_qill[$grouping][] = ts("Group(s) %1 %2", array(1 => $qillop, 2 => $qillVal));
     if (strpos($op, 'NULL') === FALSE) {
         $this->_qill[$grouping][] = ts("Group Status %1", array(1 => implode(' ' . ts('or') . ' ', $statii)));
     }
     if ($groupClause) {
         $this->_where[$grouping][] = $groupClause;
     }
 }

 /**
  * @param $values
  * @param $query
  */
 public static function whereClauseSingle(&$values, &$query)
 {
     $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
     list($name, $op, $value, $grouping, $wildcard) = $values;
     $val = $names = array();
     switch ($name) {
         case 'grant_money_transfer_date_low':
         case 'grant_money_transfer_date_high':
             $query->dateQueryBuilder($values, 'civicrm_grant', 'grant_money_transfer_date', 'money_transfer_date', 'Money Transfer Date');
             return;
         case 'grant_money_transfer_date_notset':
             $query->_where[$grouping][] = "civicrm_grant.money_transfer_date IS NULL";
             $query->_qill[$grouping][] = ts("Grant Money Transfer Date is NULL");
             $query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
             return;
         case 'grant_application_received_date_low':
         case 'grant_application_received_date_high':
             $query->dateQueryBuilder($values, 'civicrm_grant', 'grant_application_received_date', 'application_received_date', 'Application Received Date');
             return;
         case 'grant_application_received_notset':
             $query->_where[$grouping][] = "civicrm_grant.application_received_date IS NULL";
             $query->_qill[$grouping][] = ts("Grant Application Received Date is NULL");
             $query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
             return;
         case 'grant_due_date_low':
         case 'grant_due_date_high':
             $query->dateQueryBuilder($values, 'civicrm_grant', 'grant_due_date', 'grant_due_date', 'Grant Due Date');
             return;
         case 'grant_due_date_notset':
             $query->_where[$grouping][] = "civicrm_grant.grant_due_date IS NULL";
             $query->_qill[$grouping][] = ts("Grant Due Date is NULL");
             $query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
             return;
         case 'grant_decision_date_low':
         case 'grant_decision_date_high':
             $query->dateQueryBuilder($values, 'civicrm_grant', 'grant_decision_date', 'decision_date', 'Grant Decision Date');
             return;
         case 'grant_decision_date_notset':
             $query->_where[$grouping][] = "civicrm_grant.decision_date IS NULL";
             $query->_qill[$grouping][] = ts("Grant Decision Date is NULL");
             $query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
             return;
         case 'grant_type_id':
         case 'grant_type':
         case 'grant_status_id':
         case 'grant_status':
             if (strstr($name, 'type')) {
                 $name = 'grant_type_id';
                 $label = 'Grant Type(s)';
             } else {
                 $name = 'status_id';
                 $label = 'Grant Status(s)';
             }
             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_grant.{$name}", $op, $value, "Integer");
             list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Grant_DAO_Grant', $name, $value, $op);
             $query->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $label, 2 => $qillop, 3 => $qillVal));
             $query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
             return;
         case 'grant_report_received':
             if ($value == 1) {
                 $yesNo = 'Yes';
                 $query->_where[$grouping][] = "civicrm_grant.grant_report_received {$op} {$value}";
             } elseif ($value == 0) {
                 $yesNo = 'No';
                 $query->_where[$grouping][] = "civicrm_grant.grant_report_received IS NULL";
             }
             $query->_qill[$grouping][] = "Grant Report Received = {$yesNo} ";
             $query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
             return;
         case 'grant_amount':
         case 'grant_amount_low':
         case 'grant_amount_high':
             $query->numberRangeBuilder($values, 'civicrm_grant', 'grant_amount', 'amount_total', 'Total Amount');
     }
 }

//.........这里部分代码省略.........
                             $value = str_replace('|', '[:separator:]', $value);
                         } elseif ($isSerialized) {
                             if (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
                                 $op = key($value);
                                 $value = $value[$op];
                             }
                             // CRM-19006: escape characters like comma, | before building regex pattern
                             $value = (array) $value;
                             foreach ($value as $key => $val) {
                                 $value[$key] = str_replace(array('[', ']', ','), array('\\[', '\\]', '[:comma:]'), $val);
                                 $value[$key] = str_replace('|', '[:separator:]', $value[$key]);
                             }
                             $value = implode(',', $value);
                         }
                         // CRM-14563,CRM-16575 : Special handling of multi-select custom fields
                         if ($isSerialized && !CRM_Utils_System::isNull($value) && !strstr($op, 'NULL') && !strstr($op, 'LIKE')) {
                             $sp = CRM_Core_DAO::VALUE_SEPARATOR;
                             $value = str_replace(",", "{$sp}|{$sp}", $value);
                             $value = str_replace(array('[:comma:]', '(', ')'), array(',', '[[.left-parenthesis.]]', '[[.right-parenthesis.]]'), $value);
                             $op = strstr($op, '!') || strstr($op, 'NOT') ? 'NOT RLIKE' : 'RLIKE';
                             $value = $sp . $value . $sp;
                             if (!$wildcard) {
                                 foreach (explode("|", $value) as $val) {
                                     $val = str_replace('[:separator:]', '\\|', $val);
                                     $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $val, 'String');
                                 }
                             } else {
                                 $value = str_replace('[:separator:]', '\\|', $value);
                                 $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));
                     break;
                 case 'Money':
                     $value = CRM_Utils_Array::value($op, (array) $value, $value);
                     if (is_array($value)) {
                         foreach ($value as $key => $val) {
                             $value[$key] = CRM_Utils_Rule::cleanMoney($value[$key]);
                         }
                     } else {
                         $value = CRM_Utils_Rule::cleanMoney($value);
                     }
                 case 'Float':
                     $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Float');
                     $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
                     break;
                 case 'Date':
                     $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
                     list($qillOp, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, $field['label'], $value, $op, array(), CRM_Utils_Type::T_DATE);
                     $this->_qill[$grouping][] = "{$field['label']} {$qillOp} '{$qillVal}'";
                     break;
                 case 'File':
                     if ($op == 'IS NULL' || $op == 'IS NOT NULL' || $op == 'IS EMPTY' || $op == 'IS NOT EMPTY') {
                         switch ($op) {
                             case 'IS EMPTY':
                                 $op = 'IS NULL';
                                 break;
                             case 'IS NOT EMPTY':
                                 $op = 'IS NOT NULL';
                                 break;
                         }
                         $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op);
                         $this->_qill[$grouping][] = $field['label'] . " {$qillOp} ";
                     }
                     break;
             }
         }
     }
 }

 /**
  * Generate where for a single parameter.
  *
  * @param array $values
  * @param CRM_Contact_BAO_Query $query
  */
 public static function whereClauseSingle(&$values, &$query)
 {
     list($name, $op, $value, $grouping) = $values;
     switch ($name) {
         case 'member_join_date_low':
         case 'member_join_date_high':
             $query->dateQueryBuilder($values, 'civicrm_membership', 'member_join_date', 'join_date', 'Member Since');
             return;
         case 'member_start_date_low':
         case 'member_start_date_high':
             $query->dateQueryBuilder($values, 'civicrm_membership', 'member_start_date', 'start_date', 'Start Date');
             return;
         case 'member_end_date_low':
         case 'member_end_date_high':
             $query->dateQueryBuilder($values, 'civicrm_membership', 'member_end_date', 'end_date', 'End Date');
             return;
         case 'member_join_date':
             $op = '>=';
             $date = CRM_Utils_Date::format($value);
             if ($date) {
                 $query->_where[$grouping][] = "civicrm_membership.join_date {$op} {$date}";
                 $format = CRM_Utils_Date::customFormat(CRM_Utils_Date::format(array_reverse($value), '-'));
                 $query->_qill[$grouping][] = ts('Member Since %2 %1', array(1 => $format, 2 => $op));
             }
             return;
         case 'member_source':
             $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
             $value = $strtolower(CRM_Core_DAO::escapeString(trim($value)));
             $query->_where[$grouping][] = "civicrm_membership.source {$op} '{$value}'";
             $query->_qill[$grouping][] = ts('Source %2 %1', array(1 => $value, 2 => $op));
             $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
             return;
             // CRM-17011 These 2 variants appear in some smart groups saved at some time prior to 4.6.6.
         // CRM-17011 These 2 variants appear in some smart groups saved at some time prior to 4.6.6.
         case 'member_status_id':
         case 'member_membership_type_id':
             if (is_array($value)) {
                 $op = 'IN';
                 $value = array_keys($value);
             }
         case 'membership_type_id':
             // CRM-17075 we are specifically handling the possibility we are dealing with the entity reference field
             // for membership_type_id here (although status would be handled if converted). The unhandled pathway at the moment
             // is from groupContactCache::load and this is a small fix to get the entity reference field to work.
             // However, it would seem the larger fix would be to NOT invoke the form formValues for
             // the load function. The where clause and the whereTables are saved so they should suffice to generate the query
             // to get a contact list. But, better to deal with in 4.8 now...
             if (is_string($value) && strpos($value, ',') && $op == '=') {
                 $value = array('IN' => explode(',', $value));
             }
         case 'membership_status':
         case 'membership_status_id':
         case 'membership_type':
         case 'member_id':
             if (strstr($name, 'status') && is_string($value) && !is_numeric($value)) {
                 $value = CRM_Core_PseudoConstant::getKey('CRM_Member_BAO_Membership', 'status_id', $value);
             }
             if (strpos($name, 'status') !== FALSE) {
                 $name = 'status_id';
                 $qillName = 'Membership Status(s)';
             } elseif ($name == 'member_id') {
                 $name = 'id';
                 $qillName = 'Membership ID';
             } else {
                 $name = 'membership_type_id';
                 $qillName = 'Membership Type(s)';
             }
             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.{$name}", $op, $value, "Integer");
             list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Member_DAO_Membership', $name, $value, $op);
             $query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $qillName, 2 => $op, 3 => $value));
             $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
             return;
         case 'member_test':
             // We dont want to include all tests for sql OR CRM-7827
             if (!$value || $query->getOperator() != 'OR') {
                 $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_test", $op, $value, "Boolean");
                 if ($value) {
                     $query->_qill[$grouping][] = ts('Membership is a Test');
                 }
             }
             $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
             return;
         case 'member_auto_renew':
             $op = "!=";
             if ($value) {
                 $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NOT NULL";
                 $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("ccr.contribution_status_id", $op, array_search('Cancelled', CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name')), "Integer");
                 $query->_qill[$grouping][] = ts("Membership is Auto-Renew");
             } else {
                 $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NULL";
                 $query->_qill[$grouping][] = ts("Membership is NOT Auto-Renew");
             }
             $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
             return;
//.........这里部分代码省略.........

 /**
  * @param $values
  * @param bool $fromStateProvince
  *
  * @return array|NULL
  */
 public function country(&$values, $fromStateProvince = TRUE)
 {
     list($name, $op, $value, $grouping, $wildcard) = $values;
     if (!$fromStateProvince) {
         $stateValues = $this->getWhereValues('state_province', $grouping);
         if (!empty($stateValues)) {
             // return back to caller if there are state province values
             // since that handles this case
             return NULL;
         }
     }
     $countryClause = $countryQill = NULL;
     if ($values && !empty($value)) {
         $this->_tables['civicrm_address'] = 1;
         $this->_whereTables['civicrm_address'] = 1;
         $countryClause = self::buildClause('civicrm_address.country_id', $op, $value, 'Positive');
         list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, 'country_id', $value, $op);
         $countryQill = ts("%1 %2 %3", array(1 => 'Country', 2 => $qillop, 3 => $qillVal));
         if (!$fromStateProvince) {
             $this->_where[$grouping][] = $countryClause;
             $this->_qill[$grouping][] = $countryQill;
         }
     }
     if ($fromStateProvince) {
         if (!empty($countryClause)) {
             return array($countryClause, " ...AND... " . $countryQill);
         } else {
             return array(NULL, NULL);
         }
     }
 }