jjeff/coach/coach.module

<?php
// $Id: coach.module 4.2.05
 
/**
  * Fearless Living Coach Module
  *
  */
  
/** To Do:
    _ Let Admin (with 'manage coaches' access) create coach entry for a user.
        maybe they just explicitly enter the UID for the coach...
    _ Get variables out of db_query... use db_query properly.

/** 
 * Implementation of hook_help(). 
 * 
 * Throughout Drupal, hook_help() is used to display help text at the top of 
 * pages. Some other parts of Drupal pages get explanatory text from these hooks 
 * as well. We use it here to provide a description of the module on the 
 * module administration page. 
 */ 
function coach_help($section) {
  global $user;
  $top = "<div class='coach-help'><strong>HELP:</strong><br />
        This page is where you edit the coach information that is displayed to site visitors. Some things to note:<br />
        1) You should enter <strong>your first and last name for the title</strong> field below.<br />
        2) If you'd like to <strong>add a picture</strong> to appear with your coach listing,  upload the picture into your <a href='".url('user/'.$user->uid.'/edit')."'>user profile</a>. </div>";
  switch ($section) { 
    case 'node/add/coach':
      return $top;
    case (arg(0) == 'node' && is_numeric(arg(1)) && arg(2) == 'edit'):
      $node = node_load(array('nid' => arg(1)));
      if ($node->type == 'coach') {
        return $top;
      }
    case 'admin/modules#description': 
      // This description is shown in the listing at admin/modules. 
      return t('Coach information management.'); 
    case 'node/add#coach': 
      // This description shows up when users click "create content."
      return t('Edit coach information.');
   }
}

/** 
 * Implementation of hook_node_name(). 
 * 
 * This is a required node hook. Since our module only defines one node 
 * type, we won't implement hook_node_types(), and our hook_node_name() 
 * implementation simply returns the translated name of the node type. 
 */ 
function coach_node_name($node) { 
 return t('coach listing'); 
 } 

/** 
 * Implementation of hook_access(). 
 * 
 * Node modules may implement node_access() to determine the operations 
 * users may perform on nodes. This example uses a very common access pattern. 
 */ 
function coach_access($op, $node) { 
  global $user;
  //print_r($node);
  if ($op == 'create') {
    // Only users with permission to do so may create this node type.
    if (user_access('manage coaches') || (user_access('edit coach info') && ! coach_get_entry($user->uid))) {
      // has coach permission and hasn't created one yet
      return TRUE;
    }
  }

 // Users who create a node may edit or delete it later, assuming they have the 
 // necessary permissions. 
  if ($op == 'update' || $op == 'delete') { 
    if (user_access('manage coaches') || (user_access('edit coach info') && ($user->uid == $node->uid))) {
      return TRUE;
    }
  }
  if ($op == 'view') {
    return TRUE;
  }
} 

/** 
 * Implementation of hook_perm(). 
 * 
 * Since we are limiting the ability to create new nodes to certain users, 
 * we need to define what those permissions are here. We also define a permission 
 * to allow users to edit the nodes they created. 
 */ 
function coach_perm() {
  return array('edit coach info', 'edit coach personal page', 'manage coaches'); 
}


function coach_settings() {
  $form = form_textfield(t('Coaches Per Page'), 'coach_limit', variable_get('coach_limit', 15), 3, 3, 'The number of coaches that appear on a page.');
  $form .= form_textfield(t('Words Per Coach'), 'coach_list_trim', variable_get('coach_list_trim', 20), 3, 3, 'The number of words shown in coach\'s description when shown in a list.');
  return $form;

}

/** 
 * Implementation of hook_link(). 
 * 
 * This is implemented so that an edit link is displayed for users who have 
 * the rights to edit a node. 
 */ 

// why can't we use an 'edit' tab?
function coach_link($type, $node = 0, $main) { 
  $links = array(); 
  if ($type == 'node' && $node->type == 'coach') { 
    // Don't display a redundant edit link if they are node administrators. 
    if (coach_access('update', $node) && !user_access('manage coaches')) { 
      $links[] = l(t('edit this info'), "node/$node->nid/edit"); 
    } 
  } 
  return $links; 
}


/** 
  * Implementation of hook_menu(). 
  * 
  * In order for users to be able to add nodes of their own, we need to 
  * give them a link to the node composition form here. 
  */ 
  
