public async Task <IActionResult> GetPrintingEditionsAsync(string role, [FromBody] FilterPrintingEditionModel filterModel)
{
var isAdmin = false;
if (!string.IsNullOrWhiteSpace(role) && role.Equals(Constants.Roles.Admin))
{
isAdmin = true;
}
if (string.IsNullOrWhiteSpace(role))
{
var claimRole = User.Claims.FirstOrDefault(x => x.Type.Equals(ClaimTypes.Role))?.Value;
isAdmin = claimRole.Equals(Constants.Roles.Admin) ? true : false;
}
var responseModel = await _printingEditionService.GetPrintingEditionsAsync(filterModel, isAdmin);
return(Ok(responseModel));
}
public async Task <PrintingEditionModel> GetPrintingEditionsAsync(FilterPrintingEditionModel filter, bool isAdmin)
{
var responseModel = new PrintingEditionModel();
var repositoryFilter = _mapperHelper.Map <FilterPrintingEditionModel, DataFilter.FilterPrintingEditionModel>(filter);
if (repositoryFilter == null)
{
responseModel.Errors.Add(Constants.Errors.OccuredProcessing);
return(responseModel);
}
if (!isAdmin)
{
repositoryFilter.PriceMinValue = _currencyConverterHelper.Convert(repositoryFilter.Currency, Enums.Currency.USD, repositoryFilter.PriceMinValue);
repositoryFilter.PriceMaxValue = _currencyConverterHelper.Convert(repositoryFilter.Currency, Enums.Currency.USD, repositoryFilter.PriceMaxValue);
}
var printingEditionsModel = await _printingEditionRepository.GetPrintingEditionFilteredDataAsync(repositoryFilter, isAdmin);
foreach (var printingEdition in printingEditionsModel.Collection)
{
var modelItem = printingEdition.MapToModel(filter.Currency);
if (!isAdmin)
{
modelItem.Price = _currencyConverterHelper.Convert(Enums.Currency.USD, filter.Currency, modelItem.Price);
}
responseModel.Items.Add(modelItem);
}
responseModel.ItemsCount = printingEditionsModel.CollectionCount;
return(responseModel);
}
public async Task <GenericModel <PrintingEditionDataModel> > GetPrintingEditionFilteredDataAsync(FilterPrintingEditionModel filter, bool isAdmin)
{
var queryPrintingEditions = _context.PrintingEditions
.Where(x => x.IsRemoved == false)
.Include(x => x.AuthorInPrintingEditions)
.ThenInclude(x => x.Author);
IQueryable <PrintingEdition> printingEditions = null;
if (filter.PrintingEditionTypes.Any())
{
printingEditions = queryPrintingEditions.Where(x => filter.PrintingEditionTypes.Contains(x.PrintingEditionType));
}
if (!string.IsNullOrWhiteSpace(filter.SearchString))
{
printingEditions = printingEditions
.Where(x => x.AuthorInPrintingEditions.Select(z => z.Author.Name.ToLower().StartsWith(filter.SearchString.ToLower())).FirstOrDefault() ||
x.Title.ToLower().StartsWith(filter.SearchString.ToLower()));
}
Expression <Func <PrintingEdition, object> > predicate = x => x.Id;
if (!isAdmin)
{
printingEditions = printingEditions.Where(x => x.Price >= filter.PriceMinValue && x.Price <= filter.PriceMaxValue);
predicate = x => x.Price;
}
if (filter.SortType.Equals(Enums.SortType.Title))
{
predicate = x => x.Title;
}
if (filter.SortType.Equals(Enums.SortType.Price))
{
predicate = x => x.Price;
}
var responseModel = new GenericModel <PrintingEditionDataModel>
{
CollectionCount = printingEditions.Count()
};
var printingEditionPage = await PaginationAsync(filter, predicate, printingEditions);
var result = printingEditionPage.Select(x => new PrintingEditionDataModel()
{
Id = x.Id,
Currency = x.Currency,
Price = x.Price,
PrintingEditionType = x.PrintingEditionType,
Title = x.Title,
Authors = x.AuthorInPrintingEditions
.Select(z => new AuthorDataModel()
{
Id = z.Author.Id,
Name = z.Author.Name
}).ToArray()
});
responseModel.Collection = result;
return(responseModel);
}
public async Task <GenericModel <PrintingEditionDataModel> > GetPrintingEditionFilteredDataAsync(FilterPrintingEditionModel filter, bool isAdmin)
{
var responseModel = new GenericModel <PrintingEditionDataModel>();
var priceSql = string.Empty;
var sort = string.Empty;
var offsetSql = string.Empty;
var searchSql = new StringBuilder();
if (!string.IsNullOrWhiteSpace(filter.SearchString))
{
searchSql.Append($@"AND (COALESCE((
SELECT TOP(1) CASE
WHEN (LOWER(aut.Name)) LIKE @SearchString+'%'
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END
FROM AuthorInPrintingEditions AS aPe
INNER JOIN Authors AS aut ON aut.Id = aPe.AuthorId
WHERE pe.Id = aPe.PrintingEditionId
),0) = 1) OR (LOWER(pe.Title) LIKE @SearchString+'%')" );
}
var sortTypeSql = "Id";
if (!isAdmin)
{
priceSql = $@"AND pe.Price BETWEEN @PriceMinValue AND @PriceMaxValue ";
sortTypeSql = "Price";
}
if (filter.SortType.Equals(Enums.SortType.Title))
{
sortTypeSql = "Title";
}
if (filter.SortType.Equals(Enums.SortType.Price))
{
sortTypeSql = "Price";
}
if (filter.SortState.Equals(Enums.SortState.Asc))
{
sort = "ASC";
}
if (filter.SortState.Equals(Enums.SortState.Desc))
{
sort = "DESC";
}
var orderBySql = $"pe.{sortTypeSql}";
var orderBy = $"ORDER BY {orderBySql} {sort}";
var sqlPrintingEditionTypes = new StringBuilder($"AND (pe.PrintingEditionType = {(int)filter.PrintingEditionTypes.FirstOrDefault()} ");
foreach (var type in filter.PrintingEditionTypes.Skip(1))
{
sqlPrintingEditionTypes.Append($"OR pe.PrintingEditionType = {(int)type} ");
}
var columnSelectBuilder = new StringBuilder("pe.Id, pe.Title, pe.Price, pe.Description, pe.Currency, pe.PrintingEditionType, a.Id, a.Name");
var countBuilder = new StringBuilder(@"SELECT COUNT(DISTINCT pe.Id)
FROM AuthorInPrintingEditions AS AiNP
INNER JOIN Authors AS a ON AiNP.AuthorId = a.Id
INNER JOIN (
SELECT pe.Id, pe.Price, pe.Title, pe.Description, pe.Currency, pe.PrintingEditionType FROM PrintingEditions AS pe ");
var mainBuilder = new StringBuilder(countBuilder.ToString().Replace("COUNT(DISTINCT pe.Id)", columnSelectBuilder.ToString()));
var filterSqlBuilder = new StringBuilder($"\nWHERE (pe.IsRemoved = 0) {sqlPrintingEditionTypes.ToString()}) {priceSql} {searchSql.ToString()}");
var endBuilder = $@") AS pe ON AiNP.PrintingEditionId = pe.Id;";
var newOrderSql = new StringBuilder($@"{orderBy}
OFFSET (@Page - 1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY
) AS pe ON AiNP.PrintingEditionId = pe.Id
{orderBy};");
var countSql = countBuilder.Append(filterSqlBuilder.ToString())
.Append(endBuilder);
var mainSql = mainBuilder.Append(filterSqlBuilder.ToString())
.Append(newOrderSql.ToString());
var resultSql = mainSql.Append(countSql.ToString()).ToString();
var printingEditions = new List <PrintingEditionDataModel>();
using (var connection = GetSqlConnection())
{
var dict = new Dictionary <long, PrintingEditionDataModel>();
var result = await connection.QueryMultipleAsync(resultSql, filter);
printingEditions = result.Read <PrintingEditionDataModel, Author, PrintingEditionDataModel>(
(pe, author) =>
{
PrintingEditionDataModel model;
if (!dict.TryGetValue(pe.Id, out model))
{
model = pe;
model.Authors = new List <AuthorDataModel>();
dict.Add(model.Id, model);
}
model.Authors.Add(new AuthorDataModel
{
Id = author.Id,
Name = author.Name
});
return(model);
}, splitOn: "Id")
.Distinct()
.ToList();
responseModel.CollectionCount = result.Read <int>().FirstOrDefault();
}
responseModel.Collection = printingEditions;
return(responseModel);
}