CRM/Report/Form/Contribute/Summary.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  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.   |
  13  |                                                                    |
  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.        |
  18  |                                                                    |
  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  +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2015
  32  */
  33 class CRM_Report_Form_Contribute_Summary extends CRM_Report_Form {
  34   protected $_addressField = FALSE;
  35
  36   protected $_charts = array(
  37     '' => 'Tabular',
  38     'barChart' => 'Bar Chart',
  39     'pieChart' => 'Pie Chart',
  40   );
  41   protected $_customGroupExtends = array('Contribution');
  42   protected $_customGroupGroupBy = TRUE;
  43
  44   public $_drilldownReport = array('contribute/detail' => 'Link to Detail Report');
  45
  46   /**
  47    * Class constructor.
  48    */
  49   public function __construct() {
  50
  51     // Check if CiviCampaign is a) enabled and b) has active campaigns
  52     $config = CRM_Core_Config::singleton();
  53     $campaignEnabled = in_array("CiviCampaign", $config->enableComponents);
  54     if ($campaignEnabled) {
  55       $getCampaigns = CRM_Campaign_BAO_Campaign::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
  56       $this->activeCampaigns = $getCampaigns['campaigns'];
  57       asort($this->activeCampaigns);
  58     }
  59
  60     $this->_columns = array(
  61       'civicrm_contact' => array(
  62         'dao' => 'CRM_Contact_DAO_Contact',
  63         'fields' => $this->getBasicContactFields(),
  64         'grouping' => 'contact-fields',
  65         'group_bys' => array(
  66           'id' => array('title' => ts('Contact ID')),
  67           'sort_name' => array(
  68             'title' => ts('Contact Name'),
  69           ),
  70         ),
  71       ),
  72       'civicrm_email' => array(
  73         'dao' => 'CRM_Core_DAO_Email',
  74         'fields' => array(
  75           'email' => array(
  76             'title' => ts('Email'),
  77             'no_repeat' => TRUE,
  78           ),
  79         ),
  80         'grouping' => 'contact-fields',
  81       ),
  82       'civicrm_phone' => array(
  83         'dao' => 'CRM_Core_DAO_Phone',
  84         'fields' => array(
  85           'phone' => array(
  86             'title' => ts('Phone'),
  87             'no_repeat' => TRUE,
  88           ),
  89         ),
  90         'grouping' => 'contact-fields',
  91       ),
  92       'civicrm_financial_type' => array(
  93         'dao' => 'CRM_Financial_DAO_FinancialType',
  94         'fields' => array('financial_type' => NULL),
  95         'grouping' => 'contri-fields',
  96         'group_bys' => array(
  97           'financial_type' => array('title' => ts('Financial Type')),
  98         ),
  99       ),
 100       'civicrm_contribution' => array(
 101         'dao' => 'CRM_Contribute_DAO_Contribution',
 102           //'bao'           => 'CRM_Contribute_BAO_Contribution',
 103         'fields' => array(
 104           'contribution_status_id' => array(
 105             'title' => ts('Contribution Status'),
 106           ),
 107           'contribution_source' => array('title' => ts('Source')),
 108           'currency' => array(
 109             'required' => TRUE,
 110             'no_display' => TRUE,
 111           ),
 112           'total_amount' => array(
 113             'title' => ts('Contribution Amount Stats'),
 114             'default' => TRUE,
 115             'statistics' => array(
 116               'count' => ts('Contributions'),
 117               'sum' => ts('Contribution Aggregate'),
 118               'avg' => ts('Contribution Avg'),
 119             ),
 120           ),
 121         ),
 122         'grouping' => 'contri-fields',
 123         'filters' => array(
 124           'receive_date' => array('operatorType' => CRM_Report_Form::OP_DATE),
 125           'contribution_status_id' => array(
 126             'title' => ts('Contribution Status'),
 127             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 128             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 129             'default' => array(1),
 130             'type' => CRM_Utils_Type::T_INT,
 131           ),
 132           'currency' => array(
 133             'title' => 'Currency',
 134             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 135             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 136             'default' => NULL,
 137             'type' => CRM_Utils_Type::T_STRING,
 138           ),
 139           'financial_type_id' => array(
 140             'title' => ts('Financial Type'),
 141             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 142             'options' => CRM_Contribute_PseudoConstant::financialType(),
 143             'type' => CRM_Utils_Type::T_INT,
 144           ),
 145           'contribution_page_id' => array(
 146             'title' => ts('Contribution Page'),
 147             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 148             'options' => CRM_Contribute_PseudoConstant::contributionPage(),
 149             'type' => CRM_Utils_Type::T_INT,
 150           ),
 151           'total_amount' => array(
 152             'title' => ts('Contribution Amount'),
 153           ),
 154           'total_sum' => array(
 155             'title' => ts('Contribution Aggregate'),
 156             'type' => CRM_Report_Form::OP_INT,
 157             'dbAlias' => 'civicrm_contribution_total_amount_sum',
 158             'having' => TRUE,
 159           ),
 160           'total_count' => array(
 161             'title' => ts('Contribution Count'),
 162             'type' => CRM_Report_Form::OP_INT,
 163             'dbAlias' => 'civicrm_contribution_total_amount_count',
 164             'having' => TRUE,
 165           ),
 166           'total_avg' => array(
 167             'title' => ts('Contribution Avg'),
 168             'type' => CRM_Report_Form::OP_INT,
 169             'dbAlias' => 'civicrm_contribution_total_amount_avg',
 170             'having' => TRUE,
 171           ),
 172         ),
 173         'group_bys' => array(
 174           'receive_date' => array(
 175             'frequency' => TRUE,
 176             'default' => TRUE,
 177             'chart' => TRUE,
 178           ),
 179           'contribution_source' => NULL,
 180           'contribution_status_id' => array(
 181             'title' => ts('Contribution Status'),
 182             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 183             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 184             'default' => array(1),
 185             'type' => CRM_Utils_Type::T_INT,
 186           ),
 187         ),
 188       ),
 189       'civicrm_contribution_soft' => array(
 190         'dao' => 'CRM_Contribute_DAO_ContributionSoft',
 191         'fields' => array(
 192           'soft_amount' => array(
 193             'title' => ts('Soft Credit Amount Stats'),
 194             'name' => 'amount',
 195             'statistics' => array(
 196               'count' => ts('Soft Credits'),
 197               'sum' => ts('Soft Credit Aggregate'),
 198               'avg' => ts('Soft Credit Avg'),
 199             ),
 200           ),
 201         ),
 202         'grouping' => 'contri-fields',
 203         'filters' => array(
 204           'amount' => array(
 205             'title' => ts('Soft Credit Amount'),
 206           ),
 207           'soft_credit_type_id' => array(
 208             'title' => 'Soft Credit Type',
 209             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 210             'options' => CRM_Core_OptionGroup::values('soft_credit_type'),
 211             'default' => NULL,
 212             'type' => CRM_Utils_Type::T_STRING,
 213           ),
 214           'soft_sum' => array(
 215             'title' => ts('Soft Credit Aggregate'),
 216             'type' => CRM_Report_Form::OP_INT,
 217             'dbAlias' => 'civicrm_contribution_soft_soft_amount_sum',
 218             'having' => TRUE,
 219           ),
 220           'soft_count' => array(
 221             'title' => ts('Soft Credits Count'),
 222             'type' => CRM_Report_Form::OP_INT,
 223             'dbAlias' => 'civicrm_contribution_soft_soft_amount_count',
 224             'having' => TRUE,
 225           ),
 226           'soft_avg' => array(
 227             'title' => ts('Soft Credit Avg'),
 228             'type' => CRM_Report_Form::OP_INT,
 229             'dbAlias' => 'civicrm_contribution_soft_soft_amount_avg',
 230             'having' => TRUE,
 231           ),
 232         ),
 233       ),
 234     ) + $this->addAddressFields();
 235
 236     // If we have a campaign, build out the relevant elements
 237     if ($campaignEnabled && !empty($this->activeCampaigns)) {
 238       $this->_columns['civicrm_contribution']['fields']['campaign_id'] = array(
 239         'title' => 'Campaign',
 240         'default' => 'false',
 241       );
 242       $this->_columns['civicrm_contribution']['filters']['campaign_id'] = array(
 243         'title' => ts('Campaign'),
 244         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 245         'options' => $this->activeCampaigns,
 246       );
 247       $this->_columns['civicrm_contribution']['group_bys']['campaign_id'] = array('title' => ts('Campaign'));
 248     }
 249
 250     $this->_tagFilter = TRUE;
 251     $this->_groupFilter = TRUE;
 252     $this->_currencyColumn = 'civicrm_contribution_currency';
 253     parent::__construct();
 254   }
 255
 256   /**
 257    * Set select clause.
 258    */
 259   public function select() {
 260     $select = array();
 261     $this->_columnHeaders = array();
 262     foreach ($this->_columns as $tableName => $table) {
 263       if (array_key_exists('group_bys', $table)) {
 264         foreach ($table['group_bys'] as $fieldName => $field) {
 265           if ($tableName == 'civicrm_address') {
 266             $this->_addressField = TRUE;
 267           }
 268           if (!empty($this->_params['group_bys'][$fieldName])) {
 269             switch (CRM_Utils_Array::value($fieldName, $this->_params['group_bys_freq'])) {
 270               case 'YEARWEEK':
 271                 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start";
 272                 $select[] = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 273                 $select[] = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 274                 $field['title'] = 'Week';
 275                 break;
 276
 277               case 'YEAR':
 278                 $select[] = "MAKEDATE(YEAR({$field['dbAlias']}), 1)  AS {$tableName}_{$fieldName}_start";
 279                 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 280                 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 281                 $field['title'] = 'Year';
 282                 break;
 283
 284               case 'MONTH':
 285                 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start";
 286                 $select[] = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 287                 $select[] = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 288                 $field['title'] = 'Month';
 289                 break;
 290
 291               case 'QUARTER':
 292                 $select[] = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start";
 293                 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 294                 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 295                 $field['title'] = 'Quarter';
 296                 break;
 297             }
 298             if (!empty($this->_params['group_bys_freq'][$fieldName])) {
 299               $this->_interval = $field['title'];
 300               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['title'] = $field['title'] . ' Beginning';
 301               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['type'] = $field['type'];
 302               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params['group_bys_freq'][$fieldName];
 303
 304               // just to make sure these values are transfered to rows.
 305               // since we need that for calculation purpose,
 306               // e.g making subtotals look nicer or graphs
 307               $this->_columnHeaders["{$tableName}_{$fieldName}_interval"] = array('no_display' => TRUE);
 308               $this->_columnHeaders["{$tableName}_{$fieldName}_subtotal"] = array('no_display' => TRUE);
 309             }
 310           }
 311         }
 312       }
 313
 314       if (array_key_exists('fields', $table)) {
 315         foreach ($table['fields'] as $fieldName => $field) {
 316           if ($tableName == 'civicrm_address') {
 317             $this->_addressField = TRUE;
 318           }
 319           if (!empty($field['required']) ||
 320             !empty($this->_params['fields'][$fieldName])
 321           ) {
 322             // only include statistics columns if set
 323             if (!empty($field['statistics'])) {
 324               foreach ($field['statistics'] as $stat => $label) {
 325                 $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 326                 $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
 327                 $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 328                 switch (strtolower($stat)) {
 329                   case 'sum':
 330                     $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 331                     break;
 332
 333                   case 'count':
 334                     $select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 335                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
 336                     break;
 337
 338                   case 'avg':
 339                     $select[] = "ROUND(AVG({$field['dbAlias']}),2) as {$tableName}_{$fieldName}_{$stat}";
 340                     break;
 341                 }
 342               }
 343             }
 344             else {
 345               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 346               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 347               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 348             }
 349           }
 350         }
 351       }
 352     }
 353
 354     $this->_select = "SELECT " . implode(', ', $select) . " ";
 355   }
 356
 357   /**
 358    * Set form rules.
 359    *
 360    * @param array $fields
 361    * @param array $files
 362    * @param CRM_Report_Form_Contribute_Summary $self
 363    *
 364    * @return array
 365    */
 366   public static function formRule($fields, $files, $self) {
 367     // Check for searching combination of display columns and
 368     // grouping criteria
 369     $ignoreFields = array('total_amount', 'sort_name');
 370     $errors = $self->customDataFormRule($fields, $ignoreFields);
 371
 372     if (empty($fields['fields']['total_amount'])) {
 373       foreach (array(
 374                  'total_count_value',
 375                  'total_sum_value',
 376                  'total_avg_value',
 377                ) as $val) {
 378         if (!empty($fields[$val])) {
 379           $errors[$val] = ts("Please select the Amount Statistics");
 380         }
 381       }
 382     }
 383
 384     return $errors;
 385   }
 386
 387   /**
 388    * Set from clause.
 389    *
 390    * @param string $entity
 391    */
 392   public function from($entity = NULL) {
 393     $softCreditJoinType = "LEFT";
 394     if (!empty($this->_params['fields']['soft_amount']) &&
 395       empty($this->_params['fields']['total_amount'])
 396     ) {
 397       // if its only soft credit stats, use inner join
 398       $softCreditJoinType = "INNER";
 399     }
 400
 401     $softCreditJoin = "{$softCreditJoinType} JOIN civicrm_contribution_soft {$this->_aliases['civicrm_contribution_soft']}
 402                        ON {$this->_aliases['civicrm_contribution_soft']}.contribution_id = {$this->_aliases['civicrm_contribution']}.id";
 403     if ($entity == 'contribution' || empty($this->_params['fields']['soft_amount'])) {
 404       $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) ";
 405     }
 406
 407     $this->_from = "
 408         FROM civicrm_contact  {$this->_aliases['civicrm_contact']} {$this->_aclFrom}
 409              INNER JOIN civicrm_contribution   {$this->_aliases['civicrm_contribution']}
 410                      ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND
 411                         {$this->_aliases['civicrm_contribution']}.is_test = 0
 412              {$softCreditJoin}
 413              LEFT  JOIN civicrm_financial_type  {$this->_aliases['civicrm_financial_type']}
 414                      ON {$this->_aliases['civicrm_contribution']}.financial_type_id ={$this->_aliases['civicrm_financial_type']}.id
 415              LEFT  JOIN civicrm_email {$this->_aliases['civicrm_email']}
 416                      ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND
 417                         {$this->_aliases['civicrm_email']}.is_primary = 1)
 418
 419              LEFT  JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
 420                      ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
 421                         {$this->_aliases['civicrm_phone']}.is_primary = 1)";
 422
 423     if ($this->_addressField) {
 424       $this->_from .= "
 425                   LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
 426                          ON {$this->_aliases['civicrm_contact']}.id =
 427                             {$this->_aliases['civicrm_address']}.contact_id AND
 428                             {$this->_aliases['civicrm_address']}.is_primary = 1\n";
 429     }
 430   }
 431
 432   /**
 433    * Set group by clause.
 434    */
 435   public function groupBy() {
 436     $this->_groupBy = "";
 437     $append = FALSE;
 438     if (!empty($this->_params['group_bys']) &&
 439       is_array($this->_params['group_bys'])
 440     ) {
 441       foreach ($this->_columns as $tableName => $table) {
 442         if (array_key_exists('group_bys', $table)) {
 443           foreach ($table['group_bys'] as $fieldName => $field) {
 444             if (!empty($this->_params['group_bys'][$fieldName])) {
 445               if (!empty($field['chart'])) {
 446                 $this->assign('chartSupported', TRUE);
 447               }
 448
 449               if (!empty($table['group_bys'][$fieldName]['frequency']) &&
 450                 !empty($this->_params['group_bys_freq'][$fieldName])
 451               ) {
 452
 453                 $append = "YEAR({$field['dbAlias']}),";
 454                 if (in_array(strtolower($this->_params['group_bys_freq'][$fieldName]),
 455                   array('year')
 456                 )) {
 457                   $append = '';
 458                 }
 459                 $this->_groupBy[] = "$append {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
 460                 $append = TRUE;
 461               }
 462               else {
 463                 $this->_groupBy[] = $field['dbAlias'];
 464               }
 465             }
 466           }
 467         }
 468       }
 469
 470       if (!empty($this->_statFields) &&
 471         (($append && count($this->_groupBy) <= 1) || (!$append)) &&
 472         !$this->_having
 473       ) {
 474         $this->_rollup = " WITH ROLLUP";
 475       }
 476       $this->_groupBy = "GROUP BY " . implode(', ', $this->_groupBy) .
 477         " {$this->_rollup} ";
 478     }
 479     else {
 480       $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contact']}.id";
 481     }
 482   }
 483
 484   /**
 485    * Store having clauses as an array.
 486    */
 487   public function storeWhereHavingClauseArray() {
 488     parent::storeWhereHavingClauseArray();
 489     if (empty($this->_params['fields']['soft_amount']) &&
 490       !empty($this->_havingClauses)
 491     ) {
 492       foreach ($this->_havingClauses as $key => $havingClause) {
 493         if (stristr($havingClause, 'soft_soft')) {
 494           unset($this->_havingClauses[$key]);
 495         }
 496       }
 497     }
 498   }
 499
 500   /**
 501    * Set statistics.
 502    *
 503    * @param array $rows
 504    *
 505    * @return array
 506    */
 507   public function statistics(&$rows) {
 508     $statistics = parent::statistics($rows);
 509
 510     $softCredit = CRM_Utils_Array::value('soft_amount', $this->_params['fields']);
 511     $onlySoftCredit = $softCredit && !CRM_Utils_Array::value('total_amount', $this->_params['fields']);
 512     $group = "\nGROUP BY {$this->_aliases['civicrm_contribution']}.currency";
 513
 514     $this->from('contribution');
 515     $this->customDataFrom();
 516     $contriSQL = "SELECT
 517 COUNT({$this->_aliases['civicrm_contribution']}.total_amount )        as civicrm_contribution_total_amount_count,
 518 SUM({$this->_aliases['civicrm_contribution']}.total_amount )          as civicrm_contribution_total_amount_sum,
 519 ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as civicrm_contribution_total_amount_avg,
 520 {$this->_aliases['civicrm_contribution']}.currency                    as currency
 521 {$this->_from} {$this->_where} {$group} {$this->_having}";
 522
 523     if ($softCredit) {
 524       $this->from();
 525       $softSQL = "SELECT
 526 COUNT({$this->_aliases['civicrm_contribution_soft']}.amount )        as civicrm_contribution_soft_soft_amount_count,
 527 SUM({$this->_aliases['civicrm_contribution_soft']}.amount )          as civicrm_contribution_soft_soft_amount_sum,
 528 ROUND(AVG({$this->_aliases['civicrm_contribution_soft']}.amount), 2) as civicrm_contribution_soft_soft_amount_avg,
 529 {$this->_aliases['civicrm_contribution']}.currency                    as currency
 530 {$this->_from} {$this->_where} {$group} {$this->_having}";
 531     }
 532
 533     $contriDAO = CRM_Core_DAO::executeQuery($contriSQL);
 534
 535     $totalAmount = $average = $softTotalAmount = $softAverage = array();
 536     $count = $softCount = 0;
 537     while ($contriDAO->fetch()) {
 538       $totalAmount[]
 539         = CRM_Utils_Money::format($contriDAO->civicrm_contribution_total_amount_sum, $contriDAO->currency) .
 540         " (" . $contriDAO->civicrm_contribution_total_amount_count . ")";
 541       $average[] = CRM_Utils_Money::format($contriDAO->civicrm_contribution_total_amount_avg, $contriDAO->currency);
 542       $count += $contriDAO->civicrm_contribution_total_amount_count;
 543     }
 544
 545     if ($softCredit) {
 546       $softDAO = CRM_Core_DAO::executeQuery($softSQL);
 547       while ($softDAO->fetch()) {
 548         $softTotalAmount[]
 549           = CRM_Utils_Money::format($softDAO->civicrm_contribution_soft_soft_amount_sum, $softDAO->currency) .
 550           " (" . $softDAO->civicrm_contribution_soft_soft_amount_count . ")";
 551         $softAverage[] = CRM_Utils_Money::format($softDAO->civicrm_contribution_soft_soft_amount_avg, $softDAO->currency);
 552         $softCount += $softDAO->civicrm_contribution_soft_soft_amount_count;
 553       }
 554     }
 555
 556     if (!$onlySoftCredit) {
 557       $statistics['counts']['amount'] = array(
 558         'title' => ts('Total Amount'),
 559         'value' => implode(',  ', $totalAmount),
 560         'type' => CRM_Utils_Type::T_STRING,
 561       );
 562       $statistics['counts']['count'] = array(
 563         'title' => ts('Total Contributions'),
 564         'value' => $count,
 565       );
 566       $statistics['counts']['avg'] = array(
 567         'title' => ts('Average'),
 568         'value' => implode(',  ', $average),
 569         'type' => CRM_Utils_Type::T_STRING,
 570       );
 571     }
 572     if ($softCredit) {
 573       $statistics['counts']['soft_amount'] = array(
 574         'title' => ts('Total Soft Credit Amount'),
 575         'value' => implode(',  ', $softTotalAmount),
 576         'type' => CRM_Utils_Type::T_STRING,
 577       );
 578       $statistics['counts']['soft_count'] = array(
 579         'title' => ts('Total Soft Credits'),
 580         'value' => $softCount,
 581       );
 582       $statistics['counts']['soft_avg'] = array(
 583         'title' => ts('Average Soft Credit'),
 584         'value' => implode(',  ', $softAverage),
 585         'type' => CRM_Utils_Type::T_STRING,
 586       );
 587     }
 588     return $statistics;
 589   }
 590
 591   /**
 592    * Post process function.
 593    */
 594   public function postProcess() {
 595     $this->buildACLClause($this->_aliases['civicrm_contact']);
 596     parent::postProcess();
 597   }
 598
 599   /**
 600    * Build table rows for output.
 601    *
 602    * @param string $sql
 603    * @param array $rows
 604    */
 605   public function buildRows($sql, &$rows) {
 606     $dao = CRM_Core_DAO::executeQuery($sql);
 607     if (!is_array($rows)) {
 608       $rows = array();
 609     }
 610
 611     // use this method to modify $this->_columnHeaders
 612     $this->modifyColumnHeaders();
 613     $contriRows = array();
 614     $unselectedSectionColumns = $this->unselectedSectionColumns();
 615
 616     //CRM-16338 if both soft-credit and contribution are enabled then process the contribution's
 617     //total amount's average, count and sum separately and add it to the respective result list
 618     $softCredit = (!empty($this->_params['fields']['soft_amount']) && !empty($this->_params['fields']['total_amount'])) ? TRUE : FALSE;
 619     if ($softCredit) {
 620       $this->from('contribution');
 621       $contriSQL = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
 622       $contriDAO = CRM_Core_DAO::executeQuery($contriSQL);
 623       $contriFields = array(
 624         'civicrm_contribution_total_amount_sum',
 625         'civicrm_contribution_total_amount_avg',
 626         'civicrm_contribution_total_amount_count');
 627       $contriRows = array();
 628       while ($contriDAO->fetch()) {
 629         $contriRow = array();
 630         foreach ($contriFields as $column) {
 631           $contriRow[$column] = $contriDAO->$column;
 632         }
 633         $contriRows[] = $contriRow;
 634       }
 635     }
 636
 637     $count = 0;
 638     while ($dao->fetch()) {
 639       $row = array();
 640       foreach ($this->_columnHeaders as $key => $value) {
 641         if ($softCredit && array_key_exists($key, $contriRows[$count])) {
 642           $row[$key] = $contriRows[$count][$key];
 643         }
 644         elseif (property_exists($dao, $key)) {
 645           $row[$key] = $dao->$key;
 646         }
 647       }
 648
 649       // section headers not selected for display need to be added to row
 650       foreach ($unselectedSectionColumns as $key => $values) {
 651         if (property_exists($dao, $key)) {
 652           $row[$key] = $dao->$key;
 653         }
 654       }
 655
 656       $count++;
 657       $rows[] = $row;
 658     }
 659
 660   }
 661
 662   /**
 663    * Build chart.
 664    *
 665    * @param array $rows
 666    */
 667   public function buildChart(&$rows) {
 668     $graphRows = array();
 669
 670     if (!empty($this->_params['charts'])) {
 671       if (!empty($this->_params['group_bys']['receive_date'])) {
 672
 673         $contrib = !empty($this->_params['fields']['total_amount']) ? TRUE : FALSE;
 674         $softContrib = !empty($this->_params['fields']['soft_amount']) ? TRUE : FALSE;
 675
 676         foreach ($rows as $key => $row) {
 677           if ($row['civicrm_contribution_receive_date_subtotal']) {
 678             $graphRows['receive_date'][] = $row['civicrm_contribution_receive_date_start'];
 679             $graphRows[$this->_interval][] = $row['civicrm_contribution_receive_date_interval'];
 680             if ($softContrib && $contrib) {
 681               // both contri & soft contri stats are present
 682               $graphRows['multiValue'][0][] = $row['civicrm_contribution_total_amount_sum'];
 683               $graphRows['multiValue'][1][] = $row['civicrm_contribution_soft_soft_amount_sum'];
 684             }
 685             elseif ($softContrib) {
 686               // only soft contributions
 687               $graphRows['multiValue'][0][] = $row['civicrm_contribution_soft_soft_amount_sum'];
 688             }
 689             else {
 690               // only contributions
 691               $graphRows['multiValue'][0][] = $row['civicrm_contribution_total_amount_sum'];
 692             }
 693           }
 694         }
 695
 696         if ($softContrib && $contrib) {
 697           $graphRows['barKeys'][0] = ts('Contributions');
 698           $graphRows['barKeys'][1] = ts('Soft Credits');
 699           $graphRows['legend'] = ts('Contributions and Soft Credits');
 700         }
 701         elseif ($softContrib) {
 702           $graphRows['legend'] = ts('Soft Credits');
 703         }
 704
 705         // build the chart.
 706         $config = CRM_Core_Config::Singleton();
 707         $graphRows['xname'] = $this->_interval;
 708         $graphRows['yname'] = "Amount ({$config->defaultCurrency})";
 709         CRM_Utils_OpenFlashChart::chart($graphRows, $this->_params['charts'], $this->_interval);
 710         $this->assign('chartType', $this->_params['charts']);
 711       }
 712     }
 713   }
 714
 715   /**
 716    * Alter display of rows.
 717    *
 718    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 719    * such as rendering contacts as links.
 720    *
 721    * @param array $rows
 722    *   Rows generated by SQL, with an array for each row.
 723    */
 724   public function alterDisplay(&$rows) {
 725     $entryFound = FALSE;
 726     $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
 727
 728     foreach ($rows as $rowNum => $row) {
 729       // make count columns point to detail report
 730       if (!empty($this->_params['group_bys']['receive_date']) &&
 731         !empty($row['civicrm_contribution_receive_date_start']) &&
 732         CRM_Utils_Array::value('civicrm_contribution_receive_date_start', $row) &&
 733         !empty($row['civicrm_contribution_receive_date_subtotal'])
 734       ) {
 735
 736         $dateStart = CRM_Utils_Date::customFormat($row['civicrm_contribution_receive_date_start'], '%Y%m%d');
 737         $endDate = new DateTime($dateStart);
 738         $dateEnd = array();
 739
 740         list($dateEnd['Y'], $dateEnd['M'], $dateEnd['d']) = explode(':', $endDate->format('Y:m:d'));
 741
 742         switch (strtolower($this->_params['group_bys_freq']['receive_date'])) {
 743           case 'month':
 744             $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'] + 1,
 745               $dateEnd['d'] - 1, $dateEnd['Y']
 746             ));
 747             break;
 748
 749           case 'year':
 750             $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'],
 751               $dateEnd['d'] - 1, $dateEnd['Y'] + 1
 752             ));
 753             break;
 754
 755           case 'yearweek':
 756             $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'],
 757               $dateEnd['d'] + 6, $dateEnd['Y']
 758             ));
 759             break;
 760
 761           case 'quarter':
 762             $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'] + 3,
 763               $dateEnd['d'] - 1, $dateEnd['Y']
 764             ));
 765             break;
 766         }
 767         $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
 768           "reset=1&force=1&receive_date_from={$dateStart}&receive_date_to={$dateEnd}",
 769           $this->_absoluteUrl,
 770           $this->_id,
 771           $this->_drilldownReport
 772         );
 773         $rows[$rowNum]['civicrm_contribution_receive_date_start_link'] = $url;
 774         $rows[$rowNum]['civicrm_contribution_receive_date_start_hover'] = ts('List all contribution(s) for this date unit.');
 775         $entryFound = TRUE;
 776       }
 777
 778       // make subtotals look nicer
 779       if (array_key_exists('civicrm_contribution_receive_date_subtotal', $row) &&
 780         !$row['civicrm_contribution_receive_date_subtotal']
 781       ) {
 782         $this->fixSubTotalDisplay($rows[$rowNum], $this->_statFields);
 783         $entryFound = TRUE;
 784       }
 785
 786       // convert display name to links
 787       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 788         array_key_exists('civicrm_contact_id', $row)
 789       ) {
 790         $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
 791           'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'],
 792           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 793         );
 794         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 795         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("Lists detailed contribution(s) for this record.");
 796         $entryFound = TRUE;
 797       }
 798
 799       // convert contribution status id to status name
 800       if ($value = CRM_Utils_Array::value('civicrm_contribution_contribution_status_id', $row)) {
 801         $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value];
 802         $entryFound = TRUE;
 803       }
 804
 805       // If using campaigns, convert campaign_id to campaign title
 806       if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
 807         if ($value = $row['civicrm_contribution_campaign_id']) {
 808           $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->activeCampaigns[$value];
 809         }
 810         $entryFound = TRUE;
 811       }
 812
 813       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s) for this ') ? TRUE : $entryFound;
 814       $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s) for this ') ? TRUE : $entryFound;
 815
 816       // skip looking further in rows, if first row itself doesn't
 817       // have the column we need
 818       if (!$entryFound) {
 819         break;
 820       }
 821     }
 822   }
 823
 824 }