<?php

function calc_gearth($timeframe_human, $timeframe_sql, $link) {
/* Prepare an XML file for Google Earth */

	$timestamp = gmdate("D, d M Y");
	$query = 'SELECT LATITUDE, LONGITUDE FROM GPS '
	       . 'WHERE '.$timeframe_sql.' AND STATUS IN (\'A\',\'B\',\'X\',\'C\',\'T\',\'R\') '
		   . 'ORDER BY GPSMSGID ASC';
	$result = mysqli_query($link, $query);
	
	if (!$result) {
	   printf("Query failed: %s\n", mysqli_error($link));
	   exit();
	}
	
	//Start writing the XML
	$bestand = "
	<Placemark>
		<name>Distance of $timeframe_human</name>      
		<styleUrl>#yellowLineGreenPoly</styleUrl>
		<LineString>
			<extrude>1</extrude>
			<tessellate>1</tessellate>
			<altitudeMode>absolute</altitudeMode>
			<coordinates>\r\n";
	
	//Write the coordinates to the Google Earth XML file
	while ($line = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
	   foreach ($line as $col_value) {
			$lon = $line['LONGITUDE'];
			$lat = $line['LATITUDE'];
			if ($lon && $lat) {
				$bestand .= "				";
				$bestand .= $line['LONGITUDE'];
				$bestand .= ",";
				$bestand .= $line['LATITUDE'];
				$bestand .= "\r\n";
			}
		}
	}
	
	//Close the Google Earth XML properly
	$bestand .= "			</coordinates>
		</LineString>
	</Placemark>";
	
return $bestand;
}


//Open XML file with default header
$bestand = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>
<kml xmlns=\"http://earth.google.com/kml/2.2\">
	<Document>
	<name>GG-Tracker</name>
	<description>History of positions</description>
	<Style id=\"yellowLineGreenPoly\">
		<LineStyle>
			<color>7f00ffff</color>
			<width>4</width>
		</LineStyle>
		<PolyStyle>
			<color>7f00ff00</color>
		</PolyStyle>
	</Style>";


//Calculate today
if (($gearthperiod == "today") || ($gearthperiod == "yesterday") || ($gearthperiod == "week")) {
	$timeframe = "today";
	$bestand .= calc_gearth($timeframe, "TIME_RECEIVED >= CURDATE()", $link);	
} 

//Calculate yesterday
if (($gearthperiod == "yesterday") || ($gearthperiod == "week")) {
	$timeframe = "yesterday";
	$bestand .= calc_gearth($timeframe, "TIME_RECEIVED >= (CURDATE()-1) AND TIME_RECEIVED <= CURDATE()", $link);
}

//Calculate last week
if ($gearthperiod == "week") {
	$timeframe = date("d-m-Y", mktime(0,0,0,date("m"),date("d")-2,date("Y")));
	$bestand .= calc_gearth($timeframe, "TIME_RECEIVED >= (CURDATE()-2) AND TIME_RECEIVED <= (CURDATE()-1)", $link);

	//Calculate three days ago
	$timeframe = date("d-m-Y", mktime(0,0,0,date("m"),date("d")-3,date("Y")));
	$bestand .= calc_gearth($timeframe, "TIME_RECEIVED >= (CURDATE()-3) AND TIME_RECEIVED <= (CURDATE()-2)", $link);
	
	//Calculate four days ago
	$timeframe = date("d-m-Y", mktime(0,0,0,date("m"),date("d")-4,date("Y")));
	$bestand .= calc_gearth($timeframe, "TIME_RECEIVED >= (CURDATE()-4) AND TIME_RECEIVED <= (CURDATE()-3)", $link);
	
	//Calculate five days ago
	$timeframe = date("d-m-Y", mktime(0,0,0,date("m"),date("d")-5,date("Y")));
	$bestand .= calc_gearth($timeframe, "TIME_RECEIVED >= (CURDATE()-5) AND TIME_RECEIVED <= (CURDATE()-4)", $link);
	
	//Calculate six days ago
	$timeframe = date("d-m-Y", mktime(0,0,0,date("m"),date("d")-6,date("Y")));
	$bestand .= calc_gearth($timeframe, "TIME_RECEIVED >= (CURDATE()-6) AND TIME_RECEIVED <= (CURDATE()-5)", $link);
}

//Calculate last month
if ($gearthperiod == "month") {
	$timeframe = "last month";
	$bestand .= calc_gearth($timeframe, "TIME_RECEIVED >= (CURDATE()-30)", $link);
}

//Calculate last year
if ($gearthperiod == "year") {
	$timeframe = "last year";
	$bestand .= calc_gearth($timeframe, "TIME_RECEIVED >= (CURDATE()-365)", $link);
}

//Close XML file with default footer
$bestand .= "
	</Document>
</kml>";


//Write XML file, first erase old file then create new
$handle = fopen($gearthfile, 'w') or die("Can't open Google Earth file: ".$gearthfile);
fclose($handle);
unlink($gearthfile);
$handle = fopen($gearthfile, 'a');
fwrite($handle, $bestand);
fclose($handle);

?>