﻿/*******************************************************************************
 * 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.Collections.Generic;
using System.Text;
using System.Xml;
using System.Globalization;

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");
                }
                else
                {
                    return v;
                }
            }
            set
            {
                SetXmlNodeString("@name", value);
            }
        }
        /// <summary>
        /// Compact mode
        /// </summary>
        public bool Compact
        { 
            get
            {
                return 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
            {
                return GetXmlNodeBool("@outline");
            }
            set
            {
                SetXmlNodeBool("@outline",value);
            }
        }
        /// <summary>
        /// The custom text that is displayed for the subtotals label
        /// </summary>
        public bool SubtotalTop 
        { 
            get
            {
                return GetXmlNodeBool("@subtotalTop");
            }
            set
            {
                SetXmlNodeBool("@subtotalTop",value);
            }
        }
        /// <summary>
        /// A boolean that indicates whether to show all items for this field
        /// </summary>
        public bool ShowAll 
        { 
            get
            {
                return 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
            {
                return 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
            {
                return (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
            {
                return (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
        {
            get
            {
                return GetXmlNodeBool("@dataField", false);
            }
        }
        /// <summary>
        /// If the field is a page field.
        /// </summary>
        public bool IsPageField
        {
            get
            {
                return (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 ExcelPivotTablePageFieldSettings(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 = null;
        public ExcelPivotTablePageFieldSettings PageFieldSettings
        {
            get
            {
                return _pageFieldSettings;
            }
        }
        internal eDateGroupBy DateGrouping
        {
            get;
            set;
        }
        ExcelPivotTableFieldGroup _grouping=null;
        /// <summary>
        /// Grouping settings. 
        /// Null if the field has no grouping otherwise ExcelPivotTableFieldNumericGroup or ExcelPivotTableFieldNumericGroup.
        /// </summary>        
        public ExcelPivotTableFieldGroup Grouping
        {
            get
            {
                return _grouping;
            }
        }
        #region Private & internal Methods
        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);
                int fieldIndex;
                if(int.TryParse(x, out 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 = null;
        internal void SetCacheFieldNode(XmlNode cacheField)
        {
            _cacheFieldHelper = new XmlHelperInstance(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);
                }
            }
        }
        #endregion
        #region Grouping
        internal ExcelPivotTableFieldDateGroup SetDateGroup(eDateGroupBy GroupBy, DateTime StartDate, DateTime EndDate, int interval)
        {
            ExcelPivotTableFieldDateGroup group;
            group = new ExcelPivotTableFieldDateGroup(NameSpaceManager, _cacheFieldHelper.TopNode);
            _cacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsDate", true);
            _cacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsNonDate", false);
            _cacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsSemiMixedTypes", false);

            group.TopNode.InnerXml += string.Format("<fieldGroup base=\"{0}\"><rangePr groupBy=\"{1}\" /><groupItems /></fieldGroup>", BaseIndex, GroupBy.ToString().ToLower(CultureInfo.InvariantCulture));

            if (StartDate.Year < 1900)
            {
                _cacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@startDate", "1900-01-01T00:00:00");
            }
            else
            {
                _cacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@startDate", StartDate.ToString("s", CultureInfo.InvariantCulture));
                _cacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@autoStart", "0");
            }

            if (EndDate==DateTime.MaxValue)
            {
                _cacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@endDate", "9999-12-31T00:00:00");
            }
            else
            {
                _cacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@endDate", EndDate.ToString("s", CultureInfo.InvariantCulture));
                _cacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@autoEnd", "0");
            }

            int items = AddDateGroupItems(group, GroupBy, StartDate, EndDate, interval);
            AddFieldItems(items);

            _grouping = group;
            return group;
        }
        internal ExcelPivotTableFieldNumericGroup SetNumericGroup(double start, double end, double interval)
        {
            ExcelPivotTableFieldNumericGroup group;
            group = new ExcelPivotTableFieldNumericGroup(NameSpaceManager, _cacheFieldHelper.TopNode);
            _cacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsNumber", true);
            _cacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsInteger", true);
            _cacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsSemiMixedTypes", false);
            _cacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsString", false);

            group.TopNode.InnerXml += string.Format("<fieldGroup base=\"{0}\"><rangePr autoStart=\"0\" autoEnd=\"0\" startNum=\"{1}\" endNum=\"{2}\" groupInterval=\"{3}\"/><groupItems /></fieldGroup>", BaseIndex, start.ToString(CultureInfo.InvariantCulture), end.ToString(CultureInfo.InvariantCulture), interval.ToString(CultureInfo.InvariantCulture));
            int items = AddNumericGroupItems(group, start, end, interval);
            AddFieldItems(items);

            _grouping = group;
            return group;
        }

        private int AddNumericGroupItems(ExcelPivotTableFieldNumericGroup group, double start, double end, double interval)
        {
            if (interval < 0)
            {
                throw (new Exception("The interval must be a positiv"));
            }
            if (start > end)
            {
                throw(new Exception("Then End number must be larger than the Start number"));
            }

            XmlElement groupItems = group.TopNode.SelectSingleNode("d:fieldGroup/d:groupItems", group.NameSpaceManager) as XmlElement;
            int items = 2;
            //First date
            double index=start;
            double nextIndex=start+interval;
            AddGroupItem(groupItems, "<" + start.ToString(CultureInfo.InvariantCulture));

            while (index < end)
            {
                AddGroupItem(groupItems, string.Format("{0}-{1}", index.ToString(CultureInfo.InvariantCulture), nextIndex.ToString(CultureInfo.InvariantCulture)));
                index=nextIndex;
                nextIndex+=interval;
                items++;
            }
            AddGroupItem(groupItems, ">" + nextIndex.ToString(CultureInfo.InvariantCulture));
            return items;
        }

        private void AddFieldItems(int items)
        {
            XmlElement prevNode = null;
            XmlElement itemsNode = TopNode.SelectSingleNode("d:items", NameSpaceManager) as XmlElement;
            for (int x = 0; x < items; x++)
            {
                var itemNode = itemsNode.OwnerDocument.CreateElement("item", ExcelPackage.schemaMain);
                itemNode.SetAttribute("x", x.ToString());
                if (prevNode == null)
                {
                    itemsNode.PrependChild(itemNode);
                }
                else
                {
                    itemsNode.InsertAfter(itemNode, prevNode);
                }
                prevNode = itemNode;
            }
            itemsNode.SetAttribute("count", (items + 1).ToString());
        }

        private int AddDateGroupItems(ExcelPivotTableFieldGroup group, eDateGroupBy GroupBy, DateTime StartDate, DateTime EndDate, int interval)
        {
            XmlElement groupItems = group.TopNode.SelectSingleNode("d:fieldGroup/d:groupItems", group.NameSpaceManager) as XmlElement;
            int items = 2;
            //First date
            AddGroupItem(groupItems, "<" + StartDate.ToString("s", CultureInfo.InvariantCulture).Substring(0, 10));

            switch (GroupBy)
            {
                case eDateGroupBy.Seconds:
                case eDateGroupBy.Minutes:
                    AddTimeSerie(60, groupItems);
                    items += 60;
                    break;
                case eDateGroupBy.Hours:
                    AddTimeSerie(24, groupItems);
                    items += 24;
                    break;
                case eDateGroupBy.Days:
                    if (interval == 1)
                    {
                        DateTime dt = new DateTime(2008, 1, 1); //pick a year with 366 days
                        while (dt.Year == 2008)
                        {
                            AddGroupItem(groupItems, dt.ToString("dd-MMM"));
                            dt = dt.AddDays(1);
                        }
                        items += 366;
                    }
                    else
                    {
                        DateTime dt = StartDate;
                        items = 0;
                        while (dt < EndDate)
                        {
                            AddGroupItem(groupItems, dt.ToString("dd-MMM"));
                            dt = dt.AddDays(interval);
                            items++;
                        }
                    }
                    break;
                case eDateGroupBy.Months:
                    AddGroupItem(groupItems, "jan");
                    AddGroupItem(groupItems, "feb");
                    AddGroupItem(groupItems, "mar");
                    AddGroupItem(groupItems, "apr");
                    AddGroupItem(groupItems, "may");
                    AddGroupItem(groupItems, "jun");
                    AddGroupItem(groupItems, "jul");
                    AddGroupItem(groupItems, "aug");
                    AddGroupItem(groupItems, "sep");
                    AddGroupItem(groupItems, "oct");
                    AddGroupItem(groupItems, "nov");
                    AddGroupItem(groupItems, "dec");
                    items += 12;
                    break;
                case eDateGroupBy.Quarters:
                    AddGroupItem(groupItems, "Qtr1");
                    AddGroupItem(groupItems, "Qtr2");
                    AddGroupItem(groupItems, "Qtr3");
                    AddGroupItem(groupItems, "Qtr4");
                    items += 4;
                    break;
                case eDateGroupBy.Years:
                    if(StartDate.Year>=1900 && EndDate!=DateTime.MaxValue)
                    {
                        for (int year = StartDate.Year; year <= EndDate.Year; year++)
                        {
                            AddGroupItem(groupItems, year.ToString());
                        }
                        items += EndDate.Year - StartDate.Year+1;
                    }
                    break;
                default:
                    throw (new Exception("unsupported grouping"));
            }

            //Lastdate
            AddGroupItem(groupItems, ">" + EndDate.ToString("s", CultureInfo.InvariantCulture).Substring(0, 10));
            return items;
        }

        private void AddTimeSerie(int count, XmlElement groupItems)
        {
            for (int i = 0; i < count; i++)
            {
                AddGroupItem(groupItems, string.Format("{0:00}", i));
            }
        }

        private void AddGroupItem(XmlElement groupItems, string value)
        {
            var s = groupItems.OwnerDocument.CreateElement("s", ExcelPackage.schemaMain);
            s.SetAttribute("v", value);
            groupItems.AppendChild(s);
        }
        #endregion
        internal ExcelPivotTableFieldCollectionBase<ExcelPivotTableFieldItem> _items=null;
        /// <summary>
        /// Pivottable field Items. Used for grouping.
        /// </summary>
        public ExcelPivotTableFieldCollectionBase<ExcelPivotTableFieldItem> Items
        {
            get
            {
                if (_items == null)
                {
                    _items = new ExcelPivotTableFieldCollectionBase<ExcelPivotTableFieldItem>(_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);
                        }
                    }
                    //if (_grouping is ExcelPivotTableFieldDateGroup)
                    //{
                    //    ExcelPivotTableFieldDateGroup dtgrp = ((ExcelPivotTableFieldDateGroup)_grouping);

                    //    ExcelPivotTableFieldItem minItem=null, maxItem=null;
                    //    foreach (var item in _items)
                    //    {
                    //        if (item.X == 0)
                    //        {
                    //            minItem = item;
                    //        }
                    //        else if (maxItem == null || maxItem.X < item.X)
                    //        {
                    //            maxItem = item;
                    //        }
                    //    }
                    //    if (dtgrp.AutoStart)
                    //    {
                    //        _items._list.Remove(minItem);
                    //    }
                    //    if (dtgrp.AutoEnd)
                    //    {
                    //        _items._list.Remove(maxItem);
                    //    }

                    //}
                }
                return _items;
            }
        }
        /// <summary>
        /// Add numberic grouping to the field
        /// </summary>
        /// <param name="Start">Start value</param>
        /// <param name="End">End value</param>
        /// <param name="Interval">Interval</param>
        public void AddNumericGrouping(double Start, double End, double Interval)
        {
            ValidateGrouping();
            SetNumericGroup(Start, End, Interval);
        }
        /// <summary>
        /// Add a date grouping on this field.
        /// </summary>
        /// <param name="groupBy">Group by</param>
        public void AddDateGrouping(eDateGroupBy groupBy)
        {
            AddDateGrouping(groupBy, DateTime.MinValue, DateTime.MaxValue,1);
        }
        /// <summary>
        /// Add a date grouping on this field.
        /// </summary>
        /// <param name="groupBy">Group by</param>
        /// <param name="startDate">Fixed start date. Use DateTime.MinValue for auto</param>
        /// <param name="endDate">Fixed end date. Use DateTime.MaxValue for auto</param>
        public void AddDateGrouping(eDateGroupBy groupBy, DateTime startDate, DateTime endDate)
        {
            AddDateGrouping(groupBy, startDate, endDate, 1);
        }
        /// <summary>
        /// Add a date grouping on this field.
        /// </summary>
        /// <param name="days">Number of days when grouping on days</param>
        /// <param name="startDate">Fixed start date. Use DateTime.MinValue for auto</param>
        /// <param name="endDate">Fixed end date. Use DateTime.MaxValue for auto</param>
        public void AddDateGrouping(int days, DateTime startDate, DateTime endDate)
        {
            AddDateGrouping(eDateGroupBy.Days, startDate, endDate, days);
        }
        private void AddDateGrouping(eDateGroupBy groupBy, DateTime startDate, DateTime endDate, int groupInterval)
        {
            if (groupInterval < 1 || groupInterval >= Int16.MaxValue)
            {
                throw (new ArgumentOutOfRangeException("Group interval is out of range"));
            }
            if (groupInterval > 1 && groupBy != eDateGroupBy.Days)
            {
                throw (new ArgumentException("Group interval is can only be used when groupBy is Days"));
            }
            ValidateGrouping();

            bool firstField = true;            
            List<ExcelPivotTableField> fields=new List<ExcelPivotTableField>();
            //Seconds
            if ((groupBy & eDateGroupBy.Seconds) == eDateGroupBy.Seconds)
            {
                fields.Add(AddField(eDateGroupBy.Seconds, startDate, endDate, ref firstField));
            }
            //Minutes
            if ((groupBy & eDateGroupBy.Minutes) == eDateGroupBy.Minutes)
            {
                fields.Add(AddField(eDateGroupBy.Minutes, startDate, endDate, ref firstField));
            }
            //Hours
            if ((groupBy & eDateGroupBy.Hours) == eDateGroupBy.Hours)
            {
                fields.Add(AddField(eDateGroupBy.Hours, startDate, endDate, ref firstField));
            }
            //Days
            if ((groupBy & eDateGroupBy.Days) == eDateGroupBy.Days)
            {
                fields.Add(AddField(eDateGroupBy.Days, startDate, endDate, ref firstField, groupInterval));
            }
            //Month
            if ((groupBy & eDateGroupBy.Months) == eDateGroupBy.Months)
            {
                fields.Add(AddField(eDateGroupBy.Months, startDate, endDate, ref firstField));
            }
            //Quarters
            if ((groupBy & eDateGroupBy.Quarters) == eDateGroupBy.Quarters)
            {
                fields.Add(AddField(eDateGroupBy.Quarters, startDate, endDate, ref firstField));
            }
            //Years
            if ((groupBy & eDateGroupBy.Years) == eDateGroupBy.Years)
            {
                fields.Add(AddField(eDateGroupBy.Years, startDate, endDate, ref firstField));
            }

            if (fields.Count > 1) _cacheFieldHelper.SetXmlNodeString("d:fieldGroup/@par", (_table.Fields.Count - 1).ToString());
            if (groupInterval != 1)
            {
                _cacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@groupInterval", groupInterval.ToString());
            }
            else
            {
                _cacheFieldHelper.DeleteNode("d:fieldGroup/d:rangePr/@groupInterval");
            }
            _items = null;
        }

        private void ValidateGrouping()
        {
            if (!(IsColumnField || IsRowField))
            {
                throw (new Exception("Field must be a row or column field"));
            }
            foreach (var field in _table.Fields)
            {
                if (field.Grouping != null)
                {
                    throw (new Exception("Grouping already exists"));
                }
            }
        }
        private ExcelPivotTableField AddField(eDateGroupBy groupBy, DateTime startDate, DateTime endDate, ref  bool firstField)
        {
            return AddField(groupBy, startDate, endDate, ref firstField,1);
        }
        private ExcelPivotTableField AddField(eDateGroupBy groupBy, DateTime startDate, DateTime endDate, ref  bool firstField, int interval)
        {
            if (firstField == false)
            {
                //Pivot field
                var topNode = _table.PivotTableXml.SelectSingleNode("//d:pivotFields", _table.NameSpaceManager);
                var fieldNode = _table.PivotTableXml.CreateElement("pivotField", ExcelPackage.schemaMain);
                fieldNode.SetAttribute("compact", "0");
                fieldNode.SetAttribute("outline", "0");
                fieldNode.SetAttribute("showAll", "0");
                fieldNode.SetAttribute("defaultSubtotal", "0");
                topNode.AppendChild(fieldNode);

                var field = new ExcelPivotTableField(_table.NameSpaceManager, fieldNode, _table, _table.Fields.Count, Index);
                field.DateGrouping = groupBy;

                XmlNode rowColFields;
                if (IsRowField)
                {
                    rowColFields=TopNode.SelectSingleNode("../../d:rowFields", NameSpaceManager);
                }
                else
                {
                    rowColFields = TopNode.SelectSingleNode("../../d:colFields", NameSpaceManager);
                }

                int fieldIndex, index = 0;
                foreach (XmlElement rowfield in rowColFields.ChildNodes)
                {
                    if (int.TryParse(rowfield.GetAttribute("x"), out fieldIndex))
                    {
                        if (_table.Fields[fieldIndex].BaseIndex == BaseIndex)
                        {
                            var newElement = rowColFields.OwnerDocument.CreateElement("field", ExcelPackage.schemaMain);
                            newElement.SetAttribute("x", field.Index.ToString());
                            rowColFields.InsertBefore(newElement, rowfield);
                            break;
                        }
                    }
                    index++;
                }

                if (IsRowField)
                {
                    _table.RowFields.Insert(field, index);
                }
                else
                {
                    _table.ColumnFields.Insert(field, index);
                }
                
                _table.Fields.AddInternal(field);

                AddCacheField(field, startDate, endDate, interval);
                return field;
            }
            else
            {
                firstField = false;
                DateGrouping = groupBy;
                Compact = false;
                SetDateGroup(groupBy, startDate, endDate, interval);
                return this;
            }
        }
        private void AddCacheField(ExcelPivotTableField field, DateTime startDate, DateTime endDate, int interval)
        {
            //Add Cache definition field.
            var cacheTopNode = _table.CacheDefinition.CacheDefinitionXml.SelectSingleNode("//d:cacheFields", _table.NameSpaceManager);
            var cacheFieldNode = _table.CacheDefinition.CacheDefinitionXml.CreateElement("cacheField", ExcelPackage.schemaMain);

            cacheFieldNode.SetAttribute("name", field.DateGrouping.ToString());
            cacheFieldNode.SetAttribute("databaseField", "0");
            cacheTopNode.AppendChild(cacheFieldNode);
            field.SetCacheFieldNode(cacheFieldNode);

            field.SetDateGroup(field.DateGrouping, startDate, endDate, interval);
        }
    }
}