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

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

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


	CREATE ROLE ladletrackingevents_readWrite AUTHORIZATION dbo 
	GO  

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

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


-------------------
-- Menu and Route
-------------------
	declare @parent int, @result_routeId INT
	select @parent=id from [FrontEnd].[Menu] where [Name] = 'MES' and Parent is null

	INSERT INTO [FrontEnd].[Routes]
			   ([Route]
			   ,[ModuleName]
			   ,[ModulePath]
			   ,[RequiredRole]
		   )
		 VALUES 
			   (
			   '!/ladle-tracking-events(/:start)(/:end)(/:search)(/:events)(/)'
			   , 'ladle-tracking-events'
			   , 'custom-screens/ladle-tracking-events/'
			   , 'ladletrackingevents_read'           
			   )
			   SET @result_routeId = @@IDENTITY
	INSERT INTO [FrontEnd].[Menu]
			   ([Parent]
			   ,[Name]
			   ,[RouteID]
			   ,[SortOrder]
			   ,[Enabled]
			   ,[Visible]
			   ,[RequiredRole]
			   ,[Group]
			  )
		 VALUES
			   (
			   @parent
			   ,'Ladle Tracking Events'
			   ,@result_routeId 
			   ,1
			   ,1
			   ,1
			   ,'ladletrackingevents_read'
			   ,NULL
			 )
	GO




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

	DECLARE @ModuleId INT

	INSERT INTO [SECURITY].[Modules]([Code],[Name])
	VALUES('ladle_tracking_events','Ladle Tracking Events')

	SET @ModuleId = @@IDENTITY

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


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


-------------------
-- SP Permissions
-------------------
GO

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


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



update top (1) FrontEnd.[Routes] set [Route] = '!/ladle-tracking-events(/:start)(/:end)(/:search)(/:events)(/)' where ModuleName = 'ladle-tracking-events' 
---------------------
-- STORED PROCEDURES
---------------------
USE [MES]
GO
/****** Object:  StoredProcedure [LAD].[GetLadleTrackingEventsPickersWeb]    Script Date: 23/3/2023 19:09:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [LAD].[GetLadleTrackingEventsPickersWeb]	
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
	
	
	-----------------------
	-- Query
	-----------------------
	select 
		[Name], 
		[Id]= LadleEventTypeID,
		isAbnormalEvent = case when LadleEventTypeID in (21,18,11,13) then 1 else 0 end
	from LAD.catLadleEventTypes
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END

go



USE [MES]
GO
/****** Object:  StoredProcedure [LAD].[GetLadleTrackingEventsWeb]    Script Date: 23/3/2023 19:45:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [LAD].[GetLadleTrackingEventsWeb]
	@search VARCHAR(MAX) = NULL,
	@LayoutEventTypes XML = NULL,	
	@start DATE = NULL,
	@end DATE = NULL,
	@fromRow INT = NULL, -- From what row to start fetching from
	@toRow INT = NULL, -- To row (@toRow row num not included in result data)
	@rowsToFetch INT = NULL -- Amount of rows to fetch
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:'
		+ ', @search: ' + ISNULL(CAST(@search AS VARCHAR), '')

	-----------------------
	-- Parameter defaults
	-----------------------
	IF (@rowsToFetch IS NULL)
	SET @rowsToFetch = -1
	
	IF (@rowsToFetch > 0)
	BEGIN
	IF (@fromRow IS NULL)
	SET @fromRow = 1
	
	IF (@toRow IS NULL)
	SET @toRow = @fromRow + @rowsToFetch
	END
	DECLARE @SearchTextString VARCHAR(100)

	SET @searchTextString = '%' + @search + '%'

	-------------------------------------
	---- Processes convertion to Table
	--------------------------------------
	DECLARE @layoutTEventTypes TABLE([LadleEventTypeID] INT)

	INSERT INTO @layoutTEventTypes([LadleEventTypeID])
		SELECT
			[LadleEventTypeID] =  T.Variable.value('@value','INT')
		FROM
		@LayoutEventTypes.nodes('XMLRoot/Row') AS T(Variable)


	-----------------------
	-- Query
	-----------------------
	select 
		e.*
		,LadleEventName = ev.[Name]
		,EventSource = s.[Name]
		,[DatetimeLocal] = convert(varchar,e.[DateTime],20)
	into #temp1
	from LAD.LadleEvents e
	left join LAD.catLadleEventTypes ev on ev.LadleEventTypeID = e.LadleEventTypeID
	left join LAD.catEventSources s on s.EventSourceID = e.EventSourceID
	where (
		(select count(*) from @layoutTEventTypes)  = 0 or 
	e.LadleEventTypeID in (select [LadleEventTypeID] from @layoutTEventTypes))
	and 	(@SearchTextString IS NULL
					OR e.[MeltOrder] LIKE @SearchTextString
					OR e.[Description] LIKE @SearchTextString					
					OR e.[HeatNumber] LIKE @SearchTextString)	
	and e.[DateTime] between @start and @end
	   	 
	SELECT 
	t1.*,
	RowNum = ROW_NUMBER() OVER (ORDER BY t1.[Datetime] DESC)
	INTO #temp2
	FROM #temp1 t1

	SELECT
	*
	FROM #temp2 t2
	WHERE
	(@rowsToFetch = -1 OR (t2.RowNum BETWEEN @fromRow AND  @toRow))
		ORDER BY t2.[DateTime] DESC

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

	RETURN 0
END
go