<?php 

class Model_inventory extends CI_Model {	
	
	function __construct()
     {
          parent::__construct();
     }
        
        public function getBasicInfo(){ //použité
                $sql = 'SELECT inventura.ID_INV, zakaznik.Nazov_spolocnosti, zakaznik.Mesto, inventura.ID_Veduci, zamestnanec.Meno, zamestnanec.Priezvisko, inventura.Datum_zaciatku, inventura.Datum_konca, inventura.Datum_prihlasovania, inventura.Pocet_zamestnancov
                        FROM inventura
                        LEFT JOIN zakaznik ON inventura.ID_Zakaznika=zakaznik.ID_Zakaznik 
                        LEFT JOIN zamestnanec ON zamestnanec.ID_ZAM=inventura.ID_Veduci
                        LEFT JOIN typ_obchodu ON typ_obchodu.ID_TYP_I=zakaznik.ID_TYP_I
                        LEFT JOIN rozloha ON rozloha.ID_TYP_R=inventura.ID_TYP_R 
                        WHERE inventura.Datum_prihlasovania >= NOW()
                        ORDER BY inventura.Datum_zaciatku
                        ';
                return $this->db->query($sql)->result();           
        } 
        
        public function getMyInventories($id_emp){ //použité
                $sql = 'SELECT inventura.ID_INV, zakaznik.Nazov_spolocnosti, zakaznik.Mesto, inventura.ID_Veduci, zamestnanec.Meno, zamestnanec.Priezvisko, inventura.Datum_zaciatku, inventura.Datum_konca, inventura.Datum_prihlasovania
                        FROM zoznam_prihlasenych
                        LEFT JOIN inventura ON zoznam_prihlasenych.ID_INV=inventura.ID_INV
                        LEFT JOIN zakaznik ON inventura.ID_Zakaznika=zakaznik.ID_Zakaznik 
                        LEFT JOIN zamestnanec ON zamestnanec.ID_ZAM=inventura.ID_Veduci
                        LEFT JOIN typ_obchodu ON typ_obchodu.ID_TYP_I=zakaznik.ID_TYP_I
                        LEFT JOIN rozloha ON rozloha.ID_TYP_R=inventura.ID_TYP_R 
                        WHERE inventura.Datum_prihlasovania >= NOW() AND zoznam_prihlasenych.ID_ZAM='.$id_emp.'
                        ORDER BY inventura.Datum_zaciatku';
                return $this->db->query($sql)->result();        
        }
        
        public function getMyArchive($id_emp){ //použité
                $sql = 'SELECT inventura.ID_INV, zakaznik.Nazov_spolocnosti, zakaznik.Mesto, inventura.ID_Veduci, zamestnanec.Meno, zamestnanec.Priezvisko, inventura.Datum_zaciatku, inventura.Datum_konca, inventura.Datum_prihlasovania
                        FROM zoznam_prihlasenych
                        LEFT JOIN inventura ON zoznam_prihlasenych.ID_INV=inventura.ID_INV
                        LEFT JOIN zakaznik ON inventura.ID_Zakaznika=zakaznik.ID_Zakaznik 
                        LEFT JOIN zamestnanec ON zamestnanec.ID_ZAM=inventura.ID_Veduci
                        LEFT JOIN typ_obchodu ON typ_obchodu.ID_TYP_I=zakaznik.ID_TYP_I
                        LEFT JOIN rozloha ON rozloha.ID_TYP_R=inventura.ID_TYP_R 
                        WHERE inventura.Datum_prihlasovania <= NOW() AND zoznam_prihlasenych.ID_ZAM='.$id_emp.'
                        ORDER BY inventura.Datum_zaciatku';
                return $this->db->query($sql)->result();       
        }
     	 
	public function getActiveInventoryDatabase($id) {
	       $sql1 = '
                        SELECT * FROM inventura 
                        LEFT JOIN zakaznik ON inventura.ID_Zakaznika=zakaznik.ID_Zakaznik 
                        LEFT JOIN zamestnanec ON zamestnanec.ID_ZAM=inventura.ID_Veduci 
                        LEFT JOIN typ_obchodu ON typ_obchodu.ID_TYP_I=zakaznik.ID_TYP_I 
                        LEFT JOIN rozloha ON rozloha.ID_TYP_R=inventura.ID_TYP_R 
                        RIGHT JOIN zoznam_prihlasenych ON inventura.ID_INV=zoznam_prihlasenych.ID_INV
                        WHERE inventura.Datum_konca >= NOW() AND zoznam_prihlasenych.ID_ZAM=' . $id;
                $query1 = $this->db->query($sql1);
                $result1 = $query1->result();               
                return $result1;
	}      
    
