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

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

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


	CREATE ROLE mes_config_electrode_vendors_readWrite AUTHORIZATION dbo 
	GO  

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

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


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

	INSERT INTO [FrontEnd].[Routes]
			   ([Route]
			   ,[ModuleName]
			   ,[ModulePath]
			   ,[RequiredRole]
		   )
		 VALUES 
			   (
			   '!/electrode-vendors(/)'
			   , 'electrode-vendors'
			   , 'custom-screens/electrode-vendors/'
			   , 'mes_config_electrode_vendors_readWrite'           
			   )
			   SET @result_routeId = @@IDENTITY
	INSERT INTO [FrontEnd].[Menu]
			   ([Parent]
			   ,[Name]
			   ,[RouteID]
			   ,[SortOrder]
			   ,[Enabled]
			   ,[Visible]
			   ,[RequiredRole]
			   ,[Group]
			  )
		 VALUES
			   (
			   @parent
			   ,'Electrode Vendors'
			   ,@result_routeId 
			   ,1
			   ,1
			   ,1
			   ,'mes_config_electrode_vendors_readWrite'
			   ,NULL
			 )
	GO


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

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

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




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

	DECLARE @ModuleId INT

	INSERT INTO [SECURITY].[Modules]([Code],[Name])
	VALUES('electrode_vendors','Electrode Vendors')

	SET @ModuleId = @@IDENTITY

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


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



-------------------------------------------------------------------
-- Steps to follow to add a new screen in MES Configuration Section
-------------------------------------------------------------------

update FrontEnd.Menu
set [Code]='electrode-vendors',
IHBoxModule = 'MES',
[RouteId]= 9
where [Name] = 'Electrode Vendors'

update FrontEnd.[Routes]
set [Route] = 'electrode-vendors',
[Group] = 'configuration-dashboard'
where [ModuleName] = 'electrode-vendors'


 insert into [FrontEnd].MenuProperties(MenuId,[Name],[Value])
 values(
 (select top 1 Id from FrontEnd.Menu where [Name] = 'Electrode Vendors'),('data-screenid'),('electrode-vendors')
)



 insert into [FrontEnd].[MenuLinkParameters]([MenuLinkID],[Name],[Value])
 values(
 (select top 1 Id from FrontEnd.Menu where [Name] = 'Electrode Vendors'),('section'),('electrode-vendors')
)



