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