<?php



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

$Objcon = new Conectar();
$conex = $Objcon->con();
if(isset($_GET['repro'])){
$val = $_GET['repro'];

//echo $val."<br>";
if($val==1){
$anno= $_GET['anno'];
$mes= $_GET['mes'];
$idj= $_GET['idj'];
$ids= $_GET['ids'];
$idtp= $_GET['idtp'];
$ugid= $_GET['ugid'];
}

echo "<input type='hidden' id='idj' name='idj' value='".$idj."'>";
echo "<input type='hidden' id='ids' name='ids' value='".$ids."'>";
echo "<input type='hidden' id='anno' name='anno' value='".$anno."'>";
echo "<input type='hidden' id='mes' name='mes' value='".$mes."'>";
echo "<input type='hidden' id='idtp' name='idtp' value='".$idtp."'>";
echo "<input type='hidden' id='ugid' name='ugid' value='".$ugid."'>";

$sqlaskeje = "SELECT * FROM tb_usuaprueba where uap_anno='$anno' and uap_mes='$mes' and s_id='$ids' and j_id='$idj' and uap_tipoproc='3'";
//echo $sqlaskeje;
$resultaskeje = mysqli_query($conex,$sqlaskeje);
$validado =0;
if(mysqli_num_rows($resultaskeje)!=0){
$validado=1;
}
}
?>
<br>
<table align='center' width='940'><tr><td class='LlenarTablaGarea' align='center'>REPROGRAMACION DE TURNOS</td></tr><tr><td align='center' class ='FacetDataTextC'><input type='button' value='Grabar' onclick='LeerPersonalReprog()'<?php if(isset($validado)){ if($validado==1){ echo " disabled ";}}
?>></td></tr></table>
<table align='center'>
<tr><td class='LlenarTablaGarea' width='30'>CANT</td><td align='center' class='LlenarTablaGarea' width='250'>NOMBRE DEL PERSONAL</td><td align='center' width='110' class='LlenarTablaGarea'>FECHA PROGRAMADO</td><td align='center' width='70' class='LlenarTablaGarea'>TURNO</td><td align='center' width='110' class='LlenarTablaGarea' >FECHA REPROGRAMADO</td><td align='center' width='70' class='LlenarTablaGarea'>TURNO</td><td align='center' width='250' class='LlenarTablaGarea'>OBSERVACIONES</td></tr>

<?php

