USE [IHBoxSystem]
GO
CREATE ROLE cdm_r AUTHORIZATION dbo 
GO  

USE [IHBoxSystem]
GO
CREATE ROLE cdm_w AUTHORIZATION dbo 
GO  

USE [IHBoxSystem]
GO
EXEC sp_addrolemember 'cdm_r', 'admin'
GO

USE [IHBoxSystem]
GO
EXEC sp_addrolemember 'cdm_r', 'SDI/australtek'
GO

USE [IHBoxSystem]
GO
EXEC sp_addrolemember 'cdm_w', 'admin'
GO

USE [IHBoxSystem]
GO
EXEC sp_addrolemember 'cdm_w', 'SDI/australtek'
GO



declare @parent int, @result_routeId INT
select @parent=id from [FrontEnd].[Menu] where [Name] = 'Caster' and Parent is null

INSERT INTO [FrontEnd].[Routes]
		   ([Route]
		   ,[ModuleName]
		   ,[ModulePath]
		   ,[RequiredRole]
	   )
	 VALUES 
		   (
		   '!/caster-delays-manager'
		   , 'caster-delays-manager'
		   , 'custom-screens/caster-delays-manager/'
		   , 'cdm_r'           
		   )
		   SET @result_routeId = @@IDENTITY
INSERT INTO [FrontEnd].[Menu]
		   ([Parent]
		   ,[Name]
		   ,[RouteID]
		   ,[SortOrder]
		   ,[Enabled]
		   ,[Visible]
		   ,[RequiredRole]
		   ,[Group]
		  )
	 VALUES
		   (
		   @parent
		   ,'Caster Delays Manager'
		   ,@result_routeId 
		   ,1
		   ,1
		   ,1
		   ,'cdm_r'
		   ,NULL
		 )
GO

/*------------------------------------------------------Profiles------------------------------------------------------*/

USE [IHBoxSystem]
GO

DECLARE @ModuleId INT

INSERT INTO [SECURITY].[Modules]([Code],[Name])
VALUES('caster_delays_manager','Caster Delays Manager')

SET @ModuleId = @@IDENTITY

INSERT INTO [SECURITY].[ModuleActions]([ModuleId],[Code],[Name],[Role])
VALUES(@ModuleId,'readWrite','Read/Write','cdm_w')


INSERT INTO [SECURITY].ModuleActions(ModuleId,Code,[Name],[Role])
VALUES (@ModuleId,'read','Read','cdm_r')

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [cdm_r] -- The role
,@name = N'securedsp-CAS.GetDelaysCodeGroup' -- the procedure
,@value = N'' 
GO

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [cdm_r] -- The role
,@name = N'securedsp-CAS.GetDelayCodes' -- the procedure
,@value = N'' 
GO

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [cdm_r] -- The role
,@name = N'securedsp-CAS.GetDelayData' -- the procedure
,@value = N'' 
GO
EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [cdm_w] -- The role
,@name = N'securedsp-CAS.UpdateDelay' -- the procedure
,@value = N'SendCurrentUser=TRUE' 
GO


EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [cdm_w] -- The role
,@name = N'securedsp-CAS.SplitDelay' -- the procedure
,@value = N'' 
GO

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [cdm_w] -- The role
,@name = N'securedsp-CAS.SplitDelayAt' -- the procedure
,@value = N'SendCurrentUser=TRUE' 
GO

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [cdm_w] -- The role
,@name = N'securedsp-CAS.JoinDelay' -- the procedure
,@value = N'SendCurrentUser=TRUE' 
GO


---------------------------
-- PROCEDURES----
---------------------------
INSERT INTO DEL.catDelayCodes (ParentDelayCodeId, DelayAreaAssetId, DefaultDelayCategoryId, DelayCodeName, SortOrder, isActive)
VALUES
-- Caster 1 Planned
(1, 1000, 1, 'Turn-Around', 1, 1),
(1, 1000, 1, 'Size Change', 2, 1),
(1, 1000, 1, 'Scheduled Down', 3, 1),
(1, 1000, 1, 'Down Day', 4, 1),
(1, 1000, 1, 'Curtailment', 5, 1),
(1, 1000, 1, 'Other', 6, 1),

-- Caster 2 Planned
(7, 2000, 7, 'Turn-Around', 1, 1),
(7, 2000, 7, 'Size Change', 2, 1),
(7, 2000, 7, 'Scheduled Down', 3, 1),
(7, 2000, 7, 'Down Day', 4, 1),
(7, 2000, 7, 'Curtailment', 5, 1),
(7, 2000, 7, 'Other', 6, 1),

-- Caster 1 Unplanned
(2, 1000, 2, 'Turret', 1, 1),
(2, 1000, 2, 'Molds', 2, 1),
(2, 1000, 2, 'Oscillators', 3, 1),
(2, 1000, 2, 'Segments', 4, 1),
(2, 1000, 2, 'Straighteners', 5, 1),
(2, 1000, 2, 'Torches', 6, 2),
(2, 1000, 2, 'Runout Tables', 7, 1),
(2, 1000, 2, 'Cross Transfer', 8, 1),
(2, 1000, 2, 'Pusher Table', 9, 1),
(2, 1000, 2, 'Quality', 10, 1),
(2, 1000, 2, 'Water', 11, 1),
(2, 1000, 2, 'Breakout', 12, 1),
(2, 1000, 2, 'Startup Issue', 13, 1),
(2, 1000, 2, 'Ladle Issue', 14, 1),
(2, 1000, 2, 'EAFA', 15, 1),
(2, 1000, 2, 'EAFB', 16, 1),
(2, 1000, 2, 'LMFA', 17, 1),
(2, 1000, 2, 'Ladle Cranes', 18, 1),
(2, 1000, 2, 'Bloom Cranes', 19, 1),
(2, 1000, 2, 'Incoming Power', 20, 1),
(2, 1000, 2, 'Other', 21, 1),

-- Caster 2 Unplanned
(8, 2000, 8, 'Turret', 1, 1),
(8, 2000, 8, 'Molds', 2, 1),
(8, 2000, 8, 'Oscillators', 3, 1),
(8, 2000, 8, 'Segments', 4, 1),
(8, 2000, 8, 'Straighteners', 5, 1),
(8, 2000, 8, 'Torches', 6, 1),
(8, 2000, 8, 'Runout Tables', 7, 1),
(8, 2000, 8, 'CTM', 8, 1),
(8, 2000, 8, 'Walking Bed', 9, 1),
(8, 2000, 8, 'Quality', 10, 1),
(8, 2000, 8, 'Water', 11, 1),
(8, 2000, 8, 'Breakout', 12, 1),
(8, 2000, 8, 'Startup Issue', 13, 1),
(8, 2000, 8, 'Ladle Issue', 14, 1),
(8, 2000, 8, 'EAFA', 15, 1),
(8, 2000, 8, 'EAFB', 16, 1),
(8, 2000, 8, 'LMFA', 17, 1),
(8, 2000, 8, 'LMFB', 18, 1),
(8, 2000, 8, 'VTD', 19, 1),
(8, 2000, 8, 'Ladle Cranes', 20, 1),
(8, 2000, 8, 'Bloom Cranes', 21, 1),
(8, 2000, 8, 'Incoming Power', 22, 1),
(8, 2000, 8, 'Other', 23, 1);


