public static string BuildProductQuery(ProductQuery query)
{
StringBuilder sb = new StringBuilder();
sb.Append("SET Transaction Isolation Level Read UNCOMMITTED ");
SelectQueryBuilder builder = new SelectQueryBuilder();
builder.SelectFromTable("PProduct p");
builder.SelectColumns("p.ProductID");
builder.AddWhere("p.ProductStatus", Comparison.NotEquals, 0);
//BrandID
if (query.BrandID.HasValue)
{
builder.AddWhere("p.BrandID", Comparison.Equals, query.BrandID.Value);
}
//CategoryID
if (query.CategoryID.HasValue)
{
builder.AddJoin(JoinType.InnerJoin, "PProductCategory pc", "pc.ProductID", Comparison.Equals, "p", "ProductID");
//构造当前分类的所有子分类
List <ProductCategory> listCategory = ProductCategories.GetAllChildCategories(query.CategoryID.Value);
if (null == listCategory || 0 == listCategory.Count)
{
builder.AddWhere("pc.CategoryID", Comparison.Equals, query.CategoryID.Value);
}
else
{
StringBuilder sbCategory = new StringBuilder();
foreach (ProductCategory pc in listCategory)
{
sbCategory.AppendFormat("{0},", pc.CategoryID);
}
builder.AddWhere("pc.CategoryID", Comparison.In,
new SqlLiteral(sbCategory.ToString().TrimEnd(',')));
}
}
bool isJoin = false;
//CompanyID
if (query.CompanyID.HasValue)
{
isJoin = true;
builder.AddJoin(JoinType.InnerJoin, "PProductSupply ps", "ps.ProductID", Comparison.Equals, "p", "ProductID");
builder.AddWhere("ps.SupplierID", Comparison.Equals, query.CompanyID.Value);
}
if (query.Filter != null)
{
if (!isJoin)
{
builder.AddJoin(JoinType.InnerJoin, "PProductSupply ps", "ps.ProductID", Comparison.Equals, "p", "ProductID");
}
switch (query.Filter)
{
case ProviderFilter.Deny:
builder.AddWhere("p.ProductStatus", Comparison.Equals, 2);
break;
case ProviderFilter.Inspect:
builder.AddWhere("p.ProductStatus", Comparison.Equals, 1);
break;
}
}
//FocusType
if (query.FocusType.HasValue)
{
SelectQueryBuilder builderFocus = new SelectQueryBuilder();
builderFocus.Distinct = true;
builderFocus.SelectFromTable("PProductFocus pf");
builderFocus.SelectColumn("pf.ProductID");
builderFocus.AddWhere("pf.FocusType", Comparison.Equals, (int)query.FocusType.Value);
builderFocus.AddWhere("pf.FocusStatus", Comparison.GreaterThan, 0);
builderFocus.AddWhere("pf.FocusFrom", Comparison.LessOrEquals, new SqlLiteral("getdate()"));
builderFocus.AddWhere("pf.FocusEnd", Comparison.GreaterOrEquals, new SqlLiteral("getdate()"));
builder.AddWhere("p.ProductID", Comparison.In, new SqlLiteral(builderFocus.BuildQuery()));
}
//HasPictures
if (query.HasPictures.HasValue)
{
SelectQueryBuilder builderPictures = new SelectQueryBuilder();
builderPictures.Distinct = true;
builderPictures.SelectFromTable("PProductPicture pp");
builderPictures.SelectColumn("pp.ProductID");
builderPictures.AddWhere("pp.PictureStatus", Comparison.GreaterThan, 0);
if (query.HasPictures.Value)
{
builder.AddWhere("p.ProductID", Comparison.In, new SqlLiteral(builderPictures.BuildQuery()));
}
else
{
builder.AddWhere("p.ProductID", Comparison.NotIn, new SqlLiteral(builderPictures.BuildQuery()));
}
}
//HasPublished
if (query.HasPublished.HasValue)
{
if (query.HasPublished.Value)
{
builder.AddWhere("p.ProductStatus", Comparison.Equals, (int)ComponentStatus.Enabled);
}
else
{
builder.AddWhere("p.ProductStatus", Comparison.Equals, (int)ComponentStatus.Disabled);
}
}
//HasPrice
if (query.HasPrice.HasValue)
{
SelectQueryBuilder builderPrice = new SelectQueryBuilder();
builderPrice.Distinct = true;
builderPrice.SelectFromTable("PProductPrice pr");
builderPrice.SelectColumn("pr.ProductID");
builderPrice.AddWhere("pr.SupplyStatus", Comparison.GreaterThan, 0);
if (query.HasPrice.Value)
{
builder.AddWhere("p.ProductID", Comparison.In, new SqlLiteral(builderPrice.BuildQuery()));
}
else
{
builder.AddWhere("p.ProductID", Comparison.NotIn, new SqlLiteral(builderPrice.BuildQuery()));
}
}
//IndustryID
if (query.IndustryID.HasValue)
{
builder.AddJoin(JoinType.InnerJoin, "PProductIndustry pi", "pi.ProductID", Comparison.Equals, "p", "ProductID");
builder.AddWhere("pi.IndustryID", Comparison.Equals, query.IndustryID.Value);
}
//ProductID
if (query.ProductID.HasValue)
{
builder.AddWhere("p.ProductID", Comparison.Equals, query.ProductID.Value);
}
//ProductName
if (!GlobalSettings.IsNullOrEmpty(query.ProductNameFilter))
{
builder.AddWhere("p.ProductName", Comparison.Like, "%" + query.ProductNameFilter + "%");
}
//ProductKey
if (!GlobalSettings.IsNullOrEmpty(query.ProductKeywordsFilter))
{
builder.AddWhere("p.ProductKeywords", Comparison.Like, "%" + query.ProductKeywordsFilter + "%");
}
//OrderBy
switch (query.ProductOrderBy)
{
case ProductOrderBy.DataCreated:
builder.AddOrderBy("p.CreateTime", (Sorting)query.SortOrder);
break;
case ProductOrderBy.ProductName:
builder.AddOrderBy("p.ProductName", (Sorting)query.SortOrder);
break;
case ProductOrderBy.ProductStatus:
builder.AddOrderBy("p.ProductStatus", (Sorting)query.SortOrder);
break;
case ProductOrderBy.BrandName:
builder.AddJoin(JoinType.InnerJoin, "PBrand pb", "pb.BrandID", Comparison.Equals, "p", "BrandID");
builder.AddOrderBy("pb.BrandName", (Sorting)query.SortOrder);
break;
default:
case ProductOrderBy.DisplayOrder:
builder.AddOrderBy("p.DisplayOrder", (Sorting)query.SortOrder);
break;
}
return(builder.BuildQuery());
}
