public ActionResult AjaxDataProvider(JQueryDataTableParamModel param) { var allItems = from a in db.PDs select a; IEnumerable<PD> filteredItems; if (!string.IsNullOrEmpty(param.sSearch)) { //Used if particulare columns are filtered var IDFilter = Convert.ToString(Request["sSearch_0"]); var nameFilter = Convert.ToString(Request["sSearch_1"]); var uomFilter = Convert.ToString(Request["sSearch_2"]); var remarksFilter = Convert.ToString(Request["sSearch_3"]); var barcodeFilter = Convert.ToString(Request["sSearch_4"]); var withSerialFilter = Convert.ToString(Request["sSearch_5"]); var reorderFilter = Convert.ToString(Request["sSearch_6"]); var codeFilter = Convert.ToString(Request["sSearch_7"]); var g230Filter = Convert.ToString(Request["sSearch_8"]); var g233Filter = Convert.ToString(Request["sSearch_9"]); var g234Filter = Convert.ToString(Request["sSearch_10"]); var modelFilter = Convert.ToString(Request["sSearch_11"]); //Optionally check whether the columns are searchable at all var isIDSearchable = Convert.ToBoolean(Request["bSearchable_0"]); var isNameSearchable = Convert.ToBoolean(Request["bSearchable_1"]); var isUOMSearchable = Convert.ToBoolean(Request["bSearchable_2"]); var isRemarksSearchable = Convert.ToBoolean(Request["bSearchable_3"]); var isBarcodeSearchable = Convert.ToBoolean(Request["bSearchable_4"]); var isWithSerialSearchable = Convert.ToBoolean(Request["bSearchable_5"]); var isReorderSearchable = Convert.ToBoolean(Request["bSearchable_6"]); var isCodeSearchable = Convert.ToBoolean(Request["bSearchable_7"]); var isG230Searchable = Convert.ToBoolean(Request["bSearchable_8"]); var isG233Searchable = Convert.ToBoolean(Request["bSearchable_9"]); var isG234Searchable = Convert.ToBoolean(Request["bSearchable_10"]); var isModelSearchable = Convert.ToBoolean(Request["bSearchable_11"]); filteredItems = allItems.Where(c => isIDSearchable && c.ID.ToString().ToLower().Contains(param.sSearch.ToLower()) || isNameSearchable && c.Name.ToLower().Contains(param.sSearch.ToLower()) || isUOMSearchable && c.UOM.ToString().ToLower().Contains(param.sSearch.ToLower()) || isRemarksSearchable && c.Remarks.ToLower().Contains(param.sSearch.ToLower()) || isBarcodeSearchable && c.BarCode.ToLower().Contains(param.sSearch.ToLower()) || isWithSerialSearchable && c.WithSerial.ToString().ToLower().Contains(param.sSearch.ToLower()) || isReorderSearchable && c.Reorder.ToString().ToLower().Contains(param.sSearch.ToLower()) || isCodeSearchable && c.Code.ToLower().Contains(param.sSearch.ToLower()) || isG230Searchable && c.G230.ToString().ToLower().Contains(param.sSearch.ToLower()) || isG233Searchable && c.G233.ToString().ToLower().Contains(param.sSearch.ToLower()) || isG234Searchable && c.G234.ToString().ToLower().Contains(param.sSearch.ToLower()) || isModelSearchable && c.Model.ToLower().Contains(param.sSearch.ToLower())) .Select(c => c); } else { filteredItems = allItems; } var isIDSortable = Convert.ToBoolean(Request["bSortable_0"]); //int var isNameSortable = Convert.ToBoolean(Request["bSortable_1"]); //String var isUOMSortable = Convert.ToBoolean(Request["bSortable_2"]); //Int var isRemarksSortable = Convert.ToBoolean(Request["bSortable_3"]);//String var isBarcodeSortable = Convert.ToBoolean(Request["bSortable_4"]);//String var isWithSerialSortable = Convert.ToBoolean(Request["bSortable_5"]);//int var isReorderSortable = Convert.ToBoolean(Request["bSortable_6"]);//int var isCodeSortable = Convert.ToBoolean(Request["bSortable_7"]);//String var isG230Sortable = Convert.ToBoolean(Request["bSortable_8"]);//int var isG233Sortable = Convert.ToBoolean(Request["bSortable_9"]);//int var isG234Sortable = Convert.ToBoolean(Request["bSortable_10"]);//int var isModelSortable = Convert.ToBoolean(Request["bSortable_11"]);//string var sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]); if ( sortColumnIndex == 1 || sortColumnIndex == 3 || sortColumnIndex == 4 || sortColumnIndex == 7 || sortColumnIndex == 11 ) { Func<PD, string> orderingFunction = (c => sortColumnIndex == 1 && isNameSortable ? c.Name : sortColumnIndex == 3 && isRemarksSortable ? c.Remarks : sortColumnIndex == 4 && isBarcodeSortable ? c.BarCode : sortColumnIndex == 7 && isCodeSortable ? c.Code : sortColumnIndex == 11 && isModelSortable ? c.Model : ""); var sortDirection = Request["sSortDir_0"]; // asc or desc if (sortDirection == "asc") filteredItems = filteredItems.OrderBy(orderingFunction); else filteredItems = filteredItems.OrderByDescending(orderingFunction); } else // 2,5,6,8,9,10 { Func<PD, int> orderingFunction = (c => sortColumnIndex == 0 && isIDSortable ? c.ID : sortColumnIndex == 2 && isNameSortable ? Convert.ToInt32(c.UOM) : sortColumnIndex == 5 && isRemarksSortable ? Convert.ToInt32(c.WithSerial) : sortColumnIndex == 6 && isBarcodeSortable ? Convert.ToInt32(c.Reorder) : sortColumnIndex == 8 && isCodeSortable ? Convert.ToInt32(c.G230) : sortColumnIndex == 9 && isModelSortable ? Convert.ToInt32(c.G233) : sortColumnIndex == 10 && isModelSortable ? Convert.ToInt32(c.G234) : 0); var sortDirection = Request["sSortDir_0"]; // asc or desc if (sortDirection == "asc") filteredItems = filteredItems.OrderBy(orderingFunction); else filteredItems = filteredItems.OrderByDescending(orderingFunction); } var displayedItems = filteredItems.Skip(param.iDisplayStart).Take(param.iDisplayLength); var result = from c in displayedItems select new[] { Convert.ToString(c.ID), Convert.ToString(c.Name), getUOM(Convert.ToString(c.UOM)), Convert.ToString(c.Remarks), Convert.ToString(c.BarCode), Convert.ToString(c.WithSerial), Convert.ToString(c.Reorder), Convert.ToString(c.Code), getDescGroup(Convert.ToInt32(c.G230)), getDescGroup(Convert.ToInt32(c.G233)), getDescGroup(Convert.ToInt32(c.G234)), Convert.ToString(c.Model) }; return Json(new { sEcho = param.sEcho, iTotalRecords = allItems.Count(), iTotalDisplayRecords = filteredItems.Count(), aaData = result }, JsonRequestBehavior.AllowGet); }
public ActionResult AjaxDataProvider(int id, JQueryDataTableParamModel param) { var allSGMs = from a in db.SGMs .Where(c => c.SID.ToString().Equals(id)) select a; IEnumerable<SGM> filteredSGMs; if (!string.IsNullOrEmpty(param.sSearch)) { //Used if particulare columns are filtered var UgcFilter = Convert.ToString(Request["sSearch_2"]); var DescFilter = Convert.ToString(Request["sSearch_3"]); var AbbrevFilter = Convert.ToString(Request["sSearch_4"]); var ValueFilter = Convert.ToString(Request["sSearch_5"]); var SccFilter = Convert.ToString(Request["sSearch_6"]); var SeqFilter = Convert.ToString(Request["sSearch_7"]); //Optionally check whether the columns are searchable at all var isUgcSearchable = Convert.ToBoolean(Request["bSearchable_2"]); var isDescSearchable = Convert.ToBoolean(Request["bSearchable_3"]); var isAbbrevSearchable = Convert.ToBoolean(Request["bSearchable_4"]); var isValueSearchable = Convert.ToBoolean(Request["bSearchable_5"]); var isSccSearchable = Convert.ToBoolean(Request["bSearchable_6"]); var isSeqSearchable = Convert.ToBoolean(Request["bSearchable_7"]); filteredSGMs = from c in allSGMs .Where(c => isUgcSearchable && c.UGC.ToLower().Contains(param.sSearch.ToLower()) || isDescSearchable && c.Desc.ToString().ToLower().Contains(param.sSearch.ToLower()) || isAbbrevSearchable && c.Abbrev.ToLower().Contains(param.sSearch.ToLower()) || isValueSearchable && c.Val.ToString().ToLower().Contains(param.sSearch.ToLower()) || isSccSearchable && c.SCC.ToString().ToLower().Contains(param.sSearch.ToLower()) || isSeqSearchable && c.Seq.ToString().ToLower().Contains(param.sSearch.ToLower())) select c; } else { filteredSGMs = allSGMs; } var isUgcSortable = Convert.ToBoolean(Request["bSortable_2"]); //String var isDescSortable = Convert.ToBoolean(Request["bSortable_3"]); //String var isAbbrevSortable = Convert.ToBoolean(Request["bSortable_4"]);//String var isValSortable = Convert.ToBoolean(Request["bSortable_5"]);//int var isSccSortable = Convert.ToBoolean(Request["bSortable_6"]);//String var isSeqSortable = Convert.ToBoolean(Request["bSortable_7"]);//int var sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]); if (sortColumnIndex == 2 || sortColumnIndex == 3 || sortColumnIndex == 4 || sortColumnIndex == 6) { Func<SGM, string> orderingFunction = (c => sortColumnIndex == 2 && isUgcSortable ? c.UGC : sortColumnIndex == 3 && isDescSortable ? c.Desc : sortColumnIndex == 4 && isAbbrevSortable ? c.Abbrev : sortColumnIndex == 6 && isSccSortable ? c.SCC : ""); var sortDirection = Request["sSortDir_0"]; // asc or desc if (sortDirection == "asc") filteredSGMs = filteredSGMs.OrderBy(orderingFunction); else filteredSGMs = filteredSGMs.OrderByDescending(orderingFunction); } else // 2,5,6,8,9,10 { Func<SGM, int?> orderingFunction = (c => sortColumnIndex == 5 && isValSortable ? c.Val : sortColumnIndex == 7 && isSeqSortable ? c.Seq : 0); var sortDirection = Request["sSortDir_0"]; // asc or desc if (sortDirection == "asc") filteredSGMs = filteredSGMs.OrderBy(orderingFunction); else filteredSGMs = filteredSGMs.OrderByDescending(orderingFunction); } var displayedSGMs = filteredSGMs.Skip(param.iDisplayStart).Take(param.iDisplayLength); var result = from c in displayedSGMs select new[] { Convert.ToString(c.GID),Convert.ToString(c.SID), Convert.ToString(c.UGC),Convert.ToString(c.Desc), Convert.ToString(c.Abbrev), Convert.ToString(c.Val),Convert.ToString(c.SCC),Convert.ToString(c.Seq) }; return Json(new { sEcho = param.sEcho, iTotalRecords = allSGMs.Count(), iTotalDisplayRecords = filteredSGMs.Count(), aaData = result }, JsonRequestBehavior.AllowGet); }