3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
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 +--------------------------------------------------------------------+
15 * @copyright CiviCRM LLC https://civicrm.org/licensing
17 class CRM_Report_Form_Contribute_Detail
extends CRM_Report_Form
{
19 protected $_summary = NULL;
21 protected $_softFrom = NULL;
23 protected $noDisplayContributionOrSoftColumn = FALSE;
25 protected $_customGroupExtends = [
31 protected $groupConcatTested = TRUE;
33 protected $isTempTableBuilt = FALSE;
38 * This can be 'Main' or 'SoftCredit' to denote which query we are building.
42 protected $queryMode = 'Main';
45 * Is this report being run on contributions as the base entity.
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
51 * If it is in isContributionsOnlyMode we can simplify.
53 * (arguably there should be 2 separate report templates, not one doing double duty.)
57 protected $isContributionBaseMode = FALSE;
60 * This report has been optimised for group filtering.
63 * @see https://issues.civicrm.org/jira/browse/CRM-19170
65 protected $groupFilterNotOptimised = FALSE;
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,
83 'dao' => 'CRM_Core_DAO_Email',
86 'title' => ts('Donor Email'),
90 'grouping' => 'contact-fields',
92 'civicrm_line_item' => [
93 'dao' => 'CRM_Price_DAO_LineItem',
96 'dao' => 'CRM_Core_DAO_Phone',
99 'title' => ts('Donor Phone'),
104 'grouping' => 'contact-fields',
106 'civicrm_contribution' => [
107 'dao' => 'CRM_Contribute_DAO_Contribution',
109 'contribution_id' => [
111 'no_display' => TRUE,
114 'list_contri_id' => [
116 'title' => ts('Contribution ID'),
118 'financial_type_id' => [
119 'title' => ts('Financial Type'),
122 'contribution_status_id' => [
123 'title' => ts('Contribution Status'),
125 'contribution_page_id' => [
126 'title' => ts('Contribution Page'),
129 'title' => ts('Source'),
131 'payment_instrument_id' => [
132 'title' => ts('Payment Type'),
135 'title' => ts('Check Number'),
139 'no_display' => TRUE,
142 'receive_date' => ['default' => TRUE],
143 'receipt_date' => NULL,
144 'thankyou_date' => NULL,
146 'title' => ts('Amount'),
149 'non_deductible_amount' => [
150 'title' => ts('Non-deductible Amount'),
152 'fee_amount' => NULL,
153 'net_amount' => NULL,
154 'contribution_or_soft' => [
155 'title' => ts('Contribution OR Soft Credit?'),
156 'dbAlias' => "'Contribution'",
159 'title' => ts('Soft Credits'),
162 'soft_credit_for' => [
163 'title' => ts('Soft Credit For'),
167 'title' => ts('Cancelled / Refunded Date'),
168 'name' => 'contribution_cancel_date',
171 'title' => ts('Cancellation / Refund Reason'),
175 'contribution_or_soft' => [
176 'title' => ts('Contribution OR Soft Credit?'),
178 'operatorType' => CRM_Report_Form
::OP_SELECT
,
179 'type' => CRM_Utils_Type
::T_STRING
,
181 'contributions_only' => ts('Contributions Only'),
182 'soft_credits_only' => ts('Soft Credits Only'),
183 'both' => ts('Both'),
185 'default' => 'contributions_only',
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'),
193 'type' => CRM_Utils_Type
::T_STRING
,
196 'title' => ts('Currency'),
197 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
198 'options' => CRM_Core_OptionGroup
::values('currencies_enabled'),
200 'type' => CRM_Utils_Type
::T_STRING
,
202 'non_deductible_amount' => [
203 'title' => ts('Non-deductible Amount'),
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
,
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
,
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
,
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'),
228 'type' => CRM_Utils_Type
::T_INT
,
230 'total_amount' => ['title' => ts('Contribution Amount')],
232 'title' => ts('Cancelled / Refunded Date'),
233 'operatorType' => CRM_Report_Form
::OP_DATE
,
234 'name' => 'contribution_cancel_date',
237 'title' => ts('Cancellation / Refund Reason'),
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')],
249 'contribution_id' => [
253 'title' => ts('Contribution'),
256 'grouping' => 'contri-fields',
258 'civicrm_contribution_soft' => [
259 'dao' => 'CRM_Contribute_DAO_ContributionSoft',
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
],
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'),
270 'type' => CRM_Utils_Type
::T_STRING
,
274 'soft_credit_id' => [
276 'title' => ts('Soft Credit'),
280 'civicrm_financial_trxn' => [
281 'dao' => 'CRM_Financial_DAO_FinancialTrxn',
284 'title' => ts('Credit Card Type'),
289 'title' => ts('Credit Card Type'),
290 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
291 'options' => CRM_Financial_DAO_FinancialTrxn
::buildOptions('card_type_id'),
293 'type' => CRM_Utils_Type
::T_STRING
,
298 'dao' => 'CRM_Batch_DAO_EntityBatch',
299 'grouping' => 'contri-fields',
302 'name' => 'batch_id',
303 'title' => ts('Batch Name'),
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',
316 'civicrm_contribution_ordinality' => [
317 'dao' => 'CRM_Contribute_DAO_Contribution',
318 'alias' => 'cordinality',
321 'title' => ts('Contribution Ordinality'),
322 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
324 0 => ts('First by Contributor'),
325 1 => ts('Second or Later by Contributor'),
327 'type' => CRM_Utils_Type
::T_INT
,
332 'dao' => 'CRM_Core_DAO_Note',
334 'contribution_note' => [
336 'title' => ts('Contribution Note'),
342 'title' => ts('Contribution Note'),
343 'operator' => 'like',
344 'type' => CRM_Utils_Type
::T_STRING
,
348 'civicrm_pledge_payment' => [
349 'dao' => 'CRM_Pledge_DAO_PledgePayment',
352 'title' => ts('Pledge ID'),
357 'title' => ts('Pledge ID'),
358 'type' => CRM_Utils_Type
::T_INT
,
363 $this->getColumns('Address')
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);
372 $this->_currencyColumn
= 'civicrm_contribution_currency';
373 parent
::__construct();
377 * Validate incompatible report settings.
380 * true if no error found
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.'));
392 return parent
::validate();
396 * Set the FROM clause for the report.
398 public function from() {
399 $this->setFromBase('civicrm_contact');
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";
406 $this->joinContributionToSoftCredit();
407 $this->appendAdditionalFromJoins();
414 * @throws \CRM_Core_Exception
416 public function statistics(&$rows) {
417 $statistics = parent
::statistics($rows);
419 $totalAmount = $average = $fees = $net = [];
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
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);
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
;
446 $statistics['counts']['amount'] = [
447 'title' => ts('Total Amount (Contributions)'),
448 'value' => implode(', ', $totalAmount),
449 'type' => CRM_Utils_Type
::T_STRING
,
451 $statistics['counts']['count'] = [
452 'title' => ts('Total Contributions'),
455 $statistics['counts']['fees'] = [
456 'title' => ts('Fees'),
457 'value' => implode(', ', $fees),
458 'type' => CRM_Utils_Type
::T_STRING
,
460 $statistics['counts']['net'] = [
461 'title' => ts('Net'),
462 'value' => implode(', ', $net),
463 'type' => CRM_Utils_Type
::T_STRING
,
465 $statistics['counts']['avg'] = [
466 'title' => ts('Average'),
467 'value' => implode(', ', $average),
468 'type' => CRM_Utils_Type
::T_STRING
,
471 // Stats for soft credits
472 if ($this->_softFrom
&&
473 CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) !=
476 $totalAmount = $average = [];
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";
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
) . " (" .
492 $average[] = CRM_Utils_Money
::format($dao->avg
, $dao->currency
);
493 $count +
= $dao->count
;
495 $statistics['counts']['softamount'] = [
496 'title' => ts('Total Amount (Soft Credits)'),
497 'value' => implode(', ', $totalAmount),
498 'type' => CRM_Utils_Type
::T_STRING
,
500 $statistics['counts']['softcount'] = [
501 'title' => ts('Total Soft Credits'),
504 $statistics['counts']['softavg'] = [
505 'title' => ts('Average (Soft Credits)'),
506 'value' => implode(', ', $average),
507 'type' => CRM_Utils_Type
::T_STRING
,
515 * Build the report query.
517 * @param bool $applyLimit
521 public function buildQuery($applyLimit = FALSE) {
522 if ($this->isTempTableBuilt
) {
524 return "SELECT SQL_CALC_FOUND_ROWS * FROM {$this->temporaryTables['civireport_contribution_detail_temp3']['name']} $this->_orderBy $this->_limit";
526 return parent
::buildQuery($applyLimit);
530 * Shared function for preliminary processing.
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'.
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;
543 if (CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) == 'contributions_only') {
544 $this->isContributionBaseMode
= TRUE;
546 if ($this->isContributionBaseMode
&&
547 (!empty($this->_params
['fields']['soft_credit_type_id'])
548 ||
!empty($this->_params
['soft_credit_type_id_value']))
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.'));
556 // 1. use main contribution query to build temp table 1
557 $sql = $this->buildQuery();
558 $this->createTemporaryTable('civireport_contribution_detail_temp1', $sql);
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
;
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();
571 $select = str_ireplace('contribution_civireport.total_amount', 'contribution_soft_civireport.amount', $this->_select
);
572 $select = str_ireplace("'Contribution' as", "'Soft Credit' as", $select);
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);
580 if (CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) ==
583 // revise pager : prev, next based on soft-credits only
587 // copy _from for later use of stats calculation for soft credits, and reset $this->_from to main query
588 $this->_softFrom
= $this->_from
;
590 // simple reset of ->_from
593 // also include custom group from if included
594 // since this might be included in select
595 $this->customDataFrom();
597 // 3. Decide where to populate temp3 table from
598 if ($this->isContributionBaseMode
600 $this->createTemporaryTable('civireport_contribution_detail_temp3',
601 "(SELECT * FROM {$this->temporaryTables['civireport_contribution_detail_temp1']['name']})"
604 elseif (CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) ==
607 $this->createTemporaryTable('civireport_contribution_detail_temp3',
608 "(SELECT * FROM {$this->temporaryTables['civireport_contribution_detail_temp2']['name']})"
612 $this->createTemporaryTable('civireport_contribution_detail_temp3', "
613 (SELECT * FROM {$this->temporaryTables['civireport_contribution_detail_temp1']['name']})
615 (SELECT * FROM {$this->temporaryTables['civireport_contribution_detail_temp2']['name']})");
617 $this->isTempTableBuilt
= TRUE;
621 * Store group bys into array - so we can check elsewhere what is grouped.
623 * If we are generating a table of soft credits we need to group by them.
625 protected function storeGroupByArray() {
626 if ($this->queryMode
=== 'SoftCredit') {
627 $this->_groupByArray
= [$this->_aliases
['civicrm_contribution_soft'] . '.id'];
630 parent
::storeGroupByArray();
635 * Alter display of rows.
637 * Iterate through the rows retrieved via SQL and make changes for display purposes,
638 * such as rendering contacts as links.
641 * Rows generated by SQL, with an array for each row.
643 public function alterDisplay(&$rows) {
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']) {
661 if ($prev_cid == $cid) {
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
)
677 unset($rows[$rowNum][$colName]);
686 if (CRM_Utils_Array
::value('civicrm_contribution_contribution_or_soft', $rows[$rowNum]) ==
689 unset($rows[$rowNum]['civicrm_contribution_soft_soft_credit_type_id']);
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)
698 $url = CRM_Utils_System
::url("civicrm/contact/view",
699 'reset=1&cid=' . $row['civicrm_contact_id'],
702 $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
703 $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
706 if ($value = CRM_Utils_Array
::value('civicrm_contribution_financial_type_id', $row)) {
707 $rows[$rowNum]['civicrm_contribution_financial_type_id'] = $contributionTypes[$value];
710 if ($value = CRM_Utils_Array
::value('civicrm_contribution_contribution_status_id', $row)) {
711 $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value];
714 if ($value = CRM_Utils_Array
::value('civicrm_contribution_contribution_page_id', $row)) {
715 $rows[$rowNum]['civicrm_contribution_contribution_page_id'] = $contributionPages[$value];
718 if ($value = CRM_Utils_Array
::value('civicrm_contribution_payment_instrument_id', $row)) {
719 $rows[$rowNum]['civicrm_contribution_payment_instrument_id'] = $paymentInstruments[$value];
722 if (!empty($row['civicrm_batch_batch_id'])) {
723 $rows[$rowNum]['civicrm_batch_batch_id'] = $batches[$row['civicrm_batch_batch_id']] ??
NULL;
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');
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",
739 'id' => $row['civicrm_contribution_contribution_id'],
740 'cid' => $row['civicrm_contact_id'],
742 'context' => 'contribution',
743 'selectedChild' => 'contribute',
747 $rows[$rowNum]['civicrm_contribution_total_amount_link'] = $url;
748 $rows[$rowNum]['civicrm_contribution_total_amount_hover'] = ts("View Details of this Contribution.");
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];
762 if (array_key_exists('civicrm_contribution_soft_credits', $row) &&
764 CRM_Utils_Array
::value('civicrm_contribution_contribution_or_soft', $rows[$rowNum]) &&
765 array_key_exists('civicrm_contribution_contribution_id', $row)
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);
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
);
781 $rows[$rowNum]['civicrm_contribution_soft_credits'] = $string;
784 if (array_key_exists('civicrm_contribution_soft_credit_for', $row) &&
786 CRM_Utils_Array
::value('civicrm_contribution_contribution_or_soft', $rows[$rowNum]) &&
787 array_key_exists('civicrm_contribution_contribution_id', $row)
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);
795 while ($dao->fetch()) {
796 $url = CRM_Utils_System
::url("civicrm/contact/view", 'reset=1&cid=' .
797 $dao->civicrm_contact_id
);
799 "\n<a href='{$url}'>{$dao->civicrm_contact_sort_name}</a>";
801 $rows[$rowNum]['civicrm_contribution_soft_credit_for'] = $string;
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']);
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']
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",
826 'id' => $row['civicrm_pledge_payment_pledge_id'],
827 'cid' => $row['civicrm_contact_id'],
829 'context' => 'pledge',
830 'selectedChild' => 'pledge',
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.");
840 $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s) for this ') ?
TRUE : $entryFound;
842 // skip looking further in rows, if first row itself doesn't
843 // have the column we need
851 public function sectionTotals() {
853 // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
854 if (empty($this->_selectAliases
)) {
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}";
863 // pull section aliases out of $this->_sections
864 $sectionAliases = array_keys($this->_sections
);
867 foreach (array_merge($sectionAliases, $this->_selectAliases
) as $alias) {
868 $ifnulls[] = "ifnull($alias, '') as $alias";
870 $this->_select
= "SELECT " . implode(", ", $ifnulls);
871 $this->_select
= CRM_Contact_BAO_Query
::appendAnyValueToSelect($ifnulls, $sectionAliases);
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.
880 if (array_search("civicrm_contribution_total_amount", $this->_selectAliases
) !==
883 $addtotals = ", sum(civicrm_contribution_total_amount) as sumcontribs";
884 $showsumcontribs = TRUE;
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()) {
896 // let $this->_alterDisplay translate any integer ids to human-readable values.
897 $rows[0] = $dao->toArray();
898 $this->alterDisplay($rows);
901 // add totals for all permutations of section values
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
;
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
;
924 if ($showsumcontribs) {
926 // ts exception to avoid having ts("%1 %2: %3")
927 $title = '%1 contributions / soft-credits: %2';
929 if (CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) ==
932 $title = '%1 contributions: %2';
934 elseif (CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) ==
937 $title = '%1 soft-credits: %2';
939 foreach ($totals as $key => $total) {
940 $totalandsum[$key] = ts($title, [
942 2 => CRM_Utils_Money
::format($sumcontribs[$key]),
945 $this->assign('sectionTotals', $totalandsum);
948 $this->assign('sectionTotals', $totals);
954 * Generate the from clause as it relates to the soft credits.
956 public function softCreditFrom() {
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
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']);
973 $this->appendAdditionalFromJoins();
977 * Append the joins that are required regardless of context.
979 public function appendAdditionalFromJoins() {
980 if (!empty($this->_params
['ordinality_value'])) {
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";
985 $this->joinPhoneFromContact();
986 $this->joinAddressFromContact();
987 $this->joinEmailFromContact();
989 // include contribution note
990 if (!empty($this->_params
['fields']['contribution_note']) ||
991 !empty($this->_params
['note_value'])
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 )";
998 //for contribution batches
999 if (!empty($this->_params
['fields']['batch_id']) ||
1000 !empty($this->_params
['bid_value'])
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')";
1010 // for credit card type
1011 $this->addFinancialTrxnFromClause();
1013 if ($this->isTableSelected('civicrm_pledge_payment')) {
1015 LEFT JOIN civicrm_pledge_payment {$this->_aliases['civicrm_pledge_payment']} ON {$this->_aliases['civicrm_pledge_payment']}.contribution_id = {$this->_aliases['civicrm_contribution']}.id
1021 * Add join to the soft credit table.
1023 protected function joinContributionToSoftCredit() {
1024 if (CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) == 'contributions_only'
1025 && !$this->isTableSelected('civicrm_contribution_soft')) {
1028 $joinType = ' LEFT ';
1029 if (CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) == 'soft_credits_only') {
1030 $joinType = ' INNER ';
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