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