USE [IHBoxSystem]
GO
CREATE ROLE lmfheatreport_readWrite AUTHORIZATION dbo 
GO  

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

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

USE [IHBoxSystem]
GO
CREATE ROLE lmfheatreport_read AUTHORIZATION dbo 
GO  

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

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



EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [lmfheatreport_readWrite] -- The role
,@name = N'securedsp-LF.GetProcess' -- the procedure
,@value = N'' 

Go

--EXECUTE sys.sp_addextendedproperty 
--@level0type = N'USER' -- The object type
--,@level0name = [lmfheatreport_readWrite] -- The role
--,@name = N'securedsp-LF.GetProcessCatalogs' -- the procedure  -- DELETE THIS SP from DB
--,@value = N'' 

--Go

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

INSERT INTO [FrontEnd].[Routes]
		   ([Route]
		   ,[ModuleName]
		   ,[ModulePath]
		   ,[RequiredRole]
	   )
	 VALUES
		   (
		   '!/lmf-heat-summary(/:heatName)(/)'
		   , 'lmf-heat-summary'
		   , 'custom-screens/lmf-heat-summary/'
		   , 'lmfheatreport_read'           
		   )
		   SET @result_routeId = @@IDENTITY
INSERT INTO [FrontEnd].[Menu]
		   ([Parent]
		   ,[Name]
		   ,[RouteID]
		   ,[SortOrder]
		   ,[Enabled]
		   ,[Visible]
		   ,[RequiredRole]
		   ,[Group]
		  )
	 VALUES
		   (
		   @parent
		   ,'LMF Heat Summary'
		   ,@result_routeId 
		   ,3
		   ,1
		   ,1
		   ,'lmfh_r'
		   ,NULL
		 )
GO


UPDATE FrontEnd.[Routes]
	SET [Route] = '!/lmf-heat-summary(/:heatName)(/)'
 WHERE Id = (  SELECT TOP 1 Id FROM FrontEnd.[Routes] WHERE ModuleName = 'lmf-heat-summary')

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

DECLARE @ModuleId INT

INSERT INTO [SECURITY].[Modules]
		   ([Code]
		   ,[Name])
	 VALUES
		   ('lmf_heat_summary'
		   ,'LMF Heat Summary')

		  SET @ModuleId = @@IDENTITY

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


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



EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [lmfh_w] -- The role
,@name = N'securedsp-LF.GetHeatSummary' -- the procedure
,@value = N'' 









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



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


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




















