using Dynamics365InvoiceReport.Models;
using System.Collections;
using Microsoft.AspNetCore.Mvc;
using System.Linq;
using System.Collections.Generic;
using C1.Web.Mvc.Olap;
using C1.AdoNet.D365S;
using System.Globalization;
using System;
using Microsoft.Extensions.Options;

namespace Dynamics365InvoiceReport.Controllers.Olap
{
    public partial class IndexController : Controller
    {
        private readonly IOptions<DynamicConnection> config;
        public IndexController(IOptions<DynamicConnection> config)
        {
            this.config = config;
        }
        private IEnumerable<InvoiceData> GetData()
        {
            //Create the connection to Dynamic 365 server
            //connstr is connection string to Dynamic 365 server
            //Exp:
            //const string urlDynamics = @"https://...api.crm8.dynamics.com/api/data/v9.1/";
            //const string clientID = "...";
            //const string cllentSecret = "...";
            //const string username = "...";
            //const string password = "...";
            //const string tokenEnpoint = @"https://login.microsoftonline.com/.../oauth2/token";
            //const string extendProperties = @"{""resource"":""https://....crm8.dynamics.com/""}";
            //static string connstr = $@"Url={urlDynamics};Use Etag=true;OAuthClientId={clientID};OAuthClientSecret={cllentSecret};OAuthTokenEndpoint={tokenEnpoint};OAuthExtendProperties={extendProperties};Max Page Size = 100";

            var config = this.config.Value;
            string extendProperties = "{\"resource\":\"" + config.Resource + "\"}";
            var conectionStr = $@"Url={config.UrlDynamics};Use Etag=true;OAuthClientId={config.ClientID};OAuthClientSecret={config.CllentSecret};OAuthTokenEndpoint={config.TokenEnpoint};OAuthExtendProperties={extendProperties};Max Page Size = 100";
            using (C1D365SConnection con = new C1D365SConnection(conectionStr))
            {
                //Open the connection to dynamic 365 server
                con.Open();
                var cmd = con.CreateCommand();
                //query to select from dynamic 365 server
                cmd.CommandText = "Select totalamount, billto_postalcode, billto_country, datedelivered, discountpercentage, totaltax, totaldiscountamount, name from invoices where (billto_country = 'India' or billto_country='United states' or billto_country='Japan') limit 1000";
                var reader = cmd.ExecuteReader();
                List<InvoiceData> result = new List<InvoiceData>();
                CultureInfo provider = CultureInfo.InvariantCulture;
                //Generate List<InvoiceData> from reader
                while (reader.Read())
                {
                    //Put the default value if totalamount is null. Just use for demo
                    var totalamount = String.IsNullOrEmpty(reader[0].ToString()) ? 0 : Convert.ToDecimal(reader[0].ToString());
                    var billto_postalcode = reader[1].ToString();
                    var billto_country = reader[2].ToString();
                    var datedelivered = reader[3].ToString();
                    DateTime? deliveredDate = null;
                    if (!String.IsNullOrEmpty(datedelivered) && DateTime.TryParse(datedelivered, out DateTime convertedDate))
                    {
                        deliveredDate = convertedDate;
                    }
                    else
                    {
                        //Put the default value if datedelivered is null. Just use for demo
                        deliveredDate = new DateTime(2019, 12, 1);
                    }
                    //Put the default value if discountpercentage is null. Just use for demo
                    var discountpercentage = String.IsNullOrEmpty(reader[4].ToString()) ? 0 : Convert.ToDecimal(reader[4].ToString());
                    //Put the default value if totaltax is null. Just use for demo
                    var totaltax = String.IsNullOrEmpty(reader[5].ToString()) ? 0 : Convert.ToDecimal(reader[5].ToString());
                    //Put the default value if totaldiscountamount is null. Just use for demo
                    var totaldiscountamount = String.IsNullOrEmpty(reader[6].ToString()) ? 0 : Convert.ToDecimal(reader[6].ToString());
                    var name = reader[7].ToString();
                    //Add InvoiceData model from the reader
                    result.Add(new InvoiceData
                    {
                        Totalamount = totalamount,
                        Billtopostalcode = billto_postalcode,
                        //Put the default value if billto_country is null. And Standardize the billto_country. Just use for demo
                        Billtocountry = String.IsNullOrEmpty(billto_country) ? StandardizeName("United States") : StandardizeName(billto_country),
                        Discountpercentage = discountpercentage,
                        Totaltax = totaltax,
                        Totaldiscountamount = totaldiscountamount,
                        //Standardize the Name. Just use for demo
                        Name = StandardizeName(name),                       
                        Datedelivered = deliveredDate.Value.ToString(CultureInfo.InvariantCulture),
                        Year = deliveredDate.Value.Year.ToString(),
                        Month = deliveredDate.Value.ToString("MMMM"),
                    }); ;
                }
                return result;
            }
        }
        
        /// <summary>
        /// Function to Standardize string. Upper first letter of string
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        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
                    }
                }
            }
        };

        public ActionResult Index()
        {
            IEnumerable Data = GetData().ToList();
            OlapModel.ControlId = "indexPanel";
            ViewBag.DemoOptions = OlapModel;
            return View(Data);
        }
    }
}

@model IEnumerable<InvoiceData>
@{
    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="Bill To Country" binding="Billtocountry" descending="true"></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 header="Bill To Postal Code" binding="Billtopostalcode"></c1-pivot-field>
            <c1-pivot-field header="Discount Percentage" binding="Discountpercentage"></c1-pivot-field>
            <c1-pivot-field header="Total Tax" binding="Totaltax"></c1-pivot-field>
            <c1-pivot-field header="Total Discount Amount" binding="Totaldiscountamount"></c1-pivot-field>
            <c1-pivot-field header="Date Delivered" binding="Datedelivered"></c1-pivot-field>
            <c1-pivot-field header="Name" binding="Name"></c1-pivot-field>
            <c1-pivot-field header="Month" binding="Month"></c1-pivot-field>
        </c1-pivot-field-collection>
        <c1-view-field-collection c1-property="RowFields" items="Bill To Country,Month"></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.Index_Text6)</p>

}