function coach_menu($may_cache) { 
  $items = array();
  global $user;
  $nid = coach_get_entry($user->uid);
  if ($may_cache) {
    // create a coach entry
    $items[] = array('path' => 'node/add/coach', 'title' => t('coach listing'), 'access' => (user_access('edit coach info') && !$nid)); 
  } 
  return $items; 
} 

/** 
 * Implementation of hook_form(). 
 * 
 * Now it's time to describe the form for collecting the information 
 * specific to this node type. This hook requires us to return some HTML 
 * that will be later placed inside the form. 
 */ 
function coach_form(&$node, &$error) { 
 $output = '';
 global $user;
 profile_load_profile($user);

 // In order to be able to attach taxonomy terms to this node, we need 
 // to display the appropriate form elements. 
 if (function_exists('taxonomy_node_form')) { 
    $output .= implode('', taxonomy_node_form('coach', $node)); 
 } 
 // Now we define the form elements specific to our node type.
 $output .= form_textfield(t('First Name'), 'firstname', $node->firstname ? $node->firstname : $user->profile_firstname , 60, '', $error['firstname']);
 $output .= form_textfield(t('Last Name'), 'lastname', $node->lastname ? $node->lastname : $user->profile_lastname, 60, '', $error['lastname']); 
 $output .= form_textfield(t('Additional Certifications'), 'cert', $node->cert, 60, '', 'Your CFLC level will be added automatically. If you have more letters that go after your name, put \'em here.');
 // TODO: check access and show or hide uid
 $output .= form_hidden('cuid', $GLOBALS['user']->uid);
 if (!$node->email) {$node->email = $GLOBALS['user']->mail;}
 $output .= form_select(t('Gender'), 'sex', $node->sex ? $node->sex : $user->profile_sex, array("female" => "female","male" => "male"), ''); 
 $output .= form_textfield(t('Email Address'), 'email', $node->email, 60, '', $error['email']);
 $output .= form_textfield(t('Web Site'), 'website', $node->website ? $node->website : $user->profile_website, 60, '', 'Optional: Address of your website (<b>excluding</b> <i>http://</i>). Example: <i>www.fearlessliving.org</i>');
 $output .= form_textfield(t('Main Phone Number'), 'phone', $node->phone, 60, '');
 $output .= form_textfield(t('Alternative Phone'), 'altphone', $node->altphone, 60, '');
 $output .= form_textfield(t('Fax Number'), 'fax', $node->fax, 60, '');
 $output .= form_textarea(t('Directory Bio'), 'teaser', $node->teaser, 60, 10, 'This short bio is what appears in the coach directory and at the top of your coach page. There is a limit of 250 characters (approximately 40 words), so get the essentials in there and provide a reason for people to click on your coach page.');
 if (user_access('edit coach personal page')) {
   $output .= form_textarea(t('Coach Page Content'), 'body', $node->body, 60, 20, 'Use this area to publish expanded information about yourself and your CFLC services. You may choose to include an expanded bio, timely announcements, personal web site links, photos, rate information, or other relevant Fearless information. For detailed instructions on using the tools on this site to publish your information, download this PDF.');
 }
 $output .= filter_form('format', $node->format);
 $param = array();

 return $output; 
 } 

/** 
 * Implementation of hook_validate().
 * 
 */ 
function coach_validate(&$node) {
  if (!$_POST['edit']) {
    if (!$node->title) {
      $user = user_load(array('uid'=>$node->uid));
      $node->title = $user->profile_firstname.' '.$user->profile_lastname;
    }
  }
  if (strlen($node->teaser) > 250) {
    form_set_error('teaser', 'Your Directory Bio exceeds the 250 character limit. Please shorten and resubmit.');
  }
  if (!user_access('edit coach personal page')) {
    $node->body = '';
  }
} 

/** 
 * Implementation of hook_insert(). 
 * 
 * As a new node is being inserted into the database, we need to do our own 
 * database inserts. 
 */ 
function coach_insert($node) { 
 db_query("INSERT INTO {coach} (nid, cuid, firstname, lastname, cert, email, website, phone, altphone, fax, sex) VALUES (%d, %d, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')", $node->nid, $node->cuid, $node->firstname, $node->lastname, $node->cert, $node->email, $node->website, $node->phone, $node->altphone, $node->fax, $node->sex);
 } 

