USE [IHBoxSystem]
GO
CREATE ROLE eafequipmentevents_readWrite AUTHORIZATION dbo 
GO  

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

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

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [eafequipmentevents_readWrite] -- The role
,@name = N'securedsp-EAF.GetEquipmentLocationsPicker' -- the procedure
,@value = N'' 

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [eafequipmentevents_readWrite] -- The role
,@name = N'securedsp-EAF.GetEquipmentChangeEvents' -- the procedure
,@value = N''


EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [eafequipmentevents_readWrite] -- The role
,@name = N'securedsp-EAF.GetEquipmentsByLocation' -- the procedure
,@value = N''

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [eafequipmentevents_readWrite] -- The role
,@name = N'securedsp-EAF.ChangeComponentWeb' -- the procedure
,@value = N''

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [eafequipmentevents_readWrite] -- The role
,@name = N'securedsp-EAF.RemoveEvent' -- the procedure
,@value = N'SendCurrentUser=TRUE'

Go

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

INSERT INTO [FrontEnd].[Routes]
           ([Route]
           ,[ModuleName]
           ,[ModulePath]
           ,[RequiredRole]
       )
     VALUES
           (
		   '!/eaf-equipment-events(/:furnaceCode)(/:equipmentLocationId)'
           , 'eaf-equipment-events'
		   , 'custom-screens/eaf-equipment-events/'
           , 'eafequipmentevents_readWrite'           
		   )
		   SET @result_routeId = @@IDENTITY
INSERT INTO [FrontEnd].[Menu]
           ([Parent]
           ,[Name]
           ,[RouteID]
           ,[SortOrder]
           ,[Enabled]
           ,[Visible]
           ,[RequiredRole]
           ,[Group]
          )
     VALUES
           (
		   @parent
		   ,'EAF Equipment Events'
           ,@result_routeId 
           ,3
           ,1
           ,1
           ,'eafequipmentevents_readWrite'
           ,NULL
         )
GO


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

DECLARE @ModuleId INT

INSERT INTO [SECURITY].[Modules]
           ([Code]
           ,[Name])
     VALUES
           ('eaf_equipment_events'
           ,'EAF Equipment Events')

		  SET @ModuleId = @@IDENTITY

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

/*-----------------------------------------------------------------------GET-------------------------------------------------------------------------------*/
USE [MES]
GO
/****** Object:  StoredProcedure [EAF].[GetEquipmentChangeEvents]    Script Date: 7 ene. 2020 00:18:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [EAF].[GetEquipmentChangeEvents]
	@furnaceCode INT,
	@equipmentLocationId INT = 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),
		@buf NVARCHAR(max)

	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
		+ '  @furnaceCode:' + ISNULL(CAST(@furnaceCode AS VARCHAR), '')
		+ ', @equipmentLocationId:' + ISNULL(CAST(@equipmentLocationId AS VARCHAR), '')

	-----------------------
	-- Parameters default
	-----------------------

	-----------------------
	-- Begin Query
	-----------------------
	select *,
		[EventId] = cce.[Id],
		[EquipmentName] = ce.[Name]
	from eaf.ComponentChangeEvents cce
	LEFT JOIN eaf.catEquipments ce on ce.Id = cce.EquipmentID
	WHERE @furnaceCode IS NULL OR cce.FurnaceCode = @furnaceCode 
	AND (@equipmentLocationId IS NULL OR cce.EquipmentLocationId = @equipmentLocationId)
	
	order by cce.[DateTime] DESC



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

	RETURN 0
END


GO 
/*-----------------------------------------------------------------------Insert-------------------------------------------------------------------------------*/


GO

/*-----------------------------------------------------------------------Remove-------------------------------------------------------------------------------*/
USE [MES]
GO
/****** Object:  StoredProcedure [EAF].[ChangeComponentWeb]    Script Date: 7 ene. 2020 08:37:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [EAF].[RemoveEvent]
	@eventId INT,
	@CurrentUser VARCHAR(50) -- auto included by 4i
as
begin

	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		@procName VARCHAR(300),
		@result INT,
		@previousData VARCHAR(MAX)


	SELECT
		@procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID),
		@result = 0
	

	PRINT @procName + ' Parameters:'
		+ '@eventId: ' + ISNULL(CAST(@eventId AS VARCHAR), 'null')
		+ '@CurrentUser: ' + ISNULL(CAST(@CurrentUser AS VARCHAR), 'null')
				
	SELECT 
	@previousData = 'Id: ' + ISNULL(CAST(cce.Id AS VARCHAR), 'null') +
					'DateTime: ' + ISNULL(CAST(cce.[DateTime] AS VARCHAR), 'null') +
					'EquipmentID: ' + ISNULL(CAST(cce.EquipmentID AS VARCHAR), 'null') +
					'EquipmentLocationId: ' + ISNULL(CAST(cce.EquipmentLocationId AS VARCHAR), 'null') +
					'EventSourceCode: ' + ISNULL(CAST(cce.EventSourceCode AS VARCHAR), 'null') +
					'FurnaceCode: ' + ISNULL(CAST(cce.FurnaceCode AS VARCHAR), 'null') +
					'Comments: ' + ISNULL(CAST(cce.Comments AS VARCHAR), 'null')
	FROM [EAF].[ComponentChangeEvents] cce
	WHERE Id = @eventId	

	DELETE FROM [EAF].[ComponentChangeEvents]
	WHERE Id = @eventId			   

		
	BEGIN TRY
		DECLARE @msg varchar(max)

		SET @msg = 	@procName + 'Previous Data: ' + @previousData + ' Parameters:'
	+ '@eventId: ' + ISNULL(CAST(@eventId AS VARCHAR), 'null')

		EXEC SYSTEM.AuditMsg @message = @msg  
	END TRY
	BEGIN CATCH
		PRINT 'Error on message audit. ' + ERROR_MESSAGE()
	END CATCH
	

end
GO
/*-----------------------------------------------------------------------Pickers-------------------------------------------------------------------------------*/
USE [MES]
GO
/****** Object:  StoredProcedure [EAF].[GetEquipmentLocationsPicker]    Script Date: 6 ene. 2020 17:18:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [EAF].[GetEquipmentLocationsPicker]
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),
		@buf NVARCHAR(max)

	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
	-----------------------
	-- Parameters default
	-----------------------

	-----------------------
	-- Begin Query
	-----------------------
	select *,
	[EquipmentTypeName] = cet.[Name]
	from eaf.catEquipmentLocations cel
	left join eaf.catEquipmentTypes cet on cet.Id = cel.EquipmentTypeId

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

	RETURN 0
END
GO


USE [MES]
GO
/****** Object:  StoredProcedure [EAF].[GetEquipments]    Script Date: 8 ene. 2020 14:31:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [EAF].[GetEquipmentsByLocation]
 @EquipmentLocationId INT = 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),
		@buf NVARCHAR(max)

	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'

	-----------------------
	-- Parameters default
	-----------------------

	-----------------------
	-- Begin Query
	-----------------------
	
	select
	[EquipmentId] = ce.Id,
	[EquipmentName] = ce.[Name],
	[EquipmentTypeName] = cet.[Name]
	from eaf.catEquipments ce
	left join eaf.catEquipmentTypes cet on cet.Id = ce.EquipmentTypeId
	left join eaf.catEquipmentLocations cel on cel.EquipmentTypeId = cet.Id
	where cel.Id = @EquipmentLocationId
	   	 
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END
GO