CRM/Report/Form/Contribute/Summary.php

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