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