<?php
	/************************************
	*Parser le fichier NAVAIDS Aivlasoft*
	*************************************/

include("../core/codon.config.php");
include("../core/local.config.php");

$servername = "localhost";
$username = DBASE_USER;
$password = DBASE_PASS;
$dbname = DBASE_NAME;
$tbname = "phpvms_navdata";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "TRUNCATE table $tbname";


if ($conn->multi_query($sql) === TRUE) {
    echo "Deleted successfully table: ".$tbname."\n";
} else {
    echo "Error: " . $sql . "<br>\n" . $conn->error;
}


define('NAV_DME', 1);	
define('NAV_NDB', 2);
define('NAV_VOR', 3);
define('NAV_VORD', 4);
define('NAV_FIX', 5);
define('NAV_TRACK', 6);
define('NAV_ILS', 7);
define('NAV_ILSD', 8);

function get_coordinates($line) {
        /* Get the lat/long */
        preg_match('/^([A-Za-z])(\d*):(\d*:\d*)/', $line, $coords);

        $lat_dir = $coords[1];
        $lat_deg = $coords[2];
        $lat_min = $coords[3];

        $lat_deg = ($lat_deg*1.0) + ($lat_min/60.0);

        if(strtolower($lat_dir) == 's')
                $lat_deg = '-'.$lat_deg;

        if(strtolower($lat_dir) == 'w')
                $lat_deg = $lat_deg*-1;

        return $lat_deg;
}


/**
 * Return string with general location:
 * NAM - North America
 * SAM - South American
 * EUR - Europe
 * ASA - Asia
 * AFR - Africa
 * AUS - Australia
 * NAT - North Atlantic Track
 * SAT - South Atlantic Track
 * PAT - Pacific Ocean Track
 */
