Integrate QuickBooks into a Customer Self-Service Portal
Build an automated portal solution that allows employees to view their QuickBooks information 24 hours a day, 7 days a week. You could just as easily apply the solution to the integration of an internal reporting system that needs access to the books of the business.
by Wellie Chao
Mar 9, 2006
Page 1 of 3
uickBooks users can achieve most of what they need for their businesses through a graphical user interface (GUI). As the business grows, however, an operator really should not manually enter information into QuickBooks via the GUI to perform certain tasks. An example of this type of task is customer information requests. If a customer wants to see his own balance or to check on his payment history, wouldn't it be nice if the customer could simply view the requested information on a Web site or hear the requested information on the telephone from an automated system? An automated solution would not only free up employee time for more productive uses, but it also would enhance customer service by allowing you to provide information 24 hours a day, 7 days a week without employing a middleman to respond to information requests.
This article demonstrates how to integrate QuickBooks with a customer self-service portal. The solution could just as easily be integration of an internally used reporting system that needs access to the books of the business, so what you learn from the example in this article will help with a variety of similar situations.
The Self-Service Web Site Setup
The customer self-service Web site requires two pieces:
A Web component: the HTML files and dynamic code in CGI, PHP, ASP, Java servlets, or another programming language
QuickBooks running on a server
This example assumes the Web piece resides on a Linux server and the QuickBooks application resides on a Windows machine, so to follow along you will need two machines: a Windows machine for running QuickBooks and a Linux machine for running the Web server that customers access to retrieve account information. The following are the files you need to download and install on the Windows machine (If you have already gone through the previous DevX article "Automate Tedious QuickBooks Tasks with COM-based API," you can skip steps 1 and 2.):
Install QuickBooks. If you do not already have QuickBooks, you can purchase it from quickbooks.intuit.com or you can request a trial version at http://www.quicklabs.com/Trial_Version.htm. This article assumes you are running at least QuickBooks 2006. At the time of writing, QuickBooks 2006 was the latest version, but anything newer (released in subsequent years) should also work.
The procedures in this article also should work on older versions of QuickBooks (back to 2003 or 2004), but the sample company file was created on QuickBooks 2006 and unfortunately Intuit provides only backward compatibility (i.e., 2006 opens 2003 and 2004 files) but not forward compatibility (i.e., 2004 opening 2006 files). My suggestion if you have an older version of QuickBooks is to request the trial version of 2006 so that you can use the sample company file. Once you have gone through the exercise in this article, you can make your own evaluation as to whether you need the features of 2006. If not, you can apply most, if not all, of the concepts to your existing installation of QuickBooks.
This article also assumes that you are running the U.S. edition of QuickBooks. The U.S. edition is different from the internationalized editions, so the examples may not work on editions other than the U.S. edition.
Download the QuickBooks SDK 5.0 from developer.intuit.com. Install this package on the machine where your copy of QuickBooks currently runs. Make sure you choose to install Remote Data Sharing (RDS) as part of the QuickBooks SDK installation. RDS allows you to control QuickBooks from another machine. By default, it installs during the QuickBooks SDK 5.0 installation, so just don't uncheck the checkbox that tells the SDK installer to install RDS.
Download the example file for the Windows machine where QuickBooks resides (QB Sample_Windows.zip). Unpack the ZIP file on the Windows machine somewhere you will remember. The example file contains a QuickBooks company file named MySoftwareCo.qbw.
Now that you have installed all necessary software on the Windows machine, you need to start up the necessary programs and open the necessary data files. Start up QuickBooks and open the MySoftwareCo.qbw company file. Then, start up RDS Server. Create the Web user with username qbwebuser and password poltergeist (you can change this). Creating the Web user in RDS needs to happen only once, not every time you reboot or start up RDS Server.
After you create the qbwebuser user, click the "Start" button in RDS Server. QuickBooks should show a dialog box asking whether you want to permit access to your company file by an external application. Choose "Yes, always" and then confirm that it is OK. You need to do this only once. QuickBooks alerts you that an unknown program is trying to access the data file. You must approve the access in order to continue. The permission-based access control method is how QuickBooks protects your data against unauthorized and possibly malicious programs.
Now let's turn to the Linux machine. You can use any Linux distribution, but if you are just starting out I recommend Fedora Core or CentOS. They are free and popular, and thus have larger communities to support you if you run into problems. If you already have Red Hat Enterprise Linux installed, that is also a great choice. This article does not cover installation of the operating system. It assumes you have already installed the OS. Before beginning, make sure you are logged in as root.
On the Linux machine, you will need to install the following files:
Apache httpd (the most popular Web server in use globally). Most Linux distributions include Apache, so you probably do not have to do anything to obtain Apache httpd. You can check by looking in /var/www and /etc/httpd if you run Fedora Core, CentOS, or Red Hat Enterprise Linux. If you have those directories and they have files inside of them, you already have Apache httpd. If you do not have Apache httpd, you must get it and install it. The best way is to use the package manager that came with your preferred distribution of Linux because that will resolve dependencies and update the internal list of installed packages. If you use Fedora Core or CentOS, the package manager is called yum (Yellow dog Updater, Modified). If you use Red Hat Enterprise Linux, the package manager is called up2date. If it is easier and more comfortable for you, you can also just download the source code from httpd.apache.org and compile it to binary format. If you compile from source, you will also need the gcc compiler suite. I suggest getting the latest version of Apache httpd in the 2.0.x stable branch, although if you are deploying Apache in a greenfield installation, the new 2.2.x stable branch is a good choice and should be suitable for deployment in production due to its stable (rather than experimental or development) status.
This example uses plain vanilla CGI, which just about any version of Apache supports. To install Apache on Linux from the source code, untar the archive file and type ./configure in the unpacked directory. Once the compile-time configuration process has completed, compile Apache httpd by typing make. Then, once compilation has completed, install Apache by typing make install. If you accepted the defaults by not specifying any command-line arguments to configure, Apache will be installed in /usr/local/apache2.
The README and INSTALL files in the unpacked Apache httpd source distribution describe how to compile and install Apache httpd in more depth, so if you run into problems, consult those files.
Perl 5.8.x. Almost all Linux distributions come with Perl. Check by typing perl -V. If you get a "command not found" error, then you need to install it. The best way to install Perl is via the package manager that comes with your Linux distribution. If you find it easier to download the source code and compile it, then fetch the source code from www.perl.org and then compile and install it using the same sequence of steps as those you went through for Apache httpd.
The README and INSTALL files in the source distribution will provide detailed instructions for installation if you are confused.
The XML::LibXML, XML::LibXSLT, and SOAP::Lite Perl modules. These are not normally included in a stock Linux distribution. Make sure to install Perl before installing the Perl modules. To install the Perl modules, type perl -MCPAN -e shell. If you have not configured CPAN before, you will be prompted for configuration values. First of all, you will be asked if you are ready for manual configuration. You should press Enter to accept the default choice of "yes." Then you will be asked for the CPAN build and cache directory. You should press Enter to accept the default location of /root/.cpan.
For the remaining questions, you may accept all the defaults up until the question about your favorite CPAN sites (also called mirrors). The mirror values will have no defaults, so just select a few near you geographically. If you do not know which ones are close, just select a few that sound good. The only side effect of poorly chosen mirrors is that the fetching of module files may go more slowly. From the CPAN shell, type install XML::LibXML. If it asks you whether it is OK to install dependencies, answer in the affirmative. If you have a problem installing XML::LibXML, you may be missing the libxml2 libraries, in which case you should exit the CPAN shell and type yum install libxml2, and then return to the CPAN shell by typing perl -MCPAN -e shell.
Next, type install XML::LibXSLT. If it asks you whether it is OK to install dependencies, answer in the affirmative. If you have a problem installing XML::LibXSLT, it is possible that you are missing the libxslt libraries, in which case you should exit the CPAN shell and type yum install libxslt, and then return to the CPAN shell by typing perl -MCPAN -e shell.
Now, type install SOAP::Lite. Accept all the defaults. You will use the XML and XSLT libraries to manipulate XML data, both for generating requests to QuickBooks and for processing responses from QuickBooks. The SOAP::Lite module allows your Perl application to invoke Web services on a remote machine using the simple object access protocol (SOAP). SOAP is a standard for XML-based remote method calls. Type quit to exit the CPAN shell.
Download the example code for the Linux machine (QB Sample_Linux.tar.gz). The example code includes XML files and Perl code for the Web application. Unpack the example code archive and copy the files to the document root of the Web server. If Apache httpd came with your distribution, the document is usually /var/www/html. If you installed Apache httpd from source code, the document root is probably /usr/local/apache2/htdocs.
In order to invoke Web services methods under SOAP, you need to have a description of the methods supported by the Web service, the types of arguments the Web service methods accept, and the types of values the Web service methods return. You can specify the information manually in SOAP::Lite by calling setter functions. However, that is a very manual method and is non-standard. Alternatively, you can use a standard method of specifying SOAP interface information called Web services description language (WSDL). You can pass a WSDL file to SOAP::Lite, allowing SOAP::Lite to parse the WSDL file for the information it needs to interface with the Web services. Fortunately, the QuickBooks SDK will auto-generate the WSDL file if you ask. To obtain the auto-generated WSDL file on the Linux machine, type wget --no-check-certificate https://(server):(port)/QBXMLRemote?wsdl, where (server) is the hostname or IP address of the Windows machine hosting QuickBooks and (port) is the port on which the RDS server is listening (usually 3790). The saved file will be called "QBXMLRemote?wsdl". You should rename it to QBXMLRemote.wsdl since filenames do not usually have question marks in them. Move the file to the Web server document root (/var/www/html or /usr/local/apache2/htdocs or another location if you manually specified a custom location). You will overwrite the existing QBXMLRemote.wsdl file.
You may need to make one change in the auto-generated WSDL file. The QuickBooks SDK inserts the hostname of the Windows machine into the WSDL file, but does not specify the fully qualified hostname (which should include the domain name). Depending on how you have your DNS set up, this may be a problem. To avoid problems, edit the WSDL file with a text editor and replace the hostname near the end of the file with the fully qualified hostname if you do not have the search domain set up or the IP address if you do not have DNS set up at all.
You will need to configure Apache httpd so that it allows execution of CGI programs and recognizes files with the *.cgi extension as CGI programs. To do this, edit httpd.conf (located in /etc/httpd/conf if Apache httpd came with your Linux distribution or in /usr/local/apache2/conf if you installed from source). Locate the line that says <Directory "/var/www/html">. If you installed Apache httpd from source, the line may say /usr/local/apache2/htdocs instead of /var/www/html. After that line, you should see a line that says something similar to "Options Indexes FollowSymLinks". The Options prefix should definitely be there but the actual options following the Options prefix may differ. You should insert the option "ExecCGI" following the other options. You will install the Web application in the Web server document root for simplicity. Next, search for the line that says "AddHandler cgi-script .cgi". In a default installation, this line is usually commented out, so all you need to do is uncomment it.