public async Task <PrintingEditionsModel> GetAllAsync(PrintingEditionsFilterModel model)
{
var response = new PrintingEditionsModel();
if (model == null)
{
response.Errors.Add(ErrorConstants.ModelIsNull);
}
var filterModel = FilterMapper.MapPrintingEditionsFilteringModel(model);
var printingEditions = await _authorInPrintingEditionsRepository.GetFilteredPrintingEditionsAsync(filterModel);
if (printingEditions == null)
{
response.Errors.Add(ErrorConstants.ImpossibleToFindProduct);
return(response);
}
var products = new PrintingEditionsModel();
foreach (var printingEdition in printingEditions.Data)
{
var convertedPrice = _currencyConverter.Convert(CurrencyEnum.Currency.USD, model.Currency, printingEdition.PrintingEdition.Price);
var printingEditionItemModel = PrintingEditionMapper.MapProductResponseModelToModelItem(printingEdition, model.Currency, convertedPrice);
products.Items.Add(printingEditionItemModel);
}
products.TotalCount = printingEditions.TotalItemsCount;
return(products);
}
public async Task <PrintingEditionModel> GetPrintingEditionsAsync(PrintingEditionsFilterModel printingEditionsFilterModels)
{
var printingEditions = await _printingEditionRepository.GetPrintingEditionsAsync(printingEditionsFilterModels);
var resultModel = new PrintingEditionModel();
foreach (var printingEdition in printingEditions)
{
resultModel.Items.Add(printingEdition.Mapping());
}
return(resultModel);
}
public async Task <IEnumerable <PrintingEditionModelItem> > GetPrintingEditionsAsync(PrintingEditionsFilterModel printingEditionsFilterModel)
{
/*var printingEditions = (from a in _applicationContext.AuthorInPrintingEditions
* join p in _applicationContext.PrintingEditions on a.PrintingEditionId equals p.Id
* join aut in _applicationContext.Authors on a.AuthorId equals aut.Id
* select new {
* PrintingEditionId = p.Id,
* Title = p.Title,
* Price = p.Price,
* Type = p.Type,
* Name = aut.Name
* }).ToArray();*/
var printingEditions = from printingEdition in _applicationContext.PrintingEditions
select new PrintingEditionModelItem
{
Id = printingEdition.Id,
Title = printingEdition.Title,
Price = printingEdition.Price,
Description = printingEdition.Description,
Type = printingEdition.Type,
AuthorsNames = (from authorInPrintingEdition in _applicationContext.AuthorInPrintingEditions
join author in _applicationContext.Authors on authorInPrintingEdition.AuthorId equals author.Id
where (authorInPrintingEdition.PrintingEditionId == printingEdition.Id)
select author.Name).ToArray()
};
printingEditions = await OrderBy(printingEditions, printingEditionsFilterModel.SortBy, printingEditionsFilterModel.SortingDirection == SortingDirection.LowToHigh);
printingEditions = printingEditions.Where(p => p.Price >= printingEditionsFilterModel.MinPrice && p.Price <= printingEditionsFilterModel.MaxPrice);
printingEditions = printingEditions.Skip((printingEditionsFilterModel.PageCount - 1) * printingEditionsFilterModel.PageSize).Take(printingEditionsFilterModel.PageSize);
return(printingEditions);
}
public async Task <ResponseModel <List <PrintingEditionsResponseModel> > > GetFilteredPrintingEditionsAsync(PrintingEditionsFilterModel model)
{
var productTypeEnum = Enum.GetValues(typeof(ProductType)).OfType <ProductType>().ToList();
var currentProductType = productTypeEnum.Where(x => !model.ProductType.Contains(x));
var printingEditions = _dbContext.AuthorInPrintingEditions
.Include(x => x.Author)
.Include(x => x.PrintingEdition)
.Where(x => !x.IsRemoved)
.AsQueryable();
if (!string.IsNullOrWhiteSpace(model.SearchString))
{
printingEditions = printingEditions.Where(x => x.PrintingEdition.Title.Contains(model.SearchString) ||
x.PrintingEdition.Description.Contains(model.SearchString) || x.Author.Name.Contains(model.SearchString));
}
if (model.MaxPrice != 0 || model.MinPrice != 0)
{
printingEditions = printingEditions.Where(x => x.PrintingEdition.Price >= model.MinPrice &&
x.PrintingEdition.Price <= model.MaxPrice);
}
foreach (var type in currentProductType)
{
printingEditions = printingEditions.Where(x => x.PrintingEdition.Type != type);
}
var orderedProducts = printingEditions.AsEnumerable().SortBySubProperty(nameof(PrintingEdition), model.FilteredColumnType.ToString(), model.SortType);
var products = orderedProducts
.GroupBy(x => x.PrintingEdition.Id)
.Select(group =>
new PrintingEditionsResponseModel
{
Authors = group.Select(x => x.Author).ToList(),
PrintingEdition = group.Select(x => x.PrintingEdition).FirstOrDefault()
}).Skip((model.PageCount - 1) * model.PageSize).Take(model.PageSize).ToList();
var count = printingEditions.Select(x => x.PrintingEdition.Id).Distinct().Count();
var result = new ResponseModel <List <PrintingEditionsResponseModel> >(products, count);
return(result);
}
public async Task <IActionResult> GetAllAsync(PrintingEditionsFilterModel model)
{
var result = await _printingEditionService.GetAllAsync(model);
return(Ok(result));
}
public async Task <IActionResult> Test(PrintingEditionsFilterModel printingEditionsFilterModels)
{
var printingEditionModel = await _printingEditorService.GetPrintingEditionsAsync(printingEditionsFilterModels);
return(Ok(printingEditionModel));
}
public async Task <ResponseModel <List <PrintingEditionsResponseModel> > > GetFilteredPrintingEditionsAsync(PrintingEditionsFilterModel model)
{
var productTypes = model.ProductType.Select(x => x.GetHashCode()).ToArray();
var sqlQuery = new StringBuilder($@"SELECT DISTINCT printingEdition.Id, printingEdition.Title, printingEdition.Type, printingEdition.Description,
printingEdition.Price, author.AuthorName FROM (
SELECT DISTINCT PrintingEditions.Id, PrintingEditions.Description, PrintingEditions.Price, PrintingEditions.Title, PrintingEditions.Type " );
var productFilterQuery = new StringBuilder(@"
FROM PrintingEditions
LEFT JOIN AuthorInPrintingEditions ON PrintingEditionId = PrintingEditions.Id
INNER JOIN Authors ON AuthorId = Authors.Id
WHERE PrintingEditions.IsRemoved = 0 AND AuthorInPrintingEditions.IsRemoved = 0 " );
if (!string.IsNullOrWhiteSpace(model.SearchString))
{
productFilterQuery.Append($"AND (PrintingEditions.Title LIKE '%{model.SearchString}%' OR Authors.Name LIKE '%{model.SearchString}%') ");
}
productFilterQuery.Append("AND PrintingEditions.Type in (");
foreach (var item in productTypes)
{
productFilterQuery.Append($"{item},");
}
productFilterQuery.Remove(productFilterQuery.Length - 1, 1);
productFilterQuery.Append(") ");
if (model.MinPrice != 0 || model.MaxPrice != 0)
{
productFilterQuery.Append("AND PrintingEditions.Price BETWEEN @minPrice AND @maxPrice ");
}
sqlQuery.Append(productFilterQuery);
sqlQuery.Append($@"ORDER BY PrintingEditions.{model.FilteredColumnType} ");
if (model.SortType == Common.Enums.Filter.Enums.AscendingDescendingSort.Descending)
{
sqlQuery.Append("DESC ");
}
sqlQuery.Append(@"OFFSET (@pageCount -1) * @pageSize ROWS FETCH NEXT @pageSize ROWS ONLY ) AS printingEdition
INNER JOIN (
SELECT Authors.Name AS AuthorName, PrintingEditionId
FROM Authors
INNER JOIN AuthorInPrintingEditions ON AuthorId = Authors.Id
WHERE AuthorInPrintingEditions.IsRemoved = 0
) AS author ON printingEdition.Id = PrintingEditionId ");
sqlQuery.Append($@"ORDER BY printingEdition.{model.FilteredColumnType} ");
if (model.SortType == Common.Enums.Filter.Enums.AscendingDescendingSort.Descending)
{
sqlQuery.Append("DESC ");
}
sqlQuery.Append("; ");
sqlQuery.Append("SELECT COUNT(DISTINCT PrintingEditions.Id) ");
sqlQuery.Append(productFilterQuery);
var q = sqlQuery.ToString();
using (var connection = new SqlConnection(_connectionString))
{
using (var multi = await connection.QueryMultipleAsync(sqlQuery.ToString(), new
{
minPrice = model.MinPrice,
maxPrice = model.MaxPrice,
pageCount = model.PageCount,
pageSize = model.PageSize
}))
{
var dapperResponse = await multi.ReadAsync <Models.DapperResponse.PrintingEditionsResponseModel>();
var count = await multi.ReadFirstAsync <int>();
var grouppingModel = dapperResponse.GroupBy(x => x.Id).ToList();
var responseModel = CreateResponseModel(grouppingModel);
var result = new ResponseModel <List <PrintingEditionsResponseModel> >(responseModel, count);
return(result);
}
}
}