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)
{
bool firstCriteria = String.IsNullOrEmpty(_viewFilter);
if (!(firstCriteria))
{
EnsureWhereKeyword(sb);
sb.AppendLine("(");
sb.Append(ProcessViewFilter(page, command, expressions));
}
if (page.Filter != null)
{
foreach (string filterExpression in page.Filter)
{
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;
DataField field = page.FindField(alias);
if (((field != null) && field.AllowQBE) && (page.DistinctValueFieldName != field.Name || (page.AllowDistinctFieldInFilter || page.CustomFilteredBy(field.Name))))
{
if (firstValue)
{
if (firstCriteria)
{
EnsureWhereKeyword(sb);
sb.AppendLine("(");
firstCriteria = false;
}
else
{
sb.Append("and ");
}
sb.Append("(");
firstValue = false;
}
else
{
sb.Append(fieldOperator);
}
if (operation == "~")
{
bool firstWord = true;
paramValue = Convert.ToString(StringToValue(paramValue));
foreach (string paramValueWord in paramValue.Split(' '))
{
string pv = paramValueWord.Trim();
DateTime paramValueAsDate;
bool paramValueIsDate = SqlStatement.TryParseDate(command.GetType(), pv, out paramValueAsDate);
bool firstTry = true;
DbParameter parameter = command.CreateParameter();
parameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count);
parameter.DbType = DbType.String;
command.Parameters.Add(parameter);
if (!(paramValueIsDate))
{
pv = SqlStatement.EscapePattern(command, pv);
}
if (!(pv.Contains("%")))
{
pv = String.Format("%{0}%", pv);
}
parameter.Value = pv;
if (firstWord)
{
firstWord = false;
}
else
{
sb.Append("and");
}
sb.Append("(");
foreach (DataField tf in page.Fields)
{
if (tf.AllowQBE && (!((tf.IsPrimaryKey && tf.Hidden)) && (!(tf.Type.StartsWith("Date")) || paramValueIsDate)))
{
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.String;
command.Parameters.Add(dateParameter);
dateParameter.Value = paramValueAsDate;
sb.AppendFormat("({0} = {1})", expressions[tf.ExpressionName()], dateParameter.ParameterName);
}
else
if (command.GetType().Name.Contains("Oracle"))
{
sb.AppendFormat("(upper({0}) like {1})", expressions[tf.ExpressionName()], parameter.ParameterName);
parameter.Value = Convert.ToString(parameter.Value).ToUpper();
}
else
{
sb.AppendFormat("({0} like {1})", expressions[tf.ExpressionName()], parameter.ParameterName);
}
}
}
sb.Append(")");
}
}
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);
}
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 (!(firstCriteria))
{
sb.AppendLine(")");
if (!(String.IsNullOrEmpty(whereClause)))
{
sb.Append("and ");
}
}
if (!(String.IsNullOrEmpty(whereClause)))
{
sb.AppendLine("(");
sb.AppendLine(whereClause);
sb.AppendLine(")");
}
}
public static string Replace(DbCommand command, SelectClauseDictionary expressions, string filter)
{
FilterFunctions functions = new FilterFunctions(command, expressions, filter);
return(functions.ToString());
}
