<?php 

class Model_lead_employee extends CI_Model {	
	
	function __construct()
     {
          parent::__construct();
     }
     
      public function getInventoryInfo($id_inv){
             $sql = 'SELECT * FROM inventura WHERE ID_INV='.$id_inv;
             $query = $this->db->query($sql);
             return $query->row();
     }
     
     public function getEmployeeInfo($id_emp){
             $sql = 'SELECT * FROM zamestnanec LEFT JOIN typ_zamestnanca ON zamestnanec.ID_TYP_Z=typ_zamestnanca.ID_TYP_Z WHERE zamestnanec.ID_ZAM='.$id_emp;
             $query = $this->db->query($sql);
             return $query->row();
     }
     
     public function getOnlyInventoryReservations($id_inv){
             $sql = 'SELECT * FROM rezervacia WHERE ID_INV='.$id_inv;
             $query = $this->db->query($sql);
             if ($query->num_rows()==0){
                 return FALSE;
             } else {
                 return $query->result();         
             }
     }
     
     public function getCustomerId($id_inv){
             $sql = 'SELECT inventura.ID_Zakaznika FROM inventura WHERE ID_INV='.$id_inv;
             return $this->db->query($sql)->row()->ID_Zakaznika;
     }
     
     public function getReservationEmployees($id_rez){
             $sql = 'SELECT cesty.ID_ZAM FROM cesty LEFT JOIN zamestnanec ON cesty.ID_ZAM=zamestnanec.ID_ZAM WHERE cesty.ID_REZ='.$id_rez.' ORDER BY ID_TYP_Z';
             $query = $this->db->query($sql);
             return $query->result();        
     }
     
     public function getReportDateTimeStart($id_rep,$id_lead){
             $sql = 'SELECT Nastup, Koniec FROM report_zoznam WHERE ID_REP='.$id_rep.' AND ID_ZAM='.$id_lead;
             $query = $this->db->query($sql);
             return $query->row();       
     }
     
     public function getReportId($id_inv){
             $sql = 'SELECT inventura.ID_Report FROM inventura WHERE ID_INV='.$id_inv;
             $query = $this->db->query($sql);
             return $query->row()->ID_Report;
     }
     
     public function getInventoryReport($id_inv){
             $sql = 'SELECT * FROM report LEFT JOIN inventura ON report.ID_REP=inventura.ID_Report WHERE ID_INV='.$id_inv;
             $query = $this->db->query($sql);
             if ($query->num_rows()==0){
                 return FALSE;
             } else {
                 return $query->row();         
             }                 
     }
     
     public function updateReportInfo($id_rep,$date,$notice){
             $sql= 'UPDATE report SET Vlozene="'.$date.'", Poznamka="'.$notice.'" WHERE ID_REP='.$id_rep;
             $query = $this->db->query($sql);
     }
     
     public function updateReportEmployeeInfo($id_rep,$id_zam, $start, $end, $pause, $salary){
             if ($pause==NULL){
                $sql= 'UPDATE report_zoznam SET Nastup="'.$start->format('Y-m-d H:i:s').'", Koniec="'.$end->format('Y-m-d H:i:s').'", Prestavka=NULL, Vyplatene="'.$salary.'" WHERE ID_REP='.$id_rep.' AND ID_ZAM='.$id_zam;                   
                } else {
                $sql= 'UPDATE report_zoznam SET Nastup="'.$start->format('Y-m-d H:i:s').'", Koniec="'.$end->format('Y-m-d H:i:s').'", Prestavka="'.$pause.'", Vyplatene="'.$salary.'" WHERE ID_REP='.$id_rep.' AND ID_ZAM='.$id_zam;                   
             }
             $query = $this->db->query($sql);
     }
     
     public function getReportEmployeesId($id_rep){
             $sql = 'SELECT report_zoznam.ID_ZAM, zamestnanec.Meno, zamestnanec.Priezvisko, report_zoznam.Nastup, report_zoznam.Koniec, report_zoznam.Prestavka, report_zoznam.Vyplatene FROM report_zoznam LEFT JOIN zamestnanec ON report_zoznam.ID_ZAM=zamestnanec.ID_ZAM WHERE ID_REP='.$id_rep;
             $query = $this->db->query($sql);
             return $query->result();         
     }
     
     
     
