/** * @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); } if ($name == 'contribution_soft_credit_type_id') { $qillName = str_replace('_id', '', $qillName); $fields[$qillName]['type'] = $fields[$qillName]['data_type']; $name = str_replace('contribution_', '', $name); } $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution_soft.{$name}", $op, $value, CRM_Utils_Type::typeToString($fields[$qillName]['type'])); list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Contribute_DAO_ContributionSoft', $name, $value, $op); $query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value)); $query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1; return; case 'contribution_or_softcredits': if ($value == 'only_scredits') { $query->_where[$grouping][] = "contribution_search_scredit_combined.scredit_id IS NOT NULL"; $query->_qill[$grouping][] = ts('Contributions OR Soft Credits? - Soft Credits Only'); $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1; $query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1; } elseif ($value == 'both_related') { $query->_where[$grouping][] = "contribution_search_scredit_combined.filter_id IS NOT NULL"; $query->_qill[$grouping][] = ts('Contributions OR Soft Credits? - Soft Credits with related Hard Credit'); $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1; $query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1; } elseif ($value == 'both') { $query->_qill[$grouping][] = ts('Contributions OR Soft Credits? - Both'); $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1; $query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1; } // default option: $value == 'only_contribs' return; case 'contribution_is_test': // By default is Contribution Search form we choose is_test = 0 in otherwords always show active contribution // so in case if any one choose any Yes/No avoid the default clause otherwise it will be conflict in whereClause $key = array_search('civicrm_contribution.is_test = 0', $query->_where[$grouping]); if (!empty($key)) { unset($query->_where[$grouping][$key]); } case 'contribution_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_contribution.is_test", $op, $value, "Boolean"); if ($value) { $query->_qill[$grouping][] = ts("Only Display Test Contributions"); } $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1; } return; case 'contribution_is_pay_later': case 'contribution_pay_later': $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.is_pay_later", $op, $value, "Boolean"); if ($value) { $query->_qill[$grouping][] = ts("Find Pay Later Contributions"); } else { $query->_qill[$grouping][] = ts("Exclude Pay Later Contributions"); } $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1; return; case 'contribution_recurring': if ($value) { $query->_where[$grouping][] = "civicrm_contribution.contribution_recur_id IS NOT NULL"; $query->_qill[$grouping][] = ts("Find Recurring Contributions"); $query->_tables['civicrm_contribution_recur'] = $query->_whereTables['civicrm_contribution_recur'] = 1; } else { $query->_where[$grouping][] = "civicrm_contribution.contribution_recur_id IS NULL"; $query->_qill[$grouping][] = ts("Exclude Recurring Contributions"); } return; case 'contribution_recur_id': $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.contribution_recur_id", $op, $value, "Integer"); $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1; return; case 'contribution_note': $value = $strtolower(CRM_Core_DAO::escapeString($value)); if ($wildcard) { $value = "%{$value}%"; $op = 'LIKE'; } $wc = $op != 'LIKE' ? "LOWER(civicrm_note.note)" : "civicrm_note.note"; $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, "String"); $query->_qill[$grouping][] = ts('Contribution Note %1 %2', array(1 => $op, 2 => $quoteValue)); $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = $query->_whereTables['contribution_note'] = 1; return; case 'contribution_membership_id': $query->_where[$grouping][] = " civicrm_membership.id {$op} {$value}"; $query->_tables['contribution_membership'] = $query->_whereTables['contribution_membership'] = 1; return; case 'contribution_participant_id': $query->_where[$grouping][] = " civicrm_participant.id {$op} {$value}"; $query->_tables['contribution_participant'] = $query->_whereTables['contribution_participant'] = 1; return; case 'contribution_pcp_display_in_roll': $query->_where[$grouping][] = " civicrm_contribution_soft.pcp_display_in_roll {$op} '{$value}'"; if ($value) { $query->_qill[$grouping][] = ts("Personal Campaign Page Honor Roll"); } else { $query->_qill[$grouping][] = ts("NOT Personal Campaign Page Honor Roll"); } $query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1; return; case 'contribution_campaign_id': $campParams = array('op' => $op, 'campaign' => $value, 'grouping' => $grouping, 'tableName' => 'civicrm_contribution'); CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query); return; case 'contribution_batch_id': $batches = CRM_Contribute_PseudoConstant::batch(); $query->_where[$grouping][] = " civicrm_entity_batch.batch_id {$op} {$value}"; $query->_qill[$grouping][] = ts('Batch Name %1 %2', array(1 => $op, 2 => $batches[$value])); $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1; $query->_tables['contribution_batch'] = $query->_whereTables['contribution_batch'] = 1; return; default: //all other elements are handle in this case $fldName = substr($name, 13); if (!isset($fields[$fldName])) { // CRM-12597 CRM_Core_Session::setStatus(ts('We did not recognize the search field: %1. Please check and fix your contribution related smart groups.', array(1 => $fldName))); return; } $whereTable = $fields[$fldName]; $value = trim($value); $dataType = "String"; if (!empty($whereTable['type'])) { $dataType = CRM_Utils_Type::typeToString($whereTable['type']); } $wc = $op != 'LIKE' && $dataType != 'Date' ? "LOWER({$whereTable['where']})" : "{$whereTable['where']}"; $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, $dataType); $query->_qill[$grouping][] = "{$whereTable['title']} {$op} {$quoteValue}"; list($tableName, $fieldName) = explode('.', $whereTable['where'], 2); $query->_tables[$tableName] = $query->_whereTables[$tableName] = 1; if ($tableName == 'civicrm_contribution_product') { $query->_tables['civicrm_product'] = $query->_whereTables['civicrm_product'] = 1; $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1; } else { $query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1; } } }