<?php
/*
$anno='2011';
$mm='2';
$dia='3'; 
$idj='1';
$ids='2';
$per='1'; 
$codprog='20';
*/
include("../bd/sql_variables.php");
$con= mysqli_connect("$sql_host", "$sql_usuario", "$sql_pass","$sql_db") or die(header ("No Disponible Servidor de Base de Datos"));


function validareprog($anno, $mm, $i, $idj, $ids, $per, $codprog, $idga, $ida, $idh){
// consulta de cruce de horario
$sqlask="SELECT a.ut_codprog as cod,b.h_horaini as hori, b.h_horafin as horf,b.h_horaturn as hort, b.h_horanum as horn, a.ga_id as idga, a.a_id as ida, a.h_id as idh FROM tb_horareprog".$anno." a inner join tb_horario b on b.h_id = a.h_id where a.uap_anno='$anno' and a.uap_mes='$mm' and a.j_id='$idj' and a.hr_idserva='$ids' and a.tp_id='$per' and a.hr_dia='$i' and a.ut_codprog='$codprog'";
$sqlaskh ="select h_horaini as hori, h_horafin as horf, h_horaturn as hort, h_horanum as horn from tb_horario where h_id='$idh'";
$resultcontf = mysqli_query($con,$sqlask);
$resultcontfh = mysqli_query($con,$sqlaskh);
$a=0;
$valor = 0;
//echo $sqlask."<br>";
//echo $sqlaskh;
if(mysqli_num_rows($resultcontfh)>0){
while($rowfh = mysqli_fetch_array($resultcontfh)){
$hini = $rowfh['hori'];
$hfin = $rowfh['horf'];
//echo $hfin."<br>";
}}

if(mysqli_num_rows($resultcontf)>0){
$filaa = array();
$filab = array();
$filac = array();
$filad = array();
$filae = array();
$filaidga = array();
$filaida = array();
$filaidh = array();
while($rowf = mysqli_fetch_array($resultcontf)){
$a = $a + 1;
$filaa[$a]=$rowf['cod'];
$filab[$a]=$rowf['hori'];
$filac[$a]=$rowf['horf'];
$filad[$a]=$rowf['hort'];
$filae[$a]=$rowf['horn'];
$filaidga[$a] = $rowf['idga'];
$filaida[$a] = $rowf['ida'];
$filaidh[$a] = $rowf['idh'];
}}

//$hc=$valor
for($t=1;$t<=count($filaa);$t++){
if($filaidh[$t]!= $idh && $filaida[$t]!= $ida){
if($hfin > $hini){
//echo $hfin."-".$hini."<br>";
if($hfin > $filac[$t]){
	if(($hini > $filab[$t]) && ($hini > $filac[$t])){
	 $hc = $filac[$t] - $hini; break;
	}
	if(($hini < $filab[$t])&& ($filac[$t]>$filab[$t])){
	 $valor = 1; break;
	}
	if($hini < $filac[$t]){
	 $valor = 1; break;
	}
}

if ($hfin < $filac[$t]){
	
	if(($filab[$t] > $hini)&& ($filab[$t] > $hfin)){
	 $hc = $hfin - $filab[$t]; break;
	}
	if($filab[$t] < $hini){
	 $valor = 1; break;
	}
	if($hfin > $filab[$t]){
	 $valor = 1; break;
	}
}
}
if (($hfin == $filac[$t])||($hini == $filab[$t]) || ($hini==$filac[$t] and $hfin==$filab[$t])){
	$valor= 1; break;
	}

}
	if ($filaidh[$t]== $idh && $filaida[$t]!= $ida)
	{ $valor= 1; break;
	}
	if($filaidh[$t]!= $idh && $filaida[$t]== $ida and $hini==$filac[$t] and $hfin==$filab[$t]){
	$valor= 1; break;	
	}
}
//echo "salida ".$hc."<br>";
//echo "salida ".$valor."<br>";

//consulta cruce de horarios en otro servicio
if($valor==0){
$sqlaskcod ="SELECT ug_id as cod FROM tb_usutrab where ut_codprog='$codprog' and tp_id='$per' and uap_anno='$anno' and uap_mes='$mm' and j_id='$idj' and ut_idserva='$ids'";
$resultaskcod = mysqli_query($con,$sqlaskcod);
if(mysqli_num_rows($resultaskcod)!=0){
while($rowc = mysqli_fetch_array($resultaskcod)){
$codper = $rowc['cod'];
$sqlcruceserv="SELECT a.hr_idserva as serva, a.ut_codprog as cod, b.h_horaini as hori, b.h_horafin as horf,b.h_horaturn as hort, b.h_horanum as horn, a.ga_id as idga, a.a_id as ida, a.h_id as idh FROM tb_horareprog".$anno." a inner join tb_horario b on b.h_id = a.h_id where a.uap_anno='$anno' and a.uap_mes='$mm' and a.j_id='$idj' and a.tp_id='$per' and a.hr_dia='$i' and a.ug_id='$codper' and a.s_id='$ids'";
//echo $sqlcruceserv."<br>";
$resultcontfs = mysqli_query($con,$sqlcruceserv);
$a=0;
//$valor = 0;
if(mysqli_num_rows($resultcontfs)>0){
$filaa = array();
$filab = array();
$filac = array();
$filad = array();
$filae = array();
$filaidh = array();
while($rowf = mysqli_fetch_array($resultcontfs)){
if($rowf['cod']==$codprog && $rowf['serva']==$ids && $rowf['idh']==$idh){
$hini = $rowf['hori'];
$hfin = $rowf['horf'];
//echo $hini."<br>";
//echo $hfin."<br>";
}else{
$a = $a + 1;
$filaa[$a]=$rowf['cod'];
$filab[$a]=$rowf['hori'];
$filac[$a]=$rowf['horf'];
$filad[$a]=$rowf['hort'];
$filae[$a]=$rowf['horn'];
$filaidsa[$a] = $rowf['serva'];
$filaidh[$a] = $rowf['idh'];
}
}
//$hc=$valor
for($t=1;$t<=count($filaa);$t++){
if($hfin > $hini){
//echo $hfin."-".$hini."<br>";
if($hfin > $filac[$t]){
	if($hini > $filab[$t]){
	 $hc = $filac[$t] - $hini; break;
	}
	if($hini < $filab[$t]){
	 $valor = 6; break;
	}
}

if ($hfin < $filac[$t]){
	
	if($filab[$t] > $hini){
	 $hc = $hfin - $filab[$t]; break;
	}
	if($filab[$t] < $hini){
	 $valor = 6; break;
	}
}
}
if (($hfin == $filac[$t])||($hini == $filab[$t])){
	$valor= 6; break;
	}

}
}
///
}
}
}
// verifica lo post guardias
//$hdp=$valor
//$valor=0;
//for ($h=1;$h<=count($filaa); $h++){
$da = $i - 1;
$sqldiaprev = "select ut_codprog, hr_tipohora from tb_horareprog".$anno." where j_id='$idj' and uap_mes='$mm' and uap_anno='$anno' and hr_dia='$da' and h_id in (select h_id from tb_horario where h_horaturn='N' and h_horanum='12') and a_id='$ida' and ga_id='$idga' and tp_id='$per' and hr_idserva='$ids' and ut_codprog='$codprog' and hr_dia='$da'";
//echo $sqldiaprev."<br>";
$resultdiap = mysqli_query($con,$sqldiaprev);
if(mysqli_num_rows($resultdiap)>0){
$valor = 2;
}else{
if($i==1 && $mm==1){
$annoa=$anno-1;
$mesa= 12;
$mmesa = mktime( 0, 0, 0, $mesa, 1, $annoa ); 
$udia = intval(date("t",$mmesa)); 
$sqlfinanno= "select ut_codprog, hr_tipohora from tb_horareprog".$anno." where j_id='$idj' and uap_mes='$mesa' and uap_anno='$annoa' and hr_dia='$da' and h_id in (select h_id from tb_horario where h_horaturn='N' and h_horanum='12') and a_id='$ida' and ga_id='$idga' and tp_id='$per' and hr_idserva='$ids' and ut_codprog='$codprog' and hr_dia='$udia'";
$resultfinanno = mysqli_query($con,$sqlfinanno);
if(mysqli_num_rows($resultfinanno)>0){
$valor =2;
}
}else{
		if($i==1){
				$mesa= $mm - 1;
				$mmesa = mktime( 0, 0, 0, $mesa, 1, $anno ); 
				$udia = intval(date("t",$mmesa)); 
				$sqlfinmes= "select ut_codprog, hr_tipohora from tb_horareprog".$anno." where j_id='$idj' and uap_mes='$mesa' and uap_anno='$annoa' and hr_dia='$da' and h_id in (select h_id from tb_horario where h_horaturn='N' and h_horanum='12') and a_id='$ida' and ga_id='$idga' and tp_id='$per' and hr_idserva='$ids' and ut_codprog='$codprog' and hr_dia='$udia'";
				$resultfinmes = mysqli_query($con,$sqlfinmes);
				if(mysqli_num_rows($resultfinmes)>0){
				$valor =2;
				}
		
		}
}
//}

//}
//cierra la secuencia principal
}

//verifica post guardia entre servicios
if($valor==0){
$sqlaskcod ="SELECT ug_id as cod FROM tb_usutrab where ut_codprog='$codprog' and tp_id='$per' and uap_anno='$anno' and uap_mes='$mm' and j_id='$idj' and ut_idserva='$ids'";
$resultaskcod = mysqli_query($con,$sqlaskcod);
if(mysqli_num_rows($resultaskcod)!=0){
while($rowc = mysqli_fetch_array($resultaskcod)){
$codper = $rowc['cod'];
$da = $i - 1;
$sqldiaprev = "select ut_codprog, hr_tipohora from tb_horareprog".$anno." where j_id='$idj' and uap_mes='$mm' and uap_anno='$anno' and hr_dia='$da' and h_id in (select h_id from tb_horario where h_horaturn='N' and h_horanum='12') and tp_id='$per' and ug_id='$codper' and hr_dia='$da'";
//echo $sqldiaprev."<br>";
$resultdiap = mysqli_query($con,$sqldiaprev);
if(mysqli_num_rows($resultdiap)>0){
$valor = 7;
}else{
if($i==1 && $mm==1){
$annoa=$anno-1;
$mesa= 12;
$mmesa = mktime( 0, 0, 0, $mesa, 1, $annoa ); 
$udia = intval(date("t",$mmesa)); 
$sqlfinanno= "select ut_codprog, hr_tipohora from tb_horareprog".$anno." where j_id='$idj' and uap_mes='$mesa' and uap_anno='$annoa' and hr_dia='$da' and h_id in (select h_id from tb_horario where h_horaturn='N' and h_horanum='12') and tp_id='$per' and ug_id='$codper' and hr_dia='$udia'";
$resultfinanno = mysqli_query($con,$sqlfinanno);
if(mysqli_num_rows($resultfinanno)>0){
$valor = 7;
}
}else{
		if($i==1){
				$mesa= $mm - 1;
				$mmesa = mktime( 0, 0, 0, $mesa, 1, $anno ); 
				$udia = intval(date("t",$mmesa)); 
				$sqlfinmes= "select ut_codprog, hr_tipohora from tb_horareprog".$anno." where j_id='$idj' and uap_mes='$mesa' and uap_anno='$annoa' and hr_dia='$da' and h_id in (select h_id from tb_horario where h_horaturn='N' and h_horanum='12') and a_id='$ida' and ga_id='$idga' and tp_id='$per' and hr_idserva='$ids' and ut_codprog='$codprog' and hr_dia='$udia'";
				$resultfinmes = mysqli_query($con,$sqlfinmes);
				if(mysqli_num_rows($resultfinmes)>0){
				$valor =7;
				}
		
		}
}
}
}}
}


//pregunta por cumpleanos
if($valor==0){
$sqlcumple ="SELECT ug_id as cod FROM tb_usutrab where ut_codprog='$codprog' and tp_id='$per' and uap_anno='$anno' and uap_mes='$mm' and j_id='$idj' and ut_idserva='$ids'";
$resultcumple = mysqli_query($con,$sqlcumple);
if(mysqli_num_rows($resultcumple)!=0){
while($rowc = mysqli_fetch_array($resultcumple)){
$codper = $rowc['cod'];
$sqlcumpledia="SELECT * FROM tb_usuasist where day(ua_fechanac)='$i' and month(ua_fechanac)='$mm' and ug_id='$codper'";
$resultcumpledia = mysqli_query($con,$sqlcumpledia);
if(mysqli_num_rows($resultcumpledia)!=0){
$valor =3;
}
}
}}

//pregunta por vacaciones
if($valor==0){
$sqlvaca ="SELECT ug_id as cod FROM tb_usutrab where ut_codprog='$codprog' and tp_id='$per' and uap_anno='$anno' and uap_mes='$mm' and j_id='$idj' and ut_idserva='$ids'";
$resultvaca = mysqli_query($con,$sqlvaca);
if(mysqli_num_rows($resultvaca)!=0){
while($rowv = mysqli_fetch_array($resultvaca)){
$codper = $rowv['cod'];
$fecha = $anno.'-'.$mm.'-'.$i;
$sqlvacadia="SELECT * FROM tb_vacaciones where ug_id='$codper' and v_estado='0' and '$fecha' between v_fechaini and v_fechafin";
$resultvacadia = mysqli_query($con,$sqlvacadia);
if(mysqli_num_rows($resultvacadia)!=0){
$valor =4;
}
}
}}

//pregunta por descanso medico
if($valor==0){
$sqldm ="SELECT ug_id as cod FROM tb_usutrab where ut_codprog='$codprog' and tp_id='$per' and uap_anno='$anno' and uap_mes='$mm' and j_id='$idj' and ut_idserva='$ids'";
$resultdm = mysqli_query($con,$sqldm);
if(mysqli_num_rows($resultdm)!=0){
while($rowdm = mysqli_fetch_array($resultdm)){
$codper = $rowdm['cod'];
$fecha = $anno.'-'.$mm.'-'.$i;
$sqldmdia="SELECT * FROM tb_descmed where ug_id='$codper' and '$fecha' between dm_fechaini and dm_fechafin";
$resultdmdia = mysqli_query($con,$sqldmdia);
if(mysqli_num_rows($resultdmdia)!=0){
$valor =5;
}
}
}}

//cuentas las cantidad de guardias
if($valor==0){
$sqldm ="SELECT ug_id as cod FROM tb_usutrab where ut_codprog='$codprog' and tp_id='$per' and uap_anno='$anno' and uap_mes='$mm' and j_id='$idj' and ut_idserva='$ids'";
$resultdm = mysqli_query($con,$sqldm);
if(mysqli_num_rows($resultdm)!=0){
while($rowdm = mysqli_fetch_array($resultdm)){
$codper = $rowdm['cod'];
$sqlpregdia="select * from tb_horareprog".$anno." a inner join tb_horario b on a.h_id=b.h_id where a.j_id='$idj' and a.hr_idserva='$ids' and a.tp_id='$per' and b.h_horatipo='1' and a.ug_id='$codper' and a.uap_anno='$anno' and a.uap_mes='$mm' and a.hr_dia='$i' and b.h_horaturn in ('D','N') and a.hr_tipohora='0' and a.hr_dia='$i' and a.a_id='$ida' and a.ga_id='$idga' and a.h_id='$idh' and a.ug_id not in (select ug_id from tb_usuasist where tc_idcont='3')";
$sqlasknumg="select count(*) as numg from tb_horareprog".$anno." a inner join tb_horario b on a.h_id=b.h_id where a.j_id='$idj' and a.hr_idserva='$ids' and a.tp_id='$per' 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.hr_tipohora='0' and a.ug_id not in (select ug_id from tb_usuasist where tc_idcont='3')";
//echo $sqlasknumg;
$resultnumg = mysqli_query($con,$sqlasknumg);
$resultdia = mysqli_query($con,$sqlpregdia);
if(mysqli_num_rows($resultnumg)!=0 && mysqli_num_rows($resultdia)!=0){
while($rowng = mysqli_fetch_array($resultnumg)){
$numg = $rowng['numg'];
if($numg>10){
$valor =8;
}}
}}
}}

//cuentas las cantidad de horas extras por dia
if($valor==0){
$sqldm ="SELECT ug_id as cod FROM tb_usutrab where ut_codprog='$codprog' and tp_id='$per' and uap_anno='$anno' and uap_mes='$mm' and j_id='$idj' and ut_idserva='$ids'";
$resultdm = mysqli_query($con,$sqldm);
if(mysqli_num_rows($resultdm)!=0){
while($rowdm = mysqli_fetch_array($resultdm)){
$codper = $rowdm['cod'];
$sqlasknumg="select sum(b.h_horanum) as sumahe 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='$per' 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='$i' and a.hr_tipohora='1'";
//echo $sqlasknumg."<br>";
$resultnumg = mysqli_query($con,$sqlasknumg);
if(mysqli_num_rows($resultnumg)!=0){
while($rowng = mysqli_fetch_array($resultnumg)){
$numg = $rowng['sumahe'];
if($numg>12){
$valor =1;
}}
}}
}}

return $valor;
}

function guardarturno($anno, $mm, $dia, $idj, $idsa, $per, $codprog, $idga, $ida, $idh, $ugid, $thr){
$sqlask ="SELECT ug_id as cod, s_id as ids FROM tb_usutrab where ut_codprog='$codprog' and tp_id='$per' and uap_anno='$anno' and uap_mes='$mm' and j_id='$idj' and ut_idserva='$idsa'";
//echo $sqlask;
$resultask = mysqli_query($con,$sqlask);
if(mysqli_num_rows($resultask)!=0){
while($rowask = mysqli_fetch_array($resultask)){
$codper = $rowask['cod'];
$ids = $rowask['ids'];
$sqlinsert="insert into tb_horareprog".$anno." values ('$dia','$codprog','$idh','$ida','$idga','$per','$codper','$anno','$mm','$ids','$idj','$thr','$idsa','$ugid')";
//echo $sqlinsert;
$resultinsert = mysqli_query($sqlinsert);
return 10;
}}
}
?>