Jump to content
mpiscopo

EZ QuickBooks Integration Contribution

Recommended Posts

Hello,

Make sure have imported the sql file.The error tells that some part of the sql script file was not imported.

Can check the database to see that.

 

Regards,

Bogdan

 

Hello, I thought I had installed EZ GB correctly but, I'm having a few errors.

 

The prerequisites is all verified.

 

I created a new customer and purchased an item.

When to >Customers > Orders > "arrow" and I get this error

1054 - Unknown column 'products_warehouse_location' in 'field list'

select orders_products_id, products_name, products_model, products_price, products_tax, products_quantity, products_warehouse_location, final_price from orders_products where orders_id = '1'

[TEP STOP]

 

Also when I try to export orders under the Quickbooks > Export Orders I get this error:

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

SELECT orders_id FROM orders where is_exported = 0

[TEP STOP]

 

Anyone know why I am getting these errors?

Thank you

Share this post


Link to post
Share on other sites

You might want to check out AaaTeX's Shopping Cart Integrator which fully supports osCommerce and does the entire setup with you. They can not only download orders as Invoice, Sales Receipts, Sales Orders, with payments creating Customer and items if needed but also create Purchase Orders and update the inventory on hand in the store. It is EXTREMELY FLEXIBLE. Also works with Custom Fields and QuickBooks Online.

 

http://www.aaatex.com/QuickBooks_Shopping_Cart_Integrator_Cart.htm


QuickBooks Add-on, Services, Programming

http://www.AaaTeX.com

Share this post


Link to post
Share on other sites

Please releazie that an IIF Import from QuickBooks File menu is not supported and can corrupt the QuickBooks database. It also requires admin/single user mode.

 

If you must use an IIF file consider aaatex's IIFImporter which does not have these problems.

http://www.aaatex.com/QuickBooks_IIF_Importer.htm

 

If you need more they also have multiple programs for csv/excel file imports.

http://www.aaatex.com/QuickBooks_Transaction_Importer_Excel.htm

and

http://www.aaatex.com/QuickBooks_Shopping_Cart_Integrator_Cart.htm

 

The Integrator can hook to the osC database via MyODBC and make it all seamless and can import csv/excel also.


QuickBooks Add-on, Services, Programming

http://www.AaaTeX.com

Share this post


Link to post
Share on other sites

Sorry to be dim but I have loaded everything as per the instructions however I am unable to export the orders - the info is drawn from the database and updated to 1(exported) but I get an error that IE cant download quickbooks_export_orders.php from......................... any ideas whether this is a problem with the contribution or my settings?

Share this post


Link to post
Share on other sites

Hi,

 

Goto that quickbooks_export_orders.php file. And check weather $action is working or not. sometimes POST request is not come after click on export button.

IF it is correct then check to another orders query wheather any orders are coming from database or not.

Share this post


Link to post
Share on other sites

Hi,

 

I installed the contribution and all seems there in admin site. I export orders and it does create an excel file and when I import to QB is says import was successful. Problem is that no invoice is created and no customer is created either. I cannot see where the information went in QB. Can anyone help?

 

Thanks :'(

Share this post


Link to post
Share on other sites

A great bit of work, but the install file is not easy reading!

One of the confusing parts that will cause problems i have added below.

 

ii) admin/includes/classes/box.php

Add function, somewhere at the bottom of the file:

"

function simpleBox($contents){

return $this->tableBlock($contents);

}

"

 

This is for users who get this error

 

Fatal error: Call to undefined method box::simpleBox() but have followed the install file and have added the function.

 

All you ned to do is to include it in the main function, so instead of adding it after all other code, like this

 

