<?php 

class Model_employee extends CI_Model {	
	
	function __construct()
     {
          parent::__construct();
     }
     
        public function amIActiveEmployee($id_zamestnanca){
                $sql = 'SELECT zamestnanec.Status FROM zamestnanec WHERE zamestnanec.ID_ZAM='.$id_zamestnanca;
                $query = $this->db->query($sql)->row();
                if($query->Status==1){
                        return TRUE;
                }else{
                        return FALSE;
                }
        }
        
        public function getCountMyInventories($id_emp,$date){
                $sql = 'SELECT COUNT(inventura.ID_INV) AS Pocet
                        FROM zoznam_prihlasenych
                        LEFT JOIN inventura ON zoznam_prihlasenych.ID_INV=inventura.ID_INV
                        WHERE zoznam_prihlasenych.ID_ZAM='.$id_emp.' AND inventura.Datum_zaciatku LIKE "'.$date.'%"';
                return $this->db->query($sql)->row()->Pocet;
        }
        
        public function getCountMyInventoriesDriver($id_emp,$date){
                $sql = 'SELECT COUNT(rezervacia.ID_REZ) AS Pocet 
                        FROM rezervacia
                        LEFT JOIN inventura ON rezervacia.ID_INV=inventura.ID_INV
                        WHERE rezervacia.ID_SOF='.$id_emp.' AND inventura.Datum_zaciatku LIKE "'.$date.'%"';
                return $this->db->query($sql)->row()->Pocet;
        }
        
        public function createNewPost($id_emp,$subject,$post){
                $sql = 'INSERT INTO hlasenie (Predmet, Hlasenie, ID_ZAM, Datum_podania, Status)
                        VALUES ("'.$subject.'", "'.$post.'", '.$id_emp.', "'.date("Y-m-d G:i:s").'", 1)';
                $this->db->query($sql);                             
        }
        
        public function getCountPenalty($id_emp){
                $sql = 'SELECT COUNT(ID_ZAM) AS Pocet FROM report_zoznam WHERE ID_ZAM='.$id_emp.' AND Vyplatene<0 AND Koniec LIKE "'.date("Y").'%"';
                return $this->db->query($sql)->row()->Pocet;        
        }
        
        public function whenIStarted($id_emp){
                $sql = 'SELECT Prijaty FROM zamestnanec WHERE ID_ZAM='.$id_emp;
                return $this->db->query($sql)->row()->Prijaty;
        }
        
        public function whenIEnded($id_emp){
                $sql = 'SELECT Prepusteny FROM zamestnanec WHERE ID_ZAM='.$id_emp;
                return $this->db->query($sql)->row()->Prepusteny;
        }
        
