Quickbooks

[Colin Haig]

Hi All

Has anyone integrated FWH (xHarbour) with Quickbooks.

Regards

Colin

[TimStone]

Basically yes. It was provided by Ron many years ago on the xHarbour forums. The same code seems to work with Harbour although I haven't tested it yet.

Tim

[Ross_ValuSoft]

Hi Tim and Colin,

I just did a search with "quickbooks" on comp.lang.xharbour and didn't find any post by Ron. Are you able to point to a link ... or provide Ron's code?

Thanks,

Ross

[Eoeo]

sorry can you explain what are quickbooks ?

[Ross_ValuSoft]

sorry can you explain what are quickbooks ?

An accounting package.

[hag]

I would love some code to link up with QB. Now I link with a CSV file.

[Colin Haig]

I have found a product called QODBC that allows you to read and write to Quickbooks - presently
I use an ODBC connection to MYOB and this looks very similar.

Colin

[TimStone]

I am fighting some strange stuff right now but I will post some QB stuff soon. Basically, you can get the SDK free from Intuit and you can write your own functions to interact with it directly. It works and you don't need ODBC products and the like.

Tim

[TimStone]

I found it faster than I thought. This is the core class Ron shared a number of years ago. You will want to modify it accordingly. You will also want to get the SDK from Intuit ( which is free ).

Here is the class code:

Code: Select all  Expand view  RUN

/*
  Author: Ron Pinkas mailto:Ron@RonPinkas.com
  Copyright (c) 2002, Ron Pinkas and Abacus Data System Inc.
 */

#include "hbclass.ch"
#include "qbfc.ch"
#INCLUDE "fivewin.CH"

CLASS QBFC
   CLASSDATA oSessionManager
   CLASSDATA bAppErrHandler INIT ErrorBlock( {|e| QBFCErrHandler(e) } )

   DATA cAppID    INIT "001"
   DATA cAppName  INIT "ASW8"
   DATA cDataFile INIT ""
   DATA nMode     INIT omDontCare

   METHOD Connect( cDataFile, nMode, cAppId, cAppName  )
   METHOD Disconnect()
   METHOD CustomerAdd( acFields, acValues )
   METHOD CustomerFind( cFullName )
   METHOD CustomerMod( cListID, acFields, axValues )
   METHOD InvoiceAdd( acFields, acValues )
END CLASS

Function QBFCErrHandler( e )

    LOCAL oQB

    //TraceLog( e )

    IF e:CanSubstitute
       IF ValType( e:SubSystem ) == "C" .AND. e:SubSystem == "BASE"
          IF ValType( e:SubCode ) == "N" .AND. e:SubCode == 1004
             IF ValType( e:Operation ) == "C" .AND. e:Operation == "GETVALUE"
                //TraceLog( "Intercepted" )
                Return NIL
             ENDIF
          ENDIF
       ENDIF
    ENDIF

    oQB := QBFC()

Return Eval( oQB:bAppErrHandler, e )

METHOD Connect( cDataFile, nMode, cAppID, cAppName  ) CLASS QBFC

   IF cAppId != NIL
      QSelf():cAppID := cAppID
   ENDIF
   IF cAppName != NIL
      QSelf():cAppName := cAppName
   ENDIF
   IF cDataFile != NIL
      QSelf():cDataFile := cDataFile
   ENDIF
   IF nMode != NIL
      QSelf():nMode := nMode
   ENDIF

   IF QSelf():oSessionManager == NIL
      // Create the session manager object
      QSelf():oSessionManager := CreateObject( "QBFC5.QBSessionManager" )

      QSelf():oSessionManager:OpenConnection( QSelf():cAppID, QSelf():cAppName )
      QSelf():oSessionManager:BeginSession( QSelf():cDataFile, QSelf():nMode )
   ENDIF

Return QSelf()

METHOD Disconnect() CLASS QBFC

   IF QSelf():oSessionManager != NIL
      QSelf():oSessionManager:EndSession()
      QSelf():oSessionManager:CloseConnection()
      QSelf():oSessionManager := NIL
   ENDIF

