(Lianja) Quickbooks Query an Invoice

Demonstrates how to query for invoices matching a SELECT statement via the Quickbooks REST API.

For more information, see https://www.developer.intuit.com/app/developer/qbo/docs/api/accounting/most-commonly-used/invoice#query-an-invoice

Chilkat Lianja Extension Download Chilkat Lianja Extension

// This example requires the Chilkat API to have been previously unlocked.
// See Global Unlock Sample for sample code.

// First get our previously obtained OAuth2 access token.
loJsonToken = createobject("CkJsonObject")
llSuccess = loJsonToken.LoadFile("qa_data/tokens/qb-access-token.json")

loRest = createobject("CkRest")

llBTls = .T.
lnPort = 443
llBAutoReconnect = .T.
llSuccess = loRest.Connect("sandbox-quickbooks.api.intuit.com",lnPort,llBTls,llBAutoReconnect)

loSbAuth = createobject("CkStringBuilder")
loSbAuth.Append("Bearer ")
loSbAuth.Append(loJsonToken.StringOf("access_token"))
loRest.Authorization = loSbAuth.GetAsString()

// --------------------------------------------------------------------------
// Note: The above REST connection and setup of the AWS credentials
// can be done once.  After connecting, any number of REST calls can be made.
// The "auto reconnect" property passed to rest.Connect indicates that if
// the connection is lost, a REST method call will automatically reconnect
// if needed.
// --------------------------------------------------------------------------

// This is a GET request, so there should be no Content-Type
// This line of code is just to make sure..
loRest.RemoveHeader("Content-Type")

loRest.AddHeader("Accept","application/json")
loRest.AllowHeaderFolding = .F.

// Add a SELECT statement 
loRest.AddQueryParam("query","select * from Invoice where id = '239'")

loSbResponseBody = createobject("CkStringBuilder")
llSuccess = loRest.FullRequestNoBodySb("GET","/v3/company/<realmID>/invoice",loSbResponseBody)
if (llSuccess <> .T.) then
    ? loRest.LastErrorText
    release loJsonToken
    release loRest
    release loSbAuth
    release loSbResponseBody
    return
endif

lnRespStatusCode = loRest.ResponseStatusCode
if (lnRespStatusCode >= 400) then
    ? "Response Status Code = " + str(lnRespStatusCode)
    ? "Response Header:"
    ? loRest.ResponseHeader
    ? "Response Body:"
    ? loSbResponseBody.GetAsString()
    release loJsonToken
    release loRest
    release loSbAuth
    release loSbResponseBody
    return
endif

// Success is indicated by a 200 response status.
? "response status code = " + str(lnRespStatusCode)

loJson = createobject("CkJsonObject")
loJson.LoadSb(loSbResponseBody)
loJson.EmitCompact = .F.
? loJson.Emit()

// Sample output:
// Use the this online tool to generate parsing code from sample JSON: 
// Generate Parsing Code from JSON

// {
//   "QueryResponse": {
//     "startPosition": 1, 
//     "totalCount": 1, 
//     "maxResults": 1, 
//     "Invoice": [
//       {
//         "DocNumber": "1070", 
//         "SyncToken": "0", 
//         "domain": "QBO", 
//         "Balance": 150.0, 
//         "BillAddr": {
//           "City": "Bayshore", 
//           "Line1": "4581 Finch St.", 
//           "PostalCode": "94326", 
//           "Lat": "INVALID", 
//           "Long": "INVALID", 
//           "CountrySubDivisionCode": "CA", 
//           "Id": "2"
//         }, 
//         "TxnDate": "2015-07-24", 
//         "TotalAmt": 150.0, 
//         "CustomerRef": {
//           "name": "Amy's Bird Sanctuary", 
//           "value": "1"
//         }, 
//         "ShipAddr": {
//           "City": "Bayshore", 
//           "Line1": "4581 Finch St.", 
//           "PostalCode": "94326", 
//           "Lat": "INVALID", 
//           "Long": "INVALID", 
//           "CountrySubDivisionCode": "CA", 
//           "Id": "109"
//         }, 
//         "LinkedTxn": [], 
//         "DueDate": "2015-08-23", 
//         "PrintStatus": "NeedToPrint", 
//         "Deposit": 0, 
//         "sparse": false, 
//         "EmailStatus": "NotSet", 
//         "Line": [
//           {
//             "LineNum": 1, 
//             "Amount": 150.0, 
//             "SalesItemLineDetail": {
//               "TaxCodeRef": {
//                 "value": "NON"
//               }, 
//               "ItemRef": {
//                 "name": "Services", 
//                 "value": "1"
//               }
//             }, 
//             "Id": "1", 
//             "DetailType": "SalesItemLineDetail"
//           }, 
//           {
//             "DetailType": "SubTotalLineDetail", 
//             "Amount": 150.0, 
//             "SubTotalLineDetail": {}
//           }
//         ], 
//         "ApplyTaxAfterDiscount": false, 
//         "CustomField": [
//           {
//             "DefinitionId": "1", 
//             "Type": "StringType", 
//             "Name": "Crew #"
//           }
//         ], 
//         "Id": "239", 
//         "TxnTaxDetail": {
//           "TotalTax": 0
//         }, 
//         "MetaData": {
//           "CreateTime": "2015-07-24T10:35:08-07:00", 
//           "LastUpdatedTime": "2015-07-24T10:35:08-07:00"
//         }
//       }
//     ]
//   }, 
//   "time": "2015-07-24T10:38:50.01-07:00"
// }

