USE [IHBoxSystem]
GO
CREATE ROLE eafsparescatalog_readWrite AUTHORIZATION dbo 
GO  

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

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

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

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


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

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [eafsparescatalog_readWrite] -- The role
,@name = N'securedsp-EAF.RemoveEquipment' -- 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-spares-catalog(/:equipmentTypeId)'
           , 'eaf-spares-catalog'
		   , 'custom-screens/eaf-spares-catalog/'
           , 'eafsparescatalog_readWrite'           
		   )
		   SET @result_routeId = @@IDENTITY
INSERT INTO [FrontEnd].[Menu]
           ([Parent]
           ,[Name]
           ,[RouteID]
           ,[SortOrder]
           ,[Enabled]
           ,[Visible]
           ,[RequiredRole]
           ,[Group]
          )
     VALUES
           (
		   @parent
		   ,'EAF Spares Catalog'
           ,@result_routeId 
           ,3
           ,1
           ,1
           ,'eafsparescatalog_readWrite'
           ,NULL
         )
GO


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

DECLARE @ModuleId INT

INSERT INTO [SECURITY].[Modules]
           ([Code]
           ,[Name])
     VALUES
           ('eaf_spares_catalog'
           ,'EAF Spares Catalog')

		  SET @ModuleId = @@IDENTITY

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

/*-----------------------------------------------------------------------GET-------------------------------------------------------------------------------*/
USE [MES]
GO
/****** Object:  StoredProcedure [EAF].[GetEquipmentsByType]    Script Date: 4 ene. 2020 16:52:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [EAF].[GetEquipmentsByType]
	@EquipmentTypeId 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] = e.Id,
		[EquipmentName] = e.[Name],
		[EquipmentTypeId]= et.Id,
		[EquipmentTypeName] = et.[Name],
		[Status] =case when elc.equipmentLocationid is null then 'Available' else 'installed in '+ cast(elc.furnacecode as varchar) + ' ' + el.[Name] + et.[Name] end,
		[LocationId] = el.Id
	FROM EAF.catEquipments e
	
	LEFT JOIN EAF.EquipmentLastChanges elc on e.id = elc.EquipmentId
	LEFT JOIN EAF.ComponentChangeEvents cce on elc.LastChangeid = cce.id
	
	LEFT JOIN EAF.catEquipmentTypes et on e.EquipmentTypeId= et.id
	LEFT JOIN EAF.catEquipmentLocations el on cce.EquipmentLocationId = el.Id
	WHERE @EquipmentTypeId IS NULL OR e.EquipmentTypeId = @EquipmentTypeId 
	ORDER BY e.EquipmentTypeId
	

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

	RETURN 0
END


GO 
/*-----------------------------------------------------------------------Upsert-------------------------------------------------------------------------------*/

