Quickbooks Fix - Actually use the Quickbooks Export

krazik · #1 01-14-2004, 11:09 PM

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

leon · #2 01-15-2004, 12:08 AM

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.

DataViking · #3 01-15-2004, 01:56 AM

I'm going to give it a try

krazik · #4 01-15-2004, 07:41 AM

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

leon · #5 01-15-2004, 09:45 AM

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.

Pendlum · #6 01-19-2004, 11:16 AM

any luck?

Nosferatu · #7 01-20-2004, 10:06 AM

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

jpsowin · #8 01-21-2004, 06:32 AM

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.

DataViking · #9 01-21-2004, 07:33 AM

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

xam · #10 01-31-2004, 06:26 AM

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.