CRM/Report/Form/Contribute/Detail.php

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