public static string BuildWhereClause <T>(int index, string logic,
AzFilter.GridFilter filter, List <object> parameters)
{
var entityType = (typeof(T));
var property = entityType.GetProperty(filter.Field);
switch (filter.Operator.ToLower())
{
case "eq":
if (property != null)
{
if (typeof(int).IsAssignableFrom(property.PropertyType))
{
int val = 0;
if (filter.Field.ToLower() == "status" || filter.Field.ToLower() == "isactive")
{
if (filter.Value.Trim().ToLower() == "active")
{
val = 1;
parameters.Add(val);
return(string.Format("{0}={1}",
filter.Field, val));
}
else
{
if (filter.Value.Trim().ToLower() == "inactive")
{
val = 0;
}
parameters.Add(val);
return(string.Format("{0}={1}",
filter.Field, val));
}
}
}
else if (typeof(DateTime).IsAssignableFrom(property.PropertyType))
{
var val = Convert.ToDateTime(filter.Value.Trim()).ToString("MM/dd/yyyy");
parameters.Add(filter.Value.Trim());
return(string.Format("{0} = '{1}'",
filter.Field, val
));
}
}
return(string.Format("{0}='{1}'",
filter.Field, filter.Value));
case "neq":
if (typeof(DateTime).IsAssignableFrom(property.PropertyType))
{
var val = Convert.ToDateTime(filter.Value.Trim()).ToString("MM/dd/yyyy");
parameters.Add(filter.Value.Trim());
return(string.Format("{0} <> '{1}'",
filter.Field, val
));
}
parameters.Add(filter.Value.Trim());
return(string.Format("Lower({0}) != '" + "{1}'",
filter.Field,
filter.Value.Trim().ToLower()));
case "gte":
if (typeof(DateTime).IsAssignableFrom(property.PropertyType))
{
var val = Convert.ToDateTime(filter.Value.Trim()).ToString("MM/dd/yyyy");
parameters.Add(filter.Value.Trim());
return(string.Format("{0} >= '{1}'",
filter.Field, val
));
}
parameters.Add(filter.Value.Trim());
return(string.Format("{0} >= " + "{1}",
filter.Field,
filter.Value.Trim().ToLower()));
case "gt":
if (typeof(DateTime).IsAssignableFrom(property.PropertyType))
{
var val = Convert.ToDateTime(filter.Value.Trim()).ToString("MM/dd/yyyy");
parameters.Add(filter.Value.Trim());
return(string.Format("{0} > '{1}'",
filter.Field, val
));
}
parameters.Add(filter.Value.Trim());
return(string.Format("{0} > " + "{1}",
filter.Field,
filter.Value.Trim().ToLower()));
case "lte":
if (typeof(DateTime).IsAssignableFrom(property.PropertyType))
{
var val = Convert.ToDateTime(filter.Value.Trim()).ToString("MM/dd/yyyy");
parameters.Add(filter.Value.Trim());
return(string.Format("{0} <= '{1}'",
filter.Field, val
));
}
parameters.Add(filter.Value.Trim());
return(string.Format("{0} <= " + "{1}",
filter.Field,
filter.Value.Trim().ToLower()));
case "lt":
if (typeof(DateTime).IsAssignableFrom(property.PropertyType))
{
var val = Convert.ToDateTime(filter.Value.Trim()).ToString("MM/dd/yyyy");
parameters.Add(filter.Value.Trim());
return(string.Format("{0} < '{1}'",
filter.Field, val
));
}
if (typeof(int).IsAssignableFrom(property.PropertyType))
{
parameters.Add(int.Parse(filter.Value));
return(string.Format("{0}{1} {2}",
filter.Field,
ToLinqOperator(filter.Operator),
filter.Value.Trim().ToLower()));
}
parameters.Add(filter.Value);
return(string.Format("Lower({0}){1}'{2}'",
filter.Field,
ToLinqOperator(filter.Operator),
filter.Value.Trim().ToLower()));
case "startswith":
parameters.Add(filter.Value.Trim());
return(string.Format("Lower({0}) like '" + "{1}%'",
filter.Field,
filter.Value.Trim().ToLower()));
case "endswith":
parameters.Add(filter.Value.Trim());
return(string.Format("Lower({0}) like '%" + "{1}'",
filter.Field,
filter.Value.Trim()));
case "contains":
parameters.Add(filter.Value);
return(string.Format("Lower({0}) like '%" + "{1}%'",
filter.Field,
filter.Value.Trim().ToLower()));
case "doesnotcontain":
parameters.Add(filter.Value);
return(string.Format("Lower({0}) not like '%" + "{1}%'",
filter.Field,
filter.Value.Trim().ToLower()));
default:
throw new ArgumentException(
"This operator is not yet supported for this Grid",
filter.Operator);
}
}
public static string BuildWhereClause <T>(int index, string logic,
AzFilter.GridFilter filter, List <object> parameters)
{
var entityType = (typeof(T));
var property = entityType.GetProperty(filter.Field);
switch (filter.Operator.ToLower())
{
case "eq":
parameters.Add(filter.Value.Trim());
return(string.Format("Lower({0}) = '" + "{1}'",
filter.Field,
filter.Value.Trim().ToLower()));
case "neq":
parameters.Add(filter.Value.Trim());
return(string.Format("Lower({0}) != '" + "{1}'",
filter.Field,
filter.Value.Trim().ToLower()));
case "gte":
parameters.Add(filter.Value.Trim());
return(string.Format("{0} >= " + "{1}",
filter.Field,
filter.Value.Trim().ToLower()));
case "gt":
parameters.Add(filter.Value.Trim());
return(string.Format("{0} > " + "{1}",
filter.Field,
filter.Value.Trim().ToLower()));
case "lte":
parameters.Add(filter.Value.Trim());
return(string.Format("{0} <= " + "{1}",
filter.Field,
filter.Value.Trim().ToLower()));
case "lt":
if (typeof(DateTime).IsAssignableFrom(property.PropertyType))
{
parameters.Add(DateTime.Parse(filter.Value).Date);
return(string.Format("EntityFunctions.TruncateTime(Lower({0})){1}@{2}",
filter.Field,
ToLinqOperator(filter.Operator),
filter.Value.Trim().ToLower()));
}
if (typeof(int).IsAssignableFrom(property.PropertyType))
{
parameters.Add(int.Parse(filter.Value));
return(string.Format("{0}{1} {2}",
filter.Field,
ToLinqOperator(filter.Operator),
filter.Value.Trim().ToLower()));
}
parameters.Add(filter.Value);
return(string.Format("Lower({0}){1}'{2}'",
filter.Field,
ToLinqOperator(filter.Operator),
filter.Value.Trim().ToLower()));
case "startswith":
parameters.Add(filter.Value.Trim());
return(string.Format("Lower({0}) like '" + "{1}%'",
filter.Field,
filter.Value.Trim().ToLower()));
case "endswith":
parameters.Add(filter.Value.Trim());
return(string.Format("Lower({0}) like '%" + "{1}'",
filter.Field,
filter.Value.Trim()));
case "contains":
parameters.Add(filter.Value);
return(string.Format("Lower({0}) like '%" + "{1}%'",
filter.Field,
filter.Value.Trim().ToLower()));
case "doesnotcontain":
parameters.Add(filter.Value);
return(string.Format("Lower({0}) not like '%" + "{1}%'",
filter.Field,
filter.Value.Trim().ToLower()));
default:
throw new ArgumentException(
"This operator is not yet supported for this Grid",
filter.Operator);
}
}