majestic rails: quickbooks integration in rails app

By the end of this post you will be able to integrate your Web Hosted Application with QuickBooks Online Edition. You will understand below points.

Objectives:
1. Establish a communications channel with QuickBooks
2. Build a set of request messages
3. Send the request messages set to QuickBooks
4. Check the status codes & data in the response message set received from QuickBooks
5. Close the communications channel with QuickBooks

The Basic Communication Model is shown in Fig. 1

Fig. 1

Terminologies from Fig. 1
• Developer Application => Rails App
• Request => Https request generated using QBXML
• Response => Response over https in QBXML format.
• Company Owner’s Authorization => Authorization process for security purpose explained in detail in part-1. i.e. Establish a communications channel with QuickBooks
• Request Processor => Its nothing but a QBXML parser which process request from developer application and response from QuickBooks and sent in QBXML format to Developer application.
• QuickBooks => QuickBooks Account.

Now step by step:

1. Establish a communications channel with QuickBooks

Steps to establish connection between developer application and QuickBooks.
1. Obtain a test QBOE (QuickBooks Online Edition) account from IDN for testing during development. These are available to paid memberships and participants in the NFR program.

Purchase an account from QuickBooks
As the quickbooks doesn’t support Multitenant platform, so the process is defined in step-7 for connecting specific account with specific company if you have multitenant rails application.

2. Register your application with IDN (Register your app at appreg.intuit.com)

It’s an IDN Gateway Application Registration which is required to generate an Unique ID which is later used in https requests. You need to register valid https url of rails application with IDN.
URL: https://app_url
APPLICATION ID: Unique App Id generated- You will have some unique id generated eg: 169442259
Please make sure you have valid https ceritificate for the same url. Otherwise redirect it using proxies.

It also asks for some basic URLs which QuickBooks uses for further reference.

Subscribe URL: https://app_url/quickbooks/subscribe
Usually connection ticket is returned in this action when connection to QuickBooks account is created for first time with Rails App.
Change URL: https://app_url/quickbooks/change
Cancel URL: https://app_url/quickbooks/cancel
Login URL: https://app_url/login

3. Obtain a server certificate from a supported root certificate authority. This will be your https certificate.

4. Obtain a client certificate by generating a certificate signing request (CSR) and use the appreg.intuit.com site to get it signed by Intuit.
a) Obtain a client certificate by generating a certificate signing request (CSR):
i. Generate a new key:
ii. $ openssl genrsa -out idn.key 1024
iii. Generate CSR:
iv. $ openssl req -new -key idn.key -out idn-foo.csr
v. IMPORTANT: Common Name (Your Name) needs to be EXACTLY
vi. your.url(THIS MUST BE AN IP ADDRESS OF YOUR HOSTED APP SERVER):your.application.name
b) Get CSR signed at appreg.intuit.com
Go to site, copy and paste CSR text and sign that bad boy!
c) Create PEM
The signing process will return a certificate. Copy the certificate to a text file and name it qboe-cert.pem. Now open the key you used to generate the CSR and paste that into the same text file above the certificate.

5. Understand and follow the QBOE security requirements for hosted web applications.

Your hosted web application (Developer Application) needs a server certificate in order to receive callbacks from QBOE. Your hosted web application also needs a client certificate signed by Intuit in order to POST QBOE requests to QBOE. (You need register your application with IDN before you can generate your certificate signing request.)

6 .In your code, using SSL, implement the presentation of the client certificate to be used when posting to QBOE.
The Intuit-signed client certificate must be presented to QBOE at every POST to QBOE sites. You have to use SSL at your web server in conjunction with the client certificate. The client certificate is presented using “pem” feature of httparty gem.

require 'httparty'

class Qboe
include HTTParty
base_uri 'https://webapps.quickbooks.com/j/AppGateway'
format :xml
headers 'Content-Type' => 'application/x-qbxml'
pem File.read("#{RAILS_ROOT}/qboe-cert.pem")
end

7. In your code, prompt your customer to grant a connection ticket authorizing your application to access the QBOE Company. Respond by sending the customer to the QBOE login page to get a connection ticket. QBOE POSTs the connection ticket back to the application subscription URL you specified when you registered your application. Handle this POST at that URL and store the connection ticket securely.

Sending the User to QBOE to Create a Connection Ticket
In your main form displayed in the user’s web browser, provide a means for the customer to start the authorization process, for example, a Subscribe button. Whatever the means, respond to the customer’s action by sending the customer to the QBOE login page at the following URL:

for production QBOE:

https://login.quickbooks.com/j/qbn/sdkapp/confirm?appid=&serviceid=2004&appdata=

Appdata = > company id (It is not required or mandatory but preferred to use if you have multitenant application.)

After your application gets the POST from QBOE, you need to extract the connection ticket
from the POST in subscribe action.

8. In your code, implement session ticket handling code. That is, prior to sending QBOE requests for a particular customer, get a session ticket by sending a SignonMsgsRq containing a SignonAppCertRq with that customer’s connection ticket. You’ll POST this to the QBOE data exchange URL to get the session ticket in the Signon AppCertRs response.

