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

namespace DataConnectorExplorer.Controllers
{
    /// <summary>
    /// Controller for Kintone
    /// </summary>
    public class KintoneController : Controller
    {
        private readonly IOptions<KintoneConnection> config;
        /// <summary>
        /// 
        /// </summary>
        /// <param name="config"></param>
        public KintoneController(IOptions<KintoneConnection> config)
        {
            this.config = config;
        }
        private IEnumerable<KintoneOrderData> GetData()
        {
            var config = this.config.Value;
            string _cacheFolder = "Cache";
            if (!Directory.Exists(_cacheFolder))
            {
                Directory.CreateDirectory(_cacheFolder);
            }
            string specifiedCacheFile = Path.Combine(_cacheFolder, "C1CacheFile.Kintone.db");
            string connectionStringCachePart = string.Format("Use Cache = 'true'; Cache Location='{0}'; Cache Tolerance = 28800", specifiedCacheFile);
            string connectionString = string.Format("Username={0};Password={1};Url={2}; Use Pool = false; Max Page Size = 200; ", config.Username, config.Password, config.Url);
            connectionString += connectionStringCachePart;
            using (C1KintoneConnection con = new C1KintoneConnection(connectionString))
            {
                con.Open();
                var cmd = con.CreateCommand();
                //Query to select from salesforce server.
                cmd.CommandText = "Select ShipAddress, ShipCity, ShipPostalCode, ShipCountry, UnitPrice, Quantity, OrderDate from Invoices where ShipAddress != ''";

                var reader = cmd.ExecuteReader();
                List<KintoneOrderData> result = new List<KintoneOrderData>();
                CultureInfo provider = CultureInfo.InvariantCulture;
                //Generate List<KintoneOrderData> from reader
                while (reader.Read())
                {
                    //Put the default value if totalamount is null. Just use for demo
                    var unitPrice = String.IsNullOrEmpty(reader["UnitPrice"].ToString()) ? 0 : Convert.ToDouble(reader["UnitPrice"].ToString());
                    var quantity = String.IsNullOrEmpty(reader["Quantity"].ToString()) ? 0 : Convert.ToDouble(reader["UnitPrice"].ToString());
                    var totalAmount = unitPrice * quantity;
                    var shipStreet = reader["ShipAddress"].ToString();
                    var shipCity = reader["ShipCity"].ToString();
                    var shipPostalCode = reader["ShipPostalCode"].ToString();
                    var shipCountry = reader["ShipCountry"].ToString();
                    var orderDatestr = reader["OrderDate"].ToString();

                    DateTime? orderDate = null;
                    if (!String.IsNullOrEmpty(orderDatestr) && DateTime.TryParse(orderDatestr, out DateTime convertedDate))
                    {
                        orderDate = convertedDate;
                    }
                    else
                    {
                        //Put the default value if datedelivered is null. Just use for demo
                        orderDate = new DateTime(2019, 12, 1);
                    }
                    //Add OrderData model from the reader
                    result.Add(new KintoneOrderData
                    {
                        TotalAmount = totalAmount,
                        ShipCity = shipCity,
                        ShipCountry = shipCountry,
                        ShipPostalCode = shipPostalCode,
                        ShipStreet = shipStreet,
                        Year = orderDate.Value.Year.ToString(),
                        Month = orderDate.Value.ToString("MMMM"),
                    });
                }
                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>
        /// Action Index
        /// </summary>
        /// <returns></returns>
        public IActionResult Index()
        {
            IEnumerable Data = GetData().ToList();
            OlapModel.ControlId = "indexPanel";
            ViewBag.DemoOptions = OlapModel;
            return View(Data);
        }
    }
}

@model IEnumerable<KintoneOrderData>
@{
    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="Ship Country" binding="ShipCountry"></c1-pivot-field>
        <c1-pivot-field header="Ship City" binding="ShipCity"></c1-pivot-field>
        <c1-pivot-field header="Ship Street" binding="ShipStreet"></c1-pivot-field>
        <c1-pivot-field header="Month" binding="Month"></c1-pivot-field>
        <c1-pivot-field header="Year" binding="Year"></c1-pivot-field>
        <c1-pivot-field header="Total Amount" binding="TotalAmount"></c1-pivot-field>
    </c1-pivot-field-collection>
    <c1-view-field-collection c1-property="RowFields" items="Ship 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="Total 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_Kintone_Description)</p>

}