EPPlus/Table/PivotTable/ExcelPivotTableField.cs

/*******************************************************************************
 * You may amend and distribute as you like, but don't remove this header!
 *
 * EPPlus provides server-side generation of Excel 2007/2010 spreadsheets.
 * See http://www.codeplex.com/EPPlus for details.
 *
 * Copyright (C) 2011  Jan Källman
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.

 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 * See the GNU Lesser General Public License for more details.
 *
 * The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php
 * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
 *
 * All code and executables are provided "as is" with no warranty either express or implied.
 * The author accepts no liability for any damage or loss of business that this product may cause.
 *
 * Code change notes:
 *
 * Author							Change						Date
 * ******************************************************************************
 * Jan Källman		Added		21-MAR-2011
 * Jan Källman		License changed GPL-->LGPL 2011-12-16
 *******************************************************************************/

using System;
using System.Globalization;
using System.Xml;

namespace OfficeOpenXml.Table.PivotTable;

/// <summary>
/// Defines the axis for a PivotTable
/// </summary>
public enum ePivotFieldAxis {
  /// <summary>
  /// None
  /// </summary>
  None = -1,

  /// <summary>
  /// Column axis
  /// </summary>
  Column,

  /// <summary>
  /// Page axis (Include Count Filter)
  ///
  /// </summary>
  Page,

  /// <summary>
  /// Row axis
  /// </summary>
  Row,

  /// <summary>
  /// Values axis
  /// </summary>
  Values,
}

/// <summary>
/// Build-in table row functions
/// </summary>
public enum DataFieldFunctions {
  Average,
  Count,
  CountNums,
  Max,
  Min,
  Product,
  None,
  StdDev,
  StdDevP,
  Sum,
  Var,
  VarP,
}

/// <summary>
/// Defines the data formats for a field in the PivotTable
/// </summary>
public enum eShowDataAs {
  /// <summary>
  /// Indicates the field is shown as the "difference from" a value.
  /// </summary>
  Difference,

  /// <summary>
  /// Indicates the field is shown as the "index.
  /// </summary>
  Index,

  /// <summary>
  /// Indicates that the field is shown as its normal datatype.
  /// </summary>
  Normal,

  /// <summary>
  /// /Indicates the field is show as the "percentage of" a value
  /// </summary>
  Percent,

  /// <summary>
  /// Indicates the field is shown as the "percentage difference from" a value.
  /// </summary>
  PercentDiff,

  /// <summary>
  /// Indicates the field is shown as the percentage of column.
  /// </summary>
  PercentOfCol,

  /// <summary>
  /// Indicates the field is shown as the percentage of row
  /// </summary>
  PercentOfRow,

  /// <summary>
  /// Indicates the field is shown as percentage of total.
  /// </summary>
  PercentOfTotal,

  /// <summary>
  /// Indicates the field is shown as running total in the table.
  /// </summary>
  RunTotal,
}

/// <summary>
/// Built-in subtotal functions
/// </summary>
[Flags]
public enum eSubTotalFunctions {
  None = 1,
  Count = 2,
  CountA = 4,
  Avg = 8,
  Default = 16,
  Min = 32,
  Max = 64,
  Product = 128,
  StdDev = 256,
  StdDevP = 512,
  Sum = 1024,
  Var = 2048,
  VarP = 4096,
}

/// <summary>
/// Data grouping
/// </summary>
[Flags]
public enum eDateGroupBy {
  Years = 1,
  Quarters = 2,
  Months = 4,
  Days = 8,
  Hours = 16,
  Minutes = 32,
  Seconds = 64,
}

/// <summary>
/// Sorting
/// </summary>
public enum eSortType {
  None,
  Ascending,
  Descending,
}

/// <summary>
/// A pivot table field.
/// </summary>
public class ExcelPivotTableField : XmlHelper {
  internal ExcelPivotTable _table;

  internal ExcelPivotTableField(
      XmlNamespaceManager ns,
      XmlNode topNode,
      ExcelPivotTable table,
      int index,
      int baseIndex)
      : base(ns, topNode) {
    Index = index;
    BaseIndex = baseIndex;
    _table = table;
  }

