QuickBooks is Coming to Drupal, but May Need a bit 'o Help

We encourage users to post events happening in the community to the community events group on https://www.drupal.org.
Souvent22's picture

Quickbooks is coming to drupal soon, but I may need help from the community.

I currently have a module that uses SOAP and the RDS (Remote Data Sharing) server from Intuit (a program that runs locally on your machine locally and runs. It acts as a "proxy" b/t you and your quickbooks file, and accepts SOAP requests and pulls the information from quickbooks for you and returns the information in XML format). The module is pretty simple in form currently.

  • On a users "My Account" form, an admin user is presented with a drop down of current customers from Quickbooks. The admin then selects what "customers" the registered user can view.
  • A user can view a "customer's" information they have rights to. Currently this includes the customers genreal information, and all invoices.
  • A cron runs each night (or on the settings page, you can have it run up to 10 times a day), and it fetches all "new" information by only requesting the information that has been modified and/or created since the last cron run.

These XML files that are returned (if you are running a large quickbooks file) can be somewhat larget. But parsing has not been an issue. Right now, parsing is averaging 2 seconds per 1 MB of XML. I am using the SAX based xml parser in PHP ( http://www.php.net/manual/en/function.xml-parser-create.php ). It is pretty efficient.

However, I need to make more parsers. It is very easy to create a parser if one follows the template I have set forth in qb_customer_parser.class.php. It is just somewhat tedious for me.

Quickbooks is based on what it calls "Lists". What I need now is to create a parser for each major "lists".
These lists include (and this is not an exhastive listing):

  • Customers
  • Invoices
  • Items
  • Accounts
  • Account Classes
  • Tax Codes
  • Tax Amounts

My ultimate goal in doing this is an integration with E-commerce module, with Drupal acting a a front in, and "batch synchronization" with the Quickbooks file. Thus, Drupal + E-Commerce becomes the "goto" solution for small businesses with Quickbooks that are looking for a web presence, but do not what the hassel of double entry, or some complicated scheme to keep their quickbooks and e-commerece information/data/accounts in sync.

Futhermore, this will pave the way for E-Commerece to finally have some sort of General Ledger and be a true 'accounting' based solution rather than just an inventory and invoicing system. Not having a GL or any type of accounting rules is my biggiest gripe of an overall awesome module.

My first milestone though is getting this module out and some what stable. I is currently a type of "Read-Only" module in that it just display customer and invoice information from quickbooks, but you can't edit the information. I need to think that totally through, espeically since this is dealing with financial data of a business ('em InnoDB/Transactions ?).

I shall be posting the quickbooks modules to my sandbox in the coming days, and posting something similar to what I have written here on the forums asking for xml-parser help. I thought I would post here for thoughts first.

  • Souvent22

P.S. Note: The QuickBooks module uses qbXML 1.1 even though the current implementation is 6.0. All prior qbXML's are compatibile with any of the new SDK's, but the new qbXML formats are not backwards compatible. Meaing, you can't use qbXML 6.0 on a QB 2002 installation because 2002 only understands 1.1, but you can use 1.1 on a QB 2006 installation because all prior qbXML's are compatible.

Comments

what direction

moshe weitzman's picture

in your example, you are pulling customer and invoice data from QB into drupal. but in your ultimate vision, seems like you want sales data to automically flow down into QB. so what the goal here. why bother showing QB data through Drupal? to me, the sales transactions => QB is much more interesting.

Customer Service

Darren Oh's picture

Why? Wouldn't customers appreciate being able to look up their accounts on the Web?

Saftey

Souvent22's picture

Moshe,

This is very true. However, with data this sensitive, I like toe 'crawl before I walk'. Meaning, I want to only pull from QB before I start "pushing" into it. Espcially since InnoDB tables aren't exactly "native" in Drupal world :). Pulling from QB first also makes it possible for me to get a good grasp and handeling of where the information in QB is stored, etc. Once I have pulled enough information that a "customer" and/or "business owner" would like to see. My next step is to start to make that data editable.

e.g. a customer can update their information and go into a moderation queu for the business to commit later. Invoices from an ecommerce site and flow into Quickbooks every half-hour, and fire off any number of events depending on what items are on the invoice, etc.

