quickbooks 2002, is there a working export module

[chris1981]

hi

i?m using quickbooks 2002 and installed the quickbooks mod from the contributions.

 

But also the included demo *.iff files didn't really work.

The customer file works, but the sales file doesnt.

 

has anyone developed a working contribution or modified a version?

please let me know

 

best regards

christian

[Guest]

I took the quick books mod and made it work with 2002, this includes the customer and sales mod. Each file will be created with a date. Also, the sales works with SALES TAX!!!!!

 

Be sure to replace all areas of NJ SALES TAX...

this is the sales mod

 

<?php

/*

 $Id: qb_iif_sale.php,v 2.0BETA 2003/02/09 11:32:53 VyReN Exp $

Created by Steve K. of Cantexgroup

http://www.cantexgroup.ca/ email: [email protected]

Modified for Quickbooks 2001 and OSC 2.2 by Tom St.Croix of Better Than Nature

http://www.betterthannature.com/catalog/ email: [email protected]

Modified again for OSC 2.2MS1 by Glen Piwowarczyk



Modified by George R Bicking to work with 2002 pro editions

make sure you change all signs of NJ sales tax to your state or

what you have as your tax in your quickbooks





    Copyright (C) 2002 Steve K. 



Released under GPL and may be modified.

*/



$myfile = "/qb/qb_sale_" . date(m) .date(d) . date(y) . ".iif";                // Where will our transaction import file be located?

$araccount = "Accounts Receivable";    // Our "Accounts Receivable" account

$FOB = "NJ";                           // Our FOB point for the invoice

$terms = "Paid in Full";                    // Invoice terms

$storename = ".com";        // Your Store Name

$memo = "Website Sale";                     // Invoice memo

$toprint = "Y";                             // Y to mark invoice as 'to be printed', N otherwise

$cleared = "N";                             // Y to mark transaction as cleared, N otherwise



$defaultclass = "BTN";                      // the default class for the transaction

                   // The individual lines will use the product class

                // but the entire transaction will use this



$paid = "N";                                // mark the invoice as paid Y or unpaid N

$salesrep = "WEB";                          // clerk for the transaction

$invtitle = "WEBSITE SALE";                 // the 'title' for the invoice. Could be whatever you want 

           // for example LAYAWAY, INVOICE, etc.



$Shipping_Account = "Postage and Delivery";             // The account used to post your shipping charges

$shipdescription = "Shipping and Handling"; // the shipping description on the invoice

$shipitem = "Shipping";                     // the QB itemcode for shipping

$shiptaxcode = "N";                         // Your QB tax code for shipping



$productaccount ="Airsoft Sale";

// **** You shouldn't have to touch anything past this point ****



// Convert all order data



$salefile = $DOCUMENT_ROOT . $myfile;

$customers_id = $customer_id;

$customers_name = $order->customer['firstname'] . ' ' . $order->customer['lastname'];

$customers_street_address = $order->customer['street_address'];

$customers_suburb = $order->customer['suburb'];

$customers_city = $order->customer['city'];

$customers_postcode = $order->customer['postcode'];

$customers_state = $order->customer['state'];

$customers_country = $order->customer['country']['title'];

$customers_telephone = $order->customer['telephone'];

$customers_email_address = $order->customer['email_address'];

$customers_address_format_id = $order->customer['format_id'];

$delivery_name = $order->delivery['firstname'] . ' ' . $order->delivery['lastname'];

$delivery_street_address = $order->delivery['street_address'];

$delivery_suburb = $order->delivery['suburb'];

$delivery_city = $order->delivery['city'];

$delivery_postcode = $order->delivery['postcode'];

$delivery_state = $order->delivery['state'];

$delivery_country = $order->delivery['country']['title'];

$delivery_address_format_id = $order->delivery['format_id'];

$payment_method = $order->info['payment_method'];

$cc_type = $order->info['cc_type'];

$cc_owner = $order->info['cc_owner'];

$cc_number = $order->info['cc_number'];

$cc_expires = $order->info['cc_expires'];

$date_purchased = 'now()';

$orders_status = DEFAULT_ORDERS_STATUS_ID;

$comments = $order->info['comments'];

$currency = $order->info['currency'];

$currency_value = $order->info['currency_value'];

$total_tax = 0;





// First we setup the format of our transaction header, our SPL header,

// and our "we're done" lines. These can be changed to suit your requirements, but

// read the QB IIF docs for more info.

$transaction_header ="!TRNStTRNSIDtTRNSTYPEtDATEtACCNTtNAMEtCLASStAMOUNTt";

$transaction_header.="DOCNUMtMEMOtCLEARtTOPRINTtADDR1tADDR2t";

$transaction_header.="ADDR3tADDR4tADDR5tPAIDtTERMStSHIPVIAtREPt";