USE [MES]
GO
/****** Object:  StoredProcedure [EAF].[UpsertEquipment]    Script Date: 6 ene. 2020 09:44:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [EAF].[UpsertEquipment]
	@EquipmentName  VARCHAR(MAX) = NULL,
	@EquipmentId  INT = NULL,
	@EquipmentTypeId INT = NULL,
	@NewEquipment BIT,
	@CurrentUser VARCHAR(50) -- auto included by 4i
AS
BEGIN

	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--This prefix is used on all the prints of the procedure to tabulate them depending
		--its nested level.
		@printPrefix VARCHAR(200),
		--The name of the procedure with its schema.
		@procName VARCHAR(300),
		--The result of the procedure. If all goes ok it will be 0, else it will contain the code
		--of the error that has been thrown.
		@result INT,

		@NewEquipmentId INT, @Call VARCHAR(MAX), @msg VARCHAR(MAX)


	SELECT
		@procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID),
		@printPrefix = REPLICATE('    ', @@NESTLEVEL - 1),
		@result = 0
	
	SET @finishEndMSG =  @procName + ' finished.'
	

	SET @Call = @procName + ' Parameters:'
		+ '  @EquipmentName:' + ISNULL(CAST(@EquipmentName	AS VARCHAR), '')
		+ ', @EquipmentTypeId:' + ISNULL(CAST(@EquipmentTypeId	 AS VARCHAR), '')
		+ ', @EquipmentId:' + ISNULL(CAST(@EquipmentId AS VARCHAR), '')
		+ ', @IsNewEquipment:' + ISNULL(CAST(@NewEquipment	 AS VARCHAR), '')
		+ ', @CurrentUser: ' + ISNULL(CAST(@CurrentUser AS VARCHAR), '')

	-----------------------
	-- Validation
	-----------------------


	-----------------------
	-- Transaction variables
	-----------------------
	IF (@NewEquipment = 0)
	BEGIN
		UPDATE [EAF].[catEquipments]
			SET [Name] = @EquipmentName
		WHERE Id = @EquipmentId
		SET @msg = 'Update'
	END
	ELSE IF (@NewEquipment = 1)
	BEGIN
		INSERT INTO [EAF].[catEquipments]
			   ([Name]
			   ,[EquipmentTypeId]
			   ,[IdFromPLC])
		 VALUES
			   (@EquipmentName
			   ,@EquipmentTypeId
			   ,NULL
			   )
		SET @NewEquipmentId = @@IDENTITY
		SET @msg = 'INSERT: ' + '@NewEquipmentId: ' + @NewEquipmentId
	END
	-----------------------
	-- AUDIT LOG
	-----------------------
	BEGIN TRY
		DECLARE @moduleName VARCHAR(200)

		SET @moduleName = 'UpsertEquipment'
		SET @msg = @msg + ' ' + @Call

		EXEC [SYSTEM].AuditMsg 
			 @message = @msg
			,@moduleName = @moduleName
			,@currentUser = @currentUser
	END TRY

	BEGIN CATCH
		PRINT ERROR_MESSAGE() + ': Error on message audit.'
	END CATCH

	-----------------------
	-- Finish procedure
	-----------------------
	IF (@result = 0)
	BEGIN
		PRINT @printPrefix + @finishEndMSG
	END
END
GO

/*-----------------------------------------------------------------------Remove-------------------------------------------------------------------------------*/
USE [MES]
GO
/****** Object:  StoredProcedure [EAF].[UpsertEquipment]    Script Date: 6 ene. 2020 09:44:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [EAF].[RemoveEquipment]
	@EquipmentId  INT = NULL,
	@CurrentUser VARCHAR(50) -- auto included by 4i
AS
BEGIN

	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--This prefix is used on all the prints of the procedure to tabulate them depending
		--its nested level.
		@printPrefix VARCHAR(200),
		--The name of the procedure with its schema.
		@procName VARCHAR(300),
		--The result of the procedure. If all goes ok it will be 0, else it will contain the code
		--of the error that has been thrown.
		@result INT,

		@NewEquipmentId INT, @Call VARCHAR(MAX), @msg VARCHAR(MAX),@previousData VARCHAR(MAX)


	SELECT
		@procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID),
		@printPrefix = REPLICATE('    ', @@NESTLEVEL - 1),
		@result = 0
	
	SET @finishEndMSG =  @procName + ' finished.'
	

	SET @Call = @procName + ' Parameters:'
		+ ', @EquipmentId:' + ISNULL(CAST(@EquipmentId	 AS VARCHAR), '')
		+ ', @CurrentUser: ' + ISNULL(CAST(@CurrentUser AS VARCHAR), '')

	-----------------------
	-- Validation
	-----------------------

	-----------------------
	-- Transaction variables
	-----------------------
	SELECT
	@previousData = 'Id: ' + ISNULL(CAST(ce.Id AS VARCHAR), 'null') +
					'EquipmentTypeId: ' + ISNULL(CAST(ce.EquipmentTypeId AS VARCHAR), 'null') +
					'IdFromPLC: ' + ISNULL(CAST(ce.IdFromPLC AS VARCHAR), 'null') +
					'Name: ' + ISNULL(CAST(ce.[Name] AS VARCHAR), 'null') 
	FROM [EAF].[catEquipments] ce
	WHERE Id = @EquipmentId


	DELETE FROM [EAF].[catEquipments]
		WHERE Id = @EquipmentId
	-----------------------
	-- AUDIT LOG
	-----------------------
	BEGIN TRY
		DECLARE @moduleName VARCHAR(200)

		SET @moduleName = 'RemoveEquipment'
		SET @msg = @msg + ' '  + 'Previous Data: ' + @previousData + ' ' + @Call

		EXEC [SYSTEM].AuditMsg 
			 @message = @msg
			,@moduleName = @moduleName
			,@currentUser = @currentUser
	END TRY

	BEGIN CATCH
		PRINT ERROR_MESSAGE() + ': Error on message audit.'
	END CATCH

	-----------------------
	-- Finish procedure
	-----------------------
	IF (@result = 0)
	BEGIN
		PRINT @printPrefix + @finishEndMSG
	END
END
GO
/*-----------------------------------------------------------------------Pickers-------------------------------------------------------------------------------*/
USE [MES]
GO
/****** Object:  StoredProcedure [EAF].[GetEquipmentChangesPickers]    Script Date: 3 ene. 2020 16:16:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [EAF].[GetEquipmentTypesPicker]
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 
		*
	FROM [eaf].catEquipmentTypes


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

	RETURN 0
END
GO


/*---------------------------------------------------------------EAF - Equipments - Menu -----------------------------------------------------------------------------------------------*/

USE IHBoxSystem

declare @parent int, @EquipmentsParent INT
select @parent=id from [FrontEnd].[Menu] where name='EAF'
INSERT INTO [FrontEnd].[Menu]
           ([Parent]
           ,[Name]
           ,[RouteID]
           ,[SortOrder]
           ,[Enabled]
           ,[Visible]
           ,[RequiredRole]
           ,[Group]
          )
     VALUES
           (
		   @parent
		   ,'Equipments'
           ,NULL
           ,3
           ,1
           ,1
           ,NULL
           ,NULL
         )
		 SET @EquipmentsParent = @@IDENTITY


		 
UPDATE [FrontEnd].[Menu]
   SET [Parent] = @EquipmentsParent
 WHERE Id = (select top 1 Id from [FrontEnd].[Menu] where name='EAF Spares Catalog')


UPDATE [FrontEnd].[Menu]
   SET [Parent] = @EquipmentsParent
 WHERE Id = (select top 1 Id from [FrontEnd].[Menu] where name='EAF Equipment Events')

 UPDATE [FrontEnd].[Menu]
   SET [Parent] = @EquipmentsParent
 WHERE Id = (select top 1 Id from [FrontEnd].[Menu] where name='EAF Change Equipment')
GO