File "consulta_Filtro.PHP"
Full Path: C:/wamp64/www/APPSST/fconsuldota/consulta_Filtro.PHP
File size: 24.78 KB
MIME-type: text/x-php
Charset: utf-8
<?php
session_start();
include("../bd/conexion.php");
// Consulta para obtener áreas y centros de costo para los select
$query_areas = "SELECT DISTINCT area FROM entregasst WHERE area IS NOT NULL ORDER BY area";
$result_areas = mysqli_query($conexion, $query_areas);
$query_ccostos = "SELECT DISTINCT ccosto FROM entregasst WHERE ccosto IS NOT NULL ORDER BY ccosto";
$result_ccostos = mysqli_query($conexion, $query_ccostos);
// Procesar descarga a Excel si se solicita
if(isset($_POST['exportar_excel'])) {
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="entregas_filtradas.xls"');
header('Cache-Control: max-age=0');
$tipo_filtro = mysqli_real_escape_string($conexion, $_POST['tipo_filtro_export']);
$fecha_inicio = mysqli_real_escape_string($conexion, $_POST['fecha_inicio_export']);
$fecha_fin = mysqli_real_escape_string($conexion, $_POST['fecha_fin_export']);
// Construir la consulta para exportar
$query_export = "SELECT e.*, i.DESCRIPCION as descripcion_epp
FROM entregasst e
LEFT JOIN inventario i ON e.codigo = i.CODIGO
WHERE e.fEntrega BETWEEN '$fecha_inicio' AND '$fecha_fin'";
switch($tipo_filtro) {
case 'cedula':
$cedula = mysqli_real_escape_string($conexion, $_POST['cedula_export']);
$query_export .= " AND e.cedula = '$cedula'";
break;
case 'item':
$item = mysqli_real_escape_string($conexion, $_POST['item_export']);
$query_export .= " AND e.codigo = '$item'";
break;
case 'area':
$area = mysqli_real_escape_string($conexion, $_POST['area_export']);
$query_export .= " AND e.area = '$area'";
break;
case 'ccosto':
$ccosto = mysqli_real_escape_string($conexion, $_POST['ccosto_export']);
$query_export .= " AND e.ccosto = '$ccosto'";
break;
}
$query_export .= " ORDER BY e.fEntrega DESC";
$result_export = mysqli_query($conexion, $query_export);
// Crear contenido Excel
echo "<table border='1'>";
echo "<tr>";
echo "<th>#</th>";
if($tipo_filtro != 'cedula') {
echo "<th>Cédula</th>";
echo "<th>Nombre</th>";
}
echo "<th>Fecha Entrega</th>";
echo "<th>Código EPP</th>";
echo "<th>Descripción</th>";
echo "<th>Cantidad</th>";
echo "<th>Tipo Entrega</th>";
echo "<th>Tipo Estado</th>";
echo "<th>Devolutivo</th>";
echo "<th>Entregado</th>";
if($tipo_filtro != 'cedula') {
echo "<th>Área</th>";
echo "<th>Centro de Costo</th>";
}
echo "</tr>";
$contador = 1;
while($row = mysqli_fetch_assoc($result_export)) {
echo "<tr>";
echo "<td>".$contador++."</td>";
if($tipo_filtro != 'cedula') {
echo "<td>".$row['cedula']."</td>";
echo "<td>".$row['nombre']."</td>";
}
echo "<td>".date('d/m/Y', strtotime($row['fEntrega']))."</td>";
echo "<td>".$row['codigo']."</td>";
echo "<td>".($row['descripcion_epp'] ?: $row['epp'])."</td>";
echo "<td>".$row['cantidad']."</td>";
echo "<td>".$row['tpEntrega']."</td>";
echo "<td>".$row['tpEstado']."</td>";
echo "<td>".$row['tpDevolutivo']."</td>";
echo "<td>".($row['entrego'] ?: 'No')."</td>";
if($tipo_filtro != 'cedula') {
echo "<td>".$row['area']."</td>";
echo "<td>".$row['ccosto']."</td>";
}
echo "</tr>";
}
echo "</table>";
exit;
}
?>
<!DOCTYPE html>
<html lang="es">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Consulta de Entregas por Filtros</title>
<link rel="icon" type="image/png" href="../img/icono.png">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.5/css/jquery.dataTables.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/2.2.2/css/buttons.dataTables.min.css">
<style>
body {
background-image: linear-gradient(to right, #e2e2e2, #ffe5c9);
padding: 20px;
}
.card {
border-radius: 15px;
box-shadow: 0 5px 15px rgba(0,0,0,0.1);
}
.card-header {
background-color: #ff8a37;
color: white;
border-radius: 15px 15px 0 0 !important;
}
.table-container {
overflow-x: auto;
}
table.dataTable thead th {
background-color: #ff8a37;
color: white;
}
.btn-orange {
background-color: #ff8a37;
color: white;
}
.btn-orange:hover {
background-color: #e07a2e;
color: white;
}
.btn-success {
background-color: #28a745;
color: white;
}
.btn-success:hover {
background-color: #218838;
color: white;
}
.firma-img {
max-width: 100px;
cursor: pointer;
}
.firma-img:hover {
transform: scale(1.5);
transition: transform 0.3s;
z-index: 1000;
}
.filter-section {
background-color: #f8f9fa;
padding: 15px;
border-radius: 10px;
margin-bottom: 20px;
}
.filter-title {
font-weight: bold;
margin-bottom: 10px;
color: #ff8a37;
}
.buttons-excel {
background-color: #28a745 !important;
border-color: #28a745 !important;
}
</style>
</head>
<body>
<div class="container">
<div class="card mb-4">
<div class="card-header">
<div class="d-flex justify-content-between align-items-center">
<h4 class="mb-0"><i class="fas fa-filter me-2"></i>Consulta de Entregas por Filtros</h4>
<a href="../dotacion/MENUP.PHP" class="btn btn-light btn-sm">
<i class="fas fa-home me-1"></i> Menú
</a>
</div>
</div>
<div class="card-body">
<form id="formConsulta" method="post" class="row g-3">
<div class="col-md-12">
<div class="filter-section">
<div class="filter-title">Seleccione el tipo de filtro:</div>
<div class="form-check form-check-inline">
<input class="form-check-input" type="radio" name="tipo_filtro" id="filtro_cedula" value="cedula" checked>
<label class="form-check-label" for="filtro_cedula">Cédula</label>
</div>
<div class="form-check form-check-inline">
<input class="form-check-input" type="radio" name="tipo_filtro" id="filtro_item" value="item">
<label class="form-check-label" for="filtro_item">Item</label>
</div>
<div class="form-check form-check-inline">
<input class="form-check-input" type="radio" name="tipo_filtro" id="filtro_area" value="area">
<label class="form-check-label" for="filtro_area">Área</label>
</div>
<div class="form-check form-check-inline">
<input class="form-check-input" type="radio" name="tipo_filtro" id="filtro_ccosto" value="ccosto">
<label class="form-check-label" for="filtro_ccosto">Centro de Costo</label>
</div>
</div>
</div>
<!-- Campos de filtro (se muestran/ocultan según selección) -->
<div class="col-md-4 filter-field" id="cedula-field">
<label for="cedula" class="form-label">Cédula del Operario</label>
<input type="text" class="form-control" id="cedula" name="cedula">
</div>
<div class="col-md-4 filter-field" id="item-field" style="display: none;">
<label for="item" class="form-label">Código del Item</label>
<input type="text" class="form-control" id="item" name="item">
</div>
<div class="col-md-4 filter-field" id="area-field" style="display: none;">
<label for="area" class="form-label">Área</label>
<select class="form-select" id="area" name="area">
<option value="">Seleccione un área</option>
<?php while($area = mysqli_fetch_assoc($result_areas)): ?>
<option value="<?= $area['area'] ?>"><?= $area['area'] ?></option>
<?php endwhile; ?>
<?php mysqli_data_seek($result_areas, 0); ?>
</select>
</div>
<div class="col-md-4 filter-field" id="ccosto-field" style="display: none;">
<label for="ccosto" class="form-label">Centro de Costo</label>
<select class="form-select" id="ccosto" name="ccosto">
<option value="">Seleccione un centro de costo</option>
<?php while($ccosto = mysqli_fetch_assoc($result_ccostos)): ?>
<option value="<?= $ccosto['ccosto'] ?>"><?= $ccosto['ccosto'] ?></option>
<?php endwhile; ?>
<?php mysqli_data_seek($result_ccostos, 0); ?>
</select>
</div>
<div class="col-md-3">
<label for="fecha_inicio" class="form-label">Fecha Inicial</label>
<input type="date" class="form-control" id="fecha_inicio" name="fecha_inicio" required>
</div>
<div class="col-md-3">
<label for="fecha_fin" class="form-label">Fecha Final</label>
<input type="date" class="form-control" id="fecha_fin" name="fecha_fin" required>
</div>
<div class="col-md-2 d-flex align-items-end">
<button type="submit" class="btn btn-orange w-100">
<i class="fas fa-search me-1"></i> Buscar
</button>
</div>
</form>
</div>
</div>
<?php if(isset($_POST['tipo_filtro'])): ?>
<?php
$tipo_filtro = mysqli_real_escape_string($conexion, $_POST['tipo_filtro']);
$fecha_inicio = mysqli_real_escape_string($conexion, $_POST['fecha_inicio']);
$fecha_fin = mysqli_real_escape_string($conexion, $_POST['fecha_fin']);
// Construir la consulta según el tipo de filtro
$query = "SELECT e.*, i.DESCRIPCION as descripcion_epp
FROM entregasst e
LEFT JOIN inventario i ON e.codigo = i.CODIGO
WHERE e.fEntrega BETWEEN '$fecha_inicio' AND '$fecha_fin'";
$query_operario = "";
$operario = null;
switch($tipo_filtro) {
case 'cedula':
$cedula = mysqli_real_escape_string($conexion, $_POST['cedula']);
$query .= " AND e.cedula = '$cedula'";
$query_operario = "SELECT nombre, ccosto, cargo, area FROM entregasst WHERE cedula = '$cedula' LIMIT 1";
break;
case 'item':
$item = mysqli_real_escape_string($conexion, $_POST['item']);
$query .= " AND e.codigo = '$item'";
break;
case 'area':
$area = mysqli_real_escape_string($conexion, $_POST['area']);
$query .= " AND e.area = '$area'";
break;
case 'ccosto':
$ccosto = mysqli_real_escape_string($conexion, $_POST['ccosto']);
$query .= " AND e.ccosto = '$ccosto'";
break;
}
$query .= " ORDER BY e.fEntrega DESC";
// Obtener datos del operario si es filtro por cédula
if($tipo_filtro == 'cedula' && !empty($cedula)) {
$result_operario = mysqli_query($conexion, $query_operario);
$operario = mysqli_fetch_assoc($result_operario);
}
// Mostrar información del filtro aplicado
?>
<div class="card mb-4">
<div class="card-header bg-info text-white">
<div class="d-flex justify-content-between align-items-center">
<h5 class="mb-0">Filtro Aplicado</h5>
<form method="post" style="margin: 0;">
<input type="hidden" name="tipo_filtro_export" value="<?= $tipo_filtro ?>">
<input type="hidden" name="fecha_inicio_export" value="<?= $fecha_inicio ?>">
<input type="hidden" name="fecha_fin_export" value="<?= $fecha_fin ?>">
<?php if($tipo_filtro == 'cedula'): ?>
<input type="hidden" name="cedula_export" value="<?= $cedula ?>">
<?php elseif($tipo_filtro == 'item'): ?>
<input type="hidden" name="item_export" value="<?= $item ?>">
<?php elseif($tipo_filtro == 'area'): ?>
<input type="hidden" name="area_export" value="<?= $area ?>">
<?php elseif($tipo_filtro == 'ccosto'): ?>
<input type="hidden" name="ccosto_export" value="<?= $ccosto ?>">
<?php endif; ?>
<button type="submit" name="exportar_excel" class="btn btn-success btn-sm">
<i class="fas fa-file-excel me-1"></i> Exportar a Excel
</button>
</form>
</div>
</div>
<div class="card-body">
<div class="row">
<div class="col-md-12">
<p><strong>Tipo de filtro:</strong>
<?php
echo ucfirst($tipo_filtro) . ": ";
switch($tipo_filtro) {
case 'cedula': echo $cedula . " - " . ($operario ? $operario['nombre'] : ''); break;
case 'item': echo $item; break;
case 'area': echo $_POST['area']; break;
case 'ccosto': echo $_POST['ccosto']; break;
}
?>
</p>
<p><strong>Rango de fechas:</strong> <?= date('d/m/Y', strtotime($fecha_inicio)) ?> al <?= date('d/m/Y', strtotime($fecha_fin)) ?></p>
</div>
</div>
</div>
</div>
<?php if($tipo_filtro == 'cedula' && $operario): ?>
<div class="card mb-4">
<div class="card-header bg-primary text-white">
<h5 class="mb-0">Datos del Operario</h5>
</div>
<div class="card-body">
<div class="row">
<div class="col-md-3">
<p><strong>Cédula:</strong> <?= $cedula ?></p>
</div>
<div class="col-md-3">
<p><strong>Nombre:</strong> <?= $operario['nombre'] ?></p>
</div>
<div class="col-md-2">
<p><strong>Centro Costo:</strong> <?= $operario['ccosto'] ?></p>
</div>
<div class="col-md-2">
<p><strong>Área:</strong> <?= $operario['area'] ?></p>
</div>
<div class="col-md-2">
<p><strong>Cargo:</strong> <?= $operario['cargo'] ?></p>
</div>
</div>
</div>
</div>
<?php endif; ?>
<div class="card">
<div class="card-header">
<h5 class="mb-0">Resultados de Entregas</h5>
</div>
<div class="card-body">
<div class="table-container">
<table id="tablaEntregas" class="table table-striped table-bordered" style="width:100%">
<thead>
<tr>
<th>#</th>
<?php if($tipo_filtro != 'cedula'): ?>
<th>Cédula</th>
<th>Nombre</th>
<?php endif; ?>
<th>Fecha Entrega</th>
<th>Código EPP</th>
<th>Descripción</th>
<th>Cantidad</th>
<th>Tipo Entrega</th>
<th>Tipo Estado</th>
<th>Devolutivo</th>
<th>Entregado</th>
<?php if($tipo_filtro != 'cedula'): ?>
<th>Área</th>
<th>Centro Costo</th>
<?php endif; ?>
<th>Firma</th>
</tr>
</thead>
<tbody>
<?php
$result = mysqli_query($conexion, $query);
$contador = 1;
while($row = mysqli_fetch_assoc($result)):
?>
<tr>
<td><?= $contador++ ?></td>
<?php if($tipo_filtro != 'cedula'): ?>
<td><?= $row['cedula'] ?></td>
<td><?= $row['nombre'] ?></td>
<?php endif; ?>
<td><?= date('d/m/Y', strtotime($row['fEntrega'])) ?></td>
<td><?= $row['codigo'] ?></td>
<td><?= $row['descripcion_epp'] ?: $row['epp'] ?></td>
<td><?= $row['cantidad'] ?></td>
<td><?= $row['tpEntrega'] ?></td>
<td><?= $row['tpEstado'] ?></td>
<td><?= $row['tpDevolutivo'] ?></td>
<td><?= $row['entrego'] ?: 'No' ?></td>
<?php if($tipo_filtro != 'cedula'): ?>
<td><?= $row['area'] ?></td>
<td><?= $row['ccosto'] ?></td>
<?php endif; ?>
<td>
<?php if(!empty($row['firma'])): ?>
<img src="<?= $row['firma'] ?>" class="firma-img" data-bs-toggle="modal" data-bs-target="#modalFirma" onclick="mostrarFirma('<?= $row['firma'] ?>')">
<?php else: ?>
<span class="text-muted">Sin firma</span>
<?php endif; ?>
</td>
</tr>
<?php endwhile; ?>
</tbody>
</table>
</div>
</div>
</div>
<?php endif; ?>
</div>
<!-- Modal para visualizar firma -->
<div class="modal fade" id="modalFirma" tabindex="-1" aria-hidden="true">
<div class="modal-dialog modal-lg">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title">Firma del Operario</h5>
<button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
</div>
<div class="modal-body text-center">
<img id="firmaGrande" src="" class="img-fluid">
</div>
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cerrar</button>
</div>
</div>
</div>
</div>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
<script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.js"></script>
<script src="https://cdn.datatables.net/buttons/2.2.2/js/dataTables.buttons.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.2.2/js/buttons.html5.min.js"></script>
<script>
$(document).ready(function() {
// Mostrar/ocultar campos según el tipo de filtro seleccionado
$('input[name="tipo_filtro"]').change(function() {
$('.filter-field').hide();
$('#' + $(this).val() + '-field').show();
// Limpiar los otros campos
$('.filter-field input, .filter-field select').not('#' + $(this).val()).val('');
});
// Inicializar DataTable con botón de Excel
$('#tablaEntregas').DataTable({
language: {
url: '//cdn.datatables.net/plug-ins/1.11.5/i18n/es-ES.json'
},
dom: 'Bfrtip',
buttons: [
{
extend: 'excel',
text: '<i class="fas fa-file-excel"></i> Exportar a Excel',
className: 'btn btn-success',
title: 'EntregasFiltradas'
}
],
responsive: true,
pageLength: 25
});
// Establecer fecha de hoy como valor por defecto
const today = new Date().toISOString().split('T')[0];
$('#fecha_fin').val(today);
// Restar 30 días a la fecha actual
const date = new Date();
date.setDate(date.getDate() - 30);
const thirtyDaysAgo = date.toISOString().split('T')[0];
$('#fecha_inicio').val(thirtyDaysAgo);
// Mostrar campo de cédula por defecto
$('#cedula-field').show();
});
function mostrarFirma(url) {
$('#firmaGrande').attr('src', url);
}
</script>
</body>
</html>