CRM/Report/Form/Contribute/Detail.php

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