CRM/Report/Form/Pledge/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  * $Id$
  33  *
  34  */
  35 class CRM_Report_Form_Pledge_Summary extends CRM_Report_Form {
  36
  37   protected $_summary = NULL;
  38   protected $_totalPaid = FALSE;
  39   protected $_customGroupExtends = array('Pledge', 'Individual');
  40   protected $_customGroupGroupBy = TRUE;
  41   protected $_addressField = FALSE;
  42   protected $_emailField = FALSE;
  43
  44   /**
  45    * This report has not been optimised for group filtering.
  46    *
  47    * The functionality for group filtering has been improved but not
  48    * all reports have been adjusted to take care of it. This report has not
  49    * and will run an inefficient query until fixed.
  50    *
  51    * CRM-19170
  52    *
  53    * @var bool
  54    */
  55   protected $groupFilterNotOptimised = TRUE;
  56
  57   /**
  58    * Class constructor.
  59    */
  60   public function __construct() {
  61     $this->_columns = array(
  62       'civicrm_contact' => array(
  63         'dao' => 'CRM_Contact_DAO_Contact',
  64         'fields' => array(
  65           'sort_name' => array(
  66             'title' => ts('Contact Name'),
  67             'no_repeat' => TRUE,
  68           ),
  69           'postal_greeting_display' => array('title' => ts('Postal Greeting')),
  70           'id' => array(
  71             'no_display' => TRUE,
  72             'required' => TRUE,
  73           ),
  74         ),
  75         'grouping' => 'contact-fields',
  76         'group_bys' => array(
  77           'id' => array('title' => ts('Contact ID')),
  78           'sort_name' => array(
  79             'title' => ts('Contact Name'),
  80           ),
  81         ),
  82       ),
  83       'civicrm_email' => array(
  84         'dao' => 'CRM_Core_DAO_Email',
  85         'fields' => array(
  86           'email' => array(
  87             'no_repeat' => TRUE,
  88             'title' => ts('email'),
  89           ),
  90         ),
  91         'grouping' => 'contact-fields',
  92       ),
  93       'civicrm_pledge' => array(
  94         'dao' => 'CRM_Pledge_DAO_Pledge',
  95         'fields' => array(
  96           'id' => array(
  97             'no_display' => TRUE,
  98             'required' => FALSE,
  99           ),
 100           'financial_type_id' => array(
 101             'title' => ts('Financial Type'),
 102           ),
 103           'currency' => array(
 104             'required' => TRUE,
 105             'no_display' => TRUE,
 106           ),
 107           'amount' => array(
 108             'title' => ts('Pledge Amount'),
 109             'required' => TRUE,
 110             'type' => CRM_Utils_Type::T_MONEY,
 111             'statistics' => array(
 112               'sum' => ts('Aggregate Amount Pledged'),
 113               'count' => ts('Pledges'),
 114               'avg' => ts('Average'),
 115             ),
 116           ),
 117           'frequency_unit' => array(
 118             'title' => ts('Frequency Unit'),
 119           ),
 120           'installments' => array(
 121             'title' => ts('Installments'),
 122           ),
 123           'pledge_create_date' => array(
 124             'title' => ts('Pledge Made Date'),
 125           ),
 126           'start_date' => array(
 127             'title' => ts('Pledge Start Date'),
 128             'type' => CRM_Utils_Type::T_DATE,
 129           ),
 130           'end_date' => array(
 131             'title' => ts('Pledge End Date'),
 132             'type' => CRM_Utils_Type::T_DATE,
 133           ),
 134           'status_id' => array(
 135             'title' => ts('Pledge Status'),
 136           ),
 137         ),
 138         'filters' => array(
 139           'pledge_create_date' => array(
 140             'title' => ts('Pledge Made Date'),
 141             'operatorType' => CRM_Report_Form::OP_DATE,
 142           ),
 143           'pledge_amount' => array(
 144             'title' => ts('Pledged Amount'),
 145             'operatorType' => CRM_Report_Form::OP_INT,
 146           ),
 147           'currency' => array(
 148             'title' => ts('Currency'),
 149             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 150             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 151             'default' => NULL,
 152             'type' => CRM_Utils_Type::T_STRING,
 153           ),
 154           'sid' => array(
 155             'name' => 'status_id',
 156             'title' => ts('Pledge Status'),
 157             'type' => CRM_Utils_Type::T_INT,
 158             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 159             'options' => CRM_Core_OptionGroup::values('contribution_status'),
 160           ),
 161           'financial_type_id' => array(
 162             'title' => ts('Financial Type'),
 163             'type' => CRM_Utils_Type::T_INT,
 164             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 165             'options' => CRM_Contribute_PseudoConstant::financialType(),
 166           ),
 167         ),
 168         'group_bys' => array(
 169           'pledge_create_date' => array(
 170             'frequency' => TRUE,
 171             'default' => TRUE,
 172             'chart' => TRUE,
 173           ),
 174           'frequency_unit' => array(
 175             'title' => ts('Frequency Unit'),
 176           ),
 177           'status_id' => array(
 178             'title' => ts('Pledge Status'),
 179           ),
 180           'financial_type_id' => array(
 181             'title' => ts('Financial Type'),
 182           ),
 183         ),
 184       ),
 185       'civicrm_pledge_payment' => array(
 186         'dao' => 'CRM_Pledge_DAO_PledgePayment',
 187         'fields' => array(
 188           'total_paid' => array(
 189             'title' => ts('Total Amount Paid'),
 190             'type' => CRM_Utils_Type::T_STRING,
 191             'dbAlias' => 'sum(pledge_payment_civireport.actual_amount)',
 192           ),
 193         ),
 194       ),
 195     ) + $this->addAddressFields();
 196
 197     $this->_groupFilter = TRUE;
 198     $this->_tagFilter = TRUE;
 199     $this->_currencyColumn = 'civicrm_pledge_currency';
 200     parent::__construct();
 201   }
 202
 203   public function preProcess() {
 204     parent::preProcess();
 205   }
 206
 207   public function select() {
 208     parent::select();
 209   }
 210
 211   public function from() {
 212     $this->_from = "
 213             FROM civicrm_pledge {$this->_aliases['civicrm_pledge']}
 214                  LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 215                       ON ({$this->_aliases['civicrm_contact']}.id =
 216                           {$this->_aliases['civicrm_pledge']}.contact_id )
 217                  {$this->_aclFrom} ";
 218
 219     // include address field if address column is to be included
 220     if ($this->_addressField) {
 221       $this->_from .= "
 222                  LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
 223                            ON ({$this->_aliases['civicrm_contact']}.id =
 224                                {$this->_aliases['civicrm_address']}.contact_id) AND
 225                                {$this->_aliases['civicrm_address']}.is_primary = 1\n";
 226     }
 227
 228     // include email field if email column is to be included
 229     if ($this->_emailField) {
 230       $this->_from .= "
 231                  LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
 232                            ON ({$this->_aliases['civicrm_contact']}.id =
 233                                {$this->_aliases['civicrm_email']}.contact_id) AND
 234                                {$this->_aliases['civicrm_email']}.is_primary = 1\n";
 235     }
 236
 237     if (!empty($this->_params['fields']['total_paid'])) {
 238       $this->_from .= "
 239         LEFT JOIN civicrm_pledge_payment {$this->_aliases['civicrm_pledge_payment']} ON
 240           {$this->_aliases['civicrm_pledge']}.id = {$this->_aliases['civicrm_pledge_payment']}.pledge_id
 241           AND {$this->_aliases['civicrm_pledge_payment']}.status_id = 1
 242       ";
 243     }
 244   }
 245
 246   public function groupBy() {
 247     $this->_groupBy = "";
 248     $append = FALSE;
 249
 250     if (is_array($this->_params['group_bys']) &&
 251       !empty($this->_params['group_bys'])
 252     ) {
 253       foreach ($this->_columns as $tableName => $table) {
 254         if (array_key_exists('group_bys', $table)) {
 255           foreach ($table['group_bys'] as $fieldName => $field) {
 256             if (!empty($this->_params['group_bys'][$fieldName])) {
 257               if (!empty($field['chart'])) {
 258                 $this->assign('chartSupported', TRUE);
 259               }
 260
 261               if (!empty($table['group_bys'][$fieldName]['frequency']) &&
 262                 !empty($this->_params['group_bys_freq'][$fieldName])
 263               ) {
 264
 265                 $append = "YEAR({$field['dbAlias']}),";
 266                 if (in_array(strtolower($this->_params['group_bys_freq'][$fieldName]),
 267                   array('year')
 268                 )) {
 269                   $append = '';
 270                 }
 271                 $this->_groupBy[] = "$append {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
 272                 $append = TRUE;
 273               }
 274               else {
 275                 $this->_groupBy[] = $field['dbAlias'];
 276               }
 277             }
 278           }
 279         }
 280       }
 281
 282       if (!empty($this->_statFields) &&
 283         (($append && count($this->_groupBy) <= 1) || (!$append)) &&
 284         !$this->_having
 285       ) {
 286         $this->_rollup = " WITH ROLLUP";
 287       }
 288       $groupBy = $this->_groupBy;
 289       $this->_groupBy = "GROUP BY " . implode(', ', $this->_groupBy);
 290     }
 291     else {
 292       $groupBy = "{$this->_aliases['civicrm_contact']}.id";
 293       $this->_groupBy = "GROUP BY {$groupBy}";
 294     }
 295     $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, $groupBy);
 296     $this->_groupBy .= " {$this->_rollup}";
 297   }
 298
 299   /**
 300    * @param $rows
 301    *
 302    * @return array
 303    */
 304   public function statistics(&$rows) {
 305     $statistics = parent::statistics($rows);
 306
 307     if (!$this->_having) {
 308       $select = "
 309             SELECT COUNT({$this->_aliases['civicrm_pledge']}.amount )       as count,
 310                    SUM({$this->_aliases['civicrm_pledge']}.amount )         as amount,
 311                    ROUND(AVG({$this->_aliases['civicrm_pledge']}.amount), 2) as avg
 312             ";
 313
 314       $sql = "{$select} {$this->_from} {$this->_where}";
 315
 316       $dao = CRM_Core_DAO::executeQuery($sql);
 317
 318       if ($dao->fetch()) {
 319         $statistics['count']['amount'] = array(
 320           'value' => $dao->amount,
 321           'title' => ts('Total Pledged'),
 322           'type' => CRM_Utils_Type::T_MONEY,
 323         );
 324         $statistics['count']['count '] = array(
 325           'value' => $dao->count,
 326           'title' => ts('Total No Pledges'),
 327         );
 328         $statistics['count']['avg   '] = array(
 329           'value' => $dao->avg,
 330           'title' => ts('Average'),
 331           'type' => CRM_Utils_Type::T_MONEY,
 332         );
 333       }
 334     }
 335     return $statistics;
 336   }
 337
 338   public function where() {
 339     $clauses = array();
 340     foreach ($this->_columns as $tableName => $table) {
 341       if (array_key_exists('filters', $table)) {
 342         foreach ($table['filters'] as $fieldName => $field) {
 343           $clause = NULL;
 344           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 345             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 346             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 347             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 348
 349             if ($relative || $from || $to) {
 350               $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 351             }
 352           }
 353           else {
 354             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 355             if ($op) {
 356               $clause = $this->whereClause($field,
 357                 $op,
 358                 CRM_Utils_Array::value("{$fieldName}_value",
 359                   $this->_params
 360                 ),
 361                 CRM_Utils_Array::value("{$fieldName}_min",
 362                   $this->_params
 363                 ),
 364                 CRM_Utils_Array::value("{$fieldName}_max",
 365                   $this->_params
 366                 )
 367               );
 368             }
 369           }
 370
 371           if (!empty($clause)) {
 372             $clauses[] = $clause;
 373           }
 374         }
 375       }
 376     }
 377     if (empty($clauses)) {
 378       $this->_where = "WHERE ({$this->_aliases['civicrm_pledge']}.is_test=0 ) ";
 379     }
 380     else {
 381       $this->_where = "WHERE  ({$this->_aliases['civicrm_pledge']}.is_test=0 )  AND
 382                                       " . implode(' AND ', $clauses);
 383     }
 384
 385     if ($this->_aclWhere) {
 386       $this->_where .= " AND {$this->_aclWhere} ";
 387     }
 388   }
 389
 390   public function postProcess() {
 391     parent::postProcess();
 392   }
 393
 394   /**
 395    * Alter display of rows.
 396    *
 397    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 398    * such as rendering contacts as links.
 399    *
 400    * @param array $rows
 401    *   Rows generated by SQL, with an array for each row.
 402    */
 403   public function alterDisplay(&$rows) {
 404     $entryFound = FALSE;
 405     $checkList = array();
 406     $display_flag = $prev_cid = $cid = 0;
 407     foreach ($rows as $rowNum => $row) {
 408
 409       // convert display name to links
 410       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 411         array_key_exists('civicrm_contact_id', $row)
 412       ) {
 413         $url = CRM_Utils_System::url("civicrm/contact/view",
 414           'reset=1&cid=' . $row['civicrm_contact_id'],
 415           $this->_absoluteUrl
 416         );
 417         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 418         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
 419         $entryFound = TRUE;
 420       }
 421
 422       if (array_key_exists('civicrm_pledge_financial_type_id', $row)) {
 423         if ($value = $row['civicrm_pledge_financial_type_id']) {
 424           $rows[$rowNum]['civicrm_pledge_financial_type_id'] = CRM_Contribute_PseudoConstant::financialType($value, FALSE);
 425         }
 426         $entryFound = TRUE;
 427       }
 428
 429       //handle status id
 430       if (array_key_exists('civicrm_pledge_status_id', $row)) {
 431         if ($value = $row['civicrm_pledge_status_id']) {
 432           $rows[$rowNum]['civicrm_pledge_status_id'] = CRM_Contribute_PseudoConstant::contributionStatus($value);
 433         }
 434         $entryFound = TRUE;
 435       }
 436
 437       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'pledge/summary', 'List all pledge(s) for this ') ? TRUE : $entryFound;
 438
 439       // skip looking further in rows, if first row itself doesn't
 440       // have the column we need
 441       if (!$entryFound) {
 442         break;
 443       }
 444     }
 445   }
 446
 447 }