CRM/Report/Form/Pledge/Summary.php

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