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     // @todo in order to make this report testable we need to remove this function override in favour of the
 482     // functions called by the reportTemplate.getrows api - this requires a bit of tidy up!
 483     // get the acl clauses built before we assemble the query
 484     $this->buildACLClause($this->_aliases['civicrm_contact']);
 485
 486     $this->beginPostProcess();
 487
 488     // 1. use main contribution query to build temp table 1
 489     $sql = $this->buildQuery();
 490     $tempQuery = "CREATE TEMPORARY TABLE civireport_contribution_detail_temp1 {$this->_databaseAttributes} AS {$sql}";
 491     $this->addToDeveloperTab($tempQuery);
 492     CRM_Core_DAO::executeQuery($tempQuery);
 493     $this->setPager();
 494
 495     // 2. customize main contribution query for soft credit, and build temp table 2 with soft credit contributions only
 496     $this->softCreditFrom();
 497     // also include custom group from if included
 498     // since this might be included in select
 499     $this->customDataFrom();
 500
 501     $select = str_ireplace('contribution_civireport.total_amount', 'contribution_soft_civireport.amount', $this->_select);
 502     $select = str_ireplace("'Contribution' as", "'Soft Credit' as", $select);
 503     // We really don't want to join soft credit in if not required.
 504     if (!empty($this->_groupBy) && !$this->noDisplayContributionOrSoftColumn) {
 505       $this->_groupBy .= ', contribution_soft_civireport.amount';
 506     }
 507     // we inner join with temp1 to restrict soft contributions to those in temp1 table
 508     $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy}";
 509     $tempQuery = "CREATE TEMPORARY TABLE civireport_contribution_detail_temp2 {$this->_databaseAttributes} AS {$sql}";
 510     $this->addToDeveloperTab($tempQuery);
 511     CRM_Core_DAO::executeQuery($tempQuery);
 512     if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) ==
 513       'soft_credits_only'
 514     ) {
 515       // revise pager : prev, next based on soft-credits only
 516       $this->setPager();
 517     }
 518
 519     // copy _from for later use of stats calculation for soft credits, and reset $this->_from to main query
 520     $this->_softFrom = $this->_from;
 521
 522     // simple reset of ->_from
 523     $this->from();
 524
 525     // also include custom group from if included
 526     // since this might be included in select
 527     $this->customDataFrom();
 528
 529     // 3. Decide where to populate temp3 table from
 530     if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) ==
 531       'contributions_only'
 532     ) {
 533       $tempQuery = "(SELECT * FROM civireport_contribution_detail_temp1)";
 534     }
 535     elseif (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) ==
 536       'soft_credits_only'
 537     ) {
 538       $tempQuery = "(SELECT * FROM civireport_contribution_detail_temp2)";
 539     }
 540     else {
 541       $tempQuery = "
 542 (SELECT * FROM civireport_contribution_detail_temp1)
 543 UNION ALL
 544 (SELECT * FROM civireport_contribution_detail_temp2)";
 545     }
 546
 547     // 4. build temp table 3
 548     $sql = "CREATE TEMPORARY TABLE civireport_contribution_detail_temp3 {$this->_databaseAttributes} AS {$tempQuery}";
 549     $this->addToDeveloperTab($sql);
 550     CRM_Core_DAO::executeQuery($sql);
 551
 552     // 6. show result set from temp table 3
 553     $rows = array();
 554     $sql = "SELECT * FROM civireport_contribution_detail_temp3 $this->_orderBy";
 555     $this->buildRows($sql, $rows);
 556
 557     // format result set.
 558     $this->formatDisplay($rows, FALSE);
 559
 560     // assign variables to templates
 561     $this->doTemplateAssignment($rows);
 562     // do print / pdf / instance stuff if needed
 563     $this->endPostProcess($rows);
 564   }
 565
 566   /**
 567    * Shared function for preliminary processing.
 568    *
 569    * This is called by the api / unit tests and the form layer and is
 570    * the right place to do 'initial analysis of input'.
 571    */
 572   public function beginPostProcessCommon() {
 573     // CRM-18312 - display soft_credits and soft_credits_for column
 574     // when 'Contribution or Soft Credit?' column is not selected
 575     if (empty($this->_params['fields']['contribution_or_soft'])) {
 576       $this->_params['fields']['contribution_or_soft'] = 1;
 577       $this->noDisplayContributionOrSoftColumn = TRUE;
 578     }
 579
 580     if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) ==
 581       'contributions_only' &&
 582       (!empty($this->_params['fields']['soft_credit_type_id'])
 583       || !empty($this->_params['soft_credit_type_id_value']))
 584     ) {
 585       unset($this->_params['fields']['soft_credit_type_id']);
 586       if (!empty($this->_params['soft_credit_type_id_value'])) {
 587         $this->_params['soft_credit_type_id_value'] = array();
 588         CRM_Core_Session::setStatus(ts('Is it not possible to filter on soft contribution type when not including soft credits.'));
 589       }
 590     }
 591   }
 592
 593   /**
 594    * Alter display of rows.
 595    *
 596    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 597    * such as rendering contacts as links.
 598    *
 599    * @param array $rows
 600    *   Rows generated by SQL, with an array for each row.
 601    */
 602   public function alterDisplay(&$rows) {
 603     $checkList = array();
 604     $entryFound = FALSE;
 605     $display_flag = $prev_cid = $cid = 0;
 606     $contributionTypes = CRM_Contribute_PseudoConstant::financialType();
 607     $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
 608     $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument();
 609     $contributionPages = CRM_Contribute_PseudoConstant::contributionPage();
 610     $batches = CRM_Batch_BAO_Batch::getBatches();
 611     foreach ($rows as $rowNum => $row) {
 612       if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') {
 613         // don't repeat contact details if its same as the previous row
 614         if (array_key_exists('civicrm_contact_id', $row)) {
 615           if ($cid = $row['civicrm_contact_id']) {
 616             if ($rowNum == 0) {
 617               $prev_cid = $cid;
 618             }
 619             else {
 620               if ($prev_cid == $cid) {
 621                 $display_flag = 1;
 622                 $prev_cid = $cid;
 623               }
 624               else {
 625                 $display_flag = 0;
 626                 $prev_cid = $cid;
 627               }
 628             }
 629
 630             if ($display_flag) {
 631               foreach ($row as $colName => $colVal) {
 632                 // Hide repeats in no-repeat columns, but not if the field's a section header
 633                 if (in_array($colName, $this->_noRepeats) &&
 634                   !array_key_exists($colName, $this->_sections)
 635                 ) {
 636                   unset($rows[$rowNum][$colName]);
 637                 }
 638               }
 639             }
 640             $entryFound = TRUE;
 641           }
 642         }
 643       }
 644
 645       if (CRM_Utils_Array::value('civicrm_contribution_contribution_or_soft', $rows[$rowNum]) ==
 646         'Contribution'
 647       ) {
 648         unset($rows[$rowNum]['civicrm_contribution_soft_soft_credit_type_id']);
 649       }
 650
 651       $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, 'contribution/detail', ts('View Contribution Details')) ? TRUE : $entryFound;
 652       // convert donor sort name to link
 653       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 654         !empty($rows[$rowNum]['civicrm_contact_sort_name']) &&
 655         array_key_exists('civicrm_contact_id', $row)
 656       ) {
 657         $url = CRM_Utils_System::url("civicrm/contact/view",
 658           'reset=1&cid=' . $row['civicrm_contact_id'],
 659           $this->_absoluteUrl
 660         );
 661         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 662         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
 663       }
 664
 665       if ($value = CRM_Utils_Array::value('civicrm_contribution_financial_type_id', $row)) {
 666         $rows[$rowNum]['civicrm_contribution_financial_type_id'] = $contributionTypes[$value];
 667         $entryFound = TRUE;
 668       }
 669       if ($value = CRM_Utils_Array::value('civicrm_contribution_contribution_status_id', $row)) {
 670         $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value];
 671         $entryFound = TRUE;
 672       }
 673       if ($value = CRM_Utils_Array::value('civicrm_contribution_contribution_page_id', $row)) {
 674         $rows[$rowNum]['civicrm_contribution_contribution_page_id'] = $contributionPages[$value];
 675         $entryFound = TRUE;
 676       }
 677       if ($value = CRM_Utils_Array::value('civicrm_contribution_payment_instrument_id', $row)) {
 678         $rows[$rowNum]['civicrm_contribution_payment_instrument_id'] = $paymentInstruments[$value];
 679         $entryFound = TRUE;
 680       }
 681       if (!empty($row['civicrm_batch_batch_id'])) {
 682         $rows[$rowNum]['civicrm_batch_batch_id'] = CRM_Utils_Array::value($row['civicrm_batch_batch_id'], $batches);
 683         $entryFound = TRUE;
 684       }
 685       if (!empty($row['civicrm_financial_trxn_card_type_id'])) {
 686         $rows[$rowNum]['civicrm_financial_trxn_card_type_id'] = $this->getLabels($row['civicrm_financial_trxn_card_type_id'], 'CRM_Financial_DAO_FinancialTrxn', 'card_type_id');
 687         $entryFound = TRUE;
 688       }
 689
 690       // Contribution amount links to viewing contribution
 691       if (($value = CRM_Utils_Array::value('civicrm_contribution_total_amount', $row)) &&
 692         CRM_Core_Permission::check('access CiviContribute')
 693       ) {
 694         $url = CRM_Utils_System::url("civicrm/contact/view/contribution",
 695           "reset=1&id=" . $row['civicrm_contribution_contribution_id'] .
 696           "&cid=" . $row['civicrm_contact_id'] .
 697           "&action=view&context=contribution&selectedChild=contribute",
 698           $this->_absoluteUrl
 699         );
 700         $rows[$rowNum]['civicrm_contribution_total_amount_link'] = $url;
 701         $rows[$rowNum]['civicrm_contribution_total_amount_hover'] = ts("View Details of this Contribution.");
 702         $entryFound = TRUE;
 703       }
 704
 705       // convert campaign_id to campaign title
 706       if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
 707         if ($value = $row['civicrm_contribution_campaign_id']) {
 708           $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->activeCampaigns[$value];
 709           $entryFound = TRUE;
 710         }
 711       }
 712
 713       // soft credits
 714       if (array_key_exists('civicrm_contribution_soft_credits', $row) &&
 715         'Contribution' ==
 716         CRM_Utils_Array::value('civicrm_contribution_contribution_or_soft', $rows[$rowNum]) &&
 717         array_key_exists('civicrm_contribution_contribution_id', $row)
 718       ) {
 719         $query = "
 720 SELECT civicrm_contact_id, civicrm_contact_sort_name, civicrm_contribution_total_amount_sum, civicrm_contribution_currency
 721 FROM   civireport_contribution_detail_temp2
 722 WHERE  civicrm_contribution_contribution_id={$row['civicrm_contribution_contribution_id']}";
 723         $this->addToDeveloperTab($query);
 724         $dao = CRM_Core_DAO::executeQuery($query);
 725         $string = '';
 726         $separator = ($this->_outputMode !== 'csv') ? "<br/>" : ' ';
 727         while ($dao->fetch()) {
 728           $url = CRM_Utils_System::url("civicrm/contact/view", 'reset=1&cid=' .
 729             $dao->civicrm_contact_id);
 730           $string = $string . ($string ? $separator : '') .
 731             "<a href='{$url}'>{$dao->civicrm_contact_sort_name}</a> " .
 732             CRM_Utils_Money::format($dao->civicrm_contribution_total_amount_sum, $dao->civicrm_contribution_currency);
 733         }
 734         $rows[$rowNum]['civicrm_contribution_soft_credits'] = $string;
 735       }
 736
 737       if (array_key_exists('civicrm_contribution_soft_credit_for', $row) &&
 738         'Soft Credit' ==
 739         CRM_Utils_Array::value('civicrm_contribution_contribution_or_soft', $rows[$rowNum]) &&
 740         array_key_exists('civicrm_contribution_contribution_id', $row)
 741       ) {
 742         $query = "
 743 SELECT civicrm_contact_id, civicrm_contact_sort_name
 744 FROM   civireport_contribution_detail_temp1
 745 WHERE  civicrm_contribution_contribution_id={$row['civicrm_contribution_contribution_id']}";
 746         $this->addToDeveloperTab($query);
 747         $dao = CRM_Core_DAO::executeQuery($query);
 748         $string = '';
 749         while ($dao->fetch()) {
 750           $url = CRM_Utils_System::url("civicrm/contact/view", 'reset=1&cid=' .
 751             $dao->civicrm_contact_id);
 752           $string = $string .
 753             "\n<a href='{$url}'>{$dao->civicrm_contact_sort_name}</a>";
 754         }
 755         $rows[$rowNum]['civicrm_contribution_soft_credit_for'] = $string;
 756       }
 757
 758       // CRM-18312 - hide 'contribution_or_soft' column if unchecked.
 759       if (!empty($this->noDisplayContributionOrSoftColumn)) {
 760         unset($rows[$rowNum]['civicrm_contribution_contribution_or_soft']);
 761         unset($this->_columnHeaders['civicrm_contribution_contribution_or_soft']);
 762       }
 763
 764       //convert soft_credit_type_id into label
 765       if (array_key_exists('civicrm_contribution_soft_soft_credit_type_id', $rows[$rowNum])) {
 766         $rows[$rowNum]['civicrm_contribution_soft_soft_credit_type_id'] = CRM_Core_PseudoConstant::getLabel(
 767           'CRM_Contribute_BAO_ContributionSoft',
 768           'soft_credit_type_id',
 769           $row['civicrm_contribution_soft_soft_credit_type_id']
 770         );
 771       }
 772
 773       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s) for this ') ? TRUE : $entryFound;
 774
 775       // skip looking further in rows, if first row itself doesn't
 776       // have the column we need
 777       if (!$entryFound) {
 778         break;
 779       }
 780       $lastKey = $rowNum;
 781     }
 782   }
 783
 784   public function sectionTotals() {
 785
 786     // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
 787     if (empty($this->_selectAliases)) {
 788       return;
 789     }
 790
 791     if (!empty($this->_sections)) {
 792       // build the query with no LIMIT clause
 793       $select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', 'SELECT ', $this->_select);
 794       $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
 795
 796       // pull section aliases out of $this->_sections
 797       $sectionAliases = array_keys($this->_sections);
 798
 799       $ifnulls = array();
 800       foreach (array_merge($sectionAliases, $this->_selectAliases) as $alias) {
 801         $ifnulls[] = "ifnull($alias, '') as $alias";
 802       }
 803       $this->_select = "SELECT " . implode(", ", $ifnulls);
 804       $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($ifnulls, $sectionAliases);
 805
 806       /* Group (un-limited) report by all aliases and get counts. This might
 807        * be done more efficiently when the contents of $sql are known, ie. by
 808        * overriding this method in the report class.
 809        */
 810
 811       $addtotals = '';
 812
 813       if (array_search("civicrm_contribution_total_amount", $this->_selectAliases) !==
 814         FALSE
 815       ) {
 816         $addtotals = ", sum(civicrm_contribution_total_amount) as sumcontribs";
 817         $showsumcontribs = TRUE;
 818       }
 819
 820       $query = $this->_select .
 821         "$addtotals, count(*) as ct from civireport_contribution_detail_temp3 group by " .
 822         implode(", ", $sectionAliases);
 823       // initialize array of total counts
 824       $sumcontribs = $totals = array();
 825       $dao = CRM_Core_DAO::executeQuery($query);
 826       $this->addToDeveloperTab($query);
 827       while ($dao->fetch()) {
 828
 829         // let $this->_alterDisplay translate any integer ids to human-readable values.
 830         $rows[0] = $dao->toArray();
 831         $this->alterDisplay($rows);
 832         $row = $rows[0];
 833
 834         // add totals for all permutations of section values
 835         $values = array();
 836         $i = 1;
 837         $aliasCount = count($sectionAliases);
 838         foreach ($sectionAliases as $alias) {
 839           $values[] = $row[$alias];
 840           $key = implode(CRM_Core_DAO::VALUE_SEPARATOR, $values);
 841           if ($i == $aliasCount) {
 842             // the last alias is the lowest-level section header; use count as-is
 843             $totals[$key] = $dao->ct;
 844             if ($showsumcontribs) {
 845               $sumcontribs[$key] = $dao->sumcontribs;
 846             }
 847           }
 848           else {
 849             // other aliases are higher level; roll count into their total
 850             $totals[$key] = (array_key_exists($key, $totals)) ? $totals[$key] + $dao->ct : $dao->ct;
 851             if ($showsumcontribs) {
 852               $sumcontribs[$key] = array_key_exists($key, $sumcontribs) ? $sumcontribs[$key] + $dao->sumcontribs : $dao->sumcontribs;
 853             }
 854           }
 855         }
 856       }
 857       if ($showsumcontribs) {
 858         $totalandsum = array();
 859         // ts exception to avoid having ts("%1 %2: %3")
 860         $title = '%1 contributions / soft-credits: %2';
 861
 862         if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) ==
 863           'contributions_only'
 864         ) {
 865           $title = '%1 contributions: %2';
 866         }
 867         elseif (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) ==
 868           'soft_credits_only'
 869         ) {
 870           $title = '%1 soft-credits: %2';
 871         }
 872         foreach ($totals as $key => $total) {
 873           $totalandsum[$key] = ts($title, array(
 874             1 => $total,
 875             2 => CRM_Utils_Money::format($sumcontribs[$key]),
 876           ));
 877         }
 878         $this->assign('sectionTotals', $totalandsum);
 879       }
 880       else {
 881         $this->assign('sectionTotals', $totals);
 882       }
 883     }
 884   }
 885
 886   /**
 887    * Generate the from clause as it relates to the soft credits.
 888    */
 889   public function softCreditFrom() {
 890
 891     $this->_from = "
 892       FROM  civireport_contribution_detail_temp1 temp1_civireport
 893       INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
 894         ON temp1_civireport.civicrm_contribution_contribution_id = {$this->_aliases['civicrm_contribution']}.id
 895       INNER JOIN civicrm_contribution_soft contribution_soft_civireport
 896         ON contribution_soft_civireport.contribution_id = {$this->_aliases['civicrm_contribution']}.id
 897       INNER JOIN civicrm_contact      {$this->_aliases['civicrm_contact']}
 898         ON {$this->_aliases['civicrm_contact']}.id = contribution_soft_civireport.contact_id
 899       {$this->_aclFrom}
 900     ";
 901
 902     $this->appendAdditionalFromJoins();
 903   }
 904
 905   /**
 906    * Append the joins that are required regardless of context.
 907    */
 908   public function appendAdditionalFromJoins() {
 909     if (!empty($this->_params['ordinality_value'])) {
 910       $this->_from .= "
 911               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']}
 912                       ON {$this->_aliases['civicrm_contribution_ordinality']}.id = {$this->_aliases['civicrm_contribution']}.id";
 913     }
 914     $this->joinPhoneFromContact();
 915     $this->joinAddressFromContact();
 916     $this->joinEmailFromContact();
 917
 918     // include contribution note
 919     if (!empty($this->_params['fields']['contribution_note']) ||
 920       !empty($this->_params['note_value'])
 921     ) {
 922       $this->_from .= "
 923             LEFT JOIN civicrm_note {$this->_aliases['civicrm_note']}
 924                       ON ( {$this->_aliases['civicrm_note']}.entity_table = 'civicrm_contribution' AND
 925                            {$this->_aliases['civicrm_contribution']}.id = {$this->_aliases['civicrm_note']}.entity_id )";
 926     }
 927     //for contribution batches
 928     if (!empty($this->_params['fields']['batch_id']) ||
 929       !empty($this->_params['bid_value'])
 930     ) {
 931       $this->_from .= "
 932         LEFT JOIN civicrm_entity_financial_trxn eft
 933           ON eft.entity_id = {$this->_aliases['civicrm_contribution']}.id AND
 934             eft.entity_table = 'civicrm_contribution'
 935         LEFT JOIN civicrm_entity_batch {$this->_aliases['civicrm_batch']}
 936           ON ({$this->_aliases['civicrm_batch']}.entity_id = eft.financial_trxn_id
 937           AND {$this->_aliases['civicrm_batch']}.entity_table = 'civicrm_financial_trxn')";
 938     }
 939     // for credit card type
 940     $this->addFinancialTrxnFromClause();
 941   }
 942
 943 }