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.OData;
using System.Globalization;
using System;
using Microsoft.Extensions.Options;
using System.IO;

namespace DataConnectorExplorer.Controllers
{
    /// <summary>
    /// Controller for OData
    /// </summary>
    public class IndexController : Controller
    {
        private readonly IOptions<ODataConnection> config;
        /// <summary>
        /// 
        /// </summary>
        /// <param name="config"></param>
        public IndexController(IOptions<ODataConnection> config)
        {
            this.config = config;
        }
        private IEnumerable<ODataInvoiceData> GetData()
        {
            //Create the connection to OData server
            //connstr is connection string to OData server
            //Exp:
            //const string url = "...";
            //static string connstr = "Url={url};Max Page Size = 100"

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

            var conectionStr = $@"Url={config.Url};Max Page Size = 100;";
            conectionStr += connectionStringCachePart;
            using (C1ODataConnection con = new C1ODataConnection(conectionStr))
            {
                //Open the connection to OData server
                con.Open();
                var cmd = con.CreateCommand();
                //query to select from OData server
                cmd.CommandText = "Select UnitPrice, Quantity, PostalCode, Country, Address, City, Region, CustomerName, ShippedDate from Invoices limit 1000";
                var reader = cmd.ExecuteReader();
                List<ODataInvoiceData> result = new List<ODataInvoiceData>();
                CultureInfo provider = CultureInfo.InvariantCulture;
                //Generate List<ODataInvoiceData> from reader
                while (reader.Read())
                {
                    var unitPrice = String.IsNullOrEmpty(reader["UnitPrice"].ToString()) ? 0 : Convert.ToDecimal(reader["UnitPrice"].ToString());
                    var quantity = String.IsNullOrEmpty(reader["Quantity"].ToString()) ? 0 : Convert.ToDecimal(reader["Quantity"].ToString());
                    var amount = unitPrice * quantity;
                    var country = reader["Country"].ToString();
                    var city = reader["City"].ToString();
                    var region = reader["Region"].ToString();
                    var shippedDateValue = reader["ShippedDate"].ToString();
                    DateTime? shippedDate;
                    if (!String.IsNullOrEmpty(shippedDateValue) && DateTime.TryParse(shippedDateValue, out DateTime convertedDate))
                    {
                        shippedDate = convertedDate;
                    }
                    else
                    {
                        //Put the default value if shippedDate is null. Just use for demo
                        shippedDate = new DateTime(2019, 12, 1);
                    }
                    var customerName = reader["CustomerName"].ToString();
                    //Add ODataInvoiceData model from the reader
                    result.Add(new ODataInvoiceData
                    {
                        Amount = amount,
                        Country = country,
                        City = city,
                        Region = region,
                        CustomerName = customerName,
                        ShippedDate = shippedDate.Value.ToString(CultureInfo.InvariantCulture),
                        Year = shippedDate.Value.Year.ToString(),
                        Month = shippedDate.Value.ToString("MMMM"),
                    });
                    ;
                }
                return result;
            }
        }

        private static string StandardizeName(string input)
        {
            if (!String.IsNullOrEmpty(input))
            {
                return input.First().ToString().ToUpper() + input.Substring(1).ToLower();
            }
            else
            {
                return "";
            }
        }

        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>
        /// Action Index
        /// </summary>
        /// <returns></returns>
        public IActionResult Index()
        {
            IEnumerable Data = GetData().ToList();
            OlapModel.ControlId = "indexPanel";
            ViewBag.DemoOptions = OlapModel;
            return View(Data);
        }
    }
}

@model IEnumerable<ODataInvoiceData>
@{
    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="Region" binding="Region"></c1-pivot-field>
        <c1-pivot-field header="Year" binding="Year"></c1-pivot-field>
        <c1-pivot-field header="Month" binding="Month"></c1-pivot-field>
        <c1-pivot-field header="Customer Name" binding="CustomerName"></c1-pivot-field>
        <c1-pivot-field header="Amount" binding="Amount"></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="Year"></c1-view-field-collection>
    <c1-view-field-collection c1-property="ValueFields" items="Amount" 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_OData_Description)</p>

}