CRM/Report/Form/Contribute/Detail.php

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