public $_pseudoConstantsSelect = array();
+ public $_groupUniqueKey = NULL;
+ public $_groupKeys = [];
+
/**
* Class constructor which also does all the work.
*
$smartGroupIDs[] = $id;
}
else {
- $regularGroupIDs[] = $id;
+ $regularGroupIDs[] = trim($id);
}
}
if (count($regularGroupIDs) > 1) {
$op = strpos($op, 'IN') ? $op : ($op == '!=') ? 'NOT IN' : 'IN';
}
- $groupIds = implode(',', (array) $regularGroupIDs);
+ $groupIds = CRM_Utils_Type::validate(
+ implode(',', (array) $regularGroupIDs),
+ 'CommaSeparatedIntegers'
+ );
$gcTable = "`civicrm_group_contact-" . uniqid() . "`";
$joinClause = array("contact_a.id = {$gcTable}.contact_id");
//CRM-19589: contact(s) removed from a Smart Group, resides in civicrm_group_contact table
$groupContactCacheClause = '';
if (count($smartGroupIDs) || empty($value)) {
- $gccTableAlias = "civicrm_group_contact_cache";
+ $this->_groupUniqueKey = uniqid();
+ $this->_groupKeys[] = $this->_groupUniqueKey;
+ $gccTableAlias = "civicrm_group_contact_cache_{$this->_groupUniqueKey}";
$groupContactCacheClause = $this->addGroupContactCache($smartGroupIDs, $gccTableAlias, "contact_a", $op);
if (!empty($groupContactCacheClause)) {
if ($isNotOp) {
$groupIds = implode(',', (array) $smartGroupIDs);
- $gcTable = "civicrm_group_contact";
+ $gcTable = "civicrm_group_contact_{$this->_groupUniqueKey}";
$joinClause = array("contact_a.id = {$gcTable}.contact_id");
$this->_tables[$gcTable] = $this->_whereTables[$gcTable] = " LEFT JOIN civicrm_group_contact {$gcTable} ON (" . implode(' AND ', $joinClause) . ")";
if (strpos($op, 'IN') !== FALSE) {
}
}
+ public function getGroupCacheTableKeys() {
+ return $this->_groupKeys;
+ }
+
/**
* Function translates selection of group type into a list of groups.
* @param $value
$op = "LIKE";
$value = "%{$value}%";
+ $escapedValue = CRM_Utils_Type::escape("%{$value}%", 'String');
$useAllTagTypes = $this->getWhereValues('all_tag_types', $grouping);
$tagTypesText = $this->getWhereValues('tag_types_text', $grouping);
- $etTable = "`civicrm_entity_tag-" . $value . "`";
- $tTable = "`civicrm_tag-" . $value . "`";
+ $etTable = "`civicrm_entity_tag-" . uniqid() . "`";
+ $tTable = "`civicrm_tag-" . uniqid() . "`";
if ($useAllTagTypes[2]) {
$this->_tables[$etTable] = $this->_whereTables[$etTable]
LEFT JOIN civicrm_tag {$tTable} ON ( {$etTable}.tag_id = {$tTable}.id )";
// search tag in cases
- $etCaseTable = "`civicrm_entity_case_tag-" . $value . "`";
- $tCaseTable = "`civicrm_case_tag-" . $value . "`";
+ $etCaseTable = "`civicrm_entity_case_tag-" . uniqid() . "`";
+ $tCaseTable = "`civicrm_case_tag-" . uniqid() . "`";
$this->_tables[$etCaseTable] = $this->_whereTables[$etCaseTable]
= " LEFT JOIN civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id
LEFT JOIN civicrm_case
LEFT JOIN civicrm_entity_tag {$etCaseTable} ON ( {$etCaseTable}.entity_table = 'civicrm_case' AND {$etCaseTable}.entity_id = civicrm_case.id )
LEFT JOIN civicrm_tag {$tCaseTable} ON ( {$etCaseTable}.tag_id = {$tCaseTable}.id )";
// search tag in activities
- $etActTable = "`civicrm_entity_act_tag-" . $value . "`";
- $tActTable = "`civicrm_act_tag-" . $value . "`";
+ $etActTable = "`civicrm_entity_act_tag-" . uniqid() . "`";
+ $tActTable = "`civicrm_act_tag-" . uniqid() . "`";
$activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
$targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
LEFT JOIN civicrm_entity_tag as {$etActTable} ON ( {$etActTable}.entity_table = 'civicrm_activity' AND {$etActTable}.entity_id = civicrm_activity.id )
LEFT JOIN civicrm_tag {$tActTable} ON ( {$etActTable}.tag_id = {$tActTable}.id )";
- $this->_where[$grouping][] = "({$tTable}.name $op '" . $value . "' OR {$tCaseTable}.name $op '" . $value . "' OR {$tActTable}.name $op '" . $value . "')";
+ $this->_where[$grouping][] = "({$tTable}.name $op '" . $escapedValue . "' OR {$tCaseTable}.name $op '" . $escapedValue . "' OR {$tActTable}.name $op '" . $escapedValue . "')";
$this->_qill[$grouping][] = ts('Tag %1 %2', array(1 => $tagTypesText[2], 2 => $op)) . ' ' . $value;
}
else {
- $etTable = "`civicrm_entity_tag-" . $value . "`";
- $tTable = "`civicrm_tag-" . $value . "`";
+ $etTable = "`civicrm_entity_tag-" . uniqid() . "`";
+ $tTable = "`civicrm_tag-" . uniqid() . "`";
$this->_tables[$etTable] = $this->_whereTables[$etTable] = " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id AND
{$etTable}.entity_table = 'civicrm_contact' )
LEFT JOIN civicrm_tag {$tTable} ON ( {$etTable}.tag_id = {$tTable}.id ) ";
if (count($value) > 1) {
$this->_useDistinct = TRUE;
}
- $value = implode(',', (array) $value);
}
+ // implode array, then remove all spaces and validate CommaSeparatedIntegers
+ $value = CRM_Utils_Type::validate(
+ str_replace(' ', '', implode(',', (array) $value)),
+ 'CommaSeparatedIntegers'
+ );
+
$useAllTagTypes = $this->getWhereValues('all_tag_types', $grouping);
$tagTypesText = $this->getWhereValues('tag_types_text', $grouping);
- $etTable = "`civicrm_entity_tag-" . $value . "`";
+ $etTable = "`civicrm_entity_tag-" . uniqid() . "`";
if ($useAllTagTypes[2]) {
$this->_tables[$etTable] = $this->_whereTables[$etTable]
= " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id AND {$etTable}.entity_table = 'civicrm_contact') ";
// search tag in cases
- $etCaseTable = "`civicrm_entity_case_tag-" . $value . "`";
+ $etCaseTable = "`civicrm_entity_case_tag-" . uniqid() . "`";
$activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
$targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
AND civicrm_case.is_deleted = 0 )
LEFT JOIN civicrm_entity_tag {$etCaseTable} ON ( {$etCaseTable}.entity_table = 'civicrm_case' AND {$etCaseTable}.entity_id = civicrm_case.id ) ";
// search tag in activities
- $etActTable = "`civicrm_entity_act_tag-" . $value . "`";
+ $etActTable = "`civicrm_entity_act_tag-" . uniqid() . "`";
$this->_tables[$etActTable] = $this->_whereTables[$etActTable]
= " LEFT JOIN civicrm_activity_contact
ON ( civicrm_activity_contact.contact_id = contact_a.id AND civicrm_activity_contact.record_type_id = {$targetID} )
$additionalFromClause = NULL, $skipOrderAndLimit = FALSE
) {
- if ($includeContactIds) {
- $this->_includeContactIds = TRUE;
- $this->_whereClause = $this->whereClause();
- }
-
- $onlyDeleted = in_array(array('deleted_contacts', '=', '1', '0', '0'), $this->_params);
-
- // if we’re explicitly looking for a certain contact’s contribs, events, etc.
- // and that contact happens to be deleted, set $onlyDeleted to true
- foreach ($this->_params as $values) {
- $name = CRM_Utils_Array::value(0, $values);
- $op = CRM_Utils_Array::value(1, $values);
- $value = CRM_Utils_Array::value(2, $values);
- if ($name == 'contact_id' and $op == '=') {
- if (CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'is_deleted')) {
- $onlyDeleted = TRUE;
- }
- break;
- }
- }
-
- // building the query string
- $groupBy = $groupByCols = NULL;
- if (!$count) {
- if (isset($this->_groupByComponentClause)) {
- $groupBy = $this->_groupByComponentClause;
- $groupByCols = preg_replace('/^GROUP BY /', '', trim($this->_groupByComponentClause));
- $groupByCols = explode(', ', $groupByCols);
- }
- elseif ($this->_useGroupBy) {
- $groupByCols = array('contact_a.id');
- }
- }
- if ($this->_mode & CRM_Contact_BAO_Query::MODE_ACTIVITY && (!$count)) {
- $groupByCols = array('civicrm_activity.id');
- }
-
- $order = $orderBy = $limit = '';
- if (!$count) {
- list($order, $additionalFromClause) = $this->prepareOrderBy($sort, $sortByChar, $sortOrder, $additionalFromClause);
-
- if ($rowCount > 0 && $offset >= 0) {
- $offset = CRM_Utils_Type::escape($offset, 'Int');
- $rowCount = CRM_Utils_Type::escape($rowCount, 'Int');
- $limit = " LIMIT $offset, $rowCount ";
- }
- }
- // Two cases where we are disabling FGB (FULL_GROUP_BY_MODE):
- // 1. Expecting the search query to return all the first single letter characters of contacts ONLY, but when FGB is enabled
- // MySQL expect the columns present in GROUP BY, must be present in SELECT clause and that results into error, needless to have other columns.
- // 2. When GROUP BY columns are present then disable FGB otherwise it demands to add ORDER BY columns in GROUP BY and eventually in SELECT
- // clause. This will impact the search query output.
- $disableFullGroupByMode = ($sortByChar || !empty($groupByCols) || $groupContacts);
-
- if ($disableFullGroupByMode) {
- CRM_Core_DAO::disableFullGroupByMode();
- }
-
- // CRM-15231
- $this->_sort = $sort;
-
- //CRM-15967
- $this->includePseudoFieldsJoin($sort);
-
- list($select, $from, $where, $having) = $this->query($count, $sortByChar, $groupContacts, $onlyDeleted);
-
- if (!empty($groupByCols)) {
- $groupBy = " GROUP BY " . implode(', ', $groupByCols);
- }
-
- if ($additionalWhereClause) {
- $where = $where . ' AND ' . $additionalWhereClause;
- }
-
- //additional from clause should be w/ proper joins.
- if ($additionalFromClause) {
- $from .= "\n" . $additionalFromClause;
- }
-
- // if we are doing a transform, do it here
- // use the $from, $where and $having to get the contact ID
- if ($this->_displayRelationshipType) {
- $this->filterRelatedContacts($from, $where, $having);
- }
-
- if ($skipOrderAndLimit) {
- $query = "$select $from $where $having $groupBy";
- }
- else {
- $query = "$select $from $where $having $groupBy $order $limit";
- }
+ $query = $this->getSearchSQL($offset, $rowCount, $sort, $count, $includeContactIds, $sortByChar, $groupContacts, $additionalWhereClause, $sortOrder, $additionalFromClause, $skipOrderAndLimit);
if ($returnQuery) {
return $query;
$dao = CRM_Core_DAO::executeQuery($query);
- if ($disableFullGroupByMode) {
- CRM_Core_DAO::reenableFullGroupByMode();
- }
+ // We can always call this - it will only re-enable if it was originally enabled.
+ CRM_Core_DAO::reenableFullGroupByMode();
if ($groupContacts) {
$ids = array();
$summary = ['total' => []];
$this->addBasicStatsToSummary($summary, $where, $from);
- $this->addModeToStats($summary, $from, $where);
- $this->addMedianToStats($summary, $where, $from);
-
- $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'] = $summary['total']['median'] = 0;
- }
- // soft credit summary
if (CRM_Contribute_BAO_Query::isSoftCreditOptionEnabled()) {
- $softCreditWhere = "$where AND civicrm_contribution.contribution_status_id = 1 AND civicrm_contribution_soft.id IS NOT NULL";
- $query = "
- SELECT COUNT( conts.total_amount ) as total_count,
- SUM( conts.total_amount ) as total_amount,
- AVG( conts.total_amount ) as total_avg,
- conts.currency as currency
- FROM (
- SELECT civicrm_contribution_soft.amount as total_amount, civicrm_contribution_soft.currency $from $softCreditWhere
- GROUP BY civicrm_contribution_soft.id
- ) as conts
- GROUP BY currency";
- $dao = CRM_Core_DAO::executeQuery($query);
- $summary['soft_credit']['count'] = 0;
- $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = array();
- while ($dao->fetch()) {
- $summary['soft_credit']['count'] += $dao->total_count;
- $summary['soft_credit']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency);
- $summary['soft_credit']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency);
- }
- if (!empty($summary['soft_credit']['amount'])) {
- $summary['soft_credit']['amount'] = implode(', ', $summary['soft_credit']['amount']);
- $summary['soft_credit']['avg'] = implode(', ', $summary['soft_credit']['avg']);
- }
- else {
- $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = 0;
- }
- }
-
- // hack $select
- //@todo - this could be one query using the IF in mysql - eg
- // SELECT sum(total_completed), sum(count_completed), sum(count_cancelled), sum(total_cancelled) FROM (
- // SELECT civicrm_contribution.total_amount, civicrm_contribution.currency ,
- // IF(civicrm_contribution.contribution_status_id = 1, 1, 0 ) as count_completed,
- // IF(civicrm_contribution.contribution_status_id = 1, total_amount, 0 ) as total_completed,
- // IF(civicrm_contribution.cancel_date IS NOT NULL = 1, 1, 0 ) as count_cancelled,
- // IF(civicrm_contribution.cancel_date IS NOT NULL = 1, total_amount, 0 ) as total_cancelled
- // FROM civicrm_contact contact_a
- // LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id
- // WHERE ( ... where clause....
- // AND (civicrm_contribution.cancel_date IS NOT NULL OR civicrm_contribution.contribution_status_id = 1)
- // ) as conts
-
- $select = "
-SELECT COUNT( conts.total_amount ) as cancel_count,
- SUM( conts.total_amount ) as cancel_amount,
- AVG( conts.total_amount ) as cancel_avg,
- conts.currency as currency";
-
- $where .= " AND civicrm_contribution.cancel_date IS NOT NULL ";
- if ($context == 'search') {
- $where .= " AND contact_a.is_deleted = 0 ";
+ $this->addBasicSoftCreditStatsToStats($summary, $where, $from);
}
- $query = "$select FROM (
- SELECT civicrm_contribution.total_amount, civicrm_contribution.currency $from $where
- GROUP BY civicrm_contribution.id
- ) as conts
- GROUP BY currency";
-
- $dao = CRM_Core_DAO::executeQuery($query);
-
- if ($dao->N <= 1) {
- if ($dao->fetch()) {
- $summary['cancel']['count'] = $dao->cancel_count;
- $summary['cancel']['amount'] = CRM_Utils_Money::format($dao->cancel_amount, $dao->currency);
- $summary['cancel']['avg'] = CRM_Utils_Money::format($dao->cancel_avg, $dao->currency);
- }
- }
- else {
- $summary['cancel']['count'] = 0;
- $summary['cancel']['amount'] = $summary['cancel']['avg'] = array();
- while ($dao->fetch()) {
- $summary['cancel']['count'] += $dao->cancel_count;
- $summary['cancel']['amount'][] = CRM_Utils_Money::format($dao->cancel_amount, $dao->currency);
- $summary['cancel']['avg'][] = CRM_Utils_Money::format($dao->cancel_avg, $dao->currency);
- }
- $summary['cancel']['amount'] = implode(', ', $summary['cancel']['amount']);
- $summary['cancel']['avg'] = implode(', ', $summary['cancel']['avg']);
- }
+ $this->addBasicCancelStatsToSummary($summary, $where, $from);
return $summary;
}
* list(string $orderByClause, string $additionalFromClause).
*/
protected function prepareOrderBy($sort, $sortByChar, $sortOrder, $additionalFromClause) {
- $order = NULL;
- $orderByArray = array();
- $config = CRM_Core_Config::singleton();
- if ($config->includeOrderByClause ||
+ $orderByArray = [];
+ $orderBy = '';
+
+ if (CRM_Core_Config::singleton()->includeOrderByClause ||
isset($this->_distinctComponentClause)
) {
if ($sort) {
$orderBy = str_replace('sort_name', 'contact_a.sort_name', $orderBy);
}
- $order = " ORDER BY $orderBy";
-
if ($sortOrder) {
- $order .= " $sortOrder";
+ $orderBy .= " $sortOrder";
}
// always add contact_a.id to the ORDER clause
// so the order is deterministic
- if (strpos('contact_a.id', $order) === FALSE) {
- $order .= ", contact_a.id";
+ if (strpos('contact_a.id', $orderBy) === FALSE) {
+ $orderBy .= ", contact_a.id";
}
}
}
$orderByArray = array("UPPER(LEFT(contact_a.sort_name, 1)) asc");
}
else {
- $order = " ORDER BY contact_a.sort_name ASC, contact_a.id";
+ $orderBy = " contact_a.sort_name ASC, contact_a.id";
}
}
- if (!$order && empty($orderByArray)) {
- return array($order, $additionalFromClause);
+ if (!$orderBy && empty($orderByArray)) {
+ return [NULL, $additionalFromClause];
}
// Remove this here & add it at the end for simplicity.
- $order = trim(str_replace('ORDER BY', '', $order));
+ $order = trim($orderBy);
// hack for order clause
if (!empty($orderByArray)) {
$this->_select = array_merge($this->_select, $this->_customQuery->_select);
$this->_tables = array_merge($this->_tables, $this->_customQuery->_tables);
}
- foreach ($this->_pseudoConstantsSelect as $key => $pseudoConstantMetadata) {
- // By replacing the join to the option value table with the mysql construct
- // ORDER BY field('contribution_status_id', 2,1,4)
- // we can remove a join. In the case of the option value join it is
- /// a join known to cause slow queries.
- // @todo cover other pseudoconstant types. Limited to option group ones in the
- // first instance for scope reasons. They require slightly different handling as the column (label)
- // is not declared for them.
- // @todo so far only integer fields are being handled. If we add string fields we need to look at
- // escaping.
- if (isset($pseudoConstantMetadata['pseudoconstant'])
- && isset($pseudoConstantMetadata['pseudoconstant']['optionGroupName'])
- && $field === CRM_Utils_Array::value('optionGroupName', $pseudoConstantMetadata['pseudoconstant'])
- ) {
- $sortedOptions = $pseudoConstantMetadata['bao']::buildOptions($pseudoConstantMetadata['pseudoField'], NULL, array(
+
+ // By replacing the join to the option value table with the mysql construct
+ // ORDER BY field('contribution_status_id', 2,1,4)
+ // we can remove a join. In the case of the option value join it is
+ /// a join known to cause slow queries.
+ // @todo cover other pseudoconstant types. Limited to option group ones in the
+ // first instance for scope reasons. They require slightly different handling as the column (label)
+ // is not declared for them.
+ // @todo so far only integer fields are being handled. If we add string fields we need to look at
+ // escaping.
+ $fieldSpec = $this->getMetadataForRealField($field);
+ $pseudoConstantMetadata = CRM_Utils_Array::value('pseudoconstant', $fieldSpec, FALSE);
+ if (!empty($pseudoConstantMetadata)
+ ) {
+ if (!empty($pseudoConstantMetadata['optionGroupName'])) {
+ $sortedOptions = $fieldSpec['bao']::buildOptions($fieldSpec['name'], NULL, [
'orderColumn' => 'label',
- ));
- $order = str_replace("$field $direction", "field({$pseudoConstantMetadata['pseudoField']}," . implode(',', array_keys($sortedOptions)) . ") $direction", $order);
+ ]);
+ $order = str_replace("$field", "field({$fieldSpec['name']}," . implode(',', array_keys($sortedOptions)) . ")", $order);
}
//CRM-12565 add "`" around $field if it is a pseudo constant
// This appears to be for 'special' fields like locations with appended numbers or hyphens .. maybe.
* @return array
*/
protected function addBasicStatsToSummary(&$summary, $where, $from) {
- $summary['total']['count'] = $summary['total']['amount'] = $summary['total']['avg'] = "n/a";
+ $summary['total']['count'] = 0;
+ $summary['total']['amount'] = $summary['total']['avg'] = [];
$query = "
SELECT COUNT( conts.total_amount ) as total_count,
$dao = CRM_Core_DAO::executeQuery($query);
- $summary['total']['count'] = 0;
- $summary['total']['amount'] = $summary['total']['avg'] = [];
while ($dao->fetch()) {
$summary['total']['count'] += $dao->total_count;
$summary['total']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency);
$summary['total']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency);
}
+
+ if (!empty($summary['total']['amount'])) {
+ $summary['total']['amount'] = implode(', ', $summary['total']['amount']);
+ $summary['total']['avg'] = implode(', ', $summary['total']['avg']);
+ }
+ else {
+ $summary['total']['amount'] = $summary['total']['avg'] = 0;
+ }
return $summary;
}
/**
- * Add the mode to stats.
- *
- * Note that his is a slow query when performed on more than a handful or results - often taking many minutes
- *
- * See https://lab.civicrm.org/dev/core/issues/720
+ * Add basic soft credit statistics to summary array.
*
* @param array $summary
- * @param string $from
* @param string $where
+ * @param string $from
*/
- protected function addModeToStats(&$summary, $from, $where) {
- $modeSQL = "
+ protected function addBasicSoftCreditStatsToStats(&$summary, $where, $from) {
+ $query = "
SELECT COUNT( conts.total_amount ) as total_count,
- SUM( conts.total_amount ) as total_amount,
- AVG( conts.total_amount ) as total_avg,
- conts.currency as currency,
- SUBSTRING_INDEX(GROUP_CONCAT(conts.total_amount
- ORDER BY conts.civicrm_contribution_total_amount_count DESC SEPARATOR ';'), ';', 1) as amount,
- MAX(conts.civicrm_contribution_total_amount_count) as civicrm_contribution_total_amount_count
+ SUM( conts.total_amount ) as total_amount,
+ AVG( conts.total_amount ) as total_avg,
+ conts.currency as currency
FROM (
- SELECT civicrm_contribution.total_amount,
- COUNT(civicrm_contribution.total_amount) as civicrm_contribution_total_amount_count,
- civicrm_contribution.currency
+ SELECT civicrm_contribution_soft.amount as total_amount, civicrm_contribution_soft.currency
$from
- $where AND civicrm_contribution.contribution_status_id = 1
- GROUP BY currency, civicrm_contribution.total_amount
- ORDER BY civicrm_contribution_total_amount_count DESC
+ $where AND civicrm_contribution.contribution_status_id = 1 AND civicrm_contribution_soft.id IS NOT NULL
+ GROUP BY civicrm_contribution_soft.id
) as conts
GROUP BY currency";
- $summary['total']['mode'] = CRM_Contribute_BAO_Contribution::computeStats('mode', $modeSQL);
+ $dao = CRM_Core_DAO::executeQuery($query);
+ $summary['soft_credit']['count'] = 0;
+ $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = [];
+ while ($dao->fetch()) {
+ $summary['soft_credit']['count'] += $dao->total_count;
+ $summary['soft_credit']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency);
+ $summary['soft_credit']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency);
+ }
+ if (!empty($summary['soft_credit']['amount'])) {
+ $summary['soft_credit']['amount'] = implode(', ', $summary['soft_credit']['amount']);
+ $summary['soft_credit']['avg'] = implode(', ', $summary['soft_credit']['avg']);
+ }
+ else {
+ $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = 0;
+ }
}
/**
- * Add the median to the stats.
- *
- * Note that is can be a very slow query - taking many many minutes and even on a small
- * data set it's likely to take longer than all the other queries combined by a significant
- * multiple
- *
- * see https://lab.civicrm.org/dev/core/issues/720
+ * Add basic stats about cancelled contributions to the summary.
*
* @param array $summary
* @param string $where
* @param string $from
*/
- protected function addMedianToStats(&$summary, $where, $from) {
- $medianSQL = "{$from} {$where} AND civicrm_contribution.contribution_status_id = 1 ";
- $summary['total']['median'] = CRM_Contribute_BAO_Contribution::computeStats('median', $medianSQL, 'civicrm_contribution');
+ protected function addBasicCancelStatsToSummary(&$summary, $where, $from) {
+ $query = "
+ SELECT COUNT( conts.total_amount ) as cancel_count,
+ SUM( conts.total_amount ) as cancel_amount,
+ AVG( conts.total_amount ) as cancel_avg,
+ conts.currency as currency
+ FROM (
+ SELECT civicrm_contribution.total_amount, civicrm_contribution.currency
+ $from
+ $where AND civicrm_contribution.cancel_date IS NOT NULL
+ GROUP BY civicrm_contribution.id
+ ) as conts
+ GROUP BY currency";
+
+ $dao = CRM_Core_DAO::executeQuery($query);
+
+ if ($dao->N <= 1) {
+ if ($dao->fetch()) {
+ $summary['cancel']['count'] = $dao->cancel_count;
+ $summary['cancel']['amount'] = CRM_Utils_Money::format($dao->cancel_amount, $dao->currency);
+ $summary['cancel']['avg'] = CRM_Utils_Money::format($dao->cancel_avg, $dao->currency);
+ }
+ }
+ else {
+ $summary['cancel']['count'] = 0;
+ $summary['cancel']['amount'] = $summary['cancel']['avg'] = [];
+ while ($dao->fetch()) {
+ $summary['cancel']['count'] += $dao->cancel_count;
+ $summary['cancel']['amount'][] = CRM_Utils_Money::format($dao->cancel_amount, $dao->currency);
+ $summary['cancel']['avg'][] = CRM_Utils_Money::format($dao->cancel_avg, $dao->currency);
+ }
+ $summary['cancel']['amount'] = implode(', ', $summary['cancel']['amount']);
+ $summary['cancel']['avg'] = implode(', ', $summary['cancel']['avg']);
+ }
+ }
+
+ /**
+ * Create the sql query for an contact search.
+ *
+ * @param int $offset
+ * The offset for the query.
+ * @param int $rowCount
+ * The number of rows to return.
+ * @param string|CRM_Utils_Sort $sort
+ * The order by string.
+ * @param bool $count
+ * Is this a count only query ?.
+ * @param bool $includeContactIds
+ * Should we include contact ids?.
+ * @param bool $sortByChar
+ * If true returns the distinct array of first characters for search results.
+ * @param bool $groupContacts
+ * If true, return only the contact ids.
+ * @param string $additionalWhereClause
+ * If the caller wants to further restrict the search (used for components).
+ * @param null $sortOrder
+ * @param string $additionalFromClause
+ * Should be clause with proper joins, effective to reduce where clause load.
+ *
+ * @param bool $skipOrderAndLimit
+ * @return string
+ */
+ public function getSearchSQL(
+ $offset = 0, $rowCount = 0, $sort = NULL,
+ $count = FALSE, $includeContactIds = FALSE,
+ $sortByChar = FALSE, $groupContacts = FALSE,
+ $additionalWhereClause = NULL, $sortOrder = NULL,
+ $additionalFromClause = NULL, $skipOrderAndLimit = FALSE) {
+ if ($includeContactIds) {
+ $this->_includeContactIds = TRUE;
+ $this->_whereClause = $this->whereClause();
+ }
+
+ $onlyDeleted = in_array([
+ 'deleted_contacts',
+ '=',
+ '1',
+ '0',
+ '0'
+ ], $this->_params);
+
+ // if we’re explicitly looking for a certain contact’s contribs, events, etc.
+ // and that contact happens to be deleted, set $onlyDeleted to true
+ foreach ($this->_params as $values) {
+ $name = CRM_Utils_Array::value(0, $values);
+ $op = CRM_Utils_Array::value(1, $values);
+ $value = CRM_Utils_Array::value(2, $values);
+ if ($name == 'contact_id' and $op == '=') {
+ if (CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'is_deleted')) {
+ $onlyDeleted = TRUE;
+ }
+ break;
+ }
+ }
+
+ // building the query string
+ $groupBy = $groupByCols = NULL;
+ if (!$count) {
+ if (isset($this->_groupByComponentClause)) {
+ $groupByCols = preg_replace('/^GROUP BY /', '', trim($this->_groupByComponentClause));
+ $groupByCols = explode(', ', $groupByCols);
+ }
+ elseif ($this->_useGroupBy) {
+ $groupByCols = ['contact_a.id'];
+ }
+ }
+ if ($this->_mode & CRM_Contact_BAO_Query::MODE_ACTIVITY && (!$count)) {
+ $groupByCols = ['civicrm_activity.id'];
+ }
+ if (!empty($groupByCols)) {
+ $groupBy = " GROUP BY " . implode(', ', $groupByCols);
+ }
+
+ $order = $orderBy = $limit = '';
+ if (!$count) {
+ list($order, $additionalFromClause) = $this->prepareOrderBy($sort, $sortByChar, $sortOrder, $additionalFromClause);
+
+ if ($rowCount > 0 && $offset >= 0) {
+ $offset = CRM_Utils_Type::escape($offset, 'Int');
+ $rowCount = CRM_Utils_Type::escape($rowCount, 'Int');
+ $limit = " LIMIT $offset, $rowCount ";
+ }
+ }
+ // Two cases where we are disabling FGB (FULL_GROUP_BY_MODE):
+ // 1. Expecting the search query to return all the first single letter characters of contacts ONLY, but when FGB is enabled
+ // MySQL expect the columns present in GROUP BY, must be present in SELECT clause and that results into error, needless to have other columns.
+ // 2. When GROUP BY columns are present then disable FGB otherwise it demands to add ORDER BY columns in GROUP BY and eventually in SELECT
+ // clause. This will impact the search query output.
+ $disableFullGroupByMode = ($sortByChar || !empty($groupBy) || $groupContacts);
+
+ if ($disableFullGroupByMode) {
+ CRM_Core_DAO::disableFullGroupByMode();
+ }
+
+ // CRM-15231
+ $this->_sort = $sort;
+
+ //CRM-15967
+ $this->includePseudoFieldsJoin($sort);
+
+ list($select, $from, $where, $having) = $this->query($count, $sortByChar, $groupContacts, $onlyDeleted);
+
+ if ($additionalWhereClause) {
+ $where = $where . ' AND ' . $additionalWhereClause;
+ }
+
+ //additional from clause should be w/ proper joins.
+ if ($additionalFromClause) {
+ $from .= "\n" . $additionalFromClause;
+ }
+
+ // if we are doing a transform, do it here
+ // use the $from, $where and $having to get the contact ID
+ if ($this->_displayRelationshipType) {
+ $this->filterRelatedContacts($from, $where, $having);
+ }
+
+ if ($skipOrderAndLimit) {
+ $query = "$select $from $where $having $groupBy";
+ }
+ else {
+ $query = "$select $from $where $having $groupBy $order $limit";
+ }
+ return $query;
+ }
+
+ /**
+ * Get the metadata for a given field.
+ *
+ * @param string $fieldName
+ *
+ * @return array
+ */
+ protected function getMetadataForField($fieldName) {
+ if ($fieldName === 'contact_a.id') {
+ // This seems to be the only anomaly.
+ $fieldName = 'id';
+ }
+ $pseudoField = isset($this->_pseudoConstantsSelect[$fieldName]) ? $this->_pseudoConstantsSelect[$fieldName] : [];
+ $field = isset($this->_fields[$fieldName]) ? $this->_fields[$fieldName] : $pseudoField;
+ $field = array_merge($field, $pseudoField);
+ if (!empty($field) && empty($field['name'])) {
+ // standardising field formatting here - over time we can phase out variants.
+ // all paths using this currently unit tested
+ $field['name'] = CRM_Utils_Array::value('field_name', $field, $field['idCol']);
+ }
+ return $field;
+ }
+
+ /**
+ * Get the metadata for a given field, returning the 'real field' if it is a pseudofield.
+ *
+ * @param string $fieldName
+ *
+ * @return array
+ */
+ protected function getMetadataForRealField($fieldName) {
+ $field = $this->getMetadataForField($fieldName);
+ return empty($field['is_pseudofield_for']) ? $field : $this->getMetadataForField($field['is_pseudofield_for']);
}
}
projects / civicrm-core.git / blobdiff