Return QSelf()

METHOD CustomerAdd( acFields, axValues ) CLASS QBFC

   LOCAL oRequestMsgSet, oCustomerAdd, oResponseMsgSet, oResponse, oCustomerRet
   LOCAL cField, nIndex := 1
   LOCAL nStart, nAt, oParent, cParent

   IF QSelf():oSessionManager == NIL
      QSelf():Connect()
   ENDIF

   // Create the message set request object (XML version 1.1)
   oRequestMsgSet := QSelf():oSessionManager:CreateMsgSetRequest( "US", 1,1 )

   // Initialize the request's attributes
   oRequestMsgSet:Attributes:OnError := roeContinue

   // Add the request to the message set request object
   oCustomerAdd := oRequestMsgSet:AppendCustomerAddRq

   FOR EACH cField IN acFields
      nStart := 1
      oParent := oCustomerAdd
      WHILE ( nAt := At( ":", cField, nStart ) ) > 0
         cParent := SubStr( cField, nStart, nAt - nStart )
         nStart := nAt + 1
         oParent := __ObjSendMsg( oParent, cParent )
      END

      __ObjSendMsg( oParent, SubStr( cField, nStart ) ):SetValue( axValues[nIndex++] )
   NEXT

   // Perform the request
   oResponseMsgSet := QSelf():oSessionManager:DoRequests( oRequestMsgSet )

   // The response list contains only one response, which corresponds to our single request.
   oResponse := oResponseMsgSet:ResponseList:GetAt(0)

   IF oResponse:StatusCode != 0
       MsgAlert( "Add Customer failed!;Status: Code = " + CStr( oResponse:StatusCode ) + ", Severity = " + oResponse:StatusSeverity + ", Message = " + oResponse:statusMessage )
       Return NIL
   ENDIF

   //The response detail for Add is an ICustomerRet.
   oCustomerRet := oResponse:Detail

Return oCustomerRet

METHOD CustomerFind( cFullName ) CLASS QBFC

   LOCAL oRequestMsgSet, oCustomerFind, oResponseMsgSet, oResponse, oCustomerRet
   LOCAL cField, nIndex := 1

   IF QSelf():oSessionManager == NIL
      QSelf():Connect()
   ENDIF

   // Create the message set request object (XML version 1.1 )
   oRequestMsgSet := QSelf():oSessionManager:CreateMsgSetRequest( "US", 1, 1 )

   // Initialize the request's attributes
   oRequestMsgSet:Attributes:OnError := roeContinue

   // Add the request to the message set request object
   oCustomerFind := oRequestMsgSet:AppendCustomerQueryRq

   oCustomerFind:ORCustomerListQuery:FullNameList:Add( cFullName )

   // Perform the request
   oResponseMsgSet := QSelf():oSessionManager:DoRequests( oRequestMsgSet )

   // The response list contains only one response, which corresponds to our single request.
   oResponse := oResponseMsgSet:ResponseList:GetAt(0)

   IF oResponse:StatusCode != 0
       Alert( "Find Customer failed!;Status: Code = " + CStr( oResponse:StatusCode ) + ", Severity = " + oResponse:StatusSeverity + ", Message = " + oResponse:statusMessage )
       Return NIL
   ENDIF

   //First and only ICustomerRet.
   oCustomerRet := oResponse:Detail:GetAt(0)

Return oCustomerRet

