David Zhong / realops-website

<?php 

class db
    {
        public $isConnected;
        protected $datab;
        public function __construct($username, $password, $host, $dbname, $options=array()){
            $this->isConnected = true;
            try { 
                $this->datab = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8", $username, $password, $options); 
                $this->datab->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
                $this->datab->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
            } 
            catch(PDOException $e) { 
                $this->isConnected = false;
                throw new Exception($e->getMessage());
            }
        }
        public function Disconnect(){
            $this->datab = null;
            $this->isConnected = false;
        }
        public function getRow($query, $params=array()){
            try{ 
                $stmt = $this->datab->prepare($query); 
                $stmt->execute($params);
                return $stmt->fetch();  
                }catch(PDOException $e){
                throw new Exception($e->getMessage());
            }
        }
        public function getRows($query, $params=array()){
            try{ 
                $stmt = $this->datab->prepare($query); 
                $stmt->execute($params);
                return $stmt->fetchAll();       
                }catch(PDOException $e){
                throw new Exception($e->getMessage());
            }       
        }
        public function insertRow($query, $params){
            try{ 
                $stmt = $this->datab->prepare($query); 
                $stmt->execute($params);
                }catch(PDOException $e){
                throw new Exception($e->getMessage());
            }           
        }
        public function updateRow($query, $params){
            return $this->insertRow($query, $params);
        }
        public function deleteRow($query, $params){
            return $this->insertRow($query, $params);
        }
    public static function connect()
    {

            require_once $_SERVER['DOCUMENT_ROOT'] . '/config.php';

        $db = new db(SQL_USER, SQL_PASSWORD, SQL_SERVER, SQL_DATABASE, array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));
        return $db;
    }
    }
    //USAGE 
    /*      
        Connecting to DataBase
        $database = new db("root", "", "localhost", "database", array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));

        Getting row
        $getrow = $database->getRow("SELECT email, username FROM users WHERE username =?", array("yusaf"));

        Getting multiple rows
        $getrows = $database->getRows("SELECT id, username FROM users");

        inserting a row
        $insertrow = $database ->insertRow("INSERT INTO users (username, email) VALUES (?, ?)", array("yusaf", "[email protected]"));

        updating existing row           
        $updaterow = $database->updateRow("UPDATE users SET username = ?, email = ? WHERE id = ?", array("yusafk", "[email protected]", "1"));

        delete a row
        $deleterow = $database->deleteRow("DELETE FROM users WHERE id = ?", array("1"));
        disconnecting from database
        $database->Disconnect();

        checking if database is connected
        if($database->isConnected){
        echo "you are connected to the database";
        }else{
        echo "you are not connected to the database";
        }

    */  

class Get
{



    public function aircraft($icao)
    {
        $database = db::connect();
        $getrow = $database->getRow("SELECT name FROM tbl_aircraft WHERE icao =?", array($icao));
        return $getrow;
    }

    public function airport($icao)
    {
        $database = db::connect();
        $getrow = $database->getRow("SELECT name FROM tbl_airports WHERE icao =?", array($icao));
        return $getrow;
    }

    public function flight($id)
    {
        $db = db::connect();
        $getRow = $db->getRow("SELECT * FROM tbl_schedules WHERE id =?", array($id));
        return $getRow;
    }

    public function booking($id)
    {
        $db = db::connect();
        $getRow = $db->getRow("SELECT * FROM tbl_bookings WHERE id =?", array($id));
        return $getRow;
    }

