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