METHOD CustomerMod( cListID, nEditSequence, acFields, axValues ) CLASS QBFC

   LOCAL oRequestMsgSet, oCustomerMod, oResponseMsgSet, oResponse, oCustomerRet
   LOCAL cField, nIndex := 1
   LOCAL nStart, nAt, oParent, cParent

   IF QSelf():oSessionManager == NIL
      QSelf():Connect()
   ENDIF

   // Create the message set request object (XML version 1.1)
   oRequestMsgSet := QSelf():oSessionManager:CreateMsgSetRequest( "US", 1, 1 )

   // Initialize the request's attributes
   oRequestMsgSet:Attributes:OnError := roeContinue

   // Add the request to the message set request object
   oCustomerMod := oRequestMsgSet:AppendCustomerModRq

   oCustomerMod:ListID:SetValue( cListID )
   oCustomerMod:EditSequence:SetValue( nEditSequence )

   FOR EACH cField IN acFields
      nStart := 1
      oParent := oCustomerMod
      WHILE ( nAt := At( ":", cField, nStart ) ) > 0
         cParent := SubStr( cField, nStart, nAt - nStart )
         nStart := nAt + 1
         oParent := __ObjSendMsg( oParent, cParent )
      END

      __ObjSendMsg( oParent, SubStr( cField, nStart ) ):SetValue( axValues[nIndex++] )
   NEXT

   // Perform the request
   oResponseMsgSet := QSelf():oSessionManager:DoRequests( oRequestMsgSet )

   // The response list contains only one response, which corresponds to our single request.
   oResponse := oResponseMsgSet:ResponseList:GetAt(0)

   IF oResponse:StatusCode != 0
       Alert( "Modify Customer failed!;Status: Code = " + CStr( oResponse:StatusCode ) + ", Severity = " + oResponse:StatusSeverity + ", Message = " + oResponse:statusMessage )
       Return NIL
   ENDIF

   //The response detail for Mod requests is ICustomerRet.
   oCustomerRet := oResponse:Detail

Return oCustomerRet

METHOD InvoiceAdd( acFields, axValues ) CLASS QBFC

   LOCAL oRequestMsgSet, oInvoiceAdd, oResponse, oInvoiceRet
   LOCAL cField, nIndex := 1
   LOCAL nStart, nAt, oParent, cParent
   PUBLIC oResponseMsgSet

   IF QSelf():oSessionManager == NIL
      QSelf():Connect()
   ENDIF

   // Create the message set request object (XML version 1.1)
   oRequestMsgSet := QSelf():oSessionManager:CreateMsgSetRequest( "US", 5,0 )  // Ron had 1, 1  but I suggest 5,0

   // Initialize the request's attributes
   oRequestMsgSet:Attributes:OnError := roeContinue

   // Add the request to the message set request object
   oInvoiceAdd := oRequestMsgSet:AppendInvoiceAddRq

   FOR EACH cField IN acFields
      nStart := 1
      oParent := oInvoiceAdd
      WHILE ( nAt := At( ":", cField, nStart ) ) > 0
         cParent := SubStr( cField, nStart, nAt - nStart )
         nStart := nAt + 1
         oParent := __ObjSendMsg( oParent, cParent )
      END

      __ObjSendMsg( oParent, SubStr( cField, nStart ) ):SetValue( axValues[nIndex++] )
   NEXT

   // Perform the request
   oResponseMsgSet := QSelf():oSessionManager:DoRequests( oRequestMsgSet )

   // The response list contains only one response, which corresponds to our single request.
   oResponse := oResponseMsgSet:ResponseList:GetAt(0)

   IF oResponse:StatusCode != 0
       MsgAlert( "Add Invoice failed!;Status: Code = " + CStr( oResponse:StatusCode ) + ", Severity = " + oResponse:StatusSeverity + ", Message = " + oResponse:statusMessage )
       Return NIL
   ENDIF

   //The response detail for Add is an ICustomerRet.
   oInvoiceRet := oResponse:Detail

Return oInvoiceRet



EXIT Procedure QBDisconnect()

   LOCAL oQB := QBFC()

   oQB:Disconnect()

Return

 

And here is the CH file

Code: Select all  Expand view  RUN

// ENOpenMode
#define omSingleUser 0
#define omMultiUser 1
#define omDontCare 2

// ENRqOnError
#define roeStop 0
#define roeContinue 1
#define roeRollback 2

// ENReleaseLevel
#define rlPreAlpha 0
#define rlAlpha 1
#define rlBeta 2
#define rlRelease 3

// ENORPurchaseOrderLineRet
#define orpolrNA -1
#define orpolrPurchaseOrderLineRet 0
#define orpolrPurchaseOrderLineGroupRet 1

