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
);
}
- // 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);
+ }
+ // 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');
}
/**
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) {
SELECT COUNT( conts.total_amount ) as total_count,
SUM( conts.total_amount ) as total_amount,
AVG( conts.total_amount ) as total_avg,
+ conts.total_amount as amount,
conts.currency as currency";
if ($this->_permissionWhereClause) {
$where .= " AND " . $this->_permissionWhereClause;
$summary = array();
$summary['total'] = array();
$summary['total']['count'] = $summary['total']['amount'] = $summary['total']['avg'] = "n/a";
+ $innerQuery = "SELECT civicrm_contribution.total_amount, COUNT(civicrm_contribution.total_amount) as civicrm_contribution_total_amount_count,
+ civicrm_contribution.currency $from $completedWhere";
$query = "$select FROM (
- SELECT civicrm_contribution.total_amount, civicrm_contribution.currency $from $completedWhere
- GROUP BY civicrm_contribution.id
+ $innerQuery GROUP BY civicrm_contribution.id
) as conts
GROUP BY currency";
$summary['total']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency);
$summary['total']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency);
}
+
+ $orderBy = 'ORDER BY civicrm_contribution_total_amount_count DESC';
+ $groupBy = 'GROUP BY currency, civicrm_contribution.total_amount';
+ $modeSQL = "$select, conts.civicrm_contribution_total_amount_count as civicrm_contribution_total_amount_count FROM ($innerQuery
+ $groupBy $orderBy) as conts
+ GROUP BY currency";
+
+ $summary['total']['mode'] = CRM_Contribute_BAO_Contribution::computeStats('mode', $modeSQL);
+
+ $medianSQL = "{$from} {$completedWhere}";
+ $summary['total']['median'] = CRM_Contribute_BAO_Contribution::computeStats('median', $medianSQL, 'civicrm_contribution');
+ $summary['total']['currencyCount'] = count($summary['total']['median']);
+
if (!empty($summary['total']['amount'])) {
$summary['total']['amount'] = implode(', ', $summary['total']['amount']);
$summary['total']['avg'] = implode(', ', $summary['total']['avg']);
+ $summary['total']['mode'] = implode(', ', $summary['total']['mode']);
+ $summary['total']['median'] = implode(', ', $summary['total']['median']);
}
else {
- $summary['total']['amount'] = $summary['total']['avg'] = 0;
+ $summary['total']['amount'] = $summary['total']['avg'] = $summary['total']['median'] = 0;
}
// soft credit summary
$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);
$clause = " (NULLIF($field, '') IS NOT NULL) ";
return $clause;
- 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, ",") . ")";
- }
- return "$clause $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;
+ }
+ }
}
$value = CRM_Utils_Type::escape($value, $dataType);
// if we don't have a dataType we should assume
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());
}
$qillString = array();
if (!empty($pseduoOptions)) {
foreach ((array) $fieldValue as $val) {
- $qillString[] = $pseduoOptions[$val];
+ $qillString[] = CRM_Utils_Array::value($val, $pseduoOptions, $val);
}
$fieldValue = implode(', ', $qillString);
}
projects / civicrm-core.git / blobdiff