<?php

/**************************************************************************************************
**	Author:     Peter McCutcheon
**	Created:    2017-07-02
**	Modified:   2017-07-02
**
**-------------------------------------------------------------------------------------------------
**Description:
**
**		This script reads an Ajax request.  Using the data from the ajax request it creates
**      a SQL command and reads the data from the database.
**
**		The post variables are as follows:
**
**			airport=airport_code	!Three letter airport code.
**			airline=airline_code	!Unique airline id identifier.
**			startdate=date      	!The starting date for the select statement YYYY-MM-DD.
**			enddate=date			!The ending date for the select statement YYYY-MM-DD.
**
***************************************************************************************************/

/**************************************************************************************************
**
**	General set up.  Get the ajax posted values. 
**
***************************************************************************************************/

//
//  Set up an environment variable to see if we are testing
//  or live.
//
$environ = "test";
if ($environ == "test")
{
    $dbhost = "localhost";
    $dbname = "flightdata";
    $dbuser = "python";
    $dbpass = "python";
}
else
{
    $dbhost = "50.62.209.161:3306";
    $dbname = "petermccutcheon_flightdata";
    $dbuser = "webaccess";
    $dbpass = "!!Python1";    
}    

set_time_limit(0);
$fd = array(array_fill_keys(array('xValue','yValue'),''));

$dbhost = "50.62.209.161:3306";
$dbname = "petermccutcheon_flightdata";
$dbuser = "webaccess";
$dbpass = "!!Python1";

$conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
if ($conn->connect_error)
{
    die("Connection error:" . $conn->connect_error);
}

$type = $_POST['type'];
if ($type == "daily")
{
    $airport = $_POST['airport'];
    $airline = $_POST['airline'];
    $startdate = $_POST['startdate'];
    $enddate = $_POST['enddate'];
}
else
{
    if ($type == "detail")
    {
        $airport = $_POST['airport'];
        $airline = $_POST['airline'];
        $selectdate = $_POST['selectdate'];
    }
    else
    {
        if ($type == "flight")
        {
            $airport = $_POST['airport'];
            $airline = $_POST['airline'];
            $selectdate = $_POST['selectdate'];
            $flightnumber = $_POST['flightnumber'];
        }
    }
}

/**************************************************************************************************
**
**	Main program.
**
***************************************************************************************************/

if ($type == "daily")
{
    $sql = "select flight_date, sum(time_to_sec(departure_delay)/60) from flightinfo where origin = '" . $airport . "' and airline_id = '" . $airline . "' and (flight_date >= '" . $startdate . "' and flight_date <= '" . $enddate . "') group by flight_date;";    
}
else
{
    if ($type == "detail")
    {
        $sql = "select flight_number, time_to_sec(departure_delay)/60 from flightinfo where origin = '" . $airport . "' and airline_id = '" . $airline . "' and flight_date = '" . $selectdate . "';";        
    }
    else
    {
        if ($type == "flight")
        {
            $sql = "select flight_date, time_to_sec(departure_delay)/60 from flightinfo where origin = '" . $airport . "' and airline_id = '" . $airline . "' and flight_number = '" . $flightnumber . "';";
        }
    }
}
$result = $conn->query($sql);
$resp = "";
if ($result->num_rows > 0)
{
    $i = 0;
    while ($row = mysqli_fetch_array($result, MYSQLI_NUM))
    {
        $fd[$i]['xValue'] = $row[0];
        $fd[$i]['yValue'] = $row[1];
        $i++;
    }
    if ($type == "flight")
    {
        $sql = "select tail_number, ap_desc, time_to_sec(departure_delay)/60, time_to_sec(carrier_delay)/60, time_to_sec(weather_delay)/60, time_to_sec(security_delay)/60, time_to_sec(late_aircraft_delay)/60 from flightinfo inner join AirportCodes on ap_code = destination where origin = '" . $airport . "' and airline_id = '" . $airline . "' and flight_number = '" . $flightnumber . "' and flight_date ='" . $selectdate . "';";
        $result = $conn->query($sql);
        $row = mysqli_fetch_array($result, MYSQLI_NUM);
        if ($result->num_rows > 0)
        {
            $fd[$i]['xValue'] = "flightdata";
            $fd[$i]['yValue'] = $row[0] . "|" . $row[1] . "|" . $row[2] . "|" . $row[3] . "|" . $row[4] . "|" . $row[5] . "|" . $row[6] . "|" . $flightnumber;
        }
        else
        {
            $fd[$i]['xValue'] = "flightdata";
            $fd[$i]['yValue'] = "no data";
        }
    }
    $resp = json_encode($fd);
    $conn->close();
}
else
{
    $resp = "0 results";
}

/**************************************************************************************************
**
**	The last step is to send the data back to the client. 
**
***************************************************************************************************/

header("Expires: 0");
header("Last-Modified: " . gmdate("D, d m Y H:i:s") . " GMT");
header("Cache-Control: no-store, no-cache, must-revalidate");
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache");
header('Content-Type: text/plain');
print ($resp);
?>