// ENORCreditMemoLineAdd
#define orcmlaNA -1
#define orcmlaCreditMemoLineAdd 0
#define orcmlaCreditMemoLineGroupAdd 1

// ENORDateRangeFilter
#define ordrfNA -1
#define ordrfModifiedDateRangeFilter 0
#define ordrfTxnDateRangeFilter 1

// ENORTimeTrackingTxnQuery
#define ortttqNA -1
#define ortttqTxnID 0
#define ortttqTimeTrackingTxnFilter 1

// ENORNameFilter
#define ornfNA -1
#define ornfNameFilter 0
#define ornfNameRangeFilter 1

// ENORPurchaseOrderLineAdd
#define orpolaNA -1
#define orpolaPurchaseOrderLineAdd 0
#define orpolaPurchaseOrderLineGroupAdd 1

// ENORAccountFilter
#define orafNA -1
#define orafFullName 0
#define orafFullNameWithChildren 1

// ENORDiscountRate
#define ordrNA -1
#define ordrDiscountRate 0
#define ordrDiscountRatePercent 1

// ENORTermsRet
#define ortrNA -1
#define ortrStandardTermsRet 0
#define ortrDateDrivenTermsRet 1

// ENORRate
#define orrNA -1
#define orrRate 0
#define orrRatePercent 1

// ENORRefNumberFilter
#define orrnfNA -1
#define orrnfRefNumberFilter 0
#define orrnfRefNumberRangeFilter 1

// ENORItemLineRet
#define orilrNA -1
#define orilrItemLineRet 0
#define orilrItemGroupLineRet 1

// ENORSalesPurchase
#define orspNA -1
#define orspSalesOrPurchase 0
#define orspSalesAndPurchase 1

// ENORToDoListQuery
#define ortdlqNA -1
#define ortdlqListID 0
#define ortdlqToDoListFilter 1

// ENORAccountListQuery
#define oralqNA -1
#define oralqListID 0
#define oralqFullName 1
#define oralqAccountListFilter 2

// ENOREntityRet
#define orerNA -1
#define orerCustomerRet 0
#define orerEmployeeRet 1
#define orerOtherNameRet 2
#define orerVendorRet 3

// ENORSalesPurchaseMod
#define orspmNA -1
#define orspmSalesOrPurchaseMod 0
#define orspmSalesAndPurchaseMod 1

// ENORListQuery
#define orlqNA -1
#define orlqListID 0
#define orlqFullName 1
#define orlqListFilter 2

// ENORVendorListQuery
#define orvlqNA -1
#define orvlqListID 0
#define orvlqFullName 1
#define orvlqVendorListFilter 2

// ENORSalesReceiptLineRet
#define orsrlrNA -1
#define orsrlrSalesReceiptLineRet 0
#define orsrlrSalesReceiptLineGroupRet 1

// ENORCustomerListQuery
#define orclqNA -1
#define orclqListID 0
#define orclqFullName 1
#define orclqCustomerListFilter 2

// ENORItemLineAdd
#define orilaNA -1
#define orilaItemLineAdd 0
#define orilaItemGroupLineAdd 1

// ENORMarkupRate
#define ormrNA -1
#define ormrMarkupRate 0
#define ormrMarkupRatePercent 1

// ENOREstimateLineRet
#define orelrNA -1
#define orelrEstimateLineRet 0
#define orelrEstimateLineGroupRet 1

// ENORInvoiceQuery
#define oriqNA -1
#define oriqTxnID 0
#define oriqRefNumber 1
#define oriqInvoiceFilter 2

// ENORTxnQuery
#define ortqNA -1
#define ortqTxnID 0
#define ortqRefNumber 1
#define ortqTxnFilter 2

// ENORInvoiceLineRet
#define orilr1NA -1
#define orilrInvoiceLineRet 0
#define orilrInvoiceLineGroupRet 1

// ENORPrice
#define orpNA -1
#define orpPrice 0
#define orpPricePercent 1

// ENORNotes
#define ornNA -1
#define ornNotes 0
#define ornAppendNotes 1