        public function getInfoForPayCheck($id_emp){
                $sql = 'SELECT Meno, Priezvisko, Nazov_poistovne, Kod_poistovne, Cislo_uctu, Kod_banky 
                        FROM zamestnanec 
                        LEFT JOIN poistovna ON zamestnanec.ID_POIST=poistovna.ID_POIST
                        LEFT JOIN bankove_spojenie ON zamestnanec.ID_ZAM=bankove_spojenie.ID_ZAM
                        WHERE zamestnanec.ID_ZAM='.$id_emp;
                return $this->db->query($sql)->row();
        }
        
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////	 
	public function getGeneralEmployeeInfo($id_zamestnanca) {
	$sql = 'SELECT  zamestnanec.ID_ZAM, 
                        zamestnanec.ID_TYP_Z, 
                        zamestnanec.ID_POIST, 
                        zamestnanec.Rodne_cislo,
                        zamestnanec.Meno,
                        zamestnanec.Priezvisko,
                        zamestnanec.Mesto_zamestnanca,
                        zamestnanec.Ulica_zamestnanca,
                        zamestnanec.Cislo_domu_zamestnanca,
                        zamestnanec.PSC_zamestnanca,
                        zamestnanec.Tel_cislo_zamestnanca,
                        zamestnanec.Email,
                        poistovna.ID_POIST,
                        poistovna.Nazov_poistovne,
                        poistovna.Kod_poistovne,
                        bankove_spojenie.Cislo_uctu,
                        bankove_spojenie.Kod_banky,
                        zamestnanec.Prijaty,
                        zamestnanec.Prepusteny,
                        zamestnanec.Status 
                FROM zamestnanec 
                LEFT JOIN poistovna ON zamestnanec.ID_POIST=poistovna.ID_POIST 
                LEFT JOIN bankove_spojenie ON zamestnanec.ID_ZAM=bankove_spojenie.ID_ZAM
                WHERE zamestnanec.ID_ZAM =' . $id_zamestnanca;
        $query = $this->db->query($sql);
        $result = $query->row();
        return $result;
	}
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////        
        public function finallyUpdateProfile($id_zamestnanca,$Ulica_zamestnanca,$Cislo_domu_zamestnanca,$PSC_zamestnanca,$Mesto_zamestnanca,$Tel_cislo_zamestnanca,$Cislo_uctu,$Kod_banky,$ID_poist){
                $sql3 = 'SELECT ID_B_SPOJ 
                         FROM bankove_spojenie 
                         WHERE bankove_spojenie.ID_ZAM=' . $id_zamestnanca;
                $query3=$this->db->query($sql3);
                $result3=$query3->row();
                  
                $sql  = 'UPDATE zamestnanec 
                         SET Mesto_zamestnanca="' . $Mesto_zamestnanca . '", 
                             Ulica_zamestnanca="' . $Ulica_zamestnanca . '",
                             Cislo_domu_zamestnanca="' . $Cislo_domu_zamestnanca . '",
                             PSC_zamestnanca="' . $PSC_zamestnanca . '",
                             Tel_cislo_zamestnanca="' . $Tel_cislo_zamestnanca . '",
                             ID_POIST="' . $ID_poist . '"
                         WHERE ID_ZAM=' . $id_zamestnanca ;
                         $this->db->query($sql); 
                
                if($query3->num_rows() == 1){         
                        $sql2 = 'UPDATE bankove_spojenie 
                                 SET Cislo_uctu="' . $Cislo_uctu . '", 
                                     Kod_banky="' . $Kod_banky . '"                      
                                 WHERE ID_ZAM=' . $id_zamestnanca ;
       
                        $this->db->query($sql2); 
                } else {
                        $sql4 = 'INSERT INTO bankove_spojenie (ID_ZAM, Cislo_uctu, Kod_banky)
                                 VALUES ("' . $id_zamestnanca . '","' .  $Cislo_uctu . '","' . $Kod_banky . '")'; 
                        $this->db->query($sql4);
                }                                         
       }
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////                                        
        public function existEmailPassword($email, $oldpassword)  {
                $sql = 'SELECT * 
                        FROM zamestnanec 
                        WHERE ((email="' . $email . '") AND (password="' . $oldpassword . '"))';
                $query = $this->db->query($sql);
                $result = $query->row();
                
                if ($query->num_rows() == 1) {
                        return TRUE;
                } else {
                        return FALSE;
                }
        }  
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////        
        public function setPassword($password, $email) {
                $sql= 'UPDATE zamestnanec 
                       SET password="' . $password . '" 
                       WHERE Email="' . $email . '"';
                $this->db->query($sql);
        }
        
        public function getEmployeeInfoEdit($id_emp){
                $sql = 'SELECT zamestnanec.ID_ZAM, zamestnanec.Meno, zamestnanec.Priezvisko, zamestnanec.ID_TYP_Z, zamestnanec.Rodne_cislo, zamestnanec.Email, zamestnanec.Status 
                        FROM zamestnanec 
                        WHERE ID_ZAM='.$id_emp;
                $query = $this->db->query($sql);
                return $query->row();        
        }
        
