using DataConnectorExplorer.Models;
using System.Collections;
using Microsoft.AspNetCore.Mvc;
using System.Linq;
using System.Collections.Generic;
using C1.Web.Mvc.Olap;
using C1.AdoNet.GoogleAnalytics;
using System.Globalization;
using System;
using Microsoft.Extensions.Options;
using System.IO;

namespace DataConnectorExplorer.Controllers
{
    /// <summary>
    /// 
    /// </summary>
    public class GoogleAnalyticsController : Controller
    {
        private readonly IOptions<GoogleAnalyticsConnection> config;
        /// <summary>
        /// 
        /// </summary>
        /// <param name="config"></param>
        public GoogleAnalyticsController(IOptions<GoogleAnalyticsConnection> config)
        {
            this.config = config;
        }
        private IEnumerable<GoogleAnalyticsTrafficData> GetData()
        {
            //Create the connection to Google Analytics server
            //Ex:
            //const string keyFile = @"Wijmo-53f854dc3ad3.json";
            //const string viewId = @"1243234";
            //string connectionString = string.Format("Key File={0};View Id={1}", config.KeyFile, config.ViewId);


            var config = this.config.Value;
            string _cacheFolder = "Cache";
            if (!Directory.Exists(_cacheFolder))
            {
                Directory.CreateDirectory(_cacheFolder);
            }
            string specifiedCacheFile = Path.Combine(_cacheFolder, "C1CacheFile.GA.db");
            string connectionStringCachePart = string.Format("Use Cache = 'true'; Cache Location='{0}'; Cache Tolerance = 28800", specifiedCacheFile);

            string connectionString = string.Format("Key File={0};View Id={1};", config.KeyFile, config.ViewId);
            connectionString += connectionStringCachePart;
            using (C1GoogleAnalyticsConnection con = new C1GoogleAnalyticsConnection(connectionString))
            {
                con.Open();
                var cmd = con.CreateCommand();
                //Query to select from Google Analytics server.
                cmd.CommandText = "Select Sessions,Browser,Source,City,OperatingSystem,Country,Month from Traffic where Country is not null";

                var reader = cmd.ExecuteReader();
                List<GoogleAnalyticsTrafficData> result = new List<GoogleAnalyticsTrafficData>();
                CultureInfo provider = CultureInfo.InvariantCulture;
                //Generate List<GoogleAnalyticsTrafficData> from reader
                var count = 0;
                while (reader.Read() && count < 1000)
                {                    
                    //Put the default value if Sessions is null. Just use for demo
                    var sessions = String.IsNullOrEmpty(reader["Sessions"].ToString()) ? 0 : Convert.ToInt32(reader["Sessions"].ToString());
                    var browser = reader["Browser"].ToString();
                    var source = reader["Source"].ToString();
                    var city = reader["City"].ToString();
                    var operatingSystem = reader["OperatingSystem"].ToString();
                    var country = reader["Country"].ToString();
                    var month = reader["Month"].ToString();
                    if(!country.Equals("(not set)") 
                        && !browser.Equals("(not set)") 
                        && !source.Equals("(not set)")
                        && !city.Equals("(not set)")
                        && !source.Equals("(not set)")
                        && !operatingSystem.Equals("(not set)"))
                    {
                        //Add GoogleAnalyticsTrafficData model from the reader
                        result.Add(new GoogleAnalyticsTrafficData
                        {
                            Sessions = sessions,
                            Browser = browser,
                            Source = source,
                            City = city,
                            OperatingSystem = operatingSystem,
                            Country = country,
                            Month = month,
                        });
                        count++;
                    }

                    
                }
                return result;
            }
        }

