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

[mc12345678]

Thank you, u already know you are a coding genius, LOL
one more thing, when products are not in stock a banner in reds tells the customer this, but banner doesnt show with attributes out of stock

Regarding this "message", for some reason I'm not seeing where/what generates the one for the base product... seems like for me to recreate it in one of my test sites that I may need to know what the settings for the site are regarding stock control... I can recall that there were at one time some different "messages" made available on the shopping cart page, but then in later versions some things changed... I still have some older carts setup and could recreate the message for sba product if I could find out what created it for the base product.

[jimmie]

this is the define file /var/www/clients/client1/web4/web/includes/languages/english/shopping_cart.php

Line #43 : define('OUT_OF_STOCK_CAN_CHECKOUT', 'Products marked with ' . STOCK_MARK_PRODUCT_OUT_OF_STOCK . ' Can be DropShipped.<br />Items in your Cart that are Special Ordered can be DropShipped from are Distributor to you.(Guaranteed Delivery 1-7 Business Days)<br />(Please Note, if you choose Our Standard Shipping Methods and your cart contains Stock and Non-Stock items we will wait until all Items become Available. Usually within 2-14 Days)<br />If there is an exception with your Order you are Guaranteed Notification Within (1) Business Day.');

[jimmie]

I just was able to add a "special order" product to the cart and was provided a notification that there was special ordering required (when viewing shipping options from the shopping cart page), I added an instock product and had the same options overall, then removed the special order product and was provided a different series of shipping options. Don't recall which "special order" product I selected, but from my perspective and limited knowledge of the site, it seemed like *something* was handled correctly.

i have to two shipping modules activated standard shipping and vendor shipping, you should not see same method, stock vs nonstock. stock 1-2 days shipping, nonstock 7 - 14 days, i put a #1 in title of vendor shipping when all stock in cart, and a # 2 in nonstock when all nonstock in cart, #2 nevers shows, also when looking at sono product, hit listing goto sono tile, has a Red light (NonStock item), i didnt add attributes to that yet

[cubmanky]

So I removed everything and installed it all back for a clean slate.
Anytime I add a quantity on the SBA page I get a msg at the top I get "Update failed" but looks like it took it.

I added two attributes, one to pick type of vinyl and the second to pick color.
I feel it is working backwards now. If I list a product in the zc_products_with_attributes_stock_attributes_non_stock` it shows that it is in stock. The items that are out of stock no longer show in the drop down as out of stock.

[mc12345678]

So I removed everything and installed it all back for a clean slate.
Anytime I add a quantity on the SBA page I get a msg at the top I get "Update failed" but looks like it took it.

I added two attributes, one to pick type of vinyl and the second to pick color.
I feel it is working backwards now. If I list a product in the zc_products_with_attributes_stock_attributes_non_stock` it shows that it is in stock. The items that are out of stock no longer show in the drop down as out of stock.

In the update failed message there should be some information related to what failed or how it failed. It is likely something simple, but there isn't a "update kind of completed" setting. :)

As for the display being different, that's back to individual settings of what should be displayed related to stock items. Not everyone wants to have their "out-of-stock" items displayed.

Ohhh. Wait, the update failed message on modifying product, yes, there were some changes made to processing which is causing that message to appear. I've been working on the background code to provide the proper display. No its not as simple as just "always" forcing the message. It currently is likely to identify it failed when it actually succeeded. I haven't seen it identify success when it actually failed.

In otherwords it's more likely a false failure.

[cubmanky]

My biggest question now is why is the non stock working backwards. Items I place in non stock is only showing as the only items that are in stock

[mc12345678]

My biggest question now is why is the non stock working backwards. Items I place in non stock is only showing as the only items that are in stock

Two things. Since you are using two attributes for one product, now also need to consider the setting for dynamic dropdowns (separate configuration menu). The other thing regarding display of quantities depends on how the variants are defined. If you are using two attributes on a product then either the two attributes need to be defined as a pair and an individual quantity assigned to that pair or each attribute individually with quantity assigned to the individual attributes. The availability will be determined by the lowest number of the combination attempting to identify a combination by mixing the attributes together first and if none found then by individual attribute.

