Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

NEW! Complete Order Editing Tool!


jhilgeman

Recommended Posts

Oo..I think I just double posted...

 

By far, you guys have way more of a clue than I about SQL...any idea how this is easily fixed?

 

Geof.

www.smoothair.ca

Link to comment
Share on other sites

Morb,

 

The second SQL statement -- at the bottom of my previous message -- corrects the problems I see, assuming that every product has a product description, every category has a category description, and the program doesn't need the product results repeated for each language in the system. From my reading of the code these assumptions are correct, but I thought I'd better check with people who know the system.

 

You can just replace the first query from my earlier message with the second to correct the problems. Note that I presented the first query in a multi-line format for easier reading, but otherwise it is identical to the query in your edit_orders.php file.

 

In case you were wondering, the original SQL query had a minor typographical error (x.categories_name=ptc.categories_name) which caused it to expect a nonexistent product_name field in the table specified by TABLE_PRODUCTS_TO_CATEGORIES. Changing "ptc" to "cd" in that one expression will cause the Add Product feature to start working.

 

However, by leaving the language constraint out, the query retrieves a separate result for each langauge. E.g.

products_id 1, categories_id 1, english categories_name

products_id 1, categories_id 1, german categories_name

products_id 1, categories_id 1, spanish categories_name

Thus you get three times the number of rows you might expect from the query. (You only see one copy on the Order Edit display because the ensuing loop in edit_orders.php writes all of the product_name fields into the same array variable, so the last language wins.)

 

Furthermore, the second join on TABLE_CATEGORIES_DESCRIPTION will duplicate each result for each duplicated category name. For example, I have a category with a name in English, but blank names in German and Spanish. The "extra" join matches on the blank names, which produces two more copies of the result, increasing the total to five times the number of products. Normally the category names would vary according to the language, but I still don't see how this extra join is helpful.

 

Using an outer join (LEFT JOIN) causes a record containing only null fields to be provided where no record from the right hand side matches a given record from the left hand side. For instance, if a products_description record were missing, the result for (products_id, products_name) might be (1, NULL). Had an ordinary join been used, no row would have been returned because of the missing products_description record. I think the outer join is more expensive, and it suggests to the reader that an empty record might normally occur on the right side. The latter is technically true (I created a product outside all categories), but it doesn't seem to fit the way the system works.

 

In summary, by adding the language constraint I reduced the number or records returned from five per product to one per product. Dropping the "extra" join on categories_description appears to eliminate superfluous work, but I might be missing something clever there. Replacing outer joins with ordinary joins should be more efficient, and probably reflects the semantics of the system better.

Link to comment
Share on other sites

Ok Jim,

 

I'm bowing down at your knowledge of SQL and coding and stuff, but I'm a simple layman who can't figure out where the right muffler bearing goes...:D

 

How do fix the problem????

 

I'm getting this error when I run the SQL Query:

Error

 

There is a chance that you may have found a bug in the SQL parser. Please examine your query closely, and check that the quotes are correct and not mis-matched. Other possible failure causes may be that you are uploading a file with binary outside of a quoted text area. You can also try your query on the MySQL command line interface. The MySQL server error output below, if there is any, may also help you in diagnosing the problem. If you still have problems or if the parser fails where the command line interface succeeds, please reduce your SQL query input to the single query that causes problems, and submit a bug report with the data chunk in the CUT section below:

----BEGIN CUT----

eNrtmElrwkAUgGdSDzK95NjjO3jUMUvjMie3YAW3GmuvdUNDJVqDYm+e+rv60xrNAwUteCzlfTB8

h1lJYN574/Z6nZ6CqglVC5puW4EBJjh2Li+8ftTBhagOPAWpxkRB+LFYDdfhdC0X/kiu5qv0FkxZ

cMAyDDtr5LNWEYyicgrKKcJsuN76wdwfv/vBDNzdClKi9ek9NxXY0rKlkxMvXg86XhrKdbfdT8PA

jTZ89QNouJCThui2ygos6Ug7sx6bovvUPQxJd6LjPEpLRuf1g81ONMvtuoJpkPHDZaZQcIoZUxy3

4ZXNxGRMZ4wlWWz2xa6SxHaBhuZxi9bYsAmbsnASjhfsNvhxcZ8FbMwW5/NHy+WtaxAEQRAEcTsc

4/s9fQqCIK7cD4AuofexOfZr6MTZHB0N6BJ6H5vjOA2dQCfROhrQJfQ+Nl5aHIsPjjtzrFC4jgZ0

if4jQVzjLpZ+iP/u7/U/QRD/Oc4nal6tcioILtAwEXjDMd+nYH81EdDiB8OHs6TgjyQDP1juTd4=

