Date Filter Issues

stephensharp1216 · November 2017

Good afternoon,
I'm trying to filter data between 2 dates from a single source date in one column. I have read the plugins and searched numerous solutions online to no avail. Could anyone please have a look over my code and let me know if I'm missing something. Much appreciated, Steve.

PS please dont bust my balls as only been developing for a few months. Thanks

 <div class="wrapper">
    <div id="accordion">
        <h3>Qualification Management</h3> 
        <div class="row">
            <div class="col-md-12">
                <table border="0" cellspacing="5" cellpadding="5">
                    <tbody>
                        <tr>
                            <td>Minimum Date:</td>
                            <td><input name="min" id="min" type="text"></td>
                        </tr>
                        <tr>
                            <td>Maximum Date:</td>
                            <td><input name="max" id="max" type="text"></td>
                        </tr>
                    </tbody>
                </table>
                <table width="100%" class="display" id="employee_has_qual" cellspacing="0">
                    <thead>
                        <tr>
                            {{--  <th>View</th>  --}}
                            <th>Edit</th>
                            {{--  <th>Delete</th>  --}}
                            <th>First Names</th> 
                            <th>Middle Name</th> 
                            <th>Last Name</th> 
                            <th>Employee Number</th> 
                            <th>Occupation</th> 
                            <th>Company</th>  
                            <th>Division</th> 
                            <th>Region</th> 
                            <th>Card Number</th>
                            <th>Expiry</th>
                            <th>Card Front</th>
                            <th>Card Back</th>
                            <th>Type</th>
                            <th>Category</th>
                            <th>Class</th>
                            <th>Approved Date</th>
                            <th>Approved By</th>
                            <th>Card Type</th> 
                            <th>Address</th> 
                            <th>City</th> 
                            <th>Postcode</th> 
                            <th>Phone Number</th> 
                            <th>Email</th> 
                            <th>Country</th> 
                            <th>Date of Birth</th> 
                            <th>Hire Date</th> 
                            <th>NI Number</th> 
                            <th>Employee ID</th> 
                            <th>Qualification ID</th>                           
                        </tr>
                    </thead>
                </table>
                <br />
                        {{--  <button data-token="{{ csrf_token() }}" id="submit" type="submit" >Add to Site</button>  --}}
            </div>
        </div>
     </div>
</div> 

<script type="text/javascript">
$(document).ready(function() {

    $.fn.dataTable.ext.search.push(
        function (settings, data, dataIndex) {
            var min = $('#min').datepicker("getDate");
            var max = $('#max').datepicker("getDate");
            // need to change str order before making  date obect since it uses a new Date("mm/dd/yyyy") format for short date.
            //var startDate = data[10];
            var d = data[10].split("-");
            var startDate = new Date(d[0]+"-"+d[1]+"-"+d[2]);

            if (min == null && max == null) { return true; }
            if (min == null && startDate <= max) { return true;}
            if(max == null && startDate >= min) {return true;}
            if (startDate <= max && startDate >= min) { return true; }
            return false;
            console.log(startDate);
        }
    );

    $("#min").datepicker({ onSelect: function () { oTable.draw(); }, changeMonth: true, changeYear: true, dateFormat:"yy-mm-dd"});
    $("#max").datepicker({ onSelect: function () { oTable.draw(); }, changeMonth: true, changeYear: true, dateFormat:"yy-mm-dd"});

    // Setup - add a text input to each footer cell
    //$('#employee_has_qual tfoot th').each( function () {
    //    var title = $(this).text();
    //   $(this).html( '<input type="text" placeholder="Search '+title+'" />' );
    //} );

    var oTable = $('#employee_has_qual').DataTable({
        "processing": true,
        "serverSide": true,
        "lengthMenu": [[15, 25, 50, -1], [15, 25, 50, "All"]],
        "select": "multi",
        "searching": true,
        "info": true,        
        //"scrollY": "500px",
        //"scrollX": true,
        //"scrollCollapse": true,
        "paging": true,
        "ajax": "{{ route('report-mgmt.aeqdatatables.getposts') }}",
        "columns": [
            {data: 'id', name: 'id', orderable: false, searchable: false, render: function ( data, type, row, meta ) 
            {
                if(type === 'display')
                {
                    data = '<a href="/ehq-management/' + data + '/edit"><button class="btn btn-xs btn-info"><span class="glyphicon glyphicon-edit"></span> Edit</button></a>';
                }       
                return data; 
            }},
            {data: 'firstname', name: 'employee.firstname'},
            {data: 'middlename', name: 'employee.middlename'},
            {data: 'lastname', name: 'employee.lastname'},
            {data: 'employee_number', name: 'employee.employee_number'},
            {data: 'occupation', name: 'employee.occupation'},
            {data: 'company_name', name: 'company.name'},
            {data: 'division_name', name: 'division.name'},
            {data: 'region_name', name: 'region.name'},
            {data: 'card_number', name: 'card_number'}, 
            {data: 'expiry', name: 'expiry', createdCell: function (td, cellData, rowData, row, col) 
            {
                var now_date = new Date().getTime();
                var qual_date = Date.parse($(td).text().replace(/(\d{2})\/(\d{2})\/(\d{4})/, '$2/$1/$3'));
                var now_date_plus_two_mth = new Date((+new Date) + 5184000000);
                if(qual_date < now_date)
                {
                    $(td).append('<div class="led-red" id="led-red" color="red"></div>')
                }
                else if(qual_date > now_date && qual_date < now_date_plus_two_mth)
                {
                    $(td).append('<div class="led-yellow" id="led-yellow" color="yellow"></div>')
                }
                else if(qual_date > now_date_plus_two_mth)
                {
                    $(td).append('<div class="led-green" id="led-green" color="green"></div>')
                }
                else
                {
                    $(td).append('<div class="led-black" id="led-black" color="black"></div>')
                }

            } },
            {data: 'image1_path', render : function ( data, type, row) 
            {
                
                return '<img height="40px" width="55px" src="../../' + data + '">';
            }},

            {data: 'image2_path', render : function ( data, type, row) 
            {
                
                return '<img height="40px" width="55px" src="../../' + data + '">';
            }},
            {data: 'type', name: 'qual.type' },
            {data: 'category', name: 'qual.category'},
            {data: 'class', name: 'qual.class'},
            {data: 'approved_date', name: 'approved_date'},
            {data: 'approved_by_id', name: 'approved_by_id'},
            {data: 'card_type', name: 'qual.card_type'},
            {data: 'address', name: 'employee.address'},
            {data: 'city', name: 'employee.city'},
            {data: 'postcode', name: 'employee.postcode'},
            {data: 'phone_number', name: 'employee.phone_number'},
            {data: 'email', name: 'employee.email', render: function ( data, type, row, meta ) 
            {
                if(type === 'display')
                {
                    data = '<a href="mailto:' + encodeURIComponent(data) + '">' + data + '</a>';
                }
                return data; 
            }},
            {data: 'country_name', name: 'country.name'},
            {data: 'birthdate', name: 'employee.birthdate'},
            {data: 'date_hired', name: 'employee.date_hired'},
            {data: 'ni_number', name: 'employee.ni_number'}, 
            {data: 'employee_id', name: 'employee_id'},
            {data: 'qual_id', name: 'qual_id' },


        ]
    });

    // Apply the search
    //oTable.columns().every( function () {
    //    var that = this;
    //    $( 'input', this.footer() ).on( 'keyup change', function () {
    //        if ( that.search() !== this.value ) {
    //            that
    //                .search( this.value )
    //                .draw();
    //        }
    //    } );
    //} );

    // Event listener to the two range filtering inputs to redraw on input
    $('#min, #max').change(function () {
        oTable.draw();

    });


    //$('#employee_has_qual tbody').on( 'click', 'tr', function () {
    //$(this).toggleClass('selected');
    //} );

});


