CI 4.0 MedicalSoft Datatable para tabla de Bitacora

Ya vimos como guardar los datos de la bitácora, ahora veremos como usar el datatable con server side para mostrar los datos de guardados en la bitácora

Primero creamos el archivo BitacoraModel.php de la siguiente forma en la carpeta app/model/ aunque a estas altura ya debemos tenerlo

<?php

namespace App\Models;

use CodeIgniter\Model;

class BitacoraModel extends Model
{
    protected $table      = 'bitacora';
    protected $primaryKey = 'id';

    protected $useAutoIncrement = true;

    protected $returnType     = 'array';
    protected $useSoftDeletes = true;

    protected $allowedFields = ['id','descripcion', 'usuario'];

    protected $useTimestamps = true;
    protected $createdField  = 'created_at';
    protected $deletedField  = 'deleted_at';

    protected $validationRules    = [];
    protected $validationMessages = [];
    protected $skipValidation     = false;
    
    
    
    const ORDERABLE = [
        1 => 'id',
        2 => 'descripcion',
        3 => 'usuario',
        4 => 'created_at',
        
    ];

    /**
     * Get resource data.
     *
     * @param string $search
     *
     * @return \CodeIgniter\Database\BaseBuilder
     */
   public function getResource(string $search = '')
    {
        $builder = $this->builder()
            ->select('id, descripcion, usuario, created_at');

        $condition = empty($search)
            ? $builder
            : $builder->groupStart()
                ->like('descripcion', $search)
                ->orLike('Usuario', $search)
                ->orLike('created_at', $search)
            ->groupEnd();

        return $condition->where('deleted_at', null);
    }
}

Para convertir fácilmente el resultado de la consulta SQL del QUERYBUILDER a formato json que usa datatable instalamos en la terminal el siguiente componente de hermawan usando composer

composer require hermawan/codeigniter4-datatables

Creamos el archivo BitacoraController.php con los siguientes, si observamos en la función index vemos que solo con una linea se crea el json

<?php

namespace App\Controllers;

use App\Controllers\BaseController;
use \App\Models\BitacoraModel;
use CodeIgniter\API\ResponseTrait;

class BitacoraController extends BaseController {

    use ResponseTrait;

    protected $bitacora;

    public function __construct() {
        $this->bitacora = new BitacoraModel();
        helper('menu');
    }

    public function index() {


        if ($this->request->isAJAX()) {

            /*
            $start = $this->request->getGet('start');
            $length = $this->request->getGet('length');
            $search = $this->request->getGet('search[value]');
            $order = BitacoraModel::ORDERABLE[$this->request->getGet('order[0][column]')];
            $dir = $this->request->getGet('order[0][dir]');
*/

           $datos = $this->bitacora->select('id,descripcion,usuario,created_at');
           // $resultado = $this->bitacora->findAll();
                                   // $this->bitacora->getResource()->countAllResults(),
                                   // $this->bitacora->getResource($search)->countAllResults()
           return \Hermawan\DataTables\DataTable::of($datos)->toJson();
        }
        
        $titulos["title"] = lang('log.title');
        $titulos["subtitle"] = lang('log.subtitle');
        //$data["data"] = $datos;
        return view('bitacora',$titulos);
    }

    public function tabla() {


        if ($this->request->isAJAX()) {
            $start = $this->request->getGet('start');
            $length = $this->request->getGet('length');
            $search = $this->request->getGet('search[value]');
            $order = BitacoraModel::ORDERABLE[$this->request->getGet('order[0][column]')];
            $dir = $this->request->getGet('order[0][dir]');

            return $this->respond(Collection::datatable(
                                    $this->bitacora->getResource($search)->orderBy($order, $dir)->limit($length, $start)->get()->getResultObject(),
                                    $this->bitacora->getResource()->countAllResults(),
                                    $this->bitacora->getResource($search)->countAllResults()
            ));
        }
    }

}

Creamos la vista en app/views/bitacora.php

<?= $this->include('agungsugiarto\boilerplate\Views\load\select2') ?>
<?= $this->include('agungsugiarto\boilerplate\Views\load\datatables') ?>
<!-- Extend from layout index -->
<?= $this->extend('agungsugiarto\boilerplate\Views\layout\index') ?>

<!-- Section content -->
<?= $this->section('content') ?>
<!-- SELECT2 EXAMPLE -->
<div class="card card-default">
    <div class="card-header">
        <div class="card-tools">
            <div class="btn-group">


            </div>
        </div>
    </div>
    <div class="card-body">
        <div class="row">
            <div class="col-md-12">
                <div class="table-responsive">
                    <table id="tablaBitacora" class="table table-striped table-hover va-middle tablaBitacora">
                        <thead>
                            <tr>
                                <th>#</th>
                                <th><?= lang('log.description') ?></th>
                                <th><?= lang('log.user') ?></th>
                                <th><?= lang('log.date') ?></th>


                            </tr>
                        </thead>
                        <tbody>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>
<!-- /.card -->
<?= $this->endSection() ?>

<?= $this->section('js') ?>
<script>

/**
 * Cargamos la tabla
 */
$('.tablaBitacora').DataTable( {
    "ajax": "<?= route_to('admin/bitacora/manage') ?>",
    "deferRender": true,
     "serverSide" : true,
  "retrieve": true,
  "processing": true

} )

    
</script>
<?= $this->endSection() ?>

En las rutas app/config/routes.php agregamos la siguiente linea

    /*
     * RUTA BITACORA
     */

    $routes->resource('bitacora', [
        'filter' => 'permission:bitacora-permiso',
        'controller' => 'BitacoraController',
        'except' => 'show'
    ]);