// 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); }