/** 
 * Implementation of hook_update(). 
 * 
 * As an existing node is being updated in the database, we need to do our own 
 * database updates. 
 */ 
function coach_update($node) { 
 db_query("UPDATE {coach} SET firstname = '%s', lastname = '%s', cert = '%s', email = '%s', website = '%s', phone = '%s', altphone = '%s', fax = '%s', sex = '%s' WHERE nid = %d", $node->firstname, $node->lastname, $node->cert, $node->email, $node->website, $node->phone, $node->altphone, $node->fax, $node->sex, $node->nid);
 } 

/** 
 * Implementation of hook_delete(). 
 * 
 * When a node is deleted, we need to clean up related tables. 
 */ 
function coach_delete($node) { 
 db_query('DELETE FROM {coach} WHERE nid = %d', $node->nid);
 } 

/** 
 * Implementation of hook_load(). 
 * 
 * Now that we've defined how to manage the node data in the database, we 
 * need to tell Drupal how to get the node back out. This hook is called 
 * every time a node is loaded, and allows us to do some loading of our own. 
 */ 
function coach_load($node) { 
 $additions = db_fetch_object(db_query('SELECT c.cuid, c.firstname, c.lastname, c.cert, c.email, c.website, c.phone, c.altphone, c.fax, c.sex, u.picture FROM {coach} c INNER JOIN {users} u ON u.uid = c.cuid WHERE nid = %d', $node->nid));
 $additions->cert = coach_cert_role($additions->cuid) . ($additions->cert ? (', '.$additions->cert) : (''));
 return $additions;
 } 

/** 
 * Implementation of hook_view(). 
 * 
 * This is a typical implementation that simply runs the node text through 
 * the output filters. 
 */ 
function coach_view(&$node, $teaser = FALSE, $page = FALSE) { 
 $node = node_prepare($node, $teaser); 
 //$output = theme('coach_out', $node, $content); 
 $node->teaser = theme('coach_teaser', $node, $node->teaser, $page); 
 $node->body = $node->teaser . theme('coach_body', $node, $node->body);
}

function coach_load_random() {
  $node = db_fetch_object(db_query('SELECT c.cuid, c.firstname, c.lastname, c.cert, c.email, c.website, c.phone, c.altphone, c.fax, c.sex, n.body, n.teaser, n.title, n.uid, n.nid, u.picture FROM {coach} c INNER JOIN {node} n ON c.nid = n.nid INNER JOIN {users} u ON n.uid = u.uid ORDER BY rand()'));
  return $node;
}

function theme_coach_teaser($node, $content, $page) {
 $output = '<div class="coach_teaser">';
  if ($node->picture) {
    $output .= theme('image', $node->picture, 'alt', 'title', array('style'=>"float:left; margin-right: 5px; margin-bottom: 5px"));
  }
  if ($node->cert) {
    $list[] = $node->cert;
  }
  $list['loc']  = $node->location['city'] ? $node->location['city'] : '';
  $list['loc'] .= ($node->location['city'] && $node->location['province']) ? ', ' : '';
  $list['loc'] .= ($node->location['province']) ? strtoupper($node->location['province']) : '';
  if ($node->phone) {
    $list[] = 'ph: '.$node->phone;
  }
  if ($node->website) {
    $list[] = '<a href = "'.(strstr($node->website, 'http') ? ($node->website) : ('http://'.$node->website)).'">'.$node->website.'</a>';
  }  
  if ($node->email) {
    $list[] = '<a href = "mailto:'.$node->email.'">'.$node->email.'</a>';
  }
  $output .= '<div class="coach_credits">';
  $output .= implode(' | ', $list);
  $output .= '</div>';
  $output .= '<div class="coach_teaser">'.$content.'</div>';
  if (!$page) {
    $output .= ' '.l(t('Read my coach page...'), 'node/'.$node->nid);
  }
  if ($node->distance) {
    $output .= '<br />distance: <strong>'.$node->distance.' '.$node->distance_unit.'</strong> from '.$node->from_zip;
  }
  $output .= '<br clear="all" /></div>'; 
 return $output; 
 }

function theme_coach_body($node, $content) {
  if (strlen(trim($content)) > 6) {
    $output = '<div class="coach_body">';
    $output .= $content;
    $output .= '<br clear="all" /></div>';
    return $output; 
  }
}


