protected virtual void AppendFilterExpressionsToWhere(StringBuilder sb, ViewPage page, DbCommand command, SelectClauseDictionary expressions, string whereClause) { string quickFindHint = page.QuickFindHint; bool firstCriteria = String.IsNullOrEmpty(_viewFilter); if (!(firstCriteria)) { EnsureWhereKeyword(sb); sb.AppendLine("("); sb.Append(ProcessViewFilter(page, command, expressions)); } int matchListCount = 0; bool firstDoNotMatch = true; string logicalConcat = "and "; if (page.Filter != null) { foreach (string filterExpression in page.Filter) { Match matchingMode = MatchingModeRegex.Match(filterExpression); if (matchingMode.Success) { bool doNotMatch = (matchingMode.Groups["Match"].Value == "_donotmatch_"); if (doNotMatch) { if (firstDoNotMatch) { firstDoNotMatch = false; EnsureWhereKeyword(sb); if (!(firstCriteria)) { sb.AppendLine(")"); } if (matchListCount > 0) { sb.AppendLine(")"); } if (!(firstCriteria) || (matchListCount > 0)) { sb.AppendLine("and"); } matchListCount = 0; sb.AppendLine(" not"); firstCriteria = true; } } if (matchListCount == 0) { EnsureWhereKeyword(sb); if (!(firstCriteria)) { sb.Append(") and"); } // the list of matches begins sb.AppendLine("("); } else { sb.AppendLine(")"); sb.AppendLine("or"); } // begin a list of conditions for the next match if (matchingMode.Groups["Scope"].Value == "$all$") { logicalConcat = " and "; } else { logicalConcat = " or "; } matchListCount++; firstCriteria = true; } Match filterMatch = FilterExpressionRegex.Match(filterExpression); if (filterMatch.Success) { // "ProductName:?g", "CategoryCategoryName:=Condiments\x00=Seafood" bool firstValue = true; string fieldOperator = " or "; if (Regex.IsMatch(filterMatch.Groups["Values"].Value, ">|<")) { fieldOperator = " and "; } Match valueMatch = FilterValueRegex.Match(filterMatch.Groups["Values"].Value); while (valueMatch.Success) { string alias = filterMatch.Groups["Alias"].Value; string operation = valueMatch.Groups["Operation"].Value; string paramValue = valueMatch.Groups["Value"].Value; if ((operation == "~") && (alias == "_quickfind_")) { alias = page.Fields[0].Name; } bool deepSearching = alias.Contains(","); DataField field = page.FindField(alias); if (((((field != null) && field.AllowQBE) || (operation == "~")) && (((page.DistinctValueFieldName != field.Name || (matchListCount > 0)) || (operation == "~")) || (page.AllowDistinctFieldInFilter || page.CustomFilteredBy(field.Name)))) || deepSearching) { if (firstValue) { if (firstCriteria) { EnsureWhereKeyword(sb); sb.AppendLine("("); firstCriteria = false; } else { sb.Append(logicalConcat); } sb.Append("("); firstValue = false; } else { sb.Append(fieldOperator); } if (deepSearching) { string deepSearchFieldName = alias.Substring(0, alias.IndexOf(',')); string hint = alias.Substring((deepSearchFieldName.Length + 1)); string deepFilterExpression = (deepSearchFieldName + filterExpression.Substring(filterExpression.IndexOf(':'))); AppendDeepFilter(hint, page, command, sb, deepFilterExpression); } else if (operation == "~") { paramValue = Convert.ToString(StringToValue(paramValue)); List <string> words = new List <string>(); List <List <string> > phrases = new List <List <string> >(); phrases.Add(words); CultureInfo currentCulture = CultureInfo.CurrentCulture; string textDateNumber = ("\\p{L}\\d" + Regex.Escape((currentCulture.DateTimeFormat.DateSeparator + (currentCulture.DateTimeFormat.TimeSeparator + currentCulture.NumberFormat.NumberDecimalSeparator)))); string[] removableNumericCharacters = new string[] { currentCulture.NumberFormat.NumberGroupSeparator, currentCulture.NumberFormat.CurrencyGroupSeparator, currentCulture.NumberFormat.CurrencySymbol }; Match m = Regex.Match(paramValue, String.Format("\\s*(?\'Token\'((?\'Quote\'\")(?\'Value\'.+?)\")|((?\'Quote\'\\\')(?\'Value\'.+?)\\\')|(,|;|(^|\\s+" + ")-)|(?\'Value\'[{0}]+))", textDateNumber)); bool negativeSample = false; while (m.Success) { string token = m.Groups["Token"].Value.Trim(); if ((token == ",") || (token == ";")) { words = new List <string>(); phrases.Add(words); negativeSample = false; } else if (token == "-") { negativeSample = true; } else { string exactFlag = "="; if (String.IsNullOrEmpty(m.Groups["Quote"].Value)) { exactFlag = " "; } string negativeFlag = " "; if (negativeSample) { negativeFlag = "-"; negativeSample = false; } words.Add(String.Format("{0}{1}{2}", negativeFlag, exactFlag, m.Groups["Value"].Value)); } m = m.NextMatch(); } bool firstPhrase = true; foreach (List <String> phrase in phrases) { if (phrase.Count > 0) { if (firstPhrase) { firstPhrase = false; } else { sb.AppendLine("or"); } sb.AppendLine("("); bool firstWord = true; System.DateTime paramValueAsDate; foreach (string paramValueWord in phrase) { bool negativeFlag = (paramValueWord[0] == '-'); bool exactFlag = (paramValueWord[1] == '='); string comparisonOperator = "like"; if (exactFlag) { comparisonOperator = "="; } string pv = paramValueWord.Substring(2); bool paramValueIsDate = SqlStatement.TryParseDate(command.GetType(), pv, out paramValueAsDate); bool firstTry = true; DbParameter parameter = null; if (!(paramValueIsDate)) { pv = SqlStatement.EscapePattern(command, pv); } double paramValueAsNumber; string testNumber = pv; foreach (string s in removableNumericCharacters) { testNumber = testNumber.Replace(s, string.Empty); } bool paramValueIsNumber = double.TryParse(testNumber, out paramValueAsNumber); if (!(exactFlag) && !(pv.Contains("%"))) { pv = String.Format("%{0}%", pv); } if (firstWord) { firstWord = false; } else { sb.Append("and"); } if (negativeFlag) { sb.Append(" not"); } sb.Append("("); bool hasTests = false; DbParameter originalParameter = null; if (String.IsNullOrEmpty(quickFindHint) || !(quickFindHint.StartsWith(";"))) { foreach (DataField tf in page.Fields) { if ((tf.AllowQBE && String.IsNullOrEmpty(tf.AliasName)) && (!((tf.IsPrimaryKey && tf.Hidden)) && (!(tf.Type.StartsWith("Date")) || paramValueIsDate))) { hasTests = true; if ((parameter == null) || command.GetType().FullName.Contains("ManagedDataAccess")) { parameter = command.CreateParameter(); parameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); parameter.DbType = DbType.String; command.Parameters.Add(parameter); parameter.Value = pv; if (exactFlag && paramValueIsNumber) { parameter.DbType = DbType.Double; parameter.Value = paramValueAsNumber; } } if (!((exactFlag && ((!(tf.Type.Contains("String")) && !(paramValueIsNumber)) || (tf.Type.Contains("String") && paramValueIsNumber))))) { if (firstTry) { firstTry = false; } else { sb.Append(" or "); } if (tf.Type.StartsWith("Date")) { DbParameter dateParameter = command.CreateParameter(); dateParameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); dateParameter.DbType = DbType.DateTime; command.Parameters.Add(dateParameter); dateParameter.Value = paramValueAsDate; if (negativeFlag) { sb.AppendFormat("({0} is not null)and", expressions[tf.ExpressionName()]); } sb.AppendFormat("({0} = {1})", expressions[tf.ExpressionName()], dateParameter.ParameterName); } else { bool skipLike = false; if (!((comparisonOperator == "=")) && ((tf.Type == "String") && ((tf.Len > 0) && (tf.Len < pv.Length)))) { string pv2 = pv; pv2 = pv2.Substring(1); if (tf.Len < pv2.Length) { pv2 = pv2.Substring(0, (pv2.Length - 1)); } if (pv2.Length > tf.Len) { skipLike = true; } else { originalParameter = parameter; parameter = command.CreateParameter(); parameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); parameter.DbType = DbType.String; command.Parameters.Add(parameter); parameter.Value = pv2; } } if (_serverRules.EnableResultSet) { string fieldNameExpression = expressions[tf.ExpressionName()]; if ((tf.Type != "String" && !(exactFlag)) || (tf.Type == "Boolean")) { fieldNameExpression = String.Format("convert({0}, \'System.String\')", fieldNameExpression); } if (negativeFlag) { sb.AppendFormat("({0} is not null)and", fieldNameExpression); } sb.AppendFormat("({0} {2} {1})", fieldNameExpression, parameter.ParameterName, comparisonOperator); } else if (skipLike) { sb.Append("1=0"); } else { if (negativeFlag) { sb.AppendFormat("({0} is not null)and", expressions[tf.ExpressionName()]); } if (DatabaseEngineIs(command, "Oracle", "DB2", "Firebird")) { sb.AppendFormat("(upper({0}) {2} {1})", expressions[tf.ExpressionName()], parameter.ParameterName, comparisonOperator); parameter.Value = Convert.ToString(parameter.Value).ToUpper(); } else { sb.AppendFormat("({0} {2} {1})", expressions[tf.ExpressionName()], parameter.ParameterName, comparisonOperator); } } } } if (originalParameter != null) { parameter = originalParameter; originalParameter = null; } } } } if (!(String.IsNullOrEmpty(quickFindHint)) && quickFindHint.Contains(";")) { sb.AppendLine(); if (hasTests) { sb.AppendLine("or"); } else { hasTests = true; } sb.AppendLine("("); bool firstHint = true; foreach (string hint in quickFindHint.Substring((quickFindHint.IndexOf(';') + 1)).Split(new char[] { ';' })) { if (firstHint) { firstHint = false; } else { sb.AppendLine(); sb.AppendLine("or"); } sb.AppendLine("("); string newFilterExpression = filterExpression; StringBuilder reversedFilterExpression = new StringBuilder(); if (negativeFlag) { bool firstExpressionPhrase = true; foreach (List <string> ph in phrases) { if (firstExpressionPhrase) { firstExpressionPhrase = false; } else { reversedFilterExpression.Append(","); } bool firstExpressionWord = true; foreach (string w in ph) { if (firstExpressionWord) { firstExpressionWord = false; } else { reversedFilterExpression.Append(" "); } if (!((w[0] == '-'))) { reversedFilterExpression.Append("-"); } if (w[1] == '=') { reversedFilterExpression.Append("\""); } reversedFilterExpression.Append(w.Substring(2)); if (w[1] == '=') { reversedFilterExpression.Append("\""); } } } newFilterExpression = ("_quickfind_:~" + ValueToString(reversedFilterExpression.ToString())); } AppendDeepFilter(hint, page, command, sb, newFilterExpression); sb.AppendLine(")"); } sb.AppendLine(")"); } if (!(hasTests)) { if (negativeFlag && quickFindHint.StartsWith(";")) { sb.Append("1=1"); } else { sb.Append("1=0"); } } sb.Append(")"); } sb.AppendLine(")"); } } if (firstPhrase) { sb.Append("1=1"); } } else if (operation.StartsWith("$")) { sb.Append(FilterFunctions.Replace(command, expressions, String.Format("{0}({1}$comma${2})", operation.TrimEnd('$'), alias, Convert.ToBase64String(Encoding.UTF8.GetBytes(paramValue))))); } else { DbParameter parameter = command.CreateParameter(); parameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); AssignParameterDbType(parameter, field.Type); sb.Append(expressions[field.ExpressionName()]); bool requiresRangeAdjustment = ((operation == "=") && (field.Type.StartsWith("DateTime") && !(StringIsNull(paramValue)))); if ((operation == "<>") && StringIsNull(paramValue)) { sb.Append(" is not null "); } else if ((operation == "=") && StringIsNull(paramValue)) { sb.Append(" is null "); } else { if (operation == "*") { sb.Append(" like "); parameter.DbType = DbType.String; if (!(paramValue.Contains("%"))) { paramValue = (SqlStatement.EscapePattern(command, paramValue) + "%"); } } else if (requiresRangeAdjustment) { sb.Append(">="); } else { sb.Append(operation); } try { parameter.Value = StringToValue(field, paramValue); if ((parameter.DbType == DbType.Binary) && (parameter.Value is Guid)) { parameter.Value = ((Guid)(parameter.Value)).ToByteArray(); } } catch (Exception) { parameter.Value = DBNull.Value; } sb.Append(parameter.ParameterName); command.Parameters.Add(parameter); if (requiresRangeAdjustment) { DbParameter rangeParameter = command.CreateParameter(); AssignParameterDbType(rangeParameter, field.Type); rangeParameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); sb.Append(String.Format(" and {0} < {1}", expressions[field.ExpressionName()], rangeParameter.ParameterName)); if (field.Type == "DateTimeOffset") { DateTime dt = Convert.ToDateTime(parameter.Value); parameter.Value = new DateTimeOffset(dt).AddHours(-14); rangeParameter.Value = new DateTimeOffset(dt).AddDays(1).AddHours(14); } else { rangeParameter.Value = Convert.ToDateTime(parameter.Value).AddDays(1); } command.Parameters.Add(rangeParameter); } } } } valueMatch = valueMatch.NextMatch(); } if (!(firstValue)) { sb.AppendLine(")"); } } } } if (matchListCount > 0) { sb.AppendLine(")"); // the end of the "match" list sb.AppendLine(")"); firstCriteria = true; } if (!(firstCriteria)) { sb.AppendLine(")"); if (!(String.IsNullOrEmpty(whereClause))) { sb.Append("and "); } } if (!(String.IsNullOrEmpty(whereClause))) { sb.AppendLine("("); sb.AppendLine(whereClause); sb.AppendLine(")"); } }
protected virtual void AppendFilterExpressionsToWhere(StringBuilder sb, ViewPage page, DbCommand command, SelectClauseDictionary expressions, string whereClause) { string quickFindHint = page.QuickFindHint; bool firstCriteria = String.IsNullOrEmpty(_viewFilter); if (!(firstCriteria)) { EnsureWhereKeyword(sb); sb.AppendLine("("); sb.Append(ProcessViewFilter(page, command, expressions)); } int matchListCount = 0; bool firstDoNotMatch = true; string logicalConcat = "and "; if (page.Filter != null) foreach (string filterExpression in page.Filter) { Match matchingMode = MatchingModeRegex.Match(filterExpression); if (matchingMode.Success) { bool doNotMatch = (matchingMode.Groups["Match"].Value == "_donotmatch_"); if (doNotMatch) { if (firstDoNotMatch) { firstDoNotMatch = false; EnsureWhereKeyword(sb); if (!(firstCriteria)) sb.AppendLine(")"); if (matchListCount > 0) sb.AppendLine(")"); if (!(firstCriteria) || (matchListCount > 0)) sb.AppendLine("and"); matchListCount = 0; sb.AppendLine(" not"); firstCriteria = true; } } if (matchListCount == 0) { EnsureWhereKeyword(sb); if (!(firstCriteria)) sb.Append(") and"); // the list of matches begins sb.AppendLine("("); } else { sb.AppendLine(")"); sb.AppendLine("or"); } // begin a list of conditions for the next match if (matchingMode.Groups["Scope"].Value == "$all$") logicalConcat = " and "; else logicalConcat = " or "; matchListCount++; firstCriteria = true; } Match filterMatch = FilterExpressionRegex.Match(filterExpression); if (filterMatch.Success) { // "ProductName:?g", "CategoryCategoryName:=Condiments\x00=Seafood" bool firstValue = true; string fieldOperator = " or "; if (Regex.IsMatch(filterMatch.Groups["Values"].Value, ">|<")) fieldOperator = " and "; Match valueMatch = FilterValueRegex.Match(filterMatch.Groups["Values"].Value); while (valueMatch.Success) { string alias = filterMatch.Groups["Alias"].Value; string operation = valueMatch.Groups["Operation"].Value; string paramValue = valueMatch.Groups["Value"].Value; if ((operation == "~") && (alias == "_quickfind_")) alias = page.Fields[0].Name; bool deepSearching = alias.Contains(","); DataField field = page.FindField(alias); if (((((field != null) && field.AllowQBE) || (operation == "~")) && (((page.DistinctValueFieldName != field.Name || (matchListCount > 0)) || (operation == "~")) || (page.AllowDistinctFieldInFilter || page.CustomFilteredBy(field.Name)))) || deepSearching) { if (firstValue) { if (firstCriteria) { EnsureWhereKeyword(sb); sb.AppendLine("("); firstCriteria = false; } else sb.Append(logicalConcat); sb.Append("("); firstValue = false; } else sb.Append(fieldOperator); if (deepSearching) { string deepSearchFieldName = alias.Substring(0, alias.IndexOf(',')); string hint = alias.Substring((deepSearchFieldName.Length + 1)); string deepFilterExpression = (deepSearchFieldName + filterExpression.Substring(filterExpression.IndexOf(':'))); AppendDeepFilter(hint, page, command, sb, deepFilterExpression); } else if (operation == "~") { paramValue = Convert.ToString(StringToValue(paramValue)); List<string> words = new List<string>(); List<List<string>> phrases = new List<List<string>>(); phrases.Add(words); CultureInfo currentCulture = CultureInfo.CurrentCulture; string textDateNumber = ("\\p{L}\\d" + Regex.Escape((currentCulture.DateTimeFormat.DateSeparator + (currentCulture.DateTimeFormat.TimeSeparator + currentCulture.NumberFormat.NumberDecimalSeparator)))); string[] removableNumericCharacters = new string[] { currentCulture.NumberFormat.NumberGroupSeparator, currentCulture.NumberFormat.CurrencyGroupSeparator, currentCulture.NumberFormat.CurrencySymbol}; Match m = Regex.Match(paramValue, String.Format("\\s*(?\'Token\'((?\'Quote\'\")(?\'Value\'.+?)\")|((?\'Quote\'\\\')(?\'Value\'.+?)\\\')|(,|;|(^|\\s+" + ")-)|(?\'Value\'[{0}]+))", textDateNumber)); bool negativeSample = false; while (m.Success) { string token = m.Groups["Token"].Value.Trim(); if ((token == ",") || (token == ";")) { words = new List<string>(); phrases.Add(words); negativeSample = false; } else if (token == "-") negativeSample = true; else { string exactFlag = "="; if (String.IsNullOrEmpty(m.Groups["Quote"].Value)) exactFlag = " "; string negativeFlag = " "; if (negativeSample) { negativeFlag = "-"; negativeSample = false; } words.Add(String.Format("{0}{1}{2}", negativeFlag, exactFlag, m.Groups["Value"].Value)); } m = m.NextMatch(); } bool firstPhrase = true; foreach (List<String> phrase in phrases) if (phrase.Count > 0) { if (firstPhrase) firstPhrase = false; else sb.AppendLine("or"); sb.AppendLine("("); bool firstWord = true; System.DateTime paramValueAsDate; foreach (string paramValueWord in phrase) { bool negativeFlag = (paramValueWord[0] == '-'); bool exactFlag = (paramValueWord[1] == '='); string comparisonOperator = "like"; if (exactFlag) comparisonOperator = "="; string pv = paramValueWord.Substring(2); bool paramValueIsDate = SqlStatement.TryParseDate(command.GetType(), pv, out paramValueAsDate); bool firstTry = true; DbParameter parameter = null; if (!(paramValueIsDate)) pv = SqlStatement.EscapePattern(command, pv); double paramValueAsNumber; string testNumber = pv; foreach (string s in removableNumericCharacters) testNumber = testNumber.Replace(s, string.Empty); bool paramValueIsNumber = double.TryParse(testNumber, out paramValueAsNumber); if (!(exactFlag) && !(pv.Contains("%"))) pv = String.Format("%{0}%", pv); if (firstWord) firstWord = false; else sb.Append("and"); if (negativeFlag) sb.Append(" not"); sb.Append("("); bool hasTests = false; DbParameter originalParameter = null; if (String.IsNullOrEmpty(quickFindHint) || !(quickFindHint.StartsWith(";"))) foreach (DataField tf in page.Fields) if ((tf.AllowQBE && String.IsNullOrEmpty(tf.AliasName)) && (!((tf.IsPrimaryKey && tf.Hidden)) && (!(tf.Type.StartsWith("Date")) || paramValueIsDate))) { hasTests = true; if ((parameter == null) || command.GetType().FullName.Contains("ManagedDataAccess")) { parameter = command.CreateParameter(); parameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); parameter.DbType = DbType.String; command.Parameters.Add(parameter); parameter.Value = pv; if (exactFlag && paramValueIsNumber) { parameter.DbType = DbType.Double; parameter.Value = paramValueAsNumber; } } if (!((exactFlag && ((!(tf.Type.Contains("String")) && !(paramValueIsNumber)) || (tf.Type.Contains("String") && paramValueIsNumber))))) { if (firstTry) firstTry = false; else sb.Append(" or "); if (tf.Type.StartsWith("Date")) { DbParameter dateParameter = command.CreateParameter(); dateParameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); dateParameter.DbType = DbType.DateTime; command.Parameters.Add(dateParameter); dateParameter.Value = paramValueAsDate; if (negativeFlag) sb.AppendFormat("({0} is not null)and", expressions[tf.ExpressionName()]); sb.AppendFormat("({0} = {1})", expressions[tf.ExpressionName()], dateParameter.ParameterName); } else { bool skipLike = false; if (!((comparisonOperator == "=")) && ((tf.Type == "String") && ((tf.Len > 0) && (tf.Len < pv.Length)))) { string pv2 = pv; pv2 = pv2.Substring(1); if (tf.Len < pv2.Length) pv2 = pv2.Substring(0, (pv2.Length - 1)); if (pv2.Length > tf.Len) skipLike = true; else { originalParameter = parameter; parameter = command.CreateParameter(); parameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); parameter.DbType = DbType.String; command.Parameters.Add(parameter); parameter.Value = pv2; } } if (_serverRules.EnableResultSet) { string fieldNameExpression = expressions[tf.ExpressionName()]; if ((tf.Type != "String" && !(exactFlag)) || (tf.Type == "Boolean")) fieldNameExpression = String.Format("convert({0}, \'System.String\')", fieldNameExpression); if (negativeFlag) sb.AppendFormat("({0} is not null)and", fieldNameExpression); sb.AppendFormat("({0} {2} {1})", fieldNameExpression, parameter.ParameterName, comparisonOperator); } else if (skipLike) sb.Append("1=0"); else { if (negativeFlag) sb.AppendFormat("({0} is not null)and", expressions[tf.ExpressionName()]); if (DatabaseEngineIs(command, "Oracle", "DB2", "Firebird")) { sb.AppendFormat("(upper({0}) {2} {1})", expressions[tf.ExpressionName()], parameter.ParameterName, comparisonOperator); parameter.Value = Convert.ToString(parameter.Value).ToUpper(); } else sb.AppendFormat("({0} {2} {1})", expressions[tf.ExpressionName()], parameter.ParameterName, comparisonOperator); } } } if (originalParameter != null) { parameter = originalParameter; originalParameter = null; } } if (!(String.IsNullOrEmpty(quickFindHint)) && quickFindHint.Contains(";")) { sb.AppendLine(); if (hasTests) sb.AppendLine("or"); else hasTests = true; sb.AppendLine("("); bool firstHint = true; foreach (string hint in quickFindHint.Substring((quickFindHint.IndexOf(';') + 1)).Split(new char[] { ';'})) { if (firstHint) firstHint = false; else { sb.AppendLine(); sb.AppendLine("or"); } sb.AppendLine("("); string newFilterExpression = filterExpression; StringBuilder reversedFilterExpression = new StringBuilder(); if (negativeFlag) { bool firstExpressionPhrase = true; foreach (List<string> ph in phrases) { if (firstExpressionPhrase) firstExpressionPhrase = false; else reversedFilterExpression.Append(","); bool firstExpressionWord = true; foreach (string w in ph) { if (firstExpressionWord) firstExpressionWord = false; else reversedFilterExpression.Append(" "); if (!((w[0] == '-'))) reversedFilterExpression.Append("-"); if (w[1] == '=') reversedFilterExpression.Append("\""); reversedFilterExpression.Append(w.Substring(2)); if (w[1] == '=') reversedFilterExpression.Append("\""); } } newFilterExpression = ("_quickfind_:~" + ValueToString(reversedFilterExpression.ToString())); } AppendDeepFilter(hint, page, command, sb, newFilterExpression); sb.AppendLine(")"); } sb.AppendLine(")"); } if (!(hasTests)) if (negativeFlag && quickFindHint.StartsWith(";")) sb.Append("1=1"); else sb.Append("1=0"); sb.Append(")"); } sb.AppendLine(")"); } if (firstPhrase) sb.Append("1=1"); } else if (operation.StartsWith("$")) sb.Append(FilterFunctions.Replace(command, expressions, String.Format("{0}({1}$comma${2})", operation.TrimEnd('$'), alias, Convert.ToBase64String(Encoding.UTF8.GetBytes(paramValue))))); else { DbParameter parameter = command.CreateParameter(); parameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); AssignParameterDbType(parameter, field.Type); sb.Append(expressions[field.ExpressionName()]); bool requiresRangeAdjustment = ((operation == "=") && (field.Type.StartsWith("DateTime") && !(StringIsNull(paramValue)))); if ((operation == "<>") && StringIsNull(paramValue)) sb.Append(" is not null "); else if ((operation == "=") && StringIsNull(paramValue)) sb.Append(" is null "); else { if (operation == "*") { sb.Append(" like "); parameter.DbType = DbType.String; if (!(paramValue.Contains("%"))) paramValue = (SqlStatement.EscapePattern(command, paramValue) + "%"); } else if (requiresRangeAdjustment) sb.Append(">="); else sb.Append(operation); try { parameter.Value = StringToValue(field, paramValue); if ((parameter.DbType == DbType.Binary) && (parameter.Value is Guid)) parameter.Value = ((Guid)(parameter.Value)).ToByteArray(); } catch (Exception ) { parameter.Value = DBNull.Value; } sb.Append(parameter.ParameterName); command.Parameters.Add(parameter); if (requiresRangeAdjustment) { DbParameter rangeParameter = command.CreateParameter(); AssignParameterDbType(rangeParameter, field.Type); rangeParameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); sb.Append(String.Format(" and {0} < {1}", expressions[field.ExpressionName()], rangeParameter.ParameterName)); if (field.Type == "DateTimeOffset") { DateTime dt = Convert.ToDateTime(parameter.Value); parameter.Value = new DateTimeOffset(dt).AddHours(-14); rangeParameter.Value = new DateTimeOffset(dt).AddDays(1).AddHours(14); } else rangeParameter.Value = Convert.ToDateTime(parameter.Value).AddDays(1); command.Parameters.Add(rangeParameter); } } } } valueMatch = valueMatch.NextMatch(); } if (!(firstValue)) sb.AppendLine(")"); } } if (matchListCount > 0) { sb.AppendLine(")"); // the end of the "match" list sb.AppendLine(")"); firstCriteria = true; } if (!(firstCriteria)) { sb.AppendLine(")"); if (!(String.IsNullOrEmpty(whereClause))) sb.Append("and "); } if (!(String.IsNullOrEmpty(whereClause))) { sb.AppendLine("("); sb.AppendLine(whereClause); sb.AppendLine(")"); } }