CRM/Report/Form/Pledge/Summary.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.4                                                |
   6  +--------------------------------------------------------------------+
   7  | Copyright CiviCRM LLC (c) 2004-2013                                |
   8  +--------------------------------------------------------------------+
   9  | This file is a part of CiviCRM.                                    |
  10  |                                                                    |
  11  | CiviCRM is free software; you can copy, modify, and distribute it  |
  12  | under the terms of the GNU Affero General Public License           |
  13  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  14  |                                                                    |
  15  | CiviCRM is distributed in the hope that it will be useful, but     |
  16  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  17  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  18  | See the GNU Affero General Public License for more details.        |
  19  |                                                                    |
  20  | You should have received a copy of the GNU Affero General Public   |
  21  | License and the CiviCRM Licensing Exception along                  |
  22  | with this program; if not, contact CiviCRM LLC                     |
  23  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  24  | GNU Affero General Public License or the licensing of CiviCRM,     |
  25  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  26  +--------------------------------------------------------------------+
  27 */
  28
  29 /**
  30  *
  31  * @package CRM
  32  * @copyright CiviCRM LLC (c) 2004-2013
  33  * $Id$
  34  *
  35  */
  36 class CRM_Report_Form_Pledge_Summary extends CRM_Report_Form {
  37
  38   protected $_summary = NULL;
  39   protected $_totalPaid = FALSE;
  40   protected $_customGroupExtends = array('Pledge', 'Individual');
  41   protected $_customGroupGroupBy = TRUE;
  42   protected $_addressField = FALSE;
  43   protected $_emailField = FALSE;
  44
  45   function __construct() {
  46     $this->_columns = array(
  47       'civicrm_contact' =>
  48       array(
  49         'dao' => 'CRM_Contact_DAO_Contact',
  50         'fields' =>
  51         array(
  52           'sort_name' =>
  53           array('title' => ts('Contact Name'),
  54             'no_repeat' => TRUE,
  55           ),
  56           'postal_greeting_display' =>
  57           array('title' => ts('Postal Greeting')),
  58           'id' =>
  59           array(
  60             'no_display' => TRUE,
  61             'required' => TRUE,
  62           ),
  63         ),
  64         'grouping' => 'contact-fields',
  65         'group_bys' =>
  66         array(
  67           'id' =>
  68           array('title' => ts('Contact ID')),
  69           'sort_name' =>
  70           array('title' => ts('Contact Name'),
  71           ),
  72         ),
  73       ),
  74       'civicrm_email' =>
  75       array(
  76         'dao' => 'CRM_Core_DAO_Email',
  77         'fields' =>
  78         array(
  79           'email' =>
  80           array(
  81             'no_repeat' => TRUE,
  82             'title' => ts('email'),
  83           ),
  84         ),
  85         'grouping' => 'contact-fields',
  86       ),
  87       'civicrm_pledge' =>
  88       array(
  89         'dao' => 'CRM_Pledge_DAO_Pledge',
  90         'fields' =>
  91         array(
  92           'id' =>
  93           array(
  94             'no_display' => TRUE,
  95             'required' => FALSE,
  96           ),
  97           'currency' => array(
  98             'required' => TRUE,
  99             'no_display' => TRUE,
 100           ),
 101           'amount' =>
 102           array('title' => ts('Pledge Amount'),
 103             'required' => TRUE,
 104             'type' => CRM_Utils_Type::T_MONEY,
 105             'statistics' =>
 106             array('sum' => ts('Aggregate Amount Pledged'),
 107               'count' => ts('Pledges'),
 108               'avg' => ts('Average'),
 109             ),
 110           ),
 111           'frequency_unit' =>
 112           array('title' => ts('Frequency Unit'),
 113           ),
 114           'installments' =>
 115           array('title' => ts('Installments'),
 116           ),
 117           'pledge_create_date' =>
 118           array('title' => ts('Pledge Made Date'),
 119           ),
 120           'start_date' =>
 121           array('title' => ts('Pledge Start Date'),
 122             'type' => CRM_Utils_Type::T_DATE,
 123           ),
 124           'end_date' =>
 125           array('title' => ts('Pledge End Date'),
 126             'type' => CRM_Utils_Type::T_DATE,
 127           ),
 128           'status_id' =>
 129           array('title' => ts('Pledge Status'),
 130           ),
 131         ),
 132         'filters' =>
 133         array(
 134           'pledge_create_date' =>
 135           array(
 136             'title' => 'Pledge Made Date',
 137             'operatorType' => CRM_Report_Form::OP_DATE,
 138           ),
 139           'pledge_amount' =>
 140           array('title' => ts('Pledged Amount'),
 141             'operatorType' => CRM_Report_Form::OP_INT,
 142           ),
 143           'currency' =>
 144           array('title' => 'Currency',
 145             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 146             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 147             'default' => NULL,
 148             'type' => CRM_Utils_Type::T_STRING,
 149           ),
 150           'sid' =>
 151           array(
 152             'name' => 'status_id',
 153             'title' => ts('Pledge Status'),
 154             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 155             'options' => CRM_Core_OptionGroup::values('contribution_status'),
 156           ),
 157         ),
 158         'group_bys' =>
 159         array(
 160           'pledge_create_date' =>
 161           array(
 162             'frequency' => TRUE,
 163             'default' => TRUE,
 164             'chart' => TRUE,
 165           ),
 166           'frequency_unit' =>
 167           array('title' => ts('Frequency Unit'),
 168           ),
 169           'status_id' =>
 170           array('title' => ts('Pledge Status'),
 171           ),
 172         ),
 173       ),
 174       'civicrm_pledge_payment' =>
 175       array(
 176         'dao' => 'CRM_Pledge_DAO_PledgePayment',
 177         'fields' =>
 178         array(
 179           'total_paid' =>
 180             array(
 181               'title' => ts('Total Amount Paid'),
 182               'type' => CRM_Utils_Type::T_STRING,
 183               'dbAlias' => 'sum(pledge_payment_civireport.actual_amount)',
 184             ),
 185         ),
 186       ),
 187       'civicrm_group' =>
 188       array(
 189         'dao' => 'CRM_Contact_DAO_Group',
 190         'alias' => 'cgroup',
 191         'filters' =>
 192         array(
 193           'gid' =>
 194           array(
 195             'name' => 'group_id',
 196             'title' => ts(' Group'),
 197             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 198             'group' => TRUE,
 199             'options' => CRM_Core_PseudoConstant::group(),
 200           ),
 201         ),
 202       ),
 203     ) + $this->addAddressFields();
 204
 205     $this->_tagFilter = TRUE;
 206     $this->_currencyColumn = 'civicrm_pledge_currency';
 207     parent::__construct();
 208   }
 209
 210   function preProcess() {
 211     parent::preProcess();
 212   }
 213
 214   function select() {
 215     parent::select();
 216   }
 217
 218   function from() {
 219     $this->_from = "
 220             FROM civicrm_pledge {$this->_aliases['civicrm_pledge']}
 221                  LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 222                       ON ({$this->_aliases['civicrm_contact']}.id =
 223                           {$this->_aliases['civicrm_pledge']}.contact_id )
 224                  {$this->_aclFrom} ";
 225
 226     // include address field if address column is to be included
 227     if ($this->_addressField) {
 228       $this->_from .= "
 229                  LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
 230                            ON ({$this->_aliases['civicrm_contact']}.id =
 231                                {$this->_aliases['civicrm_address']}.contact_id) AND
 232                                {$this->_aliases['civicrm_address']}.is_primary = 1\n";
 233     }
 234
 235     // include email field if email column is to be included
 236     if ($this->_emailField) {
 237       $this->_from .= "
 238                  LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
 239                            ON ({$this->_aliases['civicrm_contact']}.id =
 240                                {$this->_aliases['civicrm_email']}.contact_id) AND
 241                                {$this->_aliases['civicrm_email']}.is_primary = 1\n";
 242     }
 243
 244     if (!empty($this->_params['fields']['total_paid'])){
 245       $this->_from .= "
 246         LEFT JOIN civicrm_pledge_payment {$this->_aliases['civicrm_pledge_payment']} ON
 247           {$this->_aliases['civicrm_pledge']}.id = {$this->_aliases['civicrm_pledge_payment']}.pledge_id
 248           AND {$this->_aliases['civicrm_pledge_payment']}.status_id = 1
 249       ";
 250     }
 251   }
 252
 253   function groupBy() {
 254     $this->_groupBy = "";
 255     $append = FALSE;
 256
 257     if (is_array($this->_params['group_bys']) &&
 258       !empty($this->_params['group_bys'])
 259     ) {
 260       foreach ($this->_columns as $tableName => $table) {
 261         if (array_key_exists('group_bys', $table)) {
 262           foreach ($table['group_bys'] as $fieldName => $field) {
 263             if (!empty($this->_params['group_bys'][$fieldName])) {
 264               if (!empty($field['chart'])) {
 265                 $this->assign('chartSupported', TRUE);
 266               }
 267
 268               if (!empty($table['group_bys'][$fieldName]['frequency']) && !empty($this->_params['group_bys_freq'][$fieldName])) {
 269
 270                 $append = "YEAR({$field['dbAlias']}),";
 271                 if (in_array(strtolower($this->_params['group_bys_freq'][$fieldName]),
 272                     array('year')
 273                   )) {
 274                   $append = '';
 275                 }
 276                 $this->_groupBy[] = "$append {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
 277                 $append = TRUE;
 278               }
 279               else {
 280                 $this->_groupBy[] = $field['dbAlias'];
 281               }
 282             }
 283           }
 284         }
 285       }
 286
 287       if (!empty($this->_statFields) &&
 288         (($append && count($this->_groupBy) <= 1) || (!$append)) && !$this->_having
 289       ) {
 290         $this->_rollup = " WITH ROLLUP";
 291       }
 292       $this->_groupBy = "GROUP BY " . implode(', ', $this->_groupBy) . " {$this->_rollup} ";
 293     }
 294     else {
 295       $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contact']}.id";
 296     }
 297   }
 298
 299   function statistics(&$rows) {
 300     $statistics = parent::statistics($rows);
 301
 302     if (!$this->_having) {
 303       $select = "
 304             SELECT COUNT({$this->_aliases['civicrm_pledge']}.amount )       as count,
 305                    SUM({$this->_aliases['civicrm_pledge']}.amount )         as amount,
 306                    ROUND(AVG({$this->_aliases['civicrm_pledge']}.amount), 2) as avg
 307             ";
 308
 309       $sql = "{$select} {$this->_from} {$this->_where}";
 310
 311       $dao = CRM_Core_DAO::executeQuery($sql);
 312
 313       if ($dao->fetch()) {
 314         $statistics['count']['amount'] = array(
 315           'value' => $dao->amount,
 316           'title' => 'Total Pledged',
 317           'type' => CRM_Utils_Type::T_MONEY,
 318         );
 319         $statistics['count']['count '] = array(
 320           'value' => $dao->count,
 321           'title' => 'Total No Pledges',
 322         );
 323         $statistics['count']['avg   '] = array(
 324           'value' => $dao->avg,
 325           'title' => 'Average',
 326           'type' => CRM_Utils_Type::T_MONEY,
 327         );
 328       }
 329     }
 330     return $statistics;
 331   }
 332
 333   function where() {
 334     $clauses = array();
 335     foreach ($this->_columns as $tableName => $table) {
 336       if (array_key_exists('filters', $table)) {
 337         foreach ($table['filters'] as $fieldName => $field) {
 338           $clause = NULL;
 339           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 340             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 341             $from     = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 342             $to       = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 343
 344             if ($relative || $from || $to) {
 345               $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 346             }
 347           }
 348           else {
 349             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 350             if ($op) {
 351               $clause = $this->whereClause($field,
 352                 $op,
 353                 CRM_Utils_Array::value("{$fieldName}_value",
 354                   $this->_params
 355                 ),
 356                 CRM_Utils_Array::value("{$fieldName}_min",
 357                   $this->_params
 358                 ),
 359                 CRM_Utils_Array::value("{$fieldName}_max",
 360                   $this->_params
 361                 )
 362               );
 363             }
 364           }
 365
 366           if (!empty($clause)) {
 367             $clauses[] = $clause;
 368           }
 369         }
 370       }
 371     }
 372     if (empty($clauses)) {
 373       $this->_where = "WHERE ({$this->_aliases['civicrm_pledge']}.is_test=0 ) ";
 374     }
 375     else {
 376       $this->_where = "WHERE  ({$this->_aliases['civicrm_pledge']}.is_test=0 )  AND
 377                                       " . implode(' AND ', $clauses);
 378     }
 379
 380     if ($this->_aclWhere) {
 381       $this->_where .= " AND {$this->_aclWhere} ";
 382     }
 383   }
 384
 385   function postProcess() {
 386     parent::postProcess();
 387   }
 388
 389   function alterDisplay(&$rows) {
 390     // custom code to alter rows
 391     $entryFound   = FALSE;
 392     $checkList    = array();
 393     $display_flag = $prev_cid = $cid = 0;
 394     crm_Core_error::Debug('$rows', $rows);
 395     foreach ($rows as $rowNum => $row) {
 396
 397       // convert display name to links
 398       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 399         array_key_exists('civicrm_contact_id', $row)
 400       ) {
 401         $url = CRM_Utils_System::url("civicrm/contact/view",
 402           'reset=1&cid=' . $row['civicrm_contact_id'],
 403           $this->_absoluteUrl
 404         );
 405         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 406         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
 407         $entryFound = TRUE;
 408       }
 409
 410       //handle status id
 411       if (array_key_exists('civicrm_pledge_status_id', $row)) {
 412         if ($value = $row['civicrm_pledge_status_id']) {
 413           $rows[$rowNum]['civicrm_pledge_status_id'] = CRM_Contribute_PseudoConstant::contributionStatus($value);
 414         }
 415         $entryFound = TRUE;
 416       }
 417
 418       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'pledge/summary', 'List all pledge(s) for this ') ? TRUE : $entryFound;
 419
 420       // skip looking further in rows, if first row itself doesn't
 421       // have the column we need
 422       if (!$entryFound) {
 423         break;
 424       }
 425     }
 426   }
 427 }
 428