I do agree, pulling the data yawn, ok yeah it's a bit boring....but it leads to bigger/better things.

I wil admit though, I am a bit nervous with a module like this. That last thing I want to hear is, "oh yeah, that Souvent22 guy with his QB module screwed up my entire QB file". Thank goodness for handels and not names in the IT world hehe :).

Souvent22

Much needed - very grateful

rgs2's picture

Will be doing a new site for an art school currently using a horrible filemaker pro custom database, quickbooks and manually enterring cc numbers.

This module would be a huge help, especially with precautions like a moderation queu. Thanks.

Direction

Souvent22's picture

Hm, I guess I didn't totally answer your question of "direction".

The complete model will be "push/pull", with time-stamps being key. qbXML allows almost all requests to have a and/or element. Using this along with and "drupal-side" time-stamps will help keep the entire system from going to shambels. All transactions that flow into QB must be in a SQL transaction. There has to be some way to verify that a batch of invoices was recieved correctly by QB. The RDS server is a bit cumberson to work on a 'per-item' basis. It works MUCH better with batches.

There are 2 major time-stamps (drupal side) that I will keep track of. 1 is a "per-item" stamp. Meaning, a customer for example, will have a 'time_edited' field, if that customer was edited "drupal-side".
The second is a "cron-stamp", meaning a stamp of when the last cron job to update anylists from the last cron job ran (yeah, that's a little hard to follow, but ya know, just stamping during a cron run, and using that time to pull only data modified/created since the last run.

The last issue are "collisions". To handel these, and to save for simplicities sake, currently I'm thinking of having a couple options:
1. QB is always right (probabley the best option)
2. Drupal is always right (I like Drupal...but Drupal's not a CPA)
3. A list of collisions so that the user can verify each by hand, side/by/side (a kind of diff)

Anyway, those are my thoughts. Right now though, I'm just working on the dispalying of information. I got a little side tracked with Oracle. (about to rant about that in a sec. that's bascially dead in 5.0 currently)

that clearer

moshe weitzman's picture

thanks for the details. good move to proceed cautiously on this stuff.

Excellent idea, also,

coupet's picture

Excellent idea, also, SQL-Ledger could be an option!

Thanks for the Direction

Souvent22's picture

Coupet,

Hm, I had never heard of SQL-Ledger. I thought you just meant a database ledger like "SQL Ledger". :). But for some reason you comment haunted me that it meant more. :). I'm looking into SQL-Ledger now.

Souvent22

There is an add-in for osCommerce

walterbyrd's picture

I don't know if this is helpful, but osCommerce has an add-in for importing data into QuickBooks. It's called Quickbooks Import QBI. You might be able to learn something by looking at the code.

BTW: to the guy who asked: "why do this" a lot of accounts insist on QB files. At least, that's one reason.

walterbyrd's picture

If you use paypal to accept payments, I suppose paypal will do the quickbooks intergration for you.

It's Just an email reminder tied into PayPal

Souvent22's picture

The Request wizard for Quickbooks and/or Outlook is just a glorified email-reminder with a link that takes them to paypal to accept the payment. This is completly different from the goal of this module, which is an integrated flow of information (invoices, customers, accounts, payments, etc.) b/t QB and Drupal.

I'm happy to offer any

rszrama's picture

I'm happy to offer any insight I can into this work. I've created two solutions for a small business to integrate their osCommerce with QuickBooks using qbxml and the QuickBooks Web Connector. I've done more than enough to know that Intuit's tech support is horrible and their software is buggy by default. But it's not impossible!

I am curious about the abilities of the RDS Server against the QBWC. I'm already planning on porting the code from an app I developed into Drupal, but it's based on the QBWC. For some reason, at the time I didn't think going the way of the RDS Server would work fo rus. I know with older versions of qbxml, you lose some very important functionality... but that may be because the QBWC is buggy. For instance, up until the latest version of the qbxml, I couldn't import payment information. They just now got around to adding support for the customer data fields on POs as well.. which was a huge oversight on their part.

I have available for your use or modification a class that generates qbxml from a data array for many of the list items you've mentioned, maybe all the ones you'll need. It just takes an associative array of data with keys for the various tags accepted for each request and spits out the appropriate qbxml. You'd have to backport these, I assume, to qbxml 1.1 for your use. Not a huge deal.

