<?php

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

//class UsuarioDependencia extends Conectar
class UsuarioDependencia
{
	
	private $lis_per_dep=array();
	private $lis_per_dep_esp_tp=array();
	private $lis_per_dep_tp=array();
	private $lis_per_dep_esp=array();
	private $lis_per_dep_p=array();
	
		
	public function buscar_per_dep($con,$ugid)
	{
		$sql="SELECT a.ug_id as ugid, b.tp_id as idtp, b.s_id as ids, b.j_id as idj  FROM tb_usuasist a inner join tb_usudep b on a.ug_id=b.ug_id where a.ug_id='$ugid'";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_per_dep[]=$reg;
		}
			return $this->lis_per_dep;
	}
	
	public function buscar_per_dep_programado($con,$ugid,$anno,$mes)
	{
		$sql="SELECT distinct(j_id) as idj, s_id as ids, tp_id as idtp, ug_id as ugid FROM tb_usutrab WHERE ug_id =  '$ugid' AND uap_anno =  '$anno' AND uap_mes =  '$mes'";
		//echo $sql;
		//$sql="select ug_id as ugid, tp_id as idtp, s_id as ids, j_id as idj from tb_usutrab where ug_id='$ugid' and uap_anno='$anno' and uap_mes='$mes' and ut_idserva='$ids' and j_id='$idj'";
		//echo $sql;
		//$sql="SELECT a.ug_id as ugid, b.tp_id as idtp, b.s_id as ids, b.j_id as idj  FROM tb_usuasist a inner join tb_usudep b on a.ug_id=b.ug_id where a.ug_id='$ugid'";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_per_dep_p[]=$reg;
		}
			return $this->lis_per_dep_p;
	}
	
		
	//buscar por codigo del personal
	public function buscar_cod_per($con,$ugid)
	{	
		$sql = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select esp_desc from tb_especialidad where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona,b.ua_estado as estado,b.ud_id as id from tb_usuasist a inner join tb_usudep b on a.ug_id = b.ug_id where b.ug_id='$ugid' and b.ua_estado in (1,2,4)  order by nombre, especialidad";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_per_dep[]=$reg;
		}
			return $this->lis_per_dep;
	}
	
	//dependencia del servicio
	public function listar_per_dep($con,$idj,$ids)
	{	
		$sql = "select a.ug_id as cod, a.ua_numplanilla as numplanilla, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select esp_desc from tb_especialidad where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona,b.ua_estado as estado,b.ud_id as id, month(b.ud_fechaing) as mesing, b.tc_idcont as idtopcont from tb_usuasist a inner join tb_usudep b on a.ug_id = b.ug_id where b.j_id='$idj' and b.s_id='$ids' and (b.ua_estado= 1 or b.ua_estado= 2 or b.ua_estado= 4) order by nombre, especialidad";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_per_dep[]=$reg;
		}
			return $this->lis_per_dep;
	}
	
	//dependencia del servicio y el tipo de contrato
	public function listar_per_dep_c($con,$idj,$ids,$tcont)
	{	
		$sql = "select a.ug_id as cod, a.ua_numplanilla as numplanilla, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select esp_desc from tb_especialidad where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona,b.ua_estado as estado,b.ud_id as id, month(b.ud_fechaing) as mesing, b.tc_idcont as idtopcont from tb_usuasist a inner join tb_usudep b on a.ug_id = b.ug_id where b.j_id='$idj' and b.s_id='$ids' and b.tc_idcont='$tcont' and (b.ua_estado= 1 or b.ua_estado= 2 or b.ua_estado= 4) order by nombre, especialidad";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_per_dep[]=$reg;
		}
			return $this->lis_per_dep;
	}
	
	public function listar_per_dep_esp_tp($con,$idj,$ids,$idtp,$idesp)
	{	
		$sql = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select esp_desc from tb_especialidad where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo,(select tc_abrev from tb_tipocont where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona from tb_usuasist a inner join tb_usudep b on a.ug_id = b.ug_id where b.j_id='$idj' and b.s_id='$ids'  and b.tp_id='$idtp' and b.ua_idespec='$idesp' and (b.ua_estado= 1 or b.ua_estado= 2 or b.ua_estado= 4) order by nombre, especialidad";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_per_dep_esp_tp[]=$reg;
		}
			return $this->lis_per_dep_esp_tp;
	}
	
	public function listar_per_dep_esp_tp_c($con,$idj,$ids,$idtp,$idesp,$tcont)
	{	
		$sql = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select esp_desc from tb_especialidad where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo,(select tc_abrev from tb_tipocont where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona from tb_usuasist a inner join tb_usudep b on a.ug_id = b.ug_id where b.j_id='$idj' and b.s_id='$ids'  and b.tp_id='$idtp' and b.ua_idespec='$idesp' and b.tc_idcont='$tcont' and (b.ua_estado= 1 or b.ua_estado= 2 or b.ua_estado= 4) order by nombre, especialidad";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_per_dep_esp_tp[]=$reg;
		}
			return $this->lis_per_dep_esp_tp;
	}
	//dependencia del servicio y el tipo de personal
	public function listar_per_dep_tp($con,$idj,$ids,$idtp)
	{	
		$sql = "select a.ug_id as cod, a.ua_numplanilla as numplanilla, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select esp_desc from tb_especialidad where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo,(select tc_abrev from tb_tipocont where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona, month(b.ud_fechaing) as mesing, b.tc_idcont as idtopcont from tb_usuasist a inner join tb_usudep b on a.ug_id = b.ug_id where b.j_id='$idj' and b.s_id='$ids'  and b.tp_id='$idtp' and (b.ua_estado= 1  or b.ua_estado= 2 or b.ua_estado= 4) order by nombre, especialidad";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_per_dep_tp[]=$reg;
		}
			return $this->lis_per_dep_tp;
	}
	//dependencia del servicio, tipo de contrato y tipo de personal
	public function listar_per_dep_tp_c($con,$idj,$ids,$idtp,$tcont)
	{	
		$sql = "select a.ug_id as cod, a.ua_numplanilla as numplanilla, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select esp_desc from tb_especialidad where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo,(select tc_abrev from tb_tipocont where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona, month(b.ud_fechaing) as mesing, b.tc_idcont as idtopcont from tb_usuasist a inner join tb_usudep b on a.ug_id = b.ug_id where b.j_id='$idj' and b.s_id='$ids' and b.tc_idcont='$tcont' and b.tp_id='$idtp' and (b.ua_estado= 1  or b.ua_estado= 2 or b.ua_estado= 4) order by nombre, especialidad";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_per_dep_tp[]=$reg;
		}
			return $this->lis_per_dep_tp;
	}
	
	public function listar_per_dep_esp($con,$idj,$ids,$idesp)
	{	
		$sql = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select esp_desc from tb_especialidad where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona from tb_usuasist a inner join tb_usudep b on a.ug_id = b.ug_id where b.j_id='$idj' and b.s_id='$ids' and b.ua_idespec='$idesp' and (b.ua_estado= 1  or b.ua_estado= 2 or b.ua_estado= 4) order by nombre, especialidad";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_per_dep_esp[]=$reg;
		}
			return $this->lis_per_dep_esp;
	}
	
	public function listar_per_dep_esp_c($con,$idj,$ids,$idesp,$tcont)
	{	
		$sql = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select esp_desc from tb_especialidad where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona from tb_usuasist a inner join tb_usudep b on a.ug_id = b.ug_id where b.j_id='$idj' and b.s_id='$ids' and b.ua_idespec='$idesp' and b.tc_idcont='$tcont' and (b.ua_estado= 1  or b.ua_estado= 2 or b.ua_estado= 4) order by nombre, especialidad";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_per_dep_esp[]=$reg;
		}
			return $this->lis_per_dep_esp;
	}
	
	public function listar_per_nombre($con,$idj,$ids,$nombre)
	{	
		$sql = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select esp_desc from tb_especialidad where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona from tb_usuasist a inner join tb_usudep b on a.ug_id = b.ug_id where b.j_id='$idj' and b.s_id='$ids' and  (a.ua_apepat like '$nombre%' or  a.ua_apemat like '$nombre%' or a.ua_nom like '%$nombre%') and (b.ua_estado= 1  or b.ua_estado= 2 or b.ua_estado= 4) order by nombre, especialidad";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_per_dep_esp[]=$reg;
		}
			return $this->lis_per_dep_esp;
	}
	
	public function listar_det_trab_per($con,$ugid)
	{
		$sql="select tp_id as idtp, ud_id as id, ua_idespec as idesp, ua_estado as estado, tc_idcont as idcont, c_id as idc, ud_fechaing as fechaing from tb_usudep where ug_id='$ugid' order by ud_id desc limit 1";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_per_trab[]=$reg;
		}
			return $this->lis_per_trab;
	}
	
	public function registrar_det_trab_per($con,$idtp,$ugid,$ids,$idj,$udid,$fechaing, $fecha,$idesp,$estado,$idcont,$idc)
	{
		$sql="insert into tb_usudep values ('$idtp','$ugid','$ids','$idj','$udid','$fechaing','$fecha','$idesp','$estado','$idcont','$idc')";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		
	}
	
	public function finalizar_trab_dep_per($con,$ugid,$udid,$estado)
	{
		$sql="update tb_usudep set ua_estado='$estado' where ug_id='$ugid' and ud_id='$udid'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		
	}
	
	public function modificar_fecha($con,$ugid,$udid,$fecha)
	{
		$sql="update tb_usudep set ua_fecharot='$fecha' where ug_id='$ugid' and ud_id='$udid'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		
	}
	
	public function modificar_datos_dep_per($con,$ugid,$udid,$idtp,$tipocontrato,$especialidad,$fecha,$idcargo)
	{
		$sql="update tb_usudep set tp_id='$idtp', ud_fechaing='$fecha', ua_idespec='$especialidad', tc_idcont='$tipocontrato',c_id='$idcargo' where ug_id='$ugid' and ud_id='$udid'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		
	}
	
	
	public function registrar_datos_dep_per($con,$ugid,$udid,$idtp,$ids,$idj,$fechaini, $fechafin, $estado, $idcont,$idespc,$idcargo)
	{
		$sql="insert into tb_usudep values('$idtp','$ugid','$ids','$idj','$udid','$fechaini','$fechafin','$idespc','$estado','$idcont','$idcargo')";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		
	}
}
?>