Follow us on Twitter X-Cart on Facebook Wiki
Shopping cart software Solutions for online shops and malls

Quickbooks Fix - Actually use the Quickbooks Export

 
Reply
   X-Cart forums > X-Cart 4 > Dev Questions
 
Thread Tools Search this Thread
  #1  
Old 01-14-2004, 11:09 PM
 
krazik krazik is offline
 

Member
  
Join Date: May 2003
Posts: 14
 

Default Quickbooks Fix - Actually use the Quickbooks Export

This is the 2nd version of my code and includes some fixes when exporting items that included Discounts as x-cart would round incorrectly and a transaction would be $.01 off causing QuickBooks to balk.

I have been using this template for over 6 months now and it works great so long as you only have 2 tax levels. (Taxed and Non-Taxed)

I've decided to post it here because x-cart still doesn't actually work with QuickBooks and it just takes major changes to 1 template to fix. A lot of you have been waiting for a long time.

I've spent a lot of time making and debugging this. I ask that if you use this template you send me a donation to rylan@rylan.org.

To install replace your
skin1/modules/QuickBooks/orders_export_qb.tpl with the file below

Code:
{* * ------------------------------------------------------------------ * orders_export_qb.tpl - Functional QuickBooks IIF export for X-Cart * Writen By Rylan Hazelton - rylan@rylan.org * Based on "orders_export_qb.tpl,v 1.3 2003/03/31 13:22:40 svowl Exp" * * Version: 1.10 - 01/05/2004 * * SYNOPSIS: * Creates correct IIF Output for X-Cart * * ------------------------------------------------------------------ * :::: ::: :::::::: ::::::::::: ::::::::::: :::::::: :::::::::: * :+:+: :+: :+: :+: :+: :+: :+: :+: :+: * :+:+:+ +:+ +:+ +:+ +:+ +:+ +:+ +:+ * +#+ +:+ +#+ +#+ +:+ +#+ +#+ +#+ +#++:++# * +#+ +#+#+# +#+ +#+ +#+ +#+ +#+ +#+ * #+# #+#+# #+# #+# #+# #+# #+# #+# #+# * ### #### ######## ### ########### ######## ########## * ------------------------------------------------------------------ * I am posting this template as Shareware. I would hope that if you * are using this you consider the time and money you are saving by * not entering this data by hand and send me a donation. I've spent * a lot of time on this and am sharing it for all to use. * * Support is only available to those who have donated. * Donate via PayPal to rylan@rylan.org * * ------------------------------------------------------------------ * * ------------------------------------------------------------------ * CONFIGURATION SECTION: * * NOTE: This script only supports passing 2 tax types, if you have * multiple levels of taxes you're going to have to modify the orders in * QuickBooks after importing * ------------------------------------------------------------------ * * TaxAgency: The Name of your Tax Agency from Quick Books *} {assign var="TaxAgency" value="Board of Equalization"} {* * TaxAccount: Quickbooks account for taxable orders *} {assign var="TaxAccount" value="CA Sales Tax"} {* * NoTaxAccount: Quickbooks account for non taxable orders *} {assign var="NoTaxAccount" value="Out Of State"} {* * TaxRate: Fixed tax rate for taxable items. *} {assign var="TaxRate" value="7.25%"} {* * START IIF *} {assign var="have_processed" value=""} !CUST{$delimiter}NAME{$delimiter}BADDR1{$delimiter}BADDR2{$delimiter}BADDR3{$delimiter}BADDR4{$delimiter}BADDR5{$delimiter}SADDR1{$delimiter}SADDR2{$delimiter}SADDR3{$delimiter}SADDR4{$delimiter}SADDR5{$delimiter}PHONE1{$delimiter}PHONE2{$delimiter}FAXNUM{$delimiter}EMAIL{$delimiter}NOTE{$delimiter}CONT1{$delimiter}CONT2{$delimiter}CTYPE{$delimiter}TERMS{$delimiter}USETAX{$delimiter}TAXCODE{$delimiter}LIMIT{$delimiter}TAXID{$delimiter}REP{$delimiter}NOTEPAD{$delimiter}SALUTATION{$delimiter}COMPANYNAME{$delimiter}FIRSTNAME{$delimiter}MIDINIT{$delimiter}LASTNAME{$delimiter}CUSTFLD1{$delimiter}CUSTFLD2{$delimiter}CUSTFLD3{$delimiter}CUSTFLD4{$delimiter}CUSTFLD5{$delimiter}CUSTFLD6{$delimiter}CUSTFLD7{$delimiter}CUSTFLD8{$delimiter}CUSTFLD9{$delimiter}CUSTFLD10{$delimiter}CUSTFLD11{$delimiter}CUSTFLD12{$delimiter}CUSTFLD13{$delimiter}CUSTFLD14{$delimiter}CUSTFLD15 {php} $oindex=0; $orig_logins=array(); {/php} {section name=oid loop=$orders} {php} global $orders_full; if (!in_array($orders_full[$oindex][login], $orig_logins)) { $orig_logins[]=$orders_full[$oindex][login]; {/php} CUST{$delimiter}"{$orders[oid].lastname|capitalize}, {$orders[oid].firstname|capitalize}"{$delimiter}"{$orders[oid].b_address|replace:"\"":"'"}"{$delimiter}"{$orders[oid].b_city}, {$orders[oid].b_state} {$orders[oid].b_zipcode}"{$delimiter}"{$orders[oid].b_country}"{$delimiter}{$delimiter}{$delimiter}"{$orders[oid].s_address|replace:"\"":"'"}"{$delimiter}"{$orders[oid].s_city}, {$orders[oid].s_state} {$orders[oid].s_zipcode}"{$delimiter}"{$orders[oid].s_country}"{$delimiter}{$delimiter}{$delimiter}{$orders[oid].phone}{$delimiter}{$delimiter}{$orders[oid].fax}{$delimiter}{$orders[oid].email}{$delimiter}{$delimiter}"{$orders[oid].lastname|capitalize}, {$orders[oid].firstname|capitalize}"{$delimiter}{$delimiter}{$delimiter}{$delimiter}{$delimiter}{$delimiter}10000{$delimiter}{$delimiter}{$delimiter}{$delimiter}"{$orders[oid].title}"{$delimiter}"{$orders[oid].company|replace:"\"":"'"}"{$delimiter}"{$orders[oid].firstname|capitalize}"{$delimiter}{$delimiter}"{$orders[oid].lastname|capitalize}"{$delimiter}"{$orders[oid].login}"{$delimiter}{$delimiter}{$delimiter}{$delimiter}{$delimiter}{$delimiter}{$delimiter}{$delimiter}{$delimiter}{$delimiter}{$delimiter}{$delimiter}{$delimiter}{$delimiter} {php} } $oindex++; {/php} {/section} !TRNS{$delimiter}TRNSTYPE{$delimiter}DATE{$delimiter}ACCNT{$delimiter}NAME{$delimiter}CLASS{$delimiter}AMOUNT{$delimiter}DOCNUM{$delimiter}MEMO{$delimiter}ADDR1{$delimiter}ADDR2{$delimiter}ADDR3{$delimiter}ADDR4{$delimiter}ADDR5{$delimiter}SHIPVIA{$delimiter}SADDR1{$delimiter}SADDR2{$delimiter}SADDR3{$delimiter}SADDR4{$delimiter}SADDR5{$delimiter}EXTRA{$delimiter} !SPL{$delimiter}TRNSTYPE{$delimiter}DATE{$delimiter}ACCNT{$delimiter}NAME{$delimiter}CLASS{$delimiter}AMOUNT{$delimiter}DOCNUM{$delimiter}MEMO{$delimiter}PRICE{$delimiter}QNTY{$delimiter}INVITEM{$delimiter}TAXABLE{$delimiter}EXTRA{$delimiter} !ENDTRNS {php} $oindex=0; $used_oid=array(); global $orders_full; {/php} {section name=oid loop=$orders} {php} $orderid=$orders_full[$oindex][orderid]; if (!in_array($orderid, $used_oid)) { $used_oid[]=$orderid; {/php} {math equation="x+y" x=$orders[oid].total y=$orders[oid].giftcert_discount assign="total_w_giftcerts" format="%.2f"} {assign var="ordertotal" value=$total_w_giftcerts} {assign var="ordertotalcheck" value=0} TRNS{$delimiter}INVOICE{$delimiter}{$orders[oid].date|date_format:"%m/%d/%Y"}{$delimiter}"Accounts Receivable"{$delimiter}"{$orders[oid].lastname|capitalize}, {$orders[oid].firstname|capitalize}"{$delimiter}"Website:Retail"{$delimiter}{$total_w_giftcerts}{$delimiter}{$orders[oid].orderid}{$delimiter}"Website Order"{$delimiter}"{$orders[oid].b_address|replace:"\"":"'"}"{$delimiter}"{$orders[oid].b_city}, {$orders[oid].b_state} {$orders[oid].b_zipcode}"{$delimiter}"{$orders[oid].b_country}"{$delimiter}{$delimiter}{$delimiter}"{$orders[oid].shipping|regex_replace:"/ .*$/":""}"{$delimiter}"{$orders[oid].b_address|replace:"\"":"'"}"{$delimiter}"{$orders[oid].b_city}, {$orders[oid].b_state} {$orders[oid].b_zipcode}"{$delimiter}"{$orders[oid].b_country}"{$delimiter}{$delimiter}{$delimiter}AUTOSTAX{$delimiter} {php} } $oindex++; {/php} {if $orders[oid].gcid} SPL{$delimiter}INVOICE{$delimiter}{$orders[oid].date|date_format:"%m/%d/%Y"}{$delimiter}"Sales:Gift Certificate"{$delimiter}"{$orders[oid].lastname|capitalize}, {$orders[oid].firstname|capitalize}"{$delimiter}"Website:Retail"{$delimiter}-{$orders[oid].amount}{$delimiter}{$orders[oid].orderid}{$delimiter}"GC#{$orders[oid].gcid}"{$delimiter}{$orders[oid].amount}{$delimiter}-1{$delimiter}"GIFT CERTIFICATE"{$delimiter}N{$delimiter}{$delimiter}{math equation="x+y" x=$ordertotalcheck y=$orders[oid].amount assign="ordertotalcheck" format="%.2f"} {else} SPL{$delimiter}INVOICE{$delimiter}{$orders[oid].date|date_format:"%m/%d/%Y"}{$delimiter}"Sales:Product"{$delimiter}"{$orders[oid].lastname|capitalize}, {$orders[oid].firstname|capitalize}"{$delimiter}"Website:Retail"{$delimiter}-{$orders[oid].cost}{$delimiter}{$orders[oid].orderid}{$delimiter}"#{$orders[oid].productid}: {$orders[oid].product|replace:"\"":"'"}"{$delimiter}{$orders[oid].price}{$delimiter}-{$orders[oid].amount}{$delimiter}"{$orders[oid].productcode|truncate:30:"":true}"{$delimiter}Y{$delimiter}{$delimiter}{math equation="x+y" x=$ordertotalcheck y=$orders[oid].cost assign="ordertotalcheck" format="%.2f"} {/if} {if $orders[oid].status eq "P" || $orders[oid].status eq "C"}{assign var="have_processed" value="Y"}{/if} {php} if (!$orders_full[$oindex]||$orders_full[$oindex][orderid]!=$orderid) { {/php} {if $orders[oid].coupon_discount gt 0} SPL{$delimiter}INVOICE{$delimiter}{$orders[oid].date|date_format:"%m/%d/%Y"}{$delimiter}"Sales:Coupon Discount"{$delimiter}"{$orders[oid].lastname|capitalize}, {$orders[oid].firstname|capitalize}"{$delimiter}"Website:Retail"{$delimiter}{$orders[oid].coupon_discount}{$delimiter}{$orders[oid].orderid}{$delimiter}"{$orders[oid].coupon}"{$delimiter}{$orders[oid].coupon_discount}{$delimiter}-1{$delimiter}"COUPON"{$delimiter}N{$delimiter}{$delimiter}{math equation="x-y" x=$ordertotalcheck y=$orders[oid].coupon_discount assign="ordertotalcheck" format="%.2f"} {/if} {if $orders[oid].discount gt 0} SPL{$delimiter}INVOICE{$delimiter}{$orders[oid].date|date_format:"%m/%d/%Y"}{$delimiter}"Sales:Discount"{$delimiter}"{$orders[oid].lastname|capitalize}, {$orders[oid].firstname|capitalize}"{$delimiter}"Website:Retail"{$delimiter}{$orders[oid].discount}{$delimiter}{$orders[oid].orderid}{$delimiter}"DISCOUNT"{$delimiter}{$orders[oid].discount}{$delimiter}-1{$delimiter}"DISCOUNT"{$delimiter}N{$delimiter}N{$delimiter}{$delimiter}{math equation="x-y" x=$ordertotalcheck y=$orders[oid].discount assign="ordertotalcheck" format="%.2f"} {/if} SPL{$delimiter}INVOICE{$delimiter}{$orders[oid].date|date_format:"%m/%d/%Y"}{$delimiter}"Sales:Shipping"{$delimiter}"{$orders[oid].lastname|capitalize}, {$orders[oid].firstname|capitalize}"{$delimiter}"Website:Retail"{$delimiter}-{$orders[oid].shipping_cost}{$delimiter}{$orders[oid].orderid}{$delimiter}"{$orders[oid].shipping}"{$delimiter}{$orders[oid].shipping_cost}{$delimiter}-1{$delimiter}"SHIPPING"{$delimiter}N{$delimiter}{$delimiter}{math equation="x+y" x=$ordertotalcheck y=$orders[oid].shipping_cost assign="ordertotalcheck" format="%.2f"}{math equation="x+y" x=$ordertotalcheck y=$orders[oid].tax assign="ordertotalcheck" format="%.2f"} {if $ordertotalcheck != $ordertotal} {math equation="y-x" x=$ordertotal y=$ordertotalcheck assign=ordercorrection format="%.2f"}SPL{$delimiter}INVOICE{$delimiter}{$orders[oid].date|date_format:"%m/%d/%Y"}{$delimiter}"Sales:Discount"{$delimiter}"{$orders[oid].lastname|capitalize}, {$orders[oid].firstname|capitalize}"{$delimiter}"Website:Retail"{$delimiter}{$ordercorrection}{$delimiter}{$orders[oid].orderid}{$delimiter}"DISCOUNT"{$delimiter}{$orders[oid].discount}{$delimiter}-1{$delimiter}"DISCOUNT"{$delimiter}N{$delimiter}N{$delimiter}{$delimiter} {/if} {if $orders[oid].tax gt 0} SPL{$delimiter}INVOICE{$delimiter}{$orders[oid].date|date_format:"%m/%d/%Y"}{$delimiter}"Sales Tax Payable"{$delimiter}"{$TaxAgency}"{$delimiter}{$delimiter}-{$orders[oid].tax}{$delimiter}{$orders[oid].orderid}{$delimiter}"Sales Tax"{$delimiter}{$TaxRate}{$delimiter}{$delimiter}"{$TaxAccount}"{$delimiter}N{$delimiter}AUTOSTAX{$delimiter} {else} SPL{$delimiter}INVOICE{$delimiter}{$orders[oid].date|date_format:"%m/%d/%Y"}{$delimiter}"Sales Tax Payable"{$delimiter}"{$TaxAgency}"{$delimiter}{$delimiter}0%{$delimiter}{$orders[oid].orderid}{$delimiter}"Sales Tax"{$delimiter}{$orders[oid].tax}{$delimiter}{$delimiter}"{$NoTaxAccount}"{$delimiter}N{$delimiter}AUTOSTAX{$delimiter} {/if} ENDTRNS {php} } {/php} {/section} {if $have_processed} !TRNS{$delimiter}TRNSTYPE{$delimiter}DATE{$delimiter}ACCNT{$delimiter}NAME{$delimiter}AMOUNT{$delimiter}PAYMETH{$delimiter}DOCNUM !SPL{$delimiter}TRNSTYPE{$delimiter}DATE{$delimiter}ACCNT{$delimiter}NAME{$delimiter}AMOUNT{$delimiter}PAYMETH{$delimiter}DOCNUM !ENDTRNS {assign var="new_order" value=""} {section name=oid loop=$orders} {if $new_order ne $orders[oid].orderid} {if $new_order} ENDTRNS {/if} {if $orders[oid].status eq "P" || $orders[oid].status eq "C"} {assign var="new_order" value=$orders[oid].orderid} TRNS{$delimiter}PAYMENT{$delimiter}{$orders[oid].date|date_format:"%m/%d/%Y"}{$delimiter}"Undeposited Funds"{$delimiter}"{$orders[oid].lastname|capitalize}, {$orders[oid].firstname}"{$delimiter}{$orders[oid].total}{$delimiter}{$orders[oid].payment_method}{$delimiter}{$orders[oid].orderid} SPL{$delimiter}PAYMENT{$delimiter}{$orders[oid].date|date_format:"%m/%d/%Y"}{$delimiter}"Accounts Receivable"{$delimiter}"{$orders[oid].lastname|capitalize}, {$orders[oid].firstname}"{$delimiter}-{$orders[oid].total}{$delimiter}{$orders[oid].payment_method}{$delimiter}{$orders[oid].orderid} {/if} {/if} {/section} ENDTRNS {/if}

Enjoy,
-Rylan Hazelton
Reply With Quote
  #2  
Old 01-15-2004, 12:08 AM
  leon's Avatar 
leon leon is offline
 

X-Wizard
  
Join Date: Mar 2003
Location: Costa Rica
Posts: 1,213
 

Default

Does this work with Quickbooks Pro 99, or only versions 2002 and above ?

I wouldnt want to rush and use it if it attempts to break my cart and my books. I prefer to know first.
__________________
It doesn\'t matter what is done... it is how it is done.
=============================
XCart Version: 3.5.3 -> Dmcigars.com
XCart Version: 4.1.3....
Reply With Quote
  #3  
Old 01-15-2004, 01:56 AM
  DataViking's Avatar 
DataViking DataViking is offline
 

eXpert
  
Join Date: Jan 2003
Location: Las Vegas, NV
Posts: 361
 

Default

I'm going to give it a try
__________________
Web Design Web Design and Custom X-Cart Projects

http://www.dataviking.com

Mention the forums for discounts!
x-cart Version 4.1.8
Reply With Quote
  #4  
Old 01-15-2004, 07:41 AM
 
krazik krazik is offline
 

Member
  
Join Date: May 2003
Posts: 14
 

Default

Leon:

I've only tried it w/ QB2002&2003. But I don't believe the IIF format has changed much. So its likely to work with 99. But I need someone who has 99 to confirm that.

-Rylan
Reply With Quote
  #5  
Old 01-15-2004, 09:45 AM
  leon's Avatar 
leon leon is offline
 

X-Wizard
  
Join Date: Mar 2003
Location: Costa Rica
Posts: 1,213
 

Default

In other words you want me to be your guinea pig ?

Alright, Ill give it a try this weekend and let you know if I broke it or worked it out.
__________________
It doesn\'t matter what is done... it is how it is done.
=============================
XCart Version: 3.5.3 -> Dmcigars.com
XCart Version: 4.1.3....
Reply With Quote
  #6  
Old 01-19-2004, 11:16 AM
 
Pendlum Pendlum is offline
 

Newbie
  
Join Date: Sep 2003
Location: Houston, TX
Posts: 8
 

Default

any luck?
__________________
Pendlum
www.jpcustoms.com

X-cart Version: 4.1.9
Reply With Quote
  #7  
Old 01-20-2004, 10:06 AM
 
Nosferatu Nosferatu is offline
 

Newbie
  
Join Date: May 2003
Location: Flagstaff, AZ
Posts: 6
 

Default

I keep getting the error:

Quote:

Internet Explorer cannot download ...&EndDay=20EndYear=2004&qb=on from mydomain.com

Internet Explorer was not able to open this Internet site. The requested site is either unavailable or cannot be found. Please try again later.

This happens after I check the "QB Format" box and hit export. It asks me to save the file. When I hit save, it gives the above error.

Am I doing something wrong? It seems like the filename is funkadelic.

X-Cart v3.4.0
PHP v4.3.3
MySQL v4.0.15-standard
Reply With Quote
  #8  
Old 01-21-2004, 06:32 AM
 
jpsowin jpsowin is offline
 

X-Adept
  
Join Date: Sep 2002
Posts: 459
 

Default

I think it's really sad that no one can use the Quickbooks export without a 3rd party module. It should work out of the box, like it advertises. I really wish they would address this, because it seems like every version says "fixed xxx in quickbooks export" but it never helps.
__________________
Joshua Sowin
...taking things one TPL at a time.
Reply With Quote
  #9  
Old 01-21-2004, 07:33 AM
  DataViking's Avatar 
DataViking DataViking is offline
 

eXpert
  
Join Date: Jan 2003
Location: Las Vegas, NV
Posts: 361
 

Default

Quote:
Originally Posted by jpsowin
I think it's really sad that no one can use the Quickbooks export without a 3rd party module. It should work out of the box, like it advertises. I really wish they would address this, because it seems like every version says "fixed xxx in quickbooks export" but it never helps.

right on the dot
__________________
Web Design Web Design and Custom X-Cart Projects

http://www.dataviking.com

Mention the forums for discounts!
x-cart Version 4.1.8
Reply With Quote
  #10  
Old 01-31-2004, 06:26 AM
 
xam xam is offline
 

Newbie
  
Join Date: Jan 2004
Posts: 1
 

Default

krazic, thanks for the code. If it works, I will definately make a donation. I am trying to get it to work with QB Pro 2003, and when I import the file, I get no errors. However, when I view the transactions in QB, I only get the customer information. I don't get any product or pricing information. Is there something I have to edit? TIA for any insight you may have.
Reply With Quote
Reply
   X-Cart forums > X-Cart 4 > Dev Questions


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT -8. The time now is 08:47 PM.

   

 
X-Cart forums © 2001-2020