3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2017 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2017
33 class CRM_Pledge_BAO_PledgePayment
extends CRM_Pledge_DAO_PledgePayment
{
38 public function __construct() {
39 parent
::__construct();
43 * Get pledge payment details.
45 * @param int $pledgeId
49 * associated array of pledge payment details
51 public static function getPledgePayments($pledgeId) {
53 SELECT civicrm_pledge_payment.id id,
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
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 )
73 $params[1] = array($pledgeId, 'Integer');
74 $payment = CRM_Core_DAO
::executeQuery($query, $params);
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
;
91 return $paymentDetails;
95 * @param array $params
99 public static function create($params) {
100 $transaction = new CRM_Core_Transaction();
101 $contributionStatus = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
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']);
108 if (CRM_Utils_Date
::overdue($prevScheduledDate[1], $now)) {
109 $statues[1] = array_search('Overdue', $contributionStatus);
112 $statues[1] = array_search('Pending', $contributionStatus);
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);
121 $statues[$i +
1] = array_search('Pending', $contributionStatus);
125 if ($params['installment_amount']) {
126 $params['scheduled_amount'] = $params['installment_amount'];
129 $params['scheduled_amount'] = round(($params['amount'] / $params['installments']), 2);
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'];
137 if (!isset($params['contribution_id']) && $params['installments'] > 1) {
138 $params['status_id'] = $statues[$i];
141 $params['scheduled_date'] = $prevScheduledDate[$i];
142 $payment = self
::add($params);
143 if (is_a($payment, 'CRM_Core_Error')) {
144 $transaction->rollback();
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']);
155 // update pledge status
156 self
::updatePledgePaymentStatus($params['pledge_id']);
158 $transaction->commit();
163 * Add pledge payment.
165 * @param array $params
166 * Associate array of field.
168 * @return CRM_Pledge_DAO_PledgePayment
171 public static function add($params) {
172 if (!empty($params['id'])) {
173 CRM_Utils_Hook
::pre('edit', 'PledgePayment', $params['id'], $params);
176 CRM_Utils_Hook
::pre('create', 'PledgePayment', NULL, $params);
179 $payment = new CRM_Pledge_DAO_PledgePayment();
180 $payment->copyValues($params);
182 // set currency for CRM-1496
183 if (!isset($payment->currency
)) {
184 $payment->currency
= CRM_Core_Config
::singleton()->defaultCurrency
;
187 $result = $payment->save();
189 if (!empty($params['id'])) {
190 CRM_Utils_Hook
::post('edit', 'PledgePayment', $payment->id
, $payment);
193 CRM_Utils_Hook
::post('create', 'PledgePayment', $payment->id
, $payment);
200 * Retrieve DB object based on input parameters.
202 * It also stores all the retrieved values in the default array.
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.
209 * @return CRM_Pledge_BAO_PledgePayment
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);
222 * Delete pledge payment.
229 public static function del($id) {
230 $payment = new CRM_Pledge_DAO_PledgePayment();
232 if ($payment->find()) {
235 CRM_Utils_Hook
::pre('delete', 'PledgePayment', $id, $payment);
237 $result = $payment->delete();
239 CRM_Utils_Hook
::post('delete', 'PledgePayment', $id, $payment);
249 * Delete all pledge payments.
256 public static function deletePayments($id) {
257 if (!CRM_Utils_Rule
::positiveInteger($id)) {
261 $transaction = new CRM_Core_Transaction();
263 $payment = new CRM_Pledge_DAO_PledgePayment();
264 $payment->pledge_id
= $id;
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
);
272 self
::del($payment->id
);
276 $transaction->commit();
282 * On delete contribution record update associated pledge payment and pledge.
284 * @param int $contributionID
289 public static function resetPledgePayment($contributionID) {
291 $allStatus = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
293 $transaction = new CRM_Core_Transaction();
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';
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);
314 $transaction->commit();
319 * Update Pledge Payment Status.
321 * @param int $pledgeID
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?.
336 * $newStatus, updated status id (or 0)
338 public static function updatePledgePaymentStatus(
341 $paymentStatusID = NULL,
342 $pledgeStatusID = NULL,
344 $adjustTotalAmount = FALSE,
345 $isScriptUpdate = FALSE
347 $totalAmountClause = '';
348 $paymentContributionId = NULL;
349 $editScheduled = FALSE;
352 $allStatus = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
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',
364 if (!$paymentContributionId) {
365 $editScheduled = TRUE;
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;
375 self
::updatePledgePayments($pledgeID, $paymentStatusID, $paymentIDs, $actualAmount, $paymentContributionId, $isScriptUpdate);
377 if (!empty($paymentIDs) && $actualAmount) {
378 $payments = implode(',', $paymentIDs);
379 $pledgeScheduledAmount = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_PledgePayment',
385 $pledgeStatusId = self
::calculatePledgeStatus($pledgeID);
386 // Actual Pledge Amount
387 $actualPledgeAmount = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge',
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) {
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;
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
420 $pledgeParams = array(
421 'status_id' => array_search('Pending', $allStatus),
422 'pledge_id' => $pledgeID,
423 'scheduled_amount' => ($pledgeScheduledAmount - $actualAmount),
424 'scheduled_date' => $ScheduledDate,
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);
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',
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);
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);
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
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}";
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);
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 ";
477 $pledgeStatusID = self
::calculatePledgeStatus($pledgeID);
480 if ($pledgeStatusID == array_search('Completed', $allStatus)) {
481 $endDateClause = ", civicrm_pledge.end_date = CURRENT_TIMESTAMP ";
484 // update pledge status
486 UPDATE civicrm_pledge
487 SET civicrm_pledge.status_id = %1
488 {$cancelDateClause} {$endDateClause} {$totalAmountClause}
489 WHERE civicrm_pledge.id = %2
493 1 => array($pledgeStatusID, 'Integer'),
494 2 => array($pledgeID, 'Integer'),
497 $dao = CRM_Core_DAO
::executeQuery($query, $params);
499 return $pledgeStatusID;
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 )))
508 * @param array $params
511 * Next scheduled date as an array
513 public static function calculateBaseScheduleDate(&$params) {
515 $scheduled_date = CRM_Utils_Date
::processDate($params['scheduled_date']);
516 $date['year'] = (int) substr($scheduled_date, 0, 4);
517 $date['month'] = (int) substr($scheduled_date, 4, 2);
518 $date['day'] = (int) substr($scheduled_date, 6, 2);
519 // calculation of schedule date according to frequency day of period
520 // frequency day is not applicable for daily installments
521 if ($params['frequency_unit'] != 'day' && $params['frequency_unit'] != 'year') {
522 if ($params['frequency_unit'] != 'week') {
523 // CRM-18316: To calculate pledge scheduled dates at the end of a month.
524 $date['day'] = $params['frequency_day'];
525 $lastDayOfMonth = date('t', mktime(0, 0, 0, $date['month'], 1, $date['year']));
526 if ($lastDayOfMonth < $date['day']) {
527 $date['day'] = $lastDayOfMonth;
530 elseif ($params['frequency_unit'] == 'week') {
532 // for week calculate day of week ie. Sunday,Monday etc. as next payment date
533 $dayOfWeek = date('w', mktime(0, 0, 0, $date['month'], $date['day'], $date['year']));
534 $frequencyDay = $params['frequency_day'] - $dayOfWeek;
536 $scheduleDate = explode("-", date('n-j-Y', mktime(0, 0, 0, $date['month'],
537 $date['day'] +
$frequencyDay, $date['year']
539 $date['month'] = $scheduleDate[0];
540 $date['day'] = $scheduleDate[1];
541 $date['year'] = $scheduleDate[2];
544 $newdate = date('YmdHis', mktime(0, 0, 0, $date['month'], $date['day'], $date['year']));
549 * Calculate next scheduled pledge payment date. Function calculates next pledge payment date.
551 * @param array $params
552 * must include frequency unit & frequency interval
553 * @param int $paymentNo
554 * number of payment in sequence (e.g. 1 for first calculated payment (treat initial payment as 0)
555 * @param string $basePaymentDate
556 * date to calculate payments from. This would normally be the
557 * first day of the pledge (default) & is calculated off the 'scheduled date' param. Returned date will
558 * be equal to basePaymentDate normalised to fit the 'pledge pattern' + number of installments
563 public static function calculateNextScheduledDate(&$params, $paymentNo, $basePaymentDate = NULL) {
564 $interval = $paymentNo * ($params['frequency_interval']);
565 if (!$basePaymentDate) {
566 $basePaymentDate = self
::calculateBaseScheduleDate($params);
569 //CRM-18316 - change $basePaymentDate for the end dates of the month eg: 29, 30 or 31.
570 if ($params['frequency_unit'] == 'month' && in_array($params['frequency_day'], array(29, 30, 31))) {
571 $frequency = $params['frequency_day'];
572 extract(date_parse($basePaymentDate));
573 $lastDayOfMonth = date('t', mktime($hour, $minute, $second, $month +
$interval, 1, $year));
574 // Take the last day in case the current month is Feb or frequency_day is set to 31.
575 if (in_array($lastDayOfMonth, array(28, 29)) ||
$frequency == 31) {
579 $basePaymentDate = array(
586 return CRM_Utils_Date
::format(
587 CRM_Utils_Date
::intervalAdd(
588 $params['frequency_unit'],
596 * Calculate the pledge status.
598 * @param int $pledgeId
602 * $statusId calculated status id of pledge
604 public static function calculatePledgeStatus($pledgeId) {
605 $paymentStatusTypes = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
607 //return if the pledge is cancelled.
608 $currentPledgeStatus = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeId, 'status_id', 'id', TRUE);
609 if ($currentPledgeStatus == array_search('Cancelled', $paymentStatusTypes)) {
610 return $currentPledgeStatus;
613 // retrieve all pledge payments for this particular pledge
614 $allPledgePayments = $allStatus = array();
615 $returnProperties = array('status_id');
616 CRM_Core_DAO
::commonRetrieveAll('CRM_Pledge_DAO_PledgePayment', 'pledge_id', $pledgeId, $allPledgePayments, $returnProperties);
618 // build pledge payment statuses
619 foreach ($allPledgePayments as $key => $value) {
620 $allStatus[$value['id']] = $paymentStatusTypes[$value['status_id']];
623 if (array_search('Overdue', $allStatus)) {
624 $statusId = array_search('Overdue', $paymentStatusTypes);
626 elseif (array_search('Completed', $allStatus)) {
627 if (count(array_count_values($allStatus)) == 1) {
628 $statusId = array_search('Completed', $paymentStatusTypes);
631 $statusId = array_search('In Progress', $paymentStatusTypes);
635 $statusId = array_search('Pending', $paymentStatusTypes);
642 * Update pledge payment table.
644 * @param int $pledgeId
646 * @param int $paymentStatusId
647 * Payment status id to set.
648 * @param array $paymentIds
649 * Payment ids to be updated.
650 * @param float|int $actualAmount , actual amount being paid
651 * @param int $contributionId
652 * , Id of associated contribution when payment is recorded.
653 * @param bool $isScriptUpdate
654 * , is function being called from bin script?.
657 public static function updatePledgePayments(
662 $contributionId = NULL,
663 $isScriptUpdate = FALSE
665 $allStatus = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
666 $paymentClause = NULL;
667 if (!empty($paymentIds)) {
668 $payments = implode(',', $paymentIds);
669 $paymentClause = " AND civicrm_pledge_payment.id IN ( {$payments} )";
671 elseif ($paymentStatusId == array_search('Cancelled', $allStatus)) {
672 $completedStatus = array_search('Completed', $allStatus);
673 $paymentClause = " AND civicrm_pledge_payment.status_id != {$completedStatus}";
675 $actualAmountClause = NULL;
676 $contributionIdClause = NULL;
677 if (isset($contributionId) && !$isScriptUpdate) {
678 $contributionIdClause = ", civicrm_pledge_payment.contribution_id = {$contributionId}";
679 $actualAmountClause = ", civicrm_pledge_payment.actual_amount = {$actualAmount}";
683 UPDATE civicrm_pledge_payment
684 SET civicrm_pledge_payment.status_id = {$paymentStatusId}
685 {$actualAmountClause} {$contributionIdClause}
686 WHERE civicrm_pledge_payment.pledge_id = %1
691 $params = array(1 => array($pledgeId, 'Integer'));
693 $dao = CRM_Core_DAO
::executeQuery($query, $params);
697 * Update pledge payment table when reminder is sent.
699 * @param int $paymentId
702 public static function updateReminderDetails($paymentId) {
704 UPDATE civicrm_pledge_payment
705 SET civicrm_pledge_payment.reminder_date = CURRENT_TIMESTAMP,
706 civicrm_pledge_payment.reminder_count = civicrm_pledge_payment.reminder_count + 1
707 WHERE civicrm_pledge_payment.id = {$paymentId}
709 $dao = CRM_Core_DAO
::executeQuery($query);
713 * Get oldest pending or in progress pledge payments.
715 * @param int $pledgeID
721 * associated array of pledge details
723 public static function getOldestPledgePayment($pledgeID, $limit = 1) {
724 // get pending / overdue statuses
725 $pledgeStatuses = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
727 // get pending and overdue payments
728 $status[] = array_search('Pending', $pledgeStatuses);
729 $status[] = array_search('Overdue', $pledgeStatuses);
731 $statusClause = " IN (" . implode(',', $status) . ")";
734 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
735 FROM civicrm_pledge, civicrm_pledge_payment
736 WHERE civicrm_pledge.id = civicrm_pledge_payment.pledge_id
737 AND civicrm_pledge_payment.status_id {$statusClause}
738 AND civicrm_pledge.id = %1
739 ORDER BY civicrm_pledge_payment.scheduled_date ASC
743 $params[1] = array($pledgeID, 'Integer');
744 $params[2] = array($limit, 'Integer');
745 $payment = CRM_Core_DAO
::executeQuery($query, $params);
747 $paymentDetails = array();
748 while ($payment->fetch()) {
749 $paymentDetails[] = array(
750 'id' => $payment->id
,
751 'amount' => $payment->amount
,
752 'currency' => $payment->currency
,
753 'schedule_date' => $payment->scheduled_date
,
754 'financial_type_id' => $payment->financial_type_id
,
759 return end($paymentDetails);
763 * @param int $pledgeID
764 * @param $actualAmount
765 * @param $pledgeScheduledAmount
766 * @param int $paymentContributionId
767 * @param int $pPaymentId
768 * @param int $paymentStatusID
770 public static function adjustPledgePayment($pledgeID, $actualAmount, $pledgeScheduledAmount, $paymentContributionId = NULL, $pPaymentId = NULL, $paymentStatusID = NULL) {
771 $allStatus = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
772 if ($paymentStatusID == array_search('Cancelled', $allStatus) ||
$paymentStatusID == array_search('Refunded', $allStatus)) {
774 SELECT civicrm_pledge_payment.id id
775 FROM civicrm_pledge_payment
776 WHERE civicrm_pledge_payment.contribution_id = {$paymentContributionId}
778 $paymentsAffected = CRM_Core_DAO
::executeQuery($query);
779 $paymentIDs = array();
780 while ($paymentsAffected->fetch()) {
781 $paymentIDs[] = $paymentsAffected->id
;
783 // Reset the affected values by the amount paid more than the scheduled amount
784 foreach ($paymentIDs as $key => $value) {
785 $payment = new CRM_Pledge_DAO_PledgePayment();
786 $payment->id
= $value;
787 if ($payment->find(TRUE)) {
788 $payment->contribution_id
= 'null';
789 $payment->status_id
= array_search('Pending', $allStatus);
790 $payment->scheduled_date
= NULL;
791 $payment->reminder_date
= NULL;
792 $payment->scheduled_amount
= $pledgeScheduledAmount;
793 $payment->actual_amount
= 'null';
798 // Cancel the initial paid amount
799 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', reset($paymentIDs), 'status_id', $paymentStatusID, 'id');
800 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', reset($paymentIDs), 'actual_amount', $actualAmount, 'id');
802 // Add new payment after the last payment for the pledge
803 $allPayments = self
::getPledgePayments($pledgeID);
804 $lastPayment = array_pop($allPayments);
806 $pledgeFrequencyUnit = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'frequency_unit', 'id');
807 $pledgeFrequencyInterval = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'frequency_interval', 'id');
808 $pledgeScheduledDate = $lastPayment['scheduled_date'];
809 $scheduled_date = CRM_Utils_Date
::processDate($pledgeScheduledDate);
810 $date['year'] = (int) substr($scheduled_date, 0, 4);
811 $date['month'] = (int) substr($scheduled_date, 4, 2);
812 $date['day'] = (int) substr($scheduled_date, 6, 2);
813 $newDate = date('YmdHis', mktime(0, 0, 0, $date['month'], $date['day'], $date['year']));
814 $ScheduledDate = CRM_Utils_Date
::format(CRM_Utils_Date
::intervalAdd($pledgeFrequencyUnit, $pledgeFrequencyInterval, $newDate));
815 $pledgeParams = array(
816 'status_id' => array_search('Pending', $allStatus),
817 'pledge_id' => $pledgeID,
818 'scheduled_amount' => $pledgeScheduledAmount,
819 'scheduled_date' => $ScheduledDate,
821 $payment = self
::add($pledgeParams);
824 $oldestPayment = self
::getOldestPledgePayment($pledgeID);
825 if (!$paymentContributionId) {
826 // means we are editing payment scheduled payment, so get the second pending to update.
827 $oldestPayment = self
::getOldestPledgePayment($pledgeID, 2);
828 if (($oldestPayment['count'] != 1) && ($oldestPayment['id'] == $pPaymentId)) {
829 $oldestPayment = self
::getOldestPledgePayment($pledgeID);
833 if ($oldestPayment) {
834 // not the last scheduled payment and the actual amount is less than the expected , add it to oldest pending.
835 if (($actualAmount != $pledgeScheduledAmount) && (($actualAmount < $pledgeScheduledAmount) ||
(($actualAmount - $pledgeScheduledAmount) < $oldestPayment['amount']))) {
836 $oldScheduledAmount = $oldestPayment['amount'];
837 $newScheduledAmount = $oldScheduledAmount +
($pledgeScheduledAmount - $actualAmount);
838 // store new amount in oldest pending payment record.
839 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment',
840 $oldestPayment['id'],
844 if (CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_PledgePayment', $oldestPayment['id'], 'contribution_id', 'id')) {
845 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment',
846 $oldestPayment['id'],
848 $paymentContributionId
852 elseif (($actualAmount > $pledgeScheduledAmount) && (($actualAmount - $pledgeScheduledAmount) >= $oldestPayment['amount'])) {
853 // 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
854 // set the actual amount of the next pending to '0', set contribution Id to current contribution Id and status as completed
855 $paymentId = array($oldestPayment['id']);
856 self
::updatePledgePayments($pledgeID, array_search('Completed', $allStatus), $paymentId, 0, $paymentContributionId);
857 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', $oldestPayment['id'], 'scheduled_amount', 0, 'id');
858 $oldestPayment = self
::getOldestPledgePayment($pledgeID);
859 if (!$paymentContributionId) {
860 // means we are editing payment scheduled payment.
861 $oldestPaymentAmount = self
::getOldestPledgePayment($pledgeID, 2);
863 $newActualAmount = ($actualAmount - $pledgeScheduledAmount);
864 $newPledgeScheduledAmount = $oldestPayment['amount'];
865 if (!$paymentContributionId) {
866 $newActualAmount = ($actualAmount - $pledgeScheduledAmount);
867 $newPledgeScheduledAmount = $oldestPaymentAmount['amount'];
868 // means we are editing payment scheduled payment, so update scheduled amount.
869 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment',
870 $oldestPaymentAmount['id'],
875 if ($newActualAmount > 0) {
876 self
::adjustPledgePayment($pledgeID, $newActualAmount, $newPledgeScheduledAmount, $paymentContributionId);
884 * Override buildOptions to hack out some statuses.
886 * @todo instead of using & hacking the shared optionGroup contribution_status use a separate one.
888 * @param string $fieldName
889 * @param string $context
890 * @param array $props
894 public static function buildOptions($fieldName, $context = NULL, $props = array()) {
895 $result = parent
::buildOptions($fieldName, $context, $props);
896 if ($fieldName == 'status_id') {
897 $result = array_diff($result, array('Failed', 'In Progress'));