public static function updatePledgeStatus($params)
{
$returnMessages = array();
$sendReminders = CRM_Utils_Array::value('send_reminders', $params, FALSE);
$allStatus = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name');
//unset statues that we never use for pledges
foreach (array('Completed', 'Cancelled', 'Failed') as $statusKey) {
if ($key = CRM_Utils_Array::key($statusKey, $allStatus)) {
unset($allStatus[$key]);
}
}
$statusIds = implode(',', array_keys($allStatus));
$updateCnt = 0;
$query = "\nSELECT pledge.contact_id as contact_id,\n pledge.id as pledge_id,\n pledge.amount as amount,\n payment.scheduled_date as scheduled_date,\n pledge.create_date as create_date,\n payment.id as payment_id,\n pledge.currency as currency,\n pledge.contribution_page_id as contribution_page_id,\n payment.reminder_count as reminder_count,\n pledge.max_reminders as max_reminders,\n payment.reminder_date as reminder_date,\n pledge.initial_reminder_day as initial_reminder_day,\n pledge.additional_reminder_day as additional_reminder_day,\n pledge.status_id as pledge_status,\n payment.status_id as payment_status,\n pledge.is_test as is_test,\n pledge.campaign_id as campaign_id,\n SUM(payment.scheduled_amount) as amount_due,\n ( SELECT sum(civicrm_pledge_payment.actual_amount)\n FROM civicrm_pledge_payment\n WHERE civicrm_pledge_payment.status_id = 1\n AND civicrm_pledge_payment.pledge_id = pledge.id\n ) as amount_paid\n FROM civicrm_pledge pledge, civicrm_pledge_payment payment\n WHERE pledge.id = payment.pledge_id\n AND payment.status_id IN ( {$statusIds} ) AND pledge.status_id IN ( {$statusIds} )\n GROUP By payment.id\n ";
$dao = CRM_Core_DAO::executeQuery($query);
$now = date('Ymd');
$pledgeDetails = $contactIds = $pledgePayments = $pledgeStatus = array();
while ($dao->fetch()) {
$checksumValue = CRM_Contact_BAO_Contact_Utils::generateChecksum($dao->contact_id);
$pledgeDetails[$dao->payment_id] = array('scheduled_date' => $dao->scheduled_date, 'amount_due' => $dao->amount_due, 'amount' => $dao->amount, 'amount_paid' => $dao->amount_paid, 'create_date' => $dao->create_date, 'contact_id' => $dao->contact_id, 'pledge_id' => $dao->pledge_id, 'checksumValue' => $checksumValue, 'contribution_page_id' => $dao->contribution_page_id, 'reminder_count' => $dao->reminder_count, 'max_reminders' => $dao->max_reminders, 'reminder_date' => $dao->reminder_date, 'initial_reminder_day' => $dao->initial_reminder_day, 'additional_reminder_day' => $dao->additional_reminder_day, 'pledge_status' => $dao->pledge_status, 'payment_status' => $dao->payment_status, 'is_test' => $dao->is_test, 'currency' => $dao->currency, 'campaign_id' => $dao->campaign_id);
$contactIds[$dao->contact_id] = $dao->contact_id;
$pledgeStatus[$dao->pledge_id] = $dao->pledge_status;
if (CRM_Utils_Date::overdue(CRM_Utils_Date::customFormat($dao->scheduled_date, '%Y%m%d'), $now) && $dao->payment_status != array_search('Overdue', $allStatus)) {
$pledgePayments[$dao->pledge_id][$dao->payment_id] = $dao->payment_id;
}
}
// process the updating script...
foreach ($pledgePayments as $pledgeId => $paymentIds) {
// 1. update the pledge /pledge payment status. returns new status when an update happens
$returnMessages[] = "Checking if status update is needed for Pledge Id: {$pledgeId} (current status is {$allStatus[$pledgeStatus[$pledgeId]]})";
$newStatus = CRM_Pledge_BAO_PledgePayment::updatePledgePaymentStatus($pledgeId, $paymentIds, array_search('Overdue', $allStatus), NULL, 0, FALSE, TRUE);
if ($newStatus != $pledgeStatus[$pledgeId]) {
$returnMessages[] = "- status updated to: {$allStatus[$newStatus]}";
$updateCnt += 1;
}
}
if ($sendReminders) {
// retrieve domain tokens
$domain = CRM_Core_BAO_Domain::getDomain();
$tokens = array('domain' => array('name', 'phone', 'address', 'email'), 'contact' => CRM_Core_SelectValues::contactTokens());
$domainValues = array();
foreach ($tokens['domain'] as $token) {
$domainValues[$token] = CRM_Utils_Token::getDomainTokenReplacement($token, $domain);
}
//get the domain email address, since we don't carry w/ object.
$domainValue = CRM_Core_BAO_Domain::getNameAndEmail();
$domainValues['email'] = $domainValue[1];
// retrieve contact tokens
// this function does NOT return Deceased contacts since we don't want to send them email
list($contactDetails) = CRM_Utils_Token::getTokenDetails($contactIds, NULL, FALSE, FALSE, NULL, $tokens, 'CRM_UpdatePledgeRecord');
// assign domain values to template
$template = CRM_Core_Smarty::singleton();
$template->assign('domain', $domainValues);
//set receipt from
$receiptFrom = '"' . $domainValues['name'] . '" <' . $domainValues['email'] . '>';
foreach ($pledgeDetails as $paymentId => $details) {
if (array_key_exists($details['contact_id'], $contactDetails)) {
$contactId = $details['contact_id'];
$pledgerName = $contactDetails[$contactId]['display_name'];
} else {
continue;
}
if (empty($details['reminder_date'])) {
$nextReminderDate = new DateTime($details['scheduled_date']);
$nextReminderDate->modify("-" . $details['initial_reminder_day'] . "day");
$nextReminderDate = $nextReminderDate->format("Ymd");
} else {
$nextReminderDate = new DateTime($details['reminder_date']);
$nextReminderDate->modify("+" . $details['additional_reminder_day'] . "day");
$nextReminderDate = $nextReminderDate->format("Ymd");
}
if ($details['reminder_count'] < $details['max_reminders'] && $nextReminderDate <= $now) {
$toEmail = $doNotEmail = $onHold = NULL;
if (!empty($contactDetails[$contactId]['email'])) {
$toEmail = $contactDetails[$contactId]['email'];
}
if (!empty($contactDetails[$contactId]['do_not_email'])) {
$doNotEmail = $contactDetails[$contactId]['do_not_email'];
}
if (!empty($contactDetails[$contactId]['on_hold'])) {
$onHold = $contactDetails[$contactId]['on_hold'];
}
// 2. send acknowledgement mail
if ($toEmail && !($doNotEmail || $onHold)) {
//assign value to template
$template->assign('amount_paid', $details['amount_paid'] ? $details['amount_paid'] : 0);
$template->assign('contact', $contactDetails[$contactId]);
$template->assign('next_payment', $details['scheduled_date']);
$template->assign('amount_due', $details['amount_due']);
$template->assign('checksumValue', $details['checksumValue']);
$template->assign('contribution_page_id', $details['contribution_page_id']);
$template->assign('pledge_id', $details['pledge_id']);
$template->assign('scheduled_payment_date', $details['scheduled_date']);
$template->assign('amount', $details['amount']);
$template->assign('create_date', $details['create_date']);
$template->assign('currency', $details['currency']);
list($mailSent, $subject, $message, $html) = CRM_Core_BAO_MessageTemplate::sendTemplate(array('groupName' => 'msg_tpl_workflow_pledge', 'valueName' => 'pledge_reminder', 'contactId' => $contactId, 'from' => $receiptFrom, 'toName' => $pledgerName, 'toEmail' => $toEmail));
// 3. update pledge payment details
if ($mailSent) {
CRM_Pledge_BAO_PledgePayment::updateReminderDetails($paymentId);
$activityType = 'Pledge Reminder';
$activityParams = array('subject' => $subject, 'source_contact_id' => $contactId, 'source_record_id' => $paymentId, 'assignee_contact_id' => $contactId, 'activity_type_id' => CRM_Core_OptionGroup::getValue('activity_type', $activityType, 'name'), 'activity_date_time' => CRM_Utils_Date::isoToMysql($now), 'due_date_time' => CRM_Utils_Date::isoToMysql($details['scheduled_date']), 'is_test' => $details['is_test'], 'status_id' => 2, 'campaign_id' => $details['campaign_id']);
if (is_a(civicrm_api('activity', 'create', $activityParams), 'CRM_Core_Error')) {
$returnMessages[] = "Failed creating Activity for acknowledgment";
return array('is_error' => 1, 'message' => $returnMessages);
}
$returnMessages[] = "Payment reminder sent to: {$pledgerName} - {$toEmail}";
}
}
}
}
// end foreach on $pledgeDetails
}
// end if ( $sendReminders )
$returnMessages[] = "{$updateCnt} records updated.";
return array('is_error' => 0, 'messages' => implode("\n\r", $returnMessages));
}
