X-Git-Url: https://vcs.fsf.org/?a=blobdiff_plain;f=CRM%2FContact%2FBAO%2FQuery.php;h=f9efcaaf50a32ff26b0eb55f25735af1813e523f;hb=b5d9496b80de7cb199d66140e16ef94c854f9c3c;hp=476a3aeac2ca42e86efee3dda962b9f6a5b8f0e8;hpb=b53d6881fe08b66cd576b1462eefbdb7795ce6a4;p=civicrm-core.git diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index 476a3aeac2..f9efcaaf50 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -1601,7 +1601,9 @@ class CRM_Contact_BAO_Query { } $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 { @@ -1865,26 +1867,7 @@ class CRM_Contact_BAO_Query { } // 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]); @@ -2217,7 +2200,7 @@ class CRM_Contact_BAO_Query { if (!in_array($operator, CRM_Core_DAO::acceptedSQLOperators())) { //Via Contact get api value is not in array(operator => array(values)) format ONLY for IN/NOT IN operators //so this condition will satisfy the search for now - if (strstr('IN', $op)) { + if (strpos($op, 'IN') !== FALSE) { $value = array($op => $value); } // we don't know when this might happen @@ -2431,9 +2414,14 @@ class CRM_Contact_BAO_Query { ); } - // 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 @@ -2550,13 +2538,17 @@ class CRM_Contact_BAO_Query { 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': @@ -2789,114 +2781,92 @@ class CRM_Contact_BAO_Query { 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; } /** @@ -2914,69 +2884,54 @@ class CRM_Contact_BAO_Query { 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'); } /** @@ -3531,7 +3486,14 @@ WHERE id IN ( $groupIDs ) } 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; @@ -3610,45 +3572,9 @@ WHERE id IN ( $groupIDs ) $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; @@ -4100,7 +4026,7 @@ WHERE id IN ( $groupIDs ) 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) { @@ -5064,7 +4990,7 @@ SELECT COUNT( conts.total_amount ) as cancel_count, $date = "('" . implode("','", $value) . "')"; $format = implode(', ', $format); } - elseif ($value) { + elseif ($value && (!strstr($op, 'NULL') && !strstr($op, 'EMPTY'))) { $date = CRM_Utils_Date::processDate($value); if (!$appendTimeStamp) { $date = substr($date, 0, 8); @@ -5205,23 +5131,10 @@ SELECT COUNT( conts.total_amount ) as cancel_count, case 'IN': case 'NOT IN': - if (isset($dataType)) { - if (is_array($value)) { - $values = $value; - } - else { - $value = CRM_Utils_Type::escape($value, "String"); - $values = explode(',', CRM_Utils_Array::value(0, explode(')', CRM_Utils_Array::value(1, explode('(', $value))))); - } - // supporting multiple values in IN clause - $val = array(); - foreach ($values as $v) { - $v = trim($v); - $val[] = "'" . CRM_Utils_Type::escape($v, $dataType) . "'"; - } - $value = "(" . implode($val, ",") . ")"; + // 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); } - return "$clause $value"; default: if (empty($dataType)) { @@ -5232,9 +5145,29 @@ SELECT COUNT( conts.total_amount ) as cancel_count, // 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') { @@ -5550,10 +5483,11 @@ AND displayRelType.is_active = 1 * * @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; } @@ -5563,7 +5497,7 @@ AND displayRelType.is_active = 1 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)) { @@ -5586,13 +5520,16 @@ AND displayRelType.is_active = 1 } // 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']); @@ -5695,6 +5632,12 @@ AND displayRelType.is_active = 1 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()); } @@ -5710,7 +5653,7 @@ AND displayRelType.is_active = 1 $qillString = array(); if (!empty($pseduoOptions)) { foreach ((array) $fieldValue as $val) { - $qillString[] = $pseduoOptions[$val]; + $qillString[] = CRM_Utils_Array::value($val, $pseduoOptions, $val); } $fieldValue = implode(', ', $qillString); }