How To Get Customer Profit And Loss Report From Quickbooks Online Using C#

Dec 18, 2019 0 3.1K

Tabish Rangrej

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

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

    CustPLReport.ID = CustomerID;
    CustPLReport.Name = CustomerName;
    CustPLReport.ReportText = ReportStr;

  }
  catch (IdsException ex)
  {

  }
  catch (Exception ex)
  {

  }
    return View(CustPLReport);
}
  • ProfitLossReport Model (ProfitLossReport.cs)
public class ProfitLossReport
{
 public string ID { get; set; }
 public string Name { get; set; }
 public string ReportText { get; set; }
}
  • GetCustomerProfitLossReport View (GetCustomerProfitLossReport.cshtml)
@model QuickBooksDemo.Models.ProfitLossReport

@{
    ViewBag.Title = "GetCustomerProfitLossReport";
}

<h2>Customer Profit Loss Report</h2>

<input type="hidden" id="HdnCustName" name="HdnCustName" value="@Model.Name" />
<div class="row">
    <table class="table table-bordered">
        <tr>
            <th style="width:12%;">ID :</th>
            <th>@Model.ID</th>
        </tr>
        <tr>
            <th style="width:12%;">Name :</th>
            <th>@Model.Name</th>
        </tr>
        <tr>
            <th colspan="2" style="text-align:center;">
                Profit Loss 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 + "-" + "ProfitLossReport" + "-" + 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 GetCustomerProfitLossReport page link on the customer list page.so the user can see any customer’s profit and loss 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 table-hover">
        <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;">Profit Loss Report</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("GetCustomerProfitLossReport", "Home", new { CustomerID = Cust.Id,CustomerName= Cust.DisplayName})" target="_blank"><img src="~/Content/Images/Profit_Loss_ICON_2.png" height="25" width="25" /></a></td>
            </tr>
        }

    </table>
</div>

so that how we can get the customer profit and loss report from Quickbooks online.

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