Investigate - Product Deleted disposition of SBA attributes

[potteryhouse]

Investigate: What happens to attributes of a deleted product.

[mc12345678]

Interested in expanding the background to this issue. Is it in order to restock items upon order deletion if that is an option to be performed as set in the admin, or is it to understand what currently happens related to SBA when this event occurs to then develop/verify the actions expected?

[potteryhouse]

I would say both, a user posted that they needed a way to delete attributes in the SBA page when the product is removed, Since I had not done this I decided to look at the process and maybe add behavior that will satisfy a users expectation. I have not yet tested any deletion scenario.

So the question is, What is the users expectations and scenarios to be addressed.

[mc12345678]

Well, ZC offers the opportunity to restock items upon deleting an order. One thing that has been observed in operation is that if a product becomes disabled as a result of going out of stock, it does not get re-enabled upon deletion. I believe part of that is because deleting an order is not the same as having the stock back in hand... Wouldn't necessarily make something available that is not in hand ready to sell (that is for physical objects anyways). Not so sure that with electronic products that there would be anything to "restock". But, yes would be best to understand the request/expectation. Ideally, at least the equivalent functionality as the existing ZC.

It also seemed that one of the questions was basically how does one delete a combination, which was not necessarily a stock issue, but a how to make changes to the information being maintained by this plugin.

[potteryhouse]

I have not yet look closely at this area, so you seem to have a better understanding and thus I think you should run with this and we can test what you propose. How or will this affect the "Notify" bug addressed in Bug #11 ?

[mc12345678]

Sorry, the above was incorrectly posted in this thread instead of the applicable bug #11 area. Will look to see how to move it or repost there. I know that from my current device/browser that I am unable to move or edit it.

[ZenTux]

Greetings,

I am also interested in resolving the issue where elements from deleted or discontinued products/variants are being left in the products_with_attributes_stock table. Considering that products/variants can be discontinued and deleted at anytime, it seems there needs to be some mechanism to sync the products_with_attributes_stock table to the currently active products/variants contained in Zen-Cart. I didn't see any provision in stock_by_attr_install.php (Optional SQL) to deal with this issue.

When I run the "Export Table Data" script, it is exporting data from old, discontinued/deleted products/variants that were previously in the products_with_attributes_stock table, which is a related issue.

The site I'm working on is not yet live, so I can perform any SBA testing that might be needed. Since no real Custom IDs or Stock Quantities have been added, I guess at this point, I can clear the products_with_attributes_stock table and re-run stock_by_attr_install.php to recreate the data, based on the current products in Zen Cart. Any reason why that would not work?

After that is done, hopefully, the Export/Import modules will work, but if not, I suppose I can construct a SQL query to build my unique Custom ID from the products_model concatenated with the products_options_values_name, then manually enter the Variant Quantities from the Admin panel.

I'm a programmer by trade and I do have experience with PHP and SQL. I do not yet have a handle on the scheme and relationships between all the tables in Zen-Cart, as I've just began looking at the code and trying to familiarize myself with the system. If there is anything I can do to assist, please let me know.

Thank you for any suggestions!

[potteryhouse]

Hi, I am currently traveling and will have to look at this later, as a note there is a very nice schema chart of the database at http://commons.wikimedia.org/wiki/File:ZenCart_DB_Schema.jpg

The original versions of the Optional SQLs were to get a new site started, I will have to look at variances of the SQL to satisfy this type of use. but, you can use the current version simply by deleting all rows not needing to be uploaded, the query will only update those records uploaded, leaving all other alone. I use access to edit the .csv file, and ensure I save it back to a csv type.

[ZenTux]

Thank you for the information. The schema chart will come in handy. I was able to get the SBA table updated properly with Custom IDs. I did have a problem during the SBA table import, due to several Custom IDs being duplicates (bad data).

Let me know if there is anything I can contribute to help solve the issue of old data being left in the SBA table.

[mc12345678]

Added functionality to admin/includes/functions/general.php zen_remove_product to delete data from the SBA table TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK when a product is deleted in the mc12345678 fork. It will delete all records associated with the product_id being deleted with no discrimination.

[ZenTux]

Well, it sounds like you already have the code written, mc12345678.

What is this mc12345678 fork you speak of? Is there another version of SBA I'm not aware of that's being developed? If so, which version(s) of Zen Cart does it support?

Thanks!

[mc12345678]

Oops, mispoke, mc12345678 branch not fork. I can't seem to pull it up from my current device, but if you go to the home page, in the upper left corner is a drop down that addresses branches and would typically be on master. If you change that to mc12345678 then you will see the changes that have been made off of a relatively recent version of the software. I've made some other changes as well, but those are mostly to standardize the code to typical ZenCart code.

I didn't want to merge the code into the master until it had been looked at a little, tested, etc... It is hard to undo such a merge.

[ZenTux]

Thanks great! Looks as though that line of code will delete the entire product from the SBA table whenever a product is deleted via admin, which is a needed feature.

Is there also an easy way to delete a single product variant from the SBA table when that product variant is deleted via the admin panel?

Thanks!

[mc12345678]

That is one thing to look at next. Need to identify all normally expected conditions that would cause a variant to be removed. (In admin there are a few places that attributes are dealt with.) The other thing to consider while doing that is if a switchable option should be considered for when quantities go to 0 (zero).

