From b64b7a9e28db9216a7663c25464a3dd6725b09b8 Mon Sep 17 00:00:00 2001 From: eileen Date: Mon, 18 Feb 2019 13:31:00 +1300 Subject: [PATCH] Remove median & mode stats from contribution summary in order to improve performance --- CRM/Contact/BAO/Query.php | 61 +------------------ .../Contribute/Page/ContributionTotals.tpl | 6 +- tests/phpunit/CRM/Contact/BAO/QueryTest.php | 5 -- 3 files changed, 3 insertions(+), 69 deletions(-) diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index 5247434ab8..db917cd470 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -5100,15 +5100,11 @@ civicrm_relationship.start_date > {$today} $summary = ['total' => []]; $this->addBasicStatsToSummary($summary, $where, $from); - $this->addModeToStats($summary, $from, $where); - $this->addMedianToStats($summary, $where, $from); if (CRM_Contribute_BAO_Query::isSoftCreditOptionEnabled()) { $this->addBasicSoftCreditStatsToStats($summary, $where, $from); } - $summary['total']['currencyCount'] = count($summary['total']['median']); - $this->addBasicCancelStatsToSummary($summary, $where, $from); return $summary; @@ -6558,70 +6554,17 @@ AND displayRelType.is_active = 1 $summary['total']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency); $summary['total']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency); } + $summary['total']['currencyCount'] = count($summary['total']['amount']); 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'] = $summary['total']['median'] = 0; + $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 - * - * @param array $summary - * @param string $from - * @param string $where - */ - protected function addModeToStats(&$summary, $from, $where) { - $modeSQL = " - 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 - FROM ( - SELECT civicrm_contribution.total_amount, - COUNT(civicrm_contribution.total_amount) as civicrm_contribution_total_amount_count, - civicrm_contribution.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 - ) as conts - GROUP BY currency"; - - $mode = CRM_Contribute_BAO_Contribution::computeStats('mode', $modeSQL); - $summary['total']['mode'] = implode(', ', (array) $mode); - } - - /** - * 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 - * - * @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 "; - $median = CRM_Contribute_BAO_Contribution::computeStats('median', $medianSQL, 'civicrm_contribution'); - $summary['total']['median'] = implode(', ', (array) $median); - } - /** * Add basic soft credit statistics to summary array. * diff --git a/templates/CRM/Contribute/Page/ContributionTotals.tpl b/templates/CRM/Contribute/Page/ContributionTotals.tpl index e995ee4e0f..3e5576d8df 100644 --- a/templates/CRM/Contribute/Page/ContributionTotals.tpl +++ b/templates/CRM/Contribute/Page/ContributionTotals.tpl @@ -46,14 +46,10 @@   {ts}# Completed{/ts} – {$contributionSummary.total.count} {ts}Avg{/ts} – {$contributionSummary.total.avg} -   {ts}Median{/ts} – {$contributionSummary.total.median} -   {ts}Mode{/ts} – {$contributionSummary.total.mode} {else} {ts}Total{/ts} – {$contributionSummary.total.amount}   {ts}# Completed{/ts} – {$contributionSummary.total.count} -   {ts}Avg{/ts} – {$contributionSummary.total.avg} -   {ts}Median{/ts} – {$contributionSummary.total.median} -   {ts}Mode{/ts} – {$contributionSummary.total.mode} +   {ts}Avg{/ts} – {$contributionSummary.total.avg} {/if} {/if} {if $contributionSummary.cancel.amount} diff --git a/tests/phpunit/CRM/Contact/BAO/QueryTest.php b/tests/phpunit/CRM/Contact/BAO/QueryTest.php index c0f4af3740..dc0cc0afff 100644 --- a/tests/phpunit/CRM/Contact/BAO/QueryTest.php +++ b/tests/phpunit/CRM/Contact/BAO/QueryTest.php @@ -734,8 +734,6 @@ civicrm_relationship.is_active = 1 AND 'avg' => '$ 233.33', 'amount' => '$ 1,400.00', 'count' => 6, - 'mode' => '$ 300.00', - 'median' => '$ 300.00', 'currencyCount' => 1, ], 'cancel' => [ @@ -773,9 +771,6 @@ civicrm_relationship.is_active = 1 AND 'avg' => '$ 200.00', 'amount' => '$ 400.00', 'count' => 2, - 'mode' => 'N/A', - 'median' => '$ 200.00', - 'currencyCount' => 1, ], 'cancel' => [ 'count' => 1, -- 2.25.1