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