Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

NEW! Complete Order Editing Tool!


jhilgeman

Recommended Posts

Hi there.

 

Please can anyone help me?

 

I'm having a problem with tax rates that have a decimal part (e.g. 17.5) not calculating correctly in the Order Totals box.

 

Say I've got 55 of a product at 25 base price and 17.5 tax... the Price (excl.) and Price (incl.) and Total boxes update correctly, and in this case the Total (excl.) box says 1375.0000, however the Sub-Total on the Orders Totals box below (which should be the same) says 1375.23.

 

If I change the tax to any whole number, the problem goes away --- i.e. the Sub-Total reads the same as Total (excl.) --- but if I add number after the dot for tax (17.1, 17.2, 17.3....), after updating the Sub-Total (which souldn't change) changes to 1375.23.

 

There seems to be a maths error happening with non-whole tax rates?

 

Any ideas anyone?

 

Jeff.

Link to comment
Share on other sites

Hi there.

 

Please can anyone help me?

 

I'm having a problem with tax rates that have a decimal part (e.g. 17.5) not calculating correctly in the Order Totals box.

 

Say I've got 55 of a product at 25 base price and 17.5 tax... the Price (excl.) and Price (incl.) and Total boxes update correctly, and in this case the Total (excl.) box says 1375.0000, however the Sub-Total on the Orders Totals box below (which should be the same) says 1375.23.

 

If I change the tax to any whole number, the problem goes away --- i.e. the Sub-Total reads the same as Total (excl.) --- but if I add number after the dot for tax (17.1, 17.2, 17.3....), after updating the Sub-Total (which souldn't change) changes to 1375.23.

 

There seems to be a maths error happening with non-whole tax rates?

 

Any ideas anyone?

 

Jeff.

 

The decimal point shouldn't make a difference. What version of Order Editor are you using?

Do, or do not. There is no try.

 

Order Editor 5.0.6 "Ultra Violet" is now available!

For support or to post comments, suggestions, etc, please visit the Order Editor support thread.

Link to comment
Share on other sites

3.021

 

There are three places you have to change the code.

 

The first is in admin/classes/oe_order.php; this is the file that gets the information from the database. Make sure you find and change all three instances of suburb in this file.

 

Next is at the top of admin/edit_orders.php, where the information is sent to the database for updating. You have to change each instance of $_POST['variable'] for the three incarnations of suburb ($_POST['update_customer_suburb'], etc).

 

Finally you have to get the actual HTML display in admin/edit_orders.php, where the various form elements that the user sees and interacts with are generated.

Do, or do not. There is no try.

 

Order Editor 5.0.6 "Ultra Violet" is now available!

For support or to post comments, suggestions, etc, please visit the Order Editor support thread.

Link to comment
Share on other sites

The decimal point shouldn't make a difference. What version of Order Editor are you using?

 

edit_orders.php v5.0.5 08/27/2007

 

I know it shouldn't make a difference, and if I play with the base price amount, again I can make the problem go away. e.g. If I change the price in my example above from 25 to 20, then the Price (excl.) becomes 1100.0000, and the Sub-Total also says 1,100.00.

 

But if I change ithe base price to 21, then gthe Price (excl.) becomes 1155.0000 (correct) but the Sub-Total changes to 1,155.23.

 

It's driving me crazy, I just can't figure it out.

Link to comment
Share on other sites

edit_orders.php v5.0.5 08/27/2007

 

I know it shouldn't make a difference, and if I play with the base price amount, again I can make the problem go away. e.g. If I change the price in my example above from 25 to 20, then the Price (excl.) becomes 1100.0000, and the Sub-Total also says 1,100.00.

 

But if I change ithe base price to 21, then gthe Price (excl.) becomes 1155.0000 (correct) but the Sub-Total changes to 1,155.23.

 

It's driving me crazy, I just can't figure it out.

 

Does the 23 cents correspond to anything in the order? Tax on shipping maybe?

Do, or do not. There is no try.

 

Order Editor 5.0.6 "Ultra Violet" is now available!

For support or to post comments, suggestions, etc, please visit the Order Editor support thread.

Link to comment
Share on other sites

Does the 23 cents correspond to anything in the order? Tax on shipping maybe?

No, it doesn't appear to correspond to anything. And sometimes the Sub-Total is _less_ than the correct Price (excl.)

 

Here's the Orders Total box for 55 items @ £15 each (base price) with 17.5% tax to be added (the numbers in the input boxes are correct):

 

Sub-Total: £825.23 (should be £825)

VAT @ 17.5%: £144.42 (calculated correctly but on the incorrect Sub-Total)

Shipping options (Standard Delivery): 15.00

Total: £984.65 - should be £984.38, i.e. (825 * 1.175) + 15

 

And here's the Orders Total box for 55 items @ £14.99 each (base price) with 17.5% tax to be added (again, the numbers in the input boxes are correct):

 

Sub-Total: £824.30 (should be £824.45)

VAT @ 17.5%: £ 144.25 (calculated correctly but on the incorrect Sub-Total)

Shipping options (Standard Delivery): 15.00

Total: £ 983.55 - should be £983.73, i.e. (824.45 * 1.175) + 15

 

As I say, if I remove the decimal point from the tax, there are no errors in the calculation (that is to say, I've not spotted any).

 

It's like there is some crazy rounding up/down going on :-(

 

Where would this get calculated, in the order_total.php file?

Link to comment
Share on other sites

Without changing the tax rate (17.5) I've been playing with the fractional values of all the input boxes --- Price (base) etc --- and it's looking more and more like rounding errors. A small change to the fourth decimal place is causing a large error in the Sub-Total calculation.

Link to comment
Share on other sites

No, it doesn't appear to correspond to anything. And sometimes the Sub-Total is _less_ than the correct Price (excl.)

 

Here's the Orders Total box for 55 items @ £15 each (base price) with 17.5% tax to be added (the numbers in the input boxes are correct):

 

Sub-Total: £825.23 (should be £825)

VAT @ 17.5%: £144.42 (calculated correctly but on the incorrect Sub-Total)

Shipping options (Standard Delivery): 15.00

Total: £984.65 - should be £984.38, i.e. (825 * 1.175) + 15

 

And here's the Orders Total box for 55 items @ £14.99 each (base price) with 17.5% tax to be added (again, the numbers in the input boxes are correct):

 

Sub-Total: £824.30 (should be £824.45)

VAT @ 17.5%: £ 144.25 (calculated correctly but on the incorrect Sub-Total)

Shipping options (Standard Delivery): 15.00

Total: £ 983.55 - should be £983.73, i.e. (824.45 * 1.175) + 15

 

As I say, if I remove the decimal point from the tax, there are no errors in the calculation (that is to say, I've not spotted any).

 

It's like there is some crazy rounding up/down going on :-(

 

Where would this get calculated, in the order_total.php file?

 

The calculations are done in the order class file, admin/order_editor/order.php:

		$shown_price = tep_add_tax($this->products[$index]['final_price'], $this->products[$index]['tax']) * $this->products[$index]['qty'];
	   $this->info['subtotal'] += $shown_price;

	   $products_tax = $this->products[$index]['tax'];
	   $products_tax_description = $this->products[$index]['tax_description'];
	   if (DISPLAY_PRICE_WITH_TAX == 'true') {
		 $this->info['tax'] += $shown_price - ($shown_price / (($products_tax < 10) ? "1.0" . str_replace('.', '', $products_tax) : "1." . str_replace('.', '', $products_tax)));
		 if (isset($this->info['tax_groups']["$products_tax_description"])) {
		   $this->info['tax_groups']["$products_tax_description"] += $shown_price - ($shown_price / (($products_tax < 10) ? "1.0" . str_replace('.', '', $products_tax) : "1." . str_replace('.', '', $products_tax)));
		 } else {
		   $this->info['tax_groups']["$products_tax_description"] = $shown_price - ($shown_price / (($products_tax < 10) ? "1.0" . str_replace('.', '', $products_tax) : "1." . str_replace('.', '', $products_tax)));
		 }
	   } else {
		 $this->info['tax'] += ($products_tax / 100) * $shown_price;
		 if (isset($this->info['tax_groups']["$products_tax_description"])) {
		   $this->info['tax_groups']["$products_tax_description"] += ($products_tax / 100) * $shown_price;
		 } else {
		   $this->info['tax_groups']["$products_tax_description"] = ($products_tax / 100) * $shown_price;
		 }
	   }

	   $index++;
	 }

	 if (DISPLAY_PRICE_WITH_TAX == 'true') {
	   $this->info['total'] = $this->info['subtotal'] + $this->info['shipping_cost'];
	 } else {
	   $this->info['total'] = $this->info['subtotal'] + $this->info['tax'] + $this->info['shipping_cost'];
	 }
   }

You would be most interested in the code that generates the subtotal:

	   $shown_price = tep_add_tax($this->products[$index]['final_price'], $this->products[$index]['tax']) * $this->products[$index]['qty'];
	   $this->info['subtotal'] += $shown_price;

It seems most likely to be a problem with the function tep_add_tax() although I don't know why this function would be causing problems like this. It's a stock osC function: you can find it at admin/includes/functions/general.php.

Do, or do not. There is no try.

 

Order Editor 5.0.6 "Ultra Violet" is now available!

For support or to post comments, suggestions, etc, please visit the Order Editor support thread.

Link to comment
Share on other sites

Experiment (55 units with 17.5 tax):

 

Price (base) : 15.0042

Price (excl.) : 15.0042

Price (incl.) : 17.6299

Total (excl.) : 825.2310

Total (incl.) : 969.6464

 

Order Totals box correct.

 

Change base price by 7/1000ths:

 

Price (base) : 15.0049

Price (excl.) : 15.0049

Price (incl.) : 17.6308

Total (excl.) : 825.2715

Total (incl.) : 969.6940

 

Order Totals box incorrect (i.e. Total (excl.) and Sub-Total do not match:

 

Sub-Total: 825.23

VAST @ 17.5%: 144.42

Shipping: 15.00

Total: 984.65

 

Am I barking up the wrong tree do you think?

Link to comment
Share on other sites

So it looks like the problem lies somewhere in these three functions maybe (from my admin/includes/functions/general.php file)?

 

////
// Wrapper function for round() for php3 compatibility
 function tep_round($value, $precision) {
   if (PHP_VERSION < 4) {
     $exp = pow(10, $precision);
     return round($value * $exp) / $exp;
   } else {
     return round($value, $precision);
   }
 }

////
// Add tax to a products price
 function tep_add_tax($price, $tax) {
   global $currencies;

   if (DISPLAY_PRICE_WITH_TAX == 'true') {
     return tep_round($price, $currencies->currencies[DEFAULT_CURRENCY]['decimal_places']) + tep_calculate_tax($price, $tax);
   } else {
     return tep_round($price, $currencies->currencies[DEFAULT_CURRENCY]['decimal_places']);
   }
 }

// Calculates Tax rounding the result
 function tep_calculate_tax($price, $tax) {
   global $currencies;

   return tep_round($price * $tax / 100, $currencies->currencies[DEFAULT_CURRENCY]['decimal_places']);
 }

 

What's puzzling me is that no one appears to have noticed this before, which suggests it's me, not you :blink:

Link to comment
Share on other sites

So it looks like the problem lies somewhere in these three functions maybe (from my admin/includes/functions/general.php file)?

 

////
// Wrapper function for round() for php3 compatibility
 function tep_round($value, $precision) {
   if (PHP_VERSION < 4) {
     $exp = pow(10, $precision);
     return round($value * $exp) / $exp;
   } else {
     return round($value, $precision);
   }
 }

////
// Add tax to a products price
 function tep_add_tax($price, $tax) {
   global $currencies;

   if (DISPLAY_PRICE_WITH_TAX == 'true') {
     return tep_round($price, $currencies->currencies[DEFAULT_CURRENCY]['decimal_places']) + tep_calculate_tax($price, $tax);
   } else {
     return tep_round($price, $currencies->currencies[DEFAULT_CURRENCY]['decimal_places']);
   }
 }

// Calculates Tax rounding the result
 function tep_calculate_tax($price, $tax) {
   global $currencies;

   return tep_round($price * $tax / 100, $currencies->currencies[DEFAULT_CURRENCY]['decimal_places']);
 }

 

What's puzzling me is that no one appears to have noticed this before, which suggests it's me, not you :blink:

 

I have much simpler code for the tax functions in my RC1 sandbox site:

////
  // Add tax to a products price
 function tep_add_tax($price, $tax, $override = false) {
   if ( ( (DISPLAY_PRICE_WITH_TAX == 'true') || ($override == true) ) && ($tax > 0) ) {
	 return $price + tep_calculate_tax($price, $tax);
   } else {
	 return $price;
   }
 }

  // Calculates Tax rounding the result
 function tep_calculate_tax($price, $tax) {
   return $price * $tax / 100;
 }

 

Using the function tep_round the way you've shown it probably is the problem especially as it uses your setting for default currency decimal places (usually 2) as the value of $precision.

 

I have no idea why your functions are set this way however, whether it be just an older version of osC, you changed them at some point for a contribution, etc.

 

[edit]: It is code from an earlier version of osC. It's surprising to me also that, if this is the problem, no one else has reported it. [end.edit]

Edited by djmonkey1

Do, or do not. There is no try.

 

Order Editor 5.0.6 "Ultra Violet" is now available!

For support or to post comments, suggestions, etc, please visit the Order Editor support thread.

Link to comment
Share on other sites

It is code from an earlier version of osC. It's surprising to me also that, if this is the problem, no one else has reported it.

Yes, you are correct, it was code from an old version of osCommerce (general.php,v 1.160 2003/07/12). It was installed for my client a couple of years ago by what I would call "iffy" web developers, and subsequent to that they made several iffy large customisations so that updating the osCommerce system would now be a massive job. That web development firm went bust and this client then came to me for help. It looks like my client is going to have to either bite the bullet and have me reinstall osCommerce and all the customisations again from scratch, or do without new functionality ;)

 

Thank you again for you help.

 

Jeff.

Edited by jeffwdesign
Link to comment
Share on other sites

There are three places you have to change the code.

 

The first is in admin/classes/oe_order.php; this is the file that gets the information from the database. Make sure you find and change all three instances of suburb in this file.

 

Next is at the top of admin/edit_orders.php, where the information is sent to the database for updating. You have to change each instance of $_POST['variable'] for the three incarnations of suburb ($_POST['update_customer_suburb'], etc).

 

Finally you have to get the actual HTML display in admin/edit_orders.php, where the various form elements that the user sees and interacts with are generated.

 

Thanks DJ. You're a real champion here.

 

Anthony

Link to comment
Share on other sites

Yes, you are correct, it was code from an old version of osCommerce (general.php,v 1.160 2003/07/12). It was installed for my client a couple of years ago by what I would call "iffy" web developers, and subsequent to that they made several iffy large customisations so that updating the osCommerce system would now be a massive job. That web development firm went bust and this client then came to me for help. It looks like my client is going to have to either bite the bullet and have me reinstall osCommerce and all the customisations again from scratch, or do without new functionality ;)

 

Thank you again for you help.

 

Jeff.

You're welcome.

Do, or do not. There is no try.

 

Order Editor 5.0.6 "Ultra Violet" is now available!

For support or to post comments, suggestions, etc, please visit the Order Editor support thread.

Link to comment
Share on other sites

Hi, Stew,

 

I just wonder that you can give a help about Changing SQL data base. before I used manually to edit SQL , now in your contri. V5.05 there is sql_install and sql_uninstall file which should allow to run SQL. But how. I am not good at SQL.

 

Thanks In advance.

 

Charles

Link to comment
Share on other sites

Hi, Stew,

 

I just wonder that you can give a help about Changing SQL data base. before I used manually to edit SQL , now in your contri. V5.05 there is sql_install and sql_uninstall file which should allow to run SQL. But how. I am not good at SQL.

 

Thanks In advance.

 

Charles

 

You can just cut and paste the command from the install instructions or the sql file if you want to do it manually.

Do, or do not. There is no try.

 

Order Editor 5.0.6 "Ultra Violet" is now available!

For support or to post comments, suggestions, etc, please visit the Order Editor support thread.

Link to comment
Share on other sites

Hi there,

 

I've just updated from 5.04 i think it was and now I find that on some orders products are missing. The item shows up in the standard orders.php but not in edit_orders.php (although the total is correct).

 

I didn't notice any problems when i was using 5.04 but I couldn''t say for certain that there were no problems

 

screen shot of orders.php

 

orders.jpg

 

screen shot of edit_orders.php

 

edit_orders.jpg

 

Any ideas?

Link to comment
Share on other sites

hmm,

 

i've rolled back to version 5.02 and still have the same issue, i assume it's just coincidence that I've upgraded today and spotted 2 orders with products missing.

 

Any ideas?

 

if this is any help I've included the database info for an order with a missing product.

 

orders_products_id	orders_id	products_id	products_model	products_name	products_price	final_price	products_tax	products_quantity	vendors_id
8552	22127	2369	180	2008 Fox Racing 180/HC Race Kit Combo	112.3319	112.3319	17.5	1	1
8553	22127	2857	2007 Airoh S	2007 Airoh Stelt View Black	184.99	184.99	0	1	1

 

item 8552 doesn't appear on the order when viewing through edit_orders.php

 

Thanks

 

Dave

Link to comment
Share on other sites

Hi there,

 

I've just updated from 5.04 i think it was and now I find that on some orders products are missing. The item shows up in the standard orders.php but not in edit_orders.php (although the total is correct).

 

I didn't notice any problems when i was using 5.04 but I couldn''t say for certain that there were no problems

 

screen shot of orders.php

 

screen shot of edit_orders.php

 

Any ideas?

 

This could be caused by having a products_id of '0' stored in the orders_products table for the product in question, or the products_id is not '0' but there is no corresponding entry in the products table for whatever is stored as the products_id for that item.

 

You can check this by going into your database and checking the contents of the orders_products table. Find the line item for the Race Kit Combo item (the item that shows up in orders.php but not in edit_orders.php) and see what is stored as the products_id for that item.

 

I will have to come up with a different query structure for the $orders_products array to prevent this from happening. If a user deletes an item from the catalog it should still show up in Order Editor for whatever orders it is a part of.

Do, or do not. There is no try.

 

Order Editor 5.0.6 "Ultra Violet" is now available!

For support or to post comments, suggestions, etc, please visit the Order Editor support thread.

Link to comment
Share on other sites

hmm,

 

i've rolled back to version 5.02 and still have the same issue, i assume it's just coincidence that I've upgraded today and spotted 2 orders with products missing.

 

Any ideas?

 

if this is any help I've included the database info for an order with a missing product.

 

orders_products_id    orders_id    products_id    products_model    products_name    products_price    final_price    products_tax    products_quantity    vendors_id
8552    22127    2369    180    2008 Fox Racing 180/HC Race Kit Combo    112.3319    112.3319    17.5    1    1
8553    22127    2857    2007 Airoh S    2007 Airoh Stelt View Black    184.99    184.99    0    1    1

 

item 8552 doesn't appear on the order when viewing through edit_orders.php

 

Thanks

 

Dave

 

What product in your catalog has a products_id of 2369?

Edited by djmonkey1

Do, or do not. There is no try.

 

Order Editor 5.0.6 "Ultra Violet" is now available!

For support or to post comments, suggestions, etc, please visit the Order Editor support thread.

Link to comment
Share on other sites

hmm,

 

i've rolled back to version 5.02 and still have the same issue, i assume it's just coincidence that I've upgraded today and spotted 2 orders with products missing.

 

Any ideas?

 

item 8552 doesn't appear on the order when viewing through edit_orders.php

 

Thanks

 

Dave

 

The current "hot fix", so to speak, is to find in admin/order_editor/order.php

	  $index = 0;
	 $orders_products_query = 

	 tep_db_query("
	 SELECT 
	  op.orders_products_id, 
	  op.products_name, 
	  op.products_model, 
	  op.products_price,
	  op.products_tax, 
	  op.products_quantity, 
	  op.final_price, 
	  p.products_tax_class_id,
	  p.products_weight,
	  p.products_id
	 FROM " . TABLE_ORDERS_PRODUCTS . " op
	 INNER JOIN " . TABLE_PRODUCTS . " p
	 ON op.products_id = p.products_id
	 WHERE orders_id = '" . (int)$order_id . "'
	 ORDER BY op.orders_products_id");

	 while ($orders_products = tep_db_fetch_array($orders_products_query)) {
	   $this->products[$index] = array(		
	   'qty' => $orders_products['products_quantity'],
	   'name' => $orders_products['products_name'],
	   'model' => $orders_products['products_model'],
	   'tax' => $orders_products['products_tax'],

	   'tax_description' => 
   tep_get_tax_description($orders_products['products_tax_class_id'], $this->delivery["country_id"], $this->delivery["zone_id"]),

	   'price' => $orders_products['products_price'],
	   'final_price' => $orders_products['final_price'],
	   'weight' => $orders_products['products_weight'],
	   'products_id' => $orders_products['products_id'],
	   'orders_products_id' => $orders_products['orders_products_id']);

and change it to

	  $index = 0;
	   $orders_products_query = tep_db_query("select orders_products_id, products_id, products_name, products_model, products_price, products_tax, products_quantity, final_price from " . TABLE_ORDERS_PRODUCTS . " where orders_id = '" . (int)$order_id . "' order by orders_products_id");

	   while ($orders_products = tep_db_fetch_array($orders_products_query)) {

		 $orders_products_tax_query = tep_db_query("select products_tax_class_id from " .TABLE_PRODUCTS . " where products_id = " . $orders_products['products_id'] . "");
		 $orders_products_tax = tep_db_fetch_array($orders_products_tax_query);

		 $this->products[$index] = array(		
		 'qty' => $orders_products['products_quantity'],
		 'name' => $orders_products['products_name'],
		 'model' => $orders_products['products_model'],
		 'tax' => $orders_products['products_tax'],
		 'tax_description' => tep_get_tax_description($orders_products_tax['products_tax_class_id'], $this->delivery["country_id"], $this->delivery["zone_id"]),
		 'price' => $orders_products['products_price'],
		 'final_price' => $orders_products['final_price'],
		 'products_id' => $orders_products['products_id'],
		 'orders_products_id' => $orders_products['orders_products_id']);

Edited by djmonkey1

Do, or do not. There is no try.

 

Order Editor 5.0.6 "Ultra Violet" is now available!

For support or to post comments, suggestions, etc, please visit the Order Editor support thread.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...