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

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

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


	CREATE ROLE ladletiming_readWrite AUTHORIZATION dbo 
	GO  

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

	USE [IHBoxSystem]
	GO
	EXEC sp_addrolemember 'ladletiming_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-timing-report(/:start)(/:end)(/)'
			   , 'ladle-timing-report'
			   , 'custom-screens/ladle-timing-report/'
			   , 'ladletiming_read'           
			   )
			   SET @result_routeId = @@IDENTITY
	INSERT INTO [FrontEnd].[Menu]
			   ([Parent]
			   ,[Name]
			   ,[RouteID]
			   ,[SortOrder]
			   ,[Enabled]
			   ,[Visible]
			   ,[RequiredRole]
			   ,[Group]
			  )
		 VALUES
			   (
			   @parent
			   ,'Ladle Timing Report'
			   ,@result_routeId 
			   ,5
			   ,1
			   ,1
			   ,'ladletiming_read'
			   ,NULL
			 )
	GO


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

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


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

	DECLARE @ModuleId INT

	INSERT INTO [SECURITY].[Modules]([Code],[Name])
	VALUES('ladle_timing_report','Ladle Timing Report')

	SET @ModuleId = @@IDENTITY

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


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

---------------------
-- Stored Procedures
---------------------
USE [MES]
GO
/****** Object:  StoredProcedure [MES].[GetLadleTimingReport]    Script Date: 29 oct. 2021 14:28:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [MES].[GetLadleTimingReport]
	@start Datetime = NULL,
	@end Datetime = NULL,

	@timeZoneCode CHAR(10) = 'EDTIND',

	@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
	@sortType VARCHAR(50) = NULL -- To specify how to sort the data
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:'
		+ ', @start: ' + ISNULL(CAST(@start AS VARCHAR), '')
		+ ', @end: ' + ISNULL(CAST(@end AS VARCHAR), '')
		+ ', @timeZoneCode: ' + ISNULL(CAST(@timeZoneCode AS VARCHAR), '')
		+ ', @fromRow: ' + ISNULL(CAST(@fromRow AS VARCHAR), '')
		+ ', @toRow: ' + ISNULL(CAST(@toRow AS VARCHAR), '')
		+ ', @rowsToFetch: ' + ISNULL(CAST(@rowsToFetch AS VARCHAR), '')
		+ ', @sortType: ' + ISNULL(CAST(@sortType AS VARCHAR), '')


	-----------------------
	-- Parameters default
	-----------------------
	IF ((@rowsToFetch IS NULL)	OR (@rowsToFetch = - 1))
		SET @rowsToFetch = 100000

	IF (@fromRow IS NULL)
		SET @fromRow = 0

	IF @start is null
		set @start = DATEADD(DAY,-3,GETUTCDATE())
	IF @end is null
		set @end = GETUTCDATE()
	-----------------------
	-- Begin Query
	-----------------------

	select 
		[HeatName]			= case when ps.Id is not null then ps.HeatName else pd.HeatName	end			
		,[LadleNumber] = '1'
		,[Start] = CONVERT(VARCHAR, IH.ToLocal(pd.[Datetime], @timeZoneCode),120)
		,[TapCar] = '1'
		,[EAFTap] = '1'
		,[LDBay] = '1'
		,[LMF] = '1'
		,[Caster] = '1'
		,[LDTap] = '1'
		,[End] = CONVERT(VARCHAR, IH.ToLocal(pd.[CutComplete], @timeZoneCode),120) -- change
	INTO #temp
	from cas.Products pd
	left join cas.Processes ps on ps.Id = pd.ProcessId
	left join mes.heats h on h.Id = pd.heatId
	WHERE 
		pd.[Datetime] between @Start and DATEADD(DAY,1,@End)		
	order by pd.[Datetime] desc

	SELECT *
	FROM #temp record 
	ORDER BY CASE 
			WHEN @sortType IS NULL
				THEN record.[Start]
			END DESC OFFSET @fromRow ROWS

	FETCH NEXT @rowsToFetch ROWS ONLY

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

	RETURN 0
END
GO