<?php
header("Content-Type: application/vnd.ms-excel");
header("content-disposition: attachment;filename=listadovacaciones.xls");

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

//$idesp=$_GET['esp'];
//$idtp=$_GET['tp'];

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

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

    $date1 = mktime(0,0,0,$aFecIni[2], $aFecIni[1], $aFecIni[3]);
    $date2 = mktime(0,0,0,$aFecFin[2], $aFecFin[1], $aFecFin[3]);

    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'];
$est=$_GET['est'];
}
//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 = mysql_query($sqldepa);
$resultserv = mysql_query($sqlserv);
$arrayids = array();
while ($rowj= mysql_fetch_array($resultdepa)){
$arrayids[] = $rowj['codj'];
}
while ($rows= mysql_fetch_array($resultserv)){
$arrayids[] = $rows['cods'];
}


if(isset($_GET['ids']) && $_GET['ids']<>-1){
$ids = $_GET['ids'];
	if($_GET['idtp']!=0){
	$sqllistado = " SELECT  concat(b.ua_apepat,' ',b.ua_apemat,' ',b.ua_nom) as nombre,	";
	$sqllistado = $sqllistado." (select s_abrev from tb_servicio where s_id=c.s_id and j_id=c.j_id) as servicio, (select tp_desc from tb_tipoperson where tp_id=c.tp_id) as tipo_personal, ";
	$sqllistado = $sqllistado." a.v_periodo as periodo, a.v_estado as est, date_format(a.v_fechaini,'%d/%m/%Y') as fini, date_format(a.v_fechafin,'%d/%m/%Y') as ffin, a.v_numdias as ndias ";
	$sqllistado = $sqllistado." FROM tb_vacaciones a inner join tb_usuasist b on a.ug_id= b.ug_id inner join tb_usudep c on b.ug_id=c.ug_id where a.v_estado='$est' and (month(a.v_fechaini)='$mm' or ";
	$sqllistado = $sqllistado." month(a.v_fechafin)='$mm') and (year(a.v_fechaini)='$anno' or year(a.v_fechafin)='$anno') and c.j_id='$idj' and c.s_id='$ids' and c.tp_id='$idtp' order by tipo_personal, nombre";
	}else{
	$sqllistado = " SELECT  concat(b.ua_apepat,' ',b.ua_apemat,' ',b.ua_nom) as nombre,	";
	$sqllistado = $sqllistado." (select s_abrev from tb_servicio where s_id=c.s_id and j_id=c.j_id) as servicio, (select tp_desc from tb_tipoperson where tp_id=c.tp_id) as tipo_personal, ";
	$sqllistado = $sqllistado." a.v_periodo as periodo, a.v_estado as est, date_format(a.v_fechaini,'%d/%m/%Y') as fini, date_format(a.v_fechafin,'%d/%m/%Y') as ffin, a.v_numdias as ndias ";
	$sqllistado = $sqllistado." FROM tb_vacaciones a inner join tb_usuasist b on a.ug_id= b.ug_id inner join tb_usudep c on b.ug_id=c.ug_id where a.v_estado='$est' and (month(a.v_fechaini)='$mm' or ";
	$sqllistado = $sqllistado." month(a.v_fechafin)='$mm') and (year(a.v_fechaini)='$anno' or year(a.v_fechafin)='$anno') and c.j_id='$idj' and c.s_id='$ids' order by tipo_personal, nombre";
	}
}elseif($_GET['idtp']!=0){
	$sqllistado = " SELECT  concat(b.ua_apepat,' ',b.ua_apemat,' ',b.ua_nom) as nombre,	";
	$sqllistado = $sqllistado." (select s_abrev from tb_servicio where s_id=c.s_id and j_id=c.j_id) as servicio, (select tp_desc from tb_tipoperson where tp_id=c.tp_id) as tipo_personal, ";
	$sqllistado = $sqllistado." a.v_periodo as periodo, a.v_estado as est, date_format(a.v_fechaini,'%d/%m/%Y') as fini, date_format(a.v_fechafin,'%d/%m/%Y') as ffin, a.v_numdias as ndias ";
	$sqllistado = $sqllistado." FROM tb_vacaciones a inner join tb_usuasist b on a.ug_id= b.ug_id inner join tb_usudep c on b.ug_id=c.ug_id where a.v_estado='$est' and (month(a.v_fechaini)='$mm' or ";
	$sqllistado = $sqllistado." month(a.v_fechafin)='$mm') and (year(a.v_fechaini)='$anno' or year(a.v_fechafin)='$anno') and c.j_id='$idj' and c.tp_id='$idtp' order by tipo_personal, nombre";
}else{
	$sqllistado = " SELECT  concat(b.ua_apepat,' ',b.ua_apemat,' ',b.ua_nom) as nombre,	";
	$sqllistado = $sqllistado." (select s_abrev from tb_servicio where s_id=c.s_id and j_id=c.j_id) as servicio, (select tp_desc from tb_tipoperson where tp_id=c.tp_id) as tipo_personal, ";
	$sqllistado = $sqllistado." a.v_periodo as periodo, a.v_estado as est, date_format(a.v_fechaini,'%d/%m/%Y') as fini, date_format(a.v_fechafin,'%d/%m/%Y') as ffin, a.v_numdias as ndias ";
	$sqllistado = $sqllistado." FROM tb_vacaciones a inner join tb_usuasist b on a.ug_id= b.ug_id inner join tb_usudep c on b.ug_id=c.ug_id where a.v_estado='$est' and (month(a.v_fechaini)='$mm' or ";
	$sqllistado = $sqllistado." month(a.v_fechafin)='$mm') and (year(a.v_fechaini)='$anno' or year(a.v_fechafin)='$anno') and c.j_id='$idj' order by tipo_personal, nombre";
}

//echo $sqllistado;
$resultlistado = mysql_query($sqllistado);
//echo $sqllistado;

$numerohoras="";
$aa=0;
$salida = "<table border='0' align='center' class='TableDiseno' >";
$salida = $salida."<tr><td align='center' colspan='7' >LISTADO DEL PERSONAL CON VACACIONES</td></tr>";
$salida = $salida."<tr><td align='center' width='280' >APELLIDOS Y NOMBRES</td><td align='center' width='90' >SERVICIO</td><td align='center' width='160' >TIPO DE PERSONAL</td><td align='center' width='100' >F. INICIO</td><td align='center' width='100' >F. FIN</td><td align='center' width='80' >NRO DIAS</td><td align='center' width='70' >PERIODO</td></tr>";
while ($rowl= mysql_fetch_array($resultlistado)){
$codigo=$rowl['cod'];
$nombre=$rowl['nombre'];
$tipopersona=$rowl['tipo_personal'];
$servicio=$rowl['servicio'];
$fini=$rowl['fini'];
$ffin=$rowl['ffin'];
$ndias=$rowl['ndias'];
$periodo=$rowl['periodo'];
/*
$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);
*/
$salida = $salida."<tr><td >".$nombre."</td><td align='center' >".$servicio."</td><td align='center' >".$tipopersona."</td><td align='center' >".$fini."</td><td align='center' >".$ffin."</td><td align='center' >".$ndias."</td><td align='center' >".$periodo."</td></tr>";

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

//pregunta por servicicios

if($mm<10){
$mmm = '0'.$mm;
}
}


$salida = $salida."</table>";


echo $salida;
?>
