/// <summary>
/// Converts a list of FilterLine objects to a WHERE clause usable in a SQL select query.</summary>
/// <param name="lines">
/// List of FilterLine objects to convert to a WHERE clause.</param>
/// <param name="filterFields">
/// Collection of FilterField objects to determine field types and check filterability of fields.
/// This list is usually the filter field options list that is sent to a filter panel object.
/// If this list is specified, for a filter to be considered valid, its fieldname must be
/// found in the list. If the list is given null, field name is looked up in fieldExpressions,
/// query or row and field type is determined by the field object in the row.</param>
/// <param name="fieldExpressions">
/// An optional dictionary of field expressions. If a field's corresponding SQL expression like
/// "join.field_name" is not determinable by reading in the query or the row, this list must be specified.
/// When specified, this list takes priority over the query and row objects to determine the field
/// expression.</param>
/// <param name="query">
/// An optional SqlSelect query to determine field expressions. If a field's expression is not found
/// in "fieldExpressions" dictionary, it is looked up in query object.</param>
/// <param name="row">
/// An optional Row object to determine field types and expressions.</param>
/// <param name="process">
/// An optional delegate to preprocess a filter line and return a filter. It should return an empty
/// filter if this line should be ignored. Null if this line should be processed as usual.</param>
/// <returns>
/// WHERE clause.</returns>
/// <remarks>
/// Invalid filter lines are simply skipped, no error occurs.</remarks>
public static string ToWhereString(SqlQuery query, IEnumerable <FilterLine> lines, FilterFields filterFields,
IDictionary <string, string> fieldExpressions = null, Row row = null, Func <FilterLine, BaseCriteria> process = null)
{
if (lines == null)
{
throw new ArgumentNullException("lines");
}
const string AND = " AND ";
const string OR = " OR ";
const string LPAREN = "(";
const string RPAREN = ")";
//const string TRUE = "1=1";
// build a dictionary of FilterField objects if the list is specified
// this list is usually the filter field options list that is sent to FilterPanel object.
/*Dictionary<string, FilterField> filterFieldDict = null;
* if (filterFields != null)
* {
* filterFieldDict = new Dictionary<string, FilterField>(StringComparer.OrdinalIgnoreCase);
* foreach (FilterField f in filterFields)
* {
* filterFieldDict[f.GetName()] = f;
*
* if (f.GetTextual() != null)
* filterFieldDict[f.GetTextual()] = f;
* }
* }*/
bool inParens = false;
bool hasOr = false;
StringBuilder sb = new StringBuilder();
foreach (FilterLine line in lines)
{
if (inParens &&
(line._rightParen || line._leftParen))
{
sb.Append(RPAREN);
inParens = false;
}
if (sb.Length > 0)
{
sb.Append(line._or ? OR : AND);
if (line._or)
{
hasOr = true;
}
}
if (line._leftParen)
{
sb.Append(LPAREN);
inParens = true;
}
if (!line.IsValid)
{
throw new ArgumentOutOfRangeException("InvalidFilterLine", line.ToJson());
//sb.Append(TRUE);
//continue;
}
if (process != null)
{
var filter = process(line);
if (!Object.ReferenceEquals(filter, null))
{
if (filter.IsEmpty)
{
//sb.Append(TRUE);
throw new ArgumentOutOfRangeException("EmptyFilterLine", line.ToJson());
}
else
{
sb.Append(filter.ToStringIgnoreParams()); // FIX!!!!
}
continue;
}
}
string fieldName = line.Field;
// if filter fields list is specified, the fieldname must exist in this list, otherwise
// it may be an hacking attempt, as user tries to filter a field that he is not
// represented with
IFilterField filterField = null;
if (filterFields != null)
{
filterField = filterFields.ByNameOrTextual(fieldName);
if (filterField == null)
{
throw new ArgumentOutOfRangeException("UnknownFilterField", line.ToJson());
}
//sb.Append(TRUE);
//continue;
}
Field field = null;
if (row != null)
{
field = row.FindField(fieldName);
}
/*// by default, suppose fields are string typed
* FilterFieldType type = FilterFieldType.String;
* // if given, determine field type by looking up in the filter field options
* if (filterField != null)
* type = filterField.GetType();
* // otherwise, determine field type by the class of field object
* else if (field != null)
* type = FilterField.ToFilterFieldType(field);*/
// to determine field expression, first look it up in fieldExpressions dictionary
string fieldExpr;
if (fieldExpressions == null ||
!fieldExpressions.TryGetValue(fieldName, out fieldExpr))
{
fieldExpr = null;
}
if (fieldExpr == null)
{
if (field != null)
{
fieldExpr = field.Expression;
}
else if (query != null)
{
fieldExpr = query.GetExpression(fieldName);
}
}
if (fieldExpr == null)
{
// field is not found anywhere, don't allow unknown fields as it may cause a script injection
// attack or other types of security threats!
//sb.Append(TRUE);
throw new ArgumentOutOfRangeException("UnknownFilterField", line.ToJson());
//continue;
}
bool isNumeric =
(filterField != null && (filterField.Handler == "Integer" || filterField.Handler == "Decimal")) ||
(filterField == null && field != null &&
(field is Int16Field || field is Int32Field || field is Int64Field || field is DoubleField || field is DecimalField));
// determine expression for this filter by operator type
FilterOp op = line.Op;
string sqlOp = SqlConditionOperators[(int)(op)];
if (op == FilterOp.IN)
{
StringBuilder vs = new StringBuilder();
var values = line.Values;
if (line.Values != null)
{
foreach (var s in line.Values)
{
if (isNumeric)
{ // parse invariant decimal value for integer and float fields
decimal d;
if (Decimal.TryParse(s, NumberStyles.Float, Invariants.NumberFormat, out d))
{
if (vs.Length > 0)
{
vs.Append(",");
}
vs.Append(d.ToInvariant());
}
}
else
{
if (vs.Length > 0)
{
vs.Append(",");
}
vs.Append(s.ToSql());
}
}
}
if (vs.Length == 0)
{
//sb.Append(TRUE);
throw new ArgumentOutOfRangeException("InvalidFilterLine", line.ToJson());
}
else
{
sb.AppendFormat(sqlOp, fieldExpr, vs.ToString());
}
continue;
}
// operator needs value if not one of "true", "false", "is null", "is not null"
if (op.IsNeedsValue())
{
// starts with and contains operators requires special care, as their sql expressions
// already contains single quotes, so .ToSql() cannot be used
if (op.IsLike())
{
if (line.Value != null)
{
sb.AppendFormat(SqlConditionOperators[(int)(op)],
fieldExpr, line.Value.Replace("'", "''"));
}
continue;
}
// parse value1 and value2
string value1 = "";
string value2 = "";
// simple loop to parse value1 and value2 in one turn
for (int phase = 0; phase <= 1; phase++)
{
string valueText;
if (phase == 0)
{
valueText = line.Value;
}
else
{
valueText = line.Value2;
}
valueText = valueText.TrimToNull();
// value must be entered
if (valueText == null)
{
throw new ArgumentOutOfRangeException("InvalidFilterLine", line.ToJson());
}
bool isDateTime =
(filterField != null && (filterField.Handler == "Date")) ||
(filterField == null && field != null &&
(field is DateTimeField));
if (isNumeric)
{ // parse invariant decimal value for integer and float fields
decimal d;
if (!Decimal.TryParse(valueText, NumberStyles.Float, Invariants.NumberFormat, out d))
{
throw new ArgumentOutOfRangeException("InvalidFilterLine", line.ToJson());
}
valueText = d.ToInvariant();
}
else if (isDateTime)
{ // parse iso date-time string
DateTime d;
if (!DateHelper.TryParseISO8601DateTime(valueText, out d))
{
throw new ArgumentOutOfRangeException("InvalidFilterLine", line.ToJson());
}
DateTimeKind kind = DateTimeKind.Unspecified;
object dateKindObj;
if (filterField != null && filterField.Params != null && filterField.Params.TryGetValue("DateKind", out dateKindObj))
{
var dateKind = ((DateFilterKind)Convert.ToInt32(dateKindObj));
kind = dateKind == DateFilterKind.DateTimeLocal ? DateTimeKind.Local : (dateKind == DateFilterKind.DateTimeUTC ? DateTimeKind.Utc : DateTimeKind.Unspecified);
}
else if (field != null && field is DateTimeField)
{
kind = ((DateTimeField)field).DateTimeKind;
}
d = DateTimeField.ToDateTimeKind(d, kind);
if (op == FilterOp.BW)
{
if (phase == 1)
{
sqlOp = "{0} >= {1} AND {0} < {2}";
d = d.AddDays(1);
}
}
else if (phase == 0)
{
if (op == FilterOp.NE || op == FilterOp.EQ)
{
value1 = d.ToSql();
value2 = d.AddDays(1).ToSql();
if (op == FilterOp.NE)
{
sqlOp = "NOT ({0} >= {1} AND {0} < {2})";
}
else
{
sqlOp = "{0} >= {1} AND {0} < {2}";
}
op = FilterOp.BW;
break;
}
else
{
if (op == FilterOp.GT)
{
op = FilterOp.GE;
d = d.AddDays(1);
}
else if (op == FilterOp.LE)
{
op = FilterOp.LT;
d = d.AddDays(1);
}
}
}
valueText = d.ToSql();
}
else // convert simple string value to sql string (duplicate quotes)
{
valueText = valueText.ToSql();
}
if (phase == 0)
{
value1 = valueText;
}
else
{
value2 = valueText;
}
// use second phase to parse value2 if operator is BW
if (op != FilterOp.BW)
{
break;
}
}
// format sql operator text with values
if (op == FilterOp.BW)
{
sb.AppendFormat(sqlOp, fieldExpr, value1, value2);
}
else
{
sb.AppendFormat(sqlOp, fieldExpr, value1);
}
}
else
{
sb.AppendFormat(sqlOp, fieldExpr);
}
}
if (inParens)
{
sb.Append(RPAREN);
}
if (hasOr)
{
sb.Append(RPAREN);
sb.Insert(0, LPAREN);
}
return(sb.ToString());
}
