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