function getlocations_search_info_sql in Get Locations 7.2

Same name and namespace in other branches

1. 7 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 1374

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')) {
    $module = 'getlocations_fields';
    $gtype = 'getlocations_fields';
    $entity_type = $type;
    if ($type == 'all') {
      $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_glid'] = $fieldname . '_glid';
        $tabledata[$ct]['fieldname'] = $fieldname;
        $ct++;
      }
      if ($lonrange[0] > $lonrange[1]) {
        $where = "gf.latitude > :minlat AND gf.latitude < :maxlat AND ((gf.longitude < 180 AND gf.longitude > :minlon) OR (gf.longitude < :maxlon AND gf.longitude > -180))";
      }
      else {
        $where = "gf.latitude > :minlat AND gf.latitude < :maxlat AND gf.longitude > :minlon AND gf.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[] = 'gf.glid';
      $fields[] = 'gf.name';
      $fields[] = 'gf.latitude';
      $fields[] = 'gf.longitude';
      $fields[] = 'gf.marker';
      $fields[] = 'gf.field_name';
      if (getlocations_fields_column_check('data')) {
        $fields[] = 'gf.data';
      }
      $fields[] = "t.entity_type";
      $fields[] = "t.entity_id";
      $fields[] = "t.bundle";
      $sqlsnip1 = "";
      $sqlsnip2 = "";

      // need to deal with type
      if ($entity_type) {
        $entity_get_info = entity_get_info($entity_type);
        $base_table = $entity_get_info['base table'];
        $entity_key = $entity_get_info['entity keys']['id'];
        $title = '';
        if (isset($entity_get_info['entity keys']['label'])) {
          $title = $entity_get_info['entity keys']['label'];
        }
        elseif ($entity_type == 'user') {
          $title = 'name';
        }
        if ($title) {
          $fields[] = "b.{$title} AS title";
        }
        $fields[] = "b.status AS status";
        $sqlsnip1 = "LEFT JOIN {" . $base_table . "} b ON t.entity_id = b.{$entity_key} ";
      }
      $marker = '';
      if ($dosort) {
        $sort = getlocations_earth_distance_sql($lat, $lon, 'gf');
        $fields[] = "{$sort} AS distance_sort ";
      }
      $selects = implode(",", $fields);
      $location_ct = 0;
      foreach ($tabledata as $data) {

        #$table = $data['table'];

        #$col = $data['fieldname_glid'];

        #$fieldname = $data['fieldname'];
        $sql = "SELECT {$selects} ";
        $sql .= "FROM {getlocations_fields} gf ";
        $sql .= "LEFT JOIN {" . $data['table'] . "} t ON gf.glid = t." . $data['fieldname_glid'] . " ";
        $sql .= $sqlsnip1;
        $sql .= "WHERE ({$where}) ";
        $sql .= "AND gf.field_name = '" . $data['fieldname'] . "' ";
        $sql .= "AND gf.glid IS NOT NULL ";
        $sql .= "AND gf.latitude != '0' ";
        $sql .= "AND gf.longitude != '0' ";
        if ($dosort) {
          $sql .= "ORDER BY distance_sort ASC ";
        }
        if ($limits > 0) {
          $sql .= "LIMIT 0, {$limits} ";
        }

        // run sql
        $result = db_query($sql, $sqlarr);
        foreach ($result as $row) {
          $locations[$location_ct] = $row;
          if (isset($entity_key)) {
            $locations[$location_ct]->entity_key = $entity_key;
          }
          $entity_type = $locations[$location_ct]->entity_type;
          $entity_id = $locations[$location_ct]->entity_id;
          $bundle = $locations[$location_ct]->bundle;
          $field_name = $locations[$location_ct]->field_name;
          $entity_get_info = entity_get_info($entity_type);
          $load_hook = $entity_get_info['load hook'];
          $object = FALSE;
          if ($load_hook && function_exists($load_hook)) {
            $object = $load_hook($entity_id);
          }
          if (isset($locations[$location_ct]->status)) {
            $status = $locations[$location_ct]->status;
          }
          else {
            $status = $object && isset($object->status) ? $object->status : '';
          }

          // sanity check
          if (!$status) {
            unset($locations[$location_ct]);
            continue;
          }
          if (empty($locations[$location_ct]->marker)) {
            $markertype = $entity_type . '_map_marker';

            // markers
            $getlocations_markers = variable_get('getlocations_markers', array());
            if (isset($getlocations_markers[$entity_type]['enable']) && $getlocations_markers[$entity_type]['enable']) {
              if (isset($getlocations_markers[$entity_type][$bundle][$field_name]['marker']) && $getlocations_markers[$entity_type][$bundle][$field_name]['marker']) {
                $locations[$location_ct]->marker = $getlocations_markers[$entity_type][$bundle][$field_name]['marker'];
              }
            }
            elseif (isset($getlocations_defaults[$markertype]) && $getlocations_defaults[$markertype]) {
              $locations[$location_ct]->marker = $getlocations_defaults[$markertype];
            }

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

          // title
          $entity_title = FALSE;
          if (!isset($locations[$location_ct]->title)) {

            #$locations[$location_ct]->title = $locations[$location_ct]->name;
            $title = 'name';
            if (isset($entity_get_info['entity keys']['label'])) {
              $title = $entity_get_info['entity keys']['label'];
            }
            if (isset($object->{$title})) {
              $entity_title = $object->{$title};
            }
            if (empty($locations[$location_ct]->name)) {
              if ($entity_title) {
                $locations[$location_ct]->name = $entity_title;
              }
            }
            elseif (!isset($locations[$location_ct]->title) || empty($locations[$location_ct]->title)) {
              $locations[$location_ct]->title = $locations[$location_ct]->name;
            }
          }
          $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';
        $tabledata[$ct]['fieldname'] = $fieldname;
        $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';
        $tabledata[$ct]['fieldname'] = $fieldname;
        $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'];
      $field_name = $data['fieldname'];
      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";
      $fields[] = "g.entity_id";
      $fields[] = "g.bundle";
      $sqlsnip1 = "";
      $sqlsnip2 = "";

      // need to deal with type
      if ($entity_type) {
        $entity_get_info = entity_get_info($entity_type);
        $base_table = $entity_get_info['base table'];
        $entity_key = $entity_get_info['entity keys']['id'];
        $title = '';
        if (isset($entity_get_info['entity keys']['label'])) {
          $title = $entity_get_info['entity keys']['label'];
        }
        elseif ($entity_type == 'user') {
          $title = 'name';
        }
        if ($title) {
          $fields[] = "b.{$title} AS title";
        }
        $fields[] = "b.status AS status";
        $sqlsnip1 = "LEFT JOIN {" . $base_table . "} b ON t.entity_id = b.{$entity_key} ";
      }
      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] = $row;
        if (isset($entity_key)) {
          $locations[$location_ct]->entity_key = $entity_key;
        }
        $entity_type = $locations[$location_ct]->entity_type;
        $entity_id = $locations[$location_ct]->entity_id;
        $bundle = $locations[$location_ct]->bundle;
        $entity_get_info = entity_get_info($entity_type);
        $load_hook = $entity_get_info['load hook'];
        $object = $load_hook($entity_id);
        if (isset($locations[$location_ct]->status)) {
          $status = $locations[$location_ct]->status;
        }
        else {
          $status = $object->status;
        }

        // sanity check
        if (!$status) {
          unset($locations[$location_ct]);
          continue;
        }

        // markers
        $marker = '';
        $markertype = $entity_type . '_map_marker';
        $getlocations_markers = variable_get('getlocations_markers', array());
        if (isset($getlocations_markers[$entity_type]['enable']) && $getlocations_markers[$entity_type]['enable']) {
          if (isset($getlocations_markers[$entity_type][$bundle][$field_name]['marker']) && $getlocations_markers[$entity_type][$bundle][$field_name]['marker']) {
            $marker = $getlocations_markers[$entity_type][$bundle][$field_name]['marker'];
          }
        }
        elseif (isset($getlocations_defaults[$markertype]) && $getlocations_defaults[$markertype]) {
          $marker = $getlocations_defaults[$markertype];
        }

        // just in case
        if (empty($marker)) {
          $marker = $getlocations_defaults['map_marker'];
        }
        $locations[$location_ct]->marker = $marker;
        $entity_title = FALSE;
        if (!isset($locations[$location_ct]->title)) {

          #$locations[$location_ct]->title = $locations[$location_ct]->name;
          $title = 'name';
          if (isset($entity_get_info['entity keys']['label'])) {
            $title = $entity_get_info['entity keys']['label'];
          }
          if (isset($object->{$title})) {
            $entity_title = $object->{$title};
          }
          if (empty($locations[$location_ct]->name)) {
            if ($entity_title) {
              $locations[$location_ct]->name = $entity_title;
            }
          }
          elseif (!isset($locations[$location_ct]->title) || empty($locations[$location_ct]->title)) {
            $locations[$location_ct]->title = $locations[$location_ct]->name;
          }
        }
        $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;
}