SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects WHERE id = object_id(N'[Testing].[MxOrderGuide_TestTeardown]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Testing].[MxOrderGuide_TestTeardown]
GO

CREATE PROCEDURE [Testing].[MxOrderGuide_TestTeardown]
AS
BEGIN

	-- Remove all Test Vendor Items and Entity Items
	DECLARE @ItemSearch VARCHAR(30) = 'Item Test-%'
	
	DELETE b FROM tbEntityItemDailyStockBalance b INNER JOIN tbItem i ON i.ItemId = b.ItemId WHERE i.[Description] like @ItemSearch
	
	DELETE ei FROM tbEntityItem ei
		INNER JOIN tbItem i ON i.ItemId = ei.ItemId
		WHERE i.[Description] like @ItemSearch
		
	DELETE vi FROM tbVendorItem vi
		INNER JOIN tbItem i ON i.ItemId = vi.ItemId
		WHERE i.[Description] like @ItemSearch

	-- Remove all Test Entity Zones
	DECLARE @ZoneSearch VARCHAR(30) = '% - Rules Test'
	
	DELETE ez FROM tbEntityZone ez 
		INNER JOIN tbZone z ON z.ZoneId = ez.ZoneId
		WHERE z.Zone like @ZoneSearch
	
	-- Remove all Test Rules
	DELETE r FROM tbVendorItemZoneRule r
		INNER JOIN tbItem i ON i.ItemCode = r.VendorCode
		WHERE i.[Description] like @ItemSearch 
		
	DELETE r FROM tbVendorItemZoneRule r
		INNER JOIN tbZone z ON z.ZoneId = r.ZoneId
		WHERE z.Zone like @ZoneSearch 
		
	DELETE r FROM tbVendorItemEntityRule r
		INNER JOIN tbItem i ON i.ItemCode = r.VendorCode
		WHERE i.[Description] like @ItemSearch
	
	-- Remove Zone mapping and zones
	DELETE vz FROM tbVendorZone vz
		INNER JOIN tbZone z ON vz.ZoneId = z.ZoneId
		WHERE z.Zone like @ZoneSearch
	
	DELETE FROM tbZone where Zone like @ZoneSearch
		
	-- Remove all test Items
	DELETE vei FROM tbVendorEntityItem vei INNER JOIN tbItem i ON i.ItemId = vei.ItemId WHERE i.[Description] like @ItemSearch
	
	DELETE FROM tbItem WHERE [Description] like @ItemSearch

END
GO







