<?php

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

//class HoraReprogramacion extends Conectar
class HoraReprogramacion
{

	private $lis_hr_dia=array();
	private $lis_hr_dia_th=array();
	private $lis_hr_dia_sc=array();
	private $num_gd;
	private $num_turnos;
	private $lis_hr1=array();
	private $lis_hr2=array();
	private $lis_hr3=array();
	private $lis_hr4=array();
	private $lis_hr5=array();
	private $lis_hr6=array();
	private $lis_hr7=array();
	private $lis_hr8=array();
	private $lis_hr9=array();
	private $lis_hr10=array();

	public function consulta_hrreprog_dia($con,$anno,$mm,$idj,$ids,$idsa,$idtp,$idh,$dia,$idga,$ida,$codper)
	{
		$sql="select * from tb_horareprog".$anno." where hr_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 hr_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_hrreprog_dia_tipohr($con,$anno,$mm,$idj,$ids,$idsa,$idtp,$idh,$dia,$idga,$ida,$codper,$tipohora)
	{
		$sql="select * from tb_horareprog".$anno." where hr_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 hr_idserva='$idsa' and ug_id='$codper' and hr_tipohora='$tipohora'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_hr_dia_th[]=$reg;
		}
			return $this->lis_hr_dia_th;
	}

	public function consulta_hrreprog_dia_sin_cod($con,$anno,$mm,$idj,$ids,$idtp,$idh,$dia,$idga,$ida)
	{
		$sql="select ut_codprog as codprog , hr_tipohora as thora from tb_horareprog".$anno." where j_id='$idj' and hr_idserva='$ids' and uap_mes='$mm' and uap_anno='$anno'and hr_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_hrreprog_dia($con,$anno,$mm,$idj,$idsa,$idtp,$idh,$dia,$idga,$ida)
	{
		$sql="delete from tb_horareprog".$anno." where hr_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 hr_idserva='$idsa'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);

	}

	public function elimina_hrreprog_mayor_fecha($con,$ugid,$fecha)
	{
		$sql="delete from tb_horareprog".$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 elimina_hrrereprog_mayor_fecha($con,$ugid,$fecha)
	{
		$sql="Select * FROM tb_reprohrs where ug_id='$ugid'  and date(concat(uap_anno,'-',uap_mes,'-',rh_dia)) >= date('$fecha')";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);

	}


	//elimina de un turno de las reprogramaciones 0 y 1
	public function consulta_hrsrepro_dia($con,$anno,$mm,$idj,$idsa,$idtp,$idh,$dia,$idga,$ida,$codper,$trepro)
	{
		$sql="select ug_id from tb_reprohrs where rh_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 hr_idserva='$idsa' and ug_id='$codper' and rh_id='$trepro'";
		$res=mysqli_query($con, $sql);

	}

	//elimina de un turno de las reprogramaciones 0 y 1
	public function elimina_hrsrepro_dia($con,$anno,$mm,$idj,$idsa,$idtp,$idh,$dia,$idga,$ida,$codper,$trepro)
	{
		$sql="delete from tb_reprohrs where rh_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 hr_idserva='$idsa' and ug_id='$codper' and rh_id='$trepro'";
		$res=mysqli_query($con, $sql);

	}

	//elimina de un turno de las reprogramaciones 0 y 1
	public function elimina_hrsrepro_dia_sincod($con,$anno,$mm,$idj,$idsa,$idtp,$idh,$dia,$idga,$ida,$trepro)
	{
		$sql="delete from tb_reprohrs where rh_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 hr_idserva='$idsa' and rh_id='$trepro'";
		$res=mysqli_query($con, $sql);

	}

	public function registra_hrreprog_dia($con,$anno,$mm,$idj,$ids,$idsa,$idtp,$idh,$dia,$idga,$ida,$codprog,$codper,$thora,$usuprog)
	{
		$sql="insert into tb_horareprog".$anno." values('$dia',upper('$codprog'),'$idh','$ida','$idga','$idtp','$codper','$anno','$mm','$ids','$idj','$thora','$idsa','$usuprog')";
		$res=mysqli_query($con, $sql);

	}

	public function modificar_hrreprog_dia($con,$codprog, $thora,$codper,$usuprog, $anno,$mm,$idj,$ids,$idsa,$idtp,$idh,$dia,$idga,$ida)
	{
		$sql="update tb_horareprog".$anno." set ut_codprog=upper('$codprog'), hr_tipohora='$thora', ug_id='$codper', pu_id='$usuprog', s_id='$ids' where hr_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 hr_idserva='$idsa'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);

	}

	public function listar_hrreprog($con,$anno,$mm,$idj,$ids,$idtp,$idh,$dia,$idga,$ida)
	{
		$sql="select ut_codprog as cod, hr_tipohora as tipo from tb_horareprog".$anno." where j_id='$idj' and uap_mes='$mm' and uap_anno='$anno' and hr_dia='$dia' and h_id='$idh' and a_id='$ida' and ga_id='$idga' and tp_id='$idtp' and hr_idserva='$ids'";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_hr1[]=$reg;
		}
			return $this->lis_hr1;
	}

