From 22da2be666508f5eef11b07be25bd4049ec9d942 Mon Sep 17 00:00:00 2001 From: Jon Goldberg Date: Mon, 7 Oct 2019 19:16:06 -0400 Subject: [PATCH] reporting#21 - don't multiple contribution details when a 1-to-many relationship exists --- CRM/Report/Form/Contribute/Detail.php | 20 ++++++++++++-------- 1 file changed, 12 insertions(+), 8 deletions(-) diff --git a/CRM/Report/Form/Contribute/Detail.php b/CRM/Report/Form/Contribute/Detail.php index a6117b3808..ea8615ae0c 100644 --- a/CRM/Report/Form/Contribute/Detail.php +++ b/CRM/Report/Form/Contribute/Detail.php @@ -413,17 +413,21 @@ class CRM_Report_Form_Contribute_Detail extends CRM_Report_Form { $totalAmount = $average = $fees = $net = []; $count = 0; $select = " - SELECT COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as count, - SUM( {$this->_aliases['civicrm_contribution']}.total_amount ) as amount, - ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as avg, - {$this->_aliases['civicrm_contribution']}.currency as currency, - SUM( {$this->_aliases['civicrm_contribution']}.fee_amount ) as fees, - SUM( {$this->_aliases['civicrm_contribution']}.net_amount ) as net + SELECT COUNT(civicrm_contribution_total_amount ) as count, + SUM( civicrm_contribution_total_amount ) as amount, + ROUND(AVG(civicrm_contribution_total_amount), 2) as avg, + stats.currency as currency, + SUM( stats.fee_amount ) as fees, + SUM( stats.net_amount ) as net "; - $group = "\nGROUP BY {$this->_aliases['civicrm_contribution']}.currency"; - $sql = "{$select} {$this->_from} {$this->_where} {$group}"; + $group = "\nGROUP BY civicrm_contribution_currency"; + $from = " FROM {$this->temporaryTables['civireport_contribution_detail_temp3']['name']} " + . "JOIN civicrm_contribution stats ON {$this->temporaryTables['civireport_contribution_detail_temp3']['name']}.civicrm_contribution_contribution_id = stats.id "; + $sql = "{$select} {$from} {$group} "; + CRM_Core_DAO::disableFullGroupByMode(); $dao = CRM_Core_DAO::executeQuery($sql); + CRM_Core_DAO::reenableFullGroupByMode(); $this->addToDeveloperTab($sql); while ($dao->fetch()) { -- 2.25.1