function coach_random_coach() {
  $node = coach_load_random();
  if ($node->picture) {
    $output = '<div class="coach-rand-pic">'.theme('image', $node->picture, $node->title).'</div>';
  }
  $output .= '<div class="coach-rand-name">'.$node->title.'</div>';
  $output .= '<div class="coach-rand-body">'.coach_word_trim($node->teaser, 25).' '.l('more...', 'node/'.$node->nid).'</div>';
  return $output;
}

function mt_random_coach() {
  return coach_random_coach();
}

// not used... see coach_word_trim()
function coach_word_truncate($text, $count) {
  if (!count(explode(" ", $text)) > $count) {
    return $text;
  }
  else {
    return trim(implode(" ", array_slice(explode(" ", $text), 0, $count)));
  }
}


/**
  * Return a rendered list of coaches
  * $type can be
  *   'alpha' - alphabetical list
  *   'geo' - list by distance from $zip in $country
  *
  */

function coach_list_by($type, $zip = NULL, $country = NULL) {
  global $user;
  $countries = _location_get_iso3166_list();
  switch ($type) {
    case 'alpha':
      $nids = coach_by_alpha_better($user->location['country']);
      while ($row = db_fetch_object($nids)) {
        $node = node_load(array('nid'=>$row->nid));
        if ($node->nid) {
          if ($node->location['country'] != $lastcountry) {
            $output .= theme('coach_location_head', $countries[$node->location['country']]);
          }
          $lastcountry = $node->location['country'];
          $output .= node_view($node, TRUE);
        }
      }
      $output .= '<br /><br />'.theme('pager', array(), 20);
      break;
    case 'geo':
      if ($_REQUEST['country']) {
        $zip = $_REQUEST['zip'];
        $country = $_REQUEST['country'];
      }
      elseif ($_REQUEST['zip']) {
        form_set_error('country', t('You need to select a country as well as a zip code.'));
      }
      elseif ($user->uid > 0) {
        $zip = $user->location['postal_code'];
        $country = $user->location['country'];
      }
      if (!$zip && !$country) {
        $zip = '80302';
        $country = 'us';
      }
      $output = theme('coach_location_form', $country, $zip);
      $nids = coach_by_distance_better($zip, $country);
      while ($row = db_fetch_object($nids)) {
        $node = node_load(array('nid'=>$row->nid));
        if ($node->nid) {
          if ($node->location['country'] != $lastcountry) {
            $output .= theme('coach_location_head', $countries[$node->location['country']]);
          }
          $lastcountry = $node->location['country'];
          $node->distance_unit = 'mi';
          //convert meters to miles/km
          $node->distance = round($row->distance/(($row->distance_unit == 'km') ? 1000.0 : 1609.347), 1);
          $node->from_zip = $zip;
          $output .= node_view($node, TRUE);
        }
      }
      $output .= '<br /><br />'.theme('pager', array(), 20, 0, array('zip'=>$zip, 'country'=>$country));
      break;
    case '':
    
  }
  return $output;
}

function theme_coach_location_form($country, $zip) {
  $row[0][] = form_select('Country', 'country', $country, coach_get_countries(), NULL, 0, FALSE, TRUE);
  $row[0][] = form_textfield('Postal Code', 'zip', $zip, 16, 16);
  $row[0][] = form_submit('Show by distance', 'x');
  $form = theme('table', '', $row, $attributes = array('style'=>'width:100%;'));
  $form = preg_replace('/edit\[(.*?)\]/i', '$1', $form);
  $output = form($form, 'get');
  return $output;
}

function theme_coach_location_head($text) {
  return "<div class='coach-country-head'><h1>".$text."</h1></div>";
}

/**
  * Find out if the user already has an entry.
  */
  
function coach_get_entry($cuid) {
  $obj = db_fetch_object(db_query("SELECT nid FROM {coach} WHERE cuid = %d", $cuid));
  return $obj->nid;
}

function coach_redirect() {
  global $user;
  $nid = coach_get_entry($user->uid);
  if ($nid) {
    drupal_set_message('Redirecting to your curent coach entry');
    drupal_goto('node/'.$nid.'/edit');
    return;
  }
  node_page();
}

function coach_search_form_page() {
  print theme('page', coach_search_form());
}


