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