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

//class HoraEjecucion extends Conectar
class HoraEjecucion
{

	private $num_turnos;
	private $lis_hr_dia=array();
	private $lis_he=array();
	private $lis_he1=array();

	public function consulta_hreje_dia($con,$anno,$mm,$idj,$ids,$idsa,$idtp,$idh,$dia,$idga,$ida,$codper)
	{
		$sql="select * from tb_horaeje".$anno." where he_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 he_idserva='$idsa' and ug_id='$codper'";
		$res=mysqli_query($con, $sql);
		while ($reg=mysqli_fetch_assoc($res))
		{
			$this->lis_hr_dia[]=$reg;
		}
			return $this->lis_hr_dia;
	}

		public function elimina_hreje_dia($con,$anno,$mm,$idj,$idsa,$idtp,$idh,$dia,$idga,$ida)
	{
		$sql="delete from tb_horaeje".$anno." where he_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 he_idserva='$idsa'";
		//echo $sql."<br>";
		$res=mysqli_query($con, $sql);

	}

	public function elimina_hreje_mayor_fecha($con,$udid,$fecha)
	{
		$sql="delete from tb_horaeje".$anno." where ug_id='$ugid'  and date(concat(uap_anno,'-',uap_mes,'-',hp_dia)) >= date('$fecha')";
		$res=mysqli_query($con, $sql);

	}

		public function elimina_hreje_dia_codigo($con,$anno,$mm,$idj,$ids,$idsa,$idtp,$idh,$dia,$idga,$ida,$codper)
	{
		$sql="delete from tb_horaprog".$anno." where he_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 s_id='$ids' and he_idserva='$idsa' and ug_id='$codper'";
		$res=mysqli_query($con, $sql);

	}

		public function elimina_hreje_dia_sin_codigo($con,$anno,$mm,$idj,$ids,$idsa,$idtp,$idh,$dia,$idga,$ida,$codper)
	{
		$sql="delete from tb_horaprog".$anno." where he_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 he_idserva='$idsa' and ug_id not in('$codper')";
		$res=mysqli_query($con, $sql);

	}

		public function elimina_hreje_dia_codigo_all($con,$anno,$mm,$idj,$idsa,$idtp,$idh,$dia,$idga,$ida)
	{
		$sql="delete from tb_horaprog".$anno." where he_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 he_idserva='$idsa'";
		$res=mysqli_query($con, $sql);

	}

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

	}

	//pregunta las turnos programados en la tabla de ejecucion por persona
	public function ask_hrseje_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.he_dia as dia, concat(a.he_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.he_tipohora as tipohora";
		$sql=$sql." FROM tb_horaeje".$anno." a left join tb_horareprog".$anno." b on a.he_dia=b.hr_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.he_idserva=b.hr_idserva and b.hr_tipohora=a.he_tipohora ";
		$sql=$sql." where a.uap_anno='$anno' and a.uap_mes='$mm' and a.he_idserva='$ids' and a.j_id='$idj' and a.tp_id='$idtp' and a.he_tipohora=0 and a.ug_id='$cod' and b.ut_codprog is null";

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

	public function sum_turnos_ejecutados_per($con,$anno,$mm,$idj,$ids,$idtp,$cod)
	{
		$sql=" Select count(a.ug_id) as cant FROM tb_horaeje".$anno." a left join tb_horareprog".$anno." b on a.he_dia=b.hr_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.he_idserva=b.hr_idserva ";
		$sql=$sql." where a.uap_anno='$anno' and a.uap_mes='$mm' and a.he_idserva='$ids' and a.j_id='$idj' and a.tp_id='$idtp' and a.he_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;
	}


}

?>