/*
function coach_search_form() {
  $edit = $_POST['edit'];
  $from_get = FALSE; // boolean to remember if variables came from a get
  global $user;
  
  switch ($_POST['op']) {
    case 'Show Alphabetically' :
      $sort = 'alpha';
      break;
    case 'Show By Location' :
      $sort = 'dist';
      break;
  }
  
  // if there's no zip code, or the country is not a 'supported' country
  
  //drupal_set_title('Fearless Living Certified Life Coaches');
  
  $prefilled_values = array();
  $prefilled_values['postal_code'] = isset($edit['location']['postal_code']) ? trim($edit['location']['postal_code']) : trim($user->location['postal_code']);
  $prefilled_values['country'] = isset($edit['location']['country']) ? $edit['location']['country'] : $user->location['country'];
    
  //is the country supported for distance calculations?
  $supported = function_exists('location_latlon_rough_'.$prefilled_values['country']);
  
  //how should we sort?
  if (!$edit && !$sort) { //if this is the default page
    if ($prefilled_values['postal_code'] && $supported) { // if they've got a zip and are in a covered country
      $sort = 'dist'; // sort by distance
    } else {
      $sort = 'alpha';
    }
  }
  // if there is a country, but the country is not 'supported', or if there is no zip code sort by country
  if (($prefilled_values['country'] && !$supported) || $prefilled_values['country'] && !$prefilled_values['postal_code']) {
    $sort = 'country';
  }
  
  $cell[] = array('data'=>t('<strong>Find coaches near you:</strong>'), 'colspan'=>3);
  $row[] = $cell;
  unset($cell);  
  $cell[] = form_select('Country', 'location][country', $prefilled_values['country'], coach_get_countries(), NULL, 0, FALSE, $required);
  $cell[] = form_textfield('Postal Code', 'location][postal_code', isset($prefilled_values['postal_code']) ? $prefilled_values['postal_code'] : '', 16, 16);
  $cell[] = array('data' => form_submit(t('Show By Location')), 'align'=>'right');
  $row[] = $cell;
  unset($cell);
  $cell[] = array('data'=>t('<strong>or show coaches alphabetically:</strong>'), 'colspan'=>2);
  $cell[] = form_submit(t('Show Alphabetically'));
  $row[] = $cell;
  $form = theme('table', '', $row, array('style'=>'width:100%'));
  $form = '<p>'.form($form).'<br /></p>';
  
  // THE SEARCH RESULTS ------------------------------------------
  
  $search_results = '';
  //if ($edit) {
    //$edit['location']['postal_code'] = trim($edit['location']['postal_code']);
    //if (strlen($edit['location']['postal_code']) && strlen($edit['location']['country']) && $edit['location']['country'] != 'xx') {
    
      switch ($sort) {
        case 'dist' :
          //print 'going by distance';
          $info = "Coaches sorted by distance:";
          $coaches = coach_by_distance($prefilled_values['postal_code'], $prefilled_values['country']);
          break;
        case 'country' :
          $info = "Coaches sorted by country:";
          $coaches = coach_by_alpha($prefilled_values['country']);
          break;
        case 'alpha' :
        default :
          $info = "Coaches sorted by name:";
          $coaches = coach_by_alpha();
          break;
      }
            
      $country_index = _location_get_iso3166_list();
      
      foreach ($coaches as $row) {
        //make dividers
        switch ($sort) {
          case 'dist' :
          case 'country' :
            if ($lastcoach->country != $row->country) {
              //this should be themed
              $search_results .= '<div class="coach-country-head"><h1>'.$country_index[$row->country].'</h1></div>';
            }
            break;
          case 'alpha' :
          default :
            // no dividers...
            break;
        }
        $lastcoach = $row;
        
        $city_index_key = $row->country . $row->postal_code;
        $result_location = array('street' => strlen($row->street) ? $row->street : '',
                                'additional' => strlen($row->additional) ? $row->additional : '',
                                'city' => strlen($row->city) ? $row->city : $postal_codes[$city_index_key]['city'],
                                'province' => strlen($row->province) ? $row->province : $postal_codes[$city_index_key]['province'],
                                'postal_code' => $row->postal_code,
                                'country' => $row->country,
                                'distance' => $row->distance,
                                'distance_unit' => $row->distance_unit,
                                'lat' => $row->latitude,
                                'lon' => $row->longitude
                               );        
        $extra = array();
        if ($sort == 'dist') {
          if ($result_location['postal_code'] == $edit['location']['postal_code'] && $result_location['country'] == $edit['location']['country']) {
            $extra['distance'] = t('Result is <strong>also from %postal_code</strong>', array('%postal_code' => $result_location['postal_code']));
          } elseif (!$result_location['lat']||!$result_location['lon']) {
            $extra['distance'] = t('<em>Unable to calculate distance</em>');
          } else {
            if (round($result_location['distance']) != 1) {
              $distance_unit = ($result_location['distance_unit'] == 'km') ? t($distance_unit) : t('miles');
            }
          $extra['distance'] = t('Approximately %distance from %location', array('%distance' => '<strong>'. round($result_location['distance']) .' '. $distance_unit .'</strong>', '%location' => $prefilled_values['postal_code']));
          }
        }
        $snippet = '<div class="coach-listing">'.l(theme('image', $row->picture, $row->title, $row->title, array('style'=>'float:left; margin-right: 5px; margin-bottom: 5px')), url('node/'.$row->nid), array(), NULL, NULL, FALSE, TRUE);
        $snippet .= "<strong class='coach-cert'>$row->cert</strong><br /><strong class='coach-loc'>$row->city, $row->province, ".$country_index[$row->country]."</strong></div>";
        $snippet .= coach_word_trim($row->body, variable_get('coach_list_trim', 20));
        //$snippet .= ($row->teaser != $row->body) ? '...' : '';
        $snippet .= '... <span class="coach-more">'.l(t('(more)'), url('node/'.$row->nid)).'</span>';
        if ($extra) {$snippet .= '<div class="coach-dist">'.$extra['distance'].'</div>';}
        $snippet .= '<br clear="all" />';
        
        $item = array('link' => url('node/'. $row->nid), 
                      'title' => $row->title, 
                      'snippet' => $snippet,
                      );
        $search_results .= theme('search_item', $item, 'coach');
      }
            if (!strlen($search_results)) {
        $search_results .= theme('box', t('Your search yielded no results.'), '');
      }
      else {
        $from = $_GET['from'] ? $_GET['from'] : '0';
        $coach_limit = variable_get('coach_limit', 15);
        $total = $GLOBALS['pager_total'][0];
        $to = ($from+$coach_limit)<$total ? ($from+$coach_limit) : $total ;
        $search_stats = "<div>Showing ".($from+1)." - ".$to." of ".$total."<br /><br /></div>";
        $search_results = '<dl class="search-results">'.$search_stats.$search_results.'</dl>';
        $search_results = theme('box', t($info), $search_results);
        $search_results .= theme('pager', NULL, $coach_limit, 0, $edit['location']);
      }
    //}
  //}  
  return $form . $search_results;
}
*/