        public function updateEmployeeInfo($id_emp, $First, $Last, $Email, $Birth, $Type, $Status ){
                switch($Status){
                        case 1:
                                $sql = 'UPDATE zamestnanec SET Meno="'.$First .'", Priezvisko="'.$Last.'", Email="'.$Email.'", Rodne_cislo="'.$Birth.'", ID_TYP_Z='.$Type.', Prepusteny=NULL, Status=1
                                        WHERE ID_ZAM='.$id_emp ; 
                        break;
                        case 2:
                                $sql = 'UPDATE zamestnanec SET Meno="'.$First .'", Priezvisko="'.$Last.'", Email="'.$Email.'", Rodne_cislo="'.$Birth.'", ID_TYP_Z='.$Type.', Prepusteny="'.date("Y-m-d").'", Status=2
                                        WHERE ID_ZAM='.$id_emp ;  
                        break;
                        case 3:
                                $sql = 'UPDATE zamestnanec SET Meno="'.$First .'", Priezvisko="'.$Last.'", Email="'.$Email.'", Rodne_cislo="'.$Birth.'", ID_TYP_Z='.$Type.', Prepusteny="'.date("Y-m-d").'", Status=3
                                        WHERE ID_ZAM='.$id_emp ; 
                        break;         
                }
                
                $this->db->query($sql);            
        }
        
        public function canILogin($id_emp, $trunkDate, $endDate){ //použité
                $sql=   'SELECT inventura.ID_INV
                        FROM inventura 
                        RIGHT JOIN zoznam_prihlasenych ON inventura.ID_INV=zoznam_prihlasenych.ID_INV 
                        WHERE (zoznam_prihlasenych.ID_ZAM='.$id_emp.') AND 
                                (
                                (("'.$trunkDate.'">=inventura.Datum_prihlasovania) AND ("'.$trunkDate.'"<=inventura.Datum_konca)) OR
                                (("'.$endDate.'">=inventura.Datum_prihlasovania) AND ("'.$endDate.'"<=inventura.Datum_konca)) OR
                                (("'.$trunkDate.'"<=inventura.Datum_prihlasovania) AND ("'.$endDate.'">=inventura.Datum_konca))
                                )';
                $query = $this->db->query($sql);
                if ($query->num_rows()==0){
                        return TRUE;
                } else {
                        return FALSE;
                }        
        }
        
        public function getCountEmployeesOnInventory($id_inv){ //použité
                $sql = 'SELECT * FROM zoznam_prihlasenych WHERE ID_INV='.$id_inv;
                return  $this->db->query($sql)->num_rows();
        }
        
        public function getMaxEmployeesOnInventory($id_inv){ //použité
                $sql = 'SELECT inventura.Pocet_zamestnancov FROM inventura WHERE ID_INV='.$id_inv;
                return  $this->db->query($sql)->row()->Pocet_zamestnancov;                
        }
        
        public function amILoggedThere($id_inv, $id_emp){
                $sql = 'SELECT * FROM zoznam_prihlasenych WHERE ID_INV='.$id_inv.' AND ID_ZAM='.$id_emp;
                $query = $this->db->query($sql);
                if ($query->num_rows()==0){
                        return FALSE;
                } else {
                        return TRUE;
                }
        }
        
        public function getInfoForCheckNewEmployee(){
                $sql = 'SELECT zamestnanec.ID_ZAM, zamestnanec.Email, zamestnanec.Rodne_cislo FROM zamestnanec';
                return $this->db->query($sql)->result();
        }
        
         public function getEmployeeType($id_emp){
                $sql = 'SELECT zamestnanec.ID_TYP_Z FROM zamestnanec WHERE ID_ZAM='.$id_emp;
                return $this->db->query($sql)->row()->ID_TYP_Z;
        }
        
        public function getLastInventoriesEmployee($id_emp, $limit ){
                $sql = 'SELECT inventura.ID_INV,inventura.Datum_konca, inventura.Pocet_zamestnancov, inventura.Datum_zaciatku, zakaznik.Mesto, zakaznik.Nazov_spolocnosti, zamestnanec.Meno, zamestnanec.Priezvisko
                        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 inventura.ID_Veduci = zamestnanec.ID_ZAM
                        WHERE zoznam_prihlasenych.ID_ZAM='.$id_emp.'
                        ORDER BY inventura.Datum_zaciatku DESC 
                        LIMIT '.$limit;
                return $this->db->query($sql)->result();
        }
        
