<html>
<head>
<title>HORAS SUSPENDIDAS DEL PERSONAL ASISTENCIAL</title>
<link rel="stylesheet" type="text/css" href="styles/Style.css">
<script language="JavaScript">
function del_regdm(dia,idh){

var anno = document.getElementById('anno').value; 
var mes = document.getElementById('mes').value; 
var ugid = document.getElementById('ugid').value; 
var idsp = document.getElementById('idsp').value; 
var idjp = document.getElementById('idjp').value; 
var aleatorio=Math.random();
document.location ="verdetallehrsusp.php?anno="+anno+"&mes="+mes+"&ugid="+ugid+"&idsp="+idsp+"&idjp="+idjp+"&dia="+dia+"&idh="+idh+"&save=1&aleatorio="+aleatorio;
//window.location.reload();
}

function del_reglic(dia,idh){

var anno = document.getElementById('anno').value; 
var mes = document.getElementById('mes').value; 
var ugid = document.getElementById('ugid').value; 
var idsp = document.getElementById('idsp').value; 
var idjp = document.getElementById('idjp').value; 
var aleatorio=Math.random();
document.location ="verdetallehrsusp.php?anno="+anno+"&mes="+mes+"&ugid="+ugid+"&idsp="+idsp+"&idjp="+idjp+"&dia="+dia+"&idh="+idh+"&save=2&aleatorio="+aleatorio;
//window.location.reload();
}
</script>
</head>
<body>
<?php
date_default_timezone_set('UTC');
$anno=$_GET['anno'];
$mm=$_GET['mes'];
$idsp=$_GET['idsp'];
$idjp=$_GET['idjp'];
$ugid=$_GET['ugid'];
$idtp=$_GET['idtp'];

session_start();
echo "<input type='hidden' id='mes' name='mes' value='".$mm."'>";
echo "<input type='hidden' id='anno' name='anno' value='".$anno."'>";
echo "<input type='hidden' id='ugid' name='ugid' value='".$ugid."'>";
echo "<input type='hidden' id='idjp' name='idjp' value='".$idjp."'>";
echo "<input type='hidden' id='idsp' name='idsp' value='".$idsp."'>";

$mes = mktime( 0, 0, 0, $mm, 1, $anno ); 
$numeroDeDias = intval(date("t",$mes)); 
//echo $anno;

include("bd/sql_variables.php");
$con= mysqli_connect("$sql_host", "$sql_usuario", "$sql_pass") or die(header ("No Disponible Servidor de Base de Datos"));
mysqli_select_db($con,"$sql_db");


//armando grupo de servicios y sus dependencias
$sqldepa="select j_id as codj, 0 as cods ,j_abrev as nom, j_desc as nomc from tb_jefatura where j_id='$idjp'";
$sqlserv="select j_id as codj, s_id as cods, s_abrev as nom, s_desc as nomc from tb_servicio where j_id='$idjp'";
$resultdepa = mysqli_query($con,$sqldepa);
$resultserv = mysqli_query($con,$sqlserv);
$a=0;
$cad="";
//$cad = $cad."<tr>";
$serv[] = array();
$nomserv[] = array();
//echo $sqlserv;
while ($rows= mysqli_fetch_assoc($resultserv)){
$cad = $cad."<td width='10' class='facetfilaTD' colspan='4' align='center' >".$rows['nom']."</td>";
$a=$a+1;
$serv[$a]=$rows['cods'];
$nomserv[$a]=$rows['nomc'];
}
while ($rowj= mysqli_fetch_assoc($resultdepa)){
$cad = $cad."<td width='10' class='facetfilaTD' colspan='4'  align='center' >".$rowj['nom']."</td>";
$a=$a+1;
$serv[$a]=$rowj['cods'];
$nomserv[$a]=$rowj['nomc'];
}




//algunos datos
$sqldatosjefatura ="select tp_id as idtp, s_id as ids, j_id as idj from tb_usudep where ug_id ='$ugid'";
$resultdatos = mysqli_query($con,$sqldatosjefatura);
while ($rowdj= mysqli_fetch_assoc($resultdatos)){
$idj=$rowdj['idj'];
$ids=$rowdj['ids'];
$idtp=$rowdj['idtp'];
}

//echo "<input type='hidden' id='idtp' name='id' value='".$idsp."'>";