function coach_cert_role($uid) {
  $user = user_load(array('uid' => $uid));
  $roles = $user->roles;
  if (in_array('cflc3', $roles)) {
    return "CFLC III";
  }
  elseif(in_array('cflc2', $roles)) {
    return "CFLC II";
  }
  elseif(in_array('cflc', $roles)) {
    return "CFLC";
  }
}

function coach_word_trim($data, $count) {
  $regex = "/(.*?[\s]){0,".$count."}/";
  $matches = array();
  preg_match($regex, $data, $matches);
  return trim($matches[0]);
}

// return a list of countries used in coach table
// key is iso code and value is country

function coach_get_countries() {
  $sql = 'SELECT country FROM {coach} c INNER JOIN {location} l ON c.nid = l.oid AND l.type = "node" GROUP BY country ORDER BY country';
  $res = db_query($sql);
  $countries = _location_get_iso3166_list();
  $list = array(''=>'Choose...');
  while ($row = db_fetch_object($res)) {
    $list[$row->country] = $countries[$row->country];
  }
  return $list;
}

/**
  * Returns an alphabetical array of coach objects
  * if $country is defined, coaches from that country are returned first
  *
  */

/* 
function coach_by_alpha($country = FALSE) {
  if ($country) {
    $sql = 'SELECT n.nid AS nid, a.postal_code AS postal_code, a.city AS city, a.province AS province, a.country AS country, a.latitude AS latitude, a.longitude AS longitude, n.title AS title, n.teaser AS teaser, n.body AS body, n.type AS type, n.created AS created, n.uid AS uid, u.name AS name, u.picture AS picture, c.firstname AS firstname, c.lastname AS lastname, c.cert AS cert, c.cuid AS cuid, IF(a.country = \''.$country.'\', 0, 1) AS mycountry  FROM {location} a INNER JOIN {node} n ON a.oid = n.nid INNER JOIN {coach} c ON n.nid = c.nid INNER JOIN {users} u ON n.uid = u.uid WHERE n.type = \'coach\' AND a.type = "node"  ORDER BY mycountry, country, lastname ASC';
  } else {
    $sql = 'SELECT n.nid AS nid, a.postal_code AS postal_code, a.city AS city, a.province AS province, a.country AS country, a.latitude AS latitude, a.longitude AS longitude, n.title AS title, n.teaser AS teaser, n.body AS body, n.type AS type, n.created AS created, n.uid AS uid, u.name AS name, u.picture AS picture, c.firstname AS firstname, c.lastname AS lastname, c.cert AS cert, c.cuid AS cuid, IF(a.latitude<=>NULL, 1, 0) AS gotlat FROM {location} a INNER JOIN {node} n ON a.oid = n.nid INNER JOIN {coach} c ON n.nid = c.nid INNER JOIN {users} u ON n.uid = u.uid WHERE n.type = \'coach\' AND a.type = \'node\' ORDER BY lastname ASC';
  }
  $count_sql = 'SELECT COUNT(*) FROM {location} a INNER JOIN {node} n ON a.oid = n.nid INNER JOIN {coach} c ON n.nid = c.nid INNER JOIN {users} u ON n.uid = u.uid WHERE n.type = \'coach\' AND a.type = \'node\'';
  $res = pager_query($sql, variable_get('coach_limit', 15), 0, $count_sql);
// for each one... do a distance check and stick 'em into an array with the distance
  $coaches = array();
  while ($row = db_fetch_object($res)) {
    $coaches[] = $row;
  }
  // display
  return $coaches;
}
*/

