Getting Retainer Report Data from Quickbooks Online

QuickbooksOnlineRetainerReport.js

/*
gets each customer with a postive balance in the Retainer Account
check the endDate Below
*/

var QuickBooks = require('node-quickbooks');
var numeral = require('numeral')
var retainerAccountID = 93;  // your account id maybe different

var options = {
    consumerKey: '<YOUR KEY>',
    consumerSecret: '<YOUR SECRET>',
    oauthToken: '<YOUR oauthToken>',
    oauthTokenSecret: '<YOUR oAuthTokenSecret>',
    realmId: 'companyID'
}

var qbo = new QuickBooks(
    options.consumerKey,
    options.consumerSecret,
    options.oauthToken,
    options.oauthTokenSecret,
    options.realmId,
    false, // don't use the sandbox (i.e. for testing)
    false
);

var dollar = function (num) {
    return Number(parseFloat(num).toFixed(2))
}


qbo.reportGeneralLedgerDetail(
    {
        account: retainerAccountID,
        start_date: '2000-01-01',
        end_date: '2015-12-01',
        columns: 'cust_name,credit_amt,debt_amt'
    },
    function (err, report) {
        var rows = report.Rows.Row[0].Rows.Row;

        var transactions = [];
        rows.map(function(item){
           var data = item.ColData
            var row = {
                company : data[0].value,
                id : data[0].id,
                credit : data[1].value,
                debit : data[2].value
            };
            transactions.push(row);

        });

        var sumByCompany = {};

        transactions.map(function (trans) {
            var company = trans.company
            var credit = numeral().unformat(trans.credit)
            var debit = numeral().unformat(trans.debit)
            credit = dollar(credit);
            debit = dollar(debit);

            var oldCredit = sumByCompany[company] ? sumByCompany[company].credit || 0 : 0;
            var oldDebit = sumByCompany[company] ? sumByCompany[company].debit || 0 : 0;

            if(!sumByCompany[company]){
                sumByCompany[company] = {}
            }
            sumByCompany[company].company = company;
            sumByCompany[company].credit = dollar(oldCredit) + credit
            sumByCompany[company].debit = dollar(oldDebit) + debit
            sumByCompany[company].balance = dollar(sumByCompany[company].credit - sumByCompany[company].debit)
        })


        var companies = []
        Object.keys(sumByCompany).map(function (companyKey) {
            var obj = sumByCompany[companyKey];
            companies.push(obj)
        });


        companies = companies.filter(function(company){
            return company.balance != 0
        });
        
        var total = companies.reduce(function (total, currentValue) {
            return total + currentValue.balance
        },0)

        console.log(JSON.stringify(companies));
        console.log(total)
    });