----END CUT----

----BEGIN RAW----

 

ERROR: C1 C2 LEN: 0 1 5367

STR:

 

CVS: $Id: sqlparser.lib.php,v 1.85 2003/07/29 09:58:59 garvinhicking Exp $

MySQL: 3.23.56

USR OS, AGENT, VER: Win IE 6.0

PMA: 2.5.3-rc1

PHP VER,OS: 4.2.2 Linux

LANG: en-iso-8859-1

SQL: Bud1?udesdsclincludesdsclbool @? @? @? @E?DSDB `? @? @? @----END RAW----

 

SQL-query : 

 

Bud1?udesdsclincludesdsclbool @? @? @? @ E?DSDB `? @? @? @

 

MySQL said:

 

 

#1064 - You have an error in your SQL syntax near 'Bud1' at line 1

 

It just doesn't look right..LOL

 

I've downloaded the newest version, the one that even says everything is fixed....what da heck am I doing wrong???

 

Geof.

Link to comment
Share on other sites

Well,

 

Never mind then. :) I seemed to of gotten it working on your hard work. Thank you very much. Just replaced the line, ignored the SQL, and tested tested tested everywhich way, and it works just fine.

 

Thanks again. :)

 

Geof.

Link to comment
Share on other sites

Hi

Has anyone dealt with attributes. When I use the add product to add a product into the order with attributes, it adds up correctly for the attributes are positive eg +$20. But when the attributes require deductions eg -20, the contrib did not deduct from the product price and add +20 instead. Does anyone know how to solve this problem?? :o

 

Product A => $80

attribute a => -$20

For this contrib gives me the result => $100 instead of the correct $60

 

Anyone kind to share your experience...

Link to comment
Share on other sites

Regarding akkinmore's question about negative option prices being registered as positive prices:

Has anyone dealt with attributes. When I use the add product to add a product into the order with attributes, it adds up correctly for the attributes are positive eg +$20. But when the attributes require deductions eg -20, the contrib did not deduct from the product price and add +20 instead. Does anyone know how to solve this problem??

 

The problem appears to be that products_attributes table carries the sign (+ or -) in a separate "price_prefix" field, but edit_orders doesn't make use of it. To take the price_prefix into account, I suggest the following two changes.

 

WARNING: These changes have been only lightly tested. Back up your file.

 

At about line 366 of edit_orders.php (v 1.3), replace

    extract($row, EXTR_PREFIX_ALL, "opt");
   $AddedOptionsPrice += $opt_options_values_price;
   $option_value_details[$option_id][$option_value_id] = array ("options_values_price" => $opt_options_values_price);

 

with

    extract($row, EXTR_PREFIX_ALL, "opt");
   if ($opt_price_prefix == '+')
   {
       $AddedOptionsPrice += $opt_options_values_price;
   } else {
       $AddedOptionsPrice -= $opt_options_values_price;
   }
   $option_value_details[$option_id][$option_value_id] = array (
       "options_values_price" => $opt_options_values_price,
       "price_prefix" => $opt_price_prefix);

 

and at about line 407 of edit_orders.php, replace

    price_prefix = '+';";

with

	price_prefix = '" . $option_value_details[$option_id][$option_value_id]["price_prefix"] . "';";

Link to comment
Share on other sites

Hi akkinmore.

 

When you say "When you add product to the order the quantity is not updated", do you mean that you press the "Add Product" button to add more of a product already on the order? In that case you probably see a new line item for the added quantity. Probably identical items should be merged, but it is easy enough just to change the quantity field if you are adding more of something already on the order.

 

Perhaps you are altering the quantity on a line, but not seeing the order price change? In that case, maybe you need to scroll to the bottom of the order edit page and press the "Update" button?

 

Or are you saying that it worked before you introduced my changes? In that case I will be grateful for an example or other detail to rectify the changes as soon as possible.

 

-- Jim

Link to comment
Share on other sites

Hi akkinmore.

 

When you say "When you add product to the order the quantity is not updated", do you mean that you press the "Add Product" button to add more of a product already on the order?  In that case you probably see a new line item for the added quantity.  Probably identical items should be merged, but it is easy enough just to change the quantity field if you are adding more of something already on the order.

 

Perhaps you are altering the quantity on a line, but not seeing the order price change?  In that case, maybe you need to scroll to the bottom of the order edit page and press the "Update" button?

 

Or are you saying that it worked before you introduced my changes?  In that case I will be grateful for an example or other detail to rectify the changes as soon as possible.

 

-- Jim

Hi Jim,

 

I double check again and here is my problem with this contrib. When I click the Add a new product button and was directed to the selection page. I selected a product, attributes and quantity and pressed the update button.

 