  public int Index { get; set; }

  internal int BaseIndex { get; set; }

  /// <summary>
  /// Name of the field
  /// </summary>
  public string Name {
    get {
      string v = GetXmlNodeString("@name");
      if (v == "") {
        return _cacheFieldHelper.GetXmlNodeString("@name");
      }
      return v;
    }
    set => SetXmlNodeString("@name", value);
  }

  /// <summary>
  /// Compact mode
  /// </summary>
  public bool Compact {
    get => GetXmlNodeBool("@compact");
    set => SetXmlNodeBool("@compact", value);
  }

  /// <summary>
  /// A boolean that indicates whether the items in this field should be shown in Outline form
  /// </summary>
  public bool Outline {
    get => GetXmlNodeBool("@outline");
    set => SetXmlNodeBool("@outline", value);
  }

  /// <summary>
  /// The custom text that is displayed for the subtotals label
  /// </summary>
  public bool SubtotalTop {
    get => GetXmlNodeBool("@subtotalTop");
    set => SetXmlNodeBool("@subtotalTop", value);
  }

  /// <summary>
  /// A boolean that indicates whether to show all items for this field
  /// </summary>
  public bool ShowAll {
    get => GetXmlNodeBool("@showAll");
    set => SetXmlNodeBool("@showAll", value);
  }

  /// <summary>
  /// The type of sort that is applied to this field
  /// </summary>
  public eSortType Sort {
    get {
      string v = GetXmlNodeString("@sortType");
      return v == "" ? eSortType.None : (eSortType)Enum.Parse(typeof(eSortType), v, true);
    }
    set {
      if (value == eSortType.None) {
        DeleteNode("@sortType");
      } else {
        SetXmlNodeString("@sortType", value.ToString().ToLower(CultureInfo.InvariantCulture));
      }
    }
  }

  /// <summary>
  /// A boolean that indicates whether manual filter is in inclusive mode
  /// </summary>
  public bool IncludeNewItemsInFilter {
    get => GetXmlNodeBool("@includeNewItemsInFilter");
    set => SetXmlNodeBool("@includeNewItemsInFilter", value);
  }

  /// <summary>
  /// Enumeration of the different subtotal operations that can be applied to page, row or column fields
  /// </summary>
  public eSubTotalFunctions SubTotalFunctions {
    get {
      eSubTotalFunctions ret = 0;
      XmlNodeList nl = TopNode.SelectNodes("d:items/d:item/@t", NameSpaceManager);
      if (nl.Count == 0) {
        return eSubTotalFunctions.None;
      }
      foreach (XmlAttribute item in nl) {
        try {
          ret |= (eSubTotalFunctions)Enum.Parse(typeof(eSubTotalFunctions), item.Value, true);
        } catch (ArgumentException ex) {
          throw new ArgumentException(
              "Unable to parse value of "
                  + item.Value
                  + " to a valid pivot table subtotal function",
              ex);
        }
      }
      return ret;
    }
    set {
      if ((value & eSubTotalFunctions.None) == eSubTotalFunctions.None
          && (value != eSubTotalFunctions.None)) {
        throw (new ArgumentException("Value None can not be combined with other values."));
      }
      if ((value & eSubTotalFunctions.Default) == eSubTotalFunctions.Default
          && (value != eSubTotalFunctions.Default)) {
        throw (new ArgumentException("Value Default can not be combined with other values."));
      }

      // remove old attribute
      XmlNodeList nl = TopNode.SelectNodes("d:items/d:item/@t", NameSpaceManager);
      if (nl.Count > 0) {
        foreach (XmlAttribute item in nl) {
          DeleteNode("@" + item.Value + "Subtotal");
          item.OwnerElement.ParentNode.RemoveChild(item.OwnerElement);
        }
      }

      if (value == eSubTotalFunctions.None) {
        // for no subtotals, set defaultSubtotal to off
        SetXmlNodeBool("@defaultSubtotal", false);
        TopNode.InnerXml = "";
      } else {
        string innerXml = "";
        int count = 0;
        foreach (eSubTotalFunctions e in Enum.GetValues(typeof(eSubTotalFunctions))) {
          if ((value & e) == e) {
            var newTotalType = e.ToString();
            var totalType =
                char.ToLower(newTotalType[0], CultureInfo.InvariantCulture)
                    + newTotalType.Substring(1);
            // add new attribute
            SetXmlNodeBool("@" + totalType + "Subtotal", true);
            innerXml += "<item t=\"" + totalType + "\" />";
            count++;
          }
        }
        TopNode.InnerXml = string.Format("<items count=\"{0}\">{1}</items>", count, innerXml);
      }
    }
  }

