<?php

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

$Objcon = new Conectar();
$conex = $Objcon->con();


$ugid=$_GET['ugid'];

echo "<input type='hidden' id='ugid' name='ugid' value='".$ugid."'>";
//$div=$_GET['div'];

//$idtp=1;
//$ids=2;
//$idj=1;
//and b.tp_id='$idtp' 

function restaFechas($dFecIni, $dFecFin)
{
    $dFecIni = str_replace("-","",$dFecIni);
    $dFecIni = str_replace("/","",$dFecIni);
    $dFecFin = str_replace("-","",$dFecFin);
    $dFecFin = str_replace("/","",$dFecFin);

    $diai=substr($dFecIni, 0, 2);
	$mesi=substr($dFecIni, 2, 2);
	$annoi=substr($dFecIni, 4, 4);
	
	$diaf=substr($dFecFin, 0, 2);
	$mesf=substr($dFecFin, 2, 2);
	$annof=substr($dFecFin, 4, 4);

    //preg_match( "/([0-9]{1,2})([0-9]{1,2})([0-9]{2,4})/", $dFecIni, $aFecIni);
    //preg_match( "/([0-9]{1,2})([0-9]{1,2})([0-9]{2,4})/", $dFecFin, $aFecFin);

    $date1 = mktime(0,0,0,$mesi, $diai, $annoi);
    $date2 = mktime(0,0,0,$mesf, $diaf, $annof);

    return round(($date2 - $date1) / (60 * 60 * 24)) ;
}

if(isset($_GET['idj'])&& isset($_GET['anno']) && isset($_GET['mes']) && isset($_GET['idtp'])){
$idj=$_GET['idj'];
$idtp=$_GET['idtp'];
$anno=$_GET['anno'];
$mm=$_GET['mes'];
}
//echo $idtp."<br>";
//listar servicios
$sqldepa="select j_id as codj, 0 as cods ,j_abrev as nom from tb_jefatura where j_id='$idj'";
$sqlserv="select j_id as codj, s_id as cods, s_abrev as nom from tb_servicio where j_id='$idj'";
$resultdepa = mysqli_query($conex, $sqldepa);
$resultserv = mysqli_query($conex, $sqlserv);
$arrayids = array();
while ($rowj= mysqli_fetch_array($resultdepa)){
$arrayids[] = $rowj['codj'];
}
while ($rows= mysqli_fetch_array($resultserv)){
$arrayids[] = $rows['cods'];
}


if(isset($_GET['ids']) && $_GET['ids']<>-1){
$ids = $_GET['ids'];
	if($_GET['idtp']!=0){
	$sqllistado = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ', a.ua_nom) as nombre, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tipopersona, ";
	$sqllistado = $sqllistado." (select s_abrev from tb_servicio where s_id=b.s_id and j_id=b.j_id) as servicio from tb_usuasist a inner join tb_usudep b on ";
	$sqllistado = $sqllistado." a.ug_id = b.ug_id where b.j_id='$idj' and b.s_id='$ids' and b.tp_id='$idtp' and b.ua_estado='1'  order by tipopersona, nombre";
	}else{
	$sqllistado = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ', a.ua_nom) as nombre, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tipopersona, ";
	$sqllistado = $sqllistado." (select s_abrev from tb_servicio where s_id=b.s_id and j_id=b.j_id) as servicio from tb_usuasist a inner join tb_usudep b on ";
	$sqllistado = $sqllistado." a.ug_id = b.ug_id where b.j_id='$idj' and b.s_id='$ids' and b.ua_estado='1' order by tipopersona, nombre";
	}
}elseif($_GET['idtp']!=0){
$sqllistado = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ', a.ua_nom) as nombre, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tipopersona, ";
$sqllistado = $sqllistado." (select s_abrev from tb_servicio where s_id=b.s_id and j_id=b.j_id) as servicio from tb_usuasist a inner join tb_usudep b on ";
$sqllistado = $sqllistado." a.ug_id = b.ug_id where b.j_id='$idj' and b.tp_id='$idtp'  and b.ua_estado='1' order by tipopersona, nombre";
}else{
$sqllistado = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ', a.ua_nom) as nombre, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tipopersona, ";
$sqllistado = $sqllistado." (select s_abrev from tb_servicio where s_id=b.s_id and j_id=b.j_id) as servicio from tb_usuasist a inner join tb_usudep b on ";
$sqllistado = $sqllistado." a.ug_id = b.ug_id where b.j_id='$idj' and b.ua_estado='1' order by tipopersona, nombre";
}

//echo $sqllistado;
$resultlistado = mysqli_query($conex, $sqllistado);
//echo $sqllistado;

