USE [IHBoxSystem]
GO
CREATE ROLE managechemistries_readWrite AUTHORIZATION dbo 
GO  

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

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

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

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

EXEC sp_addextendedproperty  @level0type = N'USER' ,@level0name = [managechemistries_readWrite] ,@name = N'securedsp-LAB.UpdateChemistries'		  ,@value =  N'SendCurrentUser=TRUE' 


declare @parent int, @result_routeId INT
select @parent=id from [FrontEnd].[Menu] where name='LAB'

INSERT INTO [FrontEnd].[Routes]
		   ([Route]
		   ,[ModuleName]
		   ,[ModulePath]
		   ,[RequiredRole]
	   )
	 VALUES
		   (
		   '!/manage-chemistries(/:heatName)(/:location)(/)'
		   , 'manage-chemistries'
		   , 'custom-screens/manage-chemistries/'
		   , 'managechemistries_readWrite'           
		   )
		   SET @result_routeId = @@IDENTITY
INSERT INTO [FrontEnd].[Menu]
		   ([Parent]
		   ,[Name]
		   ,[RouteID]
		   ,[SortOrder]
		   ,[Enabled]
		   ,[Visible]
		   ,[RequiredRole]
		   ,[Group]
		  )
	 VALUES
		   (
		   @parent
		   ,'Manage Chemistries'
		   ,@result_routeId 
		   ,1
		   ,1
		   ,1
		   ,'managechemistries_readWrite'
		   ,NULL
		 )
GO

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

DECLARE @ModuleId INT

INSERT INTO [SECURITY].[Modules]
		   ([Code]
		   ,[Name])
	 VALUES
		   ('manage_chemistries'
		   ,'Manage Chemistries')

		  SET @ModuleId = @@IDENTITY

INSERT INTO [SECURITY].[ModuleActions]
		   ([ModuleId]
		   ,[Code]
		   ,[Name]
		   ,[Role])
	 VALUES
		   (@ModuleId
		   ,'readWrite'
		   ,'Read/Write'
		   ,'managechemistries_readWrite')
