<html>
<head>
<link rel="stylesheet" type="text/css" href="styles/Style.css">
<script language="JavaScript" type="text/javascript" src="ajax.js"></script>
</head>
<body>
<?php
date_default_timezone_set('UTC');
//$iddepa=$_GET['departamento'];
//$idserv=$_GET['servicio'];
//$anno=$_GET['anno'];
//$mm=$_GET['mes'];
session_start();
//echo $_SESSION['nombre']."<br>";
//echo $_SESSION['usuario'];

//$idhora=$_GET['idhora'];
$mes = mktime( 0, 0, 0, $mm, 1, $anno ); 
$numeroDeDias = intval(date("t",$mes)); 
//echo $anno;

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

//algunos datos


$sqldepartamento="select iddepa, descdepa from tbdepartamento where iddepa='$iddepa'";
$resultdepa = mysql_query($sqldepartamento);
while ($row= mysql_fetch_array($resultdepa)){
$depa = $row['descdepa'];
}
$sqlservicio="select idserv, descserv from tbservicio where iddepa='$iddepa' and idserv='$idserv'";
$resultserv = mysql_query($sqlservicio);
while ($row= mysql_fetch_array($resultserv)){
$serv = $row['descserv'];
}

$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');

?>

<table>
<tr><td colspan="36" align="center"><?php echo $depa;?> </td>
<tr><td colspan="36" align="center"><?php echo $serv;?> </td>
<tr><td colspan="36" align="center">PROGRAMACION DEL PERSONAL - <?php echo $tper;?> - MES DE <?php echo $arraymes[$mm-1]['mes'];?> </td>
<?php
echo "<tr><td rowspan='2' class='facetfilaTD'>AREA</td><td rowspan='2' class='facetfilaTD'>HORARIO</td><td rowspan='2' class='facetfilaTD'>TURNO</td><td rowspan='2' class='facetfilaTD'>NH</td>";

for ($i=1;$i<=$numeroDeDias;$i++){

$dia= date("w",mktime( 0, 0, 0, $mm, $i, $anno )); 
switch ($dia) {
    case 0:
        echo "<td align='center' class='facetdataTDR'><b>DOM</b></td>";
        break;
    case 1:
        echo "<td align='center' class='facetdataTD'>LUN</td>";
        break;
    case 2:
        echo "<td align='center' class='facetdataTD'>MAR</td>";
        break;
	case 3:
        echo "<td align='center' class='facetdataTD'>MIE</td>";
        break;
	case 4:
        echo "<td align='center' class='facetdataTD'>JUE</td>";
        break;
	case 5:
        echo "<td align='center' class='facetdataTD'>VIE</td>";
        break;
	case 6:
        echo "<td align='center' class='facetdataTD'>SAB</td>";
        break;
}
}
echo "<td rowspan='2' class='facetfilaTD'><b>EDIT</b></td></tr><tr>";

for ($i=1;$i<=$numeroDeDias;$i++){

$dia= date("w",mktime( 0, 0, 0, $mm, $i, $anno )); 
switch ($dia) {
    case 0:
        echo "<td align='center' class='facetfilaTD'><b>$i</b></td>";
        break;
    case 1:
    case 2:
    case 3:
    case 4:
    case 5:
    case 6:
        echo "<td align='center' class='facetfilaTD'>$i</td>";;
        break;
}
}
echo"</tr>";

$sqlarea= "select idarea, descarea from tbareas where idarea in (SELECT idarea from tbdetalle where iddepa='$iddepa' and idserv='$idserv' and year(dia)='$anno' and month(dia)='$mm' and nivel = '$per' group by idarea)";
$resultarea = mysql_query($sqlarea);


