Explore Products App Shop Support Services App Shop Us
• Checkout
• Get in touch

osCommerce

The e-commerce.

# NEW! Complete Order Editing Tool!

## Recommended Posts

Please provide the following information from an order that you have used Order Editor on (a screenshot would be a godsend):

-Number of products, qty of each, tax rate of each, price of each

-Shipping tax rate

-What the subtotal should be

-What is being calculated for subtotal

-What the tax total(s) should be

-What tax total is being calculated

-What the total should be

-What total is being calculated

Additionally, what is your shop setting for Display Prices with Tax? How many tax classes do you have, and how many tax rates in each class?

Regards,

Stew

Hello,

if i start a new order:

www.trendarts.com/start.JPG

www.trendarts.com/article.JPG

-Number of products, qty of each, tax rate of each, price of each **** 1 product, qty 1, tax rate 20%

-Shipping tax rate ******** 0

-What the subtotal should be ******** subtotal = total amount

-What is being calculated for subtotal ********* nothing

-What the tax total(s) should be ******* if i create a new order, correct tax, only if i edit a customer order wrong tax

##### Share on other sites

iam glad i have a backup iam fixing it now

##### Share on other sites

not a good fix for me :(

now i haven?t:

SUBTOTAL

TAX

TOTAL

##### Share on other sites
Hello,

if i start a new order:

www.trendarts.com/start.JPG

www.trendarts.com/article.JPG

-Number of products, qty of each, tax rate of each, price of each **** 1 product, qty 1, tax rate 20%

-Shipping tax rate ******** 0

-What the subtotal should be ******** subtotal = total amount

-What is being calculated for subtotal ********* nothing

-What the tax total(s) should be ******* if i create a new order, correct tax, only if i edit a customer order wrong tax

www.trendarts.com/start.JPG: The totals shown are correct. There are zero products in this order => the total is zero.

www.trendarts.com/article.JPG: The totals shown are correct. One product at 415.8333 with 20% tax is 499.00. Subtotal is 499.00 (you have Display Prices with Tax set to 'True'). Tax is 83.17. Total is 499.00.

I do not see anything wrong based on what you have shown me.

Do, or do not. There is no try.

Order Editor 5.0.6 "Ultra Violet" is now available!

##### Share on other sites
not a good fix for me :(

now i haven?t:

SUBTOTAL

TAX

TOTAL

Do, or do not. There is no try.

Order Editor 5.0.6 "Ultra Violet" is now available!

##### Share on other sites
iam glad i have a backup iam fixing it now

I dont' see how installing this contribution could affect the catalog side of your website; there aren't any edits to anything in the catalog side.

Do, or do not. There is no try.

Order Editor 5.0.6 "Ultra Violet" is now available!

##### Share on other sites
At least you can see what you changed and if it's correct, and then you can hit Update to write the data to the database. It prevents you from hitting Update all the time if you make any mistakes. I think it would be very nice to have on the fly JavaScript for all the values, not just product's price.

I'll look at it. Getting JavaScript to do totals on arrays should be fun.

Do, or do not. There is no try.

Order Editor 5.0.6 "Ultra Violet" is now available!

##### Share on other sites
I still have problem with this contribution. I use QTPRO 4.2 and the prices now are good but in edit order when i add a product or ccancel it my stock are not upgraded.

Do you have a solution for that?

Thanks

xav

Hi djmonkey

I am back to this now....

Tried to edit the line I oringal presented using only add_product_stock but alas this is still not updating the attibute value when you edit stock quantity within OE (to confirm, it updates the total figure but not the attribute figure)

The product_stock table is made up of the following:

products_stock_id

products_id

products_stock_attributes

products_stock_quantity

Is there any chance you could take another look at this? Any more info needed?

Many thanks

W

Each different attribute has it's own products_stock_id? So a product with four selections and 20 options per selection, would have 80 entries in this table?

I could write up code that will update products_stock but it will do it by products_id, not products_stock_id which is probably the unique id in this table.

Do, or do not. There is no try.

Order Editor 5.0.6 "Ultra Violet" is now available!

##### Share on other sites

Each different attribute has it's own products_stock_id? So a product with four selections and 20 options per selection, would have 80 entries in this table?

I could write up code that will update products_stock but it will do it by products_id, not products_stock_id which is probably the unique id in this table.

So the best thing to do is that i try to explain you the way this contrin work...

in the table products_stock You have :

products_stock_id

products_id

products_stock_attributes

products_stock_quantity

but the product_id could be the same for different product depending the attrubute :

here is one copy of my table:

products_stock_id		  products_id	 products_stock_attributes		products_stock_quantity

374									139					  2-17								   0
31									  63						4-41								   -1
24									 138					   2-15									9
25									  138						2-16								  23

So in fact, the product_stock_id is unique

The product_id can be commom for several items depending the size or color for eample

The attributes are set like that :

in the products_stock_attributes , the first number is the main categorie attribute like color and the second number ( after the - ) is the color ---> for example red

and the products_stock_quantity is the quantity of this item.

So i hope to be simple.

for example a jean got a product_id 10

the color_id is 2

and the color red is 12 and the color yellow is 13, then you will have in the table this :

products_stock_id		  products_id	 products_stock_attributes		products_stock_quantity
150									  10						 2-12										 50
151									  10						 2-13										 47

i hope you will understand what i mean because my english is quite bad...

xav

##### Share on other sites
I dont' see how installing this contribution could affect the catalog side of your website; there aren't any edits to anything in the catalog side.

Iam sure i must have done something wrong.I will try again later..

##### Share on other sites
www.trendarts.com/start.JPG: The totals shown are correct. There are zero products in this order => the total is zero.

www.trendarts.com/article.JPG: The totals shown are correct. One product at 415.8333 with 20% tax is 499.00. Subtotal is 499.00 (you have Display Prices with Tax set to 'True'). Tax is 83.17. Total is 499.00.

I do not see anything wrong based on what you have shown me.

you see it wrong ^^

the correct amount is:

Productprice is 499,-- EUR incl. Tax (20%)

Text_Subtotal/nettoprice - correct amount is: 415.83

is it possible that i can self update this prices ?

a field, who is writeable to the database (also the text ... Text_subtotal, Text_Discount etc) ?

##### Share on other sites
you see it wrong ^^

the correct amount is:

Productprice is 499,-- EUR incl. Tax (20%)

Text_Subtotal/nettoprice - correct amount is: 415.83

is it possible that i can self update this prices ?

a field, who is writeable to the database (also the text ... Text_subtotal, Text_Discount etc) ?

Go to admin->Configuration->My Store->Display Prices with Tax. The setting in your store is 'True', correct? I expect the answer is yes, and if so then the subtotal should include tax.

If you have Display Prices with Tax set to 'True' but want the Subtotal to be calculated without tax, you will have to change the following in your file (make sure you get a backup before you do this!):

//update subtotal and total during update function
if (DISPLAY_PRICE_WITH_TAX == 'true') {
\$RunningSubTotal += ((\$products_details['tax']/100 + 1) * (\$products_details['qty'] * \$products_details['final_price']));
} else {
\$RunningSubTotal += \$products_details["qty"] * \$products_details["final_price"];
}

and replace it with this:

\$RunningSubTotal += \$products_details["qty"] * \$products_details["final_price"];

Next find at about line 394

if (DISPLAY_PRICE_WITH_TAX == 'true') {
\$RunningSubTotal += \$RunningTax;
} else {
\$RunningTotal += \$RunningTax;
}

and replace it with this:

\$RunningTotal += \$RunningTax;

Then find this at about line 508:

elseif (\$ot_class == "ot_tax") {

if (DISPLAY_PRICE_WITH_TAX != 'true') {
\$RunningTotal += \$ot_value;
}
} else {
\$RunningTotal += \$ot_value;
}

and replace it with this:

else {
\$RunningTotal += \$ot_value;
}

Next find this at about line 674:

if (DISPLAY_PRICE_WITH_TAX == 'true') {
\$RunningSubTotal += ((\$order->products[\$i]['tax'] / 100 + 1) * (\$order->products[\$i]['qty'] * \$order->products[\$i]['final_price']));
} else {
\$RunningSubTotal += (\$order->products[\$i]['qty'] * \$order->products[\$i]['final_price']);
}

and replace it with this:

\$RunningSubTotal += (\$order->products[\$i]['qty'] * \$order->products[\$i]['final_price']);

Then find at line 699:

// 2.2.2.3 Total
if (DISPLAY_PRICE_WITH_TAX == 'true') {
\$Query = 'SELECT sum(value) AS total_value from ' . TABLE_ORDERS_TOTAL . '
WHERE class != "ot_total" AND class != "ot_tax" AND orders_id= "' . \$oID . '"';
\$result = tep_db_query(\$Query);
\$row = tep_db_fetch_array(\$result);
\$Total = \$row['total_value'];
} else {
\$Query = 'SELECT sum(value) AS total_value from ' . TABLE_ORDERS_TOTAL . '
WHERE class != "ot_total" AND orders_id= "' . \$oID . '"';
\$result = tep_db_query(\$Query);
\$row = tep_db_fetch_array(\$result);
\$Total = \$row['total_value'];
}

and replace it with this:

// 2.2.2.3 Total
\$Query = 'SELECT sum(value) AS total_value from ' . TABLE_ORDERS_TOTAL . '
WHERE class != "ot_total" AND orders_id= "' . \$oID . '"';
\$result = tep_db_query(\$Query);
\$row = tep_db_fetch_array(\$result);
\$Total = \$row['total_value'];

Also for all these places where you have TABLE_HEADING_SHIPPING_TAX, TEXT_SUBTOTAL, etc, you need to update your language definition file (admin/includes/languages/your_langugage/edit_orders.php). Make sure you include a definition for ENTRY_CREDIT_CARD.

Any value that is displayed in a text box is editable. As far as editing the subtotal amounts, that doesn't make sense to me. Subtotals are calculated based on the products in the order, tax if necessary, etc. If you could edit the subtotal amount then you could have a subtotal that doesn't equal the value of all the products added together. You edit the subtotal by editing the products prices.

Do, or do not. There is no try.

Order Editor 5.0.6 "Ultra Violet" is now available!

##### Share on other sites
I just now noticed that JavaScript calculator doesn't include decimal places in Total (excl.) and Total (incl.), it's just 224 instead of 224.000. Anyway to change it?

Thanks.

The JavaScript is written to round the values out to four decimal places and automagically leaves off unneccessary zeros.

To always display zeros, find at about line 801:

function doRound(x, places) {
return Math.round(x * Math.pow(10, places)) / Math.pow(10, places);
}

function doFormat(x, places)
{
var a = doRound(x, places);
var s = a.toString();

var decimalIndex = s.indexOf(".");
if (places > 0 && decimalIndex < 0)
{
decimalIndex = s.length;
s += '.';
}
while (decimalIndex + places + 1 > s.length)
{
s += '0';
}
return s;
}

Then find this at about line 875:

if ((action != 'qty') && (action != 'tax') && (action != 'final_price')) {
document.getElementById(pid + "-final_price").value = doRound(finalPriceValue, 4);
}

if ((action != 'qty') && (action != 'price_incl')) {
document.getElementById(pid + "-price_incl").value = doRound(priceInclValue, 4);
}

if ((action != 'tax') && (action != 'total_excl')) {
document.getElementById(pid + "-total_excl").value = doRound(totalExclValue, 4);
}

if (action != 'total_incl') {
document.getElementById(pid + "-total_incl").value = doRound(totalInclValue, 4);
}

and change every instance of doRound to doFormat:

if ((action != 'qty') && (action != 'tax') && (action != 'final_price')) {
document.getElementById(pid + "-final_price").value = doFormat(finalPriceValue, 4);
}

if ((action != 'qty') && (action != 'price_incl')) {
document.getElementById(pid + "-price_incl").value = doFormat(priceInclValue, 4);
}

if ((action != 'tax') && (action != 'total_excl')) {
document.getElementById(pid + "-total_excl").value = doFormat(totalExclValue, 4);
}

if (action != 'total_incl') {
document.getElementById(pid + "-total_incl").value = doFormat(totalInclValue, 4);
}

Do, or do not. There is no try.

Order Editor 5.0.6 "Ultra Violet" is now available!

##### Share on other sites
So the best thing to do is that i try to explain you the way this contrin work...

in the table products_stock You have :

products_stock_id

products_id

products_stock_attributes

products_stock_quantity

but the product_id could be the same for different product depending the attrubute :

here is one copy of my table:

products_stock_id		  products_id	 products_stock_attributes		products_stock_quantity

374									139					  2-17								   0
31									  63						4-41								   -1
24									 138					   2-15									9
25									  138						2-16								  23

So in fact, the product_stock_id is unique

The product_id can be commom for several items depending the size or color for eample

The attributes are set like that :

in the products_stock_attributes , the first number is the main categorie attribute like color and the second number ( after the - ) is the color ---> for example red

and the products_stock_quantity is the quantity of this item.

So i hope to be simple.

for example a jean got a product_id 10

the color_id is 2

and the color red is 12 and the color yellow is 13, then you will have in the table this :

products_stock_id		  products_id	 products_stock_attributes		products_stock_quantity
150									  10						 2-12										 50
151									  10						 2-13										 47

i hope you will understand what i mean because my english is quite bad...

xav

Where else is products_stock_id written? It must be logged somewhere else, like as orders_products_stock_id in table orders_products_attributes.

Do, or do not. There is no try.

Order Editor 5.0.6 "Ultra Violet" is now available!

##### Share on other sites
Where else is products_stock_id written? It must be logged somewhere else, like as orders_products_stock_id in table orders_products_attributes.

I give you the sql files for installing this contribution, then i hope you will understand wich table is created..

# Database changes for QT Pro installation to a store that does not have a

# previous version of QT Pro installed

# Add new column to products_options to indicate if stock should be tracked

# for an option

ALTER TABLE products_options

ADD products_options_track_stock tinyint(4) default '0' not null

AFTER products_options_name;

# Add new column to orders_products to track attributes to make it possible

# to delete an order and restock

ALTER TABLE orders_products

AFTER products_quantity;

# Create new table to track stock for products attributes

DROP TABLE IF EXISTS products_stock;

CREATE TABLE products_stock (

products_stock_id int(11) not null auto_increment,

products_id int(11) default '0' not null ,

products_stock_attributes varchar(255) not null,

products_stock_quantity int(11) default '0' not null ,

PRIMARY KEY (products_stock_id),

UNIQUE idx_products_stock_attributes (products_id,products_stock_attributes)

);

thank you for your help again

lil xav

##### Share on other sites
I give you the sql files for installing this contribution, then i hope you will understand wich table is created..

thank you for your help again

lil xav

I'm trying to get my head around this. It seems like you would have to update all of the products attribute queries as well as the stock check queries to take products_stock_id into account.

Is there a QtPro forum? Maybe a developer there could shed some light on this.

Do, or do not. There is no try.

Order Editor 5.0.6 "Ultra Violet" is now available!

##### Share on other sites
The JavaScript is written to round the values out to four decimal places and automagically leaves off unneccessary zeros.

To always display zeros, find at about line 801:

function doRound(x, places) {
return Math.round(x * Math.pow(10, places)) / Math.pow(10, places);
}

function doFormat(x, places)
{
var a = doRound(x, places);
var s = a.toString();

var decimalIndex = s.indexOf(".");
if (places > 0 && decimalIndex < 0)
{
decimalIndex = s.length;
s += '.';
}
while (decimalIndex + places + 1 > s.length)
{
s += '0';
}
return s;
}

Then find this at about line 875:

if ((action != 'qty') && (action != 'tax') && (action != 'final_price')) {
document.getElementById(pid + "-final_price").value = doRound(finalPriceValue, 4);
}

if ((action != 'qty') && (action != 'price_incl')) {
document.getElementById(pid + "-price_incl").value = doRound(priceInclValue, 4);
}

if ((action != 'tax') && (action != 'total_excl')) {
document.getElementById(pid + "-total_excl").value = doRound(totalExclValue, 4);
}

if (action != 'total_incl') {
document.getElementById(pid + "-total_incl").value = doRound(totalInclValue, 4);
}

and change every instance of doRound to doFormat:

if ((action != 'qty') && (action != 'tax') && (action != 'final_price')) {
document.getElementById(pid + "-final_price").value = doFormat(finalPriceValue, 4);
}

if ((action != 'qty') && (action != 'price_incl')) {
document.getElementById(pid + "-price_incl").value = doFormat(priceInclValue, 4);
}

if ((action != 'tax') && (action != 'total_excl')) {
document.getElementById(pid + "-total_excl").value = doFormat(totalExclValue, 4);
}

if (action != 'total_incl') {
document.getElementById(pid + "-total_incl").value = doFormat(totalInclValue, 4);
}

Thanks djmonkey1. Now it looks much prettier :thumbsup:

##### Share on other sites
I'm trying to get my head around this. It seems like you would have to update all of the products attribute queries as well as the stock check queries to take products_stock_id into account.

Is there a QtPro forum? Maybe a developer there could shed some light on this.

hi, I did ask someone for the QTpro Table :

products_Stock_id -------> is the unique ID of each item on this table

products_id ---------> is the product ID for the Product Stock ID

products_stock_attributes ----> is the name of the attribut

products_stock_quantity ----> is the stock level

xav

##### Share on other sites

I downloaded v2.2.1 and overide the two edit order files in my admin and admin english, but when I tried to update the order in my administration page, this error occurs:

1054 - Unknown column 'shipping_tax' in 'field list'

Do I need to run any SQL? :blink:

Best regards,

Koh Kho King

##### Share on other sites

In order to handle the attributes stock in QT Pro you will simply need to add come code where you reduce the stock, if attributes were used for the products and if QTPRO is in use (a variable at the top of edit_order would do the trick for this or a key in the admin)

Then around this code:

if (\$products_details["qty"] != \$order['products_quantity']){
\$quantity_difference = (\$products_details["qty"] - \$order['products_quantity']);

Undepending of the stock_check TRUE or not also do an update of the QT pro table for the given product and given attribute.

I am not using QT Pro myself, so Xavkick can you confirm that for a product with attribute you still enter the total stock of the products plus the detailled stock for each of its attributes ?

And that when one article of this kind is sold, both the total stock and the particular stock for the attribute are reduced ?

##### Share on other sites
I downloaded v2.2.1 and overide the two edit order files in my admin and admin english, but when I tried to update the order in my administration page, this error occurs:

1054 - Unknown column 'shipping_tax' in 'field list'

Do I need to run any SQL? :blink:

-------------

STEP0

-------------

Run the following command on your MySQL database:

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

Do, or do not. There is no try.

Order Editor 5.0.6 "Ultra Violet" is now available!

##### Share on other sites
I am not using QT Pro myself, so Xavkick can you confirm that for a product with attribute you still enter the total stock of the products plus the detailled stock for each of its attributes ?

And that when one article of this kind is sold, both the total stock and the particular stock for the attribute are reduced ?

No you do not enter the total of stock of the product, you only update each attributes... and the contrib upgrade automatically the total of the stock.... that's make ( all the attributes of the items give the total of the product stock )

But if the item as no attributes you update then only the stock product..... :thumbsup:

##### Share on other sites
No you do not enter the total of stock of the product, you only update each attributes... and the contrib upgrade automatically the total of the stock.... that's make ( all the attributes of the items give the total of the product stock )

But if the item as no attributes you update then only the stock product..... :thumbsup:

Dig through the code that comes with qtpro and find the delete function of orders.php (the code that modifies the original delete function and the queries associated with it). That should be enough to write something up.

Do, or do not. There is no try.

Order Editor 5.0.6 "Ultra Violet" is now available!

##### Share on other sites
Dig through the code that comes with qtpro and find the delete function of orders.php (the code that modifies the original delete function and the queries associated with it). That should be enough to write something up.

in the checkout_process.php the portion of the QTPRO code are :

\$products_stock_attributes=null;
if (STOCK_LIMITED == 'true') {
\$products_attributes = \$order->products[\$i]['attributes'];

and

\$actual_stock_bought = \$order->products[\$i]['qty'];
}
\$all_nonstocked = true;
\$products_stock_attributes_array = array();
foreach (\$products_attributes as \$attribute) {
if (\$attribute['track_stock'] == 1) {
\$products_stock_attributes_array[] = \$attribute['option_id'] . "-" . \$attribute['value_id'];
\$all_nonstocked = false;
}
}
if (\$all_nonstocked) {
\$actual_stock_bought = \$order->products[\$i]['qty'];
}  else {
asort(\$products_stock_attributes_array, SORT_NUMERIC);
\$products_stock_attributes = implode(",", \$products_stock_attributes_array);
\$attributes_stock_query = tep_db_query("select products_stock_quantity from " . TABLE_PRODUCTS_STOCK . " where products_stock_attributes = '\$products_stock_attributes' AND products_id = '" . tep_get_prid(\$order->products[\$i]['id']) . "'");
if (tep_db_num_rows(\$attributes_stock_query) > 0) {
\$attributes_stock_values = tep_db_fetch_array(\$attributes_stock_query);
\$attributes_stock_left = \$attributes_stock_values['products_stock_quantity'] - \$order->products[\$i]['qty'];
tep_db_query("update " . TABLE_PRODUCTS_STOCK . " set products_stock_quantity = '" . \$attributes_stock_left . "' where products_stock_attributes = '\$products_stock_attributes' AND products_id = '" . tep_get_prid(\$order->products[\$i]['id']) . "'");
\$actual_stock_bought = (\$attributes_stock_left < 1) ? \$attributes_stock_values['products_stock_quantity'] : \$order->products[\$i]['qty'];
} else {
\$attributes_stock_left = 0 - \$order->products[\$i]['qty'];
tep_db_query("insert into " . TABLE_PRODUCTS_STOCK . " (products_id, products_stock_attributes, products_stock_quantity) values ('" . tep_get_prid(\$order->products[\$i]['id']) . "', '" . \$products_stock_attributes . "', '" . \$attributes_stock_left . "')");
\$actual_stock_bought = 0;
}
}
}
//		\$stock_query = tep_db_query("select products_quantity from " . TABLE_PRODUCTS . " where products_id = '" . tep_get_prid(\$order->products[\$i]['id']) . "'");
//	  }
//	  if (tep_db_num_rows(\$stock_query) > 0) {
//		\$stock_values = tep_db_fetch_array(\$stock_query);
// do not decrement quantities if products_attributes_filename exists
\$stock_left = \$stock_values['products_quantity'] - \$actual_stock_bought;
tep_db_query("UPDATE " . TABLE_PRODUCTS . "
SET products_quantity = products_quantity - '" . \$actual_stock_bought . "'
WHERE products_id = '" . tep_get_prid(\$order->products[\$i]['id']) . "'");

and

if (!isset(\$products_stock_attributes)) \$products_stock_attributes=null;
\$sql_data_array = array('orders_id' => \$insert_id,
'products_id' => tep_get_prid(\$order->products[\$i]['id']),
'products_model' => \$order->products[\$i]['model'],
'products_name' => \$order->products[\$i]['name'],
'products_price' => \$order->products[\$i]['price'],
'final_price' => \$order->products[\$i]['final_price'],
'products_tax' => \$order->products[\$i]['tax'],
'products_quantity' => \$order->products[\$i]['qty'],
'products_stock_attributes' => \$products_stock_attributes);

i hope you can help me

xav

##### Share on other sites
Also for all these places where you have TABLE_HEADING_SHIPPING_TAX, TEXT_SUBTOTAL, etc, you need to update your language definition file (admin/includes/languages/your_langugage/edit_orders.php). Make sure you include a definition for ENTRY_CREDIT_CARD.

and how ?

runnige

but:

TEXT_SUBTOTAL

TEXT_DISCOUNT

TEXT_DELIVERY

TEXT_TAX

TEXT_TOTAL

not with:

ENTRY

witch define for this parts ?

thx for help