<?php 

class Model_vehicles extends CI_Model {	
	
	public function getAllEngagedVehicles($zaciatok, $koniec){
		$sql='SELECT * FROM rezervacia
			  LEFT JOIN inventura ON inventura.ID_INV=rezervacia.ID_INV
			  LEFT JOIN vozidlo ON vozidlo.ID_VOZ=rezervacia.ID_VOZ
			  WHERE 
		  	  (inventura.Datum_konca>="' . $zaciatok . '" AND inventura.Datum_konca<="' . $koniec . '") OR
			  (inventura.Datum_zaciatku>="' . $zaciatok . '" AND inventura.Datum_zaciatku<="' . $koniec . '") OR
			  (inventura.Datum_zaciatku<="' . $zaciatok . '"AND inventura.Datum_konca>="' . $koniec . '")
			  GROUP BY rezervacia.ID_VOZ 
			  ';
		$query = $this->db->query($sql);
		if ($query->num_rows()==0) {
			return FALSE;
		} else {
			return $query->result();	
		}		
	}
	
	public function getIdAndTypeVehicle($id_voz){
		$sql = 'SELECT vozidlo.ID_VOZ, vozidlo.Typ_vozidla, vozidlo.Pocet_miest FROM vozidlo WHERE ID_VOZ=' .$id_voz .' ORDER BY vozidlo.Typ_vozidla';
		$query = $this->db->query($sql);
		return $query->row();
	}
	
	public function getIdTypeNumberVehicles(){
		$sql = 'SELECT vozidlo.ID_VOZ, vozidlo.Typ_vozidla, vozidlo.Pocet_miest FROM vozidlo  ORDER BY vozidlo.Typ_vozidla';
		$query = $this->db->query($sql);
		return $query->result();
	}
	
	public function getAllVehicles(){
		$sql='SELECT vozidlo.ID_VOZ FROM vozidlo';
		$query = $this->db->query($sql);
		return $query->result();
	}	
	
	public function getTypesOfVehicles(){
		$sql='SELECT Typ_vozidla FROM vozidlo GROUP BY Typ_vozidla';
		$query = $this->db->query($sql);
		return $query->result();
	} 
	
	public function createReservation($id_inv, $vehicle){				
		$sql = 'INSERT INTO `rezervacia` (`ID_INV`, `ID_VOZ`) 
				VALUES ("' . $id_inv . '","' . $vehicle . '")';
		$this->db->query($sql);
		return $this->db->insert_id();
	} 
	
	public function createReservationForLeadEmployee($id_rez){
		$sql = 'INSERT INTO cesty (ID_REZ) VALUES ('.$id_rez.')';
		$this->db->query($sql);
	}
	
	public function getNumberOfSeats($choseVehicle){
		$sql = 'SELECT Pocet_miest FROM vozidlo WHERE ID_VOZ='. $choseVehicle;
		$query = $this->db->query($sql);
		return $query->row()->Pocet_miest;
	}
	
	public function updateNumberOfEmployees($id_inv,$pocetZamestnancov){
		$sql = 'UPDATE inventura SET Pocet_zamestnancov=' . $pocetZamestnancov . ', status=1 WHERE ID_INV=' . $id_inv;
		$this->db->query($sql);
	}
	
	public function getCountOfVehicles($pocetMiest){
		$sql = 'SELECT COUNT(ID_VOZ) AS PocetMiest FROM vozidlo WHERE Pocet_miest=' .$pocetMiest;
		$query = $this->db->query($sql);
		return $query->row();
	}
	
	public function getStatusVehicle($id_voz){
		$sql = 'SELECT Status_vozidla FROM vozidlo WHERE ID_VOZ=' .$id_voz;
		$query = $this->db->query($sql);
		return $query->row();
	}
	
	public function getAllVehicles2(){
		$sql = 'SELECT * FROM vozidlo ORDER BY Typ_vozidla';
		$query = $this->db->query($sql);
		return $query->result();
	}
	
	public function getAllActiveVehicles(){
		$sql = 'SELECT * FROM vozidlo WHERE vozidlo.Status_vozidla=1 ORDER BY Typ_vozidla ';
		$query = $this->db->query($sql);
		return $query->result();
	}
	
	public function getAllUnactiveVehicles(){
		$sql = 'SELECT * FROM vozidlo WHERE vozidlo.Status_vozidla=0 ORDER BY Typ_vozidla';
		$query = $this->db->query($sql);
		return $query->result();
	}
	
	public function createNewVehicle($Nazov,$SPZ,$Pocet_miest,$Status_vozidla){
		if ($Pocet_miest==5){
			$Typ_vozidla=1;
		} elseif ($Pocet_miest==7){
			$Typ_vozidla=2;
		} elseif ($Pocet_miest==9){
			$Typ_vozidla=3;
		} else {
			$Typ_vozidla=4;
		}
		$sql = 'INSERT INTO `vozidlo` (`Nazov`, `SPZ`, `Pocet_miest`, `Status_vozidla`, `Typ_vozidla`) 
				VALUES ("' . $Nazov . '","' . $SPZ . '","' . $Pocet_miest . '","' . $Status_vozidla . '","' . $Typ_vozidla . '")';	
		$query = $this->db->query($sql);
	}
	
