Quickbook Sync

Thanks for the prompt response the_ancient. Don't know why I had authentication issues before, but my changes seem to work now.

I've been working a little on this mod. I fixed the problem with international orders: It seems that QBXML doesn't work so well with anything but simple ASCII characters. Hence, letters with accents and other characters from different languages were causing problems. Took several hours finding and fixing the problem, but I found a decent fix for it. In addition, I've added the payment method to sales receipts.

Part of the learning curve was figuring out how QBXML works, as I've never used it before. For those who are new to QBXML, check out this link: http://developer.intuit.com/qbsdk-current/Common/newOSR/index.html -- it's a lifesaver.

Next, I'm going to implement multiple tax rates and automatic inventory updates (instead of just one time when you first run it). I'd be happy to upload my changes to zen-cart.com's free software once I finish, but I don't want to step on any toes. Let me know if that is OK, the_ancient.

BTW - kudos to you the_ancient! This mod makes handling Quickbooks so much easier. You just set it and forget it...Ron Popeil would be proud!

srturner47:

Next, I'm going to implement multiple tax rates and automatic inventory updates (instead of just one time when you first run it). I'd be happy to upload my changes to zen-cart.com's free software once I finish, but I don't want to step on any toes. Let me know if that is OK, the_ancient.

BTW - kudos to you the_ancient! This mod makes handling Quickbooks so much easier. You just set it and forget it...Ron Popeil would be proud!

The only thing I ask is that I get credit for the code I wrote,

Having someone build on our Code is the main purpose IMO of OpenSource development, I want to add all of the items you plan to, unfortunately they are not needed for my Employer and I have zero time during my off hours (when I get off Hours) to advance this so it get no feature additions.

If you want to improve it:clap::clap::clap::clap::clap::clap:

Of course I'll be giving you full credit the ancient. This is exactly why I prefer to work with open source software. :)

My little changes turned into some big changes. I'll list what I've done, and what I need to do next before releasing this, but I had one question first -- hopefully someone (including the_ancient) can help me understand this. As it stands, check/money orders are sent to QB as a sales order while other payments are sent as sales receipts. I'd never heard of a sales order before, so I had to look it up. Apparently they are a way to store purchases from a customer that can later be combined into an invoice. This makes sense if you have customers who make tons of purchases regularly, as you don't have to bill them individually all the time.

Here's my issue: for the average Zen Cart store, wouldn't it make more sense to import Check/Money Orders into QB as an invoice? Two reasons: 1) Quickbooks Pro, the most popular version of QB, does not support Sales Orders. You need the more expensive Premier version for this. Hence, this isn't going to work for many people. 2) Most Zen Cart stores (although obviously not all) are not going to have customers visiting frequently that need to be invoiced together for multiple orders.

I suppose the best solution would be to have invoices, sales receipts, and sales orders available as an option that you can choose. However, its going to take a lot of work, as invoices are currently not coded and sales orders are no longer functioning properly as I have significantly changed the way Sales Receipts work. Is it worth the effort? Updating the sales orders code would be easier than adding invoices, but it seems to me that invoices would be more useful for the average user. All in all, sales receipts are obviously the most important. What do people think? My inclination is to just comment out the sales order code and send all orders as sales receipts. I hate to reduce the functionality as I update this mod, but I question whether its worth the effort to implement these features. Any input would be appreciated.

OK, so here's the changes I've made so far:

-Updated tables definitions with DB_PREFIX so that you don't have to anymore.
-Fixed issues that could cause errors including: truncating all QBXML fields to maximum values, replacing all characters with accents, ^, ~, etc... above them with their standard ASCII character without the accent, ^, ~, etc..., automatically removing all other characters above and beyond the 128 standard ASCII characters and replacing them with a ? (QB Web Connector doesn't like these characters!)
-Implemented Payment Methods
-Created a matching system that allows multiple shipping types, sales taxes, payment methods, and discounts/fees. These are now stored in the database, and I've created an Admin page that allows you to do your matching. Default is to pass through the Zen Cart values to Quickbooks. All other values need to be matched.
-Implemented 2 advanced matching features: 1) * in Zen Cart value will indicated that all values NOT yet matched should follow this rule. 2) * in Quickbooks value will indicate that this should be passed through from Zen Cart.
-Setup matching such that values need only CONTAIN the Zen Cart value to match. They do not need to match exactly. This will make it easier to match things, as many values are not static. For example, if your shipping is "UPS Ground (xxx pounds)", the xxx will change everytime. But, using "UPS Ground" in your Zen Cart matching value will always work for this because it contains "UPS Ground". This also will fix many problems with html codes appearing in matching values.
-Changed so that products will be taxable or nontaxable on sales receipt based on whether or not they were taxed on this particular order. Previously, they just took the defaults in Quickbooks, which caused problems in some cases.

