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_status_id':
if (is_array($value)) {
foreach ($value as $k => $v) {
if ($v) {
$val[$k] = $k;
}
}
$status = implode(',', $val);
if (count($val) > 1) {
$op = 'IN';
$status = "({$status})";
}
} else {
$op = '=';
$status = $value;
}
$statusValues = CRM_Core_OptionGroup::values('contribution_status');
$names = array();
if (isset($val) && is_array($val)) {
foreach ($val as $id => $dontCare) {
$names[] = $statusValues[$id];
}
} else {
$names[] = $statusValues[$value];
}
$query->_qill[$grouping][] = ts('Pledge Status %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_pledge.status_id', $op, $status, 'Integer');
$query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
return;
case 'pledge_payment_status_id':
if (is_array($value)) {
foreach ($value as $k => $v) {
if ($v) {
$val[$k] = $k;
}
}
$status = implode(',', $val);
if (count($val) > 1) {
$op = 'IN';
$status = "({$status})";
}
} else {
$op = '=';
$status = $value;
}
$statusValues = CRM_Core_OptionGroup::values('contribution_status');
$names = array();
if (is_array($val)) {
foreach ($val as $id => $dontCare) {
$names[] = $statusValues[$id];
}
} else {
$names[] = $statusValues[$value];
}
$query->_qill[$grouping][] = ts('Pledge Payment Status %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_pledge_payment.status_id', $op, $status, 'Integer');
$query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
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][] = "civicrm_pledge.id {$op} {$value}";
$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;
}
}
/**
* @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;
}
}
}
/**
* 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;
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");
} else {
$query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NOT NULL";
$query->_qill[$grouping][] = ts("Related Members Only");
}
$query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
return;
case 'member_is_override':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_override", $op, $value, "Boolean");
$query->_qill[$grouping][] = $value ? ts("Is Membership Status overriden? Yes") : ts("Is Membership Status overriden? No");
$query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
return;
case 'member_campaign_id':
if (CRM_Utils_Array::value($op, $value)) {
$value = $value[$op];
}
$campParams = array('op' => $op, 'campaign' => $value, 'grouping' => $grouping, 'tableName' => 'civicrm_membership');
CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query);
return;
}
}
function whereClauseSingle(&$values, &$query)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
$fields = $this->getFields();
if (!empty($value) && !is_array($value)) {
$quoteValue = "\"{$value}\"";
}
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
switch ($name) {
case 'hrjobcontract_details_is_primary':
$query->_qill[$grouping][] = $value ? ts('Is Primary') : ts('Is not Primary');
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("hrjobcontract.is_primary", $op, $value, "Boolean");
$query->_tables['civicrm_hrjobcontract'] = $query->_whereTables['civicrm_hrjobcontract'] = 1;
return;
case 'hrjobcontract_role_role_level_type':
case 'hrjobcontract_details_contract_type':
case 'hrjobcontract_pay_is_paid':
case 'hrjobcontract_hour_hours_type':
case 'hrjobcontract_hour_hours_unit':
$display = $options = $value;
if (is_array($value) && count($value) >= 1) {
$op = 'IN';
$options = "('" . implode("','", $value) . "')";
$display = implode(' ' . ts('or') . ' ', $value);
}
$query->_qill[$grouping][] = ts('%1 %2', array(1 => $fields[$name]['title'], 2 => $op)) . ' ' . $display;
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fields[$name]['where'], $op, $options);
list($tableName, $fieldName) = explode('.', $fields[$name]['where'], 2);
$query->_tables[$tableName] = $query->_whereTables[$tableName] = 1;
return;
/*case 'hrjobcontract_is_healthcare':
$op = "IS NOT NULL";
$query->_qill[$grouping][] = ts('Healthcare is provided');
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_hrjobcontract_health.id", $op);
$query->_tables['civicrm_hrjobcontract_health'] = $query->_whereTables['civicrm_hrjobcontract_health'] = 1;
return;*/
/*case 'hrjobcontract_is_healthcare':
$op = "IS NOT NULL";
$query->_qill[$grouping][] = ts('Healthcare is provided');
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_hrjobcontract_health.id", $op);
$query->_tables['civicrm_hrjobcontract_health'] = $query->_whereTables['civicrm_hrjobcontract_health'] = 1;
return;*/
case 'hrjobcontract_pension_is_enrolled':
$display = $options = $value;
if (is_array($value) && count($value) >= 1) {
$op = 'IN';
$options = "('" . implode("','", $value) . "')";
$display = implode(' ' . ts('or') . ' ', $value);
}
$query->_qill[$grouping][] = ts('%1 %2', array(1 => $fields[$name]['title'], 2 => $op)) . ' ' . $display;
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_hrjobcontract_pension.is_enrolled", $op, $options);
$query->_tables['civicrm_hrjobcontract_pension'] = $query->_whereTables['civicrm_hrjobcontract_pension'] = 1;
return;
case 'hrjobcontract_details_period_start_date_low':
case 'hrjobcontract_details_period_start_date_high':
$query->dateQueryBuilder($values, 'civicrm_hrjobcontract_details', 'hrjobcontract_details_period_start_date', 'period_start_date', 'Period Start Date');
return;
case 'hrjobcontract_details_period_end_date_low':
case 'hrjobcontract_details_period_end_date_high':
$query->dateQueryBuilder($values, 'civicrm_hrjobcontract_details', 'hrjobcontract_details_period_end_date', 'period_end_date', 'Period End Date');
return;
case 'hrjobcontract_hour_hours_amount':
case 'hrjobcontract_hour_hours_amount_low':
case 'hrjobcontract_hour_hours_amount_high':
// process min/max amount
$query->numberRangeBuilder($values, 'civicrm_hrjobcontract_hour', 'hrjobcontract_hour_hours_amount', 'hours_amount', 'Hours Amount', NULL);
return;
case 'hrjobcontract_hour_hours_fte':
case 'hrjobcontract_hour_hours_fte_low':
case 'hrjobcontract_hour_hours_fte_high':
// process min/max fte
$query->numberRangeBuilder($values, 'civicrm_hrjobcontract_hour', 'hrjobcontract_hour_hours_fte', 'hours_fte', 'Hours FTE', NULL);
return;
case 'hrjobcontract_health_health_provider':
$query->_qill[$grouping][] = 'Healthcare Provider contains "' . $value . '"';
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("contact_health1.sort_name", 'LIKE', '%' . $value . '%');
$query->_tables['civicrm_hrjobcontract'] = $query->_whereTables['civicrm_hrjobcontract_health'] = 1;
return;
case 'hrjobcontract_health_health_provider_life_insurance':
$query->_qill[$grouping][] = 'Life insurance Provider contains "' . $value . '"';
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("contact_health2.sort_name", 'LIKE', '%' . $value . '%');
$query->_tables['civicrm_hrjobcontract'] = $query->_whereTables['civicrm_hrjobcontract_health'] = 1;
return;
case 'hrjobcontract_hour_location_standard_hours':
$hoursLocation = new CRM_Hrjobcontract_BAO_HoursLocation();
$hoursLocation->find();
$hoursLocationOptions = array();
while ($hoursLocation->fetch()) {
$hoursLocationOptions[$hoursLocation->id] = $hoursLocation->location;
}
$displayValue = array();
foreach ($value as $optionValue) {
$displayValue[] = $hoursLocationOptions[$optionValue];
}
$query->_qill[$grouping][] = 'Location/Standard hours IN ' . implode(', ', $value) . '';
// $displayValue
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_hrjobcontract_hour.location_standard_hours", 'IN', '(' . implode(',', $value) . ')');
$query->_tables['civicrm_hrjobcontract_hour'] = $query->_whereTables['civicrm_hrjobcontract_hour'] = 1;
return;
case 'hrjobcontract_leave_leave_type':
$query->_qill[$grouping][] = 'Leave Type IN ' . implode(', ', $value) . '';
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_hrjobcontract_leave.leave_type", 'IN', '(' . implode(',', $value) . ')');
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_hrjobcontract_leave.leave_amount", '>', 0);
$query->_tables['civicrm_hrjobcontract_leave'] = $query->_whereTables['civicrm_hrjobcontract_leave'] = 1;
return;
case 'hrjobcontract_pay_pay_amount':
case 'hrjobcontract_pay_pay_amount_low':
case 'hrjobcontract_pay_pay_amount_high':
$query->numberRangeBuilder($values, 'civicrm_hrjobcontract_pay', 'hrjobcontract_pay_pay_amount', 'pay_amount', 'Pay Amount', NULL);
return;
case 'hrjobcontract_pay_pay_annualized_est':
case 'hrjobcontract_pay_pay_annualized_est_low':
case 'hrjobcontract_pay_pay_annualized_est_high':
$query->numberRangeBuilder($values, 'civicrm_hrjobcontract_pay', 'hrjobcontract_pay_pay_annualized_est', 'pay_annualized_est', 'Estimated Annual Pay', NULL);
return;
case 'hrjobcontract_pay_pay_per_cycle_gross':
case 'hrjobcontract_pay_pay_per_cycle_gross_low':
case 'hrjobcontract_pay_pay_per_cycle_gross_high':
$query->numberRangeBuilder($values, 'civicrm_hrjobcontract_pay', 'hrjobcontract_pay_pay_per_cycle_gross', 'pay_per_cycle_gross', 'Pay Per Cycle Gross', NULL);
return;
case 'hrjobcontract_pay_pay_per_cycle_net':
case 'hrjobcontract_pay_pay_per_cycle_net_low':
case 'hrjobcontract_pay_pay_per_cycle_net_high':
$query->numberRangeBuilder($values, 'civicrm_hrjobcontract_pay', 'hrjobcontract_pay_pay_per_cycle_net', 'pay_per_cycle_net', 'Pay Per Cycle Net', NULL);
return;
case 'hrjobcontract_pension_ee_contrib_pct_low':
case 'hrjobcontract_pension_ee_contrib_pct_high':
$query->numberRangeBuilder($values, 'civicrm_hrjobcontract_pension', 'hrjobcontract_pension_ee_contrib_pct', 'ee_contrib_pct', 'Employee Contribution Percentage', NULL);
return;
case 'hrjobcontract_pension_er_contrib_pct_low':
case 'hrjobcontract_pension_er_contrib_pct_high':
$query->numberRangeBuilder($values, 'civicrm_hrjobcontract_pension', 'hrjobcontract_pension_er_contrib_pct', 'er_contrib_pct', 'Employer Contribution Percentage', NULL);
return;
case 'hrjobcontract_pension_ee_contrib_abs_low':
case 'hrjobcontract_pension_ee_contrib_abs_high':
$query->numberRangeBuilder($values, 'civicrm_hrjobcontract_pension', 'hrjobcontract_pension_ee_contrib_abs', 'ee_contrib_abs', 'Employee Contribution Absolute Amount', NULL);
return;
default:
if (!isset($fields[$name])) {
CRM_Core_Session::setStatus(ts('We did not recognize the search field: %1.', array(1 => $name)));
return;
}
$whereTable = $fields[$name];
$value = trim($value);
$dataType = "String";
if (in_array($name, array('hrjobcontract_details_position', 'hrjobcontract_details_title', 'hrjobcontract_details_funding_notes', 'hrjobcontract_health_description', 'hrjobcontract_health_dependents', 'hrjobcontract_health_description_life_insurance', 'hrjobcontract_health_dependents_life_insurance', 'hrjobcontract_pension_ee_evidence_note')) && strpos($value, '%') === FALSE) {
$op = 'LIKE';
$value = "%" . trim($value, '%') . "%";
$quoteValue = "\"{$value}\"";
}
$wc = $op != 'LIKE' ? "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;
}
}
/**
* 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;
case 'case_recent_activity_type':
$names = $value;
if ($activityType = CRM_Core_OptionGroup::getLabel('activity_type', $value, 'value')) {
$names = $activityType;
}
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.activity_type_id", $op, $value, 'Int');
$query->_qill[$grouping][] = ts("Activity Type %1 %2", array(1 => $op, 2 => $names));
$query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
$query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
$query->_tables['case_activity_type'] = 1;
$query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
return;
case 'case_activity_status_id':
$names = $value;
if ($activityStatus = CRM_Core_OptionGroup::getLabel('activity_status', $value, 'value')) {
$names = $activityStatus;
}
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.status_id", $op, $value, 'Int');
$query->_qill[$grouping][] = ts("Activity Type %1 %2", array(1 => $op, 2 => $names));
$query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
$query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
$query->_tables['case_activity_status'] = 1;
$query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
return;
case 'case_activity_duration':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.duration", $op, $value, 'Int');
$query->_qill[$grouping][] = ts("Activity Duration %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_activity_medium_id':
$names = $value;
if ($activityMedium = CRM_Core_OptionGroup::getLabel('encounter_medium', $value, 'value')) {
$names = $activityMedium;
}
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.medium_id", $op, $value, 'Int');
$query->_qill[$grouping][] = ts("Activity Medium %1 %2", array(1 => $op, 2 => $names));
$query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
$query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
$query->_tables['case_activity_medium'] = 1;
$query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
return;
case 'case_activity_details':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.details", $op, $value, 'String');
$query->_qill[$grouping][] = ts("Activity Details %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_activity_is_auto':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.is_auto", $op, $value, 'Boolean');
$query->_qill[$grouping][] = ts("Activity Auto Genrated %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;
// adding where clause for case_role
// adding where clause for case_role
case 'case_role':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_relation_type.name_b_a", $op, $value, 'String');
$query->_qill[$grouping][] = ts("Role in Case %1 '%2'", array(1 => $op, 2 => $value));
$query->_tables['case_relation_type'] = $query->_whereTables['case_relationship_type'] = 1;
$query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
$query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
return;
case 'case_from_start_date_low':
case 'case_from_start_date_high':
$query->dateQueryBuilder($values, 'civicrm_case', 'case_from_start_date', 'start_date', 'Start Date');
return;
case 'case_to_end_date_low':
case 'case_to_end_date_high':
$query->dateQueryBuilder($values, 'civicrm_case', 'case_to_end_date', 'end_date', 'End Date');
return;
case 'case_start_date':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.start_date", $op, $value, 'Int');
$query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
return;
case 'case_end_date':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.end_date", $op, $value, 'Int');
$query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
return;
case 'case_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 'case_tags':
$tags = CRM_Core_PseudoConstant::get('CRM_Core_DAO_EntityTag', 'tag_id', array('onlyActive' => FALSE));
if (is_array($value)) {
foreach ($value as $k => $v) {
if ($v) {
$val[$k] = $k;
$names[] = $tags[$k];
}
}
}
$query->_where[$grouping][] = " civicrm_case_tag.tag_id IN (" . implode(',', $val) . " )";
$query->_qill[$grouping][] = ts('Case Tags %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
$query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
$query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
$query->_tables['civicrm_case_tag'] = $query->_whereTables['civicrm_case_tag'] = 1;
return;
}
}
/**
* Build the form object.
*
*
* @return void
*/
public function buildQuickForm()
{
parent::buildQuickForm();
$this->addElement('text', 'sort_name', ts('Participant Name or Email'), CRM_Core_DAO::getAttribute('CRM_Contact_DAO_Contact', 'sort_name'));
CRM_Event_BAO_Query::buildSearchForm($this);
$rows = $this->get('rows');
if (is_array($rows)) {
$lineItems = $eventIds = array();
if (!$this->_single) {
$this->addRowSelectors($rows);
}
foreach ($rows as $row) {
$eventIds[$row['event_id']] = $row['event_id'];
if (CRM_Event_BAO_Event::usesPriceSet($row['event_id'])) {
// add line item details if applicable
$lineItems[$row['participant_id']] = CRM_Price_BAO_LineItem::getLineItems($row['participant_id']);
}
}
//get actual count only when we are dealing w/ single event.
$participantCount = 0;
if (count($eventIds) == 1) {
//convert form values to clause.
$seatClause = array();
if (CRM_Utils_Array::value('participant_test', $this->_formValues) == '1' || CRM_Utils_Array::value('participant_test', $this->_formValues) == '0') {
$seatClause[] = "( participant.is_test = {$this->_formValues['participant_test']} )";
}
if (!empty($this->_formValues['participant_status_id'])) {
$seatClause[] = CRM_Contact_BAO_Query::buildClause("participant.status_id", '=', $this->_formValues['participant_status_id'], 'Int');
if ($status = CRM_Utils_Array::value('IN', $this->_formValues['participant_status_id'])) {
$this->_formValues['participant_status_id'] = $status;
}
}
if (!empty($this->_formValues['participant_role_id'])) {
$seatClause[] = '( participant.role_id IN ( ' . implode(' , ', (array) $this->_formValues['participant_role_id']) . ' ) )';
}
// CRM-15379
if (!empty($this->_formValues['participant_fee_id'])) {
$participant_fee_id = $this->_formValues['participant_fee_id'];
$feeLabel = CRM_Core_DAO::getFieldValue('CRM_Price_DAO_PriceFieldValue', $participant_fee_id, 'label');
$feeLabel = CRM_Core_DAO::escapeString(trim($feeLabel));
$seatClause[] = "( participant.fee_level LIKE '%{$feeLabel}%' )";
}
$seatClause = implode(' AND ', $seatClause);
$participantCount = CRM_Event_BAO_Event::eventTotalSeats(array_pop($eventIds), $seatClause);
}
$this->assign('participantCount', $participantCount);
$this->assign('lineItems', $lineItems);
$permission = CRM_Core_Permission::getPermission();
$tasks = CRM_Event_Task::permissionedTaskTitles($permission);
if (isset($this->_ssID)) {
if ($permission == CRM_Core_Permission::EDIT) {
$tasks = $tasks + CRM_Event_Task::optionalTaskTitle();
}
$savedSearchValues = array('id' => $this->_ssID, 'name' => CRM_Contact_BAO_SavedSearch::getName($this->_ssID, 'title'));
$this->assign_by_ref('savedSearch', $savedSearchValues);
$this->assign('ssID', $this->_ssID);
}
$this->addTaskMenu($tasks);
}
}
/**
* 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));
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':
if (is_array($value)) {
$value = CRM_Utils_Array::value($op, $value, $value);
foreach ($value as $key => $val) {
$moneyFormat = CRM_Utils_Rule::cleanMoney($value[$key]);
$value[$key] = $moneyFormat;
}
} 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':
$fromValue = CRM_Utils_Array::value('from', $value);
$toValue = CRM_Utils_Array::value('to', $value);
if (!$fromValue && !$toValue) {
if (!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}";
}
$date = CRM_Utils_Date::processDate($value);
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $date, 'String');
$this->_qill[$grouping][] = $field['label'] . " {$qillOp} " . CRM_Utils_Date::customFormat($date);
} else {
if (is_numeric($fromValue) && strlen($fromValue) == 4) {
$fromValue = "01-01-{$fromValue}";
}
if (is_numeric($toValue) && strlen($toValue) == 4) {
$toValue = "01-01-{$toValue}";
}
// TO DO: add / remove time based on date parts
$fromDate = CRM_Utils_Date::processDate($fromValue);
$toDate = CRM_Utils_Date::processDate($toValue);
if (!$fromDate && !$toDate) {
continue;
}
if ($fromDate) {
$this->_where[$grouping][] = "{$fieldName} >= {$fromDate}";
$this->_qill[$grouping][] = $field['label'] . ' >= ' . CRM_Utils_Date::customFormat($fromDate);
}
if ($toDate) {
$this->_where[$grouping][] = "{$fieldName} <= {$toDate}";
$this->_qill[$grouping][] = $field['label'] . ' <= ' . CRM_Utils_Date::customFormat($toDate);
}
}
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;
}
}
}
}
static function whereClauseSingle(&$values, &$query)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
switch ($name) {
case 'event_start_date_low':
case 'event_start_date_high':
$query->dateQueryBuilder($values, 'civicrm_event', 'event_start_date', 'start_date', 'Start Date');
return;
case 'event_end_date_low':
case 'event_end_date_high':
$query->dateQueryBuilder($values, 'civicrm_event', 'event_end_date', 'end_date', 'End Date');
return;
case 'event_id':
$query->_where[$grouping][] = "civicrm_event.id {$op} {$value}";
$eventTitle = CRM_Core_DAO::getFieldValue('CRM_Event_DAO_Event', $value, 'title');
$query->_qill[$grouping][] = ts('Event') . " {$op} {$eventTitle}";
$query->_tables['civicrm_event'] = $query->_whereTables['civicrm_event'] = 1;
return;
case 'event_type_id':
require_once 'CRM/Core/OptionGroup.php';
require_once 'CRM/Utils/Array.php';
$eventTypes = CRM_Core_OptionGroup::values("event_type");
$query->_where[$grouping][] = "civicrm_participant.event_id = civicrm_event.id and civicrm_event.event_type_id = '{$value}'";
$query->_qill[$grouping][] = ts('Event Type - %1', array(1 => $eventTypes[$value]));
$query->_tables['civicrm_event'] = $query->_whereTables['civicrm_event'] = 1;
return;
case 'participant_test':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_participant.is_test", $op, $value, "Integer");
if ($value) {
$query->_qill[$grouping][] = ts("Find Test Participants");
}
$query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
return;
case 'participant_fee_id':
$feeLabel = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionValue', $value, 'label');
if ($value) {
$query->_where[$grouping][] = "civicrm_participant.fee_level {$op} '{$feeLabel}'";
$query->_qill[$grouping][] = ts("Fee level") . " {$op} {$feeLabel}";
}
$query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
return;
case 'participant_fee_amount':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_participant.fee_amount", $op, $value, "Money");
if ($value) {
$query->_qill[$grouping][] = ts("Fee Amount") . " {$op} {$value}";
}
$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_pay_later':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_participant.is_pay_later", $op, $value, "Integer");
if ($value) {
$query->_qill[$grouping][] = ts("Find Pay Later Participants");
}
$query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
return;
case 'participant_status_id':
$val = array();
if (is_array($value)) {
foreach ($value as $k => $v) {
if ($v) {
$val[$k] = $k;
}
}
$status = implode(',', $val);
} else {
$status = $value;
}
if (count($val) > 1) {
$op = 'IN';
$status = "({$status})";
}
require_once 'CRM/Event/PseudoConstant.php';
$statusTypes = CRM_Event_PseudoConstant::participantStatus();
$names = array();
if (!empty($val)) {
foreach ($val as $id => $dontCare) {
$names[] = $statusTypes[$id];
}
} else {
$names[] = $statusTypes[$value];
}
$query->_qill[$grouping][] = ts('Participant Status %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_participant.status_id", $op, $status, "Integer");
$query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
return;
case 'participant_role_id':
$val = array();
if (is_array($value)) {
foreach ($value as $k => $v) {
if ($v) {
$val[$k] = $k;
}
}
} else {
$value = array($value => 1);
}
require_once 'CRM/Event/PseudoConstant.php';
$roleTypes = CRM_Event_PseudoConstant::participantRole();
$names = array();
if (!empty($val)) {
foreach ($val as $id => $dontCare) {
$names[] = $roleTypes[$id];
}
} else {
$names[] = $roleTypes[$value];
}
$query->_qill[$grouping][] = ts('Participant Role %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
$query->_where[$grouping][] = " civicrm_participant.role_id REGEXP '[[:<:]]" . implode('[[:>:]]|[[:<:]]', array_keys($value)) . "[[:>:]]' ";
$query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
return;
case 'participant_source':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_participant.source", $op, $value, "String");
$query->_qill[$grouping][] = ts("Participant Source") . " {$op} {$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 'participant_id':
$query->_where[$grouping][] = "civicrm_participant.id {$op} {$value}";
$query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
return;
case 'event_id':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_event.id", $op, $value, "Integer");
$query->_tables['civicrm_event'] = $query->_whereTables['civicrm_event'] = 1;
$title = CRM_Core_DAO::getFieldValue('CRM_Event_DAO_Event', $value, "title");
$query->_qill[$grouping][] = ts('Event') . " {$op} {$value}";
return;
case 'participant_contact_id':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_participant.contact_id", $op, $value, "Integer");
$query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
return;
case 'event_is_public':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_event.is_public", $op, $value, "Integer");
$query->_tables['civicrm_event'] = $query->_whereTables['civicrm_event'] = 1;
return;
}
}
/**
* @param $values
* @param $query
*/
static function whereClauseSingle(&$values, &$query)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
$quoteValue = NULL;
$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_total_amount':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.total_amount", $op, $value, "Money");
$query->_qill[$grouping][] = ts('Contribution Total Amount %1 %2', array(1 => $op, 2 => $value));
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
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_id':
case 'financial_type':
// The financial_type_id might be an array (from aggregate contributions custom search)
// In this case, we need to change the query.
if (is_array($value)) {
$val = array();
// Rebuild the array to get the data we're interested in as array
// values not array keys.
foreach ($value as $k => $v) {
if ($v) {
$val[] = $k;
}
}
if (count($val) > 0) {
// Overwrite $value so it works with an IN where statement.
$op = 'IN';
$value = '(' . implode(',', $val) . ')';
} else {
// If we somehow have an empty array, just return
return;
}
}
$types = CRM_Contribute_PseudoConstant::financialType();
// Ensure we have a sensible string to display to the user.
$names = array();
if (isset($val) && is_array($val)) {
foreach ($val as $id) {
$names[] = CRM_Utils_Array::value($id, $types);
}
} else {
if (!empty($value)) {
$names[] = $types[$value];
}
}
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.financial_type_id", $op, $value, "Integer");
$query->_qill[$grouping][] = ts('Financial Type %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_page_id':
$cPage = $value;
$pages = CRM_Contribute_PseudoConstant::contributionPage();
$query->_where[$grouping][] = "civicrm_contribution.contribution_page_id = {$cPage}";
$query->_qill[$grouping][] = ts('Contribution Page - %1', array(1 => $pages[$cPage]));
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_pcp_made_through_id':
$pcPage = $value;
$pcpages = CRM_Contribute_PseudoConstant::pcPage();
$query->_where[$grouping][] = "civicrm_contribution_soft.pcp_id = {$pcPage}";
$query->_qill[$grouping][] = ts('Personal Campaign Page - %1', array(1 => $pcpages[$pcPage]));
$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;
} else {
if ($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;
} else {
if ($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_soft_credit_type_id':
$names = array();
$softCreditTypes = CRM_Core_OptionGroup::values("soft_credit_type");
if (is_array($value)) {
$val = array();
foreach ($value as $k => $v) {
if ($v) {
$val[$k] = $v;
$names[] = $softCreditTypes[$v];
}
}
$scTypes = count($val) > 0 ? implode(',', $val) : '';
if ($scTypes) {
$op = 'IN';
$scTypes = "({$scTypes})";
}
} else {
$scTypes = $value;
$names[] = $softCreditTypes[$value];
}
$query->_qill[$grouping][] = ts('Soft Credit Type %1', array(1 => $op)) . " '" . implode("' " . ts('or') . " '", $names) . "'";
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution_soft.soft_credit_type_id", $op, $scTypes, "Integer");
$query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
return;
case 'contribution_payment_instrument_id':
case 'contribution_payment_instrument':
$pi = array();
$pis = CRM_Contribute_PseudoConstant::paymentInstrument();
if (is_array($value)) {
foreach ($value as $k => $v) {
if ($v) {
$op = 'IN';
$pi[] = $pis[$v];
}
}
} else {
if (!empty($value)) {
$pi[] = $pis[$value];
}
}
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.payment_instrument_id", $op, $value, "Integer");
$query->_qill[$grouping][] = ts('Paid By - %1', array(1 => $op)) . " '" . implode("' " . ts('or') . " '", $pi) . "'";
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_status':
case 'contribution_status_id':
if (is_array($value)) {
foreach ($value as $k => $v) {
if ($v) {
$val[$k] = $k;
}
}
$status = implode(',', $val);
if (count($val) > 0) {
$op = 'IN';
$status = "({$status})";
}
} else {
$status = $value;
}
$statusValues = CRM_Core_OptionGroup::values("contribution_status");
$names = array();
if (isset($val) && is_array($val)) {
foreach ($val as $id => $dontCare) {
$names[] = $statusValues[$id];
}
} else {
if (!empty($value)) {
$names[] = $statusValues[$value];
}
}
$query->_qill[$grouping][] = ts('Contribution Status %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.contribution_status_id", $op, $status, "Integer");
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_source':
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = $op != 'LIKE' ? "LOWER(civicrm_contribution.source)" : "civicrm_contribution.source";
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, "String");
$query->_qill[$grouping][] = ts('Contribution Source %1 %2', array(1 => $op, 2 => $quoteValue));
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_trxn_id':
case 'contribution_transaction_id':
$wc = $op != 'LIKE' ? "LOWER(civicrm_contribution.trxn_id)" : "civicrm_contribution.trxn_id";
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, "String");
$query->_qill[$grouping][] = ts('Transaction ID %1 %2', array(1 => $op, 2 => $quoteValue));
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_check_number':
$wc = $op != 'LIKE' ? "LOWER(civicrm_contribution.check_number)" : "civicrm_contribution.check_number";
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, "String");
$query->_qill[$grouping][] = ts('Check Number %1 %2', array(1 => $op, 2 => $quoteValue));
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_is_test':
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_id':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.id", $op, $value, "Integer");
$query->_qill[$grouping][] = ts('Contribution ID %1 %2', array(1 => $op, 2 => $quoteValue));
$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;
// Supporting search for currency type -- CRM-4711
// Supporting search for currency type -- CRM-4711
case 'contribution_currency_type':
$currencySymbol = CRM_Core_PseudoConstant::get('CRM_Contribute_DAO_Contribution', 'currency', array('labelColumn' => 'name'));
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.currency", $op, $currencySymbol[$value], "String");
$query->_qill[$grouping][] = ts('Currency Type - %1', array(1 => $currencySymbol[$value]));
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 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);
//contribution fields (decimal fields) which don't require a quote in where clause.
$moneyFields = array('non_deductible_amount', 'fee_amount', 'net_amount');
//date fields
$dateFields = array('receive_date', 'cancel_date', 'receipt_date', 'thankyou_date', 'fulfilled_date');
if (in_array($fldName, $dateFields)) {
$dataType = "Date";
} elseif (in_array($fldName, $moneyFields)) {
$dataType = "Money";
} else {
$dataType = "String";
}
$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;
}
}
}
/**
* @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');
}
}
/**
* 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));
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;
}
}
}
}
static function buildWhereAndQill(&$query, $value, $pseudoconstantType, $op, $grouping, $params)
{
$matches = $val = $clause = array();
if (is_array($value)) {
foreach ($value as $k => $v) {
if ($k) {
$val[] = $k;
}
}
if (count($val) > 0) {
// Overwrite $op so it works with an IN where statement.
$op = 'IN';
} else {
// If we somehow have an empty array, just return
return;
}
$value = $matches[0] = $val;
} else {
preg_match_all('/\\d+/', $value, $matches);
}
foreach ($matches[0] as $qill) {
$clause[] = CRM_Utils_Array::value($qill, $pseudoconstantType);
}
$query->_qill[$grouping][] = ts($params[0] . ' %1 ', array(1 => $op)) . implode(' ' . ts('or') . ' ', $clause);
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($params[1], $op, $value, "Integer");
return $matches[0];
}
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_status_id':
if (is_array($value)) {
foreach ($value as $k => $v) {
if ($v) {
$val[$k] = $k;
}
}
$status = implode(',', $val);
if (count($val) > 1) {
$op = 'IN';
$status = "({$status})";
}
} else {
$op = '=';
$status = $value;
}
$statusValues = CRM_Core_OptionGroup::values('contribution_status');
$names = array();
if (is_array($val)) {
foreach ($val as $id => $dontCare) {
$names[] = $statusValues[$id];
}
} else {
$names[] = $statusValues[$value];
}
$query->_qill[$grouping][] = ts('Pledge Status %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_pledge.status_id', $op, $status, 'Integer');
$query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
return;
case 'pledge_payment_status_id':
if (is_array($value)) {
foreach ($value as $k => $v) {
if ($v) {
$val[$k] = $k;
}
}
$status = implode(',', $val);
if (count($val) > 1) {
$op = 'IN';
$status = "({$status})";
}
} else {
$op = '=';
$status = $value;
}
$statusValues = CRM_Core_OptionGroup::values('contribution_status');
$names = array();
if (is_array($val)) {
foreach ($val as $id => $dontCare) {
$names[] = $statusValues[$id];
}
} else {
$names[] = $statusValues[$value];
}
$query->_qill[$grouping][] = ts('Pledge Payment Status %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_pledge_payment.status_id', $op, $status, 'Integer');
$query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
return;
case 'pledge_test':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_pledge.is_test', $op, $value, 'Integer');
if ($value) {
$query->_qill[$grouping][] = ts('Find Test Pledges');
}
$query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
return;
case 'pledge_contribution_type_id':
$type = CRM_Contribute_PseudoConstant::contributionType($value);
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_pledge.contribution_type_id', $op, $value, 'Integer');
$query->_qill[$grouping][] = ts('Contribution 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('Contribution Page - %1', array(1 => $page));
$query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
return;
case 'pledge_in_honor_of':
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
$name = trim($value);
$newName = str_replace(',', " ", $name);
$pieces = explode(' ', $newName);
foreach ($pieces as $piece) {
$value = $strtolower(CRM_Core_DAO::escapeString(trim($piece)));
$value = "'%{$value}%'";
$sub[] = " ( pledge_contact_b.sort_name LIKE {$value} )";
}
$query->_where[$grouping][] = ' ( ' . implode(' OR ', $sub) . ' ) ';
$query->_qill[$grouping][] = ts('Honor name like - \'%1\'', array(1 => $name));
$query->_tables['pledge_contact_b'] = $query->_whereTables['pledge_contact_b'] = 1;
$query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
return;
case 'pledge_id':
$query->_where[$grouping][] = "civicrm_pledge.id {$op} {$value}";
$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;
}
}
/**
* @param $values
* @param $query
*/
public static function whereClauseSingle(&$values, &$query)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
$fields = array_merge(CRM_Event_BAO_Event::fields(), CRM_Event_BAO_Participant::exportableFields());
switch ($name) {
case 'event_start_date_low':
case 'event_start_date_high':
$query->dateQueryBuilder($values, 'civicrm_event', 'event_start_date', 'start_date', 'Start Date');
return;
case 'event_end_date_low':
case 'event_end_date_high':
$query->dateQueryBuilder($values, 'civicrm_event', 'event_end_date', 'end_date', 'End Date');
return;
case 'event_include_repeating_events':
/**
* Include Repeating Events
*/
//Get parent of this event
$exEventId = '';
if ($query->_where[$grouping]) {
foreach ($query->_where[$grouping] as $key => $val) {
if (strstr($val, 'civicrm_event.id =')) {
$exEventId = $val;
$extractEventId = explode(" ", $val);
$value = $extractEventId[2];
unset($query->_where[$grouping][$key]);
}
}
$extractEventId = explode(" ", $exEventId);
$value = $extractEventId[2];
unset($query->_where[$grouping][$key]);
}
$thisEventHasParent = CRM_Core_BAO_RecurringEntity::getParentFor($value, 'civicrm_event');
if ($thisEventHasParent) {
$getAllConnections = CRM_Core_BAO_RecurringEntity::getEntitiesForParent($thisEventHasParent, 'civicrm_event');
$allEventIds = array();
foreach ($getAllConnections as $key => $val) {
$allEventIds[] = $val['id'];
}
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;
}
}
/**
* 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 (is_array($value)) {
foreach ($value as $k => $v) {
$names[] = $activityTags[$v];
}
}
$query->_where[$grouping][] = "civicrm_activity_tag.tag_id IN (" . implode(",", $value) . ")";
$query->_qill[$grouping][] = ts('Activity Tag %1', array(1 => $op)) . ' ' . implode(' ' . ts('OR') . ' ', $names);
$query->_tables['civicrm_activity_tag'] = $query->_whereTables['civicrm_activity_tag'] = 1;
break;
case 'activity_campaign_id':
$campParams = array('op' => $op, 'campaign' => $value, 'grouping' => $grouping, 'tableName' => 'civicrm_activity');
CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query);
break;
case 'activity_result':
if (is_array($value)) {
$safe = NULL;
while (list(, $k) = each($value)) {
$safe[] = "'" . CRM_Utils_Type::escape($k, 'String') . "'";
}
$query->_where[$grouping][] = "civicrm_activity.result IN (" . implode(',', $safe) . ")";
$query->_qill[$grouping][] = ts("Activity Result - %1", array(1 => implode(' or ', $safe)));
}
break;
case 'parent_id':
if ($value == 1) {
$query->_where[$grouping][] = "parent_id.parent_id IS NOT NULL";
$query->_qill[$grouping][] = ts('Activities which have Followup Activities');
} elseif ($value == 2) {
$query->_where[$grouping][] = "parent_id.parent_id IS NULL";
$query->_qill[$grouping][] = ts('Activities without Followup Activities');
}
break;
case 'followup_parent_id':
if ($value == 1) {
$query->_where[$grouping][] = "civicrm_activity.parent_id IS NOT NULL";
$query->_qill[$grouping][] = ts('Activities which are Followup Activities');
} elseif ($value == 2) {
$query->_where[$grouping][] = "civicrm_activity.parent_id IS NULL";
$query->_qill[$grouping][] = ts('Activities which are not Followup Activities');
}
break;
}
}
/**
* generate the where clause and also the english language
* equivalent
*
* @param NULL
*
* @return void
*
* @access public
*/
function where()
{
//CRM_Core_Error::debug( 'fld', $this->_fields );
//CRM_Core_Error::debug( 'ids', $this->_ids );
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;
// fix $value here to escape sql injection attacks
$field = $this->_fields[$id];
$qillValue = CRM_Core_BAO_CustomField::getDisplayValue($value, $id, $this->_options);
if (!is_array($value)) {
$value = CRM_Core_DAO::escapeString(trim($value));
}
$fieldName = "{$field['table_name']}.{$field['column_name']}";
switch ($field['data_type']) {
case 'String':
$sql = "{$fieldName}";
// if we are coming in from listings,
// for checkboxes the value is already in the right format and is NOT an array
if (is_array($value)) {
//ignoring $op value for checkbox and multi select
$sqlValue = array();
$sqlOP = ' AND ';
$sqlOPlabel = ts('match ALL');
if ($field['html_type'] == 'CheckBox') {
foreach ($value as $k => $v) {
if ($v) {
if ($k == 'CiviCRM_OP_OR') {
$sqlOP = ' OR ';
$sqlOPlabel = ts('match ANY');
continue;
}
$sqlValue[] = "( {$sql} like '%" . CRM_Core_DAO::VALUE_SEPARATOR . $k . CRM_Core_DAO::VALUE_SEPARATOR . "%' ) ";
}
}
//if user check only 'CiviCRM_OP_OR' check box
//of custom checkbox field, then ignore this field.
if (!empty($sqlValue)) {
$this->_where[$grouping][] = ' ( ' . implode($sqlOP, $sqlValue) . ' ) ';
$this->_qill[$grouping][] = "{$field['label']} {$op} {$qillValue} ( {$sqlOPlabel} )";
}
// for multi select
} else {
foreach ($value as $k => $v) {
if ($v == 'CiviCRM_OP_OR') {
$sqlOP = ' OR ';
$sqlOPlabel = ts('match ANY');
continue;
}
$v = CRM_Core_DAO::escapeString($v);
$sqlValue[] = "( {$sql} like '%" . CRM_Core_DAO::VALUE_SEPARATOR . $v . CRM_Core_DAO::VALUE_SEPARATOR . "%' ) ";
}
//if user select only 'CiviCRM_OP_OR' value
//of custom multi select field, then ignore this field.
if (!empty($sqlValue)) {
$this->_where[$grouping][] = ' ( ' . implode($sqlOP, $sqlValue) . ' ) ';
$this->_qill[$grouping][] = "{$field['label']} {$op} {$qillValue} ( {$sqlOPlabel} )";
}
}
} else {
if ($field['is_search_range'] && is_array($value)) {
$this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping);
} else {
if ($field['html_type'] == 'Autocomplete-Select') {
$wildcard = FALSE;
$val = array_search($value, $this->_options[$field['id']]);
} elseif (in_array($field['html_type'], array('Select', 'Radio'))) {
$wildcard = FALSE;
$val = CRM_Utils_Type::escape($value, 'String');
} else {
$val = CRM_Utils_Type::escape($strtolower(trim($value)), 'String');
}
if ($wildcard) {
$val = $strtolower(CRM_Core_DAO::escapeString($val));
$val = "%{$val}%";
$op = 'LIKE';
}
//FIX for custom data query fired against no value(NULL/NOT NULL)
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($sql, $op, $val, $field['data_type']);
$this->_qill[$grouping][] = "{$field['label']} {$op} {$qillValue}";
}
}
continue;
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'] . " {$op} {$label}";
continue;
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'] . " {$op} {$value}";
}
continue;
case 'Boolean':
$value = (int) $value;
$value = $value == 1 ? 1 : 0;
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer');
$value = $value ? ts('Yes') : ts('No');
$this->_qill[$grouping][] = $field['label'] . " {$op} {$value}";
continue;
case 'Link':
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
$this->_qill[$grouping][] = $field['label'] . " {$op} {$value}";
continue;
case 'Float':
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, 'Float');
$this->_qill[$grouping][] = $field['label'] . " {$op} {$value}";
}
continue;
case 'Money':
if ($field['is_search_range'] && is_array($value)) {
foreach ($value as $key => $val) {
$moneyFormat = CRM_Utils_Rule::cleanMoney($value[$key]);
$value[$key] = $moneyFormat;
}
$this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping);
} else {
$moneyFormat = CRM_Utils_Rule::cleanMoney($value);
$value = $moneyFormat;
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Float');
$this->_qill[$grouping][] = $field['label'] . " {$op} {$value}";
}
continue;
case 'Memo':
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
$this->_qill[$grouping][] = "{$field['label']} {$op} {$value}";
continue;
case 'Date':
$fromValue = CRM_Utils_Array::value('from', $value);
$toValue = CRM_Utils_Array::value('to', $value);
if (!$fromValue && !$toValue) {
if (!CRM_Utils_Date::processDate($value) && $op != 'IS NULL' && $op != 'IS NOT NULL') {
continue;
}
// hack to handle yy format during search
if (is_numeric($value) && strlen($value) == 4) {
$value = "01-01-{$value}";
}
$date = CRM_Utils_Date::processDate($value);
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $date, 'String');
$this->_qill[$grouping][] = $field['label'] . " {$op} " . CRM_Utils_Date::customFormat($date);
} else {
if (is_numeric($fromValue) && strlen($fromValue) == 4) {
$fromValue = "01-01-{$fromValue}";
}
if (is_numeric($toValue) && strlen($toValue) == 4) {
$toValue = "01-01-{$toValue}";
}
// TO DO: add / remove time based on date parts
$fromDate = CRM_Utils_Date::processDate($fromValue);
$toDate = CRM_Utils_Date::processDate($toValue);
if (!$fromDate && !$toDate) {
continue;
}
if ($fromDate) {
$this->_where[$grouping][] = "{$fieldName} >= {$fromDate}";
$this->_qill[$grouping][] = $field['label'] . ' >= ' . CRM_Utils_Date::customFormat($fromDate);
}
if ($toDate) {
$this->_where[$grouping][] = "{$fieldName} <= {$toDate}";
$this->_qill[$grouping][] = $field['label'] . ' <= ' . CRM_Utils_Date::customFormat($toDate);
}
}
continue;
case 'StateProvince':
case 'Country':
if (!is_array($value)) {
$this->_where[$grouping][] = "{$fieldName} {$op} " . CRM_Utils_Type::escape($value, 'Int');
$this->_qill[$grouping][] = $field['label'] . " {$op} {$qillValue}";
} else {
$sqlOP = ' AND ';
$sqlOPlabel = ts('match ALL');
foreach ($value as $k => $v) {
if ($v == 'CiviCRM_OP_OR') {
$sqlOP = ' OR ';
$sqlOPlabel = ts('match ANY');
continue;
}
$sqlValue[] = "( {$fieldName} like '%" . CRM_Core_DAO::VALUE_SEPARATOR . $v . CRM_Core_DAO::VALUE_SEPARATOR . "%' ) ";
}
//if user select only 'CiviCRM_OP_OR' value
//of custom multi select field, then ignore this field.
if (!empty($sqlValue)) {
$this->_where[$grouping][] = " ( " . implode($sqlOP, $sqlValue) . " ) ";
$this->_qill[$grouping][] = "{$field['label']} {$op} {$qillValue} ( {$sqlOPlabel} )";
}
}
continue;
}
}
}
}
/**
* where clause for a single field
*
* @return void
* @access public
*/
static function whereClauseSingle(&$values, &$query)
{
require_once "CRM/Contact/BAO/Query.php";
list($name, $op, $value, $grouping, $wildcard) = $values;
switch ($name) {
case 'case_status_id':
require_once "CRM/Case/PseudoConstant.php";
$statuses = CRM_Case_PseudoConstant::caseStatus();
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.status_id", $op, $value, 'Int');
$value = $statuses[$value];
$query->_qill[$grouping][] = ts('Case Status %2 %1', array(1 => $value, 2 => $op));
$query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
return;
case 'case_type_id':
require_once 'CRM/Case/PseudoConstant.php';
$caseTypes = CRM_Case_PseudoConstant::caseType('label', false);
$names = array();
$val = array();
if (is_array($value)) {
foreach ($value as $k => $v) {
if ($v) {
$val[$k] = $k;
$names[] = $caseTypes[$k];
}
}
} else {
if (is_numeric($value)) {
$val[$value] = $value;
$names[] = $value;
} else {
if ($caseTypeId = CRM_Utils_Array::key($value, $caseTypes)) {
$val[$caseTypeId] = $caseTypeId;
$names[] = $caseTypes[$caseTypeId];
}
}
}
require_once 'CRM/Case/BAO/Case.php';
$value = CRM_Case_BAO_Case::VALUE_SEPERATOR . implode(CRM_Case_BAO_Case::VALUE_SEPERATOR . "%' OR civicrm_case.case_type_id LIKE '%" . CRM_Case_BAO_Case::VALUE_SEPERATOR, $val) . CRM_Case_BAO_Case::VALUE_SEPERATOR;
$query->_where[$grouping][] = "(civicrm_case.case_type_id LIKE '%{$value}%')";
$query->_qill[$grouping][] = ts('Case Type %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
$query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
return;
case 'case_id':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.id", $op, $value, 'Int');
$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;
} else {
if ($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_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_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;
case 'case_recent_activity_type':
$names = $value;
require_once "CRM/Core/OptionGroup.php";
if ($activityType = CRM_Core_OptionGroup::getLabel('activity_type', $value, 'value')) {
$names = $activityType;
}
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.activity_type_id", $op, $value, 'Int');
$query->_qill[$grouping][] = ts("Activity Type %1 %2", array(1 => $op, 2 => $names));
$query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
$query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
$query->_tables['case_activity_type'] = 1;
$query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
return;
case 'case_activity_status_id':
$names = $value;
require_once "CRM/Core/OptionGroup.php";
if ($activityStatus = CRM_Core_OptionGroup::getLabel('activity_status', $value, 'value')) {
$names = $activityStatus;
}
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.status_id", $op, $value, 'Int');
$query->_qill[$grouping][] = ts("Activity Type %1 %2", array(1 => $op, 2 => $names));
$query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
$query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
$query->_tables['case_activity_status'] = 1;
$query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
return;
case 'case_activity_duration':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.duration", $op, $value, 'Int');
$query->_qill[$grouping][] = ts("Activity Duration %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_activity_medium_id':
$names = $value;
require_once "CRM/Core/OptionGroup.php";
if ($activityMedium = CRM_Core_OptionGroup::getLabel('encounter_medium', $value, 'value')) {
$names = $activityMedium;
}
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.medium_id", $op, $value, 'Int');
$query->_qill[$grouping][] = ts("Activity Medium %1 %2", array(1 => $op, 2 => $names));
$query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
$query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
$query->_tables['case_activity_medium'] = 1;
$query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
return;
case 'case_activity_details':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.details", $op, $value, 'String');
$query->_qill[$grouping][] = ts("Activity Details %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_activity_is_auto':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.is_auto", $op, $value, 'Boolean');
$query->_qill[$grouping][] = ts("Activity Auto Genrated %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;
// adding where clause for case_role
// adding where clause for case_role
case 'case_role':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_relation_type.name_b_a", $op, $value, 'String');
$query->_qill[$grouping][] = ts("Role in Case %1 '%2'", array(1 => $op, 2 => $value));
$query->_tables['case_relation_type'] = $query->_whereTables['case_relationship_type'] = 1;
$query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
$query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
return;
case 'case_tags':
require_once 'CRM/Core/BAO/Tag.php';
$tags = CRM_Core_BAO_Tag::getTagsUsedFor(array('civicrm_case'), true);
$names = array();
$val = array();
if (is_array($value)) {
foreach ($value as $k => $v) {
if ($v) {
$val[$k] = $k;
$names[] = $tags[$k];
}
}
}
$query->_where[$grouping][] = " civicrm_case_tag.tag_id IN (" . implode(',', $val) . " )";
$query->_qill[$grouping][] = ts('Case Tags %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
$query->_tables['civicrm_case_tag'] = $query->_whereTables['civicrm_case_tag'] = 1;
return;
}
}
/**
* Build the campaign clause for component serach.
*
*/
public static function componentSearchClause(&$params, &$query)
{
$op = CRM_Utils_Array::value('op', $params, '=');
$campaign = CRM_Utils_Array::value('campaign', $params);
$tableName = CRM_Utils_Array::value('tableName', $params);
$grouping = CRM_Utils_Array::value('grouping', $params);
if (CRM_Utils_System::isNull($campaign) || empty($tableName)) {
return;
}
// fixme - what is the purpose of this code? $campaign should be
// an integer, not an array
if (is_array($campaign)) {
foreach (array('current_campaign', 'past_campaign') as $ignore) {
$index = array_search($ignore, $campaign);
if ($index !== FALSE) {
unset($campaign[$index]);
}
}
}
$allCampaigns = CRM_Campaign_BAO_Campaign::getCampaigns(NULL, NULL, FALSE, FALSE, FALSE, TRUE);
$campaignIds = $campaignTitles = array();
if (is_array($campaign)) {
foreach ($campaign as $campId) {
$campaignIds[$campId] = $campId;
$campaignTitles[$campId] = $allCampaigns[$campId];
}
} else {
$campaignIds = $campaign;
if (array_key_exists($campaignIds, $allCampaigns)) {
$campaignTitles[$campaignIds] = $allCampaigns[$campaignIds];
}
}
$query->_qill[$grouping][] = ts('Campaigns %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $campaignTitles);
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("{$tableName}.campaign_id", $op, $campaignIds, 'Integer');
$query->_tables[$tableName] = $query->_whereTables[$tableName] = 1;
}
/**
* @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");
} else {
$query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NOT NULL";
$query->_qill[$grouping][] = ts("Related Members Only");
}
$query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
return;
case 'member_campaign_id':
$campParams = array('op' => $op, 'campaign' => $value, 'grouping' => $grouping, 'tableName' => 'civicrm_membership');
CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query);
return;
}
}
/**
* Where / qill clause for contact sort_name
*
* @param $values
*
* @return void
*/
public function job(&$values, $contact)
{
list($fieldName, $op, $value, $grouping, $wildcard) = $values;
$value = trim($value);
if (substr($value, 0, 1) == '"' && substr($value, -1, 1) == '"') {
$op = '=';
$value = substr($value, 1, -1);
} else {
$op = 'LIKE';
}
$value = strtolower(CRM_Core_DAO::escapeString(trim($value))) . '%';
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($contact . '.job_title', $op, "{$value}", 'String');
$this->_qill[$grouping][] = ts('Job title is ') . $value;
}
static function whereClauseSingle(&$values, &$query)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
$fields = array();
$fields = self::getFields();
if (!empty($value)) {
$quoteValue = "\"{$value}\"";
}
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
switch ($name) {
case 'contribution_date':
case 'contribution_date_low':
case 'contribution_date_high':
// process to / from date
$query->dateQueryBuilder($values, 'civicrm_contribution', 'contribution_date', 'receive_date', 'Contribution Date', false);
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');
return;
case 'contribution_total_amount':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.total_amount", $op, $value, "Money");
$query->_qill[$grouping][] = ts('Contribution Total Amount %1 %2', array(1 => $op, 2 => $value));
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_thankyou_date_isnull':
$query->_where[$grouping][] = "civicrm_contribution.thankyou_date is null";
$query->_qill[$grouping][] = ts('Contribution Thank-you date is null');
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_receipt_date_isnull':
$query->_where[$grouping][] = "civicrm_contribution.receipt_date is null";
$query->_qill[$grouping][] = ts('Contribution Receipt date is null');
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_type_id':
case 'contribution_type':
require_once 'CRM/Contribute/PseudoConstant.php';
$cType = $value;
$types = CRM_Contribute_PseudoConstant::contributionType();
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.contribution_type_id", $op, $value, "Integer");
$query->_qill[$grouping][] = ts('Contribution Type - %1', array(1 => $types[$cType]));
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_page_id':
require_once 'CRM/Contribute/PseudoConstant.php';
$cPage = $value;
$pages = CRM_Contribute_PseudoConstant::contributionPage();
$query->_where[$grouping][] = "civicrm_contribution.contribution_page_id = {$cPage}";
$query->_qill[$grouping][] = ts('Contribution Page - %1', array(1 => $pages[$cPage]));
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_pcp_made_through_id':
require_once 'CRM/Contribute/PseudoConstant.php';
$pcPage = $value;
$pcpages = CRM_Contribute_PseudoConstant::pcPage();
$query->_where[$grouping][] = "civicrm_contribution_soft.pcp_id = {$pcPage}";
$query->_qill[$grouping][] = ts('Personal Campaign Page - %1', array(1 => $pcpages[$pcPage]));
$query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
return;
case 'contribution_payment_instrument_id':
case 'contribution_payment_instrument':
require_once 'CRM/Contribute/PseudoConstant.php';
$pi = $value;
$pis = CRM_Contribute_PseudoConstant::paymentInstrument();
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.payment_instrument_id", $op, $value, "Integer");
$query->_qill[$grouping][] = ts('Paid By - %1', array(1 => $pis[$pi]));
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_in_honor_of':
$name = trim($value);
$newName = str_replace(',', " ", $name);
$pieces = explode(' ', $newName);
foreach ($pieces as $piece) {
$value = $strtolower(CRM_Core_DAO::escapeString(trim($piece)));
$value = "'%{$value}%'";
$sub[] = " ( contact_b.sort_name LIKE {$value} )";
}
$query->_where[$grouping][] = ' ( ' . implode(' OR ', $sub) . ' ) ';
$query->_qill[$grouping][] = ts('Honor name like - \'%1\'', array(1 => $name));
$query->_tables['civicrm_contact_b'] = $query->_whereTables['civicrm_contact_b'] = 1;
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_status_id':
if (is_array($value)) {
foreach ($value as $k => $v) {
if ($v) {
$val[$k] = $k;
}
}
$status = implode(',', $val);
if (count($val) > 1) {
$op = 'IN';
$status = "({$status})";
}
} else {
$status = $value;
}
require_once "CRM/Core/OptionGroup.php";
$statusValues = CRM_Core_OptionGroup::values("contribution_status");
$names = array();
if (is_array($val)) {
foreach ($val as $id => $dontCare) {
$names[] = $statusValues[$id];
}
} else {
$names[] = $statusValues[$value];
}
$query->_qill[$grouping][] = ts('Contribution Status %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
$query->_where[$grouping][] = "civicrm_contribution.contribution_status_id {$op} {$status}";
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_source':
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = $op != 'LIKE' ? "LOWER(civicrm_contribution.source)" : "civicrm_contribution.source";
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, "String");
$query->_qill[$grouping][] = "Contribution Source {$op} {$quoteValue}";
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_trxn_id':
case 'contribution_transaction_id':
$wc = $op != 'LIKE' ? "LOWER(civicrm_contribution.trxn_id)" : "civicrm_contribution.trxn_id";
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, "String");
$query->_qill[$grouping][] = "Transaction ID {$op} {$quoteValue}";
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_check_number':
$wc = $op != 'LIKE' ? "LOWER(civicrm_contribution.check_number)" : "civicrm_contribution.check_number";
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, "String");
$query->_qill[$grouping][] = "Check Number {$op} {$quoteValue}";
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_is_test':
case 'contribution_test':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.is_test", $op, $value, "Boolean");
if ($value) {
$query->_qill[$grouping][] = "Find 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][] = "Find Pay Later Contributions";
}
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_recurring':
$query->_qill[$grouping][] = "Displaying Recurring Contributions";
$query->_tables['civicrm_contribution_recur'] = $query->_whereTables['civicrm_contribution_recur'] = 1;
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_id':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.id", $op, $value, "Integer");
$query->_qill[$grouping][] = "Contribution ID {$op} {$quoteValue}";
$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][] = "Contribution Note {$op} {$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][] = "Display in Roll";
}
$query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
return;
default:
//all other elements are handle in this case
$fldName = substr($name, 13);
$whereTable = $fields[$fldName];
$value = trim($value);
//contribution fields (decimal fields) which don't require a quote in where clause.
$moneyFields = array('non_deductible_amount', 'fee_amount', 'net_amount');
//date fields
$dateFields = array('receive_date', 'cancel_date', 'receipt_date', 'thankyou_date', 'fulfilled_date');
if (in_array($fldName, $dateFields)) {
$dataType = "Date";
} elseif (in_array($fldName, $moneyFields)) {
$dataType = "Money";
} else {
$dataType = "String";
}
$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;
}
}
}
/**
* where clause for a single field
*
* @return void
* @access public
*/
static function whereClauseSingle(&$values, &$query)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
$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':
$types = CRM_Core_PseudoConstant::activityType(true, true);
$clause = array();
if (is_array($value)) {
foreach ($value as $id => $dontCare) {
if (array_key_exists($id, $types) && $dontCare) {
$clause[] = "'" . CRM_Utils_Type::escape($types[$id], 'String') . "'";
}
}
$activityTypes = implode(',', array_keys($value));
} else {
$clause[] = "'" . CRM_Utils_Type::escape($value, 'String') . "'";
$activityTypes = $value;
}
$query->_where[$grouping][] = ' civicrm_activity.activity_type_id IN (' . $activityTypes . ')';
$query->_qill[$grouping][] = ts('Activity Type') . ' ' . implode(' ' . ts('or') . ' ', $clause);
break;
case 'activity_survey_id':
if (!$value) {
break;
}
$value = CRM_Utils_Type::escape($value, 'Integer');
$query->_where[$grouping][] = " 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];
//for activity target name
$activityTargetName = $query->getWhereValues('activity_contact_name', $grouping);
if (!$activityTargetName[2]) {
$name = null;
} else {
$name = trim($activityTargetName[2]);
$name = strtolower(CRM_Core_DAO::escapeString($name));
}
$query->_where[$grouping][] = " contact_b.is_deleted = 0 AND contact_b.sort_name LIKE '%{$name}%'";
if ($values[2] == 1) {
$query->_where[$grouping][] = " civicrm_activity.source_contact_id = contact_b.id";
$query->_qill[$grouping][] = ts('Activity created by') . " '{$name}'";
$query->_tables['civicrm_activity_contact'] = $query->_whereTables['civicrm_activity_contact'] = 1;
} else {
if ($values[2] == 2) {
$query->_where[$grouping][] = " civicrm_activity_assignment.activity_id = civicrm_activity.id AND civicrm_activity_assignment.assignee_contact_id = contact_b.id";
$query->_tables['civicrm_activity_assignment'] = $query->_whereTables['civicrm_activity_assignment'] = 1;
$query->_tables['civicrm_activity_contact'] = $query->_whereTables['civicrm_activity_contact'] = 1;
$query->_qill[$grouping][] = ts('Activity assigned to') . " '{$name}'";
}
}
break;
case 'activity_status':
$status = CRM_Core_PseudoConstant::activityStatus();
$clause = array();
if (is_array($value)) {
foreach ($value as $k => $v) {
if ($k) {
$clause[] = "'" . CRM_Utils_Type::escape($status[$k], 'String') . "'";
}
}
} else {
$clause[] = "'" . CRM_Utils_Type::escape($value, 'String') . "'";
}
$query->_where[$grouping][] = ' civicrm_activity.status_id IN (' . implode(',', array_keys($value)) . ')';
$query->_qill[$grouping][] = ts('Activity Status') . ' - ' . implode(' ' . ts('or') . ' ', $clause);
break;
case 'activity_subject':
$n = trim($value);
$value = strtolower(CRM_Core_DAO::escapeString($n));
if ($wildcard) {
if (strpos($value, '%') !== false) {
// only add wild card if not there
$value = "'{$value}'";
} else {
$value = "'%{$value}%'";
}
$op = 'LIKE';
} else {
$value = "'{$value}'";
}
$wc = $op != 'LIKE' ? "LOWER(civicrm_activity.subject)" : "civicrm_activity.subject";
$query->_where[$grouping][] = " {$wc} {$op} {$value}";
$query->_qill[$grouping][] = ts('Subject') . " {$op} - '{$n}'";
break;
case 'activity_test':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_activity.is_test", $op, $value, "Integer");
if ($value) {
$query->_qill[$grouping][] = ts('Find Test Activities');
}
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_tags':
require_once 'CRM/Core/BAO/Tag.php';
$value = array_keys($value);
$activityTags = CRM_Core_BAO_Tag::getTagsUsedFor('civicrm_activity');
$names = array();
$val = array();
if (is_array($value)) {
foreach ($value as $k => $v) {
$names[] = $activityTags[$v];
}
}
$query->_where[$grouping][] = "civicrm_activity_tag.tag_id IN (" . implode(",", $value) . ")";
$query->_qill[$grouping][] = ts('Activity Tag %1', array(1 => $op)) . ' ' . implode(' ' . ts('OR') . ' ', $names);
$query->_tables['civicrm_activity_tag'] = $query->_whereTables['civicrm_activity_tag'] = 1;
break;
}
}
/**
* Build the form object.
*
*
* @return void
*/
public function buildQuickForm()
{
parent::buildQuickForm();
$this->addSortNameField();
if (CRM_Core_Permission::check('access deleted contacts') and Civi::settings()->get('contact_undelete')) {
$this->addElement('checkbox', 'deleted_contacts', ts('Search in Trash') . '<br />' . ts('(deleted contacts)'));
}
CRM_Event_BAO_Query::buildSearchForm($this);
$rows = $this->get('rows');
if (is_array($rows)) {
$lineItems = $eventIds = array();
if (!$this->_single) {
$this->addRowSelectors($rows);
}
foreach ($rows as $row) {
$eventIds[$row['event_id']] = $row['event_id'];
if (CRM_Event_BAO_Event::usesPriceSet($row['event_id'])) {
// add line item details if applicable
$lineItems[$row['participant_id']] = CRM_Price_BAO_LineItem::getLineItems($row['participant_id']);
}
}
//get actual count only when we are dealing w/ single event.
$participantCount = 0;
if (count($eventIds) == 1) {
//convert form values to clause.
$seatClause = array();
if (CRM_Utils_Array::value('participant_test', $this->_formValues) == '1' || CRM_Utils_Array::value('participant_test', $this->_formValues) == '0') {
$seatClause[] = "( participant.is_test = {$this->_formValues['participant_test']} )";
}
if (!empty($this->_formValues['participant_status_id'])) {
$seatClause[] = CRM_Contact_BAO_Query::buildClause("participant.status_id", '=', $this->_formValues['participant_status_id'], 'Int');
if ($status = CRM_Utils_Array::value('IN', $this->_formValues['participant_status_id'])) {
$this->_formValues['participant_status_id'] = $status;
}
}
if (!empty($this->_formValues['participant_role_id'])) {
$escapedRoles = array();
foreach ((array) $this->_formValues['participant_role_id'] as $participantRole) {
$escapedRoles[] = CRM_Utils_Type::escape($participantRole, 'String');
}
$seatClause[] = "( participant.role_id IN ( '" . implode("' , '", $escapedRoles) . "' ) )";
}
// CRM-15379
if (!empty($this->_formValues['participant_fee_id'])) {
$participant_fee_id = $this->_formValues['participant_fee_id'];
foreach ($participant_fee_id as $k => &$val) {
$val = CRM_Core_DAO::getFieldValue('CRM_Price_DAO_PriceFieldValue', $val, 'label');
$val = CRM_Core_DAO::escapeString(trim($val));
}
$feeLabel = implode('|', $participant_fee_id);
$seatClause[] = "( participant.fee_level REGEXP '{$feeLabel}' )";
}
$seatClause = implode(' AND ', $seatClause);
$participantCount = CRM_Event_BAO_Event::eventTotalSeats(array_pop($eventIds), $seatClause);
}
$this->assign('participantCount', $participantCount);
$this->assign('lineItems', $lineItems);
$permission = CRM_Core_Permission::getPermission();
$tasks = CRM_Event_Task::permissionedTaskTitles($permission);
if (isset($this->_ssID)) {
if ($permission == CRM_Core_Permission::EDIT) {
$tasks = $tasks + CRM_Event_Task::optionalTaskTitle();
}
$savedSearchValues = array('id' => $this->_ssID, 'name' => CRM_Contact_BAO_SavedSearch::getName($this->_ssID, 'title'));
$this->assign_by_ref('savedSearch', $savedSearchValues);
$this->assign('ssID', $this->_ssID);
}
$this->addTaskMenu($tasks);
}
}
/**
* @param $values
* @param $query
*/
public static function whereClauseSingle(&$values, &$query)
{
//get survey clause in force,
//only when we have survey id.
if (!self::$_applySurveyClause) {
return;
}
list($name, $op, $value, $grouping, $wildcard) = $values;
switch ($name) {
case 'campaign_survey_id':
$query->_qill[$grouping][] = ts('Survey - %1', array(1 => CRM_Core_DAO::getFieldValue('CRM_Campaign_DAO_Survey', $value, 'title')));
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_activity.source_record_id', $op, $value, 'Integer');
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_survey.id', $op, $value, 'Integer');
return;
case 'survey_status_id':
$activityStatus = CRM_Core_PseudoConstant::activityStatus();
$query->_qill[$grouping][] = ts('Survey Status - %1', array(1 => $activityStatus[$value]));
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_activity.status_id', $op, $value, 'Integer');
return;
case 'campaign_search_voter_for':
if (in_array($value, array('release', 'interview'))) {
$query->_where[$grouping][] = '(civicrm_activity.is_deleted = 0 OR civicrm_activity.is_deleted IS NULL)';
}
return;
case 'survey_interviewer_id':
$surveyInterviewerName = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name');
$query->_qill[$grouping][] = ts('Survey Interviewer - %1', array(1 => $surveyInterviewerName));
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_activity_assignment.contact_id', $op, $value, 'Integer');
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;
}
}
/**
* generate the where clause and also the english language
* equivalent
*
* @param NULL
*
* @return void
*
* @access 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);
}
// Handle multi-select search for any data type
if (is_array($value) && !$field['is_search_range']) {
$isSerialized = CRM_Core_BAO_CustomField::isSerialized($field);
$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']} {$op} {$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);
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 {
$val = CRM_Utils_Type::escape($strtolower(trim($value)), 'String');
if ($wildcard) {
$val = $strtolower(CRM_Core_DAO::escapeString($val));
$val = "%{$val}%";
$op = 'LIKE';
}
//FIX for custom data query fired against no value(NULL/NOT NULL)
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($sql, $op, $val, $field['data_type']);
$this->_qill[$grouping][] = "{$field['label']} {$op} {$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'] . " {$op} {$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'] . " {$op} {$value}";
}
break;
case 'Boolean':
if (strtolower($value) == 'yes' || strtolower($value) == strtolower(ts('Yes'))) {
$value = 1;
} else {
$value = (int) $value;
}
$value = $value == 1 ? 1 : 0;
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer');
$value = $value ? ts('Yes') : ts('No');
$this->_qill[$grouping][] = $field['label'] . " {$op} {$value}";
break;
case 'Link':
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
$this->_qill[$grouping][] = $field['label'] . " {$op} {$value}";
break;
case 'Float':
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, 'Float');
$this->_qill[$grouping][] = $field['label'] . " {$op} {$value}";
}
break;
case 'Money':
if ($field['is_search_range'] && is_array($value)) {
foreach ($value as $key => $val) {
$moneyFormat = CRM_Utils_Rule::cleanMoney($value[$key]);
$value[$key] = $moneyFormat;
}
$this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping);
} else {
$moneyFormat = CRM_Utils_Rule::cleanMoney($value);
$value = $moneyFormat;
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Float');
$this->_qill[$grouping][] = $field['label'] . " {$op} {$value}";
}
break;
case 'Memo':
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
$this->_qill[$grouping][] = "{$field['label']} {$op} {$value}";
break;
case 'Date':
$fromValue = CRM_Utils_Array::value('from', $value);
$toValue = CRM_Utils_Array::value('to', $value);
if (!$fromValue && !$toValue) {
if (!CRM_Utils_Date::processDate($value) && $op != 'IS NULL' && $op != 'IS NOT NULL') {
continue;
}
// hack to handle yy format during search
if (is_numeric($value) && strlen($value) == 4) {
$value = "01-01-{$value}";
}
$date = CRM_Utils_Date::processDate($value);
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $date, 'String');
$this->_qill[$grouping][] = $field['label'] . " {$op} " . CRM_Utils_Date::customFormat($date);
} else {
if (is_numeric($fromValue) && strlen($fromValue) == 4) {
$fromValue = "01-01-{$fromValue}";
}
if (is_numeric($toValue) && strlen($toValue) == 4) {
$toValue = "01-01-{$toValue}";
}
// TO DO: add / remove time based on date parts
$fromDate = CRM_Utils_Date::processDate($fromValue);
$toDate = CRM_Utils_Date::processDate($toValue);
if (!$fromDate && !$toDate) {
continue;
}
if ($fromDate) {
$this->_where[$grouping][] = "{$fieldName} >= {$fromDate}";
$this->_qill[$grouping][] = $field['label'] . ' >= ' . CRM_Utils_Date::customFormat($fromDate);
}
if ($toDate) {
$this->_where[$grouping][] = "{$fieldName} <= {$toDate}";
$this->_qill[$grouping][] = $field['label'] . ' <= ' . CRM_Utils_Date::customFormat($toDate);
}
}
break;
case 'StateProvince':
case 'Country':
$this->_where[$grouping][] = "{$fieldName} {$op} " . CRM_Utils_Type::escape($value, 'Int');
$this->_qill[$grouping][] = $field['label'] . " {$op} {$qillValue}";
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'] . " {$op} ";
}
break;
}
}
}
}
/**
* @param $values
* @param $query
*/
public static function whereClauseSingle(&$values, &$query)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
$fields = array();
$fields = self::getFields();
switch ($name) {
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;
}
}
/**
* Get current/future Events
*
* @param int $all
* 0 returns current and future events.
* 1 if events all are required
* 2 returns events since 3 months ago
* @param int|array $id single int event id or array of multiple event ids to return
* @param bool $isActive
* true if you need only active events.
* @param bool $checkPermission
* true if you need to check permission else false.
* @param bool $titleOnly
* true if you need only title not appended with start date
*
* @return array
*/
public static function getEvents($all = 0, $id = NULL, $isActive = TRUE, $checkPermission = TRUE, $titleOnly = FALSE)
{
$query = "\nSELECT `id`, `title`, `start_date`\nFROM `civicrm_event`\nWHERE ( civicrm_event.is_template IS NULL OR civicrm_event.is_template = 0 )";
if (!empty($id)) {
$op = is_array($id) ? 'IN' : '=';
$where = CRM_Contact_BAO_Query::buildClause('id', $op, $id);
$query .= " AND {$where}";
} elseif ($all == 0) {
// find only events ending in the future
$endDate = date('YmdHis');
$query .= "\n AND ( `end_date` >= {$endDate} OR\n (\n ( end_date IS NULL OR end_date = '' ) AND start_date >= {$endDate}\n )\n )";
} elseif ($all == 2) {
// find only events starting in the last 3 months
$startDate = date('YmdHis', strtotime('3 months ago'));
$query .= " AND ( `start_date` >= {$startDate} OR start_date IS NULL )";
}
if ($isActive) {
$query .= " AND civicrm_event.is_active = 1";
}
$query .= " ORDER BY title asc";
$events = array();
$dao = CRM_Core_DAO::executeQuery($query);
while ($dao->fetch()) {
if ((!$checkPermission || CRM_Event_BAO_Event::checkPermission($dao->id)) && $dao->title) {
$events[$dao->id] = $dao->title;
if (!$titleOnly) {
$events[$dao->id] .= ' - ' . CRM_Utils_Date::customFormat($dao->start_date);
}
}
}
return $events;
}
/**
* @param $values
* @param $query
*/
public static function whereClauseSingle(&$values, &$query)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
$fields = array_merge(CRM_Event_BAO_Event::fields(), CRM_Event_BAO_Participant::exportableFields());
switch ($name) {
case 'event_start_date_low':
case 'event_start_date_high':
$query->dateQueryBuilder($values, 'civicrm_event', 'event_start_date', 'start_date', 'Start Date');
return;
case 'event_end_date_low':
case 'event_end_date_high':
$query->dateQueryBuilder($values, 'civicrm_event', 'event_end_date', 'end_date', 'End Date');
return;
case 'event_include_repeating_events':
/**
* Include Repeating Events
*/
//Get parent of this event
$exEventId = '';
if ($query->_where[$grouping]) {
foreach ($query->_where[$grouping] as $key => $val) {
if (strstr($val, 'civicrm_event.id =')) {
$exEventId = $val;
$extractEventId = explode(" ", $val);
$value = $extractEventId[2];
$where = $query->_where[$grouping][$key];
} else {
if (strstr($val, 'civicrm_event.id IN')) {
//extract the first event id if multiple events are selected
preg_match('/civicrm_event.id IN \\(\\"(\\d+)/', $val, $matches);
$value = $matches[1];
$where = $query->_where[$grouping][$key];
}
}
}
if ($exEventId) {
$extractEventId = explode(" ", $exEventId);
$value = $extractEventId[2];
} else {
if (!empty($matches[1])) {
$value = $matches[1];
}
}
$where = $query->_where[$grouping][$key];
}
$thisEventHasParent = CRM_Core_BAO_RecurringEntity::getParentFor($value, 'civicrm_event');
if ($thisEventHasParent) {
$getAllConnections = CRM_Core_BAO_RecurringEntity::getEntitiesForParent($thisEventHasParent, 'civicrm_event');
$allEventIds = array();
foreach ($getAllConnections as $key => $val) {
$allEventIds[] = $val['id'];
}
if (!empty($allEventIds)) {
$op = "IN";
$value = "(" . implode(",", $allEventIds) . ")";
}
}
$query->_where[$grouping][] = "{$where} OR 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");
$isTest = $value ? 'a Test' : 'not a Test';
$query->_qill[$grouping][] = ts("Participant is %1", array(1 => $isTest));
$query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
}
return;
case 'participant_fee_id':
foreach ($value as $k => &$val) {
$val = CRM_Core_DAO::getFieldValue('CRM_Price_DAO_PriceFieldValue', $val, 'label');
$val = CRM_Core_DAO::escapeString(trim($val));
}
$feeLabel = implode('|', $value);
$query->_where[$grouping][] = "civicrm_participant.fee_level REGEXP '{$feeLabel}'";
$query->_qill[$grouping][] = ts("Fee level") . " IN " . implode(', ', $value);
$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':
if ($value && is_array($value) && strpos($op, 'IN') === FALSE) {
$op = 'IN';
}
case 'participant_status':
case 'participant_source':
case 'participant_id':
case 'participant_contact_id':
case 'participant_is_pay_later':
case 'participant_fee_amount':
case 'participant_fee_level':
case 'participant_campaign_id':
$qillName = $name;
if (in_array($name, array('participant_status_id', 'participant_source', 'participant_id', 'participant_contact_id', 'participant_fee_amount', 'participant_fee_level', 'participant_is_pay_later', 'participant_campaign_id'))) {
$name = str_replace('participant_', '', $name);
if ($name == 'is_pay_later') {
$qillName = $name;
}
} elseif ($name == 'participant_status') {
$name = 'status_id';
}
$dataType = !empty($fields[$qillName]['type']) ? CRM_Utils_Type::typeToString($fields[$qillName]['type']) : 'String';
$tableName = empty($tableName) ? 'civicrm_participant' : $tableName;
if (is_array($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$op = key($value);
$value = $value[$op];
}
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("{$tableName}.{$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_role':
case 'participant_role_id':
$qillName = $name;
$name = 'role_id';
if (is_array($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$op = key($value);
$value = $value[$op];
}
if (!strstr($op, 'NULL') && !strstr($op, 'EMPTY') && !strstr($op, 'LIKE')) {
$regexOp = strstr($op, '!') || strstr($op, 'NOT') ? 'NOT REGEXP' : 'REGEXP';
$regexp = "[[:cntrl:]]*" . implode('[[:>:]]*|[[:<:]]*', (array) $value) . "[[:cntrl:]]*";
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_participant.{$name}", $regexOp, $regexp, 'String');
} else {
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("{$tableName}.{$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':
case 'participant_register_date_high':
case 'participant_register_date_low':
$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;
}
}
/**
* @param $values
* @param $query
*/
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;
case 'membership_status':
case 'member_status_id':
if (!is_array($value)) {
$status = $value;
if (!empty($value)) {
$value = array_flip(explode(",", str_replace(array('(', ')'), '', $value)));
} else {
$value = array();
}
} else {
$status = implode(',', array_keys($value));
if (count($value) > 0) {
$op = 'IN';
$status = "({$status})";
}
}
$names = array();
$statusTypes = CRM_Member_PseudoConstant::membershipStatus(NULL, NULL, 'label');
foreach ($value as $id => $dontCare) {
$names[] = $statusTypes[$id];
}
$query->_qill[$grouping][] = ts('Membership Status %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.status_id", $op, $status, "Integer");
$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, CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionValue', 'Cancelled', 'value', '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 'membership_type':
case 'member_membership_type_id':
if (!is_array($value)) {
$mType = $value;
if (!empty($value)) {
$value = array_flip(explode(",", str_replace(array('(', ')'), '', $value)));
} else {
$value = array();
}
} else {
$mType = implode(',', array_keys($value));
if (count($value) > 0) {
$op = 'IN';
$mType = "({$mType})";
}
}
$names = array();
$membershipTypes = CRM_Member_PseudoConstant::membershipType();
foreach ($value as $id => $dontCare) {
if (!empty($membershipTypes[$id])) {
$names[] = $membershipTypes[$id];
}
}
$query->_qill[$grouping][] = ts('Membership Type %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.membership_type_id", $op, $mType, "Integer");
$query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
return;
case 'member_id':
$query->_where[$grouping][] = " civicrm_membership.id {$op} {$value}";
$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");
} else {
$query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NOT NULL";
$query->_qill[$grouping][] = ts("Related Members Only");
}
$query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
return;
case 'member_campaign_id':
$campParams = array('op' => $op, 'campaign' => $value, 'grouping' => $grouping, 'tableName' => 'civicrm_membership');
CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query);
return;
}
}
