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(")"); } }