<script>
function resetear() {
jQuery(':input','#atc-busca-docs')
.not(':button, :submit, :reset, :hidden')
.val('')
.removeAttr('checked')
.removeAttr('selected');
}
</script>
<?php
global $user;
$tabla='';
if ((in_array('Gestores comerciales', array_values($user->roles))) || (in_array('Gestores de soporte técnico', array_values($user->roles)))) {
$enviadoR=0;
if(isset($_POST["enviado"]) && ($_POST["enviado"]==1 || $_POST["enviado"]=="1")){
$enviadoR=1;
}
$fechaR="";
if(isset($_POST["fechaF"]) && $_POST["fechaF"]!=""){
$fechaR=$_POST["fechaF"];
}
$nombreR="";
if(isset($_POST["nombreF"]) && $_POST["nombreF"]!=""){
$nombreR=$_POST["nombreF"];
}
$empresaR="";
if(isset($_POST["empresaF"]) && $_POST["empresaF"]!=""){
$empresaR=$_POST["empresaF"];
}
$paisR="";
if(isset($_POST["paisF"]) && $_POST["paisF"]!=""){
$paisR=$_POST["paisF"];
}
$regionR="";
if(isset($_POST["regionF"]) && $_POST["regionF"]!=""){
$regionR=$_POST["regionF"];
}
$mailR="";
if(isset($_POST["mailF"]) && $_POST["mailF"]!=""){
$mailR=$_POST["mailF"];
}
$cargoR="";
if(isset($_POST["cargoF"]) && $_POST["cargoF"]!=""){
$cargoR=$_POST["cargoF"];
}
?>

<!-- Form-->
<div class="id-documentos-descargados container">
<div class="node-type-webform ">
<div class="main-container">
<div class="buscador node-webform">
<form action="" method="post" id="atc-busca-docs">

<div class="empresaF form-item webform-component webform-component-textfield form-group col-xs-6 col-sm-3">
<label class="control-label">Empresa</label>
<input class="form-control form-text" type="text" name="empresaF" id="empresaF" value="<?php echo $empresaR; ?>">
</div>

<div class="paisF form-item webform-component webform-component-textfield form-group col-xs-6 col-sm-3">
<label class="control-label">País</label>
<select class="form-control form-text" name="paisF" id="paisF">
<option value="">Todos</option>
<?php
$sql="SELECT tid, name FROM taxonomy_term_data WHERE vid=2";
$query = db_query($sql);

foreach($query as $item) {
if($paisR==$item->tid) {
echo "<option value='".$item->tid."' selected>".$item->name."</option>";
} else {
echo "<option value='".$item->tid."'>".$item->name."</option>";
}
}

?>
</select>
</div>

<div class="regionF form-item webform-component webform-component-textfield form-group col-xs-6 col-sm-3">
<label class="control-label">Región</label>
<!--<input class="form-control form-text" type="text" name="regionF" id="regionF" value="<?php// echo $regionR; ?>">-->
<select class="form-control form-text" name="regionF" id="regionF">
<option value="">Todos</option>
<?php
$sql="SELECT tid, name FROM taxonomy_term_data WHERE vid=8";
$query = db_query($sql);

foreach($query as $item) {
if($regionR==$item->tid) {
echo "<option value='".$item->tid."' selected>".$item->name."</option>";
} else {
echo "<option value='".$item->tid."'>".$item->name."</option>";
}
}

?>
</select>
</div>

<div class="fechaF form-item webform-component webform-component-textfield form-group col-xs-6 col-sm-3">
<label class="control-label">Fecha de descarga</label>
<input class="form-control form-text" type="text" name="fechaF" id="fechaF" value="<?php echo $fechaR; ?>">
</div>

<div class="nombreF form-item webform-component webform-component-textfield form-group col-xs-6 col-sm-3">
<label class="control-label">Nombre de usuario</label>
<input class="form-control form-text" type="text" name="nombreF" id="nombreF" value="<?php echo $nombreR; ?>">
</div>

<div class="mailF form-item webform-component webform-component-textfield form-group col-xs-6 col-sm-3">
<label class="control-label">Mail</label>
<input class="form-control form-text" type="text" name="mailF" id="mailF" value="<?php echo $mailR; ?>">
</div>

<div class="cargoF form-item webform-component webform-component-textfield form-group col-xs-6 col-sm-3">
<label class="control-label">Cargo</label>
<input class="form-control form-text" type="text" name="cargoF" id="cargoF" value="<?php echo $cargoR; ?>">
</div>

<div class="buscar form-actions col-md-12">
<input type="hidden" name="enviado" value="1">
<input type="button" value="Resetear formulario" class="btn btn-default" id="reset" onclick="resetear()">
<input type="submit" value="Buscar" class="webform-submit btn btn-default form-submit">
</div>

</form>
</div>
</div>
</div>
</div>
<!-- Fín form-->

<!-- Datatable-->
<?php
if($enviadoR==1) {

// ---- Cabecera del listado --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
$tabla.= "<table id='atc-download-docs' class='datatable table-bordered'>";
$tabla.= "<thead>";
$tabla.= "<tr>";
$tabla.= "<th scope='col'>Fecha</th>";
$tabla.= "<th scope='col'>Usuario</th>";
$tabla.= "<th scope='col'>Mail</th>";
$tabla.= "<th scope='col'>Cargo</th>";
$tabla.= "<th scope='col'>Empresa</th>";
$tabla.= "<th scope='col'>País</th>";
$tabla.= "<th scope='col'>Región</th>";
$tabla.= "<th scope='col'>Referencia</th>";
$tabla.= "<th scope='col'>Subreferencia</th>";
$tabla.= "<th scope='col'>Cantidad</th>";
$tabla.= "<th scope='col'>Peines</th>";
$tabla.= "</tr>";
$tabla.= "</thead>";
$tabla.= "<tbody>";

// ---- Select del listado --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
$sql="SELECT distinct a.fecha, g.name as nombreUsuario, g.mail as mail,
a.uid, DATE_FORMAT(a.fecha, '%d/%m/%Y') fechaDescarga,
a.referencia, a.subreferencia, a.cantidad, a.peines,
b.field_cargo_value cargo, d.field_empresatxt_value empresa, e.name pais, f.field_regionpais_tid region
FROM Xconfigurador a
LEFT JOIN field_data_field_cargo b ON (b.entity_id = a.uid)
LEFT JOIN field_data_field_pais_user c ON (c.entity_id = a.uid)
LEFT JOIN field_data_field_empresatxt d ON (d.entity_id = a.uid)
LEFT JOIN taxonomy_term_data e ON (e.tid = c.field_pais_user_tid)
LEFT JOIN field_data_field_regionpais f ON (f.revision_id = c.field_pais_user_tid)
LEFT JOIN users g ON (g.uid = a.uid)
WHERE (1=1) ";
//si hay criterios de búsqueda en el formulario que los tenga en cuenta
if($fechaR!=""){
$fecha1=$fechaR." 00:00:00";
$fecha2=$fechaR." 23:59:59";
$sql=$sql." AND a.fecha BETWEEN STR_TO_DATE('".$fecha1."','%d/%m/%Y %H:%i:%S') AND STR_TO_DATE('".$fecha2."','%d/%m/%Y %H:%i:%S')";
}
if($nombreR!=""){
$sql=$sql." AND g.name like '%".$nombreR."%'";
}
if($mailR!=""){
$sql=$sql." AND g.mail like '%".$mailR."%'";
}
if($cargoR!=""){
$sql=$sql." AND b.field_cargo_value like '%".$cargoR."%'";
}
if($empresaR!=""){
$sql=$sql." AND d.field_empresatxt_value like '%".$empresaR."%'";
}
if($paisR!=""){
$sql=$sql." AND c.field_pais_user_tid =".$paisR ;
}
if($regionR!=""){
$sql=$sql." AND f.field_regionpais_tid like '%".$regionR."%'";
}
$sql=$sql." ORDER BY a.fecha DESC";

// ---- Pintar resultados del listado --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
$query = db_query($sql);
foreach($query as $item) {
$nombreUsuario=$item->nombreUsuario;
$mail=$item->mail;
$cargo=$item->cargo;
$empresa=$item->empresa;
$pais=$item->pais;
$region=$item->region;

switch ($region) {
case 151:
$region="África";
break;
case 97:
$region="Asia Pacífico ";
break;
case 152:
$region="Centro América";
break;
case 153:
$region="Europa";
break;
case 154:
$region="Norte América";
break;
case 155:
$region="Oriente Medio";
break;
case 156:
$region="Sur América";
break;
default:
$region="-";
break;
}

$fechaDescarga=$item->fechaDescarga;
$tabla.= "<tr>";
$tabla.= "<td>".$fechaDescarga."</td>";
$tabla.= "<td>".$nombreUsuario."</td>";
$tabla.= "<td>".$mail."</td>";
$tabla.= "<td>".$cargo."</td>";
$tabla.= "<td>".$empresa."</td>";
$tabla.= "<td>".$pais."</td>";
$tabla.= "<td>".$region."</td>";
$tabla.= "<td>".$item->referencia."</td>";
$tabla.= "<td>".$item->subreferencia."</td>";
$tabla.= "<td>".$item->cantidad."</td>";
$tabla.= "<td>".$item->peines."</td>";
$tabla.= "</tr>";
}
$tabla.= "</tbody>";
$tabla.= "</table>";
echo "<div class='container container-contenido-defecto contenedor-download-docs'>".$tabla."<div/>";
global $base_url;
?>
<form action="<?php echo $base_url;?>/generar_excel_listados.php" method="post">
<input type="hidden" name="tabla" value="<?php echo str_replace('"',"'",$tabla); ?>">
<input type="submit" class="btn btn-default" value="Generar Excel">
</form>

</div>
<?php
} //if formulario ha sido enviado
} //if es admin
?>
<!-- Datatables -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.10/css/dataTables.bootstrap.min.css"&gt;
<script type="text/javascript" src="https://cdn.datatables.net/1.10.10/js/jquery.dataTables.min.js"></scrip…;
<script type="text/javascript" src="https://cdn.datatables.net/1.10.10/js/dataTables.bootstrap.min.js"></sc…;

<!-- Responsive -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/responsive/2.0.0/css/responsive.bootstrap.mi…;
<script type="text/javascript" src="https://cdn.datatables.net/responsive/2.0.0/js/dataTables.responsive.mi…;
<script type="text/javascript" src="https://cdn.datatables.net/responsive/2.0.0/js/responsive.bootstrap.min…;

<script>
jQuery(document).ready(function() {
jQuery('#atc-download-docs').DataTable({
searching: false,
paging: false
});
});
</script>