USE [IHBoxSystem]
GO

---------------------------------------- CREATE ROLES
IF DATABASE_PRINCIPAL_ID('gradegroups_readWrite') IS NULL
BEGIN
	CREATE ROLE gradegroups_readWrite;
end
go
IF DATABASE_PRINCIPAL_ID('gradegroups_read') IS NULL
BEGIN
	CREATE ROLE gradegroups_read;
end

---------------------------------------- CREATE MODULE 
declare @id int
insert into [Security].[Modules] (Code,[Name])
values ('grade-groups' ,'Grade Groups')

set @id = @@IDENTITY

insert into [Security].[ModuleActions] (ModuleID,Code,[Name],[Role])
values (@id,'readWrite','Read/Write','gradegroups_readWrite')

insert into [Security].[ModuleActions] (ModuleID,Code,[Name],[Role])
values (@id,'read','Read','gradegroups_read')

EXEC sp_addrolemember 'gradegroups_readWrite', 'admin'
EXEC sp_addrolemember 'gradegroups_read', 'admin'



EXEC sp_addextendedproperty  @level0type = N'USER' ,@level0name = [gradegroups_read]		,@name = N'securedsp-CAT.GetGradeGroupAttributes'	,@value =  N'null'
EXEC sp_addextendedproperty  @level0type = N'USER' ,@level0name = [gradegroups_readWrite]	,@name = N'securedsp-CAT.GetGradeGroupAttributes'	,@value =  N'null'
EXEC sp_addextendedproperty  @level0type = N'USER' ,@level0name = [gradegroups_read]		,@name = N'securedsp-CAT.GetGradeGroups'			,@value =  N'null'
EXEC sp_addextendedproperty  @level0type = N'USER' ,@level0name = [gradegroups_readWrite]	,@name = N'securedsp-CAT.GetGradeGroups'			,@value =  N'null'
EXEC sp_addextendedproperty  @level0type = N'USER' ,@level0name = [gradegroups_read]		,@name = N'securedsp-CAT.UpdateGradeGroupAttributes',@value =  N'null'
EXEC sp_addextendedproperty  @level0type = N'USER' ,@level0name = [gradegroups_readWrite]	,@name = N'securedsp-CAT.UpdateGradeGroupAttributes',@value =  N'null'

GO



update FrontEnd.Menu
set RequiredRole = 'gradegroups_read'
where [Name] = 'KPI Variables'

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


--------------------------------------------------
--- 1 ----- CREATE CAT.GradeGroupAttributes table
-------------------------------------------------
USE [MES]
GO