The order showed correctly but when I click on the category/product page for that particular product that I placed manually, the quantity is still remain the same.

 

Eg before manual edit Quantity = 10, after manual edit the order quantity = 10.

 

This contrib is not updating the product quantity. Any ideas?

 

Thank you for your help. :D

Edited by akkinmore
Link to comment
Share on other sites

Hello All!

 

Have installed and debugged this contribution and have it just about ready to go with only one problem. Nothing shows in the dropdown when I select a product with attributes. Reading through all the posts it appears that I am alone in this. Anyone heard of this problem and have any ideas on what the issue could be?

 

Installed on MS2

 

Thanks!

Link to comment
Share on other sites

Hello All!

 

Have installed and debugged this contribution and have it just about ready to go with only one problem. Nothing shows in the dropdown when I select a product with attributes. Reading through all the posts it appears that I am alone in this. Anyone heard of this problem and have any ideas on what the issue could be?

 

Installed on MS2

 

Thanks!

Hi,

 

I think you did not install properly or mess up with other contribs. I don't have such problem. All the products with attributes work okay. The problem that I have with this contrib is the product quantity. ;)

Link to comment
Share on other sites

Changing edit_orders to update the quantity in inventory will take a little work.

 

It appears that pattern established in checkout_process can be followed, but the quantity adjustment will probably have to compare the "after" order with a "before" order to find out what changes were made. It would be helpful to consolidate the quantities for each unique product id when it occurs on two order lines (if the product attributes mechansims don't interfere.) Unlike checkout_process, which only reduces inventory quantities, edit_orders will have to increase inventory quantities when an order quantity is reduced or when an order or order line is deleted.

 

The inventory adjustments of checkout_process and edit_orders probably should be combined in an inventory module, and the inventory probably should be kept in a record separate from the products record.

Link to comment
Share on other sites

Hi Jim

 

I see your point. I hope this can be sorted out as I checked the code and there is nothing about quantity deduction or addition. I think I have to resort to manually change the quantity for the moment.

 

Thanks for your help.

Link to comment
Share on other sites

  • 3 weeks later...
Changing edit_orders to update the quantity in inventory will take a little work.

 

It appears that pattern established in checkout_process can be followed, but the quantity adjustment will probably have to compare the "after" order with a "before" order to find out what changes were made. It would be helpful to consolidate the quantities for each unique product id when it occurs on two order lines (if the product attributes mechansims don't interfere.) Unlike checkout_process, which only reduces inventory quantities, edit_orders will have to increase inventory quantities when an order quantity is reduced or when an order or order line is deleted.

 

The inventory adjustments of checkout_process and edit_orders probably should be combined in an inventory module, and the inventory probably should be kept in a record separate from the products record.

Great work dude!

 

Btw i've to say that don't having the inventory auto modify for quantityt is a pain in the @ss... I receive a lot of order, and i made a lot of modification, so that modify would be great...

 

Another thing: when adding some product the list is a little strange, whit all the filed in a single column, without idention. I'like more the way oscommerce use to show categories and sub categories, like when u move products form a categories to another: that's very fast! your metod won't work cause i've a lot of categories whit the same name...

 

hoping to be clear...

 

 

btw i'll try myself to modify this great (but uge) contrib. l8r

my contribution: Alex's Contributions

Link to comment
Share on other sites

So, here we are, i've edited this great contrib for having the quantity in the inventory always up-to-date!!!

 

I've used as a base the version 1.4 of this contrib, so if u like this possibility try this modify out:

 

at line 74 find:

  $action = (isset($HTTP_GET_VARS['action']) ? $HTTP_GET_VARS['action'] : 'edit');

and add below:

//UPDATE_INVENTORY_QUANTITY_START#################################################
#############################################################
$order_query = tep_db_query("select products_id, products_quantity from " . TABLE_ORDERS_PRODUCTS . " where orders_id = '" . (int)$oID . "'");
//UPDATE_INVENTORY_QUANTITY_START#################################################
#############################################################

at line 196 find:

// Update orders_products Table

add below:

 	 //UPDATE_INVENTORY_QUANTITY_START#################################################
#############################################################
	 $order = tep_db_fetch_array($order_query);
	 if ($products_details["qty"] != $order['products_quantity']){
   $differenza_quantita = ($products_details["qty"] - $order['products_quantity']);
  	 tep_db_query("update " . TABLE_PRODUCTS . " set products_quantity = products_quantity - " . $differenza_quantita . ", products_ordered = products_ordered + " . $differenza_quantita . " where products_id = '" . (int)$order['products_id'] . "'");
	 }
	 //UPDATE_INVENTORY_QUANTITY_END###################################################
###########################################################

at line 229 find:

tep_db_query($Query);

add below:

   	 //UPDATE_INVENTORY_QUANTITY_START#################################################
#############################################################
   $order = tep_db_fetch_array($order_query);
  	 if ($products_details["qty"] != $order['products_quantity']){
     $differenza_quantita = ($products_details["qty"] - $order['products_quantity']);
     tep_db_query("update " . TABLE_PRODUCTS . " set products_quantity = products_quantity - " . $differenza_quantita . ", products_ordered = products_ordered + " . $differenza_quantita . " where products_id = '" . (int)$order['products_id'] . "'");
  	 }
  	 //UPDATE_INVENTORY_QUANTITY_END###################################################
###########################################################

at line 395 find:

$new_product_id = tep_db_insert_id();

add below:

 	 //UPDATE_INVENTORY_QUANTITY_START#################################################
#############################################################
	 tep_db_query("update " . TABLE_PRODUCTS . " set products_quantity = products_quantity - " . $add_product_quantity . ", products_ordered = products_ordered + " . $add_product_quantity . " where products_id = '" . $add_product_products_id . "'");
	 //UPDATE_INVENTORY_QUANTITY_END###################################################
###########################################################

 

That should do the trik. For me this worked very well. let me know if there is any problem. i'll try to help.

 

I've a request: how to change the selection menu when u add a new product and let it be like the "move product" menu?

 

P.S. If u like the mod let me know. Bye

my contribution: Alex's Contributions

Link to comment
Share on other sites

Changing edit_orders to update the quantity in inventory will take a little work.

 

It appears that pattern established in checkout_process can be followed, but the quantity adjustment will probably have to compare the "after" order with a "before" order to find out what changes were made.  It would be helpful to consolidate the quantities for each unique product id when it occurs on two order lines (if the product attributes mechansims don't interfere.)  Unlike checkout_process, which only reduces inventory quantities, edit_orders will have to increase inventory quantities when an order quantity is reduced or when an order or order line is deleted.

 

The inventory adjustments of checkout_process and edit_orders probably should be combined in an inventory module, and the inventory probably should be kept in a record separate from the products record.

not that difficult, i've made it a more simple way.

 

Let me know if u find out a bug in this procedure.

 

regarding the scroll down menu i think the problem is this piece of code:

$result = tep_db_query("SELECT products_name, p.products_id, categories_name, ptc.categories_id FROM " . TABLE_PRODUCTS . " p LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd ON pd.products_id=p.products_id LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " ptc ON ptc.products_id=p.products_id LEFT JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd ON cd.categories_id=ptc.categories_id ORDER BY categories_name");
 while($row = tep_db_fetch_array($result))
 {
	 extract($row,EXTR_PREFIX_ALL,"db");
	 $ProductList[$db_categories_id][$db_products_id] = $db_products_name;
	 $CategoryList[$db_categories_id] = $db_categories_name;
	 $LastCategory = $db_categories_name;
 }

 

isn't possible to use the function tep_draw_pull_down_menu() intead of this one?

 

I've got a lot of category and subcategoryes, and the selection in this way is not that simple, cause i've a lot of subcat with the same name...

 

Any help?

Edited by Drako

my contribution: Alex's Contributions

Link to comment
Share on other sites

I've got a lot of category and subcategoryes, and the selection in this way is not that simple, cause i've a lot of subcat with the same name...

 

Any help?

Don't know if it's easy to implement within the order editor, but I'm using this code to re-create the osC categories dropdown:

  /* categories box code */  
   $hidden_get_variables = '';
   reset($_GET);
   while (list($key, $value) = each($_GET)) {
       if (($key != 'category') && ($key != tep_session_name()) && ($key != 'x') && ($key != 'y')) {
           $hidden_get_variables .= tep_draw_hidden_field($key, $value);
       } 
   } 

   $categoriesBox = tep_draw_form('category', tep_href_link(FILENAME_PRICELIST, '', $request_type, false), 'get'); 
   $categoriesBox .= tep_draw_pull_down_menu('category', tep_get_category_tree('', '', '', '', '', true), $current_category_id, 'onChange="this.form.submit();"') . $hidden_get_variables . tep_hide_session_id();
   $categoriesBox .= '</form>';
  /* end categories box code */ 
  
echo $categoriesBox;

 

hth

Link to comment
Share on other sites

Im having a strange problem, for some reason when Im editing orders 2 of my 15 catatgories are showiong up in german and everything when I get to step 3 is in german but everything else in steps 1 2 and 4 are in english. This has gotten me stumped. Any one got a fix for this?

 

Thanks

~Steel~ :blink:

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...