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
{
20 * Get pledge payment details.
22 * @param int $pledgeId
26 * associated array of pledge payment details
28 public static function getPledgePayments($pledgeId) {
30 SELECT civicrm_pledge_payment.id id,
37 civicrm_pledge_payment.currency,
38 civicrm_option_value.name as status,
39 civicrm_option_value.label as label,
40 civicrm_contribution.id as contribution_id
41 FROM civicrm_pledge_payment
43 LEFT JOIN civicrm_contribution ON civicrm_pledge_payment.contribution_id = civicrm_contribution.id
44 LEFT JOIN civicrm_option_group ON ( civicrm_option_group.name = 'contribution_status' )
45 LEFT JOIN civicrm_option_value ON ( civicrm_pledge_payment.status_id = civicrm_option_value.value AND
46 civicrm_option_group.id = civicrm_option_value.option_group_id )
50 $params[1] = [$pledgeId, 'Integer'];
51 $payment = CRM_Core_DAO
::executeQuery($query, $params);
54 while ($payment->fetch()) {
55 $paymentDetails[$payment->id
]['scheduled_amount'] = $payment->scheduled_amount
;
56 $paymentDetails[$payment->id
]['scheduled_date'] = $payment->scheduled_date
;
57 $paymentDetails[$payment->id
]['reminder_date'] = $payment->reminder_date
;
58 $paymentDetails[$payment->id
]['reminder_count'] = $payment->reminder_count
;
59 $paymentDetails[$payment->id
]['total_amount'] = $payment->actual_amount
;
60 $paymentDetails[$payment->id
]['receive_date'] = $payment->receive_date
;
61 $paymentDetails[$payment->id
]['status'] = $payment->status
;
62 $paymentDetails[$payment->id
]['label'] = $payment->label
;
63 $paymentDetails[$payment->id
]['id'] = $payment->id
;
64 $paymentDetails[$payment->id
]['contribution_id'] = $payment->contribution_id
;
65 $paymentDetails[$payment->id
]['currency'] = $payment->currency
;
68 return $paymentDetails;
72 * Create pledge payments.
74 * @param array $params
76 * @return CRM_Pledge_DAO_PledgePayment
78 public static function createMultiple(array $params) {
79 $transaction = new CRM_Core_Transaction();
80 $overdueStatusID = CRM_Core_PseudoConstant
::getKey('CRM_Pledge_BAO_PledgePayment', 'status_id', 'Overdue');
81 $pendingStatusId = CRM_Core_PseudoConstant
::getKey('CRM_Pledge_BAO_PledgePayment', 'status_id', 'Pending');
82 $currency = $params['currency'] ?? CRM_Core_Config
::singleton()->defaultCurrency
;
83 //calculate the scheduled date for every installment
84 $now = date('Ymd') . '000000';
85 $statues = $prevScheduledDate = [];
86 $prevScheduledDate[1] = CRM_Utils_Date
::processDate($params['scheduled_date']);
88 if (CRM_Utils_Date
::overdue($prevScheduledDate[1], $now)) {
89 $statues[1] = $overdueStatusID;
92 $statues[1] = $pendingStatusId;
95 for ($i = 1; $i < $params['installments']; $i++
) {
96 $prevScheduledDate[$i +
1] = self
::calculateNextScheduledDate($params, $i);
97 if (CRM_Utils_Date
::overdue($prevScheduledDate[$i +
1], $now)) {
98 $statues[$i +
1] = $overdueStatusID;
101 $statues[$i +
1] = $pendingStatusId;
105 if ($params['installment_amount']) {
106 $params['scheduled_amount'] = round($params['installment_amount'], CRM_Utils_Money
::getCurrencyPrecision($currency));
109 $params['scheduled_amount'] = round(($params['amount'] / $params['installments']), 2);
112 for ($i = 1; $i <= $params['installments']; $i++
) {
113 // calculate the scheduled amount for every installment.
114 if ($i == $params['installments']) {
115 $params['scheduled_amount'] = $params['amount'] - ($i - 1) * $params['scheduled_amount'];
117 if (!isset($params['contribution_id']) && $params['installments'] > 1) {
118 $params['status_id'] = $statues[$i];
121 $params['scheduled_date'] = $prevScheduledDate[$i];
122 $payment = self
::add($params);
123 if (is_a($payment, 'CRM_Core_Error')) {
124 $transaction->rollback();
128 // we should add contribution id to only first payment record
129 if (isset($params['contribution_id'])) {
130 unset($params['contribution_id']);
131 unset($params['actual_amount']);
135 // update pledge status
136 self
::updatePledgePaymentStatus($params['pledge_id']);
138 $transaction->commit();
143 * Create individual pledge payment.
145 * @param array $params
147 * @return CRM_Pledge_DAO_PledgePayment
148 * @throws \CRM_Core_Exception
150 public static function create(array $params): CRM_Pledge_DAO_PledgePayment
{
151 // set currency for CRM-1496
152 if (empty($params['id']) && !isset($params['currency'])) {
153 $params['currency'] = CRM_Core_Config
::singleton()->defaultCurrency
;
155 return self
::writeRecord($params);
159 * Add pledge payment.
161 * @deprecated - use the api which will use create (soon).
163 * @param array $params
164 * Fields in line with the database entity.
166 * @return CRM_Pledge_DAO_PledgePayment
167 * @throws \CRM_Core_Exception
169 public static function add(array $params): CRM_Pledge_DAO_PledgePayment
{
170 return self
::create($params);
174 * Retrieve DB object and copy to defaults array.
176 * @param array $params
177 * Array of criteria values.
178 * @param array $defaults
179 * Array to be populated with found values.
182 * The DAO object, if found.
186 public static function retrieve($params, &$defaults) {
187 return self
::commonRetrieve(self
::class, $params, $defaults);
191 * Delete pledge payment.
197 public static function del($id) {
198 return (bool) self
::deleteRecord(['id' => $id]);
202 * Delete all pledge payments.
209 public static function deletePayments($id) {
210 if (!CRM_Utils_Rule
::positiveInteger($id)) {
214 $transaction = new CRM_Core_Transaction();
216 $payment = new CRM_Pledge_DAO_PledgePayment();
217 $payment->pledge_id
= $id;
219 if ($payment->find()) {
220 while ($payment->fetch()) {
221 //also delete associated contribution.
222 if ($payment->contribution_id
) {
223 CRM_Contribute_BAO_Contribution
::deleteContribution($payment->contribution_id
);
225 self
::del($payment->id
);
229 $transaction->commit();
235 * On delete contribution record update associated pledge payment and pledge.
237 * @param int $contributionID
242 public static function resetPledgePayment($contributionID) {
243 $transaction = new CRM_Core_Transaction();
245 $payment = new CRM_Pledge_DAO_PledgePayment();
246 $payment->contribution_id
= $contributionID;
247 if ($payment->find(TRUE)) {
248 $payment->contribution_id
= 'null';
249 $payment->status_id
= CRM_Core_PseudoConstant
::getKey('CRM_Pledge_BAO_Pledge', 'status_id', 'Pending');
250 $payment->scheduled_date
= NULL;
251 $payment->reminder_date
= NULL;
252 $payment->scheduled_amount
= $payment->actual_amount
;
253 $payment->actual_amount
= 'null';
256 //update pledge status.
257 $pledgeID = $payment->pledge_id
;
258 $pledgeStatusID = self
::calculatePledgeStatus($pledgeID);
259 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'status_id', $pledgeStatusID);
263 $transaction->commit();
268 * Update Pledge Payment Status.
270 * @param int $pledgeID
272 * @param array $paymentIDs
273 * Ids of pledge payment(s) to update.
274 * @param int $paymentStatusID
275 * Payment status to set.
276 * @param int $pledgeStatusID
277 * Pledge status to change (if needed).
278 * @param float|int $actualAmount , actual amount being paid
279 * @param bool $adjustTotalAmount
280 * Is amount being paid different from scheduled amount?.
281 * @param bool $isScriptUpdate
282 * Is function being called from bin script?.
285 * $newStatus, updated status id (or 0)
287 public static function updatePledgePaymentStatus(
290 $paymentStatusID = NULL,
291 $pledgeStatusID = NULL,
293 $adjustTotalAmount = FALSE,
294 $isScriptUpdate = FALSE
296 $totalAmountClause = '';
297 $paymentContributionId = NULL;
298 $editScheduled = FALSE;
301 $allStatus = CRM_Core_OptionGroup
::values('pledge_status',
302 FALSE, FALSE, FALSE, NULL, 'name', TRUE
305 // if we get do not get contribution id means we are editing the scheduled payment.
306 if (!empty($paymentIDs)) {
307 $editScheduled = FALSE;
308 $payments = implode(',', $paymentIDs);
309 $paymentContributionId = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_PledgePayment',
315 if (!$paymentContributionId) {
316 $editScheduled = TRUE;
320 // if payment ids are passed, we update payment table first, since payments statuses are not dependent on pledge status
321 $pledgeStatusName = CRM_Core_PseudoConstant
::getName('CRM_Pledge_BAO_Pledge', 'status_id', $pledgeStatusID);
322 if ((!empty($paymentIDs) ||
$pledgeStatusName == 'Cancelled') && (!$editScheduled ||
$isScriptUpdate)) {
323 if ($pledgeStatusName == 'Cancelled') {
324 $paymentStatusID = $pledgeStatusID;
327 self
::updatePledgePayments($pledgeID, $paymentStatusID, $paymentIDs, $actualAmount, $paymentContributionId, $isScriptUpdate);
329 if (!empty($paymentIDs) && $actualAmount) {
330 $payments = implode(',', $paymentIDs);
331 $pledgeScheduledAmount = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_PledgePayment',
337 $pledgeStatusId = self
::calculatePledgeStatus($pledgeID);
338 // Actual Pledge Amount
339 $actualPledgeAmount = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge',
344 // while editing scheduled we need to check if we are editing last pending
345 $lastPending = FALSE;
346 if (!$paymentContributionId) {
347 $checkPendingCount = self
::getOldestPledgePayment($pledgeID, 2);
348 if ($checkPendingCount['count'] == 1) {
353 // check if this is the last payment and adjust the actual amount.
354 if ($pledgeStatusId && $pledgeStatusId == array_search('Completed', $allStatus) ||
$lastPending) {
355 // last scheduled payment
356 if ($actualAmount >= $pledgeScheduledAmount) {
357 $adjustTotalAmount = TRUE;
359 elseif (!$adjustTotalAmount) {
360 // actual amount is less than the scheduled amount, so enter new pledge payment record
361 $pledgeFrequencyUnit = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'frequency_unit', 'id');
362 $pledgeFrequencyInterval = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'frequency_interval', 'id');
363 $pledgeScheduledDate = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_PledgePayment', $payments, 'scheduled_date', 'id');
364 $scheduled_date = CRM_Utils_Date
::processDate($pledgeScheduledDate);
365 $date['year'] = (int) substr($scheduled_date, 0, 4);
366 $date['month'] = (int) substr($scheduled_date, 4, 2);
367 $date['day'] = (int) substr($scheduled_date, 6, 2);
368 $newDate = date('YmdHis', mktime(0, 0, 0, $date['month'], $date['day'], $date['year']));
369 $ScheduledDate = CRM_Utils_Date
::format(CRM_Utils_Date
::intervalAdd($pledgeFrequencyUnit,
370 $pledgeFrequencyInterval, $newDate
373 'status_id' => array_search('Pending', $allStatus),
374 'pledge_id' => $pledgeID,
375 'scheduled_amount' => ($pledgeScheduledAmount - $actualAmount),
376 'scheduled_date' => $ScheduledDate,
378 $payment = self
::add($pledgeParams);
379 // while editing schedule, after adding a new pledge payemnt update the scheduled amount of the current payment
380 if (!$paymentContributionId) {
381 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', $payments, 'scheduled_amount', $actualAmount);
385 elseif (!$adjustTotalAmount) {
386 // not last schedule amount and also not selected to adjust Total
387 $paymentContributionId = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_PledgePayment',
392 self
::adjustPledgePayment($pledgeID, $actualAmount, $pledgeScheduledAmount, $paymentContributionId, $payments, $paymentStatusID);
393 // while editing schedule, after adding a new pledge payemnt update the scheduled amount of the current payment
394 if (!$paymentContributionId) {
395 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', $payments, 'scheduled_amount', $actualAmount);
397 // after adjusting all payments check if the actual amount was greater than the actual remaining amount , if so then update the total pledge amount.
398 $pledgeStatusId = self
::calculatePledgeStatus($pledgeID);
400 SELECT sum( civicrm_pledge_payment.actual_amount )
401 FROM civicrm_pledge_payment
402 WHERE civicrm_pledge_payment.pledge_id = %1
403 AND civicrm_pledge_payment.status_id = 1
405 $totalPaidParams = [1 => [$pledgeID, 'Integer']];
406 $totalPaidAmount = CRM_Core_DAO
::singleValueQuery($balanceQuery, $totalPaidParams);
407 $remainingTotalAmount = ($actualPledgeAmount - $totalPaidAmount);
408 if (($pledgeStatusId && $pledgeStatusId == array_search('Completed', $allStatus)) && (($actualAmount > $remainingTotalAmount) ||
($actualAmount >= $actualPledgeAmount))) {
409 $totalAmountClause = ", civicrm_pledge.amount = {$totalPaidAmount}";
412 if ($adjustTotalAmount) {
413 $newTotalAmount = ($actualPledgeAmount +
($actualAmount - $pledgeScheduledAmount));
414 $totalAmountClause = ", civicrm_pledge.amount = {$newTotalAmount}";
415 if (!$paymentContributionId) {
416 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', $payments, 'scheduled_amount', $actualAmount);
421 $cancelDateClause = $endDateClause = NULL;
422 // update pledge and payment status if status is Completed/Cancelled.
423 if ($pledgeStatusID && $pledgeStatusID == array_search('Cancelled', $allStatus)) {
424 $paymentStatusID = $pledgeStatusID;
425 $cancelDateClause = ", civicrm_pledge.cancel_date = CURRENT_TIMESTAMP ";
429 $pledgeStatusID = self
::calculatePledgeStatus($pledgeID);
432 if ($pledgeStatusID == array_search('Completed', $allStatus)) {
433 $endDateClause = ", civicrm_pledge.end_date = CURRENT_TIMESTAMP ";
436 // update pledge status
438 UPDATE civicrm_pledge
439 SET civicrm_pledge.status_id = %1
440 {$cancelDateClause} {$endDateClause} {$totalAmountClause}
441 WHERE civicrm_pledge.id = %2
445 1 => [$pledgeStatusID, 'Integer'],
446 2 => [$pledgeID, 'Integer'],
449 CRM_Core_DAO
::executeQuery($query, $params);
451 return $pledgeStatusID;
455 * Calculate the base scheduled date. This function effectively 'rounds' the $params['scheduled_date'] value
456 * to the first payment date with respect to the frequency day - ie. if payments are on the 15th of the month the date returned
457 * will be the 15th of the relevant month. Then to calculate the payments you can use intervalAdd ie.
458 * CRM_Utils_Date::intervalAdd( $params['frequency_unit'], $i * ($params['frequency_interval']) , calculateBaseScheduledDate( &$params )))
460 * @param array $params
463 * Next scheduled date in the format YmdHis
465 public static function calculateBaseScheduleDate(&$params) {
467 $scheduled_date = CRM_Utils_Date
::processDate($params['scheduled_date']);
468 $date['year'] = (int) substr($scheduled_date, 0, 4);
469 $date['month'] = (int) substr($scheduled_date, 4, 2);
470 $date['day'] = (int) substr($scheduled_date, 6, 2);
471 // calculation of schedule date according to frequency day of period
472 // frequency day is not applicable for daily installments
473 if ($params['frequency_unit'] != 'day' && $params['frequency_unit'] != 'year') {
474 if ($params['frequency_unit'] != 'week') {
475 // CRM-18316: To calculate pledge scheduled dates at the end of a month.
476 $date['day'] = $params['frequency_day'];
477 $lastDayOfMonth = date('t', mktime(0, 0, 0, $date['month'], 1, $date['year']));
478 if ($lastDayOfMonth < $date['day']) {
479 $date['day'] = $lastDayOfMonth;
482 elseif ($params['frequency_unit'] == 'week') {
484 // for week calculate day of week ie. Sunday,Monday etc. as next payment date
485 $dayOfWeek = date('w', mktime(0, 0, 0, $date['month'], $date['day'], $date['year']));
486 $frequencyDay = $params['frequency_day'] - $dayOfWeek;
488 $scheduleDate = explode("-", date('n-j-Y', mktime(0, 0, 0, $date['month'],
489 $date['day'] +
$frequencyDay, $date['year']
491 $date['month'] = $scheduleDate[0];
492 $date['day'] = $scheduleDate[1];
493 $date['year'] = $scheduleDate[2];
496 $newdate = date('YmdHis', mktime(0, 0, 0, $date['month'], $date['day'], $date['year']));
501 * Calculate next scheduled pledge payment date. Function calculates next pledge payment date.
503 * @param array $params
504 * must include frequency unit & frequency interval
505 * @param int $paymentNo
506 * number of payment in sequence (e.g. 1 for first calculated payment (treat initial payment as 0)
507 * @param string $basePaymentDate
508 * date to calculate payments from. This would normally be the
509 * first day of the pledge (default) & is calculated off the 'scheduled date' param. Returned date will
510 * be equal to basePaymentDate normalised to fit the 'pledge pattern' + number of installments
515 public static function calculateNextScheduledDate(&$params, $paymentNo, $basePaymentDate = NULL) {
516 $interval = $paymentNo * ($params['frequency_interval']);
517 if (!$basePaymentDate) {
518 $basePaymentDate = self
::calculateBaseScheduleDate($params);
521 //CRM-18316 - change $basePaymentDate for the end dates of the month eg: 29, 30 or 31.
522 if ($params['frequency_unit'] == 'month' && in_array($params['frequency_day'], [29, 30, 31])) {
523 $frequency = $params['frequency_day'];
524 extract(date_parse($basePaymentDate));
525 $lastDayOfMonth = date('t', mktime($hour, $minute, $second, $month +
$interval, 1, $year));
526 // Take the last day in case the current month is Feb or frequency_day is set to 31.
527 if (in_array($lastDayOfMonth, [28, 29]) ||
$frequency == 31) {
538 return CRM_Utils_Date
::format(
539 CRM_Utils_Date
::intervalAdd(
540 $params['frequency_unit'],
548 * Calculate the pledge status.
550 * @param int $pledgeId
554 * $statusId calculated status id of pledge
556 public static function calculatePledgeStatus($pledgeId) {
557 $paymentStatusTypes = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
558 $pledgeStatusTypes = CRM_Pledge_BAO_Pledge
::buildOptions('status_id', 'validate');
560 //return if the pledge is cancelled.
561 $currentPledgeStatusId = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeId, 'status_id', 'id', TRUE);
562 if ($currentPledgeStatusId == array_search('Cancelled', $pledgeStatusTypes)) {
563 return $currentPledgeStatusId;
566 // retrieve all pledge payments for this particular pledge
567 $allPledgePayments = $allStatus = [];
568 $returnProperties = ['status_id'];
569 CRM_Core_DAO
::commonRetrieveAll('CRM_Pledge_DAO_PledgePayment', 'pledge_id', $pledgeId, $allPledgePayments, $returnProperties);
571 // build pledge payment statuses
572 foreach ($allPledgePayments as $key => $value) {
573 $allStatus[$value['id']] = $paymentStatusTypes[$value['status_id']];
576 if (array_search('Overdue', $allStatus)) {
577 $statusId = array_search('Overdue', $pledgeStatusTypes);
579 elseif (array_search('Completed', $allStatus)) {
580 if (count(array_count_values($allStatus)) == 1) {
581 $statusId = array_search('Completed', $pledgeStatusTypes);
584 $statusId = array_search('In Progress', $pledgeStatusTypes);
588 $statusId = array_search('Pending', $pledgeStatusTypes);
595 * Update pledge payment table.
597 * @param int $pledgeId
599 * @param int $paymentStatusId
600 * Payment status id to set.
601 * @param array $paymentIds
602 * Payment ids to be updated.
603 * @param float|int $actualAmount , actual amount being paid
604 * @param int $contributionId
605 * , Id of associated contribution when payment is recorded.
606 * @param bool $isScriptUpdate
607 * , is function being called from bin script?.
610 public static function updatePledgePayments(
615 $contributionId = NULL,
616 $isScriptUpdate = FALSE
618 $allStatus = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
619 $paymentClause = NULL;
620 if (!empty($paymentIds)) {
621 $payments = implode(',', $paymentIds);
622 $paymentClause = " AND civicrm_pledge_payment.id IN ( {$payments} )";
624 elseif ($paymentStatusId == array_search('Cancelled', $allStatus)) {
625 $completedStatus = array_search('Completed', $allStatus);
626 $paymentClause = " AND civicrm_pledge_payment.status_id != {$completedStatus}";
628 $actualAmountClause = NULL;
629 $contributionIdClause = NULL;
630 if (isset($contributionId) && !$isScriptUpdate) {
631 $contributionIdClause = ", civicrm_pledge_payment.contribution_id = {$contributionId}";
632 $actualAmountClause = ", civicrm_pledge_payment.actual_amount = {$actualAmount}";
636 UPDATE civicrm_pledge_payment
637 SET civicrm_pledge_payment.status_id = {$paymentStatusId}
638 {$actualAmountClause} {$contributionIdClause}
639 WHERE civicrm_pledge_payment.pledge_id = %1
643 CRM_Core_DAO
::executeQuery($query, [1 => [$pledgeId, 'Integer']]);
647 * Update pledge payment table when reminder is sent.
649 * @param int $paymentId
652 public static function updateReminderDetails($paymentId) {
654 UPDATE civicrm_pledge_payment
655 SET civicrm_pledge_payment.reminder_date = CURRENT_TIMESTAMP,
656 civicrm_pledge_payment.reminder_count = civicrm_pledge_payment.reminder_count + 1
657 WHERE civicrm_pledge_payment.id = {$paymentId}
659 $dao = CRM_Core_DAO
::executeQuery($query);
663 * Get oldest pending or in progress pledge payments.
665 * @param int $pledgeID
671 * associated array of pledge details
673 public static function getOldestPledgePayment($pledgeID, $limit = 1) {
674 // get pending / overdue statuses
675 $pledgeStatuses = CRM_Core_OptionGroup
::values('pledge_status',
676 FALSE, FALSE, FALSE, NULL, 'name'
679 // get pending and overdue payments
680 $status[] = array_search('Pending', $pledgeStatuses);
681 $status[] = array_search('Overdue', $pledgeStatuses);
683 $statusClause = " IN (" . implode(',', $status) . ")";
686 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
687 FROM civicrm_pledge, civicrm_pledge_payment
688 WHERE civicrm_pledge.id = civicrm_pledge_payment.pledge_id
689 AND civicrm_pledge_payment.status_id {$statusClause}
690 AND civicrm_pledge.id = %1
691 ORDER BY civicrm_pledge_payment.scheduled_date ASC
695 $params[1] = [$pledgeID, 'Integer'];
696 $params[2] = [$limit, 'Integer'];
697 $payment = CRM_Core_DAO
::executeQuery($query, $params);
699 $paymentDetails = [];
700 while ($payment->fetch()) {
701 $paymentDetails[] = [
702 'id' => $payment->id
,
703 'amount' => $payment->amount
,
704 'currency' => $payment->currency
,
705 'schedule_date' => $payment->scheduled_date
,
706 'financial_type_id' => $payment->financial_type_id
,
711 return end($paymentDetails);
715 * @param int $pledgeID
716 * @param $actualAmount
717 * @param $pledgeScheduledAmount
718 * @param int $paymentContributionId
719 * @param int $pPaymentId
720 * @param int $paymentStatusID
722 public static function adjustPledgePayment($pledgeID, $actualAmount, $pledgeScheduledAmount, $paymentContributionId = NULL, $pPaymentId = NULL, $paymentStatusID = NULL) {
723 $allStatus = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
724 $paymentStatusName = CRM_Core_PseudoConstant
::getName('CRM_Pledge_BAO_PledgePayment', 'status_id', $paymentStatusID);
725 if ($paymentStatusName == 'Cancelled'||
$paymentStatusName == 'Refunded') {
727 SELECT civicrm_pledge_payment.id id
728 FROM civicrm_pledge_payment
729 WHERE civicrm_pledge_payment.contribution_id = {$paymentContributionId}
731 $paymentsAffected = CRM_Core_DAO
::executeQuery($query);
733 while ($paymentsAffected->fetch()) {
734 $paymentIDs[] = $paymentsAffected->id
;
736 // Reset the affected values by the amount paid more than the scheduled amount
737 foreach ($paymentIDs as $key => $value) {
738 $payment = new CRM_Pledge_DAO_PledgePayment();
739 $payment->id
= $value;
740 if ($payment->find(TRUE)) {
741 $payment->contribution_id
= 'null';
742 $payment->status_id
= array_search('Pending', $allStatus);
743 $payment->scheduled_date
= NULL;
744 $payment->reminder_date
= NULL;
745 $payment->scheduled_amount
= $pledgeScheduledAmount;
746 $payment->actual_amount
= 'null';
751 // Cancel the initial paid amount
752 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', reset($paymentIDs), 'status_id', $paymentStatusID, 'id');
753 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', reset($paymentIDs), 'actual_amount', $actualAmount, 'id');
755 // Add new payment after the last payment for the pledge
756 $allPayments = self
::getPledgePayments($pledgeID);
757 $lastPayment = array_pop($allPayments);
759 $pledgeFrequencyUnit = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'frequency_unit', 'id');
760 $pledgeFrequencyInterval = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'frequency_interval', 'id');
761 $pledgeScheduledDate = $lastPayment['scheduled_date'];
762 $scheduled_date = CRM_Utils_Date
::processDate($pledgeScheduledDate);
763 $date['year'] = (int) substr($scheduled_date, 0, 4);
764 $date['month'] = (int) substr($scheduled_date, 4, 2);
765 $date['day'] = (int) substr($scheduled_date, 6, 2);
766 $newDate = date('YmdHis', mktime(0, 0, 0, $date['month'], $date['day'], $date['year']));
767 $ScheduledDate = CRM_Utils_Date
::format(CRM_Utils_Date
::intervalAdd($pledgeFrequencyUnit, $pledgeFrequencyInterval, $newDate));
769 'status_id' => array_search('Pending', $allStatus),
770 'pledge_id' => $pledgeID,
771 'scheduled_amount' => $pledgeScheduledAmount,
772 'scheduled_date' => $ScheduledDate,
774 $payment = self
::add($pledgeParams);
777 $nextPledgeInstallmentDue = self
::getOldestPledgePayment($pledgeID);
778 if (!$paymentContributionId) {
779 // means we are editing payment scheduled payment, so get the second pending to update.
780 $nextPledgeInstallmentDue = self
::getOldestPledgePayment($pledgeID, 2);
781 if (($nextPledgeInstallmentDue['count'] != 1) && ($nextPledgeInstallmentDue['id'] == $pPaymentId)) {
782 $nextPledgeInstallmentDue = self
::getOldestPledgePayment($pledgeID);
786 if ($nextPledgeInstallmentDue) {
787 // not the last scheduled payment and the actual amount is less than the expected , add it to oldest pending.
788 if (($actualAmount != $pledgeScheduledAmount) && (($actualAmount < $pledgeScheduledAmount) ||
(($actualAmount - $pledgeScheduledAmount) < $nextPledgeInstallmentDue['amount']))) {
789 $oldScheduledAmount = $nextPledgeInstallmentDue['amount'];
790 $newScheduledAmount = $oldScheduledAmount +
($pledgeScheduledAmount - $actualAmount);
791 // store new amount in oldest pending payment record.
792 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment',
793 $nextPledgeInstallmentDue['id'],
797 if (CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_PledgePayment', $nextPledgeInstallmentDue['id'], 'contribution_id', 'id')) {
798 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment',
799 $nextPledgeInstallmentDue['id'],
801 $paymentContributionId
805 elseif (($actualAmount > $pledgeScheduledAmount) && (($actualAmount - $pledgeScheduledAmount) >= $nextPledgeInstallmentDue['amount'])) {
806 // 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
807 // set the actual amount of the next pending to '0', set contribution Id to current contribution Id and status as completed
808 $paymentId = [$nextPledgeInstallmentDue['id']];
809 self
::updatePledgePayments($pledgeID, array_search('Completed', $allStatus), $paymentId, 0, $paymentContributionId);
810 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', $nextPledgeInstallmentDue['id'], 'scheduled_amount', 0, 'id');
811 if (!$paymentContributionId) {
812 // means we are editing payment scheduled payment.
813 $oldestPaymentAmount = self
::getOldestPledgePayment($pledgeID, 2);
815 $newActualAmount = round(($actualAmount - $pledgeScheduledAmount), CRM_Utils_Money
::getCurrencyPrecision());
816 $newPledgeScheduledAmount = $nextPledgeInstallmentDue['amount'];
817 if (!$paymentContributionId) {
818 $newActualAmount = ($actualAmount - $pledgeScheduledAmount);
819 $newPledgeScheduledAmount = $oldestPaymentAmount['amount'];
820 // means we are editing payment scheduled payment, so update scheduled amount.
821 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment',
822 $oldestPaymentAmount['id'],
827 if ($newActualAmount > 0) {
828 self
::adjustPledgePayment($pledgeID, $newActualAmount, $newPledgeScheduledAmount, $paymentContributionId);
836 * Override buildOptions to hack out some statuses.
838 * @todo instead of using & hacking the shared optionGroup contribution_status use a separate one.
840 * @param string $fieldName
841 * @param string $context
842 * @param array $props
846 public static function buildOptions($fieldName, $context = NULL, $props = []) {
847 $result = parent
::buildOptions($fieldName, $context, $props);
848 if ($fieldName == 'status_id') {
849 $result = CRM_Pledge_BAO_Pledge
::buildOptions($fieldName, $context, $props);
850 $result = array_diff($result, ['Failed', 'In Progress']);