function coach_by_alpha_better($country = NULL) {
  if (!trim($country)) {
    $country = 'us';
  }
  if ($country) {
    return pager_query('SELECT n.nid, IF(l.country = "'.$country.'", 0, 1) AS mycountry FROM {node} n INNER JOIN {coach} c ON c.nid = n.nid INNER JOIN {location} l ON l.oid = n.nid AND l.type = "node" ORDER BY mycountry, l.country, c.lastname ASC', 20);
  }
  else {
    return pager_query('SELECT n.nid FROM {node} n INNER JOIN {coach} c ON c.nid = n.nid INNER JOIN {location} l ON l.oid = n.nid AND l.type = "node" ORDER BY l.country, c.lastname ASC', 20);    
  }
}

/**
  * Returns an array of coach objects, listed by distance from $zip
  * $country is required as well
  *
  */
/*
function coach_by_distance($zip, $country) {
  $mylatlon = location_latlon_rough(array('postal_code'=>$zip, 'country'=>$country, 'mi'));
// get all coach nodes with each's lat/long
  $distsql = earth_distance_sql($mylatlon['lon'], $mylatlon['lat']);
  $sql = 'SELECT n.nid AS nid, a.postal_code AS postal_code, a.city AS city, a.province AS province, a.country AS country, a.latitude AS latitude, a.longitude AS longitude, n.title AS title, n.teaser AS teaser, n.body as body, n.type AS type, n.created AS created, n.uid AS uid, u.name AS name, u.picture AS picture, c.firstname AS firstname, c.lastname AS lastname, c.cert AS cert, c.cuid AS cuid, IF(a.latitude<=>NULL, 1, 0) AS gotlat, '.$distsql.' AS distance FROM {location} a INNER JOIN {node} n ON a.oid = n.nid INNER JOIN {coach} c ON n.nid = c.nid INNER JOIN {users} u ON n.uid = u.uid WHERE n.type = \'coach\' AND a.type = \'node\' ORDER BY gotlat, distance, country, lastname ASC';
  $count_sql = 'SELECT COUNT(*) FROM {location} a INNER JOIN {node} n ON a.oid = n.nid INNER JOIN {coach} c ON n.nid = c.nid INNER JOIN {users} u ON n.uid = u.uid WHERE n.type = \'coach\' AND a.type = \'node\'';
  return pager_query($sql, variable_get('coach_limit', 15), 0, $count_sql);
// for each one... do a distance check and stick 'em into an array with the distance
}
*/