function get_earth_location($lat, $lng) {
        
        // Asia
        if($lng > 34 && $lng < 180) {
                // Australia
                if($lat < -11) {
                        return 'AUS';
                }
                
                return 'ASA';
        }
        
        
        // NAT
        if($lng > -58 && $lng < -11) {
                if($lat > 42) {
                        return 'NAT';
                }
        }
        
        // Europe
        if($lng > -15 && $lng < 40) {
                if($lat > 35) {
                        return 'EUR';
                }
        }
        
        // NAM
        if($lng > -180 && $lng < - 56){
                if($lat > 25) {
                        return 'NAM';
                } else {
                        return 'SAM';
                }
        }
        
        return '';
}
	
	header("Content-type: text/plain; charset=utf-8");
	/* header('Content-Type: text/csv; charset=utf-8');
	header('Content-Disposition: attachment; filename=navaids.csv'); */
	
	$mtime=microtime();
	$mtime=explode(" ", $mtime);
	$mtime=$mtime[1]+$mtime[0];
	$starttime=$mtime;
	/* add */
	$total = 1;
	/* add */
	$compteur=0;
	$requete="";
	
	$fichier=fopen("WPNAVAID.txt", "r");
	while(!feof($fichier))
	{
		$ligne=trim(fgets($fichier));
		if(!empty($ligne) && substr($ligne, 0, 1)!=";")
		{
			$ai_nom=trim(substr($ligne, 0, 24));
			$ai_code=trim(substr($ligne, 24, 5));
			$ai_type=trim(substr($ligne, 29, 4));
			$ai_lat=trim(substr($ligne, 33, 10));
			$ai_lon=trim(substr($ligne, 43, 11));
			$ai_freq=trim(substr($ligne, 54));			
			$ai_freq=substr($ai_freq, 0, strlen($ai_freq)-1);
			
		$loc = get_earth_location($ai_lat, $ai_lon);

/* 
define('NAV_NDB', 2);
define('NAV_VOR', 3);
define('NAV_DME', 4);
define('NAV_FIX', 5);
define('NAV_TRACK', 6);


define('NAV_DME', 1);	
define('NAV_NDB', 2);
define('NAV_VOR', 3);
define('NAV_VORD', 4);
define('NAV_FIX', 5);
define('NAV_TRACK', 6);
define('NAV_ILS', 7);
define('NAV_ILSD', 8);
*/		
		switch($ai_type)
		{
		case 'DME':
		$ai_type=1;
		break;
		
		case 'NDB':
		$ai_type=2;
		break;
	
		case 'VOR':
		$ai_type=3;
		break;

	
		case 'VORD':
		$ai_type=4;
		break;

		case 'FIX':
		$ai_type=5;
		break;
		
		case 'TRACK':
		$ai_type=6;
		break;
		
		case 'ILS':
		$ai_type=7;
		break;
		
		case 'ILSD':
		$ai_type=8;
		break;

		default :
		$ai_type=5;
		break;
		}
		
		$compteur++;

			if(empty($requete))
			{$requete="INSERT INTO $tbname (id, name, title, airway, airway_type, seq, loc, lat, lng, freq, type) VALUES ";}

			if($compteur%75!=0)
			{
				$requete.="('".$total++."','".$ai_code."','".$ai_nom."', NULL, NULL, NULL, '".$loc."', '".$ai_lat."', '".$ai_lon."', '".$ai_freq."', '".$ai_type."'),";
			}
			else
			{
				$requete.="('".$total++."','".$ai_code."','".$ai_nom."', NULL, NULL, NULL, '".$loc."', '".$ai_lat."', '".$ai_lon."', '".$ai_freq."', '".$ai_type."');";
				// TODO : Exécuter le contenu de $requete !
			if ($conn->multi_query($requete) === TRUE) {
			echo $compteur." New records created successfully\n";
			echo $requete."\n";
			} else {
			echo "Error: " . $requete . "<br>\n" . $conn->error;
			}
			/*$conn->close();*/
		
				$requete="";
			}
		
			/*echo $ai_nom.";".$ai_code.";".$ai_type.";".$ai_lat.";".$ai_lon.";".$ai_freq."\n";*/
			//echo $total++.";".$ai_code.";".$ai_nom.";".'NULL'.";".'NULL'.";".'NULL'.";".$loc.";".$ai_lat.";".$ai_lon.";".$ai_freq.";".$ai_type."\n";

		}
	}
	fclose($fichier);
	
	// Requête en cours si non vide
	if(!empty($requete))
	{
		$requete=rtrim($requete, ",");
		$requete.=";";
		// TODO : Exécuter le contenu de $requete !
		if ($conn->multi_query($requete) === TRUE) {
		echo $compteur." New records created successfully\n";
		echo $requete."\n";
		} else {
		echo "Error: " . $requete . "<br>\n" . $conn->error;
		}
		//$conn->close();

	}

	$mtime=microtime();
	$mtime=explode(" ", $mtime);
	$mtime=$mtime[1]+$mtime[0];
	$endtime=$mtime;
	$totaltime=($endtime-$starttime);
	echo"OK : Done in ".$totaltime." seconds";
	
