How To Get Customer Balance Summary From Quickbooks Online Using C#

In this article, we are going to get a customer balance summary from Quickbooks online and show(print) it in text formatted string and download it in a text file.

If you have not seen How To Get Reports From Quickbooks Online Using C# then I recommend you to see that first. in that article, I described how to get Reports from Quickbooks.

• First, we have to create a method that takes 2 arguments.

1. CustomerID– Quickbooks online Customer ID (For receiving a particular customer’s report by its ID).
2. CustomerName– Quickbooks online Customer Name.

• as we discussed in How To Get Reports From Quickbooks Online Using C# from Quickbooks, we will get a customer balance summary report from Quickbooks and pass it to another function that returns the report in the formatted text string.
• We have to give ReportName “CustomerBalance” and CustomerID of which customer report we need.
• The code is as below.
• GetCustomerBalanceSummaryReport method

public ActionResult GetCustomerBalanceSummaryReport(String CustomerID, String CustomerName)
{
  CustomerBalanceSummaryReport CustBalanceSummaryReport = new CustomerBalanceSummaryReport();
  try
  {
    OAuth2RequestValidator oauthValidator = new OAuth2RequestValidator(Access_token);
    // Create a ServiceContext with Auth tokens and realmId
    ServiceContext serviceContext = new ServiceContext(RealmId, IntuitServicesType.QBO, oauthValidator);
    serviceContext.IppConfiguration.MinorVersion.Qbo = "23";
    serviceContext.IppConfiguration.BaseUrl.Qbo = QboBaseUrl;


    //JSON required for QBO Reports API
    serviceContext.IppConfiguration.Message.Response.SerializationFormat = Intuit.Ipp.Core.Configuration.SerializationFormat.Json;

    //Instantiate ReportService
    ReportService reportsService = new ReportService(serviceContext);

    //Set properties for Report
    //reportsService.start_date = startDate;
    //reportsService.end_date = endDate;
    String reportName = "CustomerBalance";
    reportsService.customer = CustomerID;


    //Execute Report API call
    Report report = reportsService.ExecuteReport(reportName);

    string ReportStr = string.Empty;

    //Format the report data and print to the console
    ReportStr = PrintReportToString(report);

    CustBalanceSummaryReport.CustomerID = CustomerID;
    CustBalanceSummaryReport.CustomerName = CustomerName;
    CustBalanceSummaryReport.ReportText = ReportStr;

  }
  catch (IdsException ex)
  {

  }
  catch (Exception ex)
  {

  }
    return View(CustBalanceSummaryReport);
}

• CustomerBalanceSummaryReport Model (CustomerBalanceSummaryReport.cs)

public class CustomerBalanceSummaryReport
{
  public string CustomerID { get; set; }
  public string CustomerName { get; set; }
  public string ReportText { get; set; }
}

• GetCustomerBalanceSummaryReport View (GetCustomerBalanceSummaryReport.cshtml)

@model QuickBooksDemo.Models.CustomerBalanceSummaryReport
@{
    ViewBag.Title = "GetCustomerBalanceSummaryReport";
}

<h2>Customer Balance Summary Report</h2>


<input type="hidden" id="HdnCustName" name="HdnCustName" value="@Model.CustomerName" />
<div class="row">
    <table class="table table-bordered">
        <tr>
            <th style="width:12%;">ID :</th>
            <th>@Model.CustomerID</th>
        </tr>
        <tr>
            <th style="width:12%;">Name :</th>
            <th>@Model.CustomerName</th>
        </tr>
        <tr>
            <th colspan="2" style="text-align:center;">
                Balance Summary Report
            </th>
        </tr>
        <tr>
            <td colspan="2">
                <pre id="ReportPre">@Model.ReportText</pre>
            </td>
        </tr>
        <tr>
            <th colspan="2" style="text-align:center;">
                @*<a id="programatically" href="" download="BalanceSummaryReport.txt">Download Report</a>*@
                <button id="DownloadReport" type="button" class="btn btn-primary">Download Report</button>
            </th>
        </tr>
    </table>
</div>



@section scripts{
    <script>

        $("a#programatically").click(function () {
            var now = new Date().toString();
            var strReport = document.getElementsByTagName('pre')[0].innerHTML;
            //alert(strReport);
            this.href = "data:text/plain;charset=UTF-8," + encodeURIComponent(strReport);
        });

        function download(filename, text) {
            var element = document.createElement('a');
            element.setAttribute('href', 'data:text/plain;charset=utf-8,' + encodeURIComponent(text));
            element.setAttribute('download', filename);

            element.style.display = 'none';
            document.body.appendChild(element);

            element.click();

            document.body.removeChild(element);
        }

        $("#DownloadReport").click(function () {
            var strReport = document.getElementsByTagName('pre')[0].innerHTML;

            var today = new Date();
            var date = today.getFullYear() + '-' + (today.getMonth() + 1) + '-' + today.getDate();
            var time = today.getHours() + ":" + today.getMinutes() + ":" + today.getSeconds();
            var dateTime = date + ' ' + time;

            var HdnCustName = $("#HdnCustName").val();

            var FileName = HdnCustName + "-" + "BalanceSummaryReport" + "-" + dateTime + ".txt";
            // Start file download.
            download(FileName, strReport);
        });



    </script>
}

