3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
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
{
34 protected $_addressField = FALSE;
36 protected $_emailField = FALSE;
38 protected $_summary = NULL;
40 protected $_softFrom = NULL;
42 protected $_customGroupExtends = array(
49 * This report has been optimised for group filtering.
55 protected $groupFilterNotOptimised = FALSE;
60 public function __construct() {
61 $this->_autoIncludeIndexedFieldsAsOrderBys
= 1;
62 // Check if CiviCampaign is a) enabled and b) has active campaigns
63 $config = CRM_Core_Config
::singleton();
64 $campaignEnabled = in_array("CiviCampaign", $config->enableComponents
);
65 if ($campaignEnabled) {
66 $getCampaigns = CRM_Campaign_BAO_Campaign
::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
67 $this->activeCampaigns
= $getCampaigns['campaigns'];
68 asort($this->activeCampaigns
);
71 $this->_columns
= array_merge($this->getColumns('Contact', array(
72 'order_bys_defaults' => array('sort_name' => 'ASC '),
73 'fields_defaults' => array('sort_name'),
74 'fields_excluded' => array('id'),
75 'fields_required' => array('id'),
76 'filters_defaults' => array('is_deleted' => 0),
77 'no_field_disambiguation' => TRUE,
79 'civicrm_email' => array(
80 'dao' => 'CRM_Core_DAO_Email',
83 'title' => ts('Donor Email'),
87 'grouping' => 'contact-fields',
89 'civicrm_line_item' => array(
90 'dao' => 'CRM_Price_DAO_LineItem',
92 'civicrm_phone' => array(
93 'dao' => 'CRM_Core_DAO_Phone',
96 'title' => ts('Donor Phone'),
101 'grouping' => 'contact-fields',
103 'civicrm_contribution' => array(
104 'dao' => 'CRM_Contribute_DAO_Contribution',
106 'contribution_id' => array(
108 'no_display' => TRUE,
111 'list_contri_id' => array(
113 'title' => ts('Contribution ID'),
115 'financial_type_id' => array(
116 'title' => ts('Financial Type'),
119 'contribution_status_id' => array(
120 'title' => ts('Contribution Status'),
122 'contribution_page_id' => array(
123 'title' => ts('Contribution Page'),
126 'title' => ts('Source'),
128 'payment_instrument_id' => array(
129 'title' => ts('Payment Type'),
131 'check_number' => array(
132 'title' => ts('Check Number'),
136 'no_display' => TRUE,
139 'receive_date' => array('default' => TRUE),
140 'receipt_date' => NULL,
141 'total_amount' => array(
142 'title' => ts('Amount'),
144 'statistics' => array('sum' => ts('Amount')),
146 'non_deductible_amount' => array(
147 'title' => ts('Non-deductible Amount'),
149 'fee_amount' => NULL,
150 'net_amount' => NULL,
151 'contribution_or_soft' => array(
152 'title' => ts('Contribution OR Soft Credit?'),
153 'dbAlias' => "'Contribution'",
155 'soft_credits' => array(
156 'title' => ts('Soft Credits'),
159 'soft_credit_for' => array(
160 'title' => ts('Soft Credit For'),
165 'contribution_or_soft' => array(
166 'title' => ts('Contribution OR Soft Credit?'),
168 'operatorType' => CRM_Report_Form
::OP_SELECT
,
169 'type' => CRM_Utils_Type
::T_STRING
,
171 'contributions_only' => ts('Contributions Only'),
172 'soft_credits_only' => ts('Soft Credits Only'),
173 'both' => ts('Both'),
176 'receive_date' => array('operatorType' => CRM_Report_Form
::OP_DATE
),
177 'contribution_source' => array(
178 'title' => ts('Source'),
180 'type' => CRM_Utils_Type
::T_STRING
,
183 'title' => ts('Currency'),
184 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
185 'options' => CRM_Core_OptionGroup
::values('currencies_enabled'),
187 'type' => CRM_Utils_Type
::T_STRING
,
189 'non_deductible_amount' => array(
190 'title' => ts('Non-deductible Amount'),
192 'financial_type_id' => array(
193 'title' => ts('Financial Type'),
194 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
195 'options' => CRM_Financial_BAO_FinancialType
::getAvailableFinancialTypes(),
196 'type' => CRM_Utils_Type
::T_INT
,
198 'contribution_page_id' => array(
199 'title' => ts('Contribution Page'),
200 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
201 'options' => CRM_Contribute_PseudoConstant
::contributionPage(),
202 'type' => CRM_Utils_Type
::T_INT
,
204 'payment_instrument_id' => array(
205 'title' => ts('Payment Type'),
206 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
207 'options' => CRM_Contribute_PseudoConstant
::paymentInstrument(),
208 'type' => CRM_Utils_Type
::T_INT
,
210 'contribution_status_id' => array(
211 'title' => ts('Contribution Status'),
212 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
213 'options' => CRM_Contribute_PseudoConstant
::contributionStatus(),
214 'default' => array(1),
215 'type' => CRM_Utils_Type
::T_INT
,
217 'total_amount' => array('title' => ts('Contribution Amount')),
219 'order_bys' => array(
220 'financial_type_id' => array('title' => ts('Financial Type')),
221 'contribution_status_id' => array('title' => ts('Contribution Status')),
222 'payment_instrument_id' => array('title' => ts('Payment Method')),
223 'receive_date' => array('title' => ts('Date Received')),
225 'grouping' => 'contri-fields',
227 'civicrm_contribution_soft' => array(
228 'dao' => 'CRM_Contribute_DAO_ContributionSoft',
230 'soft_credit_type_id' => array('title' => ts('Soft Credit Type')),
233 'soft_credit_type_id' => array(
234 'title' => ts('Soft Credit Type'),
235 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
236 'options' => CRM_Core_OptionGroup
::values('soft_credit_type'),
238 'type' => CRM_Utils_Type
::T_STRING
,
242 'civicrm_financial_trxn' => array(
243 'dao' => 'CRM_Financial_DAO_FinancialTrxn',
245 'card_type_id' => array(
246 'title' => ts('Credit Card Type'),
247 'dbAlias' => 'GROUP_CONCAT(financial_trxn_civireport.card_type_id SEPARATOR ",")',
251 'card_type_id' => array(
252 'title' => ts('Credit Card Type'),
253 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
254 'options' => CRM_Financial_DAO_FinancialTrxn
::buildOptions('card_type_id'),
256 'type' => CRM_Utils_Type
::T_STRING
,
260 'civicrm_batch' => array(
261 'dao' => 'CRM_Batch_DAO_EntityBatch',
262 'grouping' => 'contri-fields',
265 'name' => 'batch_id',
266 'title' => ts('Batch Name'),
271 'title' => ts('Batch Name'),
272 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
273 'options' => CRM_Batch_BAO_Batch
::getBatches(),
274 'type' => CRM_Utils_Type
::T_INT
,
275 'dbAlias' => 'batch_civireport.batch_id',
279 'civicrm_contribution_ordinality' => array(
280 'dao' => 'CRM_Contribute_DAO_Contribution',
281 'alias' => 'cordinality',
283 'ordinality' => array(
284 'title' => ts('Contribution Ordinality'),
285 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
287 0 => 'First by Contributor',
288 1 => 'Second or Later by Contributor',
290 'type' => CRM_Utils_Type
::T_INT
,
294 'civicrm_note' => array(
295 'dao' => 'CRM_Core_DAO_Note',
297 'contribution_note' => array(
299 'title' => ts('Contribution Note'),
305 'title' => ts('Contribution Note'),
306 'operator' => 'like',
307 'type' => CRM_Utils_Type
::T_STRING
,
311 )) +
$this->addAddressFields(FALSE);
312 // The tests test for this variation of the sort_name field. Don't argue with the tests :-).
313 $this->_columns
['civicrm_contact']['fields']['sort_name']['title'] = ts('Donor Name');
314 $this->_groupFilter
= TRUE;
315 $this->_tagFilter
= TRUE;
317 // If we have active campaigns add those elements to both the fields and filters
318 if ($campaignEnabled && !empty($this->activeCampaigns
)) {
319 $this->_columns
['civicrm_contribution']['fields']['campaign_id'] = array(
320 'title' => ts('Campaign'),
321 'default' => 'false',
323 $this->_columns
['civicrm_contribution']['filters']['campaign_id'] = array(
324 'title' => ts('Campaign'),
325 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
326 'options' => $this->activeCampaigns
,
327 'type' => CRM_Utils_Type
::T_INT
,
329 $this->_columns
['civicrm_contribution']['order_bys']['campaign_id'] = array('title' => ts('Campaign'));
332 $this->_currencyColumn
= 'civicrm_contribution_currency';
333 parent
::__construct();
336 public function preProcess() {
337 parent
::preProcess();
340 public function select() {
341 $this->_columnHeaders
= array();
344 //total_amount was affected by sum as it is considered as one of the stat field
345 //so it is been replaced with correct alias, CRM-13833
346 $this->_select
= str_replace("sum({$this->_aliases['civicrm_contribution']}.total_amount)", "{$this->_aliases['civicrm_contribution']}.total_amount", $this->_select
);
347 $this->_selectClauses
= str_replace("sum({$this->_aliases['civicrm_contribution']}.total_amount)", "{$this->_aliases['civicrm_contribution']}.total_amount", $this->_selectClauses
);
350 public function orderBy() {
353 // please note this will just add the order-by columns to select query, and not display in column-headers.
354 // This is a solution to not throw fatal errors when there is a column in order-by, not present in select/display columns.
355 foreach ($this->_orderByFields
as $orderBy) {
356 if (!array_key_exists($orderBy['name'], $this->_params
['fields']) &&
357 empty($orderBy['section']) && (strpos($this->_select
, $orderBy['dbAlias']) === FALSE)
359 $this->_select
.= ", {$orderBy['dbAlias']} as {$orderBy['tplField']}";
365 * Set the FROM clause for the report.
367 public function from() {
368 $this->setFromBase('civicrm_contact');
370 INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
371 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id
372 AND {$this->_aliases['civicrm_contribution']}.is_test = 0";
374 if (CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) ==
377 $this->_from
.= "\n LEFT JOIN civicrm_contribution_soft contribution_soft_civireport
378 ON contribution_soft_civireport.contribution_id = {$this->_aliases['civicrm_contribution']}.id";
380 elseif (CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) ==
383 $this->_from
.= "\n INNER JOIN civicrm_contribution_soft contribution_soft_civireport
384 ON contribution_soft_civireport.contribution_id = {$this->_aliases['civicrm_contribution']}.id";
386 $this->appendAdditionalFromJoins();
389 public function groupBy() {
390 $groupBy = array("{$this->_aliases['civicrm_contact']}.id", "{$this->_aliases['civicrm_contribution']}.id");
391 $this->_groupBy
= CRM_Contact_BAO_Query
::getGroupByFromSelectColumns($this->_selectClauses
, $groupBy);
399 public function statistics(&$rows) {
400 $statistics = parent
::statistics($rows);
402 $totalAmount = $average = $fees = $net = array();
405 SELECT COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as count,
406 SUM( {$this->_aliases['civicrm_contribution']}.total_amount ) as amount,
407 ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as avg,
408 {$this->_aliases['civicrm_contribution']}.currency as currency,
409 SUM( {$this->_aliases['civicrm_contribution']}.fee_amount ) as fees,
410 SUM( {$this->_aliases['civicrm_contribution']}.net_amount ) as net
413 $group = "\nGROUP BY {$this->_aliases['civicrm_contribution']}.currency";
414 $sql = "{$select} {$this->_from} {$this->_where} {$group}";
415 $dao = CRM_Core_DAO
::executeQuery($sql);
417 while ($dao->fetch()) {
418 $totalAmount[] = CRM_Utils_Money
::format($dao->amount
, $dao->currency
) . " (" . $dao->count
. ")";
419 $fees[] = CRM_Utils_Money
::format($dao->fees
, $dao->currency
);
420 $net[] = CRM_Utils_Money
::format($dao->net
, $dao->currency
);
421 $average[] = CRM_Utils_Money
::format($dao->avg
, $dao->currency
);
422 $count +
= $dao->count
;
424 $statistics['counts']['amount'] = array(
425 'title' => ts('Total Amount (Contributions)'),
426 'value' => implode(', ', $totalAmount),
427 'type' => CRM_Utils_Type
::T_STRING
,
429 $statistics['counts']['count'] = array(
430 'title' => ts('Total Contributions'),
433 $statistics['counts']['fees'] = array(
434 'title' => ts('Fees'),
435 'value' => implode(', ', $fees),
436 'type' => CRM_Utils_Type
::T_STRING
,
438 $statistics['counts']['net'] = array(
439 'title' => ts('Net'),
440 'value' => implode(', ', $net),
441 'type' => CRM_Utils_Type
::T_STRING
,
443 $statistics['counts']['avg'] = array(
444 'title' => ts('Average'),
445 'value' => implode(', ', $average),
446 'type' => CRM_Utils_Type
::T_STRING
,
449 // Stats for soft credits
450 if ($this->_softFrom
&&
451 CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) !=
454 $totalAmount = $average = array();
457 SELECT COUNT(contribution_soft_civireport.amount ) as count,
458 SUM(contribution_soft_civireport.amount ) as amount,
459 ROUND(AVG(contribution_soft_civireport.amount), 2) as avg,
460 {$this->_aliases['civicrm_contribution']}.currency as currency";
464 GROUP BY {$this->_aliases['civicrm_contribution']}.currency";
465 $dao = CRM_Core_DAO
::executeQuery($sql);
466 while ($dao->fetch()) {
467 $totalAmount[] = CRM_Utils_Money
::format($dao->amount
, $dao->currency
) . " (" .
469 $average[] = CRM_Utils_Money
::format($dao->avg
, $dao->currency
);
470 $count +
= $dao->count
;
472 $statistics['counts']['softamount'] = array(
473 'title' => ts('Total Amount (Soft Credits)'),
474 'value' => implode(', ', $totalAmount),
475 'type' => CRM_Utils_Type
::T_STRING
,
477 $statistics['counts']['softcount'] = array(
478 'title' => ts('Total Soft Credits'),
481 $statistics['counts']['softavg'] = array(
482 'title' => ts('Average (Soft Credits)'),
483 'value' => implode(', ', $average),
484 'type' => CRM_Utils_Type
::T_STRING
,
492 * This function appears to have been overrriden for the purposes of facilitating soft credits in the report.
494 * The report appears to have 2 different functions:
495 * 1) contribution report
496 * 2) soft credit report - showing a row per 'payment engagement' (payment or soft credit). There is a separate
497 * soft credit report as well.
499 * Somewhat confusingly this report returns multiple rows per contribution when soft credits are included. It feels
500 * like there is a case to split it into 2 separate reports.
502 * Soft credit functionality is not currently unit tested for this report.
504 public function postProcess() {
505 // get the acl clauses built before we assemble the query
506 $this->buildACLClause($this->_aliases
['civicrm_contact']);
508 $this->beginPostProcess();
509 // CRM-18312 - display soft_credits and soft_credits_for column
510 // when 'Contribution or Soft Credit?' column is not selected
511 if (empty($this->_params
['fields']['contribution_or_soft'])) {
512 $this->_params
['fields']['contribution_or_soft'] = 1;
513 $this->noDisplayContributionOrSoftColumn
= TRUE;
516 if (CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) ==
517 'contributions_only' &&
518 !empty($this->_params
['fields']['soft_credit_type_id'])
520 unset($this->_params
['fields']['soft_credit_type_id']);
521 if (!empty($this->_params
['soft_credit_type_id_value'])) {
522 $this->_params
['soft_credit_type_id_value'] = array();
526 // 1. use main contribution query to build temp table 1
527 $sql = $this->buildQuery();
528 $tempQuery = "CREATE TEMPORARY TABLE civireport_contribution_detail_temp1 {$this->_databaseAttributes} AS {$sql}";
529 CRM_Core_DAO
::executeQuery($tempQuery);
532 // 2. customize main contribution query for soft credit, and build temp table 2 with soft credit contributions only
533 $this->softCreditFrom();
534 // also include custom group from if included
535 // since this might be included in select
536 $this->customDataFrom();
538 $select = str_ireplace('contribution_civireport.total_amount', 'contribution_soft_civireport.amount', $this->_select
);
539 $select = str_ireplace("'Contribution' as", "'Soft Credit' as", $select);
540 // We really don't want to join soft credit in if not required.
541 if (!empty($this->_groupBy
) && !$this->noDisplayContributionOrSoftColumn
) {
542 $this->_groupBy
.= ', contribution_soft_civireport.amount';
544 // we inner join with temp1 to restrict soft contributions to those in temp1 table
545 $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy}";
546 $tempQuery = "CREATE TEMPORARY TABLE civireport_contribution_detail_temp2 {$this->_databaseAttributes} AS {$sql}";
547 CRM_Core_DAO
::executeQuery($tempQuery);
548 if (CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) ==
551 // revise pager : prev, next based on soft-credits only
555 // copy _from for later use of stats calculation for soft credits, and reset $this->_from to main query
556 $this->_softFrom
= $this->_from
;
558 // simple reset of ->_from
561 // also include custom group from if included
562 // since this might be included in select
563 $this->customDataFrom();
565 // 3. Decide where to populate temp3 table from
566 if (CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) ==
569 $tempQuery = "(SELECT * FROM civireport_contribution_detail_temp1)";
571 elseif (CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) ==
574 $tempQuery = "(SELECT * FROM civireport_contribution_detail_temp2)";
578 (SELECT * FROM civireport_contribution_detail_temp1)
580 (SELECT * FROM civireport_contribution_detail_temp2)";
583 // 4. build temp table 3
584 $sql = "CREATE TEMPORARY TABLE civireport_contribution_detail_temp3 {$this->_databaseAttributes} AS {$tempQuery}";
585 CRM_Core_DAO
::executeQuery($sql);
587 // 5. Re-construct order-by to make sense for final query on temp3 table
589 if (!empty($this->_orderByArray
)) {
590 $aliases = array_flip($this->_aliases
);
591 $orderClause = array();
592 foreach ($this->_orderByArray
as $clause) {
593 list($alias, $rest) = explode('.', $clause);
594 // CRM-17280 -- In case, we are ordering by custom fields
595 // modify $rest to match the alias used for them in temp3 table
596 $grp = new CRM_Core_DAO_CustomGroup();
597 $grp->table_name
= $aliases[$alias];
599 list($fld, $order) = explode(' ', $rest);
600 foreach ($this->_columns
[$aliases[$alias]]['fields'] as $fldName => $value) {
601 if ($value['name'] == $fld) {
605 $rest = "{$fld} {$order}";
607 $orderClause[] = $aliases[$alias] . "_" . $rest;
609 $orderBy = (!empty($orderClause)) ?
"ORDER BY " . implode(', ', $orderClause) : '';
612 // 6. show result set from temp table 3
614 $sql = "SELECT * FROM civireport_contribution_detail_temp3 {$orderBy}";
615 $this->buildRows($sql, $rows);
617 // format result set.
618 $this->formatDisplay($rows, FALSE);
620 // assign variables to templates
621 $this->doTemplateAssignment($rows);
622 // do print / pdf / instance stuff if needed
623 $this->endPostProcess($rows);
627 * Alter display of rows.
629 * Iterate through the rows retrieved via SQL and make changes for display purposes,
630 * such as rendering contacts as links.
633 * Rows generated by SQL, with an array for each row.
635 public function alterDisplay(&$rows) {
636 $checkList = array();
638 $display_flag = $prev_cid = $cid = 0;
639 $contributionTypes = CRM_Contribute_PseudoConstant
::financialType();
640 $contributionStatus = CRM_Contribute_PseudoConstant
::contributionStatus();
641 $paymentInstruments = CRM_Contribute_PseudoConstant
::paymentInstrument();
642 $contributionPages = CRM_Contribute_PseudoConstant
::contributionPage();
643 $batches = CRM_Batch_BAO_Batch
::getBatches();
644 foreach ($rows as $rowNum => $row) {
645 if (!empty($this->_noRepeats
) && $this->_outputMode
!= 'csv') {
646 // don't repeat contact details if its same as the previous row
647 if (array_key_exists('civicrm_contact_id', $row)) {
648 if ($cid = $row['civicrm_contact_id']) {
653 if ($prev_cid == $cid) {
664 foreach ($row as $colName => $colVal) {
665 // Hide repeats in no-repeat columns, but not if the field's a section header
666 if (in_array($colName, $this->_noRepeats
) &&
667 !array_key_exists($colName, $this->_sections
)
669 unset($rows[$rowNum][$colName]);
678 if (CRM_Utils_Array
::value('civicrm_contribution_contribution_or_soft', $rows[$rowNum]) ==
681 unset($rows[$rowNum]['civicrm_contribution_soft_soft_credit_type_id']);
684 $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, 'contribution/detail', ts('View Contribution Details')) ?
TRUE : $entryFound;
685 // convert donor sort name to link
686 if (array_key_exists('civicrm_contact_sort_name', $row) &&
687 !empty($rows[$rowNum]['civicrm_contact_sort_name']) &&
688 array_key_exists('civicrm_contact_id', $row)
690 $url = CRM_Utils_System
::url("civicrm/contact/view",
691 'reset=1&cid=' . $row['civicrm_contact_id'],
694 $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
695 $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
698 if ($value = CRM_Utils_Array
::value('civicrm_contribution_financial_type_id', $row)) {
699 $rows[$rowNum]['civicrm_contribution_financial_type_id'] = $contributionTypes[$value];
702 if ($value = CRM_Utils_Array
::value('civicrm_contribution_contribution_status_id', $row)) {
703 $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value];
706 if ($value = CRM_Utils_Array
::value('civicrm_contribution_contribution_page_id', $row)) {
707 $rows[$rowNum]['civicrm_contribution_contribution_page_id'] = $contributionPages[$value];
710 if ($value = CRM_Utils_Array
::value('civicrm_contribution_payment_instrument_id', $row)) {
711 $rows[$rowNum]['civicrm_contribution_payment_instrument_id'] = $paymentInstruments[$value];
714 if (!empty($row['civicrm_batch_batch_id'])) {
715 $rows[$rowNum]['civicrm_batch_batch_id'] = CRM_Utils_Array
::value($row['civicrm_batch_batch_id'], $batches);
718 if (!empty($row['civicrm_financial_trxn_card_type_id'])) {
719 $rows[$rowNum]['civicrm_financial_trxn_card_type_id'] = $this->getLabels($row['civicrm_financial_trxn_card_type_id'], 'CRM_Financial_DAO_FinancialTrxn', 'card_type_id');
723 // Contribution amount links to viewing contribution
724 if (($value = CRM_Utils_Array
::value('civicrm_contribution_total_amount_sum', $row)) &&
725 CRM_Core_Permission
::check('access CiviContribute')
727 $url = CRM_Utils_System
::url("civicrm/contact/view/contribution",
728 "reset=1&id=" . $row['civicrm_contribution_contribution_id'] .
729 "&cid=" . $row['civicrm_contact_id'] .
730 "&action=view&context=contribution&selectedChild=contribute",
733 $rows[$rowNum]['civicrm_contribution_total_amount_sum_link'] = $url;
734 $rows[$rowNum]['civicrm_contribution_total_amount_sum_hover'] = ts("View Details of this Contribution.");
738 // convert campaign_id to campaign title
739 if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
740 if ($value = $row['civicrm_contribution_campaign_id']) {
741 $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->activeCampaigns
[$value];
747 if (array_key_exists('civicrm_contribution_soft_credits', $row) &&
749 CRM_Utils_Array
::value('civicrm_contribution_contribution_or_soft', $rows[$rowNum]) &&
750 array_key_exists('civicrm_contribution_contribution_id', $row)
753 SELECT civicrm_contact_id, civicrm_contact_sort_name, civicrm_contribution_total_amount_sum, civicrm_contribution_currency
754 FROM civireport_contribution_detail_temp2
755 WHERE civicrm_contribution_contribution_id={$row['civicrm_contribution_contribution_id']}";
756 $dao = CRM_Core_DAO
::executeQuery($query);
758 $separator = ($this->_outputMode
!== 'csv') ?
"<br/>" : ' ';
759 while ($dao->fetch()) {
760 $url = CRM_Utils_System
::url("civicrm/contact/view", 'reset=1&cid=' .
761 $dao->civicrm_contact_id
);
762 $string = $string . ($string ?
$separator : '') .
763 "<a href='{$url}'>{$dao->civicrm_contact_sort_name}</a> " .
764 CRM_Utils_Money
::format($dao->civicrm_contribution_total_amount_sum
, $dao->civicrm_contribution_currency
);
766 $rows[$rowNum]['civicrm_contribution_soft_credits'] = $string;
769 if (array_key_exists('civicrm_contribution_soft_credit_for', $row) &&
771 CRM_Utils_Array
::value('civicrm_contribution_contribution_or_soft', $rows[$rowNum]) &&
772 array_key_exists('civicrm_contribution_contribution_id', $row)
775 SELECT civicrm_contact_id, civicrm_contact_sort_name
776 FROM civireport_contribution_detail_temp1
777 WHERE civicrm_contribution_contribution_id={$row['civicrm_contribution_contribution_id']}";
778 $dao = CRM_Core_DAO
::executeQuery($query);
780 while ($dao->fetch()) {
781 $url = CRM_Utils_System
::url("civicrm/contact/view", 'reset=1&cid=' .
782 $dao->civicrm_contact_id
);
784 "\n<a href='{$url}'>{$dao->civicrm_contact_sort_name}</a>";
786 $rows[$rowNum]['civicrm_contribution_soft_credit_for'] = $string;
789 // CRM-18312 - hide 'contribution_or_soft' column if unchecked.
790 if (!empty($this->noDisplayContributionOrSoftColumn
)) {
791 unset($rows[$rowNum]['civicrm_contribution_contribution_or_soft']);
792 unset($this->_columnHeaders
['civicrm_contribution_contribution_or_soft']);
795 //convert soft_credit_type_id into label
796 if (array_key_exists('civicrm_contribution_soft_soft_credit_type_id', $rows[$rowNum])) {
797 $rows[$rowNum]['civicrm_contribution_soft_soft_credit_type_id'] = CRM_Core_PseudoConstant
::getLabel(
798 'CRM_Contribute_BAO_ContributionSoft',
799 'soft_credit_type_id',
800 $row['civicrm_contribution_soft_soft_credit_type_id']
804 $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s) for this ') ?
TRUE : $entryFound;
806 // skip looking further in rows, if first row itself doesn't
807 // have the column we need
815 public function sectionTotals() {
817 // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
818 if (empty($this->_selectAliases
)) {
822 if (!empty($this->_sections
)) {
823 // build the query with no LIMIT clause
824 $select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', 'SELECT ', $this->_select
);
825 $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
827 // pull section aliases out of $this->_sections
828 $sectionAliases = array_keys($this->_sections
);
831 foreach (array_merge($sectionAliases, $this->_selectAliases
) as $alias) {
832 $ifnulls[] = "ifnull($alias, '') as $alias";
834 $this->_select
= "SELECT " . implode(", ", $ifnulls);
835 $this->_select
= CRM_Contact_BAO_Query
::appendAnyValueToSelect($ifnulls, $sectionAliases);
837 /* Group (un-limited) report by all aliases and get counts. This might
838 * be done more efficiently when the contents of $sql are known, ie. by
839 * overriding this method in the report class.
844 if (array_search("civicrm_contribution_total_amount_sum", $this->_selectAliases
) !==
847 $addtotals = ", sum(civicrm_contribution_total_amount_sum) as sumcontribs";
848 $showsumcontribs = TRUE;
851 $query = $this->_select
.
852 "$addtotals, count(*) as ct from civireport_contribution_detail_temp3 group by " .
853 implode(", ", $sectionAliases);
854 // initialize array of total counts
855 $sumcontribs = $totals = array();
856 $dao = CRM_Core_DAO
::executeQuery($query);
857 while ($dao->fetch()) {
859 // let $this->_alterDisplay translate any integer ids to human-readable values.
860 $rows[0] = $dao->toArray();
861 $this->alterDisplay($rows);
864 // add totals for all permutations of section values
867 $aliasCount = count($sectionAliases);
868 foreach ($sectionAliases as $alias) {
869 $values[] = $row[$alias];
870 $key = implode(CRM_Core_DAO
::VALUE_SEPARATOR
, $values);
871 if ($i == $aliasCount) {
872 // the last alias is the lowest-level section header; use count as-is
873 $totals[$key] = $dao->ct
;
874 if ($showsumcontribs) {
875 $sumcontribs[$key] = $dao->sumcontribs
;
879 // other aliases are higher level; roll count into their total
880 $totals[$key] = (array_key_exists($key, $totals)) ?
$totals[$key] +
$dao->ct
: $dao->ct
;
881 if ($showsumcontribs) {
882 $sumcontribs[$key] = array_key_exists($key, $sumcontribs) ?
$sumcontribs[$key] +
$dao->sumcontribs
: $dao->sumcontribs
;
887 if ($showsumcontribs) {
888 $totalandsum = array();
889 // ts exception to avoid having ts("%1 %2: %3")
890 $title = '%1 contributions / soft-credits: %2';
892 if (CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) ==
895 $title = '%1 contributions: %2';
897 elseif (CRM_Utils_Array
::value('contribution_or_soft_value', $this->_params
) ==
900 $title = '%1 soft-credits: %2';
902 foreach ($totals as $key => $total) {
903 $totalandsum[$key] = ts($title, array(
905 2 => CRM_Utils_Money
::format($sumcontribs[$key]),
908 $this->assign('sectionTotals', $totalandsum);
911 $this->assign('sectionTotals', $totals);
917 * Generate the from clause as it relates to the soft credits.
919 public function softCreditFrom() {
922 FROM civireport_contribution_detail_temp1 temp1_civireport
923 INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
924 ON temp1_civireport.civicrm_contribution_contribution_id = {$this->_aliases['civicrm_contribution']}.id
925 INNER JOIN civicrm_contribution_soft contribution_soft_civireport
926 ON contribution_soft_civireport.contribution_id = {$this->_aliases['civicrm_contribution']}.id
927 INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
928 ON {$this->_aliases['civicrm_contact']}.id = contribution_soft_civireport.contact_id
932 $this->appendAdditionalFromJoins();
936 * Append the joins that are required regardless of context.
938 public function appendAdditionalFromJoins() {
939 if (!empty($this->_params
['ordinality_value'])) {
941 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']}
942 ON {$this->_aliases['civicrm_contribution_ordinality']}.id = {$this->_aliases['civicrm_contribution']}.id";
944 $this->addPhoneFromClause();
946 $this->addAddressFromClause();
948 if ($this->_emailField
) {
950 LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
951 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND
952 {$this->_aliases['civicrm_email']}.is_primary = 1\n";
954 // include contribution note
955 if (!empty($this->_params
['fields']['contribution_note']) ||
956 !empty($this->_params
['note_value'])
959 LEFT JOIN civicrm_note {$this->_aliases['civicrm_note']}
960 ON ( {$this->_aliases['civicrm_note']}.entity_table = 'civicrm_contribution' AND
961 {$this->_aliases['civicrm_contribution']}.id = {$this->_aliases['civicrm_note']}.entity_id )";
963 //for contribution batches
964 if (!empty($this->_params
['fields']['batch_id']) ||
965 !empty($this->_params
['bid_value'])
968 LEFT JOIN civicrm_entity_financial_trxn eft
969 ON eft.entity_id = {$this->_aliases['civicrm_contribution']}.id AND
970 eft.entity_table = 'civicrm_contribution'
971 LEFT JOIN civicrm_entity_batch {$this->_aliases['civicrm_batch']}
972 ON ({$this->_aliases['civicrm_batch']}.entity_id = eft.financial_trxn_id
973 AND {$this->_aliases['civicrm_batch']}.entity_table = 'civicrm_financial_trxn')";
975 // for credit card type
976 $this->addFinancialTrxnFromClause();