Enable/Disable products without prices

EnableDisable.sql

BEGIN TRAN

-- Activate variants
UPDATE uCommerce_Product SET DisplayOnSite = 1, AllowOrdering = 1 where ParentProductId IS NOT NULL

-- Insert the price group prices
INSERT into uCommerce_PriceGroupPrice (ProductId, Price, PriceGroupId)
SELECT p.ProductId, NULL, pg.PriceGroupId
FROM uCommerce_Product p  LEFT JOIN uCommerce_PriceGroupPrice pgp ON p.ProductId = pgp.ProductId, uCommerce_PriceGroup pg
WHERE pgp.ProductId IS NULL AND pg.Deleted = 0
ORDER BY pg.PriceGroupId, p.ProductId;

--Set the price for the parent where the variant has a price
UPDATE u SET Price = 0 FROM uCommerce_PriceGroupPrice u LEFT JOIN uCommerce_Product up ON u.ProductId = up.ProductId WHERE up.ParentProductId IS NULL AND u.ProductId NOT IN (SELECT pgp.ProductId FROM uCommerce_PriceGroupPrice pgp LEFT JOIN uCommerce_Product p ON pgp.ProductId = p.ProductId WHERE pgp.Price > 0 AND p.ParentProductId = u.ProductId) 
UPDATE u SET Price = (SELECT MIN(pgp.Price) FROM uCommerce_PriceGroupPrice pgp LEFT JOIN uCommerce_Product p ON pgp.ProductId = p.ProductId WHERE pgp.Price > 0 AND p.ParentProductId = u.ProductId) FROM uCommerce_PriceGroupPrice u LEFT JOIN uCommerce_Product up ON u.ProductId = up.ProductId WHERE up.ParentProductId IS NULL

UPDATE p SET DisplayOnSite = 0, AllowOrdering = 0 FROM uCommerce_Product p LEFT JOIN uCommerce_PriceGroupPrice pgp ON p.ProductId = pgp.ProductId WHERE p.ParentProductId IS NULL AND (pgp.Price IS NULL OR pgp.Price <= 0)
UPDATE p SET DisplayOnSite = 1, AllowOrdering = 1 FROM uCommerce_Product p LEFT JOIN uCommerce_PriceGroupPrice pgp ON p.ProductId = pgp.ProductId WHERE p.ParentProductId IS NULL AND pgp.Price > 0

ROLLBACK TRAN