    public function extendedBooking($id)
    {
        $db = db::connect();
        $row = $db->getRow("SELECT * FROM tbl_bookings WHERE id = ?", array($id));
            $getRow2 = $db->getRow("SELECT * FROM tbl_schedules WHERE id = ?", array($row['flight']));
            $getRow2['name'] = $this->airport($row['icao']);
            $getRow2['pilotVatsimID'] = $row['pilot'];
            $getRow2['blockOut'] = $row['blockOut'];
            $getRow2['blockOff'] = $row['blockOff'];
            $getRow2['blockOn'] = $row['blockOn'];
            $getRow2['blockIn'] = $row['blockIn'];
            $getRow2['bookingID'] = $row['id'];
            $getRow3 = $db->getRow("SELECT * FROM tbl_pilots WHERE vatsimID = ?", array($row['pilot']));
            $getRow2['pilotName'] = $getRow3['firstName'] . " " . $getRow3['lastName'];
            if($this->va($row['va']) == "0")
            {
                $getRow2['va']['name'] = "No Virtual Airline";
                $getRow2['va']['id'] = 0;
            }
            else
                $getRow2['va'] = $this->va($row['va']);
            $getRow2;

        return $getRow2;
    }

    public function airline($id)
    {
        $db = db::connect();
        $getRow = $db->getRow("SELECT * FROM tbl_airlines WHERE icao =?", array($id));
        return $getRow['name'];
    }

    public function bookingFromFlightID($id)
    {
        $db = db::connect();
        $getRow = $db->getRow("SELECT * FROM tbl_bookings WHERE flight =?", array($id));
        return $getRow;
    }

    public function schedules($get)
        {
            $database = db::connect();
            if(empty($get['deparr']))
            {
                $get['deparr'] = 0;
            }
            if(empty($get['domint']))
            {
                $get['domint'] = 0;
            }
            $valArray = array($get['deparr'], $get['domint']);
            $queryBuild = "";
            if(!empty($get['airport']))
            {
                $queryBuild .= " AND icao =?";
                $valArray[] = $get['airport'];
            }
            if(!empty($get['airline']))
            {
                $queryBuild .= " AND airline =?";
                $valArray[] = $get['airline'];
            }
            if(!empty($get['aircraft']))
            {
                $queryBuild .= " AND aircraft =?";
                $valArray[] = $get['aircraft'];
            }
            $getrow = $database->getRows("SELECT * FROM tbl_schedules WHERE deparr =? AND intdom =?" . $queryBuild, $valArray);
            $bookings = $database->getRows("SELECT * FROM tbl_bookings");
            $i = 0;
            foreach($getrow as $row)
            {
                if(!empty($get['timeFrame']))
                {
                    if(substr($row['time'], 0, 2) != $get['timeFrame'])
                    {
                        unset($getrow[$i]);
                    $i++;
                    continue;
                    }
                }
                    foreach($bookings as $row2)
                    {
                        if($row['id'] == $row2['flight'])
                            unset($getrow[$i]);
                    }
                    $i++;
            }
            return $getrow;
        }

    public function pilot($id)
    {
        $db = db::connect();
        $getRow = $db->getRow("SELECT * FROM tbl_pilots WHERE id =?", array($id));
        return $getRow;
    }

    public function va($id)
    {
        $db = db::connect();
        $getRow = $db->getRow("SELECT * FROM tbl_vas WHERE id =?", array($id));
        return $getRow;
    }

    public function scheduleAirports()
    {
        $database = db::connect();
        $getrow = $database->getRows("SELECT * FROM tbl_schedules");
        $list = array();
        foreach($getrow as $x)
        {
            $getrow2 = $database->getRow("SELECT name, icao FROM tbl_airports WHERE icao =?", array($x['icao']));
            $list[] = $x['icao'] . "." .$getrow2['name'];
        }
        $list = array_unique($list);
        return $list;
    }

    public function scheduleAircraft()
    {
        $database = db::connect();
        $getrow = $database->getRows("SELECT aircraft FROM tbl_schedules", array());
        $list = array();
        foreach($getrow as $x)
        {

            $getrow2 = $database->getRow("SELECT name FROM tbl_aircraft WHERE icao =?", array($x['aircraft']));
            if(in_array($x['aircraft'] . "." . $getrow2['name'], $list))
                continue;
            else
                $list[] = $x['aircraft'] . "." . $getrow2['name'];
        }
        return $list;
    }

