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

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

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


	CREATE ROLE casterproduction_readWrite AUTHORIZATION dbo 
	GO  

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

	USE [IHBoxSystem]
	GO
	EXEC sp_addrolemember 'casterproduction_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-production(/:start)(/:end)(/:casterCode)(/:crewId)(/:shiftId)(/)'
			   , 'caster-production'
			   , 'custom-screens/caster-production/'
			   , 'casterproduction_read'           
			   )
			   SET @result_routeId = @@IDENTITY
	INSERT INTO [FrontEnd].[Menu]
			   ([Parent]
			   ,[Name]
			   ,[RouteID]
			   ,[SortOrder]
			   ,[Enabled]
			   ,[Visible]
			   ,[RequiredRole]
			   ,[Group]
			  )
		 VALUES
			   (
			   @parent
			   ,'Production'
			   ,@result_routeId 
			   ,1
			   ,1
			   ,1
			   ,'casterproduction_read'
			   ,NULL
			 )
	GO


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

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

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

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


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

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



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

	DECLARE @ModuleId INT

	INSERT INTO [SECURITY].[Modules]([Code],[Name])
	VALUES('caster_production','Caster Production')

	SET @ModuleId = @@IDENTITY

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


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


-------------------
--	Table Changes
-------------------
insert into LAD.catLadleEventTypes
values(20,'Assing Heat to Process')




USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[ReassingCasterProcess]    Script Date: 8 feb. 2021 12:18:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [CAS].[ReassingCasterProcess]
 	@processId INT
	,@heatId INT =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)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
		+ ', @processId: ' + ISNULL(CAST(@processId AS VARCHAR), '')
		+ ', @heatId: ' + ISNULL(CAST(@heatId AS VARCHAR), '')
	
	-----------------------
	-- Begin Query
	-----------------------
	update CAS.Processes
		set [HeatId] = @heatId
	where Id = @processId


	-----------------------
	-- Log
	-----------------------

	insert into LAD.LadleEvents([DateTime],[LadleEventTypeID],[EventSourceID],[LadleID],HeatNumber,MeltOrder,GradeName,[Description])
	select 
		GETUTCDATE()
		,20  -- Assing Heat to Process
		,41  -- MES Man
		,(select LadleId from MES.Heats where Id = @heatId)
		,(select [Name] from MES.Heats where Id = @heatId)
		,(select MeltOrderNumber from MES.MeltOrders where Id = (select MeltOrderId from MES.Heats where Id = @heatId))  
		,(select [Name] from CAT.Grades where Id = (select [GradeId] from MES.Heats where Id = @heatId))  
		,'Heat ' + CAST((select [Name] from MES.Heats where Id = @heatId) as varchar) + 'was assigned to Caster ProcessId ' + CAST(@processId as varchar)

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

	RETURN 0
END
GO


USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[GetLastHeatsByProcessId]    Script Date: 7 feb. 2021 19:36:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [CAS].[GetLastHeatsByProcessId]
 	@processId INT = NULL
AS
BEGIN
	
	--------------
	-- Initialize
	--------------
	if @processId is null 
		set @processId = (select top 1 Id from CAS.Processes order by [Start] desc)

	-----------------------
	-- 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)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
		+ ', @processId: ' + ISNULL(CAST(@processId AS VARCHAR), '')
	
	DECLARE @Start DATETIME = (select [Start] from CAS.Processes where Id = @processId)
	-----------------------
	-- Begin Query
	-----------------------

	SELECT  
		[ProcessId] = p.Id
		,[FurnaceCode] = p.FurnaceCode
		,[GradeName] = (select top 1 [Name] from CAT.Grades WHERE Id = p.GradeId)
		,[StartTap] = convert(varchar, p.StartTap, 120)
		,[EndTap] = convert(varchar, p.EndTap, 120)
		,[ShiftId]= p.[ShiftId]
		,[CrewId]=p.[CrewId]

		,[HeatId] = h.Id		
		,[HeatName] = h.[Name]
		
	FROM EAF.Processes p
	LEFT JOIN MES.Heats h on h.Id = p.HeatId
	LEFT JOIN CAS.Processes cp on cp.HeatId = h.Id
	WHERE p.EndTap< @Start AND DATEDIFF(HOUR,p.EndTap,@Start) between 0 and 6 AND cp.Id is null
	ORDER BY p.EndTap DESC


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

	RETURN 0
END

GO



ALTER PROCEDURE [CAS].[GetLastHeatsByProcessId]
 	@processId INT = NULL
