From 6d68fd766b5e7bb5e23382315283bbecd3b172bd Mon Sep 17 00:00:00 2001 From: eileen Date: Fri, 15 Feb 2019 16:26:24 +1300 Subject: [PATCH] [REF] extract basic soft credit stats to separate function --- CRM/Contact/BAO/Query.php | 104 ++++++++++++++++++-------------------- 1 file changed, 49 insertions(+), 55 deletions(-) diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index 04e635399d..28086b6b25 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -5103,6 +5103,10 @@ civicrm_relationship.start_date > {$today} $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']); if (!empty($summary['total']['amount'])) { @@ -5115,63 +5119,15 @@ civicrm_relationship.start_date > {$today} $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, + $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"; - - $where .= " AND civicrm_contribution.cancel_date IS NOT NULL "; - if ($context == 'search') { - $where .= " AND contact_a.is_deleted = 0 "; - } - - $query = "$select FROM ( - SELECT civicrm_contribution.total_amount, civicrm_contribution.currency $from $where + 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"; @@ -6700,4 +6656,42 @@ AND displayRelType.is_active = 1 $summary['total']['median'] = CRM_Contribute_BAO_Contribution::computeStats('median', $medianSQL, 'civicrm_contribution'); } + /** + * 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; + } + } + } -- 2.25.1