public ActionResult Filter(ProductFilterVM model) { ViewBag.Filterable = true; ViewBag.FilterProduct = productRepo.GetList(x => x.Stock <= model.MaxStock && x.Stock >= model.MinStock && x.UnitPrice <= model.MaxPrice && x.UnitPrice >= model.MinPrice); return(View()); }
public static ICollection <ProductInStockVM> GetProductsInStockFilter( ShopContext db, PostgresContext postgresContext, ProductFilterVM filter) { var bookingProductsAmount = db.BookingProducts .Where(x => x.Booking.Status == BookingStatus.Open) .Where(x => filter.CategoryId == 0 || x.Product.Category.Id == filter.CategoryId) .Where(x => filter.ShopId == 0 || x.Product.Shop.Id == filter.ShopId) .Select(x => new { ProductId = x.ProductId, Amount = x.Amount }).ToList(); var incompleteProducts = postgresContext.IncompleteProducts .Select(x => new { ProductId = x.ProductId, Amount = x.Amount }).ToList() .GroupBy(x => x.ProductId) .Select(x => new { ProductId = x.Key, Amount = x.Sum(z => z.Amount) }) .ToList(); var productsInStock = db.SupplyProducts .Where(x => x.StockAmount > 0) .Where(x => filter.CategoryId == 0 || x.Product.Category.Id == filter.CategoryId) .Where(x => filter.ShopId == 0 || x.Product.Shop.Id == filter.ShopId) .Select(x => new { ProductId = x.ProductId, Amount = x.StockAmount }).ToList(); var result = db.Products .Select(x => new ProductInStockVM() { Id = x.Id, Title = x.Title, Cost = x.Cost, Shop = x.Shop, Category = x.Category, Code = x.Code, }) .Where(x => filter.Title == null || x.Title.Contains(filter.Title)) .Where(x => productsInStock.Select(z => z.ProductId).Contains(x.Id) || bookingProductsAmount.Select(z => z.ProductId).Contains(x.Id)) .Where(x => filter.CategoryId == 0 || x.Category.Id == filter.CategoryId) .Where(x => filter.ShopId == 0 || x.Shop.Id == filter.ShopId) .ToList() .Where(x => productsInStock.Where(s => s.ProductId == x.Id) .Sum(s => s.Amount) > 0) .OrderBy(x => x.Title) .Select(x => new ProductInStockVM() { Id = x.Id, Title = x.Title, Amount = productsInStock.Where(s => s.ProductId == x.Id) .Sum(s => s.Amount) - bookingProductsAmount .Where(z => z.ProductId == x.Id) .Sum(z => z.Amount), Cost = x.Cost, Shop = x.Shop, Category = x.Category, Code = x.Code, BookedCount = bookingProductsAmount .Where(z => z.ProductId == x.Id) .Sum(z => z.Amount), IncompleteCount = incompleteProducts .Where(z => z.ProductId == x.Id) .Sum(z => z.Amount) }).ToList(); return(result); }