Jump to content
Rochdalemark

Attribute Product Codes [Contribution]

Recommended Posts

Written by Fred Doherty (aka Rochdalemark) with a large chunk of help from Richard Lindsey (aka Velveeta)

 

This contribution will allow you to add codes to each product attribute based on a per item only basis. This is so that if you have 2 suppliers having different codes for say a colour red attribute you can have a different code for each.

The attribute product code will be used as a suffix to the base product code.

i.e

Say you sold Apples.

 

The code for your standard Apple is AP1

But you had a choice of colours and sizes for those apples, you can now have the following codes generated for each.

Green Apple: AP1/G

or

Red Apple: AP1/R

or

Apple Large: AP1/L

or

Apple Large Red: AP1/L-R

 

As you can see the base product code stayes the same but with the seperate atribute code added as a suffix to the end.

This each attribute product code can be given a sort value, so that your code is in the right order.

For example

Your code for a Large Red Apple: AP1/L-R could mean a completely different product if it was the other way round like AP1/R-L. This way there is no mistakes in the way the codes are generated when multiple attributes are selected.

 

Contribution can be found at

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

Edited by Rochdalemark

Share this post


Link to post
Share on other sites

Although I dont use the Quantity Tracking Professional contribution to track my attribute stock as mine are all made to order items, I shouldn't see why it shouldnt work with the contribution as all my contribution does is replace the way the product codes are displayed by replacing [model] ie product code with

 ie product code plus attribute suffix product code.

 

If you try it out please report back here and let me know how it went.

 

Regards

 

Fred

 

P.S

 

Maybe Ill look into intergrating both contributions into one as this would be a good solution for people who want seperate codes for attributes and also to track them for stock purposes.

Edited by Rochdalemark

Share this post


Link to post
Share on other sites

Hi,

 

I just installed your wonderful contribution, and I think it is a great idea.

 

I have a few questions and installation problems that I need your help.

 

I got an error when updating the suffix codes in the admin product attributes page.

 

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

 

update products_attributes set products_id = '2', options_id = '3', options_values_id = '6', code_suffix = 'Premium', suffix_sort_order = '1', options_values_price = '0.0000', price_prefix = '+' where products_attributes_id = '8'

 

 

I wonder if I have missed something in the installation process. Do I need to create a new field in the product options table for the code suffix? I think it would be saving a lot of database spaces if not. Does this contribution mean to create the code suffix on the fly based on the option values information without manually entering the code suffix in the database tables and the admin product attributes? Then how about the suffix sort order, can this be set to the options original order in the database without having to enter a new field in the product attribute table?

 

Thank you.

Share this post


Link to post
Share on other sites
Hi,

 

I just installed your wonderful contribution, and I think it is a great idea.

 

I have a few questions and installation problems that I need your help.

 

I got an error when updating the suffix codes in the admin product attributes page.

 

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

 

update products_attributes set products_id = '2', options_id = '3', options_values_id = '6', code_suffix = 'Premium', suffix_sort_order = '1', options_values_price = '0.0000', price_prefix = '+' where products_attributes_id = '8'

I wonder if I have missed something in the installation process. Do I need to create a new field in the product options table for the code suffix? I think it would be saving a lot of database spaces if not. Does this contribution mean to create the code suffix on the fly based on the option values information without manually entering the code suffix in the database tables and the admin product attributes? Then how about the suffix sort order, can this be set to the options original order in the database without having to enter a new field in the product attribute table?

 

Thank you.

 

Hi Betty,

 

Did you run the SQL queries?

 

ALTER TABLE `products_attributes` ADD `code_suffix` varchar(11) NOT NULL default '' AFTER `options_values_id`;

ALTER TABLE `orders_products_attributes` ADD `code_suffix` varchar(11) NOT NULL default '' AFTER `products_options_values`;

ALTER TABLE `orders_products` ADD `products_code` varchar(20) NOT NULL default '' AFTER `products_model`;

ALTER TABLE `products_attributes` ADD `suffix_sort_order` int(11) NOT NULL default '' AFTER `code_suffix`;

 

Check your product_attributes table and make sure the code_suffix field is there.

 