$sqlper="select a.ug_id as codigo, CONCAT(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, a.ua_dni as dni, a.ua_numcoleg as numcoleg, a.ua_numesp as numesp, ";
$sqlper= $sqlper." (select j_desc from tb_jefatura where j_id=b.j_id) as jefatura, (select s_desc from tb_servicio where s_id=b.s_id and j_id=b.j_id) as servicio,";
$sqlper= $sqlper." (select c_desc from tb_cargo where c_id=b.c_id) as cargo, (select esp_desc from tb_especialidad where ua_idespec=b.ua_idespec) as especialidad,";
$sqlper= $sqlper." a.ua_numplanilla as planilla, a.ua_numesp as numespecialidad from tb_usuasist a inner join tb_usudep b on a.ug_id=b.ug_id where a.ug_id='$ugid'";
$resultper = mysqli_query($con,$sqlper);
while ($row= mysqli_fetch_assoc($resultper)){
$codigo = $row['codigo'];
$nombre = $row['nombre'];
$dni = $row['dni'];
$jefatura = $row['jefatura'];
$servicio = $row['servicio'];
$cargo = $row['cargo'];
$especialidad = $row['especialidad'];
$numplanilla = $row['planilla'];
$numespecialidad = $row['numesp'];
$numcolegiatura = $row['numcoleg'];
//$numespecilidad = $row['numespecilidad'];
}

$arraymes = array();
$arraymes[] = array('idmes'=>'1','mes'=>'ENERO');
$arraymes[] = array('idmes'=>'2','mes'=>'FEBRERO');
$arraymes[] = array('idmes'=>'3','mes'=>'MARZO');
$arraymes[] = array('idmes'=>'4','mes'=>'ABRIL');
$arraymes[] = array('idmes'=>'5','mes'=>'MAYO');
$arraymes[] = array('idmes'=>'6','mes'=>'JUNIO');
$arraymes[] = array('idmes'=>'7','mes'=>'JULIO');
$arraymes[] = array('idmes'=>'8','mes'=>'AGOSTO');
$arraymes[] = array('idmes'=>'9','mes'=>'SETIEMBRE');
$arraymes[] = array('idmes'=>'10','mes'=>'OCTUBRE');
$arraymes[] = array('idmes'=>'11','mes'=>'NOVIEMBRE');
$arraymes[] = array('idmes'=>'12','mes'=>'DICIEMBRE');


//total de horas
$sqltotalhrs="select sum(chp_numgd) as tgd, sum(chp_numgn) as tgn, sum(chp_numho) as tho, sum(chp_numhe) as the from tb_consolhprog".$anno." where ug_id='$ugid' and uap_anno='$anno' and uap_mes='$mm' and tp_id='$idtp'";
$resultthrs = mysqli_query($con,$sqltotalhrs);
//echo $sqltotalhrs."<br>";
	if(mysqli_num_rows($resultthrs)==0){
		$tgd = 0;
		$tgn = 0;
		$tho = 0;
		$the = 0;
	}else{
		while ($rowth= mysqli_fetch_assoc($resultthrs)){
		
		$tgd = $rowth['tgd'];
		$tgn = $rowth['tgn'];
		$tho = $rowth['tho'];
		$the = $rowth['the'];
		}
	}
$diasresta = $numeroDeDias-27;
$diasrestantes = $diasresta + 1;
$diascabecera = $diasresta + 16;
$cabeceramayor = $diascabecera + 16;
?>

<table align="center">
<tr><td align="center" colspan='6'><b>DETALLE DE SUSPENSION DE HORAS DEL PERSONAL</b></td></tr>
<tr><td class='facetfilaTD' colspan='2'><b>DPTO / AREA</b></b></td><td align="left" colspan='4' class='FacetDataTextC'><?php echo $jefatura;?> </td></tr>
</tr><td colspan='2' class='facetfilaTD'><b>SERVICIO</b></td><td align="left" colspan='4' class='FacetDataTextC'><?php echo $servicio;?> </td></tr>
</tr><td colspan='2' class='facetfilaTD'><b>TIPO DE PERSONAL</b></td><td align="left" colspan='4' class='FacetDataTextC'><?php echo $cargo;?> </td></tr>
<tr><td colspan='2' class='facetfilaTD'><b>APELLIDOS Y NOMBRES</b></td><td align="left" colspan='4' class='FacetDataTextC'><?php echo $nombre;?></td></td></tr>
<tr><td colspan='2' class='facetfilaTD'><b>ANNO</b></td><td width='100' class='FacetDataTextC'><?php echo $anno;?></td><td class='facetfilaTD'><b>MES</b></td><td align="left" colspan='1' class='FacetDataTextC'><?php echo $arraymes[$mm-1]['mes'];?></td></tr>
<table>
<br>



