Is there a way to filter database records based on the columns QtyRecieved, QtyRecievedand Void using a static HTML dropdown list?
The QtyRecieved and QtyRecievedand column are both decimal values, while Void is a boolean.
This is what I have attempted:
@Html.DropDownList("Filter", new List<SelectListItem>
{
new SelectListItem{ Text="Open", Value = "0" },
new SelectListItem{ Text="Partial", Value = "1" },
new SelectListItem{ Text="All", Value = "2" }
})
I have used AJAX to send the request to the controller:
$("#Filter").change(function () {
var listval = $("select option:selected").text();
$.ajax({
type: "GET",
url: "@Url.Action("Index", "MaterialRequest")",
data: { id: listval }
});
});
In my controller, I have implemented an 'else if' condition to display records as follows:
When Open is selected, it should display records where QtyRecieved == QtyRequested.
When Partial is selected, it will show records where QtyRecieved < QtyRequested and Void = True.
If All is selected, then all records will be shown.
Please provide assistance with the query to filter records or suggest an alternative method to achieve this.
public ActionResult Index(string listval)
{
if (listval == "Open")
{
ViewBag.Items = db.Query<MaterialDeptItemVw>("Select mt.MaterialRequestId, mt.TDate, d.DepartmentName, it.ItemName, mt.QtyRequested, mt.Comment, mt.RecievedDateTime , u.UnitName from MaterialRequest mt INNER JOIN Department d ON mt.DepartmentId = d.DepartmentId INNER JOIN Items it ON mt.ItemId = it.ItemId INNER JOIN Units u ON it.UnitId = u.UnitId where QtyRecieved = QtyRequested");
}
else if (listval == "Partial")
{
ViewBag.Items = db.Query<MaterialDeptItemVw>("Select mt.MaterialRequestId, mt.TDate, d.DepartmentName, it.ItemName, mt.QtyRequested, mt.Comment, mt.RecievedDateTime , u.UnitName from MaterialRequest mt INNER JOIN Department d ON mt.DepartmentId = d.DepartmentId INNER JOIN Items it ON mt.ItemId = it.ItemId INNER JOIN Units u ON it.UnitId = u.UnitId where QtyRecieved < QtyRequested and Void = 0");
}
else if (listval == "All")
{
ViewBag.Items = db.Query<MaterialDeptItemVw>("Select mt.MaterialRequestId, mt.TDate, d.DepartmentName, it.ItemName, mt.QtyRequested, mt.Comment, mt.RecievedDateTime , u.UnitName from MaterialRequest mt INNER JOIN Department d ON mt.DepartmentId = d.DepartmentId INNER JOIN Items it ON mt.ItemId = it.ItemId INNER JOIN Units u ON it.UnitId = u.UnitId");
}
return View();
}