// Parse Navfix

	
	$mtime=microtime();
	$mtime=explode(" ", $mtime);
	$mtime=$mtime[1]+$mtime[0];
	$starttime=$mtime;
	/* add */
	$total = 1;
	/* add */
	$compteur=0;
	$requete="";
	
	$fichier=fopen("WPNAVFIX.txt", "r");
	while(!feof($fichier))
	{
		$ligne=trim(fgets($fichier));
		if(!empty($ligne) && substr($ligne, 0, 1)!=";")
		{
			$ai_nom=trim(substr($ligne, 0, 24));
			$ai_code=trim(substr($ligne, 24, 5));
			$ai_lat=trim(substr($ligne, 29, 10));
			$ai_lon=trim(substr($ligne, 40, 10));
			$ai_freq = 'NULL';
			/* 
	define('NAV_NDB', 2);
	define('NAV_VOR', 3);
	define('NAV_DME', 4);
	define('NAV_FIX', 5);
	define('NAV_TRACK', 6);


define('NAV_DME', 1);	
define('NAV_NDB', 2);
define('NAV_VOR', 3);
define('NAV_VORD', 4);
define('NAV_FIX', 5);
define('NAV_TRACK', 6);
define('NAV_ILS', 7);
define('NAV_ILSD', 8);
*/		
		switch($ai_type)
		{
		case 'DME':
		$ai_type=1;
		break;
		
		case 'NDB':
		$ai_type=2;
		break;
	
		case 'VOR':
		$ai_type=3;
		break;

	
		case 'VORD':
		$ai_type=4;
		break;

		case 'FIX':
		$ai_type=5;
		break;
		
		case 'TRACK':
		$ai_type=6;
		break;
		
		case 'ILS':
		$ai_type=7;
		break;
		
		case 'ILSD':
		$ai_type=8;
		break;

		default :
		$ai_type=5;
		break;
		}
			$loc = get_earth_location($ai_lat, $ai_lon);
			
			$compteur++;

			if(empty($requete))
			{$requete="INSERT INTO $tbname (id, name, title, airway, airway_type, seq, loc, lat, lng, freq, type) VALUES ";}

			if($compteur%75!=0)
			{
				$requete.="('".$total++."','".$ai_code."','".$ai_nom."',NULL,NULL,NULL,'".$loc."','".$ai_lat."','".$ai_lon."',NULL,'".$ai_type."'),";
			}
			else
			{
				$requete.="('".$total++."','".$ai_code."','".$ai_nom."',NULL,NULL,NULL,'".$loc."','".$ai_lat."','".$ai_lon."',NULL,'".$ai_type."');";
				// TODO : Exécuter le contenu de $requete !
			if ($conn->multi_query($requete) === TRUE) {
			echo $compteur." New records created successfully\n";
			echo $requete."\n";
			} else {
			echo "Error: " . $requete . "<br>\n" . $conn->error;
			}
			/*$conn->close();*/
		
				$requete="";
			}
		
			/*echo $ai_nom.";".$ai_code.";".$ai_type.";".$ai_lat.";".$ai_lon.";".$ai_freq."\n";*/
			//echo $total++.";".$ai_code.";".$ai_nom.";".'NULL'.";".'NULL'.";".'NULL'.";".$loc.";".$ai_lat.";".$ai_lon.";".$ai_freq.";".$ai_type."\n";

		}
	}
	fclose($fichier);
	
	// Requête en cours si non vide
	if(!empty($requete))
	{
		$requete=rtrim($requete, ",");
		$requete.=";";
		// TODO : Exécuter le contenu de $requete !
		if ($conn->multi_query($requete) === TRUE) {
		echo $compteur." New records created successfully\n";
		echo $requete."\n";
		} else {
		echo "Error: " . $requete . "<br>\n" . $conn->error;
		}
		//$conn->close();

	}

	$mtime=microtime();
	$mtime=explode(" ", $mtime);
	$mtime=$mtime[1]+$mtime[0];
	$endtime=$mtime;
	$totaltime=($endtime-$starttime);
	echo"OK : Done in ".$totaltime." seconds";
	
	
	// Parse Navroute
	
	$mtime=microtime();
	$mtime=explode(" ", $mtime);
	$mtime=$mtime[1]+$mtime[0];
	$starttime=$mtime;
	/* add */
	$total;
	$compteur=0;
	$requete="";
	/* add */
	//echo "Loading airways segments...\n";

	$fichier=fopen("WPNAVRTE.txt", "r");
	while(!feof($fichier))
	{
		$ligne=trim(fgets($fichier));
		if(!empty($ligne) && substr($ligne, 0, 1)!=";")
		{
			/*array (
			0 => 'R446',
			1 => '002',
			2 => 'DASKO',
			3 => '49.105278',
			4 => '138.142778',
			5 => 'FL100',
			6 => 'FL100',
			7 => 'FL530',
			8 => '120',
			9 => '121',
			10 => 'B',
			11 => '-',
			); */
			
			$pieces = explode(" ", $ligne);
			/* echo $pieces[0].";".$pieces[1].";".$pieces[2].";".$pieces[3].";".$pieces[4].";".$pieces[10]."\n"; */
			/* echo "phpvms_navdata format \n"; */
			//echo $total++.";".$pieces[2].";".$pieces[2].";".$pieces[0].";".$pieces[10].";".$pieces[1].";".'NULL'.";".$pieces[3].";".$pieces[4].";".'NULL'.";"."5"."\n";
			
			$loc = get_earth_location($pieces[3], $pieces[4]);
			/* 
			define('NAV_NDB', 2);
			define('NAV_VOR', 3);
			define('NAV_DME', 4);
			define('NAV_FIX', 5);
			define('NAV_TRACK', 6);


			define('NAV_DME', 1);	
			define('NAV_NDB', 2);
			define('NAV_VOR', 3);
			define('NAV_VORD', 4);
			define('NAV_FIX', 5);
			define('NAV_TRACK', 6);
			define('NAV_ILS', 7);
			define('NAV_ILSD', 8);
			*/		
		switch($ai_type)
		{
		case 'DME':
		$ai_type=1;
		break;
		
		case 'NDB':
		$ai_type=2;
		break;
	
		case 'VOR':
		$ai_type=3;
		break;

	
		case 'VORD':
		$ai_type=4;
		break;

		case 'FIX':
		$ai_type=5;
		break;
		
		case 'TRACK':
		$ai_type=6;
		break;
		
		case 'ILS':
		$ai_type=7;
		break;
		
		case 'ILSD':
		$ai_type=8;
		break;

		default :
		$ai_type=6;
		break;
		}
			$compteur++;

			if(empty($requete))
			{$requete="INSERT INTO $tbname (id, name, title, airway, airway_type, seq, loc, lat, lng, freq, type) VALUES ";}

			if($compteur%75!=0)
			{
				$requete.="('".$total++."','".$pieces[2]."','".$pieces[2]."','".$pieces[0]."','".$pieces[10]."','".$pieces[1]."','".$loc."','".$pieces[3]."','".$pieces[4]."',NULL,'".$ai_type."'),";
			}
			else
			{
				$requete.="('".$total++."','".$pieces[2]."','".$pieces[2]."', '".$pieces[0]."', '".$pieces[10]."','".$pieces[1]."', '".$loc."', '".$pieces[3]."', '".$pieces[4]."', NULL,'".$ai_type."');";
				// TODO : Exécuter le contenu de $requete !
			if ($conn->multi_query($requete) === TRUE) {
			echo $compteur." New records created successfully\n";
			echo $requete."\n";
			} else {
			echo "Error: " . $requete . "<br>\n" . $conn->error;
			}
			/*$conn->close();*/
		
				$requete="";
			}
		

		}
	}
	fclose($fichier);
	
	// Requête en cours si non vide
	if(!empty($requete))
	{
		$requete=rtrim($requete, ",");
		$requete.=";";
		// TODO : Exécuter le contenu de $requete !
		if ($conn->multi_query($requete) === TRUE) {
		echo $compteur." New records created successfully\n";
		echo $requete."\n";
		} else {
		echo "Error: " . $requete . "<br>\n" . $conn->error;
		}
		//$conn->close();

	}

	$mtime=microtime();
	$mtime=explode(" ", $mtime);
	$mtime=$mtime[1]+$mtime[0];
	$endtime=$mtime;
	$totaltime=($endtime-$starttime);
	echo"OK : Done in ".$totaltime." seconds\n";
	
	//update spot
$sql = "UPDATE $tbname P INNER JOIN
(SELECT * FROM $tbname
 WHERE type IN (1, 2, 3, 4, 5, 6, 7, 8)
 AND freq IS NOT NULL AND freq <> 'NULL') SUB
ON SUB.name = P.name
AND SUB.lat = P.lat
AND SUB.lng = P.lng
SET P.freq = SUB.freq , P.title = SUB.title
WHERE P.airway <> 'NULL' ";	

if ($conn->multi_query($sql) === TRUE) {
    echo "Updated NAVROUTE and FIX successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>