/*
Using SSIS (Import/Export Data Wizard) - pull all of the following tables into a local DB:

tbItem
tbRuleset
tbSalesItem
DLPS.*

Then run the following script:
*/

/****** Object:  Table [dbo].[DLPSRuleXML]    Script Date: 2/2/2021 11:21:24 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DLPSRuleXML]'))
BEGIN
	DROP TABLE [dbo].[DLPSRuleXML]
END

CREATE TABLE [dbo].[DLPSRuleXML](
	[RulesetID] [int] NOT NULL,
	[RulesetZone] [varchar](50) NULL,
	[RulesetType] [varchar](29) NULL,
	[RuleData] [xml] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

DECLARE @Module TABLE
(
	ModuleName varchar(50),
	ModuleId int
)

--Taken from \Inform\Mx.BusinessObjects\Custom\BaseSystem\Rules\RuleModule.vb
INSERT INTO @Module VALUES('Labour', 1)
INSERT INTO @Module VALUES('Inventory', 2)
INSERT INTO @Module VALUES('Loyalty', 3)
INSERT INTO @Module VALUES('Cash', 4)
INSERT INTO @Module VALUES('Export', 5)
INSERT INTO @Module VALUES('DLPS Inventory Item Mappings', 6)
INSERT INTO @Module VALUES('DLPS Production Item Mappings', 7)
INSERT INTO @Module VALUES('DLPS Batch Size Text Matrix', 8)
INSERT INTO @Module VALUES('DLPS Meal Periods', 9)
INSERT INTO @Module VALUES('DLPS Production Meal Periods', 10)
INSERT INTO @Module VALUES('DLPS Intervals', 11)
INSERT INTO @Module VALUES('DLPS Max In Chutes', 12)
INSERT INTO @Module VALUES('DLPS Routes', 13)
INSERT INTO @Module VALUES('DLPS Route Mappings', 14)

INSERT INTO DLPSRuleXML
SELECT r.RulesetId, z.[Zone] AS RulesetZone, m.ModuleName AS RulesetType, r.Ruledata
FROM tbRuleset r
INNER JOIN [ATL2CFASQLV001].[Chick-fil-a].[dbo].tbZone z WITH (NOLOCK) ON r.ZoneId = z.ZoneId
INNER JOIN @Module m ON r.ModuleId = m.ModuleId

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DLPSZoneMembers]'))
BEGIN
	DROP TABLE [dbo].[DLPSZoneMembers]
END

CREATE TABLE [dbo].[DLPSZoneMembers](
	[ZoneID] [int] NOT NULL,
	[ZoneName] [varchar](50) NULL,
	[EntityId] [int] NOT NULL,
	[StoreNumber] [varchar](50) NULL
) ON [PRIMARY]
GO

INSERT INTO DLPSZoneMembers
SELECT z.ZoneId, z.[Zone] AS ZoneName, e.EntityId, e.StoreNumber
FROM tbRuleset r
INNER JOIN [ATL2CFASQLV001].[Chick-fil-a].[dbo].tbZone z WITH (NOLOCK) ON r.ZoneId = z.ZoneId
INNER JOIN [ATL2CFASQLV001].[Chick-fil-a].[dbo].tbEntityZone ez WITH (NOLOCK) ON z.ZoneId = ez.ZoneId
INNER JOIN [ATL2CFASQLV001].[Chick-fil-a].[dbo].tbEntity e WITH (NOLOCK) ON ez.EntityId = e.EntityId

--Then backup the DB, zip, and attach to ticket