You are here

Home » API reference » Get Locations 7 » getlocations_search.module

function getlocations_search_info_sql in Get Locations 7

Same name and namespace in other branches
  1. 7.2 modules/getlocations_search/getlocations_search.module \getlocations_search_info_sql()
1 call to getlocations_search_info_sql()
getlocations_search_allinfo in modules/getlocations_search/getlocations_search.module

File

modules/getlocations_search/getlocations_search.module, line 1361
getlocations_search.module @author Bob Hutchinson http://drupal.org/user/52366 @copyright GNU GPL

Code

function getlocations_search_info_sql($lat, $lon, $distance, $units, $type, $dosort, $limits) {
  $getlocations_defaults = getlocations_defaults();
  $getlocations_search_defaults = getlocations_search_defaults();
  $dms = $getlocations_search_defaults['display_dms'] || $getlocations_search_defaults['display_geo_microformat'] ? TRUE : FALSE;
  $distance_meters = getlocations_convert_distance_to_meters($distance, $units);
  $latrange = getlocations_earth_latitude_range($lat, $lon, $distance_meters);
  $lonrange = getlocations_earth_longitude_range($lat, $lon, $distance_meters);
  $locations = array();
  if (module_exists('getlocations_fields')) {
    if ($lonrange[0] > $lonrange[1]) {
      $where = "g.latitude > :minlat AND g.latitude < :maxlat AND ((g.longitude < 180 AND g.longitude > :minlon) OR (g.longitude < :maxlon AND g.longitude > -180))";
    }
    else {
      $where = "g.latitude > :minlat AND g.latitude < :maxlat AND g.longitude > :minlon AND g.longitude < :maxlon";
    }
    $sqlarr = array(
      ':minlat' => $latrange[0],
      ':maxlat' => $latrange[1],
      ':minlon' => $lonrange[0],
      ':maxlon' => $lonrange[1],
    );
    if ($getlocations_search_defaults['search_distance_type'] == 'dist') {
      $where .= " AND " . getlocations_earth_distance_sql($lat, $lon, 'g') . ' < :distance';
      $sqlarr += array(
        ':distance' => $distance_meters,
      );
    }
    $fields = array();
    $fields[] = 'g.glid';
    $fields[] = 'g.name';
    $fields[] = 'g.latitude';
    $fields[] = 'g.longitude';
    $fields[] = 'g.marker';
    if (getlocations_fields_column_check('data')) {
      $fields[] = 'g.data';
    }
    $fields[] = 'f.nid';
    $fields[] = 'f.uid';
    $fields[] = 'f.tid';
    $fields[] = 'f.cid';
    $sqlsnip1 = "";
    $sqlsnip2 = "";
    $marker = '';
    if ($type == 'node' && user_access('access content') && user_access('access getlocations')) {
      $fields[] = 'n.title AS title';
      $fields[] = 'n.nid';
      $sqlsnip1 = "LEFT JOIN {node} n ON f.vid = n.vid ";
      $sqlsnip2 = "AND n.nid > 0 AND n.status = 1 ";
      $marker = $getlocations_defaults['node_map_marker'];
    }
    elseif ($type == 'user' && getlocations_access_user_location()) {
      $fields[] = 'u.name AS title';
      $fields[] = 'u.uid';
      $sqlsnip1 = "LEFT JOIN {users} u ON f.uid = u.uid ";
      $sqlsnip2 = "AND u.uid > 0 AND u.status = 1 ";
      $marker = $getlocations_defaults['user_map_marker'];
    }
    elseif (($type == 'term' || $type == 'taxonomy_term') && user_access('access getlocations')) {
      $fields[] = 't.name AS title';
      $fields[] = 't.tid';
      $sqlsnip1 = "LEFT JOIN {taxonomy_term_data} t ON f.tid = t.tid ";
      $sqlsnip2 = "AND t.tid > 0 ";
      $marker = $getlocations_defaults['vocabulary_map_marker'];
    }
    elseif ($type == 'comment' && user_access('access comments') && user_access('access getlocations')) {
      $fields[] = 'c.subject AS title';
      $fields[] = 'c.cid';
      $sqlsnip1 = "LEFT JOIN {comment} c ON f.cid = c.cid ";
      $sqlsnip2 = "AND c.cid > 0 AND c.status = 1 ";
      $marker = $getlocations_defaults['comment_map_marker'];
    }
    elseif ($type == 'all') {
      $sqlsnip2arr = array();
      if (user_access('access content') && user_access('access getlocations')) {
        $sqlsnip2arr[] = "f.nid > 0";
        $sqlsnip2arr[] = "f.tid > 0";
      }
      if (getlocations_access_user_location()) {
        $sqlsnip2arr[] = "f.uid > 0";
      }
      if (user_access('access comments') && user_access('access getlocations')) {
        $sqlsnip2arr[] = "f.cid > 0";
      }
      if (!empty($sqlsnip2arr)) {
        $sqlsnip2 = "AND (" . implode(" OR ", $sqlsnip2arr) . ") ";
      }
      else {

        // this user may not see anything
        $sqlsnip2 = "AND 1=0 ";
      }
    }
    if ($dosort) {
      $sort = getlocations_earth_distance_sql($lat, $lon, 'g');
      $fields[] = "{$sort} AS distance_sort ";
    }
    $selects = implode(",", $fields);
    $sql = "SELECT {$selects} ";
    $sql .= "FROM {getlocations_fields} g LEFT JOIN {getlocations_fields_entities} f ON g.glid = f.glid ";
    $sql .= $sqlsnip1;
    $sql .= "WHERE ({$where}) ";
    $sql .= "AND g.glid IS NOT NULL ";
    $sql .= "AND g.latitude != '0' ";
    $sql .= "AND g.longitude != '0' ";
    $sql .= $sqlsnip2;
    if ($dosort) {
      $sql .= "ORDER BY distance_sort ASC ";
    }
    if ($limits > 0) {
      $sql .= "LIMIT 0, {$limits} ";
    }
    $location_ct = 0;
    $result = db_query($sql, $sqlarr);
    foreach ($result as $row) {
      $locations[$location_ct] = $row;
      if (empty($locations[$location_ct]->marker)) {
        if (!empty($marker)) {
          $locations[$location_ct]->marker = $marker;
          if (isset($locations[$location_ct]->nid) && $locations[$location_ct]->nid > 0) {
            $t = getlocations_get_nodetype($locations[$location_ct]->nid);
            if ($t) {
              $typemarkers = getlocations_get_markertypes('node');
              if (isset($typemarkers[$t]) && $typemarkers[$t]) {
                $locations[$location_ct]->marker = $typemarkers[$t];
              }
            }
          }
        }
        else {

          // must be an 'all' search so we have to figure out a marker
          if ($locations[$location_ct]->nid > 0) {
            $locations[$location_ct]->marker = $getlocations_defaults['node_map_marker'];
            $t = getlocations_get_nodetype($locations[$location_ct]->nid);
            if ($t) {
              $typemarkers = getlocations_get_markertypes('node');
              if (isset($typemarkers[$t]) && $typemarkers[$t]) {
                $locations[$location_ct]->marker = $typemarkers[$t];
              }
            }

            // term markers
            $locations[$location_ct]->marker = getlocations_get_term_marker($locations[$location_ct]->nid, $locations[$location_ct]->marker);
          }
          elseif ($locations[$location_ct]->uid > 0) {
            $locations[$location_ct]->marker = $getlocations_defaults['user_map_marker'];
          }
          elseif ($locations[$location_ct]->tid > 0) {
            $locations[$location_ct]->marker = $getlocations_defaults['vocabulary_map_marker'];
          }
          elseif ($locations[$location_ct]->cid > 0) {
            $locations[$location_ct]->marker = $getlocations_defaults['comment_map_marker'];
          }
        }

        // just in case
        if (empty($locations[$location_ct]->marker)) {
          $locations[$location_ct]->marker = $getlocations_defaults['node_map_marker'];
        }
      }

      // ensure we always have a title
      if (!isset($locations[$location_ct]->title) || empty($locations[$location_ct]->title)) {
        if ($locations[$location_ct]->nid > 0) {
          $n = node_load($locations[$location_ct]->nid);
          $locations[$location_ct]->title = $n->title;

          // sanity check
          if ($n->status == 0) {
            unset($locations[$location_ct]);
            continue;
          }
        }
        elseif ($locations[$location_ct]->uid > 0) {
          $u = user_load($locations[$location_ct]->uid);
          $locations[$location_ct]->title = $u->name;

          // sanity check
          if ($u->status == 0) {
            unset($locations[$location_ct]);
            continue;
          }
        }
        elseif ($locations[$location_ct]->tid > 0) {
          $term = taxonomy_term_load($locations[$location_ct]->tid);
          $locations[$location_ct]->title = $term->name;
        }
        elseif ($locations[$location_ct]->cid > 0) {
          $comment = comment_load($locations[$location_ct]->cid);
          $locations[$location_ct]->title = $comment->subject;

          // sanity check
          if ($comment->status == 0) {
            unset($locations[$location_ct]);
            continue;
          }
        }
      }
      $location_ct++;
    }
  }
  elseif (module_exists('location_cck')) {
    if ($lonrange[0] > $lonrange[1]) {
      $where = "l.latitude > :minlat AND l.latitude < :maxlat AND ((l.longitude < 180 AND l.longitude > :minlon) OR (l.longitude < :maxlon AND l.longitude > -180))";
    }
    else {
      $where = "l.latitude > :minlat AND l.latitude < :maxlat AND l.longitude > :minlon AND l.longitude < :maxlon";
    }
    $sqlarr = array(
      ':minlat' => $latrange[0],
      ':maxlat' => $latrange[1],
      ':minlon' => $lonrange[0],
      ':maxlon' => $lonrange[1],
    );
    if ($getlocations_search_defaults['search_distance_type'] == 'dist') {
      $where .= " AND " . getlocations_earth_distance_sql($lat, $lon, 'l') . ' < :distance';
      $sqlarr += array(
        ':distance' => $distance_meters,
      );
    }
    $fields = array();
    $fields[] = 'l.lid';
    $fields[] = 'l.name';
    $fields[] = 'l.latitude';
    $fields[] = 'l.longitude';
    $fields[] = 'i.nid';
    $fields[] = 'i.uid';
    $sqlsnip1 = "";
    $sqlsnip2 = "";
    $marker = '';
    if ($type == 'node' && user_access('access content') && user_access('access getlocations')) {
      $fields[] = 'n.title AS title';
      $fields[] = 'n.nid';
      $sqlsnip1 = "LEFT JOIN {node} n ON i.vid = n.vid ";
      $sqlsnip2 = "AND n.nid > 0 AND n.status = 1 ";
      $marker = $getlocations_defaults['node_map_marker'];
    }
    elseif ($type == 'user' && getlocations_access_user_location()) {
      $fields[] = 'u.name AS title';
      $fields[] = 'u.uid';
      $sqlsnip1 = "LEFT JOIN {users} u ON i.uid = u.uid ";
      $sqlsnip2 = "AND u.uid > 0 AND u.status = 1 ";
      $marker = $getlocations_defaults['user_map_marker'];
    }
    elseif ($type == 'all') {
      $sqlsnip2arr = array();
      if (user_access('access content') && user_access('access getlocations')) {
        $sqlsnip2arr[] = "i.nid > 0";
      }
      if (getlocations_access_user_location()) {
        $sqlsnip2arr[] = "i.uid > 0";
      }
      if (!empty($sqlsnip2arr)) {
        $sqlsnip2 = "AND (" . implode(" OR ", $sqlsnip2arr) . ") ";
      }
      else {

        // this user may not see anything
        $sqlsnip2 = "AND 1=0 ";
      }
    }
    if ($dosort) {
      $sort = getlocations_earth_distance_sql($lat, $lon, 'l');
      $fields[] = "{$sort} AS distance_sort ";
    }
    $selects = implode(",", $fields);
    $sql = "SELECT {$selects} ";
    $sql .= "FROM {location} l LEFT JOIN {location_instance} i ON l.lid = i.lid ";
    $sql .= $sqlsnip1;
    $sql .= "WHERE ({$where}) ";
    $sql .= "AND l.lid IS NOT NULL ";
    $sql .= "AND l.latitude != '0' ";
    $sql .= "AND l.longitude != '0' ";
    $sql .= $sqlsnip2;
    if ($dosort) {
      $sql .= "ORDER BY distance_sort ASC ";
    }
    if ($limits > 0) {
      $sql .= "LIMIT 0, {$limits} ";
    }
    $location_ct = 0;
    $result = db_query($sql, $sqlarr);
    foreach ($result as $row) {
      $locations[$location_ct] = $row;
      if (!empty($marker)) {
        $locations[$location_ct]->marker = $marker;
      }
      else {

        // must be an 'all' search so we have to figure out a marker
        if ($locations[$location_ct]->nid > 0) {
          $locations[$location_ct]->marker = $getlocations_defaults['node_map_marker'];
        }
        elseif ($locations[$location_ct]->uid > 0) {
          $locations[$location_ct]->marker = $getlocations_defaults['user_map_marker'];
        }
      }

      // ensure we always have a title
      if (!isset($locations[$location_ct]->title) || empty($locations[$location_ct]->title)) {
        if ($locations[$location_ct]->nid > 0) {
          $n = node_load($locations[$location_ct]->nid);
          $locations[$location_ct]->title = $n->title;

          // sanity check
          if ($n->status == 0) {
            unset($locations[$location_ct]);
            continue;
          }
        }
        elseif ($locations[$location_ct]->uid > 0) {
          $u = user_load($locations[$location_ct]->uid);
          $locations[$location_ct]->title = $u->name;

          // sanity check
          if ($u->status == 0) {
            unset($locations[$location_ct]);
            continue;
          }
        }
      }
      $location_ct++;
    }
  }
  elseif (module_exists('geolocation')) {
    $module = 'geolocation';
    $gtype = 'geolocation_latlng';
    $entity_type = '';
    $fieldnames = getlocations_other_get_fieldname($gtype, $module, $entity_type);
    if (!empty($fieldnames)) {
      $tabledata = array();
      $ct = 0;
      foreach ($fieldnames as $fieldname) {
        $tabledata[$ct]['table'] = 'field_data_' . $fieldname;
        $tabledata[$ct]['fieldname_latitude'] = $fieldname . '_lat';
        $tabledata[$ct]['fieldname_longitude'] = $fieldname . '_lng';
        $ct++;
      }
    }
  }
  elseif (module_exists('geofield')) {
    $module = 'geofield';
    $gtype = 'geofield';
    $entity_type = '';
    $fieldnames = getlocations_other_get_fieldname($gtype, $module, $entity_type);
    if (!empty($fieldnames)) {
      $tabledata = array();
      $ct = 0;
      foreach ($fieldnames as $fieldname) {
        $tabledata[$ct]['table'] = 'field_data_' . $fieldname;
        $tabledata[$ct]['fieldname_latitude'] = $fieldname . '_lat';
        $tabledata[$ct]['fieldname_longitude'] = $fieldname . '_lon';
        $ct++;
      }
    }
  }
  if ((module_exists('geolocation') || module_exists('geofield')) && !empty($fieldnames)) {
    $location_ct = 0;
    foreach ($tabledata as $data) {
      $table = $data['table'];
      $latfield = 'g.' . $data['fieldname_latitude'];
      $lonfield = 'g.' . $data['fieldname_longitude'];
      if ($lonrange[0] > $lonrange[1]) {
        $where = "{$latfield} > :minlat AND {$latfield} < :maxlat AND (({$lonfield} < 180 AND {$lonfield} > :minlon) OR ({$lonfield} < :maxlon AND {$lonfield} > -180))";
      }
      else {
        $where = "{$latfield} > :minlat AND {$latfield} < :maxlat AND {$lonfield} > :minlon AND {$lonfield} < :maxlon";
      }
      $sqlarr = array(
        ':minlat' => $latrange[0],
        ':maxlat' => $latrange[1],
        ':minlon' => $lonrange[0],
        ':maxlon' => $lonrange[1],
      );
      if ($getlocations_search_defaults['search_distance_type'] == 'dist') {
        $where .= " AND " . getlocations_earth_distance_sql($lat, $lon, 'g') . ' < :distance';
        $sqlarr += array(
          ':distance' => $distance_meters,
        );
      }
      $fields = array();
      $fields[] = "{$latfield} AS latitude";
      $fields[] = "{$lonfield} AS longitude";
      $fields[] = "g.entity_type AS entity_type";
      $fields[] = "g.entity_id AS entity_id";
      $sqlsnip1 = "";
      $sqlsnip2 = "";
      $marker = '';
      if ($type == 'node' && user_access('access content') && user_access('access getlocations')) {
        $fields[] = 'n.title AS title';
        $fields[] = 'n.nid';
        $sqlsnip1 = "LEFT JOIN {node} n ON g.entity_id = n.nid ";
        $sqlsnip2 = "AND g.entity_type = :type AND n.nid > 0 AND n.status = 1 ";
        $sqlarr[':type'] = $type;
        $marker = $getlocations_defaults['node_map_marker'];
      }
      elseif ($type == 'user' && getlocations_access_user_location()) {
        $fields[] = 'u.name AS title';
        $fields[] = 'u.uid';
        $sqlsnip1 = "LEFT JOIN {users} u ON g.entity_id = u.uid ";
        $sqlsnip2 = "AND g.entity_type = :type AND u.uid > 0 AND u.status = 1 ";
        $sqlarr[':type'] = $type;
        $marker = $getlocations_defaults['user_map_marker'];
      }
      elseif (($type == 'term' || $type == 'taxonomy_term') && user_access('access getlocations')) {
        $fields[] = 't.name AS title';
        $fields[] = 't.tid';
        $sqlsnip1 = "LEFT JOIN {taxonomy_term_data} t ON g.entity_id = t.tid ";
        $sqlsnip2 = "AND g.entity_type = :type AND t.tid > 0 ";
        $sqlarr[':type'] = $type;
        $marker = $getlocations_defaults['vocabulary_map_marker'];
      }
      elseif ($type == 'comment' && user_access('access comments') && user_access('access getlocations')) {
        $fields[] = 'c.subject AS title';
        $fields[] = 'c.cid';
        $sqlsnip1 = "LEFT JOIN {comment} c ON g.entity_id = c.cid ";
        $sqlsnip2 = "AND g.entity_type = :type AND c.cid > 0 AND c.status = 1 ";
        $sqlarr[':type'] = $type;
        $marker = $getlocations_defaults['comment_map_marker'];
      }
      elseif ($type == 'all') {

        // need a list of all the entity_types in this table so that we can limit on permissions
        $sqlsnip2arr = array();
        $permitted_entity_types = array();
        if (user_access('access content') && user_access('access getlocations')) {
          $permitted_entity_types[] = 'node';
        }
        if (getlocations_access_user_location()) {
          $permitted_entity_types[] = 'user';
        }
        if (user_access('access getlocations')) {
          $permitted_entity_types[] = 'term';
        }
        if (user_access('access comments') && user_access('access getlocations')) {
          $permitted_entity_types[] = 'comment';
        }
        $sql = "SELECT DISTINCT entity_type FROM {$table} ";
        $rows = db_query($sql);
        $at = '';
        $ct = 1;
        $sqlsnip2 = '';
        foreach ($rows as $row) {
          $et = $row->entity_type;
          if (in_array($et, $permitted_entity_types)) {
            $sqlsnip2arr[] = "g.entity_type = :et_{$ct}";
            $sqlarr[":et_{$ct}"] = $et;
            $ct++;
          }
        }
        if (!empty($sqlsnip2arr)) {
          $sqlsnip2 = "AND (" . implode(" OR ", $sqlsnip2arr) . ") ";
        }
        else {

          // this user may not see anything
          $sqlsnip2 = "AND 1=0 ";
        }
      }
      if ($dosort) {
        $sort = getlocations_earth_distance_sql($lat, $lon, 'g');
        $fields[] = "{$sort} AS distance_sort ";
      }
      $selects = implode(",", $fields);
      $sql = "SELECT {$selects} FROM {$table} g ";
      $sql .= $sqlsnip1;
      $sql .= "WHERE ({$where}) ";
      $sql .= "AND {$latfield} != '0' ";
      $sql .= "AND {$lonfield} != '0' ";
      $sql .= $sqlsnip2;
      if ($dosort) {
        $sql .= "ORDER BY distance_sort ASC ";
      }
      if ($limits > 0) {
        $sql .= "LIMIT 0, {$limits} ";
      }
      $result = db_query($sql, $sqlarr);
      foreach ($result as $row) {
        $locations[$location_ct]->latitude = $row->latitude;
        $locations[$location_ct]->longitude = $row->longitude;
        if (isset($row->title)) {
          $locations[$location_ct]->title = $row->title;
        }
        if ($row->entity_type == 'node') {
          $locations[$location_ct]->nid = $row->entity_id;
          $locations[$location_ct]->marker = $getlocations_defaults['node_map_marker'];
          $t = getlocations_get_nodetype($locations[$location_ct]->nid);
          if ($t) {
            $typemarkers = getlocations_get_markertypes('node');
            if (isset($typemarkers[$t]) && $typemarkers[$t]) {
              $locations[$location_ct]->marker = $typemarkers[$t];
            }
          }

          // term markers
          $locations[$location_ct]->marker = getlocations_get_term_marker($row->entity_id, $locations[$location_ct]->marker);
          $n = node_load($locations[$location_ct]->nid);

          // sanity check
          if ($n->status == 0) {
            unset($locations[$location_ct]);
          }
        }
        elseif ($row->entity_type == 'user') {
          $locations[$location_ct]->uid = $row->entity_id;
          $locations[$location_ct]->marker = $getlocations_defaults['user_map_marker'];
          $u = user_load($locations[$location_ct]->uid);

          // sanity check
          if ($u->status == 0) {
            unset($locations[$location_ct]);
          }
        }
        elseif ($row->entity_type == 'term') {
          $locations[$location_ct]->tid = $row->entity_id;
          $locations[$location_ct]->marker = $getlocations_defaults['vocabulary_map_marker'];
        }
        elseif ($row->entity_type == 'comment') {
          $locations[$location_ct]->cid = $row->entity_id;
          $locations[$location_ct]->marker = $getlocations_defaults['comment_map_marker'];
          $comment = comment_load($locations[$location_ct]->cid);

          // sanity check
          if ($comment->status == 0) {
            unset($locations[$location_ct]);
          }
        }

        // just in case
        if (isset($locations[$location_ct]) && empty($locations[$location_ct]->marker)) {
          $locations[$location_ct]->marker = $getlocations_defaults['node_map_marker'];
        }
        $location_ct++;
      }
    }
  }
  $minmaxes = $latrange[0] . ',' . $lonrange[0] . ',' . $latrange[1] . ',' . $lonrange[1];

  // format lat/lon
  if ($dms) {
    $latout = theme('getlocations_latitude_dms', array(
      'latitude' => $lat,
    ));
    $lonout = theme('getlocations_longitude_dms', array(
      'longitude' => $lon,
    ));
  }
  else {
    $latout = round($lat, 6);
    $lonout = round($lon, 6);
  }
  $infoarr = array(
    $distance,
    $units,
    $type ? $type : 'all',
    $latout,
    $lonout,
    $distance_meters,
  );
  $info = implode(",", $infoarr);
  $ret = array(
    'locations' => $locations,
    'minmaxes' => $minmaxes,
    'info' => $info,
  );
  return $ret;
}