ogevents.module

<?php
// $Id$

/**
 * @file
 * The OGEvents module.
 *
 * This module provides an Active Transportation (walk/bike/other)
 * Challenge event system.  It requires CCK types for school, event,
 * and registrations.
 * @ingroup ogevents
 */

// query returns NIDs of schools (groups) registered for the
// given events (notice the IN (%s) in the query)
define('OGEVENTS_QUERY_REGISTERED_SCHOOLS',
  'SELECT DISTINCT oga.group_nid AS school_id
  FROM {og_ancestry} oga
  JOIN {content_type_registration} reg ON reg.nid = oga.nid
  JOIN {content_field_eventid} field_eventid ON field_eventid.vid = reg.vid
  JOIN {node} sn ON oga.group_nid = sn.nid
  WHERE field_eventid.field_eventid_nid IN (%s)
  ORDER BY sn.title ASC');


/** Constant used internally to indicate that final results have been submitted. */
define('OGEVENTS_RESULTS_SUBMITTED', 1); // 1 means true, 0 means false.

define('OGEVENTS_TALLYING', 1);
define('OGEVENTS_REGISTRATION', 2);

/** Enable or disable debug messages. */
define('OGEVENTS_DEBUG', FALSE);

// TODO - rewrite all href code to use drupal l($text,$path) function
// TODO - use t() on all output
// TODO - rename _functions to _ogevents_functions
// TODO - after we prevent revisions from being used, we wouldn't
//       need {node} in the above, as we'd know we have the latest
//       revision without needing to check node.vid.


// Note: This module doesn't have to be version-aware (it can always
//  use the most recent node revision).  Thus the {node} table
//  gives the most recent nid & vid.  node_revisions will have multiple
//  vid's for any node with multiple revisions, so when we're pulling
//  data we'll want to join {node} and {node_revisions} on (vid). Same
//  for {content_field_eventid} and {content_type_registration}: join
//  on (vid).

// The problem with node revisions is that we're using a (registration)
// node along with organic groups to track which schools are registered
// for which events, and if we make a new revision of a registration node
// we would need to update the og_ancestry table if the event node ref
// field changed from one event to another.
// Solution: do not support node revisions (there is really no reason to).
// TODO: form-alter the Registration node-edit form to disable the
//       revisions section.

/**
 * Print a debug msg/obj if OGEVENTS_DEBUG is set.
 *
 * Note that the devel module (which provides dpm() should be enabled.
 * @param foo A string or object to be printed.
 */
function _ogevents_debug($foo) {
  if (OGEVENTS_DEBUG) {
    if (is_string($foo)) {
      drupal_set_message($foo);
    } 
    else {
      function_exists('dpm') ? dpm($foo) : drupal_set_message('<pre><code>' . print_r($foo, TRUE) . '</code></pre>');
    }
  }
}


/**
 * Valid permissions for this module
 * @return array An array of valid permissions
 * Permissions:
 * reports -> can view download links for registered schools
 */
function ogevents_perm() {
  return array('access ogevents content', 'access ogevents reports');
}


/**
 * Implementation of hook_init.
 */
function ogevents_init() {
  drupal_add_css(drupal_get_path('module', 'ogevents')  . '/ogevents.css');
}


/**
 * Implementation of hook_menu
 *
 * This will register the following pages:
 *
 * - /dashboard : The 'My Schools' page w/ links to edit tallies.
 * - /duplicates : Administrative tool to find duplicate schools.
 *
 * - /event/$event_id/register
 *   The page to register a school for the event given in the url.
 *
 * - /event/$event_id/results
 *   The final results page w/ a table of all schools and their stats.
 *
 * - CSV Report download pages to get registrations and results:
 *   /event/$event_id/register/csv
 *   /event/$event_id/results/csv
 *
 * - /event/$event_id/tally/$school_id
 *   The page where you record trips for the given event and school.
 *
 * - /event/$event_id/thanks/$school_id
 *   A thanks/confirmation page when final results are submitted.
 */
function ogevents_menu() {
  $items = array();

  $items['dashboard'] = array(
        'title' => 'My Schools',
        'description' => 'View and record results',
        'page callback' => 'ogevents_page_dashboard',
        'access arguments' => array('access ogevents content'),
        'file' => 'ogevents.pages.inc',
        );

  $items['duplicates'] = array(
        'title' => 'Duplicate Schools',
        'description' => 'Show duplicate schools',
        'page callback' => 'ogevents_page_duplicates',
        'access arguments' => array('access ogevents reports'),
        'type' => MENU_SUGGESTED_ITEM, // admins may want to enable this menu item
        'file' => 'ogevents.pages.inc',
        );

  // we'll only present register links for events actually open to registration
  $open_event_ids = _ogevents_get_current_reg_events();
  foreach ($open_event_ids as $event_id) {
    $items["event/$event_id/register"] = array(
        'title callback' => '_ogevents_get_reg_page_title',
        'title arguments' => array(1),
        'page callback' => 'ogevents_page_register',
        'page arguments' => array(1),
        'access callback' => '_ogevents_is_registration_open',
        'access arguments' => array(1),
        'file' => 'ogevents.pages.inc',
        );
  }

  // users may want to view results for all current/past events
  // (even registration or tallying has ended).
  $published_event_ids = _ogevents_get_events();
  foreach ($published_event_ids as $event_id) {
    $items["event/$event_id/results"] = array(
        'title callback' => '_ogevents_get_results_page_title',
        'title arguments' => array(1),
        'description' => 'Results for all participating schools',
        'page callback' => 'ogevents_page_results',
        'page arguments' => array(1),
        'access callback' => '_ogevents_results_page_access',
        'access arguments' => array(1),
        'file' => 'ogevents.pages.inc',
        );
  }

  $items["event/%/register/csv"] = array(
      'title' => 'Registered Schools CSV Export',
      'description' => 'CSV export of all registered schools',
      'page callback' => 'ogevents_page_registered_csv',
      'page arguments' => array(1),
      'access callback' => '_ogevents_register_report_access',
      'access arguments' => array(1),
      'type' => MENU_CALLBACK,
      'file' => 'ogevents.pages.inc',
      );
  
  $items["event/%/results/csv"] = array(
      'title' => 'Results CSV Export',
      'description' => 'CSV export of results for all participating schools',
      'page callback' => 'ogevents_page_results_csv',
      'page arguments' => array(1),
      'access callback' => '_ogevents_results_page_access',
      'access arguments' => array(1),
      'type' => MENU_CALLBACK,
      'file' => 'ogevents.pages.inc',
      );

  $items["event/%/tally/%"] = array(
      'title' => t('Tally Trips'),
      'page callback' => 'ogevents_page_tally',
      'page arguments' => array(1,3),
      'access callback' => '_ogevents_tally_page_access',
      'access arguments' => array(1,3,TRUE),
      'type' => MENU_CALLBACK,
      'file' => 'ogevents.tally.inc',
      );
  $items["event/%/thanks/%"] = array(
      'title' => t('Thanks!'),
      'page callback' => 'ogevents_page_tally_thanks',
      'page arguments' => array(1,3),
      'access callback' => '_ogevents_tally_page_access',
      'access arguments' => array(1,3, FALSE),
      'type' => MENU_CALLBACK,
      'file' => 'ogevents.tally.inc',
      );

  return $items;
}


/**
 * Check if event is valid and user has reports permission. 
 */
function _ogevents_register_report_access($event_id) {
  $event_node = node_load($event_id);
  if (user_access('access ogevents reports') && $event_node) {
    return true;
  } 
  return false;
}

/**
 * Check if event is valid.
 */
function _ogevents_results_page_access($event_id) {
  $event_node = node_load($event_id);
  return $event_node ? true : false;
}


/** 
 * Check if the user should be on the tallying pages for the given school and event.
 * @param output_errors if TRUE, we output problems with drupal_set_message
 * @return true if access check passes, false otherwise
 */
function _ogevents_tally_page_access($event_id, $school_id, $output_errors = FALSE) {

  // if they don't have basic permission for tallying, return access denied
  if (! user_access('access ogevents content')) return false;

  $school_node = node_load($school_id); 

  // Sanity checks:
  // - does school exist?
  if (!$school_node) {
    if ($output_errors) {
      drupal_set_message(t('The school ID ("!school-id") does not exist.', array('!school-id' => $school_id)), 'error');
    }
    return false;
  }
  //   - is school registered for this event?
  $reg_nid = _ogevents_get_registration($event_id, $school_id);
  _ogevents_debug("get_reg returns:");
  _ogevents_debug($reg_nid);
  if (!$reg_nid) {
    if ($output_errors) {
      drupal_set_message(t('No registration was found for the given school ("!school-id") & event ("!event-id").',
                         array('!school-id' => $school_id, '!event-id' => $event_id)), 'error');
    }
    return false;
  }
  else {
    // - is this our school?
    $reg_node = node_load($reg_nid);
    global $user;
    if ($user->uid != $reg_node->uid) {
      if ($output_errors) {
        drupal_set_message(t('You cannot edit the tally for this school because you did not register it.'), 'error');
      }
      return false;
    }
  }
  // is tallying open?
  if (! _ogevents_is_tallying_open($event_id)) {
    if ($output_errors) {
      drupal_set_message(t('Challenge is not currently open for tallying.'), 'error');
    }
    return false;
  }

  return TRUE;
}


/** Provides title for the registration page(s). */
function _ogevents_get_reg_page_title($nid) {
  if (count(_ogevents_get_current_reg_events()) > 1) {
    $node = node_load($nid);
    $title = t("Register your school for $node->title");
  }
  else {
    $title = t('Register your school'); //TODO - Margaux may want this to say 'Register another school', but probably only after we get the big 'Register' link graphic
  }
  return $title;
}


/** Provides title for the results page(s). */
function _ogevents_get_results_page_title($event_id) {
  if (count(_ogevents_get_events()) > 1) {
    return t('Results for @event', array('@event' => _ogevents_get_node_title($event_id)));
  }
  else { // Keep things simple if only one event running
    return t('See Challenge results');
  }
}


/** Implements hook_theme() */
function ogevents_theme() {
  return array(
    'ogevents_tally_table' => array(
      'arguments' => array('tally_table' => NULL),
      'file' => 'ogevents.tally.inc',
      ),
  );
}


/**
 * Implementation of hook_nodeapi.
 * If school created, insert registration.
 * If school deleted, delete registration and tallies.
 */
function ogevents_nodeapi(&$node, $op, $a3 = NULL, $a4 = NULL) {
  switch ($op) {
    case 'insert':
      if ($node->type == 'school') {
        $newNode = _ogevents_get_blank_regnode();

        $event_id = $_GET['event'];
        $newNode->field_eventid[0]['nid'] = $event_id;
        $school_id = $node->nid; //school nid

        if ($event_id) {
          node_save($newNode);
          $sql = "INSERT INTO {og_ancestry} (nid, group_nid) VALUES (%d, %d)";
          db_query($sql, $newNode->nid, $school_id);
          drupal_set_message(_ogevents_get_post_registration_msg($event_id));
        }
        else {
          drupal_set_message("There's been an error; your school has not been registered.");
        }

        $_REQUEST['destination'] = 'dashboard';
      }
      else if ($node->type == 'event') {
        menu_rebuild(); 
      }
    break;
    case 'delete':
      if ($node->type == 'school') {
        $registrations = _ogevents_get_registrations(NULL, $node->nid);
        _ogevents_delete_registrations($registrations);

      }
      else if ($node->type == 'event') {
        $registrations = _ogevents_get_registrations($node->nid, NULL);
        _ogevents_delete_registrations($registrations);

        $nid = $node->nid;
        menu_link_delete(NULL, "event/$nid/register");
        menu_link_delete(NULL, "event/$nid/results");
      }

    break;
    case 'update':
      if ($node->type == 'event') {
        menu_rebuild();
      }
  }
}


function _ogevents_delete_registrations($registrations) {
  // Delete all registrations associated with this school,
  //   which subsequently will delete all associated tallies,
  foreach ($registrations as $reg_nid) {

    $reg_node = node_load($reg_nid);
    $event_id = $reg_node->field_eventid[0]['nid'];
    $school_id = _ogevents_get_school_from_reg($reg_nid);
    if (!$school_id) {
      drupal_set_message('Error getting school for reg node ' . $reg_nid, 'error');
      _ogevents_debug($node);
    }
    _ogevents_debug('Going to delete tallies for reg/event/school: '
        . $reg_nid . '/' . $event_id . '/' . $school_id);
    _ogevents_delete_tallies($school_id, $event_id);

    // Note that when we node_delete the registratin, the OG module deletes its data first,
    //  so when our turn at nodeapi comes, we can't figure out which school a registration
    //  is associated with.  That is why we needed to delete tallies first.
    node_delete($reg_nid);
    drupal_set_message("Deleted registration: $reg_nid");

  }
}


/**
 * Return the school ID registered for the given event, or FALSE on failure.
 */
function _ogevents_get_school_from_reg($reg_nid) {
  // A given registration ID will only show up in og_ancestry table once and
  // with only one associated school.
  $sql = 'SELECT group_nid FROM {og_ancestry} WHERE nid = %d';
  return db_result(db_query($sql, $reg_nid));
}


/**
 * Returns IDs of registrations for the given event and/or school.
 * @return array of registration IDs.
 */
function _ogevents_get_registrations($event_id = NULL, $school_id = NULL) {
  $registrations = array();
  $res = NULL;

  if (!$event_id && !$school_id) {
    $sql = 'SELECT nid FROM {node} WHERE type = "registration"';
    $res = db_query($sql);
  }
  else if ($event_id && !$school_id) {
    // find registrations associated with the given event.
    $sql = 'SELECT n.nid FROM {node} n '
         . 'JOIN {content_field_eventid} e ON n.nid = e.nid '
         . 'WHERE n.type = "registration" AND e.field_eventid_nid = %d';
    $res = db_query($sql, $event_id);
  }
  else if (!$event_id && $school_id) {
    $sql = 'SELECT n.nid FROM {node} n '
      . 'JOIN {og_ancestry} oga ON oga.nid = n.nid '
      . 'WHERE n.type = "registration" AND oga.group_nid = %d';
    $res = db_query($sql, $school_id);
  }
  else { // both specified
    return array(_ogevents_get_registration($event_id, $school_id));
  }

  while ($reg_nid = db_result($res)) {
    $registrations[] = $reg_nid;
  }
  return $registrations;
}


/**
 * Delete all tally nodes (content_type_triptally) for
 * the given school and event.
 */
function _ogevents_delete_tallies($school_id, $event_id) {
  // get node ids for tallies.
  $query = 'SELECT t.nid FROM {content_type_triptally} t '
         . 'JOIN {content_field_eventid} e ON t.nid = e.nid '
         . 'JOIN {og_ancestry} oga ON t.nid = oga.nid '
         . 'WHERE oga.group_nid = %d AND e.field_eventid_nid = %d';
  $res = db_query($query, $school_id, $event_id);
  while ($tally_nid = db_result($res)) {
    node_delete($tally_nid);
    drupal_set_message("Deleted tally: $tally_nid");
  }

  // delete cached results
  $sql = 'DELETE FROM {ogevents_results} WHERE school_id = %d AND event_id = %d';
  db_query($sql, $school_id, $event_id);
  // XXX/TODO - note that currently, deleting individual tally nodes isn't supported,
  //      and doing so through Drupal will not cause the results cache to be updated.
}


function _ogevents_get_school_coordinator_uid($school_id, $event_id) {
  $reg_nid = _ogevents_get_registration($event_id, $school_id);
  $reg_node = node_load($reg_nid);
  return ($reg_node->nid ? $reg_node->uid : NULL);
}


/**
 * Returns name (full name if set, or username) of the coordinator for the given school
 * during the specified event.  [Owner of registration node is considered coordinator.]
 * Pre-condition: The given school is registered for the given event.
 *
 * @param $as_link if TRUE, returns name as a link to contact page
                   (with redirect set to return to the current page).
 */
function _ogevents_get_school_coordinator($school_id, $event_id, $as_link) {

  $coordinator_uid = _ogevents_get_school_coordinator_uid($school_id, $event_id);
  if (! $coordinator_uid) {
   return;
  }

  return _ogevents_coordinator_name_from_uid($coordinator_uid, $as_link);
}

/**
 * Same as _ogevents_get_school_coordinator() but requires the UID.
 * Useful if you have the UID already.
 * @param $as_link if TRUE, returns name as a link to contact page
                   (with redirect set to return to the current page),
                   and defaults to TRUE.
 */
function _ogevents_coordinator_name_from_uid($coordinator_uid, $as_link = TRUE) {
  // get username or profile name
  $coordinator = user_load($coordinator_uid);
  $name = $coordinator->name;
  profile_load_profile($coordinator);
  if (!empty($coordinator->profile_fullname)) {
    $name = $coordinator->profile_fullname;
  }

  if ($as_link) { // show as link if user contact form is enabled.
    // see contact.module _contact_user_tab_access for reference

    global $user;

    // Anonymous users cannot have contact forms,
    // and users can't contact themselves.
    if (!$coordinator->uid ||
         $user->uid == $coordinator->uid) {
      return $name;
    }

    // User admins can contact any user, and
    // users with contact form set can be contacted by other users
    if (user_access('administer users') || !empty($coordinator->contact)) {
      if (!$user->uid) { //anonymous user
        // Here we're assuming logintoboggan will be used to show login for access denied pages
        $title = "Log in to contact coordinator.";
      }
      else {
        $title = "Contact coordinator.";
      }
      //link to contact page, and ensure that after submit,
      //they return here (so we don't need perm. to view user's page).
      return l($name, "user/$coordinator->uid/contact",
               array('attributes' => array('title' => $title),
                     'query' => drupal_get_destination()));
    }
  }

  // If we haven't already returned something, fall back on returning name only
  return $name;
}


/**
 * Return an array of results for the given school and event
 */
function _ogevents_get_results($school_id, $event_id) {

  $reg_nid = _ogevents_get_registration($event_id, $school_id);
  $reg_node = node_load($reg_nid);
  if ($reg_node->nid) {
    $staff = $reg_node->field_staff[0]['value'];
    $parents = $reg_node->field_parents[0]['value'];
    $participants = $reg_node->field_participants[0]['value'];
    $studentbody = $reg_node->field_studentbody[0]['value'];
    $percent = $studentbody ? ($participants / $studentbody * 100) : 0;
    $final_biketrips = $reg_node->field_biketrips[0]['value'];
    $final_walktrips = $reg_node->field_walktrips[0]['value'];
    $final_othertrips = $reg_node->field_othertrips[0]['value'];
    $finals_submitted = 
      $reg_node->field_results_submitted[0]['value'] == OGEVENTS_RESULTS_SUBMITTED ? 
      'Yes' : 'No';
  }

  $biketrips = $walktrips = $othertrips = 0;



  // pull from results.
  $sql = "SELECT bike_total, walk_total, other_total, sum_total
    FROM {ogevents_results}
  WHERE school_id = %d AND event_id = %d";
  $row = db_fetch_object(db_query($sql, $school_id, $event_id));
  $biketrips = $row->bike_total;
  $walktrips = $row->walk_total;
  $othertrips = $row->other_total;


  return array('bike_trips' => $biketrips,
               'walk_trips' => $walktrips,
               'other_trips' => $othertrips,
               'final_bike_trips' => $final_biketrips,
               'final_walk_trips' => $final_walktrips,
               'final_other_trips' => $final_othertrips,
               'final_results_submitted' => $finals_submitted,
               'staff' => $staff ? $staff : 0,
               'parents' => $parents ? $parents : 0,
               'participants' => $participants ? $participants : 0,
               'studentbody' => $studentbody ? $studentbody : 0,
               'student_percentage' => $percent);
}


/**
 * Remove all cached results.
 */
function _ogevents_clear_results() {
  $sql = "DELETE FROM {ogevents_results}";
  db_query($sql);
}


/**  Build results cache.
 * This will touch each registration and tally node associated with any event.
 * If caching any result fails, caching is aborted and FALSE is returned;
 *  returns TRUE on success.
 */
function _ogevents_build_results() {

  // foreach event
    // foreach registered school
      // get tallies, sum their values, store results.

  $events = _ogevents_get_all_events();
  foreach ($events as $event_id) {
    $num_weeks = _ogevents_get_event_weeks($event_id);
    // TODO - test upgrade path. previously didn't store #weeks, so may default to zero(?),
    // in which case we'd want to use the old value (5) as default.

    foreach (_ogevents_get_registered_schools($event_id) as $school_id) {
      $biketrips = $walktrips = $othertrips = $percent = 0;

      $reg_nid = _ogevents_get_registration($event_id, $school_id);
      $reg_node = node_load($reg_nid, NULL, TRUE);
      if ($reg_node->nid) {
        $participants = $reg_node->field_participants[0]['value'];
        $studentbody = $reg_node->field_studentbody[0]['value'];
        $percent = $studentbody ? ($participants / $studentbody * 100) : 0;
      }
      foreach (range(1, $num_weeks) as $week) {
        $tally_nid = _ogevents_get_nid_triptally($event_id, $school_id, $week);
        if ($tally_nid) {
          $tally_node = node_load($tally_nid, NULL, TRUE);
          // assert tally_node is valid result (s/be, since tally_nid is valid).
          $biketrips += $tally_node->field_biketrips[0]['value'];
          $walktrips += $tally_node->field_walktrips[0]['value'];
          $othertrips += $tally_node->field_othertrips[0]['value'];
        }
      }

      $ret = _ogevents_save_results($school_id, $event_id,
          array("bike_total" => $biketrips,
            "walk_total" => $walktrips,
            "other_total" => $othertrips,
            "sum_total" => $biketrips + $walktrips + $othertrips,
            "participation" => $percent,
            )
          );
      // If caching any results fails, we abort.
      if (!$ret) {
        drupal_set_message(t('Failed to build results table.'), 'error');
        return $ret;
      }
    }
  }

  return TRUE;
}


/**
 * Store the given results in the results table.
 * @param results An array with keys and values appropriate to match the results table schema.
 * @return the return value of drupal_write_record.
 */
function _ogevents_save_results($school_id, $event_id, $results) {
  $sql = "SELECT rid FROM {ogevents_results} WHERE school_id = %d AND event_id = %d";
  $rid = db_result(db_query($sql, $school_id, $event_id));

  $results["school_id"] = $school_id;
  $results["event_id"] = $event_id;

  if ($rid) { // update existing
    $results['rid'] = $rid;
    $ret = drupal_write_record('ogevents_results', $results, array('rid'));
  }
  else { // new record
    $ret = drupal_write_record('ogevents_results', $results);
  }

  if (!$ret) {
    _ogevents_debug('Failed to store result for rid: ' . $rid . ', school: ' . $school_id .
                    ', event: ' . $event_id . ', results: ' . print_r($results, TRUE));
  }

  return $ret;
}


/**
 * Returns array of NIDs of schools which are registered for the given event(s).
 * If the event is not specified, we return schools registered for any event open for registration.
 * @param events Either a single event NID, an array of event NIDs, or NULL.
 */
function _ogevents_get_registered_schools($event_ids = NULL) {
  if (empty($event_ids)) { // look for schools registered for any event
    $events = _ogevents_get_current_reg_events();
  } else if (is_array($event_ids)) {
    $events = $event_ids;
  } else {
    $events = array($event_ids);
  }
  $result = db_query(OGEVENTS_QUERY_REGISTERED_SCHOOLS, implode(', ', $events));

  $registered_schools = array();
  while ($row = db_fetch_object($result)) $registered_schools[] = $row->school_id;

  return $registered_schools;
}


function _ogevents_is_tallying_open($event_node) {
  return _ogevents_is_open_for($event_node, OGEVENTS_TALLYING);
}

function _ogevents_is_registration_open($event_node) {
  return _ogevents_is_open_for($event_node, OGEVENTS_REGISTRATION);
}

/**
 * Returns true/false if if the $event_node is open for registration or tallying.
 * @param event: either a numeric node id (nid) or a node
 * @param $window OGEVENTS_REGISTRATION or OGEVENTS_TALLYING (the event window
 *        we're interested in).
 */
function _ogevents_is_open_for($event, $window) {
  $event_node = is_numeric($event) ? node_load($event) : $event;
  
  if (!$event_node) return false; //basic sanity check

  if ($window == OGEVENTS_TALLYING) {
    $field = 'field_tallydates';
  }
  else {
    $field = 'field_regdates';
  }

  $today = getdate();
  $UTC_today = $today[0];

    $datefield = $event_node->$field;

    $start_date = $datefield[0]['value'];
    $end_date = $datefield[0]['value2'];

    if (($UTC_today >= $start_date) && ($UTC_today <= $end_date)) {
      return true;
    }
    else {
      return false;
    }
}


/**
 * Return date of tally start
 */
function _ogevents_get_tally_start($event) {
  $event_node = is_numeric($event) ? node_load($event) : $event;
  return $event_node->field_tallydates[0]['value'];
}

/**
 * Return date of tally end
 */
function _ogevents_get_tally_end($event) {
  $event_node = is_numeric($event) ? node_load($event) : $event;
  _ogevents_debug($event_node);
  return $event_node->field_tallydates[0]['value2'];
}

/**
 * Get the # of weeks in an event
 */
function _ogevents_get_event_weeks($event) {
  $event_node = is_numeric($event) ? node_load($event) : $event;
  return $event_node->field_nweeks[0]['value'];
}



/** Get events which are 'current' and published.
 * Current in this context means that today's date is between the 'from' and 'to' component
 * of the specified CCK date field given by name in the parameter $field.
 *
 * @return Array of node IDs.
 */
function _ogevents_get_current_events($field) {
  $nodes = _ogevents_get_events();

  $today = getdate();
  $UTC_today = $today[0];
  $current_event_ids = array();

  //check datefield/UTC dates against current date
  foreach ($nodes as $nid) {
    $event_node = node_load($nid);
    $datefield = $event_node->$field;

    $start_date = $datefield[0]['value'];
    $end_date = $datefield[0]['value2'];

    if (($UTC_today >= $start_date) && ($UTC_today <= $end_date)) {
      $current_event_ids[] = $nid;
    }
  }
  return $current_event_ids;
}


/**
 * Returns array of nids of published event nodes.
 */
function _ogevents_get_events() {
  $result = db_query("SELECT n.nid FROM {node} n WHERE n.type = 'event' and n.status = 1");
  $nodes = array();
  while ($row = db_fetch_object($result)) {
    $nodes[] = $row->nid;
  }
  return $nodes;
}

/**
 * Returns array of nids of all events (published or not).
 */
function _ogevents_get_all_events() {
  $result = db_query("SELECT n.nid FROM {node} n WHERE n.type = 'event'");
  $nodes = array();
  while ($row = db_fetch_object($result)) {
    $nodes[] = $row->nid;
  }
  return $nodes;
}

/**
 * Get an array of all published Events for which registration is open.
 */
function _ogevents_get_current_reg_events() {
  return _ogevents_get_current_events("field_regdates");
}

/**
 * Get an array of all published Events which are now in their tallying period.
 */
function _ogevents_get_current_tally_events() {
  return _ogevents_get_current_events("field_tallydates");
}


/**
 * Return the title for the node with the given nid
 */
function _ogevents_get_node_title($nid) {
  $node = node_load($nid);
  return $node->title;
}


/** Return a template registration node object.
 *  Everything but field_eventid[0]['nid'] is filled in
 */
function _ogevents_get_blank_regnode() {
  $newNode = (object) NULL;
  $newNode->type = 'registration';
  $newNode->title = 'registration';
  $newNode->uid = $GLOBALS['user']->uid;
  $newNode->created = strtotime("now");
  $newNode->changed = strtotime("now");
  $newNode->status = 1;
  $newNode->comment = 0;
  $newNode->promote = 0;
  $newNode->moderate = 0;
  $newNode->sticky = 0;
  return $newNode;
}



/**
 * Remove unnecessary elements (mostly OG stuff) from School node-add form, plus other cleanup.
 */
function _ogevents_cleanup_school_form(&$form) {
  $form['locations'][0]['province']['#pre_render'][] = '_ogevents_cleanup_select_province';

  $form['og_private']['#type'] = 'hidden';
  $form['og_description']['#required'] = FALSE;
  $form['og_language']['#type'] = 'hidden';

  unset($form['buttons']['preview']);

  return $form;
}


/**
 * Turn states into a drop-down, and only show OR/WA
 */
function _ogevents_cleanup_select_province($element) {
  /* If we want all states shown, uncomment this.  But for now we'll just hard-code OR/WA as options.
  $provinces = location_get_provinces('us');
  if (!empty($provinces)) {
    while (list($code, $name) = each($provinces)) {
      $matches[$name] = $name;
    }
  }
  */
  $element['#multiple'] = FALSE;
  $element['#type'] = 'select';
  $element['#options'] = array("Oregon" => "Oregon", "Washington" => "Washington");
  unset($element['#size']);
  return $element;
}


/** Simplify form and change what happens on submit.
 */
function ogevents_form_school_node_form_alter(&$form, &$form_state) {

  $form['#after_build'][] = '_ogevents_cleanup_school_form';
  $form['#submit'][] = 'ogevents_school_form_submit';
  $form['#validate'][] = 'ogevents_form_school_prevent_duplicates_validate';

  if (isset($form_state['post']['name'])) {
    drupal_set_message("Your School has been registered.");
    $form['#redirect'] = 'dashboard';
  }
  return $form;
}


/**
 * Do not allow identical schools (same name/city) from the school-node-add form.
 */
function ogevents_form_school_prevent_duplicates_validate($form, &$form_state) {

    $name = $form_state['values']['title'];
    $city = $form_state['values']['locations'][0]['city'];

    //query for count of identical schools
    $query = "SELECT COUNT(*) FROM {node} n
              JOIN {location_instance} li ON n.nid = li.nid
              JOIN {location} l ON li.lid = l.lid
              WHERE n.title = '%s' AND l.city = '%s'";

    $school_id = $form_state['values']['nid']; // will be non-empty on updates
    if (!empty($school_id)) { // when seeking duplicates, ignore this node.
      $query .= " AND n.nid <> $school_id";
    }

    $count = db_result(db_query($query, $name, $city));

    if ($count > 0) {
      $err_msg = "A school with the same name and city already exists.";
      $event_id = $_GET['event'];
      if (! empty($event_id)) {
        $err_msg .= ' <a href="/event/' . $event_id . '/register">Find it here.</a>';
      }
      form_set_error('edit-title', t($err_msg));
    }
}


function ogevents_school_form_submit($form, &$form_state) {
  // Setup form values so we're ready to add an organic group too.
  $form_state['values']['og_description'] = $form_state['values']['title']; // og desc = <school name>
  $form_state['values']['og_register'] = 0; // don't allow for group joins during user registration
  $form_state['values']['og_private'] = 0; // always public
  $form['locations'][0]['name'] = $form_state['values']['title']; // school location = <school name>
  //TODO: I think location name and og_description could be left blank. (Are they used elsewhere?)
}


/**
 * Returns text to send to drupal_set_message after registering a school.
 */
function _ogevents_get_post_registration_msg($event_id) {
  return t('Congratulations, your school has been registered. ' .
           'You may log trips via My Schools below, or <a href="@register-page">Register another school</a>.',
           array('@register-page' => url("event/$event_id/register")));
}




/**
 * Find the node id of the triptally for the given school, event, week.
 * @param $event_id The node id of the event we're interested in.
 * @param $school_id The school (group) node ID we're interested in.
 * @param $week The week number (1-5) we want.
 * @return nid for desired triptally node.
 */
function _ogevents_get_nid_triptally($event_id, $school_id, $week) {
  $query = "SELECT t.nid FROM {content_type_triptally} t
  JOIN {og_ancestry} oga ON t.nid = oga.nid
  JOIN {content_field_eventid} e ON t.vid = e.vid
  WHERE oga.group_nid = %d AND e.field_eventid_nid = %d AND t.field_week_value = %d";
  // assert - there is only one tally per event/group/week
  return db_result(db_query($query, $school_id, $event_id, $week));
}


/**
 * Returns node id of registration node for given event and school (school_id).
 */
function _ogevents_get_registration($event_id, $school_id) {
  $query = "SELECT r.nid
            FROM {og_ancestry} oga
            JOIN {content_type_registration} r ON oga.nid = r.nid
            JOIN {content_field_eventid} e ON r.vid = e.vid
            JOIN {node} n ON n.vid = r.vid
            WHERE n.type = 'registration'
            AND e.field_eventid_nid = %d AND oga.group_nid = %d";
  // assert: there can only be one registration per school+event
  return db_result(db_query($query, $event_id, $school_id));
}