djmonkey1 Posted June 5, 2007 Share Posted June 5, 2007 This occurred whencommens where already in the comments box (written both by the customer at time of checkout and by adminon the admin/orders page. When we edited the order (I think we deleted an item) and clicked update is when the error message occured. Hope this might help in solving the issue... Thanks Let me know what version of MySQL you're using, and also if you could re-create the problem and post the exact error message you see that would let me get a better idea of how to address this. 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 June 5, 2007 Share Posted June 5, 2007 It works fine, there's only a little problem. The Tax from the shipping is counting twice in the order total ex TAX. Like this: Product (incl TAX): 10.00 Subtotal products (ex. VAT): 8.40 Shipping (incl. TAX): 5.00 TAX 19%: 2.40 Order Total (ex TAX): 13.40 (0.80 to much) Total (incl TAX): 15.00 Do you now what's wrong?? Your shipping charge is 5.00, which is actually 4.20 for shipping and 0.80 for tax, so you need to add 4.20 to the ot_netto instead of 5.00. 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...
One2remember Posted June 5, 2007 Share Posted June 5, 2007 Your shipping charge is 5.00, which is actually 4.20 for shipping and 0.80 for tax, so you need to add 4.20 to the ot_netto instead of 5.00. I don't understand. Must I change the shippingcosts in excl TAX if I change the order in order editor?? All mine shippingcosts arie incl TAX. Quote Link to comment Share on other sites More sharing options...
One2remember Posted June 5, 2007 Share Posted June 5, 2007 This is an example of an order this I hav'nt change with the editor. Product: € 20.90 Total from products Excl. TAX: € 17,56 Shipping : € 5,50 (Total Excl. TAX: € 22,18) TAX 19 %: € 4,22 Total: € 26,40 This is the same order if I only used the update button in the editor. The Total Excl. TAX is changed. Product: € 20.90 Total from products Excl. TAX: € 17,56 Shipping : € 5,50 (Total Excl. TAX: € 23,06) TAX 19 %: € 4,22 Total: € 26,40 I hope you can see what I meen. Quote Link to comment Share on other sites More sharing options...
djmonkey1 Posted June 5, 2007 Share Posted June 5, 2007 (edited) I don't understand. Must I change the shippingcosts in excl TAX if I change the order in order editor?? All mine shippingcosts arie incl TAX. In your admin/edit_orders.php file find if ( ($ot_class != "ot_tax") && ($ot_class != "ot_netto") && ($ot_class != "ot_total") ) { $RunningTotalEX += $ot_value; } and change it to if ( ($ot_class != "ot_tax") && ($ot_class != "ot_netto") && ($ot_class != "ot_total") ) { if ($ot_class == "ot_shipping") { $RunningTotalEX += $ot_value; $RunningTotalEX -= ( ($ot_value * $_POST['update_shipping_tax']) / ($_POST['update_shipping_tax'] + 100) ); } else { $RunningTotalEX += $ot_value; } } Edited June 5, 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...
One2remember Posted June 5, 2007 Share Posted June 5, 2007 Stew, You did it again! Thanks! Quote Link to comment Share on other sites More sharing options...
djmonkey1 Posted June 6, 2007 Share Posted June 6, 2007 Stew, You did it again! Thanks! You're welcome- there is still a change that will need to be made to the Add a Product case or you will have the same problem when adding products. But, until that is fixed, if/when you do add a product, once you're done just hit "update" and the totals will be corrected. 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 June 6, 2007 Share Posted June 6, 2007 Stew, You did it again! Thanks! This is the fix for the add a product case. In your admin/edit_orders.php file find //just adding in shipping tax, don't mind me $ot_shipping_query = tep_db_query(" SELECT value FROM " . TABLE_ORDERS_TOTAL . " WHERE orders_id = '" . (int)$oID . "' AND class = 'ot_shipping'"); while ($ot_shipping_info = tep_db_fetch_array($ot_shipping_query)) { $ot_shipping_value = $ot_shipping_info['value']; if (DISPLAY_PRICE_WITH_TAX == 'true') { $RunningTax[$default_tax_name] += ($ot_shipping_value * $order->info['shipping_tax'] / ($order->info['shipping_tax'] + 100)); } else { $RunningTax[$default_tax_name] += (($order->info['shipping_tax'] / 100) * $ot_shipping_value); }// end if (DISPLAY_PRICE_WITH_TAX == 'true') { }// end while and change it to //just adding in shipping tax, don't mind me $shipping_tax = 0; $ot_shipping_query = tep_db_query(" SELECT value FROM " . TABLE_ORDERS_TOTAL . " WHERE orders_id = '" . (int)$oID . "' AND class = 'ot_shipping'"); while ($ot_shipping_info = tep_db_fetch_array($ot_shipping_query)) { $ot_shipping_value = $ot_shipping_info['value']; if (DISPLAY_PRICE_WITH_TAX == 'true') { $RunningTax[$default_tax_name] += ($ot_shipping_value * $order->info['shipping_tax'] / ($order->info['shipping_tax'] + 100)); $shipping_tax += ($ot_shipping_value * $order->info['shipping_tax'] / ($order->info['shipping_tax'] + 100)); } else { $RunningTax[$default_tax_name] += (($order->info['shipping_tax'] / 100) * $ot_shipping_value); }// end if (DISPLAY_PRICE_WITH_TAX == 'true') { }// end while Next find // 2.2.2.4 TotalEX $Query1 = 'SELECT sum(value) AS total_value from ' . TABLE_ORDERS_TOTAL . ' WHERE class != "ot_total" AND class != "ot_tax" AND class != "ot_netto" AND orders_id= "' . (int)$oID . '"'; $resultEX = tep_db_query($Query1); $rowEX = tep_db_fetch_array($resultEX); $TotalEX = $rowEX['total_value']; and change it to // 2.2.2.4 TotalEX $Query1 = 'SELECT sum(value) AS total_value from ' . TABLE_ORDERS_TOTAL . ' WHERE class != "ot_total" AND class != "ot_tax" AND class != "ot_netto" AND orders_id= "' . (int)$oID . '"'; $resultEX = tep_db_query($Query1); $rowEX = tep_db_fetch_array($resultEX); $TotalEX = $rowEX['total_value']; $TotalEX -= $shipping_tax; Test out the add a product function and make sure it totals up the same as when pressing the update button. 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...
tazthedev Posted June 13, 2007 Share Posted June 13, 2007 Hi, Im having some problems with edit_order. The taxes are correct with http://www.nplus.ca/catalog/admin/orders.php, but as soon as i edit the order, the taxes appears as above. I you look at the amount, 3.38$, is the total of the 2 taxes. It should be TPS = 1.46$ and TVQ = 1.92$. The field 'taxe' should be empty because i am not using the field "taxe envoie" since the taxes (tps, tvq) should be incl. the shipping. Another problem.. If im setting up the shipping (Frais de transport non déterminé).. and set it up to 5.00$, the amount will be added to the Total: 27.58 + 5.00=32.58$ (total) But the problem is, that it should be 24.20$ + 5.00$ (shipping) + TPS (6%= 1.75$) + TVQ (7.5%=2.32$) = 33.27$(Total) Another problem is when im updating the order... The taxes will appears on only 1 line... TPS + TVQ = ..... It should be as you know, on 2 separate line. Plz... help !!!! Quote Link to comment Share on other sites More sharing options...
shawnm Posted June 15, 2007 Share Posted June 15, 2007 Default shipping tax rate is not working I don't believe. I have changed the default shipping from 0 to 8 in the shipping_tax table. Also, in the my store config in the admin "select a tax class" I have defined as Taxable goods. The default tax rate appears for the products, but not for shipping. I can of course manually type it in. Just wondering if I missing something else, or is it a bug? Great contribution by the way. Using v3.0.2.1 with bug fixed, digitally remastered version. Thank you, Shawn Quote Link to comment Share on other sites More sharing options...
djmonkey1 Posted June 19, 2007 Share Posted June 19, 2007 Default shipping tax rate is not working I don't believe. I have changed the default shipping from 0 to 8 in the shipping_tax table. Also, in the my store config in the admin "select a tax class" I have defined as Taxable goods. The default tax rate appears for the products, but not for shipping. I can of course manually type it in. Just wondering if I missing something else, or is it a bug? Great contribution by the way. Using v3.0.2.1 with bug fixed, digitally remastered version. Thank you, Shawn Given that you have changed the default for the "shipping_tax" column of the orders table to "8", every order placed after you made that change should automatically have a shipping tax rate set at 8. Anything placed before the change would be at "0" and you would have to manually change them to "8". The "shipping_tax" field has no bearing whatsoever on the tax rate charged per product. 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 June 19, 2007 Share Posted June 19, 2007 Hi, Im having some problems with edit_order. The taxes are correct with http://www.nplus.ca/catalog/admin/orders.php, but as soon as i edit the order, the taxes appears as above. I you look at the amount, 3.38$, is the total of the 2 taxes. It should be TPS = 1.46$ and TVQ = 1.92$. The field 'taxe' should be empty because i am not using the field "taxe envoie" since the taxes (tps, tvq) should be incl. the shipping. Another problem.. If im setting up the shipping (Frais de transport non déterminé).. and set it up to 5.00$, the amount will be added to the Total: 27.58 + 5.00=32.58$ (total) But the problem is, that it should be 24.20$ + 5.00$ (shipping) + TPS (6%= 1.75$) + TVQ (7.5%=2.32$) = 33.27$(Total) Another problem is when im updating the order... The taxes will appears on only 1 line... TPS + TVQ = ..... It should be as you know, on 2 separate line. Plz... help !!!! It sounds like you are having a problem similar to the one reported recently by the user scrapbook (see page 97 of this thread for more). Based on the fact that Order Editor takes two tax lines, TPS and TVQ, and turns them into one tax line called TPS + TVQ, I would venture to say that you have a customized tax scenario that basically allows you to have multiple tax classes for each product, or perhaps more accurately, one that allows you to have multiple rates within a tax class but will store each tax rate as an individual entry within the order information. If this is the case, Order Editor as it now stands simply will not work for you. It is a problem that I'm looking at but as of right now there is no workaround or fix that I know of. Here is the explanation I gave to scrapbook concerning this problem: Won't it won't do is cut the tax calculation onto two lines as you want to do. The only way, in a stock osCommerce scenario, to have multiple line items for taxes is to have multiple tax classes, however it is not possible to assign more than one tax class to an item (I think allowing this would actually be the easiest way to solve this problem within the current osCommerce structure). To accomplish what you want to do (one tax class, multiple rates, line item for each rate on the invoice) you had to customize the osCommerce tax engine, so you will have to customize Order Editor to work this way as well. 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...
Recidivist Posted June 21, 2007 Share Posted June 21, 2007 Hi, I am seeing a problem with the ordering of totals (subtotal, shipping cost, tax and total) on both edit_orders.php and subsequently on orders.php. I am using v3.0.2.1 of Order Editor. It's not a great problem that the ordering in the order total block is wrong on edit_orders (since the calculations are all correct), but it is a problem on orders.php because it also affects all our invoices, packing slips etc. Currently, we see things in this order on orders.php (when manually creating an order using edit_orders.php - not when orders are automatically created by customers): VAT (Tax): Subtotal (ex vat): Delivery: Total: But we need it to be ordered like: Subtotal (ex vat): Delivery: VAT (tax): Total On orders.php it looks like this: On edit_orders.php it looks like this: Can someone point me in the right direction for finding the code which can fix this ordering? Many thanks KB Quote Network Webcams - The IP Camera Specialists Link to comment Share on other sites More sharing options...
djmonkey1 Posted June 21, 2007 Share Posted June 21, 2007 Hi, I am seeing a problem with the ordering of totals (subtotal, shipping cost, tax and total) on both edit_orders.php and subsequently on orders.php. I am using v3.0.2.1 of Order Editor. It's not a great problem that the ordering in the order total block is wrong on edit_orders (since the calculations are all correct), but it is a problem on orders.php because it also affects all our invoices, packing slips etc. Currently, we see things in this order on orders.php (when manually creating an order using edit_orders.php - not when orders are automatically created by customers): VAT (Tax): Subtotal (ex vat): Delivery: Total: But we need it to be ordered like: Subtotal (ex vat): Delivery: VAT (tax): Total On orders.php it looks like this: On edit_orders.php it looks like this: Can someone point me in the right direction for finding the code which can fix this ordering? Many thanks KB Order Editor does not have the ability to manually create orders, only edit existing orders. It sounds like you are using a manual order entry tool to generate the order in the first place, then Order Editor to finish it up. Whatever code from the manual order entry contribution that you are using that actually generates the order in the first place is where you should look to resolve this issue. 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...
shawnm Posted June 21, 2007 Share Posted June 21, 2007 DJmonkey... thanks a bunch! I see that now... I never created a new order to try to edit it... I was just looking at old orders. Thanks for the help on the tax issue! Shawn Quote Link to comment Share on other sites More sharing options...
Recidivist Posted June 21, 2007 Share Posted June 21, 2007 Order Editor does not have the ability to manually create orders, only edit existing orders. It sounds like you are using a manual order entry tool to generate the order in the first place, then Order Editor to finish it up. Whatever code from the manual order entry contribution that you are using that actually generates the order in the first place is where you should look to resolve this issue. Ah, you're right. We are using a manual order entry contribution to create a blank order, then Order Editor to amend it. The manual order entry script places all items in the correct order initially, but when the order is amended in edit_orders.php the VAT line is moved to the top of the list. I will now investigate this a little further, but I'm not convinced that the Order Editor isn't somehow involved. KB Quote Network Webcams - The IP Camera Specialists Link to comment Share on other sites More sharing options...
Recidivist Posted June 21, 2007 Share Posted June 21, 2007 Ah, you're right. We are using a manual order entry contribution to create a blank order, then Order Editor to amend it. The manual order entry script places all items in the correct order initially, but when the order is amended in edit_orders.php the VAT line is moved to the top of the list. I will now investigate this a little further, but I'm not convinced that the Order Editor isn't somehow involved. KB After further investigation the manual order entry tool is adding orders into the database with the correct order as follows: Subtotal: 1 Discount: 2 Delivery: 3 Tax/VAT: 4 Total: 5 After editing in edit_orders.php the ordering is changed as follows: Tax/VAT: 3 Subtotal: 4 Discount: 5 Delivery: 6 Total: 7 Any indication on where in edit_orders.php I can fix this would be massively helpful. Cheers KB Quote Network Webcams - The IP Camera Specialists Link to comment Share on other sites More sharing options...
Recidivist Posted June 21, 2007 Share Posted June 21, 2007 DJMonkey, your comment put me on the right track and I fixed the problem. Thank you. In edit_order.php you can set the sort order for the data (on mine it's line 401). I simply set this to match the tax sort order as set by my order creation script. Now that's fixed I've discovered another problem. When I add a discount amount in before shipping (directly after subtotal) the tax rate does not update accordingly - i.e. it still shows the tax rate from the non-discounted figure. Any ideas how to force the tax to be recalculated when a discount is applied? Cheers KB Quote Network Webcams - The IP Camera Specialists Link to comment Share on other sites More sharing options...
djmonkey1 Posted June 22, 2007 Share Posted June 22, 2007 DJMonkey, your comment put me on the right track and I fixed the problem. Thank you. In edit_order.php you can set the sort order for the data (on mine it's line 401). I simply set this to match the tax sort order as set by my order creation script. Now that's fixed I've discovered another problem. When I add a discount amount in before shipping (directly after subtotal) the tax rate does not update accordingly - i.e. it still shows the tax rate from the non-discounted figure. Any ideas how to force the tax to be recalculated when a discount is applied? Cheers KB Do you charge tax on shipping? 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...
Recidivist Posted June 22, 2007 Share Posted June 22, 2007 Yes, 17.5%. Our tax rules are all set up correctly. KB Quote Network Webcams - The IP Camera Specialists Link to comment Share on other sites More sharing options...
djmonkey1 Posted June 22, 2007 Share Posted June 22, 2007 Yes, 17.5%. Our tax rules are all set up correctly. KB After re-reading your question it sounds like you're asking about the javascript "on-the-fly" calculator not updating the tax component when you add in a custom discount. The fix below does not address this, it only makes the calculations correct upon pressing the "update" button. The javascript calculator is a neat tool, but it is not an essential part of the code. In admin/edit_orders.php find if($ot_class == "ot_shipping")//a good place to add in custom total components and change it to if( ($ot_class == "ot_shipping") || ($ot_class == "ot_custom") )//a good place to add in custom total components Next find //just adding in shipping tax, don't mind me $ot_shipping_query = tep_db_query(" SELECT value FROM " . TABLE_ORDERS_TOTAL . " WHERE orders_id = '" . (int)$oID . "' AND class = 'ot_shipping'"); and change it to //just adding in shipping tax, don't mind me $ot_shipping_query = tep_db_query(" SELECT value FROM " . TABLE_ORDERS_TOTAL . " WHERE orders_id = '" . (int)$oID . "' AND (class = 'ot_shipping' OR class = 'ot_custom')"); 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...
Recidivist Posted June 22, 2007 Share Posted June 22, 2007 After re-reading your question it sounds like you're asking about the javascript "on-the-fly" calculator not updating the tax component when you add in a custom discount. The fix below does not address this, it only makes the calculations correct upon pressing the "update" button. The javascript calculator is a neat tool, but it is not an essential part of the code. In admin/edit_orders.php find if($ot_class == "ot_shipping")//a good place to add in custom total components and change it to if( ($ot_class == "ot_shipping") || ($ot_class == "ot_custom") )//a good place to add in custom total components Next find //just adding in shipping tax, don't mind me $ot_shipping_query = tep_db_query(" SELECT value FROM " . TABLE_ORDERS_TOTAL . " WHERE orders_id = '" . (int)$oID . "' AND class = 'ot_shipping'"); and change it to //just adding in shipping tax, don't mind me $ot_shipping_query = tep_db_query(" SELECT value FROM " . TABLE_ORDERS_TOTAL . " WHERE orders_id = '" . (int)$oID . "' AND (class = 'ot_shipping' OR class = 'ot_custom')"); Thank you. This works beautifully. The javascript updates aren't correct, but like you say, that's not important. What is important is that the figure added to the database and subsequently displayed is correct. Thanks again. KB Quote Network Webcams - The IP Camera Specialists Link to comment Share on other sites More sharing options...
tazthedev Posted June 22, 2007 Share Posted June 22, 2007 It sounds like you are having a problem similar to the one reported recently by the user scrapbook (see page 97 of this thread for more). Based on the fact that Order Editor takes two tax lines, TPS and TVQ, and turns them into one tax line called TPS + TVQ, I would venture to say that you have a customized tax scenario that basically allows you to have multiple tax classes for each product, or perhaps more accurately, one that allows you to have multiple rates within a tax class but will store each tax rate as an individual entry within the order information. If this is the case, Order Editor as it now stands simply will not work for you. It is a problem that I'm looking at but as of right now there is no workaround or fix that I know of. Here is the explanation I gave to scrapbook concerning this problem: Hi Dj, In fact i will take the only solution for us.. taxes on 1 line. But i'd like to modify them. If you look at the following images, i'd like the "taxe envoie" to be the same % (some customers will only pay 6% Ontario, some others will pay 6% + 7.5% Qc, some other no tax at all = United states) as soon as we click the modify button for the invoice (instead of being 13.95% by default). Every items choosed by the customers are the same taxes amount. I just need to put that amount onto "taxe envoie" as soon as i am modifying the invoice... Again, if you look at the following image, i'd like the "taxes:" 's amount, to be added automatically to the taxes (6% + 7.5%...) . So the "taxes:" field wont be visible. And the "sous-total" is not at the same place when using the Apply button.. I need to resolve that problem.. Quote Link to comment Share on other sites More sharing options...
tazthedev Posted June 22, 2007 Share Posted June 22, 2007 The sub total isnt always at the right position. Ive looked at the phpmyadmin and the ot_subtotal is most of the time, set to 3-4 .. so, the taxes appears before the subtotal. I tried to modify the code, but i cant make it to work properly. In fact, when im using the apply button, the current record, class = ot_subtotal should be set on 1. Quote Link to comment Share on other sites More sharing options...
djmonkey1 Posted June 23, 2007 Share Posted June 23, 2007 The sub total isnt always at the right position. Ive looked at the phpmyadmin and the ot_subtotal is most of the time, set to 3-4 .. so, the taxes appears before the subtotal. I tried to modify the code, but i cant make it to work properly. In fact, when im using the apply button, the current record, class = ot_subtotal should be set on 1. For the sort order issue, try this: Find in admin/edit_orders.php if (($val > 0) && ($ot_tax_found != 1)) { $sort_order++; $Query = "INSERT INTO " . TABLE_ORDERS_TOTAL . " SET and change it to if (($val > 0) && ($ot_tax_found != 1)) { $Query = "INSERT INTO " . TABLE_ORDERS_TOTAL . " SET Next find extract($total_details,EXTR_PREFIX_ALL,"ot"); if (trim($ot_title)) { $sort_order++; if ($ot_class == "ot_subtotal") { $ot_value = $RunningSubTotal; } if ($ot_class == "ot_tax") { $ot_value = $RunningTax[preg_replace("/:$/","",$ot_title)]; } if ($ot_class == "ot_total") { $ot_value = $RunningTotal; if ( !$ot_subtotal_found ) { // There was no subtotal on this order, lets add the running subtotal in. $ot_value += $RunningSubTotal; } } and change it to extract($total_details,EXTR_PREFIX_ALL,"ot"); if (trim($ot_title)) { if ($ot_class == "ot_subtotal") { $ot_value = $RunningSubTotal; } if ($ot_class == "ot_tax") { $ot_value = $RunningTax[preg_replace("/:$/","",$ot_title)]; } if ($ot_class == "ot_total") { $ot_value = $RunningTotal; if ( !$ot_subtotal_found ) { // There was no subtotal on this order, lets add the running subtotal in. $ot_value += $RunningSubTotal; } } $sort_order++; This should prevent this problem from occurring on any orders that have not previously been edited with Order Editor. For the orders that have already been edited and have the wrong sort order, you will have to manually change the sort order in the database. 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.