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('Contributions Aggregate'),
 149               'count' => ts('Contributions'),
 150               'avg' => ts('Contributions 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('Donation 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('Contributions 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('Contributions 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('Contributions 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       'civicrm_group' =>
 268       array(
 269         'dao' => 'CRM_Contact_DAO_GroupContact',
 270         'alias' => 'cgroup',
 271         'filters' =>
 272         array(
 273           'gid' =>
 274           array(
 275             'name' => 'group_id',
 276             'title' => ts('Group'),
 277             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 278             'group' => TRUE,
 279             'options' => CRM_Core_PseudoConstant::group(),
 280             'type' => CRM_Utils_Type::T_INT,
 281           ),
 282         ),
 283       ),
 284     ) + $this->addAddressFields();
 285
 286     // If we have a campaign, build out the relevant elements
 287     $this->_tagFilter = TRUE;
 288     if ($campaignEnabled && !empty($this->activeCampaigns)) {
 289       $this->_columns['civicrm_contribution']['fields']['campaign_id'] = array(
 290         'title' => 'Campaign',
 291         'default' => 'false',
 292       );
 293       $this->_columns['civicrm_contribution']['filters']['campaign_id'] = array('title' => ts('Campaign'),
 294         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 295         'options' => $this->activeCampaigns,
 296       );
 297       $this->_columns['civicrm_contribution']['group_bys']['campaign_id'] = array('title' => ts('Campaign'));
 298     }
 299
 300     $this->_currencyColumn = 'civicrm_contribution_currency';
 301     parent::__construct();
 302   }
 303
 304   function preProcess() {
 305     parent::preProcess();
 306   }
 307
 308   /**
 309    * @param bool $freeze
 310    *
 311    * @return array
 312    */
 313   function setDefaultValues($freeze = TRUE) {
 314     return parent::setDefaultValues($freeze);
 315   }
 316
 317   function select() {
 318     $select = array();
 319     $this->_columnHeaders = array();
 320     foreach ($this->_columns as $tableName => $table) {
 321       if (array_key_exists('group_bys', $table)) {
 322         foreach ($table['group_bys'] as $fieldName => $field) {
 323           if ($tableName == 'civicrm_address') {
 324             $this->_addressField = TRUE;
 325           }
 326           if (!empty($this->_params['group_bys'][$fieldName])) {
 327             switch (CRM_Utils_Array::value($fieldName, $this->_params['group_bys_freq'])) {
 328               case 'YEARWEEK':
 329                 $select[]       = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start";
 330                 $select[]       = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 331                 $select[]       = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 332                 $field['title'] = 'Week';
 333                 break;
 334
 335               case 'YEAR':
 336                 $select[]       = "MAKEDATE(YEAR({$field['dbAlias']}), 1)  AS {$tableName}_{$fieldName}_start";
 337                 $select[]       = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 338                 $select[]       = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 339                 $field['title'] = 'Year';
 340                 break;
 341
 342               case 'MONTH':
 343                 $select[]       = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start";
 344                 $select[]       = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 345                 $select[]       = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 346                 $field['title'] = 'Month';
 347                 break;
 348
 349               case 'QUARTER':
 350                 $select[]       = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start";
 351                 $select[]       = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 352                 $select[]       = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 353                 $field['title'] = 'Quarter';
 354                 break;
 355             }
 356             if (!empty($this->_params['group_bys_freq'][$fieldName])) {
 357               $this->_interval = $field['title'];
 358               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['title'] = $field['title'] . ' Beginning';
 359               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['type'] = $field['type'];
 360               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params['group_bys_freq'][$fieldName];
 361
 362               // just to make sure these values are transfered to rows.
 363               // since we need that for calculation purpose,
 364               // e.g making subtotals look nicer or graphs
 365               $this->_columnHeaders["{$tableName}_{$fieldName}_interval"] = array('no_display' => TRUE);
 366               $this->_columnHeaders["{$tableName}_{$fieldName}_subtotal"] = array('no_display' => TRUE);
 367             }
 368           }
 369         }
 370       }
 371
 372       if (array_key_exists('fields', $table)) {
 373         foreach ($table['fields'] as $fieldName => $field) {
 374           if ($tableName == 'civicrm_address') {
 375             $this->_addressField = TRUE;
 376           }
 377           if (!empty($field['required']) || !empty($this->_params['fields'][$fieldName])) {
 378
 379             // only include statistics columns if set
 380             if (!empty($field['statistics'])) {
 381               foreach ($field['statistics'] as $stat => $label) {
 382                 switch (strtolower($stat)) {
 383                   case 'sum':
 384                     $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 385                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 386                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
 387                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 388                     break;
 389
 390                   case 'count':
 391                     $select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 392                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
 393                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 394                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 395                     break;
 396
 397                   case 'avg':
 398                     $select[] = "ROUND(AVG({$field['dbAlias']}),2) as {$tableName}_{$fieldName}_{$stat}";
 399                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
 400                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 401                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 402                     break;
 403                 }
 404               }
 405             }
 406             else {
 407               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 408               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 409               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 410             }
 411           }
 412         }
 413       }
 414     }
 415
 416     $this->_select = "SELECT " . implode(', ', $select) . " ";
 417   }
 418
 419   /**
 420    * @param $fields
 421    * @param $files
 422    * @param $self
 423    *
 424    * @return array
 425    */
 426   static function formRule($fields, $files, $self) {
 427     $errors = $grouping = array();
 428     //check for searching combination of dispaly columns and
 429     //grouping criteria
 430     $ignoreFields = array('total_amount', 'sort_name');
 431     $errors = $self->customDataFormRule($fields, $ignoreFields);
 432
 433     if (empty($fields['group_bys']['receive_date'])) {
 434       if (!empty($fields['receive_date_relative']) ||
 435         CRM_Utils_Date::isDate($fields['receive_date_from']) ||
 436         CRM_Utils_Date::isDate($fields['receive_date_to'])
 437       ) {
 438         $errors['receive_date_relative'] = ts("Do not use filter on Date if group by Receive Date is not used ");
 439       }
 440     }
 441     if (empty($fields['fields']['total_amount'])) {
 442       foreach (array(
 443         'total_count_value', 'total_sum_value', 'total_avg_value') as $val) {
 444         if (!empty($fields[$val])) {
 445           $errors[$val] = ts("Please select the Amount Statistics");
 446         }
 447       }
 448     }
 449
 450     return $errors;
 451   }
 452
 453   function from() {
 454     $softCreditJoin = "LEFT";
 455     if (!empty($this->_params['fields']['soft_amount']) && empty($this->_params['fields']['total_amount'])) {
 456       // if its only soft credit stats, use inner join
 457       $softCreditJoin = "INNER";
 458     }
 459
 460     $this->_from = "
 461         FROM civicrm_contact  {$this->_aliases['civicrm_contact']}
 462              INNER JOIN civicrm_contribution   {$this->_aliases['civicrm_contribution']}
 463                      ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND
 464                         {$this->_aliases['civicrm_contribution']}.is_test = 0
 465              {$softCreditJoin} JOIN civicrm_contribution_soft {$this->_aliases['civicrm_contribution_soft']}
 466                        ON {$this->_aliases['civicrm_contribution_soft']}.contribution_id = {$this->_aliases['civicrm_contribution']}.id
 467              LEFT  JOIN civicrm_financial_type  {$this->_aliases['civicrm_financial_type']}
 468                      ON {$this->_aliases['civicrm_contribution']}.financial_type_id ={$this->_aliases['civicrm_financial_type']}.id
 469              LEFT  JOIN civicrm_email {$this->_aliases['civicrm_email']}
 470                      ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND
 471                         {$this->_aliases['civicrm_email']}.is_primary = 1)
 472
 473              LEFT  JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
 474                      ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
 475                         {$this->_aliases['civicrm_phone']}.is_primary = 1)";
 476
 477     if ($this->_addressField) {
 478       $this->_from .= "
 479                   LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
 480                          ON {$this->_aliases['civicrm_contact']}.id =
 481                             {$this->_aliases['civicrm_address']}.contact_id AND
 482                             {$this->_aliases['civicrm_address']}.is_primary = 1\n";
 483     }
 484   }
 485
 486   function groupBy() {
 487     $this->_groupBy = "";
 488     $append = FALSE;
 489     if (is_array($this->_params['group_bys']) &&
 490       !empty($this->_params['group_bys'])
 491     ) {
 492       foreach ($this->_columns as $tableName => $table) {
 493         if (array_key_exists('group_bys', $table)) {
 494           foreach ($table['group_bys'] as $fieldName => $field) {
 495             if (!empty($this->_params['group_bys'][$fieldName])) {
 496               if (!empty($field['chart'])) {
 497                 $this->assign('chartSupported', TRUE);
 498               }
 499
 500               if (!empty($table['group_bys'][$fieldName]['frequency']) && !empty($this->_params['group_bys_freq'][$fieldName])) {
 501
 502                 $append = "YEAR({$field['dbAlias']}),";
 503                 if (in_array(strtolower($this->_params['group_bys_freq'][$fieldName]),
 504                     array('year')
 505                   )) {
 506                   $append = '';
 507                 }
 508                 $this->_groupBy[] = "$append {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
 509                 $append = TRUE;
 510               }
 511               else {
 512                 $this->_groupBy[] = $field['dbAlias'];
 513               }
 514             }
 515           }
 516         }
 517       }
 518
 519       if (!empty($this->_statFields) &&
 520         (($append && count($this->_groupBy) <= 1) || (!$append)) && !$this->_having
 521       ) {
 522         $this->_rollup = " WITH ROLLUP";
 523       }
 524       $this->_groupBy = "GROUP BY " . implode(', ', $this->_groupBy) . " {$this->_rollup} ";
 525     }
 526     else {
 527       $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contact']}.id";
 528     }
 529   }
 530
 531   function storeWhereHavingClauseArray(){
 532     parent::storeWhereHavingClauseArray();
 533     if (empty($this->_params['fields']['soft_amount']) && !empty($this->_havingClauses)){
 534       foreach ($this->_havingClauses as $key => $havingClause) {
 535         if (stristr($havingClause, 'soft_soft')){
 536           unset($this->_havingClauses[$key]);
 537         }
 538       }
 539     }
 540   }
 541
 542   /**
 543    * @param $rows
 544    *
 545    * @return array
 546    */
 547   function statistics(&$rows) {
 548     $statistics = parent::statistics($rows);
 549
 550     $softCredit = CRM_Utils_Array::value('soft_amount', $this->_params['fields']);
 551     $onlySoftCredit = $softCredit && !CRM_Utils_Array::value('total_amount', $this->_params['fields']);
 552     $totalAmount = $average = $softTotalAmount = $softAverage = array();
 553
 554     $select = "SELECT
 555 COUNT({$this->_aliases['civicrm_contribution']}.total_amount )        as civicrm_contribution_total_amount_count,
 556 SUM({$this->_aliases['civicrm_contribution']}.total_amount )          as civicrm_contribution_total_amount_sum,
 557 ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as civicrm_contribution_total_amount_avg,
 558 {$this->_aliases['civicrm_contribution']}.currency                    as currency";
 559
 560     if ($softCredit) {
 561       $select .= ",
 562 COUNT({$this->_aliases['civicrm_contribution_soft']}.amount )        as civicrm_contribution_soft_soft_amount_count,
 563 SUM({$this->_aliases['civicrm_contribution_soft']}.amount )          as civicrm_contribution_soft_soft_amount_sum,
 564 ROUND(AVG({$this->_aliases['civicrm_contribution_soft']}.amount), 2) as civicrm_contribution_soft_soft_amount_avg";
 565     }
 566     $group = "\nGROUP BY {$this->_aliases['civicrm_contribution']}.currency";
 567     $sql = "{$select} {$this->_from} {$this->_where} {$group} {$this->_having}";
 568
 569     $dao = CRM_Core_DAO::executeQuery($sql);
 570     $totalAmount = $average = $softTotalAmount = $softAverage = array();
 571     $count = $softCount = 0;
 572     while ($dao->fetch()) {
 573       $totalAmount[] = CRM_Utils_Money::format($dao->civicrm_contribution_total_amount_sum, $dao->currency)." (".$dao->civicrm_contribution_total_amount_count.")";
 574       $average[] = CRM_Utils_Money::format($dao->civicrm_contribution_total_amount_avg, $dao->currency);
 575       $count += $dao->civicrm_contribution_total_amount_count;
 576
 577       if ($softCredit) {
 578         $softTotalAmount[] = CRM_Utils_Money::format($dao->civicrm_contribution_soft_soft_amount_sum, $dao->currency)." (".$dao->civicrm_contribution_soft_soft_amount_count.")";
 579         $softAverage[] = CRM_Utils_Money::format($dao->civicrm_contribution_soft_soft_amount_avg, $dao->currency);
 580         $softCount += $dao->civicrm_contribution_soft_soft_amount_count;
 581       }
 582     }
 583
 584     if (!$onlySoftCredit) {
 585       $statistics['counts']['amount'] = array(
 586         'title' => ts('Total Amount'),
 587         'value' => implode(',  ', $totalAmount),
 588         'type' => CRM_Utils_Type::T_STRING,
 589       );
 590       $statistics['counts']['count'] = array(
 591         'title' => ts('Total Donations'),
 592         'value' => $count,
 593       );
 594       $statistics['counts']['avg'] = array(
 595         'title' => ts('Average'),
 596         'value' => implode(',  ', $average),
 597         'type' => CRM_Utils_Type::T_STRING,
 598       );
 599     }
 600     if ($softCredit) {
 601       $statistics['counts']['soft_amount'] = array(
 602         'title' => ts('Total Soft Credit Amount'),
 603         'value' => implode(',  ', $softTotalAmount),
 604         'type' => CRM_Utils_Type::T_STRING,
 605       );
 606       $statistics['counts']['soft_count'] = array(
 607         'title' => ts('Total Soft Credits'),
 608         'value' => $softCount,
 609       );
 610       $statistics['counts']['soft_avg'] = array(
 611         'title' => ts('Average Soft Credit'),
 612         'value' => implode(',  ', $softAverage),
 613         'type' => CRM_Utils_Type::T_STRING,
 614       );
 615     }
 616     return $statistics;
 617   }
 618
 619   function postProcess() {
 620     $this->buildACLClause($this->_aliases['civicrm_contact']);
 621     parent::postProcess();
 622   }
 623
 624   /**
 625    * @param $rows
 626    */
 627   function buildChart(&$rows) {
 628     $graphRows = array();
 629
 630     if (!empty($this->_params['charts'])) {
 631       if (!empty($this->_params['group_bys']['receive_date'])) {
 632
 633         $contrib = !empty($this->_params['fields']['total_amount']) ? TRUE : FALSE;
 634         $softContrib = !empty($this->_params['fields']['soft_amount']) ? TRUE : FALSE;
 635
 636         foreach ($rows as $key => $row) {
 637           if ($row['civicrm_contribution_receive_date_subtotal']) {
 638             $graphRows['receive_date'][] = $row['civicrm_contribution_receive_date_start'];
 639             $graphRows[$this->_interval][] = $row['civicrm_contribution_receive_date_interval'];
 640             if ($softContrib && $contrib) {
 641               // both contri & soft contri stats are present
 642               $graphRows['multiValue'][0][] = $row['civicrm_contribution_total_amount_sum'];
 643               $graphRows['multiValue'][1][] = $row['civicrm_contribution_soft_soft_amount_sum'];
 644             } else if ($softContrib) {
 645               // only soft contributions
 646               $graphRows['multiValue'][0][] = $row['civicrm_contribution_soft_soft_amount_sum'];
 647             } else {
 648               // only contributions
 649               $graphRows['multiValue'][0][] = $row['civicrm_contribution_total_amount_sum'];
 650             }
 651           }
 652         }
 653
 654         if ($softContrib && $contrib) {
 655           $graphRows['barKeys'][0] = ts('Contributions');
 656           $graphRows['barKeys'][1] = ts('Soft Credits');
 657           $graphRows['legend'] = ts('Contributions and Soft Credits');
 658         } else if ($softContrib) {
 659           $graphRows['legend'] = ts('Soft Credits');
 660         }
 661
 662         // build the chart.
 663         $config             = CRM_Core_Config::Singleton();
 664         $graphRows['xname'] = $this->_interval;
 665         $graphRows['yname'] = "Amount ({$config->defaultCurrency})";
 666         CRM_Utils_OpenFlashChart::chart($graphRows, $this->_params['charts'], $this->_interval);
 667         $this->assign('chartType', $this->_params['charts']);
 668       }
 669     }
 670   }
 671
 672   /**
 673    * @param $rows
 674    */
 675   function alterDisplay(&$rows) {
 676     // custom code to alter rows
 677     $entryFound = FALSE;
 678
 679     foreach ($rows as $rowNum => $row) {
 680       // make count columns point to detail report
 681       if (!empty($this->_params['group_bys']['receive_date']) && !empty($row['civicrm_contribution_receive_date_start']) &&
 682         CRM_Utils_Array::value('civicrm_contribution_receive_date_start', $row) && !empty($row['civicrm_contribution_receive_date_subtotal'])) {
 683
 684         $dateStart = CRM_Utils_Date::customFormat($row['civicrm_contribution_receive_date_start'], '%Y%m%d');
 685         $endDate   = new DateTime($dateStart);
 686         $dateEnd   = array();
 687
 688         list($dateEnd['Y'], $dateEnd['M'], $dateEnd['d']) = explode(':', $endDate->format('Y:m:d'));
 689
 690         switch (strtolower($this->_params['group_bys_freq']['receive_date'])) {
 691           case 'month':
 692             $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'] + 1,
 693                 $dateEnd['d'] - 1, $dateEnd['Y']
 694               ));
 695             break;
 696
 697           case 'year':
 698             $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'],
 699                 $dateEnd['d'] - 1, $dateEnd['Y'] + 1
 700               ));
 701             break;
 702
 703           case 'yearweek':
 704             $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'],
 705                 $dateEnd['d'] + 6, $dateEnd['Y']
 706               ));
 707             break;
 708
 709           case 'quarter':
 710             $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'] + 3,
 711                 $dateEnd['d'] - 1, $dateEnd['Y']
 712               ));
 713             break;
 714         }
 715         $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
 716                                                    "reset=1&force=1&receive_date_from={$dateStart}&receive_date_to={$dateEnd}",
 717                                                    $this->_absoluteUrl,
 718                                                    $this->_id,
 719                                                    $this->_drilldownReport
 720                                                    );
 721         $rows[$rowNum]['civicrm_contribution_receive_date_start_link'] = $url;
 722         $rows[$rowNum]['civicrm_contribution_receive_date_start_hover'] = ts('List all contribution(s) for this date unit.');
 723         $entryFound = TRUE;
 724       }
 725
 726       // make subtotals look nicer
 727       if (array_key_exists('civicrm_contribution_receive_date_subtotal', $row) &&
 728         !$row['civicrm_contribution_receive_date_subtotal']
 729       ) {
 730         $this->fixSubTotalDisplay($rows[$rowNum], $this->_statFields);
 731         $entryFound = TRUE;
 732       }
 733
 734       // convert display name to links
 735       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 736         array_key_exists('civicrm_contact_id', $row)
 737       ) {
 738         $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
 739           'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'],
 740           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 741         );
 742         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 743         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("Lists detailed contribution(s) for this record.");
 744         $entryFound = TRUE;
 745       }
 746
 747       // If using campaigns, convert campaign_id to campaign title
 748       if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
 749         if ($value = $row['civicrm_contribution_campaign_id']) {
 750           $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->activeCampaigns[$value];
 751         }
 752         $entryFound = TRUE;
 753       }
 754
 755       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s) for this ') ? TRUE : $entryFound;
 756
 757       // skip looking further in rows, if first row itself doesn't
 758       // have the column we need
 759       if (!$entryFound) {
 760         break;
 761       }
 762     }
 763   }
 764 }
 765