March 30 2010
WP e-commerce SKU on variations
Tagged Under : e-commerce, fix, sku, Wordpress, wordpress plugin, wp e-commerce
UPDATE – 01/09/2010
If you need additions that are not contemplated in this fix package you can email me for a specific quote.
UPDATE – 18/05/2010
There has been an update on wp-e-commerce a few days ago and if you have applied my add-on you’ll probably have problems with the plugin. Post a comment or email me if you’re having problems.
===============================
If you are using wp-ecommerce plugin for wordpress and you ran into some missing functionalities this might help you.
The specific mod I’ll show you today will enable you to specify SKU to any variation on any product on your system.
I don’t know how proficient your are on programming WordPress, but let me know if you run into any difficulty.
So, first of all you need to add a new field on your database on table wp_wpsc_variations_properties called “sku”. I added this field as varchar with a length of 64 characters, but do adapt this one to fit your needs.
Backup your e-commerce plugin folder.
Now, open file /wp-content/plugins/wp-e-commerce/wpsc-includes/variations.class.php.
After line,
$output .= " ".TXT_WPSC_VARIATION." \n\r"; |
add,
$output .= " ".TXT_WPSC_SKU." \n\r"; |
Change line,
$unindexed_variation_properties_data = $wpdb->get_results("SELECT `id`, `stock`, `price`, `weight`,`weight_unit`, `file` FROM `".WPSC_TABLE_VARIATION_PROPERTIES."` WHERE `id` IN({$priceandstock_id_string})", ARRAY_A); |
to,
$unindexed_variation_properties_data = $wpdb->get_results("SELECT `id`, `stock`, `sku`, `price`, `weight`,`weight_unit`, `file` FROM `".WPSC_TABLE_VARIATION_PROPERTIES."` WHERE `id` IN({$priceandstock_id_string})", ARRAY_A); |
After line,
$product_price = $variation_properties_data[$variation_set['priceandstock_id']]['price']; |
add,
$product_sku = $variation_properties_data[$variation_set['priceandstock_id']]['sku']; |
After line,
$product_price = $product_data['price']; |
add,
$product_sku = ""; |
After line,
$output .= "".str_replace(" ", " ", (stripslashes( $variation_names )))."\n\r"; |
add,
$output .= " <input name="variation_priceandstock[{$variation_ids}][sku]" size="8" type="text" value="$product_sku" />\n\r"; |
After line,
$variation_price = (float)str_replace(",","",$variation_data['price']); |
add,
$variation_sku = (string)$variation_data['sku']; |
After line,
$variation_sql = null; // return the sql array to null for each trip round the loop |
add,
if(($variation_stock_data['sku'] != $variation_sku)) { $variation_sql[] = "`sku` = '{$variation_sku}'"; } |
This should enable you fill the sku field we added before. Now to get the info of the sku on the order page.
Open file /wp-content/plugins/wp-e-commerce/wpsc-includes/purchaselogs.class.php.
On line,
function wpsc_purchaselog_details_SKU(){ |
Change all function to,
purchitem->prodid, 'sku'); $variation_name = stripslashes($purchlogitem->purchitem->name); $associated_variations = $wpdb->get_col("SELECT `variation_id` FROM `".WPSC_TABLE_VARIATION_ASSOC."` WHERE `type` IN ('product') AND `associated_id` = '{$purchlogitem->purchitem->prodid}' ORDER BY `id` ASC"); $variation_count = count($associated_variations); asort($associated_variations); if($variation_count > 0) { //stripslashes($purchlogitem->purchitem->name); foreach((array)$associated_variations as $variation) { $variation = (int)$variation; $excluded_value_sql = ''; if($purchlogitem->purchitem->prodid > 0 ) { $included_values = $wpdb->get_col("SELECT `value_id` FROM `".WPSC_TABLE_VARIATION_VALUES_ASSOC."` WHERE `product_id` IN('{$purchlogitem->purchitem->prodid}') AND `variation_id` IN ('{$variation}') AND `visible` IN ('1')"); $included_values_sql = "AND `a{$variation}`.`id` IN('".implode("','", $included_values)."')"; } //else if(count($variation_values) > 0) { //$included_values_sql = "AND `a{$variation}`.`id` IN('".implode("','", $variation_values)."')"; //} $join_selected_cols[] = "`a{$variation}`.`id` AS `id_{$variation}`, `a{$variation}`.`name` AS `name_{$variation}`"; $join_subselect_cols[] = "`a{$variation}`.`id`"; $join_tables[] = "`".WPSC_TABLE_VARIATION_VALUES."` AS `a{$variation}`"; $join_conditions[] = "`a{$variation}`.`variation_id` = '{$variation}' $included_values_sql"; } $variation_count = count($associated_variations); $all_variation_ids = implode(",", $associated_variations); $join_subselect_cols = implode(", ", $join_subselect_cols); $join_selected_cols[] = "( SELECT `priceandstock_id` FROM `".WPSC_TABLE_VARIATION_COMBINATIONS."` WHERE `product_id` = '{$purchlogitem->purchitem->prodid}' AND `value_id` IN ( {$join_subselect_cols} ) AND `all_variation_ids` IN ( '{$all_variation_ids}' ) GROUP BY `priceandstock_id` HAVING COUNT( `priceandstock_id` ) = '{$variation_count}' LIMIT 1 ) AS `priceandstock_id`"; // implode the SQL statment segments into bigger segments $join_selected_cols = implode(", ", $join_selected_cols); $join_tables = implode("\nJOIN ", $join_tables); $join_conditions = implode("\nAND ", $join_conditions); // Assemble and execute the SQL query $associated_variation_values = $wpdb->get_results( "SELECT {$join_selected_cols} FROM {$join_tables} WHERE {$join_conditions} ORDER BY {$join_subselect_cols}" , ARRAY_A); //print_r($associated_variation_values); foreach((array)$associated_variation_values as $key => $variation_set) { $variation_id_array = array(); $variation_names = array(); foreach($associated_variations as $variation_id) { $variation_id_array[] = $variation_set["id_{$variation_id}"]; $variation_names[] = $variation_set["name_{$variation_id}"]; } $variation_names = implode(", ", $variation_names); $variation_ids = implode(",", $variation_id_array); if(strpos($variation_name, $variation_names)) { $variation_sku = $wpdb->get_row("SELECT `sku` FROM `".WPSC_TABLE_VARIATION_PROPERTIES."` WHERE `id` = '{$variation_set['priceandstock_id']}' LIMIT 1", ARRAY_A); if(!empty($variation_sku['sku'])) $meta_value = $variation_sku['sku']; } } } if($meta_value ==''){ return 'N/A'; }else{ return $meta_value; } } ?> |
This should get you up and running. Let me know if you need anything.
Hi JP,
thanks a bunch for this code! I needed to make some changes to it since I’m using WP e-Commerce 3.7.6 and some of the text constants were replaced by WP’s __ function. And for some reason I had to add a global $wpdb; to wpsc_purchaselog_details_SKU().
Anyway, this is a much needed modification. Thanks again!
Tom
Hello Tom,
Thank you for the kind words. I’ve been having a lot of requests from a lot of people regarding this feature.
Yes some changes might be needed to adapt this to different versions. Let me know if you require and further assistance
Actually, there is one thing I need help on. I’m coding some extra stuff for my WPEC site and now I’m trying to get the variation SKU with a single SQL statement. I’ve been wrecking my head as SQL really isn’t my strong suit.
But I figure there must be a way to get from the purchase id to the cart id to the variations of the products in the cart all the way down to the variation SKU. But whatever I do, I get back too many rows.
Do you by any chance have an idea how to achieve this with an SQL query?
Hello again Tom,
We can do this but not in 1 single SQL statement, because field all_variation_ids is comma separated. If we would work with the SQL code we might be able to do this but would represent a break on the Database atomicity.
However I can provide you with 2 SQL statements that you can easily use to create a PHP functions to return a given product sku.
Store in an array the result of this SQL statement: SELECT `variation_id` FROM `wp_wpsc_variation_assoc` WHERE `type` IN (‘product’) AND `associated_id` = ’4′ ORDER BY `variation_id` ASC
Now lets say you have the array called $variation_array. You’ll have to do a second SQL as follows:
“SELECT `sku` FROM `wp_wpsc_variation_properties` WHERE `id` IN (SELECT DISTINCT `priceandstock_id` FROM `wp_wpsc_variation_combinations` WHERE `value_id` IN (SELECT `value_id` FROM `wp_wpsc_variation_values_assoc` WHERE `product_id` IN (’4′) AND `variation_id` IN ( SELECT `variation_id` FROM `wp_wpsc_variation_assoc` WHERE `type` IN (‘product’) AND `associated_id` = ’4′ ) AND `visible` IN (’1′)) AND `product_id` IN (’4′) AND `all_variation_ids` LIKE ‘”.implode(“,”,$variation_array).”‘)”
This will return the exact SKU for the given variation.
You just need to provide the product_id, in this case, it’s 4.
Let me know if this fits your needs.
I guess that’s why I was having such a hard time wrapping my head around this. I thought there had to be a way to get this done in a single query, but I kept hitting a roadblock with the variation_id and value_id fields.
I’ll give your solution a try, definitely looks good.
Thanks again!
WOW… this fix looks like it should do the trick but MAN what a mess. I am not a programmer so this may be a test of my ability to follow directions!
Hello Erika
Please let me know if you require aid on implementing the solution. Just follow the directions and you should be fine.
Jose – I implemented everything as is listed above in your post. Upon ‘save’ and then trying to view the backend or the frontend the site was gone. Everything was blank. Ideas? I’m using WP Version 2.9.2 & WPEC Version 3.7.5.9.
Hello Erika,
Lets try to see what is going wrong.
First thing we must do is see why the page is blank. It’s probably due to a PHP error and since WP omits all error we need to turn debug on WP.
To do this open the wp-config.php file and add this line:
define(‘WP_DEBUG’, true);
After that open your blog and check what error it’s outputting.
Looking forward to earing from you.
Hey José,
I’ve been trying to apply the changes to different versions (i.e. 3.7.5.3 and 3.7.6.1) but I get a feeling I’m either missing something or I’m not applying your changes properly. I keep getting syntax errors, SKUs don’t save on variations, etc.
Would it be possible for you to take the latest version, apply the changes yourself, and make them available for download? That way, if someone who’s not all that experienced is doing something wrong, we can rule out newbie editing mistakes.
Thank you.
John
Hello John,
I can certainly make the adaptations for different versions. I know that for some versions the syntax changes a bit.
Please sent me the two files I mention on the guide, by email, to me and I’ll gladly make the changes and provide them here on the post.
Best regards.
Hey José,
I tried sending you an email but for some reason, Yahoo couldn’t find your domain at joseairosa@core-creations.pt.
Rather, I’ve uploaded the files for 3.7.6.1 at:
http://hotfile.com/dl/39664833/262b118/3.7.6.1-files.zip.html
John
Hey John,
That email doesn’t exist anymore. I probably forgot to change it on the Blog. I’ll download and upload the corrected files here on the Post.
Hello again John,
I’ve added the changes to the files. I didn’t have a chance to test them so please give me some feedback.
http://www.joseairosa.com/downloads/3.7.6.1-files.zip
Hey José,
The modifications worked great. I’m in the process of integrating it into my site. Thanks!
John
I’m glad it worked!
Great addition to the module
One thing I noticed is when you add a variation and enter the SKU number, when saving the product, the first save doesn’t store the SKU number in the database, and the SKU field is empty, so you have to enter it again and save. I modified the insert query in the update_variation_values function to include ‘{$variation_sku}’ in the inserted fields.
$wpdb->query(“INSERT INTO `”.WPSC_TABLE_VARIATION_PROPERTIES.”` ( `product_id` , `stock`, `price`, `weight`, `weight_unit`, `file`, `sku` ) VALUES (‘{$product_id}’, ‘{$variation_stock}’, ‘{$variation_price}’, ‘{$variation_weight}’, ‘{$variation_weight_unit}’, ‘{$variation_file}’, ‘{$variation_sku}’);”);
Hello Milos,
Glad you’ve found it useful. And thank you for the addition
Jose – will you be releasing any updates to match the new release of wp-e-commerce (3.7.6.3)?
Hello Erika,
At the moment it will be a bit hard for me to focus on releasing a new compatible version.
I have already noted down this on my to-do list and I’ll try to have it ready for next week
Sorry for not being sooner.
Will the same work around you have created (above) work?
I will have to take a look at the new code, but if they didn’t change anything on those 2 files it will work as before.
You can test it out but create a backup of your wp-e-commerce files first and database.
Also, don’t forget to backup both files (variations.class.php and purchaselogs.class.php) before installing the new version. After installing the new version got to your FTP and replace them. Again, don’t forget to backup your files
I have a partial fix, but need help on the rest.
I’m running Version 3.7.6.7 and some code has been updated from what is listed in this post. I was able to make the appropriate changes. Mainly reformatting some lines into tables( adding etc.) and changing a few ” to ‘
The only significant difference was instead of adding this line:
$output .= ”
“.TXT_WPSC_SKU.”
\n\r”;
Use this line:
$output .= ” “.__(‘sku’, ‘wpsc’).”\n\r”;
So, I’m pretty sure I have the variations.class.php file updated correctly (I’m not receiving any errors) and it looks good when adding a variation to the product.
However, I can not figure out how to update the suggested code for purchaselogs.class.php . I have tried everything I can think of, and am out of options for that one. I would greatly appreciate help with the updates to the added lines of code for purchaselogs.class.php.
Thanks,
–Blake
Ok.. I some how skipped over José’s comment with this attachment: http://hotfile.com/dl/39664833/262b118/3.7.6.1-files.zip.html
That fixed my issues with purchaselogs.class.php
Everything is working now… Thanks José !!!
Hi – awesome fix. I’m on 3.7.6.7 and it works great. I’m trying to extend it to allow for the SKU to come through in the admin email once the cart has submitted. I know it’s line ~180 in the transaction_result_functions.php but can’t nail the SQL. Any help would be greatly appreciated. Thanks again!
Hello Morgan.
I’m glad it was helpful to you. I will take a look at your issue and reply asap.
Hey guys, I’m having a bit of trouble with this, I can’t seem to find this code anywhere in the variations.class.php
—————–
$output .= ”
“.TXT_WPSC_VARIATION.”
\n\r”;
—————–
any ideas?
(I’m running version 3.7.6.4 by the way)
Actually disregard all that, all seems to be working now!
I’m glad you got it to work Dan.
Let me know if you require additional assistance