• Print Report To String Method

//private static void PrintReportToConsole(Report report)
private string PrintReportToString(Report report)
{
  String ReturnStr = string.Empty;
  try
  {
    StringBuilder reportText = new StringBuilder();
    //Append Report Header
    PrintHeader(reportText, report);
    //Determine Maxmimum Text Lengths to format Report
    int[] maximumColumnTextSize = GetMaximumColumnTextSize(report);
    //Append Column Headers
    PrintColumnData(reportText, report.Columns, maximumColumnTextSize, 0);
    //Append Rows
    PrintRows(reportText, report.Rows, maximumColumnTextSize, 1);
    //Formatted Report Text to Return String
    ReturnStr = reportText.ToString();
  }
  catch (Exception ex)
  {
  }
    return ReturnStr;
}

• All Helper Methods that we got from Quickbooks Documentation for formatting report in text. you can find it in this Quickbooks GitHub link here.

#region " Helper Methods "
       #region " Determine Maximum Column Text Length "
       private static int[] GetMaximumColumnTextSize(Report report)
       {
           if (report.Columns == null) { return null; }
           int[] maximumColumnSize = new int[report.Columns.Count()];
           for (int columnIndex = 0; columnIndex < report.Columns.Count(); columnIndex++)
           {
               maximumColumnSize[columnIndex] = Math.Max(maximumColumnSize[columnIndex], report.Columns[columnIndex].ColTitle.Length);
           }
           return GetMaximumRowColumnTextSize(report.Rows, maximumColumnSize, 1);
       }
       private static int[] GetMaximumRowColumnTextSize(Row[] rows, int[] maximumColumnSize, int level)
       {
           for (int rowIndex = 0; rowIndex < rows.Length; rowIndex++)
           {
               Row row = rows[rowIndex];
               Header rowHeader = GetRowProperty<Header>(row, ItemsChoiceType1.Header);
               if (rowHeader != null) { GetMaximumColDataTextSize(rowHeader.ColData, maximumColumnSize, level); }
               ColData[] colData = GetRowProperty<ColData[]>(row, ItemsChoiceType1.ColData);
               if (colData != null) { GetMaximumColDataTextSize(colData, maximumColumnSize, level); }
               Rows nestedRows = GetRowProperty<Rows>(row, ItemsChoiceType1.Rows);
               if (nestedRows != null) { GetMaximumRowColumnTextSize(nestedRows.Row, maximumColumnSize, level + 1); }
               Summary rowSummary = GetRowProperty<Summary>(row, ItemsChoiceType1.Summary);
               if (rowSummary != null) { GetMaximumColDataTextSize(rowSummary.ColData, maximumColumnSize, level); }
           }
           return maximumColumnSize;
       }
       private static int[] GetMaximumColDataTextSize(ColData[] colData, int[] maximumColumnSize, int level)
       {
           for (int colDataIndex = 0; colDataIndex < colData.Length; colDataIndex++)
           {
               maximumColumnSize[colDataIndex] = Math.Max(maximumColumnSize[colDataIndex], (new String(' ', level * 3) + colData[colDataIndex].value).Length);
           }
           return maximumColumnSize;
       }
       #endregion
       #region " Print Report Sections "
       private static void PrintHeader(StringBuilder reportText, Report report)
       {
           const string lineDelimiter = "-----------------------------------------------------";
           reportText.AppendLine(report.Header.ReportName);
           reportText.AppendLine(lineDelimiter);
           reportText.AppendLine("As of " + report.Header.StartPeriod);
           reportText.AppendLine(lineDelimiter);
           reportText.AppendLine(lineDelimiter);
       }
       private static void PrintRows(StringBuilder reportText, Row[] rows, int[] maxColumnSize, int level)
       {
           for (int rowIndex = 0; rowIndex < rows.Length; rowIndex++)
           {
               Row row = rows[rowIndex];
               //Get Row Header
               Header rowHeader = GetRowProperty<Header>(row, ItemsChoiceType1.Header);
               //Append Row Header
               if (rowHeader != null && rowHeader.ColData != null) { PrintColData(reportText, rowHeader.ColData, maxColumnSize, level); }
               //Get Row ColData
               ColData[] colData = GetRowProperty<ColData[]>(row, ItemsChoiceType1.ColData);
               //Append ColData
               if (colData != null) { PrintColData(reportText, colData, maxColumnSize, level); }
               //Get Child Rows
               Rows childRows = GetRowProperty<Rows>(row, ItemsChoiceType1.Rows);
               //Append Child Rows
               if (childRows != null) { PrintRows(reportText, childRows.Row, maxColumnSize, level + 1); }
               //Get Row Summary
               Summary rowSummary = GetRowProperty<Summary>(row, ItemsChoiceType1.Summary);
               //Append Row Summary
               if (rowSummary != null && rowSummary.ColData != null) { PrintColData(reportText, rowSummary.ColData, maxColumnSize, level); }
           }
       }
       private static void PrintColData(StringBuilder reportText, ColData[] colData, int[] maxColumnSize, int level)
       {
           for (int colDataIndex = 0; colDataIndex < colData.Length; colDataIndex++)
           {
               if (colDataIndex > 0) { reportText.Append("     "); }
               StringBuilder rowText = new StringBuilder();
               if (colDataIndex == 0) { rowText.Append(new String(' ', level * 3)); };
               rowText.Append(colData[colDataIndex].value);
               if (rowText.Length < maxColumnSize[colDataIndex])
               {
                   rowText.Append(new String(' ', maxColumnSize[colDataIndex] - rowText.Length));
               }
               reportText.Append(rowText.ToString());
           }
           reportText.AppendLine();
       }
       private static void PrintColumnData(StringBuilder reportText, Column[] columns, int[] maxColumnSize, int level)
       {
           for (int colDataIndex = 0; colDataIndex < columns.Length; colDataIndex++)
           {
               if (colDataIndex > 0) { reportText.Append("     "); }
               StringBuilder rowText = new StringBuilder();
               if (colDataIndex == 0) { rowText.Append(new String(' ', level * 3)); };
               rowText.Append(columns[colDataIndex].ColTitle);
               if (rowText.Length < maxColumnSize[colDataIndex])
               {
                   rowText.Append(new String(' ', maxColumnSize[colDataIndex] - rowText.Length));
               }
               reportText.Append(rowText.ToString());
           }
           reportText.AppendLine();
       }
       #endregion
       #region " Get Row Property Helper Methods - Header, ColData, Rows (children), Summary "
       //Returns typed object from AnyIntuitObjects array
       private static T GetRowProperty<T>(Row row, ItemsChoiceType1 itemsChoiceType)
       {
           int choiceElementIndex = GetChoiceElementIndex(row, itemsChoiceType);
           if (choiceElementIndex == -1) { return default(T); } else { return (T)row.AnyIntuitObjects[choiceElementIndex]; }
       }
       //Finds element index in ItemsChoiceType array
       private static int GetChoiceElementIndex(Row row, ItemsChoiceType1 itemsChoiceType)
       {
           if (row.ItemsElementName != null)
           {
               for (int itemsChoiceTypeIndex = 0; itemsChoiceTypeIndex < row.ItemsElementName.Count(); itemsChoiceTypeIndex++)
               {
                   if (row.ItemsElementName[itemsChoiceTypeIndex] == itemsChoiceType) { return itemsChoiceTypeIndex; }
               }
           }
           return -1;
       }
       #endregion
       #endregion

Now we have to set the GetCustomerBalanceSummaryReport page link on the customer list page.so the user can see any customer balance summary report.

• If you don’t know how to get customers from Quickbook Online then you can find it here.
  1. How To Get Customers From Quickbooks Online Using C#

Let’s set Link in Customer List view page,

• Customer View

@model List<Intuit.Ipp.Data.Customer>

@{
    ViewBag.Title = "GetCustomer";
}

<h2>Quickbooks online Customer List</h2>

<div>
    <table class="table table-bordered">
        <tr>
            <th>QBO ID</th>
            <th>Display Name</th>
            <th>Given Name</th>
            <th>Family Name</th>
            <th>Email</th>
            <th>Primary Phone</th>
            <th style="text-align:center;">Balance Sheet</th>
            <th style="text-align:center;">Balance</th>
        </tr>

        @foreach (var Cust in Model)
        {
            <tr>
                <td>@Cust.Id</td>
                <td>@Cust.DisplayName</td>
                <td>@Cust.GivenName</td>
                <td>@Cust.FamilyName</td>
                @if (Cust.PrimaryEmailAddr != null && !string.IsNullOrEmpty(Cust.PrimaryEmailAddr.Address))
                {
                    <td>@Cust.PrimaryEmailAddr.Address</td>
                }
                else
                {
                    <td></td>
                }

                @if (Cust.PrimaryPhone != null && !string.IsNullOrEmpty(Cust.PrimaryPhone.FreeFormNumber))
                {
                    <td>@Cust.PrimaryPhone.FreeFormNumber</td>
                }
                else
                {
                    <td></td>
                }
                <td style="text-align:center;"><a href="@Url.Action("GetCustomerVendorBalanceSheetReport", "Home", new { IsCustomerVendor="customer", CustomerVendorID = Cust.Id,CustomerVendorName= Cust.DisplayName})" target="_blank"><img src="~/Content/Images/BalanceSheetICON.png" height="25" width="25" /></a></td>
        <td style="text-align:center;"><a href="@Url.Action("GetCustomerBalanceSummaryReport", "Home", new { CustomerID = Cust.Id,CustomerName= Cust.DisplayName})" target="_blank"><img src="~/Content/Images/BalanceSummaryIcon.png" height="25" width="25" /></a></td>
            </tr>
        }

    </table>
</div>

Now we can get the Balance Summary report of customers from Quickbooks online.