<?php 

class Model_graph extends CI_Model {	
	
	function __construct()
     {
          parent::__construct();
     }
	 
	 function getSalaryYearGross($id_emp, $year, $month){
		 $sql='SELECT Vyplatene FROM report_zoznam WHERE ID_ZAM='.$id_emp.' AND Koniec LIKE "'.$year.'-'.$month.'%"';
		 $query=$this->db->query($sql)->result();
		 $salary=0;
		 foreach($query as $q){
			$salary=$salary+$q->Vyplatene;	 
		 }
		 return $salary;
	 } 
	 
	 public function getSalaryNet($id_emp, $year, $month){
		 $sql='SELECT Vyplatene FROM report_zoznam WHERE ID_ZAM='.$id_emp.' AND Koniec LIKE "'.$year.'-'.$month.'%"';
		 $query=$this->db->query($sql)->result();
		 $salary=0;
		 foreach($query as $q){
			$salary=$salary+$q->Vyplatene;	 
		 }
		 return round(($salary*0.7209019914),2);
	 }
	
	 public function getCountHoursWorked($id_emp, $year, $month){		 		 
		 $sql='SELECT Nastup, Koniec FROM report_zoznam WHERE ID_ZAM='.$id_emp.' AND Koniec LIKE "'.$year.'-'.$month.'%" AND Vyplatene>0';
		 $query=$this->db->query($sql)->result();
		 $minutesWorked=0;
		 foreach($query as $q){
			 $start=date_create($q->Nastup);
			 $end=date_create($q->Koniec);
			 $date_diff=date_diff($start,$end);
			 $minutesWorked=$minutesWorked+(($date_diff->d*1440)+($date_diff->h*60)+($date_diff->i));
		 }
		 return round($minutesWorked/60,0);
	 }
	 
	 
	  
	 public function getCountCompletedInventories($id_emp, $year, $month){
		 $sql='SELECT ID_REP FROM report_zoznam WHERE ID_ZAM='.$id_emp.' AND Koniec LIKE "'.$year.'-'.$month.'%"';
		 return $this->db->query($sql)->num_rows();	 	 
	 }
	 
	 public function getCountCompletedKilometer($id_emp, $year, $month){
		 $sql='SELECT Vzdialenost FROM report_zoznam
		 	   LEFT JOIN inventura ON report_zoznam.ID_REP=inventura.ID_Report
			   WHERE ID_ZAM='.$id_emp.' AND Koniec LIKE "'.$year.'-'.$month.'%"';
		 $query=$this->db->query($sql)->result();
		 $range=0;
		 foreach($query as $q){
			$range=$range+$q->Vzdialenost;	 
		 }
		 return $range;
	 }
	 
