CRM/Report/Form/Pledge/Summary.php

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