lnQueryResponseStartPosition = loJson.IntOf("QueryResponse.startPosition")
lnQueryResponseTotalCount = loJson.IntOf("QueryResponse.totalCount")
lnQueryResponseMaxResults = loJson.IntOf("QueryResponse.maxResults")
lcTime = loJson.StringOf("time")
i = 0
lnCount_i = loJson.SizeOfArray("QueryResponse.Invoice")
do while i < lnCount_i
    loJson.I = i
    lcDocNumber = loJson.StringOf("QueryResponse.Invoice[i].DocNumber")
    lcSyncToken = loJson.StringOf("QueryResponse.Invoice[i].SyncToken")
    lcDomain = loJson.StringOf("QueryResponse.Invoice[i].domain")
    lcBalance = loJson.StringOf("QueryResponse.Invoice[i].Balance")
    lcBillAddrCity = loJson.StringOf("QueryResponse.Invoice[i].BillAddr.City")
    lcBillAddrLine1 = loJson.StringOf("QueryResponse.Invoice[i].BillAddr.Line1")
    lcBillAddrPostalCode = loJson.StringOf("QueryResponse.Invoice[i].BillAddr.PostalCode")
    lcBillAddrLat = loJson.StringOf("QueryResponse.Invoice[i].BillAddr.Lat")
    lcBillAddrLong = loJson.StringOf("QueryResponse.Invoice[i].BillAddr.Long")
    lcBillAddrCountrySubDivisionCode = loJson.StringOf("QueryResponse.Invoice[i].BillAddr.CountrySubDivisionCode")
    lcBillAddrId = loJson.StringOf("QueryResponse.Invoice[i].BillAddr.Id")
    lcTxnDate = loJson.StringOf("QueryResponse.Invoice[i].TxnDate")
    lcTotalAmt = loJson.StringOf("QueryResponse.Invoice[i].TotalAmt")
    lcCustomerRefName = loJson.StringOf("QueryResponse.Invoice[i].CustomerRef.name")
    lcCustomerRefValue = loJson.StringOf("QueryResponse.Invoice[i].CustomerRef.value")
    lcShipAddrCity = loJson.StringOf("QueryResponse.Invoice[i].ShipAddr.City")
    lcShipAddrLine1 = loJson.StringOf("QueryResponse.Invoice[i].ShipAddr.Line1")
    lcShipAddrPostalCode = loJson.StringOf("QueryResponse.Invoice[i].ShipAddr.PostalCode")
    lcShipAddrLat = loJson.StringOf("QueryResponse.Invoice[i].ShipAddr.Lat")
    lcShipAddrLong = loJson.StringOf("QueryResponse.Invoice[i].ShipAddr.Long")
    lcShipAddrCountrySubDivisionCode = loJson.StringOf("QueryResponse.Invoice[i].ShipAddr.CountrySubDivisionCode")
    lcShipAddrId = loJson.StringOf("QueryResponse.Invoice[i].ShipAddr.Id")
    lcDueDate = loJson.StringOf("QueryResponse.Invoice[i].DueDate")
    lcPrintStatus = loJson.StringOf("QueryResponse.Invoice[i].PrintStatus")
    lnDeposit = loJson.IntOf("QueryResponse.Invoice[i].Deposit")
    llSparse = loJson.BoolOf("QueryResponse.Invoice[i].sparse")
    lcEmailStatus = loJson.StringOf("QueryResponse.Invoice[i].EmailStatus")
    llApplyTaxAfterDiscount = loJson.BoolOf("QueryResponse.Invoice[i].ApplyTaxAfterDiscount")
    lcId = loJson.StringOf("QueryResponse.Invoice[i].Id")
    lnTxnTaxDetailTotalTax = loJson.IntOf("QueryResponse.Invoice[i].TxnTaxDetail.TotalTax")
    lcMetaDataCreateTime = loJson.StringOf("QueryResponse.Invoice[i].MetaData.CreateTime")
    lcMetaDataLastUpdatedTime = loJson.StringOf("QueryResponse.Invoice[i].MetaData.LastUpdatedTime")
    j = 0
    lnCount_j = loJson.SizeOfArray("QueryResponse.Invoice[i].LinkedTxn")
    do while j < lnCount_j
        loJson.J = j
        j = j + 1
    enddo
    j = 0
    lnCount_j = loJson.SizeOfArray("QueryResponse.Invoice[i].Line")
    do while j < lnCount_j
        loJson.J = j
        lnLineNum = loJson.IntOf("QueryResponse.Invoice[i].Line[j].LineNum")
        lcAmount = loJson.StringOf("QueryResponse.Invoice[i].Line[j].Amount")
        lcSalesItemLineDetailTaxCodeRefValue = loJson.StringOf("QueryResponse.Invoice[i].Line[j].SalesItemLineDetail.TaxCodeRef.value")
        lcSalesItemLineDetailItemRefName = loJson.StringOf("QueryResponse.Invoice[i].Line[j].SalesItemLineDetail.ItemRef.name")
        lcSalesItemLineDetailItemRefValue = loJson.StringOf("QueryResponse.Invoice[i].Line[j].SalesItemLineDetail.ItemRef.value")
        lcId = loJson.StringOf("QueryResponse.Invoice[i].Line[j].Id")
        lcDetailType = loJson.StringOf("QueryResponse.Invoice[i].Line[j].DetailType")
        j = j + 1
    enddo
    j = 0
    lnCount_j = loJson.SizeOfArray("QueryResponse.Invoice[i].CustomField")
    do while j < lnCount_j
        loJson.J = j
        lcDefinitionId = loJson.StringOf("QueryResponse.Invoice[i].CustomField[j].DefinitionId")
        lcInvType = loJson.StringOf("QueryResponse.Invoice[i].CustomField[j].Type")
        lcName = loJson.StringOf("QueryResponse.Invoice[i].CustomField[j].Name")
        j = j + 1
    enddo
    i = i + 1
enddo


release loJsonToken
release loRest
release loSbAuth
release loSbResponseBody
release loJson