SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

CREATE PROCEDURE [Testing].[MxOrderGuide_MultipleVendorScenerios]
AS
BEGIN

	--Input Parameters
	DECLARE @EntityName  VARCHAR(30) = '0000 Test Store'
	DECLARE @VendorName1 VARCHAR(30) = '%MBM Corp%'
	DECLARE @VendorName2 VARCHAR(30) = '%QCD SVCS%'
	
	DECLARE @DCZoneName1 VARCHAR(30) = 'MBM - Rules Test'
	DECLARE @DCZoneName2 VARCHAR(30) = 'QCD - Rules Test'

	DECLARE @EntityId BIGINT = (select EntityId From tbEntity where Entity like @EntityName)
	DECLARE @VendorId1 BIGINT = (select VendorId From tbVendor where VendorName like @VendorName1)
	DECLARE @VendorId2 BIGINT = (select VendorId From tbVendor where VendorName like @VendorName2)

	-- Make sure Store and Vendors exist
	IF @EntityId IS NULL RAISERROR('Could not find Store.', 18, 1)
	IF @VendorId1 IS NULL RAISERROR('Could not find Vendor 1.', 18, 1)
	IF @VendorId2 IS NULL RAISERROR('Could not find Vendor 2.', 18, 1)

	EXEC Testing.MxOrderGuide_TestSetup @EntityId, @VendorId1, @VendorId2

	-- Get Zone Ids (DC Zones are created by stored procedure above)
	DECLARE @DCZoneTypeId BIGINT = (select ZoneTypeId from tbZoneType where ZoneType like 'Location - DistributionCenter')	
	DECLARE @DCZoneId1 BIGINT = (select zoneId from tbZone where zone like @DCZoneName1 and zoneTypeId = @DCZoneTypeId)
	DECLARE @DCZoneID2 BIGINT = (select zoneId from tbZone where zone like @DCZoneName2 and zoneTypeId = @DCZoneTypeId)

	-- Values that will be reused for each scenario
	DECLARE @DNZoneTypeId BIGINT = (select ZoneTypeId from tbZoneType where ZoneType like 'Location - DistributorName')	
	DECLARE @DNZoneId1 BIGINT = (select zoneId from tbZone where zone like @VendorName1 and zoneTypeId = @DNZoneTypeId)
	DECLARE @DNZoneId2 BIGINT = (select zoneId from tbZone where zone like @VendorName2 and zoneTypeId = @DNZoneTypeId)
	DECLARE @OPZoneTypeId BIGINT = (select zoneTypeId from tbZoneType where ZoneType like 'Location - OperatorTeamName')	
	DECLARE @OPZoneId BIGINT = (select zoneId from tbZone where zone like 'OpTeam - Rules%' and zoneTypeId = @OPZoneTypeId)
	DECLARE @UserName VARCHAR(50) = 'Janus Systemadmin'
	DECLARE @RuleCreatedDate DATETIME = GetDate()
	DECLARE @CountryZoneTypeId BIGINT = (select ZoneTypeId from tbZoneType where ZoneType like 'Location - CountryName')
	DECLARE @CountryZoneId BIGINT = (select ZoneId from tbZone where ZoneTypeId = @CountryZoneTypeId and Zone = 'United States')

	-- Values that reset for each test
	DECLARE @ItemCode VARCHAR(30) = '' 
	DECLARE @ItemName VARCHAR(30) = '' 
	DECLARE @DNIntroDate1 DATETIME = NULL
	DECLARE @DNIntroDate2 DATETIME = NULL
	DECLARE @DCIntroDate1 DATETIME = NULL
	DECLARE @DCDiscDate1 DATETIME = NULL
	DECLARE @DCIntroDate2 DATETIME = NULL
	DECLARE @OPIntroDate DATETIME = NULL
	DECLARE @CountryIntroDate DATETIME = NULL
	DECLARE @RestIntroDate DATETIME = NULL

/* SCENARIO MULTI VENDOR-1:  Create an item for one vendor but not another vendor at the same restaurant */
	SET @ItemCode = '1001-MV1'
	SET @ItemName = 'Item Test-MV1'

	EXEC Testing.MxOrderGuide_ItemCreation @ItemCode, @ItemName, @EntityId, @VendorId1, @VendorId2

	SET @DCIntroDate1 = DATEADD(Week, 1, GetDate())

	-- Create Distribution Center Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId1, @DCZoneTypeId, 'Include', @DCIntroDate1, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
	
