CRM/Report/Form/Contribute/Summary.php

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