-------------
-- ROLES
-------------
	USE [IHBoxSystem]
	GO
	CREATE ROLE caster_torch_cooling_bed_read AUTHORIZATION dbo 
	GO  

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

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

	USE [IHBoxSystem]
	GO
	CREATE ROLE caster_torch_cooling_bed_readWrite AUTHORIZATION dbo 
	GO  

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

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


	

-------------------
-- Menu and Route
-------------------
	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-torch-cooling-bed(/)'
			   , 'caster-torch-cooling-bed'
			   , 'custom-screens/caster-torch-cooling-bed/'
			   , 'caster_torch_cooling_bed_read'           
			   )
			   SET @result_routeId = @@IDENTITY
	INSERT INTO [FrontEnd].[Menu]
			   ([Parent]
			   ,[Name]
			   ,[RouteID]
			   ,[SortOrder]
			   ,[Enabled]
			   ,[Visible]
			   ,[RequiredRole]
			   ,[Group]
			  )
		 VALUES
			   (
			   @parent
			   ,'Torch And Cooling Bed'
			   ,@result_routeId 
			   ,1
			   ,1
			   ,1
			   ,'caster_torch_cooling_bed_read'
			   ,NULL
			 )
	GO


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

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

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

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

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

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


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

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

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

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


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

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

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



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

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

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


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


-------------------
--	Profiles
-------------------
	USE [IHBoxSystem]
	GO

	DECLARE @ModuleId INT

	INSERT INTO [SECURITY].[Modules]([Code],[Name])
	VALUES('caster_torch','Caster Torch')

	SET @ModuleId = @@IDENTITY

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


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


GO


USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[AddProductManually]    Script Date: 2/5/2022 18:18:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [CAS].[UpdateProductLength]
	@productId INT,
	@length FLOAT, 

	@currentUser VARCHAR(MAX) = NULL
AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--The name of the procedure with its schema.
		@procName VARCHAR(300)

	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)

	PRINT @procName + ' Parameters:'
		+ ', @productId: ' + ISNULL(CAST(@productId AS VARCHAR), '')
		+ ', @length: ' + ISNULL(CAST(@length AS VARCHAR), '')
		+ ', @currentUser: ' + ISNULL(CAST(@currentUser AS VARCHAR), '')


	DECLARE  @logMsg varchar(max)



	-----------------------
	-- Begin Query
	-----------------------
	
	DECLARE @CurrentLength FLOAT = (select top 1 [Length] from cas.Products where id = @productId)

	

	update cas.Products
		set [Length] = @length
	where Id = @productId


	set @logMsg = 'The Length of the product with Id: ' + isnull(cast(@productId as varchar(max)),'-')
	+ ' was modified from: ' + isnull(cast(@CurrentLength as varchar(max)),'-') + ' to: ' + isnull(cast(@length as varchar(max)),'-')
	+ ' by: '+ cast(isnull(@currentUser,'-') as varchar(max))

	EXEC SYSTEM.LogMsg @message = @logMsg
		,@procedure = @procName
		,@typeCode = 'DEBUG'
		,@logError = 1


	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	SELECT [ProductMsg] = 'The product length was modified'
END















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

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


USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[SplitProduct]    Script Date: 2/6/2022 01:16:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [CAS].[ChangeDeliveredDestination]
	@productId INT 
	,@destination CHAR(2)
AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--The name of the procedure with its schema.
		@procName VARCHAR(300)

	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)

	PRINT @procName + ' Parameters:'
		+ ', @productId: ' + ISNULL(CAST(@productId AS VARCHAR), '')
		+ ', @destination: ' + ISNULL(CAST(@destination AS VARCHAR), '')
	DECLARE  @logMsg varchar(max)



	-----------------------
	-- Begin Query
	-----------------------
	UPDATE CAS.Products
		set DeliveredDestination = @destination
		,DeliveredDatetime = GETUTCDATE()
	where Id = @productId

	set @logMsg = 'The product with Id: ' + isnull(cast(@productId as varchar(max)),'-') + ' was changed its destination to: '+ cast(isnull(@destination,'-') as varchar(max))

	EXEC SYSTEM.LogMsg @message = @logMsg
		,@procedure = @procName
		,@typeCode = 'DEBUG'
		,@logError = 1


	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	SELECT [ProductMsg] = 'The destination product has changed'
END














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







----------------------- 13-07-2022------------------








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









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

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

