CRM/Pledge/BAO/PledgePayment.php

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