<?php

$sumahrsdm=0;
$sumahrslic=0;
$sumahrshp=0;

if(isset($_GET['save'])){
if($_GET['save']==1){
	$diae = $_GET['dia'];
	$idhe = $_GET['idh'];
	$sqldel = "delete from tb_horasusp_dm where ug_id='$ugid' and tp_id='$idtp' and uap_anno='$anno' and uap_mes='$mm' and hs_tipohora='0' and j_id='$idjp' and hs_idserva='$idsp' and hs_dia='$diae' and h_id='$idhe'";
	//echo $sqldel;
	$resultdel = mysqli_query($con,$sqldel);
	echo "<script type='text/javascript'>document.location ='verdetallehrsusp.php?anno=".$anno."&mes=".$mm."&ugid=".$ugid."&idsp=".$idsp."&idjp=".$idjp."&idtp=".$idtp."';</script>";
}

if($_GET['save']==2){
	$diae = $_GET['dia'];
	$idhe = $_GET['idh'];
	$sqldel = "delete from tb_horasusp_lic where ug_id='$ugid' and tp_id='$idtp' and uap_anno='$anno' and uap_mes='$mm' and hl_tipohora='0' and j_id='$idjp' and hl_idserva='$idsp' and hl_dia='$diae' and h_id='$idhe'";
	//echo $sqldel;
	$resultdel = mysqli_query($con,$sqldel);
	echo "<script type='text/javascript'>document.location ='verdetallehrsusp.php?anno=".$anno."&mes=".$mm."&ugid=".$ugid."&idsp=".$idsp."&idjp=".$idjp."&idtp=".$idtp."';</script>";
}
}
$cadena="";
$sql= "SELECT a.hs_dia as dia, concat(date_format(b.h_horaini,'%k:%i %p'),' - ',date_format(b.h_horafin,'%k:%i %p')) as hora, b.h_horanum as numhrs, b.h_horaturn as turno,  b.h_id as idh, ";
$sql= $sql." (select dm_obs from tb_descmed where date(concat(a.uap_anno,'-',a.uap_mes,'-',a.hs_dia)) between date(dm_fechaini) and date(dm_fechafin) and ug_id=a.ug_id) as obsdm FROM tb_horasusp_dm a inner join tb_horario b on a.h_id=b.h_id ";
$sql= $sql." where a.ug_id='$ugid' and a.tp_id='$idtp' and a.uap_anno='$anno' and a.uap_mes='$mm' and a.hs_tipohora='0' and a.j_id='$idjp' and a.hs_idserva='$idsp'";
//echo $sql;
$resultthrs = mysqli_query($con,$sql);
if(mysqli_num_rows($resultthrs)==0){
$cadena = $cadena."<table align='center'>";
$cadena = $cadena."<tr><td align='left' colspan='5'><b>SUSPENSION POR DESCANSO MEDICO</b></td></tr>";
$cadena = $cadena."<tr><td align='left' colspan='5'>NO EXISTEN HORAS AFECTADAS</td></tr>";
$cadena = $cadena."</table>";
echo  $cadena;
}else{
$cadena = $cadena."<table align='center'>";
$cadena = $cadena."<tr><td align='left' colspan='5'><b>SUSPENSION POR DESCANSO MEDICO</b></td></tr>";
$cadena = $cadena."<tr><td align='center' class='facetfilaTD' width='50'><b>DIA</b></td><td class='facetfilaTD' width='150' align='center'><b>HORARIO</b></td><td class='facetfilaTD' width='80' align='center'><b>NRO HRS</b></td><td class='facetfilaTD' width='80' align='center'><b>TURNO</b></td><td class='facetfilaTD' width='200' align='center'><b>OBSERVACIONES</b></td></td><td class='facetfilaTD' width='20' align='center'><b>DEL</b></td></tr>";

		while ($rowth= mysqli_fetch_array($resultthrs)){
		$cadena = $cadena."<tr><td align='center' class='FacetDataTextC'>".$rowth['dia']."</td><td align='center' class='FacetDataTextC'>".$rowth['hora']."</td><td align='center' class='FacetDataTextC'>".$rowth['numhrs']."</td><td align='center' class='FacetDataTextC'>".$rowth['turno']."</td><td align='center' class='FacetDataTextC'>".$rowth['obsdm']."</td>";
		$sumahrsdm = $sumahrsdm + $rowth['numhrs'];
		
		//agregar el delete
		$dia = $rowth['dia'];
		$idh = $rowth['idh'];
		$sqlaskdm = "select * from tb_horareprog".$anno." where j_id='$idjp' and hr_idserva='$idsp' and hr_tipohora='0' and hr_dia='".$rowth['dia']."' and h_id='".$rowth['idh']."' and ug_id='$ugid' and uap_anno='$anno' and uap_mes='$mm'";
		$resultaskdm = mysqli_query($con,$sqlaskdm);
		if(mysqli_num_rows($resultaskdm)>0){
		$cadena = $cadena."<td align='center' class='FacetDataTextC'><img src='img/del.png' weight='10' height='10' onClick='del_regdm(".$dia.",".$idh.")'></td></tr>";
		}else{
		$cadena = $cadena."<td class='FacetDataTextC'></td></tr>";
		}
		
		}
$cadena = $cadena."<tr><td align='left' colspan='4'><b>TOTAL DE HORAS</b></td><td align='center'><b>".$sumahrsdm."</b></td></tr>";
$cadena = $cadena."</table>";
echo $cadena;
	}
