Date Filter Issues

stephensharp1216

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

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:

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

Allan

crwdzr

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

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