/** * 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_status': case 'membership_status_id': case 'membership_type': case 'membership_type_id': case 'member_id': case 'member_campaign_id': if (strpos($name, 'status') !== FALSE) { $name = 'status_id'; $qillName = 'Membership Status(s)'; } elseif ($name == 'member_id') { $name = 'id'; $qillName = 'Membership ID'; } elseif ($name == 'member_campaign_id') { $name = 'campaign_id'; $qillName = 'Campaign'; } 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; } }
/** * 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; } }
/** * Where clause for a single field. * * @param array $values * @param CRM_Contact_BAO_Query $query */ 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': case 'activity_campaign_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); } if ($name == 'activity_campaign_id') { $name = 'campaign_id'; } $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_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; } }