public static List <Product> FindProductsByCategory(int categoryId, ProductSortByField sortBy)
{
ProductQuery p = new ProductQuery("p");
ProductCategoryQuery pc = new ProductCategoryQuery("pc");
vProductsSoldCountsQuery productsSold = new vProductsSoldCountsQuery("productsSold");
p.Select(p);
p.InnerJoin(pc).On(p.Id == pc.ProductId);
p.LeftJoin(productsSold).On(p.Id == productsSold.ProductId);
p.Where(p.IsActive == true);
p.Where(pc.CategoryId == categoryId);
if (!string.IsNullOrEmpty(sortBy.Field))
{
p.OrderBy(sortBy.Field,
sortBy.SortDirection == SortDirection.ASC
? esOrderByDirection.Ascending
: esOrderByDirection.Descending);
}
//string sql = p.Parse();
ProductCollection products = new ProductCollection();
products.Load(p);
return(products.Where(z => z.IsViewable == true).ToList());
}
public List <Product> GetRelatedProducts()
{
ProductQuery p = new ProductQuery("p");
RelatedProductQuery rp = new RelatedProductQuery("rp");
p.Select(p).InnerJoin(rp).On(p.Id == rp.RelatedProductId);
p.Where(rp.ProductId == this.Id.Value);
ProductCollection collection = new ProductCollection();
collection.Load(p);
return(collection.ToList());
}
public static List <Product> FindProductsByCategories(IList <int> categoryIds, ProductSortByField sortBy, bool matchAllCategories)
{
if (categoryIds.Count > 0)
{
ProductQuery p = new ProductQuery("p");
ProductCategoryQuery pc = new ProductCategoryQuery("pc");
vProductsSoldCountsQuery productsSold = new vProductsSoldCountsQuery("productsSold");
//p.es.CountAll = true;
//p.es.CountAllAlias = "TotalResultCount";
//p.es.PageNumber = 1;
//p.es.PageSize = 2;
p.es.Distinct = true;
p.Select(p);
p.InnerJoin(pc).On(p.Id == pc.ProductId);
p.LeftJoin(productsSold).On(p.Id == productsSold.ProductId);
p.Where(p.IsActive == true);
if (matchAllCategories)
{
ProductCategoryQuery pcAllCats = new ProductCategoryQuery("pcAll");
pcAllCats.Select(pcAllCats.ProductId)
.Where(pcAllCats.CategoryId.In(categoryIds.ToArray()))
.GroupBy(pcAllCats.ProductId)
.Having(pcAllCats.ProductId.Count() == categoryIds.Count);
p.Where(p.Id.In(pcAllCats));
}
else
{
p.Where(pc.CategoryId.In(categoryIds.ToArray()));
}
if (!string.IsNullOrEmpty(sortBy.Field))
{
p.OrderBy(sortBy.Field,
sortBy.SortDirection == SortDirection.ASC
? esOrderByDirection.Ascending
: esOrderByDirection.Descending);
}
string sql = p.Parse();
ProductCollection products = new ProductCollection();
products.Load(p);
return(products.Where(z => z.IsViewable == true).ToList());
}
return(new List <Product>());
}
/// <summary>
/// Get the Product entities associated with this order's order items.
/// If products were deleted since the order was placed, this product count
/// will not match the order item count.
/// </summary>
/// <returns></returns>
public List <Product> GetOrderItemProducts()
{
List <OrderItem> orderItems = this.OrderItemCollectionByOrderId.ToList();
List <int> productIds = orderItems.ConvertAll(oi => oi.ProductId.Value);
ProductQuery q = new ProductQuery();
q.Where(q.Id.In(productIds.ToArray()));
ProductCollection collection = new ProductCollection();
collection.Load(q);
return(collection.ToList());
}
public static List <Product> GetAll(int storeId, bool includeActiveProductsOnly)
{
ProductQuery q = new ProductQuery();
q.Where(q.StoreId == storeId);
if (includeActiveProductsOnly)
{
q.Where(q.IsActive == true);
}
q.OrderBy(q.Name.Ascending);
ProductCollection collection = new ProductCollection();
collection.Load(q);
return(collection.ToList());
}
public static IList <Product> GetProductsByIds(int[] productIds)
{
if (productIds.Length > 0)
{
ProductQuery q = new ProductQuery();
q.Where(q.Id.In(productIds));
q.OrderBy(q.Name.Ascending);
ProductCollection collection = new ProductCollection();
collection.Load(q);
return(collection);
}
else
{
return(new List <Product>());
}
}