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