The major work is done. Here are a couple things I'd still like to implement:
-sales orders and invoices...is it worth it? see above
-ability to add products as non-inventory part
-move configuration values other than matching to zen cart table so you can configure from admin
-ability to choose whether or not to add products from zen cart after the first time you run QB Web Connector.
-create an option for QB Sync to use a standard tax name for items that weren't taxed in Zen Cart
-add option to override whether or not products were taxed or not and simply tax or not tax all items
(the reason I want to add the last two is that this will help people that have assigned a 0% tax so that they can track their taxable vs. non-taxable orders -- very helpful when sales tax is due -- I highly recommend it!)
-create files that will make links to qbAdmin.php and the new qbMatch.php accessible from the admin menu -- no more typing the url!
-Create ability to limit log entries to a certain number. Oldest get deleted first.

Here are a few things that would be great additions to this, but I don't plan to do:
-inventory tracking sync
-handle products with attributes more accurately

Nevermind with my question. I just went ahead and fixed Sales Orders to work right. I can't test it though, as I don't have the premiere edition of Quickbooks, so when I release it, users will have to let me know if there are problems. Still haven't decided whether on not it is worth the effort to add invoices.

Hard at work finishing this. A few more things to do, but it should be done soon. This is an enjoyable project -- I'm having a good time coding this! :) Although I must admit it is distracting me from my other work.... Oh well.

srturner47:

Of course I'll be giving you full credit the ancient. This is exactly why I prefer to work with open source software. :)

My little changes turned into some big changes. I'll list what I've done, and what I need to do next before releasing this, but I had one question first -- hopefully someone (including the_ancient) can help me understand this. As it stands, check/money orders are sent to QB as a sales order while other payments are sent as sales receipts. I'd never heard of a sales order before, so I had to look it up. Apparently they are a way to store purchases from a customer that can later be combined into an invoice. This makes sense if you have customers who make tons of purchases regularly, as you don't have to bill them individually all the time.

Here's my issue: for the average Zen Cart store, wouldn't it make more sense to import Check/Money Orders into QB as an invoice? Two reasons: 1) Quickbooks Pro, the most popular version of QB, does not support Sales Orders. You need the more expensive Premier version for this. Hence, this isn't going to work for many people. 2) Most Zen Cart stores (although obviously not all) are not going to have customers visiting frequently that need to be invoiced together for multiple orders.

I was not aware QB Pro did not have Sales Orders, We only use Premier and infact we may be switching to Enterprise...

So invoices would be the only logical option, However the way our accounting wants to work it they wanted the sales order not the invoice, why I dont know they are the accountants, Infact they originally wanted me to Create a Sales Order, Invoice and Payment Record for all orders, but I convinced them to let me do a sales receipt for CC Orders:clap:

I suppose the best solution would be to have invoices, sales receipts, and sales orders available as an option that you can choose. However, its going to take a lot of work, as invoices are currently not coded and sales orders are no longer functioning properly as I have significantly changed the way Sales Receipts work. Is it worth the effort? Updating the sales orders code would be easier than adding invoices, but it seems to me that invoices would be more useful for the average user. All in all, sales receipts are obviously the most important. What do people think? My inclination is to just comment out the sales order code and send all orders as sales receipts. I hate to reduce the functionality as I update this mod, but I question whether its worth the effort to implement these features. Any input would be appreciated.

I am willing to bet 90+% of all orders are CC so I doubt that would be a major Issues,

OK, so here's the changes I've made so far:

-Updated tables definitions with DB_PREFIX so that you don't have to anymore.
-Fixed issues that could cause errors including: truncating all QBXML fields to maximum values, replacing all characters with accents, ^, ~, etc... above them with their standard ASCII character without the accent, ^, ~, etc..., automatically removing all other characters above and beyond the 128 standard ASCII characters and replacing them with a ? (QB Web Connector doesn't like these characters!)
-Implemented Payment Methods
-Created a matching system that allows multiple shipping types, sales taxes, payment methods, and discounts/fees. These are now stored in the database, and I've created an Admin page that allows you to do your matching. Default is to pass through the Zen Cart values to Quickbooks. All other values need to be matched.
-Implemented 2 advanced matching features: 1) * in Zen Cart value will indicated that all values NOT yet matched should follow this rule. 2) * in Quickbooks value will indicate that this should be passed through from Zen Cart.
-Setup matching such that values need only CONTAIN the Zen Cart value to match. They do not need to match exactly. This will make it easier to match things, as many values are not static. For example, if your shipping is "UPS Ground (xxx pounds)", the xxx will change everytime. But, using "UPS Ground" in your Zen Cart matching value will always work for this because it contains "UPS Ground". This also will fix many problems with html codes appearing in matching values.
-Changed so that products will be taxable or nontaxable on sales receipt based on whether or not they were taxed on this particular order. Previously, they just took the defaults in Quickbooks, which caused problems in some cases.