/* SCENARIO MULTI VENDOR-2:  Create an item for two vendors with different introduction dates at the same restaurant */
	SET @ItemCode = '1001-MV2'
	SET @ItemName = 'Item Test-MV2'

	EXEC Testing.MxOrderGuide_ItemCreation @ItemCode, @ItemName, @EntityId, @VendorId1, @VendorId2

	SET @DCIntroDate1 = DATEADD(Week, 1, GetDate())
	SET @DCIntroDate2 = DATEADD(Week, 2, GetDate())

	-- Create Distribution Center Rule for Vendor 1
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId1, @DCZoneTypeId, 'Include', @DCIntroDate1, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
	
	-- Create Distribution Center Rule for Vendor 2
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId2, @DCZoneTypeId, 'Include', @DCIntroDate2, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)

/* SCENARIO MULTI VENDOR-3:  Create an item for two vendors with different introduction dates at the same restaurant */
	SET @ItemCode = '1001-MV3'
	SET @ItemName = 'Item Test-MV3'

	EXEC Testing.MxOrderGuide_ItemCreation @ItemCode, @ItemName, @EntityId, @VendorId1, @VendorId2

	SET @DCIntroDate1 = DATEADD(Week, 2, GetDate())
	SET @DCIntroDate2 = DATEADD(Week, 1, GetDate())
	SET @OPIntroDate = DATEADD(Week, 3, GetDate())

	-- Create Distribution Center Rule for Vendor 1
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId1, @DCZoneTypeId, 'Include', @DCIntroDate1, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
	
	-- Create Distribution Center Rule for Vendor 2
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId2, @DCZoneTypeId, 'Include', @DCIntroDate2, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)

	-- Create Operator Team Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @OPZoneId, @OPZoneTypeId, 'Exclude', @OPIntroDate, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)

/* SCENARIO MULTI VENDOR-4:  Create an item for two vendors with a Distributor and OpTeam zone rule, and discontinue one item with a DC zone rule */
	SET @ItemCode = '1001-MV4'
	SET @ItemName = 'Item Test-MV4'

	EXEC Testing.MxOrderGuide_ItemCreation @ItemCode, @ItemName, @EntityId, @VendorId1, @VendorId2

	SET @DNIntroDate1 = DATEADD(Week, 2, GetDate())
	SET @DCIntroDate1 = DATEADD(Week, 3, GetDate())
	SET @OPIntroDate = DATEADD(Week, 1, GetDate())

	-- Create Distributor Rule for Vendor 1
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DNZoneId1, @DNZoneTypeId, 'Include', @DNIntroDate1, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
	
	-- Create Distribution Center Rule for Vendor 1
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId1, @DCZoneTypeId, 'Exclude', @DCIntroDate1, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)

	-- Create Operator Team Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @OPZoneId, @OPZoneTypeId, 'Include', @OPIntroDate, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)

/* SCENARIO MULTI VENDOR-5a:  Create an item for two vendors with a single national zone rule */
	SET @ItemCode = '1001-MV5a'
	SET @ItemName = 'Item Test-MV5a'

	EXEC Testing.MxOrderGuide_ItemCreation @ItemCode, @ItemName, @EntityId, @VendorId1, @VendorId2

	SET @CountryIntroDate = DATEADD(Week, 1, GetDate())

	-- Create National Zone Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @CountryZoneId, @CountryZoneTypeId, 'Include', @CountryIntroDate, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)

/* SCENARIO MULTI VENDOR-5b:  Create an item for two vendors with a national zone rule, then discontinue one with a DC zone rule */
	SET @ItemCode = '1001-MV5b'
	SET @ItemName = 'Item Test-MV5b'

	EXEC Testing.MxOrderGuide_ItemCreation @ItemCode, @ItemName, @EntityId, @VendorId1, @VendorId2

	SET @CountryIntroDate = DATEADD(Week, 1, GetDate())
	SET @DCIntroDate1 = DATEADD(Week, 1, GetDate())

	-- Create National Zone Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @CountryZoneId, @CountryZoneTypeId, 'Include', @CountryIntroDate, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
		
	-- Create Distribution Center Rule for Vendor 1
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId1, @DCZoneTypeId, 'Exclude', @DCIntroDate1, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)

