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 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']);
            $array[] = $getRow2;
        }
        return $array;
    }

    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;
    }

}