Also, for issues of compatibility, I'm wondering how you're using SOAP... I seem to remember in development that to get things to work properly on PHP4 I had to scrap PHP's SOAP functions and use the NuSOAP library. (Slightly modified it to make it work on PHP4/5.)

Sounds like a Plan

Souvent22's picture

Rszrama,

Awesome. Yes, I would def. like to collaborate with you. I had already thought about the qbXML version problem. What I had thought about doing was encasulating all major 'functions' into an interface (e.g. get_account_types, load_account, load_invoice, etc. etc.) in some fashion. And them implementing the interface in a class. Thus you could have multiple classes of multiple versions that all essentially do the same thing (if you are following my logic and the OO methodology of it). So I could send a qbXML query to find their version, and load the correct class, but the drupal module woudln't care becuase the class would still be implementing a standard interface.

Note: You are right though, some information/functions are only availiable with newer versions of qbXML and QB. This makes sense though, Intuit, from a business perspective, doesn't want to fully open up their API at once. Someone could come along, and make a better, more intuitive interface than they have. Thus, the api is buggy, support awful, and features 'trickle' in.

Currently, I have paused QB, but will revisit it in about a week to a week and a half. I'll ping you soon though.

As far as QBWC...yeah, I had thought about that route., but my initial project was so simple. I didn't need the advanced features that you can have with QBWC, and QB sits behind our firewall, and Drupal in front of it, so there was not a security concern of someone getting to our RDS server directly, (unless that hacked my Drupal install, and somehow could make drupal send their own hijacked SOAP requests to the RDS server, highly unlikely). But, I don't see why the module could not be setup to do both.

Ok, gotta get back to a few projects, but I'll be in touch.

P.S. Yes, I dropped PHP's internal SOAP for NuSOAP a long time ago on many projects. I always use NuSOAP. Sorry PHP, NuSOAP is such a better and easier implementation. Perhaps one day that will change. Kudos on your XML parsers though :).

This is great! One other place you might look...

ndru's picture

You're certainly right that QB is becoming the de-facto standard in many situations. A couple of years ago I was investigating exactly this problem in the context of setting up administration systems for non-profit organizations in Canada. At that time, I came across the solution called Quasar, which seemed like a very robust system from an accounting point of view (somewhat better than SQL-Ledger). I had, and still have, no knowledge of how easy it would be to integrate it with Drupal. All except the retail point-of-sale module is available under GPL.

http://www.linuxcanada.com/quasar.shtml

QB Web Connector?

simonc's picture

Hi Souvent22 & Rszrama,

I too have been thinking about QB integration, but have only now been able to spend a bit of time researching it. My goal is to import into QB:

  • customers - users that have made a purchase
  • vendors - users that own stores, or in my case, rental property owners
  • sales invoices - what we bill the guest, including tax breakdowns
  • bills - what we owe the property owner, less commission

According to Intuit, IIF import (used by osCommerce & Zen Cart) are no longer supported. IIF bypasses the business logic and can corrupt the database. Trivial changes to a customer name (change case, add puctuation), will create a new customer record.

qbXML definitely seems to be the way to go although perhaps not entirely straightforward. The RDS server sounds fine in some circumstances, but since it is designed for LAN use, I think it limits its usefulness.

The Web Connector on the otherhand seems to be best suited to general purpose use (or at least what I want to do!)

From my QB, I run up the Web Connector sync program. It connects to my Drupal server (at my ISP), authenticates, then asks for a command. The Drupal module might keep track of what has changed since the last update, or might ask QB for a list of all customers and perform a delta on the Drupal data. It sends the update, storing it locally until QB returns a success.

I've not started anything yet, but my impression from the support forums is that there's a fair amout of work needed to maintain state between requests to check for errors and recover.

Just curious what others think of the Web Connector approach? To me, it has a wider audience, and it seems to offer the most control of data integrity.

I'd be very keen to collaborate on this.

Simon
http://www.ourfernie.com

Hey Simon, The Intuit

rszrama's picture

Hey Simon,

The Intuit support forums are a bear. I became the unofficial "go to guy" for PHP support for a while... I just haven't been around lately since I've been doing unrelated development work. I'm Ryan37 with the nice little wombat icon there. : P

