CRM-18439 - Report Fixes to include Full Group by clause

[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 f71433d68983fdf32119bb0d557d7bd4eede97ad..fc350b2a6f67a44668600a7698ee7c6c2834bc0e 100644 (file)
--- a/CRM/Report/Form/Contribute/Bookkeeping.php
+++ b/CRM/Report/Form/Contribute/Bookkeeping.php
@@ -397,6 +397,7 @@ class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form {
         }
       }
     }
+    $this->_selectClauses = $select;
 
     $this->_select = 'SELECT ' . implode(', ', $select) . ' ';
   }
@@ -432,7 +433,7 @@ class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form {
                     ON  fitem.entity_id = {$this->_aliases['civicrm_line_item']}.id AND fitem.entity_table = 'civicrm_line_item' ";
     if ($this->isTableSelected('civicrm_batch')) {
       $this->_from .= "LEFT JOIN civicrm_entity_batch ent_batch
-                    ON  {$this->_aliases['civicrm_financial_trxn']}.id = ent_batch.entity_id AND ent_batch.entity_table = 'civicrm_financial_trxn' 
+                    ON  {$this->_aliases['civicrm_financial_trxn']}.id = ent_batch.entity_id AND ent_batch.entity_table = 'civicrm_financial_trxn'
               LEFT JOIN civicrm_batch batch
                     ON  ent_batch.batch_id = batch.id";
     }
@@ -512,7 +513,13 @@ class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form {
   }
 
   public function groupBy() {
-    $this->_groupBy = " GROUP BY  {$this->_aliases['civicrm_entity_financial_trxn']}.id, {$this->_aliases['civicrm_line_item']}.id ";
+    $groupFromSelect = '';
+    $groupBy = array(
+      "{$this->_aliases['civicrm_entity_financial_trxn']}.id",
+      "{$this->_aliases['civicrm_line_item']}.id"
+    );
+    $groupFromSelect = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy);
+    $this->_groupBy = " GROUP BY " . implode(', ', $groupBy) . " {$groupFromSelect}";
   }
 
   /**
@@ -523,13 +530,20 @@ class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form {
   public function statistics(&$rows) {
     $statistics = parent::statistics($rows);
     $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
-";
+
+    $this->_selectClauses = array(
+      "{$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",
+    );
+    $select = "SELECT " . implode(', ', $this->_selectClauses);
+
+    $this->groupBy();
 
     $tempQuery = "CREATE TEMPORARY TABLE {$tempTableName} CHARACTER SET utf8 COLLATE utf8_unicode_ci AS
                   {$select} {$this->_from} {$this->_where} {$this->_groupBy} ";