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':
$this->demographics($values);
return;
+ case 'age_asof_date':
+ // handled by demographics
+ return;
+
case 'log_date_low':
case 'log_date_high':
$this->modifiedDates($values);
);
}
- // 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');
}
/**
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')
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
}
}
+
+ /**
+ * @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
$clause = " (NULLIF($field, '') IS NOT NULL) ";
return $clause;
+ case 'IN':
+ case 'NOT IN':
+ 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 {
+ $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') {
// 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));
+ $pseudoOptions = CRM_Core_PseudoConstant::get('CRM_Event_DAO_Participant', $value['idCol']);
foreach (explode(CRM_Core_DAO::VALUE_SEPARATOR, $val) as $k => $v) {
$viewValues[] = $pseudoOptions[$v];
}
elseif ($daoName == 'CRM_Event_DAO_Event' && $fieldName == 'id') {
$pseduoOptions = CRM_Event_BAO_Event::getEvents(0, $fieldValue, TRUE, TRUE, TRUE);
}
+ 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