ConsultaModel

Models/ConsultaModel.php The ConsultaModel provides read-only database access for querying the return history. It’s used by ConsultaController to retrieve historical return records and individual return details.

Overview

Purpose: Query return history from devoluciones table
Database Table: devoluciones
Connection: PDO via Conexion::Conectar()
Operations: Read-only (SELECT queries)

Class Structure

ConsultaModel.php

<?php
require_once 'Config/Conexion.php';

class ConsultaModel {
    private $db;

    public function __construct() {
        $this->db = Conexion::Conectar();
    }

    public function obtenerHistorial($limite = 100) {
        try {
            $stmt = $this->db->prepare("SELECT * FROM devoluciones ORDER BY fecha_creacion DESC LIMIT ?");
            $stmt->bindValue(1, $limite, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            error_log("Error en ConsultaModel: " . $e->getMessage());
            return [];
        }
    }
    
    public function obtenerPorId($id) {
        $stmt = $this->db->prepare("SELECT * FROM devoluciones WHERE id = ?");
        $stmt->execute([$id]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
}

Methods

__construct()

Initializes database connection using the PDO singleton pattern. Behavior:

$this->db = Conexion::Conectar();

This establishes a connection to the MySQL database configured in Config/Conexion.php.

obtenerHistorial()

Retrieves a limited list of recent return records, ordered by creation date.

public function obtenerHistorial($limite = 100)

Parameters:

limite

int

default:"100"

Maximum number of records to return. Uses PDO::PARAM_INT for type safety.

Returns: Array of return records (associative arrays), or empty array [] on error. Return Record Structure: Each record contains all fields from the devoluciones table:

int

Auto-increment primary key

nit

string

Customer tax ID (VARCHAR 20)

nombre_cliente

string

Customer name (VARCHAR 255)

direccion

string

Customer address (TEXT)

codigo_producto

string

Product SKU or code (VARCHAR 50)

descripcion_producto

string

Product description (TEXT)

unidad

string

Unit of measure (VARCHAR 20)

float

Weight in kilograms (DECIMAL 10,2)

motivo

string

Return reason: ‘devolucion’, ‘faltante’, or ‘sobrante’ (ENUM)

cantidad_und

int

Quantity in units (INT)

cantidad_kg

float

Quantity in kilograms (DECIMAL 10,2)

evidencia

string

Path to uploaded evidence file (VARCHAR 255, nullable)

observacion

string

User observations (TEXT)

estado

string

Current status: ‘pendiente’, ‘aprobado’, or ‘rechazado’ (ENUM, default ‘pendiente’)

observacion_admin

string

Administrator review notes (TEXT, nullable)

codigo_admin

string

Authorization code from admin (VARCHAR 50, nullable)

fecha_creacion

timestamp

Record creation timestamp (TIMESTAMP, defaults to CURRENT_TIMESTAMP)

fecha_revision

timestamp

Review/approval timestamp (TIMESTAMP, nullable)

usuario_creador

string

Username who created the return (VARCHAR 50, references usuarios.USR)

usuario_revisor

string

Username who reviewed the return (VARCHAR 50, nullable)

SQL Query:

SELECT * 
FROM devoluciones 
ORDER BY fecha_creacion DESC 
LIMIT ?

Error Handling:

try {
    // Execute query
} catch (Exception $e) {
    error_log("Error en ConsultaModel: " . $e->getMessage());
    return [];
}

Errors are logged to PHP error log and an empty array is returned instead of throwing an exception.

obtenerPorId()

Retrieves a single return record by its unique ID.

public function obtenerPorId($id)

Parameters:

int

required

Primary key of the return record to retrieve

Returns: Single return record (associative array) or false if not found. Return Structure: Same fields as obtenerHistorial() - see above for complete field list. SQL Query:

SELECT * 
FROM devoluciones 
WHERE id = ?

Example Usage:

$model = new ConsultaModel();

// Get return #42
$devolucion = $model->obtenerPorId(42);

if ($devolucion) {
    echo "Cliente: " . $devolucion['nombre_cliente'];
    echo "Estado: " . $devolucion['estado'];
} else {
    echo "Devolución no encontrada";
}

This method does NOT use try-catch error handling like obtenerHistorial(). If the query fails, PDO will throw an exception that must be caught by the calling code.

Usage Examples

Retrieve Recent History (Default Limit)

$consultaModel = new ConsultaModel();
$recientes = $consultaModel->obtenerHistorial();

// Returns up to 100 most recent returns
// Ordered by fecha_creacion DESC

Retrieve Last 50 Returns

$consultaModel = new ConsultaModel();
$ultimas50 = $consultaModel->obtenerHistorial(50);

foreach ($ultimas50 as $devolucion) {
    echo "{$devolucion['id']}: {$devolucion['nombre_cliente']} - {$devolucion['estado']}\n";
}

Get Specific Return Details

$consultaModel = new ConsultaModel();

// AJAX endpoint for return details
$id = intval($_GET['id']);
$detalle = $consultaModel->obtenerPorId($id);

if ($detalle) {
    echo json_encode([
        'success' => true,
        'data' => $detalle
    ]);
} else {
    echo json_encode([
        'success' => false,
        'message' => 'Devolución no encontrada'
    ]);
}

Integration Points

ConsultaController

ConsultaController uses this model for all history queries:

class ConsultaController {
    private $model;
    
    public function __construct() {
        $this->model = new ConsultaModel();
    }
    
    public function index() {
        $devoluciones = $this->model->obtenerHistorial();
        require_once 'Views/admin/consulta.php';
    }
    
    public function detalles() {
        $id = intval($_GET['id']);
        $data = $this->model->obtenerPorId($id);
        echo json_encode(['success' => true, 'html' => ...]);
    }
}

AdminController

AdminController also uses ConsultaModel for viewing history:

class AdminController {
    private $consultaModel;
    
    public function __construct() {
        $this->consultaModel = new ConsultaModel();
    }
    
    public function index() {
        $historial = $this->consultaModel->obtenerHistorial(50);
        // Display recent history in admin panel
    }
}

Performance Considerations

Current Limitations

SELECT * queries: Retrieves all columns even if not needed
No pagination: Only LIMIT, no OFFSET support for pagination
No filtering: Cannot filter by status, date range, or user
No sorting options: Always sorts by fecha_creacion DESC

Recommended Enhancements

Add Pagination Support

public function obtenerHistorial($limite = 100, $offset = 0) {
    $stmt = $this->db->prepare(
        "SELECT * FROM devoluciones 
         ORDER BY fecha_creacion DESC 
         LIMIT ? OFFSET ?"
    );
    $stmt->bindValue(1, $limite, PDO::PARAM_INT);
    $stmt->bindValue(2, $offset, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

Add Filtering Options

public function obtenerHistorialFiltrado($filtros = [], $limite = 100) {
    $sql = "SELECT * FROM devoluciones WHERE 1=1";
    $params = [];
    
    if (isset($filtros['estado'])) {
        $sql .= " AND estado = ?";
        $params[] = $filtros['estado'];
    }
    
    if (isset($filtros['motivo'])) {
        $sql .= " AND motivo = ?";
        $params[] = $filtros['motivo'];
    }
    
    if (isset($filtros['fecha_desde'])) {
        $sql .= " AND fecha_creacion >= ?";
        $params[] = $filtros['fecha_desde'];
    }
    
    $sql .= " ORDER BY fecha_creacion DESC LIMIT ?";
    $params[] = $limite;
    
    $stmt = $this->db->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

Add Count Method

public function contarHistorial() {
    $stmt = $this->db->query("SELECT COUNT(*) FROM devoluciones");
    return $stmt->fetchColumn();
}

Comparison with DevolucionModel

Feature	ConsultaModel	DevolucionModel
Purpose	Read-only queries	Full CRUD operations
Methods	`obtenerHistorial()`, `obtenerPorId()`	`guardar()`, `obtenerPendientes()`, `procesarRevision()`, `obtenerEstadisticas()`
Filtering	None (all records)	By status (`estado = 'Pendiente'`)
Statistics	No	Yes (`obtenerEstadisticas()`)
Write Operations	No	Yes (INSERT, UPDATE)
Used By	ConsultaController, AdminController	PanelController, AdminController, HomeController

ConsultaController - Uses this model for history views
DevolucionModel - Full-featured return management model
Database Schema - Complete devoluciones table structure

Database Connection

Uses the singleton Conexion class:

// Config/Conexion.php
class Conexion {
    public static function Conectar() {
        $host = '192.200.100.40';
        $db   = 'devolutionsync';
        $user = 'SANMARINO';
        $pass = 'sanmarino2021*';
        $charset = 'utf8';
        
        $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
        $opciones = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ];
        
        return new PDO($dsn, $user, $pass, $opciones);
    }
}

Controllers

Models

ConsultaModel

ConsultaModel

Overview

Class Structure

Methods

__construct()

obtenerHistorial()

obtenerPorId()

Usage Examples

Retrieve Recent History (Default Limit)

Retrieve Last 50 Returns

Get Specific Return Details

Integration Points

ConsultaController

AdminController

Performance Considerations

Current Limitations

Recommended Enhancements

Comparison with DevolucionModel

Database Connection

Build docs developers (and LLMs) love

Controllers

Models

Documentation Index

​ConsultaModel

​Overview

​Class Structure

​Methods

​__construct()

​obtenerHistorial()

​obtenerPorId()

​Usage Examples

​Retrieve Recent History (Default Limit)

​Retrieve Last 50 Returns

​Get Specific Return Details

​Integration Points

​ConsultaController

​AdminController

​Performance Considerations

​Current Limitations

​Recommended Enhancements

​Comparison with DevolucionModel

​Related Components

​Database Connection

Build docs developers (and LLMs) love

ConsultaModel

Overview

Class Structure

Methods

__construct()

obtenerHistorial()

obtenerPorId()

Usage Examples

Retrieve Recent History (Default Limit)

Retrieve Last 50 Returns

Get Specific Return Details

Integration Points

ConsultaController

AdminController

Performance Considerations

Current Limitations

Recommended Enhancements

Comparison with DevolucionModel

Related Components

Database Connection