// GET: products/
// GET: products/index
// GET: products/index?minPrice=25&sortOrder=PriceHighToLow
public IActionResult Index(ProductFilter filter, ProductSortOrder sortOrder = ProductSortOrder.Default)
{
IList <Product> products = productDAO.GetAll(filter, sortOrder);
//ViewData["Products"] = products;
return(View(products));
}
/// <summary>
/// 排序
/// </summary>
/// <param name="sort"></param>
/// <returns></returns>
private static Func<IQueryable<ProductEntity>, IOrderedQueryable<ProductEntity>> GetOrder(ProductSortOrder sort)
{
Func<IQueryable<ProductEntity>, IOrderedQueryable<ProductEntity>> order;
switch (sort)
{
case ProductSortOrder.Default:
default:
order = v => v.OrderByDescending(s => s.SortOrder).ThenByDescending(s => s.CreatedDate);
break;
}
return order;
}
public IList <Product> GetAll(ProductFilter filter, ProductSortOrder sortOrder)
{
List <Product> products = new List <Product>();
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand();
//string sql = $"SELECT products.* FROM products " +
// $"JOIN categories ON products.category_id = categories.id " +
// $"WHERE (price BETWEEN {filter.MinPrice} AND {filter.MaxPrice}) " +
// $"AND (average_rating >= {filter.MinRating}) ";
string sql = $"SELECT products.* FROM products " +
$"JOIN categories ON products.category_id = categories.id " +
$"WHERE (price BETWEEN @MinPrice AND @MaxPrice) " +
$"AND (average_rating >= @MinRating) ";
command.Parameters.AddWithValue("@MinPrice", filter.MinPrice);
command.Parameters.AddWithValue("@MaxPrice", filter.MaxPrice);
command.Parameters.AddWithValue("@MinRating", filter.MinRating);
if (!String.IsNullOrEmpty(filter.Category))
{
sql += $"AND (categories.name = @category)";
command.Parameters.AddWithValue("@category", filter.Category);
}
sql += $" ORDER BY {SortChoices[sortOrder]};";
command.CommandText = sql;
command.Connection = connection;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
products.Add(MapRowToProduct(reader));
}
}
}
catch (SqlException ex)
{
Console.Error.WriteLine($"Exception occurred reading product data - ${ex}");
throw;
}
return(products);
}
public IQueryable <ProductEntity> Get(PagerRequest pagerRequest, out int totalCount, ProductSortOrder sortOrder, ProductFilter productFilter)
{
var linq = base.Get(Filter(productFilter));
//var linq = base.Get(Filter(productFilter), out totalCount, pagerRequest.PageIndex, pagerRequest.PageSize,
// GetOrder(sortOrder));
if (productFilter.TopicId != null)
{
linq = linq.Join(GetTopicRelationIds4Linq(productFilter.TopicId.Value), r => r.Id, v => v.Product_Id,
(r, v) => r);
}
if (productFilter.PromotionId != null)
{
linq = linq.Join(GetListByPromotion4Linq(productFilter.PromotionId.Value), r => r.Id, v => v.ProdId,
(r, v) => r);
}
var resetSet = linq;
var orderBy = GetOrder(sortOrder);
resetSet = orderBy != null?orderBy(resetSet).AsQueryable() : resetSet.AsQueryable();
totalCount = resetSet.Count();
var skipCount = (pagerRequest.PageIndex - 1) * pagerRequest.PageSize;
resetSet = skipCount == 0 ? resetSet.Take(pagerRequest.PageSize) : resetSet.Skip(skipCount).Take(pagerRequest.PageSize);
return(resetSet);
}
public List <ProductEntity> GetPagedList(PagerRequest pagerRequest, out int totalCount,
ProductSortOrder sortOrder, ProductFilter productFilter)
{
return(Get(pagerRequest, out totalCount, sortOrder, productFilter).ToList());
}
public List <ProductEntity> GetPagedList(PagerRequest pagerRequest, out int totalCount, ProductSortOrder sortOrder, Timestamp timestamp,
int?tagId, int?recommendUser, int?brandId)
{
List <int> tags;
if (tagId == null)
{
tags = null;
}
else
{
tags = new List <int>(1)
{
tagId.Value
};
}
return
(base.Get(Filter(DataStatus.Normal, timestamp, tags, recommendUser, brandId), out totalCount,
pagerRequest.PageIndex, pagerRequest.PageSize, GetOrder(sortOrder)).ToList());
}
public List <ProductEntity> GetPagedListForSearch(PagerRequest pagerRequest, out int totalCount, ProductSortOrder sortOrder, Timestamp timestamp,
string productName, int?recommendUser, List <int> tagids, int?brandId)
{
var filter = Filter(DataStatus.Normal, timestamp, tagids, recommendUser, brandId);
if (!String.IsNullOrWhiteSpace(productName))
{
filter = filter.And(v => v.Name.StartsWith(productName));
}
return
(base.Get(filter, out totalCount, pagerRequest.PageIndex, pagerRequest.PageSize, GetOrder(sortOrder))
.ToList());
}
public IQueryable <ProductEntity> Search(PagerRequest pagerRequest, out int totalCount, ProductSortOrder sortOrder, Timestamp timestamp,
string productName, string brandName, int?recommendUser, List <int> tagids, int?brandId, DataStatus?dataStatus)
{
var filter = Filter(dataStatus, timestamp, tagids, recommendUser, brandId);
if (!String.IsNullOrWhiteSpace(productName))
{
filter = filter.And(v => v.Name.Contains(productName));
}
totalCount = 0;
var p1 = base.Get(filter).Take(pagerRequest.PageSize);
if (!String.IsNullOrEmpty(brandName))
{
var p2 = base.Get(Filter(dataStatus, timestamp, tagids, recommendUser, brandId));
var b = (ServiceLocator.Current.Resolve <IBrandRepository>().Get(DataStatus.Normal) as IQueryable <BrandEntity>).Where(v => v.Name.Contains(brandName) || v.EnglishName.Contains(brandName));
var r = p2.Join(b, v => v.Brand_Id, j => j.Id, (v, j) => v).Take(pagerRequest.PageSize);
return(r.Union(p1).Take(pagerRequest.PageSize));
}
return(p1);
}
/// <summary>
/// 排序
/// </summary>
/// <param name="sort"></param>
/// <returns></returns>
private static Func <IQueryable <ProductEntity>, IOrderedQueryable <ProductEntity> > GetOrder(ProductSortOrder sort)
{
Func <IQueryable <ProductEntity>, IOrderedQueryable <ProductEntity> > order = null;
switch (sort)
{
case ProductSortOrder.CreatedDateDesc:
order = v => v.OrderByDescending(s => s.CreatedDate);
break;
case ProductSortOrder.Default:
default:
order = v => v.OrderByDescending(s => s.SortOrder).ThenByDescending(s => s.CreatedDate);
break;
}
return(order);
}
