/// <summary> /// Gets a page of product with applied filtering. The filtering occurs on the server side. /// </summary> /// <param name="parameters">Filter parameters</param> /// <returns>Page of products</returns> public AlzaAdminDTO <QueryResultWrapper> GetPage(QueryParametersWrapper parameters) { try { //get all children of the specified category var childCategoriesId = _categoryRelationshipRepository.GetAllRelationships().Where(c => c.Id == parameters.CategoryId).Select(c => c.ChildId); //get all products IQueryable <ProductBase> query = _productRepository.GetAllProducts(); //return only products which belong in these categories query = query.Where(p => childCategoriesId.Contains(p.CategoryId)); decimal minPrice = decimal.MaxValue; decimal maxPrice = 0; QueryResultWrapper result = new QueryResultWrapper(); HashSet <Language> languages = new HashSet <Language>(); HashSet <Publisher> publishers = new HashSet <Publisher>(); HashSet <Format> formats = new HashSet <Format>(); HashSet <Author> authors = new HashSet <Author>(); //specifying filter options correspondingly foreach (ProductBase product in query) { minPrice = product.Price < minPrice ? product.Price : minPrice; maxPrice = product.Price > maxPrice ? product.Price : maxPrice; languages.Add(product.Language); publishers.Add(product.Publisher); formats.Add(product.Format); foreach (Author author in product.Book.AuthorsBooks.Select(ab => ab.Author)) { authors.Add(author); } } result.MinPrice = minPrice; result.MaxPrice = maxPrice; result.Authors = authors.OrderBy(a => a.Surname).ToList(); result.Languages = languages.OrderBy(l => l.Name).ToList(); result.Publishers = publishers.OrderBy(p => p.Name).ToList(); result.Formats = formats.OrderBy(f => f.Name).ToList(); //filtering if (parameters.MinPrice != null) { query = query.Where(p => p.Price >= parameters.MinPrice); } if (parameters.MaxPrice != null) { query = query.Where(p => p.Price <= parameters.MaxPrice); } if (parameters.Languages != null) { query = query.Where(p => parameters.Languages.Contains(p.LanguageId)); } if (parameters.Publishers != null) { query = query.Where(p => parameters.Publishers.Contains(p.PublisherId)); } if (parameters.Formats != null) { query = query.Where(p => parameters.Formats.Contains(p.FormatId)); } if (parameters.Authors != null) { query = query.Where(p => p.Book.AuthorsBooks.Select(ab => ab.Author).Select(a => a.AuthorId).Intersect(parameters.Authors).Count() > 0); } //Add average ratings List <int> pIds = query.Select(p => p.Id).ToList(); IQueryable <ProductRating> ratings = _productRatingRepository.GetRatings().Where(pr => pIds.Contains(pr.ProductId)); var products = query.Join(ratings, q => q.Id, r => r.ProductId, (p, r) => new { product = p, rating = r }).Select(x => new ProductBO(x.product, x.rating, null)); //sort Func <ProductBO, IComparable> sortingParameter; switch (parameters.SortingParameter) { case Enums.SortingParameter.Price: sortingParameter = p => p.Price; break; case Enums.SortingParameter.Rating: sortingParameter = p => p.AverageRating; break; case Enums.SortingParameter.Date: sortingParameter = p => p.DateAdded; break; case Enums.SortingParameter.Name: sortingParameter = p => p.Name; break; default: sortingParameter = p => p.AverageRating; break; } switch (parameters.SortingType) { case Enums.SortType.Asc: products = products.OrderBy(sortingParameter).AsQueryable(); break; case Enums.SortType.Desc: products = products.OrderByDescending(sortingParameter).AsQueryable(); break; default: break; } //return a "page" result.ResultCount = products.Count(); products = products.Skip((parameters.PageNum - 1) * parameters.PageSize).Take(parameters.PageSize); result.Products = products.ToList(); return(AlzaAdminDTO <QueryResultWrapper> .Data(result)); } catch (Exception e) { return(AlzaAdminDTO <QueryResultWrapper> .Error(e.Message + Environment.NewLine + e.StackTrace)); } }
/// <summary> /// Using provided QueryParametersWrapper filters products and gets all related infromation /// </summary> /// <param name="parameters">Wrapper that contains all parameters for filtering and ordering</param> /// <returns>Filtered products and related information</returns> public QueryResultWrapper FilterProducts(QueryParametersWrapper parameters) { Dictionary <int, Author> authors = new Dictionary <int, Author>(); Dictionary <int, Book> books = new Dictionary <int, Book>(); Dictionary <int, Language> languages = new Dictionary <int, Language>(); Dictionary <int, Format> formats = new Dictionary <int, Format>(); Dictionary <int, ProductState> productStates = new Dictionary <int, ProductState>(); Dictionary <int, Category> categories = new Dictionary <int, Category>(); Dictionary <int, Publisher> publishers = new Dictionary <int, Publisher>(); // {O,null} is a country that is assigned to authors without set country, it is removed before conversion to List Dictionary <int, Country> countries = new Dictionary <int, Country> { { 0, null } }; decimal maxPrice = 0; decimal minPrice = decimal.MaxValue; //Preparing query that will select all products from selected category and its child categories ignoring all filters string queryStringAll = $"SELECT * FROM dbo.ProductFilter({parameters.CategoryId},NULL,NULL,NULL,NULL,NULL,NULL) ORDER BY ProductId ASC"; using (var conn = new SqlConnection(_connectionString)) { var command = new SqlCommand(queryStringAll, conn); conn.Open(); using (var reader = command.ExecuteReader()) { int previousProductId = 0; Book book = null; //Adding all entities that filtered pruducts might consist of into their dictionaries while (reader.Read()) { int id = (int)reader["ProductId"]; if (book != null && previousProductId != id) { books.Add(book.BookId, book); book = null; } previousProductId = id; int categoryId = (int)reader["CategoryId"]; if (!categories.ContainsKey(categoryId)) { categories.Add(categoryId, new Category() { Id = categoryId, Name = (string)reader["CategoryName"] }); } int countryId = reader["AuthorCountryId"] != DBNull.Value ? (int)reader["AuthorCountryId"] : 0; if (!countries.ContainsKey(countryId)) { countries.Add(countryId, new Country() { Name = (string)reader["AuthorCountryName"], Id = (int)countryId, CountryCode = (string)reader["AuthorCountryCode"] }); } int formatId = (int)reader["FormatId"]; if (!formats.ContainsKey(formatId)) { formats.Add(formatId, new Format() { Id = formatId, Name = (string)reader["FormatName"] }); } int stateId = (int)reader["ProductStateId"]; if (!productStates.ContainsKey(stateId)) { productStates.Add(stateId, new ProductState() { Id = stateId, Name = (string)reader["ProductStateName"] }); } int authorId = (int)reader["AuthorId"]; if (!authors.ContainsKey(authorId)) { authors.Add(authorId, new Author() { AuthorId = authorId, CountryId = countryId, Country = countries[countryId], Name = (string)reader["AuthorName"], Surname = (string)reader["AuthorSurname"] }); } ; int bookId = (int)reader["BookId"]; if (book == null && !books.ContainsKey(bookId)) { book = new Book() { BookId = bookId, Name = (string)reader["BookName"], Annotation = (string)reader["BookAnnotation"], AuthorsBooks = new List <AuthorBook>() }; } if (!books.ContainsKey(bookId)) { book.AuthorsBooks.Add(new AuthorBook() { Book = book, BookId = bookId, AuthorId = authorId, Author = authors[authorId] }); } int languageId = (int)reader["LanguageId"]; if (!languages.ContainsKey(languageId)) { languages.Add(languageId, new Language() { Id = languageId, Name = (string)reader["LanguageName"] }); } int publisherId = (int)reader["PublisherId"]; if (!publishers.ContainsKey(publisherId)) { publishers.Add(publisherId, new Publisher() { Id = publisherId, Name = (string)reader["PublisherName"] }); } decimal price = (decimal)reader["Price"]; minPrice = minPrice < price ? minPrice : price; maxPrice = maxPrice > price ? maxPrice : price; } if (book != null) { //Adding last book into the dictionary books.Add(book.BookId, book); } } } List <ProductBO> resultProducts = new List <ProductBO>(); string orderParameter; //Choosing ordering type switch (parameters.SortingParameter) { case Business.Enums.SortingParameter.Date: orderParameter = "DateAdded"; break; case Business.Enums.SortingParameter.Price: orderParameter = "Price"; break; case Business.Enums.SortingParameter.Rating: orderParameter = "AverageRating"; break; case Business.Enums.SortingParameter.Name: orderParameter = "ProductName"; break; default: orderParameter = "AverageRating"; break; } string orderType; switch (parameters.SortingType) { case Business.Enums.SortType.Asc: orderType = "ASC"; break; case Business.Enums.SortType.Desc: orderType = "DESC"; break; default: orderType = "DESC"; break; } //Preparing query for filter string queryString = $"SELECT * FROM [dbo].[ProductFilterLite] (@category, @languages, @formats, @publishers , @authors , @minPrice, @maxPrice) ORDER BY {orderParameter} {orderType};"; using (var conn = new SqlConnection(_connectionString)) { var command = new SqlCommand(queryString, conn); command.Parameters.AddWithValue("@category", parameters.CategoryId); command.Parameters.AddWithValue("@languages", parameters.Languages != null ? (object)string.Join(",", parameters.Languages) : DBNull.Value); command.Parameters.AddWithValue("@formats", parameters.Formats != null ? (object)string.Join(",", parameters.Formats) : DBNull.Value); command.Parameters.AddWithValue("@publishers", parameters.Publishers != null ? (object)string.Join(",", parameters.Publishers) : DBNull.Value); command.Parameters.AddWithValue("@authors", parameters.Authors != null ? (object)string.Join(",", parameters.Authors) : DBNull.Value); command.Parameters.AddWithValue("@minPrice", parameters.MinPrice != null ? (object)parameters.MinPrice : DBNull.Value); command.Parameters.AddWithValue("@maxPrice", parameters.MaxPrice != null ? (object)parameters.MaxPrice : DBNull.Value); conn.Open(); using (var reader = command.ExecuteReader()) { ProductBO currentProduct = null; //read all products while (reader.Read()) { //Has to be reviewed if column names change by chance int id = (int)reader["ProductId"]; if (currentProduct != null) { resultProducts.Add(currentProduct); } //Getting columns from DB int bookId = (int)reader["BookId"]; string productName = (string)reader["ProductName"]; string text = reader["Text"] != DBNull.Value ? (string)reader["Text"] : null; int categoryId = (int)reader["CategoryId"]; int publisherId = (int)reader["PublisherId"]; int languageId = (int)reader["LanguageId"]; int stateId = (int)reader["ProductStateId"]; int formatId = (int)reader["FormatId"]; string ean = reader["EAN"] != DBNull.Value ? (string)reader["EAN"] : null; string isbn = reader["ISBN"] != DBNull.Value ? (string)reader["ISBN"] : null; string picAddress = reader["PicAddress"] != DBNull.Value ? (string)reader["PicAddress"] : null; decimal price = (decimal)reader["Price"]; int? year = reader["Year"] != DBNull.Value ? (int?)reader["Year"] : null; int? pageCount = reader["PageCount"] != DBNull.Value ? (int?)reader["PageCount"] : null; decimal? avgRating = reader["AverageRating"] != DBNull.Value ? (decimal?)reader["AverageRating"] : null; DateTime dateAdded = (DateTime)reader["DateAdded"]; //creating the product using entities in dictionaries and provided ids currentProduct = new ProductBO() { Id = id, Name = productName, BookId = bookId, Book = books[bookId], Category = categories[categoryId], CategoryId = categoryId, DateAdded = dateAdded, EAN = ean, ISBN = isbn, Format = formats[formatId], FormatId = formatId, Language = languages[languageId], LanguageId = languageId, PageCount = pageCount, PicAddress = picAddress, Price = price, Publisher = publishers[publisherId], PublisherId = publisherId, Text = text, Year = year, State = productStates[stateId], StateId = stateId, AverageRating = avgRating }; } if (currentProduct != null) { resultProducts.Add(currentProduct); } conn.Close(); } } //removing dummy country with id 0 countries.Remove(0); var result = new QueryResultWrapper { Authors = new List <Author>(authors.Values), Publishers = new List <Publisher>(publishers.Values), Languages = new List <Language>(languages.Values), Formats = new List <Format>(formats.Values), ResultCount = resultProducts.Count, MaxPrice = maxPrice, MinPrice = minPrice, Products = resultProducts.AsQueryable() .Skip((parameters.PageNum - 1) * parameters.PageSize) .Take(parameters.PageSize) .ToList() }; return(result); }
public IActionResult Products(int?id, int?pageNum, ProductsViewModel model) { try { if (ModelState.IsValid) { //Default category is the first one (all items) if (id == null) { id = 1; } int catId = id.Value; model.currentCategory = _catalogueService.GetCategory(catId).data; //Unknown category id if (model.currentCategory == null) { return(RedirectToAction("Error", "Home")); } //First page is default if (model.PageNum == null) { model.PageNum = pageNum == null ? 1 : pageNum; } //creating the wrapper from filtering data QueryParametersWrapper parameters = new QueryParametersWrapper { PageNum = model.PageNum.Value, CategoryId = catId, MaxPrice = model.MaxPriceFilter, MinPrice = model.MinPriceFilter, PageSize = model.PageSize, SortingParameter = model.SortingParameter, }; //custom filtering if (model.SortingType == null) { switch (model.SortingParameter) { case SortingParameter.Date: case SortingParameter.Rating: parameters.SortingType = SortType.Desc; break; case SortingParameter.Name: case SortingParameter.Price: parameters.SortingType = SortType.Asc; break; default: parameters.SortingType = SortType.Asc; break; } } else { parameters.SortingType = model.SortingType.Value; } //making sure null is not passed into the service method parameters.Formats = model.FormatsFilter == null ? null : new List <int>() { model.FormatsFilter.Value }; parameters.Languages = model.LanguagesFilter == null ? null : new List <int>() { model.LanguagesFilter.Value }; parameters.Authors = model.AuthorsFilter == null ? null : new List <int>() { model.AuthorsFilter.Value }; parameters.Publishers = model.PublishersFilter == null ? null : new List <int>() { model.PublishersFilter.Value }; var dto = _businessService.GetPageADO(parameters); if (!dto.isOK) { return(RedirectToAction("Error", "Home")); } QueryResultWrapper result = dto.data; //Fill the ViewModel with new data model.MinPrice = result.MinPrice; model.MaxPrice = result.MaxPrice; model.Authors = result.Authors; model.Formats = result.Formats; model.Languages = result.Languages; model.Products = result.Products; model.Publishers = result.Publishers; model.ResultCount = result.ResultCount; model.MaxPriceFilter = model.MaxPriceFilter ?? model.MaxPrice; model.MinPriceFilter = model.MinPriceFilter ?? model.MinPrice; return(View(model)); } else { return(RedirectToAction("Error", "Home")); } } catch (Exception e) { return(AlzaError.ExceptionActionResult(e)); } }