cgreid Posted March 17, 2007 Share Posted March 17, 2007 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 Quote Link to comment Share on other sites More sharing options...
djmonkey1 Posted March 17, 2007 Share Posted March 17, 2007 Before edit409 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? 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 March 17, 2007 Share Posted March 17, 2007 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. 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...
Backard Posted March 17, 2007 Share Posted March 17, 2007 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? Quote // Buck Link to comment Share on other sites More sharing options...
djmonkey1 Posted March 18, 2007 Share Posted March 18, 2007 (edited) 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 March 18, 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...
Backard Posted March 18, 2007 Share Posted March 18, 2007 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. Quote // Buck Link to comment Share on other sites More sharing options...
djmonkey1 Posted March 18, 2007 Share Posted March 18, 2007 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? 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...
Backard Posted March 18, 2007 Share Posted March 18, 2007 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% Quote // Buck Link to comment Share on other sites More sharing options...
djmonkey1 Posted March 18, 2007 Share Posted March 18, 2007 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'? 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...
Backard Posted March 18, 2007 Share Posted March 18, 2007 Do you have Display Prices with Tax set to 'true' or 'false'? Display tax is true Quote // Buck Link to comment Share on other sites More sharing options...
djmonkey1 Posted March 18, 2007 Share Posted March 18, 2007 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. 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...
Backard Posted March 18, 2007 Share Posted March 18, 2007 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 Quote // Buck Link to comment Share on other sites More sharing options...
Backard Posted March 18, 2007 Share Posted March 18, 2007 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? Quote // Buck Link to comment Share on other sites More sharing options...
goyito Posted March 18, 2007 Share Posted March 18, 2007 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 Quote Link to comment Share on other sites More sharing options...
goyito Posted March 18, 2007 Share Posted March 18, 2007 [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 Quote Link to comment Share on other sites More sharing options...
dechamp Posted March 18, 2007 Share Posted March 18, 2007 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! Quote Link to comment Share on other sites More sharing options...
cgreid Posted March 18, 2007 Share Posted March 18, 2007 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 Quote Link to comment Share on other sites More sharing options...
dechamp Posted March 19, 2007 Share Posted March 19, 2007 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. Quote Link to comment Share on other sites More sharing options...
djmonkey1 Posted March 19, 2007 Share Posted March 19, 2007 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. 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 March 19, 2007 Share Posted March 19, 2007 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. 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 March 19, 2007 Share Posted March 19, 2007 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? 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 March 19, 2007 Share Posted March 19, 2007 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. 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...
dechamp Posted March 19, 2007 Share Posted March 19, 2007 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! Quote Link to comment Share on other sites More sharing options...
dechamp Posted March 19, 2007 Share Posted March 19, 2007 (edited) Sorry, actual name for the contribution I used is Credit Card With CVV2, originally posted by a guy named Donibane. Edited March 19, 2007 by dechamp Quote Link to comment Share on other sites More sharing options...
djmonkey1 Posted March 19, 2007 Share Posted March 19, 2007 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? 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.