USE [IHBoxSystem]
GO
CREATE ROLE hydrogen_samples_readWrite AUTHORIZATION dbo 
GO  

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

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


USE [IHBoxSystem]
GO
CREATE ROLE hydrogen_samples_read AUTHORIZATION dbo 
GO  

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

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

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

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


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

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



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



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


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

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

INSERT INTO [FrontEnd].[Routes]
		   ([Route]
		   ,[ModuleName]
		   ,[ModulePath]
		   ,[RequiredRole]
	   )
	 VALUES
		   (
		   '!/hydrogen-samples(/:heatName)(/)'
		   , 'hydrogen-samples'
		   , 'custom-screens/hydrogen-samples/'
		   , 'hydrogen_samples_read'           
		   )
		   SET @result_routeId = @@IDENTITY
INSERT INTO [FrontEnd].[Menu]
		   ([Parent]
		   ,[Name]
		   ,[RouteID]
		   ,[SortOrder]
		   ,[Enabled]
		   ,[Visible]
		   ,[RequiredRole]
		   ,[Group]
		  )
	 VALUES
		   (
		   @parent
		   ,'Hydrogen Samples'
		   ,@result_routeId 
		   ,1
		   ,1
		   ,1
		   ,'hydrogen_samples_read'
		   ,NULL
		 )
GO

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

DECLARE @ModuleId INT

INSERT INTO [SECURITY].[Modules]
		   ([Code]
		   ,[Name])
	 VALUES
		   ('hydrogen-samples'
		   ,'Hydrogen Samples')

		  SET @ModuleId = @@IDENTITY

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


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






/*------------------------------------------------------Procedures------------------------------------------------------*/