        private readonly ClientSettingsModel OlapModel = new ClientSettingsModel
        {
            Settings = new Dictionary<string, object[]>
            {
                {"RowTotals", new object[] { ShowTotals.Subtotals, ShowTotals.None, ShowTotals.GrandTotals} },
                {"ColumnTotals", new object[] { ShowTotals.Subtotals, ShowTotals.None, ShowTotals.GrandTotals} },
                {"ShowZeros", new object[] { false, true } },
                {"AllowMerging", new object[] {
                        C1.Web.Mvc.Grid.AllowMerging.All,
                        C1.Web.Mvc.Grid.AllowMerging.AllHeaders,
                        C1.Web.Mvc.Grid.AllowMerging.Cells,
                        C1.Web.Mvc.Grid.AllowMerging.ColumnHeaders,
                        C1.Web.Mvc.Grid.AllowMerging.None,
                        C1.Web.Mvc.Grid.AllowMerging.RowHeaders
                    }
                }
            }
        };

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public IActionResult Index()
        {
            IEnumerable Data = GetData().ToList();
            OlapModel.ControlId = "indexPanel";
            ViewBag.DemoOptions = OlapModel;
            return View(Data);
        }
    }
}

@model IEnumerable<GoogleAnalyticsTrafficData>
@{
    ClientSettingsModel optionsModel = ViewBag.DemoOptions;
}

<c1-pivot-engine id="indexEngine" show-row-totals="Subtotals" show-column-totals="Subtotals" totals-before-data="false">
    <c1-items-source source-collection="Model"></c1-items-source>

    <c1-pivot-field-collection>
        <c1-pivot-field header="Country" binding="Country"></c1-pivot-field>
        <c1-pivot-field header="City" binding="City"></c1-pivot-field>
        <c1-pivot-field header="Operating System" binding="OperatingSystem"></c1-pivot-field>
        <c1-pivot-field header="Source" binding="Source"></c1-pivot-field>
        <c1-pivot-field header="Browser" binding="Browser"></c1-pivot-field>
        <c1-pivot-field header="Month" binding="Month"></c1-pivot-field>
        <c1-pivot-field header="Sessions" binding="Sessions"></c1-pivot-field>
    </c1-pivot-field-collection>
    <c1-view-field-collection c1-property="RowFields" items="Country"></c1-view-field-collection>
    <c1-view-field-collection c1-property="ColumnFields" items="Operating System"></c1-view-field-collection>
    <c1-view-field-collection c1-property="ValueFields" items="Sessions" ca></c1-view-field-collection>

</c1-pivot-engine>

<div class="row">
    <div class="col-sm-4 col-md-4">
        <c1-pivot-panel id="@(optionsModel.ControlId)" items-source-id="indexEngine"></c1-pivot-panel>
    </div>
    <div class="col-sm-8 col-md-8">
        <c1-pivot-grid id="indexGrid" items-source-id="indexEngine" outline-mode="false" show-value-field-headers="false"></c1-pivot-grid>
        <p>
            <span style="vertical-align:top; color:black;">
                ShowValueFieldHeaders&nbsp;<input id="ShowValueFieldHeaders" type="checkbox" onchange="toggleShowValueFieldHeaders(event)" />
            </span>
        </p>
    </div>
</div>

<p>@Html.Raw(OlapRes.Index_Text0)</p>

<button type="button" class="btn btn-default" onclick="saveView()">@Html.Raw(OlapRes.Index_Text7)</button>
<button type="button" class="btn btn-default" onclick="loadView()">@Html.Raw(OlapRes.Index_Text8)</button>
<p></p>
<p>@Html.Raw(OlapRes.Index_Text2)</p>

<p>@Html.Raw(OlapRes.Index_Text3)</p> 

<button type="button" class="btn btn-default"
        onclick="excelExport()">
    @Html.Raw(OlapRes.Index_Text9)
</button>

<c1-pivot-chart id="demoChart" items-source-id="indexEngine"></c1-pivot-chart>