// ENORTimeTrackingEntityFilter
#define orttefNA -1
#define orttefFullName 0
#define orttefListID 1

// ENORApplyPayment
#define orapNA -1
#define orapIsAutoApply 0
#define orapAppliedToTxnAdd 1

// ENORBillQuery
#define orbqNA -1
#define orbqTxnID 0
#define orbqRefNumber 1
#define orbqBillFilter 2

// ENDateMacro
#define dmThisWeekToDate 0
#define dmThisMonthToDate 1
#define dmThisFiscalQuarterToDate 2
#define dmThisFiscalYearToDate 3
#define dmLastWeek 4
#define dmLastMonth 5
#define dmLastFiscalQuarter 6
#define dmLastFiscalYear 7

// ENORTxnDateRangeFilter
#define ortdrfNA -1
#define ortdrfTxnDateFilter 0
#define ortdrfDateMacro 1

// ENORSalesReceiptLineAdd
#define orsrlaNA -1
#define orsrlaSalesReceiptLineAdd 0
#define orsrlaSalesReceiptLineGroupAdd 1

// ENOREntityFilter
#define orefNA -1
#define orefFullName 0
#define orefFullNameWithChildren 1

// ENORCreditMemoLineRet
#define orcmlrNA -1
#define orcmlrCreditMemoLineRet 0
#define orcmlrCreditMemoLineGroupRet 1

// ENOREstimateLineAdd
#define orelaNA -1
#define orelaEstimateLineAdd 0
#define orelaEstimateLineGroupAdd 1

// ENORItemRet
#define orirNA -1
#define orirItemServiceRet 0
#define orirItemNonInventoryRet 1
#define orirItemOtherChargeRet 2
#define orirItemInventoryRet 3
#define orirItemSubtotalRet 4
#define orirItemDiscountRet 5
#define orirItemPaymentRet 6
#define orirItemSalesTaxRet 7
#define orirItemSalesTaxGroupRet 8
#define orirItemGroupRet 9

// ENORInvoiceLineAdd
#define orila1NA -1
#define orilaInvoiceLineAdd 0
#define orilaInvoiceLineGroupAdd 1

// ENTxnVoidType
#define tvtInvoice 0
#define tvtSalesReceipt 1
#define tvtCreditMemo 2
#define tvtBill 3
#define tvtVendorCredit 4
#define tvtCheck 5
#define tvtCreditCardCharge 6
#define tvtCreditCardCredit 7
#define tvtJournalEntry 8

// ENPaidStatus
#define psAll 0
#define psPaidOnly 1
#define psNotPaidOnly 2

// ENActiveStatus
#define asActiveOnly 0
#define asInactiveOnly 1
#define asAll 2

// ENAccountType
#define atAccountsPayable 0
#define atAccountsReceivable 1
#define atBank 2
#define atCostOfGoodsSold 3
#define atCreditCard 4
#define atEquity 5
#define atExpense 6
#define atFixedAsset 7
#define atIncome 8
#define atLongTermLiability 9
#define atOtherAsset 10
#define atOtherCurrentAsset 11
#define atOtherCurrentLiability 12
#define atOtherExpense 13
#define atOtherIncome 14
#define atNonPosting 15

// ENListDelType
#define ldtAccount 0
#define ldtCustomer 1
#define ldtEmployee 2
#define ldtOtherName 3
#define ldtVendor 4
#define ldtStandardTerms 5
#define ldtDateDrivenTerms 6
#define ldtClass 7
#define ldtSalesRep 8
#define ldtCustomerType 9
#define ldtVendorType 10
#define ldtJobType 11
#define ldtCustomerMsg 12
#define ldtPaymentMethod 13
#define ldtShipMethod 14
#define ldtSalesTaxCode 15
#define ldtToDo 16
#define ldtItemService 17
#define ldtItemNonInventory 18
#define ldtItemOtherCharge 19
#define ldtItemInventory 20
#define ldtItemSubtotal 21
#define ldtItemDiscount 22
#define ldtItemPayment 23
#define ldtItemSalesTax 24
#define ldtItemSalesTaxGroup 25
#define ldtItemGroup 26

