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

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

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


	CREATE ROLE mes_config_grade_groups_readWrite AUTHORIZATION dbo 
	GO  

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

	USE [IHBoxSystem]
	GO
	EXEC sp_addrolemember 'mes_config_grade_groups_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 
			   (
			   '!/grade-groups(/)'
			   , 'grade-groups'
			   , 'custom-screens/grade-groups/'
			   , 'mes_config_grade_groups_readWrite'           
			   )
			   SET @result_routeId = @@IDENTITY
	INSERT INTO [FrontEnd].[Menu]
			   ([Parent]
			   ,[Name]
			   ,[RouteID]
			   ,[SortOrder]
			   ,[Enabled]
			   ,[Visible]
			   ,[RequiredRole]
			   ,[Group]
			  )
		 VALUES
			   (
			   @parent
			   ,'Grade Groups'
			   ,@result_routeId 
			   ,1
			   ,1
			   ,1
			   ,'mes_config_grade_groups_readWrite'
			   ,NULL
			 )
	GO


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

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

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




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

	DECLARE @ModuleId INT

	INSERT INTO [SECURITY].[Modules]([Code],[Name])
	VALUES('grade_groups','Grade Groups')

	SET @ModuleId = @@IDENTITY

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


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



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

update FrontEnd.Menu
set [Code]='grade-groups',
IHBoxModule = 'MES',
[RouteId]= 9
where [Name] = 'Grade Groups'

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


 insert into [FrontEnd].MenuProperties(MenuId,[Name],[Value])
 values(
 (select top 1 Id from FrontEnd.Menu where [Name] = 'Grade Groups'),('data-screenid'),('grade-groups')
)



 insert into [FrontEnd].[MenuLinkParameters]([MenuLinkID],[Name],[Value])
 values(
 (select top 1 Id from FrontEnd.Menu where [Name] = 'Grade Groups'),('section'),('grade-groups')
)



--------------------------------------
--- Stored Procedures
-------------------------------------
USE [MES]
GO
/****** Object:  StoredProcedure [CAT].[UpsertGrade]    Script Date: 2 feb. 2021 10:31:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [CAT].[UpsertGradeGroups]
	@id INT = NULL, 
	@name VARCHAR(50) = NULL,
	@active 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 
		@curActive BIT,
		@curName VARCHAR(50)
	DECLARE @isNew BIT

	SET @isNew = 0


	-- Get current values if it is an updated for future audit.
	IF (@id IS NOT NULL)
	BEGIN
		SELECT
			@curActive = Active,
			@curName = [Name]
		FROM CAT.GradeGroups
		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.Grades WHERE Name = @name AND (@id IS NULL OR Id <> @id)))
		BEGIN
			--SELECT [Message] = 'ITEM_ALREADY_EXISTS'
			--SET @result = 5000
			RAISERROR('ITEM_ALREADY_EXISTS', 11, 1)
			--SET @result = @@ERROR
		END

		DECLARE @idsT TABLE (Id INT)


		MERGE INTO CAT.GradeGroups AS TARGET
		USING (SELECT
			Id = @id, 
			Active = @active,
			[Name] = @name
		) AS SOURCE
		ON TARGET.Id = SOURCE.Id
		WHEN MATCHED THEN
			UPDATE SET 
			TARGET.Active = ISNULL(SOURCE.Active, TARGET.Active),
			TARGET.Name = ISNULL(SOURCE.Name, TARGET.Name)
		WHEN NOT MATCHED THEN
			INSERT (
				Name
			) 
			VALUES (
				SOURCE.Name
			)
		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.GradeGroups
			SET 
				Active = CASE WHEN (@active IS NOT NULL) THEN @active ELSE Active 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 ((@active IS NOT NULL) AND (@active <> ISNULL(@curActive, ''))) SET @msg = @msg + CASE WHEN (@msg <> '') THEN ',' ELSE '' END + ' "Active" changed from "' + ISNULL(CAST(@curActive AS VARCHAR), 'null') + '" to "' + ISNULL(CAST(@active 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') + '"'

			SET @msg = 'Grade Group'
						-- 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 (@active IS NOT NULL)
				SET @msg = @msg + CASE WHEN (@msg <> '') THEN ',' ELSE '' END + ' Active: "' + ISNULL(CAST(@active AS VARCHAR), 'null') + '"'
		
			SET @msg = 'Grade '
						-- 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


USE [MES]
GO
/****** Object:  StoredProcedure [CAT].[RemoveGrade]    Script Date: 2 feb. 2021 12:01:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [CAT].[RemoveGradeGroup]
	@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 
		@curActive BIT,
		@curName VARCHAR(50)

	SELECT 
		TOP 1 
		@curActive = Active,
		@curName = Name
	FROM CAT.GradeGroups
	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.GradeGroups 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 + ' Active: "' + ISNULL(CAST(@curActive AS VARCHAR), 'null') + '"'

		SET @msg = 'Grade Group'
					-- 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


/*----------------------------------------------------------------*/
CREATE TABLE CAT.SmartFurnaceProfiles(
	Id int identity(1,1) NOT NULL PRIMARY KEY
	,[Name] varchar(50) NOT NULL
	,[L1Index] int NOT NULL
)
GO

