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

In this blog, we are going to get a customer balance detail 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 detail report from Quickbooks and pass it to another function that returns the report in the formatted text string.
  • We have to give ReportName “CustomerBalanceDetail” and CustomerID of which customer report we need.
  • The code is as below.
  • GetCustomerBalanceDetailReport method
public ActionResult GetCustomerBalanceDetailReport(String CustomerID, String CustomerName)
{
  CustomerBalanceDetailReport CustBalanceDetailReport = new CustomerBalanceDetailReport();
  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 = "CustomerBalanceDetail";
    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);

    CustBalanceDetailReport.CustomerID = CustomerID;
    CustBalanceDetailReport.CustomerName = CustomerName;
    CustBalanceDetailReport.ReportText = ReportStr;

  }
  catch (IdsException ex)
  {

  }
  catch (Exception ex)
  {

  }
    return View(CustBalanceDetailReport);
}
  • CustomerBalanceDetailReport Model (CustomerBalanceDetailReport.cs)
public class CustomerBalanceDetailReport
{
   public string CustomerID { get; set; }
   public string CustomerName { get; set; }
   public string ReportText { get; set; }
}
  • GetCustomerBalanceDetailReport View (GetCustomerBalanceDetailReport.cshtml)
@model QuickBooksDemo.Models.CustomerBalanceDetailReport
@{
    ViewBag.Title = "GetCustomerBalanceDetailReport";
}

<h2>Customer Balance Detail 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 Detail Report
            </th>
        </tr>
        <tr>
            <td colspan="2">
                <pre id="ReportPre">@Model.ReportText</pre>
            </td>
        </tr>
        <tr>
            <th colspan="2" style="text-align:center;">
                <button id="DownloadReport" type="button" class="btn btn-primary">Download Report</button>
            </th>
        </tr>
    </table>
</div>



@section scripts{
    <script>
        
        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 + "-" + "BalanceDetailReport" + "-" + 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 GetCustomerBalanceDetailReport page link on the customer list page.so the user can see any customer balance detail report.

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 Details</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("GetCustomerBalanceDetailReport", "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>

so this way we can get the customer Balance detail report from Quickbooks online.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe

Select Categories