I am trying to filter data by date-range using jQuery on the client side but it is not doing it properly, It is showing me data (dummy) from the year 2065 and onwards (linked the Screen shot below). I have been trying for almost a week now but finally looks like I need some help on it.
Here's my C# controller code for your inspection:
DateTime StartDate = DateTime.MinValue;
DateTime EndDate = DateTime.MaxValue;
if (dateFilter.Contains('~'))
{
StartDate = dateFilter.Split('~')[0] == "" ? DateTime.MinValue : Convert.ToDateTime(dateFilter.Split('~')[0]);
EndDate = dateFilter.Split('~')[1] == "" ? DateTime.MaxValue : Convert.ToDateTime(dateFilter.Split('~')[1]);
}
filteredTracks = filteredTracks
.Where(c => (StartDate == DateTime.MinValue || StartDate <= c.Date)
&&
(EndDate == DateTime.MaxValue || c.Date <= EndDate)
).OrderByDescending(i => i.Date).ToList();
Here's the jQuery client-side for your inspection:
var todayDate = new Date();
var endDate = todayDate.getDate() + '/' + (todayDate.getMonth() + 1) + '/' + todayDate.getFullYear();
var d = new Date();
var st = d.setDate(todayDate.getDate() - 75);
var startDate = d.getDate() + '/' + (d.getMonth() + 1) + '/' + d.getFullYear();
$('#startdateadmin-venue').val(startDate);
$('#enddateadmin-venue').val(endDate);
$('#filterStyle-venue').click(function () {
$("#AdvancedfilterForm-venue").slideToggle(300);
});
$(function () {
$("#startdateadmin-venue").datepicker({
dateFormat: 'dd/mm/yy',
changeMonth: true,
changeYear: true
});
});
$(function () {
$("#enddateadmin-venue").datepicker({
dateFormat: 'dd/mm/yy',
changeMonth: true,
changeYear: true
});
});
$('#searchrecord-button').click(function (e) {
e.preventDefault();
var startDate = $('#startdateadmin-venue').val();
var endDate = $('#enddateadmin-venue').val();
$('#myDataTable').dataTable().fnDestroy();
var oTable = $('#myDataTable').dataTable('load',{
"oLanguage": {
"sSearch": " "
},
"bAutoWidth": false,
"sAjaxSource": "AjaxHandler_datesrange",
"bServerSide": true,
"bProcessing": false,
"sScrollY": "333",
"iDisplayLength": 300,
"bPaginate": true,
"bRetrieve": true,
"bDestroy": true,
"sPaginationType": "full_numbers",
"aoColumns": [
{ "mData": "TrackID", "sWidth": "1%" },
{
"mData": "Date", "bSortable": false, "sWidth": "1%",
"fnRender": function (obj, val) {
var dx = new Date(parseInt(val.substr(6)));
var dd = dx.getDate();
var mm = dx.getMonth() + 1;
var yy = dx.getFullYear();
if (dd <= 9) {
dd = "0" + dd;
}
if (mm <= 9) {
mm = "0" + mm;
}
return dd + "/" + mm + "/" + yy;
}
},
{ "mData": "TrackName", "sWidth": "43%" },
{ "mData": "ArtistName", "sWidth": "30%" },
{ "mData": "Times", "sWidth": "1%" }
]
});
});
EDIT: Controller code: GetsData from an Excel File:
public static List<TopPlayed> GetTracks()
{
if (TrackData == null)
{
string Path = @"C:\\5Newwithdate-6r.xls";
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= '" + Path + "';Extended Properties=" + (char)34 + "Excel 8.0;IMEX=1;" + (char)34 + "");
OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", con);
con.Close();
System.Data.DataTable data = new System.Data.DataTable();
da.Fill(data);
List<TopPlayed> daa = new List<TopPlayed>();
TrackData = new List<TopPlayed>();
foreach (DataRow p in data.Rows)
{
TopPlayed top = new TopPlayed()
{
TrackID = Convert.ToInt32(p.Field<double>("TrackID")),
Date = p.Field<DateTime>("DateTimes"),
TrackName = p.Field<string>("TrackName"),
ArtistName = p.Field<string>("ArtistName"),
Times = Convert.ToInt32(p.Field<double>("Times"))
};
TrackData.Add(top);
}
}
return TrackData;
}
EDIT: Controller code: Filtering: All the filtering works apart from Dates
public ActionResult AjaxHandler_datesrange(jQueryDataTableParamModel param)
{
var allTracks = DataRepository.GetTracks();
IEnumerable<TopPlayed> filteredTracks;
var dateFilter = Convert.ToString(Request["sSearch_1"]);
var trackFilter = Convert.ToString(Request["sSearch_2"]);
var artistFilter = Convert.ToString(Request["sSearch_3"]);
// Search Filter
if (!string.IsNullOrEmpty(param.sSearch))
{
var isTrackSearchable = Convert.ToBoolean(Request["bSearchable_1"]);
filteredTracks = DataRepository.GetTracks()
.Where(c => isTrackSearchable && c.TrackName.ToLower().Contains(param.sSearch.ToLower()));
}
else
{
filteredTracks = allTracks;
}
// Dates
DateTime StartDate = DateTime.MinValue;
DateTime EndDate = DateTime.MaxValue;
if (dateFilter.Contains('~'))
{
StartDate = dateFilter.Split('~')[0] == "" ? DateTime.MinValue : Convert.ToDateTime(dateFilter.Split('~')[0]);
EndDate = dateFilter.Split('~')[1] == "" ? DateTime.MaxValue : Convert.ToDateTime(dateFilter.Split('~')[1]);
}
filteredTracks = filteredTracks
.Where(c => (StartDate == DateTime.MinValue || StartDate <= c.Date)
&&
(EndDate == DateTime.MaxValue || c.Date <= EndDate)
).OrderByDescending(i => i.Date).ToList();
// Sorting
var sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]);
var isNameSortable = Convert.ToBoolean(Request["bSortable_1"]);
var isAddressSortable = Convert.ToBoolean(Request["bSortable_2"]);
Func<TopPlayed, string> orderingFunction = (c => sortColumnIndex == 1 && isNameSortable ? c.TrackName :
sortColumnIndex == 2 && isAddressSortable ? c.ArtistName :
"");
var sortDirection = Request["sSortDir_0"]; // asc or desc
if (sortDirection == "asc")
filteredTracks = filteredTracks.OrderBy(orderingFunction);
else
filteredTracks = filteredTracks.OrderByDescending(orderingFunction);
// Paging
var displayedTracks = filteredTracks.Skip(param.iDisplayStart).Take(param.iDisplayLength);
return Json(new
{
sEcho = param.sEcho,
iTotalRecords = allTracks.Count(),
iTotalDisplayRecords = filteredTracks.Count(),
aaData = displayedTracks
},
JsonRequestBehavior.AllowGet);
}
Any help would be great and thanks for your time.