insert into CAT.SmartFurnaceProfiles
values
('Structural',1)
,('Rail',2)
,('SQB',3)
,('Pour Back',4)
,('Cold EAF',5)
GO

CREATE PROCEDURE [CAT].[GetSmartFurnaceProfiles]
AS
BEGIN
	select * from CAT.SmartFurnaceProfiles
END

GO

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

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





ALTER TABLE cat.GradeGroups ADD SmartFurnaceProfileId INT NULL;
ALTER TABLE cat.GradeGroups
ADD FOREIGN KEY (SmartFurnaceProfileId) REFERENCES CAT.SmartFurnaceProfiles(Id);


GO
CREATE PROCEDURE [CAT].[GetScrapRecipes]
AS
BEGIN
	  declare @ScrapRecipes table (Id int, [Name] varchar(100))
	  
	  insert into @ScrapRecipes([Name])
	  select distinct([Name])
	  FROM [SCRAP].[ScrapRecipes]
	  Where [Enabled] = 1


	  update SR 
		set SR.Id = (select top 1 z.Id from SCRAP.ScrapRecipes z where z.[Name] = SR.[Name] and z.[Enabled] = 1 order by z.[Version] desc )
	  from @ScrapRecipes SR

	  select * from @ScrapRecipes

END
GO


ALTER TABLE cat.GradeGroups ADD ScrapRecipeId INT NULL;
ALTER TABLE cat.GradeGroups
ADD FOREIGN KEY (ScrapRecipeId) REFERENCES [SCRAP].[ScrapRecipes](Id);



