+global\Spotter::searchSpotterData()

Complexity

Conditions	59
Paths	> 20000

Size

Total Lines	343
Code Lines	217

Duplication

Lines	0
Ratio	0 %

Importance

Changes

0

<?php
	public function searchSpotterData($q = '', $registration = '', $aircraft_icao = '', $aircraft_manufacturer = '', $highlights = '', $airline_icao = '', $airline_country = '', $airline_type = '', $airport = '', $airport_country = '', $callsign = '', $departure_airport_route = '', $arrival_airport_route = '', $owner = '',$pilot_id = '',$pilot_name = '',$altitude = '', $date_posted = '', $limit = '', $sort = '', $includegeodata = '',$origLat = '',$origLon = '',$dist = '')
	{
		global $globalTimezone, $globalDBdriver;
		require_once(dirname(__FILE__).'/class.Translation.php');
		$Translation = new Translation();

		date_default_timezone_set('UTC');

		$query_values = array();
		$additional_query = '';
		if ($q != "")
		{
			if (!is_string($q))
			{
				return false;
			} else {
				$q_array = explode(" ", $q);
				foreach ($q_array as $q_item){
					$q_item = filter_var($q_item,FILTER_SANITIZE_STRING);
					$additional_query .= " AND (";
					if (is_int($q_item)) $additional_query .= "(spotter_output.spotter_id like '%".$q_item."%') OR ";
					$additional_query .= "(spotter_output.aircraft_icao like '%".$q_item."%') OR ";
					$additional_query .= "(spotter_output.aircraft_name like '%".$q_item."%') OR ";
					$additional_query .= "(spotter_output.aircraft_manufacturer like '%".$q_item."%') OR ";
					$additional_query .= "(spotter_output.airline_icao like '%".$q_item."%') OR ";
					$additional_query .= "(spotter_output.airline_name like '%".$q_item."%') OR ";
					$additional_query .= "(spotter_output.airline_country like '%".$q_item."%') OR ";
					$additional_query .= "(spotter_output.departure_airport_icao like '%".$q_item."%') OR ";
					$additional_query .= "(spotter_output.departure_airport_name like '%".$q_item."%') OR ";
					$additional_query .= "(spotter_output.departure_airport_city like '%".$q_item."%') OR ";
					$additional_query .= "(spotter_output.departure_airport_country like '%".$q_item."%') OR ";
					$additional_query .= "(spotter_output.arrival_airport_icao like '%".$q_item."%') OR ";
					$additional_query .= "(spotter_output.arrival_airport_name like '%".$q_item."%') OR ";
					$additional_query .= "(spotter_output.arrival_airport_city like '%".$q_item."%') OR ";
					$additional_query .= "(spotter_output.arrival_airport_country like '%".$q_item."%') OR ";
					$additional_query .= "(spotter_output.registration like '%".$q_item."%') OR ";
					$additional_query .= "(spotter_output.owner_name like '%".$q_item."%') OR ";
					$additional_query .= "(spotter_output.pilot_id like '%".$q_item."%') OR ";
					$additional_query .= "(spotter_output.pilot_name like '%".$q_item."%') OR ";
					$additional_query .= "(spotter_output.ident like '%".$q_item."%') OR ";
					$translate = $Translation->ident2icao($q_item);
					if ($translate != $q_item) $additional_query .= "(spotter_output.ident like '%".$translate."%') OR ";
					$additional_query .= "(spotter_output.highlight like '%".$q_item."%')";
					$additional_query .= ")";
				}
			}
		}

		if ($registration != "")
		{
			$registration = filter_var($registration,FILTER_SANITIZE_STRING);
			if (!is_string($registration))
			{
				return false;
			} else {
				$additional_query .= " AND spotter_output.registration = :registration";
				$query_values = array_merge($query_values,array(':registration' => $registration));
			}
		}

		if ($aircraft_icao != "")
		{
			$aircraft_icao = filter_var($aircraft_icao,FILTER_SANITIZE_STRING);
			if (!is_string($aircraft_icao))
			{
				return false;
			} else {
				$additional_query .= " AND spotter_output.aircraft_icao = :aircraft_icao";
				$query_values = array_merge($query_values,array(':aircraft_icao' => $aircraft_icao));
			}
		}

		if ($aircraft_manufacturer != "")
		{
			$aircraft_manufacturer = filter_var($aircraft_manufacturer,FILTER_SANITIZE_STRING);
			if (!is_string($aircraft_manufacturer))
			{
				return false;
			} else {
				$additional_query .= " AND spotter_output.aircraft_manufacturer = :aircraft_manufacturer";
				$query_values = array_merge($query_values,array(':aircraft_manufacturer' => $aircraft_manufacturer));
			}
		}

		if ($highlights == "true")
		{
			if (!is_string($highlights))
			{
				return false;
			} else {
				$additional_query .= " AND (spotter_output.highlight <> '')";
			}
		}

		if ($airline_icao != "")
		{
			$airline_icao = filter_var($airline_icao,FILTER_SANITIZE_STRING);
			if (!is_string($airline_icao))
			{
				return false;
			} else {
				$additional_query .= " AND spotter_output.airline_icao = :airline_icao";
				$query_values = array_merge($query_values,array(':airline_icao' => $airline_icao));
			}
		}

		if ($airline_country != "")
		{
			$airline_country = filter_var($airline_country,FILTER_SANITIZE_STRING);
			if (!is_string($airline_country))
			{
				return false;
			} else {
				$additional_query .= " AND spotter_output.airline_country = :airline_country";
				$query_values = array_merge($query_values,array(':airline_country' => $airline_country));
			}
		}

		if ($airline_type != "")
		{
			if (!is_string($airline_type))
			{
				return false;
			} else {
				if ($airline_type == "passenger")
				{
					$additional_query .= " AND (spotter_output.airline_type = 'passenger')";
				}
				if ($airline_type == "cargo")
				{
					$additional_query .= " AND (spotter_output.airline_type = 'cargo')";
				}
				if ($airline_type == "military")
				{
					$additional_query .= " AND (spotter_output.airline_type = 'military')";
				}
			}
		}

		if ($airport != "")
		{
			$airport = filter_var($airport,FILTER_SANITIZE_STRING);
			if (!is_string($airport))
			{
				return false;
			} else {
				$additional_query .= " AND (spotter_output.departure_airport_icao = :airport OR spotter_output.arrival_airport_icao = :airport)";
				$query_values = array_merge($query_values,array(':airport' => $airport));
			}
		}

		if ($airport_country != "")
		{
			$airport_country = filter_var($airport_country,FILTER_SANITIZE_STRING);
			if (!is_string($airport_country))
			{
				return false;
			} else {
				$additional_query .= " AND (spotter_output.departure_airport_country = :airport_country OR spotter_output.arrival_airport_country = :airport_country)";
				$query_values = array_merge($query_values,array(':airport_country' => $airport_country));
			}
		}
    
		if ($callsign != "")
		{
			$callsign = filter_var($callsign,FILTER_SANITIZE_STRING);
			if (!is_string($callsign))
			{
				return false;
			} else {
				$translate = $Translation->ident2icao($callsign);
				if ($translate != $callsign) {
					$additional_query .= " AND (spotter_output.ident = :callsign OR spotter_output.ident = :translate)";
					$query_values = array_merge($query_values,array(':callsign' => $callsign,':translate' => $translate));
				} else {
					$additional_query .= " AND spotter_output.ident = :callsign";
					$query_values = array_merge($query_values,array(':callsign' => $callsign));
				}
			}
		}

		if ($owner != "")
		{
			$owner = filter_var($owner,FILTER_SANITIZE_STRING);
			if (!is_string($owner))
			{
				return false;
			} else {
				$additional_query .= " AND spotter_output.owner_name = :owner";
				$query_values = array_merge($query_values,array(':owner' => $owner));
			}
		}

		if ($pilot_name != "")
		{
			$pilot_name = filter_var($pilot_name,FILTER_SANITIZE_STRING);
			if (!is_string($pilot_name))
			{
				return false;
			} else {
				$additional_query .= " AND spotter_output.pilot_name = :pilot_name";
				$query_values = array_merge($query_values,array(':pilot_name' => $pilot_name));
			}
		}

		if ($pilot_id != "")
		{
			$pilot_id = filter_var($pilot_id,FILTER_SANITIZE_NUMBER_INT);
			if (!is_string($pilot_id))
			{
				return false;
			} else {
				$additional_query .= " AND spotter_output.pilot_id = :pilot_id";
				$query_values = array_merge($query_values,array(':pilot_id' => $pilot_id));
			}
		}

		if ($departure_airport_route != "")
		{
			$departure_airport_route = filter_var($departure_airport_route,FILTER_SANITIZE_STRING);
			if (!is_string($departure_airport_route))
			{
				return false;
			} else {
				$additional_query .= " AND spotter_output.departure_airport_icao = :departure_airport_route";
				$query_values = array_merge($query_values,array(':departure_airport_route' => $departure_airport_route));
			}
		}

		if ($arrival_airport_route != "")
		{
			$arrival_airport_route = filter_var($arrival_airport_route,FILTER_SANITIZE_STRING);
			if (!is_string($arrival_airport_route))
			{
				return false;
			} else {
				$additional_query .= " AND spotter_output.arrival_airport_icao = :arrival_airport_route";
				$query_values = array_merge($query_values,array(':arrival_airport_route' => $arrival_airport_route));
			}
		}

		if ($altitude != "")
		{
			$altitude_array = explode(",", $altitude);
			$altitude_array[0] = filter_var($altitude_array[0],FILTER_SANITIZE_NUMBER_FLOAT,FILTER_FLAG_ALLOW_FRACTION);
			$altitude_array[1] = filter_var($altitude_array[1],FILTER_SANITIZE_NUMBER_FLOAT,FILTER_FLAG_ALLOW_FRACTION);

			if ($altitude_array[1] != "")
			{                
				$altitude_array[0] = substr($altitude_array[0], 0, -2);
				$altitude_array[1] = substr($altitude_array[1], 0, -2);
				$additional_query .= " AND altitude BETWEEN '".$altitude_array[0]."' AND '".$altitude_array[1]."' ";
			} else {
				$altitude_array[0] = substr($altitude_array[0], 0, -2);
				$additional_query .= " AND altitude <= '".$altitude_array[0]."' ";
			}
		}

		if ($date_posted != "")
		{
			$date_array = explode(",", $date_posted);
			$date_array[0] = filter_var($date_array[0],FILTER_SANITIZE_STRING);
			$date_array[1] = filter_var($date_array[1],FILTER_SANITIZE_STRING);

			if ($globalTimezone != '') {
				date_default_timezone_set($globalTimezone);
				$datetime = new DateTime();
				$offset = $datetime->format('P');
			} else $offset = '+00:00';

			if ($date_array[1] != "")
			{
				$date_array[0] = date("Y-m-d H:i:s", strtotime($date_array[0]));
				$date_array[1] = date("Y-m-d H:i:s", strtotime($date_array[1]));
				if ($globalDBdriver == 'mysql') {
					$additional_query .= " AND TIMESTAMP(CONVERT_TZ(spotter_output.date,'+00:00', '".$offset."')) >= '".$date_array[0]."' AND TIMESTAMP(CONVERT_TZ(spotter_output.date,'+00:00', '".$offset."')) <= '".$date_array[1]."' ";
				} else {
					$additional_query .= " AND CAST(spotter_output.date AT TIME ZONE INTERVAL ".$offset." AS TIMESTAMP) >= '".$date_array[0]."' AND CAST(spotter_output.date AT TIME ZONE INTERVAL ".$offset." AS TIMESTAMP) <= '".$date_array[1]."' ";
				}
			} else {
				$date_array[0] = date("Y-m-d H:i:s", strtotime($date_array[0]));
				if ($globalDBdriver == 'mysql') {
					$additional_query .= " AND TIMESTAMP(CONVERT_TZ(spotter_output.date,'+00:00', '".$offset."')) >= '".$date_array[0]."' ";
				} else {
					$additional_query .= " AND CAST(spotter_output.date AT TIME ZONE INTERVAL ".$offset." AS TIMESTAMP) >= '".$date_array[0]."' ";
				}
			}
		}

		if ($limit != "")
		{
			$limit_array = explode(",", $limit);
			
			$limit_array[0] = filter_var($limit_array[0],FILTER_SANITIZE_NUMBER_INT);
			$limit_array[1] = filter_var($limit_array[1],FILTER_SANITIZE_NUMBER_INT);
			
			if ($limit_array[0] >= 0 && $limit_array[1] >= 0)
			{
				//$limit_query = " LIMIT ".$limit_array[0].",".$limit_array[1];
				$limit_query = " LIMIT ".$limit_array[1]." OFFSET ".$limit_array[0];
			} else $limit_query = "";
		} else $limit_query = "";


		if ($sort != "")
		{
			$search_orderby_array = $this->getOrderBy();
			$orderby_query = $search_orderby_array[$sort]['sql'];
		} else {
			if ($origLat != "" && $origLon != "" && $dist != "") {
				$orderby_query = "  ORDER BY distance ASC";
			} else {
				$orderby_query = " ORDER BY spotter_output.date DESC";
			}
		}

		if ($includegeodata == "true")
		{
			$additional_query .= " AND spotter_output.waypoints <> ''";
		}


		if ($origLat != "" && $origLon != "" && $dist != "") {
			$dist = number_format($dist*0.621371,2,'.',''); // convert km to mile

			if ($globalDBdriver == 'mysql') {
				$query="SELECT spotter_output.*, 1.60935*3956 * 2 * ASIN(SQRT( POWER(SIN(($origLat - spotter_archive.latitude)*pi()/180/2),2)+COS( $origLat *pi()/180)*COS(spotter_archive.latitude*pi()/180)*POWER(SIN(($origLon-spotter_archive.longitude)*pi()/180/2),2))) as distance 
						FROM spotter_output, spotter_archive WHERE spotter_output.flightaware_id = spotter_archive.flightaware_id AND spotter_output.ident <> '' ".$additional_query."AND spotter_archive.longitude between ($origLon-$dist/cos(radians($origLat))*69) and ($origLon+$dist/cos(radians($origLat)*69)) and spotter_archive.latitude between ($origLat-($dist/69)) and ($origLat+($dist/69)) 
						AND (3956 * 2 * ASIN(SQRT( POWER(SIN(($origLat - spotter_archive.latitude)*pi()/180/2),2)+COS( $origLat *pi()/180)*COS(spotter_archive.latitude*pi()/180)*POWER(SIN(($origLon-spotter_archive.longitude)*pi()/180/2),2)))) < $dist".$orderby_query;
			} else {
				$query="SELECT spotter_output.*, 1.60935 * 3956 * 2 * ASIN(SQRT( POWER(SIN(($origLat - CAST(spotter_archive.latitude as double precision))*pi()/180/2),2)+COS( $origLat *pi()/180)*COS(CAST(spotter_archive.latitude as double precision)*pi()/180)*POWER(SIN(($origLon-CAST(spotter_archive.longitude as double precision))*pi()/180/2),2))) as distance 
						FROM spotter_output, spotter_archive WHERE spotter_output.flightaware_id = spotter_archive.flightaware_id AND spotter_output.ident <> '' ".$additional_query."AND CAST(spotter_archive.longitude as double precision) between ($origLon-$dist/cos(radians($origLat))*69) and ($origLon+$dist/cos(radians($origLat))*69) and CAST(spotter_archive.latitude as double precision) between ($origLat-($dist/69)) and ($origLat+($dist/69)) 
						AND (3956 * 2 * ASIN(SQRT( POWER(SIN(($origLat - CAST(spotter_archive.latitude as double precision))*pi()/180/2),2)+COS( $origLat *pi()/180)*COS(CAST(spotter_archive.latitude as double precision)*pi()/180)*POWER(SIN(($origLon-CAST(spotter_archive.longitude as double precision))*pi()/180/2),2)))) < $dist".$orderby_query;
			}
		} else {		
			$query  = "SELECT spotter_output.* FROM spotter_output 
					WHERE spotter_output.ident <> '' 
					".$additional_query."
					".$orderby_query;
		}
		$spotter_array = $this->getDataFromDB($query, $query_values,$limit_query);
		return $spotter_array;
	}
	
?>