/// <summary>
/// Calculate the total of a specified data field for a row/columnn header for custom sorting.
/// </summary>
/// <param name="node">The current node that is being evaluated.</param>
/// <param name="dataFieldIndex">The index of the referenced data field.</param>
/// <returns>The calculated total.</returns>
public double CalculateSortingValues(PivotItemTreeNode node, int dataFieldIndex)
{
double sortingTotal = 0;
foreach (var record in node.CacheRecordIndices)
{
string dataFieldValue = this.Records[record].Items[dataFieldIndex].Value;
sortingTotal += double.Parse(dataFieldValue);
}
return(sortingTotal);
}
/// <summary>
/// Adds a new child with the specified values to this node.
/// </summary>
/// <param name="value">The cache record item node "v" index of the new child.</param>
/// <param name="pivotFieldIndex">The index of the pivot field referenced by the new child.</param>
/// <param name="pivotFieldItemIndex">The index of the pivot field item referenced by the new child.</param>
/// <param name="sharedItemValue">The shared item "v" value. (Only used for date groupings).</param>
/// <param name="isTabularForm">A value indicating if this node's pivot field is set as tabular form.</param>
public PivotItemTreeNode AddChild(int value, int pivotFieldIndex = -2, int pivotFieldItemIndex = -2, string sharedItemValue = null, bool isTabularForm = false)
{
var child = new PivotItemTreeNode(value)
{
PivotFieldIndex = pivotFieldIndex,
PivotFieldItemIndex = pivotFieldItemIndex,
SharedItemValue = sharedItemValue,
IsTabularForm = isTabularForm
};
this.Children.Add(child);
return(child);
}
/// <summary>
/// Creates a deep copy of this node.
/// </summary>
/// <returns>The newly created node.</returns>
public PivotItemTreeNode Clone()
{
var clone = new PivotItemTreeNode(this.Value);
clone.DataFieldIndex = this.DataFieldIndex;
clone.PivotFieldIndex = this.PivotFieldIndex;
clone.PivotFieldItemIndex = this.PivotFieldItemIndex;
clone.SubtotalTop = this.SubtotalTop;
clone.IsTabularForm = this.IsTabularForm;
clone.CacheRecordIndices = this.CacheRecordIndices;
foreach (var child in this.Children)
{
clone.Children.Add(child.Clone());
}
return(clone);
}
private double?GetCalculatedFieldTotal(PivotItemTreeNode node, ExcelPivotTable pivotTable, CacheFieldNode cacheField)
{
var totalsFunction = new TotalsFunctionHelper();
var calculatedFields = pivotTable.CacheDefinition.CacheFields.Where(f => !string.IsNullOrEmpty(f.Formula));
PivotTableDataManager.ConfigureCalculatedFields(calculatedFields, totalsFunction, pivotTable);
var fieldNameToValues = new Dictionary <string, List <object> >();
foreach (var cacheFieldName in cacheField.ReferencedCacheFieldsToIndex.Keys)
{
var values = pivotTable.CacheDefinition.CacheRecords.GetChildDataFieldValues(node.CacheRecordIndices, cacheField.ReferencedCacheFieldsToIndex[cacheFieldName]);
fieldNameToValues.Add(cacheFieldName, values);
}
var total = totalsFunction.EvaluateCalculatedFieldFormula(fieldNameToValues, cacheField.ResolvedFormula);
if (double.TryParse(total.ToString(), out var result))
{
return(result);
}
return(null);
}
private void SortWithDataFields(ExcelPivotTable pivotTable, ExcelPivotTableField pivotField, PivotItemTreeNode root)
{
int autoScopeIndex = int.Parse(pivotField.AutoSortScopeReferences[0].Value);
var referenceDataFieldIndex = pivotTable.DataFields[autoScopeIndex].Index;
var orderedList = new List <Tuple <int, double?> >();
// Get the total value for every child at the given data field index.
foreach (var child in root.Children.ToList())
{
double?sortingTotal = 0;
var cacheField = pivotTable.CacheDefinition.CacheFields[referenceDataFieldIndex];
if (!string.IsNullOrEmpty(cacheField.Formula))
{
sortingTotal = this.GetCalculatedFieldTotal(child, pivotTable, cacheField);
}
else
{
sortingTotal = pivotTable.CacheDefinition.CacheRecords.CalculateSortingValues(child, referenceDataFieldIndex);
}
orderedList.Add(new Tuple <int, double?>(child.Value, sortingTotal));
}
bool sortDescending = pivotField.Sort == eSortType.Descending;
// Sort the list of total values accordingly.
orderedList = sortDescending ? orderedList.OrderByDescending(i => i.Item2).ToList() : orderedList.OrderBy(i => i.Item2).ToList();
// If there are duplicated sortingTotal values, sort it based on the value of the first tuple.
var duplicates = orderedList.GroupBy(x => x.Item2).Where(g => g.Count() > 1).Select(k => k.Key);
if (duplicates.Count() > 0)
{
for (int i = 0; i < duplicates.ToList().Count(); i++)
{
var duplicatedList = orderedList.FindAll(j => j.Item2 == duplicates.ElementAt(i));
int startingIndex = orderedList.FindIndex(y => y == duplicatedList.ElementAt(0));
duplicatedList = sortDescending ? duplicatedList.OrderByDescending(w => w.Item1).ToList() : duplicatedList.OrderBy(w => w.Item1).ToList();
orderedList.RemoveRange(startingIndex, duplicatedList.Count());
orderedList.InsertRange(startingIndex, duplicatedList);
}
}
// Add children back to the root node in sorted order.
var newChildList = root.Children.ToList();
root.Children.Clear();
for (int i = 0; i < orderedList.Count(); i++)
{
int index = newChildList.FindIndex(x => x.Value == orderedList.ElementAt(i).Item1);
root.Children.Add(newChildList[index]);
}
}