USE [MES]
GO
/****** Object:  StoredProcedure [LAB].[RemoveHydrogenSample]    Script Date: 18/10/2023 09:45:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAB].[RemoveHydrogenSample]
  @Id int = NULL
 ,@CurrentUser VARCHAR(MAX) = null
AS
BEGIN

	--------------------
	---- Variables -----
	--------------------
	
	DECLARE	@procName VARCHAR(300)
	DECLARE	@logMsg VARCHAR(MAX)		
	SELECT @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)	
	DECLARE @msg VARCHAR(MAX) = ''
	
	declare 
	   @HeatName varchar(10)
	 ,@HydrogenSampleLocationId int = NULL
	 ,@Hydrogen float = null
	 ,@SampleTime DATETIME = null
	--------------------
	---- Begin Query -----
	--------------------
	BEGIN TRY

		SELECT
			  @HeatName = m.[Name]
			 ,@HydrogenSampleLocationId = s.HydrogenSampleLocationId
			 ,@Hydrogen  = s.Hydrogen
			 ,@SampleTime =s.SampleTime
		 FROM LAB.HydrogenSamples s
		 left join mes.heats m on s.heatId= m.Id
		WHERE s.Id = @Id

		DELETE LAB.HydrogenSamples
		WHERE Id = @Id
		RETURN @Id

		SET @msg = '[Hydrogen Sample Removed] - Id: ' + CAST(@Id AS VARCHAR) + ' Heat Name: ' + CAST(@HeatName AS VARCHAR) 
		+ ' Sample LocationId: ' + CAST(@HydrogenSampleLocationId AS VARCHAR) 
		+ ' Sample Time: ' + CAST(@SampleTime AS VARCHAR) 
		+ ' Hydrogen Value: ' + CAST(@Hydrogen AS VARCHAR) 
	
		EXEC SYSTEM.AuditMsg @message = @msg, @moduleName = 'LAB.UpsertHydrogenSample', @currentUser = @currentUser

	END TRY
	BEGIN CATCH
		SELECT @logMsg = ERROR_MESSAGE()
		print @logMsg
		EXEC SYSTEM.LogMsg @message = @logMsg
			,@procedure = @procName
			,@typeCode = 'ERROR'
			,@logError = 1
	END CATCH
END


go

USE [MES]
GO
/****** Object:  StoredProcedure [LAB].[UpsertHydrogenSample]    Script Date: 18/10/2023 08:49:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAB].[UpsertHydrogenSample]
  @Id int = NULL
 ,@HeatName varchar(10)
 ,@HydrogenSampleLocationId int = NULL
 ,@Hydrogen float = null
 ,@SampleTime DATETIME = null
 ,@CurrentUser VARCHAR(MAX) = null

AS
BEGIN
	
	--------------------
	---- Variables -----
	--------------------
	
	DECLARE	@procName VARCHAR(300)
	DECLARE	@logMsg VARCHAR(MAX)		
	SELECT @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)	
	DECLARE @msg VARCHAR(MAX) = ''

	declare @heatId int = (select top 1 Id from mes.heats where [Name] = @HeatName)
	declare @newSampleId int
	--------------------
	---- Begin Query -----
	--------------------

	if (@Id is null)
	begin
		IF @SampleTime is null
			set @SampleTime = GETUTCDATE()
		ELSE
			set @SampleTime = IH.ToUTC(@SampleTime,'EDTIND')

		INSERT INTO LAB.HydrogenSamples(HeatId,HydrogenSampleLocationId,SampleTime,Hydrogen)
		VALUES(@heatId,@HydrogenSampleLocationId,@SampleTime,@Hydrogen)

		set @newSampleId = @@IDENTITY


		
		SET @msg = '[Hydrogen Sample Created] - Id: ' + CAST(@newSampleId AS VARCHAR) + ' Heat Name: ' + CAST(@HeatName AS VARCHAR) 
		+ ' Sample LocationId: ' + CAST(@HydrogenSampleLocationId AS VARCHAR) 
		+ ' Sample Time: ' + CAST(@SampleTime AS VARCHAR) 
		+ ' Hydrogen Value: ' + CAST(@Hydrogen AS VARCHAR) 
	
		EXEC SYSTEM.AuditMsg @message = @msg, @moduleName = 'LAB.UpsertHydrogenSample', @currentUser = @currentUser

		
		RETURN @newSampleId
	end
	else
	begin
		if @SampleTime is not null
		BEGIN
			update LAB.HydrogenSamples
				set	[SampleTime] = IH.ToUTC(@SampleTime,'EDTIND')
			where Id = @Id		

					
			SET @msg = '[Hydrogen Sample Updated] - Id: ' + CAST(@Id AS VARCHAR) + ' Heat Name: ' + CAST(@HeatName AS VARCHAR) 
			+ ' Sample Time: ' + CAST(@SampleTime AS VARCHAR) 
		END
		ELSE
		BEGIN
			update LAB.HydrogenSamples
				set [Hydrogen] = @Hydrogen
				,[HydrogenSampleLocationId] = @HydrogenSampleLocationId
			where Id = @Id		

			SET @msg = '[Hydrogen Sample Created] - Id: ' + CAST(@newSampleId AS VARCHAR) + ' Heat Name: ' + CAST(@HeatName AS VARCHAR) 
			+ ' Sample LocationId: ' + CAST(@HydrogenSampleLocationId AS VARCHAR) 
			+ ' Hydrogen Value: ' + CAST(@Hydrogen AS VARCHAR) 
	
		END
		
		EXEC SYSTEM.AuditMsg @message = @msg, @moduleName = 'LAB.UpsertHydrogenSample', @currentUser = @currentUser

		RETURN @Id

	end

END

go


USE [MES]
GO
/****** Object:  StoredProcedure [LAB].[GetHydrogenSamplesCatalogs]    Script Date: 18/10/2023 08:19:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAB].[GetHydrogenSamplesCatalogs]
	@heatName varchar(10) = NULL
AS
BEGIN
	--- Locations
	select
	*
	from LAB.catHydrogenSamplesLocations
	where isEnabled = 1

	
	if @HeatName is null
		set @HeatName = (select top 1 HeatName from cas.Processes where CasterCode = 1 order by LadleOpen desc)


	select 
			[HeatName] = @HeatName

END

go

USE [MES]
GO
/****** Object:  StoredProcedure [LAB].[GetHydrogenSamples]    Script Date: 18/10/2023 08:11:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAB].[GetHydrogenSamples]
	@HeatName varchar(10) = NULL
AS
BEGIN
	if @HeatName is null
		set @HeatName = (select top 1 HeatName from cas.Processes where CasterCode = 1 order by LadleOpen desc)
	declare @heatId int = (select top 1 Id from mes.heats where [Name] = @HeatName)

		select 
		s.Id
		,h.[Name]
		,s.HeatId
		,SampleTime = convert(varchar, IH.ToLocal(s.SampleTime,'EDTIND'), 120)
		,s.HydrogenSampleLocationId
		,s.[Hydrogen]
		,SampleLocationName = loc.[Name]
	from LAB.HydrogenSamples s
	left join MES.Heats h on h.Id = s.HeatId
	left join LAB.catHydrogenSamplesLocations loc on loc.Id = s.HydrogenSampleLocationId
	where loc.isEnabled = 1 and h.Id = @heatId
END

go