@section Scripts{
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
    <script type="text/javascript">
        function saveView() {
            var ng = c1.getService('indexEngine');
            if (ng && ng.isViewDefined) {
                localStorage.viewDefinition = ng.viewDefinition;
            }
        }
        function loadView() {
            var ng = c1.getService('indexEngine');
            if (ng && localStorage.viewDefinition) {
                ng.viewDefinition = localStorage.viewDefinition;
                var cmbRowTotals = wijmo.Control.getControl('#RowTotals');
                if (cmbRowTotals) {
                    cmbRowTotals.selectedValue = ng.showRowTotals;
                }

                var cmbColTotals = wijmo.Control.getControl('#ColTotals');
                if (cmbColTotals) {
                    cmbColTotals.selectedValue = ng.showColumnTotals;
                }

                var chkShowZeros = document.getElementById('ColTotals');
                if (chkShowZeros) {
                    chkShowZeros.checked = ng.showZeros;
                }
            }
        }
        function excelExport() {
            var pivotGrid = wijmo.Control.getControl('#indexGrid');

            // create book with current view
            var book = wijmo.grid.xlsx.FlexGridXlsxConverter.save(pivotGrid, {
                includeColumnHeaders: true,
                includeRowHeaders: true
            });
            book.sheets[0].name = 'Main View';
            addTitleCell(book.sheets[0], getViewTitle(pivotGrid.engine));

            // add sheet with transposed view
            transposeView(pivotGrid.engine);
            var transposed = wijmo.grid.xlsx.FlexGridXlsxConverter.save(pivotGrid, {
                includeColumnHeaders: true,
                includeRowHeaders: true
            });
            transposed.sheets[0].name = 'Transposed View';
            addTitleCell(transposed.sheets[0], getViewTitle(pivotGrid.engine));
            book.sheets.push(transposed.sheets[0]);
            transposeView(pivotGrid.engine);

            // save the book
            book.save('wijmo.olap.xlsx');
        }

        // build a title for the current view
        function getViewTitle(ng) {
            var title = '';
            for (var i = 0; i < ng.valueFields.length; i++) {
                if (i > 0) title += ', ';
                title += ng.valueFields[i].header;
            }
            title += ' by ';
            if (ng.rowFields.length) {
                for (var i = 0; i < ng.rowFields.length; i++) {
                    if (i > 0) title += ', ';
                    title += ng.rowFields[i].header;
                }
            }
            if (ng.rowFields.length && ng.columnFields.length) {
                title += ' and by ';
            }
            if (ng.columnFields.length) {
                for (var i = 0; i < ng.columnFields.length; i++) {
                    if (i > 0) title += ', ';
                    title += ng.columnFields[i].header;
                }
            }
            return title;
        }

        function transposeView(ng) {
            ng.deferUpdate(function () {

                // save row/col fields
                var rows = [],
                    cols = [];
                for (var r = 0; r < ng.rowFields.length; r++) {
                    rows.push(ng.rowFields[r].header);
                }
                for (var c = 0; c < ng.columnFields.length; c++) {
                    cols.push(ng.columnFields[c].header);
                }

                // clear row/col fields
                ng.rowFields.clear();
                ng.columnFields.clear();

                // restore row/col fields in transposed order
                for (var r = 0; r < rows.length; r++) {
                    ng.columnFields.push(rows[r]);
                }
                for (var c = 0; c < cols.length; c++) {
                    ng.rowFields.push(cols[c]);
                }
            });
        }

        // adds a title cell into an xlsx sheet
        function addTitleCell(sheet, title) {

            // create cell
            var cell = new wijmo.xlsx.WorkbookCell();
            cell.value = title;
            cell.style = new wijmo.xlsx.WorkbookStyle();
            cell.style.font = new wijmo.xlsx.WorkbookFont();
            cell.style.font.bold = true;

            // create row to hold the cell
            var row = new wijmo.xlsx.WorkbookRow();
            row.cells[0] = cell;

            // and add the new row to the sheet
            sheet.rows.splice(0, 0, row);
        }

        // toggle outline mode
        function toggleOulineMode(e) {
            var pivotGrid = wijmo.Control.getControl('#indexGrid');
            pivotGrid.outlineMode = e.target.checked;
        }
        // toggle ShowValueFieldHeaders
        function toggleShowValueFieldHeaders(e) {
            var pivotGrid = wijmo.Control.getControl('#indexGrid');
            pivotGrid.showValueFieldHeaders = e.target.checked;
        }
    </script>
}
@section Settings{
    @Html.Partial("_OptionsMenu", optionsModel)
}

@section Summary{
    <p>@Html.Raw(OlapRes.Provider_GoogleAnalytics_Description)</p>

}