private SelectColumnBase ParseColumn(List <string> wordList, List <SqlStatementError> errors) { var startWord = wordList[0]; var ascTokenIndex = 0; var descTokenIndex = wordList.Count - 1; var nextToken = wordList.Count > 1 ? wordList[++ascTokenIndex] : null; if (nextToken == "(") { var functionName = startWord; var functionLabel = ""; if (wordList[descTokenIndex] == ")") { functionLabel = startWord; descTokenIndex--; } else { functionLabel = wordList[descTokenIndex]; descTokenIndex--; if (wordList[descTokenIndex].NotIn(")", "as")) { throw new Exception($"Unexpected token after function '{wordList[wordList.Count - 2]}'"); } if (wordList[descTokenIndex] == "as") { descTokenIndex--; } if (wordList[descTokenIndex] == ")") { descTokenIndex--; } } var functionColumn = new SelectColumnFunction(functionName, functionLabel); var argList = wordList.GetRange(2, descTokenIndex - 1); var functionArgs = ParserUtils.SplitTokens(argList); foreach (var functionArg in functionArgs) { functionColumn.Args.Add(ParseColumn(functionArg, errors)); } return(functionColumn); } double number = 0; SelectColumnBase selectColumn = null; if (startWord.IsQuoted() || (double.TryParse(startWord, out number))) { object value = startWord.IsQuoted() ? startWord.CleanRaw() : number.ToString(); var valueType = startWord.IsQuoted() ? typeof(string) : typeof(double); if (startWord.IsQuoted() == false && startWord.IndexOf(".", StringComparison.Ordinal) < 0) { valueType = typeof(int); value = int.Parse(startWord); } if (wordList.Count > 3) { errors.Add(new SqlStatementError("Unknown tokens", 0)); return(null); } if (wordList.Count == 3) { if (wordList[1] != "as") { errors.Add(new SqlStatementError("Expecting 'As' keyword", 0)); return(null); } selectColumn = new RawSelectColumn(wordList[2], value) { ValueType = valueType }; } else if (wordList.Count == 2) { selectColumn = new RawSelectColumn(wordList[1], value) { ValueType = valueType }; } else { selectColumn = new RawSelectColumn(null, value) { ValueType = valueType }; } return(selectColumn); } if (char.IsLetter(startWord[0])) { if (wordList.Count > 3) { errors.Add(new SqlStatementError("Unknown tokens", 0)); return(null); } if (wordList.Count == 3) { if (wordList[1] != "as") { errors.Add(new SqlStatementError("Expecting 'As' keyword", 0)); return(null); } selectColumn = new FieldSelectColumn(wordList[2], startWord); } else if (wordList.Count == 2) { selectColumn = new FieldSelectColumn(wordList[1], startWord); } else { selectColumn = new FieldSelectColumn(null, startWord); } return(selectColumn); } throw new Exception("Unexpected function arguments."); }
public DataTable Execute(SelectStatement statement) { var metadata = new MetadataManager(CRMInstance); ValidateStatement(statement); var entityName = ((SelectFrom)statement.From.First()).Name; var query = new QueryExpression(entityName); if (statement.Columns.Any()) { query.ColumnSet = new ColumnSet(false); // Until caching layer is ready we will retrieve fields every time var entityFields = metadata.GetEntityFields(entityName); if (entityFields == null) { throw new Exception($"Unable to read entity '{entityName}' fields."); } if (statement.Columns.Any(x => x.Type.In(SelectColumnTypeEnum.All, SelectColumnTypeEnum.System))) { for (int i = 0; i < statement.Columns.Count; i++) { var column = statement.Columns[i]; if (column.Type == SelectColumnTypeEnum.All) { foreach (var entityField in entityFields) { var fieldSelectColumn = new FieldSelectColumn(entityField.SchemaName); statement.Columns.Insert(i++, fieldSelectColumn); } } else if (column.Type == SelectColumnTypeEnum.System) { foreach (var fieldName in Configuration.Settings.SystemSelectFields) { if (fieldName.IsEmpty()) { throw new Exception($"'{fieldName}' not set."); } if (string.Equals(fieldName, "$id", StringComparison.OrdinalIgnoreCase)) { var idEntityField = entityFields.Single(x => x.IsPrimaryId); var fieldSelectColumn = new FieldSelectColumn(idEntityField.SchemaName); statement.Columns.Insert(i++, fieldSelectColumn); } else if (string.Equals(fieldName, "$name", StringComparison.OrdinalIgnoreCase)) { var nameEntityField = entityFields.Single(x => x.IsPrimaryName); var fieldSelectColumn = new FieldSelectColumn(nameEntityField.SchemaName); statement.Columns.Insert(i++, fieldSelectColumn); } else if (fieldName.StartsWith("$")) { throw new Exception($"System field '{fieldName}' not supported."); } else { var fieldSelectColumn = new FieldSelectColumn(fieldName); statement.Columns.Insert(i++, fieldSelectColumn); } } } } } for (int i = 0; i < statement.Columns.Count; i++) { var column = statement.Columns[i]; if (column.Type == SelectColumnTypeEnum.Field) { var fieldColumn = (FieldSelectColumn)column; query.ColumnSet.AddColumn(fieldColumn.Name); } else if (column.Type == SelectColumnTypeEnum.InnerSelect) { throw new Exception("Inner selects are currently unsupported."); } else if (column.Type == SelectColumnTypeEnum.Function) { throw new Exception("Columns with functions are currently unsupported."); } } } else { query.ColumnSet = new ColumnSet(true); } if (statement.Top.HasValue) { query.TopCount = statement.Top.Value; } if (statement.Where.Any()) { for (var i = 0; i < statement.Where.Count; i++) { var whereClause = statement.Where[i]; if (whereClause.Type == SelectWhereTypeEnum.Comparison) { var comparisonClause = (SelectWhereComparison)whereClause; var conditionName = GetWhereClauseAttributeName(comparisonClause.LeftExpression); var conditionValue = GetWhereClauseAttributeName(comparisonClause.RightExpression); var conditionOperator = ConditionOperator.Equal; switch (comparisonClause.Operator) { case SelectWhereComparisonOperatorEnum.Equal: conditionOperator = comparisonClause.Negate ? ConditionOperator.NotEqual : ConditionOperator.Equal; break; case SelectWhereComparisonOperatorEnum.NotEqual: conditionOperator = comparisonClause.Negate ? ConditionOperator.Equal : ConditionOperator.NotEqual; break; case SelectWhereComparisonOperatorEnum.LessThan: conditionOperator = comparisonClause.Negate ? ConditionOperator.GreaterEqual : ConditionOperator.LessThan; break; case SelectWhereComparisonOperatorEnum.LessEqualThan: conditionOperator = comparisonClause.Negate ? ConditionOperator.GreaterThan : ConditionOperator.LessEqual; break; case SelectWhereComparisonOperatorEnum.GreaterThan: conditionOperator = comparisonClause.Negate ? ConditionOperator.LessEqual : ConditionOperator.GreaterThan; break; case SelectWhereComparisonOperatorEnum.GreaterEqualThan: conditionOperator = comparisonClause.Negate ? ConditionOperator.LessThan : ConditionOperator.GreaterEqual; break; default: throw new Exception($"Found unexpected comparison operator '{comparisonClause.Operator}'"); } query.Criteria.AddCondition(conditionName, conditionOperator, conditionValue); } else if (whereClause.Type == SelectWhereTypeEnum.Null) { var nullClause = (SelectWhereNull)whereClause; var conditionName = GetWhereClauseAttributeName(nullClause.Expression); query.Criteria.AddCondition(conditionName, nullClause.Negate ? ConditionOperator.NotNull : ConditionOperator.Null); } else if (whereClause.Type == SelectWhereTypeEnum.Like) { var likeClause = (SelectWhereLike)whereClause; var conditionName = GetWhereClauseAttributeName(likeClause.LeftExpression); var conditionValue = GetWhereClauseAttributeName(likeClause.RightExpression); query.Criteria.AddCondition(conditionName, likeClause.Negate ? ConditionOperator.NotLike : ConditionOperator.Like, conditionValue); } else if (whereClause.Type != SelectWhereTypeEnum.Operator) { throw new Exception($"Found unexpected where clause {whereClause.Type}"); } } } if (statement.Order.Any()) { foreach (var orderItem in statement.Order) { if (orderItem.Type == SelectOrderTypeEnum.Column) { var columnOrder = (SelectOrderColumn)orderItem; var direction = orderItem.Direction == OrderDirection.Asc ? OrderType.Ascending : OrderType.Descending; query.AddOrder(columnOrder.Name, direction); } else if (orderItem.Type == SelectOrderTypeEnum.Position) { var columnPosition = (SelectOrderPosition)orderItem; var direction = orderItem.Direction == OrderDirection.Asc ? OrderType.Ascending : OrderType.Descending; var fieldColumn = statement.Columns[columnPosition.Position - 1] as FieldSelectColumn; if (fieldColumn == null) { throw new Exception($"Position '{columnPosition.Position}' must point to a CRM field"); } query.AddOrder(fieldColumn.Name, direction); } else { throw new Exception($"Unsupported order type '{orderItem.Type}'"); } } } var entities = CRMInstance.Service.RetrieveMultiple(query).Entities.ToList(); var list = new List <List <KeyValuePair <string, object> > >(); foreach (var entity in entities) { var values = new List <KeyValuePair <string, object> >(); foreach (var attribute in entity.Attributes) { object value = null; var type = attribute.Value.GetType(); if (type != typeof(string)) { if (entity.FormattedValues.ContainsKey(attribute.Key)) { value = entity.FormattedValues[attribute.Key]; } } if (value == null) { value = Convert.ToString(attribute.Value); } values.Add(new KeyValuePair <string, object>(attribute.Key, value)); } list.Add(values); } return(ConvertToDataTable(list, statement.Columns)); }