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