Jump to content
jhilgeman

NEW! Complete Order Editing Tool!

Recommended Posts

If you ever wanted to update any part of an order's information, add new products to an order, insert new information like coupons, change taxes on a specific product, and so on, here's the tool. I truly hope this makes life easier for some of you.

http://www.oscommerce.com/community/contributions,1435

- Jonathan

 

I am in the process of developing a marketing business with OSC and I am very interested in upgrading the orders module as suggested in this thread. I have downloaded the files and am ready to carry out the upgrade however, I have very limited knowledge of working with MYSQL database. I am not sure how to ALTER TABLE as outlined in Step 1. I feel comfortable completing Steps 2 & 3.

 

I need help in making the Database table changes.

 

Thanks,

Cliff

Share this post


Link to post
Share on other sites
Before edit

409 158 Varav svensk moms (25%): 490.50 Kr 2452.0000 ot_total_moms 1

410 158 Total: <b>2,452.00 Kr</b> 2452.0000 ot_total 4

 

After edit

423 158 Discount 10% -242.50 Kr -242.5000 ot_custom 2

410 158 Total: <b>2,210.00 Kr</b> 2210.0000 ot_total 3

 

So the calculating is right but it wont print tax on my invoice after editing.

 

It replaces tax with discount on my invoice. Everything else works OK. But I need to show both tax and discount on the invoice.

 

Order Editor turned a 25% tax component of 490.50 into a 10% discount? That's a neat trick.

 

Order Editor should create a new tax component using the ot_total_moms class once you've made the changes I recommended. Have you customized the tax zones and/or classes in any way?


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
I am in the process of developing a marketing business with OSC and I am very interested in upgrading the orders module as suggested in this thread. I have downloaded the files and am ready to carry out the upgrade however, I have very limited knowledge of working with MYSQL database. I am not sure how to ALTER TABLE as outlined in Step 1. I feel comfortable completing Steps 2 & 3.

 

I need help in making the Database table changes.

 

Thanks,

Cliff

 

Well, you need to be able to access the database for a number of reasons, one of those in order to install contributions like this one. Many webhosts allow access to the database via phpMyAdmin or a similar tool. Assuming you are using phpMyAdmin, once you have accessed the database you should see a tab somewhere near the top that says "SQL". Click on that tab and you should see a textbox to enter the command in and controls to submit the form.

 

Post here again once you have accessed your database if you need more specific information.


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
Order Editor turned a 25% tax component of 490.50 into a 10% discount? That's a neat trick.

 

Order Editor should create a new tax component using the ot_total_moms class once you've made the changes I recommended. Have you customized the tax zones and/or classes in any way?

 

All prices are including tax. The "ot_total_moms" calculates how much (20%) of the total are tax.

 

So I dont have a price without tax and the add tax to it. The tax are already included.

 

Maybe this is why order editor takes it for a custom value?


// Buck

Share this post


Link to post
Share on other sites
All prices are including tax. The "ot_total_moms" calculates how much (20%) of the total are tax.

 

So I dont have a price without tax and the add tax to it. The tax are already included.

 

Maybe this is why order editor takes it for a custom value?

 

The only way there is a ot_custom total component is if you added it in. There is no way Order Editor changed

Varav svensk moms (25%):	  490.50

to

Discount 10%							   -242.50

It is not possible. They are two completely different and separate entries. In the extract you posted from your db those entries have two different entries for orders_total_id, ergo they are two separate and distinct entries.

 

As far as your tax scenario, you're saying that for every product in an order the tax rate saved with the product (ie the number displayed by Order Editor in between "Model" and "Price (base)") is 0.00?

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
The only way there is a ot_custom total component is if you added it in. There is no way Order Editor changed
Varav svensk moms (25%):	  490.50

to

Discount 10%							   -242.50

It is not possible. They are two completely different and separate entries. In the extract you posted from your db those entries have two different entries for orders_total_id, ergo they are two separate and distinct entries.

 

