Server side date-range filtering

jrowan20

Hi All, I have utilised the server side rendering for my table now but ran into a bit of a problem. I was able to filter between 2 dates on the client side no problem using $.fn.dataTable.ext.search.push() but im not sure how to set this up for server side operations. Below are a few snippets of my code, I am using asp.net core currently. I have the searching/pagination and sorting working perfectly, but this issue im stuck on.

I am aware my fix is likely to be on the server side of the logic rather than my implementation within datatables

datatable

var dataTable
$(document).ready(function () {
    dataTable = $('#R-load').DataTable({
        "processing": true,
        "serverSide": true,
        "filter": true,
        "ajax": {
            "url": "/Ahr/Record",
            "type": "POST",
            "datatype": "json",
            data: function (d) {
                d.min = $('#min-date').val();
                d.max = $('#max-date').val();
               
            },

        },


        "columnDefs": [{
            "targets": [0],
            "visible": false,
            "searchable": false
        }],
        "columns": [
            { "data": "id", "name": "Id", "autoWidth": true },
            { "data": "patient_Id", "name": "Patient_Id", "width": "10%", "font-size": "11px" },
            { "data": "surname", "name": "Surname", "width": "10%", "font-size": "11px" },
            { "data": "forename", "name": "Forename", "width": "10%", "font-size": "11px" },

            {
                "data": "request_Date", "name": "Request_Date",
                "render": function (data) {
                    return `
                              <p> ${moment(data).format('MM/DD/YYYY')} </p>
`;

                }, "width": "7%"
            },


        ],


        "language": {
            "emptyTable": "No Data found"
        },
    });




    $('#min-date, #max-date').change(function () {
        dataTable.draw();
    })

HTML

  <input type="text"
                                   id="min-date"
                                   name="min-date"
                                   @*data-date-format="dd/M/yyyy"*@
                                   class=" mb-2 mt-1"
                                   placeholder="Start Date"
                                   style="border: 1px solid lightgrey; width: 175px; border-radius: 5%">

                            <div>
                                <span class="fa-stack fa-1x">
                                    <i class="fas fa-circle fa-stack-2x m-2 text-warning"></i>
                                    <i class="far fa-calendar-alt fa-stack-1x fa-inverse"></i>
                                </span>
                            </div>
                            <input type="text"
                                   @*data-date-format="dd/M/yyyy"*@
                                   name="max-date"
                                   id="max-date"
                                   class=" mb-2 mt-1 "
                                   placeholder="End Date"
                                   style="border: 1px solid lightgrey; width: 175px; border-radius: 5%">

Controller (Asp.net core)

  [HttpPost]
        public IActionResult GetRecord()
            {
            try
                {
                var allRecords = _db.Records;
                var draw = Request.Form["draw"].FirstOrDefault();
                var start = Request.Form["start"].FirstOrDefault();
                var length = Request.Form["length"].FirstOrDefault();
                var sortColumn = Request.Form["columns[" + Request.Form["order[0][column]"].FirstOrDefault() + "][name]"].FirstOrDefault();
                var sortColumnDirection = Request.Form["order[0][dir]"].FirstOrDefault();
                var searchValue = Request.Form["search[value]"].FirstOrDefault();            
                int pageSize = length != null ? Convert.ToInt32(length) : 0;
                int skip = start != null ? Convert.ToInt32(start) : 0;
                int recordsTotal = 0;
                var archiveData = (from record in allRecords select record);
                var min = Request.Form["min-date"];
                var max = Request.Form["min-date"];
               




                if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDirection)))

                    {
                    archiveData = archiveData.OrderBy(sortColumn + " " + sortColumnDirection);
                    }
                if (!string.IsNullOrEmpty(searchValue))

                    {
                    archiveData = archiveData.Where(m => m.Forename.Contains(searchValue.ToLower())
                                                || m.Surname.Contains(searchValue.ToLower())
                                                || m.Patient_Id.Contains(searchValue)
                                                || m.Request_Date.ToString().Contains(searchValue)
                                                
                    }

                recordsTotal = archiveData.Count();

                var data = archiveData.Skip(skip).Take(pageSize).ToList();
                var jsonData = new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data };
                return Ok(jsonData);

                }
            catch (Exception e)
                {
                throw (e);
                }
            }

jrowan20

Would my Ajax:data post request be correct also

colin

These two threads, here and here, may help as they're discussing the same thing,

Colin