ic_howto_qb.sdf

!init OPT_LOOK="icdevgroup"; OPT_STYLE="manual"
# $Id: ic_howto_qb.sdf,v 1.6 2004-05-05 15:22:30 jon Exp $

!define DOC_NAME "Interchange + QuickBooks HOWTO"
!define DOC_TYPE ""
!define DOC_CODE "ic_howto_qb"
!define DOC_VERSION substr('$Revision: 1.6 $', 11, -2)
!define DOC_STATUS "Draft"
!define DOC_PROJECT "Interchange"
!define DOC_URL "http://www.icdevgroup.org/doc/ic_howto_qb.html"
!define DOC_OWNER "Mike Heins E<lt>{{EMAIL:mike@perusion.com}}E<gt>, Dan Browning E<lt>{{EMAIL:db@kavod.com}}E<gt>"
!build_title

H1:Introduction

H2: Summary Description

Interchange QuickBooks -- QuickBooks support for transactions and items

H2:Audience

N:Users who already have Quickbooks setup and are familiar with it, in addition
to having the foundation (or other) catalog correctly working.

H2:Contact the author

N: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.       

N: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:

N:Volunteer Maintainer, Dan Browning {{EMAIL:db@kavod.com}}

or 

N:Original Author, Mike Heins {{EMAIL:mike@perusion.com}}

H2: Version

This document describes software based on Interchange 4.5 and later.

=head1 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.

=head1 Contents

The extension files can be found in the Interchange tarball under the 
'C<extensions/quickbooks>' directory.  The following files are used with this 
extension:

!block example
  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.
!endblock
  
=head1 Installation

To set up this extension, the basic steps are:

 * Create and copy directories and files.
 * Add additional database fields.
 * Modify catalog.cfg with additions.
 * Add "quickbooks" order route to checkout pages.
 * Restart Interchange.
 * Export your items from Interchange catalog (or import your existing 
   QuickBooks items to Interchange).
 * Test.

=head2 Terms and locations

Several terms are used in the examples.

=over 4

=item 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 C<foundation>, C<construct>, C<barry>, and C<simple>.)

Common locations:

    /var/lib/interchange/NAME
    /usr/local/interchange/catalogs/NAME
    $HOME/catalogs/NAME

We will use the path C</var/lib/interchange/foundation> in these examples.

=item Interchange software directory

This is the main directory for your Interchange server, where the file
C<interchange.cfg> resides. Common locations:

    /usr/lib/interchange
    /usr/local/interchange
    $HOME/ic

We will use the path C</usr/lib/interchange> in these examples.

=item Interchange tarball directory

The quickbooks files are located in the untarred distribution file, before
installation of Interchange is performed.

=item 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 C<interch> in these examples.

=back

=head2 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.

=head2 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.

Note that if you are not using a 4.9.8+ version of Interchange, you will need 
to manually install the qb_safe.filter by copying it from the 4.9.8 
code/Filter/qb_safe.filter into your Interchange version.

!block example

# 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

# Alternate usertag installation style:
#
#mkdir -p $CATROOT/usertags/global
#cp -i $QB/usertag/* \
#      $CATROOT/usertags/global
#
# Then include the global/*.tag in your interchange.cfg

# Variables that optionally modify the export process, along with 
# their help entries.	  
cat   $QB/products/variable.txt.append >> \
      $CATROOT/products/variable.txt
cat   $QB/products/mv_metadata.asc.append >> \
      $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

!endblock

=head2 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.

=over 4

=item Make orders directory

Create the directory C<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.)

=item 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

=item 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

=item 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 C<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
	 
=back

=head2 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.

=head2 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 C<inventory.txt> file, then
restart Interchange.

=head2 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

=head2 Add quickbooks order route

In the Interchange UI, there is a Preferences area "ORDER_ROUTES". You should
add the C<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.

=head2 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.

=head2 Restart the catalog

This can be done by restarting the Interchange server or by clicking
C<Apply Changes> in the UI.

=head2 Export the items 

You can access the Quickbooks UI index by making your URL:

    {{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.

=head2 Test

Place a test order on your Interchange catalog once you have finished installing.
You should find a file in the C<orders> directory with the name C<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.

=head1 Usage

=head2 Accessing Admin UI Features

A typical installation will cause the Administrative User Interface Features to 
become available via the top level menu:

 * Login to the Admin UI

 * Administration

 * Quickbooks

You should then be presented with a menu of the Admin UI features. 

=head2 Generating IIF Files

To generate the IIF files, access the corresponding page from the Admin UI
Quickbooks Menu (Administration -> Quickbooks -> Generate IIF Files).

You will be presented with a query tool.  Select the query options that you
would like and submit your query.  Among the query options, you have the option 
to input a QB transaction number.  This will be the first number that is used 
when generating the IIF files, and it will be incremented for each sequential
order in the query.

You will be notified of its success or failure.  The resulting page will:

 * Inform you of the success or failure of the query.
 * Provide a link to the "results" IIF file (which includes all of the orders
   found by the query).  Note that this "results" IIf file is overwritten every
   time a query is run.
 * Provide a link for each IIF file (one per order).  This can be used as a
   backup, or for importing one-by-one instead of all at once. 

=head1 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.

=head2 Sales Tax

QuickBooks has a taxing system whereby tax rates are defined by customer location.
There is usually also a generic C<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
C<QB_SALES_TAX_ITEM> variable.

=head2 Shipping

Interchange will add a generic item C<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 C<QB_SHIPPING_ITEM> variable.

=head2 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.

=head2 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 
C<extensions/quickbooks/legacy>.  

A1:Credits

*{{B:Mike Heins}}: This document was copied from the original POD documentation
({{EX:extensions/quickbooks/ic_qb.pod}}) written by Mike Heins {{EMAIL:mike@perusion.com}}.

*{{B:Dan Browning}}: Updated by Dan Browning {{EMAIL:db@kavod.com}}.

A1:Document history

*July 20, 2002.  Initial revision.

A1:Resources

A2:Documentation

	* What are the IIF File Headers? {{URL:http://www.quickbooks.com/support/faqs/qbw2000/121756.html}}
	* Also see the Quickbooks Help item, "Reference guide to import files"

N:Copyright 2002-2004 Interchange Development Group. Freely redistributable under terms of the GNU General Public License.