As far as your tax scenario, you're saying that for every product in an order the tax rate saved with the product (ie the number displayed by Order Editor in between "Model" and "Price (base)") is 0.00?

 

Yes, that number is 0.


// Buck

Share this post


Link to post
Share on other sites
Yes, that number is 0.

 

So your Price (base) includes tax as well as your Price (excl). Hmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm.

 

So if you had a product with a base price of 120, then the tax on that product would be 20.

 

To determine this you would use an equation such as (120 - (120/1.2)) / 100 = .2 (ie a tax of 20%) or to just get the tax dollar amount 120 - (120/1.2) = 20.

 

Is your tax rate always 20%, no exceptions?


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
So your Price (base) includes tax as well as your Price (excl). Hmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm.

 

So if you had a product with a base price of 120, then the tax on that product would be 20.

 

To determine this you would use an equation such as (120 - (120/1.2)) / 100 = .2 (ie a tax of 20%) or to just get the tax dollar amount 120 - (120/1.2) = 20.

 

Is your tax rate always 20%, no exceptions?

 

Well my tax rate is 25%

 

Products base price is 100 + 25% tax

 

100 * 1.25 = 125

or

100*0.25 + 100 = 125

 

Price including tax is 125, then the tax is 20% of the total value

 

125 * 0.2 = 25

 

Tax rate is always 25%


// Buck

Share this post


Link to post
Share on other sites
Well my tax rate is 25%

 

Products base price is 100 + 25% tax

 

100 * 1.25 = 125

or

100*0.25 + 100 = 125

 

Price including tax is 125, then the tax is 20% of the total value

 

125 * 0.2 = 25

 

Tax rate is always 25%

 

Do you have Display Prices with Tax set to 'true' or 'false'?


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
Well my tax rate is 25%

 

Products base price is 100 + 25% tax

 

100 * 1.25 = 125

or

100*0.25 + 100 = 125

 

Price including tax is 125, then the tax is 20% of the total value

 

125 * 0.2 = 25

 

Tax rate is always 25%

 

In admin/edit_orders.php change

				$RunningTax[$products_details['tax_description']] += (($products_details['tax']/100) * ($products_details['qty'] * $products_details['final_price']));

to

				$RunningTax[$products_details['tax_description']] += ((0.2) * ($products_details['qty'] * $products_details['final_price']));

 

Then change

		$RunningTax[$order->products[$i]['tax_description']] += (($order->products[$i]['tax'] / 100) * ($order->products[$i]['qty'] * $order->products[$i]['final_price']));

to

		$RunningTax[$order->products[$i]['tax_description']] += ((0.2) * ($order->products[$i]['qty'] * $order->products[$i]['final_price']));

 

Upload the changed file, press the update button in Order Editor for one of the orders that has the wrong information already stored, and see how it works then.


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
In admin/edit_orders.php change
				$RunningTax[$products_details['tax_description']] += (($products_details['tax']/100) * ($products_details['qty'] * $products_details['final_price']));

to

				$RunningTax[$products_details['tax_description']] += ((0.2) * ($products_details['qty'] * $products_details['final_price']));

 

Then change

		$RunningTax[$order->products[$i]['tax_description']] += (($order->products[$i]['tax'] / 100) * ($order->products[$i]['qty'] * $order->products[$i]['final_price']));

to

		$RunningTax[$order->products[$i]['tax_description']] += ((0.2) * ($order->products[$i]['qty'] * $order->products[$i]['final_price']));

 

Upload the changed file, press the update button in Order Editor for one of the orders that has the wrong information already stored, and see how it works then.

 

It works much better now.

 

But displayed tax is of final price before discount, it must be tax of actual price.

 

Both tax and discount is printed twice on invoice


// Buck

Share this post


Link to post
Share on other sites
It works much better now.

 

But displayed tax is of final price before discount, it must be tax of actual price.

 

Both tax and discount is printed twice on invoice

 

