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