function coach_by_distance_better($zip, $country) {
  $mylatlon = location_latlon_rough(array('postal_code'=>$zip, 'country'=>$country, 'mi'));
// get all coach nodes with each's lat/long
  if ($mylatlon != NULL) {
    $distsql = earth_distance_sql($mylatlon['lon'], $mylatlon['lat']);
    $sql = 'SELECT n.nid AS nid, c.lastname AS lastname, a.postal_code AS postal_code, a.city AS city, a.province AS province, a.country AS country, a.latitude AS latitude, a.longitude AS longitude, IF(a.latitude<=>NULL, 1, 0) AS gotlat, '.$distsql.' AS distance FROM {location} a INNER JOIN {node} n ON a.oid = n.nid INNER JOIN {coach} c ON n.nid = c.nid WHERE n.type = \'coach\' AND a.type = \'node\' ORDER BY gotlat, distance, country, lastname ASC';
  }
  else {
    $sql = 'SELECT n.nid AS nid, c.lastname AS lastname, a.postal_code AS postal_code, a.city AS city, a.province AS province, a.country AS country, a.latitude AS latitude, a.longitude AS longitude, IF(a.country="'.$country.'", 0, 1) AS samecountry FROM {location} a INNER JOIN {node} n ON a.oid = n.nid INNER JOIN {coach} c ON n.nid = c.nid WHERE n.type = \'coach\' ORDER BY samecountry, country, lastname ASC';
  }
  $count_sql = 'SELECT COUNT(*) FROM {location} a INNER JOIN {node} n ON a.oid = n.nid WHERE n.type = \'coach\' AND a.type = \'node\'';
  
  return pager_query($sql, 20, 0, $count_sql);
} 
 
// I don't know if this is used...
 
function theme_coach_search($header, $rows, $attributes = NULL) {
  $output = '<table'. drupal_attributes($attributes) .">\n";

  // Format the table header:
  if (is_array($header)) {
    $ts = tablesort_init($header);
    $output .= ' <tr>';
    foreach ($header as $cell) {
      $cell = tablesort_header($cell, $header, $ts);
      $output .= _theme_table_cell($cell, 1);
    }
    $output .= " </tr>\n";
  }

  // Format the table rows:
  if (is_array($rows)) {
    foreach ($rows as $number => $row) {
      $attributes = array();

      // Check if we're dealing with a simple or complex row
      if (isset($row['data'])) {
        foreach ($row as $key => $value) {
          if ($key == 'data') {
            $cells = $value;
          }
          else {
            $attributes[$key] = $value;
          }
        }
      }
      else {
        $cells = $row;
      }

      // Add light/dark class
      $class = ($number % 2 == 1) ? 'light': 'dark';
      if (isset($attributes['class'])) {
        $attributes['class'] .= ' '. $class;
      }
      else {
        $attributes['class'] = $class;
      }

      // Build row
      $output .= ' <tr'. drupal_attributes($attributes) .'>';
      $i = 0;
      foreach ($cells as $cell) {
        $cell = tablesort_cell($cell, $header, $ts, $i++);
        $output .= _theme_table_cell($cell, 0);
      }
      $output .= " </tr>\n";
    }
  }

  $output .= "</table>\n";
  return $output;
}
 
 
/**
  * Don't know if this is doing anything...
  *
  */
 
 function coach_search_item($item) {
  $output = ' <div class="title"><a href="'. check_url($item['link']) .'">'. check_plain($item['title']) .'</a></div>';
  $info = array();
  if (is_array($item['extra'])) {
    $info = array_merge($info, $item['extra']);
  }
  $output .= ' <div>'. ($item['snippet'] ? '<p>'. $item['snippet'] . '</p>' : '') . '<p>' . implode(' - ', $info) .'</p></div>';
  return $output;
}

/**
  * Don't know if this is doing anything either...
  *
  */
 function theme_coach_list($node) { 
 $output = '<div class="coach_list">';
  if ($node->picture) {
    $output .= theme('image', $node->picture, 'alt', 'title', 'style="float:left; margin-right: 5px; margin-bottom: 5px; margin-top: 3px;"');
  }
  if ($node->cert) {
    $output .= '<div>certifications: '.$node->cert.'</div>';
  }
 $output .= '</div><br /></div>'; 
 return $output;
 } 
 
 ?>