private bool FindGroupingRecordValueAndTupleMatch(CacheFieldNode cacheField, CacheRecordNode record, Tuple <int, int> tuple, ExcelPivotTable pivotTable)
{
if (cacheField.IsDateGrouping)
{
// Find record indices for date groupings fields.
var recordIndices = this.DateGroupingRecordValueTupleMatch(cacheField, tuple.Item2);
// If the record value is in the list, then the record value and tuple are a match.
int index = tuple.Item1 < record.Items.Count ? tuple.Item1 : cacheField.FieldGroup.BaseField;
var itemValue = record.Items[index].Value;
if (recordIndices.All(i => i != int.Parse(itemValue)))
{
return(false);
}
}
else
{
// Use discrete grouping property collection to determine match.
int baseIndex = cacheField.FieldGroup.BaseField;
// Get the pivot field item's x value and the record item's v value.
int pivotFieldValue = pivotTable.Fields[tuple.Item1].Items[tuple.Item2].X;
var recordValue = int.Parse(record.Items[baseIndex].Value);
var fieldGroup = cacheField.FieldGroup;
// Get the shared item string the pivot field item and record item is pointing to.
var pivotFieldPtrValue = fieldGroup.GroupItems[pivotFieldValue].Value;
var recordDiscretePtrValue = int.Parse(fieldGroup.DiscreteGroupingProperties[recordValue].Value);
var recordPtrValue = fieldGroup.GroupItems[recordDiscretePtrValue].Value;
// Check if the pivot field item and record item is pointing to the same shared string.
if (!pivotFieldPtrValue.IsEquivalentTo(recordPtrValue))
{
return(false);
}
}
return(true);
}
private List <int> DateGroupingRecordValueTupleMatch(CacheFieldNode cacheField, int tupleItem2)
{
var recordIndices = new List <int>();
// Go through all the shared items and if the item is the targeted value (tuple.Item2 or groupItems[tuple.Item2]),
// add the index of the shared item to the list.
var groupByType = cacheField.FieldGroup.GroupBy;
string groupFieldItemsValue = cacheField.FieldGroup.GroupItems[tupleItem2].Value;
int baseFieldIndex = cacheField.FieldGroup.BaseField;
var sharedItems = this.CacheDefinition.CacheFields[baseFieldIndex].SharedItems;
for (int i = 0; i < sharedItems.Count; i++)
{
var dateTime = DateTime.Parse(sharedItems[i].Value);
var groupByValue = string.Empty;
// Get the sharedItem's groupBy value, unless the groupBy value is months.
if (groupByType == PivotFieldDateGrouping.Months && tupleItem2 == dateTime.Month)
{
recordIndices.Add(i);
}
else if (groupByType == PivotFieldDateGrouping.Years)
{
groupByValue = dateTime.Year.ToString();
}
else if (groupByType == PivotFieldDateGrouping.Quarters)
{
groupByValue = "Qtr" + ((dateTime.Month - 1) / 3 + 1);
}
else if (groupByType == PivotFieldDateGrouping.Days)
{
groupByValue = dateTime.Day + "-" + dateTime.ToString("MMM");
}
else if (groupByType == PivotFieldDateGrouping.Minutes)
{
groupByValue = ":" + dateTime.ToString("mm");
}
else if (groupByType == PivotFieldDateGrouping.Seconds)
{
groupByValue = ":" + dateTime.ToString("ss");
}
else if (groupByType == PivotFieldDateGrouping.Hours)
{
int hour = dateTime.Hour == 00 ? 12 : dateTime.Hour;
groupByValue = hour + " " + dateTime.ToString("tt", Thread.CurrentThread.CurrentCulture);
}
// Check if the sharedItem's groupBy value matches the groupFieldItem's value in the cacheField.
if (groupFieldItemsValue.IsEquivalentTo(groupByValue))
{
recordIndices.Add(i);
}
}
return(recordIndices);
}
private string GetCacheFieldSharedItemIndexString(CacheFieldNode cacheField, PivotCacheRecordType type, object value)
{
int cacheFieldItemIndex = cacheField.GetSharedItemIndex(type, value);
// Adds a new sharedItem if the item does not exist.
if (cacheFieldItemIndex < 0)
{
cacheField.SharedItems.Add(value);
cacheFieldItemIndex = cacheField.SharedItems.Count - 1;
}
return(ConvertUtil.ConvertObjectToXmlAttributeString(cacheFieldItemIndex));
}
/// <summary>
/// Gets the <see cref="SharedItemsCollection"/> of cache items that a slicer is referencing in the specified <paramref name="cacheField"/>.
/// </summary>
/// <param name="cacheField">The cache field to retrieve cache items from.</param>
/// <returns>The <see cref="SharedItemsCollection"/> of cache items that a slicer is referencing.</returns>
public SharedItemsCollection GetCacheItemsForSlicer(CacheFieldNode cacheField)
{
// If a cacheField is a group field, the fieldGroup/groupItems are used as the values for the slicer.
SharedItemsCollection cacheItems;
if (cacheField.IsGroupField)
{
cacheItems = cacheField.FieldGroup.GroupItems;
}
else
{
cacheItems = cacheField.SharedItems;
}
return(cacheItems);
}
private List <Tuple <int, int> > ConvertGroupingTuples(List <Tuple <int, int> > tupleList, ExcelPivotTable pivotTable)
{
if (tupleList == null || pivotTable == null)
{
return(tupleList);
}
var convertedList = new List <Tuple <int, int> >();
foreach (var tuple in tupleList)
{
CacheFieldNode cacheField = tuple.Item1 == -2 ? null : pivotTable.CacheDefinition.CacheFields[tuple.Item1];
if (cacheField != null && cacheField.IsDateGrouping)
{
int pivotFieldItemValue = pivotTable.Fields[tuple.Item1].Items[tuple.Item2].X;
convertedList.Add(new Tuple <int, int>(tuple.Item1, pivotFieldItemValue));
}
else
{
convertedList.Add(tuple);
}
}
return(convertedList);
}
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);
}
