<?php 
	
class ICSTourData extends CodonData {
		
	public static function getSettingValue($setting) {
		$sql = "SELECT * FROM `icrewsystems_tour_settings` WHERE `id` = 1";
		$result = DB::get_row($sql);
		
		foreach ($result as $result => $key) {
			if($result == $setting) {
				return $key;
			}
		}
	}
	
	public static function version() {
		return self::getSettingValue('version');
	}
	
	public static function moduleStatus() {
		return self::getSettingValue('module_status');
	}
	
	public static function getRandomTour() {
		return DB::get_row("SELECT * FROM `icrewsystems_tour` WHERE `status` = '1' ORDER BY RAND() LIMIT 1");
	}
	public static function getTourProgress($pilotid, $tourid) {
		return DB::get_row("SELECT * FROM `icrewsystems_tour_participation` WHERE `pilotid` = '$pilotid' AND `tourid` = '$tourid'");
	}
	
	public static function isParticipating($pilotid, $tourid) {
		$result = DB::get_row("SELECT * FROM `icrewsystems_tour_participation` WHERE `pilotid` = '$pilotid' AND `tourid` = '$tourid'");
		if($result == '') {
			return false;
		} else {
			return true;
		}
	}	
	
	
	public static function isEligible($pilotid, $tourid) {
		$pilot = PilotData::getPilotData($pilotid);
		$pilotrank = $pilot->rankid;
		
		$tour = self::getTour($tourid);
		$minrank = $tour->minrank;
		
		if($pilotrank >= $minrank) {
			return true;
		} else {
			return false;
		}
	}
	
	public static function current_location() {
		$last_flight = PIREPData::getLastReports(Auth::$userinfo->pilotid, 1, PIREP_ACCEPTED);
		if(!$last_flight) {
			$current_location = Auth::$userinfo->hub;
		} else {
			$current_location = $last_flight->arricao;
		}
		return OperationsData::getAirportInfo($current_location);
	}
	
	public static function getAircraftbyICAO($icao) {
		$sql = "SELECT id, registration, location, fullname FROM " . TABLE_PREFIX . "aircraft WHERE `icao` = '$icao'";
		return DB::get_results($sql);
	}
	
	public static function pilotParticipatingTours($pilotid) {
		return DB::get_results("SELECT * FROM `icrewsystems_tour_participation` WHERE `pilotid` = '$pilotid'");
	}
	
	public static function getTourParticipants($tourid) {
		return DB::get_results("SELECT * FROM `icrewsystems_tour_participation` WHERE `tourid` = '$tourid'");
	}
	
	public static function getTourProgress_status($value) {
		if($value == 0) {
			$button = "<span class='label label-danger'>Not Started</span>";
		} 
		else if($value == 1) {
			$button = "<span class='label label-info'>In Tour</span>";
		}
		 else if($value == 2){
			$button = "<span class='label label-success'>Tour Completed</span>";
		}
		 else if($value == 3){
			$button = "<span class='label label-success'>Tour Completed & Award Granted</span>";
		} else {
			$button = $value;
		}
		
		return $button;
	}
	
	public static function getTourLegBy_legid($legid) {
		$sql = "SELECT * FROM `icrewsystems_tour_legs` WHERE `legid` = '$legid'";
		return DB::get_row($sql);
	}
	
	public static function getFutureTours() {
		$sql = "SELECT * FROM `icrewsystems_tour` WHERE `startdate` >= CURRENT_DATE";
		return DB::get_results($sql);
	}
	
	public static function getPastTours() {
		$sql = "SELECT * FROM `icrewsystems_tour` WHERE `enddate` < CURRENT_DATE";
		return DB::get_results($sql);
	}
	
	
	public static function getAllTours($current = 0) {
		if($current == 0) {
			$sql = "SELECT * FROM `icrewsystems_tour` WHERE `enddate` > CURRENT_DATE";
		} else {
			$sql = "SELECT * FROM `icrewsystems_tour`";	
		}
		
		return DB::get_results($sql);
	}
	
	public static function getActiveTours($current = 0) {
		if($current == 0) {
			$sql = "SELECT * FROM `icrewsystems_tour` WHERE `enddate` > CURRENT_DATE AND `status` = '1'";
		} else {
			$sql = "SELECT * FROM `icrewsystems_tour` AND `status` = '1'";	
		}
		
		return DB::get_results($sql);
	}
	
	public static function getTour($id) {
		$sql = "SELECT * FROM `icrewsystems_tour` WHERE `id` = '$id'";
		return DB::get_row($sql);
	}
	
