+ /**
+ * 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.
+ *
+ * @param array $summary
+ * @param string $where
+ * @param string $from
+ */
+ 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
+ FROM (
+ SELECT civicrm_contribution_soft.amount as total_amount, civicrm_contribution_soft.currency
+ $from
+ $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";
+
+ $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 basic stats about cancelled contributions to the summary.
+ *
+ * @param array $summary
+ * @param string $where
+ * @param string $from
+ */
+ 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']);
+ }
+ }
+