Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

EZ QuickBooks Integration Contribution


mpiscopo

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?

Link to comment
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
Link to comment
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.

Link to comment
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

Link to comment
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

Link to comment
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!

Link to comment
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'];

Link to comment
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']),

Link to comment
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.

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

Link to comment
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!

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

Link to comment
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

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

Link to comment
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

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

Link to comment
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']

);

"

Link to comment
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.

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

Link to comment
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

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

Link to comment
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'],

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

Link to comment
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.

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

Link to comment
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.

Link to comment
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. : )

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

Link to comment
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?

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

Link to comment
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.

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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...