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

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

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


$idesp=$_GET['esp'];
$idtp=$_GET['tp'];
$idj=$_GET['idj'];
$ids=$_GET['ids'];
$tcont=$_GET['tcont'];
$tbusq=$_GET['tbusq'];
$nombre=$_GET['nombre'];

if($tbusq==1){
if($ids==-1){
if($idesp!=0 and $idtp!=0 ){
	if($tcont==0){
	$sqllistado = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select s_abrev from tb_servicio where s_id=b.s_id and j_id=b.j_id ) as servicio, (select esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont ";
	$sqllistado = $sqllistado." where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona 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_idespec='$idesp' and (b.ua_estado= 1 or b.ua_estado= 2 or b.ua_estado= 4) and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR' and j_id=b.j_id) order by nombre, especialidad";
	}else{
	$sqllistado = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select s_abrev from tb_servicio where s_id=b.s_id and j_id=b.j_id ) as servicio, (select esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont ";
	$sqllistado = $sqllistado." where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona 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_idespec='$idesp' and (b.ua_estado= 1 or b.ua_estado= 2 or b.ua_estado= 4) and b.tc_idcont='$tcont' and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR' and j_id=b.j_id) order by nombre, especialidad";
	}
}elseif($idesp==0 and $idtp!=0){
	if($tcont==0){
	$sqllistado = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select s_abrev from tb_servicio where s_id=b.s_id and j_id=b.j_id ) as servicio, (select esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont ";
	$sqllistado = $sqllistado." where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona 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  or b.ua_estado= 2 or b.ua_estado= 4) and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR' and j_id=b.j_id) order by nombre, especialidad";
	}else{
	$sqllistado = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select s_abrev from tb_servicio where s_id=b.s_id and j_id=b.j_id ) as servicio, (select esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont ";
	$sqllistado = $sqllistado." where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona 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  or b.ua_estado= 2 or b.ua_estado= 4) and b.tc_idcont='$tcont' and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR' and j_id=b.j_id) order by nombre, especialidad";
	}
}elseif($idesp!=0 and $idtp==0){
	if($tcont==0){
	$sqllistado = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select s_abrev from tb_servicio where s_id=b.s_id and j_id=b.j_id ) as servicio, (select esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont ";
	$sqllistado = $sqllistado." where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona 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_idespec='$idesp' and (b.ua_estado= 1  or b.ua_estado= 2 or b.ua_estado= 4) and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR' and j_id=b.j_id) order by nombre, especialidad";
	}else{
	$sqllistado = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select s_abrev from tb_servicio where s_id=b.s_id and j_id=b.j_id ) as servicio, (select esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont ";
	$sqllistado = $sqllistado." where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona 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_idespec='$idesp' and (b.ua_estado= 1  or b.ua_estado= 2 or b.ua_estado= 4) and b.tc_idcont='$tcont' and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR' and j_id=b.j_id) order by nombre, especialidad";
	}
}else{
	if($tcont==0){
	$sqllistado = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select s_abrev from tb_servicio where s_id=b.s_id and j_id=b.j_id ) as servicio, (select esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont ";
	$sqllistado = $sqllistado." where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona 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  or b.ua_estado= 2 or b.ua_estado= 4) and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR' and j_id=b.j_id) order by nombre, especialidad";
	}else{
	$sqllistado = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select s_abrev from tb_servicio where s_id=b.s_id and j_id=b.j_id ) as servicio, (select esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont ";
	$sqllistado = $sqllistado." where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona 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  or b.ua_estado= 2 or b.ua_estado= 4) and b.tc_idcont='$tcont' and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR' and j_id=b.j_id) order by nombre, especialidad";
	}
	///finalizar
}}else{
if($idesp!=0 and $idtp!=0 ){
	if($tcont==0){
	$sqllistado = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select s_abrev from tb_servicio where s_id=b.s_id and j_id=b.j_id ) as servicio, (select esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont ";
	$sqllistado = $sqllistado." where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona 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_idespec='$idesp' and (b.ua_estado= 1 or b.ua_estado= 2 or b.ua_estado= 4) and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR' and j_id=b.j_id) order by nombre, especialidad";
	}else{
	$sqllistado = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select s_abrev from tb_servicio where s_id=b.s_id and j_id=b.j_id ) as servicio, (select esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont ";
	$sqllistado = $sqllistado." where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona 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_idespec='$idesp' and (b.ua_estado= 1 or b.ua_estado= 2 or b.ua_estado= 4) and b.tc_idcont='$tcont' and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR' and j_id=b.j_id) order by nombre, especialidad";
	}
}elseif($idesp==0 and $idtp!=0){
	if($tcont==0){
	$sqllistado = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select s_abrev from tb_servicio where s_id=b.s_id and j_id=b.j_id ) as servicio, (select esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont ";
	$sqllistado = $sqllistado." where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona 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  or b.ua_estado= 2 or b.ua_estado= 4) and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR' and j_id=b.j_id) order by nombre, especialidad";
	}else{
	$sqllistado = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select s_abrev from tb_servicio where s_id=b.s_id and j_id=b.j_id ) as servicio, (select esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont ";
	$sqllistado = $sqllistado." where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona 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  or b.ua_estado= 2 or b.ua_estado= 4) and b.tc_idcont='$tcont' and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR' and j_id=b.j_id) order by nombre, especialidad";
	}
}elseif($idesp!=0 and $idtp==0){
	if($tcont==0){
	$sqllistado = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select s_abrev from tb_servicio where s_id=b.s_id and j_id=b.j_id ) as servicio, (select esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont ";
	$sqllistado = $sqllistado." where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona 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_idespec='$idesp' and (b.ua_estado= 1  or b.ua_estado= 2 or b.ua_estado= 4) and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR' and j_id=b.j_id) order by nombre, especialidad";
	}else{
	$sqllistado = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select s_abrev from tb_servicio where s_id=b.s_id and j_id=b.j_id ) as servicio, (select esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont ";
	$sqllistado = $sqllistado." where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona 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_idespec='$idesp' and (b.ua_estado= 1  or b.ua_estado= 2 or b.ua_estado= 4) and b.tc_idcont='$tcont' and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR' and j_id=b.j_id) order by nombre, especialidad";
	}
}else{
	if($tcont==0){
	$sqllistado = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select s_abrev from tb_servicio where s_id=b.s_id and j_id=b.j_id ) as servicio, (select esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont ";
	$sqllistado = $sqllistado." where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona 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  or b.ua_estado= 2 or b.ua_estado= 4) and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR' and j_id=b.j_id) order by nombre, especialidad";
	}else{
	$sqllistado = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select s_abrev from tb_servicio where s_id=b.s_id and j_id=b.j_id ) as servicio, (select esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont ";
	$sqllistado = $sqllistado." where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona 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  or b.ua_estado= 2 or b.ua_estado= 4) and b.tc_idcont='$tcont' and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR' and j_id=b.j_id) order by nombre, especialidad";
	}
}
}}else{
$sqllistado = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select s_abrev from tb_servicio where s_id=b.s_id and j_id=b.j_id ) as servicio, (select esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont ";
	$sqllistado = $sqllistado." where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona from tb_usuasist a inner join tb_usudep b on ";
	$sqllistado = $sqllistado." a.ug_id = b.ug_id where b.j_id='$idj' and  (a.ua_apepat like '$nombre%' or  a.ua_apemat like '$nombre%' or a.ua_nom like '%$nombre%') and (b.ua_estado= 1  or b.ua_estado= 2 or b.ua_estado= 4) and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR' and j_id=b.j_id) order by nombre, especialidad";
}
//echo $sqllistado;
//aumentar  b.tc_idcont='3'
$resultlistado = mysqli_query($conex,$sqllistado);
$sumap=0;