	 public function getCountPenaltyEmp($id_emp, $year, $month){
		 $sql = "SELECT COUNT(ID_ZAM) AS Pocet FROM report_zoznam WHERE ID_ZAM=".$id_emp.' AND Vyplatene<0 AND Koniec LIKE "'.$year.'-'.$month.'%"';
	 	 return $this->db->query($sql)->row()->Pocet;
	 }
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////	 
//LEAD EMPLOYEEE
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

public function getSalaryYearGrossLead($id_emp, $year, $month){
	$sql='SELECT Vyplatene FROM report_zoznam WHERE ID_ZAM='.$id_emp.' AND Koniec LIKE "'.$year.'-'.$month.'%"';	
	
	$query=$this->db->query($sql)->result();
	$salary=0;
	
	foreach($query as $q){
	$salary=$salary+$q->Vyplatene;	 
	}
	
	return $salary;
}

public function getCountHoursWorkedLead($id_emp, $year, $month){
	$sql='SELECT Nastup, Koniec FROM report_zoznam WHERE ID_ZAM='.$id_emp.' AND Koniec LIKE "'.$year.'-'.$month.'%"  AND Vyplatene>0';
	$query=$this->db->query($sql)->result();
	$minutesWorked=0;
	foreach($query as $q){
		$start=date_create($q->Nastup);
		$end=date_create($q->Koniec);
		$date_diff=date_diff($start,$end);
		$minutesWorked=$minutesWorked+(($date_diff->d*1440)+($date_diff->h*60)+($date_diff->i));
	}
	return round($minutesWorked/60,0);
}

public function getCountCompletedInventoriesLead($id_emp, $year, $month){
	$sql='SELECT ID_REP FROM report_zoznam WHERE ID_ZAM='.$id_emp.' AND Koniec LIKE "'.$year.'-'.$month.'%"';
	return $this->db->query($sql)->num_rows();	 	 
}

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////	 
//LEAD EMPLOYEEE
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////


public function getSalaryYearGrossDriver($id_emp, $year, $month){
	$sql='SELECT Vyplatene FROM report_zoznam WHERE ID_ZAM='.$id_emp.' AND Koniec LIKE "'.$year.'-'.$month.'%"';	
	
	$query=$this->db->query($sql)->result();
	$salary=0;
	
	foreach($query as $q){
	$salary=$salary+$q->Vyplatene;	 
	}
	
	return $salary;
}

public function getCountHoursWorkedDriver($id_emp, $year, $month){
	$sql='SELECT Nastup, Koniec FROM report_zoznam WHERE ID_ZAM='.$id_emp.' AND Koniec LIKE "'.$year.'-'.$month.'%"  AND Vyplatene>0';
	$query=$this->db->query($sql)->result();
	$minutesWorked=0;
	foreach($query as $q){
		$start=date_create($q->Nastup);
		$end=date_create($q->Koniec);
		$date_diff=date_diff($start,$end);
		$minutesWorked=$minutesWorked+(($date_diff->d*1440)+($date_diff->h*60)+($date_diff->i));
	}
	return round($minutesWorked/60,0);
}

public function getCountCompletedInventoriesDriver($id_emp, $year, $month){
	$sql='SELECT ID_REP FROM report_zoznam WHERE ID_ZAM='.$id_emp.' AND Koniec LIKE "'.$year.'-'.$month.'%"';
	return $this->db->query($sql)->num_rows();	 	 
}

//MANAGER

public function getSalaryYearGrossManager($year, $month){
	$sql = 'SELECT Vyplatene FROM report_zoznam WHERE Koniec LIKE "'.$year.'-'.$month.'%"';
	$query=$this->db->query($sql)->result();
	$generalSalary=0;
	foreach($query as $q){
		$generalSalary=$generalSalary+$q->Vyplatene;	
	}
	return $generalSalary;
}

public function getSalaryYearNetManager($year, $month){
	$sql = 'SELECT Vyplatene FROM report_zoznam WHERE Koniec LIKE "'.$year.'-'.$month.'%"';
	$query=$this->db->query($sql)->result();
	$generalSalary=0;
	foreach($query as $q){
		$generalSalary=$generalSalary+$q->Vyplatene;	
	}
	return round(($generalSalary*0.7209019914),2);
}

public function getCountCompletedInventoriesManager($year, $month){
	$sql='SELECT ID_REP FROM report_zoznam LEFT JOIN inventura ON report_zoznam.ID_REP=inventura.ID_Report WHERE Koniec LIKE "'.$year.'-'.$month.'%" GROUP BY ID_INV';
	return $this->db->query($sql)->num_rows();	
}

public function getCountHoursWorkedManager($year, $month){
	$sql='SELECT Nastup, Koniec FROM report_zoznam WHERE Koniec LIKE "'.$year.'-'.$month.'%"  AND Vyplatene>0';
	$query=$this->db->query($sql)->result();
	$minutesWorked=0;
	foreach($query as $q){
		$start=date_create($q->Nastup);
		$end=date_create($q->Koniec);
		$date_diff=date_diff($start,$end);
		$minutesWorked=$minutesWorked+(($date_diff->d*1440)+($date_diff->h*60)+($date_diff->i));
	}
	return round($minutesWorked/60,0);	
}

public function getTopSalaryLeadEmployees($year, $limit){
	$sql = 'SELECT zamestnanec.ID_ZAM, zamestnanec.Meno, zamestnanec.Priezvisko , SUM(Vyplatene) AS VyplateneCelkom 
			FROM report_zoznam 
			LEFT JOIN zamestnanec ON report_zoznam.ID_ZAM=zamestnanec.ID_ZAM
			WHERE zamestnanec.ID_TYP_Z=2 AND report_zoznam.Koniec LIKE "'.$year.'%"
			GROUP BY ID_ZAM 
			ORDER BY VyplateneCelkom DESC
			LIMIT '.$limit;
	return $this->db->query($sql)->result();
}

public function getTopSalaryDrivers($year, $limit){
	$sql = 'SELECT zamestnanec.ID_ZAM, zamestnanec.Meno, zamestnanec.Priezvisko , SUM(Vyplatene) AS VyplateneCelkom 
			FROM report_zoznam 
			LEFT JOIN zamestnanec ON report_zoznam.ID_ZAM=zamestnanec.ID_ZAM
			WHERE zamestnanec.ID_TYP_Z=3 AND report_zoznam.Koniec LIKE "'.$year.'%"
			GROUP BY ID_ZAM 
			ORDER BY VyplateneCelkom DESC
			LIMIT '.$limit;
	return $this->db->query($sql)->result();
}

public function getTopSalaryEmployees($year, $limit){
	$sql = 'SELECT zamestnanec.ID_ZAM, zamestnanec.Meno, zamestnanec.Priezvisko , SUM(Vyplatene) AS VyplateneCelkom 
			FROM report_zoznam 
			LEFT JOIN zamestnanec ON report_zoznam.ID_ZAM=zamestnanec.ID_ZAM
			WHERE zamestnanec.ID_TYP_Z=4 AND report_zoznam.Koniec LIKE "'.$year.'%"
			GROUP BY ID_ZAM 
			ORDER BY VyplateneCelkom DESC
			LIMIT '.$limit;
	return $this->db->query($sql)->result();
}

public function getTopHoursVehicles($year, $limit){
      
}

public function getTopKilometersVehicles($year, $limit){
 	$sql ='SELECT rezervacia.ID_VOZ,vozidlo.SPZ, vozidlo.Nazov, vozidlo.Pocet_miest, SUM(inventura.Vzdialenost) AS CelkovaVzdialenost FROM rezervacia
		   LEFT JOIN inventura ON rezervacia.ID_INV=inventura.ID_INV
		   LEFT JOIN vozidlo ON rezervacia.ID_VOZ=vozidlo.ID_VOZ
		   WHERE inventura.Datum_konca LIKE "'.$year.'%"
		   GROUP BY rezervacia.ID_VOZ
		   ORDER BY CelkovaVzdialenost DESC
		   LIMIT '.$limit;
	return $this->db->query($sql)->result();      
}
    
public function getTopCompleteInventoriesVehicles($year, $limit){
	$sql ='SELECT rezervacia.ID_VOZ,vozidlo.SPZ, COUNT(rezervacia.ID_INV) AS CelkoveInventury FROM rezervacia 
		   LEFT JOIN inventura ON rezervacia.ID_INV=inventura.ID_INV
		   LEFT JOIN vozidlo ON rezervacia.ID_VOZ=vozidlo.ID_VOZ
		   WHERE inventura.Datum_konca LIKE "'.$year.'%"
		   GROUP BY rezervacia.ID_VOZ
		   ORDER BY CelkoveInventury DESC
		   LIMIT '.$limit;
    return $this->db->query($sql)->result();
} 

public function getSalaryYearGrossDriverAsDriver($id_emp , $year, $month){
	$sql = 'SELECT report_zoznam.Vyplatene
			FROM rezervacia
			LEFT JOIN inventura ON rezervacia.ID_INV=inventura.ID_INV
			LEFT JOIN report_zoznam ON inventura.ID_Report=report_zoznam.ID_REP
			WHERE report_zoznam.ID_ZAM='.$id_emp.' AND rezervacia.ID_SOF='.$id_emp.' AND inventura.Datum_konca LIKE "'.$year.'-'.$month.'%"';
			$query=$this->db->query($sql)->result();
			$salary=0;
			
			foreach($query as $q){
			$salary=$salary+$q->Vyplatene;	 
			}
			
			return $salary;
}

public function getSalaryNetDriverAsDriver($id_emp , $year, $month){
	$sql = 'SELECT report_zoznam.Vyplatene
			FROM rezervacia
			LEFT JOIN inventura ON rezervacia.ID_INV=inventura.ID_INV
			LEFT JOIN report_zoznam ON inventura.ID_Report=report_zoznam.ID_REP
			WHERE report_zoznam.ID_ZAM='.$id_emp.' AND rezervacia.ID_SOF='.$id_emp.' AND inventura.Datum_konca LIKE "'.$year.'-'.$month.'%"';
			$query=$this->db->query($sql)->result();
			$salary=0;
			
			foreach($query as $q){
			$salary=$salary+$q->Vyplatene;	 
			}
			
			return round(($salary*0.7209019914),2);
}

public function getCountHoursWorkedDriverAsDriver($id_emp, $year, $month){		 		 
	$sql = 'SELECT report_zoznam.Nastup, report_zoznam.Koniec
			FROM rezervacia
			LEFT JOIN inventura ON rezervacia.ID_INV=inventura.ID_INV
			LEFT JOIN report_zoznam ON inventura.ID_Report=report_zoznam.ID_REP
			WHERE report_zoznam.ID_ZAM='.$id_emp.' AND rezervacia.ID_SOF='.$id_emp.' AND inventura.Datum_konca LIKE "'.$year.'-'.$month.'%"  AND Vyplatene>0';
		$query=$this->db->query($sql)->result();
		 $minutesWorked=0;
		 foreach($query as $q){
			 $start=date_create($q->Nastup);
			 $end=date_create($q->Koniec);
			 $date_diff=date_diff($start,$end);
			 $minutesWorked=$minutesWorked+(($date_diff->d*1440)+($date_diff->h*60)+($date_diff->i));
		 }
		 return round($minutesWorked/60,0);
}

public function getCountCompletedInventoriesDriverAsDriver($id_emp, $year, $month){		 		 
	$sql = 'SELECT report_zoznam.Nastup, report_zoznam.Koniec
			FROM rezervacia
			LEFT JOIN inventura ON rezervacia.ID_INV=inventura.ID_INV
			LEFT JOIN report_zoznam ON inventura.ID_Report=report_zoznam.ID_REP
			WHERE report_zoznam.ID_ZAM='.$id_emp.' AND rezervacia.ID_SOF='.$id_emp.' AND inventura.Datum_konca LIKE "'.$year.'-'.$month.'%"';
			return $this->db->query($sql)->num_rows();	
}

function getCountCompletedKilometerDriverAsDriver($id_emp, $year, $month){
     $sql = 'SELECT inventura.Vzdialenost
			FROM rezervacia
			LEFT JOIN inventura ON rezervacia.ID_INV=inventura.ID_INV
			LEFT JOIN report_zoznam ON inventura.ID_Report=report_zoznam.ID_REP
			WHERE report_zoznam.ID_ZAM='.$id_emp.' AND rezervacia.ID_SOF='.$id_emp.' AND inventura.Datum_konca LIKE "'.$year.'-'.$month.'%"';
			$query=$this->db->query($sql)->result();
			$range=0;
			foreach($query as $q){
				$range=$range+$q->Vzdialenost;	 
			}
			return $range; 
} 

public function getSalaryYearGrossDriverAsEmployee($id_emp , $year, $month){
	$sql = 'SELECT report_zoznam.Vyplatene FROM zoznam_prihlasenych
			LEFT JOIN inventura ON zoznam_prihlasenych.ID_INV=inventura.ID_INV 
			LEFT JOIN report_zoznam ON report_zoznam.ID_REP=inventura.ID_Report
			WHERE zoznam_prihlasenych.ID_ZAM='.$id_emp.' AND report_zoznam.ID_ZAM='.$id_emp.' AND inventura.Datum_konca LIKE "'.$year.'-'.$month.'%"';
			$query=$this->db->query($sql)->result();
			$salary=0;
			
			foreach($query as $q){
			$salary=$salary+$q->Vyplatene;	 
			}
			
			return $salary;
} 

public function getSalaryNetDriverAsEmployee($id_emp , $year, $month){
	$sql = 'SELECT report_zoznam.Vyplatene FROM zoznam_prihlasenych
			LEFT JOIN inventura ON zoznam_prihlasenych.ID_INV=inventura.ID_INV 
			LEFT JOIN report_zoznam ON report_zoznam.ID_REP=inventura.ID_Report
			WHERE zoznam_prihlasenych.ID_ZAM='.$id_emp.' AND report_zoznam.ID_ZAM='.$id_emp.' AND inventura.Datum_konca LIKE "'.$year.'-'.$month.'%"';
			$query=$this->db->query($sql)->result();
			$salary=0;
			
			foreach($query as $q){
			$salary=$salary+$q->Vyplatene;	 
			}
			
			return round(($salary*0.7209019914),2);
}

public function getCountHoursWorkedDriverAsEmployee($id_emp, $year, $month){		 		 
	$sql = 'SELECT report_zoznam.Nastup, report_zoznam.Koniec FROM zoznam_prihlasenych
			LEFT JOIN inventura ON zoznam_prihlasenych.ID_INV=inventura.ID_INV 
			LEFT JOIN report_zoznam ON report_zoznam.ID_REP=inventura.ID_Report
			WHERE zoznam_prihlasenych.ID_ZAM='.$id_emp.' AND report_zoznam.ID_ZAM='.$id_emp.' AND inventura.Datum_konca LIKE "'.$year.'-'.$month.'%" AND Vyplatene>0';
			$query=$this->db->query($sql)->result();
		 	$minutesWorked=0;
			foreach($query as $q){
				$start=date_create($q->Nastup);
				$end=date_create($q->Koniec);
				$date_diff=date_diff($start,$end);
				$minutesWorked=$minutesWorked+(($date_diff->d*1440)+($date_diff->h*60)+($date_diff->i));
			}
			return round($minutesWorked/60,0);
} 

public function getCountCompletedInventoriesDriverAsEmployee($id_emp, $year, $month){		 		 
	$sql = 'SELECT report_zoznam.Nastup, report_zoznam.Koniec FROM zoznam_prihlasenych
			LEFT JOIN inventura ON zoznam_prihlasenych.ID_INV=inventura.ID_INV 
			LEFT JOIN report_zoznam ON report_zoznam.ID_REP=inventura.ID_Report
			WHERE zoznam_prihlasenych.ID_ZAM='.$id_emp.' AND report_zoznam.ID_ZAM='.$id_emp.' AND inventura.Datum_konca LIKE "'.$year.'-'.$month.'%"';
			return $this->db->query($sql)->num_rows();	
} 

public function getCountCompletedKilometerDriverAsEmployee($id_emp, $year, $month){
     $sql = 'SELECT inventura.Vzdialenost FROM zoznam_prihlasenych
			LEFT JOIN inventura ON zoznam_prihlasenych.ID_INV=inventura.ID_INV 
			LEFT JOIN report_zoznam ON report_zoznam.ID_REP=inventura.ID_Report
			WHERE zoznam_prihlasenych.ID_ZAM='.$id_emp.' AND report_zoznam.ID_ZAM='.$id_emp.' AND inventura.Datum_konca LIKE "'.$year.'-'.$month.'%"';
			$query=$this->db->query($sql)->result();
			$range=0;
			foreach($query as $q){
				$range=$range+$q->Vzdialenost;	 
			}
			return $range; 
}


     
}