CRM/Report/Form/Contribute/Summary.php

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