function _signup_date_reminder_sql in Signup 6

Same name and namespace in other branches

1. 5.2 includes/date.5x-2.inc \_signup_date_reminder_sql()
2. 5.2 includes/date.5x-1.inc \_signup_date_reminder_sql()
3. 6.2 includes/date.inc \_signup_date_reminder_sql()
4. 7 includes/date.inc \_signup_date_reminder_sql()

Return value

Array of SQL clauses for cron reminder email query builder.

1 call to _signup_date_reminder_sql()

signup_reminder_sql in includes/scheduler.inc

File

includes/date.inc, line 296

Code to support using CCK date fields for time-based signup functionality.

Code

function _signup_date_reminder_sql($content_type) {

  // Get the date field information for this content type.
  $field = signup_date_field($content_type);
  $start_field = $field['database']['columns']['value']['column'];

  // Figure out what TZ we want to do the date comparisons in.
  $compare_tz = $field['tz_handling'] == 'none' ? date_default_timezone_name() : 'UTC';

  // Get a DateAPI SQL handler class for this field.
  $handler = date_field_get_sql_handler($field, $compare_tz);

  // Find the current time in the appropriate TZ for this field.
  $now_date = date_now($compare_tz);

  // Need to enclose this in ' marks to use directly in the SQL.
  $now = "'" . date_format($now_date, DATE_FORMAT_DATETIME) . "'";

  // Extract the correct SQL to represent the start time.
  $start_time = $handler
    ->sql_field($start_field);

  // Create SQL to represent the time we should start sending reminders, based
  // on the SQL for the start time and the reminder_days_before field.
  $reminder_start = $handler
    ->sql_date_math($start_time, 'SUB', 's.reminder_days_before', 'DAY');
  $reminder_stop = $handler
    ->sql_date_math($start_time, 'ADD', 1, 'HOUR');

  // The WHERE clauses are now trivial: We want to make sure a) the current
  // time is after the time we should start sending reminders, but before the
  // actual start time itself.
  $where = array(
    "{$now} >= {$reminder_start}",
    "{$now} <= {$reminder_stop}",
  );

  // See what fields to SELECT.
  $fields[] = $start_field;
  if (isset($field['database']['columns']['timezone']['column'])) {
    $fields[] = $field['database']['columns']['timezone']['column'];
  }
  $table = '{' . $field['database']['table'] . '}';
  return array(
    'fields' => $fields,
    'joins' => array(
      "INNER JOIN {$table} ON {$table}.vid = n.vid",
    ),
    'where' => $where,
  );
}