The major work is done. Here are a couple things I'd still like to implement:
-sales orders and invoices...is it worth it? see above
-ability to add products as non-inventory part
-move configuration values other than matching to zen cart table so you can configure from admin
-ability to choose whether or not to add products from zen cart after the first time you run QB Web Connector.
-create an option for QB Sync to use a standard tax name for items that weren't taxed in Zen Cart
-add option to override whether or not products were taxed or not and simply tax or not tax all items
(the reason I want to add the last two is that this will help people that have assigned a 0% tax so that they can track their taxable vs. non-taxable orders -- very helpful when sales tax is due -- I highly recommend it!)
-create files that will make links to qbAdmin.php and the new qbMatch.php accessible from the admin menu -- no more typing the url!
-Create ability to limit log entries to a certain number. Oldest get deleted first.

Here are a few things that would be great additions to this, but I don't plan to do:
-inventory tracking sync
-handle products with attributes more accurately

Looks like a great list and I look forward to seeing them :clap::clap:

Finally done -- this ended up being a bigger project than I expected! Here is the final changelog:

V2.0 CHANGELOG

-Updated tables definitions with DB_PREFIX so that you don't have to anymore.
-Fixed issues that could cause errors including: truncating all QBXML fields to maximum values, replacing all characters with accents, ^, ~, etc... above them with their standard ASCII character without the accent, ^, ~, etc..., automatically removing all other characters above and beyond the 128 standard ASCII characters and replacing them with a ? (QB Web Connector doesn't like these characters!)
-Implemented Payment Methods
-Created a matching system that allows multiple shipping types, sales taxes, payment methods, and discounts/fees. These are now stored in the database, and I've created an Admin page that allows you to do your matching. Default is to pass through the Zen Cart values to Quickbooks. All other values need to be matched.
-Implemented 2 advanced matching features: 1) * in Zen Cart value will indicated that all values NOT yet matched should follow this rule. 2) * in Quickbooks value will indicate that this should be passed through from Zen Cart.
-Setup matching such that values need only CONTAIN the Zen Cart value to match. They do not need to match exactly. This will make it easier to match things, as many values are not static. For example, if your shipping is "UPS Ground (xxx pounds)", the xxx will change everytime. But, using "UPS Ground" in your Zen Cart matching value will always work for this because it contains "UPS Ground". This also will fix many problems with html codes appearing in matching values.
-Changed so that products will be taxable or nontaxable on sales receipt based on whether or not they were taxed on this particular order. Previously, they just took the defaults in Quickbooks, which caused problems in some cases. Also added ability to force tax or non-tax.
-Added ability to add products as tax or non-tax.
-invoices added
-Choose whether or not invoices, sales receipts, or sales orders are used based on payment name.
-ability to add products as non-inventory part
-move configuration values other than matching to zen cart table so you can configure from admin
-ability to choose whether or not to add products from zen cart after the first time you run QB Web Connector.
-create an option for QB Sync to use a standard tax name for items that weren't taxed in Zen Cart
-add option to override whether or not products were taxed or not and simply tax or not tax all items
(the reason I added the last two is that this will help people that have assigned a 0% tax so that they can track their taxable vs. non-taxable orders -- very helpful when sales tax is due -- I highly recommend it!)
-create files that will make links to qbAdmin.php and the new qbMatch.php accessible from the admin menu -- no more typing the url!
-Create ability to limit log entries to a certain number. Oldest get deleted first.
-Send error emails to you when there is a problem.
-Added customizable order number format.

• Created ability to ignore Preferred Vendor which allows products to be added without setting up manufacturers as Vendors.
  -Added ability to set orders up as “to be emailed” in addition to “to be printed”.
  -Added ability to set terms for Sales Orders/Invoices as an alternative to payment methods for Sales Receipts.

Documentation has been updated. I'll be doing some final testing over the next couple of days and then I'll release this. Now, off to bed! :smile:

For final testing, I decided to create a new Quickbooks file and see if I could import my entire store (a huge store -- 1000s of products, customers, and orders). Well, when I installed the exampleQWC file I got the following error, as discussed briefly earlier in this post:

I am getting this error
20090303.18:14:07 UTC : QBWebConnector.WebServiceManager.ReadQWC(QWCReader QWC) : QBWC1048: QuickBooks Web Connector could not verify the web application server certificate.

QBWC1051: The new application was not added

any help with this?

thanks

After a ton of testing, I found that this error happened sometimes when installing exampleQWC, but not all the time. Hence, if you were persistent, it would work. I reverted to the_ancients version and had similar results, but I found that this error happened less frequently. This NEVER causes a problem when updating, it only happens when installing exampleQWC.

Strange--I could only think of one possibility--my changes should make the script a little slower as I added a lot of SQL requests and a ton of code. Hence, it would make sense if this happened when the script doesn't return a response fast enough to the Web Connector when installing a new QWC app. Does this sound like a possibility the_ancient? Any other ideas about what might be causing this?

I'm stumped on this issue.

UPDATE: Hmmm.. Well I found this thread:

https://idnforums.intuit.com/messageview.aspx?catid=56&threadid=7784&enterthread=y

Looks like if your script sends an error, you might have an issue. I tried running qbServer.php in a browser, and sure enough I got a 503 error. Tried original and with my changes, same 503 error.

OK--but I'm still not sure how to get rid of the 503 error. :(

The 503 error suggests that the server is overloaded but still available (at least available enough to respond with a 503).

This could be because of maintenance work on the server and it might therefore clear itself, or could it be that you are doing the overloading? Is your revised version sending lots of messages to the server for processing in quick succession, intentionally or otherwise?

Yep, that's why I investigated with my server first. After submitting a support ticket, they tested the script and found that there was an error in the script causing PHP to crash. Perhaps its because I was running the script in a browser as opposed to using QWC, I really don't know. Either way, the issue with install the QWC file remains. It can be worked around by being persistent, and it only happens when installing the mod, but it is still a problem.

a> The 503 Error is not caused by running it in a browser, you should see a blank page.

Are you sure it is a 503? not a 500? 503 should have nothing to do with php at all that is a server problem.

Normally a 500 is still a server problem, i.e a module not loading, htaccess wrong, etc etc etc.

b> If you really want to do testing check out
http://www.soapui.org/

Dunno why, but everything works fine with Web Connector for me (except for installing to QWC), but in a browser I always get 503. My server did forward an error to me -- I'd rather not post the whole thing because I don't know what it all means, but the gist of the error is:

--- SIGSEGV (Segmentation fault) @ 0 (0) ---
+++ killed by SIGSEGV (core dumped) +++