/****** Object:  Table [CAT].[GradeGroupAttributes]    Script Date: 20 nov. 2020 12:13:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [CAT].[GradeGroupAttributes](
	[GradeGroupId] [int] NOT NULL,
	[Code] [varchar](50) NOT NULL,
	[Value] [float] NULL,
 CONSTRAINT [PK_CAT.GradeGroupAttributes] PRIMARY KEY CLUSTERED 
(
	[Code] ASC,
	[GradeGroupId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UK_CAT.GradeGroupAttributes_Code_GradeGroupId] UNIQUE NONCLUSTERED 
(
	[Code] ASC,
	[GradeGroupId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [CAT].[GradeGroupAttributes]  WITH CHECK ADD  CONSTRAINT [FK_CAT.GradeGroupAttributes_CAT.GradeGroups] FOREIGN KEY([GradeGroupId])
REFERENCES [CAT].[GradeGroups] ([Id])
GO

ALTER TABLE [CAT].[GradeGroupAttributes] CHECK CONSTRAINT [FK_CAT.GradeGroupAttributes_CAT.GradeGroups]
GO

---------------------------------------------------------------------------------
--- 2 ----- INSERT DATA FROM CAT.KpiVariables table INTO CAT.GradeGroupAttributes
---------------------------------------------------------------------------------


insert into cat.GradeGroupAttributes(GradeGroupId,Code,[Value]) 
select 
	(select top 1 Id from cat.GradeGroups where [Name] = 'Rail')  
	,Code
	,[Value]
from cat.KpiVariables  
where ProductTypeId = (select top 1 Id from cat.ProductTypes where [Name] = 'Rail')        

insert into cat.GradeGroupAttributes(GradeGroupId,Code,[Value]) 
select 
	(select top 1 Id from cat.GradeGroups where [Name] = 'Structural')  
	,Code
	,[Value]
from cat.KpiVariables  
where ProductTypeId = (select top 1 Id from cat.ProductTypes where [Name] = 'Structural')       

insert into cat.GradeGroupAttributes(GradeGroupId,Code,[Value]) 
select 
	(select top 1 Id from cat.GradeGroups where [Name] = 'SBQ')  
	,Code
	,[Value]
from cat.KpiVariables  
where ProductTypeId = (select top 1 Id from cat.ProductTypes where [Name] = 'SBQ')   

insert into cat.GradeGroupAttributes(GradeGroupId,Code,[Value]) 
select 
	(select top 1 Id from cat.GradeGroups where [Name] = 'Other')  
	,Code
	,[Value]
from cat.KpiVariables  
where ProductTypeId = (select top 1 Id from cat.ProductTypes where [Name] = 'Other')   


 ----------------------------
 --- 3 --- INSERT SMART CODES
 -----------------------------
 insert into cat.GradeGroupAttributes(GradeGroupId,Code)
values
 ((select top 1 Id from cat.gradeGroups where [Name] = 'Rail'),'SMART_ARC_B3_MAX_LIMIT'				)
,((select top 1 Id from cat.gradeGroups where [Name] = 'Rail'),'SMART_ARC_B3_MIN_LIMIT'				)
,((select top 1 Id from cat.gradeGroups where [Name] = 'Rail'),'SMART_ARC_AIM_TEMPERATURE_MAX_LIMIT')
,((select top 1 Id from cat.gradeGroups where [Name] = 'Rail'),'SMART_ARC_AIM_TEMPERATURE_MIN_LIMIT')
,((select top 1 Id from cat.gradeGroups where [Name] = 'Rail'),'SMART_ARC_O2PPM_MAX_LIMIT'			)
,((select top 1 Id from cat.gradeGroups where [Name] = 'Rail'),'SMART_ARC_O2PPM_MIN_LIMIT'			)
,((select top 1 Id from cat.gradeGroups where [Name] = 'Structural'),'SMART_ARC_B3_MAX_LIMIT'				)
,((select top 1 Id from cat.gradeGroups where [Name] = 'Structural'),'SMART_ARC_B3_MIN_LIMIT'				)
,((select top 1 Id from cat.gradeGroups where [Name] = 'Structural'),'SMART_ARC_AIM_TEMPERATURE_MAX_LIMIT'  )
,((select top 1 Id from cat.gradeGroups where [Name] = 'Structural'),'SMART_ARC_AIM_TEMPERATURE_MIN_LIMIT'  )
,((select top 1 Id from cat.gradeGroups where [Name] = 'Structural'),'SMART_ARC_O2PPM_MAX_LIMIT'			)
,((select top 1 Id from cat.gradeGroups where [Name] = 'Structural'),'SMART_ARC_O2PPM_MIN_LIMIT'		  	)
,((select top 1 Id from cat.gradeGroups where [Name] = 'Other'),'SMART_ARC_B3_MAX_LIMIT'			 )
,((select top 1 Id from cat.gradeGroups where [Name] = 'Other'),'SMART_ARC_B3_MIN_LIMIT'			 )
,((select top 1 Id from cat.gradeGroups where [Name] = 'Other'),'SMART_ARC_AIM_TEMPERATURE_MAX_LIMIT')
,((select top 1 Id from cat.gradeGroups where [Name] = 'Other'),'SMART_ARC_AIM_TEMPERATURE_MIN_LIMIT')
,((select top 1 Id from cat.gradeGroups where [Name] = 'Other'),'SMART_ARC_O2PPM_MAX_LIMIT'			 )
,((select top 1 Id from cat.gradeGroups where [Name] = 'Other'),'SMART_ARC_O2PPM_MIN_LIMIT'			 )
,((select top 1 Id from cat.gradeGroups where [Name] = 'SBQ'),'SMART_ARC_B3_MAX_LIMIT'				)
,((select top 1 Id from cat.gradeGroups where [Name] = 'SBQ'),'SMART_ARC_B3_MIN_LIMIT'				)
,((select top 1 Id from cat.gradeGroups where [Name] = 'SBQ'),'SMART_ARC_AIM_TEMPERATURE_MAX_LIMIT' )
,((select top 1 Id from cat.gradeGroups where [Name] = 'SBQ'),'SMART_ARC_AIM_TEMPERATURE_MIN_LIMIT' )
,((select top 1 Id from cat.gradeGroups where [Name] = 'SBQ'),'SMART_ARC_O2PPM_MAX_LIMIT'			)
,((select top 1 Id from cat.gradeGroups where [Name] = 'SBQ'),'SMART_ARC_O2PPM_MIN_LIMIT'			)

GO

 ----------------------------
 --- 3 --- SLAG CODES ONLY FOR SBQ AND OTHERS
 -----------------------------
 insert into cat.GradeGroupAttributes(GradeGroupId,Code)
values
 ((select top 1 Id from cat.gradeGroups where [Name] = 'SBQ'),'SLAG_B3_MAX_LIMIT' )
,((select top 1 Id from cat.gradeGroups where [Name] = 'SBQ'),'SLAG_B3_MIN_LIMIT' )
,((select top 1 Id from cat.gradeGroups where [Name] = 'SBQ'),'SLAG_FeO_MAX_LIMIT')
,((select top 1 Id from cat.gradeGroups where [Name] = 'SBQ'),'SLAG_FeO_MIN_LIMIT')
,((select top 1 Id from cat.gradeGroups where [Name] = 'SBQ'),'SLAG_MgO_MAX_LIMIT')
,((select top 1 Id from cat.gradeGroups where [Name] = 'SBQ'),'SLAG_MgO_MIN_LIMIT')
,((select top 1 Id from cat.gradeGroups where [Name] = 'Other'),'SLAG_B3_MAX_LIMIT' )
,((select top 1 Id from cat.gradeGroups where [Name] = 'Other'),'SLAG_B3_MIN_LIMIT' )
,((select top 1 Id from cat.gradeGroups where [Name] = 'Other'),'SLAG_FeO_MAX_LIMIT')
,((select top 1 Id from cat.gradeGroups where [Name] = 'Other'),'SLAG_FeO_MIN_LIMIT')
,((select top 1 Id from cat.gradeGroups where [Name] = 'Other'),'SLAG_MgO_MAX_LIMIT')
,((select top 1 Id from cat.gradeGroups where [Name] = 'Other'),'SLAG_MgO_MIN_LIMIT')



insert into cat.GradeGroupAttributes(GradeGroupId,Code)
values
(1,'SMARTARC_TEMPERATURE_AIM')
,(1,'SMARTARC_O2PPM_AIM')
,(1,'SMARTARC_B3RATIO_AIM')
,(1,'SMARTARC_FeO_AIM')
,(1,'SMARTARC_MgO_AIM')

,(2,'SMARTARC_TEMPERATURE_AIM')
,(2,'SMARTARC_O2PPM_AIM')
,(2,'SMARTARC_B3RATIO_AIM')
,(2,'SMARTARC_FeO_AIM')
,(2,'SMARTARC_MgO_AIM')

,(3,'SMARTARC_TEMPERATURE_AIM')
,(3,'SMARTARC_O2PPM_AIM')
,(3,'SMARTARC_B3RATIO_AIM')
,(3,'SMARTARC_FeO_AIM')
,(3,'SMARTARC_MgO_AIM')

,(4,'SMARTARC_TEMPERATURE_AIM')
,(4,'SMARTARC_O2PPM_AIM')
,(4,'SMARTARC_B3RATIO_AIM')
,(4,'SMARTARC_FeO_AIM')
,(4,'SMARTARC_MgO_AIM')

  



----------------------------
--- 4 --- CREATE GET SP
-----------------------------
  USE [MES]
GO
/****** Object:  StoredProcedure [CAT].[GetKpiVariables]    Script Date: 20 nov. 2020 13:50:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [CAT].[GetGradeGroupAttributes]
	@gradeGroupId INT = NULL
AS
BEGIN
	PRINT OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID) + ' Parameters:'
		+ '@gradeGroupId: ' + ISNULL(CAST(@gradeGroupId AS VARCHAR), '')
		


	-----------------------
	-- Parameters default
	-----------------------
	IF (@gradeGroupId IS NULL)
		SET @gradeGroupId =  (select top 1 Id from cat.GradeGroups where [Name] = 'Rail')  


	-----------------------
	-- Begin Query
	-----------------------
	SELECT *
	FROM CAT.GradeGroupAttributes 
	WHERE GradeGroupId = @gradeGroupId
			
END
GO

----------------------------
--- 5 --- CREATE GET PICKERS SP
-----------------------------

USE [MES]
GO
/****** Object:  StoredProcedure [CAT].[GetGradeGroups]    Script Date: 19 nov. 2020 20:43:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [CAT].[GetGradeGroups]
AS
BEGIN
	select * from cat.GradeGroups
END
GO

----------------------------
--- 5 --- CREATE UPDATE SP
-----------------------------


USE [MES]
GO
/****** Object:  StoredProcedure [CAT].[UpsertKpiVariable]    Script Date: 19 nov. 2020 20:29:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [CAT].[UpdateGradeGroupAttributes]
	@variables XML = 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 @variablesT TABLE (
		GradeGroupId INT,
		Code VARCHAR(50),
		Value FLOAT
	)
	DECLARE @curVariablesT TABLE (
		GradeGroupId INT,
		Code VARCHAR(50),
		Value FLOAT
	)


	INSERT INTO @variablesT
	(
		GradeGroupId,
		Code, 
		Value 
	)
	SELECT
		GradeGroupId = T.Variable.value('@gradeGroupId','INT'),
		Code = T.Variable.value('@code', 'VARCHAR(50)'), 
		Value = T.Variable.value('@value', 'FLOAT')
	FROM
	@variables.nodes('XMLRoot/Row') AS T(Variable)

	INSERT INTO @curVariablesT
	(
		GradeGroupId,
		Code, 
		Value 
	)
	SELECT
		curData.GradeGroupId,
		curData.Code,
		curData.Value
	FROM CAT.GradeGroupAttributes curData
		JOIN @variablesT newData ON newData.GradeGroupId = curData.GradeGroupId AND newData.Code = curData.Code
	

	-- 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 @variablesT WHERE Value IS NOT NULL))
		BEGIN
			MERGE INTO CAT.GradeGroupAttributes AS TARGET
			USING (SELECT
				*
			FROM @variablesT
			WHERE Value IS NOT NULL) AS SOURCE
			ON TARGET.GradeGroupId = SOURCE.GradeGroupId AND TARGET.Code = SOURCE.Code
			WHEN MATCHED THEN
				UPDATE SET 
				TARGET.Value = ISNULL(SOURCE.Value, TARGET.Value)
			WHEN NOT MATCHED THEN
				INSERT (
					GradeGroupId,
					Code,
					Value
				) 
				VALUES (
					SOURCE.GradeGroupId,
					SOURCE.Code,
					SOURCE.Value
				);
		END

		-- Finish procedure
		SELECT Success = CAST(1 AS BIT)
	END TRY
	BEGIN CATCH
		SET @result = ERROR_NUMBER()

		SELECT [Message] = ERROR_MESSAGE()
	END CATCH


	-- Audit
	BEGIN TRY
		IF (EXISTS(SELECT * FROM @variablesT WHERE Value IS NOT NULL))
		BEGIN
			DECLARE @msg VARCHAR(MAX),
					@moduleName VARCHAR(200) 

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


			SET @msg = @msg + ' Updated:'

			SELECT
				@msg = @msg + ' ['
						+ ', Code: "' + ISNULL(CAST(newData.Code AS VARCHAR), 'null') + '"'
						+ ', Value: "' + ISNULL(CAST(newData.Value AS VARCHAR), 'null') + '"'
						+ '],'
			FROM @variablesT newData
				JOIN @curVariablesT oldData ON oldData.GradeGroupId = newData.GradeGroupId AND oldData.Code = newData.Code
			WHERE 
				newData.Value IS NOT NULL

			SET @msg = @msg + ' Created:'

			SELECT
				@msg = @msg + ' ['
						+ ', Code: "' + ISNULL(CAST(newData.Code AS VARCHAR), 'null') + '"'
						+ ', Value: "' + ISNULL(CAST(newData.Value AS VARCHAR), 'null') + '"'
						+ '],'
			FROM @variablesT newData
				LEFT JOIN @curVariablesT oldData ON oldData.GradeGroupId = newData.GradeGroupId AND oldData.Code = newData.Code
			WHERE 
				newData.Value IS NOT NULL
				AND
				oldData.GradeGroupId IS NULL

			SET @msg = 'Kpi variables '
						+ CASE WHEN (@result = 0) THEN 'modified' ELSE 'failed to be modified' END
						-- Extra fields if exist.
						+ CASE WHEN (@msg <> '') THEN '.' + @msg + '.' ELSE '.' END


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


	RETURN @result
END
GO



