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