// ENTxnDelType
#define tdtInvoice 0
#define tdtEstimate 1
#define tdtSalesReceipt 2
#define tdtCreditMemo 3
#define tdtReceivePayment 4
#define tdtCharge 5
#define tdtChargeCredit 6
#define tdtPurchaseOrder 7
#define tdtBill 8
#define tdtVendorCredit 9
#define tdtTimeTracking 10
#define tdtCheck 11
#define tdtCreditCardCharge 12
#define tdtCreditCardCredit 13
#define tdtJournalEntry 14

// ENDoneStatus
#define dsNotDoneOnly 0
#define dsDoneOnly 1
#define dsAll 2

// ENDetailAccountType
#define datToBeDetermined 0

// ENPaySalesTax
#define pstMonthly 0
#define pstQuarterly 1
#define pstAnnually 2

// ENFirstDayOfWeek
#define fdowMonday 0
#define fdowTuesday 1
#define fdowWednesday 2
#define fdowThursday 3
#define fdowFriday 4
#define fdowSaturday 5
#define fdowSunday 6

// ENEmployeeType
#define etRegular 0
#define etOfficer 1
#define etStatutory 2
#define etOwner 3

// ENGender
#define gMale 0
#define gFemale 1

// ENMatchCriterion
#define mcStartsWith 0
#define mcContains 1
#define mcEndsWith 2

// ENJobStatus
#define jsNone 0
#define jsPending 1
#define jsAwarded 2
#define jsInProgress 3
#define jsClosed 4
#define jsNotAwarded 5

// ENDeliveryMethod
#define dmPrint 0
#define dmFax 1
#define dmEmail 2

// ENFirstMonthFiscalYear
#define fmfyJanuary 0
#define fmfyFebruary 1
#define fmfyMarch 2
#define fmfyApril 3
#define fmfyMay 4
#define fmfyJune 5
#define fmfyJuly 6
#define fmfyAugust 7
#define fmfySeptember 8
#define fmfyOctober 9
#define fmfyNovember 10
#define fmfyDecember 11

// ENFirstMonthIncomeTaxYear
#define fmityJanuary 0
#define fmityFebruary 1
#define fmityMarch 2
#define fmityApril 3
#define fmityMay 4
#define fmityJune 5
#define fmityJuly 6
#define fmityAugust 7
#define fmitySeptember 8
#define fmityOctober 9
#define fmityNovember 10
#define fmityDecember 11

// ENTxnType
#define ttInvoice 0
#define ttCharge 1
#define ttCheck 2
#define ttCreditMemo 3
#define ttJournalEntry 4
#define ttReceivePayment 5

// ENCalculateChargesFrom
#define ccfDueDate 0
#define ccfInvoiceOrBilledDate 1

// ENOperator
#define oLessThan 0
#define oLessThanEqual 1
#define oEqual 2
#define oGreaterThan 3
#define oGreaterThanEqual 4

// ENAgingReportBasis
#define arbAgeFromDueDate 0
#define arbAgeFromTransactionDate 1

// ENSummaryReportBasis
#define srbAccrual 0
#define srbCash 1

// ENWageType
#define wtHourly 0
#define wtSalary 1

 

This code was offered before Intuit changed their server setup ( with the release of Vista ) and I have not updated anything since then. Essentially, if you run Quickbooks on the same computer as your program, whenever you run a function to output data to QB, it will be OK ( once you have done the setup ).

Tim

[Colin Haig]

Thanks Tim

I will have a look at the code.

Regards

Colin

[TimStone]

I don't think I made this clear, but I do have the code implemented in my own package. It does work fine. With the SDK ( free ) from Intuit, you get all you need to distribute to your clients to make this happen. Its really quite seamless.

Tim

[Ross_ValuSoft]

Thanks Tim,

My client (in the US) is using "quickbooks manufacturing". I guess I will have to throw together something that can confirm access to his data before attempting what he wants to do with it.

Cheers,

Ross