CRM/Report/Form/Pledge/Detail.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2019                                |
   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  *   !!!!!!!!!!!!!!!!!!!!
  31  *     NB: this is named detail but behaves like a summary report.
  32  *   It is also accessed through the Pledge Summary link in the UI
  33  *   This should presumably be changed.
  34  *   ~ Doten
  35  *   !!!!!!!!!!!!!!!!!!!!
  36  *
  37  */
  38
  39 /**
  40  *
  41  * @package CRM
  42  * @copyright CiviCRM LLC (c) 2004-2019
  43  */
  44 class CRM_Report_Form_Pledge_Detail extends CRM_Report_Form {
  45
  46   protected $_summary = NULL;
  47   protected $_totalPaid = FALSE;
  48   protected $_pledgeStatuses = [];
  49   protected $_customGroupExtends = [
  50     'Pledge',
  51     'Individual',
  52   ];
  53
  54   /**
  55    * This report has not been optimised for group filtering.
  56    *
  57    * The functionality for group filtering has been improved but not
  58    * all reports have been adjusted to take care of it. This report has not
  59    * and will run an inefficient query until fixed.
  60    *
  61    * CRM-19170
  62    *
  63    * @var bool
  64    */
  65   protected $groupFilterNotOptimised = TRUE;
  66
  67   /**
  68    * Class constructor.
  69    */
  70   public function __construct() {
  71     $this->_pledgeStatuses = CRM_Core_OptionGroup::values('pledge_status',
  72       FALSE, FALSE, FALSE, NULL, 'name'
  73     );
  74
  75     $this->_columns = [
  76       'civicrm_contact' => [
  77         'dao' => 'CRM_Contact_DAO_Contact',
  78         'fields' => [
  79           'sort_name' => [
  80             'title' => ts('Contact Name'),
  81             'required' => TRUE,
  82             'no_repeat' => TRUE,
  83           ],
  84         ],
  85         'filters' => [
  86           'sort_name' => ['title' => ts('Contact Name')],
  87           'id' => ['no_display' => TRUE],
  88         ],
  89         'grouping' => 'contact-fields',
  90       ],
  91       'civicrm_email' => [
  92         'dao' => 'CRM_Core_DAO_Email',
  93         'fields' => [
  94           'email' => ['no_repeat' => TRUE],
  95         ],
  96         'grouping' => 'contact-fields',
  97       ],
  98       'civicrm_pledge' => [
  99         'dao' => 'CRM_Pledge_DAO_Pledge',
 100         'fields' => [
 101           'id' => [
 102             'no_display' => TRUE,
 103             'required' => TRUE,
 104           ],
 105           'contact_id' => [
 106             'no_display' => TRUE,
 107             'required' => TRUE,
 108           ],
 109           'financial_type_id' => [
 110             'title' => ts('Financial Type'),
 111           ],
 112           'amount' => [
 113             'title' => ts('Pledge Amount'),
 114             'required' => TRUE,
 115             'type' => CRM_Utils_Type::T_MONEY,
 116           ],
 117           'currency' => [
 118             'required' => TRUE,
 119             'no_display' => TRUE,
 120           ],
 121           'frequency_unit' => [
 122             'title' => ts('Frequency Unit'),
 123           ],
 124           'installments' => [
 125             'title' => ts('Installments'),
 126           ],
 127           'pledge_create_date' => [
 128             'title' => ts('Pledge Made Date'),
 129           ],
 130           'start_date' => [
 131             'title' => ts('Pledge Start Date'),
 132             'type' => CRM_Utils_Type::T_DATE,
 133           ],
 134           'end_date' => [
 135             'title' => ts('Pledge End Date'),
 136             'type' => CRM_Utils_Type::T_DATE,
 137           ],
 138           'status_id' => [
 139             'title' => ts('Pledge Status'),
 140             'required' => TRUE,
 141           ],
 142         ],
 143         'filters' => [
 144           'pledge_create_date' => [
 145             'title' => ts('Pledge Made Date'),
 146             'operatorType' => CRM_Report_Form::OP_DATE,
 147           ],
 148           'pledge_amount' => [
 149             'title' => ts('Pledged Amount'),
 150             'operatorType' => CRM_Report_Form::OP_INT,
 151           ],
 152           'currency' => [
 153             'title' => ts('Currency'),
 154             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 155             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 156             'default' => NULL,
 157             'type' => CRM_Utils_Type::T_STRING,
 158           ],
 159           'sid' => [
 160             'name' => 'status_id',
 161             'title' => ts('Pledge Status'),
 162             'type' => CRM_Utils_Type::T_INT,
 163             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 164             'options' => CRM_Core_OptionGroup::values('pledge_status'),
 165           ],
 166           'financial_type_id' => [
 167             'title' => ts('Financial Type'),
 168             'type' => CRM_Utils_Type::T_INT,
 169             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 170             'options' => CRM_Contribute_PseudoConstant::financialType(),
 171           ],
 172
 173         ],
 174       ],
 175       'civicrm_pledge_payment' => [
 176         'dao' => 'CRM_Pledge_DAO_PledgePayment',
 177         'fields' => [
 178           'total_paid' => [
 179             'title' => ts('Total Amount Paid'),
 180             'type' => CRM_Utils_Type::T_MONEY,
 181           ],
 182           'balance_due' => [
 183             'title' => ts('Balance Due'),
 184             'default' => TRUE,
 185             'type' => CRM_Utils_Type::T_MONEY,
 186           ],
 187         ],
 188       ],
 189     ];
 190
 191     $this->_columns += $this->getAddressColumns(['group_by' => FALSE]) + $this->getPhoneColumns();
 192
 193     // If we have a campaign, build out the relevant elements
 194     $this->addCampaignFields('civicrm_pledge', TRUE);
 195
 196     $this->_groupFilter = TRUE;
 197     $this->_tagFilter = TRUE;
 198     $this->_currencyColumn = 'civicrm_pledge_currency';
 199     parent::__construct();
 200   }
 201
 202   public function preProcess() {
 203     parent::preProcess();
 204   }
 205
 206   public function select() {
 207     parent::select();
 208   }
 209
 210   /**
 211    * If we are retrieving total paid we need to define the inclusion of pledge_payment.
 212    *
 213    * @param string $tableName
 214    * @param $tableKey
 215    * @param string $fieldName
 216    * @param $field
 217    *
 218    * @return bool|string
 219    */
 220   public function selectClause(&$tableName, $tableKey, &$fieldName, &$field) {
 221     if ($fieldName == 'total_paid') {
 222       // add pledge_payment join
 223       $this->_totalPaid = TRUE;
 224       $this->_columnHeaders["{$tableName}_{$fieldName}"] = [
 225         'title' => $field['title'],
 226         'type' => $field['type'],
 227       ];
 228       return "COALESCE(sum({$this->_aliases[$tableName]}.actual_amount), 0) as {$tableName}_{$fieldName}";
 229     }
 230     if ($fieldName == 'balance_due') {
 231       $cancelledStatus = array_search('Cancelled', $this->_pledgeStatuses);
 232       $completedStatus = array_search('Completed', $this->_pledgeStatuses);
 233       // add pledge_payment join
 234       $this->_totalPaid = TRUE;
 235       $this->_columnHeaders["{$tableName}_{$fieldName}"] = $field['title'];
 236       $this->_columnHeaders["{$tableName}_{$fieldName}"] = [
 237         'title' => $field['title'],
 238         'type' => $field['type'],
 239       ];
 240       return "IF({$this->_aliases['civicrm_pledge']}.status_id IN({$cancelledStatus}, $completedStatus), 0, COALESCE({$this->_aliases['civicrm_pledge']}.amount, 0) - COALESCE(sum({$this->_aliases[$tableName]}.actual_amount),0)) as {$tableName}_{$fieldName}";
 241     }
 242     return FALSE;
 243   }
 244
 245   public function groupBy() {
 246     parent::groupBy();
 247     if (empty($this->_groupBy) && $this->_totalPaid) {
 248       $groupBy = ["{$this->_aliases['civicrm_pledge']}.id", "{$this->_aliases['civicrm_pledge']}.currency"];
 249       $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy);
 250     }
 251   }
 252
 253   public function from() {
 254     $this->_from = "
 255             FROM civicrm_pledge {$this->_aliases['civicrm_pledge']}
 256                  LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 257                       ON ({$this->_aliases['civicrm_contact']}.id =
 258                           {$this->_aliases['civicrm_pledge']}.contact_id )
 259                  {$this->_aclFrom} ";
 260
 261     if ($this->_totalPaid) {
 262       $this->_from .= "
 263         LEFT JOIN civicrm_pledge_payment {$this->_aliases['civicrm_pledge_payment']} ON
 264           {$this->_aliases['civicrm_pledge']}.id = {$this->_aliases['civicrm_pledge_payment']}.pledge_id
 265           AND {$this->_aliases['civicrm_pledge_payment']}.status_id = 1
 266       ";
 267     }
 268
 269     $this->joinPhoneFromContact();
 270     $this->joinAddressFromContact();
 271     $this->joinEmailFromContact();
 272   }
 273
 274   /**
 275    * @param $rows
 276    *
 277    * @return array
 278    */
 279   public function statistics(&$rows) {
 280     $statistics = parent::statistics($rows);
 281     //regenerate the from field without extra left join on pledge payments
 282     $totalPaid = $this->_totalPaid;
 283     $this->_totalPaid = FALSE;
 284     $this->from();
 285     $this->customDataFrom();
 286     if (!$this->_having) {
 287       $totalAmount = $average = [];
 288       $count = 0;
 289       $select = "
 290         SELECT COUNT({$this->_aliases['civicrm_pledge']}.amount )       as count,
 291           SUM({$this->_aliases['civicrm_pledge']}.amount )         as amount,
 292           ROUND(AVG({$this->_aliases['civicrm_pledge']}.amount), 2) as avg,
 293           {$this->_aliases['civicrm_pledge']}.currency as currency
 294         ";
 295
 296       $group = "GROUP BY {$this->_aliases['civicrm_pledge']}.currency";
 297       $sql = "{$select} {$this->_from} {$this->_where} {$group}";
 298       $dao = CRM_Core_DAO::executeQuery($sql);
 299       $count = $index = $totalCount = 0;
 300       // this will run once per currency
 301       while ($dao->fetch()) {
 302         $totalAmount = CRM_Utils_Money::format($dao->amount, $dao->currency);
 303         $average = CRM_Utils_Money::format($dao->avg, $dao->currency);
 304         $count = $dao->count;
 305         $totalCount .= $count;
 306         $statistics['counts']['amount' . $index] = [
 307           'title' => ts('Total Pledged') . ' (' . $dao->currency . ')',
 308           'value' => $totalAmount,
 309           'type' => CRM_Utils_Type::T_STRING,
 310         ];
 311         $statistics['counts']['avg' . $index] = [
 312           'title' => ts('Average') . ' (' . $dao->currency . ')',
 313           'value' => $average,
 314           'type' => CRM_Utils_Type::T_STRING,
 315         ];
 316         $statistics['counts']['count' . $index] = [
 317           'title' => ts('Total No Pledges') . ' (' . $dao->currency . ')',
 318           'value' => $count,
 319           'type' => CRM_Utils_Type::T_INT,
 320         ];
 321         $index++;
 322       }
 323       if ($totalCount > $count) {
 324         $statistics['counts']['count' . $index] = [
 325           'title' => ts('Total No Pledges'),
 326           'value' => $totalCount,
 327           'type' => CRM_Utils_Type::T_INT,
 328         ];
 329       }
 330     }
 331     // reset from clause
 332     if ($totalPaid) {
 333       $this->_totalPaid = TRUE;
 334       $this->from();
 335     }
 336     return $statistics;
 337   }
 338
 339   public function orderBy() {
 340     $this->_orderBy = "ORDER BY {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_contact']}.id";
 341   }
 342
 343   public function where() {
 344     $clauses = [];
 345     foreach ($this->_columns as $tableName => $table) {
 346       if (array_key_exists('filters', $table)) {
 347         foreach ($table['filters'] as $fieldName => $field) {
 348           $clause = NULL;
 349           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 350             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 351             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 352             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 353
 354             if ($relative || $from || $to) {
 355               $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 356             }
 357           }
 358           else {
 359             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 360             if ($op) {
 361               $clause = $this->whereClause($field,
 362                 $op,
 363                 CRM_Utils_Array::value("{$fieldName}_value",
 364                   $this->_params
 365                 ),
 366                 CRM_Utils_Array::value("{$fieldName}_min",
 367                   $this->_params
 368                 ),
 369                 CRM_Utils_Array::value("{$fieldName}_max",
 370                   $this->_params
 371                 )
 372               );
 373             }
 374           }
 375
 376           if (!empty($clause)) {
 377             $clauses[] = $clause;
 378           }
 379         }
 380       }
 381     }
 382     if (empty($clauses)) {
 383       $this->_where = "WHERE ({$this->_aliases['civicrm_pledge']}.is_test=0 ) ";
 384     }
 385     else {
 386       $this->_where = "WHERE  ({$this->_aliases['civicrm_pledge']}.is_test=0 )  AND
 387       " . implode(' AND ', $clauses);
 388     }
 389
 390     if ($this->_aclWhere) {
 391       $this->_where .= " AND {$this->_aclWhere} ";
 392     }
 393   }
 394
 395   public function postProcess() {
 396
 397     $this->beginPostProcess();
 398
 399     // get the acl clauses built before we assemble the query
 400     $this->buildACLClause($this->_aliases['civicrm_contact']);
 401     $sql = $this->buildQuery();
 402     $rows = $payment = [];
 403
 404     $dao = CRM_Core_DAO::executeQuery($sql);
 405
 406     // Set pager for the Main Query only which displays basic information
 407     $this->setPager();
 408     $this->assign('columnHeaders', $this->_columnHeaders);
 409
 410     while ($dao->fetch()) {
 411       $pledgeID = $dao->civicrm_pledge_id;
 412       foreach ($this->_columnHeaders as $columnHeadersKey => $columnHeadersValue) {
 413         $row = [];
 414         if (property_exists($dao, $columnHeadersKey)) {
 415           $display[$pledgeID][$columnHeadersKey] = $dao->$columnHeadersKey;
 416         }
 417       }
 418       $pledgeIDArray[] = $pledgeID;
 419     }
 420
 421     // Add Special headers
 422     $this->_columnHeaders['scheduled_date'] = [
 423       'type' => CRM_Utils_Type::T_DATE,
 424       'title' => ts('Next Payment Due'),
 425     ];
 426     $this->_columnHeaders['scheduled_amount'] = [
 427       'type' => CRM_Utils_Type::T_MONEY,
 428       'title' => ts('Next Payment Amount'),
 429     ];
 430     $this->_columnHeaders['status_id'] = NULL;
 431
 432     /*
 433      * this is purely about ordering the total paid & balance due fields off to the end
 434      * of the table in case custom or address fields cause them to fall in the middle
 435      * (arguably the pledge amount should be moved to after these fields too)
 436      *
 437      */
 438     $tableHeaders = [
 439       'civicrm_pledge_payment_total_paid',
 440       'civicrm_pledge_payment_balance_due',
 441     ];
 442
 443     foreach ($tableHeaders as $header) {
 444       //per above, unset & reset them so they move to the end
 445       if (isset($this->_columnHeaders[$header])) {
 446         $headervalue = $this->_columnHeaders[$header];
 447         unset($this->_columnHeaders[$header]);
 448         $this->_columnHeaders[$header] = $headervalue;
 449       }
 450     }
 451
 452     // To Display Payment Details of pledged amount
 453     // for pledge payments In Progress
 454     if (!empty($display)) {
 455       $statusId = array_keys(CRM_Core_PseudoConstant::accountOptionValues("contribution_status", NULL, " AND v.name IN  ('Pending', 'Overdue')"));
 456       $statusId = implode(',', $statusId);
 457       $select = "payment.pledge_id, payment.scheduled_amount, pledge.contact_id";
 458       $sqlPayment = "
 459                  SELECT min(payment.scheduled_date) as scheduled_date,
 460                         {$select}
 461
 462                   FROM civicrm_pledge_payment payment
 463                        LEFT JOIN civicrm_pledge pledge
 464                                  ON pledge.id = payment.pledge_id
 465
 466                   WHERE payment.status_id IN ({$statusId})
 467
 468                   GROUP BY {$select}";
 469
 470       $daoPayment = CRM_Core_DAO::executeQuery($sqlPayment);
 471
 472       while ($daoPayment->fetch()) {
 473         foreach ($pledgeIDArray as $key => $val) {
 474           if ($val == $daoPayment->pledge_id) {
 475
 476             $display[$daoPayment->pledge_id]['scheduled_date'] = $daoPayment->scheduled_date;
 477
 478             $display[$daoPayment->pledge_id]['scheduled_amount'] = $daoPayment->scheduled_amount;
 479           }
 480         }
 481       }
 482     }
 483
 484     // Displaying entire data on the form
 485     if (!empty($display)) {
 486       foreach ($display as $key => $value) {
 487         $row = [];
 488         foreach ($this->_columnHeaders as $columnKey => $columnValue) {
 489           if (array_key_exists($columnKey, $value)) {
 490             $row[$columnKey] = !empty($value[$columnKey]) ? $value[$columnKey] : '';
 491           }
 492         }
 493         $rows[] = $row;
 494       }
 495     }
 496
 497     unset($this->_columnHeaders['status_id']);
 498     unset($this->_columnHeaders['civicrm_pledge_id']);
 499     unset($this->_columnHeaders['civicrm_pledge_contact_id']);
 500
 501     $this->formatDisplay($rows, FALSE);
 502     $this->doTemplateAssignment($rows);
 503     $this->endPostProcess($rows);
 504   }
 505
 506   /**
 507    * Alter display of rows.
 508    *
 509    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 510    * such as rendering contacts as links.
 511    *
 512    * @param array $rows
 513    *   Rows generated by SQL, with an array for each row.
 514    */
 515   public function alterDisplay(&$rows) {
 516     $entryFound = FALSE;
 517     $checkList = [];
 518     $display_flag = $prev_cid = $cid = 0;
 519
 520     foreach ($rows as $rowNum => $row) {
 521       if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') {
 522         // don't repeat contact details if its same as the previous row
 523         if (array_key_exists('civicrm_pledge_contact_id', $row)) {
 524           if ($cid = $row['civicrm_pledge_contact_id']) {
 525             if ($rowNum == 0) {
 526               $prev_cid = $cid;
 527             }
 528             else {
 529               if ($prev_cid == $cid) {
 530                 $display_flag = 1;
 531                 $prev_cid = $cid;
 532               }
 533               else {
 534                 $display_flag = 0;
 535                 $prev_cid = $cid;
 536               }
 537             }
 538
 539             if ($display_flag) {
 540               foreach ($row as $colName => $colVal) {
 541                 if (in_array($colName, $this->_noRepeats)) {
 542                   unset($rows[$rowNum][$colName]);
 543                 }
 544               }
 545             }
 546             $entryFound = TRUE;
 547           }
 548         }
 549       }
 550
 551       // convert display name to links
 552       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 553         array_key_exists('civicrm_pledge_contact_id', $row)
 554       ) {
 555         $url = CRM_Utils_System::url("civicrm/contact/view",
 556           'reset=1&cid=' . $row['civicrm_pledge_contact_id'],
 557           $this->_absoluteUrl
 558         );
 559         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 560         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
 561         $entryFound = TRUE;
 562       }
 563
 564       if (array_key_exists('civicrm_pledge_financial_type_id', $row)) {
 565         if ($value = $row['civicrm_pledge_financial_type_id']) {
 566           $rows[$rowNum]['civicrm_pledge_financial_type_id'] = CRM_Contribute_PseudoConstant::financialType($value, FALSE);
 567         }
 568         $entryFound = TRUE;
 569       }
 570
 571       //handle status id
 572       if (array_key_exists('civicrm_pledge_status_id', $row)) {
 573         if ($value = $row['civicrm_pledge_status_id']) {
 574           $rows[$rowNum]['civicrm_pledge_status_id'] = CRM_Core_PseudoConstant::getLabel('CRM_Pledge_BAO_Pledge', 'status_id', $value);
 575         }
 576         $entryFound = TRUE;
 577       }
 578
 579       // If using campaigns, convert campaign_id to campaign title
 580       if (array_key_exists('civicrm_pledge_campaign_id', $row)) {
 581         if ($value = $row['civicrm_pledge_campaign_id']) {
 582           $rows[$rowNum]['civicrm_pledge_campaign_id'] = $this->campaigns[$value];
 583         }
 584         $entryFound = TRUE;
 585       }
 586
 587       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'pledge/detail', 'List all pledge(s) for this ') ? TRUE : $entryFound;
 588
 589       // skip looking further in rows, if first row itself doesn't
 590       // have the column we need
 591       if (!$entryFound) {
 592         break;
 593       }
 594     }
 595   }
 596
 597 }