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

Dec 14, 2019 0 1.1K

Tabish Rangrej

Tabish Rangrej is an Experienced .NET Team Leader, software engineer, and Author with a demonstrated ...

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.

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

 

Tabish Rangrej

Tabish Rangrej is an Experienced .NET Team Leader, software engineer, and Author with a demonstrated history of working in the IT industry. He has quite well experience in developing, communicating, managing, and writing in his field. He has strong skills and knowledge of ASP.NET C#, ASP.NET MVC, ASP.NET CORE, Angular, AngularJS, Web API, SQL, Entity Framework, JavaScript, Jquery, Different Integrations like Quickbooks, Stripe, Google APIs, Zoho, Orion, Xero, etc., Different versioning tools like TFS, SVN, GIT, etc. and Windows services. Strong engineering professional with a Master of Computer Applications - MCA focused on Computer Science from Veer Narmad South Gujarat University, Surat. Tabish is always ready to accept new challenges and learn new things, he would like to serve better for the community.
Read More

Submit a Comment

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

Subscribe

Select Categories