USE [IHBoxSystem]
GO
CREATE ROLE lab_heat_report_readWrite AUTHORIZATION dbo 
GO  

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

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


USE [IHBoxSystem]
GO
CREATE ROLE lab_heat_report_read AUTHORIZATION dbo 
GO  

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

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


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

INSERT INTO [FrontEnd].[Routes]
		   ([Route]
		   ,[ModuleName]
		   ,[ModulePath]
		   ,[RequiredRole]
	   )
	 VALUES
		   (
		   '!/lab-heat-report(/:heatName)(/)'
		   , 'lab-heat-report'
		   , 'custom-screens/lab-heat-report/'
		   , 'lab_heat_report_read'           
		   )
		   SET @result_routeId = @@IDENTITY
INSERT INTO [FrontEnd].[Menu]
		   ([Parent]
		   ,[Name]
		   ,[RouteID]
		   ,[SortOrder]
		   ,[Enabled]
		   ,[Visible]
		   ,[RequiredRole]
		   ,[Group]
		  )
	 VALUES
		   (
		   @parent
		   ,'Heat Report'
		   ,@result_routeId 
		   ,1
		   ,1
		   ,1
		   ,'lab_heat_report_read'
		   ,NULL
		 )
GO

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

DECLARE @ModuleId INT

INSERT INTO [SECURITY].[Modules]
		   ([Code]
		   ,[Name])
	 VALUES
		   ('lab-heat-report'
		   ,'Lab Heat Report')

		  SET @ModuleId = @@IDENTITY

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


	INSERT INTO [SECURITY].ModuleActions(ModuleId,Code,[Name],[Role])
	VALUES (@ModuleId,'read','Read','lab_heat_report_read')
GO
/*------------------------------------------------------------------------------------------------------------------------*/


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

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



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


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


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


