Unique SKU for each attribute?

[AaronN]

Hey I will have to look at the code and see if I can remember how I did it. It was a while ago so let me dig around and see if I can come up with some steps.

Excellent, I'm rather stuck without this ability.
Thanks
-Aaron

[ryanf]

Ok, I've been going through the site trying to figure this out. If this was 8 months ago, I could tell you guys what I did, but I'm having trouble finding everything. I'll give you guys the most I can figure out, the rest I'll have to give you an idea of what to do.

To see what this does go to:http://www.optimumnutrition.com/prod...ard-p-201.html

You can see that the product has many flavors and sizes yet when you add a certain selection to the shopping cart, each has it's own SKU.

1: I created a new database table:

CREATE TABLE `products_attributes_skus` (
  `products_id` int(11) NOT NULL default '0',
  `products_attributes_ids` varchar(255) NOT NULL default '',
  `sku` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`products_id`,`products_attributes_ids`,`sku`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

2: I modified admin/attribute_controller.php in two places

I added a new case under the $action switch statement:

//<!-- CUSTOM SKU ENTRY MOD!!! BY RYANF !!! -->    
      case 'update_sku':
           $action = '';
          sort($_POST['sku_attributes']);
        $products_attributes_ids = '';
        for ($i=0; $i<count($_POST['sku_attributes']); ++$i) {
            $products_attributes_ids .= $_POST['sku_attributes'][$i].'_';
        }
        $products_attributes_ids = trim($products_attributes_ids, "_");
        $check_duplicate = $db->Execute("select products_id from products_attributes_skus where products_id = '".$_GET['products_filter']."' and products_attributes_ids = '".$products_attributes_ids."'");
        if ($check_duplicate->RecordCount() > 0) {
            $db->Execute("update products_attributes_skus set sku = '".$_POST['sku_number']."' where products_id = '".$_GET['products_filter']."' and products_attributes_ids = '".$products_attributes_ids."'");
        } else { 
            $db->Execute("insert into products_attributes_skus (products_id, sku, products_attributes_ids) values ('".$_GET['products_filter']."', '".$_POST['sku_number']."', '".$products_attributes_ids."')");
        }
          break;        
 //<!-- CUSTOM SKU ENTRY MOD!!! BY RYANF !!! --> 

This will add the sku number for the given combination of attributes in the new table.

I added a new form area above the new attributes part and below the legend box:

<!-- CUSTOM SKU ENTRY MOD!!! BY RYANF !!! -->
        <tr>
            <td colspan="3" class="main" height="20" align="center">
            
            <?php
            $sku = "select sku, products_attributes_ids from products_attributes_skus where products_id = '".$products_filter."'";
            $sku_query = $db->Execute($sku);
            echo '<table>';
            echo '<tr>';
                echo '<td>SKU</td>';
                echo '<td>OPTIONS</td>';
            echo '</tr>';
            while (!$sku_query->EOF) {
                echo '<tr>';
                    echo '<td>'.$sku_query->fields['sku'].'</td>';
                    $ids = explode("_", $sku_query->fields['products_attributes_ids']);
                    $info = '';
                    for ($i=0; $i<count($ids); ++$i) {
                        $query = "select po.products_options_name, pov.products_options_values_name from products_attributes pa, products_options po, products_options_values pov where pa.products_attributes_id = '".$ids[$i]."' and pa.options_id = po.products_options_id and pa.options_values_id = pov.products_options_values_id order by  po.products_options_sort_order, pov.products_options_values_sort_order";
                        $id_name = $db->Execute($query);
                        $info .= '<em>'.$id_name->fields['products_options_name'].'</em> '.$id_name->fields['products_options_values_name'].' ';
                    }
                    echo '<td>'.$info.'</td>';
                echo '</tr>';
                $sku_query->MoveNext();
            }
            echo '</table>';
            ?>
            
            
            
            <form name="sku_update" action="<?php echo zen_href_link(FILENAME_ATTRIBUTES_CONTROLLER, 'action=update_sku'. (isset($_GET['option_page']) ? '&option_page=' . $_GET['option_page'] . '&' : '') . (isset($_GET['value_page']) ? '&value_page=' . $_GET['value_page'] . '&' : '') . (isset($_GET['attribute_page']) ? '&attribute_page=' . $_GET['attribute_page'] : '') . '&products_filter=' . $products_filter ); ?>" method="post">
                <select name="sku_attributes[]" multiple size="5">
                <?php
                $attributes = "select po.products_options_name, pov.products_options_values_name, pa.products_attributes_id from products_attributes pa, products_options po, products_options_values pov where pa.products_id = '".$products_filter."' and pa.options_id = po.products_options_id and pa.options_values_id = pov.products_options_values_id order by  po.products_options_sort_order, pov.products_options_values_sort_order";
                $attribute_query = $db->Execute($attributes);
                
                while (!$attribute_query->EOF) {
                    echo '<option value="'.$attribute_query->fields['products_attributes_id'].'">';
                    echo $attribute_query->fields['products_options_name'].' '.$attribute_query->fields['products_options_values_name'];
                    echo '</option>';
                    $attribute_query->MoveNext();
                }
                ?>
                </select><br />
                <label for="sku_number">SKU:</label><input type="text" id="sku_number" name="sku_number" /><br />
                <input type="submit" value="Add" />
            </form>
            </td>
        </tr>
        <!-- END CUSTOM SKU ENTRY MOD!!! BY RYANF !!! -->

This allows you to select the different attribute combinations and enter in a sku for the combo. If you mess up just reselect the combination and reenter the sku to overwrite it. If the combination is no longer valid, just delete the attribute, the sku will still be displayed on this page, but not the products info page.

Next I modified includes/classes/order.php

I added the line:

$pas = array(); 

in the function create_add_products()
and the line:

// build products_attributes_id list to get sku number later
$pas[] = $attributes_values->fields['products_attributes_id']; 

At the end of the for loop in the same function that starts with:

for ($j=0, $n2=sizeof($this->products[$i]['attributes']); $j<$n2; $j++) { 

After the if statement closes that the for loop from above is in, I added:

// add sku number to attributes
sort($pas);
        
$products_attributes_ids = '';
for ($x=0; $x<count($pas); ++$x) {
    $products_attributes_ids .= $pas[$x].'_';
}
$products_attributes_ids = trim($products_attributes_ids, "_");
$get_sku = $db->Execute("select sku from products_attributes_skus where products_id = '".$this->products[$i]['id']."' and products_attributes_ids = '".$products_attributes_ids."'");
$this->products_ordered_attributes = "\n\tSKU: ".$get_sku->fields['sku']. $this->products_ordered_attributes; 

This adds the sku information to the product information in the shopping cart and to be used in places such as invoices.

In includes/templates/default/tpl_shopping_cart_default.php

I modified the cart display to include the sku information:

<div class="shopping_cart_product_attributes">
    <?php                        
    echo $product['buttonUpdate'];
    echo $product['attributeHiddenField'];
    if (isset($product['attributes']) && is_array($product['attributes'])) {
        echo '<div class="cartAttribsList">';
        echo '<ul>';
        reset($product['attributes']);
        $pas = array();
        $list = '';
        foreach ($product['attributes'] as $option => $value) {
            $pas[] = $value['products_attributes_id'];
            $list .= '<li>'.$value['products_options_name'] . TEXT_OPTION_DIVIDER . nl2br($value['products_options_values_name']).'</li>';
        }
        sort($pas);
        $products_attributes_ids = '';
        $id_parts = explode(':', $product['id']);
        for ($i=0; $i<count($pas); ++$i) {
            $products_attributes_ids .= $pas[$i].'_';
        }
        $products_attributes_ids = trim($products_attributes_ids, "_");
        $get_sku = $db->Execute("select sku from products_attributes_skus where products_id = '".$id_parts[0]."' and products_attributes_ids = '".$products_attributes_ids."'");
        $list = '<li>SKU: '.$get_sku->fields['sku'].'</li>'.$list;
        echo $list;
        echo '</ul>';
        echo '</div>';
    }
    ?>
</div>

Im sure your's will be different but thats the idea on how to get it to display.

I basically did the same things to includes/templates/default/tpl_checkout_confirmation.php

<td class="cartProductDisplay"><?php echo $order->products[$i]['name']; ?>
    <?php // if there are attributes, loop thru them and display one per line
    if (isset($order->products[$i]['attributes']) && sizeof($order->products[$i]['attributes']) > 0 ) {
        echo '<ul class="cartAttribsList">';
        $pas = array();
        $list = '';
        for ($j=0, $n2=sizeof($order->products[$i]['attributes']); $j<$n2; $j++) {
            $list .= '<li>'.$order->products[$i]['attributes'][$j]['option'].': '.nl2br($order->products[$i]['attributes'][$j]['value']).'</li>';
            $pas[] = $order->products[$i]['attributes'][$j]['products_attributes_id'];
        }
        sort($pas);
        $products_attributes_ids = '';
        for ($x=0; $x<count($pas); ++$x) {
            $products_attributes_ids .= $pas[$x].'_';
        }
        $products_attributes_ids = trim($products_attributes_ids, "_");
        $get_sku = $db->Execute("select sku from products_attributes_skus where products_id = '".$order->products[$i]['id']."' and products_attributes_ids = '".$products_attributes_ids."'");
        $list = '<li>SKU: '.$get_sku->fields['sku'].'</li>'.$list;
        echo $list;
        echo '</ul>';
    } // endif attribute-info
    ?>
</td>

In /includes/modules/pages/shopping_cart/header_php.php I added products_attributes_id to the $attributes query on line 80 (in my file)
with

,  pa.products_attributes_id 

then make sure to add it to the attrArray with

$attrArray[$option]['products_attributes_id'] = $attributes_values->fields['products_attributes_id']; 

in the correct location.

I made some changes to a few other files but I could not remember or see what they were, so I included the entire files for you to compare.

included in the zip:
admin/orders.php
admin/packingslip.php
admin/invoice.php
admin/includes/classes/order.php


There may be some other changes to be made, but thats what I have written down in my notes from 8 months ago (can't believe I still had them).

Also a disclaimer:
**WARNING**
Use/make these changes at your own risk. I do not accept responsibility for breaking your shopping cart. As always do all work on a backup/development copy of your site and never the real deal.

I also only use select boxes for my sites attributes so some other stuff may need to be modified but I'm not sure.

Good luck and I'll try to help out as I can but I won't do anyone's work. This is what I would consider an advanced mod and should not be done by anyone without some php skills or knowledge how to troubleshoot.

[AaronN]

Thanks I'll give it a go! I'll post back with any additional stuff I had to use to get it working for us.
-Aaron

[AaronN]

I got it displaying the "option sku" but that appears to be it, the stock level to the "option sku" isn't getting updated. Do you have that functionality on your site? I tried a couple different things like changing the product id variable if the sku is in products_attributes_skus but that didn't work out for me.

Basically I'd like it if one of the options was choses like "Large" the item that is added to the cart would be model: ITEM_LARGE instead of having an item listed for every model, and the stock level for ITEM_LARGE would be updated instead of the stock for ITEM_SMALL.

Does that make sense?

I'm open to suggestions, in the mean time I'll keep hacking at it.
TIA
-Aaron

[ryanf]

Does that mean it gets into the cart and you can see it on the invoice correctly? We don't keep track of inventory. I would assume that you would need to do more. I would add another field to the new table called inventory and keep track of it on a individual basis. Then I would update the admin/attribute_controller.php page to also include a inventory level. Then modify the update cart functions to take away from the specific attribute combo.

[rued]

Hi ryanf,
this is excellent - thanks.

But could you also zip up all the changed files mentioned here, and post them?
It would be a time saver for us/me I think, those already posted was excellent to figure out with a quick diff. :)

If you could be so kind to do that I can post an up to date and ready to go pack after that, where I also will add compatibility for use of database table prefix.

[ryanf]

I don't think I can do that, I have some other modifications in those files. Hopefully someone can get it working from a fresh install and would be able to do that. If I have time, I might try.

[rued]

OK, sorry to hear that because some parts of the instructions was a little bit unclear and requires some work to figure out where those lines are.

But hey, we has to keep on trying to figure it out then. :)

[ryanf]

which part were you stuck on? maybe I can clarify it for you.

[rued]

Basically most of it is unclear, also in the original files is not easy to see where t.ex. a loop or if statement is ended - because the '{' is often on the end of the starting line.

More specific instructions about what's on the line above and/or below the new insert would be very helpful., but I'm working on it and hopefully will figure it out soon.

Those who seems to be hardest to figure out is in:
- admin/attribute_controller.php
- includes/classes/order.php