        public function getAllActiveInventories(){
                 $sql2 = '
                        SELECT * 
                        FROM inventura 
                        LEFT JOIN zakaznik ON inventura.ID_Zakaznika=zakaznik.ID_Zakaznik 
                        LEFT JOIN zamestnanec ON zamestnanec.ID_ZAM=inventura.ID_Veduci 
                        LEFT JOIN typ_obchodu ON typ_obchodu.ID_TYP_I=zakaznik.ID_TYP_I 
                        LEFT JOIN rozloha ON rozloha.ID_TYP_R=inventura.ID_TYP_R
                        WHERE inventura.Datum_konca >= NOW()';
                        
                $query2 = $this->db->query($sql2);
                $result2 = $query2->result();
                return $result2;
        }
        

        public function getNumberOfLoggedOnInventory($ID_INV) {
                $sql ='
                        SELECT COUNT(inventura.ID_INV) AS Pocet_prihlasenych 
                        FROM inventura 
                        RIGHT JOIN zoznam_prihlasenych ON zoznam_prihlasenych.ID_INV=inventura.ID_INV 
                        WHERE inventura.ID_INV=' . $ID_INV;
                        
                $query = $this->db->query($sql);
                $result = $query->row();
                return $result->Pocet_prihlasenych; 
        }
    
       public function getTimeOfLoggedInventories ($id_zam) {
                $sql='
                        SELECT inventura.Datum_zaciatku, inventura.Datum_konca 
                        FROM `zoznam_prihlasenych`
                        LEFT JOIN inventura on inventura.ID_INV=zoznam_prihlasenych.ID_INV 
                        WHERE zoznam_prihlasenych.ID_ZAM='. $id_zam;
                        
                $query = $this->db->query($sql);
                return $query->result();
       }

       public function getTimeOfLoggedInventoriesByIDINV($id_inv){
                $sql='
                        SELECT inventura.Datum_zaciatku, inventura.Datum_konca 
                        FROM  inventura 
                        WHERE inventura.ID_INV='. $id_inv;
                        
                $query = $this->db->query($sql);
                return $query->row();
       }
     
       public function getNumberOfPlacesInventory($id_inv){
                $sql='
                        SELECT Pocet_zamestnancov 
                        FROM inventura 
                        WHERE inventura.ID_INV=' . $id_inv;  
                $query = $this->db->query($sql);
                $result = $query->row();
                return $result->Pocet_zamestnancov;    
       }
     
       public function LogOnInventory2 ($id_inv, $id_zam){
                $sql='INSERT INTO zoznam_prihlasenych (ID_INV,ID_ZAM) VALUES ("' .$id_inv. '","' .$id_zam .'")';  
                $query = $this->db->query($sql);    
       }
       
       public function getInvetoryReservations($id_inv){
               $sql ='SELECT ID_REZ FROM rezervacia WHERE rezervacia.ID_INV=' . $id_inv;
               $query = $this->db->query($sql); 
               return $query->result();
       }
       
       public function getCountOfEmployeesOnInv($id_rez){
               $sql ='SELECT COUNT(ID_REZ) AS Pocet FROM cesty WHERE cesty.ID_REZ=' . $id_rez;
               $query = $this->db->query($sql); 
               return $query->row();
       }
       
       public function getNumberOfSeats($id_rez){
               $sql = ' SELECT vozidlo.Pocet_miest 
                        FROM rezervacia 
                        LEFT JOIN vozidlo ON vozidlo.ID_VOZ=rezervacia.ID_VOZ
                        WHERE rezervacia.id_rez=' . $id_rez;
               $query = $this->db->query($sql); 
               return $query->row();
       }
       
       public function LogOnReservation($id_rez,$id_zam){
               $sql = 'INSERT INTO cesty (ID_REZ,ID_ZAM) VALUES (' . $id_rez . ', ' .$id_zam .')';
               $query = $this->db->query($sql); 
       }
              
       public function LogOutInventory($id_inv, $id_zam, $id_rez){
               $sql = 'DELETE FROM zoznam_prihlasenych WHERE zoznam_prihlasenych.ID_ZAM=' .$id_zam . ' AND zoznam_prihlasenych.ID_INV=' . $id_inv;
               $this->db->query($sql) ;
               $sql2 = 'DELETE FROM cesty WHERE cesty.ID_REZ=' . $id_rez . ' AND cesty.ID_ZAM=' . $id_zam;
               $this->db->query($sql2) ;
       }
       
       public function getReservationInfo($id_inv, $id_zam){
                $sql = 'SELECT rezervacia.ID_REZ 
                        FROM cesty 
                        LEFT JOIN rezervacia ON cesty.ID_REZ=rezervacia.ID_REZ 
                        WHERE cesty.ID_ZAM='. $id_zam . ' AND rezervacia.ID_INV=' . $id_inv;
                $query = $this->db->query($sql); 
                return $query->row()->ID_REZ;
       }
 