As to the non-stock items, that table and it's usage is to allow items to be considered against a product that is tracked by stock where those items may not necessarily be a stock related item. Ie. A gift bag, to include a receipt or not, to be contacted about the product in the future, to put a cherry on top, etc... it's different than how it is desired to work here, but that's no matter, it is intended to follow the same thought: allow the purchase of the product with this/these attributes selected even though there is not a specific stock quantity assigned.

[gohealth]

Hi all. I have an issue with the SBA add-on after upgrade to MySQL 5.7.24.

PHP Fatal error: 1140:In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'freedomh_zencart.pwas.quantity'; this is incompatible with sql_mode=only_full_group_by :: select distinct pwas.quantity as quantity, count(distinct pwasans.attribute_type) as pwasans_quantity from products_with_attributes_stock pwas, products_with_attributes_stock_attributes_non_stock pwasans

This SELECT is in file /public_html/includes/classes/pad_sba_sequenced_dropdowns.php. It is quite a complicated looking SQL statement (for me anyway) with a number of OR statements. I'm assuming I need to add a GROUP BY statement in this SQL Statement, but not sure where. The other option would be to update SQL_MODE and remove the ONLY_FULL_GROUP_BY setting, but doesn't look like the correct thing to do here.

Any ideas how to correct or make this SELECT statement work with MySQL 5.7.24?

I'm running Zen Cart 1.5.5f, Database Patch Level: 1.5.5. Everything else seems find except when you hit the product Detail Screen and it hits this code.


Thanks for any pointers.

Chris

SQL as follows:

From file /public_html/includes/classes/pad_sba_sequenced_dropdowns.php

        $attribute_stock_query = "select distinct pwas.quantity as quantity, count(distinct pwasans.attribute_type) as pwasans_quantity from " . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " pwas, " . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK_ATTRIBUTES_NON_STOCK . " pwasans 
            where pwasans.attribute_type = :products_options: 
              and pwasans.attribute_type_source_id = :products_id: 
              AND pwasans.attribute_type_id = (
                SELECT pa.options_id FROM " . TABLE_PRODUCTS_ATTRIBUTES . " pa 
                   WHERE pa.products_id = :products_id: 
                     AND pa.options_values_id = :options_values_id:) 
              OR (
                  pwasans.attribute_type = :products_values: 
                  AND pwasans.attribute_type_source_id = :products_id:
                    AND pwasans.attribute_type_id = :options_values_id:
                    " . /*(SELECT pa.options_values_id FROM " . TABLE_PRODUCTS_ATTRIBUTES . " pa 
                      WHERE pa.products_attributes_id = :check_attribute_id:) */ "
                )
              OR (
                  pwasans.attribute_type = :values: 
                  AND pwasans.attribute_type_source_id = 0
                    AND pwasans.attribute_type_id = :options_values_id:
                    AND pwasans.attribute_type_id = (SELECT pa.options_values_id FROM " . TABLE_PRODUCTS_ATTRIBUTES . " pa
                      WHERE pa.products_id = :products_id:
                          AND pa.options_values_id = :options_values_id:)
                    " . /*(SELECT pa.options_values_id FROM " . TABLE_PRODUCTS_ATTRIBUTES . " pa 
                      WHERE pa.products_attributes_id = :check_attribute_id:) */ "
                )
              OR (
                  pwasans.attribute_type = :options: 
                  AND pwasans.attribute_type_source_id = 0
                    AND pwasans.attribute_type_id = :options_values_id:
                    AND pwasans.attribute_type_id = (SELECT pa.options_id FROM " . TABLE_PRODUCTS_ATTRIBUTES . " pa
                      WHERE pa.products_id = :products_id:
                          AND pa.options_values_id = :options_values_id:)
                    " . /*(SELECT pa.options_values_id FROM " . TABLE_PRODUCTS_ATTRIBUTES . " pa 
                      WHERE pa.products_attributes_id = :check_attribute_id:) */ "
                )
              
              
              OR pwas.products_id = :products_id: 
              " . (STOCK_ALLOW_CHECKOUT !== 'false' 
                ? "" 
                : "AND pwas.quantity > 0 ") . "
                AND pwas.stock_attributes 
                   like (SELECT products_attributes_id from " . TABLE_PRODUCTS_ATTRIBUTES . " 
                     WHERE products_id = :products_id: 
                       and options_values_id = :options_values_id:) 
              OR pwas.products_id = :products_id: AND pwas.stock_attributes 
                like CONCAT((SELECT products_attributes_id from " . TABLE_PRODUCTS_ATTRIBUTES . " 
                   WHERE products_id = :products_id: 
                     and options_values_id = :options_values_id:),',%') 
              " . (STOCK_ALLOW_CHECKOUT !== 'false' 
                ? "" 
                : "AND pwas.quantity > 0 ") . "
              or pwas.products_id = :products_id: 
                AND pwas.stock_attributes 
                like CONCAT('%,',(SELECT products_attributes_id from " . TABLE_PRODUCTS_ATTRIBUTES . " 
                  WHERE products_id = :products_id: 
                    and options_values_id = :options_values_id:),',%') 
              " . (STOCK_ALLOW_CHECKOUT !== 'false' 
                ? "" 
                : "AND pwas.quantity > 0 ") . "
              or pwas.products_id = :products_id: 
                AND pwas.stock_attributes 
                like CONCAT('%,',(SELECT products_attributes_id from " . TABLE_PRODUCTS_ATTRIBUTES . " 
                  WHERE products_id = :products_id: 
                    and options_values_id = :options_values_id:))
              " . (STOCK_ALLOW_CHECKOUT !== 'false' 
                ? "" 
                : "AND pwas.quantity > 0 "); 

