CRM/Pledge/BAO/PledgePayment.php

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