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

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

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

	USE [IHBoxSystem]
	GO
	CREATE ROLE caster_supervision_readWrite AUTHORIZATION dbo 
	GO  

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

	USE [IHBoxSystem]
	GO
	EXEC sp_addrolemember 'caster_supervision_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-supervision(/)'
			   , 'caster-supervision'
			   , 'custom-screens/caster-supervision/'
			   , 'caster_torch_cooling_bed_read'           
			   )
			   SET @result_routeId = @@IDENTITY
	INSERT INTO [FrontEnd].[Menu]
			   ([Parent]
			   ,[Name]
			   ,[RouteID]
			   ,[SortOrder]
			   ,[Enabled]
			   ,[Visible]
			   ,[RequiredRole]
			   ,[Group]
			  )
		 VALUES
			   (
			   @parent
			   ,'Supervision (*)'
			   ,@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_supervision_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_supervision_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_supervision_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_supervision_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_supervision_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_supervision_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_supervision_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_supervision_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_supervision_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_supervision_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_supervision_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_supervision_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_supervision_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_supervision_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_supervision_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_supervision_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_supervision_read] -- The role
,@name = N'securedsp-CAS.GetTundishCars' -- the procedure
,@value =  N''  
GO


EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [caster_supervision_read] -- The role
,@name = N'securedsp-CAS.GetMolds' -- 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_supervision_readWrite')


	INSERT INTO [SECURITY].ModuleActions(ModuleId,Code,[Name],[Role])
	VALUES (@ModuleId,'read','Read','caster_supervision_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_supervision_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_supervision_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_supervision_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_supervision_readWrite] -- The role
,@name = N'securedsp-CAS.AssignCasterProcessInPLC' -- the procedure
,@value = N'SendCurrentUser=TRUE' 
GO



----------------------- 7-11-2022------------------



USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[AddProductManually]    Script Date: 7/11/2022 16:27:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [CAS].[AddProductManually]
	@heatName VARCHAR(10),
	@strandId INT, 
	@sequenceId 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:'
		+ ', @heatName: ' + ISNULL(CAST(@heatName AS VARCHAR), '')
		+ ', @strandId: ' + ISNULL(CAST(@strandId AS VARCHAR), '')
		+ ', @sequenceId: ' + ISNULL(CAST(@sequenceId AS VARCHAR), '')
		+ ', @length: ' + ISNULL(CAST(@length AS VARCHAR), '')
		+ ', @currentUser: ' + ISNULL(CAST(@currentUser AS VARCHAR), '')


	DECLARE  @logMsg varchar(max)



	-----------------------
	-- Begin Query
	-----------------------
	DECLARE @newProductId INT
	DECLARE @ProcessId INT
	
	select 
		@ProcessId = p.Id 
	from CAS.Processes p
	left join mes.heats h on h.Id = p.HeatId
	where h.[Name] = @heatName

	if @Length not between SYSTEM.GetConfigVariable('CAS1_MIN_LENGTH')
	and SYSTEM.GetConfigVariable('CAS1_MAX_LENGTH')
	begin
		declare @msg varchar(max)
		set @msg = 'Length ' + cast(@length as varchar) + ' outside limits ' + cast( SYSTEM.GetConfigVariable('CAS1_MIN_LENGTH') as varchar) + '-'+ cast( SYSTEM.GetConfigVariable('CAS1_MAX_LENGTH') as varchar)
		SELECT [ProductMsg] =  'Length ' + cast(@length as varchar) + ' outside limits ' + cast( SYSTEM.GetConfigVariable('CAS1_MIN_LENGTH') as varchar) + '-'+ cast( SYSTEM.GetConfigVariable('CAS1_MAX_LENGTH') as varchar)

		return
	end

	if (select count(*) from mes.Heats where [Name] = @heatName) = 0
	begin
		set @msg = 'The Heat ' + cast(@heatName as varchar) + ' does not exist '
		SELECT [ProductMsg] =   'The Heat ' + cast(@heatName as varchar) + ' does not exist '
		return
	end

	if (@ProcessId is null)
	begin
		set @msg = 'The Heat ' + cast(@heatName as varchar) + ' does not exist in Caster Process ' 
		SELECT [ProductMsg] =   'The Heat ' + cast(@heatName as varchar) + ' does not exist in Caster Process ' 
		return
	end
		
	if (select count(*) from cas.Products where ProcessId = @ProcessId and [Sequence] = @sequenceId and StrandId = @strandId) > 0
	begin
		set @msg = 'The Sequence ' + cast(@sequenceId as varchar) + ' already exists in Strand: '+ cast(@strandId as varchar)
		SELECT [ProductMsg] =  'The Sequence ' + cast(@sequenceId as varchar) + ' already exists in Strand: '+ cast(@strandId as varchar)
		return
	end	

	insert into cas.Products(ProcessId,HeatName,HeatId,StrandId,[sequence],[Length],ManuallyCreated,[Datetime],CutComplete,CutStart,Destination)
		values(
			@ProcessId
			,@heatName
			,(select top 1 Id from mes.heats where [Name] = @heatName)
			,@strandId
			,@sequenceId
			,@length
			,1
			,GETUTCDATE()
			,GETUTCDATE()
			,GETUTCDATE()
			,0
		)

		set @newProductId = @@IDENTITY
	

	set @logMsg = 'The product with Id: ' + isnull(cast(@newProductId as varchar(max)),'-') + ' was created 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 was created'
END








EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [caster_supervision_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_supervision_readWrite] -- The role
,@name = N'securedsp-CAS.ChangeProductQuality' -- the procedure
,@value = N'' 
GO