USE [MES]
GO
/****** Object:  StoredProcedure [CAT].[GetGradeGroups]    Script Date: 11 jun. 2021 09:37:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [CAT].[GetGradeGroups]
AS
BEGIN
	select 
	g.*
	,[SmartFurnaceProfileName] = s.[Name]
	,[SmartFurnaceProfileId] = s.[Id]
	,[RecipeId] = r.Id
	,[RecipeName] = r.[Name]
	from cat.GradeGroups g
	left join cat.SmartFurnaceProfiles s on s.Id = g.SmartFurnaceProfileId
	left join scrap.ScrapRecipes r on r.Id = g.ScrapRecipeId
END


GO

USE [MES]
GO
/****** Object:  StoredProcedure [CAT].[UpsertGradeGroups]    Script Date: 11 jun. 2021 10:45:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [CAT].[UpsertGradeGroups]
	@id INT = NULL, 
	@name VARCHAR(50) = NULL,
	@active BIT = NULL,
	@smartId INT = NULL,
	@recipeId INT = NULL,

	@currentUser VARCHAR(MAX) = NULL
AS
BEGIN
	DECLARE @Codes TABLE (Code VARCHAR(50))
	--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 
		@curActive BIT,
		@curName VARCHAR(50),
		@curSmartFurnaceId INT,
		@curRecipeId INT
	DECLARE @isNew BIT

	SET @isNew = 0


	-- Get current values if it is an updated for future audit.
	IF (@id IS NOT NULL)
	BEGIN
		SELECT
			@curActive = Active,
			@curName = [Name],
			@curSmartFurnaceId = [SmartFurnaceProfileId],
			@curRecipeId = [ScrapRecipeId]
		FROM CAT.GradeGroups
		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.Grades WHERE Name = @name AND (@id IS NULL OR Id <> @id)))
		BEGIN
			--SELECT [Message] = 'ITEM_ALREADY_EXISTS'
			--SET @result = 5000
			RAISERROR('ITEM_ALREADY_EXISTS', 11, 1)
			--SET @result = @@ERROR
		END

		DECLARE @idsT TABLE (Id INT)


		MERGE INTO CAT.GradeGroups AS TARGET
		USING (SELECT
			Id = @id, 
			Active = @active,
			[Name] = @name
		) AS SOURCE
		ON TARGET.Id = SOURCE.Id
		WHEN MATCHED THEN
			UPDATE SET 
			TARGET.Active = ISNULL(SOURCE.Active, TARGET.Active),
			TARGET.Name = ISNULL(SOURCE.Name, TARGET.Name),
			TARGET.[SmartFurnaceProfileId] = @smartId,
			TARGET.[ScrapRecipeId] = @recipeId
		WHEN NOT MATCHED THEN
			INSERT (
				Name,
				Active,
				SmartFurnaceProfileId,
				ScrapRecipeId
			) 
			VALUES (
				SOURCE.Name,
				@active,
				@smartId,
				@recipeId

			)
		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.GradeGroups
			SET 
				Active = CASE WHEN (@active IS NOT NULL) THEN @active ELSE Active END,
				Name = CASE WHEN (@name IS NOT NULL) THEN @name ELSE Name END
			WHERE
				Id = @id

			INSERT INTO @Codes
			SELECT Distinct(Code) FROM CAT.GradeGroupAttributes

			INSERT INTO CAT.GradeGroupAttributes(GradeGroupId,Code,Value)
			SELECT 
				@id,
				Code,
				0
			FROM @Codes
		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 ((@active IS NOT NULL) AND (@active <> ISNULL(@curActive, ''))) SET @msg = @msg + CASE WHEN (@msg <> '') THEN ',' ELSE '' END + ' "Active" changed from "' + ISNULL(CAST(@curActive AS VARCHAR), 'null') + '" to "' + ISNULL(CAST(@active 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') + '"'

			SET @msg = 'Grade Group'
						-- 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 (@active IS NOT NULL)
				SET @msg = @msg + CASE WHEN (@msg <> '') THEN ',' ELSE '' END + ' Active: "' + ISNULL(CAST(@active AS VARCHAR), 'null') + '"'
		
			SET @msg = 'Grade '
						-- 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

/*----------------------------------------------------------28/10/2021---------------------------------------------------------------------------*/
/*----------------------------------------------------------28/10/2021---------------------------------------------------------------------------*/
/*----------------------------------------------------------28/10/2021---------------------------------------------------------------------------*/
/*----------------------------------------------------------28/10/2021---------------------------------------------------------------------------*/
/*----------------------------------------------------------28/10/2021---------------------------------------------------------------------------*/
/*----------------------------------------------------------28/10/2021---------------------------------------------------------------------------*/
/*----------------------------------------------------------28/10/2021---------------------------------------------------------------------------*/
/*----------------------------------------------------------28/10/2021---------------------------------------------------------------------------*/
/*----------------------------------------------------------28/10/2021---------------------------------------------------------------------------*/
/*----------------------------------------------------------28/10/2021---------------------------------------------------------------------------*/
/*----------------------------------------------------------28/10/2021---------------------------------------------------------------------------*/

ALTER TABLE CAT.GradeGroups
DROP CONSTRAINT FK__GradeGrou__Scrap__1D520E4A;


ALTER TABLE CAT.GradeGroups
DROP COLUMN ScrapRecipeId;


ALTER TABLE CAT.GradeGroups
DROP COLUMN ScrapRecipeName;


-- Remove Recipe data in : (3 SPs change)
-- [CAT].[UpsertGradeGroups] and [CAT].[GetGradeGroups]
-- In CAT.GetGrades the select option related with recipe is: --->[RecipeName] = cat.ScrapRecipeName,