--------------------------------------
--- Stored Procedures
-------------------------------------
USE [MES]
GO
/****** Object:  StoredProcedure [CAT].[GetElectrodeVendors]    Script Date: 23 sep. 2021 13:20:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [CAT].[GetElectrodeVendors]
	@id INT = NULL,

	@onlyActives BIT = 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
	PRINT OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID) + ' Parameters:'
		+ '@id: ' + ISNULL(CAST(@id AS VARCHAR), '')
		+ ', @onlyActives: ' + ISNULL(CAST(@onlyActives AS VARCHAR), '')
		+ ', @fromRow: ' + ISNULL(CAST(@fromRow AS VARCHAR), '')
		+ ', @toRow: ' + ISNULL(CAST(@toRow AS VARCHAR), '')
		+ ', @rowsToFetch: ' + ISNULL(CAST(@rowsToFetch AS VARCHAR), '')


	-----------------------
	-- Parameters default
	-----------------------
	IF (@onlyActives IS NULL)
		SET @onlyActives = 1

	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


	-----------------------
	-- Begin Query
	-----------------------
	SELECT 
		* 
	FROM (SELECT
		cat.*,
			
		RowNum = ROW_NUMBER() OVER (ORDER BY cat.Name ASC)
	FROM [CAT].[ElectrodeVendors] cat
	WHERE
		(
			@id IS NOT NULL
			AND
			cat.Id = @id
		)
		OR
		(
			@id IS NULL
			AND
			(
				cat.IsActive = 1
				OR
				@onlyActives = 0
			)
		)
	)  AS record
	WHERE
		@rowsToFetch = -1
		OR
		(
			record.RowNum >= @fromRow
			AND 
			record.RowNum <= @toRow
		)
	ORDER BY 
		record.RowNum
END
GO


USE [MES]
GO
/****** Object:  StoredProcedure [CAT].[RemoveElectrodeVendors]    Script Date: 23 sep. 2021 13:21:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [CAT].[RemoveElectrodeVendors]
	@id INT,

	@currentUser VARCHAR(MAX) = NULL
AS
BEGIN
	--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.
	DECLARE @result INT

	SET @result = 0

	--Variables for current values for future audit.
	DECLARE 
		@curIsActive BIT,
		@curName VARCHAR(50) = NULL,
		@curL1Code INT = NULL
	
	SELECT 
		TOP 1 
		@curIsActive = IsActive,
		@curL1Code = L1Code,
		@curName = Name
	FROM CAT.ElectrodeVendors
	WHERE 
		Id = @id

	-- Use try/catch instead of @@ERROR variables because try/catch prevents raise error to IndustrialDashboard service
	-- which send on response all the errors and print messages instead of the first error message.
	BEGIN TRY
		DELETE FROM CAT.ElectrodeVendors WHERE Id = @id		

		SELECT Id = @id
	END TRY
	BEGIN CATCH
		SET @result = ERROR_NUMBER()

		SELECT [Message] = 'DB_ERROR_CAN_NOT_REMOVE_ITEM_USED'
	END CATCH


	-- Audit
	BEGIN TRY
		DECLARE @msg VARCHAR(MAX),
				@moduleName VARCHAR(200) 

		SELECT
			@moduleName = CAT.GetAuditModuleName(),
			@msg = ''


		SET @msg = @msg + CASE WHEN (@msg <> '') THEN ',' ELSE '' END + ' IsActive: "' + ISNULL(CAST(@curIsActive AS VARCHAR), 'null') + '"'
		SET @msg = @msg + CASE WHEN (@msg <> '') THEN ',' ELSE '' END + ' L1Code: "' + ISNULL(CAST(@curL1Code AS VARCHAR), 'null') + '"'
		

		SET @msg = 'Electrode Vendors '
					-- First fields that make it unique.
					+ '"' + ISNULL(CAST(@curName AS VARCHAR), 'null') + '" ' 
					+ CASE WHEN (@result = 0) THEN 'deleted' ELSE 'failed to be deleted' END
					-- Extra fields if exist.
					+ CASE WHEN (@msg <> '') THEN ':' + @msg + '.' ELSE '.' END

		EXEC SYSTEM.AuditMsg @message = @msg, @moduleName = @moduleName, @currentUser = @currentUser
	END TRY
	BEGIN CATCH
		PRINT 'Error on message audit.'
	END CATCH

	RETURN @result
END
GO



USE [MES]
GO
/****** Object:  StoredProcedure [CAT].[UpsertElectrodeVendors]    Script Date: 23 sep. 2021 13:21:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [CAT].[UpsertElectrodeVendors]
	@id INT = NULL, 
	@name VARCHAR(50) = NULL,
	@L1Code INT = NULL,
	@IsActive BIT = NULL,

	@currentUser VARCHAR(MAX) = NULL

AS
BEGIN
	--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.
	DECLARE @result INT

	SET @result = 0


	--Variables for current values for future audit.
	DECLARE 
		@curL1Code INT = NULL,
		@curName VARCHAR(50) = NULL,
		@curIsActive BIT = NULL

	DECLARE @isNew BIT

	SET @isNew = 0


	-- Get current values if it is an updated for future audit.
	IF (@id IS NOT NULL)
	BEGIN
		SELECT
			@curIsActive = IsActive,
			@curL1Code = L1Code,
			@curName = Name
		FROM CAT.ElectrodeVendors
		WHERE
			Id = @id
	END
	ELSE
	BEGIN
		SET @isNew = 1
	END

	-- Use try/catch instead of @@ERROR variables because try/catch prevents raise error to IndustrialDashboard service
	-- which send on response all the errors and print messages instead of the first error message.
	BEGIN TRY
		IF (EXISTS(SELECT * FROM CAT.ElectrodeVendors WHERE Name = @name AND (@id IS NULL OR Id <> @id)))
		BEGIN
			RAISERROR('ITEM_ALREADY_EXISTS', 11, 1)
		END

		IF ((@name IS NULL) OR (@name = ''))
		BEGIN
			RAISERROR('PARAM_NAME_CAN_NOT_BE_NULL', 11, 1)
		END

		DECLARE @idsT TABLE (Id INT)


		MERGE INTO CAT.ElectrodeVendors AS TARGET
		USING (SELECT
			Id = @id, 
			IsActive = @IsActive,
			L1Code = @L1Code,
			[Name] = @name
		) AS SOURCE
		ON TARGET.Id = SOURCE.Id
		WHEN MATCHED THEN
			UPDATE SET 
			TARGET.IsActive = ISNULL(SOURCE.IsActive, TARGET.IsActive),
			TARGET.L1Code = ISNULL(SOURCE.L1Code, TARGET.L1Code),
			TARGET.Name = ISNULL(SOURCE.Name, TARGET.Name)
		WHEN NOT MATCHED THEN
			INSERT (
				Name,
				L1Code,
				IsActive

			) 
			VALUES (
				SOURCE.Name,
				SOURCE.L1Code,
				SOURCE.IsActive
			)
		OUTPUT inserted.Id 
		INTO @idsT;


		-- Do update of not necessary columns after inserted to get default values on insert.
		-- Else it will break if any column which does not allow null receives a null in its parameter.
		IF (@id IS NULL)
		BEGIN
			SET @id = (SELECT TOP 1 Id FROM @idsT)

			UPDATE CAT.ElectrodeVendors
			SET 
				IsActive = CASE WHEN (@IsActive IS NOT NULL) THEN @IsActive ELSE IsActive END,
				L1Code = CASE WHEN (@L1Code IS NOT NULL) THEN @L1Code ELSE L1Code END,
				[Name] = CASE WHEN (@name IS NOT NULL) THEN @name ELSE Name END
			WHERE
				Id = @id
		END

		-- Finish procedure
		SELECT Id = @id
	END TRY
	BEGIN CATCH
		SET @result = ERROR_NUMBER()

		SELECT [Message] = ERROR_MESSAGE()
	END CATCH



	-- Audit
	BEGIN TRY
		DECLARE @msg VARCHAR(MAX),
				@moduleName VARCHAR(200) 

		SELECT
			@moduleName = CAT.GetAuditModuleName(),
			@msg = ''


		IF (@isNew = 0)
		BEGIN
			IF ((@IsActive IS NOT NULL) AND (@IsActive <> ISNULL(@curIsActive, ''))) SET @msg = @msg + CASE WHEN (@msg <> '') THEN ',' ELSE '' END + ' "Active" changed from "' + ISNULL(CAST(@curIsActive AS VARCHAR), 'null') + '" to "' + ISNULL(CAST(@IsActive AS VARCHAR), 'null') + '"'
			IF ((@name IS NOT NULL) AND (@name <> ISNULL(@curName, ''))) SET @msg = @msg + CASE WHEN (@msg <> '') THEN ',' ELSE '' END + ' "Name" changed from "' + ISNULL(@curName, 'null') + '" to "' + ISNULL(@name, 'null') + '"'
			IF ((@L1Code IS NOT NULL) AND (@L1Code <> ISNULL(@curL1Code, ''))) SET @msg = @msg + CASE WHEN (@msg <> '') THEN ',' ELSE '' END + ' "Mail" changed from "' + ISNULL(@curL1Code, 'null') + '" to "' + ISNULL(@L1Code, 'null') + '"'
		

			SET @msg = 'Electrode Vendors '
						-- First fields that make it unique.
						+ '"' + ISNULL(@curName, 'null') + '" '
						+ CASE WHEN (@result = 0) THEN 'modified' ELSE 'failed to be modified' END
						-- Extra fields if exist.
						+ CASE WHEN (@msg <> '') THEN ':' + @msg + '.' ELSE '.' END
		END
		ELSE
		BEGIN
			IF (@Isactive IS NOT NULL)
				SET @msg = @msg + CASE WHEN (@msg <> '') THEN ',' ELSE '' END + ' Active: "' + ISNULL(CAST(@Isactive AS VARCHAR), 'null') + '"'
			IF (@L1Code IS NOT NULL)
				SET @msg = @msg + CASE WHEN (@msg <> '') THEN ',' ELSE '' END + ' Mail: "' + ISNULL(CAST(@L1Code AS VARCHAR), 'null') + '"'
			SET @msg = 'Electrode Vendors '
						-- First fields that make it unique.
						+ '"' + ISNULL(@name, 'null') + '" '
						+ CASE WHEN (@result = 0) THEN 'created' ELSE 'failed to be created' END
						-- Extra fields if exist.
						+ CASE WHEN (@msg <> '') THEN ':' + @msg + '.' ELSE '.' END
		END


		EXEC SYSTEM.AuditMsg @message = @msg, @moduleName = @moduleName, @currentUser = @currentUser
	END TRY
	BEGIN CATCH
		PRINT 'Error on message audit.'
	END CATCH


	RETURN @result
END
GO