    public function scheduleAirline()
    {
        $database = db::connect();
        $getrow = $database->getRows("SELECT airline FROM tbl_schedules", array());
        $list = array();
        foreach($getrow as $x)
        {

            if(in_array($x['airline'], $list))
                continue;
            else
                $list[] = $x['airline'];
        }
        return $list;
    }
    public function myFlights($vatsimID)
    {
        $database = db::connect();
        $getRows = $database->getRows("SELECT * FROM tbl_bookings WHERE pilot =?", array($vatsimID));
        if(!empty($getRows))
        {
            foreach($getRows as $row)
            {
                $getFlight = $database->getRow("SELECT * FROM tbl_schedules WHERE id =?", array($row['flight']));
                $flight = $getRows['flight'];
                $getFlight['bookingID'] = $row['id'];
                $getFlight['va'] = $row['va'];
                $returnArray[] = $getFlight;
            }

            return $returnArray;
            
        }
        else
            return $getRows;
    }

    public function gate($callsign)
    {
        $stripper = ['<', '>', '/', 'd'];
        if(substr($callsign, 0, 2) == "QF" || substr($callsign, 0, 2) == "qf")
        {
            $site = file_get_contents("http://flightaware.com/live/flight/".$callsign);
            $pos = strpos($site, "Gate");

            echo str_replace($stripper, "", substr($site, $pos + 4, 5));
        }

        else
        {
        $site = file_get_contents("http://www.sydneyairport.com.au/flights/flight-arrivals-and-departures/airport-visit-planner/melbourne/" . $callsign ."/0");
        $pos = strpos($site, "Gate:");
        if(substr($site, $pos + 40, 3) == "ML ")
        {
            echo "Not available";

        }
        else
        {
            echo str_replace($stripper, "", substr($site, $pos + 40, 3));
        }
        }
    }

    public function vas()
    {
        $db = db::connect();
        $getRows = $db->getRows("SELECT * FROM tbl_vas WHERE approved = 1");
        return $getRows;
    }

    public function totalFlightsForVA($id)
    {
        $db = db::connect();
        $getRows = $db->getRows("SELECT COUNT(*) FROM tbl_bookings WHERE va = ?", array($id));
        return $getRows[0]["COUNT(*)"];
    }

    public function getVAFromID($id)
    {
        $db = db::connect();
        $getRow = $db->getRow("SELECT * FROM tbl_vas WHERE id =?", array($id));
        return $getRow;
    }

    public function leadingVA()
    {
        $db = db::connect();
        $getRow = $db->getRow("SELECT va, COUNT(`va`) AS value_occurrence FROM tbl_bookings GROUP BY va ORDER BY value_occurrence DESC LIMIT 1;");
        $vaDetails = $this->getVAFromID($getRow['va']);
        $getRow = array_merge($getRow, $vaDetails);
        return $getRow;
    }

    public function bookings()
    {
        $db = db::connect();
        $getRow = $db->getRows("SELECT * FROM tbl_bookings");
        $array = array();
        foreach($getRow as $row)
        {
            $getRow2 = $db->getRow("SELECT * FROM tbl_schedules WHERE id = ?", array($row['flight']));
            $getRow2['name'] = $this->airport($row['icao']);
            $getRow2['pilotVatsimID'] = $row['pilot'];
            $getRow2['bookingID'] = $row['id'];
            $getRow3 = $db->getRow("SELECT * FROM tbl_pilots WHERE vatsimID = ?", array($row['pilot']));
            $getRow2['pilotName'] = $getRow3['firstName'] . " " . $getRow3['lastName'];
            if($this->va($row['va']) == "0")
            {
                $getRow2['va']['name'] = "No Virtual Airline";
                $getRow2['va']['id'] = 0;
            }
            else
                $getRow2['va'] = $this->va($row['va']);
            $a[] = $getRow2;
        }
        function compareByName($a, $b) {
            return strcmp($a["time"], $b["time"]);
        }
        usort($a, 'compareByName');
        /* The next line is used for debugging, comment or delete it after testing */
        return $a;
    }

    public function userExists($username)
    {
        $db = db::connect();
        $getRow = $db->getRow("SELECT * FROM tbl_pilots WHERE vatsimID = ?", array($username));
        if(!empty($getRow))
        {
            return true;
        }
        else return false;
    }

    public function isATC($vatsimID)
    {
        $db = db::connect();
        $getRow = $db->getRow("SELECT * FROM tbl_atc WHERE vatsimID = ?", array($vatsimID));
        if(!empty($getRow))
        {
            return true;
        }
        else return false;
    }

    public function metar($location)
    {
        $fileName = "http://weather.noaa.gov/pub/data/observations/metar/stations/".$location.".TXT";
        $metar = '';
        $fileData = @file($fileName);
        if ($fileData != false) {
        list($i, $date) = each($fileData);

        $utc = strtotime(trim($date));
        $time = date("D, F jS Y g:i A",$utc);

        while (list($i, $line) = each($fileData)) {
        $metar .= ' ' . trim($line);
        }
        $metar = trim(str_replace('  ', ' ', $metar));
            return $metar;
        }
    }

    public function getFuelPlanXML($email, $account, $license, $orig, $dest, $eqpt)
    {

        $postdata["QUERY"]  = "FUEL";
        $postdata["USER"]   = $email;
        $postdata["ACCOUNT"]= $account;
        $postdata["LICENSE"]= $license;
        $postdata["ORIG"]   = $orig;
        $postdata["DEST"]   = $dest;
        $postdata["EQPT"]   = $eqpt;
        $postdata["UNITS"] = "METRIC";

        //generates a URL-encoded query string from the array.
        $postdata = http_build_query($postdata);

        $url = "http://fuelplanner.com/api/fuelapi.php";

        if ( function_exists( 'curl_init' ) ){
            $ch = curl_init();

            curl_setopt ($ch,CURLOPT_POST, true);
            curl_setopt ($ch,CURLOPT_URL, $url );
            curl_setopt ($ch,CURLOPT_POSTFIELDS, $postdata);
            curl_setopt ($ch,CURLOPT_RETURNTRANSFER, 1 );
            curl_setopt ($ch,CURLOPT_CONNECTTIMEOUT, 5 );

            $content = curl_exec( $ch );
            curl_close( $ch );
        }else{
            // see also http://www.php-mysql-tutorial.com/php-tutorial/php-read-remote-file.php
            die( 'No method to read from remote server was found.' );
        }

        return $content;
    }

    public function load($eqpt, $orig, $dest)
    {


        //An example of how to use PHP and curl to access the API


        $email   = "[email protected]";
        $account = "87DC48D3";
        $license = "0b04cbc2e34f23acf927ba244f19f1b6";

        $xmldata = $this->getFuelPlanXML($email, $account, $license, $orig, $dest, $eqpt);


        //Simple method to convert xml to an array
        $xml = simplexml_load_string($xmldata);
        $json = json_encode($xml);
        $xmlary = json_decode($json,TRUE);


        //Show keys and values of array
        //Note that any
        $ark = array_keys($xmlary);

        $return = new stdClass();
        $return->aircraft = $xmlary['DESCRIP'];
        $return->distance = $xmlary['NM'];
        $return->operatingEmptyWeight = $xmlary['OEW'];
        $return->TTL = $xmlary['TTL'];
        $return->zeroFuelWeight = $xmlary['ZFW'];
        $return->estimatedFuelUsage = $xmlary['FUEL_EFU'];
        $return->reserveFuel = $xmlary['FUEL_RSV'];
        $return->takeOffFuel = $xmlary['FUEL_TOF'];
        $return->takeOffWeight = $xmlary['TOW'];
        $return->underLoad = $xmlary['UNDERLOAD'];
        $return->landingWeight = $xmlary['LWT'];
        $return->blockTime = $xmlary['TIME_BLK'];
        $return->reserveTime = $xmlary['TIME_RSV'];
        $return->totalEndurance = $xmlary['TIME_TTE'];
        $return->flightFuel = strtotime("-15 minutes", strtotime($return->blockTime));
        $return->flightFuel = date("h.i", $return->flightFuel);


        return $return;

    }



}

Class Stats
{
    public function totalFlights()
    {
        $db = db::connect();
        $getRow = $db->getRow("SELECT COUNT(*) FROM tbl_schedules");
        return $getRow['COUNT(*)'];
    }

    public function totalBookings()
    {
        $db = db::connect();
        $getRow = $db->getRow("SELECT COUNT(*) FROM tbl_bookings");
        return $getRow['COUNT(*)'];
    }

    public function totalPilots()
    {
        $db = db::connect();
        $getRow = $db->getRow("SELECT COUNT(*) FROM tbl_pilots");
        return $getRow['COUNT(*)'];
    }

    public function totalVAS()
    {
        $db = db::connect();
        $getRow = $db->getRow("SELECT COUNT(*) FROM tbl_vas");
        return $getRow['COUNT(*)'];
    }

    public function totalVAsWithFlights()
    {
        $db = db::connect();
        $getRow = $db->getRow("SELECT COUNT(*) FROM tbl_bookings WHERE va != 0");
        return $getRow['COUNT(*)'];
    }

}

Class Set
{
    public function setUser($vatsimID, $email, $firstName, $lastName)
    {
        $db = db::connect();
        $getRow = $db->insertRow("INSERT INTO tbl_pilots (vatsimID, firstName, lastName, email) VALUES (?, ?, ?, ?)", array($vatsimID, $firstName, $lastName, $email));
        return true;
    }

    public function atcApplication($vatsimID, $firstName, $lastName, $email, $rating, $position1, $position2, $position3, $shifts)
    {
        $db = db::connect();
        $getRow = $db->insertRow("INSERT INTO tbl_atc (vatsimID, firstName, lastName, email, rating, position1, position2, position3, shifts) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", array($vatsimID, $firstName, $lastName, $email, $rating, $position1, $position2, $position3, $shifts));
        return true;
    }

    public function sendMail($name, $email, $reason, $messageContent)
    {
        $headers = 'From: [email protected]' . "\r\n" .
            'Reply-To: ' . $email . "\r\n" .
            'X-Mailer: PHP/' . phpversion();


// Create a message
        $finalMessageContent = "Name: " . $name . " \n
        Reason: " . $reason . " \n" .
        "Email: " . $email . " \n" .
        "Message: " . $messageContent;

        mail("[email protected]", "New Contact Message", $finalMessageContent, $headers);

        return true;
    }

}