        public function getLastInventoriesDriver($id_emp, $limit){
                $sql = 'SELECT inventura.ID_INV, zakaznik.Nazov_spolocnosti, zakaznik.Mesto, inventura.Pocet_zamestnancov, zamestnanec.Meno, zamestnanec.Priezvisko, inventura.Datum_zaciatku, inventura.Datum_konca
                        FROM rezervacia
                        LEFT JOIN inventura ON rezervacia.ID_INV=inventura.ID_INV
                        LEFT JOIN zakaznik ON inventura.ID_Zakaznika=zakaznik.ID_Zakaznik
                        LEFT JOIN zamestnanec ON inventura.ID_Veduci=zamestnanec.ID_ZAM 
                        WHERE rezervacia.ID_SOF='.$id_emp.'
                        ORDER BY inventura.Datum_zaciatku DESC
                        LIMIT '.$limit;       
                return $this->db->query($sql)->result();     
        }
       
        public function getLastInventoriesLeadEmployee($id_emp, $limit){
                $sql = 'SELECT inventura.ID_INV, zakaznik.Nazov_spolocnosti, zakaznik.Mesto, inventura.Pocet_zamestnancov, zamestnanec.Meno, zamestnanec.Priezvisko, inventura.Datum_zaciatku, inventura.Datum_konca
                        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_Veduci='.$id_emp.'
                        ORDER BY inventura.Datum_zaciatku DESC
                        LIMIT '.$limit;
                return $this->db->query($sql)->result();                        
        }
        
        public function getEmployeeName($id_emp){
                $sql = 'SELECT Meno, Priezvisko FROM zamestnanec WHERE ID_ZAM='.$id_emp;
                $query = $this->db->query($sql)->row(); 
                return ($query->Meno." ".$query->Priezvisko);
        }
        
        public function haveICompletedProfile($id_emp){
                $sql = 'SELECT zamestnanec.Mesto_zamestnanca, zamestnanec.Ulica_zamestnanca, zamestnanec.Cislo_domu_zamestnanca, zamestnanec.PSC_zamestnanca, zamestnanec.Tel_cislo_zamestnanca, zamestnanec.ID_POIST, bankove_spojenie.Cislo_uctu, bankove_spojenie.Kod_banky
                        FROM zamestnanec 
                        LEFT JOIN bankove_spojenie ON zamestnanec.ID_ZAM=bankove_spojenie.ID_ZAM
                        WHERE zamestnanec.ID_ZAM='.$id_emp;
                $query = $this->db->query($sql)->row();
                
                if($query->Mesto_zamestnanca==NULL || $query->Ulica_zamestnanca==NULL || $query->Cislo_domu_zamestnanca==NULL || $query->PSC_zamestnanca==NULL || $query->Tel_cislo_zamestnanca==NULL || $query->ID_POIST==NULL || $query->Cislo_uctu==NULL || $query->Kod_banky==NULL){
                        return FALSE;
                }else{
                        return TRUE;
                }
        }
        
        public function getAmercements($id_emp, $limit){
               
                $sql = 'SELECT inventura.ID_INV, report_zoznam.Vyplatene, report_zoznam.ID_REP, inventura.Datum_zaciatku, zakaznik.Nazov_spolocnosti, zakaznik.Mesto
                        FROM report_zoznam
                        LEFT JOIN inventura ON inventura.ID_Report = report_zoznam.ID_REP
                        LEFT JOIN zakaznik ON inventura.ID_Zakaznika = zakaznik.ID_Zakaznik
                        WHERE report_zoznam.ID_ZAM ='.$id_emp.'
                        AND Vyplatene <=0
                        ORDER BY inventura.Datum_zaciatku DESC ';
                       
                $query = $this->db->query($sql);  
          
                if($query->num_rows()>0){
                        return $query->result();
                }else{
                        return FALSE;
                }
        }
} 