?>
<br>
<?php
$cadena="";
$sql= "SELECT a.hl_dia as dia, concat(date_format(b.h_horaini,'%k:%i %p'),' - ',date_format(b.h_horafin,'%k:%i %p')) as hora, b.h_horanum as numhrs, b.h_horaturn as turno, b.h_id as idh,";
$sql= $sql." (select (select tlc_desc from tb_tplicencia where tlc_id=c.tlc_id) from tb_licencias c where date(concat(a.uap_anno,'-',a.uap_mes,'-',a.hl_dia)) between date(c.lc_fechaini) and date(c.lc_fechafin) and ug_id=a.ug_id) as obslic FROM tb_horasusp_lic a inner join tb_horario b on a.h_id=b.h_id ";
$sql= $sql." where a.ug_id='$ugid' and a.tp_id='$idtp' and a.uap_anno='$anno' and a.uap_mes='$mm' and a.hl_tipohora='0' and a.j_id='$idjp' and a.hl_idserva='$idsp'";
$resultthrs = mysqli_query($con,$sql);
//echo $sql;
if(mysqli_num_rows($resultthrs)==0){
$cadena = $cadena."<table align='center'>";
$cadena = $cadena."<tr><td align='left' colspan='5'><b>SUSPENSION POR LICENCIATURA</b></td></tr>";
$cadena = $cadena."<tr><td align='left' colspan='5'>NO EXISTEN HORAS AFECTADAS</td></tr>";
$cadena = $cadena."</table>";
echo  $cadena;
}else{
$cadena = $cadena."<table align='center'>";
$cadena = $cadena."<tr><td align='left' colspan='5'><b>SUSPENSION POR LICENCIAS</b></td></tr>";
$cadena = $cadena."<tr><td align='center' class='facetfilaTD' width='50'><b>DIA</b></td><td class='facetfilaTD' width='150' align='center'><b>HORARIO</b></td><td class='facetfilaTD' width='80' align='center'><b>NRO HRS</b></td><td class='facetfilaTD' width='80' align='center'><b>TURNO</b></td><td class='facetfilaTD' width='200' align='center'><b>OBSERVACIONES</b><td class='facetfilaTD' width='20' align='center'><b>DEL</b></td></tr>";

		while ($rowth= mysqli_fetch_assoc($resultthrs)){
		$cadena = $cadena."<tr><td align='center' class='FacetDataTextC'>".$rowth['dia']."</td><td align='center' class='FacetDataTextC'>".$rowth['hora']."</td><td align='center' class='FacetDataTextC'>".$rowth['numhrs']."</td><td align='center' class='FacetDataTextC'>".$rowth['turno']."</td><td align='center' class='FacetDataTextC'>".$rowth['obslic']."</td>";
		$sumahrslic = $sumahrslic + $rowth['numhrs'];
		
			//agregar el delete
			$dia = $rowth['dia'];
			$idh = $rowth['idh'];
			$sqlasklic = "select * from tb_horareprog".$anno." where j_id='$idjp' and hr_idserva='$idsp' and hr_tipohora='0' and hr_dia='".$dia."' and h_id='".$idh."' and ug_id='$ugid' and uap_anno='$anno' and uap_mes='$mm'";
			//echo $sqlasklic."<br>";
			$resultasklic = mysqli_query($con,$sqlasklic);
			if(mysqli_num_rows($resultasklic)>0){
			$cadena = $cadena."<td align='center' class='FacetDataTextC'><img src='img/del.png' weight='10' height='10' onClick='del_reglic(".$dia.",".$idh.")'></td></tr>";
			}else{
			$cadena = $cadena."<td class='FacetDataTextC'></td></tr>";
			}
		}
$cadena = $cadena."<tr><td align='left' colspan='4'><b>TOTAL DE HORAS</b></td><td align='center'><b>".$sumahrslic."</b></td></tr>";
$cadena = $cadena."</table>";
echo $cadena;
	}