function menuBox($heading, $contents) {
 	$this->table_data_parameters = 'class="menuBoxHeading"';
 	if (isset($heading[0]['link'])) {
   	$this->table_data_parameters .= ' onmouseover="this.style.cursor=\'hand\'" onclick="document.location.href=\'' . $heading[0]['link'] . '\'"';
   	$heading[0]['text'] = ' <a href="' . $heading[0]['link'] . '" class="menuBoxHeadingLink">' . $heading[0]['text'] . '</a> ';
 	} else {
   	$heading[0]['text'] = ' ' . $heading[0]['text'] . ' ';
 	}
 	$this->heading = $this->tableBlock($heading);

 	$this->table_data_parameters = 'class="menuBoxContent"';
 	$this->contents = $this->tableBlock($contents);

 	return $this->heading . $this->contents;
}
}
function simpleBox($contents){
return $this->tableBlock($contents);
}
?>

 

Change it to this

 

 

function menuBox($heading, $contents) {
 	$this->table_data_parameters = 'class="menuBoxHeading"';
 	if (isset($heading[0]['link'])) {
   	$this->table_data_parameters .= ' onmouseover="this.style.cursor=\'hand\'" onclick="document.location.href=\'' . $heading[0]['link'] . '\'"';
   	$heading[0]['text'] = ' <a href="' . $heading[0]['link'] . '" class="menuBoxHeadingLink">' . $heading[0]['text'] . '</a> ';
 	} else {
   	$heading[0]['text'] = ' ' . $heading[0]['text'] . ' ';
 	}
 	$this->heading = $this->tableBlock($heading);

 	$this->table_data_parameters = 'class="menuBoxContent"';
 	$this->contents = $this->tableBlock($contents);

 	return $this->heading . $this->contents;
}

function simpleBox($contents){
return $this->tableBlock($contents);
}
}
?>

 

This will sort out your problem :- )

Edited by FIMBLE

Sometimes you're the dog and sometimes the lamp post

[/url]

My Contributions

Share this post


Link to post
Share on other sites

Here are some questions I have about getting it configured.

Can someone offer some kind of instructions on how to set this up..... I am going crazy, I want to log the setup time but I have no idea where to start!

 

1- Under the Load Quickbooks categories - It wants me to load a file... What kind of file is it looking for?

2- Under the Configuration Options - I don't know what to enter in blanks.

3- Under the Custom Fields window in the left side bar: I can't seem to do anything. I click insert and it acts like it wants to go but it doesn't do anything.

4 - Under the Update Products window in the left side bar: it is asking for an IIF file. I do this an nothing happens again.

Share this post


Link to post
Share on other sites

Here are some questions I have about getting it configured.

Can someone offer some kind of instructions on how to set this up..... I am going crazy, I want to log the setup time but I have no idea where to start!

 

1- Under the Load Quickbooks categories - It wants me to load a file... What kind of file is it looking for?

2- Under the Configuration Options - I don't know what to enter in blanks.

3- Under the Custom Fields window in the left side bar: I can't seem to do anything. I click insert and it acts like it wants to go but it doesn't do anything.

4 - Under the Update Products window in the left side bar: it is asking for an IIF file. I do this an nothing happens again.

Share this post


Link to post
Share on other sites

hiya this looks good, can i ask please what would happen if the products where not listed in QB? I ask this becuase i do ot store any product details in QB, my products are only store in my osC db.

 

also, second question. does this addon use the QB web connector so that all the data is automnatically pulled from my db or do i have to manually create the export then manaually import into QB?

 

thanks

zac

Share this post


Link to post
Share on other sites

Hello all,

 

First post and I will say right up front that I am way over my head right now and am embarrassed about it.

 

I would like to install this contribution but have no idea what I am doing. I get step 3 and 4...I think. I am struggling with step 2. How do I "run the sql file"? I can see it and open it. But have no idea how to run it.

 

Please be kind, and talk to me like I'm five. Assume I know nothing.

 

Thanks in advance.

 

Michael

Share this post


Link to post
Share on other sites

I've finally got this module working after reading through all 10 pages of this topic.

 

Everything seems great except I'd like to do something if possible:

 

In my quickbooks file I have a class set up for in-store sales and one for website sales. Is it possible to make it so that all of the orders I export from oscommerce and then import into quickbooks will automatically assigned to my website sales class?

 