/* SCENARIO MULTI VENDOR-5c:  Create an item for two vendors with a national zone rule, then discontinue one with a DC zone rule */
	SET @ItemCode = '1001-MV5c'
	SET @ItemName = 'Item Test-MV5c'

	EXEC Testing.MxOrderGuide_ItemCreation @ItemCode, @ItemName, @EntityId, @VendorId1, @VendorId2

	SET @CountryIntroDate = DATEADD(Week, 1, GetDate())
	SET @DCIntroDate1 = DATEADD(Week, 1, GetDate())
	SET @RestIntroDate = DATEADD(Week, 1, GetDate())

	-- Create National Zone Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @CountryZoneId, @CountryZoneTypeId, 'Include', @CountryIntroDate, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
		
	-- Create Distribution Center Rule for Vendor 1
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId1, @DCZoneTypeId, 'Exclude', @DCIntroDate1, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
		
	-- Create Restaurant Rule
	INSERT INTO tbVendorItemEntityRule VALUES (
		@ItemCode, @EntityId, 'Include', @RestIntroDate, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
	

/* SCENARIO MULTI VENDOR-6:  Create two vendor items with Distributor zone rules, then discontinue one with a DC zone rule */
	SET @ItemCode = '1001-MV6'
	SET @ItemName = 'Item Test-MV6'

	EXEC Testing.MxOrderGuide_ItemCreation @ItemCode, @ItemName, @EntityId, @VendorId1, @VendorId2
	
	SET @DNIntroDate1 = DATEADD(Week, 1, GetDate())
	SET @DNIntroDate2 = DATEADD(Week, 2, GetDate())
	SET @DCIntroDate2 = DATEADD(Week, 3, GetDate())

	-- Create Distribution Center Rule for Vendor 1
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId1, @DCZoneTypeId, 'Include', @DCIntroDate1, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
		
	-- Create Distribution Center Rule for Vendor 2
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DNZoneId2, @DNZoneTypeId, 'Include', @DNIntroDate2, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
		
	-- Create Distribution Center Rule for Vendor 2
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId2, @DCZoneTypeId, 'Exclude', @DCIntroDate2, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)

	
/* SCENARIO MULTI VENDOR-7:  Create an item for two vendors with different introduction dates at the same restaurant */
	SET @ItemCode = '1001-MV7'
	SET @ItemName = 'Item Test-MV7'

	EXEC Testing.MxOrderGuide_ItemCreation @ItemCode, @ItemName, @EntityId, @VendorId1, @VendorId2

	SET @DCIntroDate1 = DATEADD(Week, 1, GetDate())
	SET @DCIntroDate2 = DATEADD(Week, 2, GetDate())
	SET @OPIntroDate = DATEADD(Week, 3, GetDate())
	
	-- Create Distribution Center Rule for Vendor 1
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId1, @DCZoneTypeId, 'Include', @DCIntroDate1, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
	
	-- Create Distribution Center Rule for Vendor 2
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId2, @DCZoneTypeId, 'Include', @DCIntroDate2, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)

	-- Create Operator Team Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @OPZoneId, @OPZoneTypeId, 'Include', @OPIntroDate, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)

/* SCENARIO MULTI VENDOR-8:  Create an item for two vendors with different introduction dates at the same restaurant */
	SET @ItemCode = '1001-MV8'
	SET @ItemName = 'Item Test-MV8'

	EXEC Testing.MxOrderGuide_ItemCreation @ItemCode, @ItemName, @EntityId, @VendorId1, @VendorId2

	SET @DCIntroDate1 = DATEADD(Week, 3, GetDate())
	SET @DCIntroDate2 = DATEADD(Week, 2, GetDate())
	SET @OPIntroDate = DATEADD(Week, 1, GetDate())
	
	-- Create Distribution Center Rule for Vendor 1
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId1, @DCZoneTypeId, 'Include', @DCIntroDate1, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
	
	-- Create Distribution Center Rule for Vendor 2
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId2, @DCZoneTypeId, 'Include', @DCIntroDate2, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)

	-- Create Operator Team Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @OPZoneId, @OPZoneTypeId, 'Include', @OPIntroDate, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)

/* SCENARIO MULTI VENDOR-9a:  Create an item for two vendors with an Operator Team zone rule */
	SET @ItemCode = '1001-MV9a'
	SET @ItemName = 'Item Test-MV9a'

	EXEC Testing.MxOrderGuide_ItemCreation @ItemCode, @ItemName, @EntityId, @VendorId1, @VendorId2

	SET @OPIntroDate = DATEADD(Week, 1, GetDate())
	
	-- Create Operator Team Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @OPZoneId, @OPZoneTypeId, 'Include', @OPIntroDate, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
	
