Stock by Attribute v4.0 addon for v1.3.5-1.3.9

[yellow1912]

Follow up:

This is the function I currently have to get product quantity:
Note: I used a function I have in my class to turn the result from db query into string, but if you want to make use of the function below you can easily use a loop to do so.

@Kuroi: can you have a look and tell me if there is anything wrong?
I used the option text name and value instead of the ids because I want to make sure that we either the stock of the exact product and attributes or nothing.

<?php
function retrieve_attribute_stock_from_order($attribute_array,$products_id){
    global $db;
    
    // Lets assume that if table TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK is defined, then we are using the stock by attribute mod
    // we can also query database to check if we want to
    if(defined('TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK')){
        $sql_query = 'SELECT products_attributes_id FROM '.TABLE_PRODUCTS_ATTRIBUTES.' pa,'.TABLE_PRODUCTS_OPTIONS_VALUES.' ov,'.TABLE_PRODUCTS_OPTIONS.' o
                WHERE ';
        $condition_array = array();
        if(count($attribute_array) == 0) return;
        foreach($attribute_array as $attribute){
            $condition_array[] = '(ov.products_options_values_name = \''.$attribute['value'].'\' AND o.products_options_name =\''.$attribute['option'].'\' AND pa.products_id = '.$products_id.' AND pa.options_id = o.products_options_id AND pa.options_values_id = ov.products_options_values_id)';
        }
        $sql_query .= implode(' OR ', $condition_array).' ORDER BY products_attributes_id';
    
        $result = $db->Execute($sql_query);
        $yobj = new yclass();
        $attribute_string = $yobj->db_result_to_string(',',$result);
        $get_quantity_query = 'SELECT quantity FROM ' . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . ' where products_id="' . $products_id . '" and stock_attributes="' . $attribute_string . '" LIMIT 1';

        $quantity = $db->Execute($get_quantity_query);
        if($quantity->RecordCount() == 1)
            return $quantity->fields['quantity'];
    }
    // get products_quantity directly from products table
    $get_quantity_query = 'SELECT products_quantity FROM ' . TABLE_PRODUCTS . ' where products_id="' . $products_id . '" LIMIT 1';    
    $quantity = $db->Execute($get_quantity_query);
    if($quantity->RecordCount() == 1)
        return $quantity->fields['products_quantity'];
    else
        return 0;
}
?>

[kuroi]

@Kuroi: can you have a look and tell me if there is anything wrong?
I used the option text name and value instead of the ids because I want to make sure that we either the stock of the exact product and attributes or nothing.

The code will generate the broadly the right SQL, but I can see one potentially show stopping problem - but it's easily trapped.

Suppose you had a two attribute product where the attributes are size and color. A customer orders (XXXL, Red). (XXXL, Red) is withdrawn - no longer exists. But you still have (XXXL, White) and (XXXL, Black).

Executing your function against (XXXL, Red) will still return the product_attribute_id for XXXL. Your second query will take that as a parameter and, if the attribute stock for (XXXL, Red) has been removed, will return an array of stock quantities for (XXXL, Black) and (XXXL, White), neither of which you want.

