Quickbooks Contribution Rounding Error with Tax Rate

mike_steinhoff · June 18, 2003

Hi all,

I have put to use the Quickbooks Contribution that calculates taxes for my store. In my testing I noticed that the import file that is created takes the tax rate to four(4) decimal places, and does not round up. This will cause havoc with my accountant ! Does that make sense ? Let me provide an example:

I place an order for 149.85, tax rate is 7%. The tax rate as OSC figures it as $10.49....the quickbooks contribution figures it as 10.4895

I think this is the part that needs to be modified, but I am not sure:

//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; 

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

 } 

 

 

$transtotal = $transtotal + ($total_tax * $qty); 

$total_tax = 0;

Here is the Quickbooks code:

<?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 FL 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 = "/home/sites/site68/web/catalog/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 = "FL";   	 // Our FOB point for the invoice 

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

$storename = "www.prismmedical.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 = "Sales"; 	 // 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 = "Shipping"; 	 // The account used to post your shipping charges 

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

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

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



$productaccount ="Sales";  

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



// Convert all order data 



$salefile = $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, '.', '');

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

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

//

// Removed Mike Steinhoff 06/17/2003

// 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;

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

 } 

 

 

$transtotal = $transtotal + ($total_tax * $qty); 

$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 = "FL Sales Tax"; 

  $taxpercent = "7%"; 

   $taxdescr = "FL 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 = "FL Only"; // for the invoice 

 $lines++; 

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

 $total_tax = -$qty * ($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); 

?>

Here is the output file

!TRNS TRNSID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR TOPRINT ADDR1 ADDR2 ADDR3 ADDR4 ADDR5 PAID TERMS SHIPVIA REP FOB INVTITLE SADDR1 SADDR2 SADDR3 SADDR4 SADDR5

!SPL SPLID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR QNTY PRICE INVITEM TAXABLE EXTRA

!ENDTRNS

TRNS 103 INVOICE 06/17/03 Accounts Receivable Simpson, Homer 165.0195 103 Website Sale N Y Homer Simpson 11111 Coral Srpings Drive Wesley Chapel,Florida United States,33543 XXX XXX XXXX N Paid in Full WEB FL WEBSITE SALE Homer Simpson 11111 Coral Srpings Drive Wesley Chapel,Florida United States,33543 XXX XXX XXXX

SPL 104 INVOICE 06/17/03 Sales -149.85 CoEnzyme Q-10 50mg-180 N -3 49.95 PM-COQ50-180 Y

SPL 105 INVOICE 06/17/03 Shipping -4.68 Shipping N -1 4.68 Shipping N

SPL 106 INVOICE 06/17/03 Sales Tax Payable FL Sales Tax -10.4895 103 FL Only N 7% FL Sales Tax N AUTOSTAX

ENDTRNS

Quickbooks Contribution Rounding Error with Tax Rate

Recommended Posts

mike_steinhoff

Archived

Browse

Activity