Jump to content
mpiscopo

EZ QuickBooks Integration Contribution

Recommended Posts

I installed this contribution and everything works great! Thank you SO much for all your hard work!

 

I do have a question from a client:

 

Do you know if you can assign all sales product items to 46000 merchandise sales and shipping to 47000 shipping in Quickbooks?

 

Thanks!

 

Can anyone help with this? My client would like his sales assigned to 46000 and shipping assigned to 47000 in Quickbooks. Is there a way to do this?

Share this post


Link to post
Share on other sites

Hello,

 

I've just downloaded this contribution and I'm trying to import my .iff file to oscommerce through "update products."

 

However, when I try to import the file, it's giving me this: Error: Destination not writeable.

 

I'm new to osCommerce and Quickbooks, so any help would be GREATLY appreciated.

 

Thank you!

Edited by cmkelly29

Share this post


Link to post
Share on other sites

Actually, I'm ok there now.

 

I've having 2 different problems now, if anyone would be so kind as to help out.

 

Once I've imported my item.iff file, I get this:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'H X 20'W 2MIL BAG'' at line 1

 

select products_id, products_model, products_quantity from products where products_model ='24'H X 20'W 2MIL BAG'

 

and when I try to export from oscommerce I get this:

1054 - Unknown column 'is_exported' in 'where clause'

 

SELECT orders_id FROM orders where is_exported = 0

 

[TEP STOP]

 

Any help would be appreciated. Thank you.

Share this post


Link to post
Share on other sites
ok so i copied the code
		  function simpleBox($contents){
		return $this->tableBlock($contents);		
		}

 

into the quickbooks_custom_fields.php file and now it says:

 

Fatal error: Cannot redeclare simplebox() (previously declared in catalog/admin/includes/classes/box.php:60) in catalog/admin/quickbooks_custom_fields.php on line 51

 

OK so it's already declared but it's not defined?

 

or it's a function and not a method?

 

frick i need this thing to work lol!

 

anybody?

 

It's a method of the box class

Share this post


Link to post
Share on other sites
Actually, I'm ok there now.

 

I've having 2 different problems now, if anyone would be so kind as to help out.

 

Once I've imported my item.iff file, I get this:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'H X 20'W 2MIL BAG'' at line 1

 

select products_id, products_model, products_quantity from products where products_model ='24'H X 20'W 2MIL BAG'

 

and when I try to export from oscommerce I get this:

1054 - Unknown column 'is_exported' in 'where clause'

 

SELECT orders_id FROM orders where is_exported = 0

 

[TEP STOP]

 

Any help would be appreciated. Thank you.

 

Hello,

For the second issue.

In the package addon, there's a db sql script.

In there it contains an alter table command that adds the column "is_exported" to the orders table.

Make sure that column is in table orders.If not make a review on the script.

 

Kind Regards,

Bogdan

Share this post


Link to post
Share on other sites
Hello,

For the second issue.

In the package addon, there's a db sql script.

In there it contains an alter table command that adds the column "is_exported" to the orders table.

Make sure that column is in table orders.If not make a review on the script.

 

Kind Regards,

Bogdan

 

Bog, thanks for the help. I actually managed to fix the problem. I removed and reloaded the sql and voila!

Share this post


Link to post
Share on other sites
So, after setting up the whole store with the main purpose of being able to import orders into quickbooks, the client still can't import her orders into quickbooks.

 

All taxable orders can't just disappear from the import file.

 

Does anybody have a fix for this?

 

In admin/quickbooks_export_orders.php in the set sales tax details section - line 212

 

replace:

 

$transaction_detail_amount = 0;

 

with

 

$transaction_detail_amount = '-' . $order->info['sales_tax'];

Share this post


Link to post
Share on other sites
In admin/quickbooks_export_orders.php in the set sales tax details section - line 212

 

replace:

 

$transaction_detail_amount = 0;

 

with

 

$transaction_detail_amount = '-' . $order->info['sales_tax'];

 

additional changes needed for last post to work

 

in admin/includes/classes/order.php

 

find

 

$order_total_query = tep_db_query("select text from " . TABLE_ORDERS_TOTAL . " where orders_id = '" . (int)$order_id . "' and class = 'ot_total'");

$order_total = tep_db_fetch_array($order_total_query);

 

add below:

 

$sales_tax_query = tep_db_query("select title,value from " . TABLE_ORDERS_TOTAL . " where orders_id = '" . (int)$order_id . "' and class = 'ot_tax'");

$sales_tax = tep_db_fetch_array($sales_tax_query);

 

in the $this->info array

 

add

 

'sales_tax' => strip_tags($sales_tax['value']),

Share this post


Link to post
Share on other sites
It's a method of the box class

 

