public async Task <FilterProductDto> GetProductSiteIdAsync(int start, int length, string search, int columnSort, int siteId, bool isAsc = true) { string query = $"SELECT count(1) FROM Product AS P JOIN Category AS C ON P.CategoryId = C.CategoryId JOIN Site AS S ON C.SiteId = S.SiteId WHERE C.SiteId=" + siteId + ";" + $" SELECT count(1) FROM Product AS P JOIN Category AS C ON P.CategoryId = C.CategoryId JOIN Site AS S ON C.SiteId = S.SiteId WHERE C.SiteId=" + siteId + $" AND Name LIKE CONCAT('%',@search,'%'); " + "SELECT Id, ProductId, Name, Price, Quantity, CategoryName, SiteName, QuantitySold, CommentCount, Discount, Url, CreatedProductDate, CreatedDate, UpdatedDate, CASE DATEDIFF(DAY, P.CreatedProductDate, GETUTCDATE()) WHEN 0 THEN 0 ELSE (QuantitySold/ DATEDIFF(DAY, P.CreatedProductDate, GETUTCDATE())) END AS Average " + "FROM Product AS P JOIN Category AS C ON P.CategoryId = C.CategoryId " + "JOIN Site AS S ON C.SiteId = S.SiteId " + "WHERE Name LIKE CONCAT('%',@search,'%')" + "AND C.SiteId=" + siteId; string orderBy = $" ORDER BY {listColumn[columnSort]} {(!isAsc ? "DESC" : "")}"; string limit = $" OFFSET {start} ROWS FETCH NEXT {length} ROWS ONLY"; string command = $"{query} {orderBy} {limit}"; using (var db = _baseRepository.GetConnection()) { DynamicParameters dynamicParameters = new DynamicParameters(); dynamicParameters.Add("@search", search.Replace("'", "''").Replace("%", "[%]")); var result = await db.QueryMultipleAsync(command, dynamicParameters); var filterProductDto = new FilterProductDto(); filterProductDto.RecordsTotal = await result.ReadFirstAsync <int>(); filterProductDto.RecordsFiltered = await result.ReadFirstAsync <int>(); filterProductDto.Data = (await result.ReadAsync <ProductDto>()).AsList(); return(filterProductDto); } }
public override async Task GetProducts(FilterProductDto request, IServerStreamWriter <ProductDto> responseStream, ServerCallContext context) { var products = _productApplication.GetProducts(request.Name); foreach (var product in products) { await responseStream.WriteAsync(product.ToProductDto()); } }
public async Task <PageResponse <ProductDto> > ListProductsAsync(FilterProductDto filter, PageRequest pageRequest) { return(await _dbContext .Products .Where(!string.IsNullOrEmpty(filter.Name), p => p.Name.ToLower().Contains(filter.Name.ToLower())) .Where(!string.IsNullOrEmpty(filter.ActiveIngredient), p => p.ActiveIngredient.ToLower().Contains(filter.ActiveIngredient.ToLower())) .OrderBy(p => p.Name) .ProjectTo <ProductDto>(_mapper.ConfigurationProvider) .ToPagedListAsync(pageRequest)); }
public async Task <ActionResult <List <ProductDto> > > GetAllProducts([FromQuery] FilterProductDto filterProductDto) { var productQueryable = context.Products.AsQueryable().Include(u => u.Category).AsQueryable(); if (!string.IsNullOrWhiteSpace(filterProductDto.Name)) { productQueryable = productQueryable.Where(x => x.Name.Contains(filterProductDto.Name)); } if (filterProductDto.MaxPrice != null) { productQueryable = productQueryable.Where(x => x.Price <= filterProductDto.MaxPrice); } if (filterProductDto.MinPrice != null) { productQueryable = productQueryable.Where(x => x.Price >= filterProductDto.MinPrice); } if (!string.IsNullOrWhiteSpace(filterProductDto.CategoryName)) { productQueryable = productQueryable.Where(x => x.Category.Name.Contains(filterProductDto.CategoryName)); } if (!string.IsNullOrWhiteSpace(filterProductDto.OrderingField)) { try { productQueryable = productQueryable .OrderBy($"{filterProductDto.OrderingField} {(filterProductDto.AscendingOrder ? "ascending" : "descending")}"); } catch { logger.LogWarning("Could not order by field: " + filterProductDto.OrderingField); } } await HttpContext.InsertPaginationParametersInResponse(productQueryable, filterProductDto.RecordsPerPage); var products = await productQueryable.Paginate(filterProductDto).ToListAsync(); return(mapper.Map <List <ProductDto> >(products)); }
public Task <PageResponse <ProductDto> > ListProducts([FromQuery] FilterProductDto filter, [FromQuery] PageRequest pageRequest) => _productService.ListProductsAsync(filter, pageRequest);