}
$result = array($id, 'IN', $values, 0, 0);
}
- elseif ($id == 'contact_type') {
+ elseif ($id == 'contact_type' ||
+ (!empty($values) && is_array($values) && !in_array(key($values), CRM_Core_DAO::acceptedSQLOperators(), TRUE))
+ ) {
$result = array($id, 'IN', $values, 0, $wildcard);
}
else {
}
// check for both id and contact_id
if ($this->_params[$id][0] == 'id' || $this->_params[$id][0] == 'contact_id') {
- if (
- $this->_params[$id][1] == 'IS NULL' ||
- $this->_params[$id][1] == 'IS NOT NULL'
- ) {
- $this->_where[0][] = "contact_a.id {$this->_params[$id][1]}";
- }
- elseif (is_array($this->_params[$id][2])) {
- $idList = implode("','", $this->_params[$id][2]);
- //why on earth do they put ' in the middle & not on the outside? We have to assume it's
- //to support 'something' so lets add them conditionally to support the api (which is a tested flow
- // so if you are looking to alter this check api test results
- if (strpos(trim($idList), "'") > 0) {
- $idList = "'" . $idList . "'";
- }
-
- $this->_where[0][] = "contact_a.id IN ({$idList})";
- }
- else {
- $this->_where[0][] = self::buildClause("contact_a.id", "{$this->_params[$id][1]}", "{$this->_params[$id][2]}");
- }
+ $this->_where[0][] = self::buildClause("contact_a.id", $this->_params[$id][1], $this->_params[$id][2]);
}
else {
$this->whereClauseSingle($this->_params[$id]);
);
}
- // add group_contact table if group table is present
- if (!empty($tables['civicrm_group']) && empty($tables['civicrm_group_contact'])) {
- $tables['civicrm_group_contact'] = " LEFT JOIN civicrm_group_contact ON civicrm_group_contact.contact_id = contact_a.id AND civicrm_group_contact.status = 'Added'";
+ // add group_contact and group_contact_cache table if group table is present
+ if (!empty($tables['civicrm_group'])) {
+ if (empty($tables['civicrm_group_contact'])) {
+ $tables['civicrm_group_contact'] = " LEFT JOIN civicrm_group_contact ON civicrm_group_contact.contact_id = contact_a.id AND civicrm_group_contact.status = 'Added' ";
+ }
+ if (empty($tables['civicrm_group_contact_cache'])) {
+ $tables['civicrm_group_contact_cache'] = " LEFT JOIN civicrm_group_contact_cache ON civicrm_group_contact_cache.contact_id = contact_a.id ";
+ }
}
// add group_contact and group table is subscription history is present
continue;
case 'civicrm_group':
- $from .= " $side JOIN civicrm_group ON civicrm_group.id = civicrm_group_contact.group_id ";
+ $from .= " $side JOIN civicrm_group ON (civicrm_group.id = civicrm_group_contact.group_id OR civicrm_group.id = civicrm_group_contact_cache.group_id) ";
continue;
case 'civicrm_group_contact':
$from .= " $side JOIN civicrm_group_contact ON contact_a.id = civicrm_group_contact.contact_id ";
continue;
+ case 'civicrm_group_contact_cache':
+ $from .= " $side JOIN civicrm_group_contact_cache ON contact_a.id = civicrm_group_contact_cache.contact_id ";
+ continue;
+
case 'civicrm_activity':
case 'civicrm_activity_tag':
case 'activity_type':
public function group(&$values) {
list($name, $op, $value, $grouping, $wildcard) = $values;
+ // Replace pseudo operators from search builder
+ $op = str_replace('EMPTY', 'NULL', $op);
+
if (count($value) > 1) {
+ if (strpos($op, 'IN') === FALSE && strpos($op, 'NULL') === FALSE) {
+ CRM_Core_Error::fatal(ts("%1 is not a valid operator", array(1 => $op)));
+ }
$this->_useDistinct = TRUE;
}
- // Replace pseudo operators from search builder
- $op = str_replace('EMPTY', 'NULL', $op);
-
- $groupNames = CRM_Core_PseudoConstant::group();
- $groupIds = '';
+ $groupIds = NULL;
$names = array();
+ $isSmart = FALSE;
+ $isNotOp = ($op == 'NOT IN' || $op == '!=');
if ($value) {
- $groupIds = implode(',', array_keys($value));
- foreach ($value as $id => $dontCare) {
- if (array_key_exists($id, $groupNames) && $dontCare) {
- $names[] = $groupNames[$id];
- }
+ if (strpos($op, 'IN') === FALSE) {
+ $value = key($value);
+ }
+ else {
+ $value = array_keys($value);
}
}
$statii = array();
- $in = FALSE;
$gcsValues = &$this->getWhereValues('group_contact_status', $grouping);
if ($gcsValues &&
is_array($gcsValues[2])
) {
foreach ($gcsValues[2] as $k => $v) {
if ($v) {
- if ($k == 'Added') {
- $in = TRUE;
- }
$statii[] = "'" . CRM_Utils_Type::escape($k, 'String') . "'";
}
}
}
else {
$statii[] = '"Added"';
- $in = TRUE;
}
$skipGroup = FALSE;
- if (count($value) == 1 &&
+ if (!is_array($value) &&
count($statii) == 1 &&
- $statii[0] == '"Added"'
+ $statii[0] == '"Added"' &&
+ !$isNotOp
) {
- // check if smart group, if so we can get rid of that one additional
- // left join
- $groupIDs = array_keys($value);
-
- if (!empty($groupIDs[0]) &&
- CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Group',
- $groupIDs[0],
- 'saved_search_id'
- )
- ) {
- $skipGroup = TRUE;
+ if (!empty($value) && CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Group', $value, 'saved_search_id')) {
+ $isSmart = TRUE;
}
}
- if (!$skipGroup) {
+ $ssClause = $this->addGroupContactCache($value, NULL, "contact_a", $op);
+ $isSmart = (!$ssClause) ? FALSE : $isSmart;
+ $groupClause = NULL;
+
+ if (!$isSmart) {
+ $groupIds = implode(',', (array) $value);
$gcTable = "`civicrm_group_contact-{$groupIds}`";
$joinClause = array("contact_a.id = {$gcTable}.contact_id");
- if ($groupIds && ($op == 'IN' || $op == 'NOT IN')) {
- $joinClause[] = "{$gcTable}.group_id IN ( $groupIds )";
- }
- // For NOT IN we join on groups the contact IS in, then exclude them in the where clause
- if ($statii && $op == 'NOT IN') {
+ if ($statii) {
$joinClause[] = "{$gcTable}.status IN (" . implode(', ', $statii) . ")";
}
$this->_tables[$gcTable] = $this->_whereTables[$gcTable] = " LEFT JOIN civicrm_group_contact {$gcTable} ON (" . implode(' AND ', $joinClause) . ")";
+ $groupClause = "{$gcTable}.group_id $op $groupIds";
+ if (strpos($op, 'IN') !== FALSE) {
+ $groupClause = "{$gcTable}.group_id $op ( $groupIds )";
+ }
}
- if ($op == 'IN') {
- $qill = ts('In group');
- }
- else {
- $qill = ts('Groups %1', array(1 => $op));
- }
- $qill .= ' ' . implode(' ' . ts('or') . ' ', $names);
-
- $groupClause = NULL;
-
- if (!$skipGroup) {
- $groupClause = "{$gcTable}.group_id $op" . ($groupIds ? " ( $groupIds ) " : '');
- if (!empty($statii) && $groupIds) {
- $groupClause .= " AND {$gcTable}.status IN (" . implode(', ', $statii) . ")";
- $qill .= " " . ($op == 'NOT IN' ? ts('WHERE') : ts('AND')) . " " . ts('Group Status') . ' - ' . implode(' ' . ts('or') . ' ', $statii);
+ if ($ssClause) {
+ $and = ($op == 'IS NULL') ? 'AND' : 'OR';
+ if ($groupClause) {
+ $groupClause = "( ( $groupClause ) $and ( $ssClause ) )";
}
- // For NOT IN op, params were set in the join
- if ($op == 'NOT IN') {
- $groupClause = "{$gcTable}.group_id IS NULL";
+ else {
+ $groupClause = $ssClause;
}
}
- if ($in) {
- $ssClause = $this->savedSearch($values);
- if ($ssClause) {
- if ($groupClause) {
- $groupClause = "( ( $groupClause ) OR ( $ssClause ) )";
- }
- else {
- $groupClause = $ssClause;
- }
- }
+ list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Contact_DAO_Group', 'id', $value, $op);
+ $this->_qill[$grouping][] = ts("Group(s) %1 %2", array(1 => $qillop, 2 => $qillVal));
+ if (strpos($op, 'NULL') === FALSE) {
+ $this->_qill[$grouping][] = ts("Group Status %1", array(1 => implode(' ' . ts('or') . ' ', $statii)));
+ }
+ if ($groupClause) {
+ $this->_where[$grouping][] = $groupClause;
}
-
- $this->_where[$grouping][] = $groupClause;
- $this->_qill[$grouping][] = $qill;
}
/**
return $groupIds;
}
- /**
- * Where / qill clause for smart groups
- *
- * @param $values
- *
- * @return string|NULL
- */
- public function savedSearch(&$values) {
- list($name, $op, $value, $grouping, $wildcard) = $values;
- return $this->addGroupContactCache(array_keys((array) $value));
- }
-
/**
* @param array $groups
* @param string $tableAlias
* @param string $joinTable
+ * @param string $op
*
* @return null|string
*/
- public function addGroupContactCache($groups, $tableAlias = NULL, $joinTable = "contact_a") {
- $config = CRM_Core_Config::singleton();
-
- // Find all the groups that are part of a saved search.
- $groupIDs = implode(',', $groups);
- if (empty($groupIDs)) {
+ public function addGroupContactCache($groups, $tableAlias = NULL, $joinTable = "contact_a", $op) {
+ $isNullOp = (strpos($op, 'NULL') !== FALSE);
+ $groupsIds = $groups;
+ if (!$isNullOp && !$groups) {
return NULL;
}
+ elseif (strpos($op, 'IN') !== FALSE) {
+ $groups = array($op => $groups);
+ }
+ elseif (is_array($groups) && count($groups)) {
+ $groups = array('IN' => $groups);
+ }
+ // Find all the groups that are part of a saved search.
+ $smartGroupClause = self::buildClause("id", $op, $groups, 'Int');
$sql = "
SELECT id, cache_date, saved_search_id, children
FROM civicrm_group
-WHERE id IN ( $groupIDs )
+WHERE $smartGroupClause
AND ( saved_search_id != 0
OR saved_search_id IS NOT NULL
OR children IS NOT NULL )
";
$group = CRM_Core_DAO::executeQuery($sql);
- $groupsFiltered = array();
while ($group->fetch()) {
- $groupsFiltered[] = $group->id;
-
$this->_useDistinct = TRUE;
-
if (!$this->_smartGroupCache || $group->cache_date == NULL) {
CRM_Contact_BAO_GroupContactCache::load($group);
}
}
- if (count($groupsFiltered)) {
- $groupIDsFiltered = implode(',', $groupsFiltered);
-
- if ($tableAlias == NULL) {
- $tableAlias = "`civicrm_group_contact_cache_{$groupIDsFiltered}`";
- }
-
- $this->_tables[$tableAlias] = $this->_whereTables[$tableAlias] = " LEFT JOIN civicrm_group_contact_cache {$tableAlias} ON {$joinTable}.id = {$tableAlias}.contact_id ";
-
- return "{$tableAlias}.group_id IN (" . $groupIDsFiltered . ")";
+ if (!$tableAlias) {
+ $tableAlias = "`civicrm_group_contact_cache_";
+ $tableAlias .= ($isNullOp) ? "a`" : implode(',', (array) $groupsIds) . "`";
}
- return NULL;
+ $this->_tables[$tableAlias] = $this->_whereTables[$tableAlias] = " LEFT JOIN civicrm_group_contact_cache {$tableAlias} ON {$joinTable}.id = {$tableAlias}.contact_id ";
+ return self::buildClause("{$tableAlias}.group_id", $op, $groups, 'Int');
}
/**
}
else {
$field = 'civicrm_address.postal_code';
- $val = CRM_Utils_Type::escape($value, 'String');
+ // Per CRM-17060 we might be looking at an 'IN' syntax so don't case arrays to string.
+ if (!is_array($value)) {
+ $val = CRM_Utils_Type::escape($value, 'String');
+ }
+ else {
+ // Do we need to escape values here? I would expect buildClause does.
+ $val = $value;
+ }
}
$this->_tables['civicrm_address'] = $this->_whereTables['civicrm_address'] = 1;
$this->_tables['civicrm_address'] = 1;
$this->_whereTables['civicrm_address'] = 1;
- $countries = CRM_Core_PseudoConstant::country();
- if (is_numeric($value)) {
- $countryClause = self::buildClause(
- 'civicrm_address.country_id',
- $op,
- $value,
- 'Positive'
- );
- $countryName = $countries[(int ) $value];
- }
-
- else {
- $intValues = self::parseSearchBuilderString($value);
- if ($intValues && ($op == 'IN' || $op == 'NOT IN')) {
- $countryClause = self::buildClause(
- 'civicrm_address.country_id',
- $op,
- $intValues,
- 'Positive'
- );
- $countryNames = array();
- foreach ($intValues as $v) {
- $countryNames[] = $countries[$v];
- }
- $countryName = implode(',', $countryNames);
- }
- else {
- $countries = CRM_Core_PseudoConstant::country();
- $intVal = CRM_Utils_Array::key($value, $countries);
- $countryClause = self::buildClause(
- 'civicrm_address.country_id',
- $op,
- $intVal,
- 'Integer'
- );
- $countryName = $value;
- }
- }
- $countryQill = ts('Country') . " {$op} '$countryName'";
+ $countryClause = self::buildClause('civicrm_address.country_id', $op, $value, 'Positive');
+ list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, 'country_id', $value, $op);
+ $countryQill = ts("%1 %2 %3", array(1 => 'Country', 2 => $qillop, 3 => $qillVal));
if (!$fromStateProvince) {
$this->_where[$grouping][] = $countryClause;
implode(",", $targetGroup[2]) . ") ) ";
//add contacts from saved searches
- $ssWhere = $this->addGroupContactCache($targetGroup[2], "civicrm_relationship_group_contact_cache", "contact_b");
+ $ssWhere = $this->addGroupContactCache($targetGroup[2], "civicrm_relationship_group_contact_cache", "contact_b", $op);
//set the group where clause
if ($ssWhere) {
$clause = " (NULLIF($field, '') IS NOT NULL) ";
return $clause;
+ case 'IN':
+ case 'NOT IN':
+ // I feel like this would be escaped properly if passed through $queryString = CRM_Core_DAO::createSqlFilter.
+ if (!empty($value) && is_array($value) && !array_key_exists($op, $value)) {
+ $value = array($op => $value);
+ }
+
default:
if (empty($dataType)) {
$dataType = 'String';
// widely used and consistent across the codebase
// adding this here won't accept the search functions which don't submit an array
if (($queryString = CRM_Core_DAO::createSqlFilter($field, $value, $dataType)) != FALSE) {
+
return $queryString;
}
+
+ // This is the here-be-dragons zone. We have no other hopes left for an array so lets assume it 'should' be array('IN' => array(2,5))
+ // but we got only array(2,5) from the form.
+ // We could get away with keeping this in 4.6 if we make it such that it throws an enotice in 4.7 so
+ // people have to de-slopify it.
+ if (!empty($value[0])) {
+ $dragonPlace = $iAmAnIntentionalENoticeThatWarnsOfAProblemYouShouldReport;
+ if (($queryString = CRM_Core_DAO::createSqlFilter($field, array($op => $value), $dataType)) != FALSE) {
+ return $queryString;
+ }
+ }
+ else {
+ $op = 'IN';
+ $dragonPlace = $iAmAnIntentionalENoticeThatWarnsOfAProblemYouShouldReportUsingOldFormat;
+ if (($queryString = CRM_Core_DAO::createSqlFilter($field, array($op => array_keys($value)), $dataType)) != FALSE) {
+ return $queryString;
+ }
+ }
}
+
$value = CRM_Utils_Type::escape($value, $dataType);
// if we don't have a dataType we should assume
if ($dataType == 'String' || $dataType == 'Text') {
*
* @param CRM_Core_DAO $dao
* @param bool $return
+ * @param bool $usedForAPI
*
* @return array|NULL
*/
- public function convertToPseudoNames(&$dao, $return = FALSE) {
+ public function convertToPseudoNames(&$dao, $return = FALSE, $usedForAPI = FALSE) {
if (empty($this->_pseudoConstantsSelect)) {
return NULL;
}
continue;
}
- if (property_exists($dao, $value['idCol'])) {
+ if (is_object($dao) && property_exists($dao, $value['idCol'])) {
$val = $dao->$value['idCol'];
if (CRM_Utils_System::isNull($val)) {
}
// FIX ME: we should potentially move this to component Query and write a wrapper function that
// handles pseudoconstant fixes for all component
- elseif (in_array($value['pseudoField'], array('participant_status', 'participant_role'))) {
- $pseudoOptions = $viewValues = array();
- $pseudoOptions = CRM_Core_PseudoConstant::get('CRM_Event_DAO_Participant', $value['pseudoField'], array('flip' => 1));
- foreach (explode(CRM_Core_DAO::VALUE_SEPARATOR, $val) as $k => $v) {
- $viewValues[] = $pseudoOptions[$v];
+ elseif (in_array($value['pseudoField'], array('participant_role_id', 'participant_role'))) {
+ $viewValues = explode(CRM_Core_DAO::VALUE_SEPARATOR, $val);
+
+ if ($value['pseudoField'] == 'participant_role') {
+ $pseudoOptions = CRM_Core_PseudoConstant::get('CRM_Event_DAO_Participant', 'role_id');
+ foreach ($viewValues as $k => $v) {
+ $viewValues[$k] = $pseudoOptions[$v];
+ }
}
- $dao->$key = implode(', ', $viewValues);
+ $dao->$key = ($usedForAPI && count($viewValues) > 1) ? $viewValues : implode(', ', $viewValues);
}
else {
$labels = CRM_Core_OptionGroup::values($value['pseudoField']);
elseif ($daoName == 'CRM_Event_DAO_Event' && $fieldName == 'id') {
$pseduoOptions = CRM_Event_BAO_Event::getEvents(0, $fieldValue, TRUE, TRUE, TRUE);
}
+ elseif ($daoName == 'CRM_Contact_DAO_Group' && $fieldName == 'id') {
+ $pseduoOptions = CRM_Core_PseudoConstant::group();
+ }
+ elseif ($fieldName == 'country_id') {
+ $pseduoOptions = CRM_Core_PseudoConstant::country();
+ }
elseif ($daoName) {
$pseduoOptions = CRM_Core_PseudoConstant::get($daoName, $fieldName, $pseduoExtraParam = array());
}
$qillString = array();
if (!empty($pseduoOptions)) {
foreach ((array) $fieldValue as $val) {
- $qillString[] = $pseduoOptions[$val];
+ $qillString[] = CRM_Utils_Array::value($val, $pseduoOptions, $val);
}
$fieldValue = implode(', ', $qillString);
}