The contribution will create a suffix on the fly when a person orders a product with multiple attributes based on the order you set the attributes to appear in i.e

 

colours: red and blue could both be set at 1, sizes: standard and large could both be set at 2, so the size suffix would allways appear after the colour suffix, i.e APP1/blue-Large etc

 

Ive done it this way so that you can be precise about how they are generated rather than relying on the order they were entered in the database. as you might want to add more attributes etc at a later date.

 

It is also done on a product by product basis due to the fact that the suffix code for a red apple might be different for a red pepper for example.

 

The field in the database for the attributes_suffix is populated when you edit each products attributes suffic code in the attributes admin. and the products code complete with suffix is generated when the customer select various product attributes. this then populates the products code field in the orders_products table for each order.

 

#################

 

My next step is to add the AJAX attribute manager to this contribution so that you can edit the attributes code suffix for each product in the main products page rather than going back and forth to the attributes page.

Share this post


Link to post
Share on other sites

Error
SQL query: 

ALTER TABLE `products_attributes` ADD `suffix_sort_order` int( 11 ) NOT NULL default '' AFTER `code_suffix` 

MySQL said:  

#1067 - Invalid default value for 'suffix_sort_order'

 

FYI: Can't default an int() as a string ''. Must default it as 0,1,etc.

Share this post


Link to post
Share on other sites
Will this contribution allow something similar to:

http://www.lumens.com/lumens/product.asp?d...AAAAANDBPDELLCF

 

I am specifically looking for a unique attribute_id and not using the "placeholder" product_id.

 

This contribution will only generate unique codes based on the products main base code followed by the attribute suffix code.

 

Maybe your looking for master slave products which uses unique products as kind of attributes. but you have to create a new product for each one.

Share this post


Link to post
Share on other sites

In the default OSC script when viewing an order you see the Model number which is like the item number for each item in the store. Will this contribution display the Model number like he has it above...AP1, AP1/G, AP1/R & etc?

 

I am asking because I see the following MySQL Query line to run & this is adding another column.

ALTER TABLE `orders_products` ADD `products_code` varchar(20) NOT NULL default '' AFTER `products_model`;

 

i.e

Say you sold Apples.

 

The code for your standard Apple is AP1

But you had a choice of colours and sizes for those apples, you can now have the following codes generated for each.

Green Apple: AP1/G

or

Red Apple: AP1/R

or

Apple Large: AP1/L

or

Apple Large Red: AP1/L-R

Share this post


Link to post
Share on other sites

Hi,

 

Yes it will display it like the above example. It adds a new column for the new product code extension.

 

In the default OSC script when viewing an order you see the Model number which is like the item number for each item in the store. Will this contribution display the Model number like he has it above...AP1, AP1/G, AP1/R & etc?

 

I am asking because I see the following MySQL Query line to run & this is adding another column.

ALTER TABLE `orders_products` ADD `products_code` varchar(20) NOT NULL default '' AFTER `products_model`;

Share this post


Link to post
Share on other sites
Hi,

 

Yes it will display it like the above example. It adds a new column for the new product code extension.

I was looking over the install file & seen something that doesn't make sense.

 

Here you are replacing the display of the product model number with displaying the product code, but in that second piece of code from the install file you are still inserting the model into the db as well as the product code & you print the product code in the order email confirmation. Wouldn't it be easier to just use the existing product model column in the db & insert the product code into products_model since you are just adding a forward slash & the attribute number...AP1/G to the existing model number? You are just adding to the model number the attribute number & inserting the modified model you call the code number into the code field...right?

 

#############################################################
**********   In catalog/admin/orders.php  *****************

Find: around line 233

echo '			</td>' . "\n" .
	   '			<td class="dataTableContent" valign="top">' . $order->products[$i]['model'] . '</td>' . "\n" .



Change To:


echo '			</td>' . "\n" .
	   '			<td class="dataTableContent" valign="top">' . $order->products[$i]['code'] . '</td>' . "\n" .




#############################################################

 

#############################################################
**********   In catalog/checkout_process.php  *****************

Find: around line 160