Thanks in advance


Stephanie D.

Share this post


Link to post
Share on other sites

Hello,

 

I am having one huge problem with this.

 

I ran the query, set all the files into their homes, and followed the modifications to the T.

 

The problem I'm having though.. It's not showing up in my main admin page. There's no Quickbooks option anywhere. I can access the .php's by manually typing in the URL, but that's it.

 

I run MindSparx Admin, is that a problem?

 

If anyone has any ideas, I would really appreciate it.

 

Thanks guys.

Share this post


Link to post
Share on other sites

Thanks for this contribution... The installation went well but I'm trouble with the prices.

 

I'm on osC 2.2.Rc2. I have tons of mods installed, but most don't get in the way of EZ QB. I do use attributes that change the price though, so in quickbooks_export_orders.php I changed

 

$transaction_detail_price = $order->products[$i]['price'];

 

to

 

$transaction_detail_price = $order->products[$i]['final_price'];

 

so the attribute price add-on would be included. The problem now is the price in the .iif shows extended decimals and does not round up or down correctly, causing a "Transaction is not in balance." error in QB.

 

It looks like this -

 

112.94

-35.168

-12.7842

-19.992

-39.992

-5

 

 

I think I need some code to specify 'currency_value', but I'm not sure and I don't know how to do it.

 

Thanks in advance to anyone who can shed some light on a solution to this problem!

Share this post


Link to post
Share on other sites

Thanks for this contribution... The installation went well but I'm trouble with the prices.

 

I'm on osC 2.2.Rc2. I have tons of mods installed, but most don't get in the way of EZ QB. I do use attributes that change the price though, so in quickbooks_export_orders.php I changed

 

$transaction_detail_price = $order->products[$i]['price'];

 

to

 

$transaction_detail_price = $order->products[$i]['final_price'];

 

so the attribute price add-on would be included. The problem now is the price in the .iif shows extended decimals and does not round up or down correctly, causing a "Transaction is not in balance." error in QB.

 

It looks like this -

 

112.94

-35.168

-12.7842

-19.992

-39.992

-5

 

 

I think I need some code to specify 'currency_value', but I'm not sure and I don't know how to do it.

 

Thanks in advance to anyone who can shed some light on a solution to this problem!

 

Sorry, that's not the problem. The 'final_price' is correctly calculated. The problem arises when there is an addition 'item' in the order... such as a donation, or if the order has been edited to apply an additional discount.

 

I get many donations... how would I include them? I've been able to add them manually to the iif file through excel, but I'd like to have it automatically included.

 

The order edits are rare, but ideally they would also be read.

 

Any ideas? I'm almost there!

 

TIA

Share this post


Link to post
Share on other sites

I am having two problems with this contribution. The first is that when I try to import the orders.iif file that is generated from the osCommerce admin, every transaction has the error:

 

"The transaction is not in balance. Make sure the amounts in the detail area on the form for this transaction equal the amount at the top of the form"

 

and then when I hit OK for that,

 

"Error on line 10. Can't record invalid transaction."

 

This is the first item from the transaction file. If anyone can spot a problem, I would be most grateful:

 

!CUST NAME BADDR1 BADDR2 BADDR3 BADDR4 BADDR5 SADDR1 SADDR2 SADDR3 SADDR4 SADDR5 PHONE1 PHONE2 FAXNUM EMAIL NOTE CONT1 CONT2 CTYPE TERMS TAXABLE TAXITEM LIMIT RESALENUM REP COMPANYNAME SALUTATION FIRSTNAME LASTNAME

CUST Toni J (4) Toni J Customer 10000 E. Streetville Albuquerque NM 87111 Toni J Customer 10000 E. Streetville Albuquerque NM 87111 505-555-1212 customer@aol.com Toni J Customer Credit Card N Toni J

!TRNS TRNSID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR TOPRINT ADDR1 ADDR2 ADDR3 ADDR4 ADDR5 SADDR1 SADDR2 SADDR3 SADDR4 SADDR5 NAMEISTAXABLE TERMS SHIPVIA REP FOB INVMEMO PAYMETH PONUM

