WhereClause PivotCaseCondition(PivotColumn col, PivotColumnValue val)
{
WhereClause clause = new WhereClause(WhereClauseRelationship.And);
clause.Terms.Add(CreateColumnValueCondition(col, val));
return(clause);
}
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, CompareOperator.GreaterOrEqual);
}
else if (step.LowBound == null)
{
term = WhereTerm.CreateCompare(fieldExpr, highBoundExpr, CompareOperator.Less);
}
else
{
term = WhereTerm.CreateBetween(fieldExpr, lowBoundExpr, highBoundExpr);
}
return(term);
}
/// <summary>
/// Creates a new PivotColumnValue with a range value
/// </summary>
public static PivotColumnValue CreateRange(string crossTabColumnName, Range range)
{
PivotColumnValue columnValue = new PivotColumnValue(crossTabColumnName);
columnValue.SetRangeValue(range);
return(columnValue);
}
/// <summary>
/// Creates a new PivotColumnValue with a scalar value
/// </summary>
public static PivotColumnValue CreateScalar(string crossTabColumnName, object val)
{
PivotColumnValue columnValue = new PivotColumnValue(crossTabColumnName);
columnValue.SetScalarValue(val);
return(columnValue);
}
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)), CompareOperator.Equal));
}
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));
}
/// <summary>
/// Determines whether a specfic PivotColumnValue value is in this PivotColumnValueCollection.
/// </summary>
/// <param name="value">
/// The PivotColumnValue value to locate in this PivotColumnValueCollection.
/// </param>
/// <returns>
/// true if value is found in this PivotColumnValueCollection;
/// false otherwise.
/// </returns>
public virtual bool Contains(PivotColumnValue value)
{
return(this.List.Contains(value));
}
/// <summary>
/// Adds an instance of type PivotColumnValue to the end of this PivotColumnValueCollection.
/// </summary>
/// <param name="value">
/// The PivotColumnValue to be added to the end of this PivotColumnValueCollection.
/// </param>
public virtual void Add(PivotColumnValue value)
{
this.List.Add(value);
}
/// <summary>
/// Removes the first occurrence of a specific PivotColumnValue from this PivotColumnValueCollection.
/// </summary>
/// <param name="value">
/// The PivotColumnValue value to remove from this PivotColumnValueCollection.
/// </param>
public virtual void Remove(PivotColumnValue value)
{
this.List.Remove(value);
}
/// <summary>
/// Inserts an element into the PivotColumnValueCollection at the specified index
/// </summary>
/// <param name="index">
/// The index at which the PivotColumnValue is to be inserted.
/// </param>
/// <param name="value">
/// The PivotColumnValue to insert.
/// </param>
public virtual void Insert(int index, PivotColumnValue value)
{
this.List.Insert(index, value);
}
/// <summary>
/// Return the zero-based index of the first occurrence of a specific value
/// in this PivotColumnValueCollection
/// </summary>
/// <param name="value">
/// The PivotColumnValue value to locate in the PivotColumnValueCollection.
/// </param>
/// <returns>
/// The zero-based index of the first occurrence of the _ELEMENT value if found;
/// -1 otherwise.
/// </returns>
public virtual int IndexOf(PivotColumnValue value)
{
return(this.List.IndexOf(value));
}
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);
}