USE [MES]
GO
/****** Object:  StoredProcedure [MES].[GetAutocompleteHeats]    Script Date: 8/24/2023 11:40:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [MES].[GetAutocompleteHeats]
 	@heatName VARCHAR(10) = NULL
	,@treatment INT = NULL
	,@SelectedHeatName VARCHAR(7) = NULL
	,@SelectedTreatment INT = NULL
	,@Asset CHAR(10) = 'LMF'
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:'
		+ ', @heatName: ' + ISNULL(CAST(@heatName AS VARCHAR), '')

		
	DECLARE @SearchTextString VARCHAR(100)

	SET @searchTextString = '%' + @heatName + '%'

	declare @heatNames table ([value] varchar(9), [label] varchar(9),[Datetime] datetime, [processId] int,[treatment] int)
	
	declare @filterHeats table ([value] varchar(9), [label] varchar(9),[processId] int)

	declare @selectedHeatId int 
	-----------------------
	-- Begin Query
	-----------------------
	if (@Asset = 'LMF')
	begin		
		insert into @heatNames
		select top 20 
			cast(h.[Name] as varchar(7)) + '-' +  cast(l.Treatment as varchar(1)),
			cast(h.[Name] as varchar(7)) + '-' +  cast(l.Treatment as varchar(1)),
			l.[Start],
			l.Id,
			l.Treatment
		from mes.heats h
		left join lf.Processes l on l.HeatId = h.Id
		where 
		@heatName is null 
		or (
			h.[Name] like @searchTextString 
		)
		and l.HeatId is not null	
		and l.isActive = 1
		order by l.[Start] desc		



		
		if (@heatName is not null and @treatment is not null)	
			insert into @filterHeats
			select [value],[label],[processId] from @heatNames where treatment = @treatment order by [Datetime] desc
		else if (@heatName is null)  	-- When the page is first opened, we need to get the last process
			insert into @filterHeats
			select top 1	
				[value] = h.[Name],
				[label] = h.[Name],
			    [processId] = p.Id
			from lf.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where [Start] is not null and [End] is not null and p.IsActive = 1 order by p.[Start] desc	
		else -- When the user searches by heat, we need to show the last 20 heats 
			insert into @filterHeats
			select [value],[label],[processId] from @heatNames order by [Datetime] desc


		-- If the pikcer has a selected heat is needed to add this to the list
		if (@SelectedHeatName is not null)
		begin
			set @selectedHeatId = (select top 1 Id from mes.heats where [Name] = @SelectedHeatName)
			insert into @filterHeats
			values(
				 @SelectedHeatName  + '-' + cast(@SelectedTreatment as varchar(1))
				, @SelectedHeatName + '-' + cast(@SelectedTreatment as varchar(1))
				,(select top 1 Id from lf.Processes where Treatment = @SelectedTreatment and HeatId = @selectedHeatId)
			)
		end
	
		select distinct([value]),[label],[processId] from @filterHeats
		
	end


	if (@Asset = 'EAF')
	begin		
		insert into @heatNames
		select top 20 
			cast(h.[Name] as varchar(7)),
			cast(h.[Name] as varchar(7)),
			l.[Start],
			l.Id,
			null
		from mes.heats h
		left join eaf.Processes l on l.HeatId = h.Id
		where 
		@heatName is null 
		or (
			h.[Name] like @searchTextString 
		)
		and l.HeatId is not null	
		order by l.[Start] desc		



		
		if (@heatName is not null)	-- When the user searches by heat, we need to show the last 20 heats 
			insert into @filterHeats
			select [value],[label],[processId] from @heatNames order by [Datetime] desc
		else if (@heatName is null)  	-- When the page is first opened, we need to get the last process
			insert into @filterHeats
			select top 1	
				[value] = h.[Name],
				[label] = h.[Name],
			    [processId] = p.Id
			from eaf.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where [Start] is not null and [End] is not null order by p.[Start] desc	

		-- If the pikcer has a selected heat is needed to add this to the list
		if (@SelectedHeatName is not null)
		begin
			set @selectedHeatId = (select top 1 Id from mes.heats where [Name] = @SelectedHeatName)
			insert into @filterHeats
			values(
				 @SelectedHeatName 
				, @SelectedHeatName
				,(select top 1 Id from eaf.Processes where HeatId = @selectedHeatId)
			)
		end
	
		select distinct([value]),[label],[processId] from @filterHeats
		
	end



	
	if (@Asset = 'CAS')
	begin		
		insert into @heatNames
		select top 20 
			cast(h.[Name] as varchar(7)),
			cast(h.[Name] as varchar(7)),
			l.[Start],
			l.Id,
			null
		from mes.heats h
		left join cas.Processes l on l.HeatId = h.Id
		where 
		@heatName is null 
		or (
			h.[Name] like @searchTextString 
		)
		and l.HeatId is not null	
		order by l.[Start] desc		



		
		if (@heatName is not null)	-- When the user searches by heat, we need to show the last 20 heats 
			insert into @filterHeats
			select [value],[label],[processId] from @heatNames order by [Datetime] desc
		else if (@heatName is null)  	-- When the page is first opened, we need to get the last process
			insert into @filterHeats
			select top 1	
				[value] = h.[Name],
				[label] = h.[Name],
			    [processId] = p.Id
			from cas.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where [Start] is not null and [End] is not null order by p.[Start] desc	

		-- If the pikcer has a selected heat is needed to add this to the list
		if (@SelectedHeatName is not null)
		begin
			set @selectedHeatId = (select top 1 Id from mes.heats where [Name] = @SelectedHeatName)
			insert into @filterHeats
			values(
				 @SelectedHeatName 
				, @SelectedHeatName
				,(select top 1 Id from cas.Processes where HeatId = @selectedHeatId)
			)
		end
	
		select distinct([value]),[label],[processId] from @filterHeats
		
	end


	
	if (@Asset = 'VTD')
	begin		
		insert into @heatNames
		select top 20 
			cast(h.[Name] as varchar(7)) + '-' +  cast(l.Treatment as varchar(1)),
			cast(h.[Name] as varchar(7)) + '-' +  cast(l.Treatment as varchar(1)),
			l.[Start],
			l.Id,
			l.Treatment
		from mes.heats h
		left join vtd.Processes l on l.HeatId = h.Id
		where 
		@heatName is null 
		or (
			h.[Name] like @searchTextString 
		)
		and l.HeatId is not null	
		order by l.[Start] desc		



		
		if (@heatName is not null and @treatment is not null)	
			insert into @filterHeats
			select [value],[label],[processId] from @heatNames where treatment = @treatment order by [Datetime] desc
		else if (@heatName is null)  	-- When the page is first opened, we need to get the last process
			insert into @filterHeats
			select top 1	
				[value] = h.[Name],
				[label] = h.[Name],
			    [processId] = p.Id
			from vtd.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where [Start] is not null and [End] is not null order by p.[Start] desc	
		else -- When the user searches by heat, we need to show the last 20 heats 
			insert into @filterHeats
			select [value],[label],[processId] from @heatNames order by [Datetime] desc


		-- If the pikcer has a selected heat is needed to add this to the list
		if (@SelectedHeatName is not null)
		begin
			set @selectedHeatId = (select top 1 Id from mes.heats where [Name] = @SelectedHeatName)
			insert into @filterHeats
			values(
				 @SelectedHeatName  + '-' + cast(@SelectedTreatment as varchar(1))
				, @SelectedHeatName + '-' + cast(@SelectedTreatment as varchar(1))
				,(select top 1 Id from vtd.Processes where Treatment = @SelectedTreatment and HeatId = @selectedHeatId)
			)
		end
	
		select distinct([value]),[label],[processId] from @filterHeats
		
	end


	if (@Asset = 'LMFTOTAL')
	begin		
		insert into @heatNames
		select top 20 
			h.[Name],
			h.[Name],
			l.[Start],
			l.Id,
			l.Treatment
		from mes.heats h
		left join lf.Processes l on l.HeatId = h.Id
		where 
		@heatName is null 
		or (
			h.[Name] like @searchTextString 
		)
		and l.HeatId is not null	
		and l.isActive = 1
		order by l.[Start] desc		



		
		if (@heatName is not null and @treatment is not null)	
			insert into @filterHeats
			select [value],[label],[processId] from @heatNames where treatment = @treatment order by [Datetime] desc
		else if (@heatName is null)  	-- When the page is first opened, we need to get the last process
			insert into @filterHeats
			select top 1	
				[value] = h.[Name],
				[label] = h.[Name],
			    [processId] = p.Id
			from lf.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where [Start] is not null and [End] is not null and p.IsActive = 1 order by p.[Start] desc	
		else -- When the user searches by heat, we need to show the last 20 heats 
			insert into @filterHeats
			select [value],[label],[processId] from @heatNames order by [Datetime] desc


		-- If the pikcer has a selected heat is needed to add this to the list
		if (@SelectedHeatName is not null)
		begin
			set @selectedHeatId = (select top 1 Id from mes.heats where [Name] = @SelectedHeatName)
			insert into @filterHeats
			values(
				 @SelectedHeatName
				, @SelectedHeatName
				,(select top 1 Id from lf.Processes where Treatment = @SelectedTreatment and HeatId = @selectedHeatId)
			)
		end
	
		select distinct([value]),[label],[processId] from @filterHeats
		
	end


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

	RETURN 0
END


GO

USE [MES]
GO
/****** Object:  StoredProcedure [LF].[GetHeatReportByHeatName]    Script Date: 8/24/2023 2:12:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LF].[GetHeatSummary]
	@heatName varchar(7),
	@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:'
		+ ', @timeZoneCode: ' + ISNULL(CAST(@timeZoneCode AS VARCHAR), '')	
		+ ', @heatName: ' + ISNULL(CAST(@heatName AS VARCHAR), '')	

	-----------------------
	-- Variables
	-----------------------
	DECLARE @heatId INT,
			@processId INT,
			@start DATETIME,
			@startProcess DATETIME,
			
			@endProcess DATETIME,
			@startFirstHeating DATETIME,
			@endLastHeating DATETIME

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

	-----------------------
	-- Begin Query
	-----------------------
	select @heatId = Id from mes.Heats where [Name] = @heatName
	select @processId = Id from lf.Processes where Treatment = 1 and HeatId = @heatId

	
		SELECT
			@startProcess = p.[Start],
			@endProcess = p.[End],
			@startFirstHeating = (SELECT TOP 1 h.[Start] FROM lf.Heatings h where ProcessId = @processId  and h.[Start] between p.[Start] and p.[End] ORDER BY h.[Start] ASC),
			 @endLastHeating = (SELECT TOP 1 h.[End] FROM lf.Heatings h where ProcessId = @processId and h.[End] between p.[Start] and p.[End]	  ORDER BY h.[Start] DESC)
		FROM MES.Heats hs
		LEFT JOIN LF.Processes p on p.HeatId = hs.Id
		WHERE p.Id = @processId 
			

		-----------------------
		-- Process
		-----------------------
		SELECT 
			process.*,
			StartLocal = IH.ToLocal(process.[Start], @timeZoneCode),
			ArriveTimestampLocal = IH.ToLocal(process.[ArriveTimestamp], @timeZoneCode),
			EndLocal = IH.ToLocal(process.[End], @timeZoneCode),
			HeatName = heat.[Name],
			SteelWeight = cast (heat.SteelWeight as decimal(18,2)),
			GradeName = grade.[Name],
			InitGradeName = NULL,
			LadleFurnaceName = ladleFurnace.[Name],
			LadleName = ladle.[Name],
			OperatorName = operator.[Name]
			, TreatmentCurrentAndTotal = cast(Treatment as varchar) + '/'  + cast((select max(z.Treatment) from lf.Processes z where z.HeatId = heat.Id) as varchar)
			
			,MeltOrder = mo.MeltOrderNumber
			,PreparationTime =	DATEDIFF(SS,@startProcess,@startFirstHeating) 
			,HeatingTime =		DATEDIFF(SS,@startFirstHeating,@endLastHeating) 
			,LastStage =		DATEDIFF(SS,@endLastHeating,@endProcess)  -- maybe endheat is null
			,TotalTreatmentTime =	DATEDIFF(SS,@startProcess,@endProcess)  -- maybe endheat is null
			,PowerOff = ABS(DATEDIFF(SS,@startProcess,@endProcess) - process.PowerOn)
			,ArgonCar = '-' --(SELECT MAX(z.Value) FROM ih.ltreal z WHERE z.tagId = @tagId_AT_Argon_South_Feed_1_FlowRate AND IH.Ms2Date(z.Offset) BETWEEN @startProcess AND @endProcess)  
			--,ArgonCar = [LF].[GetArgonCar](@startProcess,@endProcess,process.LadleFurnaceCode)

 		FROM LF.Processes process
		LEFT JOIN MES.Heats heat ON heat.Id = process.HeatId
		LEFT JOIN CAT.Grades grade ON grade.Id = heat.GradeId
		LEFT JOIN CAT.LadleFurnaces ladleFurnace ON ladleFurnace.Code = process.LadleFurnaceCode
		LEFT JOIN CAT.Ladles ladle ON ladle.Id = heat.LadleId
		LEFT JOIN CAT.Operators operator ON operator.Id = process.OperatorId
		left join MES.MeltOrders mo on heat.MeltOrderId = mo.id
		WHERE process.Id = @processId 
	
		-----------------------
		-- Probes
		-----------------------

		declare @eafProcessId int = (select top 1 Id from EAF.Processes where HeatId = @heatId)

		select 
			[Source] = 'EAF',
			[Datetime] = IH.ToLocal([Timestamp],null),
			[O] = Oxygen,
			[T] = Temperature,
			[C] = Carbon
		from EAF.Temperatures  where ProcessId = @eafProcessId
		union all
		SELECT * FROM (
				SELECT
				[Source] = 'LMF',
				[Datetime] = IH.ToLocal([Datetime],@timeZoneCode),
				[Value]= cast([Value] as decimal(8,2)),
				[ProbeType]
				FROM LF.Probes t
				left join lf.Processes p on t.ProcessId = p.Id 
				WHERE ProcessId = @processId --and t.[Datetime] between p.[Start] and p.[End]
		) ProbesResult
		PIVOT (
			MAX([Value])
			FOR [ProbeType]
			IN (
			[O],
			[T],
			[C]
			)
		) AS PivotTable
		order by [Source], [Datetime]


		--SELECT * FROM (
		--		SELECT
		--		[Datetime] = IH.ToLocal([Datetime],@timeZoneCode),
		--		[Value]= cast([Value] as decimal(8,2)),
		--		[ProbeType]
		--		FROM LF.Probes t
		--		left join lf.Processes p on t.ProcessId = p.Id 
		--		WHERE ProcessId = @processId --and t.[Datetime] between p.[Start] and p.[End]
		--) ProbesResult
		--PIVOT (
		--	MAX([Value])
		--	FOR [ProbeType]
		--	IN (
		--	[O],
		--	[T],
		--	[C]
		--	)
		--) AS PivotTable
		-----------------------
		-- Heatings
		-----------------------
		SELECT
			h.Id,
			h.ProcessId,
			[Start] = IH.ToLocal(h.[Start],@timeZoneCode),
			[End] = IH.ToLocal(h.[End],@timeZoneCode),
			[Duration] = DATEDIFF(SECOND, h.[Start],h.[End]),
			h.[Tap],
			h.[Energy]	
		FROM LF.Heatings h
		LEFT JOIN LF.Processes p on p.Id = h.ProcessId
		WHERE ProcessId = @processId
		--and h.[Start] between p.[Start] and p.[End]
		-----------------------
		-- Alloys
		-----------------------
		
		-------------------------------
		-- Next Version Alloys ------
		------------------------------
		SELECT
    MAX(mat.[Name]) AS MaterialName, 
    MAX(ac.Id) AS Id,
     MAX(ac.ProcessId) AS ProcessId,
    MAX(ac.SourceCode) AS SourceCode,
    MAX(cast(ac.isManual as int)) AS isManual,
    MAX(left(convert(varchar,IH.ToLocal(ac.[Timestamp],@timeZoneCode),108),5)) AS [Timestamp],
    MAX(ac.DumpStart) AS DumpStart,
    MAX(cast(ac.WeightSetpoint as int)) AS WeightSP,
    SUM(cast(ac.WeightValue as int)) AS WeightPV,
    MAX(ac.materialId) AS MaterialId,
    NULL AS Parameter1,
    MAX(ac.BinNumber) AS BinNumber,
    CASE MAX(ac.SourceCode) WHEN 'B' THEN 'lbs'
                         WHEN 'A' THEN 'lbs'
                         WHEN 'L' THEN 'lbs'
                         WHEN 'W' THEN 'ft'
                         WHEN 'C' THEN 'lbs'
                         WHEN 'M' THEN 'lbs'
                         ELSE '-' END AS UoM
FROM
    lf.AlloysConsumption ac
LEFT JOIN
    CAT.Materials mat ON mat.Id = ac.MaterialId
--WHERE ac.ProcessId = @processId
WHERE ac.ProcessId IN (SELECT ProcessId FROM @processIds)
    AND isActive = 1
GROUP BY
    mat.[Name] 
ORDER BY
    DumpStart; 

		declare @siblingProcesses table ([value] int, [label] varchar(500), [start] datetime, currentProcessId int)

		insert into @siblingProcesses
		select top 5 
			[value] = p.Id, 
			[label] = 
				+ left(convert(varchar,IH.ToLocal(p.[Start],@timeZoneCode),101),5) + ' ' 
				+ left(convert(varchar,IH.ToLocal(p.[Start],@timeZoneCode),108),5) 
				+ ' LMF ' + case when p.LadleFurnaceCode = 1 then 'A' else 'B' end
				+ ' ( ' + h.[Name] + ' - ' + CAST(p.Treatment as varchar) + ' )',
			[Start],
			@processId  
		from LF.Processes p
		left join MES.Heats h on h.Id = p.HeatId		
		where p.[Start] > @startProcess order by p.[Start] asc

		insert into @siblingProcesses
		select top 5 
			[value] = p.Id, 
			[label] = 
				+ left(convert(varchar,IH.ToLocal(p.[Start],@timeZoneCode),101),5) + ' ' 
				+ left(convert(varchar,IH.ToLocal(p.[Start],@timeZoneCode),108),5) 
				+ ' LMF ' + case when p.LadleFurnaceCode = 1 then 'A' else 'B' end
				+ ' ( ' + h.[Name] + ' - ' + CAST(p.Treatment as varchar) + ' )',
			[Start],
			@processId  
		from LF.Processes p
		left join MES.Heats h on h.Id = p.HeatId		
		where p.[Start] < @startProcess order by p.[Start] desc

		insert into @siblingProcesses
		select top 5 
			[value] = p.Id, 
			[label] = 
				+ left(convert(varchar,IH.ToLocal(p.[Start],@timeZoneCode),101),5) + ' ' 
				+ left(convert(varchar,IH.ToLocal(p.[Start],@timeZoneCode),108),5) 
				+ ' LMF ' + case when p.LadleFurnaceCode = 1 then 'A' else 'B' end
				+ ' ( ' + h.[Name] + ' - ' + CAST(p.Treatment as varchar) + ' )',
			[Start],
			@processId  
		from LF.Processes p
		left join MES.Heats h on h.Id = p.HeatId		
		where p.Id = @processId			
		

		select [value],[label],currentProcessId from @siblingProcesses order by [Start]

		
		--select 
		--	distinct(a.MaterialId) as [value]
		--	,m.[Name] as [label]
		--from LF.AlloysConsumption a
		--left join CAT.Materials m on m.Id  = a.MaterialId

		select 
			m.Id as [value]
			,m.[Name] as [label]
		from CAT.Materials m 
		-------------------------------
		-- Next Version Alloys ------
		------------------------------


		----------------------
		-- Phases
		----------------------
		select
			ProcessDuration =DATEDIFF(SS,@startProcess,@endProcess)   -- TotalTreatmentTime
			,Duration = DATEDIFF(SS,@startProcess,@startFirstHeating) -- PreparationTime
			,FormattedDuration = CONVERT(varchar, DATEADD(ms, DATEDIFF(SS,@startProcess,@startFirstHeating)* 1000, 0), 108)				
			,[Order] = 1
			,[Code]= 'Preparation Time'
			,[Percentage] = (DATEDIFF(SS,@startProcess,@startFirstHeating) * 100.0 / DATEDIFF(SS,@startProcess,@endProcess))-2

			union all

		select
			ProcessDuration =DATEDIFF(SS,@startProcess,@endProcess)   -- TotalTreatmentTime
			,Duration = DATEDIFF(SS,@startFirstHeating,@endLastHeating)  -- HeatingTime
			,FormattedDuration = CONVERT(varchar, DATEADD(ms, DATEDIFF(SS,@startFirstHeating,@endLastHeating)* 1000, 0), 108)					
			,[Order] = 2			
			,[Code]= 'Heating Time'
			,[Percentage] = (DATEDIFF(SS,@startFirstHeating,@endLastHeating) * 100.0 / DATEDIFF(SS,@startProcess,@endProcess)) - 2 

			union all

		select
			ProcessDuration =DATEDIFF(SS,@startProcess,@endProcess) -- TotalTreatmentTime
			,Duration = DATEDIFF(SS,@endLastHeating,@endProcess)	-- LastStage
			,FormattedDuration =  CONVERT(varchar, DATEADD(ms, DATEDIFF(SS,@endLastHeating,@endProcess)* 1000, 0), 108)	
			,[Order] = 3			
			,[Code]= 'Last Stage'
			,[Percentage] = (DATEDIFF(SS,@endLastHeating,@endProcess) * 100.0 / DATEDIFF(SS,@startProcess,@endProcess)) + 4

		-----------------------
		-- Navigate Buttons
		-----------------------
		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 AND p.Treatment = 1

		UNION all
		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]= '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 1 [Text]= 'LAB', Treatment = null, HeatName = h.[Name], HeatId = h.Id
		from LAB.ChemistrySamplesWithCalculated l 
		left join mes.Heats h on h.Id = l.HeatId
		where l.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

		union all
		select top 1 [Text]= 'THERMAL', Treatment =null, HeatName = h.[Name], HeatId = h.Id
		from mes.HeatTemperatures 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
		from CAS.Processes p
		left join mes.Heats h on h.Id = p.HeatId
		where p.HeatId = @heatId


	-----------------------
	-- Chart 
	-----------------------

		--EXEC [LF].[GetChartData] @id  = @processId

		select [Processd] = @processId

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

	RETURN 0
END




GO



USE [MES]
GO
/****** Object:  StoredProcedure [MES].[GetNavigatedHeat]    Script Date: 8/24/2023 10:30:41 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [MES].[GetNavigatedHeat]
	@heatName VARCHAR(10),
	@treatment INT  = 1,
	@direction SMALLINT,
	@Asset CHAR(10) = 'LMF'
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:'
		+ ', @heatName: ' + ISNULL(CAST(@heatName AS VARCHAR), '')
		+ ', @direction: ' + ISNULL(CAST(@direction AS VARCHAR), '')


	-----------------------
	-- Navigation Variables
	-----------------------
	declare @heatId int = (select Id from MES.Heats where [Name] = @heatName)

	declare @EAFProcessStart datetime = (select [Start] from EAF.Processes where HeatId = @heatId)
	declare @LMFProcessStart datetime = (select [Start] from LF.Processes where HeatId = @heatId and Treatment = @treatment)
	
	declare @VTDProcessStart datetime = (select [Start] from VTD.Processes where HeatId = @heatId and Treatment = @treatment)
	declare @CASProcessLadleOpen datetime -- for caster is different because we can have an UNK heat with heatId = NULL
	
	declare @newHeatName VARCHAR(10) = @heatName
	declare @newTreatment INT = 1
	---------------------------------------------------
	------------------- BEGIN QUERY -------------------
	---------------------------------------------------	
	IF (@Asset = 'EAF' or @Asset = 'LAB')
	BEGIN
		if (@direction = 1 and @EAFProcessStart is not null)
			select top 1 
				@newHeatName = isNULL(h.[Name],@heatName) 
			from eaf.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where p.[Start] > @EAFProcessStart order by p.[Start] asc

		if (@direction = -1 and @EAFProcessStart is not null)
			select top 1 
				@newHeatName = isNULL(h.[Name],@heatName) 
			from eaf.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where p.[Start] < @EAFProcessStart order by p.[Start] desc

		-- Result:
		select [HeatName] = cast(@newHeatName as varchar(7))

		return
	END		


	IF (@Asset = 'LMF')
	BEGIN
		if (@direction = 1 and @LMFProcessStart is not null)
			select top 1 
				@newHeatName = isNULL(h.[Name],@heatName) 
				,@newTreatment = p.Treatment
			from lf.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where p.[Start] > @LMFProcessStart and p.isActive = 1 order by p.[Start] asc

		if (@direction = -1 and @LMFProcessStart is not null)
			select top 1 
				@newHeatName = isNULL(h.[Name],@heatName) 
				,@newTreatment = p.Treatment
			from lf.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where p.[Start] < @LMFProcessStart and p.isActive = 1  order by p.[Start] desc

		-- Result:
		select 
			[HeatName] = cast(@newHeatName as varchar(7))
			,[Treatment] = @newTreatment

		return
	END		

	IF (@Asset = 'CAS')
	BEGIN
		-----------------------------
		--- GET CURRENT LadleOpen  --
		-----------------------------

		-- we try to find the cas process using the heatId
		select
			@CASProcessLadleOpen = p.[LadleOpen]
		from cas.Processes p 
		where p.HeatId = @heatId

		-- maybe the process exists as UNK heat
		if (@CASProcessLadleOpen is null)
		begin
			select
				@CASProcessLadleOpen = p.[LadleOpen]
			from cas.Processes p 
			where p.L1HeatName = @heatName
			
		end
		
		----------------------------------------------------------
		--- GET Next/Previous HeatName  ----------------------
		--  NOTE: The next/previous heat can have heatId null
		----------------------------------------------------------


		-- we try to find the cas process using the heatId
		if (@direction = 1 and @CASProcessLadleOpen is not null)
			select top 1 
				@newHeatName = h.[Name]
			from cas.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where p.[LadleOpen] > @CASProcessLadleOpen order by p.[LadleOpen] asc

		if (@direction = -1 and @CASProcessLadleOpen is not null)
			select top 1 
				@newHeatName = h.[Name]
			from cas.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where p.[LadleOpen] < @CASProcessLadleOpen order by p.[LadleOpen] desc

		-- it means the next/previous heat is probably a UNK
		if (@newHeatName is null)
		begin
			if (@direction = 1 and @CASProcessLadleOpen is not null)
				select top 1 
					@newHeatName = p.L1HeatName
				from cas.Processes p
				where p.[LadleOpen] > @CASProcessLadleOpen order by p.[LadleOpen] asc

			if (@direction = -1 and @CASProcessLadleOpen is not null)
				select top 1 
					@newHeatName = p.L1HeatName
				from cas.Processes p
				where p.[LadleOpen] < @CASProcessLadleOpen order by p.[LadleOpen] desc
		end

		-- Result:
		-- if @newHeatName is null means that there is no next/previus heat in the database, so, we keep the same heat
		select [HeatName] = cast(isNULL(@newHeatName,@heatName) as varchar(7))

		return
	END		


	IF (@Asset = 'VTD')
	BEGIN
		if (@direction = 1 and @VTDProcessStart is not null)
			select top 1 
				@newHeatName = isNULL(h.[Name],@heatName) 
				,@newTreatment = p.Treatment
			from vtd.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where p.[Start] > @VTDProcessStart order by p.[Start] asc

		if (@direction = -1 and @VTDProcessStart is not null)
			select top 1 
				@newHeatName = isNULL(h.[Name],@heatName) 
				,@newTreatment = p.Treatment
			from vtd.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where p.[Start] < @VTDProcessStart order by p.[Start] desc

		-- Result:
		select 
			[HeatName] = cast(@newHeatName as varchar(7))
			,[Treatment] = @newTreatment

		return
	END	


	IF (@Asset = 'LMFTOTAL')
	BEGIN
		if (@direction = 1 and @EAFProcessStart is not null)
			select top 1 
				@newHeatName = isNULL(h.[Name],@heatName) 
			from lf.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where p.[Start] > @EAFProcessStart and p.Treatment = 1 order by p.[Start] asc

		if (@direction = -1 and @EAFProcessStart is not null)
			select top 1 
				@newHeatName = isNULL(h.[Name],@heatName) 
			from lf.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where p.[Start] < @EAFProcessStart and p.Treatment = 1 order by p.[Start] desc

		-- Result:
		select [HeatName] = cast(@newHeatName as varchar(7))

		return
	END		




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

	RETURN 0
END


GO


create table MES.Audit(
	Id int identity(1,1) primary key
	, HeatId int
	, HeatName varchar(10)
	, Station varchar(10)
	, [Type] char(2)
	, [Timestamp] datetime
	, [User] varchar(100)
	, [Description] varchar(max))


	use ihboxsystem
	go

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


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




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


		

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


-------------------------------------------------------
----------------- 25/02/2023 --------------------------
-------------------------------------------------------

	--- replace this code in [LF].[GetHeatReport]
		-----------------------
		-- Probes
		-----------------------
		declare @eafProcessId int = (select top 1 Id from EAF.Processes where HeatId = @heatId)

		select 
			[Source] = 'EAF',
			[Datetime] = IH.ToLocal([Timestamp],null),
			[O] = Oxygen,
			[T] = Temperature,
			[C] = Carbon
		from EAF.Temperatures  where ProcessId = @eafProcessId
		union all
		SELECT * FROM (
				SELECT
				[Source] = 'LMF',
				[Datetime] = IH.ToLocal([Datetime],@timeZoneCode),
				[Value]= cast([Value] as decimal(8,2)),
				[ProbeType]
				FROM LF.Probes t
				left join lf.Processes p on t.ProcessId = p.Id 
				WHERE ProcessId = @processId --and t.[Datetime] between p.[Start] and p.[End]
		) ProbesResult
		PIVOT (
			MAX([Value])
			FOR [ProbeType]
			IN (
			[O],
			[T],
			[C]
			)
		) AS PivotTable
		order by [Source], [Datetime]