<?php

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");

$anno=$_GET['anno'];
$mes =$_GET['mes'];
//$mes =2;
$idtp=$_GET['idtp'];
$ids=$_GET['ids'];
$idj=$_GET['idj'];
$tipoask=$_GET['tipo'];
//nombre de las jefaturas
$sqljefatura="select j_id as id, j_desc as jefatura from tb_jefatura where j_id='$idj' ";
?>
<html>
<head>
<title>CONSOLIDADO DE HORAS</title>
<link rel="stylesheet" type="text/css" href="../styles/Style.css">
</head>
<body>
<br>
<?php
$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');

$resultf = mysqli_query($con, $sqljefatura);
if(mysqli_num_rows($resultf)!=0){
$cadena = "<table width='280' align='center' class='FacetFormTABLE'><tr><td colspan='3' align='center' class='FacetFieldCaptionTT'>CANT HRS ";
if($tipoask=='1'){
$cadena = $cadena." PROGRAMADAS ";
}
if($tipoask=='2'){
$cadena = $cadena." REPROGRAMADAS ";
}

if($tipoask=='3'){
$cadena = $cadena." EJECUTADAS ";
}

$cadena = $cadena."EN ".$arraymes[$mes-1]['mes']."</td></tr>";
while ($row= mysqli_fetch_array($resultf)){

$sumhrsord=0;
$sumhrsext=0;

$idj = $row['id'];
$jefatura = $row['jefatura'];

$cadena = $cadena."<tr><td colspan='3' align='center' class='FacetFieldCaptionTDR'>".$jefatura."</td></tr>";



$sqljefaturas="select distinct(b.s_id) as id, (select s_desc from tb_servicio where j_id=b.j_id and s_id=b.s_id) as jefatura ";
$sqljefaturas=$sqljefaturas." from tb_horaprog".$anno." b where b.uap_mes='$mes' and b.j_id='$idj' and b.s_id='$ids' order by b.s_id asc ";
//echo $sqljefaturas;
$resultfs = mysqli_query($con, $sqljefaturas);
if(mysqli_num_rows($resultfs)!=0){
while ($rows= mysqli_fetch_array($resultfs)){
$ids = $rows['id'];
$jefaturas = $rows['jefatura'];
$cadena = $cadena."<tr><td colspan='3' align='center' class='FacetFieldCaptionTDT'>".$jefaturas."</td></tr>";
}}
 //pregunta x persona
$sqlxpersona="select tp_desc as descripcion FROM tb_tipoperson where tp_id='$idtp';";
$resultper = mysqli_query($con, $sqlxpersona);
while ($rowtp= mysqli_fetch_array($resultper)){
$descpersona = $rowtp['descripcion'];
$cadena = $cadena."<tr><td colspan='3' align='center' class='FacetFieldCaptionUP'><b>".$descpersona."</b></td></tr>";
}

$sqlagarea="select b.ga_id as id, (select ga_desc from tb_garea where ga_id=a.ga_id) as area from tb_areaservicio a inner join tb_area b on a.a_id=b.a_id and ";
$sqlagarea=$sqlagarea." a.ga_id=b.ga_id where a.s_id='$ids' and a.j_id='$idj' group by b.ga_id";
//echo $sqlagarea."<br>";
$resultgarea = mysqli_query($con, $sqlagarea);
if(mysqli_num_rows($resultgarea)!=0){
while ($rowga= mysqli_fetch_array($resultgarea)){
$idga = $rowga['id'];
$nomgarea = $rowga['area'];

//pregunta por la cantidad de horas ordinarias  y horas extras x grupo area

if($tipoask=='1'){
$sqlask = "select sum(shp_numho) as hord,sum(shp_numhe) as hext   from tb_subhoraprog".$anno." where  ga_id='$idga' and uap_anno='$anno'
and uap_mes='$mes' and hp_idserva='$ids' and j_id='$idj' and tp_id='$idtp' group by ga_id";	
/*	
$sqlask="select a.ga_id as gaid, (SELECT sum(b.h_horanum) FROM tb_horaprog c inner join tb_horario b on c.h_id = b.h_id ";
$sqlask=$sqlask." where c.hp_tipohora=0 and c.hp_idserva=a.hp_idserva and c.j_id=a.j_id and c.tp_id=a.tp_id and c.ga_id=a.ga_id and c.uap_anno=a.uap_anno and c.uap_mes=a.uap_mes ";
$sqlask=$sqlask." and b.h_horatipo='1' group by c.ga_id) as hord, (SELECT sum(b.h_horanum) FROM tb_horaprog c inner join tb_horario b on c.h_id = b.h_id ";
$sqlask=$sqlask." where c.hp_tipohora=1 and c.hp_idserva=a.hp_idserva and c.j_id=a.j_id and c.tp_id=a.tp_id and c.ga_id=a.ga_id and c.uap_anno=a.uap_anno ";
$sqlask=$sqlask." and c.uap_mes=a.uap_mes and b.h_horatipo='1'group by c.ga_id) as hext from tb_horaprog a where a.ga_id='$idga' and a.uap_anno='$anno' ";
$sqlask=$sqlask." and a.uap_mes='$mes' and a.hp_idserva='$ids' and a.j_id='$idj' and a.tp_id='$idtp' group by a.ga_id ";
*/
}
if($tipoask=='2'){
$sqlask = "select sum(shr_numho) as hord,sum(shr_numhe) as hext  from tb_subhorareprog".$anno." where  ga_id='$idga' and uap_anno='$anno'
and uap_mes='$mes' and hr_idserva='$ids' and j_id='$idj' and tp_id='$idtp' group by ga_id";	
/*	
$sqlask="select a.ga_id as gaid, (SELECT sum(b.h_horanum) FROM tb_horareprog c inner join tb_horario b on c.h_id = b.h_id ";
$sqlask=$sqlask." where c.hr_tipohora=0 and c.hr_idserva=a.hr_idserva and c.j_id=a.j_id and c.tp_id=a.tp_id and c.ga_id=a.ga_id and c.uap_anno=a.uap_anno and c.uap_mes=a.uap_mes ";
$sqlask=$sqlask." and b.h_horatipo='1' group by c.ga_id) as hord, (SELECT sum(b.h_horanum) FROM tb_horareprog c inner join tb_horario b on c.h_id = b.h_id ";
$sqlask=$sqlask." where c.hr_tipohora=1 and c.hr_idserva=a.hr_idserva and c.j_id=a.j_id and c.tp_id=a.tp_id and c.ga_id=a.ga_id and c.uap_anno=a.uap_anno ";
$sqlask=$sqlask." and c.uap_mes=a.uap_mes and b.h_horatipo='1'group by c.ga_id) as hext from tb_horareprog a where a.ga_id='$idga' and a.uap_anno='$anno' ";
$sqlask=$sqlask." and a.uap_mes='$mes' and a.hr_idserva='$ids' and a.j_id='$idj' and a.tp_id='$idtp' group by a.ga_id ";
*/
}

if($tipoask=='3'){
$sqlask = "select sum(she_numho) as hord,sum(she_numhe) as hext  from tb_subhoraeje".$anno." where  ga_id='$idga' and uap_anno='$anno'
and uap_mes='$mes' and he_idserva='$ids' and j_id='$idj' and tp_id='$idtp' group by ga_id";	
/*
$sqlask="select a.ga_id as gaid, (SELECT sum(b.h_horanum) FROM tb_horaeje c inner join tb_horario b on c.h_id = b.h_id ";
$sqlask=$sqlask." where c.he_tipohora=0 and c.he_idserva=a.he_idserva and c.j_id=a.j_id and c.tp_id=a.tp_id and c.ga_id=a.ga_id and c.uap_anno=a.uap_anno and c.uap_mes=a.uap_mes ";
$sqlask=$sqlask." and b.h_horatipo='1' group by c.ga_id) as hord, (SELECT sum(b.h_horanum) FROM tb_horaeje c inner join tb_horario b on c.h_id = b.h_id ";
$sqlask=$sqlask." where c.he_tipohora=1 and c.he_idserva=a.he_idserva and c.j_id=a.j_id and c.tp_id=a.tp_id and c.ga_id=a.ga_id and c.uap_anno=a.uap_anno ";
$sqlask=$sqlask." and c.uap_mes=a.uap_mes and b.h_horatipo='1'group by c.ga_id) as hext from tb_horaeje a where a.ga_id='$idga' and a.uap_anno='$anno' ";
$sqlask=$sqlask." and a.uap_mes='$mes' and a.he_idserva='$ids' and a.j_id='$idj' and a.tp_id='$idtp' group by a.ga_id ";
*/
}
//echo $sqlask."<br>";
$resultdia = mysqli_query($con, $sqlask);

if(mysqli_num_rows($resultdia)!=0){
$cadena = $cadena."<tr><td align='center' class='FacetFieldCaptionTL'>AREA DEL SERVICIO</td><td align='center' class='FacetFieldCaptionTL'>HRS ORD</td><td align='center' class='FacetFieldCaptionTL'>HRS EXT</td></tr>";
$color = 1;
while ($rownp= mysqli_fetch_array($resultdia)){
$hrsordsuma = $rownp['hord'];
$sumhrsord = $sumhrsord + $hrsordsuma; 
$hrsextsuma = $rownp['hext'];
$sumhrsext = $sumhrsext + $hrsextsuma; 
if($color==1){
$cadena = $cadena."<tr><td class='FacetFieldCaptionON'>".$nomgarea."</td><td align='center' class='FacetFieldCaptionON'>".$hrsordsuma."</td><td align='center' class='FacetFieldCaptionON'>".$hrsextsuma."</td></tr>";
$color =0;
}else{
$cadena = $cadena."<tr><td class='FacetFieldCaptionUP'>".$nomgarea."</td><td align='center' class='FacetFieldCaptionUP'>".$hrsordsuma."</td><td align='center' class='FacetFieldCaptionON'>".$hrsextsuma."</td></tr>";
$color=1;
}
}
}//termina hrs grupo area


}}
//}}

}}
$cadena = $cadena."<tr><td align='center' class='FacetFieldCaptionTL'>TOTALES </td><td align='center' class='FacetFieldCaptionTL'>".$sumhrsord."</td><td align='center' class='FacetFieldCaptionTL'>".$sumhrsext."</td></tr>";
$cadena = $cadena."</table>";
echo $cadena;
?>
</body>
</html>