ok great, but is it not defined by that code already? how do i define it so it works? it looks ok to me so i'm confused as to how it's working for others but not on the 2 sites i've got.


☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆

Share this post


Link to post
Share on other sites
In admin/quickbooks_export_orders.php in the set sales tax details section - line 212

 

replace:

 

$transaction_detail_amount = 0;

 

with

 

$transaction_detail_amount = '-' . $order->info['sales_tax'];

 

 

ok you totally fixed it!!!

 

thank you so much.

 

now to fix the method thing and i'm done!


☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆

Share this post


Link to post
Share on other sites
additional changes needed for last post to work

 

in admin/includes/classes/order.php

 

find

 

$order_total_query = tep_db_query("select text from " . TABLE_ORDERS_TOTAL . " where orders_id = '" . (int)$order_id . "' and class = 'ot_total'");

$order_total = tep_db_fetch_array($order_total_query);

 

add below:

 

$sales_tax_query = tep_db_query("select title,value from " . TABLE_ORDERS_TOTAL . " where orders_id = '" . (int)$order_id . "' and class = 'ot_tax'");

$sales_tax = tep_db_fetch_array($sales_tax_query);

 

in the $this->info array

 

add

 

'sales_tax' => strip_tags($sales_tax['value']),

 

ok wait i'm having a strange problem. this worked for one store just fine, but the other store is heavily modded and is having tax problems. she says the orders with taxes still will not export into quickbooks, that it's giving her the same error. I exported an order myself and it has a blank for the sales tax. also, taxes aren't showing up when you view orders in the admin panel. they're there, and they get charged, but it's not showing up anywhere. see below:

 

ORDER PAGE IN ADMIN PANEL:

 

ordertotal.gif

 

ORDER EXPORTED INTO IIF (used excel to view):

 

orderexport.gif


☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆

Share this post


Link to post
Share on other sites
...

Another question: Is there a way to re-export all of the orders? I am looking for a counter, or something, that I can reset that will allow me to try testing the export function on a full scale rather than one new order at a time (if that makes sense).

 

I had written a reset page for one of my clients and I decided to install it on another store, make some instructions and zip it up.

 

I hope I didn't do a bad thing by uploading it to the main contribution page, but I thought people might be able to use it.

 

QUICKBOOKS RESET ORDERS ADD-ON


☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆

Share this post


Link to post
Share on other sites

I'm having trouble on the order.php edit.

Can someone please clarify theses steps:

iii) admin/includes/classes/order.php

 

In function "function query($order_id) {" modify the line:

 

$order_query = tep_db_query("select customers_name, customers_company, customers_street_address, customers_suburb, customers_city, customers_postcode, customers_state, customers_country, o.customers_telephone, o.customers_email_address, customers_address_format_id, delivery_name, delivery_company, delivery_street_address, delivery_suburb, delivery_city, delivery_postcode, delivery_state, delivery_country, delivery_address_format_id, billing_name, billing_company, billing_street_address, billing_suburb, billing_city, billing_postcode, billing_state, billing_country, billing_address_format_id, payment_method, cc_type, cc_owner, cc_number, cc_expires, currency, currency_value, date_purchased, orders_status, last_modified from " . TABLE_ORDERS . " o left join " . TABLE_CUSTOMERS . " using(customers_id) where orders_id = '" . (int)$order_id . "'");

 

add

 

"customers_id, customers_fax," in fields retrieval list.

 

 

In the same function, under line:

 

$totals_query = tep_db_query("select title, text from " . TABLE_ORDERS_TOTAL . " where orders_id = '" . (int)$order_id . "' order by sort_order");

 

Add the following lines:

 

$order_total_query = tep_db_query("select text from " . TABLE_ORDERS_TOTAL . " where orders_id = '" . (int)$order_id . "' and class = 'ot_total'");

$order_total = tep_db_fetch_array($order_total_query);

 

$shipping_method_query = tep_db_query("select title,value from " . TABLE_ORDERS_TOTAL . " where orders_id = '" . (int)$order_id . "' and class = 'ot_shipping'");

$shipping_method = tep_db_fetch_array($shipping_method_query);

 

In the same function, in the $this->info array,

"