$transaction_header.="FOBtINVTITLEtSADDR1tSADDR2tSADDR3tSADDR4tSADDR5tn";



$distribution_line_header ="!SPLtSPLIDtTRNSTYPEtDATEtACCNTtNAMEtCLASStAMOUNTt";

$distribution_line_header.="DOCNUMtMEMOtCLEARtQNTYtPRICEtINVITEMtTAXABLEtEXTRAtn";



$end_header ="!ENDTRNStn";

$end_transaction = "ENDTRNStn";





// Now we setup the info that we need to send to the file

$order_date = date('m/d/y');

$ordernum = $insert_id;

$sale_name = $order->customer['lastname'] . ', ' . $order->customer['firstname'];

$shipping = $order->info['shipping_cost']; 

$shipping_cost = $shipping - ($shipping * 2); // MUST BE NEGATIVE NUMBER, SO 'FLIP' IT



// Convert transaction amounts from TEP to QB currency, if needed.

if (CONVERT_QB_CURRENCY==1) {

 $shipping = ($shipping * EXCHANGE_RATE);

 $shipping_cost = ($shipping_cost * EXCHANGE_RATE);

 $total_tax = ($total_tax * EXCHANGE_RATE);

 $total_cost = ($total_cost * EXCHANGE_RATE);

}



// finish setting up our info for the IIF file.

$shipping = number_format($shipping, 2, '.', '');

$shipping_cost = number_format($shipping_cost, 2, '.', '');

$total_cost = number_format($total_cost, 2, '.', '');

$transtotal = $cart->show_total() + $shipping;



// setup our "ship via" for QB. Change these to match your own needs

if (left($order->info['shipping_method'],13) == "United Parcel") {

$shipvia="UPS";

} elseif ($HTTP_POST_VARS['shipping_method'] == "I will Pickup my order") {

 $shipvia="Pickup";

} else {

 $shipvia = "USPS";

}

//$shipvia = $order->info['shipping_method'];



// Check if the transaction import file exists;

$oldfile = file_exists ($salefile);



// Open transaction import file for appending, create it if necessary;

$fp = fopen($salefile,"a");



// Create header line & write it to file if the transaction import file did NOT exist previously

// This means that everytime you 'import' the file into QB, you must delete it immediately.

// This will prevent re-importation AND it will keep the IIF file small.

if (!$oldfile) {

 fputs($fp,$transaction_header);

 fputs($fp,$distribution_line_header);

 fputs($fp,$end_header);

}





//here we need to get the total tax



for ($i=0; $i<sizeof($order->products); $i++) {

  $products_tax = (($order->products[$i]['tax']*.01) * $order->products[$i]['price']);

 $total_tax = $total_tax + $products_tax;

 }

 

 

$transtotal = $transtotal + $total_tax;

$total_tax = 0;









// Create the IIF transaction data line & write it to the file.



$transaction ="TRNSt$ordernumtINVOICEt$order_datet$araccountt$sale_namet";

$transaction.="t$transtotalt$ordernumt";

$transaction.="$memot$clearedt$toprintt$customers_namet";

$transaction.="$customers_street_addresst";

$transaction.="$customers_city,$customers_statet";

$transaction.="$customers_country,$customers_postcodet";

$transaction.="$customers_telephonet";

$transaction.="$paidt$termst$shipviat$salesrept$FOBt$invtitlet$delivery_namet";

$transaction.="$delivery_street_addresst";

$transaction.="$delivery_city,$delivery_statet";

$transaction.="$delivery_country,$delivery_postcodet$customers_telephonetn";

fputs($fp,$transaction);





// Now lets add each product to an SPL line

$lines=$ordernum;

$lines++;

