[interchange-cvs] interchange - danb modified 17 files
interchange-core@icdevgroup.org
interchange-core@icdevgroup.org
Wed Jun 4 05:24:00 2003
User: danb
Date: 2003-06-04 09:23:29 GMT
Modified: . WHATSNEW
Modified: extensions/quickbooks/pages/admin/quickbooks index.html
Added: code/Filter qb_safe.filter
Added: extensions/quickbooks README.html README.txt
Added: extensions/quickbooks/legacy qb.catalog.cfg trans_quickbooks
Added: extensions/quickbooks/menus Admin.txt.append
Added: extensions/quickbooks/pages/admin/quickbooks
Added: generate_new_iif.html
Added: extensions/quickbooks/products mv_metadata.asc.append
Added: variable.txt.append
Added: extensions/quickbooks/vars TRANS_QUICKBOOKS
Removed: extensions/quickbooks README ic_qb.pod
Removed: mv_metadata-additions.asc qb.catalog.cfg
Removed: extensions/quickbooks/etc trans_quickbooks
Log:
* Quickbooks updates:
- Documentation built from updated Interchange + Quickbooks HOWTO.
- Major change to IIF generation routine:
- IIF files are generated on-demand via the Admin UI
- In-route method still available
- New "Generate new IIF files" and "download IIF files"
- New "qb_safe" filter, to cater to Quickbooks sensative side.
- New default values (like defaulting to cash receipt instead of invoice)
- Payment and Shipping methods are recorded in corresponding Quickbooks fields.
- Allow customization of the transaction account (new default is "Checking")
- Optionally set the transaction class (e.g., to track online sales in a
separate class from walk-in sales, and report on them differently).
- Optional Item Prefixes (e.g. if all items are accounting in Quickbooks under
a given category.
- Optionally get the tax agency from the "state" table, falling back to the
configured default tax agency.
- Bug fixes
Revision Changes Path
2.128 +31 -0 interchange/WHATSNEW
rev 2.128, prev_rev 2.127
Index: WHATSNEW
===================================================================
RCS file: /var/cvs/interchange/WHATSNEW,v
retrieving revision 2.127
retrieving revision 2.128
diff -u -r2.127 -r2.128
--- WHATSNEW 24 May 2003 00:05:18 -0000 2.127
+++ WHATSNEW 4 Jun 2003 09:23:28 -0000 2.128
@@ -953,8 +953,39 @@
* Fix a bug that prevented the cgi-bin link program from being associated
with the chosen owner and group. The patch was created and tested by Carl
Bailey.
+
+Extensions
+----------
+* Quickbooks updates:
+ - Documentation built from updated Interchange + Quickbooks HOWTO.
+
+ - Major change to IIF generation routine:
+ - IIF files are generated on-demand via the Admin UI
+ - In-route method still available
+ - New "Generate new IIF files" and "download IIF files"
+
+ - New "qb_safe" filter, to cater to Quickbooks sensative side.
+
+ - New default values (like defaulting to cash receipt instead of invoice)
+
+ - Payment and Shipping methods are recorded in corresponding Quickbooks fields.
+
+ - Allow customization of the transaction account (new default is "Checking")
+
+ - Optionally set the transaction class (e.g., to track online sales in a
+ separate class from walk-in sales, and report on them differently).
+
+ - Optional Item Prefixes (e.g. if all items are accounting in Quickbooks under
+ a given category.
+
+ - Optionally get the tax agency from the "state" table, falling back to the
+ configured default tax agency.
+
+ - Bug fixes
+
+
------------------------------------------------------------------------------
1.1 interchange/code/Filter/qb_safe.filter
rev 1.1, prev_rev 1.0
Index: qb_safe.filter
===================================================================
# QuickBooks Safe: make data safe for quickbooks
# QB will crash at the drop of a hat, so don't forget to remove
# any unfriendly characters.
CodeDef qb_safe Filter
CodeDef qb_safe Routine <<EOR
sub {
my $val = shift;
$val =~ s/[\"\.\/\\]//g;
return $val;
}
EOR
1.1 interchange/extensions/quickbooks/README.html
rev 1.1, prev_rev 1.0
Index: README.html
===================================================================
<!doctype html public "-//W30//DTD W3 HTML 2.0//EN">
<HTML>
<!-- This file was generated using SDF 2.001 by
Ian Clatworthy (ianc@mincom.com). SDF is freely
available from http://www.mincom.com/mtr/sdf. -->
<HEAD>
<TITLE>Interchange + QuickBooks HOWTO</TITLE>
</HEAD>
<BODY BGCOLOR="ffffff" LINK="993333">
<DIV CLASS="header">
<DIV CLASS="navigate">
<P ALIGN="Center"><A HREF="index.html" TARGET="_top">Catalog</A></P>
</DIV>
</DIV>
<DIV CLASS="title">
<P><IMG SRC="iclogo.gif" ALIGN="Right"></P>
<H1 CLASS="doc-title">Interchange + QuickBooks HOWTO</H1>
<ADDRESS CLASS="doc-author"></ADDRESS>
<BR CLEAR="All">
</DIV>
<DIV CLASS="contents">
<HR>
<H2>Table of Contents</H2>
<UL>
<A HREF="#Introduction">1. Introduction</A><UL>
<A HREF="#Summary Description">1.1. Summary Description</A>
<BR>
<A HREF="#Audience">1.2. Audience</A>
<BR>
<A HREF="#Contact the author">1.3. Contact the author</A>
<BR>
<A HREF="#Version">1.4. Version</A></UL>
<BR>
<A HREF="#Description">2. Description</A>
<BR>
<A HREF="#Contents">3. Contents</A>
<BR>
<A HREF="#Installation">4. Installation</A><UL>
<A HREF="#Terms and locations">4.1. Terms and locations</A>
<BR>
<A HREF="#Create and copy directories and files">4.2. Create and copy directories and files</A>
<BR>
<A HREF="#Quick Installation Script">4.3. Quick Installation Script</A>
<BR>
<A HREF="#Admin UI Usage">4.4. Admin UI Usage</A>
<BR>
<A HREF="#Additional database fields -- userdb">4.5. Additional database fields -- userdb</A>
<BR>
<A HREF="#Additional database fields -- inventory">4.6. Additional database fields -- inventory</A>
<BR>
<A HREF="#Modify catalog.cfg with additions:">4.7. Modify catalog.cfg with additions:</A>
<BR>
<A HREF="#Add quickbooks order route">4.8. Add quickbooks order route</A>
<BR>
<A HREF="#Additional Variables">4.9. Additional Variables</A>
<BR>
<A HREF="#Restart the catalog">4.10. Restart the catalog</A>
<BR>
<A HREF="#Export the items">4.11. Export the items</A>
<BR>
<A HREF="#Test">4.12. Test</A></UL>
<BR>
<A HREF="#Discussion">5. Discussion</A><UL>
<A HREF="#Sales Tax">5.1. Sales Tax</A>
<BR>
<A HREF="#Shipping">5.2. Shipping</A>
<BR>
<A HREF="#Customer Imports">5.3. Customer Imports</A>
<BR>
<A HREF="#IIF generation at time of order">5.4. IIF generation at time of order</A></UL>
<BR>
<A HREF="#Credits">A. Credits</A>
<BR>
<A HREF="#Document history">B. Document history</A>
<BR>
<A HREF="#Resources">C. Resources</A><UL>
<A HREF="#Documentation">C.1. Documentation</A></UL></UL>
</DIV>
<DIV CLASS="main">
<HR>
<H1><A NAME="Introduction">1. Introduction</A></H1>
<H2><A NAME="Summary Description">1.1. Summary Description</A></H2>
<P>Interchange QuickBooks -- QuickBooks support for transactions and items</P>
<H2><A NAME="Audience">1.2. Audience</A></H2>
<P>Users who already have Quickbooks setup and are familiar with it, in addition to having the foundation (or other) catalog correctly working.</P>
<H2><A NAME="Contact the author">1.3. Contact the author</A></H2>
<P>If you find any spelling errors, technical slip-ups, mistakes, subliminal messages, or if you wish to send feedback, critique, remarks, comments, or if you wish to contribute examples, instructions for alternative platforms, chapters, or other material, please do so.</P>
<P>The preferred method of submitting changes is in the form of a context diff against the SDF source file (ic_howto_qb.sdf). Please address your correspondence to:</P>
<P>Volunteer Maintainer, Dan Browning <A HREF="mailto:db@kavod.com">db@kavod.com</A></P>
<P>or</P>
<P>Original Author, Mike Heins <A HREF="mailto:mike@perusion.com">mike@perusion.com</A></P>
<H2><A NAME="Version">1.4. Version</A></H2>
<P>This document describes software based on Interchange 4.5 and later.</P>
<HR>
<H1><A NAME="Description">2. Description</A></H1>
<P>Interchange is a business-to-business and business-to-consumer internet ordering and cataloguing product. It has the ability to take orders via the World Wide Web, and store transaction data.</P>
<P>This document describes how to interface Interchange with QuickBooks, the popular small-business accounting program from Intuit.</P>
<P>QuickBooks has an import/export format called IIF, a mnemonic for Intuit Interchange Format. Fitting, eh?</P>
<P>The standard capabilities of Interchange allow production of IIF files for transaction passing. With some support from Interchange UserTags, it can even import and export item listings.</P>
<HR>
<H1><A NAME="Contents">3. Contents</A></H1>
<P>The extension files can be found in the Interchange tarball under the '<TT>extensions/quickbooks</TT>' directory. The following files are used with this extension:</P>
<PRE>
usertag/import_quicken_items UserTag for importing items
usertag/export_quicken_items UserTag for exporting items
pages/admin/quickbooks/* Menu support for Interchange UI
qb.catalog.cfg Quickbooks configuration.
</PRE>
<HR>
<H1><A NAME="Installation">4. Installation</A></H1>
<P>To set up this extension, the basic steps are:</P>
<UL>
<LI>Create and copy directories and files.
<LI>Add additional database fields.
<LI>Modify catalog.cfg with additions.
<LI>Add "quickbooks" order route to checkout pages.
<LI>Restart Interchange.
<LI>Export your items from Interchange catalog (or import your existing QuickBooks items to Interchange).
<LI>Test.</UL>
<H2><A NAME="Terms and locations">4.1. Terms and locations</A></H2>
<P>Several terms are used in the examples.</P>
<P><B>Catalog Directory</B></P>
<UL>
This is the main directory for the catalog, where catalog.cfg resides. It will have a NAME, the name for the catalog. (Some common Interchange demo names are <TT>foundation</TT>, <TT>construct</TT>, <TT>barry</TT>, and <TT>simple</TT>.)
<BR>
Common locations:
<BR>
/var/lib/interchange/NAME /usr/local/interchange/catalogs/NAME $HOME/catalogs/NAME
<BR>
We will use the path <TT>/var/lib/interchange/foundation</TT> in these examples.</UL>
<P><B>Interchange software directory</B></P>
<UL>
This is the main directory for your Interchange server, where the file <TT>interchange.cfg</TT> resides. Common locations:
<BR>
/usr/lib/interchange /usr/local/interchange $HOME/ic
<BR>
We will use the path <TT>/usr/lib/interchange</TT> in these examples.</UL>
<P><B>Interchange tarball directory</B></P>
<UL>
The quickbooks files are located in the untarred distribution file, before installation of Interchange is performed.</UL>
<P><B>Interchange User</B></P>
<UL>
The Interchange daemon runs as a user ID that cannot be root. It will require write permission on directories it must modify to do its work.
<BR>
We will use the user ID <TT>interch</TT> in these examples.</UL>
<H2><A NAME="Create and copy directories and files">4.2. Create and copy directories and files</A></H2>
<P>This extension requires you to add some files to your catalog.</P>
<P>It is assumed you have tools and knowledge to create directories with the proper permissions. Any directories that will contain varying files like order transaction logs will require write permission for the Interchange daemon user; pages and configuration only need have read permission.</P>
<H2><A NAME="Quick Installation Script">4.3. Quick Installation Script</A></H2>
<P>This script will install the necessary files for you, provided that you modify the variables to your environment. Alternately, you can follow the more detailed installation instructions that follow it.</P>
<PRE>
# Modify these three variables to match your environment.
export QB=/path/to/interchange/extensions/quickbooks
export VENDROOT=/usr/local/interchange
export CATROOT=/home/interch/catalogs/foundation
mkdir -p $CATROOT/include/menus $CATROOT/vars
cp -r $QB/TRANS_QUICKBOOKS \
$CATROOT/vars
cp -r $QB/pages/admin/quickbooks \
$CATROOT/pages/admin
cp -i $QB/usertag/* \
$VENDROOT/code/UI_Tag
# Variables that optionally modify the export process, along with
# their help entries.
cat $QB/variable.txt-additions >> \
$CATROOT/products/variable.txt
cat $QB/mv_metadata.asc-additions >> \
$CATROOT/products/mv_metadata.asc
# Menu entries: start with the existing menu, then add ours.
cp -i $VENDROOT/lib/UI/pages/include/menus/Admin.txt \
$CATROOT/include/menus
cat $QB/menus/Admin.txt.append >> \
$CATROOT/include/menus/Admin.txt
# Some configuration changes.
cat >> $CATROOT/catalog.cfg <<EOF
# Allows vars/TRANS_QUICKBOOKS
DirConfig vars
# You can remove these requires if you don't want to use the
# Quickbooks UI menu items
Require usertag import_quicken_items
Require usertag export_quicken_items
EOF
</PRE>
<H2><A NAME="Admin UI Usage">4.4. Admin UI Usage</A></H2>
<P>After successful installation, there should be a "Quickbooks" menu item under the "Admin" category. From there, you can "generate IIF files", download them, and perform other Quickbooks-related tasks.</P>
<P><B>Make orders directory</B></P>
<UL>
Create the directory <TT>orders</TT> in your Catalog Directory if it doesn't already exist. (It may be a symbolic link to another location.) It must have write permission on it.
<BR>
cd /var/lib/interchange/foundation mkdir orders
<BR>
If you are doing this as root, also do:
<BR>
chown interch orders
<BR>
This directory is used to store the QuickBooks IIF files produced for orders. The files are created with the form:
<BR>
qbYYYYMMDD.iif
<BR>
Each day will have a file, and when a day is complete you should download the orders. (There are other schemes possible.)</UL>
<P><B>Copy pages</B></P>
<UL>
You will want the Interchange UI support if you are using the UI. It provides links for importing/exporting items, downloading and viewing IIF files, and possibly other functions over time. At the UNIX command line:
<BR>
cd /usr/lib/interchange/ cp -r extensions/quickbooks/pages/admin/quickbooks \ /var/lib/interchange/foundation/pages</UL>
<P><B>Copy report generation file etc/trans_quickbooks</B></P>
<UL>
This file is used to generate the IIF file(s) for transaction import into QuickBooks.
<BR>
cd /usr/lib/interchange/ cp extensions/quickbooks/etc/trans_quickbooks \ /var/lib/interchange/foundation/etc</UL>
<P><B>Copy usertags</B></P>
<UL>
If you want to use the UI item import/export, two usertags are required. The easiest thing is just to copy them to the Interchange software directory subdirectory <TT>lib/UI/usertag</TT>, which is #included as a part of the UI configuration file.
<BR>
cd /usr/lib/interchange cp -i extensions/quickbooks/usertag/* lib/UI/usertag</UL>
<H2><A NAME="Additional database fields -- userdb">4.5. Additional database fields -- userdb</A></H2>
<P>If your catalog is not based on a 4.6+ version of the foundation catalog, you may not have some of the additional database fields necessary. If you want the user to retain their customer number, add the following field to the "userdb" table:</P>
<P>customer_number</P>
<P>It can be an integer number field if your database needs that information. To add the field in MySQL, you can issue the following queries at the mysql prompt:</P>
<P>alter table userdb add column customer_number int;</P>
<P>If you don't add it, it just means that a new customer number will be assigned every time.</P>
<P>WARNING If you are using Interchange DBM files and have live data it is not recommended you add this field unless you are positive you will not overwrite your data. If you are not a developer, get one to help you. In any case, back up your userdb.gdbm or userdb.db file first.</P>
<H2><A NAME="Additional database fields -- inventory">4.6. Additional database fields -- inventory</A></H2>
<P>Quicken also needs an account to debit for the split transactions it uses to track item sales. If you don't create these fields to relate to each SKU, the account "Other Income" will be used in the exports.</P>
<P>Add the following fields to the "inventory" table:</P>
<P>account cogs_account</P>
<P>To add the fields in MySQL, you can issue the following queries at the mysql prompt:</P>
<P>alter table inventory add column account char(20); alter table inventory add column cogs_account char(20);</P>
<P>Other SQL databases will have similar facilities.</P>
<P>If you are using Interchange DBM files, just export the inventory database, stop the Interchange server (to prevent corruption), add the fields on the first line by editing the <TT>inventory.txt</TT> file, then restart Interchange.</P>
<H2><A NAME="Modify catalog.cfg with additions:">4.7. Modify catalog.cfg with additions:</A></H2>
<P>Add the entries in qb.catalog.cfg to catalog.cfg (you can use an include statement if you wish).</P>
<P>There are some Require directives to ensure that the needed UserTag definitions are included in the catalog, as well as the Route which is used</P>
<H2><A NAME="Add quickbooks order route">4.8. Add quickbooks order route</A></H2>
<P>In the Interchange UI, there is a Preferences area "ORDER_ROUTES". You should add the <TT>quickbooks</TT> route. Place it after the transaction logging step, i.e.</P>
<P>code ORDER_ROUTES Variable log quickbooks main copy_user</P>
<P>ADVANCED, If you know Interchange Variable settings, you can add it directly:</P>
<P>Variable ORDER_ROUTES log quickbooks main copy_user</P>
<P>Also, you can use other methods to set order routes. See the Interchange reference documentation.</P>
<H2><A NAME="Additional Variables">4.9. Additional Variables</A></H2>
<P>Optionally, you may specify some variables that modify the behavior of the Quickbooks export feature. Documentation for these variables is provided via item-specific meta data, which can be added to your mv_metadata.asc file for automatic display by the Admin UI.</P>
<P>See the installation script at the top of this document for commands that will append the empty variables to your variable.txt and the help information to your mv_metadata.asc files.</P>
<H2><A NAME="Restart the catalog">4.10. Restart the catalog</A></H2>
<P>This can be done by restarting the Interchange server or by clicking <TT>Apply Changes</TT> in the UI.</P>
<H2><A NAME="Export the items">4.11. Export the items</A></H2>
<P>You can access the Quickbooks UI index by making your URL:</P>
<P><A HREF="http://YOURCATALOG_URL/admin/quickbooks/index">http://YOURCATALOG_URL/admin/quickbooks/index</A></P>
<P>It will provide options for importing and exporting items. This is necessary so QuickBooks will be able to take orders for your items.</P>
<P>QuickBooks uses the product "name" as an SKU, along with an integer reference number. Either you need to make your SKUs match the integer reference number, or you must ensure your product title is unique.</P>
<H2><A NAME="Test">4.12. Test</A></H2>
<P>Place a test order on your Interchange catalog once you have finished installing. You should find a file in the <TT>orders</TT> directory with the name <TT>qbYYYYMMDD.iif</TT>. (YYYY=year, MM=month, DD=day.) Transfer this file to your QuickBooks machine and run File/Import and select that file as the source. This should import the customer and order into the system. If it doesn't work, it may be due to lack of sales tax or shipping definitions, discussed below.</P>
<HR>
<H1><A NAME="Discussion">5. Discussion</A></H1>
<P>The interface provided works for the sample company data distributed with QuickBooks. There are certain requirements to make sure it works in your environment.</P>
<P>Also, you can change the configuration by editing the file etc/trans_quickbooks to suit your IIF file needs.</P>
<H2><A NAME="Sales Tax">5.1. Sales Tax</A></H2>
<P>QuickBooks has a taxing system whereby tax rates are defined by customer location. There is usually also a generic <TT>Sales Tax Item</TT>, such as contained in the sample company data. This allows Interchange to calculate the sales tax. If that item is not present then you will need to create it, or specify your tax item using the <TT>QB_SALES_TAX_ITEM</TT> variable.</P>
<H2><A NAME="Shipping">5.2. Shipping</A></H2>
<P>Interchange will add a generic item <TT>Shipping</TT> to each order that has a shipping cost. Its MEMO field will contain the text description of the mode. If that item is not in your QuickBooks item definitions, then you must create it, or specify your shipping item using the <TT>QB_SHIPPING_ITEM</TT> variable.</P>
<H2><A NAME="Customer Imports">5.3. Customer Imports</A></H2>
<P>To generate a QuickBooks transtype of INVOICE, a CUSTOMER is required. Interchange outputs a CUST IIF record for each sale with the customer information. Since QuickBooks uses the customer name or company to generate the unique listing, we place the Interchange username in parentheses after the company or name.</P>
<H2><A NAME="IIF generation at time of order">5.4. IIF generation at time of order</A></H2>
<P>As of 4.9, the IIF generation was moved from an order route into the Admin UI. This was done so that the IIF generation process could be fine tuned without restarting Interchange and placing an order. If you need the IIF file generated at the time of order, you can still access the pre-4.9.6 files in <TT>extensions/quickbooks/legacy</TT>.</P>
<HR>
<H1><A NAME="Credits">A. Credits</A></H1>
<UL>
<LI><B>Mike Heins</B>: This document was copied from the original POD documentation (<TT>extensions/quickbooks/ic_qb.pod</TT>) written by Mike Heins <A HREF="mailto:mike@perusion.com">mike@perusion.com</A>.
<LI><B>Dan Browning</B>: Updated by Dan Browning <A HREF="mailto:db@kavod.com">db@kavod.com</A>.</UL>
<HR>
<H1><A NAME="Document history">B. Document history</A></H1>
<UL>
<LI>July 20, 2002. Initial revision.</UL>
<HR>
<H1><A NAME="Resources">C. Resources</A></H1>
<H2><A NAME="Documentation">C.1. Documentation</A></H2>
<UL>
<LI>What are the IIF File Headers? <A HREF="http://www.quickbooks.com/support/faqs/qbw2000/121756.html">http://www.quickbooks.com/support/faqs/qbw2000/121756.html</A>
<LI>Also see the Quickbooks Help item, "Reference guide to import files"</UL>
<P>Copyright 2002 ICDEVGROUP. Freely redistributable under terms of the GNU General Public License.</P>
</DIV>
<DIV CLASS="footer">
<DIV CLASS="navigate">
<P ALIGN="Center"><A HREF="index.html" TARGET="_top">Catalog</A></P>
</DIV>
<HR>
<ADDRESS><SPAN CLASS="doc-id">ic_howto_qb-1.3</SPAN> <SPAN CLASS="doc-status">(Draft)</SPAN></ADDRESS>
<ADDRESS CLASS="doc-modified">4 June 2003</ADDRESS>
<ADDRESS CLASS="copyright">Copyright © 2001,2002 Mike Heins <<A HREF="mailto:mike@perusion.com">mike@perusion.com</A>>, Dan Browning <<A HREF="mailto:db@kavod.com">db@kavod.com</A>></ADDRESS>
</DIV>
</BODY>
</HTML>
1.1 interchange/extensions/quickbooks/README.txt
rev 1.1, prev_rev 1.0
Index: README.txt
===================================================================
Interchange + QuickBooks HOWTO
==============================
ic_howto_qb.1.3 (Draft)
1. Introduction
===============
1.1. Summary Description
------------------------
Interchange QuickBooks -- QuickBooks support for transactions and
items
1.2. Audience
-------------
Users who already have Quickbooks setup and are familiar with it, in
addition to having the foundation (or other) catalog correctly
working.
1.3. Contact the author
-----------------------
If you find any spelling errors, technical slip-ups, mistakes,
subliminal messages, or if you wish to send feedback, critique,
remarks, comments, or if you wish to contribute examples, instructions
for alternative platforms, chapters, or other material, please do so.
The preferred method of submitting changes is in the form of a context
diff against the SDF source file (ic_howto_qb.sdf). Please address
your correspondence to:
Volunteer Maintainer, Dan Browning db@kavod.com
or
Original Author, Mike Heins mike@perusion.com
1.4. Version
------------
This document describes software based on Interchange 4.5 and later.
2. Description
==============
Interchange is a business-to-business and business-to-consumer
internet ordering and cataloguing product. It has the ability to take
orders via the World Wide Web, and store transaction data.
This document describes how to interface Interchange with QuickBooks,
the popular small-business accounting program from Intuit.
QuickBooks has an import/export format called IIF, a mnemonic for
Intuit Interchange Format. Fitting, eh?
The standard capabilities of Interchange allow production of IIF files
for transaction passing. With some support from Interchange UserTags,
it can even import and export item listings.
3. Contents
===========
The extension files can be found in the Interchange tarball under the
'extensions/quickbooks' directory. The following files are used with
this extension:
usertag/import_quicken_items UserTag for importing items
usertag/export_quicken_items UserTag for exporting items
pages/admin/quickbooks/* Menu support for Interchange UI
qb.catalog.cfg Quickbooks configuration.
4. Installation
===============
To set up this extension, the basic steps are:
o Create and copy directories and files.
o Add additional database fields.
o Modify catalog.cfg with additions.
o Add "quickbooks" order route to checkout pages.
o Restart Interchange.
o Export your items from Interchange catalog (or import your
existing QuickBooks items to Interchange).
o Test.
4.1. Terms and locations
------------------------
Several terms are used in the examples.
Catalog Directory
This is the main directory for the catalog, where catalog.cfg
resides. It will have a NAME, the name for the catalog. (Some
common Interchange demo names are foundation, construct, barry,
and simple.)
Common locations:
/var/lib/interchange/NAME /usr/local/interchange/catalogs/NAME
$HOME/catalogs/NAME
We will use the path /var/lib/interchange/foundation in these
examples.
Interchange software directory
This is the main directory for your Interchange server, where the
file interchange.cfg resides. Common locations:
/usr/lib/interchange /usr/local/interchange $HOME/ic
We will use the path /usr/lib/interchange in these examples.
Interchange tarball directory
The quickbooks files are located in the untarred distribution
file, before installation of Interchange is performed.
Interchange User
The Interchange daemon runs as a user ID that cannot be root. It
will require write permission on directories it must modify to do
its work.
We will use the user ID interch in these examples.
4.2. Create and copy directories and files
------------------------------------------
This extension requires you to add some files to your catalog.
It is assumed you have tools and knowledge to create directories with
the proper permissions. Any directories that will contain varying
files like order transaction logs will require write permission for
the Interchange daemon user; pages and configuration only need have
read permission.
4.3. Quick Installation Script
------------------------------
This script will install the necessary files for you, provided that
you modify the variables to your environment. Alternately, you can
follow the more detailed installation instructions that follow it.
# Modify these three variables to match your environment.
export QB=/path/to/interchange/extensions/quickbooks
export VENDROOT=/usr/local/interchange
export CATROOT=/home/interch/catalogs/foundation
mkdir -p $CATROOT/include/menus $CATROOT/vars
cp -r $QB/TRANS_QUICKBOOKS \
$CATROOT/vars
cp -r $QB/pages/admin/quickbooks \
$CATROOT/pages/admin
cp -i $QB/usertag/* \
$VENDROOT/code/UI_Tag
# Variables that optionally modify the export process, along with
# their help entries.
cat $QB/variable.txt-additions >> \
$CATROOT/products/variable.txt
cat $QB/mv_metadata.asc-additions >> \
$CATROOT/products/mv_metadata.asc
# Menu entries: start with the existing menu, then add ours.
cp -i $VENDROOT/lib/UI/pages/include/menus/Admin.txt \
$CATROOT/include/menus
cat $QB/menus/Admin.txt.append >> \
$CATROOT/include/menus/Admin.txt
# Some configuration changes.
cat >> $CATROOT/catalog.cfg <<EOF
# Allows vars/TRANS_QUICKBOOKS
DirConfig vars
# You can remove these requires if you don't want to use the
# Quickbooks UI menu items
Require usertag import_quicken_items
Require usertag export_quicken_items
EOF
4.4. Admin UI Usage
-------------------
After successful installation, there should be a "Quickbooks" menu
item under the "Admin" category. From there, you can "generate IIF
files", download them, and perform other Quickbooks-related tasks.
Make orders directory
Create the directory orders in your Catalog Directory if it
doesn't already exist. (It may be a symbolic link to another
location.) It must have write permission on it.
cd /var/lib/interchange/foundation mkdir orders
If you are doing this as root, also do:
chown interch orders
This directory is used to store the QuickBooks IIF files produced
for orders. The files are created with the form:
qbYYYYMMDD.iif
Each day will have a file, and when a day is complete you should
download the orders. (There are other schemes possible.)
Copy pages
You will want the Interchange UI support if you are using the UI.
It provides links for importing/exporting items, downloading and
viewing IIF files, and possibly other functions over time. At the
UNIX command line:
cd /usr/lib/interchange/ cp -r
extensions/quickbooks/pages/admin/quickbooks \
/var/lib/interchange/foundation/pages
Copy report generation file etc/trans_quickbooks
This file is used to generate the IIF file(s) for transaction
import into QuickBooks.
cd /usr/lib/interchange/ cp
extensions/quickbooks/etc/trans_quickbooks \
/var/lib/interchange/foundation/etc
Copy usertags
If you want to use the UI item import/export, two usertags are
required. The easiest thing is just to copy them to the
Interchange software directory subdirectory lib/UI/usertag, which
is #included as a part of the UI configuration file.
cd /usr/lib/interchange cp -i extensions/quickbooks/usertag/*
lib/UI/usertag
4.5. Additional database fields -- userdb
-----------------------------------------
If your catalog is not based on a 4.6+ version of the foundation
catalog, you may not have some of the additional database fields
necessary. If you want the user to retain their customer number, add
the following field to the "userdb" table:
customer_number
It can be an integer number field if your database needs that
information. To add the field in MySQL, you can issue the following
queries at the mysql prompt:
alter table userdb add column customer_number int;
If you don't add it, it just means that a new customer number will be
assigned every time.
WARNING If you are using Interchange DBM files and have live data it
is not recommended you add this field unless you are positive you will
not overwrite your data. If you are not a developer, get one to help
you. In any case, back up your userdb.gdbm or userdb.db file first.
4.6. Additional database fields -- inventory
--------------------------------------------
Quicken also needs an account to debit for the split transactions it
uses to track item sales. If you don't create these fields to relate
to each SKU, the account "Other Income" will be used in the exports.
Add the following fields to the "inventory" table:
account cogs_account
To add the fields in MySQL, you can issue the following queries at the
mysql prompt:
alter table inventory add column account char(20); alter table
inventory add column cogs_account char(20);
Other SQL databases will have similar facilities.
If you are using Interchange DBM files, just export the inventory
database, stop the Interchange server (to prevent corruption), add the
fields on the first line by editing the inventory.txt file, then
restart Interchange.
4.7. Modify catalog.cfg with additions:
---------------------------------------
Add the entries in qb.catalog.cfg to catalog.cfg (you can use an
include statement if you wish).
There are some Require directives to ensure that the needed UserTag
definitions are included in the catalog, as well as the Route which is
used
4.8. Add quickbooks order route
-------------------------------
In the Interchange UI, there is a Preferences area "ORDER_ROUTES". You
should add the quickbooks route. Place it after the transaction
logging step, i.e.
code ORDER_ROUTES Variable log quickbooks main copy_user
ADVANCED, If you know Interchange Variable settings, you can add it
directly:
Variable ORDER_ROUTES log quickbooks main copy_user
Also, you can use other methods to set order routes. See the
Interchange reference documentation.
4.9. Additional Variables
-------------------------
Optionally, you may specify some variables that modify the behavior of
the Quickbooks export feature. Documentation for these variables is
provided via item-specific meta data, which can be added to your
mv_metadata.asc file for automatic display by the Admin UI.
See the installation script at the top of this document for commands
that will append the empty variables to your variable.txt and the help
information to your mv_metadata.asc files.
4.10. Restart the catalog
-------------------------
This can be done by restarting the Interchange server or by clicking
Apply Changes in the UI.
4.11. Export the items
----------------------
You can access the Quickbooks UI index by making your URL:
http://YOURCATALOG_URL/admin/quickbooks/index
It will provide options for importing and exporting items. This is
necessary so QuickBooks will be able to take orders for your items.
QuickBooks uses the product "name" as an SKU, along with an integer
reference number. Either you need to make your SKUs match the integer
reference number, or you must ensure your product title is unique.
4.12. Test
----------
Place a test order on your Interchange catalog once you have finished
installing. You should find a file in the orders directory with the
name qbYYYYMMDD.iif. (YYYY=year, MM=month, DD=day.) Transfer this file
to your QuickBooks machine and run File/Import and select that file as
the source. This should import the customer and order into the system.
If it doesn't work, it may be due to lack of sales tax or shipping
definitions, discussed below.
5. Discussion
=============
The interface provided works for the sample company data distributed
with QuickBooks. There are certain requirements to make sure it works
in your environment.
Also, you can change the configuration by editing the file
etc/trans_quickbooks to suit your IIF file needs.
5.1. Sales Tax
--------------
QuickBooks has a taxing system whereby tax rates are defined by
customer location. There is usually also a generic Sales Tax Item,
such as contained in the sample company data. This allows Interchange
to calculate the sales tax. If that item is not present then you will
need to create it, or specify your tax item using the
QB_SALES_TAX_ITEM variable.
5.2. Shipping
-------------
Interchange will add a generic item Shipping to each order that has a
shipping cost. Its MEMO field will contain the text description of the
mode. If that item is not in your QuickBooks item definitions, then
you must create it, or specify your shipping item using the
QB_SHIPPING_ITEM variable.
5.3. Customer Imports
---------------------
To generate a QuickBooks transtype of INVOICE, a CUSTOMER is required.
Interchange outputs a CUST IIF record for each sale with the customer
information. Since QuickBooks uses the customer name or company to
generate the unique listing, we place the Interchange username in
parentheses after the company or name.
5.4. IIF generation at time of order
------------------------------------
As of 4.9, the IIF generation was moved from an order route into the
Admin UI. This was done so that the IIF generation process could be
fine tuned without restarting Interchange and placing an order. If
you need the IIF file generated at the time of order, you can still
access the pre-4.9.6 files in extensions/quickbooks/legacy.
A. Credits
==========
o Mike Heins: This document was copied from the original POD
documentation (extensions/quickbooks/ic_qb.pod) written by Mike
Heins mike@perusion.com.
o Dan Browning: Updated by Dan Browning db@kavod.com.
B. Document history
===================
o July 20, 2002. Initial revision.
C. Resources
============
C.1. Documentation
------------------
o What are the IIF File Headers?
http://www.quickbooks.com/support/faqs/qbw2000/121756.html
o Also see the Quickbooks Help item, "Reference guide to import
files"
Copyright 2002 ICDEVGROUP. Freely redistributable under terms of the
GNU General Public License.
1.1 interchange/extensions/quickbooks/legacy/qb.catalog.cfg
rev 1.1, prev_rev 1.0
Index: qb.catalog.cfg
===================================================================
# You can remove these requires if you don't want to use the
# Quickbooks UI menu items
Require usertag import_quicken_items
Require usertag export_quicken_items
# This route simply calls the file etc/trans_quickbooks and
# runs it
Route quickbooks attach 0
Route quickbooks cybermode ""
Route quickbooks empty 1
Route quickbooks encrypt 0
Route quickbooks increment 0
Route quickbooks report etc/trans_quickbooks
Route quickbooks supplant 0
Route quickbooks track logs/log
### THIS ENTRY SHOULD REPLACE EXISTING Route default cascade
Route default cascade "log quickbooks main copy_user"
1.1 interchange/extensions/quickbooks/legacy/trans_quickbooks
rev 1.1, prev_rev 1.0
Index: trans_quickbooks
===================================================================
#### begin quickbooks for [value mv_order_number] #####
[tmp quickbooks_record]
[if type=explicit compare="[salestax noformat=1]"]
[set NAMEISTAXABLE]Y[/set]
[set ISTAXABLE]1[/set]
[set NAME]Invoice[/set]
[else]
[set NAMEISTAXABLE]N[/set]
[set ISTAXABLE]0[/set]
[set NAME]Invoice with Sales Tax[/set]
[/else]
[/if]
Setting salestax: ISTAXABLE=[scratch ISTAXABLE]
[if type=explicit compare="[shipping noformat=1]"]
[set HASSHIPPING]1[/set]
[else]
[set HASSHIPPING]0[/set]
[/else]
[/if]
Checking shipping: HASSHIPPING=[scratch HASSHIPPING]
[if value b_address1]
[then]
[seti ADDR1][if value b_fname][value b_fname] [value b_lname][else][value fname] [value lname][/else][/if][/seti]
[seti ADDR2][if value company][value company][/if][/seti]
[seti ADDR3][value b_address1][if value b_address2], [value b_address2][/if][/seti]
[seti ADDR4][value b_city], [value b_state] [value b_zip][/seti]
[seti ADDR5][if value b_country][if !value country =~ /^US.*/]INTL: [data table=country col=name key="[value b_country]"][/if][/if][/seti]
[seti SADDR1][value fname] [value lname][/seti]
[seti SADDR2][if value company][value company][/if][/seti]
[seti SADDR3][value address1][if value address2], [value address2][/if][/seti]
[seti SADDR4][value city], [value state] [value zip][/seti]
[seti SADDR5][if value country][if !value country =~ /^US.*/]INTL: [data table=country col=name key="[value country]"][/if][/if][/seti]
[/then]
[else]
[seti ADDR1][value fname] [value lname][/seti]
[seti ADDR2][if value company][value company][/if][/seti]
[seti ADDR3][value address1][if value address2], [value address2][/if][/seti]
[seti ADDR4][value city], [value state] [value zip][/seti]
[seti ADDR5][if value country][if !value country =~ /^US.*/]INTL: [data table=country col=name key="[value country]"][/if][/if][/seti]
[seti SADDR1][value fname] [value lname][/seti]
[seti SADDR2][if value company][value company][/if][/seti]
[seti SADDR3][value address1][if value address2], [value address2][/if][/seti]
[seti SADDR4][value city], [value state] [value zip][/seti]
[seti SADDR5][if value country][if !value country =~ /^US.*/]INTL: [data table=country col=name key="[value country]"][/if][/if][/seti]
[/else]
[/if]
[seti CUSTNAME][either][value company][or][value lname], [value fname][/either] ([either][scratch auto_username][or][data session username][/either])[/seti]
[set TRNSTYPE]INVOICE[/set]
[seti DATE][tag time]%m/%d/%y[/tag][/seti]
[seti FILEDATE][tag time]%Y%m%d[/tag][/seti]
Setting customer number: [if !value customer_number][value name=customer_number set="[fcounter etc/qbcust.number]"][userdb save][/if]
[seti qb_trans]!CUST NAME REFNUM TIMESTAMP EMAIL BADDR1 BADDR2 BADDR3 BADDR4 BADDR5 SADDR1 SADDR2 SADDR3 SADDR4 SADDR5 PHONE1 PHONE2 FAXNUM NOTE CONT1 CONT2 CTYPE TERMS TAXABLE LIMIT RESALENUM REP TAXITEM NOTEPAD SALUTATION COMPANYNAME FIRSTNAME MIDINIT LASTNAME CUSTFLD1 CUSTFLD2 CUSTFLD3 CUSTFLD4 CUSTFLD5 CUSTFLD6 CUSTFLD7 CUSTFLD8 CUSTFLD9 CUSTFLD10 CUSTFLD11 CUSTFLD12 CUSTFLD13 CUSTFLD14 CUSTFLD15 JOBDESC JOBTYPE JOBSTATUS JOBSTART JOBPROJEND JOBEND HIDDEN DELCOUNT
[filter tabbed]CUST
[scratch CUSTNAME]
[value customer_number]
[tag time]%s[/tag]
[value email]
[scratch ADDR1]
[scratch ADDR2]
[scratch ADDR3]
[scratch ADDR4]
[scratch ADDR5]
[scratch SADDR1]
[scratch SADDR2]
[scratch SADDR3]
[scratch SADDR4]
[scratch SADDR5]
[value name=phone_day filter=mac]
[value name=phone_night filter=mac]
[value name=fax filter=mac]
[value name=gift_note filter=mac]
[value fname] [value lname]
[value b_fname] [value b_lname]
Retail
Cash
[if value ISTAXABLE]Y[else]N[/else][/if]
[scratch credit_limit]
[scratch resale_number]
[value affiliate]
[if value ISTAXABLE][value state][else][/else][/if]
[value company]
[value fname]
[comment]MIDINIT[/comment]
[value lname]
[comment]CUSTFLD1[/comment]
[value email][comment]CUSTFLD2[/comment]
[comment]CUSTFLD3[/comment]
[value email]
[comment]CUSTFLD5[/comment]
[comment]CUSTFLD6[/comment]
[comment]CUSTFLD7[/comment]
[comment]CUSTFLD8[/comment]
[comment]CUSTFLD9[/comment]
[comment]CUSTFLD10[/comment]
[comment]CUSTFLD11[/comment]
[comment]CUSTFLD12[/comment]
[comment]CUSTFLD13[/comment]
[comment]CUSTFLD14[/comment]
[comment]CUSTFLD15[/comment]
[comment]JOBDESC[/comment]
[comment]JOBDESC[/comment]
[comment]JOBDESC[/comment]
[comment]JOBDESC[/comment]
[comment]JOBDESC[/comment]
[comment]JOBDESC[/comment]
N
__STORE_ID__[/filter]
!TRNS TRNSID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT [if !var QB_DISABLE_DOCNUM]DOCNUM[/if] MEMO CLEAR TOPRINT NAMEISTAXABLE ADDR1 ADDR2 ADDR3 ADDR4 ADDR5 SADDR1 SADDR2 SADDR3 SADDR4 SADDR5
!SPL SPLID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR QNTY PRICE INVITEM PAYMETH TAXABLE VALADJ SERVICEDATE OTHER2 EXTRA
!ENDTRNS
[filter tabbed]TRNS
[value mv_order_number]
[scratch TRNSTYPE]
[scratch DATE]
Accounts Receivable
[scratch CUSTNAME]
[total-cost noformat=1]
[if !var QB_DISABLE_DOCNUM][value mv_order_number][/if]
[value name=gift_note filter=mac] - Order Number: [value mv_order_number]
N
N
[scratch NAMEISTAXABLE]
[scratch ADDR1]
[scratch ADDR2]
[scratch ADDR3]
[scratch ADDR4]
[scratch ADDR5]
[scratch SADDR1]
[scratch SADDR2]
[scratch SADDR3]
[scratch SADDR4]
[scratch SADDR5][/filter]
[item-list][filter tabbed]SPL
[fcounter etc/qbline.number]
[scratch TRNSTYPE]
[scratch DATE]
[either][item-data inventory account][or]__QB_DEFAULT_ACCOUNT__[or]Other Income[/either]
[scratch CUSTNAME]
-[item-subtotal noformat]
[value mv_order_number]
[item-description][if-item-modifier size] [item-modifier size][/if-item-modifier][if-item-modifier color] [item-modifier color][/if-item-modifier]
N
-[item-quantity]
[item-price noformat=1]
[seti DESC][filter
interpolate=1
op="strip mac __QB_ITEM_LENGTH__ 30"
]
[either]
__QB_ITEM_PREFIX__[item-field title]
[or]
__QB_ITEM_PREFIX__[item-description]
[/either]
[/filter][/seti][scratch DESC]
[if-item-field nontaxable]N[else][scratch NAMEISTAXABLE][/else][/if-item-field]
N
[/filter]
[/item-list][if scratch HASSHIPPING][filter tabbed]SPL
[fcounter etc/qbline.number]
[scratch TRNSTYPE]
[scratch DATE]
[either]__QB_SHIPPING_ACCOUNT__[or]Other Income[/either]
[scratch CUSTNAME]
-[shipping noformat=1]
[value mv_order_number]
[shipping-desc]
N
-1
[shipping noformat=1]
[either]__QB_SHIPPING_ITEM__[or]Shipping[/either]
N
N
[/filter]
[/if][filter tabbed]SPL
[fcounter etc/qbline.number]
[scratch TRNSTYPE]
[scratch DATE]
[either]__QB_SALES_TAX_ACCOUNT__[or]Sales Tax Payable[/either]
[either]__QB_SALES_TAX_AGENCY__[or]State Board of Equalization[/either]
-[salestax noformat=1]
[value mv_order_number]
[value state]
N
-1
[either]__QB_SALES_TAX_ITEM__[or]Sales Tax Item[/either]
N
N
AUTOSTAX[/filter]
ENDTRNS
[/seti]
[calc]
$Scratch->{qb_trans} =~ s/\r?\n/\r\n/g;
$Scratch->{qb_trans} =~ s/\s*$/\r\n/;
return;
[/calc]
Logging transaction to orders/qb[either]__QB_FILE_SUFFIX__[or][scratch FILEDATE][/either].iif: [tag op=log umask=022 file="orders/qb[value mv_order_number].iif" type=text interpolate=1][scratch qb_trans]
[/tag]
[calc]
for (qw/
ADDR1
ADDR2
ADDR3
ADDR4
ADDR5
CUSTNAME
DATE
FILEDATE
HASSHIPPING
ISTAXABLE
NAME
NAMEISTAXABLE
SADDR1
SADDR2
SADDR3
SADDR4
SADDR5
TRNSTYPE
qb_trans
/)
{
delete $Scratch->{$_};
}
return;
[/calc]
[/tmp][perl]
my $out = $Scratch->{quickbooks_record};
$out =~ s/^\s+//mg;
$out =~ s/\s+$//mg;
$out =~ s/[\r\n]+/\n/;
$out =~ s/:\n(1|yes|succe\w+|fail\w+)\n/: $1\n/g;
return $out;
[/perl]
#### end quickbooks for [value mv_order_number] #####
1.1 interchange/extensions/quickbooks/menus/Admin.txt.append
rev 1.1, prev_rev 1.0
Index: Admin.txt.append
===================================================================
030 Admin x030 admin/quickbooks/index Quickbooks 0
2.1 +18 -5 interchange/extensions/quickbooks/pages/admin/quickbooks/index.html
rev 2.1, prev_rev 2.0
Index: index.html
===================================================================
RCS file: /var/cvs/interchange/extensions/quickbooks/pages/admin/quickbooks/index.html,v
retrieving revision 2.0
retrieving revision 2.1
diff -u -r2.0 -r2.1
--- index.html 18 Jul 2001 02:22:52 -0000 2.0
+++ index.html 4 Jun 2003 09:23:29 -0000 2.1
@@ -13,6 +13,7 @@
<td bgcolor=__UI_C_TOPBLOCKBAR__><img src="bg.gif" width=__UI_MAIN_WIDTH__ height=1></td>
</tr>
+
[if-mm advanced items]
<tr><td align=left bgcolor=__UI_C_INTBLOCK__>
<font size="+1" face="arial,helvetica,sans-serif">
@@ -37,13 +38,25 @@
</td></tr>
[/if-mm]
-[if-mm advanced orders]
<tr><td align=left bgcolor=__UI_C_INTBLOCK__>
-<font size="+1" face="arial,helvetica,sans-serif">
-<a href="[area __UI_BASE__/quickbooks/import_items]">Download new orders</a>
-</font>
+ <font size="+1" face="arial,helvetica,sans-serif">
+ <a href="[area __UI_BASE__/quickbooks/generate_new_iif]">Generate new orders</a>
+ </font>
</td></tr>
-[/if-mm]
+
+<tr><td align=left bgcolor=__UI_C_INTBLOCK__>
+ <font size="+1" face="arial,helvetica,sans-serif">
+ [page
+ href="__UI_BASE__/file_transfer"
+ form="
+ action=chdir
+ dir=orders/new
+ "
+ ]Download generated orders</a>
+ </font>
+</td></tr>
+
+
<tr>
<td bgcolor=__UI_C_TOPBLOCKBAR__><img src="bg.gif" width=__UI_MAIN_WIDTH__ height=1></td>
2.1 interchange/extensions/quickbooks/pages/admin/quickbooks/generate_new_iif.html
rev 2.1, prev_rev 2.0
1.1 interchange/extensions/quickbooks/products/mv_metadata.asc.append
rev 1.1, prev_rev 1.0
Index: mv_metadata.asc.append
===================================================================
variable::Variable::QB_DISABLE_DOCNUM yesno <b>Yes</b> will cause the invoice number (AKA DOCNUM & Document Number) to be blank. No by default. <b>No</b> will cause etc/trans_quickbooks the use of the Interchange order number as the Quickbooks invoice number. Some prefer to have the number left blank so as not to cause Quickbooks to change its auto-count.
variable::Variable::QB_ITEM_PREFIX text A prefix applied to all product names (the unique identifier in Quickbooks). Empty by default. For example, some Quickbooks users may have a category for all the products sold on their website such as "E-Commerce Products:" (the colon is a category deliniator for Quickbooks). Other Quickbooks users may use the same category scheme in in their Interchange store as they do in their Quickbooks item lists, in which case something to this effect would be applicable: "[item-field category]:"
variable::Variable::QB_ITEM_LENGTH text The item name maximum length for product line items (any characters past this length will be truncated). 30 by default. Quickbooks versions 6.0 and 99 may have a problem with item names that are longer than 30 characters; however, lengths of 45 and longer have been known to work on Quickbooks 2000 and later version. Please report any compatibility problems/information to the interchange-users@icdevgroup.org mailing list.
variable::Variable::QB_FILE_SUFFIX text If left blank, the current date will be used and each order will be appended to the file. <BR><BR>Another option is to use [value mv_order_number] to cause the creation of a separate file for each order.
variable::Variable::QB_SHIPPING_ACCOUNT text The income account associated with shipping. "Other Income" by default.
variable::Variable::QB_SHIPPING_ITEM text The item name given for the shipping. "Shipping" by default.
variable::Variable::QB_DEFAULT_ACCOUNT text The income account associated with each product line item. "Other Income" by default; however, the highest precence is 'account' field in the 'inventory' table.
variable::Variable::QB_SALES_TAX_ACCOUNT text The liability account associated with sales tax. "Sales Tax Payable" by default.
variable::Variable::QB_SALES_TAX_AGENCY text The name of the sales tax agency for your locality. "State Board of Equalization" by default (the name given in the Quickbooks 2000 sample products based business).
variable::Variable::QB_SALES_TAX_ITEM text The item name (Quickbooks unique identifier) for the sales tax. "Sales Tax Item" by default (from Quickbooks 2000 sample products based business).
1.1 interchange/extensions/quickbooks/products/variable.txt.append
rev 1.1, prev_rev 1.0
Index: variable.txt.append
===================================================================
QB_TRANSACTION_TYPE Quickbooks
QB_TRANSACTION_ACCOUNT Quickbooks
QB_INCOME_ACCOUNT Quickbooks
QB_DISABLE_DOCNUM Quickbooks
QB_ITEM_PREFIX Quickbooks
QB_ITEM_LENGTH Quickbooks
QB_FILE_SUFFIX Quickbooks
QB_SHIPPING_ACCOUNT Quickbooks
QB_SHIPPING_ITEM Quickbooks
QB_SALES_TAX_ACCOUNT Quickbooks
QB_SALES_TAX_AGENCY Quickbooks
QB_SALES_TAX_ITEM Quickbooks
QB_TRANSACTION_CLASS Quickbooks
1.1 interchange/extensions/quickbooks/vars/TRANS_QUICKBOOKS
rev 1.1, prev_rev 1.0
Index: TRANS_QUICKBOOKS
===================================================================
#### begin quickbooks for [tx-param order_number] #####
[tmp quickbooks_record]
[if-tx-param salestax]
[set NAMEISTAXABLE]Y[/set]
[set ISTAXABLE]1[/set]
[set NAME]Invoice[/set]
[else]
[set NAMEISTAXABLE]N[/set]
[set ISTAXABLE]0[/set]
[set NAME]Invoice with Sales Tax[/set]
[/else]
[/if-tx-param]
Setting salestax: ISTAXABLE=[scratch ISTAXABLE]
[if-tx-param shipping]
[set HASSHIPPING]1[/set]
[else]
[set HASSHIPPING]0[/set]
[/else]
[/if-tx-param]
Checking shipping: HASSHIPPING=[scratch HASSHIPPING]
[if-tx-param b_address1] [comment]User put in billing info, use that[/comment]
[seti ADDR1][if-tx-param b_fname][tx-param b_fname] [tx-param b_lname][else][tx-param fname] [tx-param lname][/else][/if-tx-param][/seti]
[seti ADDR2][if-tx-param company][tx-param company][/if-tx-param][/seti]
[seti ADDR3][tx-param b_address1][if-tx-param b_address2], [tx-param b_address2][/if-tx-param][/seti]
[seti ADDR4][tx-param b_city], [tx-param b_state] [tx-param b_zip][/seti]
[seti ADDR5][tx-param b_country][/seti]
[seti SADDR1][tx-param fname] [tx-param lname][/seti]
[seti SADDR2][if-tx-param company][tx-param company][/if-tx-param][/seti]
[seti SADDR3][tx-param address1][if-tx-param address2], [tx-param address2][/if-tx-param][/seti]
[seti SADDR4][tx-param city], [tx-param state] [tx-param zip][/seti]
[seti SADDR5][tx-param country][/seti]
[else]
[seti ADDR1][tx-param fname] [tx-param lname][/seti]
[seti ADDR2][if-tx-param company][tx-param company][/if-tx-param][/seti]
[seti ADDR3][tx-param address1][if-tx-param address2], [tx-param address2][/if-tx-param][/seti]
[seti ADDR4][tx-param city], [tx-param state] [tx-param zip][/seti]
[seti ADDR5][tx-param country][/seti]
[seti SADDR1][tx-param fname] [tx-param lname][/seti]
[seti SADDR2][if-tx-param company][tx-param company][/if-tx-param][/seti]
[seti SADDR3][tx-param address1][if-tx-param address2], [tx-param address2][/if-tx-param][/seti]
[seti SADDR4][tx-param city], [tx-param state] [tx-param zip][/seti]
[seti SADDR5][tx-param country][/seti]
[/else]
[/if-tx-param]
[if variable FILE_BY_COMPANY]
[seti CUSTNAME][tx-param company] ([tx-param username])[/seti]
[else]
[seti CUSTNAME][tx-param lname], [tx-param fname] ([tx-param username])[/seti]
[/else]
[/if]
[seti TRNSTYPE][either]__QB_TRANSACTION_TYPE__[or]CASH SALE[/either][/seti]
[seti DATE][convert-date format="%m/%d/%y"][tx-param order_ymd][/convert-date][/seti]
[seti FILEDATE][tag time]%Y%m%d[/tag][/seti]
[seti qb_trans]!CUST NAME REFNUM TIMESTAMP EMAIL BADDR1 BADDR2 BADDR3 BADDR4 BADDR5 SADDR1 SADDR2 SADDR3 SADDR4 SADDR5 PHONE1 PHONE2 FAXNUM NOTE CONT1 CONT2 CTYPE TERMS TAXABLE LIMIT RESALENUM REP TAXITEM NOTEPAD SALUTATION COMPANYNAME FIRSTNAME MIDINIT LASTNAME CUSTFLD1 CUSTFLD2 CUSTFLD3 CUSTFLD4 CUSTFLD5 CUSTFLD6 CUSTFLD7 CUSTFLD8 CUSTFLD9 CUSTFLD10 CUSTFLD11 CUSTFLD12 CUSTFLD13 CUSTFLD14 CUSTFLD15 JOBDESC JOBTYPE JOBSTATUS JOBSTART JOBPROJEND JOBEND HIDDEN DELCOUNT
[filter tabbed]CUST
[scratch CUSTNAME]
[tx-param order_number][comment][data table=userdb field=customer_number key="[tx-param username]"][/comment][comment]Customer number... just use order number?[/comment]
[tag time]%s[/tag]
[tx-param email]
[scratch ADDR1]
[scratch ADDR2]
[scratch ADDR3]
[scratch ADDR4]
[scratch ADDR5]
[scratch SADDR1]
[scratch SADDR2]
[scratch SADDR3]
[scratch SADDR4]
[scratch SADDR5]
[filter op=mac interpoloate=1][tx-param phone_day][/filter]
[filter op=mac interpoloate=1][tx-param phone_night][/filter]
[filter op=mac interpoloate=1][tx-param fax][/filter]
[filter op=mac interpoloate=1][tx-param gift_note][/filter]
[tx-param fname] [tx-param lname]
[tx-param b_fname] [tx-param b_lname]
Retail
Cash
[if scratch ISTAXABLE]Y[else]N[/else][/if]
[scratch credit_limit]
[scratch resale_number]
[tx-param affiliate]
[if scratch ISTAXABLE][tx-param state][/if]
[tx-param company]
[tx-param fname]
[comment]MIDINIT[/comment]
[tx-param lname]
[comment]CUSTFLD1[/comment]
[tx-param email][comment]CUSTFLD2[/comment]
[comment]CUSTFLD3[/comment]
[tx-param email]
[comment]CUSTFLD5[/comment]
[comment]CUSTFLD6[/comment]
[comment]CUSTFLD7[/comment]
[comment]CUSTFLD8[/comment]
[comment]CUSTFLD9[/comment]
[comment]CUSTFLD10[/comment]
[comment]CUSTFLD11[/comment]
[comment]CUSTFLD12[/comment]
[comment]CUSTFLD13[/comment]
[comment]CUSTFLD14[/comment]
[comment]CUSTFLD15[/comment]
[comment]JOBDESC[/comment]
[comment]JOBDESC[/comment]
[comment]JOBDESC[/comment]
[comment]JOBDESC[/comment]
[comment]JOBDESC[/comment]
[comment]JOBDESC[/comment]
N
__STORE_ID__[/filter]
!TRNS TRNSID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT [if !variable QB_DISABLE_DOCNUM]DOCNUM [/if]MEMO CLEAR TOPRINT NAMEISTAXABLE ADDR1 ADDR2 ADDR3 ADDR4 ADDR5 PAYMETH SHIPVIA SADDR1 SADDR2 SADDR3 SADDR4 SADDR5
!SPL SPLID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR QNTY PRICE INVITEM PAYMETH TAXABLE VALADJ SERVICEDATE OTHER2 EXTRA
!ENDTRNS
[filter tabbed]TRNS
[tx-param order_number]
[scratch TRNSTYPE]
[scratch DATE]
[either]__QB_TRANSACTION_ACCOUNT__[or]Checking[/either]
[scratch CUSTNAME]
__QB_TRANSACTION_CLASS__
[tx-param total_cost][calc] return "\t" . q{[tx-param order_number]} unless $Variable->{QB_DISABLE_DOCNUM}; [/calc]
Order Number: [tx-param order_number]. [filter op=mac interpoloate=1][tx-param gift_note][/filter]
N
N
[scratch NAMEISTAXABLE]
[scratch ADDR1]
[scratch ADDR2]
[scratch ADDR3]
[scratch ADDR4]
[scratch ADDR5]
[filter op=14 interpolate=1][calc]$_ = $p = q{[tx-param payment_method]}; ($_) = /\((.*)\)/; $_ = uc($_); $_ || $p;[/calc][/filter]
[filter op=14 interpolate=1][tx-param shipmode][/filter]
[scratch SADDR1]
[scratch SADDR2]
[scratch SADDR3]
[scratch SADDR4]
[scratch SADDR5][/filter][comment]end [filter tabbed] for TRNS[/comment][loop prefix=ol search="
fi=orderline
sf=order_number
op=eq
se='[tx-param order_number]'
rf=*
"]
SPL [filter tabbed][fcounter etc/qbline.number]
[scratch TRNSTYPE]
[scratch DATE]
[either][data table=inventory field=account key="[ol-param sku]"][or]__QB_INCOME_ACCOUNT__[or]Other Income[/either]
[scratch CUSTNAME]
__QB_TRANSACTION_CLASS__
-[ol-param subtotal]
[ol-param order_number]
[seti DESC][filter
interpolate=1
op="strip mac __QB_ITEM_LENGTH__ 45"
]
[tmp qb_item_prefix]__QB_ITEM_PREFIX__[/tmp]
[filter op=qb_safe interpolate=1][scratch qb_item_prefix][either][ol-param title][or][ol-param description][/either][/filter]
[/filter][/seti][scratch DESC]
N
-[ol-param quantity]
[ol-param price]
[filter op="qb_safe 25" interpolate=1][ol-param sku][/filter]
[if-ol-param nontaxable]N[else][scratch NAMEISTAXABLE][/else][/if-ol-param]
N
[/filter][/loop][comment]/end [/loop][/comment][if scratch HASSHIPPING]
SPL[filter tabbed]
[fcounter etc/qbline.number]
[scratch TRNSTYPE]
[scratch DATE]
[either]__QB_SHIPPING_ACCOUNT__[or]__QB_INCOME_ACCOUNT__[or]Other Income[/either]
[scratch CUSTNAME]
-[tx-param shipping]
[tx-param order_number]
[tx-param shipmode]
N
-1
[tx-param shipping]
[either]__QB_SHIPPING_ITEM__[or]Shipping[/either]
N
N
[/filter]
[/if][filter tabbed]SPL
[fcounter etc/qbline.number]
[scratch TRNSTYPE]
[scratch DATE]
[either]__QB_SALES_TAX_ACCOUNT__[or]Sales Tax Payable[/either]
[either][query list=1 sql=|SELECT qb_tax_agency FROM state WHERE country = '[tx-param country]' AND state = '[tx-param state]'|][sql-param qb_tax_agency][/query][or]__QB_SALES_TAX_AGENCY__[or]State Board of Equalization[/either]
-[tx-param salestax]
[tx-param order_number]
[tx-param state]
N
-1
[tmp qb_sales_tax_item]__QB_SALES_TAX_ITEM__[/tmp][either][scratch qb_sales_tax_item][or]Sales Tax Item[/either]
N
N
AUTOSTAX[/filter]
ENDTRNS
[/seti]
[calc]
$Scratch->{qb_trans} =~ s/\r?\n/\r\n/g;
$Scratch->{qb_trans} =~ s/\s*$/\r\n/;
return;
[/calc]
Logging transaction to orders/qb[either]__QB_FILE_SUFFIX__[or][scratch FILEDATE][/either].iif: [log umask=022 file=">orders/qb[tx-param order_number].iif" type=text interpolate=1][scratch qb_trans][/log]
Also appending transaction to orders/0_qb_results.iif: [log umask=022 file="orders/0_qb_results.iif" type=text interpolate=1][scratch qb_trans][/log]
[scratch qb_trans]
[calc]
for (qw/
ADDR1
ADDR2
ADDR3
ADDR4
ADDR5
CUSTNAME
DATE
FILEDATE
HASSHIPPING
ISTAXABLE
NAME
NAMEISTAXABLE
SADDR1
SADDR2
SADDR3
SADDR4
SADDR5
TRNSTYPE
qb_trans
/)
{
delete $Scratch->{$_};
}
return;
[/calc]
[/tmp][perl]
my $out = $Scratch->{quickbooks_record};
$out =~ s/^\s+//mg;
$out =~ s/\s+$//mg;
$out =~ s/[\r\n]+/\n/;
$out =~ s/:\n(1|yes|succe\w+|fail\w+)\n/: $1\n/g;
return $out;
[/perl]
#### end quickbooks for [tx-param order_number] #####