$numerohoras="";
$aa=0;
$salida = "<table border='0' align='center' class='TableDiseno' >";
$salida = $salida."<tr><td align='center' colspan='10' class='LlenarTablaGareaTitulo'>LISTADO DEL PERSONAL</td></tr>";
$salida = $salida."<tr><td align='center' width='280' class='LlenarTablaGareaTituloUNO'>APELLIDOS Y NOMBRES</td><td align='center' width='90' class='LlenarTablaGareaTituloUNO'>SERVICIO</td><td align='center' width='160' class='LlenarTablaGareaTituloUNO'>TIPO DE PERSONAL</td><td align='center' width='100' class='LlenarTablaGareaTituloUNO'>F. INICIO</td><td align='center' width='100' class='LlenarTablaGareaTituloUNO'>F. FIN</td><td align='center' width='80' class='LlenarTablaGareaTituloUNO'>NRO DIAS</td><td align='center' width='70' class='LlenarTablaGareaTituloUNO'>DIAS EN EL MES</td><td align='center' width='80' class='LlenarTablaGareaTituloUNO'>NRO HORAS</td><td align='center' width='180' class='LlenarTablaGareaTituloUNO'>OBSERVACIONES</td><td align='center' width='40' class='LlenarTablaGareaTituloUNO'>INF</td></tr>";
while ($rowl= mysqli_fetch_array($resultlistado)){
$codigo=$rowl['cod'];
$nombre=$rowl['nombre'];
$tipopersona=$rowl['tipopersona'];
$servicio=$rowl['servicio'];

$fechaask= $anno.'/'.$mm.'/01';
  
$sqlaskdm = " select a.lc_id as id, a.lc_fechaini as ffini, date_format(a.lc_fechaini,'%d/%m/%Y') as fini, a.lc_fechafin as fffin, date_format(a.lc_fechafin,'%d/%m/%Y') as ffin, a.lc_numdias as ndias, ";
$sqlaskdm = $sqlaskdm." (select tlc_desc as desclic from tb_tplicencia where tlc_id=a.tlc_id) as desclic from tb_licencias a where ";
$sqlaskdm = $sqlaskdm." ug_id='$codigo' and ((year(a.lc_fechaini)='$anno' and month(a.lc_fechaini)='$mm') or (year(a.lc_fechafin)='$anno' and month(a.lc_fechafin)='$mm') or ('$fechaask' between a.lc_fechaini and a.lc_fechafin)) ";
$resultdm = mysqli_query($conex, $sqlaskdm);
//echo $sqlaskdm."<br>";
while ($row= mysqli_fetch_array($resultdm)){
$iddm=$row['id'];
$fini=$row['fini'];
$ffin=$row['ffin'];
$ndias=$row['ndias'];
$obs=$row['desclic'];
$ffini=$row['ffini'];
$fffin=$row['fffin'];

$diaini=substr($fini, 0, 2);
$diafin=substr($ffin, 0, 2);
$mesini=substr($fini, 3, 2);
$mesfin=substr($ffin, 3, 2);
$annoini=substr($fini, 6, 4);
$annofin=substr($ffin, 6, 4);

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

//pregunta por servicicios

if($mm<10){
$mmm = '0'.$mm;
}
//pregunta por horas del descanso medicos afectado
//$sql = "SELECT (select sum(h_horanum) from tb_horario where h_id=a.h_id) as num FROM tb_horasusp_lic a where a.ug_id='$codigo' and date(concat(a.uap_anno,'-',a.uap_mes,'-',a.hl_dia)) between date('$ffini') and date('$fffin')";
$sql = "SELECT a.uap_mes as mes, a.uap_anno as anno, sum(b.h_horanum) as num FROM tb_horasusp_lic a inner join tb_horario b on a.h_id=b.h_id where a.ug_id='$codigo' and date(concat(a.uap_anno,'-',a.uap_mes,'-',a.hl_dia)) between date('$ffini') and date('$fffin') and a.uap_anno='$anno' and a.uap_mes='$mm' group by a.uap_mes, a.uap_anno";

$resultsql = mysqli_query($conex, $sql);
if(mysqli_num_rows($resultsql)>0){


	while ($rowl= mysqli_fetch_array($resultsql)){
	$nhrs = $rowl['num'];
		$afin = $numeroDeDias.'/'.$mmm.'/'.$anno;
		$numdiasmes = restaFechas($fini,$afin) + 1;
		$salida = $salida."<tr><td class ='FacetDataTextC'>".$nombre."</td><td align='center' class ='FacetDataTextC'>".$servicio."</td><td align='center' class ='FacetDataTextC'>".$tipopersona."</td><td align='center' class ='FacetDataTextC'>".$fini."</td><td align='center' class ='FacetDataTextC'>".$ffin."</td><td align='center' class ='FacetDataTextC'>".$ndias."</td><td align='center' class ='FacetDataTextC'>".$numdiasmes."</td><td align='center' class ='FacetDataTextC'>".$nhrs."</td><td align='center' class ='FacetDataTextC'>".$obs."</td><td align='center' class ='FacetDataTextC'><input type='checkbox' name='lista[".$aa."]' id='lista[".$aa."]'</td></tr>";
		$cadena1 = $cadena1."<input type='hidden' id='idcoda[".$aa."]' name='idcoda[".$aa."]' value='".$codigo."'>";
		$cadena2 = $cadena2."<input type='hidden' id='iddma[".$aa."]' name='iddma[".$aa."]' value='".$iddm."'>";
		$aa = $aa +1;
		$numerohoras = $numerohoras + $nhrs;
	
	}
}else{
$sql2 = " SELECT a.uap_mes as mes, a.uap_anno as anno, sum(b.h_horanum) as num FROM tb_horaprog a inner join tb_horario b on a.h_id=b.h_id where a.ug_id='$codigo' and date(concat(a.uap_anno,'-',a.uap_mes,'-',a.hp_dia)) between date('$ffini') and date('$fffin') and a.uap_anno='$anno' and a.uap_mes='$mm' group by a.uap_mes, a.uap_anno";

$resultsql2 = mysqli_query($conex,$sql2);

if(mysqli_num_rows($resultsql2)>0){

	while ($rowl= mysqli_fetch_array($resultsql2)){
	$nhrs = $rowl['num'];
		$afin = $numeroDeDias.'/'.$mmm.'/'.$anno;
		if($mesfin > $mm or  $annofin > $anno){
		$numdiasmes = restaFechas($fini,$afin) + 1;
		}else{
		$numdiasmes = $ndias;
		}
		$salida = $salida."<tr><td class ='FacetDataTextC'>".$nombre."</td><td align='center' class ='FacetDataTextC'>".$servicio."</td><td align='center' class ='FacetDataTextC'>".$tipopersona."</td><td align='center' class ='FacetDataTextC'>".$fini."</td><td align='center' class ='FacetDataTextC'>".$ffin."</td><td align='center' class ='FacetDataTextC'>".$ndias."</td><td align='center' class ='FacetDataTextC'>".$numdiasmes."</td><td align='center' class ='FacetDataTextC'>".$nhrs."</td><td align='center' class ='FacetDataTextC'>".$obs."</td><td align='center' class ='FacetDataTextC'><input type='checkbox' name='lista[".$aa."]' id='lista[".$aa."]'</td></tr>";
		$cadena1 = $cadena1."<input type='hidden' id='idcoda[".$aa."]' name='idcoda[".$aa."]' value='".$codigo."'>";
		$cadena2 = $cadena2."<input type='hidden' id='iddma[".$aa."]' name='iddma[".$aa."]' value='".$iddm."'>";
		$aa = $aa +1;
		$numerohoras = $numerohoras + $nhrs;
	   }
	}else{
		//echo "3<br>";
		$nhrs =0;
		$afin = $numeroDeDias.'/'.$mmm.'/'.$anno;
		if($mesfin > $mm or  $annofin > $anno){
		$numdiasmes = restaFechas($fini,$afin) + 1;
		}else{
		$numdiasmes = $ndias;
		}
		$salida = $salida."<tr><td class ='FacetDataTextC'>".$nombre."</td><td align='center' class ='FacetDataTextC'>".$servicio."</td><td align='center' class ='FacetDataTextC'>".$tipopersona."</td><td align='center' class ='FacetDataTextC'>".$fini."</td><td align='center' class ='FacetDataTextC'>".$ffin."</td><td align='center' class ='FacetDataTextC'>".$ndias."</td><td align='center' class ='FacetDataTextC'>".$numdiasmes."</td><td align='center' class ='FacetDataTextC'>0</td><td align='center' class ='FacetDataTextC'>".$obs."</td><td align='center' class ='FacetDataTextC'><input type='checkbox' name='lista[".$aa."]' id='lista[".$aa."]'</td></tr>";
		$cadena1 = $cadena1."<input type='hidden' id='idcoda[".$aa."]' name='idcoda[".$aa."]' value='".$codigo."'>";
		$cadena2 = $cadena2."<input type='hidden' id='iddma[".$aa."]' name='iddma[".$aa."]' value='".$iddm."'>";
		$aa = $aa +1;
		$numerohoras = $numerohoras + $nhrs;
	}
//termina del analisis
}
}

}
$salida = $salida."<tr><td align='center' colspan='10' class ='FacetDataTextC'><b>NUMERO DE HORAS AFECTADAS    ".$numerohoras."</b></td></tr>";
$salida = $salida."</table>";


echo $salida;
echo $cadena1;
echo $cadena2;
echo "<input type='hidden' id='numlic' name='numlic' value='".$aa."'>";
?>