</script>

allan · November 2017

You have:

"serverSide": true,

So client-side filtering will have no effect. When server-side processing is enabled, the server-side does all of the processing (sort, page and filter).

So you have two options:

Don't use server-side processing. You should only need it if you have tens of thousands of rows or more.
Implement the date search in the server-side processing script.

Allan

crwdzr · November 2017

Ya it won't make much sense to do client side filtering with serverside filtering enabled.

I prefer to do client side filtering for my apps and I have to do range based filtering all the time. You don't look like you're too far off.

I do have a few minor recommendations, if you opt to switch to client-side to implement this:

// you may want to include this, by doing so you 
// clear the previous filter(s) when you want to 
// run your next one.
$.fn.dataTable.ext.search = []

// cache them ahead of time, it's better than 
// grabbing them from inside the search function. 
// otherwise, jquery will dive into the DOM every
// single time it tests a row with this filter
// thanks to closure, they will still be accessible.
const min = $('#min').datepicker("getDate");
const max = $('#max').datepicker("getDate");

$.fn.dataTable.ext.search.push((settings, data, dataIndex) => {
    const d = data[10].split("-");
    const startDate = new Date(d[0]+"-"+d[1]+"-"+d[2]);

    // I didn't go over any of the logic inside this function, 
    // but this should be enough to accurately filter it, 
    // assuming 'max' and 'min' are both formatted correctly, too
    const startValid = (startDate >= max)
    const endValid = (startDate <= min)
    return (startValid && endValid) ? true : false      
})

(yourTable).draw(false)
// Additionally, you may want to put all of this in a function. By
// the looks of it, your change listeners for min/max only redraw
// the table, rather than call this logic to push a new set of filters and
// THEN redraw the table.
$('#min, #max').change(function () {        
    updateRangeFilters()
    // have that function or w/e you name it run the above logic, instead
});

If you choose to do it serverside, this is how I approached it:

// in my datatables config: 
ajax: {
    url: whatever.com/your/url,
    type: 'POST',
    data: args => getAjaxArguments(args),
},

// adapt it however you want:
function getAjaxArguments(args) {
    const min = $('#min').datepicker("getDate")
    const max = $('#max').datepicker("getDate")

    // add your custom arguments wherever you want
    args.myArgs.min = min
    args.myArgs.max = max

    return args
}

// then in the server side, just access $_POST['myArgs']['min']
// or wherever it ends up and insert it into your 
// query however you want

stephensharp1216 · November 2017

Thanks Allan,
That makes a lot of sense. I have tens of thousands of records so will add to the server-side processing script.
Thanks for the quick response.
Regards
Steve

Date Filter Issues

Date Filter Issues

Answers

Howdy, Stranger!

Categories

DataTables

Date Filter Issues

Date Filter Issues

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables