CRM-17251 - Book-keeping report displays rows inaccurately when contribution amount...

[civicrm-core.git] / CRM / Report / Form / Contribute / Bookkeeping.php

diff --git a/CRM/Report/Form/Contribute/Bookkeeping.php b/CRM/Report/Form/Contribute/Bookkeeping.php
index 864aacba44cf4edd1b0b65084932e3926112c9c4..e084aefe927e0ba01f62e7e19789a79ee2441bba 100644 (file)
--- a/CRM/Report/Form/Contribute/Bookkeeping.php
+++ b/CRM/Report/Form/Contribute/Bookkeeping.php
@@ -483,6 +483,10 @@ class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form {
     parent::postProcess();
   }
 
+  public function groupBy() {
+    $this->_groupBy = " GROUP BY  {$this->_aliases['civicrm_entity_financial_trxn']}.id, {$this->_aliases['civicrm_line_item']}.id ";
+  }
+
   /**
    * @param $rows
    *
@@ -490,20 +494,22 @@ class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form {
    */
   public function statistics(&$rows) {
     $statistics = parent::statistics($rows);
-
-    $select = " SELECT COUNT({$this->_aliases['civicrm_financial_trxn']}.id ) as count,
-                {$this->_aliases['civicrm_contribution']}.currency,
-                SUM(CASE
-                  WHEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id IS NOT NULL
-                  THEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.amount
-                  ELSE {$this->_aliases['civicrm_entity_financial_trxn']}.amount
-                END) as amount
+    $tempTableName = CRM_Core_DAO::createTempTableName('civicrm_contribution');
+    $select = "SELECT {$this->_aliases['civicrm_contribution']}.id, {$this->_aliases['civicrm_entity_financial_trxn']}.id as trxnID, {$this->_aliases['civicrm_contribution']}.currency,
+               CASE
+                 WHEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id IS NOT NULL
+                 THEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.amount
+                 ELSE {$this->_aliases['civicrm_entity_financial_trxn']}.amount
+               END as amount
 ";
 
-    $sql = "{$select} {$this->_from} {$this->_where}
-            GROUP BY {$this->_aliases['civicrm_contribution']}.currency
-";
+    $tempQuery = "CREATE TEMPORARY TABLE {$tempTableName} CHARACTER SET utf8 COLLATE utf8_unicode_ci AS
+                  {$select} {$this->_from} {$this->_where} {$this->_groupBy} ";
+    CRM_Core_DAO::executeQuery($tempQuery);
 
+    $sql = "SELECT COUNT(trxnID) as count, SUM(amount) as amount, currency
+            FROM {$tempTableName}
+            GROUP BY currency";
     $dao = CRM_Core_DAO::executeQuery($sql);
     $amount = $avg = array();
     while ($dao->fetch()) {