?>
<br>
<?php
$cadena="";
$sql= "SELECT date_format(a.hrp_fecha,'%d/%m/%Y') as fecha, concat(date_format(a.hrp_horaini,'%k:%i %p'),' - ',date_format(a.hrp_horafin,'%k:%i %p'))  as hora, ";
$sql= $sql." a.hrp_numhora as numhrs, (select  thp_desc from tb_tphoraper where thp_id=a.thp_id) as tipohrper FROM tb_horapermiso a where a.ug_id='$ugid' and month(a.hrp_fecha)='$mm' and year(a.hrp_fecha)='$anno'" ;
$resultthrs = mysqli_query($con,$sql);
if(mysqli_num_rows($resultthrs)==0){
$cadena = $cadena."<table align='center'>";
$cadena = $cadena."<tr><td align='left' colspan='4'><b>NUMERO DE HORAS PERMISO</b></td></tr>";
$cadena = $cadena."<tr><td align='left' colspan='4'>NO EXISTEN HORAS AFECTADAS</td></tr>";
$cadena = $cadena."</table>";
echo  $cadena;}else{
$cadena = $cadena."<table align='center'>";
$cadena = $cadena."<tr><td align='left' colspan='5'><b>NUMERO DE HORAS PERSMISO</b></td></tr>";
$cadena = $cadena."<tr><td align='center' class='facetfilaTD' width='50'><b>FECHA</b></td><td class='facetfilaTD' width='150' align='center'><b>HORARIO</b></td><td class='facetfilaTD' width='80' align='center'><b>NRO HRS</b></td><td class='facetfilaTD' width='200' align='center'><b>OBSERVACIONES</b></td></tr>";

		while ($rowth= mysqli_fetch_assoc($resultthrs)){
		$cadena = $cadena."<tr><td align='center' class='FacetDataTextC'>".$rowth['fecha']."</td><td align='center' class='FacetDataTextC'>".$rowth['hora']."</td><td align='center' class='FacetDataTextC'>".$rowth['numhrs']."</td><td align='center' class='FacetDataTextC'>".$rowth['tipohrper']."</td></tr>";
		$sumahrshp = $sumahrshp + $rowth['numhrs'];
		}
$cadena = $cadena."<tr><td align='left' colspan='3'><b>TOTAL DE HORAS</b></td><td align='center'><b>".$sumahrshp."</b></td></tr>";
$cadena = $cadena."</table>";
echo $cadena;
	}

if(($sumahrsdm + $sumahrslic +$sumahrshp)>0){
$sumahrstotal =$sumahrsdm + $sumahrslic +$sumahrshp;
$cadenaout = "<br><table align='center'>";
$cadenaout = $cadenaout."<tr><td align='left' colspan='3' class='facetfilaTD' width='250'><b>TOTAL DE HORAS AFECTADAS</b></td><td align='center' class='facetfilaTD' width='50'><b>".$sumahrstotal."</b></td></tr>";
$cadenaout = $cadenaout."</table>";
echo $cadenaout;
}	
?>
</form>
</body>
</html>
