<?php

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'];
$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 esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=a.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=a.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 a.ua_idespec='$idesp' and (a.ua_estado= 1 or a.ua_estado= 2 or a.ua_estado= 4) and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR') 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 esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=a.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=a.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 a.ua_idespec='$idesp' and (a.ua_estado= 1 or a.ua_estado= 2 or a.ua_estado= 4) and a.tc_idcont='$tcont' and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR') 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 esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=a.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=a.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 (a.ua_estado= 1  or a.ua_estado= 2 or a.ua_estado= 4) and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR') 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 esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=a.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=a.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 (a.ua_estado= 1  or a.ua_estado= 2 or a.ua_estado= 4) and a.tc_idcont='$tcont' and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR') 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 esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=a.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=a.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_idespec='$idesp' and (a.ua_estado= 1  or a.ua_estado= 2 or a.ua_estado= 4) and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR') 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 esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=a.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=a.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_idespec='$idesp' and (a.ua_estado= 1  or a.ua_estado= 2 or a.ua_estado= 4) and a.tc_idcont='$tcont' and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR') 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 esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=a.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=a.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_estado= 1  or a.ua_estado= 2 or a.ua_estado= 4) and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR') 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 esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=a.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=a.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_estado= 1  or a.ua_estado= 2 or a.ua_estado= 4) and a.tc_idcont='$tcont' and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR') 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 esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=a.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=a.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 a.ua_idespec='$idesp' and (a.ua_estado= 1 or a.ua_estado= 2 or a.ua_estado= 4) and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR') 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 esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=a.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=a.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 a.ua_idespec='$idesp' and (a.ua_estado= 1 or a.ua_estado= 2 or a.ua_estado= 4) and a.tc_idcont='$tcont' and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR') 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 esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=a.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=a.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 (a.ua_estado= 1  or a.ua_estado= 2 or a.ua_estado= 4) and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR') 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 esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=a.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=a.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 (a.ua_estado= 1  or a.ua_estado= 2 or a.ua_estado= 4) and a.tc_idcont='$tcont' and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR') 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 esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=a.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=a.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 a.ua_idespec='$idesp' and (a.ua_estado= 1  or a.ua_estado= 2 or a.ua_estado= 4) and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR') 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 esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=a.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=a.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 a.ua_idespec='$idesp' and (a.ua_estado= 1  or a.ua_estado= 2 or a.ua_estado= 4) and a.tc_idcont='$tcont' and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR') 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 esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=a.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=a.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 (a.ua_estado= 1  or a.ua_estado= 2 or a.ua_estado= 4) and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR') 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 esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=a.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=a.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 (a.ua_estado= 1  or a.ua_estado= 2 or a.ua_estado= 4) and a.tc_idcont='$tcont' and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR') 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 esp_desc from tb_especialidad ";
	$sqllistado = $sqllistado." where ua_idespec=a.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=a.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 (a.ua_apepat like '$nombre%' or  a.ua_apemat like '$nombre%' or a.ua_nom like '%$nombre%') and (a.ua_estado= 1  or a.ua_estado= 2 or a.ua_estado= 4) and b.s_id not in (SELECT s_id FROM tb_servicio where s_abrev='APOR') order by nombre, especialidad";
}
//echo $sqllistado;
//aumentar  a.tc_idcont='3'
$resultlistado = mysql_query($sqllistado);
$sumap=0;



$cadenalis="";
$cadenalis="<table align='center' class='FacetFormTABLE'><tr><td colspan='13'class='FacetFieldCaptionTDT' align='center'> PERSONAL ASISTENCIAL </td></tr>";
$cadenalis=$cadenalis."<tr><td class='FacetFieldCaptionTD' align='center' width='250'>APELLIDOS Y NOMBRES</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><td class='FacetFieldCaptionTD' align='center'>EDITAR</td><td class='FacetFieldCaptionTD' align='center'>TRANSF</td><td class='FacetFieldCaptionTD' align='center'>SUSPE</td><td class='FacetFieldCaptionTD' align='center'>DesMed</td><td class='FacetFieldCaptionTD' align='center'>VACAC</td><td class='FacetFieldCaptionTD' align='center'>DATOS</td><td class='FacetFieldCaptionTD' align='center'>PROG</td></tr>";
$a=1;
$muestra="";
while ($rowl= mysql_fetch_array($resultlistado)){
$codigo = $rowl['cod'];
$muestra = $muestra."<input type='hidden' id='codper".$a."'  name='codper".$a."' value='".$codigo."'>";
//$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><td align='center'> <a href='#' onClick='verdescansomedico($a)'><img src='img/heart_red.png' width='17' height='17' border='0'></a></td><td align='center'><a href='#' onClick=verdetallevacaprog($a); ><img src='img/eye.png' border='0'></a></td><td align='center'><a href='#' onClick=datospersonal($a);> <img src='img/book.png' width='17' height='17' id='person' border='0' ></a></td><td align='center'><a href='#' onClick=askprogramacion($a);> <img src='img/calendar.png' width='17' height='17' id='person' value='$codigo' border='0'></a></td></tr>";
$cadenalis=$cadenalis."<tr><td align='left'>".utf8_encode($rowl['nombre'])."</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><td align='center'><a href='#' onClick=editarpersonal($a); ><img src='img/b_edit.png' width='17' height='17' border='0'></a></td><td align='center'><a href='#' onClick=transferirpersonal($a); ><img src='img/ver.jpg' width='17' height='17' border='0'></a></td><td align='center'><a href='#' onClick=suspenderpersonal($a); ><img src='img/b_drop.png' border='0'></a></td><td align='center'><a href='#' onClick='verdescansomedico($a)'><img src='img/heart_red.png' width='17' height='17' border='0'></a></td><td align='center'><a href='#' onClick=verdetallevacaprog($a); ><img src='img/eye.png' border='0'></a></td><td align='center'><a href='#' onClick=datospersonal($a);> <img src='img/book.png' width='17' height='17' id='person' border='0' ></a></td><td align='center'><a href='#' onClick=askprogramacion($a);> <img src='img/calendar.png' width='17' height='17' id='person' value='$codigo' border='0'></a></td></tr>";
$sumap = $sumap +1;
$a=$a+1;
}
$cadenalis=$cadenalis."<tr><td colspan='13' 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" />
<link rel="stylesheet" type="text/css" href="styles/Style.css">
</head>
<body>
<?php
echo $cadenalis;echo $muestra;
?>
</body>
</html>