USE [IHBoxSystem]
GO
CREATE ROLE lab_real_time_readWrite AUTHORIZATION dbo 
GO  

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

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


USE [IHBoxSystem]
GO
CREATE ROLE lab_real_time_read AUTHORIZATION dbo 
GO  

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

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

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


EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [lab_real_time_read] -- The role
,@name = N'securedsp-LAB.GetChemistrySamplesWithCalculatedByHeat' -- 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
		   (
		   '!/lab-real-time(/:heatName)(/)'
		   , 'lab-real-time'
		   , 'custom-screens/lab-real-time/'
		   , 'lab_real_time_read'           
		   )
		   SET @result_routeId = @@IDENTITY
INSERT INTO [FrontEnd].[Menu]
		   ([Parent]
		   ,[Name]
		   ,[RouteID]
		   ,[SortOrder]
		   ,[Enabled]
		   ,[Visible]
		   ,[RequiredRole]
		   ,[Group]
		  )
	 VALUES
		   (
		   @parent
		   ,'Real Time'
		   ,@result_routeId 
		   ,1
		   ,1
		   ,1
		   ,'lab_real_time_read'
		   ,NULL
		 )
GO

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

DECLARE @ModuleId INT

INSERT INTO [SECURITY].[Modules]
		   ([Code]
		   ,[Name])
	 VALUES
		   ('lab-real-time'
		   ,'Lab Real Time')

		  SET @ModuleId = @@IDENTITY

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


	INSERT INTO [SECURITY].ModuleActions(ModuleId,Code,[Name],[Role])
	VALUES (@ModuleId,'read','Read','lab_real_time_read')
GO
/*------------------------------------------------------------------------------------------------------------------------*/
/*------------------------------------------------------NEW SP------------------------------------------------------*/
USE [MES]
GO

ALTER PROCEDURE [LAB].[GetAutocompleteHeats]
 	@heatName VARCHAR(7) = NULL
	,@SelectedHeatName VARCHAR(7) = 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), '')

		
	DECLARE @SearchTextString VARCHAR(100)

	SET @searchTextString = '%' + @heatName + '%'

	declare @heatNames table ([value] varchar(7), [label] varchar(7))
	-----------------------
	-- Begin Query
	-----------------------
	IF (@heatName IS NOT NULL)
	begin
		insert into @heatNames
		select top 20 
			cast([Name] as varchar(7)),
			cast([Name] as varchar(7))
		from mes.heats where [Name] like @searchTextString
		order by Id desc 
		
		if (@SelectedHeatName is not null)
		insert into @heatNames
		values (@SelectedHeatName,@SelectedHeatName)


		select distinct([value]), [label] from @heatNames
	end
	else
	begin
		select 
			 cast(HeatNumber as varchar(7))
			,cast(HeatNumber as varchar(7))
		from lad.Ladles 
		where HeatNumber is not null
		and  datediff(HOUR,LastPositionChange,GETUTCDATE()) < 2

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

	RETURN 0
END
/*------------------------------------------------------------------------------------------------------------------------*/
