public override string ExpandWith(DbCommand command, SelectClauseDictionary expressions, string arguments)
{
DbParameter p = CreateParameter(command);
p.Value = Controller.StringToValue(SecondArgument(arguments));
DbParameter p2 = CreateParameter(command);
p2.Value = Controller.StringToValue(ThirdArgument(arguments));
if (expressions.ContainsKey("_DataView_RowFilter_"))
return String.Format("{0} >= {1} and {0} <= {2}", expressions[FirstArgument(arguments)], p.ParameterName, p2.ParameterName);
else
return String.Format("{0} between {1} and {2}", expressions[FirstArgument(arguments)], p.ParameterName, p2.ParameterName);
}
public override string ExpandWith(DbCommand command, SelectClauseDictionary expressions, string arguments)
{
StringBuilder sb = new StringBuilder();
int currentYear = DateTime.Today.Year;
for (int i = (currentYear - _startYear); (i <= (currentYear + _endYear)); i++)
{
DbParameter p = CreateParameter(command);
DbParameter p2 = CreateParameter(command);
DateTime startDate;
DateTime endDate;
AssignRange(i, out startDate, out endDate);
p.Value = startDate;
p2.Value = endDate;
if (sb.Length > 0)
sb.Append("or");
if (expressions.ContainsKey("_DataView_RowFilter_"))
sb.AppendFormat("({0} >= {1} and {0} <= {2})", expressions[FirstArgument(arguments)], p.ParameterName, p2.ParameterName);
else
sb.AppendFormat("({0} between {1} and {2})", expressions[FirstArgument(arguments)], p.ParameterName, p2.ParameterName);
}
return sb.ToString();
}
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(")");
}
}
private void AppendSelectExpressions(StringBuilder sb, ViewPage page, SelectClauseDictionary expressions, bool firstField)
{
foreach (DataField field in page.Fields)
{
if (firstField)
firstField = false;
else
sb.Append(",");
try
{
if (field.OnDemand)
sb.Append(String.Format("case when {0} is not null then 1 else null end as ", expressions[field.ExpressionName()]));
else
sb.Append(expressions[field.ExpressionName()]);
}
catch (Exception )
{
throw new Exception(String.Format("Unknown data field \'{0}\'.", field.Name));
}
sb.Append(" \"");
sb.Append(field.Name);
sb.AppendLine("\"");
}
}
private void AppendOrderByExpression(StringBuilder sb, ViewPage page, SelectClauseDictionary expressions, string orderByClause)
{
if (String.IsNullOrEmpty(page.SortExpression))
page.SortExpression = _view.GetAttribute("sortExpression", String.Empty);
bool hasOrderBy = false;
sb.Append("order by ");
if (String.IsNullOrEmpty(page.SortExpression))
{
if (!(String.IsNullOrEmpty(orderByClause)))
{
sb.Append(orderByClause);
hasOrderBy = true;
}
}
else
{
bool firstSortField = true;
Match orderByMatch = Regex.Match(page.SortExpression, "\\s*(?\'Alias\'[\\s\\w]+?)\\s*(?\'Order\'\\s(ASC|DESC))?\\s*(,|$)", RegexOptions.IgnoreCase);
while (orderByMatch.Success)
{
if (firstSortField)
firstSortField = false;
else
sb.Append(",");
string fieldName = orderByMatch.Groups["Alias"].Value;
if (fieldName.EndsWith("_Mirror"))
fieldName = fieldName.Substring(0, (fieldName.Length - 7));
sb.Append(expressions[fieldName]);
sb.Append(" ");
sb.Append(orderByMatch.Groups["Order"].Value);
orderByMatch = orderByMatch.NextMatch();
hasOrderBy = true;
}
}
bool firstKey = !(hasOrderBy);
foreach (DataField field in page.Fields)
if (field.IsPrimaryKey)
{
if (firstKey)
firstKey = false;
else
sb.Append(",");
sb.Append(expressions[field.ExpressionName()]);
}
if (firstKey)
sb.Append(expressions[page.Fields[0].ExpressionName()]);
}
void AppendAggregateExpressions(StringBuilder sb, ViewPage page, SelectClauseDictionary expressions)
{
bool firstField = true;
foreach (DataField field in page.Fields)
{
if (firstField)
firstField = false;
else
sb.Append(",");
if (field.Aggregate == DataFieldAggregate.None)
sb.Append("null ");
else
{
string functionName = field.Aggregate.ToString();
if (functionName == "Average")
functionName = "Avg";
string fmt = "{0}({1})";
if (functionName == "Count")
fmt = "{0}(distinct {1})";
sb.AppendFormat(fmt, functionName, expressions[field.ExpressionName()]);
}
sb.Append(" \"");
sb.Append(field.Name);
sb.AppendLine("\"");
}
}
protected virtual bool ConfigureCTE(StringBuilder sb, ViewPage page, DbCommand command, SelectClauseDictionary expressions, bool performCount)
{
if (!(RequiresHierarchy(page)))
return false;
// detect hierarchy
DataField primaryKeyField = null;
DataField parentField = null;
DataField sortField = null;
string sortOrder = "asc";
string hierarchyOrganization = HierarchyOrganizationFieldName;
foreach (DataField field in page.Fields)
{
if (field.IsPrimaryKey)
primaryKeyField = field;
if (field.IsTagged("hierarchy-parent"))
parentField = field;
else
if (field.IsTagged("hierarchy-organization"))
hierarchyOrganization = field.Name;
}
if (parentField == null)
return false;
// select a hierarchy sort field
if (sortField == null)
{
if (!(String.IsNullOrEmpty(page.SortExpression)))
{
Match sortExpression = Regex.Match(page.SortExpression, "(?\'FieldName\'\\w+)(\\s+(?\'SortOrder\'asc|desc)?)", RegexOptions.IgnoreCase);
if (sortExpression.Success)
foreach (DataField field in page.Fields)
if (field.Name == sortExpression.Groups["FieldName"].Value)
{
sortField = field;
sortOrder = sortExpression.Groups["SortOrder"].Value;
break;
}
}
if (sortField == null)
foreach (DataField field in page.Fields)
if (!(field.Hidden))
{
sortField = field;
break;
}
}
if (sortField == null)
sortField = page.Fields[0];
// append a hierarchical CTE
bool isOracle = DatabaseEngineIs(command, "Oracle");
sb.AppendLine("),");
sb.AppendLine("h__(");
bool first = true;
foreach (DataField field in page.Fields)
{
if (first)
first = false;
else
sb.Append(",");
sb.AppendFormat("{0}{1}{2}", _leftQuote, field.Name, _rightQuote);
sb.AppendLine();
}
sb.AppendFormat(",{0}{1}{2}", _leftQuote, hierarchyOrganization, _rightQuote);
sb.AppendLine(")as(");
// top-level of self-referring CTE
sb.AppendLine("select");
first = true;
foreach (DataField field in page.Fields)
{
if (first)
first = false;
else
sb.Append(",");
sb.AppendFormat("h1__.{0}{1}{2}", _leftQuote, field.Name, _rightQuote);
sb.AppendLine();
}
// add top-level hierarchy organization field
if (isOracle)
sb.AppendFormat(",lpad(cast(row_number() over (partition by h1__.{0}{1}{2} order by h1__.{0}{3}{2}" +
" {4}) as varchar(5)), 5, \'0\') as {0}{5}{2}", _leftQuote, parentField.Name, _rightQuote, sortField.Name, sortOrder, hierarchyOrganization);
else
sb.AppendFormat(",cast(right(\'0000\' + cast(row_number() over (partition by h1__.{0}{1}{2} order by" +
" h1__.{0}{3}{2} {4}) as varchar), 4) as varchar) as {0}{5}{2}", _leftQuote, parentField.Name, _rightQuote, sortField.Name, sortOrder, hierarchyOrganization);
// add top-level "from" clause
sb.AppendLine();
sb.AppendFormat("from page_cte__ h1__ where h1__.{0}{1}{2} is null ", _leftQuote, parentField.Name, _rightQuote);
sb.AppendLine();
sb.AppendLine("union all");
// sublevel of self-referring CTE
sb.AppendLine("select");
first = true;
foreach (DataField field in page.Fields)
{
if (first)
first = false;
else
sb.Append(",");
sb.AppendFormat("h2__.{0}{1}{2}", _leftQuote, field.Name, _rightQuote);
sb.AppendLine();
}
// add sublevel hierarchy organization field
if (isOracle)
sb.AppendFormat(",h__.{0}{5}{2} || \'/\' || lpad(cast(row_number() over (partition by h2__.{0}{1}{2}" +
" order by h2__.{0}{3}{2} {4}) as varchar(5)), 5, \'0\') as {0}{5}{2}", _leftQuote, parentField.Name, _rightQuote, sortField.Name, sortOrder, hierarchyOrganization);
else
sb.AppendFormat(",convert(varchar, h__.{0}{5}{2} + \'/\' + cast(right(\'0000\' + cast(row_number() ove" +
"r (partition by h2__.{0}{1}{2} order by h2__.{0}{3}{2} {4}) as varchar), 4) as v" +
"archar)) as {0}{5}{2}", _leftQuote, parentField.Name, _rightQuote, sortField.Name, sortOrder, hierarchyOrganization);
sb.AppendLine();
// add sublevel "from" clause
sb.AppendFormat("from page_cte__ h2__ inner join h__ on h2__.{0}{1}{2} = h__.{0}{3}{2}", _leftQuote, parentField.Name, _rightQuote, primaryKeyField.Name);
sb.AppendLine();
sb.AppendLine("),");
sb.AppendFormat("ho__ as (select row_number() over (order by ({0}{1}{2})) as row_number__, h__.* f" +
"rom h__)", _leftQuote, hierarchyOrganization, _rightQuote);
if (performCount)
sb.AppendLine("select count(*) from ho__");
else
sb.AppendLine("select * from ho__");
sb.AppendLine();
return true;
}
private void ProcessExpressionParameters(DbCommand command, SelectClauseDictionary expressions)
{
foreach (string fieldName in expressions.Keys)
{
this._currentCommand = command;
string formula = expressions[fieldName];
Match m = ParamDetectionRegex.Match(formula);
if (m.Success)
AssignFilterParameterValue(m.Groups[3].Value);
}
}
private void EnsurePageFields(ViewPage page, SelectClauseDictionary expressions)
{
XPathNavigator statusBar = _config.SelectSingleNode("/c:dataController/c:statusBar");
if (statusBar != null)
page.StatusBar = statusBar.Value;
if (page.Fields.Count == 0)
{
XPathNodeIterator fieldIterator = _config.Select("/c:dataController/c:fields/c:field");
while (fieldIterator.MoveNext())
{
string fieldName = fieldIterator.Current.GetAttribute("name", String.Empty);
if (expressions.ContainsKey(fieldName))
page.Fields.Add(new DataField(fieldIterator.Current, Resolver));
}
}
XPathNodeIterator keyFieldIterator = _config.Select("/c:dataController/c:fields/c:field[@isPrimaryKey=\'true\' or @hidden=\'true\']");
while (keyFieldIterator.MoveNext())
{
string fieldName = keyFieldIterator.Current.GetAttribute("name", String.Empty);
if (!(page.ContainsField(fieldName)))
page.Fields.Add(new DataField(keyFieldIterator.Current, Resolver, true));
}
XPathNodeIterator aliasIterator = _view.Select(".//c:dataFields/c:dataField/@aliasFieldName", Resolver);
while (aliasIterator.MoveNext())
if (!(page.ContainsField(aliasIterator.Current.Value)))
{
XPathNodeIterator fieldIterator = _config.Select("/c:dataController/c:fields/c:field[@name=\'{0}\']", aliasIterator.Current.Value);
if (fieldIterator.MoveNext())
page.Fields.Add(new DataField(fieldIterator.Current, Resolver, true));
}
XPathNodeIterator lookupFieldIterator = _config.Select("/c:dataController/c:fields/c:field[c:items/@dataController]");
while (lookupFieldIterator.MoveNext())
{
string fieldName = lookupFieldIterator.Current.GetAttribute("name", String.Empty);
if (!(page.ContainsField(fieldName)))
page.Fields.Add(new DataField(lookupFieldIterator.Current, Resolver, true));
}
int i = 0;
while (i < page.Fields.Count)
{
DataField field = page.Fields[i];
if ((!(field.FormatOnClient) && !(String.IsNullOrEmpty(field.DataFormatString))) && !(field.IsMirror))
{
page.Fields.Insert((i + 1), new DataField(field));
i = (i + 2);
}
else
i++;
}
XPathNodeIterator dynamicConfigIterator = _config.Select("/c:dataController/c:fields/c:field[c:configuration!=\'\']/c:configuration|/c:dataCo" +
"ntroller/c:fields/c:field/c:items[@copy!=\'\']/@copy");
while (dynamicConfigIterator.MoveNext())
{
Match dynamicConfig = Regex.Match(dynamicConfigIterator.Current.Value, "(\\w+)=(\\w+)");
while (dynamicConfig.Success)
{
int groupIndex = 2;
if (dynamicConfigIterator.Current.Name == "copy")
groupIndex = 1;
if (!(page.ContainsField(dynamicConfig.Groups[groupIndex].Value)))
{
XPathNavigator nav = _config.SelectSingleNode("/c:dataController/c:fields/c:field[@name=\'{0}\']", dynamicConfig.Groups[1].Value);
if (nav != null)
page.Fields.Add(new DataField(nav, Resolver, true));
}
dynamicConfig = dynamicConfig.NextMatch();
}
if (page.InTransaction)
{
XPathNodeIterator globalFieldIterator = _config.Select("/c:dataController/c:fields/c:field");
while (globalFieldIterator.MoveNext())
{
string fieldName = globalFieldIterator.Current.GetAttribute("name", String.Empty);
if (!(page.ContainsField(fieldName)))
page.Fields.Add(new DataField(globalFieldIterator.Current, Resolver, true));
}
}
}
foreach (DataField field in page.Fields)
ConfigureDataField(page, field);
}
public override string ExpandWith(DbCommand command, SelectClauseDictionary expressions, string arguments)
{
return String.Format("not({0})", base.ExpandWith(command, expressions, arguments));
}
public override string ExpandWith(DbCommand command, SelectClauseDictionary expressions, string arguments)
{
string fieldExpression = expressions[FirstArgument(arguments)];
StringBuilder sb = new StringBuilder(fieldExpression);
sb.Append(" in (");
string[] list = SecondArgument(arguments).Split(new string[] {
"$or$"}, StringSplitOptions.RemoveEmptyEntries);
bool hasNull = false;
bool hasValues = false;
foreach (string v in list)
if (Controller.StringIsNull(v))
hasNull = true;
else
{
if (hasValues)
sb.Append(",");
else
hasValues = true;
DbParameter p = CreateParameter(command);
p.Value = Controller.StringToValue(v);
sb.Append(p.ParameterName);
}
sb.Append(")");
if (hasNull)
if (hasValues)
return String.Format("({0} is null) or ({1})", fieldExpression, sb.ToString());
else
return String.Format("{0} is null", fieldExpression);
else
return sb.ToString();
}
public static string Replace(DbCommand command, SelectClauseDictionary expressions, string filter)
{
FilterFunctions functions = new FilterFunctions(command, expressions, filter);
return functions.ToString();
}
public FilterFunctions(DbCommand command, SelectClauseDictionary expressions, string filter)
{
this._command = command;
this._filter = filter;
this._expressions = expressions;
}
public virtual string ExpandWith(DbCommand command, SelectClauseDictionary expressions, string arguments)
{
return String.Empty;
}
public override string ExpandWith(DbCommand command, SelectClauseDictionary expressions, string arguments)
{
DbParameter p = CreateParameter(command);
p.Value = DBNull.Value;
if (PageRequest.Current != null)
{
string parameterName = Regex.Match(arguments, "\\w+").Value;
foreach (FieldValue v in PageRequest.Current.ExternalFilter)
if (v.Name.Equals(parameterName, StringComparison.InvariantCultureIgnoreCase))
{
p.Value = v.Value;
break;
}
}
return p.ParameterName;
}
private void ConfigureCommandForSelect(DbCommand command, ViewPage page, SelectClauseDictionary expressions, string fromClause, string whereClause, string orderByClause, CommandConfigurationType commandConfiguration)
{
bool useServerPaging = ((commandConfiguration != CommandConfigurationType.SelectDistinct && !(_serverRules.EnableResultSet)) && (commandConfiguration != CommandConfigurationType.SelectAggregates && commandConfiguration != CommandConfigurationType.SelectFirstLetters));
bool useLimit = SupportsLimitInSelect(command);
bool useSkip = SupportsSkipInSelect(command);
if (useServerPaging)
page.AcceptAllRows();
StringBuilder sb = new StringBuilder();
if (useLimit || useSkip)
useServerPaging = false;
bool countUsingHierarchy = false;
if ((commandConfiguration == CommandConfigurationType.SelectCount) && (useServerPaging && RequiresHierarchy(page)))
{
countUsingHierarchy = true;
commandConfiguration = CommandConfigurationType.Select;
}
if (commandConfiguration == CommandConfigurationType.SelectExisting)
useServerPaging = false;
if (commandConfiguration == CommandConfigurationType.SelectCount)
sb.AppendLine("select count(*)");
else
{
if (useServerPaging)
sb.AppendLine("with page_cte__ as (");
else
if ((commandConfiguration == CommandConfigurationType.Sync) && useLimit)
sb.Append("select * from (select @row_num := @row_num+1 row_number__,cte__.* from (select @r" +
"ow_num:=0) r,(");
sb.AppendLine("select");
if (useServerPaging)
AppendRowNumberExpression(sb, page, expressions, orderByClause);
if (commandConfiguration == CommandConfigurationType.SelectDistinct)
{
DataField distinctField = page.FindField(page.DistinctValueFieldName);
string distinctExpression = expressions[distinctField.ExpressionName()];
if (distinctField.Type.StartsWith("Date"))
{
string commandType = command.GetType().ToString();
if (commandType == "System.Data.SqlClient.SqlCommand")
distinctExpression = String.Format("DATEADD(dd, 0, DATEDIFF(dd, 0, {0}))", distinctExpression);
if (commandType == "MySql.Data.MySqlClient.MySqlCommand")
distinctExpression = String.Format("cast({0} as date)", distinctExpression);
}
sb.AppendFormat("distinct {0} \"{1}\"\r\n", distinctExpression, page.DistinctValueFieldName);
}
else
if (commandConfiguration == CommandConfigurationType.SelectAggregates)
AppendAggregateExpressions(sb, page, expressions);
else
if (commandConfiguration == CommandConfigurationType.SelectFirstLetters)
{
string substringFunction = "substring";
if (DatabaseEngineIs(command, "Oracle", "DB2"))
substringFunction = "substr";
AppendFirstLetterExpressions(sb, page, expressions, substringFunction);
}
else
{
if ((commandConfiguration == CommandConfigurationType.Select) && useSkip)
sb.AppendFormat(" first {0} skip {1}\r\n", page.PageSize, (page.PageSize * page.PageIndex));
if ((commandConfiguration == CommandConfigurationType.Sync) && useSkip)
{
// Only select the primary key.
foreach (DataField field in page.Fields)
if (field.IsPrimaryKey)
{
sb.Append(expressions[field.ExpressionName()]);
break;
}
}
else
if (commandConfiguration == CommandConfigurationType.SelectExisting)
sb.AppendLine("*");
else
AppendSelectExpressions(sb, page, expressions, !(useServerPaging));
}
}
sb.AppendLine("from");
sb.AppendLine(fromClause);
_hasWhere = false;
if (String.IsNullOrEmpty(_viewFilter))
{
_viewFilter = _view.GetAttribute("filter", String.Empty);
if (String.IsNullOrEmpty(_viewFilter) && ((_viewType == "Form") && !(String.IsNullOrEmpty(page.LastView))))
{
XPathNavigator lastView = _config.SelectSingleNode("/c:dataController/c:views/c:view[@id=\'{0}\']", page.LastView);
if (lastView != null)
_viewFilter = lastView.GetAttribute("filter", String.Empty);
}
}
if (!(String.IsNullOrEmpty(_viewFilter)))
_viewFilter = String.Format("({0})", _viewFilter);
if (commandConfiguration == CommandConfigurationType.SelectExisting)
{
EnsureWhereKeyword(sb);
sb.Append(expressions[page.InnerJoinForeignKey.ToLower()]);
sb.Append("=");
sb.Append(page.InnerJoinPrimaryKey);
sb.AppendLine(" and ");
}
AppendSystemFilter(command, page, expressions);
AppendAccessControlRules(command, page, expressions);
if (((page.Filter != null) && (page.Filter.Length > 0)) || !(String.IsNullOrEmpty(_viewFilter)))
AppendFilterExpressionsToWhere(sb, page, command, expressions, whereClause);
else
if (!(String.IsNullOrEmpty(whereClause)))
{
EnsureWhereKeyword(sb);
sb.AppendLine(whereClause);
}
if (commandConfiguration == CommandConfigurationType.Select)
{
bool preFetch = RequiresPreFetching(page);
if (useServerPaging)
{
if (!(ConfigureCTE(sb, page, command, expressions, countUsingHierarchy)))
sb.Append(")\r\nselect * from page_cte__ ");
if (!(countUsingHierarchy))
{
sb.AppendFormat("where row_number__ > {0}PageRangeFirstRowNumber and row_number__ <= {0}PageRangeL" +
"astRowNumber order by row_number__", _parameterMarker);
DbParameter p = command.CreateParameter();
p.ParameterName = (_parameterMarker + "PageRangeFirstRowNumber");
p.Value = ((page.PageSize * page.PageIndex)
+ page.PageOffset);
if (preFetch)
p.Value = (((int)(p.Value)) - page.PageSize);
command.Parameters.Add(p);
DbParameter p2 = command.CreateParameter();
p2.ParameterName = (_parameterMarker + "PageRangeLastRowNumber");
p2.Value = ((page.PageSize
* (page.PageIndex + 1))
+ page.PageOffset);
if (preFetch)
p2.Value = (((int)(p2.Value)) + page.PageSize);
command.Parameters.Add(p2);
}
}
else
{
AppendOrderByExpression(sb, page, expressions, orderByClause);
if (useLimit)
{
sb.AppendFormat("\r\nlimit {0}Limit_PageOffset, {0}Limit_PageSize", _parameterMarker);
DbParameter p = command.CreateParameter();
p.ParameterName = (_parameterMarker + "Limit_PageOffset");
p.Value = ((page.PageSize * page.PageIndex)
+ page.PageOffset);
if (preFetch && (((int)(p.Value)) > page.PageSize))
p.Value = (((int)(p.Value)) - page.PageSize);
command.Parameters.Add(p);
DbParameter p2 = command.CreateParameter();
p2.ParameterName = (_parameterMarker + "Limit_PageSize");
p2.Value = page.PageSize;
if (preFetch)
{
int pagesToFetch = 2;
if (((int)(p.Value)) > page.PageSize)
pagesToFetch = 3;
p2.Value = (page.PageSize * pagesToFetch);
}
command.Parameters.Add(p2);
}
}
}
else
if (commandConfiguration == CommandConfigurationType.Sync)
{
if (useServerPaging)
{
if (!(ConfigureCTE(sb, page, command, expressions, false)))
sb.Append(")\r\nselect * from page_cte__ ");
sb.Append("where ");
}
else
{
if (useLimit || useSkip)
AppendOrderByExpression(sb, page, expressions, orderByClause);
if (!(useSkip))
sb.Append(") cte__)cte2__ where ");
}
bool first = true;
if (!(useSkip))
foreach (DataField field in page.Fields)
if (field.IsPrimaryKey)
{
if (first)
first = false;
else
sb.AppendFormat(" and ");
sb.AppendFormat("{2}{1}{3}={0}PrimaryKey_{1}", _parameterMarker, field.Name, _leftQuote, _rightQuote);
}
}
else
if ((commandConfiguration == CommandConfigurationType.SelectDistinct) || (commandConfiguration == CommandConfigurationType.SelectFirstLetters))
sb.Append("order by 1");
command.CommandText = sb.ToString();
_viewFilter = null;
}
private bool ConfigureCommandForUpdate(DbCommand command, ViewPage page, SelectClauseDictionary expressions, string tableName, FieldValue[] values)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("update {0} set ", tableName);
bool firstField = true;
foreach (FieldValue v in values)
{
DataField field = page.FindField(v.Name);
if ((field != null) && v.Modified)
{
sb.AppendLine();
if (firstField)
firstField = false;
else
sb.Append(",");
sb.AppendFormat(RemoveTableAliasFromExpression(expressions[v.Name]));
if ((v.NewValue == null) && field.HasDefaultValue)
sb.Append(String.Format("={0}", field.DefaultValue));
else
{
sb.AppendFormat("={0}p{1}", _parameterMarker, command.Parameters.Count);
DbParameter parameter = command.CreateParameter();
parameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count);
AssignParameterValue(parameter, field.Type, v.NewValue);
command.Parameters.Add(parameter);
}
}
}
if (firstField)
return false;
AppendWhereExpressions(sb, command, page, expressions, values);
command.CommandText = sb.ToString();
return true;
}
public override string ExpandWith(DbCommand command, SelectClauseDictionary expressions, string arguments)
{
string[] list = SecondArgument(arguments).Split(new string[] {
"$or$"}, StringSplitOptions.RemoveEmptyEntries);
string filter = String.Format("not({0})", base.ExpandWith(command, expressions, arguments));
if (Array.IndexOf(list, "null") == -1)
filter = String.Format("({0}) or {1} is null", filter, expressions[FirstArgument(arguments)]);
return filter;
}
private SelectClauseDictionary ParseSelectExpressions(string selectClause)
{
SelectClauseDictionary expressions = new SelectClauseDictionary();
Match fieldMatch = SelectExpressionRegex.Match(selectClause);
while (fieldMatch.Success)
{
string expression = fieldMatch.Groups["Expression"].Value;
string fieldName = fieldMatch.Groups["FieldName"].Value;
string alias = fieldMatch.Groups["Alias"].Value;
if (!(String.IsNullOrEmpty(expression)))
{
if (String.IsNullOrEmpty(alias))
if (String.IsNullOrEmpty(fieldName))
alias = expression;
else
alias = fieldName;
if (!(expressions.ContainsKey(alias)))
expressions.Add(alias, expression);
}
fieldMatch = fieldMatch.NextMatch();
}
return expressions;
}
public override string ExpandWith(DbCommand command, SelectClauseDictionary expressions, string arguments)
{
DbParameter p = CreateParameter(command);
p.Value = String.Format(_pattern, SqlStatement.EscapePattern(command, Convert.ToString(Controller.StringToValue(SecondArgument(arguments)))));
return String.Format("{0} like {1}", expressions[FirstArgument(arguments)], p.ParameterName);
}
protected virtual bool ConfigureCommand(DbCommand command, ViewPage page, CommandConfigurationType commandConfiguration, FieldValue[] values)
{
if (page == null)
page = new ViewPage();
PopulatePageFields(page);
if (command == null)
return true;
if (command.CommandType == CommandType.Text)
{
Match statementMatch = SqlSelectRegex1.Match(command.CommandText);
if (!(statementMatch.Success))
statementMatch = SqlSelectRegex2.Match(command.CommandText);
SelectClauseDictionary expressions = _expressions;
if (expressions == null)
{
expressions = ParseSelectExpressions(statementMatch.Groups["Select"].Value);
_expressions = expressions;
}
EnsurePageFields(page, expressions);
string commandId = _view.GetAttribute("commandId", String.Empty);
bool commandIsCustom = ((_config.SelectSingleNode("/c:dataController/c:commands/c:command[@id=\'{0}\' and @custom=\'true\']", commandId) != null) || page.RequiresResultSet(commandConfiguration));
AddComputedExpressions(expressions, page, commandConfiguration, commandIsCustom);
if (statementMatch.Success)
{
string fromClause = statementMatch.Groups["From"].Value;
string whereClause = statementMatch.Groups["Where"].Value;
string orderByClause = statementMatch.Groups["OrderBy"].Value;
if (commandIsCustom)
{
fromClause = String.Format("({0}) resultset__", command.CommandText);
whereClause = String.Empty;
orderByClause = String.Empty;
}
string tableName = null;
if (!(commandConfiguration.ToString().StartsWith("Select")))
tableName = ((string)(_config.Evaluate("string(/c:dataController/c:commands/c:command[@id=\'{0}\']/@tableName)", commandId)));
if (String.IsNullOrEmpty(tableName))
tableName = TableNameRegex.Match(fromClause).Groups["Table"].Value;
if (commandConfiguration == CommandConfigurationType.Update)
return ConfigureCommandForUpdate(command, page, expressions, tableName, values);
else
if (commandConfiguration == CommandConfigurationType.Insert)
return ConfigureCommandForInsert(command, page, expressions, tableName, values);
else
if (commandConfiguration == CommandConfigurationType.Delete)
return ConfigureCommandForDelete(command, page, expressions, tableName, values);
else
{
ConfigureCommandForSelect(command, page, expressions, fromClause, whereClause, orderByClause, commandConfiguration);
ProcessExpressionParameters(command, expressions);
}
}
else
if ((commandConfiguration == CommandConfigurationType.Select) && YieldsSingleRow(command))
{
StringBuilder sb = new StringBuilder();
sb.Append("select ");
AppendSelectExpressions(sb, page, expressions, true);
command.CommandText = sb.ToString();
}
return commandConfiguration != CommandConfigurationType.None;
}
return (command.CommandType == CommandType.StoredProcedure);
}
public override string ExpandWith(DbCommand command, SelectClauseDictionary expressions, string arguments)
{
DbParameter p = CreateParameter(command);
p.Value = true;
return String.Format("{0}={1}", expressions[FirstArgument(arguments)], p.ParameterName);
}
private void AddComputedExpressions(SelectClauseDictionary expressions, ViewPage page, CommandConfigurationType commandConfiguration, bool generateFormula)
{
bool useFormulaAsIs = ((commandConfiguration == CommandConfigurationType.Insert) || (commandConfiguration == CommandConfigurationType.Update));
foreach (DataField field in page.Fields)
if (!(String.IsNullOrEmpty(field.Formula)))
if (useFormulaAsIs)
expressions[field.ExpressionName()] = field.Formula;
else
expressions[field.ExpressionName()] = String.Format("({0})", field.Formula);
else
if (generateFormula)
if (useFormulaAsIs)
expressions[field.ExpressionName()] = field.Name;
else
expressions[field.ExpressionName()] = String.Format("({0})", field.Name);
}
public override string ExpandWith(DbCommand command, SelectClauseDictionary expressions, string arguments)
{
DbParameter p = CreateParameter(command);
p.Value = Membership.GetUser().ProviderUserKey;
if (String.IsNullOrEmpty(arguments))
return p.ParameterName;
return String.Format("{0}={1}", arguments, p.ParameterName);
}
private void AppendFirstLetterExpressions(StringBuilder sb, ViewPage page, SelectClauseDictionary expressions, string substringFunction)
{
foreach (DataField field in page.Fields)
if ((!(field.Hidden) && field.AllowQBE) && (field.Type == "String"))
{
string fieldName = field.AliasName;
if (String.IsNullOrEmpty(fieldName))
fieldName = field.Name;
sb.AppendFormat("distinct {1}({0},1,1) first_letter__\r\n", expressions[fieldName], substringFunction);
page.FirstLetters = fieldName;
page.RemoveFromFilter(fieldName);
break;
}
}
public override string ExpandWith(DbCommand command, SelectClauseDictionary expressions, string arguments)
{
DbParameter p = CreateParameter(command);
p.Value = HttpContext.Current.User.Identity.Name;
if (String.IsNullOrEmpty(arguments))
return p.ParameterName;
return String.Format("{0}={1}", arguments, p.ParameterName);
}
private void AppendRowNumberExpression(StringBuilder sb, ViewPage page, SelectClauseDictionary expressions, string orderByClause)
{
sb.Append("row_number() over (");
AppendOrderByExpression(sb, page, expressions, orderByClause);
sb.AppendLine(") as row_number__");
}
private bool ConfigureCommandForDelete(DbCommand command, ViewPage page, SelectClauseDictionary expressions, string tableName, FieldValue[] values)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("delete from {0}", tableName);
AppendWhereExpressions(sb, command, page, expressions, values);
command.CommandText = sb.ToString();
return true;
}
DbDataReader ExecuteResultSetReader(ViewPage page)
{
if (_serverRules.ResultSet == null)
return null;
SelectClauseDictionary expressions = new SelectClauseDictionary();
foreach (DataColumn c in _serverRules.ResultSet.Columns)
expressions[c.ColumnName] = c.ColumnName;
if (page.Fields.Count == 0)
{
PopulatePageFields(page);
EnsurePageFields(page, null);
}
DataView resultView = new DataView(_serverRules.ResultSet);
resultView.Sort = page.SortExpression;
using (DbConnection connection = CreateConnection(false))
{
DbCommand command = connection.CreateCommand();
StringBuilder sb = new StringBuilder();
_resultSetParameters = command.Parameters;
expressions.Add("_DataView_RowFilter_", "true");
AppendFilterExpressionsToWhere(sb, page, command, expressions, String.Empty);
string filter = sb.ToString();
if (filter.StartsWith("where"))
filter = filter.Substring(5);
filter = Regex.Replace(filter, (Regex.Escape(_parameterMarker) + "\\w+"), DoReplaceResultSetParameter);
resultView.RowFilter = filter;
if (page.PageSize > 0)
page.TotalRowCount = resultView.Count;
}
if (RequiresPreFetching(page))
page.ResetSkipCount(true);
return resultView.ToTable().CreateDataReader();
}
private string ProcessViewFilter(ViewPage page, DbCommand command, SelectClauseDictionary expressions)
{
_currentCommand = command;
_currentExpressions = expressions;
string filter = Regex.Replace(_viewFilter, "/\\*Sql\\*/(?\'Sql\'[\\s\\S]+)/\\*Sql\\*/|(?\'Parameter\'(@|:)\\w+)|(?\'Other\'(\"|\'|\\[|`)\\s*\\w" +
"+)|(?\'Function\'\\$\\w+\\s*\\((?\'Arguments\'[\\S\\s]*?)\\))|(?\'Name\'\\w+)", DoReplaceKnownNames);
return filter;
}