X-Git-Url: https://vcs.fsf.org/?a=blobdiff_plain;f=CRM%2FContact%2FBAO%2FQuery.php;h=990cca71e3c89291931b43d559fe7b5027d5291a;hb=ba41614e99343a658d9eb585bac13185698ed798;hp=841c19910c413e00b4666ec67a4c127a995b2b45;hpb=c30897643e6607912705d3aecd3039446e3a6f0d;p=civicrm-core.git diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index 841c19910c..990cca71e3 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -1770,6 +1770,8 @@ class CRM_Contact_BAO_Query { CRM_Activity_BAO_Query::whereClauseSingle($values, $this); return; + case 'age_low': + case 'age_high': case 'birth_date_low': case 'birth_date_high': case 'deceased_date_low': @@ -1777,6 +1779,10 @@ class CRM_Contact_BAO_Query { $this->demographics($values); return; + case 'age_asof_date': + // handled by demographics + return; + case 'log_date_low': case 'log_date_high': $this->modifiedDates($values); @@ -2217,7 +2223,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 +2437,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 +2561,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 +2804,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 +2907,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'); } /** @@ -3884,7 +3862,12 @@ WHERE id IN ( $groupIDs ) public function demographics(&$values) { list($name, $op, $value, $grouping, $wildcard) = $values; - if (($name == 'birth_date_low') || ($name == 'birth_date_high')) { + if (($name == 'age_low') || ($name == 'age_high')) { + $this->ageRangeQueryBuilder($values, + 'contact_a', 'age', 'birth_date', ts('Age') + ); + } + elseif (($name == 'birth_date_low') || ($name == 'birth_date_high')) { $this->dateQueryBuilder($values, 'contact_a', 'birth_date', 'birth_date', ts('Birth Date') @@ -4100,7 +4083,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) { @@ -5081,7 +5064,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); @@ -5186,6 +5169,104 @@ SELECT COUNT( conts.total_amount ) as cancel_count, } } + + /** + * @param $values + * @param string $tableName + * @param string $fieldName + * @param string $dbFieldName + * @param $fieldTitle + * @param null $options + */ + public function ageRangeQueryBuilder( + &$values, + $tableName, $fieldName, + $dbFieldName, $fieldTitle, + $options = NULL + ) { + list($name, $op, $value, $grouping, $wildcard) = $values; + + $asofDateValues = $this->getWhereValues("{$fieldName}_asof_date", $grouping); + $asofDate = NULL; // will be treated as current day + if ($asofDateValues) { + $asofDate = CRM_Utils_Date::processDate($asofDateValues[2]); + $asofDateFormat = CRM_Utils_Date::customFormat(substr($asofDate, 0, 8)); + $fieldTitle .= ' ' . ts('as of') . ' ' . $asofDateFormat; + } + + if ($name == "{$fieldName}_low" || + $name == "{$fieldName}_high" + ) { + if (isset($this->_rangeCache[$fieldName])) { + return; + } + $this->_rangeCache[$fieldName] = 1; + + $secondOP = $secondPhrase = $secondValue = NULL; + + if ($name == "{$fieldName}_low") { + $firstPhrase = ts('greater than or equal to'); + // NB: age > X means date of birth < Y + $firstOP = '<='; + $firstDate = self::calcDateFromAge($asofDate, $value, 'min'); + + $secondValues = $this->getWhereValues("{$fieldName}_high", $grouping); + if (!empty($secondValues)) { + $secondOP = '>='; + $secondPhrase = ts('less than or equal to'); + $secondValue = $secondValues[2]; + $secondDate = self::calcDateFromAge($asofDate, $secondValue, 'max'); + } + } + else { + $firstOP = '>='; + $firstPhrase = ts('less than or equal to'); + $firstDate = self::calcDateFromAge($asofDate, $value, 'max'); + + $secondValues = $this->getWhereValues("{$fieldName}_low", $grouping); + if (!empty($secondValues)) { + $secondOP = '<='; + $secondPhrase = ts('greater than or equal to'); + $secondValue = $secondValues[2]; + $secondDate = self::calcDateFromAge($asofDate, $secondValue, 'min'); + } + } + + if ($secondOP) { + $this->_where[$grouping][] = " +( {$tableName}.{$dbFieldName} $firstOP '$firstDate' ) AND +( {$tableName}.{$dbFieldName} $secondOP '$secondDate' ) +"; + $displayValue = $options ? $options[$value] : $value; + $secondDisplayValue = $options ? $options[$secondValue] : $secondValue; + + $this->_qill[$grouping][] + = "$fieldTitle - $firstPhrase \"$displayValue\" " . ts('AND') . " $secondPhrase \"$secondDisplayValue\""; + } + else { + $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $firstOP '$firstDate'"; + $displayValue = $options ? $options[$value] : $value; + $this->_qill[$grouping][] = "$fieldTitle - $firstPhrase \"$displayValue\""; + } + $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1; + return; + } + } + + public static function calcDateFromAge($asofDate, $age, $type) { + $date = new DateTime($asofDate); + if ($type == "min") { + // minimum age is $age: dob <= date - age "235959" + $date->sub(new DateInterval("P" . $age . "Y")); + return $date->format('Ymd') . "235959"; + } + else { + // max age is $age: dob >= date - (age + 1y) + 1d "000000" + $date->sub(new DateInterval("P" . ($age + 1) . "Y"))->add(new DateInterval("P1D")); + return $date->format('Ymd') . "000000"; + } + } + /** * Given the field name, operator, value & its data type * builds the where Clause for the query @@ -5222,23 +5303,9 @@ 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, ",") . ")"; + if (!empty($value) && is_array($value) && !array_key_exists($op, $value)) { + $value = array($op => $value); } - return "$clause $value"; default: if (empty($dataType)) { @@ -5249,9 +5316,28 @@ 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 { + $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') { @@ -5715,6 +5801,9 @@ AND displayRelType.is_active = 1 elseif ($fieldName == 'contribution_product_id') { $pseduoOptions = CRM_Contribute_PseudoConstant::products(); } + elseif ($daoName == 'CRM_Contact_DAO_Group' && $fieldName == 'id') { + $pseduoOptions = CRM_Core_PseudoConstant::group(); + } elseif ($daoName) { $pseduoOptions = CRM_Core_PseudoConstant::get($daoName, $fieldName, $pseduoExtraParam = array()); } @@ -5730,7 +5819,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); }