for ($i=0; $i<sizeof($order->products); $i++) {

 $product_name = $order->products[$i]['name'];

 $product_price = $order->products[$i]['price'];

 $product_number = $order->products[$i]['model'];

 $class = $order->products[$i]['class'];

 $memo = $order->products[$i]['name'];

 $vendor = $order->products[$i]['vendor'];

 $qty = $order->products[$i]['qty'];

 $qty = $qty - ($qty * 2);  // MUST BE NEGATIVE NUMBER, SO 'FLIP' IT

 $total_product_price = ($product_price + $cart->attributes_price($order->products[$i]['id']));

        //reinvokeed tax

  //  $product_tax = tep_get_tax_rate($order->products[$i]['tax_class_id'],1,$delivery_values['zone_id']);

 $products_tax = (($order->products[$i]['tax']*.01) * $order->products[$i]['price']);

 $total_tax = $total_tax + $products_tax;

 $attributes_exist = '0';

 if ($order->products[$i]['attributes']) {

   $attributes_exist = '1';

   reset($order->products[$i]['attributes']);

   while (list($option, $value) = each($order->products[$i]['attributes'])) {

     $attributes = tep_db_query("select popt.products_options_name, poval.products_options_values_name, pa.options_values_price, pa.price_prefix from products_options popt, products_options_values poval, products_attributes pa where pa.products_id = '" . $order->products[$i]['id'] . "' and pa.options_id = '" . $option . "' and pa.options_id = popt.products_options_id and pa.options_values_id = '" . $value . "' and pa.options_values_id = poval.products_options_values_id");

     $attributes_values = tep_db_fetch_array($attributes);

     // If there are attributes, we'll simply append the values to the item description

     // on the invoice line

     $memo.= ' ';

     $memo.= $attributes_values['products_options_values_name'];

     $product_price += $attributes_values['options_values_price'];

   }

 }

 if (CONVERT_QB_CURRENCY==1) {

   $product_price = ($product_price * EXCHANGE_RATE);

   $total_product_price = ($total_product_price * EXCHANGE_RATE);

 }

 $product_price = number_format($product_price, 2, '.', '');

 $total_product_price = number_format($total_product_price, 2, '.', '');

 //$account = $order->products[$i]['products_qb_sales_account'];

$account = $productaccount;

$total_product_price = $order->products[$i]['final_price'];

 // MUST BE NEGATIVE NUMBER, SO 'FLIP' IT

 // $ItemCost = $total_product_price - ($total_product_price * 2);



$ItemCost = $total_product_price * $qty;

//$ItemCost = $ItemCost - ($ItemCost * 2);



 // CHANGE THESE TO SUIT YOUR QB TAX CODES

//  if ($product_tax == "0.0000") {

   if ($total_tax == 0) :

$taxcode= "N";

$taxcode2 = "Out of State";

$taxpercent = "0%";

  $taxdescr = "Out of State";

else:

//  } elseif ($product_tax == "6.0000") {

$taxcode= "Y";

   $taxcode2 = "NJ Sales Tax";

$taxpercent = "6%";

 $taxdescr = "NJ Sales Tax";

endif;

//  } else {

//    $taxcode = "Sales Tax";

//  }



 // Finally, we build the SPL line and write it to the file.

 // $ItemNo = $vendor . ":" . $product_number; // the Item Number in QB

                  // We use sub-items, grouped by vendor code,

                  // hence this structure. If you just use

                  // item numbers then your line would be

                  // $ItemNo = $product_number;

 $ItemNo = $product_number;



 $line ="SPLt$linestINVOICEt$order_datet$accounttt$classt$ItemCostt";

 $line.="t$memot$clearedt$qtyt$product_pricet";

 $line.="$ItemNot$taxcodetn";

 fputs($fp,$line);

 $lines++;

}





// Add a "shipping charge" SPL line if there is any shipping charge



if ($shipping_cost <> "0.00") {

 $shipping_line ="SPLt$linestINVOICEt$order_datet$Shipping_Accountt";

 $shipping_line.="tt";

 $shipping_line.="$shipping_costtt$shipdescriptiont$clearedt-1t$shippingt";

 $shipping_line.="$shipitemt$shiptaxcodetn";

 fputs($fp,$shipping_line);

}



// Now lets add the total tax





 $taxaccount = "Sales Tax Payable"; // the QB 'sales tax' account

 $taxdescription = "NJ Only"; // for the invoice

 $lines++;

// MUST BE NEGATIVE NUMBER, SO 'FLIP' IT

 $total_tax = $total_tax - ($total_tax * 2);

 $tax_line ="SPLt$linestINVOICEt$order_datet$taxaccountt$taxcode2tt$total_taxt";

 $tax_line.="$ordernumt$taxdescriptiont$clearedtt$taxpercentt$taxdescrtNtAUTOSTAXn";

 fputs($fp,$tax_line);





// Add the END OF SPL and END OF TRANSACTION lines and close the file

fputs($fp,$end_transaction);

fclose($fp);

?>

[Guest]

<?php

/*

  $Id: qb_iif_sale.php,v 2.0BETA 2003/02/09 11:32:53 VyReN Exp $

Created by Steve K. of Cantexgroup

http://www.cantexgroup.ca/ email: [email protected]

Modified for Quickbooks 2001 and OSC 2.2 by Tom St.Croix of Better Than Nature

http://www.betterthannature.com/catalog/ email: [email protected]

Modified again for OSC 2.2MS1 by Glen Piwowarczyk

Modifeied by George R Bicking



Please change all signs of NJ sales tax to your state or what you

have for tax in quickbooks.



    Copyright (C) 2002 Steve K. 



Released under GPL and may be modified.



*/



$myfile = "/qb/qb_customer_" . date(m) .date(d) . date(y) . ".iif";                // Where will our transaction import file be located?



// **** You shouldn't have to touch anything past this point ****



$custfile = $DOCUMENT_ROOT . $myfile;



// Check if the customer import file exists;

$oldfile = file_exists ($custfile);



// Open customer import file for appending, create it if necessary;

$fp = fopen($custfile,"a");



// Create header line & write it to file if the customer import file did NOT exist previously

// This means that everytime you 'import' the file into QB, you must delete it immediately.

// This will prevent re-importation AND it will keep the IIF file small.

// The header line values can be changed to whatever you need - read the QB IIF docs

// for more info.

if (!$oldfile) {

 $line ="!CUSTtNAMEtBADDR1tBADDR2tBADDR3tBADDR4tBADDR5t";

 $line.="SADDR1tSADDR2tSADDR3tSADDR4tSADDR5tPHONE1tFAXNUMt";

 $line.="CTYPEtTERMStLIMITtFIRSTNAMEtLASTNAMEtEMAILtTAXABLEtTAXITEMtREPtn";

 fputs($fp,$line);

}



// Setup our variables for creating customer record

$creditlimit = ""; // the default credit limit for new customers

$salesman = "WebSite"; // The "default clerk" in QB. Can also be blank ""

$firstname = $HTTP_POST_VARS['firstname'];

$lastname = $HTTP_POST_VARS['lastname'];

$accnum = $lastname . ", " . $firstname;

$street = $HTTP_POST_VARS['street_address'];

$city = $HTTP_POST_VARS['city'];

$province = tep_get_zone_name($HTTP_POST_VARS['country'], $zone_id, $state);

$countryname = tep_get_country_name($HTTP_POST_VARS['country']);

$pcode = $HTTP_POST_VARS['postcode'];

$phone = $HTTP_POST_VARS['telephone'];

$fax = $HTTP_POST_VARS['fax'];

$DOB = $HTTP_POST_VARS['dob'];

$emailto = $HTTP_POST_VARS['email_address'];

$rep = "WEB";



if ($state == 'New Jersey') :

$tax = 'Y';

$taxstate = 'NJ Sales Tax';

else: 

$tax = 'N';

$taxstate = 'Out of State';

endif;

// Create the IIF line & write it to the file. If you changed the header line, you

// will also need to change these lines to match - read the QB IIF docs for more info

$line ="CUSTt$accnumt$firstname $lastnamet$streett$city, $provincet";

$line.="$countrynamet$pcodet$firstname $lastnamet$streett$city, $provincet";

$line.="$countrynamet$pcodet$phonet$faxt$salesmantDue on receiptt$creditlimitt$firstnamet";

$line.="$lastnamet$emailtot$taxt$taxstatet$reptn";

fputs($fp,$line);



// Close the customer import file

fclose($fp);

?>

[cpv1st]

i notice on your post it reads

 

$Id: qb_iif_sale.php,v 2.0BETA 2003/02/09 11:32:53 VyReN Exp $

 

and when i downlaoded the mod mine reads

 

$Id: qb_iif_sale.php,v 1.2 2002/08/28 04:52:42 crshNbrn Exp $

 

will this still work with you modifications...and do you know wherei can go and download the version you have....also...are you still using quickbooks 2002 or 2003 and do you like it if you are...thanks for all help in advance

 

 

chris

[Guest]

I am using the latest version from the mod section.

 

I am still using 2002, I have no need to go to 2003.

 

If you can't find the version of quickbooks mod let me know.

 

George

[Guest]

Hi!

 

I am interested in setting up the quickbooks IIF creation mod so that we can import sales data into Quickbooks.

 

How does this mod handle attributes of products? Usually, in Quickbooks, we have every products as its own ID, even for those with attributes (i.e. blue t-shirt medium is a different id from red t-shirt medium). But OSC handles these products as a single product id that has an attribute id. How will this work when importing into Quickbooks?

 

Thanks!!!

Zac

[mike_steinhoff]

This is a great contributon!

 

I do have one problem though, I am hoping that somone can help me with it. I am using OSC 2.2MS1, and have used the QB contribution in listed in the contributions section. I then found this and it solved most of my probems. The problem I have left is this:

 

In the Categories/Products section, I edit a product and at the bottom there are two fields for the quickbooks entries: QB Sales Account and QB Class. In these fields there are two selections, neither of which I want to use, I only want to use "Sales". (If possible I would like it do default to sales when an new item is created and completely remove the other two.)

 

My problems are

 

1. I have added "Sales" to the list, but it does not populate in the database when selected.

 

2. Whatever I select does not stay in the box....What I mean here is that when I open the product again, I do not know which QB Sales Account I have assignd to this product.

 

3. What is the QB Class field used for ?

 

I appreciate any assistance.

 

Thanks

 

Mike