CRM/Pledge/BAO/PledgePayment.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_Pledge_BAO_PledgePayment extends CRM_Pledge_DAO_PledgePayment {
  18
  19   /**
  20    * Class constructor.
  21    */
  22   public function __construct() {
  23     parent::__construct();
  24   }
  25
  26   /**
  27    * Get pledge payment details.
  28    *
  29    * @param int $pledgeId
  30    *   Pledge id.
  31    *
  32    * @return array
  33    *   associated array of pledge payment details
  34    */
  35   public static function getPledgePayments($pledgeId) {
  36     $query = "
  37 SELECT    civicrm_pledge_payment.id id,
  38           scheduled_amount,
  39           scheduled_date,
  40           reminder_date,
  41           reminder_count,
  42           actual_amount,
  43           receive_date,
  44           civicrm_pledge_payment.currency,
  45           civicrm_option_value.name as status,
  46           civicrm_option_value.label as label,
  47           civicrm_contribution.id as contribution_id
  48 FROM      civicrm_pledge_payment
  49
  50 LEFT JOIN civicrm_contribution ON civicrm_pledge_payment.contribution_id = civicrm_contribution.id
  51 LEFT JOIN civicrm_option_group ON ( civicrm_option_group.name = 'contribution_status' )
  52 LEFT JOIN civicrm_option_value ON ( civicrm_pledge_payment.status_id = civicrm_option_value.value AND
  53                                     civicrm_option_group.id = civicrm_option_value.option_group_id )
  54 WHERE     pledge_id = %1
  55 ";
  56
  57     $params[1] = [$pledgeId, 'Integer'];
  58     $payment = CRM_Core_DAO::executeQuery($query, $params);
  59
  60     $paymentDetails = [];
  61     while ($payment->fetch()) {
  62       $paymentDetails[$payment->id]['scheduled_amount'] = $payment->scheduled_amount;
  63       $paymentDetails[$payment->id]['scheduled_date'] = $payment->scheduled_date;
  64       $paymentDetails[$payment->id]['reminder_date'] = $payment->reminder_date;
  65       $paymentDetails[$payment->id]['reminder_count'] = $payment->reminder_count;
  66       $paymentDetails[$payment->id]['total_amount'] = $payment->actual_amount;
  67       $paymentDetails[$payment->id]['receive_date'] = $payment->receive_date;
  68       $paymentDetails[$payment->id]['status'] = $payment->status;
  69       $paymentDetails[$payment->id]['label'] = $payment->label;
  70       $paymentDetails[$payment->id]['id'] = $payment->id;
  71       $paymentDetails[$payment->id]['contribution_id'] = $payment->contribution_id;
  72       $paymentDetails[$payment->id]['currency'] = $payment->currency;
  73     }
  74
  75     return $paymentDetails;
  76   }
  77
  78   /**
  79    * @param array $params
  80    *
  81    * @return pledge
  82    */
  83   public static function create($params) {
  84     $transaction = new CRM_Core_Transaction();
  85     $overdueStatusID = CRM_Core_PseudoConstant::getKey('CRM_Pledge_BAO_PledgePayment', 'status_id', 'Overdue');
  86     $pendingStatusId = CRM_Core_PseudoConstant::getKey('CRM_Pledge_BAO_PledgePayment', 'status_id', 'Pending');
  87
  88     //calculate the scheduled date for every installment
  89     $now = date('Ymd') . '000000';
  90     $statues = $prevScheduledDate = [];
  91     $prevScheduledDate[1] = CRM_Utils_Date::processDate($params['scheduled_date']);
  92
  93     if (CRM_Utils_Date::overdue($prevScheduledDate[1], $now)) {
  94       $statues[1] = $overdueStatusID;
  95     }
  96     else {
  97       $statues[1] = $pendingStatusId;
  98     }
  99
 100     for ($i = 1; $i < $params['installments']; $i++) {
 101       $prevScheduledDate[$i + 1] = self::calculateNextScheduledDate($params, $i);
 102       if (CRM_Utils_Date::overdue($prevScheduledDate[$i + 1], $now)) {
 103         $statues[$i + 1] = $overdueStatusID;
 104       }
 105       else {
 106         $statues[$i + 1] = $pendingStatusId;
 107       }
 108     }
 109
 110     if ($params['installment_amount']) {
 111       $params['scheduled_amount'] = $params['installment_amount'];
 112     }
 113     else {
 114       $params['scheduled_amount'] = round(($params['amount'] / $params['installments']), 2);
 115     }
 116
 117     for ($i = 1; $i <= $params['installments']; $i++) {
 118       // calculate the scheduled amount for every installment.
 119       if ($i == $params['installments']) {
 120         $params['scheduled_amount'] = $params['amount'] - ($i - 1) * $params['scheduled_amount'];
 121       }
 122       if (!isset($params['contribution_id']) && $params['installments'] > 1) {
 123         $params['status_id'] = $statues[$i];
 124       }
 125
 126       $params['scheduled_date'] = $prevScheduledDate[$i];
 127       $payment = self::add($params);
 128       if (is_a($payment, 'CRM_Core_Error')) {
 129         $transaction->rollback();
 130         return $payment;
 131       }
 132
 133       // we should add contribution id to only first payment record
 134       if (isset($params['contribution_id'])) {
 135         unset($params['contribution_id']);
 136         unset($params['actual_amount']);
 137       }
 138     }
 139
 140     // update pledge status
 141     self::updatePledgePaymentStatus($params['pledge_id']);
 142
 143     $transaction->commit();
 144     return $payment;
 145   }
 146
 147   /**
 148    * Add pledge payment.
 149    *
 150    * @param array $params
 151    *   Associate array of field.
 152    *
 153    * @return CRM_Pledge_DAO_PledgePayment
 154    *   pledge payment id
 155    */
 156   public static function add($params) {
 157     // set currency for CRM-1496
 158     if (empty($params['id']) && !isset($params['currency'])) {
 159       $params['currency'] = CRM_Core_Config::singleton()->defaultCurrency;
 160     }
 161     return self::writeRecord($params);
 162   }
 163
 164   /**
 165    * Retrieve DB object based on input parameters.
 166    *
 167    * It also stores all the retrieved values in the default array.
 168    *
 169    * @param array $params
 170    *   (reference ) an assoc array of name/value pairs.
 171    * @param array $defaults
 172    *   (reference ) an assoc array to hold the flattened values.
 173    *
 174    * @return CRM_Pledge_BAO_PledgePayment
 175    */
 176   public static function retrieve(&$params, &$defaults) {
 177     $payment = new CRM_Pledge_BAO_PledgePayment();
 178     $payment->copyValues($params);
 179     if ($payment->find(TRUE)) {
 180       CRM_Core_DAO::storeValues($payment, $defaults);
 181       return $payment;
 182     }
 183     return NULL;
 184   }
 185
 186   /**
 187    * Delete pledge payment.
 188    *
 189    * @param int $id
 190    *
 191    * @return int
 192    *   pledge payment id
 193    */
 194   public static function del($id) {
 195     $payment = new CRM_Pledge_DAO_PledgePayment();
 196     $payment->id = $id;
 197     if ($payment->find()) {
 198       $payment->fetch();
 199
 200       CRM_Utils_Hook::pre('delete', 'PledgePayment', $id, $payment);
 201
 202       $result = $payment->delete();
 203
 204       CRM_Utils_Hook::post('delete', 'PledgePayment', $id, $payment);
 205
 206       return $result;
 207     }
 208     else {
 209       return FALSE;
 210     }
 211   }
 212
 213   /**
 214    * Delete all pledge payments.
 215    *
 216    * @param int $id
 217    *   Pledge id.
 218    *
 219    * @return bool
 220    */
 221   public static function deletePayments($id) {
 222     if (!CRM_Utils_Rule::positiveInteger($id)) {
 223       return FALSE;
 224     }
 225
 226     $transaction = new CRM_Core_Transaction();
 227
 228     $payment = new CRM_Pledge_DAO_PledgePayment();
 229     $payment->pledge_id = $id;
 230
 231     if ($payment->find()) {
 232       while ($payment->fetch()) {
 233         //also delete associated contribution.
 234         if ($payment->contribution_id) {
 235           CRM_Contribute_BAO_Contribution::deleteContribution($payment->contribution_id);
 236         }
 237         self::del($payment->id);
 238       }
 239     }
 240
 241     $transaction->commit();
 242
 243     return TRUE;
 244   }
 245
 246   /**
 247    * On delete contribution record update associated pledge payment and pledge.
 248    *
 249    * @param int $contributionID
 250    *   Contribution id.
 251    *
 252    * @return bool
 253    */
 254   public static function resetPledgePayment($contributionID) {
 255     $transaction = new CRM_Core_Transaction();
 256
 257     $payment = new CRM_Pledge_DAO_PledgePayment();
 258     $payment->contribution_id = $contributionID;
 259     if ($payment->find(TRUE)) {
 260       $payment->contribution_id = 'null';
 261       $payment->status_id = CRM_Core_PseudoConstant::getKey('CRM_Pledge_BAO_Pledge', 'status_id', 'Pending');
 262       $payment->scheduled_date = NULL;
 263       $payment->reminder_date = NULL;
 264       $payment->scheduled_amount = $payment->actual_amount;
 265       $payment->actual_amount = 'null';
 266       $payment->save();
 267
 268       //update pledge status.
 269       $pledgeID = $payment->pledge_id;
 270       $pledgeStatusID = self::calculatePledgeStatus($pledgeID);
 271       CRM_Core_DAO::setFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'status_id', $pledgeStatusID);
 272
 273     }
 274
 275     $transaction->commit();
 276     return TRUE;
 277   }
 278
 279   /**
 280    * Update Pledge Payment Status.
 281    *
 282    * @param int $pledgeID
 283    *   Id of pledge.
 284    * @param array $paymentIDs
 285    *   Ids of pledge payment(s) to update.
 286    * @param int $paymentStatusID
 287    *   Payment status to set.
 288    * @param int $pledgeStatusID
 289    *   Pledge status to change (if needed).
 290    * @param float|int $actualAmount , actual amount being paid
 291    * @param bool $adjustTotalAmount
 292    *   Is amount being paid different from scheduled amount?.
 293    * @param bool $isScriptUpdate
 294    *   Is function being called from bin script?.
 295    *
 296    * @return int
 297    *   $newStatus, updated status id (or 0)
 298    */
 299   public static function updatePledgePaymentStatus(
 300     $pledgeID,
 301     $paymentIDs = NULL,
 302     $paymentStatusID = NULL,
 303     $pledgeStatusID = NULL,
 304     $actualAmount = 0,
 305     $adjustTotalAmount = FALSE,
 306     $isScriptUpdate = FALSE
 307   ) {
 308     $totalAmountClause = '';
 309     $paymentContributionId = NULL;
 310     $editScheduled = FALSE;
 311
 312     // get all statuses
 313     $allStatus = CRM_Core_OptionGroup::values('pledge_status',
 314       FALSE, FALSE, FALSE, NULL, 'name', TRUE
 315     );
 316
 317     // if we get do not get contribution id means we are editing the scheduled payment.
 318     if (!empty($paymentIDs)) {
 319       $editScheduled = FALSE;
 320       $payments = implode(',', $paymentIDs);
 321       $paymentContributionId = CRM_Core_DAO::getFieldValue('CRM_Pledge_DAO_PledgePayment',
 322         $payments,
 323         'contribution_id',
 324         'id'
 325       );
 326
 327       if (!$paymentContributionId) {
 328         $editScheduled = TRUE;
 329       }
 330     }
 331
 332     // if payment ids are passed, we update payment table first, since payments statuses are not dependent on pledge status
 333     $pledgeStatusName = CRM_Core_PseudoConstant::getName('CRM_Pledge_BAO_Pledge', 'status_id', $pledgeStatusID);
 334     if ((!empty($paymentIDs) || $pledgeStatusName == 'Cancelled') && (!$editScheduled || $isScriptUpdate)) {
 335       if ($pledgeStatusName == 'Cancelled') {
 336         $paymentStatusID = $pledgeStatusID;
 337       }
 338
 339       self::updatePledgePayments($pledgeID, $paymentStatusID, $paymentIDs, $actualAmount, $paymentContributionId, $isScriptUpdate);
 340     }
 341     if (!empty($paymentIDs) && $actualAmount) {
 342       $payments = implode(',', $paymentIDs);
 343       $pledgeScheduledAmount = CRM_Core_DAO::getFieldValue('CRM_Pledge_DAO_PledgePayment',
 344         $payments,
 345         'scheduled_amount',
 346         'id'
 347       );
 348
 349       $pledgeStatusId = self::calculatePledgeStatus($pledgeID);
 350       // Actual Pledge Amount
 351       $actualPledgeAmount = CRM_Core_DAO::getFieldValue('CRM_Pledge_DAO_Pledge',
 352         $pledgeID,
 353         'amount',
 354         'id'
 355       );
 356       // while editing scheduled  we need to check if we are editing last pending
 357       $lastPending = FALSE;
 358       if (!$paymentContributionId) {
 359         $checkPendingCount = self::getOldestPledgePayment($pledgeID, 2);
 360         if ($checkPendingCount['count'] == 1) {
 361           $lastPending = TRUE;
 362         }
 363       }
 364
 365       // check if this is the last payment and adjust the actual amount.
 366       if ($pledgeStatusId && $pledgeStatusId == array_search('Completed', $allStatus) || $lastPending) {
 367         // last scheduled payment
 368         if ($actualAmount >= $pledgeScheduledAmount) {
 369           $adjustTotalAmount = TRUE;
 370         }
 371         elseif (!$adjustTotalAmount) {
 372           // actual amount is less than the scheduled amount, so enter new pledge payment record
 373           $pledgeFrequencyUnit = CRM_Core_DAO::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'frequency_unit', 'id');
 374           $pledgeFrequencyInterval = CRM_Core_DAO::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'frequency_interval', 'id');
 375           $pledgeScheduledDate = CRM_Core_DAO::getFieldValue('CRM_Pledge_DAO_PledgePayment', $payments, 'scheduled_date', 'id');
 376           $scheduled_date = CRM_Utils_Date::processDate($pledgeScheduledDate);
 377           $date['year'] = (int) substr($scheduled_date, 0, 4);
 378           $date['month'] = (int) substr($scheduled_date, 4, 2);
 379           $date['day'] = (int) substr($scheduled_date, 6, 2);
 380           $newDate = date('YmdHis', mktime(0, 0, 0, $date['month'], $date['day'], $date['year']));
 381           $ScheduledDate = CRM_Utils_Date::format(CRM_Utils_Date::intervalAdd($pledgeFrequencyUnit,
 382             $pledgeFrequencyInterval, $newDate
 383           ));
 384           $pledgeParams = [
 385             'status_id' => array_search('Pending', $allStatus),
 386             'pledge_id' => $pledgeID,
 387             'scheduled_amount' => ($pledgeScheduledAmount - $actualAmount),
 388             'scheduled_date' => $ScheduledDate,
 389           ];
 390           $payment = self::add($pledgeParams);
 391           // while editing schedule,  after adding a new pledge payemnt update the scheduled amount of the current payment
 392           if (!$paymentContributionId) {
 393             CRM_Core_DAO::setFieldValue('CRM_Pledge_DAO_PledgePayment', $payments, 'scheduled_amount', $actualAmount);
 394           }
 395         }
 396       }
 397       elseif (!$adjustTotalAmount) {
 398         // not last schedule amount and also not selected to adjust Total
 399         $paymentContributionId = CRM_Core_DAO::getFieldValue('CRM_Pledge_DAO_PledgePayment',
 400           $payments,
 401           'contribution_id',
 402           'id'
 403         );
 404         self::adjustPledgePayment($pledgeID, $actualAmount, $pledgeScheduledAmount, $paymentContributionId, $payments, $paymentStatusID);
 405         // while editing schedule,  after adding a new pledge payemnt update the scheduled amount of the current payment
 406         if (!$paymentContributionId) {
 407           CRM_Core_DAO::setFieldValue('CRM_Pledge_DAO_PledgePayment', $payments, 'scheduled_amount', $actualAmount);
 408         }
 409         // after adjusting all payments check if the actual amount was greater than the actual remaining amount , if so then update the total pledge amount.
 410         $pledgeStatusId = self::calculatePledgeStatus($pledgeID);
 411         $balanceQuery = "
 412  SELECT sum( civicrm_pledge_payment.actual_amount )
 413  FROM civicrm_pledge_payment
 414  WHERE civicrm_pledge_payment.pledge_id = %1
 415  AND civicrm_pledge_payment.status_id = 1
 416  ";
 417         $totalPaidParams = [1 => [$pledgeID, 'Integer']];
 418         $totalPaidAmount = CRM_Core_DAO::singleValueQuery($balanceQuery, $totalPaidParams);
 419         $remainingTotalAmount = ($actualPledgeAmount - $totalPaidAmount);
 420         if (($pledgeStatusId && $pledgeStatusId == array_search('Completed', $allStatus)) && (($actualAmount > $remainingTotalAmount) || ($actualAmount >= $actualPledgeAmount))) {
 421           $totalAmountClause = ", civicrm_pledge.amount = {$totalPaidAmount}";
 422         }
 423       }
 424       if ($adjustTotalAmount) {
 425         $newTotalAmount = ($actualPledgeAmount + ($actualAmount - $pledgeScheduledAmount));
 426         $totalAmountClause = ", civicrm_pledge.amount = {$newTotalAmount}";
 427         if (!$paymentContributionId) {
 428           CRM_Core_DAO::setFieldValue('CRM_Pledge_DAO_PledgePayment', $payments, 'scheduled_amount', $actualAmount);
 429         }
 430       }
 431     }
 432
 433     $cancelDateClause = $endDateClause = NULL;
 434     // update pledge and payment status if status is Completed/Cancelled.
 435     if ($pledgeStatusID && $pledgeStatusID == array_search('Cancelled', $allStatus)) {
 436       $paymentStatusID = $pledgeStatusID;
 437       $cancelDateClause = ", civicrm_pledge.cancel_date = CURRENT_TIMESTAMP ";
 438     }
 439     else {
 440       // get pledge status
 441       $pledgeStatusID = self::calculatePledgeStatus($pledgeID);
 442     }
 443
 444     if ($pledgeStatusID == array_search('Completed', $allStatus)) {
 445       $endDateClause = ", civicrm_pledge.end_date = CURRENT_TIMESTAMP ";
 446     }
 447
 448     // update pledge status
 449     $query = "
 450 UPDATE civicrm_pledge
 451  SET   civicrm_pledge.status_id = %1
 452        {$cancelDateClause} {$endDateClause} {$totalAmountClause}
 453 WHERE  civicrm_pledge.id = %2
 454 ";
 455
 456     $params = [
 457       1 => [$pledgeStatusID, 'Integer'],
 458       2 => [$pledgeID, 'Integer'],
 459     ];
 460
 461     CRM_Core_DAO::executeQuery($query, $params);
 462
 463     return $pledgeStatusID;
 464   }
 465
 466   /**
 467    * Calculate the base scheduled date. This function effectively 'rounds' the $params['scheduled_date'] value
 468    * to the first payment date with respect to the frequency day  - ie. if payments are on the 15th of the month the date returned
 469    * will be the 15th of the relevant month. Then to calculate the payments you can use intervalAdd ie.
 470    * CRM_Utils_Date::intervalAdd( $params['frequency_unit'], $i * ($params['frequency_interval']) , calculateBaseScheduledDate( &$params )))
 471    *
 472    * @param array $params
 473    *
 474    * @return array
 475    *   Next scheduled date as an array
 476    */
 477   public static function calculateBaseScheduleDate(&$params) {
 478     $date = [];
 479     $scheduled_date = CRM_Utils_Date::processDate($params['scheduled_date']);
 480     $date['year'] = (int) substr($scheduled_date, 0, 4);
 481     $date['month'] = (int) substr($scheduled_date, 4, 2);
 482     $date['day'] = (int) substr($scheduled_date, 6, 2);
 483     // calculation of schedule date according to frequency day of period
 484     // frequency day is not applicable for daily installments
 485     if ($params['frequency_unit'] != 'day' && $params['frequency_unit'] != 'year') {
 486       if ($params['frequency_unit'] != 'week') {
 487         // CRM-18316: To calculate pledge scheduled dates at the end of a month.
 488         $date['day'] = $params['frequency_day'];
 489         $lastDayOfMonth = date('t', mktime(0, 0, 0, $date['month'], 1, $date['year']));
 490         if ($lastDayOfMonth < $date['day']) {
 491           $date['day'] = $lastDayOfMonth;
 492         }
 493       }
 494       elseif ($params['frequency_unit'] == 'week') {
 495
 496         // for week calculate day of week ie. Sunday,Monday etc. as next payment date
 497         $dayOfWeek = date('w', mktime(0, 0, 0, $date['month'], $date['day'], $date['year']));
 498         $frequencyDay = $params['frequency_day'] - $dayOfWeek;
 499
 500         $scheduleDate = explode("-", date('n-j-Y', mktime(0, 0, 0, $date['month'],
 501           $date['day'] + $frequencyDay, $date['year']
 502         )));
 503         $date['month'] = $scheduleDate[0];
 504         $date['day'] = $scheduleDate[1];
 505         $date['year'] = $scheduleDate[2];
 506       }
 507     }
 508     $newdate = date('YmdHis', mktime(0, 0, 0, $date['month'], $date['day'], $date['year']));
 509     return $newdate;
 510   }
 511
 512   /**
 513    * Calculate next scheduled pledge payment date. Function calculates next pledge payment date.
 514    *
 515    * @param array $params
 516    *   must include frequency unit & frequency interval
 517    * @param int $paymentNo
 518    *   number of payment in sequence (e.g. 1 for first calculated payment (treat initial payment as 0)
 519    * @param string $basePaymentDate
 520    *   date to calculate payments from. This would normally be the
 521    *   first day of the pledge (default) & is calculated off the 'scheduled date' param. Returned date will
 522    *   be equal to basePaymentDate normalised to fit the 'pledge pattern' + number of installments
 523    *
 524    * @return string
 525    *   formatted date
 526    */
 527   public static function calculateNextScheduledDate(&$params, $paymentNo, $basePaymentDate = NULL) {
 528     $interval = $paymentNo * ($params['frequency_interval']);
 529     if (!$basePaymentDate) {
 530       $basePaymentDate = self::calculateBaseScheduleDate($params);
 531     }
 532
 533     //CRM-18316 - change $basePaymentDate for the end dates of the month eg: 29, 30 or 31.
 534     if ($params['frequency_unit'] == 'month' && in_array($params['frequency_day'], [29, 30, 31])) {
 535       $frequency = $params['frequency_day'];
 536       extract(date_parse($basePaymentDate));
 537       $lastDayOfMonth = date('t', mktime($hour, $minute, $second, $month + $interval, 1, $year));
 538       // Take the last day in case the current month is Feb or frequency_day is set to 31.
 539       if (in_array($lastDayOfMonth, [28, 29]) || $frequency == 31) {
 540         $frequency = 0;
 541         $interval++;
 542       }
 543       $basePaymentDate = [
 544         'M' => $month,
 545         'd' => $frequency,
 546         'Y' => $year,
 547       ];
 548     }
 549
 550     return CRM_Utils_Date::format(
 551       CRM_Utils_Date::intervalAdd(
 552         $params['frequency_unit'],
 553         $interval,
 554         $basePaymentDate
 555       )
 556     );
 557   }
 558
 559   /**
 560    * Calculate the pledge status.
 561    *
 562    * @param int $pledgeId
 563    *   Pledge id.
 564    *
 565    * @return int
 566    *   $statusId calculated status id of pledge
 567    */
 568   public static function calculatePledgeStatus($pledgeId) {
 569     $paymentStatusTypes = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name');
 570     $pledgeStatusTypes = CRM_Pledge_BAO_Pledge::buildOptions('status_id', 'validate');
 571
 572     //return if the pledge is cancelled.
 573     $currentPledgeStatusId = CRM_Core_DAO::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeId, 'status_id', 'id', TRUE);
 574     if ($currentPledgeStatusId == array_search('Cancelled', $pledgeStatusTypes)) {
 575       return $currentPledgeStatusId;
 576     }
 577
 578     // retrieve all pledge payments for this particular pledge
 579     $allPledgePayments = $allStatus = [];
 580     $returnProperties = ['status_id'];
 581     CRM_Core_DAO::commonRetrieveAll('CRM_Pledge_DAO_PledgePayment', 'pledge_id', $pledgeId, $allPledgePayments, $returnProperties);
 582
 583     // build pledge payment statuses
 584     foreach ($allPledgePayments as $key => $value) {
 585       $allStatus[$value['id']] = $paymentStatusTypes[$value['status_id']];
 586     }
 587
 588     if (array_search('Overdue', $allStatus)) {
 589       $statusId = array_search('Overdue', $pledgeStatusTypes);
 590     }
 591     elseif (array_search('Completed', $allStatus)) {
 592       if (count(array_count_values($allStatus)) == 1) {
 593         $statusId = array_search('Completed', $pledgeStatusTypes);
 594       }
 595       else {
 596         $statusId = array_search('In Progress', $pledgeStatusTypes);
 597       }
 598     }
 599     else {
 600       $statusId = array_search('Pending', $pledgeStatusTypes);
 601     }
 602
 603     return $statusId;
 604   }
 605
 606   /**
 607    * Update pledge payment table.
 608    *
 609    * @param int $pledgeId
 610    *   Pledge id.
 611    * @param int $paymentStatusId
 612    *   Payment status id to set.
 613    * @param array $paymentIds
 614    *   Payment ids to be updated.
 615    * @param float|int $actualAmount , actual amount being paid
 616    * @param int $contributionId
 617    *   , Id of associated contribution when payment is recorded.
 618    * @param bool $isScriptUpdate
 619    *   , is function being called from bin script?.
 620    *
 621    */
 622   public static function updatePledgePayments(
 623     $pledgeId,
 624     $paymentStatusId,
 625     $paymentIds = NULL,
 626     $actualAmount = 0,
 627     $contributionId = NULL,
 628     $isScriptUpdate = FALSE
 629   ) {
 630     $allStatus = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name');
 631     $paymentClause = NULL;
 632     if (!empty($paymentIds)) {
 633       $payments = implode(',', $paymentIds);
 634       $paymentClause = " AND civicrm_pledge_payment.id IN ( {$payments} )";
 635     }
 636     elseif ($paymentStatusId == array_search('Cancelled', $allStatus)) {
 637       $completedStatus = array_search('Completed', $allStatus);
 638       $paymentClause = " AND civicrm_pledge_payment.status_id != {$completedStatus}";
 639     }
 640     $actualAmountClause = NULL;
 641     $contributionIdClause = NULL;
 642     if (isset($contributionId) && !$isScriptUpdate) {
 643       $contributionIdClause = ", civicrm_pledge_payment.contribution_id = {$contributionId}";
 644       $actualAmountClause = ", civicrm_pledge_payment.actual_amount = {$actualAmount}";
 645     }
 646
 647     $query = "
 648 UPDATE civicrm_pledge_payment
 649 SET    civicrm_pledge_payment.status_id = {$paymentStatusId}
 650        {$actualAmountClause} {$contributionIdClause}
 651 WHERE  civicrm_pledge_payment.pledge_id = %1
 652        {$paymentClause}
 653 ";
 654
 655     CRM_Core_DAO::executeQuery($query, [1 => [$pledgeId, 'Integer']]);
 656   }
 657
 658   /**
 659    * Update pledge payment table when reminder is sent.
 660    *
 661    * @param int $paymentId
 662    *   Payment id.
 663    */
 664   public static function updateReminderDetails($paymentId) {
 665     $query = "
 666 UPDATE civicrm_pledge_payment
 667 SET civicrm_pledge_payment.reminder_date = CURRENT_TIMESTAMP,
 668     civicrm_pledge_payment.reminder_count = civicrm_pledge_payment.reminder_count + 1
 669 WHERE  civicrm_pledge_payment.id = {$paymentId}
 670 ";
 671     $dao = CRM_Core_DAO::executeQuery($query);
 672   }
 673
 674   /**
 675    * Get oldest pending or in progress pledge payments.
 676    *
 677    * @param int $pledgeID
 678    *   Pledge id.
 679    *
 680    * @param int $limit
 681    *
 682    * @return array
 683    *   associated array of pledge details
 684    */
 685   public static function getOldestPledgePayment($pledgeID, $limit = 1) {
 686     // get pending / overdue statuses
 687     $pledgeStatuses = CRM_Core_OptionGroup::values('pledge_status',
 688       FALSE, FALSE, FALSE, NULL, 'name'
 689     );
 690
 691     // get pending and overdue payments
 692     $status[] = array_search('Pending', $pledgeStatuses);
 693     $status[] = array_search('Overdue', $pledgeStatuses);
 694
 695     $statusClause = " IN (" . implode(',', $status) . ")";
 696
 697     $query = "
 698 SELECT civicrm_pledge_payment.id id, civicrm_pledge_payment.scheduled_amount amount, civicrm_pledge_payment.currency, civicrm_pledge_payment.scheduled_date,civicrm_pledge.financial_type_id
 699 FROM civicrm_pledge, civicrm_pledge_payment
 700 WHERE civicrm_pledge.id = civicrm_pledge_payment.pledge_id
 701   AND civicrm_pledge_payment.status_id {$statusClause}
 702   AND civicrm_pledge.id = %1
 703 ORDER BY civicrm_pledge_payment.scheduled_date ASC
 704 LIMIT 0, %2
 705 ";
 706
 707     $params[1] = [$pledgeID, 'Integer'];
 708     $params[2] = [$limit, 'Integer'];
 709     $payment = CRM_Core_DAO::executeQuery($query, $params);
 710     $count = 1;
 711     $paymentDetails = [];
 712     while ($payment->fetch()) {
 713       $paymentDetails[] = [
 714         'id' => $payment->id,
 715         'amount' => $payment->amount,
 716         'currency' => $payment->currency,
 717         'schedule_date' => $payment->scheduled_date,
 718         'financial_type_id' => $payment->financial_type_id,
 719         'count' => $count,
 720       ];
 721       $count++;
 722     }
 723     return end($paymentDetails);
 724   }
 725
 726   /**
 727    * @param int $pledgeID
 728    * @param $actualAmount
 729    * @param $pledgeScheduledAmount
 730    * @param int $paymentContributionId
 731    * @param int $pPaymentId
 732    * @param int $paymentStatusID
 733    */
 734   public static function adjustPledgePayment($pledgeID, $actualAmount, $pledgeScheduledAmount, $paymentContributionId = NULL, $pPaymentId = NULL, $paymentStatusID = NULL) {
 735     $allStatus = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name');
 736     $paymentStatusName = CRM_Core_PseudoConstant::getName('CRM_Pledge_BAO_PledgePayment', 'status_id', $paymentStatusID);
 737     if ($paymentStatusName == 'Cancelled'|| $paymentStatusName == 'Refunded') {
 738       $query = "
 739 SELECT civicrm_pledge_payment.id id
 740 FROM  civicrm_pledge_payment
 741 WHERE civicrm_pledge_payment.contribution_id = {$paymentContributionId}
 742 ";
 743       $paymentsAffected = CRM_Core_DAO::executeQuery($query);
 744       $paymentIDs = [];
 745       while ($paymentsAffected->fetch()) {
 746         $paymentIDs[] = $paymentsAffected->id;
 747       }
 748       // Reset the affected values by the amount paid more than the scheduled amount
 749       foreach ($paymentIDs as $key => $value) {
 750         $payment = new CRM_Pledge_DAO_PledgePayment();
 751         $payment->id = $value;
 752         if ($payment->find(TRUE)) {
 753           $payment->contribution_id = 'null';
 754           $payment->status_id = array_search('Pending', $allStatus);
 755           $payment->scheduled_date = NULL;
 756           $payment->reminder_date = NULL;
 757           $payment->scheduled_amount = $pledgeScheduledAmount;
 758           $payment->actual_amount = 'null';
 759           $payment->save();
 760         }
 761       }
 762
 763       // Cancel the initial paid amount
 764       CRM_Core_DAO::setFieldValue('CRM_Pledge_DAO_PledgePayment', reset($paymentIDs), 'status_id', $paymentStatusID, 'id');
 765       CRM_Core_DAO::setFieldValue('CRM_Pledge_DAO_PledgePayment', reset($paymentIDs), 'actual_amount', $actualAmount, 'id');
 766
 767       // Add new payment after the last payment for the pledge
 768       $allPayments = self::getPledgePayments($pledgeID);
 769       $lastPayment = array_pop($allPayments);
 770
 771       $pledgeFrequencyUnit = CRM_Core_DAO::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'frequency_unit', 'id');
 772       $pledgeFrequencyInterval = CRM_Core_DAO::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'frequency_interval', 'id');
 773       $pledgeScheduledDate = $lastPayment['scheduled_date'];
 774       $scheduled_date = CRM_Utils_Date::processDate($pledgeScheduledDate);
 775       $date['year'] = (int) substr($scheduled_date, 0, 4);
 776       $date['month'] = (int) substr($scheduled_date, 4, 2);
 777       $date['day'] = (int) substr($scheduled_date, 6, 2);
 778       $newDate = date('YmdHis', mktime(0, 0, 0, $date['month'], $date['day'], $date['year']));
 779       $ScheduledDate = CRM_Utils_Date::format(CRM_Utils_Date::intervalAdd($pledgeFrequencyUnit, $pledgeFrequencyInterval, $newDate));
 780       $pledgeParams = [
 781         'status_id' => array_search('Pending', $allStatus),
 782         'pledge_id' => $pledgeID,
 783         'scheduled_amount' => $pledgeScheduledAmount,
 784         'scheduled_date' => $ScheduledDate,
 785       ];
 786       $payment = self::add($pledgeParams);
 787     }
 788     else {
 789       $nextPledgeInstallmentDue = self::getOldestPledgePayment($pledgeID);
 790       if (!$paymentContributionId) {
 791         // means we are editing payment scheduled payment, so get the second pending to update.
 792         $nextPledgeInstallmentDue = self::getOldestPledgePayment($pledgeID, 2);
 793         if (($nextPledgeInstallmentDue['count'] != 1) && ($nextPledgeInstallmentDue['id'] == $pPaymentId)) {
 794           $nextPledgeInstallmentDue = self::getOldestPledgePayment($pledgeID);
 795         }
 796       }
 797
 798       if ($nextPledgeInstallmentDue) {
 799         // not the last scheduled payment and the actual amount is less than the expected , add it to oldest pending.
 800         if (($actualAmount != $pledgeScheduledAmount) && (($actualAmount < $pledgeScheduledAmount) || (($actualAmount - $pledgeScheduledAmount) < $nextPledgeInstallmentDue['amount']))) {
 801           $oldScheduledAmount = $nextPledgeInstallmentDue['amount'];
 802           $newScheduledAmount = $oldScheduledAmount + ($pledgeScheduledAmount - $actualAmount);
 803           // store new amount in oldest pending payment record.
 804           CRM_Core_DAO::setFieldValue('CRM_Pledge_DAO_PledgePayment',
 805             $nextPledgeInstallmentDue['id'],
 806             'scheduled_amount',
 807             $newScheduledAmount
 808           );
 809           if (CRM_Core_DAO::getFieldValue('CRM_Pledge_DAO_PledgePayment', $nextPledgeInstallmentDue['id'], 'contribution_id', 'id')) {
 810             CRM_Core_DAO::setFieldValue('CRM_Pledge_DAO_PledgePayment',
 811               $nextPledgeInstallmentDue['id'],
 812               'contribution_id',
 813               $paymentContributionId
 814             );
 815           }
 816         }
 817         elseif (($actualAmount > $pledgeScheduledAmount) && (($actualAmount - $pledgeScheduledAmount) >= $nextPledgeInstallmentDue['amount'])) {
 818           // here the actual amount is greater than expected and also greater than the next installment amount, so update the next installment as complete and again add it to next subsequent pending payment
 819           // set the actual amount of the next pending to '0', set contribution Id to current contribution Id and status as completed
 820           $paymentId = [$nextPledgeInstallmentDue['id']];
 821           self::updatePledgePayments($pledgeID, array_search('Completed', $allStatus), $paymentId, 0, $paymentContributionId);
 822           CRM_Core_DAO::setFieldValue('CRM_Pledge_DAO_PledgePayment', $nextPledgeInstallmentDue['id'], 'scheduled_amount', 0, 'id');
 823           if (!$paymentContributionId) {
 824             // means we are editing payment scheduled payment.
 825             $oldestPaymentAmount = self::getOldestPledgePayment($pledgeID, 2);
 826           }
 827           $newActualAmount = round(($actualAmount - $pledgeScheduledAmount), CRM_Utils_Money::getCurrencyPrecision());
 828           $newPledgeScheduledAmount = $nextPledgeInstallmentDue['amount'];
 829           if (!$paymentContributionId) {
 830             $newActualAmount = ($actualAmount - $pledgeScheduledAmount);
 831             $newPledgeScheduledAmount = $oldestPaymentAmount['amount'];
 832             // means we are editing payment scheduled payment, so update scheduled amount.
 833             CRM_Core_DAO::setFieldValue('CRM_Pledge_DAO_PledgePayment',
 834               $oldestPaymentAmount['id'],
 835               'scheduled_amount',
 836               $newActualAmount
 837             );
 838           }
 839           if ($newActualAmount > 0) {
 840             self::adjustPledgePayment($pledgeID, $newActualAmount, $newPledgeScheduledAmount, $paymentContributionId);
 841           }
 842         }
 843       }
 844     }
 845   }
 846
 847   /**
 848    * Override buildOptions to hack out some statuses.
 849    *
 850    * @todo instead of using & hacking the shared optionGroup contribution_status use a separate one.
 851    *
 852    * @param string $fieldName
 853    * @param string $context
 854    * @param array $props
 855    *
 856    * @return array|bool
 857    */
 858   public static function buildOptions($fieldName, $context = NULL, $props = []) {
 859     $result = parent::buildOptions($fieldName, $context, $props);
 860     if ($fieldName == 'status_id') {
 861       $result = CRM_Pledge_BAO_Pledge::buildOptions($fieldName, $context, $props);
 862       $result = array_diff($result, ['Failed', 'In Progress']);
 863     }
 864     return $result;
 865   }
 866
 867 }