CRM/Report/Form/Contribute/Bookkeeping.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2018                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  10  | CiviCRM is free software; you can copy, modify, and distribute it  |
  11  | under the terms of the GNU Affero General Public License           |
  12  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  14  | CiviCRM is distributed in the hope that it will be useful, but     |
  15  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17  | See the GNU Affero General Public License for more details.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2018
  32  */
  33 class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form {
  34
  35   protected $_summary = NULL;
  36
  37   protected $_customGroupExtends = array(
  38     'Contact',
  39     'Individual',
  40     'Contribution',
  41     'Membership',
  42   );
  43
  44   /**
  45    * This report has not been optimised for group filtering.
  46    *
  47    * The functionality for group filtering has been improved but not
  48    * all reports have been adjusted to take care of it. This report has not
  49    * and will run an inefficient query until fixed.
  50    *
  51    * CRM-19170
  52    *
  53    * @var bool
  54    */
  55   protected $groupFilterNotOptimised = TRUE;
  56
  57   /**
  58    * Class constructor.
  59    */
  60   public function __construct() {
  61     $this->_autoIncludeIndexedFieldsAsOrderBys = 1;
  62     $this->_columns = array(
  63       'civicrm_contact' => array(
  64         'dao' => 'CRM_Contact_DAO_Contact',
  65         'fields' => array(
  66           'sort_name' => array(
  67             'title' => ts('Contact Name'),
  68             'required' => TRUE,
  69             'no_repeat' => TRUE,
  70           ),
  71           'first_name' => array(
  72             'title' => ts('First Name'),
  73           ),
  74           'middle_name' => array(
  75             'title' => ts('Middle Name'),
  76           ),
  77           'last_name' => array(
  78             'title' => ts('Last Name'),
  79           ),
  80           'id' => array(
  81             'no_display' => TRUE,
  82             'required' => TRUE,
  83           ),
  84           'gender_id' => array(
  85             'title' => ts('Gender'),
  86           ),
  87           'birth_date' => array(
  88             'title' => ts('Birth Date'),
  89           ),
  90           'age' => array(
  91             'title' => ts('Age'),
  92             'dbAlias' => 'TIMESTAMPDIFF(YEAR, contact_civireport.birth_date, CURDATE())',
  93           ),
  94           'contact_type' => array(
  95             'title' => ts('Contact Type'),
  96           ),
  97           'contact_sub_type' => array(
  98             'title' => ts('Contact Subtype'),
  99           ),
 100         ),
 101         'grouping' => 'contact-fields',
 102         'order_bys' => array(
 103           'sort_name' => array(
 104             'title' => ts('Last Name, First Name'),
 105             'default' => '1',
 106             'default_weight' => '0',
 107             'default_order' => 'ASC',
 108           ),
 109           'first_name' => array(
 110             'name' => 'first_name',
 111             'title' => ts('First Name'),
 112           ),
 113           'gender_id' => array(
 114             'name' => 'gender_id',
 115             'title' => ts('Gender'),
 116           ),
 117           'birth_date' => array(
 118             'name' => 'birth_date',
 119             'title' => ts('Birth Date'),
 120           ),
 121           'contact_type' => array(
 122             'title' => ts('Contact Type'),
 123           ),
 124           'contact_sub_type' => array(
 125             'title' => ts('Contact Subtype'),
 126           ),
 127         ),
 128         'filters' => array(
 129           'sort_name' => array(
 130             'title' => ts('Contact Name'),
 131             'operator' => 'like',
 132           ),
 133           'id' => array(
 134             'title' => ts('Contact ID'),
 135             'no_display' => TRUE,
 136           ),
 137           'gender_id' => array(
 138             'title' => ts('Gender'),
 139             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 140             'options' => CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'gender_id'),
 141           ),
 142           'birth_date' => array(
 143             'title' => ts('Birth Date'),
 144             'operatorType' => CRM_Report_Form::OP_DATE,
 145           ),
 146           'contact_type' => array(
 147             'title' => ts('Contact Type'),
 148           ),
 149           'contact_sub_type' => array(
 150             'title' => ts('Contact Subtype'),
 151           ),
 152         ),
 153       ),
 154       'civicrm_membership' => array(
 155         'dao' => 'CRM_Member_DAO_Membership',
 156         'fields' => array(
 157           'id' => array(
 158             'title' => ts('Membership #'),
 159             'no_display' => TRUE,
 160             'required' => TRUE,
 161           ),
 162         ),
 163       ),
 164       'civicrm_financial_account' => array(
 165         'dao' => 'CRM_Financial_DAO_FinancialAccount',
 166         'fields' => array(
 167           'debit_accounting_code' => array(
 168             'title' => ts('Financial Account Code - Debit'),
 169             'name' => 'accounting_code',
 170             'alias' => 'financial_account_civireport_debit',
 171             'default' => TRUE,
 172           ),
 173           'debit_contact_id' => array(
 174             'title' => ts('Financial Account Owner - Debit'),
 175             'name' => 'organization_name',
 176             'alias' => 'debit_contact',
 177           ),
 178           'credit_accounting_code' => array(
 179             'title' => ts('Financial Account Code - Credit'),
 180             'name' => 'accounting_code',
 181             'alias' => 'financial_account_civireport_credit',
 182             'default' => TRUE,
 183           ),
 184           'credit_contact_id' => array(
 185             'title' => ts('Financial Account Owner - Credit'),
 186             'name' => 'organization_name',
 187             'alias' => 'credit_contact',
 188           ),
 189           'debit_name' => array(
 190             'title' => ts('Financial Account Name - Debit'),
 191             'name' => 'name',
 192             'alias' => 'financial_account_civireport_debit',
 193             'default' => TRUE,
 194           ),
 195           'credit_name' => array(
 196             'title' => ts('Financial Account Name - Credit'),
 197             'name' => 'name',
 198             'alias' => 'financial_account_civireport_credit',
 199             'default' => TRUE,
 200           ),
 201         ),
 202         'filters' => array(
 203           'debit_accounting_code' => array(
 204             'title' => ts('Financial Account Code - Debit'),
 205             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 206             'options' => CRM_Contribute_PseudoConstant::financialAccount(NULL, NULL, 'accounting_code', 'accounting_code'),
 207             'name' => 'accounting_code',
 208             'alias' => 'financial_account_civireport_debit',
 209           ),
 210           'debit_contact_id' => array(
 211             'title' => ts('Financial Account Owner - Debit'),
 212             'operatorType' => CRM_Report_Form::OP_SELECT,
 213             'type' => CRM_Utils_Type::T_INT,
 214             'options' => array('' => '- Select Organization -') + CRM_Financial_BAO_FinancialAccount::getOrganizationNames(),
 215             'name' => 'contact_id',
 216             'alias' => 'financial_account_civireport_debit',
 217           ),
 218           'credit_accounting_code' => array(
 219             'title' => ts('Financial Account Code - Credit'),
 220             'type' => CRM_Utils_Type::T_INT,
 221             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 222             'options' => CRM_Contribute_PseudoConstant::financialAccount(NULL, NULL, 'accounting_code', 'accounting_code'),
 223             'name' => 'accounting_code',
 224             'alias' => 'financial_account_civireport_credit',
 225           ),
 226           'credit_contact_id' => array(
 227             'title' => ts('Financial Account Owner - Credit'),
 228             'operatorType' => CRM_Report_Form::OP_SELECT,
 229             'type' => CRM_Utils_Type::T_INT,
 230             'options' => array('' => '- Select Organization -') + CRM_Financial_BAO_FinancialAccount::getOrganizationNames(),
 231             'name' => 'contact_id',
 232             'alias' => 'financial_account_civireport_credit',
 233           ),
 234           'debit_name' => array(
 235             'title' => ts('Financial Account Name - Debit'),
 236             'type' => CRM_Utils_Type::T_STRING,
 237             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 238             'options' => CRM_Contribute_PseudoConstant::financialAccount(),
 239             'name' => 'id',
 240             'alias' => 'financial_account_civireport_debit',
 241           ),
 242           'credit_name' => array(
 243             'title' => ts('Financial Account Name - Credit'),
 244             'type' => CRM_Utils_Type::T_STRING,
 245             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 246             'options' => CRM_Contribute_PseudoConstant::financialAccount(),
 247             'name' => 'id',
 248             'alias' => 'financial_account_civireport_credit',
 249           ),
 250         ),
 251       ),
 252       'civicrm_line_item' => array(
 253         'dao' => 'CRM_Price_DAO_LineItem',
 254         'fields' => array(
 255           'financial_type_id' => array(
 256             'title' => ts('Financial Type'),
 257             'default' => TRUE,
 258           ),
 259         ),
 260         'filters' => array(
 261           'financial_type_id' => array(
 262             'title' => ts('Financial Type'),
 263             'type' => CRM_Utils_Type::T_INT,
 264             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 265             'options' => CRM_Financial_BAO_FinancialType::getAvailableFinancialTypes(),
 266           ),
 267         ),
 268         'order_bys' => array(
 269           'financial_type_id' => array('title' => ts('Financial Type')),
 270         ),
 271       ),
 272       'civicrm_batch' => array(
 273         'dao' => 'CRM_Batch_DAO_Batch',
 274         'fields' => array(
 275           'title' => array(
 276             'title' => ts('Batch Title'),
 277             'alias' => 'batch',
 278             'default' => FALSE,
 279           ),
 280           'name' => array(
 281             'title' => ts('Batch Name'),
 282             'alias' => 'batch',
 283             'default' => TRUE,
 284           ),
 285         ),
 286       ),
 287       'civicrm_contribution' => array(
 288         'dao' => 'CRM_Contribute_DAO_Contribution',
 289         'fields' => array(
 290           'receive_date' => array(
 291             'default' => TRUE,
 292           ),
 293           'invoice_id' => array(
 294             'title' => ts('Invoice Reference'),
 295             'default' => TRUE,
 296           ),
 297           'invoice_number' => array(
 298             'title' => ts('Invoice Number'),
 299           ),
 300           'contribution_status_id' => array(
 301             'title' => ts('Contribution Status'),
 302             'default' => TRUE,
 303           ),
 304           'id' => array(
 305             'title' => ts('Contribution ID'),
 306             'default' => TRUE,
 307           ),
 308         ),
 309         'filters' => array(
 310           'contribution_id' => array(
 311             'title' => ts('Contribution ID'),
 312             'name' => 'id',
 313             'operatorType' => CRM_Report_Form::OP_INT,
 314             'type' => CRM_Utils_Type::T_INT,
 315           ),
 316           'receive_date' => array('operatorType' => CRM_Report_Form::OP_DATE),
 317           'contribution_status_id' => array(
 318             'title' => ts('Contribution Status'),
 319             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 320             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 321             'default' => array(1),
 322           ),
 323         ),
 324         'order_bys' => array(
 325           'contribution_id' => array('title' => ts('Contribution #')),
 326           'contribution_status_id' => array('title' => ts('Contribution Status')),
 327         ),
 328         'grouping' => 'contri-fields',
 329       ),
 330       'civicrm_financial_trxn' => array(
 331         'dao' => 'CRM_Financial_DAO_FinancialTrxn',
 332         'fields' => array(
 333           'check_number' => array(
 334             'title' => ts('Cheque #'),
 335             'default' => TRUE,
 336           ),
 337           'payment_instrument_id' => array(
 338             'title' => ts('Payment Method'),
 339             'default' => TRUE,
 340           ),
 341           'currency' => array(
 342             'required' => TRUE,
 343             'no_display' => TRUE,
 344           ),
 345           'trxn_date' => array(
 346             'title' => ts('Transaction Date'),
 347             'default' => TRUE,
 348             'type' => CRM_Utils_Type::T_DATE,
 349           ),
 350           'trxn_id' => array(
 351             'title' => ts('Trans #'),
 352             'default' => TRUE,
 353           ),
 354           'card_type_id' => array(
 355             'title' => ts('Credit Card Type'),
 356           ),
 357         ),
 358         'filters' => array(
 359           'payment_instrument_id' => array(
 360             'title' => ts('Payment Method'),
 361             'type' => CRM_Utils_Type::T_INT,
 362             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 363             'options' => CRM_Contribute_PseudoConstant::paymentInstrument(),
 364           ),
 365           'currency' => array(
 366             'title' => ts('Currency'),
 367             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 368             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 369             'default' => NULL,
 370             'type' => CRM_Utils_Type::T_STRING,
 371           ),
 372           'trxn_date' => array(
 373             'title' => ts('Transaction Date'),
 374             'operatorType' => CRM_Report_Form::OP_DATE,
 375             'type' => CRM_Utils_Type::T_DATE,
 376           ),
 377           'status_id' => array(
 378             'title' => ts('Financial Transaction Status'),
 379             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 380             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 381             'default' => array(1),
 382           ),
 383           'card_type_id' => array(
 384             'title' => ts('Credit Card Type'),
 385             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 386             'options' => CRM_Financial_DAO_FinancialTrxn::buildOptions('card_type_id'),
 387             'default' => NULL,
 388             'type' => CRM_Utils_Type::T_STRING,
 389           ),
 390         ),
 391         'order_bys' => array(
 392           'payment_instrument_id' => array('title' => ts('Payment Method')),
 393         ),
 394       ),
 395       'civicrm_entity_financial_trxn' => array(
 396         'dao' => 'CRM_Financial_DAO_EntityFinancialTrxn',
 397         'fields' => array(
 398           'amount' => array(
 399             'title' => ts('Amount'),
 400             'default' => TRUE,
 401             'type' => CRM_Utils_Type::T_STRING,
 402           ),
 403         ),
 404         'filters' => array(
 405           'amount' => array('title' => ts('Amount')),
 406         ),
 407       ),
 408     );
 409
 410     $this->_groupFilter = TRUE;
 411     $this->_tagFilter = TRUE;
 412     parent::__construct();
 413   }
 414
 415   public function preProcess() {
 416     parent::preProcess();
 417   }
 418
 419   public function select() {
 420     $select = array();
 421
 422     $this->_columnHeaders = array();
 423     foreach ($this->_columns as $tableName => $table) {
 424       if (array_key_exists('fields', $table)) {
 425         foreach ($table['fields'] as $fieldName => $field) {
 426           if (!empty($field['required']) ||
 427             !empty($this->_params['fields'][$fieldName])
 428           ) {
 429             switch ($fieldName) {
 430               case 'credit_accounting_code':
 431               case 'credit_name':
 432                 $select[] = " CASE
 433                             WHEN {$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id IS NOT NULL
 434                             THEN  {$this->_aliases['civicrm_financial_account']}_credit_1.{$field['name']}
 435                             ELSE  {$this->_aliases['civicrm_financial_account']}_credit_2.{$field['name']}
 436                             END AS civicrm_financial_account_{$fieldName} ";
 437                 break;
 438
 439               case 'amount':
 440                 $select[] = " CASE
 441                             WHEN  {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id IS NOT NULL
 442                             THEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.amount
 443                             ELSE {$this->_aliases['civicrm_entity_financial_trxn']}.amount
 444                             END AS civicrm_entity_financial_trxn_amount ";
 445                 break;
 446
 447               case 'credit_contact_id':
 448                 $select[] = " CASE
 449                             WHEN {$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id IS NOT NULL
 450                             THEN  credit_contact_1.{$field['name']}
 451                             ELSE  credit_contact_2.{$field['name']}
 452                             END AS civicrm_financial_account_{$fieldName} ";
 453                 break;
 454
 455               default:
 456                 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 457                 break;
 458             }
 459             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 460             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 461           }
 462         }
 463       }
 464     }
 465     $this->_selectClauses = $select;
 466
 467     $this->_select = 'SELECT ' . implode(', ', $select) . ' ';
 468   }
 469
 470   public function from() {
 471     $this->_from = NULL;
 472
 473     $this->_from = "FROM  civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}
 474               INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
 475                     ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND
 476                          {$this->_aliases['civicrm_contribution']}.is_test = 0
 477               LEFT JOIN civicrm_membership_payment payment
 478                     ON ( {$this->_aliases['civicrm_contribution']}.id = payment.contribution_id )
 479               LEFT JOIN civicrm_membership {$this->_aliases['civicrm_membership']}
 480                     ON payment.membership_id = {$this->_aliases['civicrm_membership']}.id
 481               LEFT JOIN civicrm_entity_financial_trxn {$this->_aliases['civicrm_entity_financial_trxn']}
 482                     ON ({$this->_aliases['civicrm_contribution']}.id = {$this->_aliases['civicrm_entity_financial_trxn']}.entity_id AND
 483                         {$this->_aliases['civicrm_entity_financial_trxn']}.entity_table = 'civicrm_contribution')
 484               LEFT JOIN civicrm_financial_trxn {$this->_aliases['civicrm_financial_trxn']}
 485                     ON {$this->_aliases['civicrm_financial_trxn']}.id = {$this->_aliases['civicrm_entity_financial_trxn']}.financial_trxn_id
 486               LEFT JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account']}_debit
 487                     ON {$this->_aliases['civicrm_financial_trxn']}.to_financial_account_id = {$this->_aliases['civicrm_financial_account']}_debit.id
 488               LEFT JOIN civicrm_contact debit_contact ON {$this->_aliases['civicrm_financial_account']}_debit.contact_id = debit_contact.id
 489               LEFT JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account']}_credit_1
 490                     ON {$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id = {$this->_aliases['civicrm_financial_account']}_credit_1.id
 491               LEFT JOIN civicrm_contact credit_contact_1 ON {$this->_aliases['civicrm_financial_account']}_credit_1.contact_id = credit_contact_1.id
 492               LEFT JOIN civicrm_entity_financial_trxn {$this->_aliases['civicrm_entity_financial_trxn']}_item
 493                     ON ({$this->_aliases['civicrm_financial_trxn']}.id = {$this->_aliases['civicrm_entity_financial_trxn']}_item.financial_trxn_id AND
 494                         {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_table = 'civicrm_financial_item')
 495               LEFT JOIN civicrm_financial_item fitem
 496                     ON fitem.id = {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id
 497               LEFT JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account']}_credit_2
 498                     ON fitem.financial_account_id = {$this->_aliases['civicrm_financial_account']}_credit_2.id
 499               LEFT JOIN civicrm_contact credit_contact_2 ON {$this->_aliases['civicrm_financial_account']}_credit_2.contact_id = credit_contact_2.id
 500               LEFT JOIN civicrm_line_item {$this->_aliases['civicrm_line_item']}
 501                     ON  fitem.entity_id = {$this->_aliases['civicrm_line_item']}.id AND fitem.entity_table = 'civicrm_line_item'
 502               ";
 503
 504     if ($this->isTableSelected('civicrm_batch')) {
 505       $this->_from .= "LEFT JOIN civicrm_entity_batch ent_batch
 506                     ON  {$this->_aliases['civicrm_financial_trxn']}.id = ent_batch.entity_id AND ent_batch.entity_table = 'civicrm_financial_trxn'
 507               LEFT JOIN civicrm_batch batch
 508                     ON  ent_batch.batch_id = batch.id";
 509     }
 510   }
 511
 512   public function orderBy() {
 513     parent::orderBy();
 514
 515     // please note this will just add the order-by columns to select query, and not display in column-headers.
 516     // This is a solution to not throw fatal errors when there is a column in order-by, not present in select/display columns.
 517     foreach ($this->_orderByFields as $orderBy) {
 518       if (!array_key_exists($orderBy['name'], $this->_params['fields']) &&
 519         empty($orderBy['section'])
 520       ) {
 521         $this->_select .= ", {$orderBy['dbAlias']} as {$orderBy['tplField']}";
 522       }
 523     }
 524   }
 525
 526   public function where() {
 527     foreach ($this->_columns as $tableName => $table) {
 528       if (array_key_exists('filters', $table)) {
 529         foreach ($table['filters'] as $fieldName => $field) {
 530           $clause = NULL;
 531           if (in_array($fieldName, array(
 532                'credit_accounting_code',
 533                'credit_name',
 534                'credit_contact_id',
 535              )
 536           )) {
 537             $field['dbAlias'] = "CASE
 538               WHEN financial_trxn_civireport.from_financial_account_id IS NOT NULL
 539               THEN  financial_account_civireport_credit_1.{$field['name']}
 540               ELSE  financial_account_civireport_credit_2.{$field['name']}
 541               END";
 542           }
 543           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 544             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 545             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 546             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 547
 548             $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 549           }
 550           else {
 551             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 552             if ($op) {
 553               $clause = $this->whereClause($field,
 554                 $op,
 555                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 556                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 557                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 558               );
 559             }
 560           }
 561           if (!empty($clause)) {
 562             $clauses[] = $clause;
 563           }
 564         }
 565       }
 566     }
 567     if (empty($clauses)) {
 568       $this->_where = 'WHERE ( 1 )';
 569     }
 570     else {
 571       $this->_where = 'WHERE ' . implode(' AND ', $clauses);
 572     }
 573   }
 574
 575   public function postProcess() {
 576     // get the acl clauses built before we assemble the query
 577     $this->buildACLClause($this->_aliases['civicrm_contact']);
 578     parent::postProcess();
 579   }
 580
 581   public function groupBy() {
 582     $groupBy = array(
 583       "{$this->_aliases['civicrm_entity_financial_trxn']}.id",
 584       "{$this->_aliases['civicrm_line_item']}.id",
 585     );
 586     $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy);
 587   }
 588
 589   /**
 590    * @param $rows
 591    *
 592    * @return array
 593    */
 594   public function statistics(&$rows) {
 595     $statistics = parent::statistics($rows);
 596     $tempTableName = CRM_Core_DAO::createTempTableName('civicrm_contribution');
 597     $financialSelect = "CASE WHEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id IS NOT NULL
 598             THEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.amount
 599             ELSE {$this->_aliases['civicrm_entity_financial_trxn']}.amount
 600             END as amount";
 601
 602     $this->_selectClauses = array(
 603       "{$this->_aliases['civicrm_contribution']}.id",
 604       "{$this->_aliases['civicrm_entity_financial_trxn']}.id as trxnID",
 605       "{$this->_aliases['civicrm_contribution']}.currency",
 606       $financialSelect,
 607     );
 608     $select = "SELECT " . implode(', ', $this->_selectClauses);
 609
 610     $this->groupBy();
 611
 612     $tempQuery = "CREATE TEMPORARY TABLE {$tempTableName} {$this->_databaseAttributes} AS
 613                   {$select} {$this->_from} {$this->_where} {$this->_groupBy} ";
 614     CRM_Core_DAO::executeQuery($tempQuery);
 615
 616     $sql = "SELECT COUNT(trxnID) as count, SUM(amount) as amount, currency
 617             FROM {$tempTableName}
 618             GROUP BY currency";
 619     $dao = CRM_Core_DAO::executeQuery($sql);
 620     $amount = $avg = array();
 621     while ($dao->fetch()) {
 622       $amount[] = CRM_Utils_Money::format($dao->amount, $dao->currency);
 623       $avg[] = CRM_Utils_Money::format(round(($dao->amount /
 624         $dao->count), 2), $dao->currency);
 625     }
 626
 627     $statistics['counts']['amount'] = array(
 628       'value' => implode(', ', $amount),
 629       'title' => ts('Total Amount'),
 630       'type' => CRM_Utils_Type::T_STRING,
 631     );
 632     $statistics['counts']['avg'] = array(
 633       'value' => implode(', ', $avg),
 634       'title' => ts('Average'),
 635       'type' => CRM_Utils_Type::T_STRING,
 636     );
 637     return $statistics;
 638   }
 639
 640   /**
 641    * Alter display of rows.
 642    *
 643    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 644    * such as rendering contacts as links.
 645    *
 646    * @param array $rows
 647    *   Rows generated by SQL, with an array for each row.
 648    */
 649   public function alterDisplay(&$rows) {
 650     $contributionTypes = CRM_Contribute_PseudoConstant::financialType();
 651     $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument();
 652     $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
 653     $creditCardTypes = CRM_Financial_DAO_FinancialTrxn::buildOptions('card_type_id');
 654     foreach ($rows as $rowNum => $row) {
 655       // convert display name to links
 656       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 657         !empty($rows[$rowNum]['civicrm_contact_sort_name']) &&
 658         array_key_exists('civicrm_contact_id', $row)
 659       ) {
 660         $url = CRM_Utils_System::url('civicrm/contact/view',
 661           'reset=1&cid=' . $row['civicrm_contact_id'],
 662           $this->_absoluteUrl
 663         );
 664         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 665         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts('View Contact Summary for this Contact.');
 666       }
 667
 668       // handle contribution status id
 669       if ($value = CRM_Utils_Array::value('civicrm_contribution_contribution_status_id', $row)) {
 670         $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value];
 671       }
 672
 673       // handle payment instrument id
 674       if ($value = CRM_Utils_Array::value('civicrm_financial_trxn_payment_instrument_id', $row)) {
 675         $rows[$rowNum]['civicrm_financial_trxn_payment_instrument_id'] = $paymentInstruments[$value];
 676       }
 677
 678       // handle financial type id
 679       if ($value = CRM_Utils_Array::value('civicrm_line_item_financial_type_id', $row)) {
 680         $rows[$rowNum]['civicrm_line_item_financial_type_id'] = $contributionTypes[$value];
 681       }
 682       if ($value = CRM_Utils_Array::value('civicrm_entity_financial_trxn_amount', $row)) {
 683         $rows[$rowNum]['civicrm_entity_financial_trxn_amount'] = CRM_Utils_Money::format($rows[$rowNum]['civicrm_entity_financial_trxn_amount'], $rows[$rowNum]['civicrm_financial_trxn_currency']);
 684       }
 685
 686       //handle gender
 687       if (array_key_exists('civicrm_contact_gender_id', $row)) {
 688         if ($value = $row['civicrm_contact_gender_id']) {
 689           $gender = CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'gender_id');
 690           $rows[$rowNum]['civicrm_contact_gender_id'] = $gender[$value];
 691         }
 692         $entryFound = TRUE;
 693       }
 694
 695       if (!empty($row['civicrm_financial_trxn_card_type_id'])) {
 696         $rows[$rowNum]['civicrm_financial_trxn_card_type_id'] = CRM_Utils_Array::value($row['civicrm_financial_trxn_card_type_id'], $creditCardTypes);
 697         $entryFound = TRUE;
 698       }
 699
 700       // display birthday in the configured custom format
 701       if (array_key_exists('civicrm_contact_birth_date', $row)) {
 702         $birthDate = $row['civicrm_contact_birth_date'];
 703         if ($birthDate) {
 704           $rows[$rowNum]['civicrm_contact_birth_date'] = CRM_Utils_Date::customFormat($birthDate, '%Y%m%d');
 705         }
 706         $entryFound = TRUE;
 707       }
 708
 709     }
 710   }
 711
 712 }