Apparently the database was updated twice for some reason?

 

Anyway it works now except for that the tax is calculated from the value of orders_products - final_price which is never updated.

 

It is just orders-total class ot_total that is updated with the discounted price.

 

Thats why I get the tax calculated from the standard value instead of the discounted value.

 

Any hint of how to solve this?


// Buck

Share this post


Link to post
Share on other sites

hello, this is a great contribution, I have a flowerstore and I have installed in the osc the shipdate module. I have try to insert this shipdate module to the order editor but i am not able to do it. Can anybody insert the shipdate to the ordereditor?

Thanks in advance

Share this post


Link to post
Share on other sites

[hello, this is a great contribution, I have a flowerstore and I have installed in the osc the shipdate module. I have try to insert this shipdate module to the order editor but i am not able to do it. Can anybody insert the shipdate to the ordereditor?

Thanks in advance

Share this post


Link to post
Share on other sites
Well, you need to be able to access the database for a number of reasons, one of those in order to install contributions like this one. Many webhosts allow access to the database via phpMyAdmin or a similar tool. Assuming you are using phpMyAdmin, once you have accessed the database you should see a tab somewhere near the top that says "SQL". Click on that tab and you should see a textbox to enter the command in and controls to submit the form.

 

Sweet! and just what I needed to install this excellent contribution.

 

I was in the same boat. I've been working with oscommerce and sts for a month or so and I was stumped with this process until reading your instructions which were perfect for my situation. I had made one other edit manually to the database to add the CVV2 contribution, but I did it the hard way apparently... :blink: This was much easier, done the right way. I'm learning so much by working with all these improvements.

 

I got this contribution installed, and it seems to work great. I don't have any tax issues because we will be shipping food items in the US and there's no tax in Washington state, even on shipping, if the product is tax free.

 

Is there a way to show the CVV number in the edit page? I'm just thinking that a customer might want to change their card info and would like to be able to track that also.

 

Thanks again for the hard work!

Share this post


Link to post
Share on other sites
Well, you need to be able to access the database for a number of reasons, one of those in order to install contributions like this one. Many webhosts allow access to the database via phpMyAdmin or a similar tool. Assuming you are using phpMyAdmin, once you have accessed the database you should see a tab somewhere near the top that says "SQL". Click on that tab and you should see a textbox to enter the command in and controls to submit the form.

 

Post here again once you have accessed your database if you need more specific information.

 

I have access to phpMyAdmin as you suggested and have been able to open the SQL textbox. This is a first for me. Below is the information from the Install.txt file. Am I to copy it entirely into the SQL textbox and Click GO?? Or is there any portion of this that makes up the complete form??? Sorry to sound so ignorant, but I have not worked with database commands before - just microsoft access.

 

ALTER TABLE orders ADD shipping_tax DECIMAL( 7, 4 ) DEFAULT '0.000' NOT NULL ;

INSERT into configuration (configuration_id, configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) values ('', 'Order Editor- Display Payment Method dropdown?', 'DISPLAY_PAYMENT_METHOD_DROPDOWN', 'true', 'Display Payment Method in Order Editor as dropdown menu (true) or as input field (false)', '1', '21', NULL, '2006-04-02 11:51:01', NULL, 'tep_cfg_select_option(array(\'true\', \'false\'),');

 

 

Thanks

Cliff

Share this post


Link to post
Share on other sites
I have access to phpMyAdmin as you suggested and have been able to open the SQL textbox. This is a first for me. Below is the information from the Install.txt file. Am I to copy it entirely into the SQL textbox and Click GO?? Or is there any portion of this that makes up the complete form??? Sorry to sound so ignorant, but I have not worked with database commands before - just microsoft access.

 

That's exactly what I did and - magic! - I'm a newbie to mySql also, but figured that the worst thing that could happen is that I have to reload the database file that I've backed up at least 10 times over the last couple of weeks (I just ftp the whole store folder to my computer). After the sql addtion I did not have any problems finding and replacing the text in the listed documents and adding the new files.

 

I did have one panic when I tried to change an order that had already been made. The "New Order Confirmation" system was sending emails to the buyer with a broken link. It works fine with orders created after the installation.

Share this post


Link to post
Share on other sites
I have access to phpMyAdmin as you suggested and have been able to open the SQL textbox. This is a first for me. Below is the information from the Install.txt file. Am I to copy it entirely into the SQL textbox and Click GO?? Or is there any portion of this that makes up the complete form??? Sorry to sound so ignorant, but I have not worked with database commands before - just microsoft access.

 

Thanks

Cliff

 

Run the entire statement on 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
Is there a way to show the CVV number in the edit page? I'm just thinking that a customer might want to change their card info and would like to be able to track that also.

 

But of course- I'm assuming that your CVV field is stored in the orders table, but I need to know what the actual field name is.


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
hello, this is a great contribution, I have a flowerstore and I have installed in the osc the shipdate module. I have try to insert this shipdate module to the order editor but i am not able to do it. Can anybody insert the shipdate to the ordereditor?

Thanks in advance

 

In what table of the database is the shipdate stored and what is the field named?


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 is just orders-total class ot_total that is updated with the discounted price.

 

Thats why I get the tax calculated from the standard value instead of the discounted value.

 

Any hint of how to solve this?

 

I'm assuming then that you don't charge tax on shipping.

 

If that's the case and only need the discount tax computed in admin/edit_orders.php change

				if($ot_class == "ot_shipping")//a good place to add in custom total components

to

				if($ot_class == "ot_custom")//a good place to add in custom total components

 

then change

		AND class = 'ot_shipping'");

to

		AND class = 'ot_custom'");

 

Finally, make sure "25.00" is always entered into the field in Order Editor usually called "shipping tax". For the sake of simplicity you can simply make this entry 25.00 by default in the database itself.


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
But of course- I'm assuming that your CVV field is stored in the orders table, but I need to know what the actual field name is.

 

I used the contribution named cvv2_2.1

 

It added a field named 'cc_cvv2' to the orders table.

 

I was able to do something that basic by just poking around, although the entry box is as long as the name or credit card number boxes on the input form (I'll poke around a little more to see how those boxes are sized. I just did another backup...so I'm feeling brave again). At least it does work. Inserting that field manually made me realize that I wouldn't be able to manually install the complex sql changes in your contribution. That's what was so great about finding out about copying the text into the SQL box in phpMyAdmin :) I started at page one and made it to about page 20 in this forum before I started jumping around. Finally, page 87, the last page, had me stoked because "cgreid" asked my question, but it was 3am and I waited until this morning to actually try it. I try to read a lot before asking questions. I don't usually like to advertise my inexperience :rolleyes:

 

This is getting to be more fun everyday, er night!

Share this post


Link to post
Share on other sites

Sorry, actual name for the contribution I used is Credit Card With CVV2, originally posted by a guy named Donibane.

Edited by dechamp

Share this post


Link to post
Share on other sites
I used the contribution named cvv2_2.1

 

It added a field named 'cc_cvv2' to the orders table.

 

I was able to do something that basic by just poking around, although the entry box is as long as the name or credit card number boxes on the input form (I'll poke around a little more to see how those boxes are sized. I just did another backup...so I'm feeling brave again). At least it does work. Inserting that field manually made me realize that I wouldn't be able to manually install the complex sql changes in your contribution. That's what was so great about finding out about copying the text into the SQL box in phpMyAdmin :) I started at page one and made it to about page 20 in this forum before I started jumping around. Finally, page 87, the last page, had me stoked because "cgreid" asked my question, but it was 3am and I waited until this morning to actually try it. I try to read a lot before asking questions. I don't usually like to advertise my inexperience :rolleyes:

 

This is getting to be more fun everyday, er night!

 

So you've got the field displayed in Order Editor? But it's not pulling the info from the database or updating it?


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

×