[mc12345678]

ZenTux (and others that have ideas), With regards to deletion of product variants related to changes in attribute characteristics, there are a few things to consider and tackle and perhaps gain understanding of the group's thought(s) on expected functionality/response.

There are two parts to an attribute, the option name and the option value(s). 1) Deletion of an option value for a product that has one attribute is perhaps the simplest to deal with as the table would only contain that one option value/name pairing for that product and thus if the product only is affected (option value removed) then that one variant gets removed. 2) Deletion of an option value from the admin at the option values screen is a little more "destructive", but again for such a single option name/value pairing to a product(s) is again relatively simple nd straight forward, one less variant for all product that has that one and only one attribute. (Side item to 2) I'm not so sure that SBA currently is tied into ZC enough to transfer/duplicate SBA stock from one product to a copied product. New question to investigate. 3) Next comes the system response for a product that has two or more attributes, typical example is say shoes: size and color. If a shoe no longer is offered in a particular color, then I guess all variants that contain that color are to be removed. But, this is still based on the curent principle that all attributes of a product are in some way "hard" attributes, ie. color is a characteristic of the object being sold, as is size... But if in the future the object has some additional attribute that is itself a product, then well, I guess I just discovered that again the individual variant would be removed that contains the dependent characteristic, but if an option value of the attribute as an individual product is deleted, if the tables were setup properly only that attribute would be affected not all of the individual variants... Again, perhaps just figured that one out as well. 4) The root deletion of a variant that happens to be associated with product that has multiple option names would be similar, just more at a macroscopic level.

Now, though the part that is probably more "boggling"... The manipulation of option names... (Mind you the deletion of the last remaining option value is nearly equivalent to the deletion of the associated option name. 1) Deletion of an option name from a product that has only one attribute, seems that it is somewhat straight forward, remove all variants of that product. Afterall, no attribute, no stock by attribute. The only issue there is that currently the quantity of a product is in a way dependent on it's attributes (sum of the quantity of available attributes), therefore, when the attribute is deleted/non-existent so would/shoule the quantity of the product. (Making it zero, which is not necessarily desirable.) 2) Deletion of an option name in the option names controller, well, again for product having only one attribute, every such product would be affected and again the stock quantity would be in jeopardy of becoming zero or as is currently could remain the same as was previously before the deletion. 3) Here's a doozy. Deletion of the option name for a product that has two or more attributes... Stock... What dependency on the quantity of a product does that one attribute have? And again similar scenario of considering that an attribute may itself in the future be considered a product and the associated quantity to remain behind for the product in question. 4) Similar as three just above, but deletion of an option name from the database as a whole. What dependency does the particular option name have on the total quantity of stock (individual quantity of the option name) and what stock should be left behind as a result of deleting the option name? 5) Not yet really considered, but I believe the option name controller in ZC offers the ability to remove all attributes from a product or from the database entirely. In an SBA environment, how should that be handled/addressed as there isn't exactly a first or last item removal on which to make the dependency of stock deletion/adjustment to the total quantity.

My current thought is to address individual option name deletion by leaving behind the stock quantity of the last option name deleted, but in the case of complete attribute removal (through a function such as delete all option names at once) to leave behind zero quantity.

I realize that all of the above is probably more than any one individual was considering when addressing this aspect, but for proper complete development this type of grand view is prudent rather than a onsey twosey type aspect. I still believe that the base data structure needs revision as indicated in issue #6, but overall if the central functioning can be accomplished the outlying data structure can be modified and sufficient helper functions developed to support such a radical revision.

Thoughts?

[mc12345678]

Have added some various handling code for item/attribute deletion. Have not integrated yet with the attribute controller, but looking for testing of option values manager and option names manager. Currently programmed to delete sba variants containing a value when a value is removed... The same thread also includes code for a new table to help with orders and deleting orders. Much cleaner than the build a prid process that was used previously. (ie., back on track with a database like method of working with the data rather than so much code.)

So as said, the entire variant is removed if a value is removed from the database via either the options values or options names manager, though could use some rigorous testing to validate. Code is in the mc12345678 branch.

[mc12345678]

In looking through the code to address deletion of attributes, the base ZC code does whole sale delete downstream attribute information when the "parent" attribute (name/value) is deleted. When looking at the attribute_controller, if a product's option name is removed, then all of it's options values that fall under that option name are removed. A similar situation for each option value, all downstream information for that option value for that product is removed.

So, hopefully (and am looking for others to test/verify or find a problem with the code) the changes that have been captured in the mc12345678 branch related to deleting a product, option values, option names, and orders can be reviewed/validated.

See pull request issue #13 as it contains all of the changed code made to address these issues and stays up-to-date until basically either a new branch is created or the branch is merged.

[mc12345678]

Have added deletion of all SBA variants for a product if the attributes of a product in the attributes controller is selected to have all attributes deleted. This is in addition to the previous actions incorporated to delete individual option values or option names. As far as stock control, the only thing that is removed in each of these cases currently is the stock specifically associated with the SBA variant. The total stock of products that is tracked (or not) independently is not modified with deletion of these items.

Code is/will be provided in the version that is being maintained to support multiple versions of ZC found at: https://github.com/mc12345678/Stock_By_Attributes_Combined

Closing this issue in effort to address open items and consolidate.