CRM/Report/Form/Contribute/Summary.php

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