        public function AmILoggedThere($id_inv, $id_zam) {
               $sql = '
                        SELECT zoznam_prihlasenych.ID_INV, zoznam_prihlasenych.ID_ZAM
                        FROM zoznam_prihlasenych
                        WHERE (zoznam_prihlasenych.ID_INV='.$id_inv.')AND (zoznam_prihlasenych.ID_ZAM='.$id_zam .')';
               $query = $this->db->query($sql); 
               if ($query->num_rows() == 1) {			
			return true;
		} else {
			return false;
		}          
        }
        
        public function DetailOfInventory($id_inv) {
                $sql = '
                        SELECT *
                        FROM inventura
                        LEFT JOIN rozloha ON inventura.ID_TYP_R = rozloha.ID_TYP_R
                        LEFT JOIN zakaznik ON inventura.ID_Zakaznika = zakaznik.ID_Zakaznik
                        LEFT JOIN typ_obchodu ON zakaznik.ID_TYP_I = typ_obchodu.ID_TYP_I
                        LEFT JOIN zamestnanec ON inventura.ID_Veduci = zamestnanec.ID_ZAM
                        WHERE inventura.ID_INV=' . $id_inv;
                $query = $this->db->query($sql); 
                return $query->row();
        }
        
        public function getAllJoinedEmployees($id_inv){
                $sql = '
                        SELECT zamestnanec.ID_ZAM, zamestnanec.Meno, zamestnanec.Priezvisko, zamestnanec.ID_TYP_Z
                        FROM zoznam_prihlasenych
                        LEFT JOIN zamestnanec ON zoznam_prihlasenych.ID_ZAM =  zamestnanec.ID_ZAM
                        WHERE ID_INV='. $id_inv;
                $query = $this->db->query($sql); 
                return $query->result();       
                                
        }
        
        public function getAllReservations($id_inv){
               $sql = '
                        SELECT ID_REZ, ID_SOF, Meno, Priezvisko, vozidlo.Nazov, vozidlo.SPZ 
                        FROM rezervacia
                        LEFT JOIN zamestnanec ON rezervacia.ID_SOF=zamestnanec.ID_ZAM
                        LEFT JOIN vozidlo ON vozidlo.ID_VOZ = rezervacia.ID_VOZ
                        WHERE rezervacia.ID_INV='. $id_inv;
                $query = $this->db->query($sql); 
                return $query->result(); 
        }

        public function getEmployeesOnRes($id_rez){
               $sql = 'SELECT zamestnanec.Meno, zamestnanec.Priezvisko, zamestnanec.ID_ZAM FROM cesty LEFT JOIN zamestnanec ON zamestnanec.ID_ZAM=cesty.ID_ZAM WHERE ID_REZ='. $id_rez;
                $query = $this->db->query($sql); 
                return $query->result();  
        }
        
        public function getLeadEmployeeName($id_inv){
                $sql = 'SELECT zamestnanec.ID_ZAM, zamestnanec.Meno, zamestnanec.Priezvisko
                        FROM inventura
                        LEFT JOIN zamestnanec ON inventura.ID_Veduci=zamestnanec.ID_ZAM
                        WHERE inventura.ID_INV='.$id_inv;
                $query = $this->db->query($sql); 
                return $query->row();
        }
        
        public function getCustomersInventories($id_cus){
                $sql = 'SELECT inventura.ID_INV, zakaznik.Nazov_spolocnosti, zakaznik.Mesto, inventura.Datum_zaciatku, zamestnanec.Meno, zamestnanec.Priezvisko 
                FROM inventura 
                LEFT JOIN zakaznik ON inventura.ID_Zakaznika=zakaznik.ID_Zakaznik
                LEFT JOIN zamestnanec ON inventura.ID_Veduci=zamestnanec.ID_ZAM
                WHERE inventura.ID_Zakaznika='.$id_cus.'
                ORDER BY inventura.Datum_zaciatku DESC';
                return $this->db->query($sql)->result(); 
        }
 
        public function getEmployeesForCancel($id_inv){
                $sql = 'SELECT inventura.ID_Veduci AS ID_ZAM, zamestnanec.Meno, zamestnanec.Priezvisko, zamestnanec.Email 
                        FROM inventura LEFT JOIN zamestnanec ON inventura.ID_Veduci=zamestnanec.ID_ZAM
                        WHERE inventura.ID_INV='.$id_inv;
                $data['leadEmployee']=$this->db->query($sql)->result();
                
                $sql = 'SELECT rezervacia.ID_SOF AS ID_ZAM, zamestnanec.Meno, zamestnanec.Priezvisko, zamestnanec.Email
                        FROM rezervacia LEFT JOIN zamestnanec ON rezervacia.ID_SOF=zamestnanec.ID_ZAM
                        WHERE rezervacia.ID_INV='.$id_inv;  
                $data['drivers']=$this->db->query($sql)->result();
                
                $sql = 'SELECT zoznam_prihlasenych.ID_ZAM, zamestnanec.Meno, zamestnanec.Priezvisko, zamestnanec.Email
                        FROM zoznam_prihlasenych LEFT JOIN zamestnanec ON zoznam_prihlasenych.ID_ZAM=zamestnanec.ID_ZAM
                        WHERE zoznam_prihlasenych.ID_INV='.$id_inv; 
                $data['employees']=$this->db->query($sql)->result(); 
                
                return $data;    
        }
        