$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']);
tep_db_perform(TABLE_ORDERS_PRODUCTS, $sql_data_array);



Change To:

$sql_data_array = array('orders_id' => $insert_id, 
						'products_id' => tep_get_prid($order->products[$i]['id']), 
						'products_model' => $order->products[$i]['model'],
						'products_code' => $order->products[$i]['code'],
						'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']);
tep_db_perform(TABLE_ORDERS_PRODUCTS, $sql_data_array);



#############################################################

Share this post


Link to post
Share on other sites
Hi,

 

Yes it will display it like the above example. It adds a new column for the new product code extension.

I was looking over the install file & seen something that doesn't make sense.

 

Here you are replacing the display of the product model number with displaying the product code, but in that second piece of code from the install file you are still inserting the model into the db as well as the product code & you print the product code in the order email confirmation. Wouldn't it be easier to just use the existing product model column in the db & insert the product code into products_model since you are just adding a forward slash & the attribute number...AP1/G to the existing model number? You are just adding to the model number the attribute number & inserting the modified model you call the code number into the code field...right?

 

#############################################################
**********   In catalog/admin/orders.php  *****************

Find: around line 233

echo '			</td>' . "\n" .
	   '			<td class="dataTableContent" valign="top">' . $order->products[$i]['model'] . '</td>' . "\n" .



Change To:


echo '			</td>' . "\n" .
	   '			<td class="dataTableContent" valign="top">' . $order->products[$i]['code'] . '</td>' . "\n" .




#############################################################

 

#############################################################
**********   In catalog/checkout_process.php  *****************

Find: around line 160

$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']);
tep_db_perform(TABLE_ORDERS_PRODUCTS, $sql_data_array);



Change To:

$sql_data_array = array('orders_id' => $insert_id, 
						'products_id' => tep_get_prid($order->products[$i]['id']), 
						'products_model' => $order->products[$i]['model'],
						'products_code' => $order->products[$i]['code'],
						'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']);
tep_db_perform(TABLE_ORDERS_PRODUCTS, $sql_data_array);



#############################################################

Share this post


Link to post
Share on other sites
Hi,

 

Yes it will display it like the above example. It adds a new column for the new product code extension.

I was looking over the install file & seen something that doesn't make sense.

 

Here you are replacing the display of the product model number with displaying the product code, but in that second piece of code from the install file you are still inserting the model into the db as well as the product code & you print the product code in the order email confirmation. Wouldn't it be easier to just use the existing product model column in the db & insert the product code into products_model since you are just adding a forward slash & the attribute number...AP1/G to the existing model number? You are just adding to the model number the attribute number & inserting the modified model you call the code number into the code field...right?

 

#############################################################
**********   In catalog/admin/orders.php  *****************

Find: around line 233

echo '			</td>' . "\n" .
	   '			<td class="dataTableContent" valign="top">' . $order->products[$i]['model'] . '</td>' . "\n" .



Change To:


echo '			</td>' . "\n" .
	   '			<td class="dataTableContent" valign="top">' . $order->products[$i]['code'] . '</td>' . "\n" .




#############################################################

 

#############################################################
**********   In catalog/checkout_process.php  *****************

Find: around line 160

$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']);
tep_db_perform(TABLE_ORDERS_PRODUCTS, $sql_data_array);



Change To:

$sql_data_array = array('orders_id' => $insert_id, 
						'products_id' => tep_get_prid($order->products[$i]['id']), 
						'products_model' => $order->products[$i]['model'],
						'products_code' => $order->products[$i]['code'],
						'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']);
tep_db_perform(TABLE_ORDERS_PRODUCTS, $sql_data_array);



#############################################################

Share this post


Link to post
Share on other sites

Sorry about those three post above...I was having problems w/internet. I attempted to post my reply, but page wouldn't load so I just tried it again until the page loaded. I didn't realize that it did post the first two time.

Share this post


Link to post
Share on other sites

I have been wanting this kind of contribution for so long!

Installed like a dream, absolutely no problems!

But...