they also sent me (some stuff ommited that I don't know what it means):

#1 (OMMITED) in zend_objects_destroy_object (object=(OMITTED), handle=(OMMITED)) at /home/cpeasyapache/src/php-5.2.9/Zend/zend_objects.c:78
#2 0x0000000000000001 in ?? ()
#3 0x0000000000000000 in ?? ()
(gdb) quit

My server runs litespeed instead of apache -- I wonder if this has something to do with this... I thought litespeed was supposed to be a drop-in replacement for apache, but who knows...

As for my progress with this, I did finish testing and everything is working great after ironing out a few more bugs, but then I decided to add another feature. It should be a few more days. I realized that Quickbooks Sync would be even better if it could actually sync instead of just exporting one-time. This was definitely tricky to implement, but it seems to be going well so far. I've implemented this with items (both non-inventory and inventory). It won't let me delete items (and frankly, this is probably not desirable, as it would fail if the item was ever on an invoice, sales order, or sales receipt), so I made items inactive in qb when deleted. I also set it up so that product model, name, and price could be updated at any time in ZC, and this would automatically sync with QB. One tricky problem I realized was that an inactive item could end up with the same model number as an active item, which obviously wouldn't work. But, I think I found a work-around for this by allowing Quickbooks Sync to "take-over" the inactive item that was deleted in zen cart, and convert it to an active item.

Now I need to implement the same functionality with customers. Sales Receipts, Sales Orders, and Invoices should be a little easier as Zen Cart doesn't allow modification, so all I'll have to implement is deletion of these. Of course, I'll make all of these optional for those who just want to export one-time.

this may be a stupid question (sorry:blush:) but will this mod transfer enough info to figure cogs in quickbooks??? if not, can someone share what mod to use for that? I am at a loss and really need to find something that will work.
thanks!

Not quite sure what you are looking for, but Quickbooks Sync will add items, assigning their COGS account and then use a standard markup to determine the purchase price for your zen cart items.

Koi Supplies UK:

Hi you can add this xml line to the QWC file
<CertURL>https://yoururl.com/</CertURL>

I was getting this error also and this worked, thanks, you saved my hair! lol

I am so close I can taste it, but when I try to use the web connect, I get Authentication Failed with this description:
Version:
Not provided by service
Message:
Authentication failed
Description:
QBWC1012: Authentication failed due to following error message.
Client found response content type of 'text/html; charset=iso-8859-1', but expected 'text/xml'.
The request failed with an empty response. See QWCLog for more details. Remember to turn logging on.

helpme:

I am so close I can taste it, but when I try to use the web connect, I get Authentication Failed with this description:
Version:
Not provided by service
Message:
Authentication failed
Description:
QBWC1012: Authentication failed due to following error message.
Client found response content type of 'text/html; charset=iso-8859-1', but expected 'text/xml'.
The request failed with an empty response. See QWCLog for more details. Remember to turn logging on.

Since it is returning HTML, it is outputting an error of some kind, you will have to look at the logs to find out more

Just wonder if anyone can help - would dearly like to get this working. Am getting the following error in the QWCLog:

20090818.17:28:46 UTC : QBWebConnector.CompanyFileLock.unsetLock() : Company file has been unlocked for ownerID = <{CD8D6B56-9AF2-11DD-ACC9-5D4756D89593}>
20090818.17:28:46 UTC : QBWebConnector.SOAPWebService.update() : Company file has been unlocked.
20090818.17:28:46 UTC : QBWebConnector.SOAPWebService.update() : Ending session to QuickBooks.
20090818.17:28:46 UTC : QBWebConnector.SOAPWebService.DisconnectFromQB() : Session ended and connection closed
20090818.17:28:46 UTC : QBWebConnector.SOAPWebService.do_closeConnection() : *** Calling closeConnection() with following parameter:<wcTicket="e79ed057f064cd7e4bb0748eebd575a5">
20090818.17:28:46 UTC : QBWebConnector.SOAPWebService.do_closeConnection() : QBWC1044: closeConnection failed
Error message: The server committed a protocol violation. Section=ResponseStatusLine
More info:
StackTrace = at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request)
at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at QBWebConnector.localhost.WCWebServiceDoc.closeConnection(String ticket)
at QBWebConnector.localhost.WCWebService.closeConnection(String ticket)
at QBWebConnector.SOAPWebService.closeConnection(String wcTicket)
at QBWebConnector.WebService.do_closeConnection(String wcTicket, Boolean success)

Have looked everywhere for some kind of pointer to a solution but no luck

TPHoare - That's a tricky error... You might try the developer forums at intuit.

Work continues on the next version. This has become a much larger project than I ever imagined. QbClass.php has ballooned from a little over 500 lines of code to a little over 2000 lines of code. I've finished the continuing sync functions (where orders, customers, and items are updated and deleted whenever you do so on Quickbooks). It's pretty incredible! It really does sync now, not just export. Anyway, I realized that only people who started with a new quickbooks file would reap the benefit from this, as the information needed for continuing sync to work is entered into the database when things are added. Hence, if it wasn't added from QBSync, it wouldn't work with Continuing Sync. That would limit the usefulness of this for many people who didn't want to start over with Quickbooks, so now I am creating a matching system that will find the info from Quickbooks and enter it into the database when it tries to add a product, order, or customer and it already exists in Quickbooks. The continuing sync functions were much more complicated to implement than I had previously imagined. I had to redesign many elements to make it work right all the time including:

• A second, high priority que. This allows QBSync to do additional queries. This was important as several functions have two or three queries in a row now.
  -Several new tables in the database.
  -Adding customers and products to orders using ListID (a unique identifier in Quickbooks that cannot be changed by the user) instead of by name. Actually, pretty much everything is done by ListID now. For those that do not use Continuing Sync, this will be optional if you prefer to identify by Name -- but using ListID has many other benefits, so I would suggest using it.

I've added several new features. Here are some notable ones:
-option to add customer comments to invoice, sales orders, or sales receipt
-option to add attributes from products to invoice, sales orders, or sales receipt
-Fixed an issue with the state name not always importing correctly for customers.
-Continuing Sync is Done! :) This was over 700 lines of code alone!
-Option to view only errors on Log Page.
-Better email error reporting. One email tells everything about the errors from one session instead of one email per error.
-Fixed an issue with admin log page that would cause the table to be very wide.

I'm really hoping to finish soon. It always seems like I reach a finishing point and then start seriously testing it, and I realize it either needs a new feature or has a major problem that needs fixing. I'm definitely close to the finish line though.

really stupid question, but where are the logs, I can't find them....boy this mod is bring the stupidity out! lol