public async Task <PagingProductModel> GetFilteredProductsAsync(GetFilteredProducts query)
{
string sql = GenerateSqlFromFilteredParameters(query);
await using var connection = new SqlConnection(_connectionString);
await using var command = new SqlCommand(sql, connection);
command.Parameters.Add("@brand", SqlDbType.NVarChar, ProductDbConstants.Brand).SetValue(query.Brand);
command.Parameters.Add("@color", SqlDbType.NVarChar, ProductDbConstants.Color).SetValue(query.Color);
command.Parameters.Add("@forBaby", SqlDbType.Bit).SetValue(query.ForBaby);
command.Parameters.Add("@gender", SqlDbType.TinyInt).SetValue(query.Gender);
command.Parameters.Add("@name", SqlDbType.NVarChar, ProductDbConstants.Name).SetValue(query.Name);
command.Parameters.Add("@priceFrom", SqlDbType.SmallMoney).SetValue(query.PriceFrom);
command.Parameters.Add("@priceTo", SqlDbType.SmallMoney).SetValue(query.PriceTo);
command.Parameters.Add("@productType", SqlDbType.NVarChar, ProductDbConstants.ProductType).SetValue(query.ProductType);
command.Parameters.Add("@size", SqlDbType.NVarChar, ProductDbConstants.Size).SetValue(query.Size);
await connection.EnsureIsOpenAsync().ConfigureAwait(false);
await using var reader = await command.ExecuteReaderAsync().ConfigureAwait(false);
var products = new List <ProductReadModel>(20);
products = await ProductListReaderAsync(reader, products).ConfigureAwait(false);
var totalCount = 2;//todo
var pageCount = CountPages(query.PageSize, totalCount);
return(new PagingProductModel(products, pageCount, query.Page));
}
public Task <PagingProductModel> HandleAsync(GetFilteredProducts query)
{
if (query.PriceFrom is null || query.PriceFrom < ProductConstants.MinPrice)
{
query.PriceFrom = ProductConstants.MinPrice;
}
if (query.PriceTo is null || query.PriceFrom > ProductConstants.MaxPrice)
{
query.PriceTo = ProductConstants.MaxPrice;
}
if (query.Page <= 0)
{
query.Page = 1;
}
if (query.PageSize <= 0)
{
query.PageSize = 10;
}
return(_readRepository.GetFilteredProductsAsync(query));
}
private static string GenerateSqlFromFilteredParameters(GetFilteredProducts query)
{
var skip = (query.Page - 1) * query.PageSize;
string sql =
$@"WITH pg AS
(SELECT Products.Id, c = COUNT(*) OVER()
FROM Products
ORDER BY Products.Id
OFFSET {query.PageSize} * {skip} ROWS
FETCH NEXT {query.PageSize} ROWS ONLY)
SELECT dbo.Products.Id,Brand,Color,CreateTime,[Description],Discount,Expiration,DiscountPrice,ForBaby,Gender,IsDeleted,[Name],Price,Quantity,ProductType,[Weight],Size,Images.[Url],Images.MainImage,Images.Id AS ImgId,Images.ProductId
FROM Products
LEFT JOIN Images ON dbo.Products.Id=Images.ProductId
INNER JOIN pg ON pg.Id= dbo.Products.Id" ;
var sb = new StringBuilder();
sb.AppendLine(sql);
sb.AppendLine("WHERE IsDeleted=0");
if (query.Brand is not null)
{
sb.AppendWithAnd("Brand=@brand");
}
if (query.Color is not null)
{
sb.AppendWithAnd("Color=@color");
}
if (query.ForBaby is not null)
{
sb.AppendWithAnd("ForBaby=@forBaby");
}
if (query.Gender is not null)
{
sb.AppendWithAnd("Gender=@gender");
}
if (query.Name is not null)
{
sb.AppendWithAnd("Name=@name");
}
if (query.Size is not null)
{
sb.AppendWithAnd("Size=@size");
}
if (query.ProductType is not null)
{
sb.AppendWithAnd("ProductType=@productType");
}
sb.AppendWithAnd("(Price >= @priceFrom OR DiscountPrice >= @priceFrom)");
sb.AppendWithAnd("(Price <= @priceTo OR DiscountPrice <= @priceTo)");
// sb.AppendWhereIfHaveCondition(sql);
return(sb.ToString());
}
public async Task <IActionResult> GetFilteredProductsAsync([FromQuery] GetFilteredProducts query)
{
var data = await Mediator.QueryAsync(query).ConfigureAwait(false);
return(Ok(data));
}
public async Task <IActionResult> GetFilteredProductPage(GetFilteredProducts query)
{
var(pageValue, allValueCount) = await _queryProcessor.QueryAsync(query);
return(Ok(new { pageValue, allValueCount }));
}
