<?php

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

class HoraProgramacion

//class HoraProgramacion extends Conectar
{
	
	private $lis_hr=array();
	private $lis_hr1=array();
	private $lis_hr2=array();
	private $lis_hr3=array();
	private $lis_hr_dia=array();
	private $lis_hr_dia_th=array();
	private $lis_hr_dia_sc=array();
	private $lis_hr_gd=array();
	private $ask_sep_dias=array();
	private $ask_shrs_prog=array();
	private $ask_shrs_prog_sinth=array();
	private $ask_cturn_prog =array();
	private $num_gd;
		
	//consulta turnos programados en el dia
	public function consulta_hrprog_dia($con,$anno,$mm,$idj,$ids,$idsa,$idtp,$idh,$dia,$idga,$ida,$codper)
	{
		$sql="select * from tb_horaprog".$anno." where hp_dia='$dia' and h_id='$idh' and a_id='$ida' and ga_id='$idga' and tp_id='$idtp' and uap_anno='$anno' and uap_mes='$mm' and s_id='$ids' and j_id='$idj' and hp_idserva='$idsa' and ug_id='$codper'";
		//ECHO $sql."<BR>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_hr_dia[]=$reg;
		}
			return $this->lis_hr_dia;
	}
	public function consulta_hrprog_dia_tipohr($con,$anno,$mm,$idj,$ids,$idsa,$idtp,$idh,$dia,$idga,$ida,$codper,$tipohora)
	{
		$sql="select * from tb_horaprog".$anno." where hp_dia='$dia' and h_id='$idh' and a_id='$ida' and ga_id='$idga' and tp_id='$idtp' and uap_anno='$anno' and uap_mes='$mm' and s_id='$ids' and j_id='$idj' and hp_idserva='$idsa' and ug_id='$codper' and hp_tipohora='$tipohora'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		//print_r($res);
        if(count($res)>0){
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_hr_dia_th[]=$reg;
		}
			return $this->lis_hr_dia_th;
        }
	}
	public function consulta_hrprog_dia_sin_cod($con,$anno,$mm,$idj,$ids,$idtp,$idh,$dia,$idga,$ida)
	{
		$sql="select ut_codprog as codprog , hp_tipohora as thora from tb_horaprog".$anno." where j_id='$idj' and hp_idserva='$ids' and uap_mes='$mm' and uap_anno='$anno'and hp_dia='$dia' and h_id='$idh'and ga_id='$idga' and a_id='$ida' and tp_id='$idtp'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_hr_dia_sc[]=$reg;
		}
			return $this->lis_hr_dia_sc;
	}
	
	public function elimina_hrprog_dia($con,$anno,$mm,$idj,$idsa,$idtp,$idh,$dia,$idga,$ida)
	{
		$sql="delete from tb_horaprog".$anno." where hp_dia='$dia' and h_id='$idh' and a_id='$ida' and ga_id='$idga' and tp_id='$idtp' and uap_anno='$anno' and uap_mes='$mm' and j_id='$idj' and hp_idserva='$idsa'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		
	}
	
	public function elimina_hrprog_mayor_fecha($con,$ugid,$fecha)
	{
		$sql="delete from tb_horaprog".$anno." where ug_id='$ugid'  and date(concat(uap_anno,'-',uap_mes,'-',hp_dia)) >= date('$fecha')";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		
	}
	
	
	public function registra_hrprog_dia($con,$anno,$mm,$idj,$ids,$idsa,$idtp,$idh,$dia,$idga,$ida,$codprog,$codper,$thora,$usuprog)
	{
		$sql="insert into tb_horaprog".$anno." values('$dia',upper('$codprog'),'$idh','$ida','$idga','$idtp','$codper','$anno','$mm','$ids','$idj','$thora','$idsa','$usuprog')";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		
	}
	
	public function modificar_hrprog_dia($con,$codprog, $thora,$codper,$usuprog, $anno,$mm,$idj,$ids,$idsa,$idtp,$idh,$dia,$idga,$ida)
	{
		$sql="update tb_horaprog".$anno." set ut_codprog=upper('$codprog'), hp_tipohora='$thora', ug_id='$codper', pu_id='$usuprog' where hp_dia='$dia' and h_id='$idh' and a_id='$ida' and ga_id='$idga' and tp_id='$idtp' and uap_anno='$anno' and uap_mes='$mm' and s_id='$ids' and j_id='$idj' and hp_idserva='$idsa'";
		$res=mysqli_query($con, $sql);
		
	}
	
	public function listar_hrprog($con,$anno,$mm,$idj,$ids,$idtp,$idh,$dia,$idga,$ida)
	{
		$sql="select ut_codprog as cod, hp_tipohora as tipo from tb_horaprog".$anno." where j_id='$idj' and uap_mes='$mm' and uap_anno='$anno' and hp_dia='$dia' and h_id='$idh' and a_id='$ida' and ga_id='$idga' and tp_id='$idtp' and hp_idserva='$ids'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		if($res){
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_hr1[]=$reg;
		}
			return $this->lis_hr1;
		}	
	}
	
		public function listar_hrprog_pos_gd($con,$anno,$mm,$idj,$ids,$idtp,$dia,$codper)
	{
		$sql="select ut_codprog as cod, hp_idserva as idso, hp_tipohora as tipo from tb_horaprog".$anno." where j_id='$idj' and uap_mes='$mm' and uap_anno='$anno' and hp_dia='$dia' and h_id in (select h_id from tb_horario where h_horaturn='N' and h_horanum='12') and tp_id='$idtp' and hp_idserva='$ids' and ug_id='$codper' limit 1";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		if($res){
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_hr_gd[]=$reg;
		}
			return $this->lis_hr_gd;
		}
	}
	
	//cuenta la cantidad de guardias programadas
	public function contar_gd_prog($con,$anno,$mm,$idj,$ids,$idtp,$codper,$thora)
	{
		//$sql="select count(a.ug_id) as numg from tb_horaprog a inner join tb_horario b on a.h_id=b.h_id where a.j_id='$idj' and a.hp_idserva='$ids' and a.tp_id='$idtp' and b.h_horatipo='1' and a.ug_id='$codper' and a.uap_anno='$anno' and a.uap_mes='$mm' and b.h_horaturn in ('D','N') and a.hp_tipohora='$thora' and a.ug_id not in (select ug_id from tb_usuasist where tc_idcont in ('3','6','7'))";
		$sql="SELECT (chp_numgd+chp_numgn) as numg FROM tb_consolhprog".$anno." where ug_id='$codper' and uap_anno='$anno' and uap_mes='$mm' and j_id='$idj' and s_id='$ids' and tp_id='$idtp' and ug_id not in (select ug_id from tb_usudep where tc_idcont in ('3','6','7') and ua_estado not in ('0'))";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		if($res){
			while ($reg=mysqli_fetch_assoc($res))
		{
			$this->num_gd=$reg['numg'];
		}
			return $this->num_gd;
		}
	}
	
	//consulta la reparacion de los turnos del personal
	public function ask_separacion_dias($con,$anno,$mm,$idj,$ids,$idtp,$codper,$thora,$fechai,$fechaf)
	{
		$sql="select ug_id from tb_horaprog".$anno." where ug_id='$codper' and tp_id='$idtp' and uap_anno='$anno' and uap_mes='$mm' and j_id='$idj' and hp_idserva='$ids' and hp_tipohora='$thora' and hp_dia between '$fechai' and '$fechaf' limit 1";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		if($res){
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->ask_sep_dias[]=$reg;
		}
			return $this->ask_sep_dias;
		}
	}
	
	public function ask_superposicion1($con,$anno,$mm,$idj,$ids,$idtp,$dia,$idga,$ida)
	{
		$sql="SELECT a.hp_dia, a.a_id, a.ga_id, a.s_id, b.h_horaturn FROM tb_horaprog".$anno." a inner join tb_horario b on a.h_id = b.h_id where a.uap_anno='$anno' and a.uap_mes='$mm' and a.j_id='$idj' and a.tp_id='$idtp' and a.hp_idserva='$ids' and a.ga_id='$idga' and a.hp_dia='$dia' and a_id='$ida' and b.h_horaturn in ('D','M') ";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_hr2[]=$reg;
		}
			return $this->lis_hr2;
	}
	
		public function ask_superposicion2($con,$anno,$mm,$idj,$ids,$idtp,$dia,$idga,$ida)
	{
		$sql="SELECT a.hp_dia, a.a_id, a.ga_id, a.s_id, b.h_horaturn FROM tb_horaprog".$anno." a inner join tb_horario b on a.h_id = b.h_id where a.uap_anno='$anno' and a.uap_mes='$mm' and a.j_id='$idj' and a.tp_id='$idtp' and a.hp_idserva='$ids' and a.ga_id='$idga' and a.hp_dia='$dia' and a_id='$ida' and  b.h_horaturn in ('D','T') ";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_hr3[]=$reg;
		}
			return $this->lis_hr3;
	}
	
		public function ask_sum_hrs_prog($con,$anno,$mm,$idj,$ids,$idtp,$dia,$codper,$thora)
	{
		//$sql="select sum(b.h_horanum) as sumahrs from tb_horaprog a inner join tb_horario b on a.h_id=b.h_id where a.j_id='$idj' and a.tp_id='$idtp' and b.h_horatipo='1' and a.hp_idserva='$ids' and a.ug_id='$codper' and a.uap_anno='$anno' and a.uap_mes='$mm' and a.hp_dia='$dia' and a.hp_tipohora='$thora'";
		$sql="select sum((select h_horanum from tb_horario where h_id=a.h_id and h_horatipo='1')) as sumahrs from tb_horaprog".$anno." a where a.j_id='$idj' and a.tp_id='$idtp' and  a.ug_id='$codper' and a.uap_anno='$anno' and a.uap_mes='$mm' and a.hp_dia='$dia' and a.hp_tipohora='$thora'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->ask_shrs_prog[]=$reg;
		}
			return $this->ask_shrs_prog;
	}
	
	public function ask_sum_hrs_prog_sinth($con,$anno,$mm,$idj,$ids,$idtp,$dia,$codper)
	{
		$sql="select sum(b.h_horanum) as sumahrs from tb_horaprog".$anno." a inner join tb_horario b on a.h_id=b.h_id where a.j_id='$idj' and a.tp_id='$idtp' and b.h_horatipo='1' and a.hp_idserva='$ids' and a.ug_id='$codper' and a.uap_anno='$anno' and a.uap_mes='$mm' and a.hp_dia='$dia'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		if($res){
			while ($reg=mysqli_fetch_assoc($res))
		{
			$this->ask_shrs_prog_sinth[]=$reg;
		}
			return $this->ask_shrs_prog_sinth;
		}

	}
	
		public function ask_contar_turnos_prog($con,$anno,$mm,$idj,$ids,$idtp,$dia,$codper,$thora)
	{
		$sql="select count(b.h_horanum) as numtn from tb_horaprog".$anno." a inner join tb_horario b on a.h_id=b.h_id where a.j_id='$idj' and a.tp_id='$idtp' and b.h_horatipo='1' and a.hp_idserva='$ids' and a.ug_id='$codper' and a.uap_anno='$anno' and a.uap_mes='$mm' and a.hp_dia='$dia' and a.hp_tipohora='$thora'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->ask_cturn_prog[]=$reg;
		}
			return $this->ask_cturn_prog;
	}
	
	
}
?>