


-----------------------------
-- read is alread created
USE [IHBoxSystem]
GO
CREATE ROLE mes_config_grades_read AUTHORIZATION dbo 
GO  


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

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

-----------------------------------


USE [IHBoxSystem]
GO
CREATE ROLE mes_config_grades_readWrite AUTHORIZATION dbo 
GO  

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

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


EXECUTE sys.sp_addextendedproperty @level0type = N'USER' ,@level0name = [mes_config_grades_read]		,@name = N'securedsp-CAT.GetGradeGroups'	,@value = N'' 
GO

EXECUTE sys.sp_addextendedproperty @level0type = N'USER' ,@level0name = [mes_config_grades_read]		,@name = N'securedsp-CAT.GetGrades'			,@value = N'' 
GO
EXECUTE sys.sp_addextendedproperty @level0type = N'USER' ,@level0name = [mes_config_grades_readWrite]	,@name = N'securedsp-CAT.UpsertGrade'		,@value = N'SendCurrentUser=TRUE'
GO
EXECUTE sys.sp_addextendedproperty @level0type = N'USER' ,@level0name = [mes_config_grades_readWrite]	,@name = N'securedsp-CAT.RemoveGrade'		,@value = N'SendCurrentUser=TRUE'
GO
EXECUTE sys.sp_addextendedproperty @level0type = N'USER' ,@level0name = [mes_config_grades_read]		,@name = N'securedsp-CAT.GetGradeChemistry' ,@value = N'' 
GO
EXECUTE sys.sp_addextendedproperty @level0type = N'USER' ,@level0name = [mes_config_grades_read]		,@name = N'securedsp-CAT.GetScrapRecipes'	,@value = N'' 
GO
EXECUTE sys.sp_addextendedproperty @level0type = N'USER' ,@level0name = [mes_config_grades_readWrite]	,@name = N'securedsp-CAT.CreateGradeSpec'	,@value = N'SendCurrentUser=TRUE' 
GO





--.CAT.GetGrades
--.CAT.UpsertGrade
--.CAT.RemoveGrade
--.CAT.GetGradeChemistry
--.CAT.GetScrapRecipes
--.CAT.GetGradeGroups
--.CAT.CreateGradeSpec



-------------------
--	Profiles
-------------------


update  [SECURITY].[ModuleActions] 
	set Code = 'read'
	,[Name] = 'Read'
where [Role] = 'mes_config_grades_read'


	USE [IHBoxSystem]
	GO

	DECLARE @ModuleId INT = (select Id from [SECURITY].[Modules] where [Name] = 'MES Configuration - Grades')

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














/*SAME Screen under LAB menu*/
declare @parent int, @result_routeId INT
select @parent=id from [FrontEnd].[Menu] where name='LAB'

INSERT INTO [FrontEnd].[Routes]
		   ([Route]
		   ,[ModuleName]
		   ,[ModulePath]
		   ,[RequiredRole]
	   )
	 VALUES
		   (
		   '!/grades(/)'
		   , 'grades'
		   , 'custom-screens/grades/'
		   , 'mes_config_grades_read'           
		   )
		   SET @result_routeId = @@IDENTITY

INSERT INTO [FrontEnd].[Menu]
		   ([Parent]
		   ,[Name]
		   ,[RouteID]
		   ,[SortOrder]
		   ,[Enabled]
		   ,[Visible]
		   ,[RequiredRole]
		   ,[Group]
		  )
	 VALUES
		   (
		   @parent
		   ,'Grades and Chemistries'
		   ,@result_routeId 
		   ,1
		   ,1
		   ,1
		   ,'mes_config_grades_read'
		   ,NULL
		 )
GO




/*----------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------*/


USE [IHBoxSystem]
GO
EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [mes_config_grades_read] -- The role
,@name = N'securedsp-CAT.GetGradeGroups' -- the procedure
,@value = N'' 
GO
USE [IHBoxSystem]
GO
EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [mes_config_grades_read] -- The role
,@name = N'securedsp-CAT.GetGradeChemistry' -- the procedure
,@value = N'' 
GO








