CiviPledge : Update Pledge and Pledge Payment Statuses / Generate Pledge Reminder Emails

KURUND: Assigning to you for review of the spec. Please get back to me with questions OR update the spec below if needed.
=================================================================================================

A cron-driven PHP script - UpdatePledge is needed to accomplish two tasks:
1. Keep Pledge and Pledge Payment statuses updated
2. Send payment reminders to contacts with active pledges based on the reminder criteria in their Pledge and their payment "schedule".

The processing logic of the script is:
1. Status updating (changing Pending to Overdue as needed):
UPDATE civicrm_pledge_payment pp
SET pp.status_id = 6
WHERE pp.status_id = 2 AND
pp.scheduled_date > today

We also need to update the civicrm_pledge.status_id to 6 (overdue) for any pledges for which a pledge_payment had a status set to overdue. Might be able to do both in a single query ??

2. Sending reminders:
For each Pledge with status = Pending, In Progress or Overdue ...

• check if contact has a useable Email (i.e. not On Hold, and Privacy setting allows us to send them email).
  If no Email, skip.
  Else...
• retrieve the pledge reminder criteria from the pledge (p) record (p.max_reminders, p.initial_reminder_day, p.additional_reminder_day)
• retrieve any pledge_payment (pp) records for the pledge where

• - - pp.status = Pending OR Overdue AND
• - - pp.reminder_count < p.max_reminders AND
• - - ( pp.reminder_date = NULL AND (pp.scheduled_date + p.initial_reminder_day >= today ) OR pp.reminder_date + p.additional_reminder_day >= today )

• For each pledge payment returned by above query, send a reminder email. The draft template for this email is in the repository at: templates/CRM/Pledge/Form/ReminderMessage.tpl. All contact and domain tokens should be available to this template so it can be modified / extended by the admin (by modifying the tpl file directly). EXAMPLES: {$contact.first_name}, {$domain.phone}. The FROM email for these reminders is "civicrm_domain.email_name" <civicrm_domain.email_address>