while ($row= mysql_fetch_array($resultarea)){
$idarea = $row['idarea'];

$sqlnumturnoarea="select idhora as num from tbdetalle where idarea='$idarea' and iddepa='$iddepa' and idserv='$idserv' and year(dia)='$anno' and month(dia)='$mm' and nivel = '$per' group by idhora"; 
$resultnumturnoarea = mysql_query($sqlnumturnoarea);
//while ($rowturno = mysql_fetch_array($resultnumturnoarea))
$filas = mysql_num_rows($resultnumturnoarea);
echo "<tr><td rowspan='".$filas."' class ='FacetDataTextC'>".$row['descarea']."</td>";
//}
$sqlturno = "select a.idhora as idhora, concat(b.horaini,' - ',b.horafin) as hora, b.horaturno as turno, b.horanum as numhora from  tbdetalle a inner join tbhorario b on a.idhora=b.idhora where a.iddepa='$iddepa' and a.idserv='$idserv' and a.idarea='$idarea' and month(a.dia)='$mm' and year(a.dia)='$anno' and nivel = '$per' group by a.idhora";
$resultturno = mysql_query($sqlturno);
while ($row= mysql_fetch_array($resultturno)){
$turnottt=$row['turno'];
echo "<td align='center' class ='FacetDataTextC'>".$row['hora']."</td><td align='center' class ='FacetDataTextC'>".$row['turno']."</td><td align='center' class ='FacetDataTextC'>".$row['numhora']."</td>";
$idhora = $row['idhora'];
for ($i=1;$i<=$numeroDeDias;$i++){
$sqldetalle="select codprogra ,tipohora from tbdetalle where iddepa='$iddepa' and idserv='$idserv' and month(dia)='$mm' and year(dia)='$anno' and diaprogra='$i' and idhora='$idhora'and idarea='$idarea' and nivel = '$per'";
$resultdetalle = mysql_query($sqldetalle);
$color =0;
$colorn =0;
$colorusu = 0;
if(mysql_num_rows($resultdetalle)==0){

$valor ="";
}else{

while ($row= mysql_fetch_array($resultdetalle)){

$cp =$row['codprogra'];
$sqlvalida = "select sum(b.horanum) as horas from tbdetalle a inner join tbhorario b on a.idhora = b.idhora where a.codprogra ='$cp' and a.iddepa='$iddepa' and a.idserv='$idserv' and a.nivel ='$per' and year(a.dia)='$anno' and month(dia)='$mm' and a.diaprogra= '$i'";
$resultvalida = mysql_query($sqlvalida);
while ($rowval= mysql_fetch_array($resultvalida)){
$canthrsdia = $rowval['horas'];
}
if($canthrsdia > 18){
$color = 1;
}elseif($canthrsdia == 18){
$sqlt = "select b.horaturno as turno from tbdetalle a inner join tbhorario b on a.idhora = b.idhora where a.codprogra ='$cp' and a.iddepa='$iddepa' and a.idserv='$idserv' and a.nivel= '$per' and year(a.dia)='$anno' and month(dia)='$mm' and a.diaprogra= '$i' order by b.horaturno asc";
$resultt = mysql_query($sqlt);
$tt[] = array();
$a= 0;
while ($rowt= mysql_fetch_array($resultt)){
$tt[$a] = $rowt['turno'];
$a = $a +1;
}
if (($tt[0]=='M' and $tt[1]=='N') or ($tt[0]=='N' and $tt[1]=='M')){
}else{
$color = 1;
}}elseif($canthrsdia == 12){
$sqlt = "select b.horaturno as turno from tbdetalle a inner join tbhorario b on a.idhora = b.idhora where a.codprogra ='$cp' and a.iddepa='$iddepa' and a.idserv='$idserv' and a.nivel= '$per' and year(a.dia)='$anno' and month(dia)='$mm' and a.diaprogra= '$i' order by b.horaturno asc";
$resultt = mysql_query($sqlt);
$tt[] = array();
$a= 0;
while ($rowt= mysql_fetch_array($resultt)){
$tt[$a] = $rowt['turno'];
$a = $a +1;
}
if (($tt[0]=='M' and $tt[1]=='T') or ($tt[0]=='T' and $tt[1]=='M') or $tt[0]=='D' or $tt[0]=='N'){
$color =0;
}else{
$color = 1;
}}

if ($row['codprogra']==true and $row['tipohora']==1){
$valor = $row['codprogra']."e";
}elseif($row['codprogra']==true and $row['tipohora']==0){
$valor = $row['codprogra'];}

//guardias nocturnas
$j = $i + 1;
$sqlnoche="select * from tbdetalle a inner join tbhorario b on
a.idhora = b.idhora where a.iddepa='$iddepa' and a.idserv='$idserv' and month(a.dia)='$mm'
and year(a.dia)='$anno' and a.diaprogra='$j'
and a.nivel = '$per' and a.codprogra='$cp'";
$resultnoche = mysql_query($sqlnoche);

if(mysql_num_rows($resultnoche)>0 and $turnottt=='N'){
$colorn = 1;
}

//valida usuario
$sqlusu="select * from tdtrabservicio where codprog='$cp' and iddepa='$iddepa' and idserv='$idserv' and nivel='$per' and mes='$mm' and anno='$anno'";
$resultusu = mysql_query($sqlusu);

if(mysql_num_rows($resultusu)==0){
$colorusu = 1;
}


}}
$dia= date("w",mktime( 0, 0, 0, $mm, $i, $anno )); 
switch ($dia) {
    case 0: echo "<td align='center' "; if ($color ==1){ echo  "bgcolor='red'";}elseif($colorn==1){echo  "bgcolor='green'";}elseif($colorusu==1){echo  "bgcolor='golden'";}else{echo "class ='FacetDataText'";} echo "><b>".$valor."</b></td>";break;
    case 1: 
    case 2:
    case 3:
    case 4:
    case 5:
    case 6:
        echo "<td align='center'"; if ($color ==1){ echo  "bgcolor='red'";}elseif($colorn==1){echo  "bgcolor='green'";}elseif($colorusu==1){echo  "bgcolor='golden'";}else{ echo "class ='FacetDataTextR'";} echo ">" .$valor."</td>";break;
}
} echo "<td class ='FacetDataTextC' align='center'><a href='editarturno.php?iddepa=$iddepa&idserv=$idserv&anno=$anno&mes=$mm&hora=$idhora&area=$idarea&nivel=$per'><img src='icono/b_edit.png'></a></td></tr>";}}


?>



<table>
</body>
</html>