public List <Category> GetCategories(bool includeHiddenCategories)
{
// This is correct, but there's a bug in ES that doesn't honor the DNN Object Qualifier for M2M collections :(
//List<Category> categories = this.UpToCategoryCollection;
//if (!includeHiddenCategories)
//{
// categories.RemoveAll(c => !c.IsDisplayed.GetValueOrDefault());
//}
//return categories;
//SELECT
//c.*
//FROM DNNspot_Store_Category c
//INNER JOIN DNNspot_Store_ProductCategory pc ON pc.CategoryId = c.Id
//WHERE pc.ProductId = 21
CategoryQuery c = new CategoryQuery("c");
ProductCategoryQuery pc = new ProductCategoryQuery("pc");
c.Select(c).InnerJoin(pc).On(c.Id == pc.CategoryId);
c.Where(pc.ProductId == this.Id.Value);
CategoryCollection collection = new CategoryCollection();
collection.Load(c);
return(collection.ToList());
}
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());
}
private bool LoadByPrimaryKeyDynamic(System.Int32 productId, System.Int32 categoryId)
{
ProductCategoryQuery query = new ProductCategoryQuery();
query.Where(query.ProductId == productId, query.CategoryId == categoryId);
return(this.Load(query));
}
protected void InitQuery(ProductCategoryQuery query)
{
query.OnLoadDelegate = this.OnQueryLoaded;
if (!query.es2.HasConnection)
{
query.es2.Connection = ((IEntityCollection)this).Connection;
}
}
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>());
}
public bool Load(ProductCategoryQuery query)
{
this.query = query;
InitQuery(this.query);
return(Query.Load());
}