     public function getEmployeeTimeReport($id_rep,$id_emp){
             $sql = 'SELECT Nastup, Koniec, Prestavka FROM report_zoznam WHERE ID_REP='.$id_rep.' AND ID_ZAM='.$id_emp;
             $query = $this->db->query($sql);
             return $query->row();        
     }
     
     public function getReportEmployeesExceptLead($id_rep,$id_lead){
             $sql = 'SELECT * FROM report_zoznam WHERE ID_REP='.$id_rep.' AND ID_ZAM !='.$id_lead;
             $query = $this->db->query($sql);
             return $query->result();        
     }
     
     public function getGeneralInventoryInfo($id_inv){
        $sql =  'SELECT * FROM inventura 
                LEFT JOIN zakaznik ON zakaznik.ID_Zakaznik=inventura.ID_Zakaznika
                LEFT JOIN rozloha ON rozloha.ID_TYP_R=inventura.ID_TYP_R
                LEFT JOIN typ_obchodu ON zakaznik.ID_TYP_I=typ_obchodu.ID_TYP_I
                WHERE inventura.ID_INV='.$id_inv;
        $query = $this->db->query($sql);
        return $query->row();               
     }
     
     public function getMyInventories($id_emp){
	   $sql = 'SELECT * FROM inventura
             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 (inventura.ID_Veduci='.$id_emp.')
             ORDER BY inventura.Datum_zaciatku';
       $query = $this->db->query($sql);
       return $query->result();	 
	}
       
