crm.fsf.org/20131203/files/sites/all/modules-old/civicrm/CRM/Report/Form/Contribute/Summary.php

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