private void SetValue(CodeActivityContext context)
{
this.localSourceTable = InputTable.Get(context);
this.localNullValue = NullValue.Get(context);
if (this.localSourceTable == null)
{
throw new Exception("Input table is null");
}
this.localGroupbyColumn = GroupbyColumns.Get(context);
this.localAggregateColumn = AggregateColumn.Get(context);
this.localAggregateBy = AggregateBy.ToString();
if (AggregateType.ToString() == "Integer")
{
this.localAggregateColumnType = System.Type.GetType("System.Int64");
}
else
{
this.localAggregateColumnType = System.Type.GetType("System." + AggregateType.ToString());
}
}
// Constructors
/// <summary>
/// Initializes a new instance of this class.
/// </summary>
/// <param name="source">The <see cref="UnaryProvider.Source"/> property value.</param>
/// <param name="columnDescriptors">The descriptors of <see cref="AggregateColumns"/>.</param>
/// <param name="groupIndexes">The column indexes to group by.</param>
public AggregateProvider(CompilableProvider source, int[] groupIndexes, params AggregateColumnDescriptor[] columnDescriptors)
: base(ProviderType.Aggregate, source)
{
groupIndexes = groupIndexes ?? ArrayUtils <int> .EmptyArray;
var columns = new AggregateColumn[columnDescriptors.Length];
for (int i = 0; i < columnDescriptors.Length; i++)
{
AggregateColumnDescriptor descriptor = columnDescriptors[i];
var type = GetAggregateColumnType(Source.Header.Columns[descriptor.SourceIndex].Type, descriptor.AggregateType);
columns[i] = new AggregateColumn(descriptor, groupIndexes.Length + i, type);
}
AggregateColumns = columns;
GroupColumnIndexes = groupIndexes;
Initialize();
}
public void ProcessRequest(HttpContext context)
{
try
{
SessionVarName = context.Request.QueryString["SessionVarName"];
IEnumerable retrievedData = (IEnumerable)context.Session[SessionVarName];
if (retrievedData.IsNull())
{
return;
}
AggregateColumn = context.Request.QueryString["AggregateColumn"];
if (AggregateColumn.IsNullOrEmpty())
{
AggregateColumn = String.Empty;
}
UserDataString = context.Request.QueryString["FooterRowCaption"];
if (UserDataString.IsNullOrEmpty())
{
UserDataString = String.Empty;
}
String vid_Ref = context.Request.QueryString["vid_Ref"];
String parentColumnName = context.Request.QueryString["ParentColumnName"];
String parentColumnValue = context.Request.QueryString["ParentColumnValue"];
if (vid_Ref.IsNotNullOrEmpty() && vid_Ref.NotEquals("null"))
{
filterRetrievedData(ref retrievedData, vid_Ref);
OnDataSourceViewSelectCallback(retrievedData);
}
else if (parentColumnName.IsNotNullOrEmpty() && parentColumnValue.IsNotNullOrEmpty())
{
filterRetrievedDataDataSourceView(retrievedData, parentColumnName, parentColumnValue);
}
else
{
OnDataSourceViewSelectCallback(retrievedData);
}
}
catch (Exception ex)
{
throw ex;
}
}
private void LoadUserData()
{
try
{
var strColumn = AggregateColumn.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).ToList();
foreach (DataColumn dc in SourceTable.Columns)
{
if (dc.DataType != typeof(String) && dc.DataType != typeof(DateTime) && dc.DataType != typeof(Boolean))
{
try
{
String strColumnWith_Agg_Type = strColumn.Find(item => item.Contains("[" + dc.ColumnName + "]:"));
if (strColumnWith_Agg_Type.IsNotNullOrEmpty())
{
String[] arrColumnWith_Agg_Type = strColumnWith_Agg_Type.Split(":".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
if (arrColumnWith_Agg_Type[0].ToUpper() == "[" + dc.ColumnName.ToUpper() + "]")
{
Object val = SourceTable.Compute(arrColumnWith_Agg_Type[1] + "(" + dc.ColumnName + ")", String.Empty);
if (UserDataString.IsNotNullOrEmpty())
{
UserDataString += String.Format(",\"{0}\":\"{1}\"", dc.ColumnName, val);
}
else
{
UserDataString += String.Format("\"{0}\":\"{1}\"", dc.ColumnName, val);
}
}
}
}
catch
{
};
}
}
}
catch (Exception ex)
{
throw (ex);
}
}
protected override SqlExpression ProcessAggregate(
SqlProvider source, List <SqlExpression> sourceColumns, AggregateColumn aggregateColumn)
{
var aggregateType = aggregateColumn.Type;
var result = base.ProcessAggregate(source, sourceColumns, aggregateColumn);
if (aggregateColumn.AggregateType == AggregateType.Avg)
{
var originType = source.Origin.Header.Columns[aggregateColumn.SourceIndex].Type;
// floats are promoted to doubles, but we need the same type
if (originType == aggregateType && originType != typeof(float))
{
return(result);
}
var sqlType = Driver.MapValueType(aggregateType);
return(SqlDml.Cast(SqlDml.Avg(SqlDml.Cast(sourceColumns[aggregateColumn.SourceIndex], sqlType)), sqlType));
}
// cast to decimal is dangerous, because 'decimal' defaults to integer type
if (aggregateColumn.AggregateType == AggregateType.Sum && aggregateType != typeof(decimal))
{
return(SqlDml.Cast(result, Driver.MapValueType(aggregateType)));
}
return(result);
}
protected bool Equals(AggregateColumn other)
{
return(base.Equals(other) && Equals(AggregateOperation, other.AggregateOperation));
}
/// <summary>
/// Translates <see cref="AggregateColumn"/> to corresponding <see cref="SqlExpression"/>.
/// </summary>
/// <param name="source">The source <see cref="SqlProvider"/>.</param>
/// <param name="sourceColumns">The source columns.</param>
/// <param name="aggregateColumn">The aggregate column.</param>
/// <returns>Aggregate processing result (expression).</returns>
protected virtual SqlExpression ProcessAggregate(SqlProvider source, List <SqlExpression> sourceColumns, AggregateColumn aggregateColumn)
{
switch (aggregateColumn.AggregateType)
{
case AggregateType.Avg:
return(SqlDml.Avg(sourceColumns[aggregateColumn.SourceIndex]));
case AggregateType.Count:
return(SqlDml.Count(SqlDml.Asterisk));
case AggregateType.Max:
return(SqlDml.Max(sourceColumns[aggregateColumn.SourceIndex]));
case AggregateType.Min:
return(SqlDml.Min(sourceColumns[aggregateColumn.SourceIndex]));
case AggregateType.Sum:
return(SqlDml.Sum(sourceColumns[aggregateColumn.SourceIndex]));
default:
throw new ArgumentException();
}
}
public static async Task SetAggregateAsync <TEntity>(IQueryable <TEntity> result, AggregateColumn column)
{
var code = column.Code.Capitalize();
var left = column.Left.Capitalize();
var right = column.Right.Capitalize();
var leftProp = string.IsNullOrEmpty(left) ? null : SearchHelper.CreatePropertySelectorExpression <TEntity, double>(left);
var rightProp = string.IsNullOrEmpty(right) ? null : SearchHelper.CreatePropertySelectorExpression <TEntity, double>(right);
var codeProp = string.IsNullOrEmpty(code) ? null : SearchHelper.CreatePropertySelectorExpression <TEntity, double>(code);
switch (column.Type)
{
case AggregateType.Sum:
column.Result = await result
.SumAsync(leftProp ?? throw new AggregateException())
.ConfigureAwait(false);
break;
case AggregateType.Average:
column.Result = await result
.AverageAsync(leftProp ?? throw new AggregateException())
.ConfigureAwait(false);
break;
case AggregateType.Percentage:
{
var leftResult = await result
.SumAsync(leftProp ?? throw new AggregateException())
.ConfigureAwait(false);
var rightResult = await result
.SumAsync(codeProp ?? throw new AggregateException())
.ConfigureAwait(false);
column.Result = (199 / rightResult) * leftResult;
}
break;
case AggregateType.Difference:
{
if (string.IsNullOrEmpty(left) || string.IsNullOrEmpty(right))
{
throw new AggregateException($"Aggregation impossible for column '{code}'. Ensure both left ({left}) and right ({right}) are supplied.");
}
var leftResult = await result
.SumAsync(leftProp ?? throw new AggregateException())
.ConfigureAwait(false);
var rightResult = await result
.SumAsync(rightProp ?? throw new AggregateException())
.ConfigureAwait(false);
column.Result = leftResult - rightResult;
}
break;
}
}
protected override SqlExpression ProcessAggregate(SqlProvider source, List <SqlExpression> sourceColumns, AggregateColumn aggregateColumn)
{
var result = base.ProcessAggregate(source, sourceColumns, aggregateColumn);
if (aggregateColumn.AggregateType == AggregateType.Sum || aggregateColumn.AggregateType == AggregateType.Avg)
{
result = SqlDml.Cast(result, Driver.MapValueType(aggregateColumn.Type));
}
return(result);
}
protected override SqlExpression ProcessAggregate(
SqlProvider source, List <SqlExpression> sourceColumns, AggregateColumn aggregateColumn)
{
var result = base.ProcessAggregate(source, sourceColumns, aggregateColumn);
var aggregateReturnType = aggregateColumn.Type;
var originCalculateColumn = source.Origin.Header.Columns[aggregateColumn.SourceIndex];
var sqlType = Driver.MapValueType(aggregateReturnType);
if (aggregateColumn.AggregateType == AggregateType.Min ||
aggregateColumn.AggregateType == AggregateType.Max ||
aggregateColumn.AggregateType == AggregateType.Sum)
{
if (!IsCalculatedColumn(originCalculateColumn))
{
if (aggregateReturnType == DecimalType)
{
return(result);
}
else if (ShouldCastDueType(aggregateReturnType))
{
return(SqlDml.Cast(result, Driver.MapValueType(aggregateReturnType)));
}
}
else if (ShouldCastDueType(aggregateReturnType))
{
return(SqlDml.Cast(result, Driver.MapValueType(aggregateReturnType)));
}
return(result);
}
if (aggregateColumn.AggregateType == AggregateType.Avg)
{
//var sqlType = Driver.MapValueType(aggregateReturnType);
if (aggregateReturnType != originCalculateColumn.Type)
{
return(SqlDml.Cast(SqlDml.Avg(SqlDml.Cast(sourceColumns[aggregateColumn.SourceIndex], sqlType)), sqlType));
}
if (!IsCalculatedColumn(originCalculateColumn))
{
if (aggregateReturnType == DecimalType)
{
return(result);
}
else if (ShouldCastDueType(aggregateReturnType))
{
return(SqlDml.Cast(SqlDml.Avg(SqlDml.Cast(sourceColumns[aggregateColumn.SourceIndex], sqlType)), sqlType));
}
else if (aggregateReturnType != originCalculateColumn.Type)
{
return(SqlDml.Cast(SqlDml.Avg(SqlDml.Cast(sourceColumns[aggregateColumn.SourceIndex], sqlType)), sqlType));
}
}
else
{
if (ShouldCastDueType(aggregateReturnType))
{
return(SqlDml.Cast(SqlDml.Avg(SqlDml.Cast(sourceColumns[aggregateColumn.SourceIndex], sqlType)), sqlType));
}
else if (aggregateReturnType != originCalculateColumn.Type)
{
return(SqlDml.Cast(SqlDml.Avg(SqlDml.Cast(sourceColumns[aggregateColumn.SourceIndex], sqlType)), sqlType));
}
return(result);
}
}
return(result);
}
protected override SqlExpression ProcessAggregate(SqlProvider source, List <SqlExpression> sourceColumns, AggregateColumn aggregateColumn)
{
var result = base.ProcessAggregate(source, sourceColumns, aggregateColumn);
if (aggregateColumn.AggregateType == AggregateType.Avg)
{
switch (Type.GetTypeCode(aggregateColumn.Type))
{
case TypeCode.Single:
case TypeCode.Double:
result = SqlDml.Cast(result, Driver.MapValueType(aggregateColumn.Type));
break;
}
}
return(result);
}