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()); }