You currently have a check that the record count is one, so your response to this situation would be to not return a false attribute stock result (which is good) but instead to drop through and return the overall product stock instead, which I suspect is bad (and a reason why I don't personally like code that relies on returns to control the logic).

Better would be to test earlier that the number of products_attributes_id returned matches the number of attributes in the original array and if not, deal with the exception at that point.

[kuroi]

@yellow1912

if the attribute stock for (XXXL, Red) has been removed

should read

if the attribute for Red has been removed

[yellow1912]

Thanks Kuroi, that does make sense. I added a simple checking to make sure that we get the number of attribute id we expected, if not then just return 0.

<?php
function retrieve_attribute_stock_from_order($attribute_array,$products_id){
    global $db;
    
    // Lets assume that if table TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK is defined, then we are using the stock by attribute mod
    // we can also query database to check if we want to
    if(defined('TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK')){
        $sql_query = 'SELECT products_attributes_id FROM '.TABLE_PRODUCTS_ATTRIBUTES.' pa,'.TABLE_PRODUCTS_OPTIONS_VALUES.' ov,'.TABLE_PRODUCTS_OPTIONS.' o
                WHERE ';
        $condition_array = array();
        if(count($attribute_array) == 0) return;
        foreach($attribute_array as $attribute){
            $condition_array[] = '(ov.products_options_values_name = \''.$attribute['value'].'\' AND o.products_options_name =\''.$attribute['option'].'\' AND pa.products_id = '.$products_id.' AND pa.options_id = o.products_options_id AND pa.options_values_id = ov.products_options_values_id)';
        }
        $sql_query .= implode(' OR ', $condition_array).' ORDER BY products_attributes_id';
    
        $result = $db->Execute($sql_query);
        
        if($result->RecordCount() != count($attribute_array))
            return 0;
            
        $yobj = new yclass();
        $attribute_string = $yobj->db_result_to_string(',',$result);
        $get_quantity_query = 'SELECT quantity FROM ' . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . ' where products_id="' . $products_id . '" and stock_attributes="' . $attribute_string . '" LIMIT 1';

        $quantity = $db->Execute($get_quantity_query);
        if($quantity->RecordCount() == 1)
            return $quantity->fields['quantity'];
    }
    // get products_quantity directly from products table
    $get_quantity_query = 'SELECT products_quantity FROM ' . TABLE_PRODUCTS . ' where products_id="' . $products_id . '" LIMIT 1';    
    $quantity = $db->Execute($get_quantity_query);
    if($quantity->RecordCount() == 1)
        return $quantity->fields['products_quantity'];
    else
        return 0;
}
?>

[aly22]

Happy to find this module, but as a TOTAL NEWBIE I am confused by the installation instructions.

1. I see no folders to rename to my template??

2. Your wiki instructions say to drop the admin and includes directories into the root directory of your site. BUT the files extracted to a directory: CART. Where exactly do we upload and/or overwrite each directory for this plugin? (Will dropping the 'cart' directory distribute itself to where it needs to go on its own? -- I use WS_FTP Pro)

THANK YOU!!

[kuroi]

Happy to find this module, but as a TOTAL NEWBIE I am confused by the installation instructions.

Both your questions, and your need to go to the Wiki to find installation instructions, point to you having downloaded something other than this add-in. There are other similarly named modules and I suspect you must have one of those.

[aly22]

Both your questions, and your need to go to the Wiki to find installation instructions, point to you having downloaded something other than this add-in. There are other similarly named modules and I suspect you must have one of those.

Hmmm ... Thanks. That must be the problem. I downloaded "stockAttribute3.0.1.1"
Any chance there's a link to the RIGHT one from this thread?

Meanwhile, I'll go try to find the one I downloaded ...

EDIT: Here's the file I downloaded: stockattribute_by_danielcor_3.01.1.zip

[aly22]

NEVERMIND ... I've now downloaded YOUR Stock by Attributes and will see how that goes. (Any clue how to UNinstall the SQL Patch I installed for the wrong one?) Thanks

[aly22]

Yikes!!!!!!

1062 Duplicate entry 'STOCK_SHOW_LOW_IN_CART' for key 2
in:
[INSERT INTO zen_configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('Show available stock level in cart when less than order', 'STOCK_SHOW_LOW_IN_CART', 'false', 'When customer places more items in cart than are available, show the available amount on the shopping cart page:', '9', '6', NULL, now(), NULL, "zen_cfg_select_option(array('true', 'false')," );]

I really really messed up now DROPPED the table "products_with_attributes_stock" but now cannot re-create without the error above.

[kuroi]

Yikes!!!!!!

1062 Duplicate entry 'STOCK_SHOW_LOW_IN_CART' for key 2
in:
[INSERT INTO zen_configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('Show available stock level in cart when less than order', 'STOCK_SHOW_LOW_IN_CART', 'false', 'When customer places more items in cart than are available, show the available amount on the shopping cart page:', '9', '6', NULL, now(), NULL, "zen_cfg_select_option(array('true', 'false')," );]

I really really messed up now DROPPED the table "products_with_attributes_stock" but now cannot re-create without the error above.

Ignore this error. From way back in time, both these mods have a common ancestor and use the same flag, so its just telling you it can't add it 'cos it's already there.