<?php

//require_once("class.conectar.php");

//class Licencia extends Conectar
class Licencia
{
	//private $visitas=array();
	private $lis_lic_a=array();
	private $lis_lic_p=array();
	private $lis_lic_per=array();
	private $lis_f_lic_per=array();
	private $lis_lic_tipo=array();
	private $bus_lic_tipo=array();
	private $lis_lic_p_am=array();
	private $idlic;
	private $cant_lic_hrs;
	
//	public function __construct()
	//	{
		//	$this->visitas=array();
		//}
	public function buscar_id_licencia($con,$ugid)
	{
		$sql="select lc_id from tb_licencias where ug_id='$ugid' order by lc_id desc limit 1";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->idlic=$reg['lc_id'];
		}
			return $this->idlic;
	}
	
	public function consultar_licencia_anterior($con,$ugid, $fechaini)
	{
		$sql="select * from tb_licencias where ug_id='$ugid' and date('$fechaini') between date(lc_fechaini) and date(lc_fechafin)";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_lic_a[]=$reg;
		}
			return $this->lis_lic_a;
	}
	
	public function consultar_licencia_posterior($con,$ugid, $fechafin)
	{
		$sql="select * from tb_licencias where ug_id='$ugid' and date('$fechafin') between date(lc_fechaini) and date(lc_fechafin)";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_lic_p[]=$reg;
		}
			return $this->lis_lic_p;
	}
	
		public function consultar_licencia_anterior_id($con,$ugid, $fechaini,$numid)
	{
		$sql="select * from tb_licencias where ug_id='$ugid' and '$fechaini' between lc_fechaini and lc_fechafin and lc_id not in ('$numid')";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_lic_a[]=$reg;
		}
			return $this->lis_lic_a;
	}
	
	public function consultar_licencia_posterior_id($con,$ugid, $fechafin,$numid)
	{
		$sql="select * from tb_licencias where ug_id='$ugid' and '$fechafin' between lc_fechaini and lc_fechafin and lc_id not in ('$numid')";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_lic_p[]=$reg;
		}
			return $this->lis_lic_p;
	}
	public function insert_licencia_prog($con,$numid,$idtlic,$ugid,$fechaini,$fechafin,$numdias)
	{
		$sql="insert into tb_licencias values('$numid','$idtlic','$ugid','$fechaini','$fechafin','$numdias')";
		$res=mysqli_query($con, $sql);
	}
	
	public function consultar_licencia_personal($con,$ugid)
	{
		$sql="select lc_id as id, concat(date_format(lc_fechaini,'%d/%m/%Y'),' - ', date_format(lc_fechafin,'%d/%m/%Y'),' (', lc_numdias,')') ";
		$sql= $sql." as dias from tb_licencias where ug_id='$ugid' order by lc_fechaini desc"; 
		//echo $sql;
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_lic_per[]=$reg;
		}
			return $this->lis_lic_per;
	}
	
	public function consultar_fechas_licencia_personal($con,$ugid,$numid) 
	{
		$sql="select date_format(lc_fechaini,'%d/%m/%Y') as fechaini, date_format(lc_fechafin,'%d/%m/%Y') as fechafin from tb_licencias where ug_id='$ugid' and lc_id='$numid'";
			//echo $sql;
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_f_lic_per[]=$reg;
		}
			return $this->lis_f_lic_per;
	}
	
	public function modificar_fechas_licencia($con,$fechaini, $fechafin, $numdias, $numid, $ugid, $idtlic)
	{
		$sql="update tb_licencias set lc_fechaini='$fechaini', lc_fechafin='$fechafin', lc_numdias='$numdias', tlc_id='$idtlic' where lc_id='$numid' and ug_id='$ugid'";
		$res=mysqli_query($con, $sql);
	}
	
	public function listar_tipo_licencia($con) 
	{
		$sql="select * from tb_tplicencia";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_lic_tipo[]=$reg;
		}
			return $this->lis_lic_tipo;
	}
	
	public function buscar_tipo_licencia($con,$idtlic) 
	{
		$sql="select * from tb_tplicencia where tlc_id='$idtlic'";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->bus_lic_tipo[]=$reg;
		}
			return $this->bus_lic_tipo;
	}
	
	public function consultar_licencia_hrssusp($con,$codper,$idtp,$anno,$mm,$thora) 
	{
		$sql="SELECT sum(b.h_horanum) as numhrs FROM tb_horasusp_lic a inner join tb_horario b on a.h_id=b.h_id where a.tp_id='$idtp' and a.ug_id='$codper' and a.uap_anno='$anno' and a.uap_mes='$mm' and a.hl_tipohora='$thora'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->cant_lic_hrs=$reg['numhrs'];
		}
			return $this->cant_lic_hrs;
	}
	
		public function listar_licencia_per_anno_mes($con,$codper,$anno,$mm) 
	{
		$sql="select a.lc_id as id, (select tlc_desc from tb_tplicencia where tlc_id=a.tlc_id) as tipolic, concat(date_format(a.lc_fechaini,'%d/%m/%Y'),' AL ', date_format(a.lc_fechafin,'%d/%m/%Y')) as fecha from tb_licencias a where ((year(a.lc_fechaini)='$anno' and month(a.lc_fechaini)='$mm') or (year(a.lc_fechafin)='$anno' and month(a.lc_fechafin)='$mm')) and a.ug_id='$codper'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_lic_p_am[]=$reg;
		}
			return $this->lis_lic_p_am;
	}

}
?>