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_Summary
extends CRM_Report_Form
{
35 protected $_charts = array(
37 'barChart' => 'Bar Chart',
38 'pieChart' => 'Pie Chart',
40 protected $_customGroupExtends = array('Contribution', 'Contact', 'Individual');
41 protected $_customGroupGroupBy = TRUE;
43 public $_drilldownReport = array('contribute/detail' => 'Link to Detail Report');
46 * To what frequency group-by a date column
50 protected $_groupByDateFreq = array(
54 'QUARTER' => 'Quarter',
56 'FISCALYEAR' => 'Fiscal Year',
60 * This report has been optimised for group filtering.
66 protected $groupFilterNotOptimised = FALSE;
71 public function __construct() {
73 // Check if CiviCampaign is a) enabled and b) has active campaigns
74 $config = CRM_Core_Config
::singleton();
75 $campaignEnabled = in_array("CiviCampaign", $config->enableComponents
);
76 if ($campaignEnabled) {
77 $getCampaigns = CRM_Campaign_BAO_Campaign
::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
78 $this->activeCampaigns
= $getCampaigns['campaigns'];
79 asort($this->activeCampaigns
);
82 $this->_columns
= array(
83 'civicrm_contact' => array(
84 'dao' => 'CRM_Contact_DAO_Contact',
85 'fields' => array_merge(
86 $this->getBasicContactFields(),
89 'title' => ts('Contact Name'),
94 'grouping' => 'contact-fields',
96 'id' => array('title' => ts('Contact ID')),
98 'title' => ts('Contact Name'),
102 'civicrm_email' => array(
103 'dao' => 'CRM_Core_DAO_Email',
106 'title' => ts('Email'),
110 'grouping' => 'contact-fields',
112 'civicrm_line_item' => array(
113 'dao' => 'CRM_Price_DAO_LineItem',
115 'civicrm_phone' => array(
116 'dao' => 'CRM_Core_DAO_Phone',
119 'title' => ts('Phone'),
123 'grouping' => 'contact-fields',
125 'civicrm_financial_type' => array(
126 'dao' => 'CRM_Financial_DAO_FinancialType',
127 'fields' => array('financial_type' => NULL),
128 'grouping' => 'contri-fields',
129 'group_bys' => array(
130 'financial_type' => array('title' => ts('Financial Type')),
133 'civicrm_contribution' => array(
134 'dao' => 'CRM_Contribute_DAO_Contribution',
135 //'bao' => 'CRM_Contribute_BAO_Contribution',
137 'contribution_status_id' => array(
138 'title' => ts('Contribution Status'),
140 'contribution_source' => array('title' => ts('Source')),
143 'no_display' => TRUE,
145 'total_amount' => array(
146 'title' => ts('Contribution Amount Stats'),
148 'statistics' => array(
149 'count' => ts('Contributions'),
150 'sum' => ts('Contribution Aggregate'),
151 'avg' => ts('Contribution Avg'),
154 'non_deductible_amount' => array(
155 'title' => ts('Non-deductible Amount'),
158 'grouping' => 'contri-fields',
160 'receive_date' => array('operatorType' => CRM_Report_Form
::OP_DATE
),
161 'contribution_status_id' => array(
162 'title' => ts('Contribution Status'),
163 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
164 'options' => CRM_Contribute_PseudoConstant
::contributionStatus(),
165 'default' => array(1),
166 'type' => CRM_Utils_Type
::T_INT
,
169 'title' => ts('Currency'),
170 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
171 'options' => CRM_Core_OptionGroup
::values('currencies_enabled'),
173 'type' => CRM_Utils_Type
::T_STRING
,
175 'financial_type_id' => array(
176 'title' => ts('Financial Type'),
177 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
178 'options' => CRM_Financial_BAO_FinancialType
::getAvailableFinancialTypes(),
179 'type' => CRM_Utils_Type
::T_INT
,
181 'contribution_page_id' => array(
182 'title' => ts('Contribution Page'),
183 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
184 'options' => CRM_Contribute_PseudoConstant
::contributionPage(),
185 'type' => CRM_Utils_Type
::T_INT
,
187 'total_amount' => array(
188 'title' => ts('Contribution Amount'),
190 'non_deductible_amount' => array(
191 'title' => ts('Non-deductible Amount'),
193 'total_sum' => array(
194 'title' => ts('Contribution Aggregate'),
195 'type' => CRM_Report_Form
::OP_INT
,
196 'dbAlias' => 'civicrm_contribution_total_amount_sum',
199 'total_count' => array(
200 'title' => ts('Contribution Count'),
201 'type' => CRM_Report_Form
::OP_INT
,
202 'dbAlias' => 'civicrm_contribution_total_amount_count',
205 'total_avg' => array(
206 'title' => ts('Contribution Avg'),
207 'type' => CRM_Report_Form
::OP_INT
,
208 'dbAlias' => 'civicrm_contribution_total_amount_avg',
212 'group_bys' => array(
213 'receive_date' => array(
218 'contribution_source' => NULL,
219 'contribution_status_id' => array(
220 'title' => ts('Contribution Status'),
221 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
222 'options' => CRM_Contribute_PseudoConstant
::contributionStatus(),
223 'default' => array(1),
224 'type' => CRM_Utils_Type
::T_INT
,
228 'civicrm_financial_trxn' => array(
229 'dao' => 'CRM_Financial_DAO_FinancialTrxn',
231 'card_type_id' => array(
232 'title' => ts('Credit Card Type'),
233 'dbAlias' => 'GROUP_CONCAT(financial_trxn_civireport.card_type_id SEPARATOR ",")',
237 'card_type_id' => array(
238 'title' => ts('Credit Card Type'),
239 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
240 'options' => CRM_Financial_DAO_FinancialTrxn
::buildOptions('card_type_id'),
242 'type' => CRM_Utils_Type
::T_STRING
,
246 'civicrm_batch' => array(
247 'dao' => 'CRM_Batch_DAO_EntityBatch',
248 'grouping' => 'contri-fields',
251 'name' => 'batch_id',
252 'title' => ts('Batch Title'),
253 'dbAlias' => 'GROUP_CONCAT(DISTINCT batch_civireport.batch_id
254 ORDER BY batch_civireport.batch_id SEPARATOR ",")',
259 'title' => ts('Batch Title'),
260 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
261 'options' => CRM_Batch_BAO_Batch
::getBatches(),
262 'type' => CRM_Utils_Type
::T_INT
,
265 'group_bys' => array(
266 'batch_id' => array('title' => ts('Batch Title')),
269 'civicrm_contribution_soft' => array(
270 'dao' => 'CRM_Contribute_DAO_ContributionSoft',
272 'soft_amount' => array(
273 'title' => ts('Soft Credit Amount Stats'),
275 'statistics' => array(
276 'count' => ts('Soft Credits'),
277 'sum' => ts('Soft Credit Aggregate'),
278 'avg' => ts('Soft Credit Avg'),
282 'grouping' => 'contri-fields',
285 'title' => ts('Soft Credit Amount'),
287 'soft_credit_type_id' => array(
288 'title' => ts('Soft Credit Type'),
289 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
290 'options' => CRM_Core_OptionGroup
::values('soft_credit_type'),
292 'type' => CRM_Utils_Type
::T_STRING
,
295 'title' => ts('Soft Credit Aggregate'),
296 'type' => CRM_Report_Form
::OP_INT
,
297 'dbAlias' => 'civicrm_contribution_soft_soft_amount_sum',
300 'soft_count' => array(
301 'title' => ts('Soft Credits Count'),
302 'type' => CRM_Report_Form
::OP_INT
,
303 'dbAlias' => 'civicrm_contribution_soft_soft_amount_count',
307 'title' => ts('Soft Credit Avg'),
308 'type' => CRM_Report_Form
::OP_INT
,
309 'dbAlias' => 'civicrm_contribution_soft_soft_amount_avg',
314 ) +
$this->addAddressFields();
316 // If we have a campaign, build out the relevant elements
317 if ($campaignEnabled && !empty($this->activeCampaigns
)) {
318 $this->_columns
['civicrm_contribution']['fields']['campaign_id'] = array(
319 'title' => ts('Campaign'),
320 'default' => 'false',
322 $this->_columns
['civicrm_contribution']['filters']['campaign_id'] = array(
323 'title' => ts('Campaign'),
324 'type' => CRM_Utils_Type
::T_INT
,
325 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
326 'options' => $this->activeCampaigns
,
328 $this->_columns
['civicrm_contribution']['group_bys']['campaign_id'] = array('title' => ts('Campaign'));
331 $this->_tagFilter
= TRUE;
332 $this->_groupFilter
= TRUE;
333 $this->_currencyColumn
= 'civicrm_contribution_currency';
334 parent
::__construct();
340 public function select() {
342 $this->_columnHeaders
= array();
343 foreach ($this->_columns
as $tableName => $table) {
344 if (array_key_exists('group_bys', $table)) {
345 foreach ($table['group_bys'] as $fieldName => $field) {
346 if (!empty($this->_params
['group_bys'][$fieldName])) {
347 switch (CRM_Utils_Array
::value($fieldName, $this->_params
['group_bys_freq'])) {
349 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start";
350 $select[] = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
351 $select[] = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
352 $field['title'] = ts('Week Beginning');
356 $select[] = "MAKEDATE(YEAR({$field['dbAlias']}), 1) AS {$tableName}_{$fieldName}_start";
357 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
358 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
359 $field['title'] = ts('Year Beginning');
363 $config = CRM_Core_Config
::singleton();
364 $fy = $config->fiscalYearStart
;
365 $fiscal = self
::fiscalYearOffset($field['dbAlias']);
367 $select[] = "DATE_ADD(MAKEDATE({$fiscal}, 1), INTERVAL ({$fy{'M'}})-1 MONTH) AS {$tableName}_{$fieldName}_start";
368 $select[] = "{$fiscal} AS {$tableName}_{$fieldName}_subtotal";
369 $select[] = "{$fiscal} AS {$tableName}_{$fieldName}_interval";
370 $field['title'] = ts('Fiscal Year Beginning');
374 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start";
375 $select[] = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
376 $select[] = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
377 $field['title'] = ts('Month Beginning');
381 $select[] = "DATE({$field['dbAlias']}) as {$tableName}_{$fieldName}_start";
382 $field['title'] = ts('Date');
386 $select[] = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start";
387 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
388 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
389 $field['title'] = 'Quarter';
392 if (!empty($this->_params
['group_bys_freq'][$fieldName])) {
393 $this->_interval
= $this->_params
['group_bys_freq'][$fieldName];
394 $this->_columnHeaders
["{$tableName}_{$fieldName}_start"]['title'] = $field['title'];
395 $this->_columnHeaders
["{$tableName}_{$fieldName}_start"]['type'] = $field['type'];
396 $this->_columnHeaders
["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params
['group_bys_freq'][$fieldName];
398 // just to make sure these values are transferred to rows.
399 // since we need that for calculation purpose,
400 // e.g making subtotals look nicer or graphs
401 $this->_columnHeaders
["{$tableName}_{$fieldName}_interval"] = array('no_display' => TRUE);
402 $this->_columnHeaders
["{$tableName}_{$fieldName}_subtotal"] = array('no_display' => TRUE);
408 if (array_key_exists('fields', $table)) {
409 foreach ($table['fields'] as $fieldName => $field) {
410 if (!empty($field['required']) ||
411 !empty($this->_params
['fields'][$fieldName])
413 // only include statistics columns if set
414 if (!empty($field['statistics'])) {
415 foreach ($field['statistics'] as $stat => $label) {
416 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
417 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
418 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
419 switch (strtolower($stat)) {
421 $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
425 $select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
426 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type
::T_INT
;
430 $select[] = "ROUND(AVG({$field['dbAlias']}),2) as {$tableName}_{$fieldName}_{$stat}";
436 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
437 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array
::value('type', $field);
438 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array
::value('title', $field);
445 $this->_selectClauses
= $select;
446 $this->_select
= "SELECT " . implode(', ', $select) . " ";
452 * @param array $fields
453 * @param array $files
454 * @param CRM_Report_Form_Contribute_Summary $self
458 public static function formRule($fields, $files, $self) {
459 // Check for searching combination of display columns and
461 $ignoreFields = array('total_amount', 'sort_name');
462 $errors = $self->customDataFormRule($fields, $ignoreFields);
464 if (empty($fields['fields']['total_amount'])) {
470 if (!empty($fields[$val])) {
471 $errors[$val] = ts("Please select the Amount Statistics");
482 * @param string $entity
484 * @todo fix function signature to match parent. Remove hacky passing of $entity
485 * to acheive unclear results.
487 public function from($entity = NULL) {
488 $softCreditJoinType = "LEFT";
489 if (!empty($this->_params
['fields']['soft_amount']) &&
490 empty($this->_params
['fields']['total_amount'])
492 // if its only soft credit stats, use inner join
493 $softCreditJoinType = "INNER";
496 $softCreditJoin = "{$softCreditJoinType} JOIN civicrm_contribution_soft {$this->_aliases['civicrm_contribution_soft']}
497 ON {$this->_aliases['civicrm_contribution_soft']}.contribution_id = {$this->_aliases['civicrm_contribution']}.id";
498 if ($entity == 'contribution' ||
empty($this->_params
['fields']['soft_amount'])) {
499 $softCreditJoin .= " AND {$this->_aliases['civicrm_contribution_soft']}.id = (SELECT MIN(id) FROM civicrm_contribution_soft cs WHERE cs.contribution_id = {$this->_aliases['civicrm_contribution']}.id) ";
502 $this->setFromBase('civicrm_contact');
505 INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
506 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND
507 {$this->_aliases['civicrm_contribution']}.is_test = 0
509 LEFT JOIN civicrm_financial_type {$this->_aliases['civicrm_financial_type']}
510 ON {$this->_aliases['civicrm_contribution']}.financial_type_id ={$this->_aliases['civicrm_financial_type']}.id
513 $this->joinAddressFromContact();
514 $this->joinPhoneFromContact();
515 $this->joinEmailFromContact();
517 //for contribution batches
518 if ($this->isTableSelected('civicrm_batch')) {
520 LEFT JOIN civicrm_entity_financial_trxn eft
521 ON eft.entity_id = {$this->_aliases['civicrm_contribution']}.id AND
522 eft.entity_table = 'civicrm_contribution'
523 LEFT JOIN civicrm_entity_batch {$this->_aliases['civicrm_batch']}
524 ON ({$this->_aliases['civicrm_batch']}.entity_id = eft.financial_trxn_id
525 AND {$this->_aliases['civicrm_batch']}.entity_table = 'civicrm_financial_trxn')";
528 // for credit card type
529 if ($this->isTableSelected('civicrm_financial_trxn')) {
531 LEFT JOIN civicrm_entity_financial_trxn eftcc
532 ON ({$this->_aliases['civicrm_contribution']}.id = eftcc.entity_id AND
533 eftcc.entity_table = 'civicrm_contribution')
534 LEFT JOIN civicrm_financial_trxn {$this->_aliases['civicrm_financial_trxn']}
535 ON {$this->_aliases['civicrm_financial_trxn']}.id = eftcc.financial_trxn_id";
540 * Set group by clause.
542 public function groupBy() {
543 $this->_groupBy
= "";
544 $groupByColumns = array();
546 if (!empty($this->_params
['group_bys']) &&
547 is_array($this->_params
['group_bys'])
549 foreach ($this->_columns
as $tableName => $table) {
550 if (array_key_exists('group_bys', $table)) {
551 foreach ($table['group_bys'] as $fieldName => $field) {
552 if (!empty($this->_params
['group_bys'][$fieldName])) {
553 if (!empty($field['chart'])) {
554 $this->assign('chartSupported', TRUE);
557 if (!empty($table['group_bys'][$fieldName]['frequency']) &&
558 !empty($this->_params
['group_bys_freq'][$fieldName])
561 $append = "YEAR({$field['dbAlias']});;";
562 if (in_array(strtolower($this->_params
['group_bys_freq'][$fieldName]),
567 if ($this->_params
['group_bys_freq'][$fieldName] == 'FISCALYEAR') {
568 $groupByColumns[] = self
::fiscalYearOffset($field['dbAlias']);
571 $groupByColumns[] = "$append {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
576 $groupByColumns[] = $field['dbAlias'];
583 if (!empty($this->_statFields
) &&
584 (($append && count($groupByColumns) <= 1) ||
(!$append)) &&
587 $this->_rollup
= " WITH ROLLUP";
590 foreach ($groupByColumns as $key => $val) {
591 if (strpos($val, ';;') !== FALSE) {
592 $groupBy = array_merge($groupBy, explode(';;', $val));
595 $groupBy[] = $groupByColumns[$key];
598 $this->_groupBy
= "GROUP BY " . implode(', ', $groupBy);
601 $groupBy = "{$this->_aliases['civicrm_contact']}.id";
602 $this->_groupBy
= "GROUP BY {$this->_aliases['civicrm_contact']}.id";
604 $this->_groupBy
.= $this->_rollup
;
605 // append select with ANY_VALUE() keyword
606 $this->_select
= CRM_Contact_BAO_Query
::appendAnyValueToSelect($this->_selectClauses
, $groupBy);
610 * Store having clauses as an array.
612 public function storeWhereHavingClauseArray() {
613 parent
::storeWhereHavingClauseArray();
614 if (empty($this->_params
['fields']['soft_amount']) &&
615 !empty($this->_havingClauses
)
617 foreach ($this->_havingClauses
as $key => $havingClause) {
618 if (stristr($havingClause, 'soft_soft')) {
619 unset($this->_havingClauses
[$key]);
632 public function statistics(&$rows) {
633 $statistics = parent
::statistics($rows);
635 $softCredit = CRM_Utils_Array
::value('soft_amount', $this->_params
['fields']);
636 $onlySoftCredit = $softCredit && !CRM_Utils_Array
::value('total_amount', $this->_params
['fields']);
637 $group = "\nGROUP BY {$this->_aliases['civicrm_contribution']}.currency";
639 $this->from('contribution');
640 $this->customDataFrom();
643 COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as civicrm_contribution_total_amount_count,
644 SUM({$this->_aliases['civicrm_contribution']}.total_amount ) as civicrm_contribution_total_amount_sum,
645 ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as civicrm_contribution_total_amount_avg,
646 {$this->_aliases['civicrm_contribution']}.currency as currency
647 {$this->_from} {$this->_where}";
652 COUNT({$this->_aliases['civicrm_contribution_soft']}.amount ) as civicrm_contribution_soft_soft_amount_count,
653 SUM({$this->_aliases['civicrm_contribution_soft']}.amount ) as civicrm_contribution_soft_soft_amount_sum,
654 ROUND(AVG({$this->_aliases['civicrm_contribution_soft']}.amount), 2) as civicrm_contribution_soft_soft_amount_avg";
655 $contriQuery = "{$select}, {$contriQuery}";
656 $softSQL = "SELECT {$select}, {$this->_aliases['civicrm_contribution']}.currency as currency
657 {$this->_from} {$this->_where} {$group} {$this->_having}";
660 $contriSQL = "SELECT {$contriQuery} {$group} {$this->_having}";
661 $contriDAO = CRM_Core_DAO
::executeQuery($contriSQL);
663 $totalAmount = $average = $mode = $median = $softTotalAmount = $softAverage = array();
664 $count = $softCount = 0;
665 while ($contriDAO->fetch()) {
667 = CRM_Utils_Money
::format($contriDAO->civicrm_contribution_total_amount_sum
, $contriDAO->currency
) .
668 " (" . $contriDAO->civicrm_contribution_total_amount_count
. ")";
669 $average[] = CRM_Utils_Money
::format($contriDAO->civicrm_contribution_total_amount_avg
, $contriDAO->currency
);
670 $count +
= $contriDAO->civicrm_contribution_total_amount_count
;
673 $groupBy = "\n{$group}, {$this->_aliases['civicrm_contribution']}.total_amount";
674 $orderBy = "\nORDER BY civicrm_contribution_total_amount_count DESC";
675 $modeSQL = "SELECT MAX(civicrm_contribution_total_amount_count) as civicrm_contribution_total_amount_count,
676 SUBSTRING_INDEX(GROUP_CONCAT(amount ORDER BY mode.civicrm_contribution_total_amount_count DESC SEPARATOR ';'), ';', 1) as amount,
678 FROM (SELECT {$this->_aliases['civicrm_contribution']}.total_amount as amount,
679 {$contriQuery} {$groupBy} {$orderBy}) as mode GROUP BY currency";
681 $mode = CRM_Contribute_BAO_Contribution
::computeStats('mode', $modeSQL);
683 $medianSQL = "{$this->_from} {$this->_where}";
684 $median = CRM_Contribute_BAO_Contribution
::computeStats('median', $medianSQL, $this->_aliases
['civicrm_contribution']);
687 $softDAO = CRM_Core_DAO
::executeQuery($softSQL);
688 while ($softDAO->fetch()) {
690 = CRM_Utils_Money
::format($softDAO->civicrm_contribution_soft_soft_amount_sum
, $softDAO->currency
) .
691 " (" . $softDAO->civicrm_contribution_soft_soft_amount_count
. ")";
692 $softAverage[] = CRM_Utils_Money
::format($softDAO->civicrm_contribution_soft_soft_amount_avg
, $softDAO->currency
);
693 $softCount +
= $softDAO->civicrm_contribution_soft_soft_amount_count
;
697 if (!$onlySoftCredit) {
698 $statistics['counts']['amount'] = array(
699 'title' => ts('Total Amount'),
700 'value' => implode(', ', $totalAmount),
701 'type' => CRM_Utils_Type
::T_STRING
,
703 $statistics['counts']['count'] = array(
704 'title' => ts('Total Contributions'),
707 $statistics['counts']['avg'] = array(
708 'title' => ts('Average'),
709 'value' => implode(', ', $average),
710 'type' => CRM_Utils_Type
::T_STRING
,
712 $statistics['counts']['mode'] = array(
713 'title' => ts('Mode'),
714 'value' => implode(', ', $mode),
715 'type' => CRM_Utils_Type
::T_STRING
,
717 $statistics['counts']['median'] = array(
718 'title' => ts('Median'),
719 'value' => implode(', ', $median),
720 'type' => CRM_Utils_Type
::T_STRING
,
724 $statistics['counts']['soft_amount'] = array(
725 'title' => ts('Total Soft Credit Amount'),
726 'value' => implode(', ', $softTotalAmount),
727 'type' => CRM_Utils_Type
::T_STRING
,
729 $statistics['counts']['soft_count'] = array(
730 'title' => ts('Total Soft Credits'),
731 'value' => $softCount,
733 $statistics['counts']['soft_avg'] = array(
734 'title' => ts('Average Soft Credit'),
735 'value' => implode(', ', $softAverage),
736 'type' => CRM_Utils_Type
::T_STRING
,
743 * Post process function.
745 public function postProcess() {
746 $this->buildACLClause($this->_aliases
['civicrm_contact']);
747 parent
::postProcess();
751 * Build table rows for output.
756 public function buildRows($sql, &$rows) {
757 $dao = CRM_Core_DAO
::executeQuery($sql);
758 if (!is_array($rows)) {
762 // use this method to modify $this->_columnHeaders
763 $this->modifyColumnHeaders();
764 $contriRows = array();
765 $unselectedSectionColumns = $this->unselectedSectionColumns();
767 //CRM-16338 if both soft-credit and contribution are enabled then process the contribution's
768 //total amount's average, count and sum separately and add it to the respective result list
769 $softCredit = (!empty($this->_params
['fields']['soft_amount']) && !empty($this->_params
['fields']['total_amount'])) ?
TRUE : FALSE;
771 $this->from('contribution');
772 $contriSQL = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
773 $contriDAO = CRM_Core_DAO
::executeQuery($contriSQL);
774 $contriFields = array(
775 'civicrm_contribution_total_amount_sum',
776 'civicrm_contribution_total_amount_avg',
777 'civicrm_contribution_total_amount_count');
778 $contriRows = array();
779 while ($contriDAO->fetch()) {
780 $contriRow = array();
781 foreach ($contriFields as $column) {
782 $contriRow[$column] = $contriDAO->$column;
784 $contriRows[] = $contriRow;
789 while ($dao->fetch()) {
791 foreach ($this->_columnHeaders
as $key => $value) {
792 if ($softCredit && array_key_exists($key, $contriRows[$count])) {
793 $row[$key] = $contriRows[$count][$key];
795 elseif (property_exists($dao, $key)) {
796 $row[$key] = $dao->$key;
800 // section headers not selected for display need to be added to row
801 foreach ($unselectedSectionColumns as $key => $values) {
802 if (property_exists($dao, $key)) {
803 $row[$key] = $dao->$key;
818 public function buildChart(&$rows) {
819 $graphRows = array();
821 if (!empty($this->_params
['charts'])) {
822 if (!empty($this->_params
['group_bys']['receive_date'])) {
824 $contrib = !empty($this->_params
['fields']['total_amount']) ?
TRUE : FALSE;
825 $softContrib = !empty($this->_params
['fields']['soft_amount']) ?
TRUE : FALSE;
827 foreach ($rows as $key => $row) {
828 if ($row['civicrm_contribution_receive_date_subtotal']) {
829 $graphRows['receive_date'][] = $row['civicrm_contribution_receive_date_start'];
830 $graphRows[$this->_interval
][] = $row['civicrm_contribution_receive_date_interval'];
831 if ($softContrib && $contrib) {
832 // both contri & soft contri stats are present
833 $graphRows['multiValue'][0][] = $row['civicrm_contribution_total_amount_sum'];
834 $graphRows['multiValue'][1][] = $row['civicrm_contribution_soft_soft_amount_sum'];
836 elseif ($softContrib) {
837 // only soft contributions
838 $graphRows['multiValue'][0][] = $row['civicrm_contribution_soft_soft_amount_sum'];
841 // only contributions
842 $graphRows['multiValue'][0][] = $row['civicrm_contribution_total_amount_sum'];
847 if ($softContrib && $contrib) {
848 $graphRows['barKeys'][0] = ts('Contributions');
849 $graphRows['barKeys'][1] = ts('Soft Credits');
850 $graphRows['legend'] = ts('Contributions and Soft Credits');
852 elseif ($softContrib) {
853 $graphRows['legend'] = ts('Soft Credits');
857 $config = CRM_Core_Config
::Singleton();
858 $graphRows['xname'] = $this->_interval
;
859 $graphRows['yname'] = "Amount ({$config->defaultCurrency})";
860 CRM_Utils_OpenFlashChart
::chart($graphRows, $this->_params
['charts'], $this->_interval
);
861 $this->assign('chartType', $this->_params
['charts']);
867 * Alter display of rows.
869 * Iterate through the rows retrieved via SQL and make changes for display purposes,
870 * such as rendering contacts as links.
873 * Rows generated by SQL, with an array for each row.
875 public function alterDisplay(&$rows) {
877 $contributionStatus = CRM_Contribute_PseudoConstant
::contributionStatus();
879 foreach ($rows as $rowNum => $row) {
880 // make count columns point to detail report
881 if (!empty($this->_params
['group_bys']['receive_date']) &&
882 !empty($row['civicrm_contribution_receive_date_start']) &&
883 CRM_Utils_Array
::value('civicrm_contribution_receive_date_start', $row) &&
884 !empty($row['civicrm_contribution_receive_date_subtotal'])
887 $dateStart = CRM_Utils_Date
::customFormat($row['civicrm_contribution_receive_date_start'], '%Y%m%d');
888 $endDate = new DateTime($dateStart);
891 list($dateEnd['Y'], $dateEnd['M'], $dateEnd['d']) = explode(':', $endDate->format('Y:m:d'));
893 switch (strtolower($this->_params
['group_bys_freq']['receive_date'])) {
895 $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'] +
1,
896 $dateEnd['d'] - 1, $dateEnd['Y']
901 $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'],
902 $dateEnd['d'] - 1, $dateEnd['Y'] +
1
907 $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'],
908 $dateEnd['d'] - 1, $dateEnd['Y'] +
1
913 $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'],
914 $dateEnd['d'] +
6, $dateEnd['Y']
919 $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'] +
3,
920 $dateEnd['d'] - 1, $dateEnd['Y']
924 $url = CRM_Report_Utils_Report
::getNextUrl('contribute/detail',
925 "reset=1&force=1&receive_date_from={$dateStart}&receive_date_to={$dateEnd}",
928 $this->_drilldownReport
930 $rows[$rowNum]['civicrm_contribution_receive_date_start_link'] = $url;
931 $rows[$rowNum]['civicrm_contribution_receive_date_start_hover'] = ts('List all contribution(s) for this date unit.');
935 // make subtotals look nicer
936 if (array_key_exists('civicrm_contribution_receive_date_subtotal', $row) &&
937 !$row['civicrm_contribution_receive_date_subtotal']
939 $this->fixSubTotalDisplay($rows[$rowNum], $this->_statFields
);
943 // convert display name to links
944 if (array_key_exists('civicrm_contact_sort_name', $row) &&
945 array_key_exists('civicrm_contact_id', $row)
947 $url = CRM_Report_Utils_Report
::getNextUrl('contribute/detail',
948 'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'],
949 $this->_absoluteUrl
, $this->_id
, $this->_drilldownReport
951 $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
952 $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("Lists detailed contribution(s) for this record.");
956 // convert contribution status id to status name
957 if ($value = CRM_Utils_Array
::value('civicrm_contribution_contribution_status_id', $row)) {
958 $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value];
962 if (!empty($row['civicrm_financial_trxn_card_type_id'])) {
963 $rows[$rowNum]['civicrm_financial_trxn_card_type_id'] = $this->getLabels($row['civicrm_financial_trxn_card_type_id'], 'CRM_Financial_DAO_FinancialTrxn', 'card_type_id');
967 // If using campaigns, convert campaign_id to campaign title
968 if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
969 if ($value = $row['civicrm_contribution_campaign_id']) {
970 $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->activeCampaigns
[$value];
975 // convert batch id to batch title
976 if (!empty($row['civicrm_batch_batch_id']) && !in_array('Subtotal', $rows[$rowNum])) {
977 $rows[$rowNum]['civicrm_batch_batch_id'] = $this->getLabels($row['civicrm_batch_batch_id'], 'CRM_Batch_BAO_EntityBatch', 'batch_id');
981 $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s) for this ') ?
TRUE : $entryFound;
982 $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s) for this ') ?
TRUE : $entryFound;
984 // skip looking further in rows, if first row itself doesn't
985 // have the column we need