Features

Overview

Overview

OLAP Component displays data from QuickBooks Online Invoice table using ADO.NET Connector for QuickBooks Online.

Change provider

ShowValueFieldHeaders 

You can use the viewDefinition property to save and restore view definitions, as shown in the following example:

The PivotGrid control extends the FlexGrid control, so you can export it to any of
the formats supported by the extension modules provided with the FlexGrid. The
list of the supported formats includes .xlsx, .csv, and .pdf.

For example, clicking the button below creates an Excel file with two sheets: the current view and a transposed version of the current view.

Settings

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

namespace DataConnectorExplorer.Controllers
{
    /// <summary>
    /// Controller for QuickBooks Online
    /// </summary>
    public class QBOController : Controller
    {
        private readonly IOptions<QuickBooksOnlineConnection> config;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="config"></param>
        public QBOController(IOptions<QuickBooksOnlineConnection> config)
        {
            this.config = config;
        }

        static void OnOAuthTokenRefreshed(object sender, EventArgs e)
        {
            Console.WriteLine("OAuth Token refreshed.");
            var conn = sender as C1QuickBooksOnlineConnection;
            var strAuthen = $"{conn.CompanyId};{conn.OAuthToken.AccessToken};{conn.OAuthToken.RefreshToken}";
            System.IO.File.WriteAllText(@"QBO_LastSavedToken.txt", strAuthen);
        }

        private IEnumerable<QBOInvoiceData> GetData()
        {
            //Create the connection to QuickBooks Online server
            //Ex:
            //const string OAuthAccessToken = "...";
            //const string OAuthRefreshToken = "...";
            //const string OAuthClientId = "ABIfYd9COX67eHNveQjQjIbK2tzBRUzWjxr...";
            //const string OAuthClientSecret = "5tazgPsSjQDtm2gvED6LJxKVO6Q6z37...";
            //const string CompanyId = "462081636514810...";
            //const string MinorVersion = "52";
            //string connectionString = $"Company Id={CompanyId};Use SandBox=true;OAuth Client Secret={OAuthClientSecret};OAuth Client Id={OAuthClientId};" +
            //            $"OAuth Access Token={OAuthAccessToken};OAuth Refresh Token={OAuthRefreshToken}; OAuth Token Endpoint=;Minor Version={MinorVersion}";
            
            var config = this.config.Value;

            string OAuthAccessToken = config.OAuthAccessToken;
            string OAuthRefreshToken = config.OAuthRefreshToken;

            try
            {
                var arrAuth = System.IO.File.ReadAllText(@"QBO_LastSavedToken.txt").Split(';');
                if (arrAuth[0] == config.CompanyId)
                {
                    OAuthAccessToken = arrAuth[1];
                    OAuthRefreshToken = arrAuth[2];
                }
            }
            catch { }

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


            string connectionString = $"Company Id={config.CompanyId};Use SandBox=true;OAuth Client Secret={config.OAuthClientSecret};OAuth Client Id={config.OAuthClientId};" +
                        $"OAuth Access Token={OAuthAccessToken};OAuth Refresh Token={OAuthRefreshToken}; OAuth Token Endpoint=;Minor Version={config.MinorVersion};";
            connectionString += connectionStringCachePart;
            using (C1QuickBooksOnlineConnection con = new C1QuickBooksOnlineConnection(connectionString))
            {
                con.OAuthTokenRefreshed += OnOAuthTokenRefreshed;
                con.Open();

                var cmd = con.CreateCommand();
                //Query to select from salesforce server.
                cmd.CommandText = "Select * from Invoices";


                var reader = cmd.ExecuteReader();
                List<QBOInvoiceData> result = new List<QBOInvoiceData>();
                CultureInfo provider = CultureInfo.InvariantCulture;
                //Generate List<QBOInvoiceData> from reader
                while (reader.Read())
                {
                    //Put the default value if totalamount is null. Just use for demo
                    var totalAmount = String.IsNullOrEmpty(reader["TotalAmt"].ToString()) ? 0 : Convert.ToDouble(reader["TotalAmt"].ToString());
                    var shipAddr_Line1 = reader["ShipAddr_Line1"].ToString();
                    var shipAddr_City = reader["ShipAddr_City"].ToString();
                    var shipAddr_PostalCode = reader["ShipAddr_PostalCode"].ToString();
                    var shipAddr_Country = reader["ShipAddr_CountrySubDivisionCode"].ToString();
                    var dueDateValue = reader["DueDate"].ToString();

                    //Put default vallue if string value is empty
                    shipAddr_Country = string.IsNullOrEmpty(shipAddr_Country) ? "CA" : shipAddr_Country;
                    shipAddr_City = string.IsNullOrEmpty(shipAddr_City) ? "San Jose" : shipAddr_City;
                    shipAddr_Line1 = string.IsNullOrEmpty(shipAddr_Line1) ? "847 California Ave." : shipAddr_Line1;
                    shipAddr_PostalCode = string.IsNullOrEmpty(shipAddr_PostalCode) ? "95021" : shipAddr_PostalCode;

                    DateTime? dueDate = null;
                    if (!String.IsNullOrEmpty(dueDateValue) && DateTime.TryParse(dueDateValue, out DateTime convertedDate))
                    {
                        dueDate = convertedDate;
                    }
                    else
                    {
                        //Put the default value if DueDate is null. Just use for demo
                        dueDate = new DateTime(2019, 12, 1);
                    }
                    //Add OrderData model from the reader
                    result.Add(new QBOInvoiceData
                    {
                        TotalAmount = totalAmount,
                        ShipAddrStreet = shipAddr_Line1,
                        ShipAddrCity = shipAddr_City,
                        ShipAddrCountry = shipAddr_Country,
                        ShipAddrPostalCode = shipAddr_PostalCode,
                        Year = dueDate.Value.Year.ToString(),
                        Month = dueDate.Value.Month.ToString("00"),
                    });
                }
                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<QBOInvoiceData>
@{
    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 Code" binding="ShipAddrCountry"></c1-pivot-field>
        <c1-pivot-field header="City" binding="ShipAddrCity"></c1-pivot-field>
        <c1-pivot-field header="Street" binding="ShipAddrStreet"></c1-pivot-field>
        <c1-pivot-field header="Postal Code" binding="ShipAddrPostalCode"></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="Total Amount" binding="TotalAmount"></c1-pivot-field>
    </c1-pivot-field-collection>
    <c1-view-field-collection c1-property="RowFields" items="City"></c1-view-field-collection>
    <c1-view-field-collection c1-property="ColumnFields" items="Month"></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_QBO_Description)</p>

}