/// <summary>
/// Append filter to a query checking for ignore case
/// </summary>
/// <param name="query">Where to add the filter</param>
/// <param name="op">Operation</param>
/// <param name="filter">Filter</param>
/// <param name="parName">Name of the parameter</param>
/// <param name="metadata">Field metadata</param>
private void AppendFilter(StringBuilder query, string op, RestFilter filter, string parName, FieldMetadata metadata)
{
if (query == null)
{
throw new ArgumentNullException(nameof(query));
}
if (string.IsNullOrEmpty(op))
{
throw new ArgumentNullException(nameof(query));
}
if (filter == null)
{
throw new ArgumentNullException(nameof(filter));
}
if (string.IsNullOrEmpty(parName))
{
throw new ArgumentNullException(nameof(parName));
}
if (metadata?.Type == FieldDataTypes.String &&
filter.IgnoreCase)
{
query.Append($" LOWER([{filter.Field}]) {op} LOWER({parName}) ");
}
else
{
query.Append($" [{filter.Field}] {op} {parName} ");
}
}
/// <summary>
/// Expression per il valore di string comparison
/// </summary>
/// <param name="filter">Filtro per cui si vuole il valore</param>
/// <returns></returns>
private ConstantExpression GetStringComparisonExpression(RestFilter filter)
{
if (filter == null)
{
throw new ArgumentNullException(nameof(filter));
}
if (filter.IgnoreCase)
{
return(Expression.Constant(StringComparison.InvariantCultureIgnoreCase));
}
return(Expression.Constant(StringComparison.InvariantCulture));
}
/// <summary>
/// Gets the where clause for the given filter
/// </summary>
/// <param name="filter">Filter to parse</param>
/// <param name="sqlParameters">Added parameter for filter</param>
/// <returns>Where clause, empty string if there are no filter</returns>
private string GetWhereClauseForFilter(RestFilter filter, List <SqlParameter> sqlParameters)
{
if (sqlParameters == null)
{
throw new ArgumentNullException(nameof(sqlParameters));
}
if (filter == null)
{
return(string.Empty);
}
var query = new StringBuilder();
AppendWhereClauseForFilter(filter, query, sqlParameters);
return(query.ToString());
}
private byte[] FilterRESTRequest(
RestHandlerOpCode opCode,
RestRequestParameters restInput,
out string responseProperties)
{
try
{
responseProperties = null;
IRESTRequestHandler restRequestHandler = _restServiceSOI.FindRequestHandlerDelegate <IRESTRequestHandler>();
if (restRequestHandler == null)
{
throw new RestErrorException("Service handler not found");
}
RestFilter restFilterOp = _restServiceSOI.GetFilter(opCode);
if (null != restFilterOp && null != restFilterOp.PreFilter)
{
restInput = restFilterOp.PreFilter(restInput);
}
byte[] response =
restRequestHandler.HandleRESTRequest(restInput.Capabilities, restInput.ResourceName, restInput.OperationName, restInput.OperationInput,
restInput.OutputFormat, restInput.RequestProperties, out responseProperties);
if (null == restFilterOp || null == restFilterOp.PostFilter)
{
return(response);
}
string newResponseProperties;
var newResponse = restFilterOp.PostFilter(restInput, response, responseProperties, out newResponseProperties);
responseProperties = newResponseProperties;
return(newResponse);
}
catch (RestErrorException restException)
{
// pre- or post- filters can throw restException with the error JSON output in the Message property.
// we catch them here and return JSON response.
responseProperties = "{\"Content-Type\":\"text/plain;charset=utf-8\"}";
//catch and return a JSON error from the pre- or postFilter.
return(System.Text.Encoding.UTF8.GetBytes(restException.Message));
}
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="restParam">Rest parameters</param>
/// <param name="metadata">Field metadata</param>
public VisionFilterApplier(RestParameters restParam, List <FieldMetadata> metadata)
{
if (restParam == null)
{
throw new ArgumentNullException(nameof(restParam));
}
if (metadata == null)
{
throw new ArgumentNullException(nameof(metadata));
}
Filter = restParam.Filter;
Metadata = metadata;
// If we have a smart filter, add it to common filter
if (restParam.SmartFilter != null)
{
var restSmartFilter = restParam.SmartFilter.ComposeFilter(metadata);
if (restSmartFilter != null)
{
if (Filter == null)
{
Filter = restSmartFilter;
}
else
{
Filter = new RestFilter
{
Logic = FilterLogics.And,
Filters = new List <RestFilter>
{
restParam.Filter,
restSmartFilter
}
};
}
}
}
}
/// <summary>
/// Composes the where clause for a simple filter
/// </summary>
/// <param name="filter">Filter</param>
/// <param name="query">String builder where to add the where clause</param>
/// <param name="sqlParameters">Added parameter for filter</param>
private void AppendWhereClauseForFilter(RestFilter filter, StringBuilder query, List <SqlParameter> sqlParameters)
{
if (filter == null)
{
return;
}
if (query == null)
{
throw new ArgumentNullException(nameof(query));
}
// filtro semplice
if (!string.IsNullOrEmpty(filter.Field))
{
AppendClauseForSimpleFilter(filter, query, sqlParameters);
}
else if (filter.Filters.Count > 0)
{
bool isFirst = true;
var filterCount = filter.Filters.Count;
for (int i = 0; i < filterCount; i++)
{
if (isFirst)
{
query.Append("(");
}
else
{
var andOr = filter.Logic.ToLowerInvariant() == FilterLogics.And ? "AND" : "OR";
query.Append($" {andOr} (");
}
isFirst = false;
var filterToElab = filter.Filters[i];
AppendWhereClauseForFilter(filterToElab, query, sqlParameters);
query.Append(")");
}
}
}
private Expression ComposeExpressionFilter(RestFilter filter, Type objectType, ParameterExpression objectParam)
{
if (filter == null)
{
throw new ArgumentNullException(nameof(filter));
}
if (objectType == null)
{
throw new ArgumentNullException(nameof(objectType));
}
if (objectParam == null)
{
throw new ArgumentNullException(nameof(objectParam));
}
// Complex filter
if (filter.Filters != null &&
filter.Filters.Any())
{
var leftFilter = filter.Filters[0];
var rightFilter = filter.Filters[1];
var leftExpression = ComposeExpressionFilter(leftFilter, objectType, objectParam);
var rightExpression = ComposeExpressionFilter(rightFilter, objectType, objectParam);
if (FilterLogics.And.Equals(filter.Logic, StringComparison.InvariantCultureIgnoreCase))
{
return(Expression.And(leftExpression, rightExpression));
}
return(Expression.Or(leftExpression, rightExpression));
}
// Simple filter
var fieldName = filter.Field;
var fieldValueAsExpressionParam = GetDynamciProperty(fieldName, objectParam);
var filterValueAsExpressionValue = ConvertData(Expression.Constant(filter.Value), fieldName);
var nullAsExpressionParam = Expression.Constant(null);
var emptyStringAsExpressionParam = Expression.Constant(string.Empty);
ConstantExpression stringComparison;
UnaryExpression fieldValueAsStringExpression;
switch (filter.Operator)
{
case FilterOperators.IsEqual:
return(Expression.Equal(fieldValueAsExpressionParam, filterValueAsExpressionValue));
case FilterOperators.IsNotEqual:
return(Expression.NotEqual(fieldValueAsExpressionParam, filterValueAsExpressionValue));
case FilterOperators.IsGreaterThan:
return(Expression.GreaterThan(fieldValueAsExpressionParam, filterValueAsExpressionValue));
case FilterOperators.IsGreatherThanOrEqual:
return(Expression.GreaterThanOrEqual(fieldValueAsExpressionParam, filterValueAsExpressionValue));
case FilterOperators.IsLessThan:
return(Expression.LessThan(fieldValueAsExpressionParam, filterValueAsExpressionValue));
case FilterOperators.IsLessThanOrEqual:
return(Expression.LessThanOrEqual(fieldValueAsExpressionParam, filterValueAsExpressionValue));
case FilterOperators.IsNull:
return(Expression.Equal(fieldValueAsExpressionParam, filterValueAsExpressionValue));
case FilterOperators.IsNotNull:
return(Expression.NotEqual(fieldValueAsExpressionParam, nullAsExpressionParam));
case FilterOperators.IsEmpty:
return(Expression.Equal(fieldValueAsExpressionParam, emptyStringAsExpressionParam));
case FilterOperators.IsNotEmpty:
return(Expression.NotEqual(fieldValueAsExpressionParam, emptyStringAsExpressionParam));
case FilterOperators.Contains:
case FilterOperators.DoesNotContain:
var containsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string), typeof(StringComparison) });
stringComparison = GetStringComparisonExpression(filter);
fieldValueAsStringExpression = GetStringValue(fieldValueAsExpressionParam);
var contains = Expression.Call(fieldValueAsStringExpression, containsMethod, filterValueAsExpressionValue, stringComparison);
if (filter.Operator.Equals(FilterOperators.DoesNotContain, StringComparison.InvariantCultureIgnoreCase))
{
return(Expression.Not(contains));
}
return(contains);
case FilterOperators.EndsWith:
var endsWithMethod = typeof(string).GetMethod("EndsWith", new[] { typeof(string), typeof(StringComparison) });
stringComparison = GetStringComparisonExpression(filter);
fieldValueAsStringExpression = GetStringValue(fieldValueAsExpressionParam);
var endsWith = Expression.Call(fieldValueAsStringExpression, endsWithMethod, filterValueAsExpressionValue, stringComparison);
return(endsWith);
case FilterOperators.StartsWith:
var startsWithMethod = typeof(string).GetMethod("StartsWith", new[] { typeof(string), typeof(StringComparison) });
stringComparison = GetStringComparisonExpression(filter);
fieldValueAsStringExpression = GetStringValue(fieldValueAsExpressionParam);
var startsWith = Expression.Call(fieldValueAsStringExpression, startsWithMethod, filterValueAsExpressionValue, stringComparison);
return(startsWith);
default:
throw new ApplicationException($"Operation '{filter.Operator}' not managed in Vision filter");
}
}
/// <summary>
/// Composes the where clause for a simple filter
/// </summary>
/// <param name="filter">Filter</param>
/// <param name="query">String builder where to add the where clause</param>
/// <param name="sqlParameters">Added parameter for filter</param>
private void AppendClauseForSimpleFilter(RestFilter filter, StringBuilder query, List <SqlParameter> sqlParameters)
{
if (filter == null)
{
return;
}
if (query == null)
{
throw new ArgumentNullException(nameof(query));
}
if (string.IsNullOrEmpty(filter.Field))
{
throw new ArgumentException("Field value is mandatory", nameof(filter.Field));
}
if (string.IsNullOrEmpty(filter.Operator))
{
throw new ArgumentException("Operato value is mandatory", nameof(filter.Operator));
}
var parName = $"@{sqlParameters.Count}";
// find the metadata
var metadata = EntityMetadata.Fields
.Where(x => string.Equals(x.Name, filter.Field, StringComparison.InvariantCultureIgnoreCase))
.SingleOrDefault();
switch (filter.Operator.ToLowerInvariant())
{
// Equal
case FilterOperators.IsEqual:
AppendFilter(query, "=", filter, parName, metadata);
sqlParameters.Add(new SqlParameter(parName, filter.Value));
break;
// Not equal
case FilterOperators.IsNotEqual:
AppendFilter(query, "<>", filter, parName, metadata);
sqlParameters.Add(new SqlParameter(parName, filter.Value));
break;
// Is null
case FilterOperators.IsNull:
query.Append($" [{filter.Field}] IS NULL ");
break;
// Is not null
case FilterOperators.IsNotNull:
query.Append($" [{filter.Field}] IS NOT NULL ");
break;
// Less than
case FilterOperators.IsLessThan:
AppendFilter(query, "<", filter, parName, metadata);
sqlParameters.Add(new SqlParameter(parName, filter.Value));
break;
// Less than or equal
case FilterOperators.IsLessThanOrEqual:
AppendFilter(query, "<=", filter, parName, metadata);
sqlParameters.Add(new SqlParameter(parName, filter.Value));
break;
// Greater than
case FilterOperators.IsGreaterThan:
AppendFilter(query, ">", filter, parName, metadata);
sqlParameters.Add(new SqlParameter(parName, filter.Value));
break;
// Greater than or equal
case FilterOperators.IsGreatherThanOrEqual:
AppendFilter(query, ">=", filter, parName, metadata);
sqlParameters.Add(new SqlParameter(parName, filter.Value));
break;
// Start with
case FilterOperators.StartsWith:
AppendFilter(query, "LIKE", filter, parName, metadata);
var startValue = $"{filter.Value}%";
sqlParameters.Add(new SqlParameter(parName, startValue));
break;
// End with
case FilterOperators.EndsWith:
AppendFilter(query, "LIKE", filter, parName, metadata);
var endValue = $"%{filter.Value}";
sqlParameters.Add(new SqlParameter(parName, endValue));
break;
// Contain
case FilterOperators.Contains:
AppendFilter(query, "LIKE", filter, parName, metadata);
var contValue = $"%{filter.Value}%";
sqlParameters.Add(new SqlParameter(parName, contValue));
break;
// Do not contain
case FilterOperators.DoesNotContain:
AppendFilter(query, "NOT LIKE", filter, parName, metadata);
var notContValue = $"%{filter.Value}%";
sqlParameters.Add(new SqlParameter(parName, notContValue));
break;
// Empty
case FilterOperators.IsEmpty:
query.Append($" [{filter.Field}] = '' ");
break;
// Not empty
case FilterOperators.IsNotEmpty:
query.Append($" [{filter.Field}] <> '' ");
break;
default:
throw new NotSupportedException($"Operator of type '{filter.Operator}' is not supported");
}
}