I am using Attribute Sets Plus (if that makes a diff) and I do not understand how to edit the options ID to match my inventory ID. For example I have 12 options (colors) as a set 'Crystal Colors' , they have to be labeled 'Jan', 'Feb', etc on the product info page, but I would like to have the prod id set to match my inventory code, for example 'Jan' code would be 'GAR' and 'Feb' would be 'AME' so that when someone orders it will the full product id would come out 'CLA/GAR/G' etc. You can see the options in action on this page

I would appreciate any help on sorting this out, this contribution could be the answer to my prayers on sorting the orders into Quickbooks!


learn...love...laugh...live

Share this post


Link to post
Share on other sites

You cant use the existing model column as you have to think about backward compatibility with people who already have orders and products in there stores. It would screw up there past orders etc

This is the best way I could think of.

 

I was looking over the install file & seen something that doesn't make sense.

 

Here you are replacing the display of the product model number with displaying the product code, but in that second piece of code from the install file you are still inserting the model into the db as well as the product code & you print the product code in the order email confirmation. Wouldn't it be easier to just use the existing product model column in the db & insert the product code into products_model since you are just adding a forward slash & the attribute number...AP1/G to the existing model number? You are just adding to the model number the attribute number & inserting the modified model you call the code number into the code field...right?

 

#############################################################
**********   In catalog/admin/orders.php  *****************

Find: around line 233

echo '			</td>' . "\n" .
	   '			<td class="dataTableContent" valign="top">' . $order->products[$i]['model'] . '</td>' . "\n" .
Change To:
echo '			</td>' . "\n" .
	   '			<td class="dataTableContent" valign="top">' . $order->products[$i]['code'] . '</td>' . "\n" .
#############################################################

 

#############################################################
**********   In catalog/checkout_process.php  *****************

Find: around line 160

$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']);
tep_db_perform(TABLE_ORDERS_PRODUCTS, $sql_data_array);
Change To:

$sql_data_array = array('orders_id' => $insert_id, 
						'products_id' => tep_get_prid($order->products[$i]['id']), 
						'products_model' => $order->products[$i]['model'],
						'products_code' => $order->products[$i]['code'],
						'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']);
tep_db_perform(TABLE_ORDERS_PRODUCTS, $sql_data_array);
#############################################################

Share this post


Link to post
Share on other sites

Just go the the attributes page (Not your sets page) in admin, at the bottom of the page is your seperate item attributes. Just click edit on each of them to add your unique code suffix for each.

 

I have been wanting this kind of contribution for so long!

Installed like a dream, absolutely no problems!

But...

I am using Attribute Sets Plus (if that makes a diff) and I do not understand how to edit the options ID to match my inventory ID. For example I have 12 options (colors) as a set 'Crystal Colors' , they have to be labeled 'Jan', 'Feb', etc on the product info page, but I would like to have the prod id set to match my inventory code, for example 'Jan' code would be 'GAR' and 'Feb' would be 'AME' so that when someone orders it will the full product id would come out 'CLA/GAR/G' etc. You can see the options in action on this page

I would appreciate any help on sorting this out, this contribution could be the answer to my prayers on sorting the orders into Quickbooks!

Share this post


Link to post
Share on other sites
Error
SQL query: 

ALTER TABLE `products_attributes` ADD `suffix_sort_order` int( 11 ) NOT NULL default '' AFTER `code_suffix` 

MySQL said:  

#1067 - Invalid default value for 'suffix_sort_order'

 

FYI: Can't default an int() as a string ''. Must default it as 0,1,etc.

 

I have the same issue, but I don't understand your explanation here. Is this an answer or a thought/comment? should I use 0? 1? etc?

 

Sorry, i'm a mysql GUI kind of guy, not too good with syntax just yet.

 

seems to me that int(11) is asking for room for an 11 digit integer - I don't understand how this is a string.

 

either way, this sql is not working and i'd sure love to fix it!

 

thanks

Share this post


Link to post
Share on other sites

Okay, so maybe I did do something wrong...On my attributes page, on the bottom I can edit my product name, option name, option value, value price, prefix, and sort order. There is also an ID column, but I cannot edit that. Did I mess up somewhere, and if so, any idea where?


learn...love...laugh...live

