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