3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2016 |
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-2016
33 class CRM_Report_Form_Contribute_Summary
extends CRM_Report_Form
{
34 protected $_addressField = FALSE;
36 protected $_charts = array(
38 'barChart' => 'Bar Chart',
39 'pieChart' => 'Pie Chart',
41 protected $_customGroupExtends = array('Contribution');
42 protected $_customGroupGroupBy = TRUE;
44 public $_drilldownReport = array('contribute/detail' => 'Link to Detail Report');
47 * To what frequency group-by a date column
51 protected $_groupByDateFreq = array(
55 'QUARTER' => 'Quarter',
57 'FISCALYEAR' => 'Fiscal Year',
61 * This report has been optimised for group filtering.
67 protected $groupFilterNotOptimised = FALSE;
72 public function __construct() {
74 // Check if CiviCampaign is a) enabled and b) has active campaigns
75 $config = CRM_Core_Config
::singleton();
76 $campaignEnabled = in_array("CiviCampaign", $config->enableComponents
);
77 if ($campaignEnabled) {
78 $getCampaigns = CRM_Campaign_BAO_Campaign
::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
79 $this->activeCampaigns
= $getCampaigns['campaigns'];
80 asort($this->activeCampaigns
);
83 $this->_columns
= array(
84 'civicrm_contact' => array(
85 'dao' => 'CRM_Contact_DAO_Contact',
86 'fields' => array_merge(
87 $this->getBasicContactFields(),
90 'title' => ts('Contact Name'),
95 'grouping' => 'contact-fields',
97 'id' => array('title' => ts('Contact ID')),
99 'title' => ts('Contact Name'),
103 'civicrm_email' => array(
104 'dao' => 'CRM_Core_DAO_Email',
107 'title' => ts('Email'),
111 'grouping' => 'contact-fields',
113 'civicrm_line_item' => array(
114 'dao' => 'CRM_Price_DAO_LineItem',
116 'civicrm_phone' => array(
117 'dao' => 'CRM_Core_DAO_Phone',
120 'title' => ts('Phone'),
124 'grouping' => 'contact-fields',
126 'civicrm_financial_type' => array(
127 'dao' => 'CRM_Financial_DAO_FinancialType',
128 'fields' => array('financial_type' => NULL),
129 'grouping' => 'contri-fields',
130 'group_bys' => array(
131 'financial_type' => array('title' => ts('Financial Type')),
134 'civicrm_contribution' => array(
135 'dao' => 'CRM_Contribute_DAO_Contribution',
136 //'bao' => 'CRM_Contribute_BAO_Contribution',
138 'contribution_status_id' => array(
139 'title' => ts('Contribution Status'),
141 'contribution_source' => array('title' => ts('Source')),
144 'no_display' => TRUE,
146 'total_amount' => array(
147 'title' => ts('Contribution Amount Stats'),
149 'statistics' => array(
150 'count' => ts('Contributions'),
151 'sum' => ts('Contribution Aggregate'),
152 'avg' => ts('Contribution Avg'),
156 'grouping' => 'contri-fields',
158 'receive_date' => array('operatorType' => CRM_Report_Form
::OP_DATE
),
159 'contribution_status_id' => array(
160 'title' => ts('Contribution Status'),
161 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
162 'options' => CRM_Contribute_PseudoConstant
::contributionStatus(),
163 'default' => array(1),
164 'type' => CRM_Utils_Type
::T_INT
,
167 'title' => ts('Currency'),
168 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
169 'options' => CRM_Core_OptionGroup
::values('currencies_enabled'),
171 'type' => CRM_Utils_Type
::T_STRING
,
173 'financial_type_id' => array(
174 'title' => ts('Financial Type'),
175 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
176 'options' => CRM_Financial_BAO_FinancialType
::getAvailableFinancialTypes(),
177 'type' => CRM_Utils_Type
::T_INT
,
179 'contribution_page_id' => array(
180 'title' => ts('Contribution Page'),
181 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
182 'options' => CRM_Contribute_PseudoConstant
::contributionPage(),
183 'type' => CRM_Utils_Type
::T_INT
,
185 'total_amount' => array(
186 'title' => ts('Contribution Amount'),
188 'total_sum' => array(
189 'title' => ts('Contribution Aggregate'),
190 'type' => CRM_Report_Form
::OP_INT
,
191 'dbAlias' => 'civicrm_contribution_total_amount_sum',
194 'total_count' => array(
195 'title' => ts('Contribution Count'),
196 'type' => CRM_Report_Form
::OP_INT
,
197 'dbAlias' => 'civicrm_contribution_total_amount_count',
200 'total_avg' => array(
201 'title' => ts('Contribution Avg'),
202 'type' => CRM_Report_Form
::OP_INT
,
203 'dbAlias' => 'civicrm_contribution_total_amount_avg',
207 'group_bys' => array(
208 'receive_date' => array(
213 'contribution_source' => NULL,
214 'contribution_status_id' => array(
215 'title' => ts('Contribution Status'),
216 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
217 'options' => CRM_Contribute_PseudoConstant
::contributionStatus(),
218 'default' => array(1),
219 'type' => CRM_Utils_Type
::T_INT
,
223 'civicrm_batch' => array(
224 'dao' => 'CRM_Batch_DAO_EntityBatch',
225 'grouping' => 'contri-fields',
228 'title' => ts('Batch Title'),
229 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
230 'options' => CRM_Batch_BAO_Batch
::getBatches(),
231 'type' => CRM_Utils_Type
::T_INT
,
235 'civicrm_contribution_soft' => array(
236 'dao' => 'CRM_Contribute_DAO_ContributionSoft',
238 'soft_amount' => array(
239 'title' => ts('Soft Credit Amount Stats'),
241 'statistics' => array(
242 'count' => ts('Soft Credits'),
243 'sum' => ts('Soft Credit Aggregate'),
244 'avg' => ts('Soft Credit Avg'),
248 'grouping' => 'contri-fields',
251 'title' => ts('Soft Credit Amount'),
253 'soft_credit_type_id' => array(
254 'title' => ts('Soft Credit Type'),
255 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
256 'options' => CRM_Core_OptionGroup
::values('soft_credit_type'),
258 'type' => CRM_Utils_Type
::T_STRING
,
261 'title' => ts('Soft Credit Aggregate'),
262 'type' => CRM_Report_Form
::OP_INT
,
263 'dbAlias' => 'civicrm_contribution_soft_soft_amount_sum',
266 'soft_count' => array(
267 'title' => ts('Soft Credits Count'),
268 'type' => CRM_Report_Form
::OP_INT
,
269 'dbAlias' => 'civicrm_contribution_soft_soft_amount_count',
273 'title' => ts('Soft Credit Avg'),
274 'type' => CRM_Report_Form
::OP_INT
,
275 'dbAlias' => 'civicrm_contribution_soft_soft_amount_avg',
280 ) +
$this->addAddressFields();
282 // If we have a campaign, build out the relevant elements
283 if ($campaignEnabled && !empty($this->activeCampaigns
)) {
284 $this->_columns
['civicrm_contribution']['fields']['campaign_id'] = array(
285 'title' => ts('Campaign'),
286 'default' => 'false',
288 $this->_columns
['civicrm_contribution']['filters']['campaign_id'] = array(
289 'title' => ts('Campaign'),
290 'type' => CRM_Utils_Type
::T_INT
,
291 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
292 'options' => $this->activeCampaigns
,
294 $this->_columns
['civicrm_contribution']['group_bys']['campaign_id'] = array('title' => ts('Campaign'));
297 $this->_tagFilter
= TRUE;
298 $this->_groupFilter
= TRUE;
299 $this->_currencyColumn
= 'civicrm_contribution_currency';
300 parent
::__construct();
306 public function select() {
308 $this->_columnHeaders
= array();
309 foreach ($this->_columns
as $tableName => $table) {
310 if (array_key_exists('group_bys', $table)) {
311 foreach ($table['group_bys'] as $fieldName => $field) {
312 if ($tableName == 'civicrm_address') {
313 $this->_addressField
= TRUE;
315 if (!empty($this->_params
['group_bys'][$fieldName])) {
316 switch (CRM_Utils_Array
::value($fieldName, $this->_params
['group_bys_freq'])) {
318 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start";
319 $select[] = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
320 $select[] = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
321 $field['title'] = ts('Week Beginning');
325 $select[] = "MAKEDATE(YEAR({$field['dbAlias']}), 1) AS {$tableName}_{$fieldName}_start";
326 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
327 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
328 $field['title'] = ts('Year Beginning');
332 $config = CRM_Core_Config
::singleton();
333 $fy = $config->fiscalYearStart
;
334 $fiscal = self
::fiscalYearOffset($field['dbAlias']);
336 $select[] = "DATE_ADD(MAKEDATE({$fiscal}, 1), INTERVAL ({$fy{'M'}})-1 MONTH) AS {$tableName}_{$fieldName}_start";
337 $select[] = "{$fiscal} AS {$tableName}_{$fieldName}_subtotal";
338 $select[] = "{$fiscal} AS {$tableName}_{$fieldName}_interval";
339 $field['title'] = ts('Fiscal Year Beginning');
343 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start";
344 $select[] = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
345 $select[] = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
346 $field['title'] = ts('Month Beginning');
350 $select[] = "DATE({$field['dbAlias']}) as {$tableName}_{$fieldName}_start";
351 $field['title'] = ts('Date');
355 $select[] = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start";
356 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
357 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
358 $field['title'] = 'Quarter';
361 if (!empty($this->_params
['group_bys_freq'][$fieldName])) {
362 $this->_interval
= $this->_params
['group_bys_freq'][$fieldName];
363 $this->_columnHeaders
["{$tableName}_{$fieldName}_start"]['title'] = $field['title'];
364 $this->_columnHeaders
["{$tableName}_{$fieldName}_start"]['type'] = $field['type'];
365 $this->_columnHeaders
["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params
['group_bys_freq'][$fieldName];
367 // just to make sure these values are transferred to rows.
368 // since we need that for calculation purpose,
369 // e.g making subtotals look nicer or graphs
370 $this->_columnHeaders
["{$tableName}_{$fieldName}_interval"] = array('no_display' => TRUE);
371 $this->_columnHeaders
["{$tableName}_{$fieldName}_subtotal"] = array('no_display' => TRUE);
377 if (array_key_exists('fields', $table)) {
378 foreach ($table['fields'] as $fieldName => $field) {
379 if ($tableName == 'civicrm_address') {
380 $this->_addressField
= TRUE;
382 if (!empty($field['required']) ||
383 !empty($this->_params
['fields'][$fieldName])
385 // only include statistics columns if set
386 if (!empty($field['statistics'])) {
387 foreach ($field['statistics'] as $stat => $label) {
388 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
389 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
390 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
391 switch (strtolower($stat)) {
393 $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
397 $select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
398 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type
::T_INT
;
402 $select[] = "ROUND(AVG({$field['dbAlias']}),2) as {$tableName}_{$fieldName}_{$stat}";
408 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
409 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array
::value('type', $field);
410 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array
::value('title', $field);
417 $this->_selectClauses
= $select;
418 $this->_select
= "SELECT " . implode(', ', $select) . " ";
424 * @param array $fields
425 * @param array $files
426 * @param CRM_Report_Form_Contribute_Summary $self
430 public static function formRule($fields, $files, $self) {
431 // Check for searching combination of display columns and
433 $ignoreFields = array('total_amount', 'sort_name');
434 $errors = $self->customDataFormRule($fields, $ignoreFields);
436 if (empty($fields['fields']['total_amount'])) {
442 if (!empty($fields[$val])) {
443 $errors[$val] = ts("Please select the Amount Statistics");
454 * @param string $entity
456 * @todo fix function signature to match parent. Remove hacky passing of $entity
457 * to acheive unclear results.
459 public function from($entity = NULL) {
460 $softCreditJoinType = "LEFT";
461 if (!empty($this->_params
['fields']['soft_amount']) &&
462 empty($this->_params
['fields']['total_amount'])
464 // if its only soft credit stats, use inner join
465 $softCreditJoinType = "INNER";
468 $softCreditJoin = "{$softCreditJoinType} JOIN civicrm_contribution_soft {$this->_aliases['civicrm_contribution_soft']}
469 ON {$this->_aliases['civicrm_contribution_soft']}.contribution_id = {$this->_aliases['civicrm_contribution']}.id";
470 if ($entity == 'contribution' ||
empty($this->_params
['fields']['soft_amount'])) {
471 $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) ";
474 $this->setFromBase('civicrm_contact');
477 INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
478 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND
479 {$this->_aliases['civicrm_contribution']}.is_test = 0
481 LEFT JOIN civicrm_financial_type {$this->_aliases['civicrm_financial_type']}
482 ON {$this->_aliases['civicrm_contribution']}.financial_type_id ={$this->_aliases['civicrm_financial_type']}.id
483 LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
484 ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND
485 {$this->_aliases['civicrm_email']}.is_primary = 1)
487 LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
488 ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
489 {$this->_aliases['civicrm_phone']}.is_primary = 1)";
491 $this->addAddressFromClause();
492 if (!empty($this->_params
['batch_id_value'])) {
494 LEFT JOIN civicrm_entity_financial_trxn eft
495 ON eft.entity_id = {$this->_aliases['civicrm_contribution']}.id AND
496 eft.entity_table = 'civicrm_contribution'
497 LEFT JOIN civicrm_entity_batch {$this->_aliases['civicrm_batch']}
498 ON {$this->_aliases['civicrm_batch']}.entity_id = eft.financial_trxn_id AND
499 {$this->_aliases['civicrm_batch']}.entity_table = 'civicrm_financial_trxn'\n";
504 * Set group by clause.
506 public function groupBy() {
507 $this->_groupBy
= "";
509 if (!empty($this->_params
['group_bys']) &&
510 is_array($this->_params
['group_bys'])
512 foreach ($this->_columns
as $tableName => $table) {
513 if (array_key_exists('group_bys', $table)) {
514 foreach ($table['group_bys'] as $fieldName => $field) {
515 if (!empty($this->_params
['group_bys'][$fieldName])) {
516 if (!empty($field['chart'])) {
517 $this->assign('chartSupported', TRUE);
520 if (!empty($table['group_bys'][$fieldName]['frequency']) &&
521 !empty($this->_params
['group_bys_freq'][$fieldName])
524 $append = "YEAR({$field['dbAlias']});;";
525 if (in_array(strtolower($this->_params
['group_bys_freq'][$fieldName]),
530 if ($this->_params
['group_bys_freq'][$fieldName] == 'FISCALYEAR') {
531 $this->_groupBy
[] = self
::fiscalYearOffset($field['dbAlias']);
534 $this->_groupBy
[] = "$append {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
539 $this->_groupBy
[] = $field['dbAlias'];
546 if (!empty($this->_statFields
) &&
547 (($append && count($this->_groupBy
) <= 1) ||
(!$append)) &&
550 $this->_rollup
= " WITH ROLLUP";
553 foreach ($this->_groupBy
as $key => $val) {
554 if (strpos($val, ';;') !== FALSE) {
555 $groupBy = array_merge($groupBy, explode(';;', $val));
558 $groupBy[] = $this->_groupBy
[$key];
561 $this->_groupBy
= "GROUP BY " . implode(', ', $groupBy);
564 $groupBy = "{$this->_aliases['civicrm_contact']}.id";
565 $this->_groupBy
= "GROUP BY {$this->_aliases['civicrm_contact']}.id";
567 $this->_groupBy
.= $this->_rollup
;
568 // append select with ANY_VALUE() keyword
569 $this->_select
= CRM_Contact_BAO_Query
::appendAnyValueToSelect($this->_selectClauses
, $groupBy);
573 * Store having clauses as an array.
575 public function storeWhereHavingClauseArray() {
576 parent
::storeWhereHavingClauseArray();
577 if (empty($this->_params
['fields']['soft_amount']) &&
578 !empty($this->_havingClauses
)
580 foreach ($this->_havingClauses
as $key => $havingClause) {
581 if (stristr($havingClause, 'soft_soft')) {
582 unset($this->_havingClauses
[$key]);
595 public function statistics(&$rows) {
596 $statistics = parent
::statistics($rows);
598 $softCredit = CRM_Utils_Array
::value('soft_amount', $this->_params
['fields']);
599 $onlySoftCredit = $softCredit && !CRM_Utils_Array
::value('total_amount', $this->_params
['fields']);
600 $group = "\nGROUP BY {$this->_aliases['civicrm_contribution']}.currency";
602 $this->from('contribution');
603 $this->customDataFrom();
606 COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as civicrm_contribution_total_amount_count,
607 SUM({$this->_aliases['civicrm_contribution']}.total_amount ) as civicrm_contribution_total_amount_sum,
608 ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as civicrm_contribution_total_amount_avg,
609 {$this->_aliases['civicrm_contribution']}.currency as currency
610 {$this->_from} {$this->_where}";
615 COUNT({$this->_aliases['civicrm_contribution_soft']}.amount ) as civicrm_contribution_soft_soft_amount_count,
616 SUM({$this->_aliases['civicrm_contribution_soft']}.amount ) as civicrm_contribution_soft_soft_amount_sum,
617 ROUND(AVG({$this->_aliases['civicrm_contribution_soft']}.amount), 2) as civicrm_contribution_soft_soft_amount_avg";
618 $contriQuery = "{$select}, {$contriQuery}";
619 $softSQL = "SELECT {$select}, {$this->_aliases['civicrm_contribution']}.currency as currency
620 {$this->_from} {$this->_where} {$group} {$this->_having}";
623 $contriSQL = "SELECT {$contriQuery} {$group} {$this->_having}";
624 $contriDAO = CRM_Core_DAO
::executeQuery($contriSQL);
626 $totalAmount = $average = $mode = $median = $softTotalAmount = $softAverage = array();
627 $count = $softCount = 0;
628 while ($contriDAO->fetch()) {
630 = CRM_Utils_Money
::format($contriDAO->civicrm_contribution_total_amount_sum
, $contriDAO->currency
) .
631 " (" . $contriDAO->civicrm_contribution_total_amount_count
. ")";
632 $average[] = CRM_Utils_Money
::format($contriDAO->civicrm_contribution_total_amount_avg
, $contriDAO->currency
);
633 $count +
= $contriDAO->civicrm_contribution_total_amount_count
;
636 $groupBy = "\n{$group}, {$this->_aliases['civicrm_contribution']}.total_amount";
637 $orderBy = "\nORDER BY civicrm_contribution_total_amount_count DESC";
638 $modeSQL = "SELECT MAX(civicrm_contribution_total_amount_count) as civicrm_contribution_total_amount_count,
639 SUBSTRING_INDEX(GROUP_CONCAT(amount ORDER BY mode.civicrm_contribution_total_amount_count DESC SEPARATOR ';'), ';', 1) as amount,
641 FROM (SELECT {$this->_aliases['civicrm_contribution']}.total_amount as amount,
642 {$contriQuery} {$groupBy} {$orderBy}) as mode GROUP BY currency";
644 $mode = CRM_Contribute_BAO_Contribution
::computeStats('mode', $modeSQL);
646 $medianSQL = "{$this->_from} {$this->_where}";
647 $median = CRM_Contribute_BAO_Contribution
::computeStats('median', $medianSQL, $this->_aliases
['civicrm_contribution']);
650 $softDAO = CRM_Core_DAO
::executeQuery($softSQL);
651 while ($softDAO->fetch()) {
653 = CRM_Utils_Money
::format($softDAO->civicrm_contribution_soft_soft_amount_sum
, $softDAO->currency
) .
654 " (" . $softDAO->civicrm_contribution_soft_soft_amount_count
. ")";
655 $softAverage[] = CRM_Utils_Money
::format($softDAO->civicrm_contribution_soft_soft_amount_avg
, $softDAO->currency
);
656 $softCount +
= $softDAO->civicrm_contribution_soft_soft_amount_count
;
660 if (!$onlySoftCredit) {
661 $statistics['counts']['amount'] = array(
662 'title' => ts('Total Amount'),
663 'value' => implode(', ', $totalAmount),
664 'type' => CRM_Utils_Type
::T_STRING
,
666 $statistics['counts']['count'] = array(
667 'title' => ts('Total Contributions'),
670 $statistics['counts']['avg'] = array(
671 'title' => ts('Average'),
672 'value' => implode(', ', $average),
673 'type' => CRM_Utils_Type
::T_STRING
,
675 $statistics['counts']['mode'] = array(
676 'title' => ts('Mode'),
677 'value' => implode(', ', $mode),
678 'type' => CRM_Utils_Type
::T_STRING
,
680 $statistics['counts']['median'] = array(
681 'title' => ts('Median'),
682 'value' => implode(', ', $median),
683 'type' => CRM_Utils_Type
::T_STRING
,
687 $statistics['counts']['soft_amount'] = array(
688 'title' => ts('Total Soft Credit Amount'),
689 'value' => implode(', ', $softTotalAmount),
690 'type' => CRM_Utils_Type
::T_STRING
,
692 $statistics['counts']['soft_count'] = array(
693 'title' => ts('Total Soft Credits'),
694 'value' => $softCount,
696 $statistics['counts']['soft_avg'] = array(
697 'title' => ts('Average Soft Credit'),
698 'value' => implode(', ', $softAverage),
699 'type' => CRM_Utils_Type
::T_STRING
,
706 * Post process function.
708 public function postProcess() {
709 $this->buildACLClause($this->_aliases
['civicrm_contact']);
710 parent
::postProcess();
714 * Build table rows for output.
719 public function buildRows($sql, &$rows) {
720 $dao = CRM_Core_DAO
::executeQuery($sql);
721 if (!is_array($rows)) {
725 // use this method to modify $this->_columnHeaders
726 $this->modifyColumnHeaders();
727 $contriRows = array();
728 $unselectedSectionColumns = $this->unselectedSectionColumns();
730 //CRM-16338 if both soft-credit and contribution are enabled then process the contribution's
731 //total amount's average, count and sum separately and add it to the respective result list
732 $softCredit = (!empty($this->_params
['fields']['soft_amount']) && !empty($this->_params
['fields']['total_amount'])) ?
TRUE : FALSE;
734 $this->from('contribution');
735 $contriSQL = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
736 $contriDAO = CRM_Core_DAO
::executeQuery($contriSQL);
737 $contriFields = array(
738 'civicrm_contribution_total_amount_sum',
739 'civicrm_contribution_total_amount_avg',
740 'civicrm_contribution_total_amount_count');
741 $contriRows = array();
742 while ($contriDAO->fetch()) {
743 $contriRow = array();
744 foreach ($contriFields as $column) {
745 $contriRow[$column] = $contriDAO->$column;
747 $contriRows[] = $contriRow;
752 while ($dao->fetch()) {
754 foreach ($this->_columnHeaders
as $key => $value) {
755 if ($softCredit && array_key_exists($key, $contriRows[$count])) {
756 $row[$key] = $contriRows[$count][$key];
758 elseif (property_exists($dao, $key)) {
759 $row[$key] = $dao->$key;
763 // section headers not selected for display need to be added to row
764 foreach ($unselectedSectionColumns as $key => $values) {
765 if (property_exists($dao, $key)) {
766 $row[$key] = $dao->$key;
781 public function buildChart(&$rows) {
782 $graphRows = array();
784 if (!empty($this->_params
['charts'])) {
785 if (!empty($this->_params
['group_bys']['receive_date'])) {
787 $contrib = !empty($this->_params
['fields']['total_amount']) ?
TRUE : FALSE;
788 $softContrib = !empty($this->_params
['fields']['soft_amount']) ?
TRUE : FALSE;
790 foreach ($rows as $key => $row) {
791 if ($row['civicrm_contribution_receive_date_subtotal']) {
792 $graphRows['receive_date'][] = $row['civicrm_contribution_receive_date_start'];
793 $graphRows[$this->_interval
][] = $row['civicrm_contribution_receive_date_interval'];
794 if ($softContrib && $contrib) {
795 // both contri & soft contri stats are present
796 $graphRows['multiValue'][0][] = $row['civicrm_contribution_total_amount_sum'];
797 $graphRows['multiValue'][1][] = $row['civicrm_contribution_soft_soft_amount_sum'];
799 elseif ($softContrib) {
800 // only soft contributions
801 $graphRows['multiValue'][0][] = $row['civicrm_contribution_soft_soft_amount_sum'];
804 // only contributions
805 $graphRows['multiValue'][0][] = $row['civicrm_contribution_total_amount_sum'];
810 if ($softContrib && $contrib) {
811 $graphRows['barKeys'][0] = ts('Contributions');
812 $graphRows['barKeys'][1] = ts('Soft Credits');
813 $graphRows['legend'] = ts('Contributions and Soft Credits');
815 elseif ($softContrib) {
816 $graphRows['legend'] = ts('Soft Credits');
820 $config = CRM_Core_Config
::Singleton();
821 $graphRows['xname'] = $this->_interval
;
822 $graphRows['yname'] = "Amount ({$config->defaultCurrency})";
823 CRM_Utils_OpenFlashChart
::chart($graphRows, $this->_params
['charts'], $this->_interval
);
824 $this->assign('chartType', $this->_params
['charts']);
830 * Alter display of rows.
832 * Iterate through the rows retrieved via SQL and make changes for display purposes,
833 * such as rendering contacts as links.
836 * Rows generated by SQL, with an array for each row.
838 public function alterDisplay(&$rows) {
840 $contributionStatus = CRM_Contribute_PseudoConstant
::contributionStatus();
842 foreach ($rows as $rowNum => $row) {
843 // make count columns point to detail report
844 if (!empty($this->_params
['group_bys']['receive_date']) &&
845 !empty($row['civicrm_contribution_receive_date_start']) &&
846 CRM_Utils_Array
::value('civicrm_contribution_receive_date_start', $row) &&
847 !empty($row['civicrm_contribution_receive_date_subtotal'])
850 $dateStart = CRM_Utils_Date
::customFormat($row['civicrm_contribution_receive_date_start'], '%Y%m%d');
851 $endDate = new DateTime($dateStart);
854 list($dateEnd['Y'], $dateEnd['M'], $dateEnd['d']) = explode(':', $endDate->format('Y:m:d'));
856 switch (strtolower($this->_params
['group_bys_freq']['receive_date'])) {
858 $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'] +
1,
859 $dateEnd['d'] - 1, $dateEnd['Y']
864 $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'],
865 $dateEnd['d'] - 1, $dateEnd['Y'] +
1
870 $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'],
871 $dateEnd['d'] - 1, $dateEnd['Y'] +
1
876 $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'],
877 $dateEnd['d'] +
6, $dateEnd['Y']
882 $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'] +
3,
883 $dateEnd['d'] - 1, $dateEnd['Y']
887 $url = CRM_Report_Utils_Report
::getNextUrl('contribute/detail',
888 "reset=1&force=1&receive_date_from={$dateStart}&receive_date_to={$dateEnd}",
891 $this->_drilldownReport
893 $rows[$rowNum]['civicrm_contribution_receive_date_start_link'] = $url;
894 $rows[$rowNum]['civicrm_contribution_receive_date_start_hover'] = ts('List all contribution(s) for this date unit.');
898 // make subtotals look nicer
899 if (array_key_exists('civicrm_contribution_receive_date_subtotal', $row) &&
900 !$row['civicrm_contribution_receive_date_subtotal']
902 $this->fixSubTotalDisplay($rows[$rowNum], $this->_statFields
);
906 // convert display name to links
907 if (array_key_exists('civicrm_contact_sort_name', $row) &&
908 array_key_exists('civicrm_contact_id', $row)
910 $url = CRM_Report_Utils_Report
::getNextUrl('contribute/detail',
911 'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'],
912 $this->_absoluteUrl
, $this->_id
, $this->_drilldownReport
914 $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
915 $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("Lists detailed contribution(s) for this record.");
919 // convert contribution status id to status name
920 if ($value = CRM_Utils_Array
::value('civicrm_contribution_contribution_status_id', $row)) {
921 $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value];
925 // If using campaigns, convert campaign_id to campaign title
926 if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
927 if ($value = $row['civicrm_contribution_campaign_id']) {
928 $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->activeCampaigns
[$value];
933 $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s) for this ') ?
TRUE : $entryFound;
934 $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s) for this ') ?
TRUE : $entryFound;
936 // skip looking further in rows, if first row itself doesn't
937 // have the column we need