In any event, my program works by dumping actions into an action queue. Our accountants liked to have as much control as possible, so we nixed automated processes and even automated queue population. I gave them pages to look at to see what could be imported and buttons to add customer, items, invoices, and POs to the queue for import. Then they used the WC to establish a connection and my SOAP server would just poll through the queue and start importing. Interestingly, I never added in a check to make sure there was only one person importing at a time. We haven't had any problems as of yet, but it seems like a possible future bug. I generated the tickets based on the timestamp or something and just kept a log in the DB of how many actions a session had performed, what its state was, and what the last error message was if it crapped out. Fairly simple, and it all fit quite nicely in the SOAP server. I think there's code in the QB forums, and if not I put some info up at http://www.vittitow.com/psu.

We'd be glad to have your help!

-Ryan

I moved the old stuff from

rszrama's picture

I moved the old stuff from the psu site to a section on Ubercart.org:

http://www.ubercart.org/quickbooks_integration

I think this would be an

deadlyromio's picture

I think this would be an excellent module. For the admin to pull up the payroll for employees and various information that is inputed by there sale and account departments, for vender reports, for customers to view previous orders.

I'd have to say that although I personally would not need this I'm sure many people that use the ecommerce module and run a online store or service based on drupal would find this a very welcome module.

Good luck on the development!

User Interface

Souvent22's picture

K, so I have the module pulling invoice and customer data in. And you can page through the data, and map QB customers to Drupal users. What i need now is UI clean up help. Meaning, when you click on a customer/invoice, theming that page out nice and pretty. Was wondering if anyone was interested in helping with this task?

I'm just searching around

nimzie-gdo's picture

I'm just searching around these forums and am wondering if you folks have unleashed this beast to the masses yet. I have a requirement to track paid downloads right in to QB...

I can possibly devote some time to some of this depending on where the project is as far as contributions...

Cheers,

Adam

Not yet

Souvent22's picture

Not yet, but lemme dig around ,and i'll shoot you my code base.

Same here

elemental's picture

Hey everyone,

I am also needing to integrate a Drupal site with the Client's Quickbooks system. I can definitely help out to get this integration complete. I am going to keep searching around a bit to see if there is anything in the works that is available. Otherwise please let me know where your at in this Project and I will jump in!

Thanks,
Erik

Is there any life left in

1.kenthomas@drupal.org's picture

Is there any life left in this project? We're in the same boat, looking for or to build a QuickBooks POS gateway, willing to split the work/cost with others if possible/practical.

There's a new project for

rszrama's picture

There's a new project for 6.x that shows promise... has code right now to form qbxml and interface w/ the QuickBooks Web Connector. It's not going to be a fix-all for you, but it might be a good foundation:

http://drupal.org/project/qb

I see no code in that link?

krisbfunk's picture

I see no code in that link?

^^ that's what CVS is for :)

1.kenthomas@drupal.org's picture

^^ that's what CVS is for :)

Any progression on the project?

kjv1611's picture

Has anyone made any progress towards being able to make it a module install as of yet? Or any other references for using Drupal with QuickBooks?

I know I'd love to be able to use such - possibly - to offer to small businesses where I live. I help one small business quite a bit when not working my full time job with computer-related tasks, and I may try to set up a Drupal-based website at some point in time to offer as well. Integration with Quickbooks might turn out to be a really big help in that area... or better yet if there were a way to just replace the quickbooks need altogether. But I suppose that'd be way too big an undertaking, as Quickbooks has SO many tools, apparently, at least it uses enough resources to be really loaded with features! ;0)

I've actually got two clients

jenlampton's picture

I've actually got two clients requesting QB/Intuit integration between Drupal and Commerce. If I end up writing a module (or hopefully - finding one that already exists) I'll post here as well.

Making Progress

flux423's picture

We've had some success integrating Commerce with QB Web Connect using a sandbox module with some custom code - https://www.drupal.org/sandbox/mattreba/2094911.

Had anyone else gone down this road?
What other development directions have been explored?

I wonder if integrating QB Desktop and commerce is even best practice anymore with QB's Cloud and Inuit's merchant services.

Any thoughts are appreciated.

Enterprise

Group organizers

Group notifications

This group offers an RSS feed. Or subscribe to these personalized, sitewide feeds: