How To Get Balance Sheet From Quickbooks Online Using C# Part-2

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

If you have not seen Part-1 then I recommend you to see that first. in Part-1 I described the balance sheet and describe how to get a balance sheet from Quickbooks.

• First, create a method that takes 3 arguments.

1. IsCustomerVendor – to check different between customer and vendor.
2. CustomerVendorID – Quickbooks online Customer or Vendor ID.
3. CustomerVendorName – Quickbooks online Customer or Vendor Name.

• as we discussed in Part-1 how to get a balance sheet report from Quickbooks, we will get a balance sheet report from Quickbooks and pass it to another function that returns the report in the formatted text string.
• The code is as below.
• GetCustomerVendorBalanceSheetReport method

public ActionResult GetCustomerVendorBalanceSheetReport(String IsCustomerVendor, String CustomerVendorID, String CustomerVendorName)
{
  BalanceSheetCustomerReport balanceSheetCustomerReport = new BalanceSheetCustomerReport();
  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 = "BalanceSheet";
    if (IsCustomerVendor == "customer")
    {
        reportsService.customer = CustomerVendorID;
    }
    else if (IsCustomerVendor == "vendor")
    {
        reportsService.vendor = CustomerVendorID;
    }

    //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);

    balanceSheetCustomerReport.CustomerID = CustomerVendorID;
    balanceSheetCustomerReport.CustomerName = CustomerVendorName;
    balanceSheetCustomerReport.BalanceSheetReport = ReportStr;

  }
  catch (IdsException ex)
  {

  }
  catch (Exception ex)
  {

  }
    return View(balanceSheetCustomerReport);
}

• BalanceSheetCustomerReport Model (BalanceSheetCustomerReport.cs)

public class BalanceSheetCustomerReport
{
  public string CustomerID { get; set; }
  public string CustomerName { get; set; }
  public string BalanceSheetReport { get; set; }
}

• GetCustomerVendorBalanceSheetReport View (GetCustomerVendorBalanceSheetReport .cshtml)

@model QuickBooksDemo.Models.BalanceSheetCustomerReport
@{
    ViewBag.Title = "GetCustomerVendorBalanceSheetReport";
}

<h2>Balance Sheet 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;">
                BalanceSheet Report
            </th>
        </tr>
        <tr>
            <td colspan="2">
                <pre id="ReportPre">@Model.BalanceSheetReport</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 + "-" + "BalanceSheetReport" + "-" + 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 formating 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 GetCustomerVendorBalanceSheetReport page link in the customer and vendor list page.so the user can see any customer or vendor balance sheet report.

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

Let’s set Link in Customer and Vendor 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;">BalanceSheet</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>
            </tr>
        }

    </table>
</div>

• Vendor View

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

@{
    ViewBag.Title = "GetAllVendor";
}

<h2>Quickbooks online Vendor 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;">BalanceSheet</th>
        </tr>

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

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

        </tr>
        }

    </table>
</div>

here we are ready for getting the Balance Sheet report of customers and vendors from Quickbooks online.

• Customer Balance Sheet Report

• Vendor Balance Sheet Report