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

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

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


	CREATE ROLE lfelectrodeevents_readWrite AUTHORIZATION dbo 
	GO  

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

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


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

	INSERT INTO [FrontEnd].[Routes]
			   ([Route]
			   ,[ModuleName]
			   ,[ModulePath]
			   ,[RequiredRole]
		   )
		 VALUES 
			   (
			   '!/lf-electrode-events(/:start)(/:end)(/:furnaceCode)(/:eventCode)(/:phaseCode)(/)'
			   , 'lf-electrode-events'
			   , 'custom-screens/lf-electrode-events/'
			   , 'lfelectrodeevents_read'           
			   )
			   SET @result_routeId = @@IDENTITY
	INSERT INTO [FrontEnd].[Menu]
			   ([Parent]
			   ,[Name]
			   ,[RouteID]
			   ,[SortOrder]
			   ,[Enabled]
			   ,[Visible]
			   ,[RequiredRole]
			   ,[Group]
			  )
		 VALUES
			   (
			   @parent
			   ,'Electrode Events'
			   ,@result_routeId 
			   ,1
			   ,1
			   ,1
			   ,'lfelectrodeevents_read'
			   ,NULL
			 )
	GO


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

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


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

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

	DECLARE @ModuleId INT

	INSERT INTO [SECURITY].[Modules]([Code],[Name])
	VALUES('lf_electrode_events','LF Electrode Events')

	SET @ModuleId = @@IDENTITY

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


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


	go

---------------------
-- Stored Procedure
---------------------

USE [MES]
GO
/****** Object:  StoredProcedure [EAF].[GetElectrodeEventsWeb]    Script Date: 22 dic. 2021 16:30:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [LF].[GetElectrodeEventsWeb]
	@start datetime = null,
	@end datetime = null,
	@furnaceCode int = NULL,
	@eventCode char(1) = NULL,
	@phase int = NULL,
	@timeZoneCode CHAR(10) = NULL
as
begin
	if @start is null
		set @start = DATEADD(DAY,-3,GETUTCDATE())
		
	if @end is null
		set @end = GETUTCDATE()

	if @timeZoneCode is NULL
		set @timeZoneCode = 'EDTIND'
			
	select 
		ee.*
		,[FurnaceName] = case when Furnace = 1 then 'A' else 'B' end
		,[PhaseName] = case when PhaseCode = 1 then 'A' when PhaseCode = 2 then 'B' else 'C' end
		,[ElectrodeEventName] = case when ElectrodeEventCode = 'A' then 'ADD' when ElectrodeEventCode = 'B' then 'BREAK' else 'SLIP' end
		,[DatetimeString] =  convert(varchar, IH.ToLocal([Datetime], @Timezonecode), 120)
		,[HeatName] = h.[Name]
		,[ArcLengthName] = al.[Name]
		,[ControlModeName] = fc.[Name]
		,[BreakLocationName] = bl.[Name]
		,[BreakReasonName] = br.[Name]
		,[VendorName] = v.[Name]
	from lf.electrodeevents ee
	left join mes.Heats h on ee.HeatId = h.Id
	left join cat.ElectrodeBreakArcLengths al on al.Id = ee.BreakArchLengthId
	left join cat.ElectrodeBreakFurnaceControlModes fc on fc.Id = ee.BreakControlModeId
	left join cat.ElectrodeBreakLocations bl on bl.Id = ee.BreakLocationId
	left join cat.ElectrodeBreakReasons br on br.Id = ee.ElectrodeBreakReasonId
	left join cat.ElectrodeVendors v on v.Id = ee.ElectrodeVendorId
	where
	FurnaceType = 'E' and
	(@furnaceCode is null or Furnace = @furnaceCode) and
	(@eventCode is null or ElectrodeEventCode = @eventCode) and
	(@phase is null or PhaseCode = @phase) and
	(ee.ProductionDate between @start and dateadd(DAY,1,@end))
	
end
go