Stock control issues

I have some odd things happening in 4.40.3 that I hope others might help shed light on. A customer managed to order an item that the database shows it out of stock and that also appears to be unpublished. Let my elaborate.

First things first, I had better explain what we are doing outside of nopCommerce that might have influence on this particular issue.

All orders whether from the Bricks and Mortar premises or taken online and processed in the premises through a sales and stock control system. Every 10 minutes an automated script is run to generate a CSV with all stock and current price. This is uploaded via FTP to the server where another script runs every 10 minutes to update nopCommerce tables [Products] (were an item has no attributes)  and [ProductAttributeCombination] where that applies.

Both aforementioned scripts are running and no errors have been logged. Cross checking random items from CSV with database just after the update confirms the data has correctly updated. However, in nopCommerce Admin, an item was still showing as having stock, even though the CSV and SQL stated otherwise. Forcing a ‘Clear Cache’ remedied that.

An item with setting in ‘Admin>Product>Inventory>Low stock activity’ set to Unpublish appears to have unpublished items where some Product Attribute Combination remain, surely this should not trigger until all of the Products Product Attribute Combinations reach zero?

The reason all this came to light is because a customer bought an item last night where the particular size sold out 6 days ago, yet when staff went to check online this morning the item had been unpublished even though other sizes remained. The Admin indicated the size bought still had remaining stock until I forced that cache flush.

What I’m trying to work out is how the Admin inventory still showed stock that was sold out 6 days ago, why an item with remaining attributes was unpublished (presumably after the customer bought the item last night), and whether my behind the scenes script might adversely affect the site cache allowing items sold out in store to still appear active online. Any suggestions will be most gratefully received.

I think you will have to look at the code to see how "Low stock activity’ set to Unpublish" looks at "some Product Attribute Combination remain".

Using external scripts to update the database could be a problem due to the caching.  Are you running the "Clear cache" scheduled task (periodically)?  That might be a good thing to do.

I've been away this past week but just ran a query to identify all products with stock that were not published. There were 127 of them.
I have now run an update to set publish=true where the sum of quantity in the related attributes exceeds 0 to ensure these are republished.
I'm still not sure what is causing them to become unpublished but will regularly check to see when this next occurs and hopefully that will shed light.

I have an update.

Since resetting all products a sale has gone through and this triggered an item to unpublish.

The product in question sold in a size large though 2 medium's remain. Clearly that wasn't what I expected. I would have thought the item would remain published until all stock was sold.

In Inventory I have min stock at 0 and selected Unpublish on Low Stock,  Allow only existing attribute combinations is checked. Not sure what else is relevant but I hope this is enough for someone to suggest what might be wrong.

It has happened again. This time on selling the last medium it unpublished all despite the product still having availability in S, L, XL and 2XL. This product has the same settings as stated before.
It appears that whenever the last in a size is sold that action is taken to unpublish whereas it should be running a query more akin to the query I run to identify item that have been wrongly stitched off:

SELECT        SUM(dbo.ProductAttributeCombination.StockQuantity) AS Qty, dbo.Product.Id, dbo.Product.Name, dbo.Product.Sku
FROM            dbo.Product INNER JOIN
                         dbo.ProductAttributeCombination ON dbo.Product.Id = dbo.ProductAttributeCombination.ProductId
WHERE        (dbo.ProductAttributeCombination.StockQuantity > 0) AND (dbo.Product.Published = 0) AND (dbo.Product.Deleted = 0)
GROUP BY dbo.Product.Id, dbo.Product.Name, dbo.Product.Sku

and only unpublishing where Qty is below the minimum value set. In this case stock should run down to zero.