public PWMsModel GetPWM(PWMsFilter filter) { string searchTextQuery = ""; string subquery = ""; string filterQuery = ""; string CountTextQuery = ""; if (!string.IsNullOrWhiteSpace(filter.SearchText)) { searchTextQuery = " P.ProductName like '%" + filter.SearchText + "%' or P.Category like '%" + filter.SearchText + "%' or P.SubCategory like '%" + filter.SearchText + "%' or c.Quantity like '%" + filter.SearchText + "%' or W.WarehouseName like '%" + filter.SearchText + "%' and "; CountTextQuery = " where P.ProductName like '%" + filter.SearchText + "%' or P.Category like '%" + filter.SearchText + "%' or P.SubCategory like '%" + filter.SearchText + "%' or c.Quantity like '%" + filter.SearchText + "%' or W.WarehouseName like '%" + filter.SearchText + "%' "; } string rawQuery = @" declare @pagesize int declare @pageno int set @pagesize = " + filter.UnitPerPage + @" set @pageno = " + filter.PageNumber + @" declare @pagestart int set @pagestart=(@pageno-1)* @pagesize select TOP (@pagesize) c.*, P.*, W.* FROM ProductWarehouseMaps c inner join Products P on P.ProductId=C.productId inner join Warehouses W on W.WarehouseId=C.WarehouseId where {1}{2} c.Id NOT IN(Select TOP (@pagestart) Id from ProductWarehouseMaps {0}) {0} "; string CountQuery = string.Format("Select * from ProductWarehouseMaps c {0}", CountTextQuery); rawQuery = string.Format(rawQuery, subquery, searchTextQuery, filterQuery); int TotalCount = 0; List <PWMvm> dsResult = new List <PWMvm>(); try { var ctx = DataContext.getInstance(); dsResult = context.Database.SqlQuery <PWMvm>(rawQuery, new object[] { }).ToList <PWMvm>(); TotalCount = ctx.ProductWarehouseMap.SqlQuery(CountQuery).ToList().Count; } catch (Exception ex) { } PWMsModel pwmsModel = new PWMsModel(); pwmsModel.PWMList = dsResult; //context.Dispose(); pwmsModel.TotalCount = TotalCount; return(pwmsModel); }
public ActionResult LoadPWMList(PWMsFilter filter) { if (filter.WarehouseId == "-1" || filter.WarehouseId == null) { filter.WarehouseId = ""; } if (filter.PageNumber == 0) { filter.PageNumber = 1; } filter.UnitPerPage = 12; if (filter.PageNumber == null || filter.PageNumber == 0) { filter.PageNumber = 1; } PWMsModel pwmsList = productsFacade.GetPWM(filter); ViewBag.OutOfNumber = pwmsList.TotalCount; if ((int)ViewBag.OutOfNumber == 0) { ViewBag.Message = "No Content Available !"; } if (@ViewBag.OutOfNumber == 0) { filter.PageNumber = 1; } ViewBag.PageNumber = filter.PageNumber; if ((int)ViewBag.PageNumber * filter.UnitPerPage > (int)ViewBag.OutOfNumber) { ViewBag.CurrentNumber = (int)ViewBag.OutOfNumber; } else { ViewBag.CurrentNumber = (int)ViewBag.PageNumber * filter.UnitPerPage; } ViewBag.PageCount = Math.Ceiling((double)ViewBag.OutOfNumber / filter.UnitPerPage.Value); pwmsList.PWMList = pwmsList.PWMList.Where(x => x.WarehouseId.ToString().Contains(filter.WarehouseId)).ToList(); return(View(pwmsList.PWMList)); }
public PWMsModel GetPWM(PWMsFilter filter) { return(productRepository.GetPWM(filter)); }