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