CRM/Report/Form/Contribute/Bookkeeping.php

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