public JsonResult AjaxGetJsonProductData(int draw, int start, int length, FilterArgs Fvm) { string search = Request.Form["search[value]"]; int sortColumn = -1; string sortDirection = "asc"; string SortColName = ""; if (length == -1) { length = TOTAL_ROWS; } // note: we only sort one column at a time if (Request.Form["order[0][column]"] != null) { sortColumn = int.Parse(Request.Form["order[0][column]"]); SortColName = Request.Form["columns[" + sortColumn + "][data]"]; } if (Request.Form["order[0][dir]"] != null) { sortDirection = Request.Form["order[0][dir]"]; } DataTableProductData dataTableData = new DataTableProductData(); dataTableData.draw = draw; int recordsFiltered = 0; dataTableData.data = FilterProductData(ref recordsFiltered, ref TOTAL_ROWS, start, length, search, sortColumn, sortDirection, Fvm); dataTableData.recordsTotal = TOTAL_ROWS; dataTableData.recordsFiltered = recordsFiltered; return(Json(dataTableData, JsonRequestBehavior.AllowGet)); }
public ActionResult Filters(FilterArgs Fvm) { List <SelectListItem> temp = new List <SelectListItem>(); temp.Add(new SelectListItem { Text = "Pending", Value = "Pending" }); temp.Add(new SelectListItem { Text = "All", Value = "All" }); ViewBag.WizType = new SelectList(temp, "Value", "Text", Fvm.WizardType); List <SelectListItem> tempSOD = new List <SelectListItem>(); tempSOD.Add(new SelectListItem { Text = "All", Value = "All" }); ViewBag.IncSample = new SelectList(tempSOD, "Value", "Text", Fvm.SOD); return(PartialView("_Filters", Fvm)); }
// here we simulate SQL search, sorting and paging operations private List <ProductRateIndex> FilterProductData(ref int recordFiltered, ref int recordTotal, int start, int length, string search, int sortColumn, string sortDirection, FilterArgs Fvm) { bool Pending = false; bool Sample = false; if (string.IsNullOrEmpty(Fvm.WizardType) || Fvm.WizardType == "Pending") { Pending = true; } List <ProductRateIndex> list = new List <ProductRateIndex>(); IQueryable <ProductRateIndex> _data = (from Pp in db.ProductProcess join Rh in db.RateListHeader on Pp.ProcessId equals Rh.ProcessId into RateListHeaderTable from RateListHeaderTab in RateListHeaderTable.DefaultIfEmpty() join P in db.Product on Pp.ProductId equals P.ProductId into ProductTable from ProductTab in ProductTable.DefaultIfEmpty() join D1 in db.Dimension1 on Pp.Dimension1Id equals D1.Dimension1Id into Dimension1Table from Dimension1Tab in Dimension1Table.DefaultIfEmpty() join D2 in db.Dimension2 on Pp.Dimension2Id equals D2.Dimension2Id into Dimension2Table from Dimension2Tab in Dimension2Table.DefaultIfEmpty() where RateListHeaderTab.RateListHeaderId == Fvm.RateListHeaderId && (Pending ? Pp.ProductRateGroupId == null : 1 == 1) && RateListHeaderTab.DivisionId == ProductTab.DivisionId && ProductTab.IsActive == true group new { Pp, ProductTab, Dimension1Tab, Dimension2Tab } by new { ProductTab.ProductId, Dimension1Tab.Dimension1Id, Dimension2Tab.Dimension2Id } into g select new ProductRateIndex { ProductId = g.Key.ProductId, ProductName = g.Max(m => m.ProductTab.ProductName), Dimension1Id = g.Key.Dimension1Id, Dimension1Name = g.Max(m => m.Dimension1Tab.Dimension1Name), Dimension2Id = g.Key.Dimension2Id, Dimension2Name = g.Max(m => m.Dimension2Tab.Dimension2Name), ImageFileName = g.Max(m => m.ProductTab.ImageFileName), ImageFolderName = g.Max(m => m.ProductTab.ImageFolderName), ProductRateGroupId = g.Max(m => m.Pp.ProductRateGroupId), }); recordTotal = _data.Count(); if (string.IsNullOrEmpty(search)) { } else { // simulate search _data = from m in _data where (m.ProductName).ToLower().Contains(search.ToLower()) || (m.Dimension1Name).ToLower().Contains(search.ToLower()) || (m.Dimension2Name).ToLower().Contains(search.ToLower()) select m; } _data = _data.OrderBy(m => m.ProductName); recordFiltered = _data.Count(); // get just one page of data list = _data.Select(m => new ProductRateIndex { ProductId = m.ProductId, ProductName = m.ProductName, Dimension1Id = m.Dimension1Id, Dimension1Name = m.Dimension1Name, Dimension2Id = m.Dimension2Id, Dimension2Name = m.Dimension2Name, ImageFileName = m.ImageFileName, ImageFolderName = m.ImageFolderName, ProductRateGroupId = m.ProductRateGroupId, }) .Skip(start).Take((start == 0) ? 90 : length).ToList(); return(list); }
// here we simulate SQL search, sorting and paging operations private List <ProductViewModel> FilterProductData(ref int recordFiltered, ref int recordTotal, int start, int length, string search, int sortColumn, string sortDirection, FilterArgs Fvm) { bool Pending = false; bool Sample = false; bool All = false; if (string.IsNullOrEmpty(Fvm.WizardType) || Fvm.WizardType == "Pending") { Pending = true; } if (!string.IsNullOrEmpty(Fvm.SOD) && Fvm.SOD == "Sample") { Sample = true; } else if (!string.IsNullOrEmpty(Fvm.SOD) && Fvm.SOD == "Design") { Sample = false; } else if (!string.IsNullOrEmpty(Fvm.SOD) && Fvm.SOD == "All") { All = true; } string[] Collections = null; if (!string.IsNullOrEmpty(Fvm.ProductCollection)) { Collections = Fvm.ProductCollection.Split(",".ToCharArray()); } else { Collections = new string[] { "NA" }; } string[] Category = null; if (!string.IsNullOrEmpty(Fvm.ProductCategory)) { Category = Fvm.ProductCategory.Split(",".ToCharArray()); } else { Category = new string[] { "NA" }; } List <ProductViewModel> list = new List <ProductViewModel>(); IQueryable <ProductViewModel> _data = (from p in db.ProductProcess join rh in db.RateListHeader on p.ProcessId equals rh.ProcessId join fp in db.FinishedProduct on p.ProductId equals fp.ProductId join pcol in db.ProductCollections on fp.ProductCollectionId equals pcol.ProductCollectionId join pcat in db.ProductCategory on fp.ProductCategoryId equals pcat.ProductCategoryId join pd in db.ProductDesigns on fp.ProductDesignId equals pd.ProductDesignId join rll in db.RateListLine on new { x = p.ProductId, y = rh.RateListHeaderId } equals new { x = rll.ProductId ?? 0, y = rll.RateListHeaderId } into rlltable from rlltab in rlltable.DefaultIfEmpty() where rh.RateListHeaderId == Fvm.RateListHeaderId && (Pending ? rlltab == null : 1 == 1) && (All ? 1 == 1 : fp.IsSample == Sample) && (string.IsNullOrEmpty(Fvm.ProductCollection) ? 1 == 1 : Collections.Contains(fp.ProductCollectionId.ToString())) && (string.IsNullOrEmpty(Fvm.ProductCategory) ? 1 == 1 : Category.Contains(fp.ProductCategoryId.ToString())) && rh.DivisionId == fp.DivisionId && fp.IsActive == true && (Fvm.DisContinued == "All" ? 1 == 1 : fp.DiscontinuedDate == null) group new { fp, rlltab, pcol, pcat, pd } by fp.ProductId into g select new ProductViewModel { ProductId = g.Key, ProductName = g.Max(m => m.fp.ProductName), ProductDesignName = g.Max(m => m.pd.ProductDesignName), ImageFileName = g.Max(m => m.fp.ImageFileName), ImageFolderName = g.Max(m => m.fp.ImageFolderName), SampleName = g.Max(m => m.fp.IsSample.ToString() == "True" ? "Sample" : "Design"), Rate = g.Max(m => m.rlltab.Rate), ProductCollectionName = g.Max(m => m.pcol.ProductCollectionName), ProductCategoryName = g.Max(m => m.pcat.ProductCategoryName), }); recordTotal = _data.Count(); if (string.IsNullOrEmpty(search)) { } else { // simulate search _data = from m in _data where (m.ProductName).ToLower().Contains(search.ToLower()) || (m.ProductDesignName).ToLower().Contains(search.ToLower()) || (m.SampleName).ToLower().Contains(search.ToLower()) || (m.ProductCollectionName).ToLower().Contains(search.ToLower()) || (m.ProductCategoryName).ToLower().Contains(search.ToLower()) select m; } _data = _data.OrderBy(m => m.ProductName); recordFiltered = _data.Count(); // get just one page of data list = _data.Select(m => new ProductViewModel { ProductId = m.ProductId, ProductName = m.ProductName, ProductDesignName = m.ProductDesignName, ImageFileName = m.ImageFileName, ImageFolderName = m.ImageFolderName, SampleName = m.SampleName, Rate = m.Rate, ProductCollectionName = m.ProductCollectionName, ProductCategoryName = m.ProductCategoryName, }) .Skip(start).Take((start == 0) ? 90 : length).ToList(); return(list); }