membership renewal reminder w/ email templates

Membership Renewal Reminders Using Email Templates
-------------------------------------------------------------------------------
Users will need a way to save and retrieve templates with tokens for use in recurring email communication tasks (and eventually for other types of communications (e.g. hard-copy letters, forms, etc.)

Initially we'll create the message template object, and support for parsing tokens. Then allow the templates to be used for Membership Renewal Reminders only. We'll support the same set of tokens currently available in CiviMail (and "share/generalize" the parsing/substitution code). We can allow other components / functionality to use the templates in subsequent releases (e.g. Send Email to Contacts, CiviMail, etc.)

1. Schema
1.1 civicrm_msg_template (new table/data object)

• id
• domain_id
• msg_title VARCHAR(255) COMMENT Descriptive title of message - used for template selection.
• msg_subject VARCHAR(255) COMMENT Subject for email message. Allows tokens.
• msg_text TEXT COMMENT Text formatted message. Allows tokens.
• msg_html TEXT
• is_active

1.2 civicrm_membership_type (add to existing table/data object)

• renewal_msg_id FK to civicrm_msg_template.id
• renewal_reminder_day INT Number of days prior to expiration to send renewal reminder

NOTE: Suspect we'll need a "second renewal reminder msg" id, as well as the ability to send up to X
reminders Y days apart. But let's go with the single reminder for now.

1.3 civicrm_membership (add to existing table/data object)

• reminder_date DATE When should a reminder be sent.

NOTE: This date is set at membership signup or renewal IF the membership type specifies and cleared when a reminder is sent. If and when we support multiple reminders, we can just set it to next reminder date.

1.4 civicrm_membership_log (add to existing table/data object)

• renewal_reminder_date DATE The day we sent a renewal reminder (so we can track if/when we sent reminders)

NOTE: If we extend this later to send multiple renewals, we can create a membership_log record for EACH send-reminder event. This allows us to manage frequency and max number of renewal reminders if needed.

2. Administration
2.1 Message Templates
New admin menu (control panel) option with page (selector) and form to list/edit/disable/delete/add message templates. For this version, we'll expose both the msg_text and msg_html fields as simple textarea input elements.

NOTE: When we add Ajax widgets for HTML wysiwyg and for selecting/inserting tokens into message body for CiviMail - we should add it to this interface as well.

2.2 Membership Types

• Add select form field for "Renewal Reminder Message" - select labels are msg_template.title values. If no active templates exist - don't show this field, but include the following text w/ crmURL link to Administer Message Templates:
  "If you like to send an automatic renewal reminder to members, click here to create a reminder email template."

• Add input field (text) for "Send Reminder" [ ] days prior to membership expiration"

3. Modify UpdateMembershipRecord.php (cron script)
Script should check for membership.reminder_date for each membership record. If date <= today's date, then email the renewal reminder message specified for that membership_type to the primary email address of the contact after parsing / substituting any tokens in the subject and msg_text. (We're only supporting plain text for this iteration, so the msg_html columns is ignored.) After send, set the reminder_date to NULL and insert a civicrm_membership_log record with the renewal_reminder_date set (copy current values in membership for status_id, start and end date to the log - although these are probably not changing for this "event") .