USE [MES]
GO
/****** Object:  StoredProcedure [CAT].[UpsertGrade]    Script Date: 1 feb. 2021 22:16:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [CAT].[UpsertGrade]
	@id INT = NULL, 
	@name VARCHAR(50) = NULL,
	@description VARCHAR(200) = NULL,
	@gradeGroupId INT = 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,
		@curDescription VARCHAR(200),
		@curName VARCHAR(50),
		@currGradeGroupId 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,
			@curDescription = [Description],
			@curName = [Name],
			@currGradeGroupId = GradeGroupId
		FROM CAT.Grades
		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.Grades AS TARGET
		USING (SELECT
			Id = @id, 
			Active = @active,
			[Description] = @description,
			[Name] = @name,
			[GradeGroupId] = @gradeGroupId
		) AS SOURCE
		ON TARGET.Id = SOURCE.Id
		WHEN MATCHED THEN
			UPDATE SET 
			TARGET.Active = ISNULL(SOURCE.Active, TARGET.Active),
			TARGET.[Description] = ISNULL(SOURCE.[Description], TARGET.[Description]),
			TARGET.Name = ISNULL(SOURCE.Name, TARGET.Name),
			TARGET.[GradeGroupId] = ISNULL(SOURCE.[GradeGroupId], TARGET.[GradeGroupId])
		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.Grades
			SET 
				Active = CASE WHEN (@active IS NOT NULL) THEN @active ELSE Active END,
				[Description] = CASE WHEN (@description IS NOT NULL) THEN @description ELSE [Description] 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 ((@description IS NOT NULL) AND (@description <> ISNULL(@curDescription, ''))) SET @msg = @msg + CASE WHEN (@msg <> '') THEN ',' ELSE '' END + ' "Description" changed from "' + ISNULL(CAST(@curDescription AS VARCHAR), 'null') + '" to "' + ISNULL(CAST(@description 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 '
						-- 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') + '"'
			IF (@description IS NOT NULL)
				SET @msg = @msg + CASE WHEN (@msg <> '') THEN ',' ELSE '' END + ' Description: "' + ISNULL(CAST(@description 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


/*--------------------------------------------------------------------*/
/*--------------------------------------------------------------------*/
/*--------------------------------------------------------------------*/
/*--------------------------------------------------------------------*/
/*------------------------------14 Sep 2021---------------------------*/
/*--------------------------------------------------------------------*/
/*--------------------------------------------------------------------*/
/*--------------------------------------------------------------------*/
/*--------------------------------------------------------------------*/


ALTER TABLE CAT.Grades
ADD ScrapRecipeId INT;

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


ALTER TABLE CAT.GradeGroups
ADD ScrapRecipeName VARCHAR(MAX);


update gr
	set ScrapRecipeName = (select top 1 z.[Name] from scrap.ScrapRecipes z where z.Id =gr.ScrapRecipeId)
from CAT.GradeGroups gr

/*------------------------------- 22 Nov 2021 ------------------------------------------*/
/*------------------------------- 22 Nov 2021 ------------------------------------------*/
/*------------------------------- 22 Nov 2021 ------------------------------------------*/
/*------------------------------- 22 Nov 2021 ------------------------------------------*/
/*------------------------------- 22 Nov 2021 ------------------------------------------*/
/*------------------------------- 22 Nov 2021 ------------------------------------------*/
/*------------------------------- 22 Nov 2021 ------------------------------------------*/
/*------------------------------- 22 Nov 2021 ------------------------------------------*/

create table CAT.ChemistryElementOrder(
	[Order] int identity(1,1) not null
	,[ElementCode] varchar(10)
)



insert into CAT.ChemistryElementOrder
values

 ((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'C'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'Mn'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'S'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'P'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'Si'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'Cu'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'Ni'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'Cr'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'Mo'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'Sn'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'Nb'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'V'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'N'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'Al'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'B'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'Ca'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'Pb'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'Ti'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'FP'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'Zn'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'Sb'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'As'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'Co'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'Zr'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'W'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'Ta'))

,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'Bi'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'Se'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'Te'))
,((select top 1 ElementCode from CAT.GradeSpecs where ElementCode = 'Mg'))



insert into CAT.ChemistryElementOrder
select distinct(ElementCode) from CAT.GradeSpecs where ElementCode not in ( select ElementCode from  CAT.ChemistryElementOrder)