  /// <summary>
  /// Type of axis
  /// </summary>
  public ePivotFieldAxis Axis {
    get {
      switch (GetXmlNodeString("@axis")) {
        case "axisRow":
          return ePivotFieldAxis.Row;
        case "axisCol":
          return ePivotFieldAxis.Column;
        case "axisPage":
          return ePivotFieldAxis.Page;
        case "axisValues":
          return ePivotFieldAxis.Values;
        default:
          return ePivotFieldAxis.None;
      }
    }
    internal set {
      switch (value) {
        case ePivotFieldAxis.Row:
          SetXmlNodeString("@axis", "axisRow");
          break;
        case ePivotFieldAxis.Column:
          SetXmlNodeString("@axis", "axisCol");
          break;
        case ePivotFieldAxis.Values:
          SetXmlNodeString("@axis", "axisValues");
          break;
        case ePivotFieldAxis.Page:
          SetXmlNodeString("@axis", "axisPage");
          break;
        default:
          DeleteNode("@axis");
          break;
      }
    }
  }

  /// <summary>
  /// If the field is a row field
  /// </summary>
  public bool IsRowField {
    get =>
      (TopNode.SelectSingleNode(
              string.Format("../../d:rowFields/d:field[@x={0}]", Index),
              NameSpaceManager) != null);
    internal set {
      if (value) {
        var rowsNode = TopNode.SelectSingleNode("../../d:rowFields", NameSpaceManager);
        if (rowsNode == null) {
          _table.CreateNode("d:rowFields");
        }
        rowsNode = TopNode.SelectSingleNode("../../d:rowFields", NameSpaceManager);

        AppendField(rowsNode, Index, "field", "x");
        if (BaseIndex == Index) {
          TopNode.InnerXml = "<items count=\"1\"><item t=\"default\" /></items>";
        } else {
          TopNode.InnerXml = "<items count=\"0\"></items>";
        }
      } else {
        XmlElement node =
            TopNode.SelectSingleNode(
                string.Format("../../d:rowFields/d:field[@x={0}]", Index),
                NameSpaceManager) as XmlElement;
        if (node != null) {
          node.ParentNode.RemoveChild(node);
        }
      }
    }
  }

  /// <summary>
  /// If the field is a column field
  /// </summary>
  public bool IsColumnField {
    get =>
      (TopNode.SelectSingleNode(
              string.Format("../../d:colFields/d:field[@x={0}]", Index),
              NameSpaceManager) != null);
    internal set {
      if (value) {
        var columnsNode = TopNode.SelectSingleNode("../../d:colFields", NameSpaceManager);
        if (columnsNode == null) {
          _table.CreateNode("d:colFields");
        }
        columnsNode = TopNode.SelectSingleNode("../../d:colFields", NameSpaceManager);

        AppendField(columnsNode, Index, "field", "x");
        if (BaseIndex == Index) {
          TopNode.InnerXml = "<items count=\"1\"><item t=\"default\" /></items>";
        } else {
          TopNode.InnerXml = "<items count=\"0\"></items>";
        }
      } else {
        XmlElement node =
            TopNode.SelectSingleNode(
                string.Format("../../d:colFields/d:field[@x={0}]", Index),
                NameSpaceManager) as XmlElement;
        if (node != null) {
          node.ParentNode.RemoveChild(node);
        }
      }
    }
  }

  /// <summary>
  /// If the field is a datafield
  /// </summary>
  public bool IsDataField => GetXmlNodeBool("@dataField", false);

