3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
6 | This work is published under the GNU AGPLv3 license with some |
7 | permitted exceptions and without any warranty. For full license |
8 | and copyright information, see https://civicrm.org/licensing |
9 +--------------------------------------------------------------------+
15 * @copyright CiviCRM LLC https://civicrm.org/licensing
17 class CRM_Pledge_BAO_PledgePayment
extends CRM_Pledge_DAO_PledgePayment
{
22 public function __construct() {
23 parent
::__construct();
27 * Get pledge payment details.
29 * @param int $pledgeId
33 * associated array of pledge payment details
35 public static function getPledgePayments($pledgeId) {
37 SELECT civicrm_pledge_payment.id id,
44 civicrm_pledge_payment.currency,
45 civicrm_option_value.name as status,
46 civicrm_option_value.label as label,
47 civicrm_contribution.id as contribution_id
48 FROM civicrm_pledge_payment
50 LEFT JOIN civicrm_contribution ON civicrm_pledge_payment.contribution_id = civicrm_contribution.id
51 LEFT JOIN civicrm_option_group ON ( civicrm_option_group.name = 'contribution_status' )
52 LEFT JOIN civicrm_option_value ON ( civicrm_pledge_payment.status_id = civicrm_option_value.value AND
53 civicrm_option_group.id = civicrm_option_value.option_group_id )
57 $params[1] = [$pledgeId, 'Integer'];
58 $payment = CRM_Core_DAO
::executeQuery($query, $params);
61 while ($payment->fetch()) {
62 $paymentDetails[$payment->id
]['scheduled_amount'] = $payment->scheduled_amount
;
63 $paymentDetails[$payment->id
]['scheduled_date'] = $payment->scheduled_date
;
64 $paymentDetails[$payment->id
]['reminder_date'] = $payment->reminder_date
;
65 $paymentDetails[$payment->id
]['reminder_count'] = $payment->reminder_count
;
66 $paymentDetails[$payment->id
]['total_amount'] = $payment->actual_amount
;
67 $paymentDetails[$payment->id
]['receive_date'] = $payment->receive_date
;
68 $paymentDetails[$payment->id
]['status'] = $payment->status
;
69 $paymentDetails[$payment->id
]['label'] = $payment->label
;
70 $paymentDetails[$payment->id
]['id'] = $payment->id
;
71 $paymentDetails[$payment->id
]['contribution_id'] = $payment->contribution_id
;
72 $paymentDetails[$payment->id
]['currency'] = $payment->currency
;
75 return $paymentDetails;
79 * Create pledge payments.
81 * @param array $params
83 * @return CRM_Pledge_DAO_PledgePayment
85 public static function createMultiple(array $params) {
86 $transaction = new CRM_Core_Transaction();
87 $overdueStatusID = CRM_Core_PseudoConstant
::getKey('CRM_Pledge_BAO_PledgePayment', 'status_id', 'Overdue');
88 $pendingStatusId = CRM_Core_PseudoConstant
::getKey('CRM_Pledge_BAO_PledgePayment', 'status_id', 'Pending');
89 $currency = $params['currency'] ?? CRM_Core_Config
::singleton()->defaultCurrency
;
90 //calculate the scheduled date for every installment
91 $now = date('Ymd') . '000000';
92 $statues = $prevScheduledDate = [];
93 $prevScheduledDate[1] = CRM_Utils_Date
::processDate($params['scheduled_date']);
95 if (CRM_Utils_Date
::overdue($prevScheduledDate[1], $now)) {
96 $statues[1] = $overdueStatusID;
99 $statues[1] = $pendingStatusId;
102 for ($i = 1; $i < $params['installments']; $i++
) {
103 $prevScheduledDate[$i +
1] = self
::calculateNextScheduledDate($params, $i);
104 if (CRM_Utils_Date
::overdue($prevScheduledDate[$i +
1], $now)) {
105 $statues[$i +
1] = $overdueStatusID;
108 $statues[$i +
1] = $pendingStatusId;
112 if ($params['installment_amount']) {
113 $params['scheduled_amount'] = round($params['installment_amount'], CRM_Utils_Money
::getCurrencyPrecision($currency));
116 $params['scheduled_amount'] = round(($params['amount'] / $params['installments']), 2);
119 for ($i = 1; $i <= $params['installments']; $i++
) {
120 // calculate the scheduled amount for every installment.
121 if ($i == $params['installments']) {
122 $params['scheduled_amount'] = $params['amount'] - ($i - 1) * $params['scheduled_amount'];
124 if (!isset($params['contribution_id']) && $params['installments'] > 1) {
125 $params['status_id'] = $statues[$i];
128 $params['scheduled_date'] = $prevScheduledDate[$i];
129 $payment = self
::add($params);
130 if (is_a($payment, 'CRM_Core_Error')) {
131 $transaction->rollback();
135 // we should add contribution id to only first payment record
136 if (isset($params['contribution_id'])) {
137 unset($params['contribution_id']);
138 unset($params['actual_amount']);
142 // update pledge status
143 self
::updatePledgePaymentStatus($params['pledge_id']);
145 $transaction->commit();
150 * Create individual pledge payment.
152 * @param array $params
154 * @return CRM_Pledge_DAO_PledgePayment
155 * @throws \CRM_Core_Exception
157 public static function create(array $params): CRM_Pledge_DAO_PledgePayment
{
158 // set currency for CRM-1496
159 if (empty($params['id']) && !isset($params['currency'])) {
160 $params['currency'] = CRM_Core_Config
::singleton()->defaultCurrency
;
162 return self
::writeRecord($params);
166 * Add pledge payment.
168 * @deprecated - use the api which will use create (soon).
170 * @param array $params
171 * Fields in line with the database entity.
173 * @return CRM_Pledge_DAO_PledgePayment
174 * @throws \CRM_Core_Exception
176 public static function add(array $params): CRM_Pledge_DAO_PledgePayment
{
177 return self
::create($params);
181 * Retrieve DB object based on input parameters.
183 * It also stores all the retrieved values in the default array.
185 * @param array $params
186 * (reference ) an assoc array of name/value pairs.
187 * @param array $defaults
188 * (reference ) an assoc array to hold the flattened values.
190 * @return CRM_Pledge_BAO_PledgePayment
192 public static function retrieve(&$params, &$defaults) {
193 $payment = new CRM_Pledge_BAO_PledgePayment();
194 $payment->copyValues($params);
195 if ($payment->find(TRUE)) {
196 CRM_Core_DAO
::storeValues($payment, $defaults);
203 * Delete pledge payment.
209 public static function del($id) {
210 return (bool) self
::deleteRecord(['id' => $id]);
214 * Delete all pledge payments.
221 public static function deletePayments($id) {
222 if (!CRM_Utils_Rule
::positiveInteger($id)) {
226 $transaction = new CRM_Core_Transaction();
228 $payment = new CRM_Pledge_DAO_PledgePayment();
229 $payment->pledge_id
= $id;
231 if ($payment->find()) {
232 while ($payment->fetch()) {
233 //also delete associated contribution.
234 if ($payment->contribution_id
) {
235 CRM_Contribute_BAO_Contribution
::deleteContribution($payment->contribution_id
);
237 self
::del($payment->id
);
241 $transaction->commit();
247 * On delete contribution record update associated pledge payment and pledge.
249 * @param int $contributionID
254 public static function resetPledgePayment($contributionID) {
255 $transaction = new CRM_Core_Transaction();
257 $payment = new CRM_Pledge_DAO_PledgePayment();
258 $payment->contribution_id
= $contributionID;
259 if ($payment->find(TRUE)) {
260 $payment->contribution_id
= 'null';
261 $payment->status_id
= CRM_Core_PseudoConstant
::getKey('CRM_Pledge_BAO_Pledge', 'status_id', 'Pending');
262 $payment->scheduled_date
= NULL;
263 $payment->reminder_date
= NULL;
264 $payment->scheduled_amount
= $payment->actual_amount
;
265 $payment->actual_amount
= 'null';
268 //update pledge status.
269 $pledgeID = $payment->pledge_id
;
270 $pledgeStatusID = self
::calculatePledgeStatus($pledgeID);
271 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'status_id', $pledgeStatusID);
275 $transaction->commit();
280 * Update Pledge Payment Status.
282 * @param int $pledgeID
284 * @param array $paymentIDs
285 * Ids of pledge payment(s) to update.
286 * @param int $paymentStatusID
287 * Payment status to set.
288 * @param int $pledgeStatusID
289 * Pledge status to change (if needed).
290 * @param float|int $actualAmount , actual amount being paid
291 * @param bool $adjustTotalAmount
292 * Is amount being paid different from scheduled amount?.
293 * @param bool $isScriptUpdate
294 * Is function being called from bin script?.
297 * $newStatus, updated status id (or 0)
299 public static function updatePledgePaymentStatus(
302 $paymentStatusID = NULL,
303 $pledgeStatusID = NULL,
305 $adjustTotalAmount = FALSE,
306 $isScriptUpdate = FALSE
308 $totalAmountClause = '';
309 $paymentContributionId = NULL;
310 $editScheduled = FALSE;
313 $allStatus = CRM_Core_OptionGroup
::values('pledge_status',
314 FALSE, FALSE, FALSE, NULL, 'name', TRUE
317 // if we get do not get contribution id means we are editing the scheduled payment.
318 if (!empty($paymentIDs)) {
319 $editScheduled = FALSE;
320 $payments = implode(',', $paymentIDs);
321 $paymentContributionId = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_PledgePayment',
327 if (!$paymentContributionId) {
328 $editScheduled = TRUE;
332 // if payment ids are passed, we update payment table first, since payments statuses are not dependent on pledge status
333 $pledgeStatusName = CRM_Core_PseudoConstant
::getName('CRM_Pledge_BAO_Pledge', 'status_id', $pledgeStatusID);
334 if ((!empty($paymentIDs) ||
$pledgeStatusName == 'Cancelled') && (!$editScheduled ||
$isScriptUpdate)) {
335 if ($pledgeStatusName == 'Cancelled') {
336 $paymentStatusID = $pledgeStatusID;
339 self
::updatePledgePayments($pledgeID, $paymentStatusID, $paymentIDs, $actualAmount, $paymentContributionId, $isScriptUpdate);
341 if (!empty($paymentIDs) && $actualAmount) {
342 $payments = implode(',', $paymentIDs);
343 $pledgeScheduledAmount = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_PledgePayment',
349 $pledgeStatusId = self
::calculatePledgeStatus($pledgeID);
350 // Actual Pledge Amount
351 $actualPledgeAmount = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge',
356 // while editing scheduled we need to check if we are editing last pending
357 $lastPending = FALSE;
358 if (!$paymentContributionId) {
359 $checkPendingCount = self
::getOldestPledgePayment($pledgeID, 2);
360 if ($checkPendingCount['count'] == 1) {
365 // check if this is the last payment and adjust the actual amount.
366 if ($pledgeStatusId && $pledgeStatusId == array_search('Completed', $allStatus) ||
$lastPending) {
367 // last scheduled payment
368 if ($actualAmount >= $pledgeScheduledAmount) {
369 $adjustTotalAmount = TRUE;
371 elseif (!$adjustTotalAmount) {
372 // actual amount is less than the scheduled amount, so enter new pledge payment record
373 $pledgeFrequencyUnit = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'frequency_unit', 'id');
374 $pledgeFrequencyInterval = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'frequency_interval', 'id');
375 $pledgeScheduledDate = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_PledgePayment', $payments, 'scheduled_date', 'id');
376 $scheduled_date = CRM_Utils_Date
::processDate($pledgeScheduledDate);
377 $date['year'] = (int) substr($scheduled_date, 0, 4);
378 $date['month'] = (int) substr($scheduled_date, 4, 2);
379 $date['day'] = (int) substr($scheduled_date, 6, 2);
380 $newDate = date('YmdHis', mktime(0, 0, 0, $date['month'], $date['day'], $date['year']));
381 $ScheduledDate = CRM_Utils_Date
::format(CRM_Utils_Date
::intervalAdd($pledgeFrequencyUnit,
382 $pledgeFrequencyInterval, $newDate
385 'status_id' => array_search('Pending', $allStatus),
386 'pledge_id' => $pledgeID,
387 'scheduled_amount' => ($pledgeScheduledAmount - $actualAmount),
388 'scheduled_date' => $ScheduledDate,
390 $payment = self
::add($pledgeParams);
391 // while editing schedule, after adding a new pledge payemnt update the scheduled amount of the current payment
392 if (!$paymentContributionId) {
393 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', $payments, 'scheduled_amount', $actualAmount);
397 elseif (!$adjustTotalAmount) {
398 // not last schedule amount and also not selected to adjust Total
399 $paymentContributionId = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_PledgePayment',
404 self
::adjustPledgePayment($pledgeID, $actualAmount, $pledgeScheduledAmount, $paymentContributionId, $payments, $paymentStatusID);
405 // while editing schedule, after adding a new pledge payemnt update the scheduled amount of the current payment
406 if (!$paymentContributionId) {
407 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', $payments, 'scheduled_amount', $actualAmount);
409 // after adjusting all payments check if the actual amount was greater than the actual remaining amount , if so then update the total pledge amount.
410 $pledgeStatusId = self
::calculatePledgeStatus($pledgeID);
412 SELECT sum( civicrm_pledge_payment.actual_amount )
413 FROM civicrm_pledge_payment
414 WHERE civicrm_pledge_payment.pledge_id = %1
415 AND civicrm_pledge_payment.status_id = 1
417 $totalPaidParams = [1 => [$pledgeID, 'Integer']];
418 $totalPaidAmount = CRM_Core_DAO
::singleValueQuery($balanceQuery, $totalPaidParams);
419 $remainingTotalAmount = ($actualPledgeAmount - $totalPaidAmount);
420 if (($pledgeStatusId && $pledgeStatusId == array_search('Completed', $allStatus)) && (($actualAmount > $remainingTotalAmount) ||
($actualAmount >= $actualPledgeAmount))) {
421 $totalAmountClause = ", civicrm_pledge.amount = {$totalPaidAmount}";
424 if ($adjustTotalAmount) {
425 $newTotalAmount = ($actualPledgeAmount +
($actualAmount - $pledgeScheduledAmount));
426 $totalAmountClause = ", civicrm_pledge.amount = {$newTotalAmount}";
427 if (!$paymentContributionId) {
428 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', $payments, 'scheduled_amount', $actualAmount);
433 $cancelDateClause = $endDateClause = NULL;
434 // update pledge and payment status if status is Completed/Cancelled.
435 if ($pledgeStatusID && $pledgeStatusID == array_search('Cancelled', $allStatus)) {
436 $paymentStatusID = $pledgeStatusID;
437 $cancelDateClause = ", civicrm_pledge.cancel_date = CURRENT_TIMESTAMP ";
441 $pledgeStatusID = self
::calculatePledgeStatus($pledgeID);
444 if ($pledgeStatusID == array_search('Completed', $allStatus)) {
445 $endDateClause = ", civicrm_pledge.end_date = CURRENT_TIMESTAMP ";
448 // update pledge status
450 UPDATE civicrm_pledge
451 SET civicrm_pledge.status_id = %1
452 {$cancelDateClause} {$endDateClause} {$totalAmountClause}
453 WHERE civicrm_pledge.id = %2
457 1 => [$pledgeStatusID, 'Integer'],
458 2 => [$pledgeID, 'Integer'],
461 CRM_Core_DAO
::executeQuery($query, $params);
463 return $pledgeStatusID;
467 * Calculate the base scheduled date. This function effectively 'rounds' the $params['scheduled_date'] value
468 * to the first payment date with respect to the frequency day - ie. if payments are on the 15th of the month the date returned
469 * will be the 15th of the relevant month. Then to calculate the payments you can use intervalAdd ie.
470 * CRM_Utils_Date::intervalAdd( $params['frequency_unit'], $i * ($params['frequency_interval']) , calculateBaseScheduledDate( &$params )))
472 * @param array $params
475 * Next scheduled date as an array
477 public static function calculateBaseScheduleDate(&$params) {
479 $scheduled_date = CRM_Utils_Date
::processDate($params['scheduled_date']);
480 $date['year'] = (int) substr($scheduled_date, 0, 4);
481 $date['month'] = (int) substr($scheduled_date, 4, 2);
482 $date['day'] = (int) substr($scheduled_date, 6, 2);
483 // calculation of schedule date according to frequency day of period
484 // frequency day is not applicable for daily installments
485 if ($params['frequency_unit'] != 'day' && $params['frequency_unit'] != 'year') {
486 if ($params['frequency_unit'] != 'week') {
487 // CRM-18316: To calculate pledge scheduled dates at the end of a month.
488 $date['day'] = $params['frequency_day'];
489 $lastDayOfMonth = date('t', mktime(0, 0, 0, $date['month'], 1, $date['year']));
490 if ($lastDayOfMonth < $date['day']) {
491 $date['day'] = $lastDayOfMonth;
494 elseif ($params['frequency_unit'] == 'week') {
496 // for week calculate day of week ie. Sunday,Monday etc. as next payment date
497 $dayOfWeek = date('w', mktime(0, 0, 0, $date['month'], $date['day'], $date['year']));
498 $frequencyDay = $params['frequency_day'] - $dayOfWeek;
500 $scheduleDate = explode("-", date('n-j-Y', mktime(0, 0, 0, $date['month'],
501 $date['day'] +
$frequencyDay, $date['year']
503 $date['month'] = $scheduleDate[0];
504 $date['day'] = $scheduleDate[1];
505 $date['year'] = $scheduleDate[2];
508 $newdate = date('YmdHis', mktime(0, 0, 0, $date['month'], $date['day'], $date['year']));
513 * Calculate next scheduled pledge payment date. Function calculates next pledge payment date.
515 * @param array $params
516 * must include frequency unit & frequency interval
517 * @param int $paymentNo
518 * number of payment in sequence (e.g. 1 for first calculated payment (treat initial payment as 0)
519 * @param string $basePaymentDate
520 * date to calculate payments from. This would normally be the
521 * first day of the pledge (default) & is calculated off the 'scheduled date' param. Returned date will
522 * be equal to basePaymentDate normalised to fit the 'pledge pattern' + number of installments
527 public static function calculateNextScheduledDate(&$params, $paymentNo, $basePaymentDate = NULL) {
528 $interval = $paymentNo * ($params['frequency_interval']);
529 if (!$basePaymentDate) {
530 $basePaymentDate = self
::calculateBaseScheduleDate($params);
533 //CRM-18316 - change $basePaymentDate for the end dates of the month eg: 29, 30 or 31.
534 if ($params['frequency_unit'] == 'month' && in_array($params['frequency_day'], [29, 30, 31])) {
535 $frequency = $params['frequency_day'];
536 extract(date_parse($basePaymentDate));
537 $lastDayOfMonth = date('t', mktime($hour, $minute, $second, $month +
$interval, 1, $year));
538 // Take the last day in case the current month is Feb or frequency_day is set to 31.
539 if (in_array($lastDayOfMonth, [28, 29]) ||
$frequency == 31) {
550 return CRM_Utils_Date
::format(
551 CRM_Utils_Date
::intervalAdd(
552 $params['frequency_unit'],
560 * Calculate the pledge status.
562 * @param int $pledgeId
566 * $statusId calculated status id of pledge
568 public static function calculatePledgeStatus($pledgeId) {
569 $paymentStatusTypes = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
570 $pledgeStatusTypes = CRM_Pledge_BAO_Pledge
::buildOptions('status_id', 'validate');
572 //return if the pledge is cancelled.
573 $currentPledgeStatusId = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeId, 'status_id', 'id', TRUE);
574 if ($currentPledgeStatusId == array_search('Cancelled', $pledgeStatusTypes)) {
575 return $currentPledgeStatusId;
578 // retrieve all pledge payments for this particular pledge
579 $allPledgePayments = $allStatus = [];
580 $returnProperties = ['status_id'];
581 CRM_Core_DAO
::commonRetrieveAll('CRM_Pledge_DAO_PledgePayment', 'pledge_id', $pledgeId, $allPledgePayments, $returnProperties);
583 // build pledge payment statuses
584 foreach ($allPledgePayments as $key => $value) {
585 $allStatus[$value['id']] = $paymentStatusTypes[$value['status_id']];
588 if (array_search('Overdue', $allStatus)) {
589 $statusId = array_search('Overdue', $pledgeStatusTypes);
591 elseif (array_search('Completed', $allStatus)) {
592 if (count(array_count_values($allStatus)) == 1) {
593 $statusId = array_search('Completed', $pledgeStatusTypes);
596 $statusId = array_search('In Progress', $pledgeStatusTypes);
600 $statusId = array_search('Pending', $pledgeStatusTypes);
607 * Update pledge payment table.
609 * @param int $pledgeId
611 * @param int $paymentStatusId
612 * Payment status id to set.
613 * @param array $paymentIds
614 * Payment ids to be updated.
615 * @param float|int $actualAmount , actual amount being paid
616 * @param int $contributionId
617 * , Id of associated contribution when payment is recorded.
618 * @param bool $isScriptUpdate
619 * , is function being called from bin script?.
622 public static function updatePledgePayments(
627 $contributionId = NULL,
628 $isScriptUpdate = FALSE
630 $allStatus = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
631 $paymentClause = NULL;
632 if (!empty($paymentIds)) {
633 $payments = implode(',', $paymentIds);
634 $paymentClause = " AND civicrm_pledge_payment.id IN ( {$payments} )";
636 elseif ($paymentStatusId == array_search('Cancelled', $allStatus)) {
637 $completedStatus = array_search('Completed', $allStatus);
638 $paymentClause = " AND civicrm_pledge_payment.status_id != {$completedStatus}";
640 $actualAmountClause = NULL;
641 $contributionIdClause = NULL;
642 if (isset($contributionId) && !$isScriptUpdate) {
643 $contributionIdClause = ", civicrm_pledge_payment.contribution_id = {$contributionId}";
644 $actualAmountClause = ", civicrm_pledge_payment.actual_amount = {$actualAmount}";
648 UPDATE civicrm_pledge_payment
649 SET civicrm_pledge_payment.status_id = {$paymentStatusId}
650 {$actualAmountClause} {$contributionIdClause}
651 WHERE civicrm_pledge_payment.pledge_id = %1
655 CRM_Core_DAO
::executeQuery($query, [1 => [$pledgeId, 'Integer']]);
659 * Update pledge payment table when reminder is sent.
661 * @param int $paymentId
664 public static function updateReminderDetails($paymentId) {
666 UPDATE civicrm_pledge_payment
667 SET civicrm_pledge_payment.reminder_date = CURRENT_TIMESTAMP,
668 civicrm_pledge_payment.reminder_count = civicrm_pledge_payment.reminder_count + 1
669 WHERE civicrm_pledge_payment.id = {$paymentId}
671 $dao = CRM_Core_DAO
::executeQuery($query);
675 * Get oldest pending or in progress pledge payments.
677 * @param int $pledgeID
683 * associated array of pledge details
685 public static function getOldestPledgePayment($pledgeID, $limit = 1) {
686 // get pending / overdue statuses
687 $pledgeStatuses = CRM_Core_OptionGroup
::values('pledge_status',
688 FALSE, FALSE, FALSE, NULL, 'name'
691 // get pending and overdue payments
692 $status[] = array_search('Pending', $pledgeStatuses);
693 $status[] = array_search('Overdue', $pledgeStatuses);
695 $statusClause = " IN (" . implode(',', $status) . ")";
698 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
699 FROM civicrm_pledge, civicrm_pledge_payment
700 WHERE civicrm_pledge.id = civicrm_pledge_payment.pledge_id
701 AND civicrm_pledge_payment.status_id {$statusClause}
702 AND civicrm_pledge.id = %1
703 ORDER BY civicrm_pledge_payment.scheduled_date ASC
707 $params[1] = [$pledgeID, 'Integer'];
708 $params[2] = [$limit, 'Integer'];
709 $payment = CRM_Core_DAO
::executeQuery($query, $params);
711 $paymentDetails = [];
712 while ($payment->fetch()) {
713 $paymentDetails[] = [
714 'id' => $payment->id
,
715 'amount' => $payment->amount
,
716 'currency' => $payment->currency
,
717 'schedule_date' => $payment->scheduled_date
,
718 'financial_type_id' => $payment->financial_type_id
,
723 return end($paymentDetails);
727 * @param int $pledgeID
728 * @param $actualAmount
729 * @param $pledgeScheduledAmount
730 * @param int $paymentContributionId
731 * @param int $pPaymentId
732 * @param int $paymentStatusID
734 public static function adjustPledgePayment($pledgeID, $actualAmount, $pledgeScheduledAmount, $paymentContributionId = NULL, $pPaymentId = NULL, $paymentStatusID = NULL) {
735 $allStatus = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
736 $paymentStatusName = CRM_Core_PseudoConstant
::getName('CRM_Pledge_BAO_PledgePayment', 'status_id', $paymentStatusID);
737 if ($paymentStatusName == 'Cancelled'||
$paymentStatusName == 'Refunded') {
739 SELECT civicrm_pledge_payment.id id
740 FROM civicrm_pledge_payment
741 WHERE civicrm_pledge_payment.contribution_id = {$paymentContributionId}
743 $paymentsAffected = CRM_Core_DAO
::executeQuery($query);
745 while ($paymentsAffected->fetch()) {
746 $paymentIDs[] = $paymentsAffected->id
;
748 // Reset the affected values by the amount paid more than the scheduled amount
749 foreach ($paymentIDs as $key => $value) {
750 $payment = new CRM_Pledge_DAO_PledgePayment();
751 $payment->id
= $value;
752 if ($payment->find(TRUE)) {
753 $payment->contribution_id
= 'null';
754 $payment->status_id
= array_search('Pending', $allStatus);
755 $payment->scheduled_date
= NULL;
756 $payment->reminder_date
= NULL;
757 $payment->scheduled_amount
= $pledgeScheduledAmount;
758 $payment->actual_amount
= 'null';
763 // Cancel the initial paid amount
764 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', reset($paymentIDs), 'status_id', $paymentStatusID, 'id');
765 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', reset($paymentIDs), 'actual_amount', $actualAmount, 'id');
767 // Add new payment after the last payment for the pledge
768 $allPayments = self
::getPledgePayments($pledgeID);
769 $lastPayment = array_pop($allPayments);
771 $pledgeFrequencyUnit = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'frequency_unit', 'id');
772 $pledgeFrequencyInterval = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'frequency_interval', 'id');
773 $pledgeScheduledDate = $lastPayment['scheduled_date'];
774 $scheduled_date = CRM_Utils_Date
::processDate($pledgeScheduledDate);
775 $date['year'] = (int) substr($scheduled_date, 0, 4);
776 $date['month'] = (int) substr($scheduled_date, 4, 2);
777 $date['day'] = (int) substr($scheduled_date, 6, 2);
778 $newDate = date('YmdHis', mktime(0, 0, 0, $date['month'], $date['day'], $date['year']));
779 $ScheduledDate = CRM_Utils_Date
::format(CRM_Utils_Date
::intervalAdd($pledgeFrequencyUnit, $pledgeFrequencyInterval, $newDate));
781 'status_id' => array_search('Pending', $allStatus),
782 'pledge_id' => $pledgeID,
783 'scheduled_amount' => $pledgeScheduledAmount,
784 'scheduled_date' => $ScheduledDate,
786 $payment = self
::add($pledgeParams);
789 $nextPledgeInstallmentDue = self
::getOldestPledgePayment($pledgeID);
790 if (!$paymentContributionId) {
791 // means we are editing payment scheduled payment, so get the second pending to update.
792 $nextPledgeInstallmentDue = self
::getOldestPledgePayment($pledgeID, 2);
793 if (($nextPledgeInstallmentDue['count'] != 1) && ($nextPledgeInstallmentDue['id'] == $pPaymentId)) {
794 $nextPledgeInstallmentDue = self
::getOldestPledgePayment($pledgeID);
798 if ($nextPledgeInstallmentDue) {
799 // not the last scheduled payment and the actual amount is less than the expected , add it to oldest pending.
800 if (($actualAmount != $pledgeScheduledAmount) && (($actualAmount < $pledgeScheduledAmount) ||
(($actualAmount - $pledgeScheduledAmount) < $nextPledgeInstallmentDue['amount']))) {
801 $oldScheduledAmount = $nextPledgeInstallmentDue['amount'];
802 $newScheduledAmount = $oldScheduledAmount +
($pledgeScheduledAmount - $actualAmount);
803 // store new amount in oldest pending payment record.
804 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment',
805 $nextPledgeInstallmentDue['id'],
809 if (CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_PledgePayment', $nextPledgeInstallmentDue['id'], 'contribution_id', 'id')) {
810 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment',
811 $nextPledgeInstallmentDue['id'],
813 $paymentContributionId
817 elseif (($actualAmount > $pledgeScheduledAmount) && (($actualAmount - $pledgeScheduledAmount) >= $nextPledgeInstallmentDue['amount'])) {
818 // 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
819 // set the actual amount of the next pending to '0', set contribution Id to current contribution Id and status as completed
820 $paymentId = [$nextPledgeInstallmentDue['id']];
821 self
::updatePledgePayments($pledgeID, array_search('Completed', $allStatus), $paymentId, 0, $paymentContributionId);
822 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', $nextPledgeInstallmentDue['id'], 'scheduled_amount', 0, 'id');
823 if (!$paymentContributionId) {
824 // means we are editing payment scheduled payment.
825 $oldestPaymentAmount = self
::getOldestPledgePayment($pledgeID, 2);
827 $newActualAmount = round(($actualAmount - $pledgeScheduledAmount), CRM_Utils_Money
::getCurrencyPrecision());
828 $newPledgeScheduledAmount = $nextPledgeInstallmentDue['amount'];
829 if (!$paymentContributionId) {
830 $newActualAmount = ($actualAmount - $pledgeScheduledAmount);
831 $newPledgeScheduledAmount = $oldestPaymentAmount['amount'];
832 // means we are editing payment scheduled payment, so update scheduled amount.
833 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment',
834 $oldestPaymentAmount['id'],
839 if ($newActualAmount > 0) {
840 self
::adjustPledgePayment($pledgeID, $newActualAmount, $newPledgeScheduledAmount, $paymentContributionId);
848 * Override buildOptions to hack out some statuses.
850 * @todo instead of using & hacking the shared optionGroup contribution_status use a separate one.
852 * @param string $fieldName
853 * @param string $context
854 * @param array $props
858 public static function buildOptions($fieldName, $context = NULL, $props = []) {
859 $result = parent
::buildOptions($fieldName, $context, $props);
860 if ($fieldName == 'status_id') {
861 $result = CRM_Pledge_BAO_Pledge
::buildOptions($fieldName, $context, $props);
862 $result = array_diff($result, ['Failed', 'In Progress']);