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