$this->info = array('currency' => $order['currency'],

'currency_value' => $order['currency_value'],

...................................

"

add some lines, to look like this:

"

$this->info = array('order_id' => $order_id,

'currency' => $order['currency'],

'currency_value' => $order['currency_value'],

......................................................

'total' => strip_tags($order_total['text']),

'shipping_method' => ((substr($shipping_method['title'], -1) == ':') ? substr(strip_tags($shipping_method['title']), 0, -1) : strip_tags($shipping_method['title'])),

'shipping_cost' => strip_tags($shipping_method['value']));

"

 

In the same function, in the $this->customer array,

 

"

$this->customer = array('name' => $order['customers_name'],

'company' => $order['customers_company'],

......................................................

"

 

add some lines to look like this:

 

$this->customer = array('id' => $order['customers_id'],

'name' => $order['customers_name'],

'company' => $order['customers_company'],

......................................................

'fax' => $order['customers_fax'])

 

 

/////warehouse location as custom fields

In the same function, see the following line:

 

$orders_products_query = tep_db_query("select orders_products_id, products_name, products_model, products_price, products_tax, products_quantity, final_price from " . TABLE_ORDERS_PRODUCTS . " where orders_id = '" . (int)$order_id . "'");

 

add the following field to it:

 

"products_warehouse_location " in field list

 

 

In the same function, in the $this->products[$i] array, add some lines, so from:

"

while ($orders_products = tep_db_fetch_array($orders_products_query)) {

$this->products[$index] = array('qty' => $orders_products['products_quantity'],

'name' => $orders_products['products_name'],

......................................................

"

 

should have this:

 

"

while ($orders_products = tep_db_fetch_array($orders_products_query)) {

$this->products[$index] = array('qty' => $orders_products['products_quantity'],

'name' => $orders_products['products_name'],

......................................................

'products_warehouse_location' => $orders_products['products_warehouse_location']

);

"

Share this post


Link to post
Share on other sites
I'm having trouble on the order.php edit.

Can someone please clarify theses steps:

iii) admin/includes/classes/order.php

 

In function "function query($order_id) {" modify the line:

[...]

"

 

yeah the instructions aren't really too clear, but just take it step by step and make sure your code looks like what he's showing and you should be fine.


☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆

Share this post


Link to post
Share on other sites
wrong.jpg

Is it supposed to look like this?

 

no you're missing some definitions somewhere.

 

you're missing step ix) admin/includes/languages/english.php


☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆

Share this post


Link to post
Share on other sites
yeah the instructions aren't really too clear, but just take it step by step and make sure your code looks like what he's showing and you should be fine.

Specifically I'm having trouble understanding this:

 

In the same function, in the $this->info array,

"

$this->info = array('currency' => $order['currency'],

'currency_value' => $order['currency_value'],

...................................

"

add some lines, to look like this:

"

$this->info = array('order_id' => $order_id,

'currency' => $order['currency'],

'currency_value' => $order['currency_value'],

......................................................

Share this post


Link to post
Share on other sites
Specifically I'm having trouble understanding this:

 

In the same function, in the $this->info array,

"

$this->info = array('currency' => $order['currency'],

'currency_value' => $order['currency_value'],

...................................

"

add some lines, to look like this:

"

$this->info = array('order_id' => $order_id,

'currency' => $order['currency'],

'currency_value' => $order['currency_value'],

......................................................

 

 

ok just search for the first chunk and replace it with the 2nd chunk. only difference is that he added

 

'order_id' => $order_id,

 

into the array, so if you just wanted to insert that in the middle or something you could probably get away with that. i don't think it has to be in order. i always try to make it like they have it though.


☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆

Share this post


Link to post
Share on other sites
ok wait i'm having a strange problem. this worked for one store just fine, but the other store is heavily modded and is having tax problems. she says the orders with taxes still will not export into quickbooks, that it's giving her the same error. I exported an order myself and it has a blank for the sales tax. also, taxes aren't showing up when you view orders in the admin panel. they're there, and they get charged, but it's not showing up anywhere. see below:

 

ORDER PAGE IN ADMIN PANEL:

 

ordertotal.gif

 

ORDER EXPORTED INTO IIF (used excel to view):

 

orderexport.gif

 

Is the tax amount appearing in the value field of the orders_total table in the database? If not you could try pulling it from the text field instead.

Share this post


Link to post
Share on other sites
Is the tax amount appearing in the value field of the orders_total table in the database? If not you could try pulling it from the text field instead.

 

No it wasn't but now it is. Thanks for your suggestion though. That might have been a good fix if I hadn't already gotten it working properly. : )


☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆

Share this post


Link to post
Share on other sites
It's a method of the box class

 

ok so why isn't it defined? i'm not understanding this. can anybody help?


☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆

Share this post


Link to post
Share on other sites

has any one tryed this contrib with the new alpha vertion 3.0a5

Edited by MoisesZaragoza

at the end of the day the code will be good

Share this post


Link to post
Share on other sites

i was wondering how i import orders into the QuickBooks

 

 

Thanks


at the end of the day the code will be good

Share this post


Link to post
Share on other sites
i was wondering how i import orders into the QuickBooks

 

 

Thanks

 

first you have to export orders through your admin panel in oscommerce. then you save it somewhere like your desktop or whatever (it's an .iif file). then you import that into quickbooks.


☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆ ☆

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×