CRM/Report/Form/Contribute/Summary.php

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