Scheduled Reminders for Events

Scheduled Reminders for Events

===============
Schema / Database
===============

1. civicrm_action_mapping
entity : civicrm_participant | civicrm_participant |
entity_value : event_type | civicrm_event |
entity_value_label : Event Type | Event Name |
entity_status : civicrm_participant_status_type | civicrm_participant_status_type |
entity_status_label : Participant Status | Participant Status |
entity_date_start : event_start_date | event_start_date |
entity_date_end : event_end_date | event_end_date |
entity_recipient : event_contacts| event_contacts |

2. civicrm_option_group
A new group for participants for recipients(they can either be role based OR status based)
name = event_recipients

3. civicrm_option_value
values corresponding to event_recipients
> civicrm_participant_status_type
> participant_role

3. civicrm_action_schedule

• Define a new column:
  absolute_date date
  (for specific date driven reminder, for instance send an email to all participants on new years eve, absolute_date = 31-12-2011)

=============
Implementation
=============

1. Schedule Reminders Form

1.1 Should be able to configure Entity : Event Type and Event Name and auto load 1st multi-select accordingly for event types or event names and the 2nd multi-select should load the participant statuses in either case.

1.2 When : Add a Date field for specifying absolute dates OR the current block of date and repetition block.
If a date has been selected hide the entire block and vice versa.
FormRule : When Date > now
If absolute_date is specified, all the DB fields
start_action_offset, start_action_unit, start_action_condition, start_action_date, is_repeat, repetition_frequency_unit, repetition_frequency_interval, end_frequency_unit, end_frequency_interval, end_action, end_date are set to NULL.

1.3 On choosing event entity for scheduling reminders, Recipient(s) drop down should 've the following options.
• Participant Status (is a hier-select that allows to select a particular Status)
• Participant Role (is a hier-select that allows to select a particular Role)
• Choose Recipient
• CiviCRM Group

1.4 Ability to include event information in the message as "civicrm mail tokens". The list of tokens will include:
• Event ID
• Event Title
• Event start and end dates
• Event Type
• Event Summary
• Event Description
• Event Contact email and phone
• Event Location
• Event Description
• Event Fees
• Event Info URL
• Event Registration URL

2. Event Interface

2.1 Add a new tab 'Schedule Reminders' between Online Registration tab and Tell a Friend tab.
Scenarios :

• If no reminders for the event, show the following form when tab is clicked.
• Saving reminder leaves you on the tab but with selector displayed
• If 1 or more reminders exist when tab is clicked, show the selector, plus an 'Add Reminder' button
• Clicking edit on an existing reminder row replaces the selector (in the tab) with the Reminder form (edit mode). Saving the reminder reloads the selector (in the tab).

Schedule Reminders Tab Form :
Interface is same as the Schedule Reminders form with one difference for Entity.
Replace Entity with a multi-select of Participant Statuses ( since at this tab/stage, we already know its an
entity 'Event Name ' and we also know which event. So basically doing away with the 1st select and and 2nd multiselect from Schedule Reminder form.