CRM/Report/Form/Contribute/Summary.php

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