$sqlaskpr = "SELECT * FROM tb_horareprog".$anno." where tp_id='$idtp' and uap_anno='$anno' and uap_mes='$mes' and hr_idserva='$ids' and j_id='$idj'";
//ECHO $sqlaskpr."<BR>";
$resultaskp = mysqli_query($conex,$sqlaskpr);
if(mysqli_num_rows($resultaskp)!=0){

//pregunta por el personal
$sqlper1="SELECT a.ug_id as cod, a.ut_codprog as codigo, (select CONCAT(ua_apepat,' ',ua_apemat,' ',ua_nom) from tb_usuasist where ug_id=a.ug_id) as nombre, a.ut_idserva as idsa ";
$sqlper1= $sqlper1." FROM tb_usutrab a where a.j_id='$idj' and a.tp_id='$idtp' and a.ut_idserva='$ids' ";
$sqlper1= $sqlper1." and a.uap_anno='$anno' and a.uap_mes ='$mes' order by nombre ";
//echo $sqlper1."<br>";
$resultper1 = mysqli_query($conex,$sqlper1);

$aa=0;
$bb=0;
$pp=0;
while ($row= mysqli_fetch_array($resultper1)){
$codper=$row['cod'];
$codprog=$row['codigo'];
$nombre=$row['nombre'];



	
//pregunta por la programacion
$sqlprog="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.hp_dia as dia, concat(a.hp_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";
$sqlprog=$sqlprog." FROM tb_horaprog".$anno." a left join tb_horareprog".$anno." b on a.hp_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 ";
$sqlprog=$sqlprog." 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.hp_idserva=b.hr_idserva and a.hp_tipohora=b.hr_tipohora";
$sqlprog=$sqlprog." where a.uap_anno='$anno' and a.uap_mes='$mes' and a.hp_idserva='$ids' and a.j_id='$idj' and a.tp_id='$idtp' and a.hp_tipohora=0 and a.ug_id='$codper' and b.ut_codprog is null ";
//echo $sqlprog."<br>";

//pregunta por la programacion
$sqlrprog="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";
$sqlrprog=$sqlrprog." FROM tb_horareprog".$anno." a left join tb_horaprog".$anno." b on a.hr_dia=b.hp_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 ";
$sqlrprog=$sqlrprog." 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.hp_idserva and b.hp_tipohora=a.hr_tipohora";
$sqlrprog=$sqlrprog." where a.uap_anno='$anno' and a.uap_mes='$mes' and a.hr_idserva='$ids' and a.j_id='$idj' and a.tp_id='$idtp' and a.hr_tipohora=0 and a.ug_id='$codper' and b.ut_codprog is null";
//echo $sqlrprog."<br>";



$resulthp = mysqli_query($conex,$sqlprog);
$resulthrp = mysqli_query($conex,$sqlrprog);

if(mysqli_num_rows($resulthp)!=0  || mysqli_num_rows($resulthrp)!=0){

echo "<input type='hidden' id='codpertxt[".$pp."]' name='codpertxt[".$pp."]' value='".$codper."'>";

//pregunta por la cantidad de turnos programados del personal
$sqlsprog=" select count(a.ug_id) as cant FROM tb_horaprog".$anno." a left join tb_horareprog".$anno." b on a.hp_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 ";
$sqlsprog=$sqlsprog." 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.hp_idserva=b.hr_idserva ";
$sqlsprog=$sqlsprog." where a.uap_anno='$anno' and a.uap_mes='$mes' and a.hp_idserva='$ids' and a.j_id='$idj' and a.tp_id='$idtp' and a.hp_tipohora=0 and a.ug_id='$codper' and b.ut_codprog is null group by a.ug_id";

//pregunta por la cantidad de turnos reprogramados del personal
$sqlsrprog=" Select count(a.ug_id) as cant FROM tb_horareprog".$anno." a left join tb_horaprog".$anno." b on a.hr_dia=b.hp_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 ";
$sqlsrprog=$sqlsrprog." 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.hp_idserva ";
$sqlsrprog=$sqlsrprog." where a.uap_anno='$anno' and a.uap_mes='$mes' and a.hr_idserva='$ids' and a.j_id='$idj' and a.tp_id='$idtp' and a.hr_tipohora=0 and a.ug_id='$codper' and b.ut_codprog is null group by a.ug_id";

//echo "<br>".$sqlsrprog."<br>";
$resultcant = mysqli_query($conex,$sqlsprog);
$resultcantr = mysqli_query($conex,$sqlsrprog);
$cantp=0;
$cantpr=0;
while ($rowc= mysqli_fetch_array($resultcant)){
$cantp=$rowc['cant'];
}
while ($rowcr= mysqli_fetch_array($resultcantr)){
$cantpr=$rowcr['cant'];
}
$nmax=0;
if($cantp>$cantpr){
$nmax=$cantp;
}else{
$nmax=$cantpr;
}

echo "<tr><td align='center' class ='FacetDataTextC'>".$nmax."</td><td class ='FacetDataTextC'>".$nombre."</td>";

//busca codigo del personal y el servicio
$sqlask ="SELECT ug_id as cod, s_id as ids FROM tb_usutrab where ut_codprog='$codprog' and  ug_id='$codper' and tp_id='$idtp' and uap_anno='$anno' and uap_mes='$mes' and j_id='$idj' and ut_idserva='$ids'";
//echo $sqlask."<br>"; 
$resultask = mysqli_query($conex,$sqlask);
if(mysqli_num_rows($resultask)!=0){
while($rowask = mysqli_fetch_array($resultask)){
$idso = $rowask['ids'];
}}

//PERSONAL QUE AUN NO ESTA LISTA EN REPRO
$sqlsaverepro ="select ug_id from tb_reprohrs where uap_anno='$anno' and ug_id='$codper' and uap_mes='$mes' and s_id='$idso' and rh_idserva='$ids' and j_id='$idj' and tp_id='$idtp'";
$resultsaverepro = mysqli_query($conex,$sqlsaverepro);
//echo $sqlsaverepro."<br>";
if(mysqli_num_rows($resultsaverepro)==0){
//echo $sqlrepro."<br>";
//turnos programados
$cadenaprog ="<table>";
while ($rowhp= mysqli_fetch_array($resulthp)){
//	pregunta por los descansos medicos
$fechap = $anno."-".$mes."-".$rowhp['dia'];
$sqldmp = "select ug_id from tb_descmed where ug_id='$codper' and date('$fechap') between date(dm_fechaini) and date(dm_fechafin)";
$sqllic = "select ug_id from tb_licencias where ug_id='$codper' and date('$fechap') between date(lc_fechaini) and date(lc_fechafin)";
$sqldescrad = "select ug_id from tb_descrad where ug_id='$codper' and date('$fechap') between date(dr_fechaini) and date(dr_fechafin) and dr_estado='1'";
//echo $sqldmp."<br>";
$resultdmp = mysqli_query($conex,$sqldmp);
$resultlic = mysqli_query($conex,$sqllic);
$resultdescrad = mysqli_query($conex,$sqldescrad);
if((mysqli_num_rows($resultdmp)==0) && (mysqli_num_rows($resultlic)==0) && (mysqli_num_rows($resultdescrad)==0) ){
$cadenaprog= $cadenaprog."<tr><td align='center' width='100'>".$rowhp['fechap']."</td><td width='40' align='center'>".$rowhp['turnop']."</td><td width='20' align='center'><input type='checkbox' name='progra[".$aa."]' id='progra[".$aa."]' ></td></tr>";
echo "<input type='hidden' id='codper[".$aa."]' name='codper[".$aa."]' value='".$codper."'>";
echo "<input type='hidden' id='codprog[".$aa."]' name='codprog[".$aa."]' value='".$codprog."'>";
echo "<input type='hidden' id='dia[".$aa."]' name='dia[".$aa."]' value='".$rowhp['dia']."'>";
echo "<input type='hidden' id='horario[".$aa."]' name='horario[".$aa."]' value='".$rowhp['idhora']."'>";
echo "<input type='hidden' id='area[".$aa."]' name='area[".$aa."]' value='".$rowhp['area']."'>";
echo "<input type='hidden' id='garea[".$aa."]' name='garea[".$aa."]' value='".$rowhp['garea']."'>";
echo "<input type='hidden' id='tiporeg[".$aa."]' name='tiporeg[".$aa."]' value='1'>";
$aa=$aa+1;
}
}
$cadenaprog =$cadenaprog."</table>";

//turnos reprogramados
$cadenareprog ="<table>";
while ($rowhrp= mysqli_fetch_array($resulthrp)){
//	pregunta por los descansos medicos
$fechap = $anno."-".$mes."-".$rowhrp['dia'];
$sqldmrp = "select ug_id from tb_descmed where ug_id='$codper' and date('$fechap') between date(dm_fechaini) and date(dm_fechafin)";
$sqllic = "select ug_id from tb_licencias where ug_id='$codper' and date('$fechap') between date(lc_fechaini) and date(lc_fechafin)";
$sqldescrad = "select ug_id from tb_descrad where ug_id='$codper' and date('$fechap') between date(dr_fechaini) and date(dr_fechafin) and dr_estado='1'";
//echo $sqldmp."<br>";
$resultdmrp = mysqli_query($conex,$sqldmrp);
$resultlic = mysqli_query($conex,$sqllic);
$resultdescrad = mysqli_query($conex,$sqldescrad);
if((mysqli_num_rows($resultdmrp)==0) && (mysqli_num_rows($resultlic)==0) && (mysqli_num_rows($resultdescrad)==0)){
$cadenareprog= $cadenareprog."<tr><td align='center' width='100'>".$rowhrp['fechap']."</td><td width='40' align='center'>".$rowhrp['turnop']."</td><td width='20' align='center'><input type='checkbox' name='progra[".$aa."]' id='progra[".$aa."]' ></td></tr>";
echo "<input type='hidden' id='codper[".$aa."]' name='codper[".$aa."]' value='".$codper."'>";
echo "<input type='hidden' id='codprog[".$aa."]' name='codprog[".$aa."]' value='".$codprog."'>";
echo "<input type='hidden' id='dia[".$aa."]' name='dia[".$aa."]' value='".$rowhrp['dia']."'>";
echo "<input type='hidden' id='horario[".$aa."]' name='horario[".$aa."]' value='".$rowhrp['idhora']."'>";
echo "<input type='hidden' id='area[".$aa."]' name='area[".$aa."]' value='".$rowhrp['area']."'>";
echo "<input type='hidden' id='garea[".$aa."]' name='garea[".$aa."]' value='".$rowhrp['garea']."'>";
echo "<input type='hidden' id='tiporeg[".$aa."]' name='tiporeg[".$aa."]' value='2'>";
$aa=$aa+1;
$bb=$bb+1;
}
}
$cadenareprog =$cadenareprog."</table>";


//imprime cambios
echo "<td colspan='2' align='center' class ='FacetDataTextC'>".$cadenaprog."</td><td colspan='2' align='center' class ='FacetDataTextC'>".$cadenareprog."</td><td class ='FacetDataTextC' align='center'><TEXTAREA name='txtobs[".$pp."]' id='txtobs[".$pp."]' rows='3' cols='30' ></TEXTAREA></td></tr>";

}else{
//PERSONAL REGISTRADO CON LAS REPROGRAMACIONES
//cuando ya esta registrado la reprogramacion
$cadenaprog ="<table>";
while ($rowhp= mysqli_fetch_array($resulthp)){
//	pregunta por los descansos medicos
$fechap = $anno."-".$mes."-".$rowhp['dia'];
$sqldmp = "select ug_id from tb_descmed where ug_id='$codper' and date('$fechap') between date(dm_fechaini) and date(dm_fechafin)";
$sqllic = "select ug_id from tb_licencias where ug_id='$codper' and date('$fechap') between date(lc_fechaini) and date(lc_fechafin)";
$sqldescrad = "select ug_id from tb_descrad where ug_id='$codper' and date('$fechap') between date(dr_fechaini) and date(dr_fechafin) and dr_estado='1'";
//echo $sqldmp."<br>";
$resultdmp = mysqli_query($conex,$sqldmp);
$resultlic = mysqli_query($conex,$sqllic);
$resultdescrad = mysqli_query($conex,$sqldescrad);
if((mysqli_num_rows($resultdmp)==0) && (mysqli_num_rows($resultlic)==0) && (mysqli_num_rows($resultdescrad)==0)){
$cadenaprog= $cadenaprog."<tr><td align='center' width='100'>".$rowhp['fechap']."</td><td width='40' align='center'>".$rowhp['turnop']."</td><td width='20' align='center'><input type='checkbox' name='progra[".$aa."]' id='progra[".$aa."]'";
//pregunta por turno prog
$sqlrepro = "select ug_id from tb_reprohrs where h_id='".$rowhp['idhora']."' and a_id='".$rowhp['area']."' and ga_id='".$rowhp['garea']."' and tp_id='$idtp'"; 
$sqlrepro = $sqlrepro." and ug_id='$codper' and rh_dia='".$rowhp['dia']."' and uap_anno='$anno' and uap_mes='$mes' and s_id='$idso' and rh_idserva='$ids' and j_id='$idj' and rh_tipo='1'";
//echo $sqlrepro."<br>";
$resultrepro = mysqli_query($conex,$sqlrepro);
if(mysqli_num_rows($resultrepro)!=0){
$cadenaprog= $cadenaprog." CHECKED ";
}
$cadenaprog= $cadenaprog." ></td></tr>";

echo "<input type='hidden' id='codper[".$aa."]' name='codper[".$aa."]' value='".$codper."'>";
echo "<input type='hidden' id='codprog[".$aa."]' name='codprog[".$aa."]' value='".$codprog."'>";
echo "<input type='hidden' id='dia[".$aa."]' name='dia[".$aa."]' value='".$rowhp['dia']."'>";
echo "<input type='hidden' id='horario[".$aa."]' name='horario[".$aa."]' value='".$rowhp['idhora']."'>";
echo "<input type='hidden' id='area[".$aa."]' name='area[".$aa."]' value='".$rowhp['area']."'>";
echo "<input type='hidden' id='garea[".$aa."]' name='garea[".$aa."]' value='".$rowhp['garea']."'>";
echo "<input type='hidden' id='tiporeg[".$aa."]' name='tiporeg[".$aa."]' value='1'>";
$aa=$aa+1;
}
}
$cadenaprog =$cadenaprog."</table>";

//turnos reprogramados
$cadenareprog ="<table>";
while ($rowhrp= mysqli_fetch_array($resulthrp)){
//	pregunta por los descansos medicos
$fechap = $anno."-".$mes."-".$rowhrp['dia'];
$sqldmrp = "select ug_id from tb_descmed where ug_id='$codper' and date('$fechap') between date(dm_fechaini) and date(dm_fechafin)";
$sqllic = "select ug_id from tb_licencias where ug_id='$codper' and date('$fechap') between date(lc_fechaini) and date(lc_fechafin)";
$sqldescrad = "select ug_id from tb_descrad where ug_id='$codper' and date('$fechap') between date(dr_fechaini) and date(dr_fechafin) and dr_estado='1'";
//echo $sqldmp."<br>";
$resultdmrp = mysqli_query($conex,$sqldmrp);
$resultlic = mysqli_query($conex,$sqllic);
$resultdescrad = mysqli_query($conex,$sqldescrad);
if((mysqli_num_rows($resultdmrp)==0) && (mysqli_num_rows($resultlic)==0) && (mysqli_num_rows($resultdescrad)==0)){
$cadenareprog= $cadenareprog."<tr><td align='center' width='100'>".$rowhrp['fechap']."</td><td width='40' align='center'>".$rowhrp['turnop']."</td><td width='20' align='center'><input type='checkbox' name='progra[".$aa."]' id='progra[".$aa."]'";
//pregunta por turno prog
$sqlrepro = "select ug_id from tb_reprohrs where h_id='".$rowhrp['idhora']."' and a_id='".$rowhrp['area']."' and ga_id='".$rowhrp['garea']."' and tp_id='$idtp'"; 
$sqlrepro = $sqlrepro." and ug_id='$codper' and rh_dia='".$rowhrp['dia']."' and uap_anno='$anno' and uap_mes='$mes' and s_id='$idso' and rh_idserva='$ids' and j_id='$idj' and rh_tipo='2'";
//echo $sqlrepro."<br>";
$resultrepro = mysqli_query($conex,$sqlrepro);
if(mysqli_num_rows($resultrepro)!=0){
$cadenareprog= $cadenareprog." CHECKED ";
}
$cadenareprog= $cadenareprog." ></td></tr>";


echo "<input type='hidden' id='codper[".$aa."]' name='codper[".$aa."]' value='".$codper."'>";
echo "<input type='hidden' id='codprog[".$aa."]' name='codprog[".$aa."]' value='".$codprog."'>";
echo "<input type='hidden' id='dia[".$aa."]' name='dia[".$aa."]' value='".$rowhrp['dia']."'>";
echo "<input type='hidden' id='horario[".$aa."]' name='horario[".$aa."]' value='".$rowhrp['idhora']."'>";
echo "<input type='hidden' id='area[".$aa."]' name='area[".$aa."]' value='".$rowhrp['area']."'>";
echo "<input type='hidden' id='garea[".$aa."]' name='garea[".$aa."]' value='".$rowhrp['garea']."'>";
echo "<input type='hidden' id='tiporeg[".$aa."]' name='tiporeg[".$aa."]' value='2'>";
$aa=$aa+1;
$bb=$bb+1;
}
}
$cadenareprog =$cadenareprog."</table>";


//imprime cambios
$sqlask2 ="select or_obs from tb_obsreprog where j_id='$idj' and ug_id='$codper' and uap_anno='$anno' and uap_mes='$mes' and or_idserva='$ids'";
$resultask2 = mysqli_query($conex,$sqlask2);
//echo $sqlask2."<br>";
echo "<td colspan='2' align='center' class ='FacetDataTextC'>".$cadenaprog."</td><td colspan='2' align='center' class ='FacetDataTextC'>".$cadenareprog."</td><td class ='FacetDataTextC' align='center'><TEXTAREA name='txtobs[".$pp."]' id='txtobs[".$pp."]' rows='3' cols='30'  >";
if(mysqli_num_rows($resultask2)!=0){
while ($rowob= mysqli_fetch_array($resultask2)){
echo $rowob['or_obs'];
}
}
echo "</TEXTAREA></td></tr>";

}
////////////
//echo $pp."-<br>";
$pp = $pp + 1;
}

}
echo "<input type='hidden' id='np' name='np' value='".$aa."'>";
echo "<input type='hidden' id='nrp' name='nrp' value='".$bb."'>";
echo "<input type='hidden' id='pp' name='pp' value='".$pp."'>";
}

?>
</table>
