USE [IHBoxSystem]
GO
CREATE ROLE onlineheatreport_readWrite AUTHORIZATION dbo 
GO  

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

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


USE [IHBoxSystem]
GO
CREATE ROLE onlineheatreport_read AUTHORIZATION dbo 
GO  

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

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




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



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





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


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





declare @parent int, @result_routeId INT
select @parent=id from [FrontEnd].[Menu] where name='MES'

INSERT INTO [FrontEnd].[Routes]
		   ([Route]
		   ,[ModuleName]
		   ,[ModulePath]
		   ,[RequiredRole]
	   )
	 VALUES
		   (
		   '!/online-heat-report(/:heatName)(/)'
		   , 'online-heat-report'
		   , 'custom-screens/online-heat-report/'
		   , 'onlineheatreport_read'           
		   )
		   SET @result_routeId = @@IDENTITY
INSERT INTO [FrontEnd].[Menu]
		   ([Parent]
		   ,[Name]
		   ,[RouteID]
		   ,[SortOrder]
		   ,[Enabled]
		   ,[Visible]
		   ,[RequiredRole]
		   ,[Group]
		  )
	 VALUES
		   (
		   @parent
		   ,'Heat Events'
		   ,@result_routeId 
		   ,1
		   ,1
		   ,1
		   ,'onlineheatreport_read'
		   ,NULL
		 )
GO

/*------------------------------------------------------Profiles------------------------------------------------------*/
USE [IHBoxSystem]
GO

DECLARE @ModuleId INT

INSERT INTO [SECURITY].[Modules]
		   ([Code]
		   ,[Name])
	 VALUES
		   ('online_heat_report'
		   ,'Heat Events')

		  SET @ModuleId = @@IDENTITY

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

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

/*---------------------------------------------------------------------------------------------------------------------------------------------*/
/*-----------------------------------------------------------------------------------------------------*/
/*-----------------------------------------------------------------------------------------------------*/
/*-----------------------------------------------------------------------------------------------------*/
/*-----------------------------------------------------------------------------------------------------*/
/*-----------------------------------------------------------------------------------------------------*/


create table MES.ManualHeatComments (Id int identity(1,1), HeatId int, [Datetime] datetime, [Description] varchar(max), [Source] varchar(200), isReply bit default 0)




/*-----------------------------------------------------------------------------------------------------*/
/*-----------------------------------------------------------------------------------------------------*/
/*-----------------------------------------------------------------------------------------------------*/
/*-----------------------------------------------------------------------------------------------------*/
/*-----------------------------------------------------------------------------------------------------*/
/*-----------------------------------------------------------------------------------------------------*/
/*-----------------------------------------------------------------------------------------------------*/

USE [MES]
GO
/****** Object:  StoredProcedure [MES].[GetOnlineHeatReport]    Script Date: 15/11/2022 10:01:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  PROCEDURE [MES].[AddManualHeatComment]
	@heatName varchar(7),
	@comment varchar(max) = '',	
	@datetime datetime = null,
	@isReply bit = 0,
	@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.'

	-----------------------
	-- Variables
	-----------------------
	declare @heatId int = (select Id from mes.heats where [Name] = @heatName)


	-------------------------
	---- Begin Query
	-------------------------
	insert into MES.ManualHeatComments(HeatId,[Datetime],[Description],[Source],[isReply])
	values(
		@heatId,
		case when @datetime is null  then GETUTCDATE() else @datetime end,
		@comment,
		@currentUser,
		@isReply
	)

	select [ManualHeatCommentsId] = @@IDENTITY
	-----------------------
	-- Finish procedure
	-----------------------

	PRINT @finishEndMSG

	RETURN 0
END

GO


/*-----------------------------------------------------------------------------------------------------*/
/*-----------------------------------------------------------------------------------------------------*/
/*-----------------------------------------------------------------------------------------------------*/
/*-----------------------------------------------------------------------------------------------------*/
/*-----------------------------------------------------------------------------------------------------*/
/*-----------------------------------------------------------------------------------------------------*/
/*-----------------------------------------------------------------------------------------------------*/

USE [MES]
GO
/****** Object:  StoredProcedure [MES].[GetOnlineHeatReport]    Script Date: 15/11/2022 14:05:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  PROCEDURE [MES].[GetOnlineHeatReport]
	@heatName varchar(7),
	@timeZoneCode CHAR(10) = 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:'
		+ '@heatName: ' + ISNULL(CAST(@heatName AS VARCHAR), '')
		+ ', @timeZoneCode: ' + ISNULL(CAST(@timeZoneCode AS VARCHAR), '')	
	-----------------------
	-- Variables
	-----------------------
	declare @heatId int = (select Id from mes.heats where [Name] = @heatName)

	--Navigate Buttons
	declare @buttons table ([Text] varchar(50), HeatId int, Treatment int)

	-------------------------
	---- Begin Query
	-------------------------
	declare @events table ([DateTimeUTC] datetime,[Datetime] varchar(5),[Description] varchar(max),[Source] varchar(200),isReply bit, [Manual] bit)

	insert into @events
	select 
		[Datetime],
		cast( left(convert(varchar,  IH.ToLocal([DateTime],@timeZoneCode), 8),5) as varchar),
		[Description],
		'EAF',
		0,
		0
	from lad.LadleEvents where HeatNumber = @heatName

	insert into @events
	select 
		[Datetime],
		cast( left(convert(varchar,  IH.ToLocal([DateTime],@timeZoneCode), 8),5) as varchar),
		[Description],
		[Source],
		isReply,
		1
	from MES.ManualHeatComments where heatID = @heatId

	select * from @events order by [DateTimeUTC] asc
	----------------------------------------------------------------------------------------------------------
	----------------------------------------------------------------------------------------------------------
	----------------------------------------------------------------------------------------------------------
	---------------------------------------  Navigate Buttons 	----------------------------------------------
	----------------------------------------------------------------------------------------------------------	
	----------------------------------------------------------------------------------------------------------
	----------------------------------------------------------------------------------------------------------

	select top 5 [Text]= 'EAF', Treatment = null, HeatName = h.[Name], HeatId = h.Id 
	from EAF.Processes p 
	left join mes.Heats h on h.Id = p.HeatId
	where p.HeatId = @heatId

	union all
	select top 5 [Text]= 'LMF', Treatment = p.Treatment, HeatName = h.[Name], HeatId = h.Id 
	from LF.Processes p 
	left join mes.Heats h on h.Id = p.HeatId
	where p.HeatId = @heatId

	union all
		
	select top 5 [Text]= 'VTD', Treatment = p.Treatment, HeatName = h.[Name], HeatId = h.Id
	from VTD.Processes p 
	left join mes.Heats h on h.Id = p.HeatId
	where p.HeatId = @heatId
		
	union all
		
	select top 1 [Text]= 'LAB', Treatment = null, HeatName = h.[Name], HeatId = h.Id
	from LAB.ChemistrySamplesWithCalculated l 
	left join mes.Heats h on h.Id = l.HeatId
	where l.HeatId = @heatId
			
	union all

	select top 5 [Text]= 'SLAG EAF', Treatment = null, HeatName = h.[Name], HeatId = h.Id
	from LAB.SlagChemistrySamples l
	left join mes.Heats h on h.Id = l.HeatId
	where l.HeatId = @heatId


	-----------------------
	-- Finish procedure
	-----------------------

	PRINT @finishEndMSG

	RETURN 0
END

GO