Magento Custom - Migrate Gift Registry products from deleted duplicate products to the desired product IDs.

gistfile1.sql

DELIMITER //
DROP PROCEDURE IF EXISTS MigrateDeletedProduct// 
CREATE PROCEDURE MigrateDeletedProduct(
		IN source_product_id INT,
		IN dest_product_id INT)
	BEGIN
		UPDATE
			enterprise_giftregistry_item
		SET
			# Define the destination product ID:
			enterprise_giftregistry_item.product_id = dest_product_id
		WHERE
			# Define the source product ID:
			enterprise_giftregistry_item.product_id = source_product_id AND
			# Only do this for holidays that have not yet departed
			# enterprise_giftregistry_item.entity_id IN
			# 	(SELECT
			# 		enterprise_giftregistry_entity.entity_id
			# 	FROM 
			# 		enterprise_giftregistry_entity
			# 	WHERE
			# 		tui_holiday_departure_date > NOW()
			# 	) AND
			# Product has not yet been purchased (If the item is already paid for we are not removing it)
			# (enterprise_giftregistry_item.qty_fulfilled IS NULL OR
			# enterprise_giftregistry_item.qty_fulfilled < enterprise_giftregistry_item.qty) AND 
			# Product no longer exists because it was forcibly deleted
			enterprise_giftregistry_item.product_id NOT IN
				(SELECT
					entity_id
				FROM
					catalog_product_entity
				);
		# Also migrate over the product options to prevent other critical errors. 
		# This assumes that product options are identical for migration to occur without errors.
		UPDATE
			enterprise_giftregistry_item_option
		SET
			# Define the destination product ID:
			enterprise_giftregistry_item_option.product_id = dest_product_id
		WHERE
			# Define the source product ID:
			enterprise_giftregistry_item_option.product_id = source_product_id AND
			# Only do this for holidays that have not yet departed
			# enterprise_giftregistry_item.entity_id IN
			# 	(SELECT
			# 		enterprise_giftregistry_entity.entity_id
			# 	FROM 
			# 		enterprise_giftregistry_entity
			# 	WHERE
			# 		tui_holiday_departure_date > NOW()
			# 	) AND
			# Product has not yet been purchased (If the item is already paid for we are not removing it)
			# (enterprise_giftregistry_item_option.qty_fulfilled IS NULL OR
			# enterprise_giftregistry_item_option.qty_fulfilled < enterprise_giftregistry_item.qty) AND 
			# Product no longer exists because it was forcibly deleted
			enterprise_giftregistry_item_option.product_id NOT IN
				(SELECT
					entity_id
				FROM
					catalog_product_entity
				);
	END//
	
# Migrate our products from the old ID to the new ID (see spreadsheet for explainations)
CALL MigrateDeletedProduct(640, 615);//
CALL MigrateDeletedProduct(709, 2155);//
CALL MigrateDeletedProduct(712, 2152);//
CALL MigrateDeletedProduct(714, 2017);//
CALL MigrateDeletedProduct(739, 2122);//
CALL MigrateDeletedProduct(761, 2132);//
CALL MigrateDeletedProduct(762, 2132);//
CALL MigrateDeletedProduct(765, 2131);//
CALL MigrateDeletedProduct(769, 2132);//
CALL MigrateDeletedProduct(797, 2168);//
CALL MigrateDeletedProduct(927, 2089);//
CALL MigrateDeletedProduct(934, 1677);//
CALL MigrateDeletedProduct(980, 4989);//
CALL MigrateDeletedProduct(2154, 2512);//
CALL MigrateDeletedProduct(2211, 2210);//
CALL MigrateDeletedProduct(2212, 2213);//
CALL MigrateDeletedProduct(2214, 2215);//
CALL MigrateDeletedProduct(2217, 2216);//
CALL MigrateDeletedProduct(2219, 2218);//
CALL MigrateDeletedProduct(2220, 937);//
CALL MigrateDeletedProduct(2221, 937);//
CALL MigrateDeletedProduct(2222, 2223);//
CALL MigrateDeletedProduct(2230, 2231);//
CALL MigrateDeletedProduct(2232, 2231);//
CALL MigrateDeletedProduct(2253, 2252);//
CALL MigrateDeletedProduct(2254, 2255);//

# Products added per Rod
CALL MigrateDeletedProduct(841, 2250);//
CALL MigrateDeletedProduct(1201, 2249);//
CALL MigrateDeletedProduct(2017, 2414);//

# No longer need the stored proc:
DROP PROCEDURE IF EXISTS MigrateDeletedProduct// 

# Output complete results (should show any remaining product items that are still missing)
SELECT
	*
FROM
	enterprise_giftregistry_item,
	enterprise_giftregistry_entity,
	customer_entity
WHERE
	# Only do this for holidays that have not yet departed
	# enterprise_giftregistry_entity.tui_holiday_departure_date > NOW() AND
	# Product no longer exists because it was forcibly deleted
	enterprise_giftregistry_item.product_id NOT IN
		(SELECT entity_id FROM catalog_product_entity) AND
	# Product has not yet been purchased (If the item is already paid for we are not removing it)
	# (enterprise_giftregistry_item.qty_fulfilled IS NULL OR
	# enterprise_giftregistry_item.qty_fulfilled < enterprise_giftregistry_item.qty) AND 
	# Get the associated giftregistry entity
	enterprise_giftregistry_entity.entity_id = enterprise_giftregistry_item.entity_id AND
	# Get the associated user entity
	enterprise_giftregistry_entity.customer_id = customer_entity.entity_id;//

This has been modified to now modify the past. In other words, carts that have been half-provisioned, or unfinished carts, or even carts that were completely paid for will all now have the replacement products in place. We have to do this because we've found that customers will still get errors when they try to access the site with one of those edge-cases if the products are missing that are still tied in to an old registry, etc.

Typically we would never want to do this, since it alters history. But in this case, we are trying to repair the hole in the time-space continuum that the product eradication event created... So we must do what must be done.

Has now been modified to migrate over product item options as well... This should correct issues we are seeing with carts still causing critical errors. However, this assumes that the replacement products are made up of the same exact options. If the replacement products do not match up exactly there could be unintended consequences.