		public function listar_hrreprog_pos_gd($con,$anno,$mm,$idj,$ids,$idtp,$dia,$codper)
	{
		$sql="select ut_codprog as cod, hr_idserva as idso, hr_tipohora as tipo from tb_horareprog".$anno." where j_id='$idj' and uap_mes='$mm' and uap_anno='$anno' and hr_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 hr_idserva='$ids' and ug_id='$codper' limit 1";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_hr2[]=$reg;
		}
			return $this->lis_hr2;
	}

	public function contar_gd_reprog($con,$anno,$mm,$idj,$ids,$idtp,$codper,$thora)
	{
		$sql="Select (chr_numgd+chr_numgn) as numg FROM tb_consolhreprog".$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);
		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_horareprog".$anno." where ug_id='$codper' and tp_id='$idtp' and uap_anno='$anno' and uap_mes='$mm' and j_id='$idj' and hr_idserva='$ids' and hr_tipohora='$thora' and hr_dia between '$fechai' and '$fechaf' limit 1";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_hr3[]=$reg;
		}
			return $this->lis_hr3;
	}

	//consulta si hay superposicion de turnos con la ma�ana
	public function ask_superposicion1($con,$anno,$mm,$idj,$ids,$idtp,$dia,$idga,$ida)
	{
		$sql="Select a.hr_dia, a.a_id, a.ga_id, a.s_id, b.h_horaturn FROM tb_horareprog".$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.hr_idserva='$ids' and a.ga_id='$idga' and a.hr_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_hr4[]=$reg;
		}
			return $this->lis_hr4;
	}

		public function ask_superposicion2($con,$anno,$mm,$idj,$ids,$idtp,$dia,$idga,$ida)
	{
		$sql="Select a.hr_dia, a.a_id, a.ga_id, a.s_id, b.h_horaturn FROM tb_horareprog".$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.hr_idserva='$ids' and a.ga_id='$idga' and a.hr_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_hr5[]=$reg;
		}
			return $this->lis_hr5;
	}

	public function ask_sum_hrs_reprog($con,$anno,$mm,$idj,$ids,$idtp,$dia,$codper,$thora)
	{
		$sql="select sum((select h_horanum from tb_horario where h_id=a.h_id and h_horatipo='1')) as sumahrs from tb_horareprog".$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.hr_dia='$dia' and a.hr_tipohora='$thora'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_hr6[]=$reg;
		}
			return $this->lis_hr6;
	}

		public function ask_contar_turnos_reprog($con,$anno,$mm,$idj,$ids,$idtp,$dia,$codper,$thora)
	{
		$sql="select count(b.h_horanum) as numtn from tb_horareprog".$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.hr_idserva='$ids' and a.ug_id='$codper' and a.uap_anno='$anno' and a.uap_mes='$mm' and a.hr_dia='$dia' and a.hr_tipohora='$thora'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_hr7[]=$reg;
		}
			return $this->lis_hr7;
	}

	public function ask_sum_hrs_reprog_sinth($con,$anno,$mm,$idj,$ids,$idtp,$dia,$codper)
	{
		$sql="select sum(b.h_horanum) as sumahrs from tb_horareprog".$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.hr_idserva='$ids' and a.ug_id='$codper' and a.uap_anno='$anno' and a.uap_mes='$mm' and a.hr_dia='$dia'";

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

	public function ask_reprog_mes($con,$anno,$mm,$idj,$ids,$idtp)
	{
		$sql = "select * FROM tb_horareprog".$anno." where uap_anno='$anno' and uap_mes='$mm' and hr_idserva='$ids' and j_id='$idj' and tp_id='$idtp'";

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

	//pregunta las turnos programados en la tabla reprogramacion por persona
	public function ask_reprog_mes_per($con,$anno,$mm,$idj,$ids,$idtp,$cod)
	{
		$sql="Select a.ut_codprog as codprog, a.ug_id as ugid, a.h_id as idhora, a.a_id as area, a.ga_id as garea, a.hr_dia as dia, concat(a.hr_dia,'/', a.uap_mes,'/', a.uap_anno) as fechap, (select concat(h_horanum,'(',h_horaturn,')') from tb_horario where h_id=a.h_id) as turnop, a.hr_tipohora as tipohora ";
		$sql=$sql." FROM tb_horareprog".$anno." a left join tb_horaeje".$anno." b on a.hr_dia=b.he_dia and a.ut_codprog=b.ut_codprog and a.h_id = b.h_id and a.ga_id=b.ga_id and a.a_id=b.a_id ";
		$sql=$sql." and a.tp_id = b.tp_id and a.ug_id = b.ug_id and a.uap_anno= b.uap_anno and a.uap_mes= b.uap_mes and a.s_id=b.s_id and a.j_id=b.j_id and a.hr_idserva=b.he_idserva and a.hr_tipohora=b.he_tipohora ";
		$sql=$sql." where a.uap_anno='$anno' and a.uap_mes='$mm' and a.hr_idserva='$ids' and a.j_id='$idj' and a.tp_id='$idtp' and a.hr_tipohora in (0,1) and a.ug_id='$cod' and b.ut_codprog is null ";

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

	public function sum_turnos_reprogramados_per($con,$anno,$mm,$idj,$ids,$idtp,$cod)
	{
		$sql=" select count(a.ug_id) as cant FROM tb_horareprog".$anno." a left join tb_horaeje".$anno." b on a.hr_dia=b.he_dia and a.ut_codprog=b.ut_codprog and a.h_id = b.h_id and a.ga_id=b.ga_id and a.a_id=b.a_id ";
		$sql=$sql." and a.tp_id = b.tp_id and a.ug_id = b.ug_id and a.uap_anno= b.uap_anno and a.uap_mes= b.uap_mes and a.s_id=b.s_id and a.j_id=b.j_id and a.hr_idserva=b.he_idserva";
		$sql=$sql." where a.uap_anno='$anno' and a.uap_mes='$mm' and a.hr_idserva='$ids' and a.j_id='$idj' and a.tp_id='$idtp' and a.hr_tipohora =0 and a.ug_id='$cod' and b.ut_codprog is null group by a.ug_id";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
				$this->num_turnos=$reg['cant'];
		}
			return $this->num_turnos;
	}

}
?>
