CRM/Pledge/BAO/PledgePayment.php

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