public static string EFilterOperatorToSql(EFilterOperator op)
{
switch (op)
{
case EFilterOperator.EqualTo:
return("=");
case EFilterOperator.GreaterThan:
return(">");
case EFilterOperator.GreaterThanEqualTo:
return(">=");
case EFilterOperator.LessThan:
return("<");
case EFilterOperator.LessThanEqualTo:
return("<=");
case EFilterOperator.Like:
return("LIKE");
case EFilterOperator.NotEqual:
return("!=");
}
return(string.Empty);
}
private static string ToStringFilterOperator(EFilterOperator fileOperator)
{
switch (fileOperator)
{
case EFilterOperator.IsEqualTo:
return("eq");
case EFilterOperator.IsGreaterThan:
return("gt");
case EFilterOperator.IsGreaterThanOrEqualTo:
return("gte");
case EFilterOperator.IsLessThan:
return("lt");
case EFilterOperator.IsLessThanOrEqualTo:
return("lte");
case EFilterOperator.IsNotEqualTo:
return("neq");
default:
return(fileOperator.ToString().ToLower());
}
}
public static MvcHtmlString FilterTextBox(this HtmlHelper html, string name, EFilterOperator filterOperator, string member = null, object value = null)
{
return(html.TextBox(name, value, new
{
data_filter = string.Empty,
data_field = member ?? name,
data_op = ToStringFilterOperator(filterOperator)
}));
}
public XFilter(Guid id, Guid propertyId, string property, EFilterOperator operatorId, string op, string value, int order)
{
this.Id = id;
this.PropertyId = propertyId;
this.Property = property;
this.OperatorId = operatorId;
this.Operator = op;
this.Value = value;
this.Order = order;
}
public static EFilterOperator EFilterOperatorFromValue(int value)
{
foreach (EFilterOperator item in EFilterOperator.GetValues(typeof(EFilterOperator)))
{
if (item.GetHashCode() == value)
{
return(item);
}
}
throw new Exception("Invalid value for EFilterOperator");
}
public XFilter(Guid id, Guid propertyId, string property, EFilterOperator operatorId, string op, string value, int order, bool isIncludes, Guid?logOpId)
{
this.Id = id;
this.PropertyId = propertyId;
this.Property = property;
this.OperatorId = operatorId;
this.Operator = op;
this.Value = value;
this.Order = order;
this.IsIncludesFilter = isIncludes;
this.LogicalOperatorId = logOpId;
}
private static Expression GetOperatorExpression(this EFilterOperator @operator, MemberExpression property, UnaryExpression valueExpression)
{
switch (@operator)
{
case EFilterOperator.GreaterThan:
return(Expression.GreaterThan(property, valueExpression));
case EFilterOperator.GreaterThanOrEqualTo:
return(Expression.GreaterThanOrEqual(property, valueExpression));
case EFilterOperator.LessThan:
return(Expression.LessThan(property, valueExpression));
case EFilterOperator.LessThanOrEqualTo:
return(Expression.LessThanOrEqual(property, valueExpression));
case EFilterOperator.Equals:
return(Expression.Equal(property, valueExpression));
case EFilterOperator.NotEquals:
return(Expression.NotEqual(property, valueExpression));
case EFilterOperator.StartsWith:
return(GetMethodExpression("StartsWith", typeof(string), property, valueExpression));
case EFilterOperator.NotStartsWith:
return(Expression.Not(GetMethodExpression("StartsWith", typeof(string), property, valueExpression)));
case EFilterOperator.Contains:
return(GetMethodExpression("Contains", typeof(string), property, valueExpression));
case EFilterOperator.NotContains:
return(Expression.Not(GetMethodExpression("Contains", typeof(string), property, valueExpression)));
default:
return(Expression.Equal(property, valueExpression));
}
}
/// <summary>
/// Gets the sql operation string.
/// </summary>
/// <param name="searchingOperator">The operation.</param>
/// <param name="field">The field.</param>
/// <param name="value">The value to filter. It can be a <see cref="System.Collections.Generic.IEnumerable{T}"/>.</param>
/// <returns></returns>
public static string GetSqlOperatorString(EFilterOperator searchingOperator, string field, object value)
{
string result = field + " " + GetSqlOperatorString(searchingOperator);
switch (searchingOperator)
{
case EFilterOperator.In:
case EFilterOperator.NotIn:
result += " (" + GetCollection(value) + ")";
break;
case EFilterOperator.BeginsWith:
case EFilterOperator.NotBeginsWith:
result += " '" + value.ToString() + "%'";
break;
case EFilterOperator.EndsWith:
case EFilterOperator.NotEndsWith:
result += " '%" + value.ToString() + "'";
break;
case EFilterOperator.Contains:
case EFilterOperator.NotContains:
result += " '%" + value.ToString() + "%'";
break;
case EFilterOperator.IsNull:
case EFilterOperator.IsNotNull:
result = " NULL";
break;
default:
result += " " + value.ToString();
break;
}
return(result);
}
/// <summary>
/// Gets an SQL server operator.
/// </summary>
/// <param name="searchingOperator">Search operator.</param>
/// <returns></returns>
private static string GetSqlOperatorString(EFilterOperator searchingOperator)
{
string result;
switch (searchingOperator)
{
case EFilterOperator.Equals:
default:
result = "=";
break;
case EFilterOperator.NotEquals:
result = "<>";
break;
case EFilterOperator.LessThan:
result = "<";
break;
case EFilterOperator.LessOrEquals:
result = "<=";
break;
case EFilterOperator.GreaterThan:
result = ">";
break;
case EFilterOperator.GreaterOrEquals:
result = ">=";
break;
case EFilterOperator.BeginsWith:
case EFilterOperator.EndsWith:
case EFilterOperator.Contains:
result = "LIKE";
break;
case EFilterOperator.NotBeginsWith:
case EFilterOperator.NotEndsWith:
case EFilterOperator.NotContains:
result = "NOT LIKE";
break;
case EFilterOperator.In:
result = "IN";
break;
case EFilterOperator.NotIn:
result = "NOT IN";
break;
case EFilterOperator.IsNull:
result = "IS";
break;
case EFilterOperator.IsNotNull:
result = "IS NOT";
break;
}
return(result);
}
/// <summary>
/// Gets the filter statement adding an index as parameter
/// </summary>
/// <param name="searchingName">Searching name or field name.</param>
/// <param name="searchingOperator">Operator.</param>
/// <param name="parameterIndex">Parameter index.</param>
/// <param name="searchingValue">Value to find.</param>
/// <returns></returns>
public static string GetFilterStatement(string searchingName, EFilterOperator searchingOperator, object searchingValue, int parameterIndex)
{
string searchingOperatorString = string.Empty;
string result = null;
switch (searchingOperator)
{
case EFilterOperator.Equals:
result = string.Format("{0} = @{1}", searchingName, parameterIndex);
break;
case EFilterOperator.NotEquals:
result = string.Format("{0} != @{1}", searchingName, parameterIndex);
break;
case EFilterOperator.LessThan:
result = string.Format("{0} < @{1}", searchingName, parameterIndex);
break;
case EFilterOperator.LessOrEquals:
result = string.Format("{0} <= @{1}", searchingName, parameterIndex);
break;
case EFilterOperator.GreaterThan:
result = string.Format("{0} > @{1}", searchingName, parameterIndex);
break;
case EFilterOperator.GreaterOrEquals:
result = string.Format("{0} >= @{1}", searchingName, parameterIndex);
break;
case EFilterOperator.BeginsWith:
result = string.Format("{0}.StartsWith(\"{1}\")", searchingName, searchingValue);
break;
case EFilterOperator.NotBeginsWith:
result = string.Format("!{0}.StartsWith(\"{1}\")", searchingName, searchingValue);
break;
case EFilterOperator.EndsWith:
result = string.Format("{0}.EndsWith(\"{1}\")", searchingName, searchingValue);
break;
case EFilterOperator.NotEndsWith:
result = string.Format("!{0}.EndsWith(\"{1}\")", searchingName, searchingValue);
break;
case EFilterOperator.Contains:
result = string.Format("{0}.Contains(\"{1}\")", searchingName, searchingValue);
//result = System.String.Format("{0} = \"@{1}\"", searchingName, searchingValue);
break;
case EFilterOperator.NotContains:
result = string.Format("!{0}.Contains(\"{1}\")", searchingName, parameterIndex);
break;
default:
result = string.Format("{0} = @{1}", searchingName, parameterIndex);
break;
}
return(result);
}
