3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2018 |
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-2018
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 $overdueStatusID = CRM_Core_PseudoConstant
::getKey('CRM_Pledge_BAO_PledgePayment', 'status_id', 'Overdue');
102 $pendingStatusId = CRM_Core_PseudoConstant
::getKey('CRM_Pledge_BAO_PledgePayment', 'status_id', 'Pending');
104 //calculate the scheduled date for every installment
105 $now = date('Ymd') . '000000';
106 $statues = $prevScheduledDate = array();
107 $prevScheduledDate[1] = CRM_Utils_Date
::processDate($params['scheduled_date']);
109 if (CRM_Utils_Date
::overdue($prevScheduledDate[1], $now)) {
110 $statues[1] = $overdueStatusID;
113 $statues[1] = $pendingStatusId;
116 for ($i = 1; $i < $params['installments']; $i++
) {
117 $prevScheduledDate[$i +
1] = self
::calculateNextScheduledDate($params, $i);
118 if (CRM_Utils_Date
::overdue($prevScheduledDate[$i +
1], $now)) {
119 $statues[$i +
1] = $overdueStatusID;
122 $statues[$i +
1] = $pendingStatusId;
126 if ($params['installment_amount']) {
127 $params['scheduled_amount'] = $params['installment_amount'];
130 $params['scheduled_amount'] = round(($params['amount'] / $params['installments']), 2);
133 for ($i = 1; $i <= $params['installments']; $i++
) {
134 // calculate the scheduled amount for every installment.
135 if ($i == $params['installments']) {
136 $params['scheduled_amount'] = $params['amount'] - ($i - 1) * $params['scheduled_amount'];
138 if (!isset($params['contribution_id']) && $params['installments'] > 1) {
139 $params['status_id'] = $statues[$i];
142 $params['scheduled_date'] = $prevScheduledDate[$i];
143 $payment = self
::add($params);
144 if (is_a($payment, 'CRM_Core_Error')) {
145 $transaction->rollback();
149 // we should add contribution id to only first payment record
150 if (isset($params['contribution_id'])) {
151 unset($params['contribution_id']);
152 unset($params['actual_amount']);
156 // update pledge status
157 self
::updatePledgePaymentStatus($params['pledge_id']);
159 $transaction->commit();
164 * Add pledge payment.
166 * @param array $params
167 * Associate array of field.
169 * @return CRM_Pledge_DAO_PledgePayment
172 public static function add($params) {
173 if (!empty($params['id'])) {
174 CRM_Utils_Hook
::pre('edit', 'PledgePayment', $params['id'], $params);
177 CRM_Utils_Hook
::pre('create', 'PledgePayment', NULL, $params);
180 $payment = new CRM_Pledge_DAO_PledgePayment();
181 $payment->copyValues($params);
183 // set currency for CRM-1496
184 if (!isset($payment->currency
)) {
185 $payment->currency
= CRM_Core_Config
::singleton()->defaultCurrency
;
188 $result = $payment->save();
190 if (!empty($params['id'])) {
191 CRM_Utils_Hook
::post('edit', 'PledgePayment', $payment->id
, $payment);
194 CRM_Utils_Hook
::post('create', 'PledgePayment', $payment->id
, $payment);
201 * Retrieve DB object based on input parameters.
203 * It also stores all the retrieved values in the default array.
205 * @param array $params
206 * (reference ) an assoc array of name/value pairs.
207 * @param array $defaults
208 * (reference ) an assoc array to hold the flattened values.
210 * @return CRM_Pledge_BAO_PledgePayment
212 public static function retrieve(&$params, &$defaults) {
213 $payment = new CRM_Pledge_BAO_PledgePayment();
214 $payment->copyValues($params);
215 if ($payment->find(TRUE)) {
216 CRM_Core_DAO
::storeValues($payment, $defaults);
223 * Delete pledge payment.
230 public static function del($id) {
231 $payment = new CRM_Pledge_DAO_PledgePayment();
233 if ($payment->find()) {
236 CRM_Utils_Hook
::pre('delete', 'PledgePayment', $id, $payment);
238 $result = $payment->delete();
240 CRM_Utils_Hook
::post('delete', 'PledgePayment', $id, $payment);
250 * Delete all pledge payments.
257 public static function deletePayments($id) {
258 if (!CRM_Utils_Rule
::positiveInteger($id)) {
262 $transaction = new CRM_Core_Transaction();
264 $payment = new CRM_Pledge_DAO_PledgePayment();
265 $payment->pledge_id
= $id;
267 if ($payment->find()) {
268 while ($payment->fetch()) {
269 //also delete associated contribution.
270 if ($payment->contribution_id
) {
271 CRM_Contribute_BAO_Contribution
::deleteContribution($payment->contribution_id
);
273 self
::del($payment->id
);
277 $transaction->commit();
283 * On delete contribution record update associated pledge payment and pledge.
285 * @param int $contributionID
290 public static function resetPledgePayment($contributionID) {
291 $transaction = new CRM_Core_Transaction();
293 $payment = new CRM_Pledge_DAO_PledgePayment();
294 $payment->contribution_id
= $contributionID;
295 if ($payment->find(TRUE)) {
296 $payment->contribution_id
= 'null';
297 $payment->status_id
= CRM_Core_PseudoConstant
::getKey('CRM_Pledge_BAO_Pledge', 'status_id', 'Pending');
298 $payment->scheduled_date
= NULL;
299 $payment->reminder_date
= NULL;
300 $payment->scheduled_amount
= $payment->actual_amount
;
301 $payment->actual_amount
= 'null';
304 //update pledge status.
305 $pledgeID = $payment->pledge_id
;
306 $pledgeStatusID = self
::calculatePledgeStatus($pledgeID);
307 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'status_id', $pledgeStatusID);
312 $transaction->commit();
317 * Update Pledge Payment Status.
319 * @param int $pledgeID
321 * @param array $paymentIDs
322 * Ids of pledge payment(s) to update.
323 * @param int $paymentStatusID
324 * Payment status to set.
325 * @param int $pledgeStatusID
326 * Pledge status to change (if needed).
327 * @param float|int $actualAmount , actual amount being paid
328 * @param bool $adjustTotalAmount
329 * Is amount being paid different from scheduled amount?.
330 * @param bool $isScriptUpdate
331 * Is function being called from bin script?.
334 * $newStatus, updated status id (or 0)
336 public static function updatePledgePaymentStatus(
339 $paymentStatusID = NULL,
340 $pledgeStatusID = NULL,
342 $adjustTotalAmount = FALSE,
343 $isScriptUpdate = FALSE
345 $totalAmountClause = '';
346 $paymentContributionId = NULL;
347 $editScheduled = FALSE;
350 $allStatus = CRM_Core_OptionGroup
::values('pledge_status',
351 FALSE, FALSE, FALSE, NULL, 'name', TRUE
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 $pledgeStatusName = CRM_Core_PseudoConstant
::getName('CRM_Pledge_BAO_Pledge', 'status_id', $pledgeStatusID);
371 if ((!empty($paymentIDs) ||
$pledgeStatusName == 'Cancelled') && (!$editScheduled ||
$isScriptUpdate)) {
372 if ($pledgeStatusName == 'Cancelled') {
373 $paymentStatusID = $pledgeStatusID;
376 self
::updatePledgePayments($pledgeID, $paymentStatusID, $paymentIDs, $actualAmount, $paymentContributionId, $isScriptUpdate);
378 if (!empty($paymentIDs) && $actualAmount) {
379 $payments = implode(',', $paymentIDs);
380 $pledgeScheduledAmount = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_PledgePayment',
386 $pledgeStatusId = self
::calculatePledgeStatus($pledgeID);
387 // Actual Pledge Amount
388 $actualPledgeAmount = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge',
393 // while editing scheduled we need to check if we are editing last pending
394 $lastPending = FALSE;
395 if (!$paymentContributionId) {
396 $checkPendingCount = self
::getOldestPledgePayment($pledgeID, 2);
397 if ($checkPendingCount['count'] == 1) {
402 // check if this is the last payment and adjust the actual amount.
403 if ($pledgeStatusId && $pledgeStatusId == array_search('Completed', $allStatus) ||
$lastPending) {
404 // last scheduled payment
405 if ($actualAmount >= $pledgeScheduledAmount) {
406 $adjustTotalAmount = TRUE;
408 elseif (!$adjustTotalAmount) {
409 // actual amount is less than the scheduled amount, so enter new pledge payment record
410 $pledgeFrequencyUnit = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'frequency_unit', 'id');
411 $pledgeFrequencyInterval = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'frequency_interval', 'id');
412 $pledgeScheduledDate = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_PledgePayment', $payments, 'scheduled_date', 'id');
413 $scheduled_date = CRM_Utils_Date
::processDate($pledgeScheduledDate);
414 $date['year'] = (int) substr($scheduled_date, 0, 4);
415 $date['month'] = (int) substr($scheduled_date, 4, 2);
416 $date['day'] = (int) substr($scheduled_date, 6, 2);
417 $newDate = date('YmdHis', mktime(0, 0, 0, $date['month'], $date['day'], $date['year']));
418 $ScheduledDate = CRM_Utils_Date
::format(CRM_Utils_Date
::intervalAdd($pledgeFrequencyUnit,
419 $pledgeFrequencyInterval, $newDate
421 $pledgeParams = array(
422 'status_id' => array_search('Pending', $allStatus),
423 'pledge_id' => $pledgeID,
424 'scheduled_amount' => ($pledgeScheduledAmount - $actualAmount),
425 'scheduled_date' => $ScheduledDate,
427 $payment = self
::add($pledgeParams);
428 // while editing schedule, after adding a new pledge payemnt update the scheduled amount of the current payment
429 if (!$paymentContributionId) {
430 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', $payments, 'scheduled_amount', $actualAmount);
434 elseif (!$adjustTotalAmount) {
435 // not last schedule amount and also not selected to adjust Total
436 $paymentContributionId = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_PledgePayment',
441 self
::adjustPledgePayment($pledgeID, $actualAmount, $pledgeScheduledAmount, $paymentContributionId, $payments, $paymentStatusID);
442 // while editing schedule, after adding a new pledge payemnt update the scheduled amount of the current payment
443 if (!$paymentContributionId) {
444 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', $payments, 'scheduled_amount', $actualAmount);
446 // after adjusting all payments check if the actual amount was greater than the actual remaining amount , if so then update the total pledge amount.
447 $pledgeStatusId = self
::calculatePledgeStatus($pledgeID);
449 SELECT sum( civicrm_pledge_payment.actual_amount )
450 FROM civicrm_pledge_payment
451 WHERE civicrm_pledge_payment.pledge_id = %1
452 AND civicrm_pledge_payment.status_id = 1
454 $totalPaidParams = array(1 => array($pledgeID, 'Integer'));
455 $totalPaidAmount = CRM_Core_DAO
::singleValueQuery($balanceQuery, $totalPaidParams);
456 $remainingTotalAmount = ($actualPledgeAmount - $totalPaidAmount);
457 if (($pledgeStatusId && $pledgeStatusId == array_search('Completed', $allStatus)) && (($actualAmount > $remainingTotalAmount) ||
($actualAmount >= $actualPledgeAmount))) {
458 $totalAmountClause = ", civicrm_pledge.amount = {$totalPaidAmount}";
461 if ($adjustTotalAmount) {
462 $newTotalAmount = ($actualPledgeAmount +
($actualAmount - $pledgeScheduledAmount));
463 $totalAmountClause = ", civicrm_pledge.amount = {$newTotalAmount}";
464 if (!$paymentContributionId) {
465 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', $payments, 'scheduled_amount', $actualAmount);
470 $cancelDateClause = $endDateClause = NULL;
471 // update pledge and payment status if status is Completed/Cancelled.
472 if ($pledgeStatusID && $pledgeStatusID == array_search('Cancelled', $allStatus)) {
473 $paymentStatusID = $pledgeStatusID;
474 $cancelDateClause = ", civicrm_pledge.cancel_date = CURRENT_TIMESTAMP ";
478 $pledgeStatusID = self
::calculatePledgeStatus($pledgeID);
481 if ($pledgeStatusID == array_search('Completed', $allStatus)) {
482 $endDateClause = ", civicrm_pledge.end_date = CURRENT_TIMESTAMP ";
485 // update pledge status
487 UPDATE civicrm_pledge
488 SET civicrm_pledge.status_id = %1
489 {$cancelDateClause} {$endDateClause} {$totalAmountClause}
490 WHERE civicrm_pledge.id = %2
494 1 => array($pledgeStatusID, 'Integer'),
495 2 => array($pledgeID, 'Integer'),
498 CRM_Core_DAO
::executeQuery($query, $params);
500 return $pledgeStatusID;
504 * Calculate the base scheduled date. This function effectively 'rounds' the $params['scheduled_date'] value
505 * to the first payment date with respect to the frequency day - ie. if payments are on the 15th of the month the date returned
506 * will be the 15th of the relevant month. Then to calculate the payments you can use intervalAdd ie.
507 * CRM_Utils_Date::intervalAdd( $params['frequency_unit'], $i * ($params['frequency_interval']) , calculateBaseScheduledDate( &$params )))
509 * @param array $params
512 * Next scheduled date as an array
514 public static function calculateBaseScheduleDate(&$params) {
516 $scheduled_date = CRM_Utils_Date
::processDate($params['scheduled_date']);
517 $date['year'] = (int) substr($scheduled_date, 0, 4);
518 $date['month'] = (int) substr($scheduled_date, 4, 2);
519 $date['day'] = (int) substr($scheduled_date, 6, 2);
520 // calculation of schedule date according to frequency day of period
521 // frequency day is not applicable for daily installments
522 if ($params['frequency_unit'] != 'day' && $params['frequency_unit'] != 'year') {
523 if ($params['frequency_unit'] != 'week') {
524 // CRM-18316: To calculate pledge scheduled dates at the end of a month.
525 $date['day'] = $params['frequency_day'];
526 $lastDayOfMonth = date('t', mktime(0, 0, 0, $date['month'], 1, $date['year']));
527 if ($lastDayOfMonth < $date['day']) {
528 $date['day'] = $lastDayOfMonth;
531 elseif ($params['frequency_unit'] == 'week') {
533 // for week calculate day of week ie. Sunday,Monday etc. as next payment date
534 $dayOfWeek = date('w', mktime(0, 0, 0, $date['month'], $date['day'], $date['year']));
535 $frequencyDay = $params['frequency_day'] - $dayOfWeek;
537 $scheduleDate = explode("-", date('n-j-Y', mktime(0, 0, 0, $date['month'],
538 $date['day'] +
$frequencyDay, $date['year']
540 $date['month'] = $scheduleDate[0];
541 $date['day'] = $scheduleDate[1];
542 $date['year'] = $scheduleDate[2];
545 $newdate = date('YmdHis', mktime(0, 0, 0, $date['month'], $date['day'], $date['year']));
550 * Calculate next scheduled pledge payment date. Function calculates next pledge payment date.
552 * @param array $params
553 * must include frequency unit & frequency interval
554 * @param int $paymentNo
555 * number of payment in sequence (e.g. 1 for first calculated payment (treat initial payment as 0)
556 * @param string $basePaymentDate
557 * date to calculate payments from. This would normally be the
558 * first day of the pledge (default) & is calculated off the 'scheduled date' param. Returned date will
559 * be equal to basePaymentDate normalised to fit the 'pledge pattern' + number of installments
564 public static function calculateNextScheduledDate(&$params, $paymentNo, $basePaymentDate = NULL) {
565 $interval = $paymentNo * ($params['frequency_interval']);
566 if (!$basePaymentDate) {
567 $basePaymentDate = self
::calculateBaseScheduleDate($params);
570 //CRM-18316 - change $basePaymentDate for the end dates of the month eg: 29, 30 or 31.
571 if ($params['frequency_unit'] == 'month' && in_array($params['frequency_day'], array(29, 30, 31))) {
572 $frequency = $params['frequency_day'];
573 extract(date_parse($basePaymentDate));
574 $lastDayOfMonth = date('t', mktime($hour, $minute, $second, $month +
$interval, 1, $year));
575 // Take the last day in case the current month is Feb or frequency_day is set to 31.
576 if (in_array($lastDayOfMonth, array(28, 29)) ||
$frequency == 31) {
580 $basePaymentDate = array(
587 return CRM_Utils_Date
::format(
588 CRM_Utils_Date
::intervalAdd(
589 $params['frequency_unit'],
597 * Calculate the pledge status.
599 * @param int $pledgeId
603 * $statusId calculated status id of pledge
605 public static function calculatePledgeStatus($pledgeId) {
606 $paymentStatusTypes = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
607 $pledgeStatusTypes = CRM_Pledge_BAO_Pledge
::buildOptions('status_id', 'validate');
609 //return if the pledge is cancelled.
610 $currentPledgeStatusId = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeId, 'status_id', 'id', TRUE);
611 if ($currentPledgeStatusId == array_search('Cancelled', $pledgeStatusTypes)) {
612 return $currentPledgeStatusId;
615 // retrieve all pledge payments for this particular pledge
616 $allPledgePayments = $allStatus = array();
617 $returnProperties = array('status_id');
618 CRM_Core_DAO
::commonRetrieveAll('CRM_Pledge_DAO_PledgePayment', 'pledge_id', $pledgeId, $allPledgePayments, $returnProperties);
620 // build pledge payment statuses
621 foreach ($allPledgePayments as $key => $value) {
622 $allStatus[$value['id']] = $paymentStatusTypes[$value['status_id']];
625 if (array_search('Overdue', $allStatus)) {
626 $statusId = array_search('Overdue', $pledgeStatusTypes);
628 elseif (array_search('Completed', $allStatus)) {
629 if (count(array_count_values($allStatus)) == 1) {
630 $statusId = array_search('Completed', $pledgeStatusTypes);
633 $statusId = array_search('In Progress', $pledgeStatusTypes);
637 $statusId = array_search('Pending', $pledgeStatusTypes);
644 * Update pledge payment table.
646 * @param int $pledgeId
648 * @param int $paymentStatusId
649 * Payment status id to set.
650 * @param array $paymentIds
651 * Payment ids to be updated.
652 * @param float|int $actualAmount , actual amount being paid
653 * @param int $contributionId
654 * , Id of associated contribution when payment is recorded.
655 * @param bool $isScriptUpdate
656 * , is function being called from bin script?.
659 public static function updatePledgePayments(
664 $contributionId = NULL,
665 $isScriptUpdate = FALSE
667 $allStatus = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
668 $paymentClause = NULL;
669 if (!empty($paymentIds)) {
670 $payments = implode(',', $paymentIds);
671 $paymentClause = " AND civicrm_pledge_payment.id IN ( {$payments} )";
673 elseif ($paymentStatusId == array_search('Cancelled', $allStatus)) {
674 $completedStatus = array_search('Completed', $allStatus);
675 $paymentClause = " AND civicrm_pledge_payment.status_id != {$completedStatus}";
677 $actualAmountClause = NULL;
678 $contributionIdClause = NULL;
679 if (isset($contributionId) && !$isScriptUpdate) {
680 $contributionIdClause = ", civicrm_pledge_payment.contribution_id = {$contributionId}";
681 $actualAmountClause = ", civicrm_pledge_payment.actual_amount = {$actualAmount}";
685 UPDATE civicrm_pledge_payment
686 SET civicrm_pledge_payment.status_id = {$paymentStatusId}
687 {$actualAmountClause} {$contributionIdClause}
688 WHERE civicrm_pledge_payment.pledge_id = %1
692 CRM_Core_DAO
::executeQuery($query, array(1 => array($pledgeId, 'Integer')));
696 * Update pledge payment table when reminder is sent.
698 * @param int $paymentId
701 public static function updateReminderDetails($paymentId) {
703 UPDATE civicrm_pledge_payment
704 SET civicrm_pledge_payment.reminder_date = CURRENT_TIMESTAMP,
705 civicrm_pledge_payment.reminder_count = civicrm_pledge_payment.reminder_count + 1
706 WHERE civicrm_pledge_payment.id = {$paymentId}
708 $dao = CRM_Core_DAO
::executeQuery($query);
712 * Get oldest pending or in progress pledge payments.
714 * @param int $pledgeID
720 * associated array of pledge details
722 public static function getOldestPledgePayment($pledgeID, $limit = 1) {
723 // get pending / overdue statuses
724 $pledgeStatuses = CRM_Core_OptionGroup
::values('pledge_status',
725 FALSE, FALSE, FALSE, NULL, 'name'
728 // get pending and overdue payments
729 $status[] = array_search('Pending', $pledgeStatuses);
730 $status[] = array_search('Overdue', $pledgeStatuses);
732 $statusClause = " IN (" . implode(',', $status) . ")";
735 SELECT civicrm_pledge_payment.id id, civicrm_pledge_payment.scheduled_amount amount, civicrm_pledge_payment.currency, civicrm_pledge_payment.scheduled_date,civicrm_pledge.financial_type_id
736 FROM civicrm_pledge, civicrm_pledge_payment
737 WHERE civicrm_pledge.id = civicrm_pledge_payment.pledge_id
738 AND civicrm_pledge_payment.status_id {$statusClause}
739 AND civicrm_pledge.id = %1
740 ORDER BY civicrm_pledge_payment.scheduled_date ASC
744 $params[1] = array($pledgeID, 'Integer');
745 $params[2] = array($limit, 'Integer');
746 $payment = CRM_Core_DAO
::executeQuery($query, $params);
748 $paymentDetails = array();
749 while ($payment->fetch()) {
750 $paymentDetails[] = array(
751 'id' => $payment->id
,
752 'amount' => $payment->amount
,
753 'currency' => $payment->currency
,
754 'schedule_date' => $payment->scheduled_date
,
755 'financial_type_id' => $payment->financial_type_id
,
760 return end($paymentDetails);
764 * @param int $pledgeID
765 * @param $actualAmount
766 * @param $pledgeScheduledAmount
767 * @param int $paymentContributionId
768 * @param int $pPaymentId
769 * @param int $paymentStatusID
771 public static function adjustPledgePayment($pledgeID, $actualAmount, $pledgeScheduledAmount, $paymentContributionId = NULL, $pPaymentId = NULL, $paymentStatusID = NULL) {
772 $allStatus = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
773 $paymentStatusName = CRM_Core_PseudoConstant
::getName('CRM_Pledge_BAO_PledgePayment', 'status_id', $paymentStatusID);
774 if ($paymentStatusName == 'Cancelled'||
$paymentStatusName == 'Refunded') {
776 SELECT civicrm_pledge_payment.id id
777 FROM civicrm_pledge_payment
778 WHERE civicrm_pledge_payment.contribution_id = {$paymentContributionId}
780 $paymentsAffected = CRM_Core_DAO
::executeQuery($query);
781 $paymentIDs = array();
782 while ($paymentsAffected->fetch()) {
783 $paymentIDs[] = $paymentsAffected->id
;
785 // Reset the affected values by the amount paid more than the scheduled amount
786 foreach ($paymentIDs as $key => $value) {
787 $payment = new CRM_Pledge_DAO_PledgePayment();
788 $payment->id
= $value;
789 if ($payment->find(TRUE)) {
790 $payment->contribution_id
= 'null';
791 $payment->status_id
= array_search('Pending', $allStatus);
792 $payment->scheduled_date
= NULL;
793 $payment->reminder_date
= NULL;
794 $payment->scheduled_amount
= $pledgeScheduledAmount;
795 $payment->actual_amount
= 'null';
800 // Cancel the initial paid amount
801 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', reset($paymentIDs), 'status_id', $paymentStatusID, 'id');
802 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', reset($paymentIDs), 'actual_amount', $actualAmount, 'id');
804 // Add new payment after the last payment for the pledge
805 $allPayments = self
::getPledgePayments($pledgeID);
806 $lastPayment = array_pop($allPayments);
808 $pledgeFrequencyUnit = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'frequency_unit', 'id');
809 $pledgeFrequencyInterval = CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_Pledge', $pledgeID, 'frequency_interval', 'id');
810 $pledgeScheduledDate = $lastPayment['scheduled_date'];
811 $scheduled_date = CRM_Utils_Date
::processDate($pledgeScheduledDate);
812 $date['year'] = (int) substr($scheduled_date, 0, 4);
813 $date['month'] = (int) substr($scheduled_date, 4, 2);
814 $date['day'] = (int) substr($scheduled_date, 6, 2);
815 $newDate = date('YmdHis', mktime(0, 0, 0, $date['month'], $date['day'], $date['year']));
816 $ScheduledDate = CRM_Utils_Date
::format(CRM_Utils_Date
::intervalAdd($pledgeFrequencyUnit, $pledgeFrequencyInterval, $newDate));
817 $pledgeParams = array(
818 'status_id' => array_search('Pending', $allStatus),
819 'pledge_id' => $pledgeID,
820 'scheduled_amount' => $pledgeScheduledAmount,
821 'scheduled_date' => $ScheduledDate,
823 $payment = self
::add($pledgeParams);
826 $nextPledgeInstallmentDue = self
::getOldestPledgePayment($pledgeID);
827 if (!$paymentContributionId) {
828 // means we are editing payment scheduled payment, so get the second pending to update.
829 $nextPledgeInstallmentDue = self
::getOldestPledgePayment($pledgeID, 2);
830 if (($nextPledgeInstallmentDue['count'] != 1) && ($nextPledgeInstallmentDue['id'] == $pPaymentId)) {
831 $nextPledgeInstallmentDue = self
::getOldestPledgePayment($pledgeID);
835 if ($nextPledgeInstallmentDue) {
836 // not the last scheduled payment and the actual amount is less than the expected , add it to oldest pending.
837 if (($actualAmount != $pledgeScheduledAmount) && (($actualAmount < $pledgeScheduledAmount) ||
(($actualAmount - $pledgeScheduledAmount) < $nextPledgeInstallmentDue['amount']))) {
838 $oldScheduledAmount = $nextPledgeInstallmentDue['amount'];
839 $newScheduledAmount = $oldScheduledAmount +
($pledgeScheduledAmount - $actualAmount);
840 // store new amount in oldest pending payment record.
841 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment',
842 $nextPledgeInstallmentDue['id'],
846 if (CRM_Core_DAO
::getFieldValue('CRM_Pledge_DAO_PledgePayment', $nextPledgeInstallmentDue['id'], 'contribution_id', 'id')) {
847 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment',
848 $nextPledgeInstallmentDue['id'],
850 $paymentContributionId
854 elseif (($actualAmount > $pledgeScheduledAmount) && (($actualAmount - $pledgeScheduledAmount) >= $nextPledgeInstallmentDue['amount'])) {
855 // here the actual amount is greater than expected and also greater than the next installment amount, so update the next installment as complete and again add it to next subsequent pending payment
856 // set the actual amount of the next pending to '0', set contribution Id to current contribution Id and status as completed
857 $paymentId = array($nextPledgeInstallmentDue['id']);
858 self
::updatePledgePayments($pledgeID, array_search('Completed', $allStatus), $paymentId, 0, $paymentContributionId);
859 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment', $nextPledgeInstallmentDue['id'], 'scheduled_amount', 0, 'id');
860 if (!$paymentContributionId) {
861 // means we are editing payment scheduled payment.
862 $oldestPaymentAmount = self
::getOldestPledgePayment($pledgeID, 2);
864 $newActualAmount = round(($actualAmount - $pledgeScheduledAmount), CRM_Utils_Money
::getCurrencyPrecision());
865 $newPledgeScheduledAmount = $nextPledgeInstallmentDue['amount'];
866 if (!$paymentContributionId) {
867 $newActualAmount = ($actualAmount - $pledgeScheduledAmount);
868 $newPledgeScheduledAmount = $oldestPaymentAmount['amount'];
869 // means we are editing payment scheduled payment, so update scheduled amount.
870 CRM_Core_DAO
::setFieldValue('CRM_Pledge_DAO_PledgePayment',
871 $oldestPaymentAmount['id'],
876 if ($newActualAmount > 0) {
877 self
::adjustPledgePayment($pledgeID, $newActualAmount, $newPledgeScheduledAmount, $paymentContributionId);
885 * Override buildOptions to hack out some statuses.
887 * @todo instead of using & hacking the shared optionGroup contribution_status use a separate one.
889 * @param string $fieldName
890 * @param string $context
891 * @param array $props
895 public static function buildOptions($fieldName, $context = NULL, $props = array()) {
896 $result = parent
::buildOptions($fieldName, $context, $props);
897 if ($fieldName == 'status_id') {
898 $result = CRM_Pledge_BAO_Pledge
::buildOptions($fieldName, $context, $props);
899 $result = array_diff($result, array('Failed', 'In Progress'));