using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace iDiTect.Excel.Demo
{
    public static class PivotTablesHelper
    {
        public static void AddPivotTable()
        {
            var list = GetRandomData();

            var workbook = new Workbook();

            ///Add data table to sheet1
            var worksheet = workbook.Worksheets.Add("PivotData");
            //Bind data to cells
            var dataTable = worksheet.Cell(1, 1).InsertTable(list, "FruitSaleData", true);
            worksheet.Columns().AutoFitContents();


            ///Add a simple pivot table to second sheet
            var wsPivot1 = workbook.Worksheets.Add("Pivot1");
            //Create the pivot table, using the data from the "FruitSaleData" table
            var pivotTable1 = wsPivot1.PivotTables.Add("Fruit1", wsPivot1.Cell(1, 1), dataTable);

            //Add row field
            pivotTable1.RowLabels.Add("Name");
            //Add column field
            pivotTable1.ColumnLabels.Add("Title");
            // The values will be the "InCome" field, default is total calculation
            pivotTable1.Values.Add("InCome");
            //Customize the pivot table style
            pivotTable1.Theme = PivotTableTheme.PivotStyleDark1;


            ///Add a more complex pivot table to third sheet
            var wsPivot2 = workbook.Worksheets.Add("Pivot2");
            var pivotTable2 = wsPivot2.PivotTables.Add("Fruit2", wsPivot2.Cell(1, 1), dataTable);

            //Add first row field
            var rowField = pivotTable2.RowLabels.Add("Name");
            //Add Collapsed control to field
            rowField.SetCollapsed();
            //Add another row field
            pivotTable2.RowLabels.Add("OrderDate");
            
            //Add data fields with different summary type
            pivotTable2.Values.Add("BuyIn").SetSummaryFormula(PivotSummary.Average);
            pivotTable2.Values.Add("SaleOut").SetSummaryFormula(PivotSummary.Sum);
            //Add data field with target number format
            pivotTable2.Values.Add("InCome").NumberFormat.Format = "#,##0";

            //Add data filter to only show Surplus data
            var filterField = pivotTable2.ReportFilters.Add("Title");
            filterField.AddSelectedValue("Surplus");
            
            //Change row title
            pivotTable2.SetRowHeaderTitle("Fruit sample");

            workbook.Save("PivotTable.xlsx");
        }

        public class Product
        {
            public string Name { get; set; }
            public int BuyIn { get; set; }
            public int SaleOut { get; set; }
            public DateTime OrderDate { get; set; }
            public int InCome
            {
                get { return SaleOut - BuyIn; }
            }

            public string Title
            {
                get { return InCome >= 0 ? "Surplus" : "Loss"; }
            }
        }

        private static List<Product> GetRandomData()
        {
            List<Product> fruits = new List<Product>();
            var names = new string[] { "Orange", "Apple", "Banana", "Pear", "Grape", "Watermelon" };
            Random r = new Random();
            for (int i = 0; i < 20; i++)
            {
                fruits.Add(
                    new Product()
                    {
                        Name = names[r.Next(6)],
                        BuyIn = r.Next(50, 200),
                        SaleOut = r.Next(50, 200),
                        OrderDate = new DateTime(2000, 1, 1).AddDays(r.Next(1000)),
                    });
            }
            return fruits;
        }

    }    
}
