CRM/Report/Form/Contribute/Detail.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_Detail extends CRM_Report_Form {
  34
  35   protected $_summary = NULL;
  36
  37   protected $_softFrom = NULL;
  38
  39   protected $noDisplayContributionOrSoftColumn = FALSE;
  40
  41   protected $_customGroupExtends = array(
  42     'Contact',
  43     'Individual',
  44     'Contribution',
  45   );
  46
  47   protected $groupConcatTested = TRUE;
  48
  49   /**
  50    * This report has been optimised for group filtering.
  51    *
  52    * CRM-19170
  53    *
  54    * @var bool
  55    */
  56   protected $groupFilterNotOptimised = FALSE;
  57
  58   /**
  59    * Class constructor.
  60    */
  61   public function __construct() {
  62     $this->_autoIncludeIndexedFieldsAsOrderBys = 1;
  63     // Check if CiviCampaign is a) enabled and b) has active campaigns
  64     $config = CRM_Core_Config::singleton();
  65     $campaignEnabled = in_array("CiviCampaign", $config->enableComponents);
  66     if ($campaignEnabled) {
  67       $getCampaigns = CRM_Campaign_BAO_Campaign::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
  68       $this->activeCampaigns = $getCampaigns['campaigns'];
  69       asort($this->activeCampaigns);
  70     }
  71
  72     $this->_columns = array_merge($this->getColumns('Contact', array(
  73       'order_bys_defaults' => array('sort_name' => 'ASC '),
  74       'fields_defaults' => array('sort_name'),
  75       'fields_excluded' => array('id'),
  76       'fields_required' => array('id'),
  77       'filters_defaults' => array('is_deleted' => 0),
  78       'no_field_disambiguation' => TRUE,
  79     )), array(
  80       'civicrm_email' => array(
  81         'dao' => 'CRM_Core_DAO_Email',
  82         'fields' => array(
  83           'email' => array(
  84             'title' => ts('Donor Email'),
  85             'default' => TRUE,
  86           ),
  87         ),
  88         'grouping' => 'contact-fields',
  89       ),
  90       'civicrm_line_item' => array(
  91         'dao' => 'CRM_Price_DAO_LineItem',
  92       ),
  93       'civicrm_phone' => array(
  94         'dao' => 'CRM_Core_DAO_Phone',
  95         'fields' => array(
  96           'phone' => array(
  97             'title' => ts('Donor Phone'),
  98             'default' => TRUE,
  99             'no_repeat' => TRUE,
 100           ),
 101         ),
 102         'grouping' => 'contact-fields',
 103       ),
 104       'civicrm_contribution' => array(
 105         'dao' => 'CRM_Contribute_DAO_Contribution',
 106         'fields' => array(
 107           'contribution_id' => array(
 108             'name' => 'id',
 109             'no_display' => TRUE,
 110             'required' => TRUE,
 111           ),
 112           'list_contri_id' => array(
 113             'name' => 'id',
 114             'title' => ts('Contribution ID'),
 115           ),
 116           'financial_type_id' => array(
 117             'title' => ts('Financial Type'),
 118             'default' => TRUE,
 119           ),
 120           'contribution_status_id' => array(
 121             'title' => ts('Contribution Status'),
 122           ),
 123           'contribution_page_id' => array(
 124             'title' => ts('Contribution Page'),
 125           ),
 126           'source' => array(
 127             'title' => ts('Source'),
 128           ),
 129           'payment_instrument_id' => array(
 130             'title' => ts('Payment Type'),
 131           ),
 132           'check_number' => array(
 133             'title' => ts('Check Number'),
 134           ),
 135           'currency' => array(
 136             'required' => TRUE,
 137             'no_display' => TRUE,
 138           ),
 139           'trxn_id' => NULL,
 140           'receive_date' => array('default' => TRUE),
 141           'receipt_date' => NULL,
 142           'total_amount' => array(
 143             'title' => ts('Amount'),
 144             'required' => TRUE,
 145             'statistics' => array('sum' => ts('Amount')),
 146           ),
 147           'non_deductible_amount' => array(
 148             'title' => ts('Non-deductible Amount'),
 149           ),
 150           'fee_amount' => NULL,
 151           'net_amount' => NULL,
 152           'contribution_or_soft' => array(
 153             'title' => ts('Contribution OR Soft Credit?'),
 154             'dbAlias' => "'Contribution'",
 155           ),
 156           'soft_credits' => array(
 157             'title' => ts('Soft Credits'),
 158             'dbAlias' => "NULL",
 159           ),
 160           'soft_credit_for' => array(
 161             'title' => ts('Soft Credit For'),
 162             'dbAlias' => "NULL",
 163           ),
 164         ),
 165         'filters' => array(
 166           'contribution_or_soft' => array(
 167             'title' => ts('Contribution OR Soft Credit?'),
 168             'clause' => "(1)",
 169             'operatorType' => CRM_Report_Form::OP_SELECT,
 170             'type' => CRM_Utils_Type::T_STRING,
 171             'options' => array(
 172               'contributions_only' => ts('Contributions Only'),
 173               'soft_credits_only' => ts('Soft Credits Only'),
 174               'both' => ts('Both'),
 175             ),
 176           ),
 177           'receive_date' => array('operatorType' => CRM_Report_Form::OP_DATE),
 178           'contribution_source' => array(
 179             'title' => ts('Source'),
 180             'name' => 'source',
 181             'type' => CRM_Utils_Type::T_STRING,
 182           ),
 183           'currency' => array(
 184             'title' => ts('Currency'),
 185             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 186             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 187             'default' => NULL,
 188             'type' => CRM_Utils_Type::T_STRING,
 189           ),
 190           'non_deductible_amount' => array(
 191             'title' => ts('Non-deductible Amount'),
 192           ),
 193           'financial_type_id' => array(
 194             'title' => ts('Financial Type'),
 195             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 196             'options' => CRM_Financial_BAO_FinancialType::getAvailableFinancialTypes(),
 197             'type' => CRM_Utils_Type::T_INT,
 198           ),
 199           'contribution_page_id' => array(
 200             'title' => ts('Contribution Page'),
 201             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 202             'options' => CRM_Contribute_PseudoConstant::contributionPage(),
 203             'type' => CRM_Utils_Type::T_INT,
 204           ),
 205           'payment_instrument_id' => array(
 206             'title' => ts('Payment Type'),
 207             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 208             'options' => CRM_Contribute_PseudoConstant::paymentInstrument(),
 209             'type' => CRM_Utils_Type::T_INT,
 210           ),
 211           'contribution_status_id' => array(
 212             'title' => ts('Contribution Status'),
 213             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 214             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 215             'default' => array(1),
 216             'type' => CRM_Utils_Type::T_INT,
 217           ),
 218           'total_amount' => array('title' => ts('Contribution Amount')),
 219         ),
 220         'order_bys' => array(
 221           'financial_type_id' => array('title' => ts('Financial Type')),
 222           'contribution_status_id' => array('title' => ts('Contribution Status')),
 223           'payment_instrument_id' => array('title' => ts('Payment Method')),
 224           'receive_date' => array('title' => ts('Date Received')),
 225         ),
 226         'group_bys' => array(
 227           'contribution_id' => array(
 228             'name' => 'id',
 229             'required' => TRUE,
 230             'title' => ts('Contribution'),
 231           ),
 232         ),
 233         'grouping' => 'contri-fields',
 234       ),
 235       'civicrm_contribution_soft' => array(
 236         'dao' => 'CRM_Contribute_DAO_ContributionSoft',
 237         'fields' => array(
 238           'soft_credit_type_id' => array('title' => ts('Soft Credit Type')),
 239         ),
 240         'filters' => array(
 241           'soft_credit_type_id' => array(
 242             'title' => ts('Soft Credit Type'),
 243             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 244             'options' => CRM_Core_OptionGroup::values('soft_credit_type'),
 245             'default' => NULL,
 246             'type' => CRM_Utils_Type::T_STRING,
 247           ),
 248         ),
 249       ),
 250       'civicrm_financial_trxn' => array(
 251         'dao' => 'CRM_Financial_DAO_FinancialTrxn',
 252         'fields' => array(
 253           'card_type_id' => array(
 254             'title' => ts('Credit Card Type'),
 255           ),
 256         ),
 257         'filters' => array(
 258           'card_type_id' => array(
 259             'title' => ts('Credit Card Type'),
 260             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 261             'options' => CRM_Financial_DAO_FinancialTrxn::buildOptions('card_type_id'),
 262             'default' => NULL,
 263             'type' => CRM_Utils_Type::T_STRING,
 264           ),
 265         ),
 266       ),
 267       'civicrm_batch' => array(
 268         'dao' => 'CRM_Batch_DAO_EntityBatch',
 269         'grouping' => 'contri-fields',
 270         'fields' => array(
 271           'batch_id' => array(
 272             'name' => 'batch_id',
 273             'title' => ts('Batch Name'),
 274           ),
 275         ),
 276         'filters' => array(
 277           'bid' => array(
 278             'title' => ts('Batch Name'),
 279             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 280             'options' => CRM_Batch_BAO_Batch::getBatches(),
 281             'type' => CRM_Utils_Type::T_INT,
 282             'dbAlias' => 'batch_civireport.batch_id',
 283           ),
 284         ),
 285       ),
 286       'civicrm_contribution_ordinality' => array(
 287         'dao' => 'CRM_Contribute_DAO_Contribution',
 288         'alias' => 'cordinality',
 289         'filters' => array(
 290           'ordinality' => array(
 291             'title' => ts('Contribution Ordinality'),
 292             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 293             'options' => array(
 294               0 => 'First by Contributor',
 295               1 => 'Second or Later by Contributor',
 296             ),
 297             'type' => CRM_Utils_Type::T_INT,
 298           ),
 299         ),
 300       ),
 301       'civicrm_note' => array(
 302         'dao' => 'CRM_Core_DAO_Note',
 303         'fields' => array(
 304           'contribution_note' => array(
 305             'name' => 'note',
 306             'title' => ts('Contribution Note'),
 307           ),
 308         ),
 309         'filters' => array(
 310           'note' => array(
 311             'name' => 'note',
 312             'title' => ts('Contribution Note'),
 313             'operator' => 'like',
 314             'type' => CRM_Utils_Type::T_STRING,
 315           ),
 316         ),
 317       ),
 318     )) + $this->addAddressFields(FALSE);
 319     // The tests test for this variation of the sort_name field. Don't argue with the tests :-).
 320     $this->_columns['civicrm_contact']['fields']['sort_name']['title'] = ts('Donor Name');
 321     $this->_groupFilter = TRUE;
 322     $this->_tagFilter = TRUE;
 323
 324     // If we have active campaigns add those elements to both the fields and filters
 325     if ($campaignEnabled && !empty($this->activeCampaigns)) {
 326       $this->_columns['civicrm_contribution']['fields']['campaign_id'] = array(
 327         'title' => ts('Campaign'),
 328         'default' => 'false',
 329       );
 330       $this->_columns['civicrm_contribution']['filters']['campaign_id'] = array(
 331         'title' => ts('Campaign'),
 332         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 333         'options' => $this->activeCampaigns,
 334         'type' => CRM_Utils_Type::T_INT,
 335       );
 336       $this->_columns['civicrm_contribution']['order_bys']['campaign_id'] = array('title' => ts('Campaign'));
 337     }
 338
 339     $this->_currencyColumn = 'civicrm_contribution_currency';
 340     parent::__construct();
 341   }
 342
 343   /**
 344    * Set the FROM clause for the report.
 345    */
 346   public function from() {
 347     $this->setFromBase('civicrm_contact');
 348     $this->_from .= "
 349       INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
 350         ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id
 351         AND {$this->_aliases['civicrm_contribution']}.is_test = 0";
 352
 353     if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) ==
 354       'both'
 355     ) {
 356       $this->_from .= "\n LEFT JOIN civicrm_contribution_soft contribution_soft_civireport
 357                          ON contribution_soft_civireport.contribution_id = {$this->_aliases['civicrm_contribution']}.id";
 358     }
 359     elseif (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) ==
 360       'soft_credits_only'
 361     ) {
 362       $this->_from .= "\n INNER JOIN civicrm_contribution_soft contribution_soft_civireport
 363                          ON contribution_soft_civireport.contribution_id = {$this->_aliases['civicrm_contribution']}.id";
 364     }
 365     $this->appendAdditionalFromJoins();
 366   }
 367
 368   /**
 369    * @param $rows
 370    *
 371    * @return array
 372    */
 373   public function statistics(&$rows) {
 374     $statistics = parent::statistics($rows);
 375
 376     $totalAmount = $average = $fees = $net = array();
 377     $count = 0;
 378     $select = "
 379         SELECT COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as count,
 380                SUM( {$this->_aliases['civicrm_contribution']}.total_amount ) as amount,
 381                ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as avg,
 382                {$this->_aliases['civicrm_contribution']}.currency as currency,
 383                SUM( {$this->_aliases['civicrm_contribution']}.fee_amount ) as fees,
 384                SUM( {$this->_aliases['civicrm_contribution']}.net_amount ) as net
 385         ";
 386
 387     $group = "\nGROUP BY {$this->_aliases['civicrm_contribution']}.currency";
 388     $sql = "{$select} {$this->_from} {$this->_where} {$group}";
 389     $dao = CRM_Core_DAO::executeQuery($sql);
 390     $this->addToDeveloperTab($sql);
 391
 392     while ($dao->fetch()) {
 393       $totalAmount[] = CRM_Utils_Money::format($dao->amount, $dao->currency) . " (" . $dao->count . ")";
 394       $fees[] = CRM_Utils_Money::format($dao->fees, $dao->currency);
 395       $net[] = CRM_Utils_Money::format($dao->net, $dao->currency);
 396       $average[] = CRM_Utils_Money::format($dao->avg, $dao->currency);
 397       $count += $dao->count;
 398     }
 399     $statistics['counts']['amount'] = array(
 400       'title' => ts('Total Amount (Contributions)'),
 401       'value' => implode(',  ', $totalAmount),
 402       'type' => CRM_Utils_Type::T_STRING,
 403     );
 404     $statistics['counts']['count'] = array(
 405       'title' => ts('Total Contributions'),
 406       'value' => $count,
 407     );
 408     $statistics['counts']['fees'] = array(
 409       'title' => ts('Fees'),
 410       'value' => implode(',  ', $fees),
 411       'type' => CRM_Utils_Type::T_STRING,
 412     );
 413     $statistics['counts']['net'] = array(
 414       'title' => ts('Net'),
 415       'value' => implode(',  ', $net),
 416       'type' => CRM_Utils_Type::T_STRING,
 417     );
 418     $statistics['counts']['avg'] = array(
 419       'title' => ts('Average'),
 420       'value' => implode(',  ', $average),
 421       'type' => CRM_Utils_Type::T_STRING,
 422     );
 423
 424     // Stats for soft credits
 425     if ($this->_softFrom &&
 426       CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) !=
 427       'contributions_only'
 428     ) {
 429       $totalAmount = $average = array();
 430       $count = 0;
 431       $select = "
 432 SELECT COUNT(contribution_soft_civireport.amount ) as count,
 433        SUM(contribution_soft_civireport.amount ) as amount,
 434        ROUND(AVG(contribution_soft_civireport.amount), 2) as avg,
 435        {$this->_aliases['civicrm_contribution']}.currency as currency";
 436       $sql = "
 437 {$select}
 438 {$this->_softFrom}
 439 GROUP BY {$this->_aliases['civicrm_contribution']}.currency";
 440       $dao = CRM_Core_DAO::executeQuery($sql);
 441       $this->addToDeveloperTab($sql);
 442       while ($dao->fetch()) {
 443         $totalAmount[] = CRM_Utils_Money::format($dao->amount, $dao->currency) . " (" .
 444           $dao->count . ")";
 445         $average[] = CRM_Utils_Money::format($dao->avg, $dao->currency);
 446         $count += $dao->count;
 447       }
 448       $statistics['counts']['softamount'] = array(
 449         'title' => ts('Total Amount (Soft Credits)'),
 450         'value' => implode(',  ', $totalAmount),
 451         'type' => CRM_Utils_Type::T_STRING,
 452       );
 453       $statistics['counts']['softcount'] = array(
 454         'title' => ts('Total Soft Credits'),
 455         'value' => $count,
 456       );
 457       $statistics['counts']['softavg'] = array(
 458         'title' => ts('Average (Soft Credits)'),
 459         'value' => implode(',  ', $average),
 460         'type' => CRM_Utils_Type::T_STRING,
 461       );
 462     }
 463
 464     return $statistics;
 465   }
 466
 467   /**
 468    * This function appears to have been overrriden for the purposes of facilitating soft credits in the report.
 469    *
 470    * The report appears to have 2 different functions:
 471    * 1) contribution report
 472    * 2) soft credit report - showing a row per 'payment engagement' (payment or soft credit). There is a separate
 473    * soft credit report as well.
 474    *
 475    * Somewhat confusingly this report returns multiple rows per contribution when soft credits are included. It feels
 476    * like there is a case to split it into 2 separate reports.
 477    *
 478    * Soft credit functionality is not currently unit tested for this report.
 479    */
 480   public function postProcess() {
 481     // get the acl clauses built before we assemble the query
 482     $this->buildACLClause($this->_aliases['civicrm_contact']);
 483
 484     $this->beginPostProcess();
 485     // CRM-18312 - display soft_credits and soft_credits_for column
 486     // when 'Contribution or Soft Credit?' column is not selected
 487     if (empty($this->_params['fields']['contribution_or_soft'])) {
 488       $this->_params['fields']['contribution_or_soft'] = 1;
 489       $this->noDisplayContributionOrSoftColumn = TRUE;
 490     }
 491
 492     if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) ==
 493       'contributions_only' &&
 494       !empty($this->_params['fields']['soft_credit_type_id'])
 495     ) {
 496       unset($this->_params['fields']['soft_credit_type_id']);
 497       if (!empty($this->_params['soft_credit_type_id_value'])) {
 498         $this->_params['soft_credit_type_id_value'] = array();
 499       }
 500     }
 501
 502     // 1. use main contribution query to build temp table 1
 503     $sql = $this->buildQuery();
 504     $tempQuery = "CREATE TEMPORARY TABLE civireport_contribution_detail_temp1 {$this->_databaseAttributes} AS {$sql}";
 505     $this->addToDeveloperTab($tempQuery);
 506     CRM_Core_DAO::executeQuery($tempQuery);
 507     $this->setPager();
 508
 509     // 2. customize main contribution query for soft credit, and build temp table 2 with soft credit contributions only
 510     $this->softCreditFrom();
 511     // also include custom group from if included
 512     // since this might be included in select
 513     $this->customDataFrom();
 514
 515     $select = str_ireplace('contribution_civireport.total_amount', 'contribution_soft_civireport.amount', $this->_select);
 516     $select = str_ireplace("'Contribution' as", "'Soft Credit' as", $select);
 517     // We really don't want to join soft credit in if not required.
 518     if (!empty($this->_groupBy) && !$this->noDisplayContributionOrSoftColumn) {
 519       $this->_groupBy .= ', contribution_soft_civireport.amount';
 520     }
 521     // we inner join with temp1 to restrict soft contributions to those in temp1 table
 522     $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy}";
 523     $tempQuery = "CREATE TEMPORARY TABLE civireport_contribution_detail_temp2 {$this->_databaseAttributes} AS {$sql}";
 524     $this->addToDeveloperTab($tempQuery);
 525     CRM_Core_DAO::executeQuery($tempQuery);
 526     if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) ==
 527       'soft_credits_only'
 528     ) {
 529       // revise pager : prev, next based on soft-credits only
 530       $this->setPager();
 531     }
 532
 533     // copy _from for later use of stats calculation for soft credits, and reset $this->_from to main query
 534     $this->_softFrom = $this->_from;
 535
 536     // simple reset of ->_from
 537     $this->from();
 538
 539     // also include custom group from if included
 540     // since this might be included in select
 541     $this->customDataFrom();
 542
 543     // 3. Decide where to populate temp3 table from
 544     if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) ==
 545       'contributions_only'
 546     ) {
 547       $tempQuery = "(SELECT * FROM civireport_contribution_detail_temp1)";
 548     }
 549     elseif (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) ==
 550       'soft_credits_only'
 551     ) {
 552       $tempQuery = "(SELECT * FROM civireport_contribution_detail_temp2)";
 553     }
 554     else {
 555       $tempQuery = "
 556 (SELECT * FROM civireport_contribution_detail_temp1)
 557 UNION ALL
 558 (SELECT * FROM civireport_contribution_detail_temp2)";
 559     }
 560
 561     // 4. build temp table 3
 562     $sql = "CREATE TEMPORARY TABLE civireport_contribution_detail_temp3 {$this->_databaseAttributes} AS {$tempQuery}";
 563     $this->addToDeveloperTab($sql);
 564     CRM_Core_DAO::executeQuery($sql);
 565
 566     // 6. show result set from temp table 3
 567     $rows = array();
 568     $sql = "SELECT * FROM civireport_contribution_detail_temp3 $this->_orderBy";
 569     $this->buildRows($sql, $rows);
 570
 571     // format result set.
 572     $this->formatDisplay($rows, FALSE);
 573
 574     // assign variables to templates
 575     $this->doTemplateAssignment($rows);
 576     // do print / pdf / instance stuff if needed
 577     $this->endPostProcess($rows);
 578   }
 579
 580   /**
 581    * Alter display of rows.
 582    *
 583    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 584    * such as rendering contacts as links.
 585    *
 586    * @param array $rows
 587    *   Rows generated by SQL, with an array for each row.
 588    */
 589   public function alterDisplay(&$rows) {
 590     $checkList = array();
 591     $entryFound = FALSE;
 592     $display_flag = $prev_cid = $cid = 0;
 593     $contributionTypes = CRM_Contribute_PseudoConstant::financialType();
 594     $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
 595     $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument();
 596     $contributionPages = CRM_Contribute_PseudoConstant::contributionPage();
 597     $batches = CRM_Batch_BAO_Batch::getBatches();
 598     foreach ($rows as $rowNum => $row) {
 599       if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') {
 600         // don't repeat contact details if its same as the previous row
 601         if (array_key_exists('civicrm_contact_id', $row)) {
 602           if ($cid = $row['civicrm_contact_id']) {
 603             if ($rowNum == 0) {
 604               $prev_cid = $cid;
 605             }
 606             else {
 607               if ($prev_cid == $cid) {
 608                 $display_flag = 1;
 609                 $prev_cid = $cid;
 610               }
 611               else {
 612                 $display_flag = 0;
 613                 $prev_cid = $cid;
 614               }
 615             }
 616
 617             if ($display_flag) {
 618               foreach ($row as $colName => $colVal) {
 619                 // Hide repeats in no-repeat columns, but not if the field's a section header
 620                 if (in_array($colName, $this->_noRepeats) &&
 621                   !array_key_exists($colName, $this->_sections)
 622                 ) {
 623                   unset($rows[$rowNum][$colName]);
 624                 }
 625               }
 626             }
 627             $entryFound = TRUE;
 628           }
 629         }
 630       }
 631
 632       if (CRM_Utils_Array::value('civicrm_contribution_contribution_or_soft', $rows[$rowNum]) ==
 633         'Contribution'
 634       ) {
 635         unset($rows[$rowNum]['civicrm_contribution_soft_soft_credit_type_id']);
 636       }
 637
 638       $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, 'contribution/detail', ts('View Contribution Details')) ? TRUE : $entryFound;
 639       // convert donor sort name to link
 640       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 641         !empty($rows[$rowNum]['civicrm_contact_sort_name']) &&
 642         array_key_exists('civicrm_contact_id', $row)
 643       ) {
 644         $url = CRM_Utils_System::url("civicrm/contact/view",
 645           'reset=1&cid=' . $row['civicrm_contact_id'],
 646           $this->_absoluteUrl
 647         );
 648         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 649         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
 650       }
 651
 652       if ($value = CRM_Utils_Array::value('civicrm_contribution_financial_type_id', $row)) {
 653         $rows[$rowNum]['civicrm_contribution_financial_type_id'] = $contributionTypes[$value];
 654         $entryFound = TRUE;
 655       }
 656       if ($value = CRM_Utils_Array::value('civicrm_contribution_contribution_status_id', $row)) {
 657         $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value];
 658         $entryFound = TRUE;
 659       }
 660       if ($value = CRM_Utils_Array::value('civicrm_contribution_contribution_page_id', $row)) {
 661         $rows[$rowNum]['civicrm_contribution_contribution_page_id'] = $contributionPages[$value];
 662         $entryFound = TRUE;
 663       }
 664       if ($value = CRM_Utils_Array::value('civicrm_contribution_payment_instrument_id', $row)) {
 665         $rows[$rowNum]['civicrm_contribution_payment_instrument_id'] = $paymentInstruments[$value];
 666         $entryFound = TRUE;
 667       }
 668       if (!empty($row['civicrm_batch_batch_id'])) {
 669         $rows[$rowNum]['civicrm_batch_batch_id'] = CRM_Utils_Array::value($row['civicrm_batch_batch_id'], $batches);
 670         $entryFound = TRUE;
 671       }
 672       if (!empty($row['civicrm_financial_trxn_card_type_id'])) {
 673         $rows[$rowNum]['civicrm_financial_trxn_card_type_id'] = $this->getLabels($row['civicrm_financial_trxn_card_type_id'], 'CRM_Financial_DAO_FinancialTrxn', 'card_type_id');
 674         $entryFound = TRUE;
 675       }
 676
 677       // Contribution amount links to viewing contribution
 678       if (($value = CRM_Utils_Array::value('civicrm_contribution_total_amount', $row)) &&
 679         CRM_Core_Permission::check('access CiviContribute')
 680       ) {
 681         $url = CRM_Utils_System::url("civicrm/contact/view/contribution",
 682           "reset=1&id=" . $row['civicrm_contribution_contribution_id'] .
 683           "&cid=" . $row['civicrm_contact_id'] .
 684           "&action=view&context=contribution&selectedChild=contribute",
 685           $this->_absoluteUrl
 686         );
 687         $rows[$rowNum]['civicrm_contribution_total_amount_link'] = $url;
 688         $rows[$rowNum]['civicrm_contribution_total_amount_hover'] = ts("View Details of this Contribution.");
 689         $entryFound = TRUE;
 690       }
 691
 692       // convert campaign_id to campaign title
 693       if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
 694         if ($value = $row['civicrm_contribution_campaign_id']) {
 695           $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->activeCampaigns[$value];
 696           $entryFound = TRUE;
 697         }
 698       }
 699
 700       // soft credits
 701       if (array_key_exists('civicrm_contribution_soft_credits', $row) &&
 702         'Contribution' ==
 703         CRM_Utils_Array::value('civicrm_contribution_contribution_or_soft', $rows[$rowNum]) &&
 704         array_key_exists('civicrm_contribution_contribution_id', $row)
 705       ) {
 706         $query = "
 707 SELECT civicrm_contact_id, civicrm_contact_sort_name, civicrm_contribution_total_amount, civicrm_contribution_currency
 708 FROM   civireport_contribution_detail_temp2
 709 WHERE  civicrm_contribution_contribution_id={$row['civicrm_contribution_contribution_id']}";
 710         $this->addToDeveloperTab($query);
 711         $dao = CRM_Core_DAO::executeQuery($query);
 712         $string = '';
 713         $separator = ($this->_outputMode !== 'csv') ? "<br/>" : ' ';
 714         while ($dao->fetch()) {
 715           $url = CRM_Utils_System::url("civicrm/contact/view", 'reset=1&cid=' .
 716             $dao->civicrm_contact_id);
 717           $string = $string . ($string ? $separator : '') .
 718             "<a href='{$url}'>{$dao->civicrm_contact_sort_name}</a> " .
 719             CRM_Utils_Money::format($dao->civicrm_contribution_total_amount, $dao->civicrm_contribution_currency);
 720         }
 721         $rows[$rowNum]['civicrm_contribution_soft_credits'] = $string;
 722       }
 723
 724       if (array_key_exists('civicrm_contribution_soft_credit_for', $row) &&
 725         'Soft Credit' ==
 726         CRM_Utils_Array::value('civicrm_contribution_contribution_or_soft', $rows[$rowNum]) &&
 727         array_key_exists('civicrm_contribution_contribution_id', $row)
 728       ) {
 729         $query = "
 730 SELECT civicrm_contact_id, civicrm_contact_sort_name
 731 FROM   civireport_contribution_detail_temp1
 732 WHERE  civicrm_contribution_contribution_id={$row['civicrm_contribution_contribution_id']}";
 733         $this->addToDeveloperTab($query);
 734         $dao = CRM_Core_DAO::executeQuery($query);
 735         $string = '';
 736         while ($dao->fetch()) {
 737           $url = CRM_Utils_System::url("civicrm/contact/view", 'reset=1&cid=' .
 738             $dao->civicrm_contact_id);
 739           $string = $string .
 740             "\n<a href='{$url}'>{$dao->civicrm_contact_sort_name}</a>";
 741         }
 742         $rows[$rowNum]['civicrm_contribution_soft_credit_for'] = $string;
 743       }
 744
 745       // CRM-18312 - hide 'contribution_or_soft' column if unchecked.
 746       if (!empty($this->noDisplayContributionOrSoftColumn)) {
 747         unset($rows[$rowNum]['civicrm_contribution_contribution_or_soft']);
 748         unset($this->_columnHeaders['civicrm_contribution_contribution_or_soft']);
 749       }
 750
 751       //convert soft_credit_type_id into label
 752       if (array_key_exists('civicrm_contribution_soft_soft_credit_type_id', $rows[$rowNum])) {
 753         $rows[$rowNum]['civicrm_contribution_soft_soft_credit_type_id'] = CRM_Core_PseudoConstant::getLabel(
 754           'CRM_Contribute_BAO_ContributionSoft',
 755           'soft_credit_type_id',
 756           $row['civicrm_contribution_soft_soft_credit_type_id']
 757         );
 758       }
 759
 760       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s) for this ') ? TRUE : $entryFound;
 761
 762       // skip looking further in rows, if first row itself doesn't
 763       // have the column we need
 764       if (!$entryFound) {
 765         break;
 766       }
 767       $lastKey = $rowNum;
 768     }
 769   }
 770
 771   public function sectionTotals() {
 772
 773     // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
 774     if (empty($this->_selectAliases)) {
 775       return;
 776     }
 777
 778     if (!empty($this->_sections)) {
 779       // build the query with no LIMIT clause
 780       $select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', 'SELECT ', $this->_select);
 781       $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
 782
 783       // pull section aliases out of $this->_sections
 784       $sectionAliases = array_keys($this->_sections);
 785
 786       $ifnulls = array();
 787       foreach (array_merge($sectionAliases, $this->_selectAliases) as $alias) {
 788         $ifnulls[] = "ifnull($alias, '') as $alias";
 789       }
 790       $this->_select = "SELECT " . implode(", ", $ifnulls);
 791       $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($ifnulls, $sectionAliases);
 792
 793       /* Group (un-limited) report by all aliases and get counts. This might
 794        * be done more efficiently when the contents of $sql are known, ie. by
 795        * overriding this method in the report class.
 796        */
 797
 798       $addtotals = '';
 799
 800       if (array_search("civicrm_contribution_total_amount", $this->_selectAliases) !==
 801         FALSE
 802       ) {
 803         $addtotals = ", sum(civicrm_contribution_total_amount) as sumcontribs";
 804         $showsumcontribs = TRUE;
 805       }
 806
 807       $query = $this->_select .
 808         "$addtotals, count(*) as ct from civireport_contribution_detail_temp3 group by " .
 809         implode(", ", $sectionAliases);
 810       // initialize array of total counts
 811       $sumcontribs = $totals = array();
 812       $dao = CRM_Core_DAO::executeQuery($query);
 813       $this->addToDeveloperTab($query);
 814       while ($dao->fetch()) {
 815
 816         // let $this->_alterDisplay translate any integer ids to human-readable values.
 817         $rows[0] = $dao->toArray();
 818         $this->alterDisplay($rows);
 819         $row = $rows[0];
 820
 821         // add totals for all permutations of section values
 822         $values = array();
 823         $i = 1;
 824         $aliasCount = count($sectionAliases);
 825         foreach ($sectionAliases as $alias) {
 826           $values[] = $row[$alias];
 827           $key = implode(CRM_Core_DAO::VALUE_SEPARATOR, $values);
 828           if ($i == $aliasCount) {
 829             // the last alias is the lowest-level section header; use count as-is
 830             $totals[$key] = $dao->ct;
 831             if ($showsumcontribs) {
 832               $sumcontribs[$key] = $dao->sumcontribs;
 833             }
 834           }
 835           else {
 836             // other aliases are higher level; roll count into their total
 837             $totals[$key] = (array_key_exists($key, $totals)) ? $totals[$key] + $dao->ct : $dao->ct;
 838             if ($showsumcontribs) {
 839               $sumcontribs[$key] = array_key_exists($key, $sumcontribs) ? $sumcontribs[$key] + $dao->sumcontribs : $dao->sumcontribs;
 840             }
 841           }
 842         }
 843       }
 844       if ($showsumcontribs) {
 845         $totalandsum = array();
 846         // ts exception to avoid having ts("%1 %2: %3")
 847         $title = '%1 contributions / soft-credits: %2';
 848
 849         if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) ==
 850           'contributions_only'
 851         ) {
 852           $title = '%1 contributions: %2';
 853         }
 854         elseif (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) ==
 855           'soft_credits_only'
 856         ) {
 857           $title = '%1 soft-credits: %2';
 858         }
 859         foreach ($totals as $key => $total) {
 860           $totalandsum[$key] = ts($title, array(
 861             1 => $total,
 862             2 => CRM_Utils_Money::format($sumcontribs[$key]),
 863           ));
 864         }
 865         $this->assign('sectionTotals', $totalandsum);
 866       }
 867       else {
 868         $this->assign('sectionTotals', $totals);
 869       }
 870     }
 871   }
 872
 873   /**
 874    * Generate the from clause as it relates to the soft credits.
 875    */
 876   public function softCreditFrom() {
 877
 878     $this->_from = "
 879       FROM  civireport_contribution_detail_temp1 temp1_civireport
 880       INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
 881         ON temp1_civireport.civicrm_contribution_contribution_id = {$this->_aliases['civicrm_contribution']}.id
 882       INNER JOIN civicrm_contribution_soft contribution_soft_civireport
 883         ON contribution_soft_civireport.contribution_id = {$this->_aliases['civicrm_contribution']}.id
 884       INNER JOIN civicrm_contact      {$this->_aliases['civicrm_contact']}
 885         ON {$this->_aliases['civicrm_contact']}.id = contribution_soft_civireport.contact_id
 886       {$this->_aclFrom}
 887     ";
 888
 889     $this->appendAdditionalFromJoins();
 890   }
 891
 892   /**
 893    * Append the joins that are required regardless of context.
 894    */
 895   public function appendAdditionalFromJoins() {
 896     if (!empty($this->_params['ordinality_value'])) {
 897       $this->_from .= "
 898               INNER JOIN (SELECT c.id, IF(COUNT(oc.id) = 0, 0, 1) AS ordinality FROM civicrm_contribution c LEFT JOIN civicrm_contribution oc ON c.contact_id = oc.contact_id AND oc.receive_date < c.receive_date GROUP BY c.id) {$this->_aliases['civicrm_contribution_ordinality']}
 899                       ON {$this->_aliases['civicrm_contribution_ordinality']}.id = {$this->_aliases['civicrm_contribution']}.id";
 900     }
 901     $this->joinPhoneFromContact();
 902     $this->joinAddressFromContact();
 903     $this->joinEmailFromContact();
 904
 905     // include contribution note
 906     if (!empty($this->_params['fields']['contribution_note']) ||
 907       !empty($this->_params['note_value'])
 908     ) {
 909       $this->_from .= "
 910             LEFT JOIN civicrm_note {$this->_aliases['civicrm_note']}
 911                       ON ( {$this->_aliases['civicrm_note']}.entity_table = 'civicrm_contribution' AND
 912                            {$this->_aliases['civicrm_contribution']}.id = {$this->_aliases['civicrm_note']}.entity_id )";
 913     }
 914     //for contribution batches
 915     if (!empty($this->_params['fields']['batch_id']) ||
 916       !empty($this->_params['bid_value'])
 917     ) {
 918       $this->_from .= "
 919         LEFT JOIN civicrm_entity_financial_trxn eft
 920           ON eft.entity_id = {$this->_aliases['civicrm_contribution']}.id AND
 921             eft.entity_table = 'civicrm_contribution'
 922         LEFT JOIN civicrm_entity_batch {$this->_aliases['civicrm_batch']}
 923           ON ({$this->_aliases['civicrm_batch']}.entity_id = eft.financial_trxn_id
 924           AND {$this->_aliases['civicrm_batch']}.entity_table = 'civicrm_financial_trxn')";
 925     }
 926     // for credit card type
 927     $this->addFinancialTrxnFromClause();
 928   }
 929
 930 }