private string DoReplaceKnownNames(Match m)
{
string sql = m.Groups["Sql"].Value;
if (!(String.IsNullOrEmpty(sql)))
{
return(sql);
}
if (!(String.IsNullOrEmpty(m.Groups["Other"].Value)))
{
return(m.Value);
}
if (!(String.IsNullOrEmpty(m.Groups["Parameter"].Value)))
{
return(AssignFilterParameterValue(m.Groups["Parameter"].Value));
}
else
if (!(String.IsNullOrEmpty(m.Groups["Function"].Value)))
{
return(FilterFunctions.Replace(_currentCommand, _currentExpressions, m.Groups["Function"].Value));
}
else
{
string s = null;
if (_currentExpressions.TryGetValue(m.Groups["Name"].Value, out s))
{
return(s);
}
}
return(m.Value);
}
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(")");
}
}