SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

CREATE PROCEDURE [Testing].[MxOrderGuide_SingleVendorScenerios]
AS
BEGIN

	--Input Parameters
	DECLARE @EntityName  VARCHAR(30) = '%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 'MBM - Rules%' and zoneTypeId = @DCZoneTypeId)
	DECLARE @DCZoneID2 BIGINT = (select zoneId from tbZone where zone like 'QCD - Rules%' 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 'MBM%' and zoneTypeId = @DNZoneTypeId)
	DECLARE @DNZoneId2 BIGINT = (select zoneId from tbZone where zone like 'QCD%' 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()
	
	-- Values that reset for each test
	DECLARE @ItemCode VARCHAR(30) = '' 
	DECLARE @ItemName VARCHAR(30) = '' 
	DECLARE @DNIntroDate DATETIME = NULL
	DECLARE @DCIntroDate DATETIME = NULL
	DECLARE @OPIntroDate DATETIME = NULL
	DECLARE @RestIntroDate DATETIME = NULL
	
	/* SCENARIO SINGLE VENDOR-1:  Create an item with two competing zone include rules for the same vendor */
	SET @ItemCode = '1001-V1'
	SET @ItemName = 'Item Test-V1'

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

	SET @DNIntroDate = DATEADD(Week, 1, GetDate())
	SET @DCIntroDate = DATEADD(Week, 2, GetDate())

	-- Create Distribution Name Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DNZoneId1, @DNZoneTypeId, 'Include', @DNIntroDate, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
		
	-- Create Distribution Center Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId1, @DCZoneTypeId, 'Include', @DCIntroDate, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)

	/* SCENARIO SINGLE VENDOR-2:  Create an item with two competing zone include rules for the same vendor */
	SET @ItemCode = '1001-V2'
	SET @ItemName = 'Item Test-V2'

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

	SET @DNIntroDate = DATEADD(Week, 2, GetDate())
	SET @DCIntroDate = DATEADD(Week, 1, GetDate())

	-- Create Distribution Name Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DNZoneId1, @DNZoneTypeId, 'Include', @DNIntroDate, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
		
	-- Create Distribution Center Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId1, @DCZoneTypeId, 'Include', @DCIntroDate, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)

	/* SCENARIO SINGLE VENDOR-3:  Create an item with an include DN zone rule and exclude DC zone rule */
	SET @ItemCode = '1001-V3'
	SET @ItemName = 'Item Test-V3'

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

	SET @DNIntroDate = DATEADD(Week, 1, GetDate())
	SET @DCIntroDate = DATEADD(Week, 2, GetDate())

	-- Create Distribution Name Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DNZoneId1, @DNZoneTypeId, 'Include', @DNIntroDate, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
		
	-- Create Distribution Center Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId1, @DCZoneTypeId, 'Exclude', @DCIntroDate, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
	
	/* SCENARIO SINGLE VENDOR-4:  Create an item with an include non-DC/DN zone rule */
	SET @ItemCode = '1001-V4'
	SET @ItemName = 'Item Test-V4'

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

	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 SINGLE VENDOR-5:  Create an item with DC/DN include zone rules and an Operator Team exclude rule */
	SET @ItemCode = '1001-V5'
	SET @ItemName = 'Item Test-V5'

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

	SET @DNIntroDate = DATEADD(Week, 1, GetDate())
	SET @DCIntroDate = DATEADD(Week, 2, GetDate())
	SET @OPIntroDate = DATEADD(Week, 1, GetDate())
	
	-- Create Distributor Name Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DNZoneId1, @OPZoneTypeId, 'Include', @DNIntroDate, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
	
	-- Create Distributor Center Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId1, @OPZoneTypeId, 'Include', @DNIntroDate, 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 SINGLE VENDOR-6:  Create an item with competing DC exclude rule and Restaurant include rule */
	SET @ItemCode = '1001-V6'
	SET @ItemName = 'Item Test-V6'

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

	SET @DNIntroDate = DATEADD(Week, 1, GetDate())
	SET @DCIntroDate = DATEADD(Week, 1, GetDate())
	SET @RestIntroDate = DATEADD(Week, 1, GetDate())
	
	-- Create Distributor Name Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DNZoneId1, @OPZoneTypeId, 'Include', @DNIntroDate, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
	
	-- Create Distributor Center Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId1, @OPZoneTypeId, 'Exclude', @DNIntroDate, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
	
	-- Create Restaurant Rule
	INSERT INTO tbVendorItemEntityRule VALUES (
		@ItemCode, @EntityId, 'Include', @RestIntroDate, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)	

	/* SCENARIO SINGLE VENDOR-7:  Create an item with a DN include rule, and competing DC and OpTeam exclude rules */
	SET @ItemCode = '1001-V7'
	SET @ItemName = 'Item Test-V7'

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

	SET @DNIntroDate = DATEADD(Week, 1, GetDate())
	SET @DCIntroDate = DATEADD(Week, 2, GetDate())
	SET @OPIntroDate = DATEADD(Week, 1, GetDate())
	
	-- Create Distributor Name Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DNZoneId1, @OPZoneTypeId, 'Include', @DNIntroDate, NULL, @RuleCreatedDate, @UserName, 0, NULL, NULL)
	
	-- Create Distributor Center Rule
	INSERT INTO tbVendorItemZoneRule VALUES (
		@ItemCode, @DCZoneId1, @OPZoneTypeId, 'Exclude', @DNIntroDate, 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 SINGLE VENDOR-8:  Create an item with a DN include rule, and competing DC and OpTeam exclude rules */
	SET @ItemCode = '1001-V8'
	SET @ItemName = 'Item Test-V8'

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

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

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