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