NEW! Complete Order Editing Tool!

[LowCastle]

I am using version 2.8.5. I just updated from 2.8.2.

I ran into an error when using the Order Editor to edit an order from a customer who had an apostrophe in his City name.

Here is the error, with the customer's personal information edited for privacy:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Alene', customers_state = 'Idaho', customers_postcode = '83814', ' at line 6

 

UPDATE orders SET customers_name = 'Richard Smith', customers_company = '', customers_street_address = '800 N Crestview dr', customers_suburb = '', customers_city = 'Coeur d'Alene', customers_state = 'Idaho', customers_postcode = '83814', customers_country = 'United States', customers_telephone = '(208) 555-5555', customers_email_address = '[email protected]', billing_name = 'Richard Smith', billing_company = '', billing_street_address = '800 N Crestview dr', billing_suburb = '', billing_city = 'Coeur d'Alene', billing_state = 'Idaho', billing_postcode = '83814', billing_country = 'United States', delivery_name = 'Richard Smith', delivery_company = '', delivery_street_address = '800 N Crestview dr', delivery_suburb = '', delivery_city = 'Coeur d'Alene', delivery_state = 'Idaho', delivery_postcode = '83814', delivery_country = 'United States', payment_method = 'Purchase Order Account', currency = 'USD', currency_value = '1.00000000', cc_type = '', cc_owner = '', cc_number = '', cc_expires = '', shipping_tax = '0.0' where orders_id = '36';

 

I'm assuming that I somehow need to implement the tep_html_no_oe_quote function to strip the apostrophe but I'm new to PhP. Can anyone help with this?

[djmonkey1]

I am using version 2.8.5. I just updated from 2.8.2.

I ran into an error when using the Order Editor to edit an order from a customer who had an apostrophe in his City name.

Here is the error, with the customer's personal information edited for privacy:

 

 

I'm assuming that I somehow need to implement the tep_html_no_oe_quote function to strip the apostrophe but I'm new to PhP. Can anyone help with this?

 

Try this: from

		$UpdateOrders = "UPDATE " . TABLE_ORDERS . " SET 
	customers_name = '" . tep_db_prepare_input($_POST['update_customer_name']) . "',

all the way down through

		cc_expires = '" . tep_db_prepare_input($_POST['update_info_cc_expires']) . "',
	shipping_tax = '" . tep_db_prepare_input($_POST['update_shipping_tax']) . "'";

change every instance of

tep_db_prepare_input

to

tep_db_input

and see how things go that way.

 

You may run into different problems but the one you described should go away. I need to look at this more closely.

Edited February 8, 2007 by djmonkey1

[LowCastle]

You may run into different problems but the one you described should go away. I need to look at this more closely.

 

Thank you for your help! I really appreciate it. This fix worked for me, but am still having a problem with apostrophes in the product attributes.

I have custom options that include text (similar to imprinted coffee mugs) but if the customer enters an apostrophe (or if I do in the Order Editor) the text is cut off after the apostrophe.

 

I haven't done much testing yet to see what other problems may occur.

[djmonkey1]

Thank you for your help! I really appreciate it. This fix worked for me, but am still having a problem with apostrophes in the product attributes.

I have custom options that include text (similar to imprinted coffee mugs) but if the customer enters an apostrophe (or if I do in the Order Editor) the text is cut off after the apostrophe.

 

I haven't done much testing yet to see what other problems may occur.

 

At about line 1758 change

echo '<br /><nobr><small> <i> - ' . "<input name='update_products[$orders_products_id][attributes][$orders_products_attributes_id][option]' size='6' value='" . $order->products[$i]['attributes'][$j]['option'] . "'>" . ': ' . "<input name='update_products[$orders_products_id][attributes][$orders_products_attributes_id][value]' size='10' value='" . $order->products[$i]['attributes'][$j]['value'] . "'>" . ': ' . "</i><input name='update_products[$orders_products_id][attributes][$orders_products_attributes_id][prefix]' size='1' id='p" . $orders_products_id . "_" . $orders_products_attributes_id . "_prefix' value='" . $order->products[$i]['attributes'][$j]['prefix'] . "' onKeyUp=\"updatePrices('att_price', 'p" . $orders_products_id . "', '" . $order->products[$i]['tax_description'] . "', '" . $orders_products_attributes_id . "')\">" . ': ' . "<input name='update_products[$orders_products_id][attributes][$orders_products_attributes_id][price]' size='7' value='" . $order->products[$i]['attributes'][$j]['price'] . "' onKeyUp=\"updatePrices('att_price', 'p" . $orders_products_id . "', '" . $order->products[$i]['tax_description'] . "', '" . $orders_products_attributes_id . "')\" id='p". $orders_products_id . "a" . $orders_products_attributes_id . "'>";

to

