CRM/Pledge/BAO/PledgePayment.php

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