	public static function getTourLegs($tourid) {
		$sql = "SELECT * FROM `icrewsystems_tour_legs` WHERE `tourid` = '$tourid' ORDER BY `legid` ASC";
		return DB::get_results($sql);
	}
	
	public static function getTourLegs_progress($tourid, $legid) {
///////////////////////////////////////////////////////////////////////////////////////////////
		// ADD PILOTID to avoid confusion.
		$sql = "SELECT * FROM `icrewsystems_tour_legs` WHERE `tourid` = '$tourid' AND `legid` = '$legid'";
		return DB::get_row($sql);
	}
	
	
	
	public static function getTourStatus($value) {
		if($value == 1) {
			$button = "<span class='badge badge-success'>Active</span>";
		}
		 else {
			$button = "<span class='badge badge-danger'>Inactive</span>";
		}
		
		return $button;
	}
	
	public static function getTourStatus_frontend($value) {
		if($value == 1) {
			$button = "<span class='label label-success'>Active</span>";
		}
		 else {
			$button = "<span class='label label-danger'>Inactive</span>";
		}
		
		return $button;
	}
	
	public static function TourisBetween($tourid, $totallegs) {
		$sql = "SELECT * FROM `icrewsystems_tour_legs` WHERE `tourid` = '$tourid' and `legid` = '1'";
		$start = DB::get_row($sql);
		
		$sql = "SELECT * FROM `icrewsystems_tour_legs` WHERE `tourid` = '$tourid' and `legid` = '$totallegs'";
		$end = DB::get_row($sql);
		
		return $start->depicao.' <i class="fa fa-plane"></i> '.$end->arricao;
	}
	