GO
/*------------------------------------------------------------------------------------------------------------------------*/
USE [MES]
GO
/****** Object:  StoredProcedure [LAB].[UpdateChemistries]    Script Date: 16 feb. 2021 22:23:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAB].[UpdateChemistries]
	@HeatId INT
	,@TypeCode varchar(10)	
	,@SampleIdCode int	
	,@GradeVersionId int
	--if an element is modified
	,@ElementCode varchar(10) = NULL
	,@ElementCodeValue float  = NULL	
	--if the datetime is modified
	,@Timestamp datetime = NULL
	,@timeZoneCode CHAR(10) = NULL		
	,@currentUser VARCHAR(MAX) = 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)

	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
		+ ', @heatId: ' + ISNULL(CAST(@heatId AS VARCHAR), '')
		+ ', @TypeCode: ' + ISNULL(CAST(@TypeCode AS VARCHAR), '')
		+ ', @SampleIdCode: ' + ISNULL(CAST(@SampleIdCode AS VARCHAR), '')
		+ ', @GradeVersionId: ' + ISNULL(CAST(@GradeVersionId AS VARCHAR), '')
		+ ', @ElementCode: ' + ISNULL(CAST(@ElementCode AS VARCHAR), '')
		+ ', @ElementCodeValue: ' + ISNULL(CAST(@ElementCodeValue AS VARCHAR), '')
		+ ', @Timestamp: ' + ISNULL(CAST(@Timestamp AS VARCHAR), '')
		+ ', @currentUser: ' + ISNULL(CAST(@currentUser AS VARCHAR), '')
		+ ', @timeZoneCode: ' + ISNULL(CAST(@timeZoneCode AS VARCHAR), '')

	-----------------------
	-- Variables
	-----------------------
				
	DECLARE @buf NVARCHAR(MAX)
	
	DECLARE @ChemistrySampleId INT
	DECLARE @heatName varchar(10) = (select [Name] from MES.Heats where Id = @HeatId)
	DECLARE @PreviousElementValue table ([value] varchar(10))
	DECLARE @LastTimestamp NVARCHAR(MAX)
	DECLARE	@result INT
	DECLARE @logMsg VARCHAR(MAX)
	-----------------------
	-- Validation
	-----------------------
	IF (@timeZoneCode IS NULL)	SET @timeZoneCode = 'EDTIND'

	DECLARE @msg VARCHAR(MAX),
			@moduleName VARCHAR(200) 


	-----------------------
	-- Begin Query
	-----------------------
		-------------------------
		------ GET ROW ID ------- 
		-------------------------

		SELECT 
			@ChemistrySampleId = Id 
		FROM LAB.ChemistrySamplesWithCalculated
		WHERE HeatId = @heatId AND SampleIdCode = @sampleIdCode AND TypeCode = @TypeCode

		
		------------------------------
		------- DYNAMIC UPDATE -------
		------------------------------
		
		IF @Timestamp IS NULL
		BEGIN
			BEGIN TRY
				
				--------------------------------
				--- AUDIT ELEMENT CODE DATA ----
				--------------------------------

				
				SET  @buf= '
				(SELECT '+cast(@ElementCode as varchar(10))+'
				FROM
				LAB.ChemistrySamplesWithCalculated
				WHERE Id =  '+cast(@ChemistrySampleId as varchar(10))+'
				)'
			
				INSERT INTO @PreviousElementValue
				EXECUTE sp_executesql @statement=@buf

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


					SET @msg = 'Chemistry Element Value Changed : ' +  CAST(@ElementCode as varchar(10))  + 						
							' - Prev: ' + CAST((select [value] from @PreviousElementValue) as varchar(10)) +
							' - New: ' + CAST(@ElementCodeValue as varchar(10)) +
							' - Heat : ' + CAST(@heatName as varchar(10)) +
							' - Location : ' + CAST(@TypeCode as varchar(10)) +
							' - Id : ' + CAST(@SampleIdCode as varchar(10)) +
							' - Chemistry Sample Id: ' + CAST(@ChemistrySampleId as varchar(10)) 
			
				EXEC SYSTEM.AuditMsg @message = @msg, @moduleName = @moduleName, @currentUser = @currentUser

				----------------------------
				--- UPDATE ELEMENT CODE ----
				----------------------------

				set  @buf= '
				UPDATE
				LAB.ChemistrySamplesWithCalculated
				SET  '+cast(@ElementCode as nvarchar(10))+' ='+cast(@elementCodeValue as nvarchar(10))+'
				WHERE Id =  '+cast(@ChemistrySampleId as nvarchar(10))+'
				'		
				EXECUTE sp_executesql @statement=@buf
				
				
			
			
			END TRY
			BEGIN CATCH
				SELECT @result = ERROR_NUMBER()
						,@logMsg = ERROR_MESSAGE()

				SET @logMsg = 'Failed UPDATE ELEMENT CODE: '  + @logMsg
					RAISERROR( @logMsg ,0,1) WITH NOWAIT

				EXEC SYSTEM.LogMsg @message = @logMsg
					,@procedure = @procName
					,@typeCode = 'ERROR'
					,@logError = 1
			END CATCH


		END
		ELSE 
		BEGIN
			BEGIN TRY

				-----------------------------------
				--- AUDIT TIMESTAMP DATA ----------
				-----------------------------------
			
				SELECT
				@moduleName = CAT.GetAuditModuleName(),
				@msg = ''

				SET @msg = 'Chemistry Timestamp  Changed - Prev : ' + CAST((SELECT IH.ToLocal([Timestamp], @timeZoneCode) FROM LAB.ChemistrySamplesWithCalculated WHERE Id =  @ChemistrySampleId) as varchar(20)) +
					' - New : ' + CAST( CONVERT(VARCHAR,@Timestamp,120) as varchar(20)) + 
					' - Heat : ' + CAST(@heatName as varchar(10)) +
					' - Location : ' + CAST(@TypeCode as varchar(10)) +
					' - Id : ' + CAST(@SampleIdCode as varchar(10)) +
					' - Chemistry Sample Id: ' + CAST(@ChemistrySampleId as varchar(10)) 		
			
				EXEC SYSTEM.AuditMsg @message = @msg, @moduleName = @moduleName, @currentUser = @currentUser

				--------------------------
				--- UPDATE TIMESTAMP  ----
				--------------------------
				UPDATE
				LAB.ChemistrySamplesWithCalculated
				SET  [Timestamp] = IH.ToUTC(@Timestamp, @timeZoneCode)
				WHERE Id =  @ChemistrySampleId

			
			END TRY
			BEGIN CATCH
				SELECT @result = ERROR_NUMBER()
						,@logMsg = ERROR_MESSAGE()

				SET @logMsg = 'Failed UPDATE TIMESTAMP: '  + @logMsg
					RAISERROR( @logMsg ,0,1) WITH NOWAIT

				EXEC SYSTEM.LogMsg @message = @logMsg
					,@procedure = @procName
					,@typeCode = 'ERROR'
					,@logError = 1
			END CATCH
		END
		
		
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END
GO


USE [MES]
GO
/****** Object:  StoredProcedure [LAB].[GetManageChemistries]    Script Date: 16 feb. 2021 23:53:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAB].[GetManageChemistries]
	@heatId INT = NULL,
	@direction BIT = NULL,
	@ladleFurnaceCode INT = NULL,
	@timeZoneCode CHAR(10) = 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)

	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
		+ ', @heatId: ' + ISNULL(CAST(@heatId AS VARCHAR), '')
		+ ', @direction: ' + ISNULL(CAST(@direction AS VARCHAR), '')
		+ ', @ladleFurnaceCode: ' + ISNULL(CAST(@ladleFurnaceCode AS VARCHAR), '')
		+ ', @timeZoneCode: ' + ISNULL(CAST(@timeZoneCode AS VARCHAR), '')

		
	-----------------------
	-- Variables
	-----------------------
	--Navigate Buttons
	declare @buttons table ([Text] VARCHAR(50), HeatId INT, Treatment INT)

	--Navigation
	DECLARE @newHeatId INT,
			@start DATETIME

	
	-----------------------
	-- Validation
	-----------------------
	IF (@timeZoneCode IS NULL)	SET @timeZoneCode = 'EDTIND'

	-----------------------
	-- Navigation
	-----------------------
	IF (@direction IS NOT NULL)
	BEGIN
		if (@direction = 1)
			SET @start = (SELECT TOP 1 [Timestamp] FROM LAB.ChemistrySamplesWithCalculated WHERE HeatId = @heatId ORDER BY [Timestamp] DESC)
		if (@direction = 0)
			SET @start = (SELECT TOP 1 [Timestamp] FROM LAB.ChemistrySamplesWithCalculated WHERE HeatId = @heatId ORDER BY [Timestamp] ASC)

		print @start
		SET @newHeatId = CASE 
						WHEN (@direction = 1) THEN
								(SELECT TOP 1 l.HeatId 
									FROM LAB.ChemistrySamplesWithCalculated l
									left join eaf.Processes p on p.HeatId = l.HeatId
									WHERE l.[Timestamp] >= @start and l.HeatId <> @heatId
									AND (@ladleFurnaceCode IS NULL OR p.FurnaceCode = @ladleFurnaceCode) 
									ORDER BY l.[Timestamp] ASC
								)
						WHEN (@direction = 0) THEN
								(SELECT TOP 1 l.HeatId 
									FROM LAB.ChemistrySamplesWithCalculated l
									left join eaf.Processes p on p.HeatId = l.HeatId
									WHERE l.[Timestamp] <= @start and l.HeatId <> @heatId
									AND (@ladleFurnaceCode IS NULL OR p.FurnaceCode = @ladleFurnaceCode)  
									ORDER BY l.[Timestamp] DESC
								)
					END
		print @newHeatId
		IF (@newHeatId) IS NOT NULL 
			SET @heatId =@newHeatId
	END



	-----------------------
	-- Begin Query
	-----------------------
	SELECT 
		record.*
		,LocalTimestamp = IH.ToLocal(record.[Timestamp], @timeZoneCode)
		,GradeId = heat.gradeId
		,GradeName = grade.[Name]
		,HeatName = heat.[Name]
		

		,MinAl		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Al','MIN')               
		,MinAs		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'As','MIN')               
		,MinB		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'B ','MIN')                
		,MinBi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Bi','MIN')               
		,MinC		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'C','MIN')                
		,MinC8		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'C8','MIN')               
		,MinCE		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE','MIN')               
		,MinCE1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE1','MIN')              
		,MinCE2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE2','MIN')              
		,MinCE3		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE3','MIN')              
		,MinCa		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ca','MIN')               
		,MinCb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cb','MIN')               
		,MinCo		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Co','MIN')                
		,MinCorrIdx	= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CorrIdx','MIN')         
		,MinCr		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cr','MIN')               
		,MinCu		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cu','MIN')               
		,MinD1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'D1','MIN')               
		,MinDI		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'DI','MIN')               
		,MinE1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'E1','MIN')               
		,MinE8		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'E8','MIN')               
		,MinFP		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'FP','MIN')               
		,MinFe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Fe','MIN')              
		,MinI		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'I ','MIN')                
		,MinId		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Id','MIN')               
		,MinLiq		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Liq','MIN')              
		,MinM2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'M2','MIN')               
		,MinMg		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mg','MIN')               
		,MinMn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mn','MIN')               
		,MinMn2S	= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mn2S','MIN')             
		,MinMo		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mo','MIN')               
		,MinN		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'N','MIN')                
		,MinNb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Nb','MIN')               
		,MinNi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ni','MIN')               
		,MinP		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'P','MIN')                
		,MinPC		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'PC','MIN')               
		,MinPb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Pb','MIN')               
		,MinS		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S','MIN')                
		,MinS1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S1','MIN')               
		,MinS2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S2','MIN')               
		,MinS3		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S3','MIN')               
		,MinS4		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S4','MIN')               
		,MinS5		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S5','MIN')               
		,MinSb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Sb','MIN')               
		,MinSe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Se','MIN')               
		,MinSi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Si','MIN')               
		,MinSn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Sn','MIN')               
		,MinTa		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ta','MIN')               
		,MinTe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Te','MIN')               
		,MinTi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ti','MIN')               
		,MinV		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'V','MIN')                
		,MinW		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'W','MIN')                
		,MinZn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Zn','MIN')               
		,MinZr		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Zr','MIN')

		,MaxAl		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Al','MAX')               
		,MaxAs		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'As','MAX')               
		,MaxB		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'B ','MAX')                
		,MaxBi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Bi','MAX')               
		,MaxC		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'C','MAX')                
		,MaxC8		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'C8','MAX')               
		,MaxCE		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE','MAX')               
		,MaxCE1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE1','MAX')              
		,MaxCE2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE2','MAX')              
		,MaxCE3		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE3','MAX')              
		,MaxCa		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ca','MAX')               
		,MaxCb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cb','MAX')               
		,MaxCo		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Co','MAX')                
		,MaxCorrIdx	= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CorrIdx','MAX')         
		,MaxCr		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cr','MAX')               
		,MaxCu		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cu','MAX')               
		,MaxD1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'D1','MAX')               
		,MaxDI		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'DI','MAX')               
		,MaxE1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'E1','MAX')               
		,MaxE8		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'E8','MAX')               
		,MaxFP		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'FP','MAX')               
		,MaxFe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Fe','MAX')              
		,MaxI		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'I ','MAX')                
		,MaxId		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Id','MAX')               
		,MaxLiq		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Liq','MAX')              
		,MaxM2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'M2','MAX')               
		,MaxMg		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mg','MAX')               
		,MaxMn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mn','MAX')               
		,MaxMn2S	= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mn2S','MAX')             
		,MaxMo		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mo','MAX')               
		,MaxN		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'N','MAX')                
		,MaxNb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Nb','MAX')               
		,MaxNi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ni','MAX')               
		,MaxP		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'P','MAX')                
		,MaxPC		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'PC','MAX')               
		,MaxPb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Pb','MAX')               
		,MaxS		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S','MAX')                
		,MaxS1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S1','MAX')               
		,MaxS2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S2','MAX')               
		,MaxS3		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S3','MAX')               
		,MaxS4		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S4','MAX')               
		,MaxS5		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S5','MAX')               
		,MaxSb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Sb','MAX')               
		,MaxSe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Se','MAX')               
		,MaxSi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Si','MAX')               
		,MaxSn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Sn','MAX')               
		,MaxTa		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ta','MAX')               
		,MaxTe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Te','MAX')               
		,MaxTi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ti','MAX')               
		,MaxV		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'V','MAX')                
		,MaxW		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'W','MAX')                
		,MaxZn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Zn','MAX')               
		,MaxZr		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Zr','MAX')  

		,AimAl		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Al','AIM')               
		,AimAs		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'As','AIM')               
		,AimB		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'B ','AIM')                
		,AimBi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Bi','AIM')               
		,AimC		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'C','AIM')                
		,AimC8		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'C8','AIM')               
		,AimCE		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE','AIM')               
		,AimCE1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE1','AIM')              
		,AimCE2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE2','AIM')              
		,AimCE3		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE3','AIM')              
		,AimCa		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ca','AIM')               
		,AimCb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cb','AIM')               
		,AimCo		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Co','AIM')                
		,AimCorrIdx	= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CorrIdx','AIM')         
		,AimCr		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cr','AIM')               
		,AimCu		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cu','AIM')               
		,AimD1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'D1','AIM')               
		,AimDI		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'DI','AIM')               
		,AimE1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'E1','AIM')               
		,AimE8		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'E8','AIM')               
		,AimFP		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'FP','AIM')               
		,AimFe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Fe','AIM')              
		,AimI		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'I ','AIM')                
		,AimId		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Id','AIM')               
		,AimLiq		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Liq','AIM')              
		,AimM2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'M2','AIM')               
		,AimMg		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mg','AIM')               
		,AimMn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mn','AIM')               
		,AimMn2S	= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mn2S','AIM')             
		,AimMo		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mo','AIM')               
		,AimN		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'N','AIM')                
		,AimNb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Nb','AIM')               
		,AimNi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ni','AIM')               
		,AimP		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'P','AIM')                
		,AimPC		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'PC','AIM')               
		,AimPb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Pb','AIM')               
		,AimS		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S','AIM')                
		,AimS1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S1','AIM')               
		,AimS2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S2','AIM')               
		,AimS3		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S3','AIM')               
		,AimS4		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S4','AIM')               
		,AimS5		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S5','AIM')               
		,AimSb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Sb','AIM')               
		,AimSe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Se','AIM')               
		,AimSi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Si','AIM')               
		,AimSn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Sn','AIM')               
		,AimTa		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ta','AIM')               
		,AimTe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Te','AIM')               
		,AimTi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ti','AIM')               
		,AimV		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'V','AIM')                
		,AimW		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'W','AIM')                
		,AimZn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Zn','AIM')               
		,AimZr		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Zr','AIM')  


	FROM LAB.ChemistrySamplesWithCalculated record
		LEFT JOIN MES.Heats heat ON heat.Id = record.HeatId
		LEFT JOIN CAT.Grades grade ON grade.Id = heat.GradeId

	WHERE record.HeatId = @heatId
	

		-----------------------
		-- Navigate Buttons
		-----------------------
		
		select top 5 [Text]= 'EAF', Treatment = null, HeatName = h.[Name], HeatId = h.Id
		from EAF.Processes p 
		left join mes.Heats h on h.Id = p.HeatId
		where p.HeatId = @heatId
		
		union all
		
		select top 5 [Text]= 'LMF', Treatment = p.Treatment, HeatName = h.[Name], HeatId = h.Id 
		from LF.Processes p 
		left join mes.Heats h on h.Id = p.HeatId
		where p.HeatId = @heatId

		union all
		
		select top 5 [Text]= 'VTD', Treatment = p.Treatment, HeatName = h.[Name], HeatId = h.Id
		from VTD.Processes p 
		left join mes.Heats h on h.Id = p.HeatId
		where p.HeatId = @heatId

		union all
				
		select top 5 [Text]= 'SLAG EAF', Treatment = null, HeatName = h.[Name], HeatId = h.Id
		from LAB.SlagChemistrySamples l
		left join mes.Heats h on h.Id = l.HeatId
		where l.HeatId = @heatId

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

	RETURN 0
END

GO


USE [MES]
GO
/****** Object:  StoredProcedure [LAB].[GetManageChemistries]    Script Date: 21 feb. 2021 10:41:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAB].[GetManageChemistries]
	@heatId INT = NULL,
	@direction BIT = NULL,
	@ladleFurnaceCode INT = NULL,
	@timeZoneCode CHAR(10) = 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)

	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
		+ ', @heatId: ' + ISNULL(CAST(@heatId AS VARCHAR), '')
		+ ', @direction: ' + ISNULL(CAST(@direction AS VARCHAR), '')
		+ ', @ladleFurnaceCode: ' + ISNULL(CAST(@ladleFurnaceCode AS VARCHAR), '')
		+ ', @timeZoneCode: ' + ISNULL(CAST(@timeZoneCode AS VARCHAR), '')

		
	-----------------------
	-- Variables
	-----------------------
	--Navigate Buttons
	declare @buttons table ([Text] VARCHAR(50), HeatId INT, Treatment INT)

	--Navigation
	DECLARE @newHeatId INT,
			@start DATETIME

	declare @elements table ([Name] char(10))

insert into @elements([Name])
values 

('C'),('Mn'),('S'),('P'),('Si'),('Cu'),('Ni'),('Cr'),('Mo'),('Sn'),('Nb'),('V'),('N'),('Al'),('B'),('Ca'),('Pb'),('Ti'),('Mn2S'),('Liq'),('CorrIdx'),('CE')
,('FP'),('Fe'),('Zn'),('Sb'),('As'),('Co'),('Zr'),('W'),('Ta'),('Bi'),('Se'),('Te'),('Mg'),('Cb'),('E1'),('CE2'),('CE3'),('CE1'),('E8'),('PC'),('I'),('S1'),('S2'),('S3'),('S4'),('S5')
,('M2'),('C8'),('D1'),('DI')


	-----------------------
	-- Validation
	-----------------------
	IF (@timeZoneCode IS NULL)	SET @timeZoneCode = 'EDTIND'

	-----------------------
	-- Navigation
	-----------------------
	IF (@direction IS NOT NULL)
	BEGIN
		if (@direction = 1)
			SET @start = (SELECT TOP 1 [Timestamp] FROM LAB.ChemistrySamplesWithCalculated WHERE HeatId = @heatId ORDER BY [Timestamp] DESC)
		if (@direction = 0)
			SET @start = (SELECT TOP 1 [Timestamp] FROM LAB.ChemistrySamplesWithCalculated WHERE HeatId = @heatId ORDER BY [Timestamp] ASC)

		print @start
		SET @newHeatId = CASE 
						WHEN (@direction = 1) THEN
								(SELECT TOP 1 l.HeatId 
									FROM LAB.ChemistrySamplesWithCalculated l
									left join eaf.Processes p on p.HeatId = l.HeatId
									WHERE l.[Timestamp] >= @start and l.HeatId <> @heatId
									AND (@ladleFurnaceCode IS NULL OR p.FurnaceCode = @ladleFurnaceCode) 
									ORDER BY l.[Timestamp] ASC
								)
						WHEN (@direction = 0) THEN
								(SELECT TOP 1 l.HeatId 
									FROM LAB.ChemistrySamplesWithCalculated l
									left join eaf.Processes p on p.HeatId = l.HeatId
									WHERE l.[Timestamp] <= @start and l.HeatId <> @heatId
									AND (@ladleFurnaceCode IS NULL OR p.FurnaceCode = @ladleFurnaceCode)  
									ORDER BY l.[Timestamp] DESC
								)
					END
		print @newHeatId
		IF (@newHeatId) IS NOT NULL 
			SET @heatId =@newHeatId
	END



	-----------------------
	-- Begin Query
	-----------------------
	declare @gradeId int
	declare @gradeVersionId int

	SELECT
		@gradeId = grade.Id
		,@gradeVersionId =record.GradeVersionId 	
	FROM LAB.ChemistrySamplesWithCalculated record
		LEFT JOIN MES.Heats heat ON heat.Id = record.HeatId
		LEFT JOIN CAT.Grades grade ON grade.Id = heat.GradeId
	WHERE record.HeatId = @heatId

	SELECT 
		 record.Id
		,record.[HeatId]
		,record.[SampleIdCode]		 
		,LocalTimestamp = IH.ToLocal(record.[Timestamp], @timeZoneCode)
		,GradeId = heat.gradeId
		,GradeName = grade.[Name]
		,HeatName = heat.[Name]

		
		
		,record.[TypeCode]
		,record.[C]
		,record.[Mn],record.[S],record.[P],record.[Si],record.[Cu],record.[Ni],record.[Cr],record.[Mo],record.[Sn],record.[Nb],record.[V],record.[N],record.[Al],record.[B],record.[Ca],record.[Pb],record.[Ti],record.[Mn2S],record.[Liq],record.[CorrIdx],record.[CE]
		,record.[FP],record.[Fe],record.[Zn],record.[Sb],record.[As],record.[Co],record.[Zr],record.[W],record.[Ta],record.[Bi],record.[Se],record.[Te],record.[Mg],record.[Cb],record.[E1],record.[CE2],record.[CE3],record.[CE1],record.[E8],record.[PC],record.[I],record.[S1],record.[S2],record.[S3],record.[S4],record.[S5]
		,record.[M2],record.[C8],record.[D1],record.[DI]
		

		,MinAl		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Al','MIN')               
		,MinAs		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'As','MIN')               
		,MinB		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'B ','MIN')                
		,MinBi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Bi','MIN')               
		,MinC		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'C','MIN')                
		,MinC8		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'C8','MIN')               
		,MinCE		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE','MIN')               
		,MinCE1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE1','MIN')              
		,MinCE2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE2','MIN')              
		,MinCE3		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE3','MIN')              
		,MinCa		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ca','MIN')               
		,MinCb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cb','MIN')               
		,MinCo		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Co','MIN')                
		,MinCorrIdx	= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CorrIdx','MIN')         
		,MinCr		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cr','MIN')               
		,MinCu		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cu','MIN')               
		,MinD1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'D1','MIN')               
		,MinDI		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'DI','MIN')               
		,MinE1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'E1','MIN')               
		,MinE8		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'E8','MIN')               
		,MinFP		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'FP','MIN')               
		,MinFe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Fe','MIN')              
		,MinI		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'I ','MIN')                
		,MinId		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Id','MIN')               
		,MinLiq		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Liq','MIN')              
		,MinM2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'M2','MIN')               
		,MinMg		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mg','MIN')               
		,MinMn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mn','MIN')               
		,MinMn2S	= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mn2S','MIN')             
		,MinMo		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mo','MIN')               
		,MinN		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'N','MIN')                
		,MinNb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Nb','MIN')               
		,MinNi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ni','MIN')               
		,MinP		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'P','MIN')                
		,MinPC		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'PC','MIN')               
		,MinPb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Pb','MIN')               
		,MinS		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S','MIN')                
		,MinS1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S1','MIN')               
		,MinS2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S2','MIN')               
		,MinS3		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S3','MIN')               
		,MinS4		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S4','MIN')               
		,MinS5		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S5','MIN')               
		,MinSb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Sb','MIN')               
		,MinSe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Se','MIN')               
		,MinSi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Si','MIN')               
		,MinSn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Sn','MIN')               
		,MinTa		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ta','MIN')               
		,MinTe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Te','MIN')               
		,MinTi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ti','MIN')               
		,MinV		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'V','MIN')                
		,MinW		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'W','MIN')                
		,MinZn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Zn','MIN')               
		,MinZr		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Zr','MIN')

		,MaxAl		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Al','MAX')               
		,MaxAs		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'As','MAX')               
		,MaxB		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'B ','MAX')                
		,MaxBi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Bi','MAX')               
		,MaxC		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'C','MAX')                
		,MaxC8		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'C8','MAX')               
		,MaxCE		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE','MAX')               
		,MaxCE1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE1','MAX')              
		,MaxCE2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE2','MAX')              
		,MaxCE3		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE3','MAX')              
		,MaxCa		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ca','MAX')               
		,MaxCb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cb','MAX')               
		,MaxCo		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Co','MAX')                
		,MaxCorrIdx	= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CorrIdx','MAX')         
		,MaxCr		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cr','MAX')               
		,MaxCu		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cu','MAX')               
		,MaxD1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'D1','MAX')               
		,MaxDI		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'DI','MAX')               
		,MaxE1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'E1','MAX')               
		,MaxE8		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'E8','MAX')               
		,MaxFP		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'FP','MAX')               
		,MaxFe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Fe','MAX')              
		,MaxI		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'I ','MAX')                
		,MaxId		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Id','MAX')               
		,MaxLiq		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Liq','MAX')              
		,MaxM2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'M2','MAX')               
		,MaxMg		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mg','MAX')               
		,MaxMn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mn','MAX')               
		,MaxMn2S	= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mn2S','MAX')             
		,MaxMo		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mo','MAX')               
		,MaxN		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'N','MAX')                
		,MaxNb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Nb','MAX')               
		,MaxNi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ni','MAX')               
		,MaxP		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'P','MAX')                
		,MaxPC		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'PC','MAX')               
		,MaxPb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Pb','MAX')               
		,MaxS		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S','MAX')                
		,MaxS1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S1','MAX')               
		,MaxS2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S2','MAX')               
		,MaxS3		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S3','MAX')               
		,MaxS4		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S4','MAX')               
		,MaxS5		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S5','MAX')               
		,MaxSb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Sb','MAX')               
		,MaxSe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Se','MAX')               
		,MaxSi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Si','MAX')               
		,MaxSn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Sn','MAX')               
		,MaxTa		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ta','MAX')               
		,MaxTe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Te','MAX')               
		,MaxTi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ti','MAX')               
		,MaxV		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'V','MAX')                
		,MaxW		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'W','MAX')                
		,MaxZn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Zn','MAX')               
		,MaxZr		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Zr','MAX')  

		,AimAl		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Al','AIM')               
		,AimAs		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'As','AIM')               
		,AimB		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'B ','AIM')                
		,AimBi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Bi','AIM')               
		,AimC		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'C','AIM')                
		,AimC8		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'C8','AIM')               
		,AimCE		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE','AIM')               
		,AimCE1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE1','AIM')              
		,AimCE2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE2','AIM')              
		,AimCE3		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE3','AIM')              
		,AimCa		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ca','AIM')               
		,AimCb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cb','AIM')               
		,AimCo		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Co','AIM')                
		,AimCorrIdx	= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CorrIdx','AIM')         
		,AimCr		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cr','AIM')               
		,AimCu		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cu','AIM')               
		,AimD1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'D1','AIM')               
		,AimDI		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'DI','AIM')               
		,AimE1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'E1','AIM')               
		,AimE8		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'E8','AIM')               
		,AimFP		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'FP','AIM')               
		,AimFe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Fe','AIM')              
		,AimI		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'I ','AIM')                
		,AimId		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Id','AIM')               
		,AimLiq		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Liq','AIM')              
		,AimM2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'M2','AIM')               
		,AimMg		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mg','AIM')               
		,AimMn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mn','AIM')               
		,AimMn2S	= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mn2S','AIM')             
		,AimMo		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mo','AIM')               
		,AimN		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'N','AIM')                
		,AimNb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Nb','AIM')               
		,AimNi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ni','AIM')               
		,AimP		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'P','AIM')                
		,AimPC		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'PC','AIM')               
		,AimPb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Pb','AIM')               
		,AimS		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S','AIM')                
		,AimS1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S1','AIM')               
		,AimS2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S2','AIM')               
		,AimS3		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S3','AIM')               
		,AimS4		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S4','AIM')               
		,AimS5		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S5','AIM')               
		,AimSb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Sb','AIM')               
		,AimSe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Se','AIM')               
		,AimSi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Si','AIM')               
		,AimSn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Sn','AIM')               
		,AimTa		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ta','AIM')               
		,AimTe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Te','AIM')               
		,AimTi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ti','AIM')               
		,AimV		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'V','AIM')                
		,AimW		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'W','AIM')                
		,AimZn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Zn','AIM')               
		,AimZr		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Zr','AIM')  
	into #temp
	FROM LAB.ChemistrySamplesWithCalculated record
		LEFT JOIN MES.Heats heat ON heat.Id = record.HeatId
		LEFT JOIN CAT.Grades grade ON grade.Id = heat.GradeId

	WHERE record.HeatId = @heatId


	select 
		[Code] = ElementCode
		,[Max]=cat.[Max]
		,[Min]=cat.[Min]
		,[Aim]=cat.[Aim]
	into #Limits
	FROM CAT.GradeSpecs cat
	JOIN CAT.GradeVersions gradeVersion ON gradeVersion.Id = cat.GradeVersionId
	JOIN CAT.Grades gr ON gr.Id = gradeVersion.GradeId
	CROSS APPLY @elements e
	WHERE gr.Id = @gradeId and cat.GradeVersionId = @gradeVersionId and ElementCode = e.[Name]
	
		SELECT [TypeCode], [C],[Mn],[S],[P],[Si],[Cu],[Ni],[Cr],[Mo],[Sn],[Nb],[V],[N],[Al],[B],[Ca],[Pb],[Ti],[Mn2S],[Liq],[CorrIdx],[CE]
		,[FP],[Fe],[Zn],[Sb],[As],[Co],[Zr],[W],[Ta],[Bi],[Se],[Te],[Mg],[Cb],[E1],[CE2],[CE3],[CE1],[E8],[PC],[I],[S1],[S2],[S3],[S4],[S5]
		,[M2],[C8],[D1],[DI]
			into #MaxLimits
			FROM
			(
				select
				[TypeCode] = 'MAX', 
				Code,
				[Max]
				from
				#Limits
			)t
			Pivot(
				MAX([Max])
				FOR [Code]
				IN ([C],[Mn],[S],[P],[Si],[Cu],[Ni],[Cr],[Mo],[Sn],[Nb],[V],[N],[Al],[B],[Ca],[Pb],[Ti],[Mn2S],[Liq],[CorrIdx],[CE]
					,[FP],[Fe],[Zn],[Sb],[As],[Co],[Zr],[W],[Ta],[Bi],[Se],[Te],[Mg],[Cb],[E1],[CE2],[CE3],[CE1],[E8],[PC],[I],[S1],[S2],[S3],[S4],[S5]
					,[M2],[C8],[D1],[DI]
				) 
			) AS PivotTable

	SELECT [TypeCode], [C],[Mn],[S],[P],[Si],[Cu],[Ni],[Cr],[Mo],[Sn],[Nb],[V],[N],[Al],[B],[Ca],[Pb],[Ti],[Mn2S],[Liq],[CorrIdx],[CE]
		,[FP],[Fe],[Zn],[Sb],[As],[Co],[Zr],[W],[Ta],[Bi],[Se],[Te],[Mg],[Cb],[E1],[CE2],[CE3],[CE1],[E8],[PC],[I],[S1],[S2],[S3],[S4],[S5]
		,[M2],[C8],[D1],[DI]
			into #MinLimits
			FROM
			(
				select
				[TypeCode] = 'MIN', 
				Code,
				[Min]
				from
				#Limits
			)t
			Pivot(
				MIN([Min])
				FOR [Code]
				IN ([C],[Mn],[S],[P],[Si],[Cu],[Ni],[Cr],[Mo],[Sn],[Nb],[V],[N],[Al],[B],[Ca],[Pb],[Ti],[Mn2S],[Liq],[CorrIdx],[CE]
					,[FP],[Fe],[Zn],[Sb],[As],[Co],[Zr],[W],[Ta],[Bi],[Se],[Te],[Mg],[Cb],[E1],[CE2],[CE3],[CE1],[E8],[PC],[I],[S1],[S2],[S3],[S4],[S5]
					,[M2],[C8],[D1],[DI]
				) 
			) AS PivotTable

	
	SELECT [TypeCode], [C],[Mn],[S],[P],[Si],[Cu],[Ni],[Cr],[Mo],[Sn],[Nb],[V],[N],[Al],[B],[Ca],[Pb],[Ti],[Mn2S],[Liq],[CorrIdx],[CE]
		,[FP],[Fe],[Zn],[Sb],[As],[Co],[Zr],[W],[Ta],[Bi],[Se],[Te],[Mg],[Cb],[E1],[CE2],[CE3],[CE1],[E8],[PC],[I],[S1],[S2],[S3],[S4],[S5]
		,[M2],[C8],[D1],[DI]
			into #AimLimits
			FROM
			(
				select
				[TypeCode] = 'AIM', 
				Code,
				[Aim]
				from
				#Limits
			)t
			Pivot(
				AVG([Aim])
				FOR [Code]
				IN ([C],[Mn],[S],[P],[Si],[Cu],[Ni],[Cr],[Mo],[Sn],[Nb],[V],[N],[Al],[B],[Ca],[Pb],[Ti],[Mn2S],[Liq],[CorrIdx],[CE]
					,[FP],[Fe],[Zn],[Sb],[As],[Co],[Zr],[W],[Ta],[Bi],[Se],[Te],[Mg],[Cb],[E1],[CE2],[CE3],[CE1],[E8],[PC],[I],[S1],[S2],[S3],[S4],[S5]
					,[M2],[C8],[D1],[DI]
				) 
			) AS PivotTable



	select * from #temp --union all
	select [Id]= NULL,[HeatId]=NULL,[SampleIdCode]=NULL, [LocalTimestamp] = NULL,GradeId = NULL, GradeName=NULL,HeatName=NULL,* from #MaxLimits union all	
	select [Id]= NULL,[HeatId]=NULL,[SampleIdCode]=NULL, [LocalTimestamp] = NULL,GradeId = NULL, GradeName=NULL,HeatName=NULL,* from #AimLimits union all
	select [Id]= NULL,[HeatId]=NULL,[SampleIdCode]=NULL, [LocalTimestamp] = NULL,GradeId = NULL, GradeName=NULL,HeatName=NULL,* from #MinLimits 


		-----------------------
		-- Navigate Buttons
		-----------------------
		
		select top 5 [Text]= 'EAF', Treatment = null, HeatName = h.[Name], HeatId = h.Id
		from EAF.Processes p 
		left join mes.Heats h on h.Id = p.HeatId
		where p.HeatId = @heatId
		
		union all
		
		select top 5 [Text]= 'LMF', Treatment = p.Treatment, HeatName = h.[Name], HeatId = h.Id 
		from LF.Processes p 
		left join mes.Heats h on h.Id = p.HeatId
		where p.HeatId = @heatId

		union all
		
		select top 5 [Text]= 'VTD', Treatment = p.Treatment, HeatName = h.[Name], HeatId = h.Id
		from VTD.Processes p 
		left join mes.Heats h on h.Id = p.HeatId
		where p.HeatId = @heatId

		union all
				
		select top 5 [Text]= 'SLAG EAF', Treatment = null, HeatName = h.[Name], HeatId = h.Id
		from LAB.SlagChemistrySamples l
		left join mes.Heats h on h.Id = l.HeatId
		where l.HeatId = @heatId

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

	RETURN 0
END

go 
[LAB].[GetManageChemistries] @heatID = 69039



----------------------------------------------------
-------- 31 March 2021
---------------------------------------------------
USE [MES]
GO
/****** Object:  StoredProcedure [LAB].[GetManageChemistries]    Script Date: 31 mar. 2021 11:46:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAB].[GetManageChemistries]
	@heatId INT = NULL,
	@direction BIT = NULL,
	@ladleFurnaceCode INT = NULL,
	@timeZoneCode CHAR(10) = 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)

	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
		+ ', @heatId: ' + ISNULL(CAST(@heatId AS VARCHAR), '')
		+ ', @direction: ' + ISNULL(CAST(@direction AS VARCHAR), '')
		+ ', @ladleFurnaceCode: ' + ISNULL(CAST(@ladleFurnaceCode AS VARCHAR), '')
		+ ', @timeZoneCode: ' + ISNULL(CAST(@timeZoneCode AS VARCHAR), '')

		
	-----------------------
	-- Variables
	-----------------------
	--Navigate Buttons
	declare @buttons table ([Text] VARCHAR(50), HeatId INT, Treatment INT)

	--Navigation
	DECLARE @newHeatId INT,
			@start DATETIME

	declare @elements table ([Name] char(10))

insert into @elements([Name])
values 

('C'),('Mn'),('S'),('P'),('Si'),('Cu'),('Ni'),('Cr'),('Mo'),('Sn'),('Nb'),('V'),('N'),('Al'),('B'),('Ca'),('Pb'),('Ti'),('Mn2S'),('Liq'),('CorrIdx'),('CE')
,('FP'),('Fe'),('Zn'),('Sb'),('As'),('Co'),('Zr'),('W'),('Ta'),('Bi'),('Se'),('Te'),('Mg'),('Cb'),('E1'),('CE2'),('CE3'),('CE1'),('E8'),('PC'),('I'),('S1'),('S2'),('S3'),('S4'),('S5')
,('M2'),('C8'),('D1'),('DI')


	-----------------------
	-- Validation
	-----------------------
	IF (@timeZoneCode IS NULL)	SET @timeZoneCode = 'EDTIND'

	-----------------------
	-- Navigation
	-----------------------
	IF (@direction IS NOT NULL)
	BEGIN
		if (@direction = 1)
			SET @start = (SELECT TOP 1 [Timestamp] FROM LAB.ChemistrySamplesWithCalculated WHERE HeatId = @heatId ORDER BY [Timestamp] DESC)
		if (@direction = 0)
			SET @start = (SELECT TOP 1 [Timestamp] FROM LAB.ChemistrySamplesWithCalculated WHERE HeatId = @heatId ORDER BY [Timestamp] ASC)

		print @start
		SET @newHeatId = CASE 
						WHEN (@direction = 1) THEN
								(SELECT TOP 1 l.HeatId 
									FROM LAB.ChemistrySamplesWithCalculated l
									left join eaf.Processes p on p.HeatId = l.HeatId
									WHERE l.[Timestamp] >= @start and l.HeatId <> @heatId
									AND (@ladleFurnaceCode IS NULL OR p.FurnaceCode = @ladleFurnaceCode) 
									ORDER BY l.[Timestamp] ASC
								)
						WHEN (@direction = 0) THEN
								(SELECT TOP 1 l.HeatId 
									FROM LAB.ChemistrySamplesWithCalculated l
									left join eaf.Processes p on p.HeatId = l.HeatId
									WHERE l.[Timestamp] <= @start and l.HeatId <> @heatId
									AND (@ladleFurnaceCode IS NULL OR p.FurnaceCode = @ladleFurnaceCode)  
									ORDER BY l.[Timestamp] DESC
								)
					END
		print @newHeatId
		IF (@newHeatId) IS NOT NULL 
			SET @heatId =@newHeatId
	END



	-----------------------
	-- Begin Query
	-----------------------
	declare @gradeId int
	declare @gradeVersionId int

	SELECT
		@gradeId = grade.Id
		,@gradeVersionId =record.GradeVersionId 	
	FROM LAB.ChemistrySamplesWithCalculated record
		LEFT JOIN MES.Heats heat ON heat.Id = record.HeatId
		LEFT JOIN CAT.Grades grade ON grade.Id = heat.GradeId
	WHERE record.HeatId = @heatId

	SELECT 
		 record.Id
		,record.[HeatId]
		,record.[SampleIdCode]		 
		,LocalTimestamp = IH.ToLocal(record.[Timestamp], @timeZoneCode)
		,GradeId = heat.gradeId
		,GradeName = grade.[Name]
		,HeatName = heat.[Name]

		
		
		,record.[TypeCode]
		,record.[C]
		,record.[Mn],record.[S],record.[P],record.[Si],record.[Cu],record.[Ni],record.[Cr],record.[Mo],record.[Sn],record.[Nb],record.[V],record.[N],record.[Al],record.[B],record.[Ca],record.[Pb],record.[Ti],record.[Mn2S],record.[Liq],record.[CorrIdx],record.[CE]
		,record.[FP],record.[Fe],record.[Zn],record.[Sb],record.[As],record.[Co],record.[Zr],record.[W],record.[Ta],record.[Bi],record.[Se],record.[Te],record.[Mg],record.[Cb],record.[E1],record.[CE2],record.[CE3],record.[CE1],record.[E8],record.[PC],record.[I],record.[S1],record.[S2],record.[S3],record.[S4],record.[S5]
		,record.[M2],record.[C8],record.[D1],record.[DI]
		

		,MinAl		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Al','MIN')               
		,MinAs		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'As','MIN')               
		,MinB		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'B ','MIN')                
		,MinBi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Bi','MIN')               
		,MinC		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'C','MIN')                
		,MinC8		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'C8','MIN')               
		,MinCE		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE','MIN')               
		,MinCE1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE1','MIN')              
		,MinCE2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE2','MIN')              
		,MinCE3		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE3','MIN')              
		,MinCa		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ca','MIN')               
		,MinCb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cb','MIN')               
		,MinCo		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Co','MIN')                
		,MinCorrIdx	= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CorrIdx','MIN')         
		,MinCr		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cr','MIN')               
		,MinCu		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cu','MIN')               
		,MinD1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'D1','MIN')               
		,MinDI		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'DI','MIN')               
		,MinE1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'E1','MIN')               
		,MinE8		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'E8','MIN')               
		,MinFP		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'FP','MIN')               
		,MinFe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Fe','MIN')              
		,MinI		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'I ','MIN')                
		,MinId		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Id','MIN')               
		,MinLiq		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Liq','MIN')              
		,MinM2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'M2','MIN')               
		,MinMg		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mg','MIN')               
		,MinMn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mn','MIN')               
		,MinMn2S	= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mn2S','MIN')             
		,MinMo		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mo','MIN')               
		,MinN		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'N','MIN')                
		,MinNb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Nb','MIN')               
		,MinNi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ni','MIN')               
		,MinP		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'P','MIN')                
		,MinPC		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'PC','MIN')               
		,MinPb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Pb','MIN')               
		,MinS		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S','MIN')                
		,MinS1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S1','MIN')               
		,MinS2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S2','MIN')               
		,MinS3		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S3','MIN')               
		,MinS4		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S4','MIN')               
		,MinS5		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S5','MIN')               
		,MinSb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Sb','MIN')               
		,MinSe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Se','MIN')               
		,MinSi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Si','MIN')               
		,MinSn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Sn','MIN')               
		,MinTa		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ta','MIN')               
		,MinTe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Te','MIN')               
		,MinTi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ti','MIN')               
		,MinV		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'V','MIN')                
		,MinW		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'W','MIN')                
		,MinZn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Zn','MIN')               
		,MinZr		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Zr','MIN')

		,MaxAl		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Al','MAX')               
		,MaxAs		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'As','MAX')               
		,MaxB		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'B ','MAX')                
		,MaxBi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Bi','MAX')               
		,MaxC		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'C','MAX')                
		,MaxC8		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'C8','MAX')               
		,MaxCE		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE','MAX')               
		,MaxCE1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE1','MAX')              
		,MaxCE2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE2','MAX')              
		,MaxCE3		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE3','MAX')              
		,MaxCa		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ca','MAX')               
		,MaxCb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cb','MAX')               
		,MaxCo		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Co','MAX')                
		,MaxCorrIdx	= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CorrIdx','MAX')         
		,MaxCr		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cr','MAX')               
		,MaxCu		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cu','MAX')               
		,MaxD1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'D1','MAX')               
		,MaxDI		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'DI','MAX')               
		,MaxE1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'E1','MAX')               
		,MaxE8		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'E8','MAX')               
		,MaxFP		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'FP','MAX')               
		,MaxFe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Fe','MAX')              
		,MaxI		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'I ','MAX')                
		,MaxId		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Id','MAX')               
		,MaxLiq		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Liq','MAX')              
		,MaxM2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'M2','MAX')               
		,MaxMg		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mg','MAX')               
		,MaxMn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mn','MAX')               
		,MaxMn2S	= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mn2S','MAX')             
		,MaxMo		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mo','MAX')               
		,MaxN		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'N','MAX')                
		,MaxNb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Nb','MAX')               
		,MaxNi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ni','MAX')               
		,MaxP		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'P','MAX')                
		,MaxPC		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'PC','MAX')               
		,MaxPb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Pb','MAX')               
		,MaxS		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S','MAX')                
		,MaxS1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S1','MAX')               
		,MaxS2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S2','MAX')               
		,MaxS3		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S3','MAX')               
		,MaxS4		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S4','MAX')               
		,MaxS5		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S5','MAX')               
		,MaxSb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Sb','MAX')               
		,MaxSe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Se','MAX')               
		,MaxSi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Si','MAX')               
		,MaxSn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Sn','MAX')               
		,MaxTa		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ta','MAX')               
		,MaxTe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Te','MAX')               
		,MaxTi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ti','MAX')               
		,MaxV		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'V','MAX')                
		,MaxW		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'W','MAX')                
		,MaxZn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Zn','MAX')               
		,MaxZr		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Zr','MAX')  

		,AimAl		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Al','AIM')               
		,AimAs		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'As','AIM')               
		,AimB		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'B ','AIM')                
		,AimBi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Bi','AIM')               
		,AimC		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'C','AIM')                
		,AimC8		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'C8','AIM')               
		,AimCE		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE','AIM')               
		,AimCE1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE1','AIM')              
		,AimCE2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE2','AIM')              
		,AimCE3		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CE3','AIM')              
		,AimCa		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ca','AIM')               
		,AimCb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cb','AIM')               
		,AimCo		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Co','AIM')                
		,AimCorrIdx	= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'CorrIdx','AIM')         
		,AimCr		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cr','AIM')               
		,AimCu		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Cu','AIM')               
		,AimD1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'D1','AIM')               
		,AimDI		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'DI','AIM')               
		,AimE1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'E1','AIM')               
		,AimE8		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'E8','AIM')               
		,AimFP		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'FP','AIM')               
		,AimFe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Fe','AIM')              
		,AimI		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'I ','AIM')                
		,AimId		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Id','AIM')               
		,AimLiq		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Liq','AIM')              
		,AimM2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'M2','AIM')               
		,AimMg		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mg','AIM')               
		,AimMn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mn','AIM')               
		,AimMn2S	= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mn2S','AIM')             
		,AimMo		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Mo','AIM')               
		,AimN		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'N','AIM')                
		,AimNb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Nb','AIM')               
		,AimNi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ni','AIM')               
		,AimP		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'P','AIM')                
		,AimPC		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'PC','AIM')               
		,AimPb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Pb','AIM')               
		,AimS		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S','AIM')                
		,AimS1		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S1','AIM')               
		,AimS2		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S2','AIM')               
		,AimS3		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S3','AIM')               
		,AimS4		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S4','AIM')               
		,AimS5		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'S5','AIM')               
		,AimSb		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Sb','AIM')               
		,AimSe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Se','AIM')               
		,AimSi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Si','AIM')               
		,AimSn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Sn','AIM')               
		,AimTa		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ta','AIM')               
		,AimTe		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Te','AIM')               
		,AimTi		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Ti','AIM')               
		,AimV		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'V','AIM')                
		,AimW		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'W','AIM')                
		,AimZn		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Zn','AIM')               
		,AimZr		= [LAB].[GetGradeSpecsLimit](grade.Id,record.GradeVersionId,'Zr','AIM')  
	into #temp
	FROM LAB.ChemistrySamplesWithCalculated record
		LEFT JOIN MES.Heats heat ON heat.Id = record.HeatId
		LEFT JOIN CAT.Grades grade ON grade.Id = heat.GradeId

	WHERE record.HeatId = @heatId


	select 
		[Code] = ElementCode
		,[Max]=cat.[Max]
		,[Min]=cat.[Min]
		,[Aim]=cat.[Aim]
	into #Limits
	FROM CAT.GradeSpecs cat
	JOIN CAT.GradeVersions gradeVersion ON gradeVersion.Id = cat.GradeVersionId
	JOIN CAT.Grades gr ON gr.Id = gradeVersion.GradeId
	CROSS APPLY @elements e
	WHERE gr.Id = @gradeId and cat.GradeVersionId = @gradeVersionId and ElementCode = e.[Name]
	
		SELECT [SampleIdCode], [C],[Mn],[S],[P],[Si],[Cu],[Ni],[Cr],[Mo],[Sn],[Nb],[V],[N],[Al],[B],[Ca],[Pb],[Ti],[Mn2S],[Liq],[CorrIdx],[CE]
		,[FP],[Fe],[Zn],[Sb],[As],[Co],[Zr],[W],[Ta],[Bi],[Se],[Te],[Mg],[Cb],[E1],[CE2],[CE3],[CE1],[E8],[PC],[I],[S1],[S2],[S3],[S4],[S5]
		,[M2],[C8],[D1],[DI]
			into #MaxLimits
			FROM
			(
				select
				[SampleIdCode] = 'MAX', 
				Code,
				[Max]
				from
				#Limits
			)t
			Pivot(
				MAX([Max])
				FOR [Code]
				IN ([C],[Mn],[S],[P],[Si],[Cu],[Ni],[Cr],[Mo],[Sn],[Nb],[V],[N],[Al],[B],[Ca],[Pb],[Ti],[Mn2S],[Liq],[CorrIdx],[CE]
					,[FP],[Fe],[Zn],[Sb],[As],[Co],[Zr],[W],[Ta],[Bi],[Se],[Te],[Mg],[Cb],[E1],[CE2],[CE3],[CE1],[E8],[PC],[I],[S1],[S2],[S3],[S4],[S5]
					,[M2],[C8],[D1],[DI]
				) 
			) AS PivotTable

	SELECT [SampleIdCode], [C],[Mn],[S],[P],[Si],[Cu],[Ni],[Cr],[Mo],[Sn],[Nb],[V],[N],[Al],[B],[Ca],[Pb],[Ti],[Mn2S],[Liq],[CorrIdx],[CE]
		,[FP],[Fe],[Zn],[Sb],[As],[Co],[Zr],[W],[Ta],[Bi],[Se],[Te],[Mg],[Cb],[E1],[CE2],[CE3],[CE1],[E8],[PC],[I],[S1],[S2],[S3],[S4],[S5]
		,[M2],[C8],[D1],[DI]
			into #MinLimits
			FROM
			(
				select
				[SampleIdCode] = 'MIN', 
				Code,
				[Min]
				from
				#Limits
			)t
			Pivot(
				MIN([Min])
				FOR [Code]
				IN ([C],[Mn],[S],[P],[Si],[Cu],[Ni],[Cr],[Mo],[Sn],[Nb],[V],[N],[Al],[B],[Ca],[Pb],[Ti],[Mn2S],[Liq],[CorrIdx],[CE]
					,[FP],[Fe],[Zn],[Sb],[As],[Co],[Zr],[W],[Ta],[Bi],[Se],[Te],[Mg],[Cb],[E1],[CE2],[CE3],[CE1],[E8],[PC],[I],[S1],[S2],[S3],[S4],[S5]
					,[M2],[C8],[D1],[DI]
				) 
			) AS PivotTable

	
	SELECT [SampleIdCode], [C],[Mn],[S],[P],[Si],[Cu],[Ni],[Cr],[Mo],[Sn],[Nb],[V],[N],[Al],[B],[Ca],[Pb],[Ti],[Mn2S],[Liq],[CorrIdx],[CE]
		,[FP],[Fe],[Zn],[Sb],[As],[Co],[Zr],[W],[Ta],[Bi],[Se],[Te],[Mg],[Cb],[E1],[CE2],[CE3],[CE1],[E8],[PC],[I],[S1],[S2],[S3],[S4],[S5]
		,[M2],[C8],[D1],[DI]
			into #AimLimits
			FROM
			(
				select
				[SampleIdCode] = 'AIM', 
				Code,
				[Aim]
				from
				#Limits
			)t
			Pivot(
				AVG([Aim])
				FOR [Code]
				IN ([C],[Mn],[S],[P],[Si],[Cu],[Ni],[Cr],[Mo],[Sn],[Nb],[V],[N],[Al],[B],[Ca],[Pb],[Ti],[Mn2S],[Liq],[CorrIdx],[CE]
					,[FP],[Fe],[Zn],[Sb],[As],[Co],[Zr],[W],[Ta],[Bi],[Se],[Te],[Mg],[Cb],[E1],[CE2],[CE3],[CE1],[E8],[PC],[I],[S1],[S2],[S3],[S4],[S5]
					,[M2],[C8],[D1],[DI]
				) 
			) AS PivotTable



	select * from #temp --union all
	select [Id]= NULL,[HeatId]=NULL, [LocalTimestamp] = NULL,GradeId = NULL, GradeName=NULL,HeatName=NULL,* from #MaxLimits union all	
	select [Id]= NULL,[HeatId]=NULL, [LocalTimestamp] = NULL,GradeId = NULL, GradeName=NULL,HeatName=NULL,* from #AimLimits union all
	select [Id]= NULL,[HeatId]=NULL, [LocalTimestamp] = NULL,GradeId = NULL, GradeName=NULL,HeatName=NULL,* from #MinLimits 


		-----------------------
		-- Navigate Buttons
		-----------------------
		
		select top 5 [Text]= 'EAF', Treatment = null, HeatName = h.[Name], HeatId = h.Id
		from EAF.Processes p 
		left join mes.Heats h on h.Id = p.HeatId
		where p.HeatId = @heatId
		
		union all
		
		select top 5 [Text]= 'LMF', Treatment = p.Treatment, HeatName = h.[Name], HeatId = h.Id 
		from LF.Processes p 
		left join mes.Heats h on h.Id = p.HeatId
		where p.HeatId = @heatId

		union all
		
		select top 5 [Text]= 'VTD', Treatment = p.Treatment, HeatName = h.[Name], HeatId = h.Id
		from VTD.Processes p 
		left join mes.Heats h on h.Id = p.HeatId
		where p.HeatId = @heatId

		union all
				
		select top 5 [Text]= 'SLAG EAF', Treatment = null, HeatName = h.[Name], HeatId = h.Id
		from LAB.SlagChemistrySamples l
		left join mes.Heats h on h.Id = l.HeatId
		where l.HeatId = @heatId

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

	RETURN 0
END

GO


USE [MES]
GO
/****** Object:  StoredProcedure [LAB].[UpdateChemistries]    Script Date: 16 feb. 2021 22:23:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAB].[UpdateChemistries]
	@HeatId INT
	,@TypeCode varchar(10)	
	,@SampleIdCode varchar(10)	
	--if an element is modified
	,@ElementCode varchar(10) = NULL
	,@ElementCodeValue varchar(10)  = NULL	
	--if the datetime is modified
	,@Timestamp datetime = NULL
	,@timeZoneCode CHAR(10) = NULL		
	,@currentUser VARCHAR(MAX) = 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)

	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
		+ ', @heatId: ' + ISNULL(CAST(@heatId AS VARCHAR), '')
		+ ', @TypeCode: ' + ISNULL(CAST(@TypeCode AS VARCHAR), '')
		+ ', @SampleIdCode: ' + ISNULL(CAST(@SampleIdCode AS VARCHAR), '')
		+ ', @ElementCode: ' + ISNULL(CAST(@ElementCode AS VARCHAR), '')
		+ ', @ElementCodeValue: ' + ISNULL(CAST(@ElementCodeValue AS VARCHAR), '')
		+ ', @Timestamp: ' + ISNULL(CAST(@Timestamp AS VARCHAR), '')
		+ ', @currentUser: ' + ISNULL(CAST(@currentUser AS VARCHAR), '')
		+ ', @timeZoneCode: ' + ISNULL(CAST(@timeZoneCode AS VARCHAR), '')

	-----------------------
	-- Variables
	-----------------------
				
	DECLARE @buf NVARCHAR(MAX)
	
	DECLARE @ChemistrySampleId INT
	DECLARE @heatName varchar(10) = (select [Name] from MES.Heats where Id = @HeatId)
	DECLARE @PreviousElementValue table ([value] varchar(20))
	DECLARE @LastTimestamp NVARCHAR(MAX)
	DECLARE	@result INT
	DECLARE @logMsg VARCHAR(MAX)

	DECLARE @ColumnName varchar(20)
	-----------------------
	-- Validation
	-----------------------
	IF (@timeZoneCode IS NULL)	SET @timeZoneCode = 'EDTIND'

	IF @ElementCode = 'Sample'
		SET @ColumnName = 'SampleIdCode'
	ELSE 
		SET @ColumnName = @ElementCode

	DECLARE @msg VARCHAR(MAX),
			@moduleName VARCHAR(200) 


	-----------------------
	-- Begin Query
	-----------------------
		-------------------------
		------ GET ROW ID ------- 
		-------------------------
		SELECT 
			@ChemistrySampleId = Id 
		FROM LAB.ChemistrySamplesWithCalculated
		WHERE HeatId = @heatId AND SampleIdCode = @sampleIdCode AND TypeCode = @TypeCode

		


		----------------------------
		----- DYNAMIC UPDATE -------
		----------------------------

		IF @Timestamp IS NULL
		BEGIN
			BEGIN TRY
				
				--------------------------------
				--- AUDIT ELEMENT CODE DATA ----
				--------------------------------

				
				SET  @buf= '
				(SELECT '+cast(@ColumnName as varchar(12))+'
				FROM
				LAB.ChemistrySamplesWithCalculated
				WHERE Id =  '+cast(@ChemistrySampleId as varchar(10))+'
				)'
			
				INSERT INTO @PreviousElementValue
				EXECUTE sp_executesql @statement=@buf

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


					SET @msg = 'Chemistry Element Value Changed : ' +  CAST(@ColumnName as varchar(10))  + 						
							' - Prev: ' + CAST((select [value] from @PreviousElementValue) as varchar(10)) +
							' - New: ' + CAST(@ElementCodeValue as varchar(10)) +
							' - Heat : ' + CAST(@heatName as varchar(10)) +
							' - Location : ' + CAST(@TypeCode as varchar(10)) +
							' - Id : ' + CAST(@SampleIdCode as varchar(10)) +
							' - Chemistry Sample Id: ' + CAST(@ChemistrySampleId as varchar(10)) 
			
				EXEC SYSTEM.AuditMsg @message = @msg, @moduleName = @moduleName, @currentUser = @currentUser
				
	SELECT @ElementCode,@ColumnName, @ChemistrySampleId
				----------------------------
				--- UPDATE ELEMENT CODE ----
				----------------------------
				IF (@ColumnName= 'TypeCode')
				BEGIN

					UPDATE lab.ChemistrySamples
						set [TypeCode] = @ElementCodeValue
					WHERE Id = @ChemistrySampleId


				END
				ELSE IF (@ColumnName = 'SampleIdCode')
				BEGIN
					
					SELECT 'SampleIdCode'

					UPDATE lab.ChemistrySamples
						set [SampleIdCode] = @ElementCodeValue
					WHERE Id = @ChemistrySampleId
				END
				ELSE
				BEGIN

					set  @buf= '
					UPDATE
					LAB.ChemistrySamplesWithCalculated
					SET  '+cast(@ColumnName as nvarchar(10))+' ='+cast(@elementCodeValue as nvarchar(10))+'
					WHERE Id =  '+cast(@ChemistrySampleId as nvarchar(10))+'
					'		
					EXECUTE sp_executesql @statement=@buf
				END
				
			
			
			END TRY
			BEGIN CATCH
				SELECT @result = ERROR_NUMBER()
						,@logMsg = ERROR_MESSAGE()

				SET @logMsg = 'Failed UPDATE ELEMENT CODE: '  + @logMsg
					RAISERROR( @logMsg ,0,1) WITH NOWAIT

				EXEC SYSTEM.LogMsg @message = @logMsg
					,@procedure = @procName
					,@typeCode = 'ERROR'
					,@logError = 1
			END CATCH


		END
		ELSE 
		BEGIN
			BEGIN TRY

				-----------------------------------
				--- AUDIT TIMESTAMP DATA ----------
				-----------------------------------
			
				SELECT
				@moduleName = CAT.GetAuditModuleName(),
				@msg = ''

				SET @msg = 'Chemistry Timestamp  Changed - Prev : ' + CAST((SELECT IH.ToLocal([Timestamp], @timeZoneCode) FROM LAB.ChemistrySamplesWithCalculated WHERE Id =  @ChemistrySampleId) as varchar(20)) +
					' - New : ' + CAST( CONVERT(VARCHAR,@Timestamp,120) as varchar(20)) + 
					' - Heat : ' + CAST(@heatName as varchar(10)) +
					' - Location : ' + CAST(@TypeCode as varchar(10)) +
					' - Id : ' + CAST(@SampleIdCode as varchar(10)) +
					' - Chemistry Sample Id: ' + CAST(@ChemistrySampleId as varchar(10)) 		
			
				EXEC SYSTEM.AuditMsg @message = @msg, @moduleName = @moduleName, @currentUser = @currentUser

				--------------------------
				--- UPDATE TIMESTAMP  ----
				--------------------------
				UPDATE
				LAB.ChemistrySamplesWithCalculated
				SET  [Timestamp] = IH.ToUTC(@Timestamp, @timeZoneCode)
				WHERE Id =  @ChemistrySampleId

			
			END TRY
			BEGIN CATCH
				SELECT @result = ERROR_NUMBER()
						,@logMsg = ERROR_MESSAGE()

				SET @logMsg = 'Failed UPDATE TIMESTAMP: '  + @logMsg
					RAISERROR( @logMsg ,0,1) WITH NOWAIT

				EXEC SYSTEM.LogMsg @message = @logMsg
					,@procedure = @procName
					,@typeCode = 'ERROR'
					,@logError = 1
			END CATCH
		END
		
		SELECT [Id] = @ChemistrySampleId
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END
GO

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------ 01 April 2021 ----------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------


ALTER TABLE lab.ChemistrySamples
    ADD Modified Bit NOT NULL 
DEFAULT (0)


USE [MES]
GO
/****** Object:  StoredProcedure [LAB].[UpdateChemistries]    Script Date: 1 abr. 2021 16:57:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAB].[UpdateChemistries]
	@HeatId INT
	,@TypeCode varchar(10)	
	,@SampleIdCode varchar(10)	
	--if an element is modified
	,@ElementCode varchar(10) = NULL
	,@ElementCodeValue varchar(10)  = NULL	
	--if the datetime is modified
	,@Timestamp datetime = NULL
	,@timeZoneCode CHAR(10) = NULL		
	,@currentUser VARCHAR(MAX) = 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)

	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
		+ ', @heatId: ' + ISNULL(CAST(@heatId AS VARCHAR), '')
		+ ', @TypeCode: ' + ISNULL(CAST(@TypeCode AS VARCHAR), '')
		+ ', @SampleIdCode: ' + ISNULL(CAST(@SampleIdCode AS VARCHAR), '')
		+ ', @ElementCode: ' + ISNULL(CAST(@ElementCode AS VARCHAR), '')
		+ ', @ElementCodeValue: ' + ISNULL(CAST(@ElementCodeValue AS VARCHAR), '')
		+ ', @Timestamp: ' + ISNULL(CAST(@Timestamp AS VARCHAR), '')
		+ ', @currentUser: ' + ISNULL(CAST(@currentUser AS VARCHAR), '')
		+ ', @timeZoneCode: ' + ISNULL(CAST(@timeZoneCode AS VARCHAR), '')

	-----------------------
	-- Variables
	-----------------------
				
	DECLARE @buf NVARCHAR(MAX)
	
	DECLARE @ChemistrySampleId INT
	DECLARE @heatName varchar(10) = (select [Name] from MES.Heats where Id = @HeatId)
	DECLARE @PreviousElementValue table ([value] varchar(20))
	DECLARE @LastTimestamp NVARCHAR(MAX)
	DECLARE	@result INT
	DECLARE @logMsg VARCHAR(MAX)

	DECLARE @ColumnName varchar(20)
	-----------------------
	-- Validation
	-----------------------
	IF (@timeZoneCode IS NULL)	SET @timeZoneCode = 'EDTIND'

	IF @ElementCode = 'Sample'
		SET @ColumnName = 'SampleIdCode'
	ELSE 
		SET @ColumnName = @ElementCode

	DECLARE @msg VARCHAR(MAX),
			@msg2 VARCHAR(MAX),
			@moduleName VARCHAR(200) 


	-----------------------
	-- Begin Query
	-----------------------
		-------------------------
		------ GET ROW ID ------- 
		-------------------------
		SELECT 
			@ChemistrySampleId = Id 
		FROM LAB.ChemistrySamplesWithCalculated
		WHERE HeatId = @heatId AND SampleIdCode = @sampleIdCode AND TypeCode = @TypeCode

		


		----------------------------
		----- DYNAMIC UPDATE -------
		----------------------------

		IF @Timestamp IS NULL
		BEGIN
			BEGIN TRY
				SET @msg2 = NULL
			
				
				----------------------------
				--- UPDATE ELEMENT CODE ----
				----------------------------

				IF (@ColumnName= 'TypeCode')
				BEGIN

					UPDATE lab.ChemistrySamples
						set [TypeCode] = @ElementCodeValue
					WHERE Id = @ChemistrySampleId

					SET @msg2 = 'Type Code Value Changed : '

				END
				ELSE IF (@ColumnName = 'SampleIdCode')
				BEGIN
					
					SELECT 'SampleIdCode'

					UPDATE lab.ChemistrySamples
						set [SampleIdCode] = @ElementCodeValue
					WHERE Id = @ChemistrySampleId

					
					SET @msg2 = 'Sample Id Code Value Changed : '
				END
				ELSE
				BEGIN

					set  @buf= '
					UPDATE
					lab.ChemistrySamples
					SET  '+cast(@ColumnName as nvarchar(10))+' ='+cast(@elementCodeValue as nvarchar(10))+'
					WHERE Id =  '+cast(@ChemistrySampleId as nvarchar(10))+'
					'		
					EXECUTE sp_executesql @statement=@buf

					
					SET @msg2 = 'Chemistry Element Value Changed : '

				END
				
				--------------------------------
				--- AUDIT ELEMENT CODE DATA ----
				--------------------------------

				
				SET  @buf= '
				(SELECT '+cast(@ColumnName as varchar(12))+'
				FROM
				lab.ChemistrySamples
				WHERE Id =  '+cast(@ChemistrySampleId as varchar(10))+'
				)'
			
				INSERT INTO @PreviousElementValue
				EXECUTE sp_executesql @statement=@buf

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

				SET @msg = @msg2 +  CAST(@ColumnName as varchar(10))  + 						
							' - Prev: ' + CAST((select [value] from @PreviousElementValue) as varchar(10)) +
							' - New: ' + CAST(@ElementCodeValue as varchar(10)) +
							' - Heat : ' + CAST(@heatName as varchar(10)) +
							' - Location : ' + CAST(@TypeCode as varchar(10)) +
							' - Id : ' + CAST(@SampleIdCode as varchar(10)) +
							' - Chemistry Sample Id: ' + CAST(@ChemistrySampleId as varchar(10)) 
			
				EXEC SYSTEM.AuditMsg @message = @msg, @moduleName = @moduleName, @currentUser = @currentUser

						
				
				UPDATE lab.ChemistrySamples
					set [Comments] = '[User : ' + cast (@currentUser as varchar(max))
					+ ' Datetime : ' + cast(CONVERT(varchar,IH.ToLocal(GETUTCDATE(),@timezoneCode),20) as varchar(max))
					+ ' Msg : ' + isnull(@msg,'')  + ' ] ' + isnull([Comments],'') 
					,[Modified] = 1
				WHERE Id = @ChemistrySampleId	
			
			
			END TRY
			BEGIN CATCH
				SELECT @result = ERROR_NUMBER()
						,@logMsg = ERROR_MESSAGE()

				SET @logMsg = 'Failed UPDATE ELEMENT CODE: '  + @logMsg
					RAISERROR( @logMsg ,0,1) WITH NOWAIT

				EXEC SYSTEM.LogMsg @message = @logMsg
					,@procedure = @procName
					,@typeCode = 'ERROR'
					,@logError = 1
			END CATCH


		END
		ELSE 
		BEGIN
			BEGIN TRY
					
				--------------------------
				--- UPDATE TIMESTAMP  ----
				--------------------------
				UPDATE
					lab.ChemistrySamples
				SET  [Timestamp] = IH.ToUTC(@Timestamp, @timeZoneCode)
				WHERE Id =  @ChemistrySampleId

				-----------------------------------
				--- AUDIT TIMESTAMP DATA ----------
				-----------------------------------
			
				SELECT
				@moduleName = CAT.GetAuditModuleName(),
				@msg = ''

				SET @msg = 'Chemistry Timestamp  Changed - Prev : ' + CAST((SELECT IH.ToLocal([Timestamp], @timeZoneCode) FROM LAB.ChemistrySamplesWithCalculated WHERE Id =  @ChemistrySampleId) as varchar(20)) +
					' - New : ' + CAST( CONVERT(VARCHAR,@Timestamp,120) as varchar(20)) + 
					' - Heat : ' + CAST(@heatName as varchar(10)) +
					' - Location : ' + CAST(@TypeCode as varchar(10)) +
					' - Id : ' + CAST(@SampleIdCode as varchar(10)) +
					' - Chemistry Sample Id: ' + CAST(@ChemistrySampleId as varchar(10)) 		
			
				EXEC SYSTEM.AuditMsg @message = @msg, @moduleName = @moduleName, @currentUser = @currentUser
				
				
				UPDATE lab.ChemistrySamples
					set [Comments] = '[User : ' + cast (@currentUser as varchar(max))
					+ ' Datetime : ' + cast(CONVERT(varchar,IH.ToLocal(GETUTCDATE(),@timezoneCode),20) as varchar(max))
					+ ' Msg : ' + isnull(@msg,'')  + ' ] ' + isnull([Comments],'') 
					,[Modified] = 1
				WHERE Id = @ChemistrySampleId	
			
			
			END TRY
			BEGIN CATCH
				SELECT @result = ERROR_NUMBER()
						,@logMsg = ERROR_MESSAGE()

				SET @logMsg = 'Failed UPDATE TIMESTAMP: '  + @logMsg
					RAISERROR( @logMsg ,0,1) WITH NOWAIT

				EXEC SYSTEM.LogMsg @message = @logMsg
					,@procedure = @procName
					,@typeCode = 'ERROR'
					,@logError = 1
			END CATCH
		END
		
		SELECT [Id] = @ChemistrySampleId
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END
GO