<?php class AirlineMapData extends CodonModule 
{


public static function getdeps()
{
$sql = "SELECT a.*, s.depicao FROM ".TABLE_PREFIX."schedules s
 LEFT JOIN ".TABLE_PREFIX."airports a ON s.depicao = a.icao GROUP BY s.depicao ORDER BY a.icao ASC";
return DB::get_results($sql);
}

public static function getdirectarrs($depicao)
{
$sql = "SELECT a.*, s.arricao FROM ".TABLE_PREFIX."schedules s
 LEFT JOIN ".TABLE_PREFIX."airports a ON s.arricao = a.icao WHERE s.depicao = '$depicao' GROUP BY s.arricao ORDER BY a.icao ASC";
return DB::get_results($sql);
}

public static function getviaflights($reticao, $depicao)
{
$sql = "SELECT a.*, s.arricao FROM ".TABLE_PREFIX."schedules s
 LEFT JOIN ".TABLE_PREFIX."airports a ON s.arricao = a.icao WHERE s.depicao = '$depicao' AND s.arricao != '$reticao' AND s.distance != '' AND s.distance > 200 AND s.distance < 500 AND a.hub != '1' GROUP BY s.arricao ORDER BY s.distance ASC LIMIT 2";
return DB::get_results($sql);
}

public static function getschedulesfromto($depicao, $arricao)
{
$sql = "SELECT s.*, a.name as aircraftname, dep.name as depname, arr.name as arrname FROM ".TABLE_PREFIX."schedules s
 LEFT JOIN ".TABLE_PREFIX."airports dep ON s.depicao = dep.icao
 LEFT JOIN ".TABLE_PREFIX."airports arr ON s.arricao = arr.icao
 LEFT JOIN ".TABLE_PREFIX."aircraft a ON s.aircraft = a.id
 WHERE s.depicao = '$depicao' AND s.arricao = '$arricao' AND s.enabled = '1' ORDER BY s.deptime ASC";
return DB::get_results($sql);
}

public static function GetACARSData($cutofftime = '')
	{
		//cutoff time in days
		if(empty($cutofftime))
		{
			// Go from minutes to hours
			$cutofftime = Config::Get('ACARS_LIVE_TIME');
			//$cutofftime = $cutofftime / 60;			
		}
		
		$sql = 'SELECT a.*, c.name as aircraftname,
					p.code as pilcode, p.pilotid as pilotid, p.firstname, p.lastname,
					dep.name as depname, dep.lat AS deplat, dep.lng AS deplng,
					arr.name as arrname, arr.lat AS arrlat, arr.lng AS arrlng
				FROM ' . TABLE_PREFIX .'acarsdata a
				LEFT JOIN '.TABLE_PREFIX.'aircraft c ON a.`aircraft`= c.`registration`
				LEFT JOIN '.TABLE_PREFIX.'pilots p ON a.`pilotid`= p.`pilotid`
				LEFT JOIN '.TABLE_PREFIX.'airports AS dep ON dep.icao = a.depicao
				LEFT JOIN '.TABLE_PREFIX.'airports AS arr ON arr.icao = a.arricao ';
		
		if($cutofftime !== 0)
		{
			$sql .= 'WHERE DATE_SUB(NOW(), INTERVAL '.$cutofftime.' MINUTE) <= a.`lastupdate`';
		}
		
		return DB::get_results($sql);
		DB::debug();
	}

public static function getflightinfo($acarsid)
	{
		
		$sql = "SELECT a.*, c.name as aircraftname,
					p.code as pilcode, p.pilotid as pilotid, p.firstname, p.lastname,
					dep.name as depname, dep.lat AS deplat, dep.lng AS deplng,
					arr.name as arrname, arr.lat AS arrlat, arr.lng AS arrlng
				FROM ". TABLE_PREFIX."acarsdata a
				LEFT JOIN ".TABLE_PREFIX."aircraft c ON a.`aircraft`= c.`registration`
				LEFT JOIN ".TABLE_PREFIX."pilots p ON a.`pilotid`= p.`pilotid`
				LEFT JOIN ".TABLE_PREFIX."airports AS dep ON dep.icao = a.depicao
				LEFT JOIN ".TABLE_PREFIX."airports AS arr ON arr.icao = a.arricao
                WHERE a.id = '$acarsid'";
		
		return DB::get_row($sql);
	}


public static function countposreports($pilotid, $flightnum)
	{
		
		$sql = "SELECT count(*) as count FROM acars_position WHERE pilotid = '$pilotid' AND flightnum ='$flightnum' ORDER BY id ASC";
		
		return $flights = DB::get_results($sql);
   	}


public static function getposreports($pilotid, $flightnum)
	{
		
		$sql = "SELECT * FROM acars_position WHERE pilotid = '$pilotid' AND flightnum ='$flightnum' ORDER BY positiontime ASC";
		
		return DB::get_results($sql);
	}
}
?>