Visual Foxpro connecting to QuickBooks Online

This is the API Call for a report. It differs only slightly from a transaction query in that the request is not an SQL type. Instead it looks like calling a url and passing the report request with options and authorization. The application I needed to create is for a vendor detail balance report. You might ask, why don't i just output an excel report. But in this case, I want to do a comparison of one company's vendor detail report and a second company's customer detail report. It is a question of determining the transactions between two QBO companies intercompany balance. I won't bore you with the last steps to do the comparison. I will leave the eventual processing to you. My blog will be about running the API on a report and parsing the result in JSON.

Lets get started. We will call our program getvdb.prg. (Get Vendor Detail Balance)
We will need to construct a resulting table and so I added some records to my fieldlist.dbf. For my proposes, I only need 5 fields.
Create table result (vendor c(40),docnumber c(20),amount n(12,2),balance n(12,2),txndate c(20)) I could do this in the code or i can call the build table routine

**GETVDB.PRG**
SET SAFETY OFF
CLOSE DATA
CLEAR
SET TALK OFF
mreport="VendorBalanceDetail?minorversion=75"
mfilename="Result"
**we are doing JSON
mxml=.f.
mentity="VendorBalance"
**you could just create the table here instead of this routing
DO createtable WITH mentity,mfilename
CLOSE DATA

DO makecallreport WITH mreport

**Do whatever you need with the result.dbf

FUNCTION createtable
PARAMETER lcentity,lcfilename,
USE fieldlist
SET FILTER TO entity=lcentity
mfieldcount=1
SCAN
   DIME marray(mfieldcount,4)
   marray(mfieldcount,1)=(colname)
   DO CASE
      CASE TYPE="String"
         marray(mfieldcount,2)="C"
         marray(mfieldcount,3)=VAL(MAXLEN)
         marray(mfieldcount,4)=0
      CASE TYPE="Number"
         marray(mfieldcount,2)="N"
         marray(mfieldcount,3)=12
         marray(mfieldcount,4)=2
      CASE TYPE="Boulean"
         marray(mfieldcount,2)="L"
         marray(mfieldcount,3)=1
         marray(mfieldcount,4)=0
      ENDCASE
      mfieldcount=mfieldcount+1
ENDSCAN
CREATE TABLE (lcfilename) FROM ARRAY marray
RETURN

The next step is the makecallreport. This is almost identical to the makecallprg.

PARAMETERS mreport
*****************************
*This is now the api call for reports
**lets check the dates stored
*Enter the choice of which client and secret for each of my clients
USE oauth_tokens
COUNT FOR NOT EMPTY(atoken)
IF _TALLY = 0
   MESSAGEBOX("No OAuth tokens found.")
   RETURN
ENDIF
LOCATE
lcaccesstoken = atoken
lcrefreshtoken = rtoken
dtaccessexpires = CTOT(aexpires)
dtrefreshexpires = CTOT(rexpires)
lcrealmid=TRIM(realmid)
CLOSE DATA
* Check if access token expired
IF DATETIME() >= dtaccessexpires
   mclientid = "your cliend id here"
   mclientsecret = "your client secret here"
   mtokenendpoint = "https://oauth.platform.intuit.com/oauth2/v1/tokens/bearer"
   lohttp = CREATEOBJECT("WinHttp.WinHttpRequest.5.1")
   lohttp.OPEN("POST", mtokenendpoint, .F.)
   lohttp.setrequestheader("Content-type","application/x-www-form-urlencoded")
   lohttp.setrequestheader("Accept","Application/json")
   lcbody= ;
      "grant_type=refresh_token" + ;
      "&refresh_token="+lcrefreshtoken + ;
      "&client_id=" + mclientid + ;
      "&client_secret=" + mclientsecret
   lohttp.SEND(lcbody)
   IF lohttp.STATUS = 200
      lchtml = lohttp.responsetext
   ELSE
      MESSAGEBOX("Got nothing back from the exchange." + TRANSFORM(lohttp.STATUS))
      CANCEL
   ENDIF
   ?"Parsing the response"
   *this is a simple parse, the only things we need are the access and refresh tokens
   lcresponse=lohttp.responsetext
   losc=CREATEOBJECT("ScriptControl")
   losc.language = "JScript"
   losc.addcode( ;
   "function parseJSON(s) { " + ;
   " return eval('(' + s + ')');" + ;
   "}")
   loobj=losc.RUN("parseJSON",lcresponse)
   lcaccesstoken=loobj.access_token
   lcrefreshtoken=loobj.refresh_token
   dtaccessexpires = TTOC(DATETIME() + 3600)
   dtrefreshexpires = DATETIME() + 3600*24*100
   USE oauth_tokens EXCLUSIVE
   DELETE ALL
   PACK
   INSERT INTO oauth_tokens VALUES ;
      (lcaccesstoken, lcrefreshtoken, dtaccessexpires, DTOC(dtrefreshexpires),lcrealmid)
   CLOSE DATA
ENDIF

lcrealmid=STRTRAN(lcrealmid, CHR(13)+CHR(10),"") &&remove newlines
lcrealmid=STRTRAN(lcrealmid, CHR(32),"") &&remove spaces

*this is the actual call for a report
lcurl = "https://quickbooks.api.intuit.com/v3/company/" + lcrealmid + "/reports/" + mreport
IF mxml
   tcaccept = "application/xml"
ELSE
   tcaccept = "application/json"
ENDIF
lohttp = CREATEOBJECT("WinHttp.WinHttpRequest.5.1")
lohttp.OPEN("GET", lcurl, .F.)
lohttp.setrequestheader("Accept",tcaccept)
lohttp.setrequestheader("Authorization", "Bearer " + lcaccesstoken)
lohttp.SEND()
IF lohttp.STATUS = 200
   lcresponse = lohttp.responsetext
ELSE
   lcresponse = "ERROR: " + TRANSFORM(lohttp.STATUS) + ;
      " - " + lohttp.responsetext
ENDIF

?"Removing extraneous characters from the stream."
lcresponse = LTRIM(RTRIM(lcresponse))
lcresponse = STRTRAN(lcresponse, CHR(13), "")
lcresponse = STRTRAN(lcresponse, CHR(10), "")
IF lohttp.STATUS = 200
   LOCAL json, success, root, customers, i, CUST
   lcxml=STRTRAN(lcresponse,'"',"")
   IF mxml
      DO dbfit7xml WITH mfilename
   ELSE
      DO dbfit7json WITH mfilename
   ENDIF
ENDIF

That is the MakeCallreport.prg. It should be universal for all of your API calls for reports.
See the JSON parsing for reports blog for that last chunk of code.