void Flatten(StringBuilder sb, Search search)
{
search.LoadLayout(layout);
switch (search.Mode)
{
case SearchMode.None:
sb.Append("1=1");
return;
case SearchMode.In:
{
fieldNames.Include(search.FieldName);
var fieldName = storage.EscapeFieldName(search.FieldProperties);
sb.Append(fieldName);
sb.Append(" ");
if (search.Inverted)
{
sb.Append("NOT ");
}
sb.Append("IN (");
var i = 0;
foreach (var value in (Set <object>)search.FieldValue)
{
if (i++ > 0)
{
sb.Append(",");
}
var dbValue = storage.GetDatabaseValue(search.FieldProperties, value);
var parameter = AddParameter(dbValue);
sb.Append(parameter.Name);
}
sb.Append(")");
return;
}
case SearchMode.Equals:
{
fieldNames.Include(search.FieldName);
var fieldName = storage.EscapeFieldName(search.FieldProperties);
// is value null -> yes return "name IS [NOT] NULL"
if (search.FieldValue == null)
{
sb.Append($"{fieldName} IS {(search.Inverted ? "NOT " : string.Empty)}NULL");
}
else
{
// no add parameter and return querytext
var dbValue = storage.GetDatabaseValue(search.FieldProperties, search.FieldValue);
var parameter = AddParameter(dbValue);
sb.Append($"{fieldName} {(search.Inverted ? "<>" : "=")} {parameter.Name}");
}
break;
}
case SearchMode.Like:
{
fieldNames.Include(search.FieldName);
var fieldName = storage.EscapeFieldName(search.FieldProperties);
// is value null -> yes return "name IS [NOT] NULL"
if (search.FieldValue == null)
{
sb.Append($"{fieldName} IS {(search.Inverted ? "NOT " : string.Empty)}NULL");
}
else
{
// no add parameter and return querytext
var dbValue = storage.GetDatabaseValue(search.FieldProperties, search.FieldValue);
var parameter = AddParameter(dbValue);
sb.Append($"{fieldName} {(search.Inverted ? "NOT " : string.Empty)}LIKE {parameter.Name}");
}
break;
}
case SearchMode.Greater:
{
fieldNames.Include(search.FieldName);
var fieldName = storage.EscapeFieldName(search.FieldProperties);
var dbValue = storage.GetDatabaseValue(search.FieldProperties, search.FieldValue);
var parameter = AddParameter(dbValue);
sb.Append(search.Inverted ? $"{fieldName}<={parameter.Name}" : $"{fieldName}>{parameter.Name}");
break;
}
case SearchMode.GreaterOrEqual:
{
fieldNames.Include(search.FieldName);
var fieldName = storage.EscapeFieldName(search.FieldProperties);
var dbValue = storage.GetDatabaseValue(search.FieldProperties, search.FieldValue);
var parameter = AddParameter(dbValue);
sb.Append(search.Inverted ? $"{fieldName}<{parameter.Name}" : $"{fieldName}>={parameter.Name}");
break;
}
case SearchMode.Smaller:
{
fieldNames.Include(search.FieldName);
var name = storage.EscapeFieldName(search.FieldProperties);
var dbValue = storage.GetDatabaseValue(search.FieldProperties, search.FieldValue);
var parameter = AddParameter(dbValue);
sb.Append(search.Inverted ? $"{name}>={parameter.Name}" : $"{name}<{parameter.Name}");
break;
}
case SearchMode.SmallerOrEqual:
{
fieldNames.Include(search.FieldName);
var name = storage.EscapeFieldName(search.FieldProperties);
var dbValue = storage.GetDatabaseValue(search.FieldProperties, search.FieldValue);
var parameter = AddParameter(dbValue);
sb.Append(search.Inverted ? $"{name}>{parameter.Name}" : $"{name}<={parameter.Name}");
break;
}
case SearchMode.And:
{
if (search.Inverted)
{
sb.Append("NOT ");
}
sb.Append("(");
Flatten(sb, search.SearchA);
sb.Append(" AND ");
Flatten(sb, search.SearchB);
sb.Append(")");
break;
}
case SearchMode.Or:
{
if (search.Inverted)
{
sb.Append("NOT ");
}
sb.Append("(");
Flatten(sb, search.SearchA);
sb.Append(" OR ");
Flatten(sb, search.SearchB);
sb.Append(")");
break;
}
default: throw new NotImplementedException($"Mode {search.Mode} not implemented!");
}
}