/* SCENARIO MULTI VENDOR-9b:  Create an item for two vendors with an Operator Team zone rule */
	SET @ItemCode = '1001-MV9b'
	SET @ItemName = 'Item Test-MV9b'

	EXEC Testing.MxOrderGuide_ItemCreation @ItemCode, @ItemName, @EntityId, @VendorId1, @VendorId2

	SET @OPIntroDate = DATEADD(Week, 1, GetDate())
	SET @DCIntroDate2 = DATEADD(Week, 2, GetDate())
	
	-- Create Operator Team Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @OPZoneId, @OPZoneTypeId, 'Include', @OPIntroDate, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
		
	-- Create Distribution Center Rule for Vendor 2
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId2, @DCZoneTypeId, 'Include', @DCIntroDate2, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
	
	
/* SCENARIO MULTI VENDOR-9c:  Create an item for two vendors, one from a vendor specific zone and one from a vendor agnostic zone */
	SET @ItemCode = '1001-MV9c'
	SET @ItemName = 'Item Test-MV9c'

	EXEC Testing.MxOrderGuide_ItemCreation @ItemCode, @ItemName, @EntityId, @VendorId1, @VendorId2

	SET @DCIntroDate1 = DATEADD(Week, 3, GetDate())
	SET @DCIntroDate2 = DATEADD(Week, 2, GetDate())
	SET @OPIntroDate = DATEADD(Week, 1, GetDate())
	
	-- Create Distribution Center Rule for Vendor 1
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId1, @DCZoneTypeId, 'Include', @DCIntroDate1, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
	
	-- Create Distribution Center Rule for Vendor 2
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId2, @DCZoneTypeId, 'Include', @DCIntroDate2, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)

	-- Create Operator Team Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @OPZoneId, @OPZoneTypeId, 'Include', @OPIntroDate, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)	
	
	------------------------------------------------

/* SCENARIO MULTI VENDOR-10:  Create an item for a vendor using a Distributor and DC zone rule */
	SET @ItemCode = '1001-MV10'
	SET @ItemName = 'Item Test-MV10'

	EXEC Testing.MxOrderGuide_ItemCreation @ItemCode, @ItemName, @EntityId, @VendorId1, @VendorId2

	SET @DNIntroDate2 = DATEADD(Week, 2, GetDate())
	SET @DCIntroDate2 = DATEADD(Week, 1, GetDate())

	-- Create Distributor Rule for Vendor 1
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DNZoneId2, @DNZoneTypeId, 'Include', @DNIntroDate2, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
	
	-- Create Distribution Center Rule for Vendor 2
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId2, @DCZoneTypeId, 'Include', @DCIntroDate2, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)

	------------------------------------------------

/* SCENARIO MULTI VENDOR-11:  Don't create an item for a vendor when the vendor item is discontinued */
	SET @ItemCode = '1001-MV11'
	SET @ItemName = 'Item Test-MV11'

	EXEC Testing.MxOrderGuide_ItemCreation @ItemCode, @ItemName, @EntityId, @VendorId1, @VendorId2

	SET @DCIntroDate1 = DATEADD(Week, 1, GetDate())
	SET @OPIntroDate = DATEADD(Week, 2, GetDate())

	-- Create Distributor Rule for Vendor 1
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId1, @DNZoneTypeId, 'Include', @DCIntroDate1, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
	
	-- Discontinue the Vendor Item
	UPDATE tbVendorItem SET DiscontinueDate = DATEADD(Day, -1, GetDate())
	WHERE VendorId = @VendorId1 AND VendorCode = @ItemCode
	
	-- Create Operator Team Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @OPZoneId, @OPZoneTypeId, 'Include', @OPIntroDate, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
		
	------------------------------------------------

/* SCENARIO MULTI VENDOR-12:  Don't create an item for an include rule that has a discontinued date in the past */
	SET @ItemCode = '1001-MV12'
	SET @ItemName = 'Item Test-MV12'

	EXEC Testing.MxOrderGuide_ItemCreation @ItemCode, @ItemName, @EntityId, @VendorId1, @VendorId2

	SET @DCIntroDate1 = DATEADD(Week, -2, GetDate())
	SET @DCDiscDate1 = DATEADD(Week, -1, GetDate())

	-- Create Distributor Rule for Vendor 1
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId1, @DNZoneTypeId, 'Include', @DCIntroDate1, @DCDiscDate1, @RuleCreatedDate, @UserName, 0, NULL, NULL)
		
END
GO