  /// <summary>
  /// If the field is a page field.
  /// </summary>
  public bool IsPageField {
    get => (Axis == ePivotFieldAxis.Page);
    internal set {
      if (value) {
        var dataFieldsNode = TopNode.SelectSingleNode("../../d:pageFields", NameSpaceManager);
        if (dataFieldsNode == null) {
          _table.CreateNode("d:pageFields");
          dataFieldsNode = TopNode.SelectSingleNode("../../d:pageFields", NameSpaceManager);
        }

        TopNode.InnerXml = "<items count=\"1\"><item t=\"default\" /></items>";

        XmlElement node = AppendField(dataFieldsNode, Index, "pageField", "fld");
        _pageFieldSettings = new(NameSpaceManager, node, this, Index);
      } else {
        _pageFieldSettings = null;
        XmlElement node =
            TopNode.SelectSingleNode(
                string.Format("../../d:pageFields/d:pageField[@fld={0}]", Index),
                NameSpaceManager) as XmlElement;
        if (node != null) {
          node.ParentNode.RemoveChild(node);
        }
      }
    }
  }

  //public ExcelPivotGrouping DateGrouping
  //{

  //}
  internal ExcelPivotTablePageFieldSettings _pageFieldSettings;

  public ExcelPivotTablePageFieldSettings PageFieldSettings => _pageFieldSettings;

  internal eDateGroupBy DateGrouping { get; set; }

  private ExcelPivotTableFieldGroup _grouping;

  /// <summary>
  /// Grouping settings.
  /// Null if the field has no grouping otherwise ExcelPivotTableFieldNumericGroup or ExcelPivotTableFieldNumericGroup.
  /// </summary>
  public ExcelPivotTableFieldGroup Grouping => _grouping;

  internal XmlElement AppendField(
      XmlNode rowsNode,
      int index,
      string fieldNodeText,
      string indexAttrText) {
    XmlElement prevField = null,
        newElement;
    foreach (XmlElement field in rowsNode.ChildNodes) {
      string x = field.GetAttribute(indexAttrText);
      if (int.TryParse(x, out var fieldIndex)) {
        if (fieldIndex
            == index) //Row already exists
        {
          return field;
        }
        //else if (fieldIndex > index)
        //{
        //    newElement = rowsNode.OwnerDocument.CreateElement(fieldNodeText, ExcelPackage.schemaMain);
        //    newElement.SetAttribute(indexAttrText, index.ToString());
        //    rowsNode.InsertAfter(newElement, field);
        //}
      }
      prevField = field;
    }
    newElement = rowsNode.OwnerDocument.CreateElement(fieldNodeText, ExcelPackage._schemaMain);
    newElement.SetAttribute(indexAttrText, index.ToString());
    rowsNode.InsertAfter(newElement, prevField);

    return newElement;
  }

  internal XmlHelperInstance _cacheFieldHelper;

  internal void SetCacheFieldNode(XmlNode cacheField) {
    _cacheFieldHelper = new(NameSpaceManager, cacheField);
    var groupNode = cacheField.SelectSingleNode("d:fieldGroup", NameSpaceManager);
    if (groupNode != null) {
      var groupBy = groupNode.SelectSingleNode("d:rangePr/@groupBy", NameSpaceManager);
      if (groupBy == null) {
        _grouping = new ExcelPivotTableFieldNumericGroup(NameSpaceManager, cacheField);
      } else {
        DateGrouping = (eDateGroupBy)Enum.Parse(typeof(eDateGroupBy), groupBy.Value, true);
        _grouping = new ExcelPivotTableFieldDateGroup(NameSpaceManager, groupNode);
      }
    }
  }

  internal ExcelPivotTableFieldCollectionBase<ExcelPivotTableFieldItem> _items;

  /// <summary>
  /// Pivottable field Items. Used for grouping.
  /// </summary>
  public ExcelPivotTableFieldCollectionBase<ExcelPivotTableFieldItem> Items {
    get {
      if (_items == null) {
        _items = new(_table);
        foreach (XmlNode node in TopNode.SelectNodes("d:items//d:item", NameSpaceManager)) {
          var item = new ExcelPivotTableFieldItem(NameSpaceManager, node, this);
          if (item.T == "") {
            _items.AddInternal(item);
          }
        }
      }
      return _items;
    }
  }
}