Jump to content
jhilgeman

NEW! Complete Order Editing Tool!

Recommended Posts

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.


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.

Share this post


Link to post
Share on other sites
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.


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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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 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.

Share this post


Link to post
Share on other sites
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.


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.

Share this post


Link to post
Share on other sites
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.


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.

Share this post


Link to post
Share on other sites

Hi,

 

Im having some problems with edit_order.

 

edit_order.JPG

 

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 !!!!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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.


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.

Share this post


Link to post
Share on other sites
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.

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.

Share this post


Link to post
Share on other sites

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:

 

osc-post-01.gif

 

On edit_orders.php it looks like this:

 

osc-post-02.gif

 

Can someone point me in the right direction for finding the code which can fix this ordering?

 

Many thanks

 

KB


Network Webcams - The IP Camera Specialists

Share this post


Link to post
Share on other sites
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:

 

osc-post-01.gif

 

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.


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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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


Network Webcams - The IP Camera Specialists

Share this post


Link to post
Share on other sites
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


Network Webcams - The IP Camera Specialists

Share this post


Link to post
Share on other sites

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


Network Webcams - The IP Camera Specialists

Share this post


Link to post
Share on other sites
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?


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.

Share this post


Link to post
Share on other sites
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')");


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.

Share this post


Link to post
Share on other sites
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


Network Webcams - The IP Camera Specialists

Share this post


Link to post
Share on other sites
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..

 

 

edit_order.JPG

edit_order2.jpg

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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.


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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×