private IEnumerable <IReadOnlyDictionary <string, object> > GetContactAnniversaryData(
UserConnection userConnection, Guid entitySchemaUId, IEntitySchemaQueryFilterItem filter)
{
var entitySchema = userConnection.EntitySchemaManager.GetInstanceByUId(entitySchemaUId);
EntitySchemaQuery query = new EntitySchemaQuery(entitySchema);
var dateColumn = query.AddColumn("Date");
var contactColumn = query.AddColumn("Contact");
var typeColumn = query.AddColumn("AnniversaryType");
var subQueryExpression = query.CreateSubEntitySchemaExpression("Contact.Id");
subQueryExpression.SubQuery.Filters.Add(filter);
var subFilter = new EntitySchemaQueryFilter(FilterComparisonType.Exists);
subFilter.RightExpressions.Add(subQueryExpression);
query.Filters.Add(subFilter);
EntityCollection collection = query.GetEntityCollection(userConnection);
var result = new List <IReadOnlyDictionary <string, object> >();
foreach (var entity in collection)
{
var lookupContactColumn = entity.Schema.Columns.GetByName(contactColumn.Name);
var lookupTypeColumn = entity.Schema.Columns.GetByName(typeColumn.Name);
result.Add(new Dictionary <string, object> {
["Date"] = entity.GetTypedColumnValue <DateTime>(dateColumn.Name),
["ContactId"] = entity.GetColumnValue(lookupContactColumn.ColumnValueName),
["TypeName"] = entity.GetTypedColumnValue <string>(lookupTypeColumn.DisplayColumnValueName)
});
}
return(result);
}
/// <summary>
/// Searches for the accounts by their names.
/// </summary>
/// <param name="name">The name.</param>
/// <returns>Identifiers of the found accounts or <c>null</c> if nothing was found.</returns>
public virtual IEnumerable <Guid> SearchByName(string name)
{
if (name.IsNullOrEmpty())
{
return(null);
}
EntitySchema accountSchema = _userConnection.EntitySchemaManager.GetInstanceByName("Account");
string primaryDisplayColumnName = accountSchema.PrimaryDisplayColumn.Name;
var esq = new EntitySchemaQuery(accountSchema);
esq.PrimaryQueryColumn.IsVisible = true;
EntitySchemaQueryFilter filter = AddUpperFunctionFilter(esq, primaryDisplayColumnName, name);
EntityCollection entities = esq.GetEntityCollection(_userConnection);
if (entities.IsNotEmpty())
{
return(entities.Select(entity => entity.PrimaryColumnValue));
}
filter.LeftExpression.Function = esq.CreateUpperFunction("AlternativeName");
esq.ResetSelectQuery();
entities = esq.GetEntityCollection(_userConnection);
if (entities.IsNotEmpty())
{
return(entities.Select(entity => entity.PrimaryColumnValue));
}
return(null);
}
private void SetRigthExpression(EntitySchemaQueryFilter filter, object value)
{
filter.RightExpressions.Clear();
EntitySchemaQueryExpression parameter =
EntitySchemaQuery.CreateParameterExpression(value, filter.LeftExpression.SchemaColumn.DataValueType);
filter.RightExpressions.Add(parameter);
}
private EntitySchemaQueryFilter GetDatePartFilter(EntitySchemaQueryExpression columnExpression, int datePartValue, EntitySchemaDatePartQueryFunctionInterval interval)
{
var queryFunction = new EntitySchemaDatePartQueryFunction(Esq, interval, columnExpression);
var filter = new EntitySchemaQueryFilter(FilterComparisonType.Equal)
{
LeftExpression = new EntitySchemaQueryExpression(queryFunction)
};
EntitySchemaQueryExpression rightExpression = EntitySchemaQuery.CreateParameterExpression(datePartValue);
filter.RightExpressions.Add(rightExpression);
return(filter);
}
/// <summary>
/// Adds include filter to entity schema query.
/// </summary>
/// <param name="filterColumn">Column for filtration</param>
/// <param name="recordIds">Filter values</param>
public void AddIncludeFilter(string filterColumn, Guid[] recordIds)
{
if (recordIds != null && recordIds.Length > 0)
{
var filter = new EntitySchemaQueryFilter(FilterComparisonType.Equal)
{
LeftExpression = Esq.CreateSchemaColumnExpression(filterColumn)
};
recordIds.ForEach(recordId =>
filter.RightExpressions.Add(EntitySchemaQuery.CreateParameterExpression(recordId)));
Esq.Filters.Add(filter);
}
}
private static EntitySchemaQueryFilter AddUpperFunctionFilter(EntitySchemaQuery esq, string columnName,
string filterValue)
{
EntitySchemaQueryFunction upperNameFunction = esq.CreateUpperFunction(columnName);
var filter = new EntitySchemaQueryFilter(FilterComparisonType.Equal)
{
LeftExpression = new EntitySchemaQueryExpression(upperNameFunction)
};
filter.RightExpressions.Add(new EntitySchemaQueryExpression(EntitySchemaQueryExpressionType.Parameter)
{
ParameterValue = filterValue.ToUpperInvariant()
});
esq.Filters.Add(filter);
return(filter);
}
private static bool IsFilterRightExressionsEmpty(EntitySchemaQueryFilter filter)
{
EntitySchemaQueryExpressionCollection rightExpressions = filter.RightExpressions;
bool isEmpty = (rightExpressions.Count == 0 && filter.ComparisonType != FilterComparisonType.IsNull &&
filter.ComparisonType != FilterComparisonType.IsNotNull);
if (!isEmpty && rightExpressions.Count == 1)
{
EntitySchemaQueryExpression rightExpression = rightExpressions[0];
DataValueType expressionType = rightExpression.ParameterValueForcedType;
isEmpty = (expressionType is TextDataValueType || expressionType is DateTimeDataValueType) &&
(rightExpression.ParameterValue == null) &&
(rightExpression.ExpressionType == EntitySchemaQueryExpressionType.Parameter);
}
return(isEmpty);
}
/// <summary>
/// Adds length function filter for entity schema query to current filter collection.
/// </summary>
/// <param name="filterCollection">Filter collection, which the created filter will be added to.</param>
/// <param name="schema">Entity schema.</param>
/// <param name="columnName">Name of column, which should be operand for the function.</param>
/// <param name="comparisonType">Type of comparison with function.</param>
/// <param name="value">Value to compare function with.</param>
/// <returns>Created compare filter like "LEN(column_name) comparison_type value".</returns>
public static EntitySchemaQueryFilter AddLengthFilter(this EntitySchemaQueryFilterCollection filterCollection,
EntitySchema schema, string columnName, FilterComparisonType comparisonType, int value)
{
var esq = filterCollection.ParentQuery;
EntitySchemaQueryFunction function = esq.CreateLengthFunction(
EntitySchemaQuery.CreateSchemaColumnExpression(schema, columnName));
var filter = new EntitySchemaQueryFilter(FilterComparisonType.Equal)
{
LeftExpression = new EntitySchemaQueryExpression(function),
ComparisonType = comparisonType
};
filter.RightExpressions.Add(new EntitySchemaQueryExpression(EntitySchemaQueryExpressionType.Parameter)
{
ParameterValue = value
});
filterCollection.Add(filter);
return(filter);
}
private void ApplyFilterParameterByDateQueryFunction(DateTime currentDate, EntitySchemaQueryFilterCollection filters)
{
EntitySchemaQueryFilter firstFilter = (EntitySchemaQueryFilter)filters[0];
EntitySchemaQueryFilter secondFilter = (EntitySchemaQueryFilter)filters[1];
EntitySchemaQueryExpression firstExpression = firstFilter.RightExpressions[0];
EntitySchemaQueryExpression secondExpression = secondFilter.RightExpressions[0];
var firstFunction = firstExpression.Function as EntitySchemaBaseCurrentDateQueryFunction;
var secondFunction = secondExpression.Function as EntitySchemaBaseCurrentDateQueryFunction;
if (firstExpression.Function.GetType() == typeof(EntitySchemaCurrentDateQueryFunction))
{
SetRigthExpression(firstFilter, DateTimeUtilities.DateTimeToDate(currentDate, firstFunction.Offset));
SetRigthExpression(secondFilter, DateTimeUtilities.DateTimeToDate(currentDate, secondFunction.Offset));
}
else if (firstExpression.Function.GetType() == typeof(EntitySchemaStartOfCurrentWeekQueryFunction))
{
SetRigthExpression(firstFilter, DateTimeUtilities.StartOfWeek(currentDate, firstFunction.Offset));
SetRigthExpression(secondFilter, DateTimeUtilities.StartOfWeek(currentDate, secondFunction.Offset));
}
else if (firstExpression.Function.GetType() == typeof(EntitySchemaStartOfCurrentMonthQueryFunction))
{
SetRigthExpression(firstFilter, DateTimeUtilities.StartOfMonth(currentDate, firstFunction.Offset));
SetRigthExpression(secondFilter, DateTimeUtilities.StartOfMonth(currentDate, secondFunction.Offset));
}
else if (firstExpression.Function.GetType() == typeof(EntitySchemaStartOfCurrentQuarterQueryFunction))
{
SetRigthExpression(firstFilter, DateTimeUtilities.StartOfQuarter(currentDate, firstFunction.Offset));
SetRigthExpression(secondFilter, DateTimeUtilities.StartOfQuarter(currentDate, secondFunction.Offset));
}
else if (firstExpression.Function.GetType() == typeof(EntitySchemaStartOfCurrentHalfYearQueryFunction))
{
SetRigthExpression(firstFilter, DateTimeUtilities.StartOfHalfYear(currentDate, firstFunction.Offset));
SetRigthExpression(secondFilter, DateTimeUtilities.StartOfHalfYear(currentDate, secondFunction.Offset));
}
else if (firstExpression.Function.GetType() == typeof(EntitySchemaStartOfCurrentYearQueryFunction))
{
SetRigthExpression(firstFilter, DateTimeUtilities.StartOfYear(currentDate, firstFunction.Offset));
SetRigthExpression(secondFilter, DateTimeUtilities.StartOfYear(currentDate, secondFunction.Offset));
}
}
private IEnumerable <IReadOnlyDictionary <string, object> > GetActivityData(UserConnection userConnection,
Guid entitySchemaUId, IEntitySchemaQueryFilterItem filter)
{
var entitySchema = userConnection.EntitySchemaManager.GetInstanceByName("Activity");
EntitySchemaQuery query = new EntitySchemaQuery(entitySchema);
var idColumn = query.AddColumn("Id");
query.AddColumn("Title");
query.AddColumn("StartDate");
query.AddColumn("DueDate");
var ownerId = query.AddColumn("Owner.Id");
var subQueryExpression = query.CreateSubEntitySchemaExpression("Owner.Id");
subQueryExpression.SubQuery.Filters.Add(filter);
var subFilter = new EntitySchemaQueryFilter(FilterComparisonType.Exists);
subFilter.RightExpressions.Add(subQueryExpression);
query.Filters.Add(subFilter);
EntityCollection collection = query.GetEntityCollection(userConnection);
var result = new List <IReadOnlyDictionary <string, object> >();
foreach (var entity in collection)
{
result.Add(new Dictionary <string, object>
{
["Owner"] = entity.GetTypedColumnValue <Guid>(ownerId.Name),
["Id"] = entity.GetTypedColumnValue <Guid>(idColumn.Name),
["Title"] = entity.GetTypedColumnValue <string>("Title"),
["StartDate"] = entity.GetTypedColumnValue <DateTime>("StartDate"),
["DueDate"] = entity.GetTypedColumnValue <DateTime>("DueDate"),
});
}
//DataValueTypes provided in (ENUM)Terrasoft.Nui.ServiceModel.DataContract.DataValueType
return(result);
}
private void SetRigthExpression(EntitySchemaQueryFilter filter, object value)
{
filter.RightExpressions.Clear();
filter.RightExpressions.Add(EntitySchemaQuery.CreateParameterExpression(value));
}