USE [MES]
GO
/****** Object:  StoredProcedure [LAB].[GetHeatReportExcel]    Script Date: 18/10/2023 16:02:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAB].[GetHeatReportExcel]
	@heatId INT,
	@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), '')
		+ ', @timeZoneCode: ' + ISNULL(CAST(@timeZoneCode AS VARCHAR), '')

		
	-----------------------
	-- Variables
	-----------------------
	-- Grade and Limits
	declare @gradeId int
	declare @gradeVersionId int	


	-- Chemistry Columns and SQL Query 
	DECLARE @colnameList varchar(MAX)	
	DECLARE @colnameList2 varchar(MAX)
	DECLARE @SQLQuery NVARCHAR(MAX)

	--Navigation
	DECLARE @newHeatId INT,
			@start DATETIME
			
	----------------------------------------------
	-------- GET COLUMNS FROM THE VIEW -----------
	----------------------------------------------
	declare @elements table ([Name] char(10))
	declare @elements2 table ([Name] char(10))

	declare @viewColumns table (Id int, [Name] char(10))

	insert into @viewColumns
	select 
			c.column_id,
			c.[name]
	from sys.columns c
	join sys.views v 
			on v.object_id = c.object_id
			where object_name(c.object_id) = 'ChemistrySamplesWithCalculated'
			and c.column_id > 7
	order by schema_name(v.schema_id) ,
				object_name(c.object_id),
				column_id;


	insert into @elements([Name])
	select '[' + rtrim(ltrim([Name])) + ']'
	from @viewColumns
	order by Id asc
	
	insert into @elements2([Name])
	select rtrim(ltrim([Name]))
	from @viewColumns
	order by Id asc

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

	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
	

	---------------------------------------------------
	------------------- BEGIN QUERY -------------------
	---------------------------------------------------

	----------------------------------------
	--- Step 1: Get the Chemistries by Heat 
	----------------------------------------
	select 
		[TypeSample] =h.[Name] + '-' + TypeCode + '-'  + SampleIdCode 
		,c.*
		,[Datetime] = IH.ToLocal([Timestamp],'EDTIND')
	into #chemistriesByHeat
	from LAB.ChemistrySamplesWithCalculated  c
	left join MES.Heats h on h.Id = c.HeatId
	where h.Id = @heatId
	order by [Timestamp] desc

	------------------------------------------------
	--- Step 2: Get the Chemistry Codes in a string
	------------------------------------------------
	SET @colnameList = NULL
	--SELECT @colnameList = COALESCE(@colnameList + ',','') + ltrim(rtrim(([Name]))) +  ' = cast(' + ltrim(rtrim(([Name]))) + ' as varchar)'
	SELECT @colnameList = COALESCE(@colnameList + ',','') + ltrim(rtrim(([Name]))) +  ' = cast(isNull(' + ltrim(rtrim(([Name]))) + ',0) as varchar)'	
	FROM @elements;	

	DECLARE @colnameList3 varchar(MAX) = NULL
	SELECT @colnameList3 = COALESCE(@colnameList3 + ',','') + ltrim(rtrim(([Name]))) 
	FROM @elements;	
	

	------------------------------------------------------------------------------
	--- Step 3: Unpivot #chemistriesByHeat to get the Chemistry Codes as rows
	------------------------------------------------------------------------------

	SET @SQLQuery ='
	select [TypeCode], [SampleIdCode],[TypeSample], Code, [Value]
	from (select [TypeCode], [SampleIdCode],[TypeSample],'+@colnameList+'
		from #chemistriesByHeat	) p		
	UNPIVOT (
		[Value] for Code IN
		('+@colnameList3+')
	) AS unpvt2 '
	
	declare @unPivotChemistriesByHeat table (TypeCode varchar(max),SampleIdCode varchar(max), TypeSample varchar(max),[Code] varchar(50), [Value] decimal(18,7))

	insert into @unPivotChemistriesByHeat
	EXEC(@SQLQuery)

	
	
	------------------------------------------------------------------------------
	--- Step 4: Get the different combination between Type Codes and Sample Code Ids
	------------------------------------------------------------------------------
	select distinct(TypeSample) 
	into #TypesAndSampleS
	from @unPivotChemistriesByHeat
	
	-----------------------------------------------------------
	--- Step 5: Get the Type-Sample combinations in a string.
	-----------------------------------------------------------
	SET @colnameList2 = NULL

	SELECT @colnameList2 = COALESCE(@colnameList2 + ',','') + '[' + TypeSample + ']'
	FROM #TypesAndSampleS;


	DECLARE @colnameList4  VARCHAR (max)= NULL

	SELECT @colnameList4 = COALESCE(@colnameList4 + ' ,[' + TypeSample + '] = cast(', + '[' + TypeSample + '] = cast(') + '[' + TypeSample + '] as varchar)'
	FROM #TypesAndSampleS;
    ----------------------------------------------------------------------------------------------------------
	--- Step 6: FInal Table - Pivot the @unPivotChemistriesByHeat Table to obatin the "Type-Sample" as columns
	-----------------------------------------------------------------------------------------------------------
	
	select *
	into #unPivotChemistriesByHeat
	from @unPivotChemistriesByHeat

	
	IF OBJECT_ID('dbo.ChemistriesByHeat', 'U') IS NOT NULL 
	DROP TABLE dbo.ChemistriesByHeat; 	
		
		
	IF OBJECT_ID('dbo.ChemistriesByHeatVARCHAR', 'U') IS NOT NULL 
	DROP TABLE dbo.ChemistriesByHeatVARCHAR; 	

	
	IF OBJECT_ID('dbo.ChemistriesByHeatTIMES', 'U') IS NOT NULL 
	DROP TABLE dbo.ChemistriesByHeatTIMES; 	


	SET @SQLQuery = NULL
	SET @SQLQuery ='
	select [Code] AS [Code],'+@colnameList2+'
	into dbo.ChemistriesByHeat
	from (
		select [Code],[Value],[TypeSample] from #unPivotChemistriesByHeat
	
	) AS SourceTable
	PIVOT
	(
		AVG([Value])
		for [TypeSample] in ('+@colnameList2+')
	) as pivotTable'

	EXEC(@SQLQuery)

	

	----------------------------
	--- Step 7: Grade and Limits
	----------------------------

	declare @ElementsLimits table ([Code] varchar(20),[Min] float, [Aim] float, [Max] float)
	declare @ElementsOrder table ([Code] varchar(20), [Order] int)
	
	insert into @ElementsOrder
	select 
		ElementCode 
		,[Order]
	from CAT.ChemistryElementOrder
	where ElementCode in (select [Code] from @unPivotChemistriesByHeat)

   insert into @ElementsLimits
   select 
		ElementCode		
		,cat.[Min]
		,cat.[Aim]
		,cat.[Max]
	FROM CAT.GradeSpecs cat
	JOIN CAT.GradeVersions gradeVersion ON gradeVersion.Id = cat.GradeVersionId
	JOIN CAT.Grades gr ON gr.Id = gradeVersion.GradeId
	CROSS APPLY @elements2 e
	WHERE gr.Id = @gradeId and cat.GradeVersionId = @gradeVersionId and ElementCode = e.[Name]
	
    ----------------------------
	--- Step 8: Result Table
	----------------------------
		select 
		 TypeSample,
		 [Time]  =left(cast(IH.ToLocal([Timestamp],@timeZoneCode) as [Time]),5),
		 rn = ROW_NUMBER() over(order by [Timestamp] asc)
		 into #times
		from #chemistriesByHeat
		order by [Timestamp] asc

		

		IF OBJECT_ID('dbo.ChemistriesByHeat', 'U') IS NOT NULL
		begin

			SET @SQLQuery ='
		select [Code], '+@colnameList4+'
		into dbo.ChemistriesByHeatVARCHAR
		from dbo.ChemistriesByHeat'
						
		EXEC (@SQLQuery)		
	
		SET @SQLQuery ='
		SELECT *
		into dbo.ChemistriesByHeatTIMES
		FROM   
		(
			select 
				[TypeSample],
				[Datetime]  = convert(varchar,[Datetime],22) 
			from #chemistriesByHeat


		) t 
		PIVOT(
			MAX([Datetime]) 
			FOR [TypeSample] in ('+@colnameList2+')
		) AS pivot_table;'
		EXEC (@SQLQuery)
	
		
		--- last table is an union query
		select 
			[Code] = 'Datetime', 
			*, 
			[Min] = NULL, 
			[Aim] = NULL,
			[Max] =NULL,
			[Order] = 0  
		INTO #excelresulTable
		from  dbo.ChemistriesByHeatTIMES		
		union all
		select	
				h.*
				,l.[Min]
				,l.[Aim]
				,l.[Max]
				,o.[Order]
			from dbo.ChemistriesByHeatVARCHAR h
			left join @ElementsLimits l on l.Code = h.Code
			left join @ElementsOrder o on o.Code = l.Code
			
			select * from #excelresulTable 
			order by case when [Order] is null then 1 else 0 end, [Order]
		
		end




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

	RETURN 0
END

GO

-----------------------------------------------------------
USE [MES]
GO
/****** Object:  StoredProcedure [LAB].[GetChemistrySamplesWithCalculatedByHeat]    Script Date: 18/10/2023 16:01:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAB].[GetChemistrySamplesWithCalculatedByHeat]
	@HeatName VARCHAR(10) = NULL
	,@timeZoneCode CHAR(10) = NULL
	,@getRealTimeData BIT = 0 --When the Real Time Report is first opened we need the last 10 chemistries.
	,@ExcelExport BIT = 0
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:'
		+ ', @timeZoneCode: ' + ISNULL(CAST(@timeZoneCode AS VARCHAR), '')

		
	-----------------------
	-- Variables
	-----------------------
	-- HeatId
	declare @heatId int

	--Navigate Buttons
	declare @buttons table ([Text] VARCHAR(50), HeatId INT, Treatment INT)
	
	-- Grade and Limits
	declare @gradeId int
	declare @gradeVersionId int	


	-- Chemistry Columns and SQL Query 
	DECLARE @colNamesDefinition varchar(MAX) = NULL
	DECLARE @colNameList varchar(MAX) = NULL
	DECLARE @colNamesByTypeSample varchar(MAX) = NULL

	DECLARE @SQLQuery NVARCHAR(MAX)

	--Navigation
	DECLARE @newHeatId INT,
			@start DATETIME


	declare @SampleByElementsAndLimits table(HeatTypeSample varchar(200), [Code] varchar(20),[Min] real, [Aim] real, [Max] real, GradeName varchar(20))

	
	----------------------------------------------
	-------- GET VIEW COLUMNS - ---------
	----------------------------------------------
	declare @viewColumns table (Id int, [Name] char(10),[Precision] int, [Scale] int,[Definition] varchar(MAX))

	insert into @viewColumns
	select 
			c.column_id,
			rtrim(ltrim(c.[Name])),
			c.[precision],
			c.scale,
			'[' + rtrim(ltrim(c.[name])) + ']'
	from sys.columns c
	join sys.views v 
			on v.object_id = c.object_id
			where object_name(c.object_id) = 'ChemistrySamplesWithCalculated'
			and c.column_id > 7
	order by schema_name(v.schema_id) ,
				object_name(c.object_id),
				column_id;

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

	IF (@HeatName IS NULL OR NOT EXISTS (select 1 from MES.Heats where [Name] = @HeatName))
	BEGIN	
		SET @HeatName = (select top 1 [Name] from MES.Heats order by ProductionDate desc, Id desc)
	END

	select 
		@heatId = Id
	from MES.Heats where [Name] = @HeatName

	-----------------------
	-- Export to Excel
	-----------------------
	IF (@ExcelExport = 1 and @heatId is not null)
	BEGIN
		EXEC [LAB].[GetHeatReportExcel] @heatId = @heatId --100331
	return 
	END

	---------------------------------------------------
	------------------- BEGIN QUERY -------------------
	---------------------------------------------------
	------------------------------------------------------------
	--- Step 0: Get the GradeId and GradeVersionId by HeatName -
	------------------------------------------------------------

	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 heat.[Name] = @HeatName

	----------------------------------------
	--- Step 1: Get the Chemistries by Heat 
	----------------------------------------	

	IF (@getRealTimeData = 0)
	BEGIN		
		select 
			[HeatTypeSample] = h.[Name] + '-' + TypeCode + '-'  + SampleIdCode 
			,[GradeId] = h.GradeId
			,[GradeVersion] = h.GradeVersionId
			,c.*
		into #chemistriesByHeat
		from LAB.ChemistrySamplesWithCalculated  c
		left join MES.Heats h on h.Id = c.HeatId
		left join CAT.Grades grade ON grade.Id = h.GradeId
		where h.Id = @heatId
		order by [Timestamp] desc

		insert into @SampleByElementsAndLimits
		select 
			r.HeatTypeSample
			,specs.ElementCode
			,specs.[Min]
			,specs.[Aim]
			,specs.[Max]
			,rtrim(ltrim(gr.[Name]))
		from #chemistriesByHeat r
		left join CAT.Grades gr ON gr.Id = r.GradeId										
		left join CAT.GradeSpecs specs on specs.GradeVersionId = r.GradeVersionId
		order by specs.[ElementCode], r.HeatTypeSample

	END
	ELSE
	BEGIN
		select top 10
			[HeatTypeSample] = h.[Name] + '-' + TypeCode + '-'  + SampleIdCode 
			,[GradeId] = h.GradeId
			,[GradeVersion] = h.GradeVersionId
			,c.*
		into #chemistriesByHeatRealTime
		from LAB.ChemistrySamplesWithCalculated  c
		left join MES.Heats h on h.Id = c.HeatId
		left join CAT.Grades grade ON grade.Id = h.GradeId
		order by [Timestamp] desc

		
		insert into @SampleByElementsAndLimits
		select 
			r.HeatTypeSample
			,specs.ElementCode
			,specs.[Min]
			,specs.[Aim]
			,specs.[Max]
			,rtrim(ltrim(gr.[Name]))
		from #chemistriesByHeatRealTime r
		left join CAT.Grades gr ON gr.Id = r.GradeId										
		left join CAT.GradeSpecs specs on specs.GradeVersionId = r.GradeVersionId
		order by specs.[ElementCode], r.HeatTypeSample
	END
	
	------------------------------------------------
	--- Step 2: Get the Chemistry Codes in a string
	------------------------------------------------
	SELECT @colNamesDefinition = COALESCE(@colNamesDefinition + ',','') + ltrim(rtrim(([Definition]))) +  ' = cast(isNull(' + ltrim(rtrim(([Definition]))) + ', NULL) as varchar(MAX)) ' 
	FROM @viewColumns;	

	SELECT @colNameList = COALESCE(@colNameList + ',','') + ltrim(rtrim(([Definition]))) 
	FROM @viewColumns;	

	------------------------------------------------------------------------------
	--- Step 3: Unpivot #chemistriesByHeat to get the Chemistry Codes as rows
	------------------------------------------------------------------------------
	IF (@getRealTimeData = 0)
	BEGIN		
		SET @SQLQuery ='
		select [TypeCode], [SampleIdCode],[HeatTypeSample], Code, [Value] = cast([Value] as varchar)
		from (select [TypeCode], [SampleIdCode],[HeatTypeSample],'+@colNamesDefinition+'
			from #chemistriesByHeat	) p		
		UNPIVOT (
			[Value] for Code IN
			('+@colNameList+')
		) AS unpvt2 '
	
	END
	ELSE
	BEGIN
	--		select [TypeCode], [SampleIdCode],[HeatTypeSample], Code, [Value] = cast([Value] as varchar)  + ''* ''  '+ ' 
		SET @SQLQuery ='
		select [TypeCode], [SampleIdCode],[HeatTypeSample], Code, [Value] = cast([Value] as varchar) 
		from (select [TypeCode], [SampleIdCode],[HeatTypeSample],'+@colNamesDefinition+'
			from #chemistriesByHeatRealTime	) p		
		UNPIVOT (
			[Value] for Code IN
			('+@colNameList+')
		) AS unpvt2 '

	END
	

	declare @unPivotChemistriesByHeat table (TypeCode varchar(max),SampleIdCode varchar(max), HeatTypeSample varchar(max),[Code] varchar(50), [Value] varchar(50))

	insert into @unPivotChemistriesByHeat(TypeCode,SampleIdCode, HeatTypeSample,[Code], [Value])
	EXEC(@SQLQuery)

	------------------------------------------------------------------------------
	--- Step 4: Get the different combination between Type Codes and Sample Code Ids
	------------------------------------------------------------------------------
	select distinct(HeatTypeSample) 
	into #TypesAndSampleS
	from @unPivotChemistriesByHeat
	
	-----------------------------------------------------------
	--- Step 5: Get the Type-Sample combinations in a string.
	-----------------------------------------------------------
	SELECT @colNamesByTypeSample = COALESCE(@colNamesByTypeSample + ',','') + '[' + HeatTypeSample + ']'
	FROM #TypesAndSampleS;

    ----------------------------------------------------------------------------------------------------------
	--- Step 6: FInal Table - Pivot the @unPivotChemistriesByHeat Table to obatin the "Type-Sample" as columns
	-----------------------------------------------------------------------------------------------------------
	select *
	into #unPivotChemistriesByHeat
	from @unPivotChemistriesByHeat

	SET @SQLQuery = NULL
	SET @SQLQuery ='
	select [Code] AS [Code],'+@colNamesByTypeSample+'
	from (
		select [Code]
		,[Value]
		,[HeatTypeSample] from #unPivotChemistriesByHeat
	
	) AS SourceTable
	PIVOT
	(
		MAX([Value])
		for [HeatTypeSample] in ('+@colNamesByTypeSample+')
	) as pivotTable'

	   
	EXEC(@SQLQuery)


	----------------------------
	--- Step 7: Order
	----------------------------
	
	declare @ElementsLimits table ([Code] varchar(20),[Min] float, [Aim] float, [Max] float)
	declare @ElementsOrder table ([Code] varchar(20), [Order] int)
	
	insert into @ElementsOrder
	select 
		ElementCode 
		,[Order]
	from CAT.ChemistryElementOrder
	where ElementCode in (select [Code] from @unPivotChemistriesByHeat)

	-- insert alias
	insert into @ElementsOrder
	select 
		Alias
		,[Order]
	from CAT.ChemistryElementOrder
	where Alias in (select [Code] from @unPivotChemistriesByHeat)


   insert into @ElementsLimits
   select 
		cat.ElementCode		
		,cat.[Min]
		,cat.[Aim]
		,cat.[Max]
	FROM CAT.GradeSpecs cat
	JOIN CAT.GradeVersions gradeVersion ON gradeVersion.Id = cat.GradeVersionId
	JOIN CAT.Grades gr ON gr.Id = gradeVersion.GradeId
	CROSS APPLY @viewColumns e
	WHERE gr.Id = @gradeId and cat.GradeVersionId = @gradeVersionId and cat.ElementCode	 = e.[Name]
	
	-- insert alias 
   insert into @ElementsLimits
   select 
		Alias	
		,cat.[Min]
		,cat.[Aim]
		,cat.[Max]
	FROM 
	CAT.GradeSpecs cat
	JOIN CAT.GradeVersions gradeVersion ON gradeVersion.Id = cat.GradeVersionId
	JOIN CAT.Grades gr ON gr.Id = gradeVersion.GradeId
	left join CAT.ChemistryElementOrder ceo on ceo.ElementCode=cat.ElementCode
	CROSS APPLY @viewColumns e
	WHERE gr.Id = @gradeId and cat.GradeVersionId = @gradeVersionId and alias = e.[Name]
	and alias is not null

	select 
		o.[Code]
		,o.[Order]
		,[Min] = cast(l.[Min] as decimal(5,4))
		,[Aim] = cast(l.[Aim] as decimal(5,4))
		,[Max] = cast(l.[Max] as decimal(5,4))
	from @ElementsOrder o
	left join @ElementsLimits l on l.[Code] = o.[Code] 
	order by o.[Order] asc


	
 --   ----------------------------
	----- Step 8: Main Information
	------------------------------	
	--IF (@getRealTimeData = 0)
	--BEGIN
	--	select 
	--	 HeatTypeSample,
	--	 [Time]  =left(cast(IH.ToLocal([Timestamp],@timeZoneCode) as [Time]),5),
	--	 rn = ROW_NUMBER() over(Order By [Timestamp] asc),
	--	 [Comments] = Comments
	--	from #chemistriesByHeat
	--	order By [Timestamp] asc 
	--END
	--ELSE
	--BEGIN
	--	select 
	--	 HeatTypeSample,
	--	 [Time]  =left(cast(IH.ToLocal([Timestamp],@timeZoneCode) as [Time]),5),
	--	 rn = ROW_NUMBER() over(Order By [Timestamp] asc),
	--	 [Comments] = Comments
	--	from #chemistriesByHeatRealTime
	--	order By [Timestamp] asc 
	--END

	----------------------------
	--- Step 8: Main Information
	----------------------------
	IF (@getRealTimeData = 0)
	BEGIN
	select
	Id,
	HeatTypeSample,
	TypeCode,
	SampleIdCode,
	[Time]  =left(cast(IH.ToLocal([Timestamp],@timeZoneCode) as [Time]),5),
	[Timestamp] = IH.ToLocal([Timestamp],null),
	rn = ROW_NUMBER() over(Order By [Timestamp] asc),
	[Comments] = Comments
	from #chemistriesByHeat
	order By [Timestamp] asc
	END
	ELSE
	BEGIN
	select
	Id,
	HeatTypeSample,
	TypeCode,
	SampleIdCode,
	[Time]  =left(cast(IH.ToLocal([Timestamp],@timeZoneCode) as [Time]),5),
	[Timestamp] = IH.ToLocal([Timestamp],null),
	rn = ROW_NUMBER() over(Order By [Timestamp] asc),
	[Comments] = Comments
	from #chemistriesByHeatRealTime
	order By [Timestamp] asc
	END

	-------------------------------
	--- Step 9: Grade Information
	-------------------------------	
	declare @gradeName varchar(50)

   	SELECT top 1
		@gradeName = grade.[Name]	
	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 heat.[Name] = @HeatName

	
	select 
	[GradeName] = @gradeName

	-----------------------
	-- Step 10: Navigate Buttons
	-----------------------
		
		select top 5 [Text]= 'EAF', Treatment = null, HeatName = h.[Name], HeatId = h.Id, [CurrentPage] = 'false'
		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 =null, HeatName = h.[Name], HeatId = h.Id , [CurrentPage] = 'false'
		from LF.Processes p 
		left join mes.Heats h on h.Id = p.HeatId
		where p.HeatId = @heatId

		union all
		select top 1 [Text]= 'CASTER', Treatment =null, HeatName = h.[Name], HeatId = h.Id, [CurrentPage] = 'false'
		from CAS.Processes p
		left join mes.Heats h on h.Id = p.HeatId
		where p.HeatId = @heatId

		union all
		select top 1 [Text]= 'LAB', Treatment = null, HeatName = h.[Name], HeatId = h.Id, [CurrentPage] = 'true'
		from LAB.ChemistrySamplesWithCalculated l 
		left join mes.Heats h on h.Id = l.HeatId
		where l.HeatId = @heatId

		union all
		select top 1 [Text]= 'THERMAL', Treatment =null, HeatName = h.[Name], HeatId = h.Id, [CurrentPage] = 'false'
		from mes.HeatTemperatures p
		left join mes.Heats h on h.Id = p.HeatId
		where p.HeatId = @heatId
	
	--------------------
   -- Step 11: Limits by Grade
   --------------------

	select * from @SampleByElementsAndLimits

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

	RETURN 0
END




/*------------------------------------- 25/10/2023 -------------------------*/


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



USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[ResendProductToL3]    Script Date: 25/10/2023 16:36:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [LAB].[ResendChemistrySampleToL3]
	@Id int,
	@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)

	PRINT @procName + ' Parameters:'
		+ ', @Id: ' + ISNULL(CAST(@Id AS VARCHAR), '')
		+ ', @currentUser: ' + ISNULL(CAST(@currentUser AS VARCHAR), '')


	DECLARE  @logMsg varchar(max)
	declare @msg varchar(max)
	-----------------------
	-- Begin Query
	-----------------------

	BEGIN TRY
	
		update LAB.ChemistrySamples
			set SentToL3 = 0
		where Id = @Id
		
		set @logMsg = 'The Chemistry Sample with Id: ' + isnull(cast(@Id as varchar(max)),'-') + ' was resent to L3 by: '+ cast(isnull(@currentUser,'-') as varchar(max))
		

		EXEC SYSTEM.LogMsg @message = @logMsg
			,@procedure = @procName
			,@typeCode = 'DEBUG'
			,@logError = 1


		Select 
			[Sample] = cast(h.[Name] as varchar) + '-' + CAST(s.TypeCode as varchar) + '-' + CAST(s.SampleIdCode as varchar) 
		from LAB.ChemistrySamples s
		left join MES.Heats h on h.Id = s.HeatId
		where s.Id = @Id

	END TRY
	BEGIN CATCH
		SELECT @logMsg = ERROR_MESSAGE()

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

	END CATCH

		
end
GO