!SPL SPLID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR QNTY PRICE INVITEM TAXABLE PAYMETHOD EXTRA

!ENDTRNS

TRNS INVOICE 10/18/2001 Accounts Receivable Toni J (4) 21 N N Toni J Customer 10000 E. Streetville Albuquerque NM 87111 Toni J Customer 10000 E. Streetville Albuqurque NM 87111 N Credit Card Method no set Thank you for shopping at the Button Emporium.

SPL INVOICE 10/18/2001 Sales:Merchandise -21 Flag N -12 1.7500 24850 N

SPL INVOICE 10/18/2001 Shipping Charge -4.5000 N -1 Shipping N

SPL INVOICE 10/18/2001 Sales Tax Payable 0 N N AUTOSTAX

ENDTRNS

 

 

Cheers,

Avery

Share this post


Link to post
Share on other sites

Avery>

I had the same problem about the transaction 'not balancing' and what this means is that somewhere in this part:

TRNS INVOICE 10/18/2001 Accounts Receivable Toni J (4) 21 N N T

is the total amount of the transaction (I think it's 21)

and that does not correspond to this part:

SPL INVOICE 10/18/2001 Sales:Merchandise -21 Flag N -12 1.7500 24850 N

SPL INVOICE 10/18/2001 Shipping Charge -4.5000 N -1 Shipping N

SPL INVOICE 10/18/2001 Sales Tax Payable 0 N N AUTOSTAX

As you can see 21 + 4.5 = 26.50

 

In my case I was using the wrong tax Code. In Quickbooks I have 'GST' and in OsCommerce my sales tax is called 'GST', and then in my EZ Quickbooks settings (on Oscommerce) I set the tax setting to 'GST' and now all my orders are balancing.

 

 

My problem: It's not creating invoices - it seems to be doing sales receipts or something :(

Share this post


Link to post
Share on other sites

I installed this contribution, and everything seems to work ok, except the includes/classes/order.php For some reason, it's breaking the connection to the DB, and I get a "The SQL database as gone away" error if I access the customers/orders page in admin.

 

I'm assuming I screwed up something while adding lines to the arrays and stuff in that file.

 

Either I'm a total idiot, or the directions were kind of vague, because i've tried to edit that file 3 times and I keep getting the same error. if I upload my backup of the order.php file the orders page works just fine.

 

My question is, does anyone have a working copy of that one file available? I have no other contributions that affect this file, so there won't be any compatibility issues.

 

I'm slowly going bald pulling my hair out over this silly issue.

Share this post


Link to post
Share on other sites
Ok this is for anyone who wants to install this contribution. You'll see from my posts above that I have a lot of trouble getting this to work, and this is compounded by the lack of installation support......

 

Thanks Halfhidden!

You gave me exactly what I was looking for! :)

Share this post


Link to post
Share on other sites

Well, I followed Halfhidden's instructions, and re edited the admin/includes/classes/order.php file hoping it would fix my problem, but once again, as soon as I upload that one file, the customer orders page in admin loads as only an SQL error. Here's the error message if it helps

 

2006 - MySQL server has gone away

 

select count(*) as total from sessions where sesskey = '67raocp6ephnkdk0l17pur88n6'

 

[TEP STOP]

 

If I upload the backup file, it works fine, I thought maybe I just put something in the wrong place, but I followed HH's instructions very carefully and it still doesn't work. does anyone have any ideas?

Share this post


Link to post
Share on other sites

There doesn't seem to be any support here anymore, but I thought I'd try again.

 

The problem arises when there is an addition 'item' in the order... such as a donation, or if the order has been edited to apply an additional discount.

 

I get many donations... how would I include them? I've been able to add them manually to the iif file through excel, but I'd like to have it automatically included.

 

Anybody have an idea on how to fix this??? Maybe through a custom field but I've tried a few ways and donations still don't get exported.

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

×