        public function deleteInventory($id_inv){
                $sql = 'SELECT ID_REZ FROM rezervacia WHERE ID_INV='.$id_inv;
                $reservations = $this->db->query($sql)->result();
                
                foreach($reservations as $r){
                        $sql = 'DELETE FROM cesty WHERE ID_REZ='.$r->ID_REZ;
                        $this->db->query($sql);
                }
                
                $sql = 'DELETE FROM zoznam_prihlasenych WHERE ID_INV='.$id_inv;
                $this->db->query($sql);
                
                $sql = 'DELETE FROM rezervacia WHERE ID_INV='.$id_inv;
                $this->db->query($sql);
                $sql = 'DELETE FROM inventura WHERE ID_INV='.$id_inv;
                $this->db->query($sql);
        }
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
       
/*       
              public function getAllDeclaredInventories() {
               $sql='
SELECT	 	zakaznik.Nazov_spolocnosti, zakaznik.Mesto, zakaznik.Ulica, zakaznik.PSC, zakaznik.Cislo,
	        inventura.Pocet_zamestnancov, inventura.Datum_prihlasovania, inventura.Datum_zaciatku, inventura.Datum_konca, inventura.ID_INV,
   	 	rozloha.Nazov_rozloha,
   		typ_obchodu.Nazov_typ_obchodu,
        	zamestnanec.Meno, zamestnanec.Priezvisko
FROM		inventura
LEFT JOIN 	zakaznik ON zakaznik.ID_Zakaznik=inventura.ID_Zakaznika
LEFT JOIN	zamestnanec ON zamestnanec.ID_ZAM=inventura.ID_Veduci
LEFT JOIN	typ_obchodu ON typ_obchodu.ID_TYP_I=zakaznik.ID_TYP_I
LEFT JOIN	rozloha ON rozloha.ID_TYP_R=inventura.ID_TYP_R
WHERE		inventura.Datum_konca >= NOW()';
$query = $this->db->query($sql);
return $query->result();

        
       }
       public function getAllLoggedOnInventoriesUser($id) {
               $sql='
SELECT          zakaznik.Nazov_spolocnosti, zakaznik.mesto, zakaznik.Ulica, zakaznik.PSC, 
	        inventura.Pocet_zamestnancov, inventura.Datum_prihlasovania, inventura.Datum_zaciatku, inventura.Datum_konca,
   	 	rozloha.Nazov_rozloha,
   		typ_obchodu.Nazov_typ_obchodu,
        	zamestnanec.Meno, zamestnanec.Priezvisko

FROM            inventura 
LEFT JOIN zakaznik ON inventura.ID_Zakaznika=zakaznik.ID_Zakaznik 
LEFT JOIN zamestnanec ON zamestnanec.ID_ZAM=inventura.ID_Veduci 
LEFT JOIN typ_obchodu ON typ_obchodu.ID_TYP_I=zakaznik.ID_TYP_I 
LEFT JOIN rozloha ON rozloha.ID_TYP_R=inventura.ID_TYP_R 
RIGHT JOIN zoznam_prihlasenych ON inventura.ID_INV=zoznam_prihlasenych.ID_INV
WHERE inventura.Datum_konca >= NOW() AND zoznam_prihlasenych.ID_ZAM=' . $id;
$query = $this->db->query($sql);
return $query->result();
       }
       
        public function LogOnInventory($email, $id_inv) {
        
                $sql = 'SELECT  zamestnanec.ID_ZAM FROM zamestnanec WHERE email="' . $email . '"';   
                $query = $this->db->query($sql);
                $result = $query->row();
                $id_zam = $result->ID_ZAM;
        
                $sql2= 'SELECT * FROM zoznam_prihlasenych WHERE zoznam_prihlasenych.ID_ZAM=' . $id_zam . ' AND zoznam_prihlasenych.ID_INV=' . $id_inv;          
                $query2 = $this->db->query($sql2);
                $result2 = $query2->row();       
                
                        if ($query2->num_rows() == 1) {			
                                return false;
		        } else {
			        $sql3='INSERT INTO zoznam_prihlasenych (ID_INV,ID_ZAM) VALUES (' . $id_inv . ', ' .$id_zam .')';
                                $this->db->query($sql3);
                                return true;
		              }       
        }
 */      
}