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 based on input parameters.
176 * It also stores all the retrieved values in the default array.
178 * @param array $params
179 * (reference ) an assoc array of name/value pairs.
180 * @param array $defaults
181 * (reference ) an assoc array to hold the flattened values.
183 * @return CRM_Pledge_BAO_PledgePayment
185 public static function retrieve(&$params, &$defaults) {
186 $payment = new CRM_Pledge_BAO_PledgePayment();
187 $payment->copyValues($params);
188 if ($payment->find(TRUE)) {
189 CRM_Core_DAO
::storeValues($payment, $defaults);
196 * Delete pledge payment.
202 public static function del($id) {
203 return (bool) self
::deleteRecord(['id' => $id]);
207 * Delete all pledge payments.
214 public static function deletePayments($id) {
215 if (!CRM_Utils_Rule
::positiveInteger($id)) {
219 $transaction = new CRM_Core_Transaction();
221 $payment = new CRM_Pledge_DAO_PledgePayment();
222 $payment->pledge_id
= $id;
224 if ($payment->find()) {
225 while ($payment->fetch()) {
226 //also delete associated contribution.
227 if ($payment->contribution_id
) {
228 CRM_Contribute_BAO_Contribution
::deleteContribution($payment->contribution_id
);
230 self
::del($payment->id
);
234 $transaction->commit();
240 * On delete contribution record update associated pledge payment and pledge.
242 * @param int $contributionID
247 public static function resetPledgePayment($contributionID) {
248 $transaction = new CRM_Core_Transaction();
250 $payment = new CRM_Pledge_DAO_PledgePayment();
251 $payment->contribution_id
= $contributionID;
252 if ($payment->find(TRUE)) {
253 $payment->contribution_id
= 'null';
254 $payment->status_id
= CRM_Core_PseudoConstant
::getKey('CRM_Pledge_BAO_Pledge', 'status_id', 'Pending');
255 $payment->scheduled_date
= NULL;
256 $payment->reminder_date
= NULL;
257 $payment->scheduled_amount
= $payment->actual_amount
;
258 $payment->actual_amount
= 'null';
261 //update pledge status.
262 $pledgeID = $payment->pledge_id
;
263 $pledgeStatusID = self
::calculatePledgeStatus($pledgeID);
264 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'status_id', $pledgeStatusID);
268 $transaction->commit();
273 * Update Pledge Payment Status.
275 * @param int $pledgeID
277 * @param array $paymentIDs
278 * Ids of pledge payment(s) to update.
279 * @param int $paymentStatusID
280 * Payment status to set.
281 * @param int $pledgeStatusID
282 * Pledge status to change (if needed).
283 * @param float|int $actualAmount , actual amount being paid
284 * @param bool $adjustTotalAmount
285 * Is amount being paid different from scheduled amount?.
286 * @param bool $isScriptUpdate
287 * Is function being called from bin script?.
290 * $newStatus, updated status id (or 0)
292 public static function updatePledgePaymentStatus(
295 $paymentStatusID = NULL,
296 $pledgeStatusID = NULL,
298 $adjustTotalAmount = FALSE,
299 $isScriptUpdate = FALSE
301 $totalAmountClause = '';
302 $paymentContributionId = NULL;
303 $editScheduled = FALSE;
306 $allStatus = CRM_Core_OptionGroup
::values('pledge_status',
307 FALSE, FALSE, FALSE, NULL, 'name', TRUE
310 // if we get do not get contribution id means we are editing the scheduled payment.
311 if (!empty($paymentIDs)) {
312 $editScheduled = FALSE;
313 $payments = implode(',', $paymentIDs);
314 $paymentContributionId = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_PledgePayment',
320 if (!$paymentContributionId) {
321 $editScheduled = TRUE;
325 // if payment ids are passed, we update payment table first, since payments statuses are not dependent on pledge status
326 $pledgeStatusName = CRM_Core_PseudoConstant
::getName('CRM_Pledge_BAO_Pledge', 'status_id', $pledgeStatusID);
327 if ((!empty($paymentIDs) ||
$pledgeStatusName == 'Cancelled') && (!$editScheduled ||
$isScriptUpdate)) {
328 if ($pledgeStatusName == 'Cancelled') {
329 $paymentStatusID = $pledgeStatusID;
332 self
::updatePledgePayments($pledgeID, $paymentStatusID, $paymentIDs, $actualAmount, $paymentContributionId, $isScriptUpdate);
334 if (!empty($paymentIDs) && $actualAmount) {
335 $payments = implode(',', $paymentIDs);
336 $pledgeScheduledAmount = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_PledgePayment',
342 $pledgeStatusId = self
::calculatePledgeStatus($pledgeID);
343 // Actual Pledge Amount
344 $actualPledgeAmount = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge',
349 // while editing scheduled we need to check if we are editing last pending
350 $lastPending = FALSE;
351 if (!$paymentContributionId) {
352 $checkPendingCount = self
::getOldestPledgePayment($pledgeID, 2);
353 if ($checkPendingCount['count'] == 1) {
358 // check if this is the last payment and adjust the actual amount.
359 if ($pledgeStatusId && $pledgeStatusId == array_search('Completed', $allStatus) ||
$lastPending) {
360 // last scheduled payment
361 if ($actualAmount >= $pledgeScheduledAmount) {
362 $adjustTotalAmount = TRUE;
364 elseif (!$adjustTotalAmount) {
365 // actual amount is less than the scheduled amount, so enter new pledge payment record
366 $pledgeFrequencyUnit = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'frequency_unit', 'id');
367 $pledgeFrequencyInterval = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'frequency_interval', 'id');
368 $pledgeScheduledDate = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_PledgePayment', $payments, 'scheduled_date', 'id');
369 $scheduled_date = CRM_Utils_Date
::processDate($pledgeScheduledDate);
370 $date['year'] = (int) substr($scheduled_date, 0, 4);
371 $date['month'] = (int) substr($scheduled_date, 4, 2);
372 $date['day'] = (int) substr($scheduled_date, 6, 2);
373 $newDate = date('YmdHis', mktime(0, 0, 0, $date['month'], $date['day'], $date['year']));
374 $ScheduledDate = CRM_Utils_Date
::format(CRM_Utils_Date
::intervalAdd($pledgeFrequencyUnit,
375 $pledgeFrequencyInterval, $newDate
378 'status_id' => array_search('Pending', $allStatus),
379 'pledge_id' => $pledgeID,
380 'scheduled_amount' => ($pledgeScheduledAmount - $actualAmount),
381 'scheduled_date' => $ScheduledDate,
383 $payment = self
::add($pledgeParams);
384 // while editing schedule, after adding a new pledge payemnt update the scheduled amount of the current payment
385 if (!$paymentContributionId) {
386 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', $payments, 'scheduled_amount', $actualAmount);
390 elseif (!$adjustTotalAmount) {
391 // not last schedule amount and also not selected to adjust Total
392 $paymentContributionId = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_PledgePayment',
397 self
::adjustPledgePayment($pledgeID, $actualAmount, $pledgeScheduledAmount, $paymentContributionId, $payments, $paymentStatusID);
398 // while editing schedule, after adding a new pledge payemnt update the scheduled amount of the current payment
399 if (!$paymentContributionId) {
400 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', $payments, 'scheduled_amount', $actualAmount);
402 // after adjusting all payments check if the actual amount was greater than the actual remaining amount , if so then update the total pledge amount.
403 $pledgeStatusId = self
::calculatePledgeStatus($pledgeID);
405 SELECT sum( civicrm_pledge_payment.actual_amount )
406 FROM civicrm_pledge_payment
407 WHERE civicrm_pledge_payment.pledge_id = %1
408 AND civicrm_pledge_payment.status_id = 1
410 $totalPaidParams = [1 => [$pledgeID, 'Integer']];
411 $totalPaidAmount = CRM_Core_DAO
::singleValueQuery($balanceQuery, $totalPaidParams);
412 $remainingTotalAmount = ($actualPledgeAmount - $totalPaidAmount);
413 if (($pledgeStatusId && $pledgeStatusId == array_search('Completed', $allStatus)) && (($actualAmount > $remainingTotalAmount) ||
($actualAmount >= $actualPledgeAmount))) {
414 $totalAmountClause = ", civicrm_pledge.amount = {$totalPaidAmount}";
417 if ($adjustTotalAmount) {
418 $newTotalAmount = ($actualPledgeAmount +
($actualAmount - $pledgeScheduledAmount));
419 $totalAmountClause = ", civicrm_pledge.amount = {$newTotalAmount}";
420 if (!$paymentContributionId) {
421 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', $payments, 'scheduled_amount', $actualAmount);
426 $cancelDateClause = $endDateClause = NULL;
427 // update pledge and payment status if status is Completed/Cancelled.
428 if ($pledgeStatusID && $pledgeStatusID == array_search('Cancelled', $allStatus)) {
429 $paymentStatusID = $pledgeStatusID;
430 $cancelDateClause = ", civicrm_pledge.cancel_date = CURRENT_TIMESTAMP ";
434 $pledgeStatusID = self
::calculatePledgeStatus($pledgeID);
437 if ($pledgeStatusID == array_search('Completed', $allStatus)) {
438 $endDateClause = ", civicrm_pledge.end_date = CURRENT_TIMESTAMP ";
441 // update pledge status
443 UPDATE civicrm_pledge
444 SET civicrm_pledge.status_id = %1
445 {$cancelDateClause} {$endDateClause} {$totalAmountClause}
446 WHERE civicrm_pledge.id = %2
450 1 => [$pledgeStatusID, 'Integer'],
451 2 => [$pledgeID, 'Integer'],
454 CRM_Core_DAO
::executeQuery($query, $params);
456 return $pledgeStatusID;
460 * Calculate the base scheduled date. This function effectively 'rounds' the $params['scheduled_date'] value
461 * to the first payment date with respect to the frequency day - ie. if payments are on the 15th of the month the date returned
462 * will be the 15th of the relevant month. Then to calculate the payments you can use intervalAdd ie.
463 * CRM_Utils_Date::intervalAdd( $params['frequency_unit'], $i * ($params['frequency_interval']) , calculateBaseScheduledDate( &$params )))
465 * @param array $params
468 * Next scheduled date as an array
470 public static function calculateBaseScheduleDate(&$params) {
472 $scheduled_date = CRM_Utils_Date
::processDate($params['scheduled_date']);
473 $date['year'] = (int) substr($scheduled_date, 0, 4);
474 $date['month'] = (int) substr($scheduled_date, 4, 2);
475 $date['day'] = (int) substr($scheduled_date, 6, 2);
476 // calculation of schedule date according to frequency day of period
477 // frequency day is not applicable for daily installments
478 if ($params['frequency_unit'] != 'day' && $params['frequency_unit'] != 'year') {
479 if ($params['frequency_unit'] != 'week') {
480 // CRM-18316: To calculate pledge scheduled dates at the end of a month.
481 $date['day'] = $params['frequency_day'];
482 $lastDayOfMonth = date('t', mktime(0, 0, 0, $date['month'], 1, $date['year']));
483 if ($lastDayOfMonth < $date['day']) {
484 $date['day'] = $lastDayOfMonth;
487 elseif ($params['frequency_unit'] == 'week') {
489 // for week calculate day of week ie. Sunday,Monday etc. as next payment date
490 $dayOfWeek = date('w', mktime(0, 0, 0, $date['month'], $date['day'], $date['year']));
491 $frequencyDay = $params['frequency_day'] - $dayOfWeek;
493 $scheduleDate = explode("-", date('n-j-Y', mktime(0, 0, 0, $date['month'],
494 $date['day'] +
$frequencyDay, $date['year']
496 $date['month'] = $scheduleDate[0];
497 $date['day'] = $scheduleDate[1];
498 $date['year'] = $scheduleDate[2];
501 $newdate = date('YmdHis', mktime(0, 0, 0, $date['month'], $date['day'], $date['year']));
506 * Calculate next scheduled pledge payment date. Function calculates next pledge payment date.
508 * @param array $params
509 * must include frequency unit & frequency interval
510 * @param int $paymentNo
511 * number of payment in sequence (e.g. 1 for first calculated payment (treat initial payment as 0)
512 * @param string $basePaymentDate
513 * date to calculate payments from. This would normally be the
514 * first day of the pledge (default) & is calculated off the 'scheduled date' param. Returned date will
515 * be equal to basePaymentDate normalised to fit the 'pledge pattern' + number of installments
520 public static function calculateNextScheduledDate(&$params, $paymentNo, $basePaymentDate = NULL) {
521 $interval = $paymentNo * ($params['frequency_interval']);
522 if (!$basePaymentDate) {
523 $basePaymentDate = self
::calculateBaseScheduleDate($params);
526 //CRM-18316 - change $basePaymentDate for the end dates of the month eg: 29, 30 or 31.
527 if ($params['frequency_unit'] == 'month' && in_array($params['frequency_day'], [29, 30, 31])) {
528 $frequency = $params['frequency_day'];
529 extract(date_parse($basePaymentDate));
530 $lastDayOfMonth = date('t', mktime($hour, $minute, $second, $month +
$interval, 1, $year));
531 // Take the last day in case the current month is Feb or frequency_day is set to 31.
532 if (in_array($lastDayOfMonth, [28, 29]) ||
$frequency == 31) {
543 return CRM_Utils_Date
::format(
544 CRM_Utils_Date
::intervalAdd(
545 $params['frequency_unit'],
553 * Calculate the pledge status.
555 * @param int $pledgeId
559 * $statusId calculated status id of pledge
561 public static function calculatePledgeStatus($pledgeId) {
562 $paymentStatusTypes = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
563 $pledgeStatusTypes = CRM_Pledge_BAO_Pledge
::buildOptions('status_id', 'validate');
565 //return if the pledge is cancelled.
566 $currentPledgeStatusId = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeId, 'status_id', 'id', TRUE);
567 if ($currentPledgeStatusId == array_search('Cancelled', $pledgeStatusTypes)) {
568 return $currentPledgeStatusId;
571 // retrieve all pledge payments for this particular pledge
572 $allPledgePayments = $allStatus = [];
573 $returnProperties = ['status_id'];
574 CRM_Core_DAO
::commonRetrieveAll('CRM_Pledge_DAO_PledgePayment', 'pledge_id', $pledgeId, $allPledgePayments, $returnProperties);
576 // build pledge payment statuses
577 foreach ($allPledgePayments as $key => $value) {
578 $allStatus[$value['id']] = $paymentStatusTypes[$value['status_id']];
581 if (array_search('Overdue', $allStatus)) {
582 $statusId = array_search('Overdue', $pledgeStatusTypes);
584 elseif (array_search('Completed', $allStatus)) {
585 if (count(array_count_values($allStatus)) == 1) {
586 $statusId = array_search('Completed', $pledgeStatusTypes);
589 $statusId = array_search('In Progress', $pledgeStatusTypes);
593 $statusId = array_search('Pending', $pledgeStatusTypes);
600 * Update pledge payment table.
602 * @param int $pledgeId
604 * @param int $paymentStatusId
605 * Payment status id to set.
606 * @param array $paymentIds
607 * Payment ids to be updated.
608 * @param float|int $actualAmount , actual amount being paid
609 * @param int $contributionId
610 * , Id of associated contribution when payment is recorded.
611 * @param bool $isScriptUpdate
612 * , is function being called from bin script?.
615 public static function updatePledgePayments(
620 $contributionId = NULL,
621 $isScriptUpdate = FALSE
623 $allStatus = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
624 $paymentClause = NULL;
625 if (!empty($paymentIds)) {
626 $payments = implode(',', $paymentIds);
627 $paymentClause = " AND civicrm_pledge_payment.id IN ( {$payments} )";
629 elseif ($paymentStatusId == array_search('Cancelled', $allStatus)) {
630 $completedStatus = array_search('Completed', $allStatus);
631 $paymentClause = " AND civicrm_pledge_payment.status_id != {$completedStatus}";
633 $actualAmountClause = NULL;
634 $contributionIdClause = NULL;
635 if (isset($contributionId) && !$isScriptUpdate) {
636 $contributionIdClause = ", civicrm_pledge_payment.contribution_id = {$contributionId}";
637 $actualAmountClause = ", civicrm_pledge_payment.actual_amount = {$actualAmount}";
641 UPDATE civicrm_pledge_payment
642 SET civicrm_pledge_payment.status_id = {$paymentStatusId}
643 {$actualAmountClause} {$contributionIdClause}
644 WHERE civicrm_pledge_payment.pledge_id = %1
648 CRM_Core_DAO
::executeQuery($query, [1 => [$pledgeId, 'Integer']]);
652 * Update pledge payment table when reminder is sent.
654 * @param int $paymentId
657 public static function updateReminderDetails($paymentId) {
659 UPDATE civicrm_pledge_payment
660 SET civicrm_pledge_payment.reminder_date = CURRENT_TIMESTAMP,
661 civicrm_pledge_payment.reminder_count = civicrm_pledge_payment.reminder_count + 1
662 WHERE civicrm_pledge_payment.id = {$paymentId}
664 $dao = CRM_Core_DAO
::executeQuery($query);
668 * Get oldest pending or in progress pledge payments.
670 * @param int $pledgeID
676 * associated array of pledge details
678 public static function getOldestPledgePayment($pledgeID, $limit = 1) {
679 // get pending / overdue statuses
680 $pledgeStatuses = CRM_Core_OptionGroup
::values('pledge_status',
681 FALSE, FALSE, FALSE, NULL, 'name'
684 // get pending and overdue payments
685 $status[] = array_search('Pending', $pledgeStatuses);
686 $status[] = array_search('Overdue', $pledgeStatuses);
688 $statusClause = " IN (" . implode(',', $status) . ")";
691 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
692 FROM civicrm_pledge, civicrm_pledge_payment
693 WHERE civicrm_pledge.id = civicrm_pledge_payment.pledge_id
694 AND civicrm_pledge_payment.status_id {$statusClause}
695 AND civicrm_pledge.id = %1
696 ORDER BY civicrm_pledge_payment.scheduled_date ASC
700 $params[1] = [$pledgeID, 'Integer'];
701 $params[2] = [$limit, 'Integer'];
702 $payment = CRM_Core_DAO
::executeQuery($query, $params);
704 $paymentDetails = [];
705 while ($payment->fetch()) {
706 $paymentDetails[] = [
707 'id' => $payment->id
,
708 'amount' => $payment->amount
,
709 'currency' => $payment->currency
,
710 'schedule_date' => $payment->scheduled_date
,
711 'financial_type_id' => $payment->financial_type_id
,
716 return end($paymentDetails);
720 * @param int $pledgeID
721 * @param $actualAmount
722 * @param $pledgeScheduledAmount
723 * @param int $paymentContributionId
724 * @param int $pPaymentId
725 * @param int $paymentStatusID
727 public static function adjustPledgePayment($pledgeID, $actualAmount, $pledgeScheduledAmount, $paymentContributionId = NULL, $pPaymentId = NULL, $paymentStatusID = NULL) {
728 $allStatus = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
729 $paymentStatusName = CRM_Core_PseudoConstant
::getName('CRM_Pledge_BAO_PledgePayment', 'status_id', $paymentStatusID);
730 if ($paymentStatusName == 'Cancelled'||
$paymentStatusName == 'Refunded') {
732 SELECT civicrm_pledge_payment.id id
733 FROM civicrm_pledge_payment
734 WHERE civicrm_pledge_payment.contribution_id = {$paymentContributionId}
736 $paymentsAffected = CRM_Core_DAO
::executeQuery($query);
738 while ($paymentsAffected->fetch()) {
739 $paymentIDs[] = $paymentsAffected->id
;
741 // Reset the affected values by the amount paid more than the scheduled amount
742 foreach ($paymentIDs as $key => $value) {
743 $payment = new CRM_Pledge_DAO_PledgePayment();
744 $payment->id
= $value;
745 if ($payment->find(TRUE)) {
746 $payment->contribution_id
= 'null';
747 $payment->status_id
= array_search('Pending', $allStatus);
748 $payment->scheduled_date
= NULL;
749 $payment->reminder_date
= NULL;
750 $payment->scheduled_amount
= $pledgeScheduledAmount;
751 $payment->actual_amount
= 'null';
756 // Cancel the initial paid amount
757 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', reset($paymentIDs), 'status_id', $paymentStatusID, 'id');
758 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', reset($paymentIDs), 'actual_amount', $actualAmount, 'id');
760 // Add new payment after the last payment for the pledge
761 $allPayments = self
::getPledgePayments($pledgeID);
762 $lastPayment = array_pop($allPayments);
764 $pledgeFrequencyUnit = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'frequency_unit', 'id');
765 $pledgeFrequencyInterval = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'frequency_interval', 'id');
766 $pledgeScheduledDate = $lastPayment['scheduled_date'];
767 $scheduled_date = CRM_Utils_Date
::processDate($pledgeScheduledDate);
768 $date['year'] = (int) substr($scheduled_date, 0, 4);
769 $date['month'] = (int) substr($scheduled_date, 4, 2);
770 $date['day'] = (int) substr($scheduled_date, 6, 2);
771 $newDate = date('YmdHis', mktime(0, 0, 0, $date['month'], $date['day'], $date['year']));
772 $ScheduledDate = CRM_Utils_Date
::format(CRM_Utils_Date
::intervalAdd($pledgeFrequencyUnit, $pledgeFrequencyInterval, $newDate));
774 'status_id' => array_search('Pending', $allStatus),
775 'pledge_id' => $pledgeID,
776 'scheduled_amount' => $pledgeScheduledAmount,
777 'scheduled_date' => $ScheduledDate,
779 $payment = self
::add($pledgeParams);
782 $nextPledgeInstallmentDue = self
::getOldestPledgePayment($pledgeID);
783 if (!$paymentContributionId) {
784 // means we are editing payment scheduled payment, so get the second pending to update.
785 $nextPledgeInstallmentDue = self
::getOldestPledgePayment($pledgeID, 2);
786 if (($nextPledgeInstallmentDue['count'] != 1) && ($nextPledgeInstallmentDue['id'] == $pPaymentId)) {
787 $nextPledgeInstallmentDue = self
::getOldestPledgePayment($pledgeID);
791 if ($nextPledgeInstallmentDue) {
792 // not the last scheduled payment and the actual amount is less than the expected , add it to oldest pending.
793 if (($actualAmount != $pledgeScheduledAmount) && (($actualAmount < $pledgeScheduledAmount) ||
(($actualAmount - $pledgeScheduledAmount) < $nextPledgeInstallmentDue['amount']))) {
794 $oldScheduledAmount = $nextPledgeInstallmentDue['amount'];
795 $newScheduledAmount = $oldScheduledAmount +
($pledgeScheduledAmount - $actualAmount);
796 // store new amount in oldest pending payment record.
797 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment',
798 $nextPledgeInstallmentDue['id'],
802 if (CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_PledgePayment', $nextPledgeInstallmentDue['id'], 'contribution_id', 'id')) {
803 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment',
804 $nextPledgeInstallmentDue['id'],
806 $paymentContributionId
810 elseif (($actualAmount > $pledgeScheduledAmount) && (($actualAmount - $pledgeScheduledAmount) >= $nextPledgeInstallmentDue['amount'])) {
811 // 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
812 // set the actual amount of the next pending to '0', set contribution Id to current contribution Id and status as completed
813 $paymentId = [$nextPledgeInstallmentDue['id']];
814 self
::updatePledgePayments($pledgeID, array_search('Completed', $allStatus), $paymentId, 0, $paymentContributionId);
815 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', $nextPledgeInstallmentDue['id'], 'scheduled_amount', 0, 'id');
816 if (!$paymentContributionId) {
817 // means we are editing payment scheduled payment.
818 $oldestPaymentAmount = self
::getOldestPledgePayment($pledgeID, 2);
820 $newActualAmount = round(($actualAmount - $pledgeScheduledAmount), CRM_Utils_Money
::getCurrencyPrecision());
821 $newPledgeScheduledAmount = $nextPledgeInstallmentDue['amount'];
822 if (!$paymentContributionId) {
823 $newActualAmount = ($actualAmount - $pledgeScheduledAmount);
824 $newPledgeScheduledAmount = $oldestPaymentAmount['amount'];
825 // means we are editing payment scheduled payment, so update scheduled amount.
826 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment',
827 $oldestPaymentAmount['id'],
832 if ($newActualAmount > 0) {
833 self
::adjustPledgePayment($pledgeID, $newActualAmount, $newPledgeScheduledAmount, $paymentContributionId);
841 * Override buildOptions to hack out some statuses.
843 * @todo instead of using & hacking the shared optionGroup contribution_status use a separate one.
845 * @param string $fieldName
846 * @param string $context
847 * @param array $props
851 public static function buildOptions($fieldName, $context = NULL, $props = []) {
852 $result = parent
::buildOptions($fieldName, $context, $props);
853 if ($fieldName == 'status_id') {
854 $result = CRM_Pledge_BAO_Pledge
::buildOptions($fieldName, $context, $props);
855 $result = array_diff($result, ['Failed', 'In Progress']);