Sending emails on a specified interval, based on a date field

Question

So I'm trying to create a schedule that will send an email to a user on a few dates - on their birthday (in the system), and on whatever registration dates they have for a program (NOT their user registration - this is a separate date field).

So there will be between 2 and 11 date fields per person.

I would like to send an email on a certain interval - say, on their birthday, or a year after one of their other registrations.

How would I go about this? Rules doesn't seem to have any specific "send based on date field value" condition. I'm seeing something about possibly running on drush cron and checking date fields individually, but wouldn't this get resource intensive with a high number of users with up to 11 fields?

How is this (I would think fairly normal) use case usually dealt with?

Answer 1

I've built a similar feature for the Commerce Recurring module, but instead of sending mails, it creates orders. Here's how I did it.

Interval field module would become handy so you can control how often the action happens (sending mails i.e.).

You could create your own cron event or just rely in the Drupal one, here's a the rule used for generating orders, yours would probably be simpler:

  $rule = rules_reaction_rule();
  $rule->label = t('Generate recurring orders on cron run');
  $rule->tags = array('Commerce recurring');
  $rule->active = TRUE;
  $rule
    ->event('commerce_recurring_cron')
    ->action('commerce_recurring_get_due_items', array(
      'number_items' => 5,
      'timestamp:select' => 'site:current-date',
      'provides' => array('commerce_recurring_entities', 'commerce_recurring_entity'),
    ));

  $actions = array(
    'commerce_recurring:select' => 'current-recurring-entity',
    'timestamp:select' => 'site:current-date',
  );

  $loop = rules_loop(array(
    'list:select' => 'commerce-recurring-entities',
    'item:var' => 'current_recurring_entity',
    'item:label' => t('Current recurring entity'),
  ))->action('commerce_recurring_provide_order_properties', array('commerce_recurring:select' => 'current-recurring-entity'))
  ->action('commerce_recurring_generate_order_from_recurring', $actions);
  $rule->action($loop);

  $rules['commerce_recurring_cron_generate_orders'] = $rule;

The calculation of the due entities (commerce_recurring_get_due_items) is a rules action that calls this function, based in Entity Field Query to calculate those entities "due" to actions:

/**
 * Return recurring entities with due dates.
 */
function commerce_recurring_rules_get_due_items($number_items = 0, $due_date = NULL) {
  if (empty($due_date)) {
    $due_date = new DateObject();
    $due_date = $due_date->getTimestamp();
  }

  $query = new EntityFieldQuery();
  $query->entityCondition('entity_type', 'commerce_recurring', '=');
  $query->propertyCondition('status', TRUE, '=');
  $query->propertyCondition('due_date', $due_date, '<');
  if ($number_items > 0) {
    $query->range(0, $number_items);
  }
  $result = $query->execute();

  $recurring_entities = array();
  if (!empty($result['commerce_recurring'])) {
    foreach($result['commerce_recurring'] as $recurring_entity) {
      $recurring_entities[] = entity_load_single('commerce_recurring', $recurring_entity->id);
    }
  }

  return array('commerce_recurring_entities' => $recurring_entities);
}