     public function getNoticedInventories($id_emp){
       $sql = 'SELECT * FROM inventura 
               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())
               ORDER BY inventura.Datum_zaciatku';
       $query = $this->db->query($sql);
       return $query->result();	      
     }
     
     public function getFreeInventories($id_emp){
       $sql = 'SELECT * FROM inventura 
               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 (ID_Veduci IS NULL)
               ORDER BY inventura.Datum_zaciatku';
       $query = $this->db->query($sql);
       return $query->result();  
     }
     
     public function getMyArchive($id_emp, $limit, $offset=0){
        if ($offset==''){
                $offset=0;
        } 
       $sql = 'SELECT * FROM inventura 
               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 (inventura.ID_Veduci='.$id_emp.')
               ORDER BY inventura.Datum_zaciatku DESC
               LIMIT '.$limit.' OFFSET '.$offset;
       $query = $this->db->query($sql);
       return $query->result();	        
     }
     
     public function getCountMyArchive($id_emp){
        $sql = 'SELECT COUNT(inventura.ID_INV) AS Pocet FROM inventura 
               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 (inventura.ID_Veduci='.$id_emp.')
               ORDER BY inventura.Datum_zaciatku DESC';
       $query = $this->db->query($sql);
       return $query->row()->Pocet;       
     }
     
     public function canIDownloadReport($id_inv){
             $sql='SELECT inventura.ID_INV 
                   FROM inventura
                   WHERE Datum_prihlasovania<=NOW() AND id_inv='.$id_inv;
             $query = $this->db->query($sql);
             if ($query->num_rows()==0){
                return TRUE;
        } else {
                return FALSE;
        }
             
     }
     
     public function amILoggedElseWhere($id_emp, $id_inv, $startDate, $endDate){
  
        $sql= 'SELECT inventura.ID_INV
               FROM inventura 
               WHERE (inventura.ID_Veduci='.$id_emp.') AND 
                (
                (("'.$startDate.'">=inventura.Datum_zaciatku) AND ("'.$startDate.'"<=inventura.Datum_konca)) OR
                (("'.$endDate.'">=inventura.Datum_zaciatku) AND ("'.$endDate.'"<=inventura.Datum_konca)) OR
                (("'.$startDate.'"<=inventura.Datum_zaciatku) AND ("'.$endDate.'">=inventura.Datum_konca))
                )'; 
        $query = $this->db->query($sql);
  
        if ($query->num_rows()==0){
                return FALSE;
        } else {
                return TRUE;
        }
     }
     
     public function getCustomerInfo($id_inv){
        $sql = 'SELECT * FROM zakaznik 
                RIGHT JOIN inventura ON inventura.ID_Zakaznika=zakaznik.ID_Zakaznik
                LEFT JOIN zamestnanec ON inventura.ID_Veduci=zamestnanec.ID_ZAM
                WHERE inventura.ID_INV='. $id_inv;
        $query = $this->db->query($sql);
        
        if ($query->num_rows()==0){
                return FALSE;
        } else {
                return $query->row();
        }
        
     }
     
     public function getInventoryReservations($id_inv){
        $sql = 'SELECT * FROM rezervacia LEFT JOIN zamestnanec ON zamestnanec.ID_ZAM=rezervacia.ID_SOF WHERE rezervacia.ID_INV='.$id_inv;
               
        $query = $this->db->query($sql);
        
        if ($query->num_rows()==0){
                return FALSE;
        } else {
                return $query->result(); 
        }              
     }
     
     public function getInventoryEmployees($id_inv,$id_lead){
        $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 zoznam_prihlasenych.ID_INV='.$id_inv.' AND zamestnanec.ID_ZAM !='.$id_lead.' 
                ORDER BY zamestnanec.Priezvisko';
        $query = $this->db->query($sql);
         return $query->result();
     }
     
     public function getSalaryPerHourDriver(){
        $sql = 'SELECT Vyplata_sofer, MAX(Datum_nastavenia) FROM nastavenia';
        $query = $this->db->query($sql);
        return $query->row();
     }
     
     public function getSalaryPerHourEmployee(){
        $sql = 'SELECT Vyplata_zamestnanec, MAX(Datum_nastavenia) FROM nastavenia';
        $query = $this->db->query($sql);
        return $query->row();
     }  
     
     public function getSalaryPerHourLead(){
        $sql = 'SELECT Vyplata_veduci_inventury, MAX(Datum_nastavenia) FROM nastavenia';
        $query = $this->db->query($sql);
        return $query->row();        
     } 
     
     public function getSalaryPerKilometer(){
        $sql = 'SELECT Vyplata_kilometer, MAX(Datum_nastavenia) FROM nastavenia';
        $query = $this->db->query($sql);
        return $query->row();         
     }
     
     public function createReport($id_inv,$date,$notice){
        $sql = 'INSERT INTO report (Vlozene,Poznamka) VALUES ("'.$date.'","'.$notice.'")';
        $query = $this->db->query($sql); 
        $lastinsert = $this->db->insert_id(); 
        $sql2 = 'UPDATE inventura SET ID_Report='.$lastinsert.' WHERE ID_INV='.$id_inv;
        $query2 = $this->db->query($sql2);
        return $lastinsert;
     }
     
     public function getLeadEmployeeID($id_inv){
        $sql = 'SELECT ID_Veduci FROM inventura WHERE ID_INV='.$id_inv;
        $query = $this->db->query($sql);
        return $query->row();    
     }
     
     public function insertIntoListLeadEmployee($id_rep,$id_lead_employee,$invStart,$invEnd,$salary){
        $sql = 'INSERT INTO report_zoznam (ID_REP,ID_ZAM,Nastup,Koniec,Vyplatene)
                VALUES ("'.$id_rep.'",
                "'.$id_lead_employee->ID_Veduci.'",
                "'.$invStart->format('Y-m-d H:i:s').'",
                "'.$invEnd->format('Y-m-d H:i:s').'",
                "'.$salary.'")';
             
        $this->db->query($sql);
     }
     
     public function insertIntoListDriver($id_rep,$id_emp,$DriverDateTimeStart,$DriverDateTimeEnd,$salary){
        $sql = 'INSERT INTO report_zoznam (ID_REP,ID_ZAM,Nastup,Koniec,Vyplatene)
                VALUES ("'.$id_rep.'",
                "'.$id_emp.'",
                "'.$DriverDateTimeStart->format('Y-m-d H:i:s').'",
                "'.$DriverDateTimeEnd->format('Y-m-d H:i:s').'",
                "'.$salary.'")';
             
        $this->db->query($sql);        
     }
     
     public function getRealId($id){
             if (strlen($id)==1) {
                       return "0000".$id;
                } elseif (strlen($id)==2) {
                        return "000".$id;
                } elseif (strlen($id)==3) {
                        return "00".$id;
                } elseif (strlen($id)==4) {
                        return "0".$id;
                } else {
                        return $id;
                }
     }
     
     public function insertIntoListEmployee($id_rep,$id_emp,$EmployeeDateTimeStart,$EmployeeDateTimeEnd,$EmployeePause,$salary){
        $sql = 'INSERT INTO report_zoznam (ID_REP,ID_ZAM,Nastup,Koniec,Prestavka,Vyplatene)
                VALUES ("'.$id_rep.'",
                "'.$id_emp.'",
                "'.$EmployeeDateTimeStart->format('Y-m-d H:i:s').'",
                "'.$EmployeeDateTimeEnd->format('Y-m-d H:i:s').'",
                "'.$EmployeePause.'",
                "'.$salary.'")';           
        $this->db->query($sql); 
        if($salary==0){
                $sql="SELECT Meno, Priezvisko FROM zamestnanec WHERE ID_ZAM=".$id_emp;
                $x=$this->db->query($sql)->row(); 
                $this->load->model("model_employee");
                $subject="Storno zamestnanca ".$x->Priezvisko." ".$x->Meno;
                $sql="SELECT inventura.Datum_zaciatku, zakaznik.Nazov_spolocnosti, zakaznik.Mesto, inventura.ID_INV, zamestnanec.Meno, zamestnanec.Priezvisko, zamestnanec.ID_ZAM 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_Report=".$id_rep;
                $y=$this->db->query($sql)->row();
                $post="Zamestnanec ".$x->Priezvisko." ".$x->Meno." s identifikačným číslom ".$this->getRealId($id_emp)." sa nedostavil na inventúru pre zákazníka ".$y->Nazov_spolocnosti." - ".$y->Mesto.", ktorá sa konala ".date('d.m.Y',strtotime($y->Datum_zaciatku));             
                $this->model_employee->createNewPost($y->ID_ZAM,$subject,$post);
                                            
               
        }        
     }
     
     public function isDriver($id_inv,$id_emp){
        $sql = 'SELECT ID_SOF FROM rezervacia WHERE ID_INV='.$id_inv.' AND ID_SOF!=0';
        $query = $this->db->query($sql);
        $status = FALSE;
        foreach($query->result() as $result){
                if($result->ID_SOF==$id_emp){
                        $status = TRUE;       
                }
        }
        return $status;
     }
     
     public function getCarInfo($id_car){
        $sql = 'SELECT * FROM vozidlo WHERE ID_VOZ='.$id_car;
        $query = $this->db->query($sql);
        return $query->row();        
     }
     
     public function joinLeadEmployeeInventory($id_inv,$id_emp){
        //update inventura veduci
        $sql = 'UPDATE inventura SET ID_Veduci='.$id_emp.'
                WHERE inventura.ID_INV='.$id_inv;
        $query = $this->db->query($sql);
        //zistenie pozicie rezervacie pre veduceho v cestach
        $sql = 'SELECT cesty.ID_REZ FROM cesty 
                LEFT JOIN rezervacia ON cesty.ID_REZ=rezervacia.ID_REZ 
                WHERE rezervacia.ID_INV='.$id_inv.' AND ID_ZAM IS NULL';
        $query = $this->db->query($sql);
        $id_rez= $query->row()->ID_REZ;
        //vlozene id zamestnanca do rezervacie cesty
        $sql = 'UPDATE cesty SET ID_ZAM='.$id_emp.' 
                WHERE ID_REZ='.$id_rez.' AND ID_ZAM IS NULL';
        $query = $this->db->query($sql);            
     }
     
     public function disjoinLeadEmployeeInventory($id_inv,$id_emp){
        //zistenie pozicie rezervacie pre veduceho na cestach
        $sql = 'SELECT cesty.ID_REZ FROM cesty 
                LEFT JOIN rezervacia ON cesty.ID_REZ=rezervacia.ID_REZ 
                WHERE rezervacia.ID_INV='.$id_inv.' AND ID_ZAM='.$id_emp;
        $query = $this->db->query($sql);
        $id_rez= $query->row()->ID_REZ;
        $sql = 'UPDATE inventura SET ID_Veduci=NULL WHERE ID_INV='.$id_inv;
        $query = $this->db->query($sql);
        //zmazanie rezervacie, pretoze nejde boh vie preco update a sere ma to uz
        $sql = 'DELETE FROM cesty WHERE ID_REZ='.$id_rez.' AND ID_ZAM='.$id_emp;
        $query = $this->db->query($sql);
        //vytvorenie novej rezervacie na cestach veduceho
        $sql = 'INSERT INTO cesty (ID_REZ) VALUES ('.$id_rez.')'; 
        $query = $this->db->query($sql);     
     }
     
     
     public function getCountAllEmployees(){
        $sql='SELECT COUNT(ID_ZAM) AS pocet FROM zamestnanec WHERE ID_TYP_Z>1';
        $query = $this->db->query($sql);
        return $query->row()->pocet;			
     }     
          
     public function getCountAllLeadEmployees(){
        $sql='SELECT COUNT(ID_ZAM) AS pocet FROM zamestnanec WHERE ID_TYP_Z=2';
        $query = $this->db->query($sql);
        return $query->row()->pocet;        
     }
     
     public function getCountAllDrivers(){
        $sql='SELECT COUNT(ID_ZAM) AS pocet FROM zamestnanec WHERE ID_TYP_Z=3';
        $query = $this->db->query($sql);
        return $query->row()->pocet;        
     }
     
     public function getCountAllBasicEmployees(){
        $sql='SELECT COUNT(ID_ZAM) AS pocet FROM zamestnanec WHERE ID_TYP_Z=4';
        $query = $this->db->query($sql);
        return $query->row()->pocet;        
     }

     
     public function fetchAllEmployees($limit,$offset){
        $query = $this->db->get_where('zamestnanec', 'ID_TYP_Z > 1' ,$limit,$offset);       
        if($query->num_rows()>0){
                foreach($query->result() as $row) {
                        $data[] = $row;
                }
                return $data;
        } else {
                return false;	
        }
     }
     
     public function fetchLeadEmployees($limit,$offset){
        $query = $this->db->get_where('zamestnanec', 'ID_TYP_Z = 2' ,$limit,$offset);       
        if($query->num_rows()>0){
                foreach($query->result() as $row) {
                        $data[] = $row;
                }
                return $data;
        } else {
                return false;	
        }
     }
     
     public function fetchDrivers($limit,$offset){
        $query = $this->db->get_where('zamestnanec', 'ID_TYP_Z = 3' ,$limit,$offset);       
        if($query->num_rows()>0){
                foreach($query->result() as $row) {
                        $data[] = $row;
                }
                return $data;
        } else {
                return false;	
        }
     }
     
     public function fetchEmployees($limit,$offset){
        $query = $this->db->get_where('zamestnanec', 'ID_TYP_Z = 4' ,$limit,$offset);       
        if($query->num_rows()>0){
                foreach($query->result() as $row) {
                        $data[] = $row;
                }
                return $data;
        } else {
                return false;	
        }
     }
     
     public function searchEmployee($Employee, $mode){
		if ($mode==0){
			$sql = 'SELECT * 
					FROM zamestnanec 
					WHERE (Meno LIKE "%'. $Employee .'%" OR Priezvisko LIKE "%'. $Employee .'%" OR ID_ZAM LIKE "%'. $Employee .'%" OR Mesto_zamestnanca LIKE "%'. $Employee .'%" OR Ulica_zamestnanca LIKE "%'. $Employee .'%" OR Email LIKE "%'. $Employee .'%")';			
		} else {
			$sql = 'SELECT * 
					FROM zamestnanec 
					WHERE (Meno LIKE "%'. $Employee .'%" OR Priezvisko LIKE "%'. $Employee .'%" OR ID_ZAM LIKE "%'. $Employee .'%" OR Mesto_zamestnanca LIKE "%'. $Employee .'%" OR Ulica_zamestnanca LIKE "%'. $Employee .'%" OR Email LIKE "%'. $Employee .'%") AND ID_TYP_Z='. $mode;			
		}
		
		$query = $this->db->query($sql);		 
		 if($query->num_rows()>0){
			 return $query->result();
		 } else { 
			 return $query->result();
		 }	 
	 }
    
    public function canILogin($id_emp, $startDate, $endDate){
        $sql=   'SELECT inventura.ID_INV
                 FROM inventura                         
                 WHERE (inventura.ID_Veduci='.$id_emp.') AND 
                        (
                        (("'.$startDate.'">=inventura.Datum_zaciatku) AND ("'.$startDate.'"<=inventura.Datum_konca)) OR
                        (("'.$endDate.'">=inventura.Datum_zaciatku) AND ("'.$endDate.'"<=inventura.Datum_konca)) OR
                        (("'.$startDate.'"<=inventura.Datum_zaciatku) AND ("'.$endDate.'">=inventura.Datum_konca))
                        )';
                $query = $this->db->query($sql);
                if ($query->num_rows()==0){
                        return TRUE;
                } else {
                        return FALSE;
                }       
    }
     
     
} 