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);
}