	public function getVehicleInfo($id_voz){
		$sql = 'SELECT * FROM vozidlo WHERE vozidlo.ID_VOZ='. $id_voz; 
		$query = $this->db->query($sql);
		return $query->row();	
	}
	
	public function getCountVehicleActiveInventories($id_voz){
		$sql='SELECT COUNT(ID_REZ) AS pocet FROM rezervacia
			  LEFT JOIN inventura ON rezervacia.ID_INV=inventura.ID_INV
			  WHERE inventura.Datum_zaciatku>NOW() AND ID_VOZ='. $id_voz;
			$query = $this->db->query($sql);
			return $query->row()->pocet;	
	}
	
	public function getCountVehicleArchiveInventories($id_voz){
		$sql='SELECT COUNT(ID_REZ) AS pocet FROM rezervacia
			  LEFT JOIN inventura ON rezervacia.ID_INV=inventura.ID_INV
			  WHERE inventura.Datum_zaciatku<=NOW() AND ID_VOZ='. $id_voz;
			$query = $this->db->query($sql);
			return $query->row()->pocet;	
	}
	
	public function fetchVehicleActiveInventories($id_voz, $limit, $offset){
		if ($offset==''){
			$offset=0;
		} 		
		 $sql = 'SELECT * FROM inventura 
		         RIGHT JOIN rezervacia ON inventura.ID_INV=rezervacia.ID_INV
				 LEFT JOIN zakaznik ON inventura.ID_Zakaznika=zakaznik.ID_Zakaznik
				 LEFT JOIN zamestnanec ON inventura.ID_Veduci=zamestnanec.ID_ZAM
		 		 WHERE (inventura.Datum_zaciatku>NOW()) AND (rezervacia.ID_VOZ='. $id_voz .')
				 ORDER BY inventura.Datum_zaciatku
				 LIMIT '. $limit .' OFFSET '. $offset; 
		 $query = $this->db->query($sql);
		 
		 if($query->num_rows()>0){
			 foreach($query->result() as $row) {
				 $data[] = $row;
			 }
			 return $data;
		 } else {
			 return false;	
		 }	
	}
	
	public function fetchVehicleArchiveInventories($id_voz,$limit,$offset){
		if ($offset==''){
			$offset=0;
		} 		
		 $sql = 'SELECT * FROM inventura 
		         RIGHT JOIN rezervacia ON inventura.ID_INV=rezervacia.ID_INV
				 LEFT JOIN zakaznik ON inventura.ID_Zakaznika=zakaznik.ID_Zakaznik
				 LEFT JOIN zamestnanec ON inventura.ID_Veduci=zamestnanec.ID_ZAM
		 		 WHERE (inventura.Datum_zaciatku<NOW()) AND (rezervacia.ID_VOZ='. $id_voz .')
				 ORDER BY inventura.Datum_konca
				 LIMIT '. $limit .' OFFSET '. $offset; 
		 $query = $this->db->query($sql);
		 
		 if($query->num_rows()>0){
			 foreach($query->result() as $row) {
				 $data[] = $row;
			 }
			 return $data;
		 } else {
			 return false;	
		 }	
	}
	
	public function getVehicleActiveInventories($id_voz){
		$sql = 'SELECT * FROM inventura 
		        RIGHT JOIN rezervacia ON inventura.ID_INV=rezervacia.ID_INV
				LEFT JOIN zakaznik ON inventura.ID_Zakaznika=zakaznik.ID_Zakaznik
				LEFT JOIN zamestnanec ON inventura.ID_Veduci=zamestnanec.ID_ZAM
		 		WHERE (inventura.Datum_zaciatku>NOW()) AND (rezervacia.ID_VOZ='. $id_voz .')
				ORDER BY inventura.Datum_konca';
		$query = $this->db->query($sql);
		return $query->result();
	}	
	
	public function setVehicleActivity($id_voz,$activity){
		$sql = 'UPDATE vozidlo SET Status_vozidla='. $activity . ' WHERE ID_VOZ='. $id_voz;
		$query = $this->db->query($sql);	
	}
	
	public function updateVehicleInfo($id_voz,$name,$SPZ,$NumberSeats){
		$sql = 'UPDATE vozidlo SET Nazov="'. $name . '", SPZ="' . $SPZ . '", Pocet_miest=' . $NumberSeats . ' WHERE ID_VOZ='. $id_voz;
		$query = $this->db->query($sql);
	}
	
	public function swapInventoryVehicle($id_inv,$id_OldVoz,$idNewVoz){
		$sql = 'UPDATE rezervacia SET rezervacia.ID_VOZ=' . $idNewVoz . ' WHERE (rezervacia.ID_INV=' . $id_inv . ') AND (rezervacia.ID_VOZ='. $id_OldVoz . ')';
		$query = $this->db->query($sql);
	}
	}

		
