
--Run this to set up all the data
/* EXEC [Testing].[MxOrderGuide_MultipleVendorScenerios] */

--Change this value to see only one scenario at a time, for example '%MV2' if you only want to see scenario MV2
DECLARE @ItemCodeSearch VARCHAR(30) = '%MV%'

-- Run this to see the items and rules created by the setup. 
-- The rules will still need to be applied manually, until then the vendor entity items won't exist.
SELECT
	 zt.ZoneType as [Type],
	 z.Zone as ZoneOrEntityName,
	 z.Zoneid as ZoneOrEntityId,
	 vz.VendorCode,
	 'Zone' as RuleLevel,
	 vz.RuleType,
	 vz.ActiveDate,
	 vz.ExpirationDate,
	 vz.Deleted	 
FROM tbVendorItemZoneRule vz with (nolock)
INNER JOIN tbZone z with (nolock) ON z.ZoneId = vz.ZoneId
INNER JOIN tbZoneType zt with (nolock) ON zt.ZoneTypeId = z.ZoneTypeId
WHERE vendorcode like @ItemCodeSearch
UNION
SELECT
	'Restaurant' as [Type],
	 e.Entity as ZoneOrEntityName,
	 e.EntityId as ZoneOrEntityId,
	 ve.VendorCode,
	 'Store' as RuleLevel, 
	 ve.RuleType,
	 ve.ActiveDate,
	 ve.ExpirationDate,
	 ve.Deleted
FROM tbVendorItemEntityRule ve with (nolock)
INNER JOIN tbEntity e with (nolock) ON e.EntityId = ve.EntityId
WHERE vendorcode like @ItemCodeSearch
ORDER BY VendorCode, RuleType, ZoneOrEntityId


-- Run this to see the items created by the setup. 
-- The VendorEntityItems won't exist until after the rules are applied manually.
SELECT 
	i.itemid,
	i.ItemCode,
	i.[Description],
	e.Entity,
	v.VendorName,
	Case When vei.VendorEntityItemId IS NULL 
		Then 'Not Created' Else 'Created' END as [VendorEntityItem],
	vei.IntroductionDate,
	vei.DiscontinueDate,
	vei.VendorEntityItemId
FROM tbItem i
INNER JOIN tbEntityItem ei ON ei.ItemId = i.ItemId
INNER JOIN tbEntity e ON e.EntityId = ei.EntityId
INNER JOIN tbVendorItem vi ON vi.ItemId = i.ItemId
INNER JOIN tbVendor v ON v.VendorId = vi.VendorId
LEFT JOIN tbVendorEntityItem vei ON vei.VendorId = v.VendorId AND vei.EntityId = e.EntityId AND vei.ItemId = i.ItemId
WHERE i.ItemCode like @ItemCodeSearch
ORDER BY [Description]


-- Run this after the test is done to clean up the data created by the test.
/* EXEC [Testing].[MxOrderGuide_TestTeardown] */
