Quickbooks DEV

[Peper]

Any one here have implemented the dev kit available at consolibyte.com/quickbooks-open-source ?

If so, please I need help on this.

 

[BrockleyJohn]

Yes, that kit and Intuit's Quickbooks Web Connector were the basis of an implementation I did.

 

The key issue is that the dev kit is based around a queue of requests for the qbwc to do things with QB. When an order is taken in osC, there's a whole sequence of things that need to happen to get that through to QB.

 

I wrote a whole set of middleware to sit between this and the normal osC store, to manage the communication between the two, based around a (business) transaction queue. The requirements we implemented were complex, because the client has a bricks & mortar store as well as osC. QB was used for customers, stock and sales in the shop, and it was necessary to keep osC in sync. Also, both databases already existed before the integration, so we had to be able to match up existing data rather than just squirt it across one way or the other. As implemented for them the interface supported:

- equivalence of products online and in QB (based on a hidden mapping and not matching names, product models etc)

- automated stock updates (product quantities) at configurable intervals (stock transactions)

- feeding online orders back to QB (order transactions):

-- creating new customers or adding to existing ones, with all the correct address information from the online order

-- creating a sales receipt for each order when properly confirmed (ie. at a suitable payment status like Paypal [Verified] because some methods involved a manual process and status change)

--- each order product represented by the right QB item on the sales receipt

--- a QB item on the sales receipt for the delivery

--- a corresponding QB payment method for the online one

The QB sales receipt was the VAT receipt and when printed and folded in the right way, also the address label.

There's a bit more detail of this in the admin overview, here: http://sewebsites.net/download/osC-QB-interface-admin-user-guide-generic-1.0.pdf

 

I guess if you use master products, you're probably not using QB for stock management as I don't think it does that (unless you use groups in some complicated way, perhaps). And SPPC probably rules that out too

 

I have another client thinking of a much simpler implementation, just using QB as accounting software:

- needs a sales receipt for each online order

-- could be a generic online customer, with the name and address changing each time

-- needs a line for each order product and the delivery, but again could be a generic online sale product with the correct description and price each time

-- would need the correct payment method but in fact they only use paypal

 

For something as straightforward as this, you might get away with putting a hook into Paypal processing to queue a Create Sales Receipt request. However, if you get any errors it'll mean hacking around in the database to requeue the request.

 

How far have you got with the kit and what kind of help do you need?

[Peper]

Hi John

 

That is exactly what I need

For starters the web connector is connecting, querying the database and it can see ok e.g 2 new orders 2 new customers

 

Now i must tell the code what further info needs to be fetched from the osc database like order details, customer details and match/update store items with QB

I have now idea how to go from here

 

Also do you know if this will this work on Quick Books 2015?

[BrockleyJohn]

I don't know for sure about QB2015, but I expect so. We ran it on QB2008 express, Pro 2011 and Pro 2012 (all UK versions). IIRC there were some minor differences in supported XML at one version but nothing serious.

 

 

match/update store items with QB

 

It's what you need to do here that determines the complexity of your integration. Do you have/need accurate stock figures in QB or only online? Or can you, like scenario 2, get away with telling QB "we sold some stuff worth this much"?

 

The difference between the two is around 5,000 lines of code, an extra 4-page section in the admin panel and a whole lot of data-cleansing reporting built into admin categories to make manageable the task of keeping the two suitably in step!

[Peper]

The items name, pricing would need updating to QB (brick and mortar scenario)

Item stock tracking no

 

 "we sold some stuff worth this much"

For every order that is updated to delivered we need to create sales receipt per customer order - once a month run this query

Shipping/discounts created as item 

[BrockleyJohn]

Sounds like you're not tracking stock in QB, that makes life a lot easier. If you don't track customers either, you might be able to do without the transaction management and just plug into the framework.

In other words, in QB there's a customer called 'online customer' and for every order you create a sales receipt for them.

 

You update your payment module(s) so that when the order's confirmed it creates an entry in the QB queue table to 'create sales receipt request'. Or if you only need to do this every so often, you can make something that creates a queue entry for each order since last time.

 

The rest is in the soap server. When you create the sales receipt, you still feed it the name and address of the osc order

- for each line of the osc order, you create a line on the sales receipt using item 'online item' but as description the stock tracking no and the item name, and of course the price

- you do the same for the shipping & discounts unless you need them separately for your accounting

 

This way you only need a few different things, which can maybe be hard-coded or at worst put in a little lookup table.

 

The only complication is you need the QB 'list-id's (internal identifiers) for the customer and item(s). This isn't accessible within QB, you'll have to create them in QB first and then run a query through the interface to get their details.

[Peper]

I will need to track customers, products items and price/special price, orders

 

I found from Zen cart sync addon  - zen-cart.com/downloads.php?do=file&id=808

 

Converting this to Osc, but then again will it work as described

I have tried all the QB addons already for osc and all of them fail

[BrockleyJohn]

Reading between the lines - I couldn't find it explicitly - I think this zen addon assumes that osC is master for the products/items and creates corresponding ones in QB. If that's what you want to happen, you might be ok with it.

 

What version of osc are you running?

[Peper]

2.2RCA2 php upgraded functions and so(php5.4 server) 

[BrockleyJohn]

2.2RCA2 php upgraded functions and so(php5.4 server) 

 

OK so even if I end up doing something for 2.3.x it wouldn't help. Shame what I already have is QB master - it assumes all the osc products already exist in QB and won't send an order across unless all the products on it have already been matched to QB items.

[Peper]

I see. 

That is why the products needs to be imported to Qb and match them

[BrockleyJohn]

Yes, the starting point was the need to link an existing QB database to an existing osC store, so the most efficient way of doing that was to pull in all the QB items and build a user interface that made matching them to osC products feasible. 

 

QB was the stock master - purchases added to the inventory, b&m shop sales subtracted from it. Before the integration, online sales were added manually to QB, and stock quantities maintained manually online. Afterwards both of these were automated.

 

The alternative to matching would have been to throw away one of the existing databases and recreate it automatically from the other... with QB as the stock master, it would have meant having to recreate the online catalog and rewriting all the product descriptions etc. As soon as you start to look at reusing the existing data, you're effectively going into a process of matching the two datastores - saying this item in QB corresponds to this product in osC.

 

Basing it around matching also made roll-out manageable. Once the interface was turned on, matching could be prioritised for things whose stock levels changed, whether purchased, sold in store or sold online.