Class Admin
{
    public function vas()
    {
        $db = db::connect();
        $getRows = $db->getRows("SELECT * FROM tbl_vas");
        return $getRows;
    }

    public function atc()
    {
        $db = db::connect();
        $getRows = $db->getRows("SELECT * FROM tbl_atc");
        return $getRows;
    }

    public function pilots()
    {
        $db = db::connect();
        $getRows = $db->getRows("SELECT * FROM tbl_pilots");
        return $getRows;
    }

    public function checkStatus($vatsimID)
    {
        $db = db::connect();
        $getRows = $db->getRow("SELECT admin FROM tbl_pilots WHERE vatsimID = ?", array($vatsimID));
        if($getRows['admin'] == 0)
        {
            return false;
        }
        else if($getRows['admin'] == 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }

}

Class Dispatch
{
    public function getRemarks($icao, $va, $reg, $ac, $sel)
    {
        $dof = date("ymd");
        $orgn = "";
        if($icao == "VOZ")
        {
            $pbn = "A1B2C2D2O2S2";
            $nav = " NAV/RNP2";
            if($va == "No Virtual Airline")
            {
                $opr = " OPR/VIRGIN AUSTRALIA";
            }
            else
            {
                $opr = " OPR/" . strtoupper($va);
            }
            $rmk = " RMK/CALLSIGN VELOCITY";
        }
        else if($icao == "QFA")
        {
            $pbn = "A1B1C1D1O2S2T1";
            $nav = " NAV/RNP2";
            if($va == "No Virtual Airline")
            {
                $opr = " OPR/QANTAS";
            }
            else
            {
                $opr = " OPR/" . strtoupper($va);
            }
            $orgn = " ORGN/YSSYQFA0";
            $rmk = "";
        }
        else if($icao == "UAE")
        {
            $pbn = "A1B1C1D1L1O1S2T1";
            $nav = "";
            if($va == "No Virtual Airline")
            {
                $opr = " OPR/UAE";
            }
            else
            {
                $opr = " OPR/" . strtoupper($va);
            }
            $rmk = " RMK/NRP HAR TCAS ADSB";
        }
        else if($icao == "JST")
        {
            $pbn = "A1B1C1D1O2S1T1";
            $nav = "";
            if($va == "No Virtual Airline")
            {
                $opr = " OPR/JETSTAR";
            }
            else
            {
                $opr = " OPR/" . strtoupper($va);
            }
            $rmk = " RMK/TCAS EQUIPPED";
        }
        else if($icao == "QLK")
        {
            $pbn = "S1";
            $nav = "";
            if($va == "No Virtual Airline")
            {
                $opr = " OPR/QANTASLINK";
            }
            else
            {
                $opr = " OPR/" . strtoupper($va);
            }
            $rmk = "";
        }
        else if($icao == "TGW")
        {
            $pbn = "A1B2C2D2O2";
            $nav = " NAV/GPSRNAV";
            if($va == "No Virtual Airline")
            {
                $opr = " OPR/TIGERAIR";
            }
            else
            {
                $opr = " OPR/" . strtoupper($va);
            }
            $rmk = "";
        }
        else
        {
            $pbn = "A1B1C1D1L1O1S2T1";
            $nav = "";
            if($va == "No Virtual Airline")
            {
                $opr = " OPR/" . strtoupper($va);
            }
            $opr = $icao;
        }
        if($ac == "A388" || $ac == "B744" || $ac == "A332" || $ac == "A333" || $ac == "B763" || $ac == "B77W" || $ac == "B773" || $ac == "B772" || $ac == "B77L" || $ac == "A321")
            $per = "D";
        else
            $per = "C";
        if(!empty($reg))
        {
            $reg = " REG/" . $reg;
            $reg = str_replace("-", "", $reg);
        }
        if(!empty($sel))
        {
            $sel = " SEL/" . $sel;
            $sel = str_replace("-", "", $sel);
        }
        return "PBN/" . $pbn . $nav . " DOF/" . $dof . $reg . $sel . $opr . $orgn . " PER/" . $per . " " . $rmk;
    }

    public function getWeightCategory($ac)
    {
        if($ac == "A388")
            $cat = "J";
        else if($ac == "B744" || $ac == "A332" || $ac == "A333" || $ac == "B763" || $ac == "B77W" || $ac == "B773" || $ac == "B772" || $ac == "B77L" || $ac == "A321")
            $cat = "H";
        else
            $cat = "M";
        return $cat;
    }

    public function getRoute($aircraft, $dept, $dest)
    {
        $db = db::connect();
        if($aircraft == "DH8D" || $aircraft == "DH8C" || $aircraft == "SF34" || $aircraft == "AT72")
            $stmt = $db->getRow("SELECT * FROM tbl_routes WHERE acft = \"P\" AND dept = ? AND dest = ?", array($dept, $dest));
        else
            $stmt = $db->getRow("SELECT * FROM tbl_routes WHERE acft = \"J\" AND dept = ? AND dest = ?", array($dept, $dest));
        if(!empty($stmt))
            return $stmt['route'];
        else
            $stmt2 = $db->getRow("SELECT * FROM tbl_routes WHERE dept = ? AND dest = ?", array($dept, $dest));
        if(empty($stmt2))
            return "";
        else
            return $stmt2['route'];
    }
}