WhereTerm CreateRangeTerm(PivotColumn pivotCol, PivotColumnValue pivotColValue)
{
Range step = pivotColValue.Range;
SqlExpression fieldExpr = SqlExpression.Field(pivotCol.ColumnField);
if (step.HighBound == null && step.LowBound == null)
{
throw new PivotTableException("At least one bound of a Range must be set.");
}
SqlExpression lowBoundExpr = (step.LowBound != null) ? SqlExpression.Constant(pivotCol.DataType, pivotColValue.Range.LowBound) : null;
SqlExpression highBoundExpr = (step.HighBound != null) ? SqlExpression.Constant(pivotCol.DataType, pivotColValue.Range.HighBound) : null;
WhereTerm term;
if (step.HighBound == null)
{
term = WhereTerm.CreateCompare(fieldExpr, lowBoundExpr, FilterOperator.IsGreaterThanOrEqualTo);
}
else if (step.LowBound == null)
{
term = WhereTerm.CreateCompare(fieldExpr, highBoundExpr, FilterOperator.IsLessThan);
}
else
{
term = WhereTerm.CreateBetween(fieldExpr, lowBoundExpr, highBoundExpr);
}
return(term);
}
WhereClause PivotCaseCondition(PivotColumn col, PivotColumnValue val)
{
WhereClause clause = new WhereClause(FilterCompositionLogicalOperator.And);
clause.Terms.Add(CreateColumnValueCondition(col, val));
return(clause);
}
void AddCrossTabColumns(SelectQuery query, PivotColumn column)
{
foreach (PivotColumnValue val in column.Values)
{
query.Columns.Add(new SelectColumn(SqlExpression.Function(valueFuntion, PivotCaseExpression(column, val)), val.CrossTabColumnName));
}
}
WhereTerm CreateColumnValueCondition(PivotColumn col, PivotColumnValue val)
{
if (val.ValueType == PivotColumnValueType.Scalar)
{
return(WhereTerm.CreateCompare(SqlExpression.Field(col.ColumnField), SqlExpression.Constant(new SqlConstant(col.DataType, val.Value)), FilterOperator.IsEqualTo));
}
else
{
return(CreateRangeTerm(col, val));
}
}
SqlExpression PivotCaseExpression(PivotColumn col, PivotColumnValue val)
{
CaseClause caseClause = new CaseClause();
caseClause.ElseValue = SqlExpression.Null();
CaseTerm term = new CaseTerm(PivotCaseCondition(col, val), SqlExpression.Field(valueField));
caseClause.Terms.Add(term);
return(SqlExpression.Case(caseClause));
}
bool FindPivotColumnValue(string crossTabColumnName, out PivotColumn pivotCol, out PivotColumnValue pivotVal)
{
pivotCol = null;
pivotVal = null;
foreach (PivotColumn col in columns)
{
foreach (PivotColumnValue val in col.Values)
{
if (string.Compare(val.CrossTabColumnName, crossTabColumnName, true) == 0)
{
pivotCol = col;
pivotVal = val;
return(true);
}
}
}
return(false);
}
/// <summary>
/// Adds an instance of type PivotColumn to the end of this PivotColumnCollection.
/// </summary>
/// <param name="value">
/// The PivotColumn to be added to the end of this PivotColumnCollection.
/// </param>
public virtual void Add(PivotColumn value)
{
this.List.Add(value);
}
/// <summary>
/// Removes the first occurrence of a specific PivotColumn from this PivotColumnCollection.
/// </summary>
/// <param name="value">
/// The PivotColumn value to remove from this PivotColumnCollection.
/// </param>
public virtual void Remove(PivotColumn value)
{
this.List.Remove(value);
}
/// <summary>
/// Inserts an element into the PivotColumnCollection at the specified index
/// </summary>
/// <param name="index">
/// The index at which the PivotColumn is to be inserted.
/// </param>
/// <param name="value">
/// The PivotColumn to insert.
/// </param>
public virtual void Insert(int index, PivotColumn value)
{
this.List.Insert(index, value);
}
/// <summary>
/// Return the zero-based index of the first occurrence of a specific value
/// in this PivotColumnCollection
/// </summary>
/// <param name="value">
/// The PivotColumn value to locate in the PivotColumnCollection.
/// </param>
/// <returns>
/// The zero-based index of the first occurrence of the _ELEMENT value if found;
/// -1 otherwise.
/// </returns>
public virtual int IndexOf(PivotColumn value)
{
return(this.List.IndexOf(value));
}
/// <summary>
/// Determines whether a specfic PivotColumn value is in this PivotColumnCollection.
/// </summary>
/// <param name="value">
/// The PivotColumn value to locate in this PivotColumnCollection.
/// </param>
/// <returns>
/// true if value is found in this PivotColumnCollection;
/// false otherwise.
/// </returns>
public virtual bool Contains(PivotColumn value)
{
return(this.List.Contains(value));
}