Kopite Posted August 31, 2007 Share Posted August 31, 2007 From what I said before, I don't mean that you should re-do the entire contribution, just that one file. Thanks, done. Fixed. Cheers. :D Quote Link to comment Share on other sites More sharing options...
jeffwdesign Posted September 1, 2007 Share Posted September 1, 2007 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. Quote Link to comment Share on other sites More sharing options...
djmonkey1 Posted September 1, 2007 Share Posted September 1, 2007 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? Quote 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 More sharing options...
djmonkey1 Posted September 1, 2007 Share Posted September 1, 2007 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. Quote 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 More sharing options...
jeffwdesign Posted September 1, 2007 Share Posted September 1, 2007 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. Quote Link to comment Share on other sites More sharing options...
djmonkey1 Posted September 1, 2007 Share Posted September 1, 2007 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? Quote 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 More sharing options...
jeffwdesign Posted September 1, 2007 Share Posted September 1, 2007 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? Quote Link to comment Share on other sites More sharing options...
jeffwdesign Posted September 1, 2007 Share Posted September 1, 2007 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. Quote Link to comment Share on other sites More sharing options...
djmonkey1 Posted September 1, 2007 Share Posted September 1, 2007 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. Quote 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 More sharing options...
jeffwdesign Posted September 1, 2007 Share Posted September 1, 2007 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? Quote Link to comment Share on other sites More sharing options...
jeffwdesign Posted September 2, 2007 Share Posted September 2, 2007 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: Quote Link to comment Share on other sites More sharing options...
djmonkey1 Posted September 2, 2007 Share Posted September 2, 2007 (edited) 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 September 2, 2007 by djmonkey1 Quote 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 More sharing options...
jeffwdesign Posted September 2, 2007 Share Posted September 2, 2007 YES! You darling! Out of date osC code in general.php. Replaced those three functions with the same ones from the MS2.2 RC1 file, and the problem is fixed. Thank you for taking the time to look at this and throw me a hint. Quote Link to comment Share on other sites More sharing options...
jeffwdesign Posted September 2, 2007 Share Posted September 2, 2007 (edited) 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 September 2, 2007 by jeffwdesign Quote Link to comment Share on other sites More sharing options...
analli Posted September 2, 2007 Share Posted September 2, 2007 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 Quote Link to comment Share on other sites More sharing options...
djmonkey1 Posted September 2, 2007 Share Posted September 2, 2007 dj, thanks a lot. dca Posted. Quote 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 More sharing options...
djmonkey1 Posted September 2, 2007 Share Posted September 2, 2007 Thanks DJ. You're a real champion here. Anthony You're welcome. Quote 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 More sharing options...
djmonkey1 Posted September 2, 2007 Share Posted September 2, 2007 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. Quote 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 More sharing options...
Guest Posted September 3, 2007 Share Posted September 3, 2007 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 Quote Link to comment Share on other sites More sharing options...
djmonkey1 Posted September 3, 2007 Share Posted September 3, 2007 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. Quote 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 More sharing options...
♥stubbsy Posted September 3, 2007 Share Posted September 3, 2007 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? Quote Link to comment Share on other sites More sharing options...
♥stubbsy Posted September 3, 2007 Share Posted September 3, 2007 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 Quote Link to comment Share on other sites More sharing options...
djmonkey1 Posted September 3, 2007 Share Posted September 3, 2007 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. Quote 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 More sharing options...
djmonkey1 Posted September 3, 2007 Share Posted September 3, 2007 (edited) 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 September 3, 2007 by djmonkey1 Quote 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 More sharing options...
djmonkey1 Posted September 3, 2007 Share Posted September 3, 2007 (edited) 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 September 3, 2007 by djmonkey1 Quote 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 More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.