$cadenalis="";
$cadenalis="<table align='center' class='FacetFormTABLE'><tr><td colspan='7'class='FacetFieldCaptionTDT' align='center'> PERSONAL ASISTENCIAL </td></tr>";
$cadenalis=$cadenalis."<tr><td class='FacetFieldCaptionTD' align='center' width='250'>APEDLLIDOS Y NOMBRES</td><td class='FacetFieldCaptionTD' align='center' width='130'>SERVICIO</td><td class='FacetFieldCaptionTD' align='center' width='130'>TIPO PERSONAL</td><td class='FacetFieldCaptionTD' align='center' width='130'>ESPECIALIDAD</td><td class='FacetFieldCaptionTD' align='center' width='130'>TIPO DE CONTRATO</td><td class='FacetFieldCaptionTD' align='center' width='100'>FECHA DE INGRESO</td><td class='FacetFieldCaptionTD' align='center' width='70'>PERIOD. LAB.</td></tr>";
	
while ($rowl= mysqli_fetch_array($resultlistado)){
$codigo = $rowl['cod'];
$cadenalis=$cadenalis."<tr><td align='left'>".utf8_encode($rowl['nombre'])."</td><td align='center'>".$rowl['servicio']."</td><td align='center'>".$rowl['tpersona']."</td><td align='center'>".$rowl['especialidad']."</td><td align='center'>".$rowl['tipocontrato']."</td><td align='center'>".$rowl['fingreso']."</td><td align='center'>".$rowl['tiempo']."</td></tr>";
$sumap = $sumap +1;
}
$cadenalis=$cadenalis."<tr><td colspan='7' align='center'>El numero de Personal es ".$sumap."</td></tr>";
$cadenalis=$cadenalis."</table>";
?>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>
<body>
<?php
echo $cadenalis;
?>
</body>
</html>