Eg: session.erb
<?xml version="1.0"?>
<?qbxml version="6.0"?>
<QBXML>
   <SignonMsgsRq>
      <SignonAppCertRq>
         <ClientDateTime><%=today%></ClientDateTime>
         <ApplicationLogin><%=APPLICATION_LOGIN%></ApplicationLogin>
         <ConnectionTicket><%=CONNECTION_TICKET%></ConnectionTicket>
         <Language>English</Language>
         <AppID><%=APPLICATION_ID%></AppID>
         <AppVer>1</AppVer>
      </SignonAppCertRq>
   </SignonMsgsRq>
</QBXML>

   APPLICATION_LOGIN = 'app_url'
   CONNECTION_TICKET = 'TGT-68-wltE9aMgPpzDAC46PJwZvQ'
   APPLICATION_ID = '169442259'

def self.get_session_ticket
      today = Time.now.strftime("%Y-%m-%d")
      xml_to_send = ERB.new(get_file_as_string("lib/quickbooks/session.erb")).result(binding)
      result = post('/', :body => xml_to_send )
      result["QBXML"]["SignonMsgsRs"]["SignonAppCertRs"]["SessionTicket"]
    end

Response text:

{ "QBXML"=> { "SignonMsgsRs"=> { "SignonAppCertRs"=> {"ServerDateTime"=>"2010-03-17T05:29:26", "SessionTicket"=>"V1-211-BN1A_Bt_lRGjZQRgHeVIFQ:169714916", "statusSeverity"=>"INFO", "statusCode"=>"0" } } } }

2. Build a set of request messages
Below example explains a request created to create an invoice in QuickBooks.
<?xml version="1.0"?>
<?qbxml version="6.0"?>
<QBXML>
   <SignonMsgsRq>
    <SignonTicketRq>
      <ClientDateTime><%= today %></ClientDateTime>
      <SessionTicket><%= session %></SessionTicket>
      <Language>English</Language>
      <AppID><%= APPLICATION_ID %></AppID>
      <AppVer>1</AppVer>
    </SignonTicketRq>
</SignonMsgsRq>
<QBXMLMsgsRq onError="continueOnError">
    <InvoiceAddRq>
      <InvoiceAdd defMacro="TxnID:1234">
        <CustomerRef> 
          <ListID>4</ListID>
        </CustomerRef>
        <TxnDate>2010-10-10</TxnDate>
        
        <InvoiceLineAdd > 
          <ItemRef> 
            <FullName >Hours</FullName> 
          </ItemRef>
          <Desc >Its added from rails app.</Desc> 
          <Quantity >2</Quantity> 
          
          <Rate >111</Rate> 
          <Amount >101.00</Amount> 
          <ServiceDate>2010-10-10</ServiceDate>
        </InvoiceLineAdd>
        
        
      </InvoiceAdd>
    </InvoiceAddRq>
</QBXMLMsgsRq>
</QBXML>

Following Fields of Rails App are mapped with QuickBooks Invoice using QBXML.
Rails App (Eg. Time/Expense Field Names) QuickBooks Invoice Field Names
Contact/Matter Contact (Full Name) => Client Name/Bill To
Time Entry Date/Expense Entry => Date Service Date
Activity Type/Expense Type => Product/Service
Description => Description
Duration (Hrs) => Qty
Rate/hr ($) => Rate
Final Bill Amount => Amount

Actual Mapping in QBXML for expense entry in invoice is explained below.
QBXML:
    <InvoiceAddRq>
      <InvoiceAdd defMacro="TxnID:1234">
        <CustomerRef>
          <ListID>4</ListID>
        </CustomerRef>
        <TxnDate>2010-10-10</TxnDate>
      <InvoiceLineAdd >
          <ItemRef>
              <FullName >Fax</FullName>
          </ItemRef>
          <Desc >Fax to eric</Desc>
     <Amount >101.00</Amount>
          <ServiceDate>2010-10-10</ServiceDate>
        </InvoiceLineAdd>
    </InvoiceAddRq>

Eg: To add a client name in QuickBooks invoice we need to send ListID in QBXML request. ListID is acquired by executing a query to QuickBooks, Contact Full Name is the parameter passed to query.
3. Send the request messages set to QuickBooks
It’s done using “post” method from http arty gem.
4. Check the status codes & data in the response message set received from QuickBooks
Usually below set of parameters values exists in every request and response which we need to consider for every request and specifically for responses.

Attributes in Request and Response Messages

5. Close the communications channel with QuickBooks
Basically it not compulsory or needed while accessing QuickBooks Online Edition from Web Hosted application.

Tools & Software: Development Environment

 QuickBooks SDK 8.0
 QBXML Version 6.0
 Ruby 1.8.6
 Rails 2.3.5
 Net Beans IDE 6.7
 Gems: Http arty 0.5.2

References
1. Intuit QuickBooks SDK Technical Overview
2. Intuit QuickBooks SDK Programmer’s Guide
3. Onscreen Reference(OSR)
4. Web site for the Intuit Developer Network (IDN): http://developer.intuit.com
5. Blog: http://jnylund.typepad.com/joels_blog/2010/02/quickbooks-online-integration-from-ruby-on-rails-the-easy-way.html
6. Blog: http://www.depixelate.com/2007/05/03/rails-and-quickbooks-online-integration-part-1/
7. http://www.servicesidekick.com/ (Quickbooks Integration)

majestic rails

Monday, May 9, 2011

quickbooks integration in rails app

2 comments: