3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2018 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2018
33 class CRM_Report_Form_Contribute_Detail
extends CRM_Report_Form
{
35 protected $_summary = NULL;
37 protected $_softFrom = NULL;
39 protected $noDisplayContributionOrSoftColumn = FALSE;
41 protected $_customGroupExtends = array(
47 protected $groupConcatTested = TRUE;
50 * This report has been optimised for group filtering.
56 protected $groupFilterNotOptimised = FALSE;
61 public function __construct() {
62 $this->_autoIncludeIndexedFieldsAsOrderBys
= 1;
63 // Check if CiviCampaign is a) enabled and b) has active campaigns
64 $config = CRM_Core_Config
::singleton();
65 $campaignEnabled = in_array("CiviCampaign", $config->enableComponents
);
66 if ($campaignEnabled) {
67 $getCampaigns = CRM_Campaign_BAO_Campaign
::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
68 $this->activeCampaigns
= $getCampaigns['campaigns'];
69 asort($this->activeCampaigns
);
72 $this->_columns
= array_merge($this->getColumns('Contact', array(
73 'order_bys_defaults' => array('sort_name' => 'ASC '),
74 'fields_defaults' => array('sort_name'),
75 'fields_excluded' => array('id'),
76 'fields_required' => array('id'),
77 'filters_defaults' => array('is_deleted' => 0),
78 'no_field_disambiguation' => TRUE,
80 'civicrm_email' => array(
81 'dao' => 'CRM_Core_DAO_Email',
84 'title' => ts('Donor Email'),
88 'grouping' => 'contact-fields',
90 'civicrm_line_item' => array(
91 'dao' => 'CRM_Price_DAO_LineItem',
93 'civicrm_phone' => array(
94 'dao' => 'CRM_Core_DAO_Phone',
97 'title' => ts('Donor Phone'),
102 'grouping' => 'contact-fields',
104 'civicrm_contribution' => array(
105 'dao' => 'CRM_Contribute_DAO_Contribution',
107 'contribution_id' => array(
109 'no_display' => TRUE,
112 'list_contri_id' => array(
114 'title' => ts('Contribution ID'),
116 'financial_type_id' => array(
117 'title' => ts('Financial Type'),
120 'contribution_status_id' => array(
121 'title' => ts('Contribution Status'),
123 'contribution_page_id' => array(
124 'title' => ts('Contribution Page'),
127 'title' => ts('Source'),
129 'payment_instrument_id' => array(
130 'title' => ts('Payment Type'),
132 'check_number' => array(
133 'title' => ts('Check Number'),
137 'no_display' => TRUE,
140 'receive_date' => array('default' => TRUE),
141 'receipt_date' => NULL,
142 'total_amount' => array(
143 'title' => ts('Amount'),
145 'statistics' => array('sum' => ts('Amount')),
147 'non_deductible_amount' => array(
148 'title' => ts('Non-deductible Amount'),
150 'fee_amount' => NULL,
151 'net_amount' => NULL,
152 'contribution_or_soft' => array(
153 'title' => ts('Contribution OR Soft Credit?'),
154 'dbAlias' => "'Contribution'",
156 'soft_credits' => array(
157 'title' => ts('Soft Credits'),
160 'soft_credit_for' => array(
161 'title' => ts('Soft Credit For'),
166 'contribution_or_soft' => array(
167 'title' => ts('Contribution OR Soft Credit?'),
169 'operatorType' => CRM_Report_Form
::OP_SELECT
,
170 'type' => CRM_Utils_Type
::T_STRING
,
172 'contributions_only' => ts('Contributions Only'),
173 'soft_credits_only' => ts('Soft Credits Only'),
174 'both' => ts('Both'),
177 'receive_date' => array('operatorType' => CRM_Report_Form
::OP_DATE
),
178 'contribution_source' => array(
179 'title' => ts('Source'),
181 'type' => CRM_Utils_Type
::T_STRING
,
184 'title' => ts('Currency'),
185 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
186 'options' => CRM_Core_OptionGroup
::values('currencies_enabled'),
188 'type' => CRM_Utils_Type
::T_STRING
,
190 'non_deductible_amount' => array(
191 'title' => ts('Non-deductible Amount'),
193 'financial_type_id' => array(
194 'title' => ts('Financial Type'),
195 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
196 'options' => CRM_Financial_BAO_FinancialType
::getAvailableFinancialTypes(),
197 'type' => CRM_Utils_Type
::T_INT
,
199 'contribution_page_id' => array(
200 'title' => ts('Contribution Page'),
201 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
202 'options' => CRM_Contribute_PseudoConstant
::contributionPage(),
203 'type' => CRM_Utils_Type
::T_INT
,
205 'payment_instrument_id' => array(
206 'title' => ts('Payment Type'),
207 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
208 'options' => CRM_Contribute_PseudoConstant
::paymentInstrument(),
209 'type' => CRM_Utils_Type
::T_INT
,
211 'contribution_status_id' => array(
212 'title' => ts('Contribution Status'),
213 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
214 'options' => CRM_Contribute_PseudoConstant
::contributionStatus(),
215 'default' => array(1),
216 'type' => CRM_Utils_Type
::T_INT
,
218 'total_amount' => array('title' => ts('Contribution Amount')),
220 'order_bys' => array(
221 'financial_type_id' => array('title' => ts('Financial Type')),
222 'contribution_status_id' => array('title' => ts('Contribution Status')),
223 'payment_instrument_id' => array('title' => ts('Payment Method')),
224 'receive_date' => array('title' => ts('Date Received')),
226 'group_bys' => array(
227 'contribution_id' => array(
230 'title' => ts('Contribution'),
233 'grouping' => 'contri-fields',
235 'civicrm_contribution_soft' => array(
236 'dao' => 'CRM_Contribute_DAO_ContributionSoft',
238 'soft_credit_type_id' => array('title' => ts('Soft Credit Type')),
241 'soft_credit_type_id' => array(
242 'title' => ts('Soft Credit Type'),
243 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
244 'options' => CRM_Core_OptionGroup
::values('soft_credit_type'),
246 'type' => CRM_Utils_Type
::T_STRING
,
250 'civicrm_financial_trxn' => array(
251 'dao' => 'CRM_Financial_DAO_FinancialTrxn',
253 'card_type_id' => array(
254 'title' => ts('Credit Card Type'),
258 'card_type_id' => array(
259 'title' => ts('Credit Card Type'),
260 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
261 'options' => CRM_Financial_DAO_FinancialTrxn
::buildOptions('card_type_id'),
263 'type' => CRM_Utils_Type
::T_STRING
,
267 'civicrm_batch' => array(
268 'dao' => 'CRM_Batch_DAO_EntityBatch',
269 'grouping' => 'contri-fields',
272 'name' => 'batch_id',
273 'title' => ts('Batch Name'),
278 'title' => ts('Batch Name'),
279 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
280 'options' => CRM_Batch_BAO_Batch
::getBatches(),
281 'type' => CRM_Utils_Type
::T_INT
,
282 'dbAlias' => 'batch_civireport.batch_id',
286 'civicrm_contribution_ordinality' => array(
287 'dao' => 'CRM_Contribute_DAO_Contribution',
288 'alias' => 'cordinality',
290 'ordinality' => array(
291 'title' => ts('Contribution Ordinality'),
292 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
294 0 => 'First by Contributor',
295 1 => 'Second or Later by Contributor',
297 'type' => CRM_Utils_Type
::T_INT
,
301 'civicrm_note' => array(
302 'dao' => 'CRM_Core_DAO_Note',
304 'contribution_note' => array(
306 'title' => ts('Contribution Note'),
312 'title' => ts('Contribution Note'),
313 'operator' => 'like',
314 'type' => CRM_Utils_Type
::T_STRING
,
318 )) +
$this->addAddressFields(FALSE);
319 // The tests test for this variation of the sort_name field. Don't argue with the tests :-).
320 $this->_columns
['civicrm_contact']['fields']['sort_name']['title'] = ts('Donor Name');
321 $this->_groupFilter
= TRUE;
322 $this->_tagFilter
= TRUE;
324 // If we have active campaigns add those elements to both the fields and filters
325 if ($campaignEnabled && !empty($this->activeCampaigns
)) {
326 $this->_columns
['civicrm_contribution']['fields']['campaign_id'] = array(
327 'title' => ts('Campaign'),
328 'default' => 'false',
330 $this->_columns
['civicrm_contribution']['filters']['campaign_id'] = array(
331 'title' => ts('Campaign'),
332 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
333 'options' => $this->activeCampaigns
,
334 'type' => CRM_Utils_Type
::T_INT
,
336 $this->_columns
['civicrm_contribution']['order_bys']['campaign_id'] = array('title' => ts('Campaign'));
339 $this->_currencyColumn
= 'civicrm_contribution_currency';
340 parent
::__construct();
344 * Set the FROM clause for the report.
346 public function from() {
347 $this->setFromBase('civicrm_contact');
349 INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
350 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id
351 AND {$this->_aliases['civicrm_contribution']}.is_test = 0";
353 if (CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) ==
356 $this->_from
.= "\n LEFT JOIN civicrm_contribution_soft contribution_soft_civireport
357 ON contribution_soft_civireport.contribution_id = {$this->_aliases['civicrm_contribution']}.id";
359 elseif (CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) ==
362 $this->_from
.= "\n INNER JOIN civicrm_contribution_soft contribution_soft_civireport
363 ON contribution_soft_civireport.contribution_id = {$this->_aliases['civicrm_contribution']}.id";
365 $this->appendAdditionalFromJoins();
373 public function statistics(&$rows) {
374 $statistics = parent
::statistics($rows);
376 $totalAmount = $average = $fees = $net = array();
379 SELECT COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as count,
380 SUM( {$this->_aliases['civicrm_contribution']}.total_amount ) as amount,
381 ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as avg,
382 {$this->_aliases['civicrm_contribution']}.currency as currency,
383 SUM( {$this->_aliases['civicrm_contribution']}.fee_amount ) as fees,
384 SUM( {$this->_aliases['civicrm_contribution']}.net_amount ) as net
387 $group = "\nGROUP BY {$this->_aliases['civicrm_contribution']}.currency";
388 $sql = "{$select} {$this->_from} {$this->_where} {$group}";
389 $dao = CRM_Core_DAO
::executeQuery($sql);
390 $this->addToDeveloperTab($sql);
392 while ($dao->fetch()) {
393 $totalAmount[] = CRM_Utils_Money
::format($dao->amount
, $dao->currency
) . " (" . $dao->count
. ")";
394 $fees[] = CRM_Utils_Money
::format($dao->fees
, $dao->currency
);
395 $net[] = CRM_Utils_Money
::format($dao->net
, $dao->currency
);
396 $average[] = CRM_Utils_Money
::format($dao->avg
, $dao->currency
);
397 $count +
= $dao->count
;
399 $statistics['counts']['amount'] = array(
400 'title' => ts('Total Amount (Contributions)'),
401 'value' => implode(', ', $totalAmount),
402 'type' => CRM_Utils_Type
::T_STRING
,
404 $statistics['counts']['count'] = array(
405 'title' => ts('Total Contributions'),
408 $statistics['counts']['fees'] = array(
409 'title' => ts('Fees'),
410 'value' => implode(', ', $fees),
411 'type' => CRM_Utils_Type
::T_STRING
,
413 $statistics['counts']['net'] = array(
414 'title' => ts('Net'),
415 'value' => implode(', ', $net),
416 'type' => CRM_Utils_Type
::T_STRING
,
418 $statistics['counts']['avg'] = array(
419 'title' => ts('Average'),
420 'value' => implode(', ', $average),
421 'type' => CRM_Utils_Type
::T_STRING
,
424 // Stats for soft credits
425 if ($this->_softFrom
&&
426 CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) !=
429 $totalAmount = $average = array();
432 SELECT COUNT(contribution_soft_civireport.amount ) as count,
433 SUM(contribution_soft_civireport.amount ) as amount,
434 ROUND(AVG(contribution_soft_civireport.amount), 2) as avg,
435 {$this->_aliases['civicrm_contribution']}.currency as currency";
439 GROUP BY {$this->_aliases['civicrm_contribution']}.currency";
440 $dao = CRM_Core_DAO
::executeQuery($sql);
441 $this->addToDeveloperTab($sql);
442 while ($dao->fetch()) {
443 $totalAmount[] = CRM_Utils_Money
::format($dao->amount
, $dao->currency
) . " (" .
445 $average[] = CRM_Utils_Money
::format($dao->avg
, $dao->currency
);
446 $count +
= $dao->count
;
448 $statistics['counts']['softamount'] = array(
449 'title' => ts('Total Amount (Soft Credits)'),
450 'value' => implode(', ', $totalAmount),
451 'type' => CRM_Utils_Type
::T_STRING
,
453 $statistics['counts']['softcount'] = array(
454 'title' => ts('Total Soft Credits'),
457 $statistics['counts']['softavg'] = array(
458 'title' => ts('Average (Soft Credits)'),
459 'value' => implode(', ', $average),
460 'type' => CRM_Utils_Type
::T_STRING
,
468 * This function appears to have been overrriden for the purposes of facilitating soft credits in the report.
470 * The report appears to have 2 different functions:
471 * 1) contribution report
472 * 2) soft credit report - showing a row per 'payment engagement' (payment or soft credit). There is a separate
473 * soft credit report as well.
475 * Somewhat confusingly this report returns multiple rows per contribution when soft credits are included. It feels
476 * like there is a case to split it into 2 separate reports.
478 * Soft credit functionality is not currently unit tested for this report.
480 public function postProcess() {
481 // get the acl clauses built before we assemble the query
482 $this->buildACLClause($this->_aliases
['civicrm_contact']);
484 $this->beginPostProcess();
485 // CRM-18312 - display soft_credits and soft_credits_for column
486 // when 'Contribution or Soft Credit?' column is not selected
487 if (empty($this->_params
['fields']['contribution_or_soft'])) {
488 $this->_params
['fields']['contribution_or_soft'] = 1;
489 $this->noDisplayContributionOrSoftColumn
= TRUE;
492 if (CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) ==
493 'contributions_only' &&
494 !empty($this->_params
['fields']['soft_credit_type_id'])
496 unset($this->_params
['fields']['soft_credit_type_id']);
497 if (!empty($this->_params
['soft_credit_type_id_value'])) {
498 $this->_params
['soft_credit_type_id_value'] = array();
502 // 1. use main contribution query to build temp table 1
503 $sql = $this->buildQuery();
504 $tempQuery = "CREATE TEMPORARY TABLE civireport_contribution_detail_temp1 {$this->_databaseAttributes} AS {$sql}";
505 $this->addToDeveloperTab($tempQuery);
506 CRM_Core_DAO
::executeQuery($tempQuery);
509 // 2. customize main contribution query for soft credit, and build temp table 2 with soft credit contributions only
510 $this->softCreditFrom();
511 // also include custom group from if included
512 // since this might be included in select
513 $this->customDataFrom();
515 $select = str_ireplace('contribution_civireport.total_amount', 'contribution_soft_civireport.amount', $this->_select
);
516 $select = str_ireplace("'Contribution' as", "'Soft Credit' as", $select);
517 // We really don't want to join soft credit in if not required.
518 if (!empty($this->_groupBy
) && !$this->noDisplayContributionOrSoftColumn
) {
519 $this->_groupBy
.= ', contribution_soft_civireport.amount';
521 // we inner join with temp1 to restrict soft contributions to those in temp1 table
522 $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy}";
523 $tempQuery = "CREATE TEMPORARY TABLE civireport_contribution_detail_temp2 {$this->_databaseAttributes} AS {$sql}";
524 $this->addToDeveloperTab($tempQuery);
525 CRM_Core_DAO
::executeQuery($tempQuery);
526 if (CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) ==
529 // revise pager : prev, next based on soft-credits only
533 // copy _from for later use of stats calculation for soft credits, and reset $this->_from to main query
534 $this->_softFrom
= $this->_from
;
536 // simple reset of ->_from
539 // also include custom group from if included
540 // since this might be included in select
541 $this->customDataFrom();
543 // 3. Decide where to populate temp3 table from
544 if (CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) ==
547 $tempQuery = "(SELECT * FROM civireport_contribution_detail_temp1)";
549 elseif (CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) ==
552 $tempQuery = "(SELECT * FROM civireport_contribution_detail_temp2)";
556 (SELECT * FROM civireport_contribution_detail_temp1)
558 (SELECT * FROM civireport_contribution_detail_temp2)";
561 // 4. build temp table 3
562 $sql = "CREATE TEMPORARY TABLE civireport_contribution_detail_temp3 {$this->_databaseAttributes} AS {$tempQuery}";
563 $this->addToDeveloperTab($sql);
564 CRM_Core_DAO
::executeQuery($sql);
566 // 6. show result set from temp table 3
568 $sql = "SELECT * FROM civireport_contribution_detail_temp3 $this->_orderBy";
569 $this->buildRows($sql, $rows);
571 // format result set.
572 $this->formatDisplay($rows, FALSE);
574 // assign variables to templates
575 $this->doTemplateAssignment($rows);
576 // do print / pdf / instance stuff if needed
577 $this->endPostProcess($rows);
581 * Alter display of rows.
583 * Iterate through the rows retrieved via SQL and make changes for display purposes,
584 * such as rendering contacts as links.
587 * Rows generated by SQL, with an array for each row.
589 public function alterDisplay(&$rows) {
590 $checkList = array();
592 $display_flag = $prev_cid = $cid = 0;
593 $contributionTypes = CRM_Contribute_PseudoConstant
::financialType();
594 $contributionStatus = CRM_Contribute_PseudoConstant
::contributionStatus();
595 $paymentInstruments = CRM_Contribute_PseudoConstant
::paymentInstrument();
596 $contributionPages = CRM_Contribute_PseudoConstant
::contributionPage();
597 $batches = CRM_Batch_BAO_Batch
::getBatches();
598 foreach ($rows as $rowNum => $row) {
599 if (!empty($this->_noRepeats
) && $this->_outputMode
!= 'csv') {
600 // don't repeat contact details if its same as the previous row
601 if (array_key_exists('civicrm_contact_id', $row)) {
602 if ($cid = $row['civicrm_contact_id']) {
607 if ($prev_cid == $cid) {
618 foreach ($row as $colName => $colVal) {
619 // Hide repeats in no-repeat columns, but not if the field's a section header
620 if (in_array($colName, $this->_noRepeats
) &&
621 !array_key_exists($colName, $this->_sections
)
623 unset($rows[$rowNum][$colName]);
632 if (CRM_Utils_Array
::value('civicrm_contribution_contribution_or_soft', $rows[$rowNum]) ==
635 unset($rows[$rowNum]['civicrm_contribution_soft_soft_credit_type_id']);
638 $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, 'contribution/detail', ts('View Contribution Details')) ?
TRUE : $entryFound;
639 // convert donor sort name to link
640 if (array_key_exists('civicrm_contact_sort_name', $row) &&
641 !empty($rows[$rowNum]['civicrm_contact_sort_name']) &&
642 array_key_exists('civicrm_contact_id', $row)
644 $url = CRM_Utils_System
::url("civicrm/contact/view",
645 'reset=1&cid=' . $row['civicrm_contact_id'],
648 $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
649 $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
652 if ($value = CRM_Utils_Array
::value('civicrm_contribution_financial_type_id', $row)) {
653 $rows[$rowNum]['civicrm_contribution_financial_type_id'] = $contributionTypes[$value];
656 if ($value = CRM_Utils_Array
::value('civicrm_contribution_contribution_status_id', $row)) {
657 $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value];
660 if ($value = CRM_Utils_Array
::value('civicrm_contribution_contribution_page_id', $row)) {
661 $rows[$rowNum]['civicrm_contribution_contribution_page_id'] = $contributionPages[$value];
664 if ($value = CRM_Utils_Array
::value('civicrm_contribution_payment_instrument_id', $row)) {
665 $rows[$rowNum]['civicrm_contribution_payment_instrument_id'] = $paymentInstruments[$value];
668 if (!empty($row['civicrm_batch_batch_id'])) {
669 $rows[$rowNum]['civicrm_batch_batch_id'] = CRM_Utils_Array
::value($row['civicrm_batch_batch_id'], $batches);
672 if (!empty($row['civicrm_financial_trxn_card_type_id'])) {
673 $rows[$rowNum]['civicrm_financial_trxn_card_type_id'] = $this->getLabels($row['civicrm_financial_trxn_card_type_id'], 'CRM_Financial_DAO_FinancialTrxn', 'card_type_id');
677 // Contribution amount links to viewing contribution
678 if (($value = CRM_Utils_Array
::value('civicrm_contribution_total_amount', $row)) &&
679 CRM_Core_Permission
::check('access CiviContribute')
681 $url = CRM_Utils_System
::url("civicrm/contact/view/contribution",
682 "reset=1&id=" . $row['civicrm_contribution_contribution_id'] .
683 "&cid=" . $row['civicrm_contact_id'] .
684 "&action=view&context=contribution&selectedChild=contribute",
687 $rows[$rowNum]['civicrm_contribution_total_amount_link'] = $url;
688 $rows[$rowNum]['civicrm_contribution_total_amount_hover'] = ts("View Details of this Contribution.");
692 // convert campaign_id to campaign title
693 if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
694 if ($value = $row['civicrm_contribution_campaign_id']) {
695 $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->activeCampaigns
[$value];
701 if (array_key_exists('civicrm_contribution_soft_credits', $row) &&
703 CRM_Utils_Array
::value('civicrm_contribution_contribution_or_soft', $rows[$rowNum]) &&
704 array_key_exists('civicrm_contribution_contribution_id', $row)
707 SELECT civicrm_contact_id, civicrm_contact_sort_name, civicrm_contribution_total_amount, civicrm_contribution_currency
708 FROM civireport_contribution_detail_temp2
709 WHERE civicrm_contribution_contribution_id={$row['civicrm_contribution_contribution_id']}";
710 $this->addToDeveloperTab($query);
711 $dao = CRM_Core_DAO
::executeQuery($query);
713 $separator = ($this->_outputMode
!== 'csv') ?
"<br/>" : ' ';
714 while ($dao->fetch()) {
715 $url = CRM_Utils_System
::url("civicrm/contact/view", 'reset=1&cid=' .
716 $dao->civicrm_contact_id
);
717 $string = $string . ($string ?
$separator : '') .
718 "<a href='{$url}'>{$dao->civicrm_contact_sort_name}</a> " .
719 CRM_Utils_Money
::format($dao->civicrm_contribution_total_amount
, $dao->civicrm_contribution_currency
);
721 $rows[$rowNum]['civicrm_contribution_soft_credits'] = $string;
724 if (array_key_exists('civicrm_contribution_soft_credit_for', $row) &&
726 CRM_Utils_Array
::value('civicrm_contribution_contribution_or_soft', $rows[$rowNum]) &&
727 array_key_exists('civicrm_contribution_contribution_id', $row)
730 SELECT civicrm_contact_id, civicrm_contact_sort_name
731 FROM civireport_contribution_detail_temp1
732 WHERE civicrm_contribution_contribution_id={$row['civicrm_contribution_contribution_id']}";
733 $this->addToDeveloperTab($query);
734 $dao = CRM_Core_DAO
::executeQuery($query);
736 while ($dao->fetch()) {
737 $url = CRM_Utils_System
::url("civicrm/contact/view", 'reset=1&cid=' .
738 $dao->civicrm_contact_id
);
740 "\n<a href='{$url}'>{$dao->civicrm_contact_sort_name}</a>";
742 $rows[$rowNum]['civicrm_contribution_soft_credit_for'] = $string;
745 // CRM-18312 - hide 'contribution_or_soft' column if unchecked.
746 if (!empty($this->noDisplayContributionOrSoftColumn
)) {
747 unset($rows[$rowNum]['civicrm_contribution_contribution_or_soft']);
748 unset($this->_columnHeaders
['civicrm_contribution_contribution_or_soft']);
751 //convert soft_credit_type_id into label
752 if (array_key_exists('civicrm_contribution_soft_soft_credit_type_id', $rows[$rowNum])) {
753 $rows[$rowNum]['civicrm_contribution_soft_soft_credit_type_id'] = CRM_Core_PseudoConstant
::getLabel(
754 'CRM_Contribute_BAO_ContributionSoft',
755 'soft_credit_type_id',
756 $row['civicrm_contribution_soft_soft_credit_type_id']
760 $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s) for this ') ?
TRUE : $entryFound;
762 // skip looking further in rows, if first row itself doesn't
763 // have the column we need
771 public function sectionTotals() {
773 // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
774 if (empty($this->_selectAliases
)) {
778 if (!empty($this->_sections
)) {
779 // build the query with no LIMIT clause
780 $select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', 'SELECT ', $this->_select
);
781 $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
783 // pull section aliases out of $this->_sections
784 $sectionAliases = array_keys($this->_sections
);
787 foreach (array_merge($sectionAliases, $this->_selectAliases
) as $alias) {
788 $ifnulls[] = "ifnull($alias, '') as $alias";
790 $this->_select
= "SELECT " . implode(", ", $ifnulls);
791 $this->_select
= CRM_Contact_BAO_Query
::appendAnyValueToSelect($ifnulls, $sectionAliases);
793 /* Group (un-limited) report by all aliases and get counts. This might
794 * be done more efficiently when the contents of $sql are known, ie. by
795 * overriding this method in the report class.
800 if (array_search("civicrm_contribution_total_amount", $this->_selectAliases
) !==
803 $addtotals = ", sum(civicrm_contribution_total_amount) as sumcontribs";
804 $showsumcontribs = TRUE;
807 $query = $this->_select
.
808 "$addtotals, count(*) as ct from civireport_contribution_detail_temp3 group by " .
809 implode(", ", $sectionAliases);
810 // initialize array of total counts
811 $sumcontribs = $totals = array();
812 $dao = CRM_Core_DAO
::executeQuery($query);
813 $this->addToDeveloperTab($query);
814 while ($dao->fetch()) {
816 // let $this->_alterDisplay translate any integer ids to human-readable values.
817 $rows[0] = $dao->toArray();
818 $this->alterDisplay($rows);
821 // add totals for all permutations of section values
824 $aliasCount = count($sectionAliases);
825 foreach ($sectionAliases as $alias) {
826 $values[] = $row[$alias];
827 $key = implode(CRM_Core_DAO
::VALUE_SEPARATOR
, $values);
828 if ($i == $aliasCount) {
829 // the last alias is the lowest-level section header; use count as-is
830 $totals[$key] = $dao->ct
;
831 if ($showsumcontribs) {
832 $sumcontribs[$key] = $dao->sumcontribs
;
836 // other aliases are higher level; roll count into their total
837 $totals[$key] = (array_key_exists($key, $totals)) ?
$totals[$key] +
$dao->ct
: $dao->ct
;
838 if ($showsumcontribs) {
839 $sumcontribs[$key] = array_key_exists($key, $sumcontribs) ?
$sumcontribs[$key] +
$dao->sumcontribs
: $dao->sumcontribs
;
844 if ($showsumcontribs) {
845 $totalandsum = array();
846 // ts exception to avoid having ts("%1 %2: %3")
847 $title = '%1 contributions / soft-credits: %2';
849 if (CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) ==
852 $title = '%1 contributions: %2';
854 elseif (CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) ==
857 $title = '%1 soft-credits: %2';
859 foreach ($totals as $key => $total) {
860 $totalandsum[$key] = ts($title, array(
862 2 => CRM_Utils_Money
::format($sumcontribs[$key]),
865 $this->assign('sectionTotals', $totalandsum);
868 $this->assign('sectionTotals', $totals);
874 * Generate the from clause as it relates to the soft credits.
876 public function softCreditFrom() {
879 FROM civireport_contribution_detail_temp1 temp1_civireport
880 INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
881 ON temp1_civireport.civicrm_contribution_contribution_id = {$this->_aliases['civicrm_contribution']}.id
882 INNER JOIN civicrm_contribution_soft contribution_soft_civireport
883 ON contribution_soft_civireport.contribution_id = {$this->_aliases['civicrm_contribution']}.id
884 INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
885 ON {$this->_aliases['civicrm_contact']}.id = contribution_soft_civireport.contact_id
889 $this->appendAdditionalFromJoins();
893 * Append the joins that are required regardless of context.
895 public function appendAdditionalFromJoins() {
896 if (!empty($this->_params
['ordinality_value'])) {
898 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']}
899 ON {$this->_aliases['civicrm_contribution_ordinality']}.id = {$this->_aliases['civicrm_contribution']}.id";
901 $this->joinPhoneFromContact();
902 $this->joinAddressFromContact();
903 $this->joinEmailFromContact();
905 // include contribution note
906 if (!empty($this->_params
['fields']['contribution_note']) ||
907 !empty($this->_params
['note_value'])
910 LEFT JOIN civicrm_note {$this->_aliases['civicrm_note']}
911 ON ( {$this->_aliases['civicrm_note']}.entity_table = 'civicrm_contribution' AND
912 {$this->_aliases['civicrm_contribution']}.id = {$this->_aliases['civicrm_note']}.entity_id )";
914 //for contribution batches
915 if (!empty($this->_params
['fields']['batch_id']) ||
916 !empty($this->_params
['bid_value'])
919 LEFT JOIN civicrm_entity_financial_trxn eft
920 ON eft.entity_id = {$this->_aliases['civicrm_contribution']}.id AND
921 eft.entity_table = 'civicrm_contribution'
922 LEFT JOIN civicrm_entity_batch {$this->_aliases['civicrm_batch']}
923 ON ({$this->_aliases['civicrm_batch']}.entity_id = eft.financial_trxn_id
924 AND {$this->_aliases['civicrm_batch']}.entity_table = 'civicrm_financial_trxn')";
926 // for credit card type
927 $this->addFinancialTrxnFromClause();