	public static function CreateTour($title, $poster, $description, $startdate, $enddate, $airline, $airframes, $total_legs, $minrank, $status, $award_id) {
		  
		$title = DB::escape($title);
		$poster = DB::escape($poster);
		$description = DB::escape($description);
		$startdate = DB::escape($startdate);
		$enddate = DB::escape($enddate);
		$airline = DB::escape($airline);
		$airframes = DB::escape($airframes);
		$total_legs = DB::escape($total_legs);
		$minrank = DB::escape($minrank);
		$status = DB::escape($status);
		$award_id = DB::escape($award_id);
		$sql = "INSERT INTO `icrewsystems_tour` (`title`, `poster`, `description`, `startdate`,
		`enddate`, `airline`, `airframes`, `total_legs`, `minrank`, `status`, `award_id`)
		VALUES ('$title','$poster','$description','$startdate','$enddate','$airline','$airframes','$total_legs','$minrank', '$status', '$award_id')";
		
		
		$ret = DB::query($sql);
		return DB::$insert_id;
	}
	
	public static function UpdateTour($id, $title, $poster, $description, $startdate, $enddate, $airline, $airframes, $total_legs, $minrank, $status, $award_id) {
		
		$id = DB::escape($id);
		$title = DB::escape($title);
		$poster = DB::escape($poster);
		$description = DB::escape($description);
		$startdate = DB::escape($startdate);
		$enddate = DB::escape($enddate);
		$airline = DB::escape($airline);
		$airframes = DB::escape($airframes);
		$total_legs = DB::escape($total_legs);
		$minrank = DB::escape($minrank);
		$status = DB::escape($status);
		$award_id = DB::escape($award_id);
		
		
		$sql = "UPDATE `icrewsystems_tour` SET `award_id` = '$award_id', `title` = '$title',`poster` = '$poster',`description` = '$description',`startdate` = '$startdate',`enddate` = '$enddate',`airline` = '$airline',`airframes` = '$airframes',`total_legs` = '$total_legs',`minrank` = '$minrank',`status` = '$status'
		WHERE	`id` = '$id'";
		return DB::query($sql);
		 
	}
	
	public static function DeleteTour($tourid = 0) {
		if($tourid == 0) {
			return false;
		}
		// Delete Tour 
		$sql = "DELETE FROM `icrewsystems_tour` WHERE `id` = '$tourid'";
		DB::query($sql);
		
		// Delete Tour Legs 
		$sql = "DELETE FROM `icrewsystems_tour_legs` WHERE `tourid` = '$tourid'";
		DB::query($sql);
		
		// Delete Tour Pilots  
		$sql = "DELETE FROM `icrewsystems_tour_participation` WHERE `tourid` = '$tourid'";
		DB::query($sql);
		
		// Delete Signups
		
		return true;
	}
	
	public static function DeletePilot($tourid, $pilotid) {
		
	$sql="DELETE FROM icrewsystems_tour_participation WHERE pilotid = '$pilotid' AND tourid ='$tourid'";
    return DB::query($sql);
	}
	
	
	public static function getLeg($id) {
		$sql = "SELECT * FROM `icrewsystems_tour_legs` WHERE `id` = '$id'";
		return DB::get_row($sql);
	}
	
	public static function addLeg($tourid, $legid, $depicao, $arricao) {
		$tourid = DB::escape($tourid);  
		$legid = DB::escape($legid);  
		$depicao = DB::escape($depicao);  
		$arricao = DB::escape($arricao); 
		
		$sql = "INSERT INTO `icrewsystems_tour_legs` (`id`, `tourid`, `legid`, `depicao`, `arricao`) 
		VALUES (NULL, '$tourid', '$legid', '$depicao', '$arricao')";
		$ret = DB::query($sql);
		return $ret;
	}
	
	public static function updateLeg($id, $tourid, $legid, $depicao, $arricao) {
		$id = DB::escape($id);
		$tourid = DB::escape($tourid);  
		$legid = DB::escape($legid);  
		$depicao = DB::escape($depicao);  
		$arricao = DB::escape($arricao); 
		
		$sql = "UPDATE `icrewsystems_tour_legs` SET `tourid` = '$tourid', `legid` = '$legid', `depicao` = '$depicao', `arricao` = '$arricao' WHERE `id` = '$id'";
		DB::query($sql);
	}
	
	public static function deleteLeg() {
		
	}
	
	
	public static function makeFlightEntry($pilotid, $tourid, $legid, $flightnum, $depicao, $arricao) {
		$pilotid = DB::escape($pilotid);  
		$tourid = DB::escape($tourid);  
		$legid = DB::escape($legid);  
		$depicao = DB::escape($depicao);  
		$arricao = DB::escape($arricao); 
		$flightnum = DB::escape($flightnum); 
		
		$sql = "INSERT INTO `icrewsystems_tour_flights` (`id`, `pilotid`, `tourid`, `legid`, `flightnum`, `depicao`, `arricao`) 
		VALUES (NULL, '$pilotid', '$tourid', '$legid', '$flightnum', '$depicao', '$arricao')";
		$ret = DB::query($sql);
		return $ret;
	}
	
	public static function checkFlightEntry($tourid, $pilotid = '') {
		if($pilotid == '') {
			$pilotid = Auth::$userinfo->pilotid;
		}
		
		return DB::get_row("SELECT * FROM `icrewsystems_tour_flights` WHERE `tourid` = '$tourid' AND `pilotid` = '$pilotid'");
	}
	
	public static function getFlightEntry($flightnum) {
		return DB::get_row("SELECT * FROM `icrewsystems_tour_flights` WHERE `flightnum` = '$flightnum'");
	}
	public static function deleteFlightEntry($pilotid, $tourid, $legid) {
		
		$esql = "SELECT * FROM `icrewsystems_tour_flights` WHERE `pilotid` = '$pilotid' AND `tourid` = '$tourid' AND `legid` = '$legid'";
		$entry = DB::get_row($esql);
		if($entry == '') {
			return false;
		} else {
			$id = $entry->id;
			$sql = "DELETE FROM `icrewsystems_tour_flights` WHERE `id` = '$id'";
			DB::query($sql);
			return true;
		}
	}
	
	public static function updateTourPosition($pilotid, $tourid, $depicao, $arricao) {
		
		$tour = self::getTour($tourid);
		$tour_id = $tour->id;
		$tour_total_legs = $tour->total_legs;
		
		//We're doing this because, we don't want to update someone else's progress.
		$pos_sql = "SELECT * FROM `icrewsystems_tour_participation` WHERE `pilotid` = '$pilotid' AND `tourid` = '$tour_id' AND `current_location` = '$depicao'";
		$position = DB::get_row($pos_sql);
			
		if($position == '') {
			//Either the pilot has wrongly flown the leg, or there was some mishap during the tour.
			return "Was unable to locate leg in tour participation table...Pilot might have flown the wrong leg. Tour progress not updated";
		}
		
		$positionid = $position->id;		
		// Current Leg 
		//If the last leg = total legs
		if($position->current_leg != $tour_total_legs) {
			// NOT LAST LEG
			$next_leg = $position->current_leg + 1;
			
			$sql = "UPDATE `icrewsystems_tour_participation` SET `current_leg` = '$next_leg', `current_location` = '$arricao', `status` = '1' WHERE `id` = '$positionid'";
			$ret = DB::query($sql);
		} else {
			// If last, change tour status	
			$next_leg = $position->current_leg + 1;
			$sql = "UPDATE `icrewsystems_tour_participation` SET `current_leg` = '$next_leg', `current_location` = '$arricao', `status` = '2', `ended_on` = CURRENT_TIMESTAMP WHERE `id` = '$positionid'";
			$ret = DB::query($sql);
			
			//Check if pilot already has the award.
			$pilotid = $pilotid;
			$awardid = $tour->award_id;
			if($awardid != 0 || $awardid != null) {
				if(!AwardsData::GetPilotAward($pilotid, $awardid)) {
					AwardsData::AddAwardToPilot($pilotid, $awardid);
				} 
			}
			
			//Send Mail for completing tour!
			$pilotid = PilotData::getPilotData($pilotid);
			$email = Template::GetTemplate('ICSTour/emails/tourCompleted.php', true);
			Util::SendEmail($pilot->email, '[iCrew] '.SITE_NAME.' | Yay! You have completed '.$tour->title.'', $email);
		}
		return $sql;
	}
	
	
	public static function participate($pilotid, $tour) {
		
		//Get tour
		$legs = DB::get_results("SELECT * FROM `icrewsystems_tour_legs` WHERE `tourid` = '$tour->id' ORDER BY `legid` ASC");
    	$first_leg = $legs[0];
    	
		$icao =	$first_leg->depicao;
    	$legid = $first_leg->legid;
		
		// Get first leg ICAO
		$tourid = $tour->id;
		$total_legs = $tour->total_legs;
		$sql = "INSERT INTO `icrewsystems_tour_participation` 
				(`pilotid`, `tourid`, `current_leg`, `current_location`, `total_legs`, `status`) 
		VALUES ('$pilotid', '$tourid', '$legid', '$icao', '$total_legs', '0')";
		$ret = DB::query($sql);
		return $ret;
	}
	
	public static function addSchedule($data) {

         if (!is_array($data)) return "Data is not an array";
         
		$route = SchedulesData::findSchedules(array('s.code' => $data['code'], 's.flightnum'=> $data['flightnum']));
        if(count($route)) {
        	return true;
        }
        
        $data['code'] = strtoupper($data['code']);
        $data['flightnum'] = strtoupper($data['flightnum']);
        $data['deptime'] = strtoupper($data['deptime']);
        $data['arrtime'] = strtoupper($data['arrtime']);
        $data['depicao'] = strtoupper($data['depicao']);
        $data['arricao'] = strtoupper($data['arricao']);

		
		$deptime = explode(":", $data['deptime']);
		$data['deptime'] = $deptime[0].'.'.$deptime[1];
		
		$arrtime = explode(":", $data['arrtime']);
		$data['arrtime'] = $arrtime[0].'.'.$arrtime[1];
		
		
        if ($data['enabled'] == true) $data['enabled'] = 1;
        else  $data['enabled'] = 0;

        # If they didn't specify
        $data['flighttype'] = strtoupper($data['flighttype']);
        if ($data['flighttype'] == '') $data['flighttype'] = 'P';

        $data['flightlevel'] = str_replace(',', '', $data['flightlevel']);

        if(isset($data['week1'])) {

        }

        if (isset($fields['route'])) {
            $fields['route'] = str_replace('SID', '', $fields['route']);
            $fields['route'] = str_replace('STAR', '', $fields['route']);
            $fields['route'] = str_replace('DCT', '', $fields['route']);
            $fields['route'] = trim($fields['route']);
            $fields['route_details'] = '';
        }

        foreach ($data as $key => $value) {
            $data[$key] = DB::escape($value);
        }

        $data['flighttime'] = str_replace(':', '.', $data['flighttime']);


        # Do the insert based on the columns here
        $cols = array();
        $col_values = array();

        foreach ($data as $key => $value) {

            if($key == 'daysofweek' || $key == 'week1' || $key == 'week2' || $key == 'week3' || $key == 'week4') {
                $value = str_replace('7', '0', $value);
            } else {
                $value = DB::escape($value);
            }


            $cols[] = "`{$key}`";
            $col_values[] = "'{$value}'";
        }

        $cols = implode(', ', $cols);
        $col_values = implode(', ', $col_values);
        $sql = 'INSERT INTO ' . TABLE_PREFIX . "schedules ({$cols}) VALUES ({$col_values});";
        $res = DB::query($sql);

        if (DB::errno() != 0) return false;
		
        return true;
    }
}
?>