Share this post


Link to post
Share on other sites

Hi Sean yeah that was an oversight. all you need to do is run this sql:

 

ALTER TABLE `products_attributes` ADD `suffix_sort_order` int( 11 ) NOT NULL default '0' AFTER `code_suffix`

 

Notice the only change is the default '0' instaead of default ''

 

I should realy make that change and add it to the contribution

 

I have the same issue, but I don't understand your explanation here. Is this an answer or a thought/comment? should I use 0? 1? etc?

 

Sorry, i'm a mysql GUI kind of guy, not too good with syntax just yet.

 

seems to me that int(11) is asking for room for an 11 digit integer - I don't understand how this is a string.

 

either way, this sql is not working and i'd sure love to fix it!

 

thanks

Share this post


Link to post
Share on other sites

By the way

 

Anyone who has old orders in there store and who wants to display the old product codes for them can just add an if statement to there admin/orders.php code

 

ie

 

if (tep_not_null($order->products[$i]['code'] {

<display the code value>

} else {

<display the model value>

}

 

this should make it more backward compatible.

Share this post


Link to post
Share on other sites

Acharme

 

You have obviously not done the correct editing of your admin/products_attributes.php

 

Please re check the first part of the install file detailing the changes needed to the files.

or

use a compare tool to check the differences between your products_attributes file and the one included in the contribution.

 

do a search on google for something like beyond compare, download it and you will be able to see your file on one side and the contrib file on the other and spot anything thats different.

 

Okay, so maybe I did do something wrong...On my attributes page, on the bottom I can edit my product name, option name, option value, value price, prefix, and sort order. There is also an ID column, but I cannot edit that. Did I mess up somewhere, and if so, any idea where?

Share this post


Link to post
Share on other sites

What a dodo-head I can be :lol: I originally used WinMerge for these files, so I was absolutely sure that I had got it right. Well, I may have got it right, but I must have neglected to save! :blush: It works perfectly from the admin side, (I have not made a test order to see it on the orders page yet).

One more question: Do I have to put in the codes for every option/for every product? Or if I put the codes in for one product/all options, will it pick up for other products that use the same options?

This is the greatest thing since sliced bread, and I cannot say thankyou loud enough.


learn...love...laugh...live

Share this post


Link to post
Share on other sites

LOL

 

You will have to do it for each product. Might seem like a pain in the bum but I'll explain why its that way..

 

Imagine you had a YELLO attribute.

 

the code for a yellow apple APP/Y could be different for a yellow mellon MEL/YEL .

This way it allows you to be more precise about what codes you give for each product as different suppliers use different codes for the same attributes.

 

Its easy if your just starting to enter products, but could take a bit of time if you have hundreds or even thousands of products in your store..

 

What a dodo-head I can be :lol: I originally used WinMerge for these files, so I was absolutely sure that I had got it right. Well, I may have got it right, but I must have neglected to save! :blush: It works perfectly from the admin side, (I have not made a test order to see it on the orders page yet).

One more question: Do I have to put in the codes for every option/for every product? Or if I put the codes in for one product/all options, will it pick up for other products that use the same options?

This is the greatest thing since sliced bread, and I cannot say thankyou loud enough.

Share this post


Link to post
Share on other sites

Me again, aren't you thrilled?

I used one bracelet kit as a test model. It has 12 crystal color options, 4 lengths, 2 pearl colors, 2 metal colors. I figured out that I had to delete the old attributes as the new ones do not overwrite. So, got all of the new ones in there, and it seemed to rearrange the order of display (what was Jan, Feb, Mar on the prod info page is now Mar, Jan, Feb), so I fixed that through Att Sets Plus. Put a test order through, it was missing 2 of my 4 options in the model number (only the pearl and metal colors showed up) in the emails, and no model number appears in the admin/orders page, and only the pearl and metal options are retaining the formating in the admin/prod attributes page. All of the others are now missing the code suffix and suffix order. (and before you ask, I made certain that I saved each and every one :D )

Grasshopper is confused, Master. (But that doesn't take much)

Thankyou for your time and patience


learn...love...laugh...live

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

×