// Extensions
/// <summary>
/// Adds filters to the specified database query considering the specified filter query string.
/// </summary>
/// <param name="dbQuery">The database query to consider.</param>
/// <param name="filterQuery">The filter query string to consider.</param>
/// <param name="definition">The clause statement to consider.</param>
/// <param name="log">The log to consider.</param>
/// <returns>The built query.</returns>
public static IDbSingleQuery Filter(
this IDbSingleQuery dbQuery,
string filterQuery,
DbApiFilterDefinition definition = null,
IBdoLog log = null)
{
if (dbQuery != null && !string.IsNullOrEmpty(filterQuery))
{
string scriptText = filterQuery.ConvertToExtensionScript(log, definition);
if (scriptText?.Length > 0)
{
if (dbQuery.WhereClause?.Expression != null && !string.IsNullOrEmpty(dbQuery.WhereClause?.Expression?.Text))
{
dbQuery.WhereClause.Expression.Text = "$sqlAnd(" + dbQuery.WhereClause.Expression.Text + "," + scriptText + ")";
}
else
{
if (dbQuery.WhereClause == null)
{
dbQuery.WhereClause = new DbQueryWhereClause();
}
dbQuery.WhereClause.Expression = scriptText.CreateExpAsScript();
}
}
}
return(dbQuery);
}
/// <summary>
/// Creates an Api filter clause.
/// </summary>
/// <param name="fieldAlias">The field alias to consider.</param>
/// <param name="field">The field to consider.</param>
/// <param name="aOperator">The operator to consider.</param>
/// <param name="filterDefinition">The filter definition to consider.</param>
public static DbApiFilterClause CreateFilterClause(
string fieldAlias,
DbField field,
DataOperators aOperator,
DbApiFilterDefinition filterDefinition)
{
var clause = CreateFilterClause(fieldAlias, field, new[] { aOperator });
clause.FilterDefinition = filterDefinition;
return(clause);
}
// Filter definition
/// <summary>
/// Creates an Api filter definition.
/// </summary>
/// <param name="clauses">The clauses to consider.</param>
public static DbApiFilterDefinition CreateFilterDefinition(params DbApiFilterClause[] clauses)
{
var definition = new DbApiFilterDefinition();
foreach (DbApiFilterClause clause in clauses)
{
if (clause != null)
{
definition.Add(clause.FieldAlias, clause);
}
}
return(definition);
}
/// <summary>
/// Converts the specifed search query into an extension script.
/// </summary>
/// <param name="searchQuery">The search query to consider.</param>
/// <param name="log">The </param>
/// <param name="definition">The clause statement to consider.</param>
/// <param name="i"></param>
/// <returns></returns>
internal static string ConvertToExtensionScript(
this string searchQuery,
IBdoLog log = null,
DbApiFilterDefinition definition = null,
int i = 0)
{
string script = searchQuery;
if (!string.IsNullOrEmpty(script))
{
// boolean instructions
foreach (string instruction in new string[] { "Or", "And", "Not" })
{
int j = i;
List <string> clauses = new List <string>();
script.IndexOfNextString(" " + instruction + " ", ref j);
while (j < script.Length - 1)
{
string clause = script.Substring(i, j - i + 1);
clause = clause.ConvertToExtensionScript(log, definition, 0);
clauses.Add(clause);
j = i = j + (" " + instruction + " ").Length;
script.IndexOfNextString(" " + instruction + " ", ref j);
if (j == script.Length)
{
clause = script.Substring(i);
clause = clause.ConvertToExtensionScript(log, definition, 0);
clauses.Add(clause);
}
}
if (clauses.Count > 0)
{
script = "$sql" + instruction + "(" + clauses.Aggregate((p, q) => p + "," + q) + ")";
}
}
if (i == 0)
{
DataOperators aOperator = DataOperators.None;
int k = script.Length;
string scriptOperator = null;
foreach (DataOperators currentOperator in new DataOperators[] {
DataOperators.Exists,
DataOperators.Contains,
DataOperators.Different, DataOperators.Equal,
DataOperators.GreaterOrEqual, DataOperators.Greater,
DataOperators.LesserOrEqual, DataOperators.Lesser,
DataOperators.Has, DataOperators.In
})
{
int k1 = 0;
string currentScriptOperator = DbQueryExtension.GetInstruction(currentOperator);
script.IndexOfNextString(currentScriptOperator, ref k1);
if (k1 < k)
{
scriptOperator = currentScriptOperator;
aOperator = currentOperator;
k = k1;
}
}
if (k == script.Length)
{
log.AddError("No operator found in clause '" + searchQuery + "'", resultCode: "user");
}
else
{
string scriptFunction = DbQueryExtension.GetScriptFunction(aOperator)?.Trim();
string fieldName = script.Substring(0, k)?.Trim();
string value = script.Substring(k + scriptOperator.Length)?.Trim();
if (value.Length > 2 && value.StartsWith("'") && value.EndsWith("'"))
{
value = "$sqlText('" + value.Substring(1, value.Length - 2) + "')";
}
// check that the field is in the dictionary
if (!definition.ContainsKey(fieldName))
{
log.AddError("Undefined field '" + fieldName + "' in clause '" + searchQuery + "''", resultCode: "user");
}
else
{
DbApiFilterClause clause = definition?[fieldName];
// check the instruction found corresponds to the definition in dictionary
if (!clause.Operators.Any(p => p == aOperator))
{
log.AddError("Undefined operator '" + aOperator.ToString() + "' for field '" + fieldName + "'", resultCode: "user");
}
else
{
if (clause.Field == null)
{
clause.Field = DbFluent.Field(fieldName);
}
if (aOperator == DataOperators.Has)
{
if (value.Length > 2 && value.StartsWith("{") && value.EndsWith("}"))
{
value = value.Substring(1, value.Length - 2);
}
value = value.ConvertToExtensionScript(log, clause.FilterDefinition, 0);
script = "(" + value + ")";
}
else
{
script = scriptFunction + clause.Field;
}
}
}
}
}
}
return(script);
}
