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'];
            $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'];
            $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;
    }



}

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)
    {
        $dof = date("ymd");
        if($icao == "VOZ")
        {
            $pbn = "";
            $nav = "";
            if($va == 0)
            {
                $opr = "VIRGIN AUSTRALIA";
            }
            else
            {
                $opr = $va;
            }
            $rmk = "RMK/CALLSIGN VELOCITY";
        }
        else if($icao == "QFA")
        {

        }
        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";
        return "PBN/" . $pbn . " NAV/" . $nav . " DOF/" . $dof . " REG/" . $reg . " OPR/" . $opr . " 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;
    }
}