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.
- CustomerID– Quickbooks online Customer ID (For receiving a particular customer’s report by its ID).
- 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.
- If you don’t know how to get customers from Quickbook Online then you can find it here.
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.