<?php

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

//class UsuarioTrabaja extends Conectar
class UsuarioTrabaja
{

	private $lis_per_trab=array();
	private $cod_perd=array();
	private $lis_usu_trab=array();
	private $lis_per_trab_serv=array();
	private $lis_per_trab_pp=array();
	private $lis_per_trab_np=array();
	private $lis_per_trab_pa_np=array();
	private $cod_per_serv=array();
	private $obs_per_trab;
	private $cod_per;

	public function ask_usu_prog($con,$idj,$ids,$anno,$mm,$idtp)
	{
		$sql="select * from tb_usutrab where j_id='$idj' and s_id='$ids' and uap_anno='$anno' and uap_mes ='$mm' and tp_id='$idtp'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_usu_trab[]=$reg;
		}
			return $this->lis_usu_trab;
	}

	public function buscar_per_trab($con,$ugid,$anno,$mm,$idj,$ids,$idtp)
	{
		$sql="select ug_id as cod, ut_codprog as codp, ";
		$sql= $sql." ut_idserva as idsa, ut_obs as obs from tb_usutrab where j_id='$idj' and tp_id='$idtp' and ut_idserva='$ids' and uap_anno='$anno' and uap_mes ='$mm' and ug_id='$ugid'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_per_trab[]=$reg;
		}
			return $this->lis_per_trab;
	}

	//busca el codigo de un personal programado en un periodo
	public function buscar_per_trab_codper($con,$anno,$mm,$idj,$ids,$idtp,$codprog)
	{
		$sql= "SELECT ug_id as cod FROM tb_usutrab where ut_codprog='$codprog' and tp_id='$idtp' and uap_anno='$anno' and uap_mes='$mm' and j_id='$idj' and ut_idserva='$ids'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->cod_per=$reg['cod'];
		}
			return $this->cod_per;
	}

	//busca el codigo de un personal  Y el servicio de origen
	public function buscar_per_trab_usudep($con,$anno,$mm,$idj,$ids,$idtp,$codprog)
	{
		$sql= "SELECT ug_id, s_id FROM tb_usutrab where ut_codprog='$codprog' and tp_id='$idtp' and uap_anno='$anno' and uap_mes='$mm' and j_id='$idj' and ut_idserva='$ids'";

		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->cod_perd[]=$reg;
		}
			return $this->cod_perd;
	}

	//busca el servicio de origen
	public function buscar_per_trab_serv($con,$anno,$mm,$idj,$ids,$idtp,$codigo)
	{
		$sql= "select s_id as idsa from tb_usutrab where ug_id='$codigo' and tp_id='$idtp' and uap_anno='$anno' and uap_mes ='$mm' and s_id not in('$ids') and j_id='$idj'";

		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->cod_per_serv[]=$reg;
		}
			return $this->cod_per_serv;
	}
	public function ask_obs_per($con,$idj,$ids,$idtp,$idso,$anno,$mm,$ugid)
	{
		$sql="select ut_obs as obs from tb_usutrab where j_id='$idj' and tp_id='$idtp' and  s_id='$ids' and ut_idserva='$idso' and uap_anno='$anno' and uap_mes ='$mm' and ug_id='$ugid'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->obs_per_trab=$reg['obs'];
		}
			return $this->obs_per_trab;

	}

	public function ask_obs_per_reprog($con,$idj,$ids,$idtp,$idso,$anno,$mm,$ugid)
	{
		$sql="select ut_obs2 as obs from tb_usutrab where j_id='$idj' and tp_id='$idtp' and  s_id='$ids' and ut_idserva='$idso' and uap_anno='$anno' and uap_mes ='$mm' and ug_id='$ugid'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->obs_per_trab=$reg['obs'];
		}
			return $this->obs_per_trab;

	}

	public function update_obs_per($con,$idj,$idtp,$idso,$anno,$mm,$ugid,$obs)
	{
		$sql="update tb_usutrab set ut_obs= upper('$obs') where ug_id='$ugid' and uap_anno='$anno' and uap_mes='$mm' and j_id='$idj' and ut_idserva='$idso' and tp_id='$idtp'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);

	}

	public function update_obs2_per($con,$idj,$idtp,$idso,$anno,$mm,$ugid,$obs)
	{
		$sql="update tb_usutrab set ut_obs2= upper('$obs') where ug_id='$ugid' and uap_anno='$anno' and uap_mes='$mm' and j_id='$idj' and ut_idserva='$idso' and tp_id='$idtp'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);

	}

	public function update_codprog_per($con,$codprog,$idj,$ids,$idtp,$idso,$anno,$mm,$ugid)
	{
		$sql="update tb_usutrab set ut_codprog='$codprog' where ug_id='$ugid' and uap_anno='$anno' and uap_mes='$mm' and s_id='$ids' and j_id='$idj' and tp_id='$idtp' and ut_idserva='$idso'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);

	}

	//lista del personal pertenece a una jefatura programado en un periodo
	public function listar_per_trab_per($con,$anno,$mm,$idj,$ids,$idso,$idtp)
	{
		$sql="select a.ug_id as cod, b.ut_codprog as codigo, CONCAT(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, b.ut_idserva as idsa, b.ut_obs as obs from tb_usutrab b  inner join tb_usuasist a on a.ug_id= b.ug_id where b.j_id='$idj' and b.tp_id='$idtp' and b.s_id='$ids' and b.ut_idserva='$idso' and b.uap_anno='$anno' and b.uap_mes ='$mm'  order by nombre";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_per_trab_pp[]=$reg;
		}
			return $this->lis_per_trab_pp;
	}

	//lista del personal que no pertenece a la jefatura programado en un periodo
	public function listar_per_trab_per_pa($con,$anno,$mm,$idj,$ids,$idso,$idtp)
	{
		$sql="select a.ug_id as cod, b.ut_codprog as codigo, CONCAT(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, b.ut_idserva as idsa, b.ut_obs as obs from tb_usutrab b  inner join tb_usuasist a on a.ug_id= b.ug_id where b.j_id='$idj' and b.tp_id='$idtp' and b.s_id not in('$ids') and b.ut_idserva='$idso' and b.uap_anno='$anno' and b.uap_mes ='$mm' and a.ua_estado not in (0) order by nombre";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_per_trab_pp[]=$reg;
		}
			return $this->lis_per_trab_pp;
	}

		//lista del personal no programado en un periodo
	public function listar_per_trab_per_np($con,$anno,$mm,$idj,$ids,$idso,$idtp)
	{
		$sql="select a.ug_id as cod, a.ua_codprog as codigo, CONCAT(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, b.s_id as ids from tb_usudep b  inner join tb_usuasist a on a.ug_id= b.ug_id where b.j_id='$idj' and b.s_id='$ids' and b.ug_id not in (select ug_id from tb_usutrab where j_id=b.j_id and s_id=b.s_id and tp_id=b.tp_id and ut_idserva='$idso' and uap_anno='$anno' and uap_mes ='$mm') and b.tp_id='$idtp' and b.ua_estado not in (0) order by nombre";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_per_trab_np[]=$reg;
		}
			return $this->lis_per_trab_np;
	}

		//lista del personal no programado en un periodo
	public function listar_per_trab_per_pa_np($con,$anno,$mm,$idj,$ids,$idso,$idtp)
	{
			$sql="select a.ug_id as cod, b.ut_codprog as codigo, CONCAT(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, b.ut_idserva as idsa from tb_usutrab b  inner join tb_usuasist a on a.ug_id= b.ug_id where b.j_id='$idj' and b.s_id not in ($ids) and b.tp_id='$idtp' and b.ut_idserva='$idso' and b.uap_anno='$anno' and b.uap_mes ='$mm' order by nombre";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_per_trab_pa_np[]=$reg;
		}
			return $this->lis_per_trab_pa_np;
	}

	public function listar_per_trab_per_serv($con,$anno,$mm,$idj,$ids,$idtp)
	{
		$sql="select a.ug_id as cod, b.ut_codprog as codigo, CONCAT(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, ";
		$sql= $sql." b.ut_idserva as idsa, b.ut_obs as obs, b.ut_obs2 as obs2  from tb_usutrab b  inner join tb_usuasist a on a.ug_id= b.ug_id where b.j_id='$idj'";
		$sql= $sql." and b.tp_id='$idtp' and b.ut_idserva='$ids' and b.uap_anno='$anno' and b.uap_mes ='$mm' order by nombre";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_per_trab_serv[]=$reg;
		}
			return $this->lis_per_trab_serv;
	}

	//ingresar el lugra donde va elaborar un periodo el personal
	public function reg_usu_trab_per($con,$ugid,$anno,$mm,$idj,$ids,$idtp,$idso)
	{
		$sql="insert into tb_usutrab values ('','$idtp','$ugid','$anno','$mm','$ids','$idj','','','$idso')";
		$res=mysqli_query($con, $sql);

	}

	public function elimina_usu_trab_per($con,$ugid,$anno,$mm,$idj,$ids,$idtp,$idso)
	{
		$sql="delete from tb_usutrab where ug_id='$ugid' and uap_anno='$anno' and uap_mes='$mm' and s_id='$ids' and j_id='$idj' and tp_id='$idtp' and ut_idserva='$idso'";
		$res=mysqli_query($con, $sql);

	}


}
?>
