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 b8e854fd2994de6403fc8f47dbe85c8b3fc3cf71..fc350b2a6f67a44668600a7698ee7c6c2834bc0e 100644 (file)
--- a/CRM/Report/Form/Contribute/Bookkeeping.php
+++ b/CRM/Report/Form/Contribute/Bookkeeping.php
@@ -3,7 +3,7 @@
  +--------------------------------------------------------------------+
  | CiviCRM version 4.7                                                |
  +--------------------------------------------------------------------+
- | Copyright CiviCRM LLC (c) 2004-2015                                |
+ | Copyright CiviCRM LLC (c) 2004-2016                                |
  +--------------------------------------------------------------------+
  | This file is a part of CiviCRM.                                    |
  |                                                                    |
@@ -28,7 +28,7 @@
 /**
  *
  * @package CRM
- * @copyright CiviCRM LLC (c) 2004-2015
+ * @copyright CiviCRM LLC (c) 2004-2016
  * $Id$
  *
  */
@@ -190,11 +190,13 @@ class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form {
           ),
           'credit_accounting_code' => array(
             'title' => ts('Financial Account Code - Credit'),
+            'type' => CRM_Utils_Type::T_INT,
             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
             'options' => CRM_Contribute_PseudoConstant::financialAccount(NULL, NULL, 'accounting_code', 'accounting_code'),
           ),
           'debit_name' => array(
             'title' => ts('Financial Account Name - Debit'),
+            'type' => CRM_Utils_Type::T_STRING,
             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
             'options' => CRM_Contribute_PseudoConstant::financialAccount(),
             'name' => 'id',
@@ -202,6 +204,7 @@ class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form {
           ),
           'credit_name' => array(
             'title' => ts('Financial Account Name - Credit'),
+            'type' => CRM_Utils_Type::T_STRING,
             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
             'options' => CRM_Contribute_PseudoConstant::financialAccount(),
           ),
@@ -218,6 +221,7 @@ class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form {
         'filters' => array(
           'financial_type_id' => array(
             'title' => ts('Financial Type'),
+            'type' => CRM_Utils_Type::T_INT,
             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
             'options' => CRM_Financial_BAO_FinancialType::getAvailableFinancialTypes(),
           ),
@@ -226,6 +230,21 @@ class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form {
           'financial_type_id' => array('title' => ts('Financial Type')),
         ),
       ),
+      'civicrm_batch' => array(
+        'dao' => 'CRM_Batch_DAO_Batch',
+        'fields' => array(
+          'title' => array(
+            'title' => ts('Batch Title'),
+            'alias' => 'batch',
+            'default' => FALSE,
+          ),
+          'name' => array(
+            'title' => ts('Batch Name'),
+            'alias' => 'batch',
+            'default' => TRUE,
+          ),
+        ),
+      ),
       'civicrm_contribution' => array(
         'dao' => 'CRM_Contribute_DAO_Contribution',
         'fields' => array(
@@ -288,6 +307,7 @@ class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form {
         'filters' => array(
           'payment_instrument_id' => array(
             'title' => ts('Payment Method'),
+            'type' => CRM_Utils_Type::T_INT,
             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
             'options' => CRM_Contribute_PseudoConstant::paymentInstrument(),
           ),
@@ -377,6 +397,7 @@ class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form {
         }
       }
     }
+    $this->_selectClauses = $select;
 
     $this->_select = 'SELECT ' . implode(', ', $select) . ' ';
   }
@@ -410,6 +431,13 @@ class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form {
                     ON fitem.financial_account_id = {$this->_aliases['civicrm_financial_account']}_credit_2.id
               LEFT JOIN civicrm_line_item {$this->_aliases['civicrm_line_item']}
                     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'
+              LEFT JOIN civicrm_batch batch
+                    ON  ent_batch.batch_id = batch.id";
+    }
+
     $this->getPermissionedFTQuery($this, "civicrm_line_item_1");
   }
 
@@ -485,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}";
   }
 
   /**
@@ -496,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} ";