how to post a model to controller in MVC using DataTables date range filter?

how to post a model to controller in MVC using DataTables date range filter?

jcanalesjcanales Posts: 3Questions: 1Answers: 0
edited June 2022 in Free community support
****-------------------------------------------- VIEW ----------------------------------------****
<!-- FILTRAR POR RANGO DE FECHA DE SUSCRIPCIÓN -->                       
            <div class="fechas">
                <label class="control-label col-md-2">FILTRO POR FECHA DE SUSCRIPCIÓN</label>   
                <input type="date" id="min"  class="dt-fecha">
                <input type="date" id="max" class="dt-fecha">   
            </div>
//....
     var minDate, maxDate;
        // Función de filtrado personalizado que buscará datos en la columna siete entre dos valores
        $.fn.dataTable.ext.search.push(
            function( settings, data, dataIndex ) {
                var min = minDate.val();
                var max = maxDate.val();
                var date = new Date( data[8] );
                if (
                    ( min === null && max === null ) ||
                    ( min === null && date <= max ) ||
                    ( min <= date   && max === null ) ||
                    ( min <= date   && date <= max )
                ) {
                    return true;
                }
                return false;
            }
        );
        
        $(document).ready(function() {
           //Crear entradas de fecha 
           minDate  = moment($("#min")).format('YYYY/MM/DD');
           maxDate = moment($("#max")).format('YYYY/MM/DD');
            
            //Inicialización de tablas de datos
            var table = $('#table').DataTable({ 
                processing: true, 
                serverSide: true, 
                ordering: false,      
               ajax: {
                    "url": "<?php echo site_url('info/ajax_list')?>",
                    "type": "POST"
                },
                columnDefs: [
                    { 
                        "targets": [ -1 ],
                        "orderable": false,   
                    },
                    { 
                        "targets": [ -2 ],   
                        "orderable": false,   
                    }
                ],
                language: {
                    "sProcessing": "Procesando...",
                    "sZeroRecords": "No se encontraron resultados",
                    "sEmptyTable": "Ningún dato disponible en esta tabla",
                    "sInfo": "Mostrando registros del _START_ al _END_ de un total de _TOTAL_ registros",
                    "sInfoEmpty": "Mostrando registros del 0 al 0 de un total de 0 registros",
                    "infoFiltered": "(filtrado de un total de _MAX_ datos)",
                    "sInfoPostFix": "",
                    "sUrl": "",
                    "sInfoThousands": ",",
                    "sLoadingRecords": "Cargando...",
                 
                    "oAria": {
                    "sSortAscending": ": Activar para ordenar la columna de manera ascendente",
                    "sSortDescending": ": Activar para ordenar la columna de manera descendente"},
                    "paginate": {
                        "first": "Primero",
                        "last": "Último",
                        "next": "Siguiente",
                        "previous": "Anterior"
                    },
                },
            });  
            // Refiltrar la tabla
            $('#min, #max').on('change', function () { table.draw(); });  


****-------------------------------------------- CONTROLLER  ----------------------------------------****
public function ajax_list()
    {
        $list = $this->Info_model->get_datatables();
        $data = array();
        $no = $_POST['start'];
        foreach ($list as $incidencia) {
            $no++;
            $row = array();
                        
            $row[] = '-';
            $row[] = $incidencia->info_id;          
            $row[] = $incidencia->nombrenivel;
            $row[] = $incidencia->nombreambito;
            $row[] = $incidencia->nombretipo;
            $row[] = $incidencia->nombrecategoria;
            $row[] = $incidencia->nombreinstitucion;
            $row[] = $incidencia->info_objetivo;
            $row[] =  date("d/m/Y", strtotime($incidencia->info_fecha_suscripcion)); 
            $row[] = $incidencia->info_vigencia;
            $row[] = $incidencia->info_extension;
            $row[] =  $incidencia->info_fecha_termino; 
            $row[] = $incidencia->nombrealerta;
            $row[] = $incidencia->info_observaciones;
            $row[] = $incidencia->info_estado;
            $row[] = $incidencia->nombreresponsable;
        
            
            if($incidencia->archivo) {
                $row[] = '<a href="'.base_url('upload/info/'.$incidencia->archivo).'" target="_blank" title="'.$incidencia->archivo.'"> <i class="fa fa-file-image-o" style="font-size: 20px;"></i></a>';
            }else {
            $row[] = '<i class="fa fa-chain-broken" style="font-size: 15px;color: gray;"  title="Sin adjunto"></i>';
            }
            $row[] =  date("d/m/Y", strtotime($incidencia->fecha_creacion)); 
                
            if($this->session->userdata("rolid")==1){
                $row[] = '<div class="dropdown">
                <button class="btn btn-default dropdown-toggle" type="button" data-toggle="dropdown">
                <i class="glyphicon glyphicon-cog"></i>
                <span class="caret"></span></button>
                <ul class="dropdown-menu  navbar-left  pull-right">
                <li><a  href="javascript:void(0)" title="Editar" onclick="edit_incidencia('."'".$incidencia->info_id."'".')"><i class="glyphicon glyphicon-pencil"></i> Editar</a> </li>
                  <li><a  href="javascript:void(0)" title="Borrar" onclick="delete_incidencia('."'".$incidencia->info_id."'".')"><i class="glyphicon glyphicon-trash"></i> Borrar</a> </li>
                </ul></div>';
            }else{
                $row[] = '<div class="dropdown">
                <button class="btn btn-default dropdown-toggle" type="button" data-toggle="dropdown">
                <i class="glyphicon glyphicon-cog"></i></div>';
            } 
            
                
            $data[] = $row;
        }

        $output = array(
                        "draw" => $_POST['draw'],
                        "recordsTotal" => $this->Info_model->count_all(),
                        "recordsFiltered" => $this->Info_model->count_filtered(),
                        "data" => $data,
                );
            echo json_encode($output);
    }

******-------------------------------------------- MODEL ----------------------------------------******

    
private function _get_datatables_query($term=''){ 
        
$column = array('info_id','id_nivel','id_ambito','id_tipo','id_categoiria','id_institucion','info_objetivo','info_fecha_suscripcion','info_vigencia','info_extension','info_fecha_termino','id_alerta','info_observaciones'.'info_estado','id_responsable','info_en_basedatos','info_en_fisico','info_en_pdf','info_varios_documentos','info_fecha_creacion');

$this->db->select('i.*,n.nivel_nombre as nombrenivel, a.ambito_nombre as nombreambito,t.tipo_nombre as nombretipo, 
        c.categoria_nombre as nombrecategoria, in.institucion_nombre as nombreinstitucion, al.alerta_nombre as nombrealerta,r.responsable_nombre as nombreresponsable');//
    
        $this->db->from('info as i');
        $this->db->join('nivel as n', 'i.id_nivel =n.nivel_id','left');
        $this->db->join('ambito as a', 'i.id_ambito =a.ambito_id','left');
        $this->db->join('tipo as t', 'i.id_tipo =t.tipo_id','left');
        $this->db->join('categoria as c', 'i.id_categoiria =c.categoria_id','left');
        $this->db->join('institucion as in', 'i.id_institucion =in.institucion_id','left');
        $this->db->join('alerta as al', 'i.id_alerta =al.alerta_id','left');
        $this->db->join('responsable as r', 'i.id_responsable =r.responsable_id','left');
        $this->db->or_like('n.nivel_nombre', $term);    
        $this->db->or_like('a.ambito_nombre', $term);
        $this->db->or_like('t.tipo_nombre', $term);
        $this->db->or_like('c.categoria_nombre', $term);         
        $this->db->or_like('in.institucion_nombre', $term); 
        $this->db->or_like('i.info_id', $term);
        $this->db->or_like('i.info_objetivo', $term);
        $this->db->or_like('i.info _fecha_suscripcion', $term);
        $this->db->or_like('i.info _vigencia', $term);
        $this->db->or_like('i.info _extension', $term);
        $this->db->or_like('i.info _fecha_termino', $term);
        $this->db->or_like('al.alerta_nombre', $term);
        $this->db->or_like('i.info _observaciones', $term);
        $this->db->or_like('i.info _estado', $term);
        $this->db->or_like('r.responsable_nombre', $term);
        $this->db->or_like('i.info _en_basedatos', $term);
        $this->db->or_like('i.info _en_fisico', $term);
        $this->db->or_like('i.info _en_pdf', $term);
        $this->db->or_like('i.info _varios_documentos', $term);
        $this->db->or_like('i.info _fecha_creacion', $term);

    //  $this->db->where('i.info_fecha_suscripcion BETWEEN "'. $from_date2. '" AND "'. $to_date2. '" ');
    
        if(isset($_POST['order'])) 
        {$this->db->order_by($column[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);} 
        else if(isset($this->order)){
           $order = $this->order;
           $this->db->order_by(key($order), $order[key($order)]);}
    }

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

Answers

  • allanallan Posts: 63,288Questions: 1Answers: 10,427 Site admin

    $.fn.dataTable.ext.search won't do anything when you are using server-side processing. That is a client-side search, which is irrelevant with serverSide enabled.

    What you need to do is add the min / max value to the data sent to the server by using ajax.data - e.g.:

    data: function (d) {
      d.min = minDate.val();
      d.max = maxDate.val();
    }
    

    Then add logic to your server-side controller to apply the filter.

    Allan

  • jcanalesjcanales Posts: 3Questions: 1Answers: 0

    thanks for your answer, I am new in the world of dataTable, I would like to ask you what would be the logic for "DataTablesAdvanced interaction
    features for your tables" with MVC.

  • jcanalesjcanales Posts: 3Questions: 1Answers: 0

    I wanted to comment that you enter the indicated line of code:

    1. var table = $('#table').DataTable({
    2. processing: true,
    3. serverSide: true,
    4. ordering: false,
    5. ajax: {
    6. "url": <?php echo site_url('convenios/ajax_list')?>,
    7. "type": POST,
    8. "data": function (d){
    9. d.min = $('#min').val();
    10. d.max = $('#max').val();
    11. }
    12. },
    13. columnDefs: [... ],
    14. language: {... },
    15. });

    but when executing I get the following error:
    "Uncaught SyntaxError: Unexpected token ':' (at (índice):6:32)"

    Could you point me to a link that I can use as a guide to "DataTables date range filter with MVC".

    thank you

  • colincolin Posts: 15,237Questions: 1Answers: 2,599

    That's because of the PHP line mixed into the JavaScript - that would cause that error.

    Colin

Sign In or Register to comment.