public virtual bool ToWhereClause(FilterSql context, List <FilterCriteria> findIn, Predicate <FilterCriteria> match) { if (context.Criteria != null) { context.Criteria.Clear(); // ! } context.Criteria = findIn.FindAll(match); return(ToWhereClause(context)); }
private bool IsShortcut(FilterSql context, FilterCriteria itm, ref int index) { if (itm.Entity == ShortcutPrice) { // TODO: where clause of special price not correct. product can appear in price and in special price range. if (itm.IsRange) { string valueLeft, valueRight; itm.Value.SplitToPair(out valueLeft, out valueRight, "~"); context.WhereClause.AppendFormat("((Price >= {0} And Price {1} {2}) Or (SpecialPrice >= {0} And SpecialPrice {1} {2} And SpecialPriceStartDateTimeUtc <= {3} And SpecialPriceEndDateTimeUtc >= {3}))", FormatParameterIndex(ref index), itm.Operator == FilterOperator.RangeGreaterEqualLessEqual ? "<=" : "<", FormatParameterIndex(ref index), FormatParameterIndex(ref index) ); context.Values.Add(FilterValueToObject(valueLeft, itm.Type ?? "Decimal")); context.Values.Add(FilterValueToObject(valueRight, itm.Type ?? "Decimal")); context.Values.Add(DateTime.UtcNow); } else { context.WhereClause.AppendFormat("(Price {0} {1} Or (SpecialPrice {0} {1} And SpecialPriceStartDateTimeUtc <= {2} And SpecialPriceEndDateTimeUtc >= {2}))", itm.Operator == null ? "=" : itm.Operator.ToString(), FormatParameterIndex(ref index), FormatParameterIndex(ref index)); context.Values.Add(FilterValueToObject(itm.Value, itm.Type ?? "Decimal")); context.Values.Add(DateTime.UtcNow); } } else if (itm.Entity == ShortcutSpecAttribute) { context.WhereClause.AppendFormat("SpecificationAttributeOptionId {0} {1}", itm.Operator == null ? "=" : itm.Operator.ToString(), FormatParameterIndex(ref index)); context.Values.Add(itm.ID ?? 0); } else { return(false); } return(true); }
private bool IsShortcut(FilterSql context, FilterCriteria itm, ref int index) { if (itm.Entity == ShortcutPrice) { if (itm.IsRange) { string valueLeft, valueRight; itm.Value.SplitToPair(out valueLeft, out valueRight, "~"); context.WhereClause.AppendFormat("((Price >= {0} And Price {1} {2}) Or (SpecialPrice >= {0} And SpecialPrice {1} {2} And SpecialPriceStartDateTimeUtc <= {3} And SpecialPriceEndDateTimeUtc >= {3}))", FormatParameterIndex(ref index), itm.Operator == FilterOperator.RangeGreaterEqualLessEqual ? "<=" : "<", FormatParameterIndex(ref index), FormatParameterIndex(ref index) ); context.Values.Add(FilterValueToObject(valueLeft, itm.Type ?? "Decimal")); context.Values.Add(FilterValueToObject(valueRight, itm.Type ?? "Decimal")); context.Values.Add(DateTime.UtcNow); } else { context.WhereClause.AppendFormat("(Price {0} {1} Or (SpecialPrice {0} {1} And SpecialPriceStartDateTimeUtc <= {2} And SpecialPriceEndDateTimeUtc >= {2}))", itm.Operator == null ? "=" : itm.Operator.ToString(), FormatParameterIndex(ref index), FormatParameterIndex(ref index)); context.Values.Add(FilterValueToObject(itm.Value, itm.Type ?? "Decimal")); context.Values.Add(DateTime.UtcNow); } } else if (itm.Entity == ShortcutSpecAttribute) { context.WhereClause.AppendFormat("SpecificationAttributeOptionId {0} {1}", itm.Operator == null ? "=" : itm.Operator.ToString(), FormatParameterIndex(ref index)); context.Values.Add(itm.ID ?? 0); } else { return false; } return true; }
public virtual bool ToWhereClause(FilterSql context, List<FilterCriteria> findIn, Predicate<FilterCriteria> match) { if (context.Criteria != null) context.Criteria.Clear(); // ! context.Criteria = findIn.FindAll(match); return ToWhereClause(context); }
public virtual bool ToWhereClause(FilterSql context) { if (context.Values == null) context.Values = new List<object>(); else context.Values.Clear(); if (context.WhereClause == null) context.WhereClause = new StringBuilder(); else context.WhereClause.Clear(); int index = 0; FilterParentheses(context.Criteria); foreach (var itm in context.Criteria) { if (context.WhereClause.Length > 0) context.WhereClause.AppendFormat(" {0} ", itm.Or ? "Or" : "And"); if (itm.Open.HasValue && itm.Open.Value) context.WhereClause.Append("("); if (IsShortcut(context, itm, ref index)) { } else if (itm.IsRange) { string valueLeft, valueRight; itm.Value.SplitToPair(out valueLeft, out valueRight, "~"); context.WhereClause.AppendFormat("({0} >= {1} And {0} {2} {3})", itm.SqlName, FormatParameterIndex(ref index), itm.Operator == FilterOperator.RangeGreaterEqualLessEqual ? "<=" : "<", FormatParameterIndex(ref index) ); context.Values.Add(FilterValueToObject(valueLeft, itm.Type)); context.Values.Add(FilterValueToObject(valueRight, itm.Type)); } else if (itm.Value.IsEmpty()) { context.WhereClause.AppendFormat("ASCII({0}) Is Null", itm.SqlName); // true if null or empty (string) } else { context.WhereClause.Append(itm.SqlName); if (itm.Operator == FilterOperator.Contains) context.WhereClause.Append(".Contains("); else if (itm.Operator == FilterOperator.StartsWith) context.WhereClause.Append(".StartsWith("); else if (itm.Operator == FilterOperator.EndsWith) context.WhereClause.Append(".EndsWith("); else context.WhereClause.AppendFormat(" {0} ", itm.Operator == null ? "=" : itm.Operator.ToString()); context.WhereClause.Append(FormatParameterIndex(ref index)); if (itm.Operator == FilterOperator.Contains || itm.Operator == FilterOperator.StartsWith || itm.Operator == FilterOperator.EndsWith) context.WhereClause.Append(")"); context.Values.Add(FilterValueToObject(itm.Value, itm.Type)); } if (itm.Open.HasValue && !itm.Open.Value) context.WhereClause.Append(")"); } return (context.WhereClause.Length > 0); }
public virtual IQueryable<Product> ProductFilter(FilterProductContext context) { var sql = new FilterSql(); var query = AllProducts(context.CategoryIds); // prices if (ToWhereClause(sql, context.Criteria, c => !c.IsInactive && c.Entity == ShortcutPrice)) { query = query.Where(sql.WhereClause.ToString(), sql.Values.ToArray()); } // manufacturer if (ToWhereClause(sql, context.Criteria, c => !c.IsInactive && c.Entity == "Manufacturer")) { var pmq = from p in query from pm in p.ProductManufacturers where !pm.Manufacturer.Deleted select pm; query = pmq .Where(sql.WhereClause.ToString(), sql.Values.ToArray()) .Select(pm => pm.Product); } // specification attribute if (ToWhereClause(sql, context.Criteria, c => !c.IsInactive && (c.Entity == "SpecificationAttributeOption" || c.Entity == ShortcutSpecAttribute))) { //var saq = ( // from p in query // from sa in p.ProductSpecificationAttributes // select sa).Where(a => a.AllowFiltering).Where(sql.WhereClause.ToString(), sql.Values.ToArray()); //query = saq.Select(sa => sa.Product); int countSameNameAttributes = sql.Criteria .Where(c => c.Entity == ShortcutSpecAttribute) .GroupBy(c => c.Name) .Count(); var specRepository = EngineContext.Current.Resolve<IRepository<ProductSpecificationAttribute>>(); var saq = specRepository.TableUntracked .Where(a => a.AllowFiltering) .Where(sql.WhereClause.ToString(), sql.Values.ToArray()) .GroupBy(a => a.ProductId) .Where(grp => (grp.Count() >= countSameNameAttributes)); query = from p in query join sa in saq on p.Id equals sa.Key select p; } // sort var order = (ProductSortingEnum)(context.OrderBy ?? 0); switch (order) { case ProductSortingEnum.NameDesc: query = query.OrderByDescending(p => p.Name); break; case ProductSortingEnum.PriceAsc: query = query.OrderBy(p => p.Price); break; case ProductSortingEnum.PriceDesc: query = query.OrderByDescending(p => p.Price); break; case ProductSortingEnum.CreatedOn: query = query.OrderByDescending(p => p.CreatedOnUtc); break; case ProductSortingEnum.CreatedOnAsc: query = query.OrderBy(p => p.CreatedOnUtc); break; default: query = query.OrderBy(p => p.Name); break; } // distinct cause same products can be mapped to sub-categories... too slow //query = // from p in query // group p by p.Id into grp // orderby grp.Key // select grp.FirstOrDefault(); //query.ToString().Dump(); return query; }
public virtual IQueryable <Product> ProductFilter(FilterProductContext context) { var nowUtc = DateTime.UtcNow; var sql = new FilterSql(); var query = AllProducts(context.CategoryIds); // manufacturer if (ToWhereClause(sql, context.Criteria, c => !c.IsInactive && c.Entity == "Manufacturer")) { bool includeFeatured = IncludeFeatured; var pmq = ( from p in query from pm in p.ProductManufacturers where (!includeFeatured || includeFeatured == pm.IsFeaturedProduct) && !pm.Manufacturer.Deleted select pm).Where(sql.WhereClause.ToString(), sql.Values.ToArray()); query = pmq.Select(pm => pm.Product); } // specification attribute if (ToWhereClause(sql, context.Criteria, c => !c.IsInactive && (c.Entity == "SpecificationAttributeOption" || c.Entity == ShortcutSpecAttribute))) { //var saq = ( // from p in query // from sa in p.ProductSpecificationAttributes // select sa).Where(a => a.AllowFiltering).Where(sql.WhereClause.ToString(), sql.Values.ToArray()); //query = saq.Select(sa => sa.Product); int countSameNameAttributes = sql.Criteria .Where(c => c.Entity == ShortcutSpecAttribute) .GroupBy(c => c.Name) .Count(); var specRepository = EngineContext.Current.Resolve <IRepository <ProductSpecificationAttribute> >(); var saq = specRepository.Table .Where(a => a.AllowFiltering && a.ShowOnProductPage) .Where(sql.WhereClause.ToString(), sql.Values.ToArray()) .GroupBy(a => a.ProductId) .Where(grp => (grp.Count() >= countSameNameAttributes)); query = from p in query join sa in saq on p.Id equals sa.Key select p; } // sort var order = (ProductSortingEnum)(context.OrderBy ?? 0); switch (order) { case ProductSortingEnum.NameDesc: query = query.OrderByDescending(p => p.Name); break; case ProductSortingEnum.PriceAsc: query = query.OrderBy(p => p.Price); break; case ProductSortingEnum.PriceDesc: query = query.OrderByDescending(p => p.Price); break; case ProductSortingEnum.CreatedOn: query = query.OrderByDescending(p => p.CreatedOnUtc); break; case ProductSortingEnum.CreatedOnAsc: query = query.OrderBy(p => p.CreatedOnUtc); break; default: query = query.OrderBy(p => p.Name); break; } // distinct (required?) //query = // from p in query // group p by p.Id into grp // orderby grp.Key // select grp.FirstOrDefault(); //query.ToString().Dump(); return(query); }
public virtual bool ToWhereClause(FilterSql context) { if (context.Values == null) { context.Values = new List <object>(); } else { context.Values.Clear(); } if (context.WhereClause == null) { context.WhereClause = new StringBuilder(); } else { context.WhereClause.Clear(); } int index = 0; FilterParentheses(context.Criteria); foreach (var itm in context.Criteria) { if (context.WhereClause.Length > 0) { context.WhereClause.AppendFormat(" {0} ", itm.Or ? "Or" : "And"); } if (itm.Open.HasValue && itm.Open.Value) { context.WhereClause.Append("("); } if (IsShortcut(context, itm, ref index)) { } else if (itm.IsRange) { string valueLeft, valueRight; itm.Value.SplitToPair(out valueLeft, out valueRight, "~"); context.WhereClause.AppendFormat("({0} >= {1} And {0} {2} {3})", itm.SqlName, FormatParameterIndex(ref index), itm.Operator == FilterOperator.RangeGreaterEqualLessEqual ? "<=" : "<", FormatParameterIndex(ref index) ); context.Values.Add(FilterValueToObject(valueLeft, itm.Type)); context.Values.Add(FilterValueToObject(valueRight, itm.Type)); } else if (itm.Value.IsNullOrEmpty()) { context.WhereClause.AppendFormat("ASCII({0}) Is Null", itm.SqlName); // true if null or empty (string) } else { context.WhereClause.Append(itm.SqlName); if (itm.Operator == FilterOperator.Contains) { context.WhereClause.Append(".Contains("); } else if (itm.Operator == FilterOperator.StartsWith) { context.WhereClause.Append(".StartsWith("); } else if (itm.Operator == FilterOperator.EndsWith) { context.WhereClause.Append(".EndsWith("); } else { context.WhereClause.AppendFormat(" {0} ", itm.Operator == null ? "=" : itm.Operator.ToString()); } context.WhereClause.Append(FormatParameterIndex(ref index)); if (itm.Operator == FilterOperator.Contains || itm.Operator == FilterOperator.StartsWith || itm.Operator == FilterOperator.EndsWith) { context.WhereClause.Append(")"); } context.Values.Add(FilterValueToObject(itm.Value, itm.Type)); } if (itm.Open.HasValue && !itm.Open.Value) { context.WhereClause.Append(")"); } } return(context.WhereClause.Length > 0); }
public virtual IQueryable <Product> ProductFilter(FilterProductContext context) { var sql = new FilterSql(); var query = AllProducts(context.CategoryIds); // prices if (ToWhereClause(sql, context.Criteria, c => !c.IsInactive && c.Entity.IsCaseInsensitiveEqual(ShortcutPrice))) { query = query.Where(sql.WhereClause.ToString(), sql.Values.ToArray()); } // manufacturer if (ToWhereClause(sql, context.Criteria, c => !c.IsInactive && c.Entity.IsCaseInsensitiveEqual("Manufacturer"))) { var pmq = from p in query from pm in p.ProductManufacturers where !pm.Manufacturer.Deleted select pm; query = pmq .Where(sql.WhereClause.ToString(), sql.Values.ToArray()) .Select(pm => pm.Product); } // specification attribute if (ToWhereClause(sql, context.Criteria, c => !c.IsInactive && (c.Entity.IsCaseInsensitiveEqual("SpecificationAttributeOption") || c.Entity.IsCaseInsensitiveEqual(ShortcutSpecAttribute)))) { //var saq = ( // from p in query // from sa in p.ProductSpecificationAttributes // select sa).Where(a => a.AllowFiltering).Where(sql.WhereClause.ToString(), sql.Values.ToArray()); //query = saq.Select(sa => sa.Product); int countSameNameAttributes = sql.Criteria .Where(c => c.Entity.IsCaseInsensitiveEqual(ShortcutSpecAttribute)) .GroupBy(c => c.Name) .Count(); var specRepository = EngineContext.Current.Resolve <IRepository <ProductSpecificationAttribute> >(); var saq = specRepository.TableUntracked .Where(a => a.AllowFiltering) .Where(sql.WhereClause.ToString(), sql.Values.ToArray()) .GroupBy(a => a.ProductId) .Where(grp => (grp.Count() >= countSameNameAttributes)); query = from p in query join sa in saq on p.Id equals sa.Key select p; } // sort var order = (ProductSortingEnum)(context.OrderBy ?? 0); switch (order) { case ProductSortingEnum.NameDesc: query = query.OrderByDescending(p => p.Name); break; case ProductSortingEnum.PriceAsc: query = query.OrderBy(p => p.Price); break; case ProductSortingEnum.PriceDesc: query = query.OrderByDescending(p => p.Price); break; case ProductSortingEnum.CreatedOn: query = query.OrderByDescending(p => p.CreatedOnUtc); break; case ProductSortingEnum.CreatedOnAsc: query = query.OrderBy(p => p.CreatedOnUtc); break; default: query = query.OrderBy(p => p.Name); break; } // distinct cause same products can be mapped to sub-categories... too slow //query = // from p in query // group p by p.Id into grp // orderby grp.Key // select grp.FirstOrDefault(); //query.ToString().Dump(); return(query); }