echo '<br /><nobr><small> <i> - ' . "<input name='update_products[$orders_products_id][attributes][$orders_products_attributes_id][option]' size='6' value='" . tep_html_quotes($order->products[$i]['attributes'][$j]['option']) . "'>" . ': ' . "<input name='update_products[$orders_products_id][attributes][$orders_products_attributes_id][value]' size='10' value='" . tep_html_quotes($order->products[$i]['attributes'][$j]['value']) . "'>" . ': ' . "</i><input name='update_products[$orders_products_id][attributes][$orders_products_attributes_id][prefix]' size='1' id='p" . $orders_products_id . "_" . $orders_products_attributes_id . "_prefix' value='" . $order->products[$i]['attributes'][$j]['prefix'] . "' onKeyUp=\"updatePrices('att_price', 'p" . $orders_products_id . "', '" . $order->products[$i]['tax_description'] . "', '" . $orders_products_attributes_id . "')\">" . ': ' . "<input name='update_products[$orders_products_id][attributes][$orders_products_attributes_id][price]' size='7' value='" . $order->products[$i]['attributes'][$j]['price'] . "' onKeyUp=\"updatePrices('att_price', 'p" . $orders_products_id . "', '" . $order->products[$i]['tax_description'] . "', '" . $orders_products_attributes_id . "')\" id='p". $orders_products_id . "a" . $orders_products_attributes_id . "'>";

[mjfontec]

Hi,

 

Forgive me if this has been addressed already, I didn't see it.

 

I'm getting the following error:

 

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

 

I don't charge a tax on shipping. The error occurs anytime I try to update.

 

Any assistance is appreciated, love the contrib.

 

Mike

[djmonkey1]

I'm getting the following error:

 

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

You missed this step from the install text:

 

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

STEP 1

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

 

Run the following command on your MySQL database

 

###If you always or almost always charge tax on shipping,

###you may want to enter a different default value for shipping_tax,

###ie 16.0000 instead of 0.0000):

 

 

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\'),');

[Jari646]

Hi djmonkey1,

 

Great contribution!

 

I have small problem with tax calculation. When I give discount the tax goes wrong (for me).

The discount tax calculation should work like shipping calculation.

 

Example if (correct):

Subtotal 299

Shipping 10

Tax 55.72

TOTAL 309

 

If I give discount (wrong):

Subtotal 299

Discount -50

Shipping 10

Tax 55.72

TOTAL 259

 

 

Hope you can help me :)

[mjfontec]

Ok, I ran it and it seemed to work, but I'm getting this error now at the bottom of the page:

 

Fatal error: Call to undefined function: tep_draw_textarea_field() in

 

/catalog/admin/edit_orders.php on line 2071

 

Any ideas?

 

You missed this step from the install text:

[LowCastle]

Thank you for your help! I really appreciate it. This fix worked for me, but am still having a problem with apostrophes in the product attributes.

I have custom options that include text (similar to imprinted coffee mugs) but if the customer enters an apostrophe (or if I do in the Order Editor) the text is cut off after the apostrophe.

 

I haven't done much testing yet to see what other problems may occur.

 

I changed the line you suggested that I change. The attributes are still cut off after using an apostrophe or quotation mark. I haven't tried other symbols in there.

[khoking]

Look at the orders_total table for information from a brand new order (not edited with Order Editor). The "value" and "text" fields should correspond (i.e. if text is $12.50 then value should be 12.50). If the "value" field is blank, NULL, or zero, then that's why Order Editor is deleting it.

 

If that is the case, then there's something wrong with your Fuel Surcharge contribution because it's not writing all the necessary information to your database. But, in this case, you can change Order Editor to not delete your fuel surcharge.

 