AS
BEGIN
	
	--------------
	-- Initialize
	--------------
	if @processId is null 
		set @processId = (select top 1 Id from CAS.Processes where CasterCode =1 order by [Start] desc)

	-----------------------
	-- 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)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
		+ ', @processId: ' + ISNULL(CAST(@processId AS VARCHAR), '')
	
	
	------------------------
	--- Variables and Tables
	------------------------
	
	declare @LadleOpen datetime 
	declare @LastEAFProcess table (ProcessId int, HeatId int, EndTap varchar(50), HeatName varchar(50), FurnaceCode int, [ShiftId] char(1), [CrewId] char(1), GradeName varchar(50), EndLMF varchar(50))

	---------------------
	---
	---------------------
	set @LadleOpen = (select LadleOpen from cas.Processes where id = @processId)


	insert into @LastEAFProcess
	select 
		eaf.Id
		,eaf.HeatId
		,convert(varchar, eaf.EndTap, 120)
		,h.[Name]
		,eaf.FurnaceCode
		,case when eaf.[ShiftId] = 1 then 'D' else 'N' end
		,eaf.CrewId
		,g.[Name]
		,case when lp.[End] is not null then convert(varchar, lp.[End], 120) else '-'End  
	from eaf.Processes eaf
	left join mes.heats h on h.Id = eaf.HeatId
	left join cat.grades g on g.Id = eaf.GradeId
	left join lf.processes lp on lp.HeatId = h.Id and Treatment =1 
	left join cas.Processes cas on eaf.HeatId = cas.HeatId
	where DATEDIFF(MINUTE,EndTap,@LadleOpen) between 0 and 6*60 
	and cas.HeatId is null
	order by EndTap desc



	select *
	from @LastEAFProcess 











	-----------------------
	-- Begin Query
	-----------------------

	--SELECT  
	--	[ProcessId] = p.Id
	--	,[FurnaceCode] = p.FurnaceCode
	--	,[GradeName] = (select top 1 [Name] from CAT.Grades WHERE Id = p.GradeId)
	--	,[StartTap] = convert(varchar, p.StartTap, 120)
	--	,[EndTap] = convert(varchar, p.EndTap, 120)
	--	,[ShiftId]= case when p.[ShiftId] = 1 then 'D' else 'N' end
	--	,[CrewId]=p.[CrewId]

	--	,[HeatId] = h.Id		
	--	,[HeatName] = h.[Name]

	--	,[EndLMF] = case when lp.[End] is not null then convert(varchar, lp.[End], 120) else '-'End  
		
	--FROM EAF.Processes p
	--LEFT JOIN MES.Heats h on h.Id = p.HeatId
	----LEFT JOIN CAS.Processes cp on cp.HeatId = h.Id
	--LEFT JOIN CAS.Processes cp on cp.HeatId = h.Id
	--LEFT JOIN LF.Processes lp on lp.HeatId = h.Id and Treatment =1 
	--WHERE p.EndTap< @Start 
	--AND DATEDIFF(HOUR,p.EndTap,@Start) between 0 and 6 
	----AND cp.HeatId is null 
	--and cp.CasterCode =1
	--ORDER BY p.EndTap DESC


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

	RETURN 0
END
GO 

















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

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

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

USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[ReassingCasterProcess]    Script Date: 13/7/2022 08:56:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [CAS].[AssignCasterProcessMES]
 	@processId INT
	,@heatId INT
	,@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)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
		+ ', @processId: ' + ISNULL(CAST(@processId AS VARCHAR), '')
		+ ', @heatId: ' + ISNULL(CAST(@heatId AS VARCHAR), '')
		+ ', @currentUser: ' + ISNULL(CAST(@currentUser AS VARCHAR), '')
		
	
	-----------------------
	-- Begin Query
	-----------------------
	-----------------------
	-- Assign Heat
	-----------------------
	DECLARE @ToHeatName varchar(50) = (select [Name] from mes.heats where id = @heatId)

		
	update CAS.Processes
		set [HeatId] = @heatId
			,[HeatName] = @ToHeatName
	where Id = @processId
		
	-- Log
	insert into LAD.LadleEvents([DateTime],[LadleEventTypeID],[EventSourceID],[LadleID],HeatNumber,MeltOrder,GradeName,[Description])
	select 
		GETUTCDATE()
		,20  -- Assing Heat to Process
		,41  -- MES Man
		,(select LadleId from MES.Heats where Id = @heatId)
		,(select [Name] from MES.Heats where Id = @heatId)
		,(select MeltOrderNumber from MES.MeltOrders where Id = (select MeltOrderId from MES.Heats where Id = @heatId))  
		,(select [Name] from CAT.Grades where Id = (select [GradeId] from MES.Heats where Id = @heatId))  
		,'Heat ' + CAST((select [Name] from MES.Heats where Id = @heatId) as varchar) + ' was assigned to Caster ProcessId ' + CAST(@processId as varchar)
		+ ' User: ' + CAST(@currentUser AS VARCHAR(100))



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

	RETURN 0
END

go
USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[UnassignCasterProcess]    Script Date: 13/7/2022 08:41:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [CAS].[UnassignCasterProcessMES]
 	@processId INT	
	,@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)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
		+ ', @processId: ' + ISNULL(CAST(@processId AS VARCHAR), '')
		+ ', @currentUser: ' + ISNULL(CAST(@currentUser AS VARCHAR), '')
		
	
	-----------------------
	-- Begin Query
	-----------------------

	-------------------------------
	-- basic code to unassign heat
	-------------------------------
		DECLARE @heatId INT = (select top 1 HeatId from cas.Processes where Id = @processId)

		update CAS.Processes
			set [HeatId] = null
		where Id = @processId

		--Log

		insert into LAD.LadleEvents([DateTime],[LadleEventTypeID],[EventSourceID],[LadleID],HeatNumber,MeltOrder,GradeName,[Description])
		select 
			GETUTCDATE()
			,22  -- Assing Heat to Process ----> New Type
			,41  -- MES Man
			,(select LadleId from MES.Heats where Id = @heatId)
			,(select [Name] from MES.Heats where Id = @heatId)
			,(select MeltOrderNumber from MES.MeltOrders where Id = (select MeltOrderId from MES.Heats where Id = @heatId))  
			,(select [Name] from CAT.Grades where Id = (select [GradeId] from MES.Heats where Id = @heatId))  
			,'Heat ' + CAST((select [Name] from MES.Heats where Id = @heatId) as varchar) + ' was unassigned from Caster ProcessId ' + CAST(@processId as varchar)
			+ ' User: ' + CAST(@currentUser AS VARCHAR(100))


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

	RETURN 0
END
go