[mc12345678]

Hi all. I have an issue with the SBA add-on after upgrade to MySQL 5.7.24.

PHP Fatal error: 1140:In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'freedomh_zencart.pwas.quantity'; this is incompatible with sql_mode=only_full_group_by :: select distinct pwas.quantity as quantity, count(distinct pwasans.attribute_type) as pwasans_quantity from products_with_attributes_stock pwas, products_with_attributes_stock_attributes_non_stock pwasans

This SELECT is in file /public_html/includes/classes/pad_sba_sequenced_dropdowns.php. It is quite a complicated looking SQL statement (for me anyway) with a number of OR statements. I'm assuming I need to add a GROUP BY statement in this SQL Statement, but not sure where. The other option would be to update SQL_MODE and remove the ONLY_FULL_GROUP_BY setting, but doesn't look like the correct thing to do here.

Any ideas how to correct or make this SELECT statement work with MySQL 5.7.24?

I'm running Zen Cart 1.5.5f, Database Patch Level: 1.5.5. Everything else seems find except when you hit the product Detail Screen and it hits this code.


Thanks for any pointers.

Chris

SQL as follows:

From file /public_html/includes/classes/pad_sba_sequenced_dropdowns.php

        $attribute_stock_query = "select distinct pwas.quantity as quantity, count(distinct pwasans.attribute_type) as pwasans_quantity from " . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " pwas, " . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK_ATTRIBUTES_NON_STOCK . " pwasans 
            where pwasans.attribute_type = :products_options: 
              and pwasans.attribute_type_source_id = :products_id: 
              AND pwasans.attribute_type_id = (
                SELECT pa.options_id FROM " . TABLE_PRODUCTS_ATTRIBUTES . " pa 
                   WHERE pa.products_id = :products_id: 
                     AND pa.options_values_id = :options_values_id:) 
              OR (
                  pwasans.attribute_type = :products_values: 
                  AND pwasans.attribute_type_source_id = :products_id:
                    AND pwasans.attribute_type_id = :options_values_id:
                    " . /*(SELECT pa.options_values_id FROM " . TABLE_PRODUCTS_ATTRIBUTES . " pa 
                      WHERE pa.products_attributes_id = :check_attribute_id:) */ "
                )
              OR (
                  pwasans.attribute_type = :values: 
                  AND pwasans.attribute_type_source_id = 0
                    AND pwasans.attribute_type_id = :options_values_id:
                    AND pwasans.attribute_type_id = (SELECT pa.options_values_id FROM " . TABLE_PRODUCTS_ATTRIBUTES . " pa
                      WHERE pa.products_id = :products_id:
                          AND pa.options_values_id = :options_values_id:)
                    " . /*(SELECT pa.options_values_id FROM " . TABLE_PRODUCTS_ATTRIBUTES . " pa 
                      WHERE pa.products_attributes_id = :check_attribute_id:) */ "
                )
              OR (
                  pwasans.attribute_type = :options: 
                  AND pwasans.attribute_type_source_id = 0
                    AND pwasans.attribute_type_id = :options_values_id:
                    AND pwasans.attribute_type_id = (SELECT pa.options_id FROM " . TABLE_PRODUCTS_ATTRIBUTES . " pa
                      WHERE pa.products_id = :products_id:
                          AND pa.options_values_id = :options_values_id:)
                    " . /*(SELECT pa.options_values_id FROM " . TABLE_PRODUCTS_ATTRIBUTES . " pa 
                      WHERE pa.products_attributes_id = :check_attribute_id:) */ "
                )
              
              
              OR pwas.products_id = :products_id: 
              " . (STOCK_ALLOW_CHECKOUT !== 'false' 
                ? "" 
                : "AND pwas.quantity > 0 ") . "
                AND pwas.stock_attributes 
                   like (SELECT products_attributes_id from " . TABLE_PRODUCTS_ATTRIBUTES . " 
                     WHERE products_id = :products_id: 
                       and options_values_id = :options_values_id:) 
              OR pwas.products_id = :products_id: AND pwas.stock_attributes 
                like CONCAT((SELECT products_attributes_id from " . TABLE_PRODUCTS_ATTRIBUTES . " 
                   WHERE products_id = :products_id: 
                     and options_values_id = :options_values_id:),',%') 
              " . (STOCK_ALLOW_CHECKOUT !== 'false' 
                ? "" 
                : "AND pwas.quantity > 0 ") . "
              or pwas.products_id = :products_id: 
                AND pwas.stock_attributes 
                like CONCAT('%,',(SELECT products_attributes_id from " . TABLE_PRODUCTS_ATTRIBUTES . " 
                  WHERE products_id = :products_id: 
                    and options_values_id = :options_values_id:),',%') 
              " . (STOCK_ALLOW_CHECKOUT !== 'false' 
                ? "" 
                : "AND pwas.quantity > 0 ") . "
              or pwas.products_id = :products_id: 
                AND pwas.stock_attributes 
                like CONCAT('%,',(SELECT products_attributes_id from " . TABLE_PRODUCTS_ATTRIBUTES . " 
                  WHERE products_id = :products_id: 
                    and options_values_id = :options_values_id:))
              " . (STOCK_ALLOW_CHECKOUT !== 'false' 
                ? "" 
                : "AND pwas.quantity > 0 "); 

While I don't have mysql 5.7 currently installed to test and haven't yet forced the same requirement as what set off this error, my initial test under 5.6.xx worked by changing line 267 from:

                : "AND pwas.quantity > 0 ");

To:

                : "AND pwas.quantity > 0 ") . "
                GROUP BY pwas.quantity";

I'll see about changing the server's settings, but wanted to report what looked like would work.

[mc12345678]

While I don't have mysql 5.7 currently installed to test and haven't yet forced the same requirement as what set off this error, my initial test under 5.6.xx worked by changing line 267 from:

                : "AND pwas.quantity > 0 ");

To:

                : "AND pwas.quantity > 0 ") . "
                GROUP BY pwas.quantity";

I'll see about changing the server's settings, but wanted to report what looked like would work.

I can confirm that setting the db mode to ONLY_FULL_GROUP_BY and incorporating the above modification fixes that issue. Please let me know if other such issues arise so that they may be addressed.

As to the complexity of the query, well, some of that is because of how the SBA table has more information in a field than it should for trying to have a normalized database. There is a plan to split the data out a little more but is a bit of an overhaul of the underlying system.