Change

	if (!trim($ot_value) && ($ot_class != "ot_shipping") && ($ot_class != "ot_subtotal") && ($ot_class != "ot_total")) { // value = 0 => Delete Total Piece

to

	if (!trim($ot_value) && ($ot_class != "ot_shipping") && ($ot_class != "ot_fuel_surcharge") && ($ot_class != "ot_subtotal") && ($ot_class != "ot_total")) { // value = 0 => Delete Total Piece

where "ot_fuel_surcharge" should be whatever is stored in the "class" column of the orders_total table for those entries.

 

That's my theory, anyway, try it out and see how it goes.

 

Dear djmonkey1,

 

Thank you very much for the reply.

 

I checked both text and value in phpadmin in my database, both are not NULL with text US$5 and value 5 inside.

 

I tried your method above, though it did not delete the Fuel surcharge column now, but it changes the value to ZERO and I can't edit it anymore :(

 

Any idea how I can solve this? My fuel surchage is based on shipping cost is having the value of 12.5% of shipping cost. Only oversea countries have fuel surcharge, local doesnt have. The class used is "ot_tax".

 

Thanks in advance!

[Phocea]

Hello,

Thanks for the good job you are doing on this contribution.

However I was wondering why in the last version you change the edit_orders.php script to use tep_db_prepare_input function instead of tep_db_input ?

 

Using the tep_db_prepare_input means that fiels which include quotes fail to be inserted in the database (this happened to me when I tried to edit an order to change its payment method value).

[djmonkey1]

Ok, I ran it and it seemed to work, but I'm getting this error now at the bottom of the page:

 

Fatal error: Call to undefined function: tep_draw_textarea_field() in

 

/catalog/admin/edit_orders.php on line 2071

 

Any ideas?

That function should be defined in admin/includes/functions/html_output.php. It's a standard osCommerce function.

[djmonkey1]

The class used is "ot_tax".

 

That would be the problem. It would be a lot easier to get the surcharge module to use the class value of something like "ot_surcharge" than it would be to get Order Editor to stop hacking at it. It shouldn't be called "ot_tax" anyway, that isn't appropriate.

[djmonkey1]

Hello,

Thanks for the good job you are doing on this contribution.

However I was wondering why in the last version you change the edit_orders.php script to use tep_db_prepare_input function instead of tep_db_input ?

 

Using the tep_db_prepare_input means that fiels which include quotes fail to be inserted in the database (this happened to me when I tried to edit an order to change its payment method value).

 

If you go back to the bottom of the previous page of this thread you'll see I've been discussing this exact issue with LowCastle. If it works better for you, by all means use tep_db_input. Right now I'm testing code that uses both, as in

tep_db_input(tep_db_prepare_input($variable))

[djmonkey1]

Hi djmonkey1,

 

Great contribution!

 

I have small problem with tax calculation. When I give discount the tax goes wrong (for me).

The discount tax calculation should work like shipping calculation.

 

Example if (correct):

Subtotal 299

Shipping 10

Tax 55.72

TOTAL 309

 

If I give discount (wrong):

Subtotal 299

Discount -50

Shipping 10

Tax 55.72

TOTAL 259

 

Hope you can help me

 

Is the discount added in during the checkout process or is it something you add in with Order Editor?

[djmonkey1]

I changed the line you suggested that I change. The attributes are still cut off after using an apostrophe or quotation mark. I haven't tried other symbols in there.

 

That's strange, it works fine for me. I'd upload the file again to make sure it made it to the server. Also, for the ones that were cut off before the change, they're gone and you'll have to type them in manually.

[johntolhurst]

If a customer has trouble ordering I ask them to fill a basic form (using Notes Domino). Then I want to enter the order into osc.

 

Does this contrib support that?

 

Is there an FAQ covering the operation of the contrib? What I can find is a little cryptic for my level of expertise. For example, I see that you can modify orders, but can you add a customer to the database and create an order for them, and make it 'paid' and lower the stock count?

 

Should I be looking at a different contrib?

 

Sorry if this is a previous question, 1500 posts on the thread is rather a lot to read! :)

 

Hope you can guide me,

John

www.cruzbike.com oscommerce site

[Jari646]

Is the discount added in during the checkout process or is it something you add in with Order Editor?

 

I did add discount with Order Editor.

[djmonkey1]

If a customer has trouble ordering I ask them to fill a basic form (using Notes Domino). Then I want to enter the order into osc.

 

Does this contrib support that?

 

Is there an FAQ covering the operation of the contrib? What I can find is a little cryptic for my level of expertise. For example, I see that you can modify orders, but can you add a customer to the database and create an order for them, and make it 'paid' and lower the stock count?

 

Should I be looking at a different contrib?

 

Sorry if this is a previous question, 1500 posts on the thread is rather a lot to read! :)

 

Hope you can guide me,

John

www.cruzbike.com oscommerce site

 

Order Editor is designed for the editing of existing orders. For creating new customers and/or new orders you should search for "manual order entry" in the contributions.

[djmonkey1]

I did add discount with Order Editor.

 

Find in admin/edit_orders.php

// 1.4.0.1 Shipping Tax

and a few lines below that change

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

to

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

then 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')");

and see how it works then.

[Jari646]

Find in admin/edit_orders.php

// 1.4.0.1 Shipping Tax

and a few lines below that change

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

to

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

then 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')");

and see how it works then.

 

 

Just PERFECT!!! :D

 

Thank you very much!!!

[djmonkey1]

Just PERFECT!!! :D

 

Thank you very much!!!

You're welcome!

Edited February 14, 2007 by djmonkey1

[c1badmofo]

I have currently installed the lastest version of Order Editor v2.8.5, and I like to thank everyone for there contributions.

 

The question I have is on the order_editor.php.

 

If I click on "Add a Product" to the existing order, the order total weight is updated and recalculated correctly, but the total for the shipping weight has not change nor updated based on the new total weight.

 

Is there a simple fix for this?

[djmonkey1]

I have currently installed the lastest version of Order Editor v2.8.5, and I like to thank everyone for there contributions.

 

The question I have is on the order_editor.php.

 

If I click on "Add a Product" to the existing order, the order total weight is updated and recalculated correctly, but the total for the shipping weight has not change nor updated based on the new total weight.

 

Is there a simple fix for this?

Changes to shipping charges have to be made manually.

[grabbags]

Hello, djmoney1! Your order editing tool is so cool. However, I don't need the shipping tax feature. Is it possbile not to modify the database? Would you please tell me where to comment out to prevent it from displaying from the order editor.php as well as the calculations~! Thanks a lot!