USE [IHBoxSystem]
GO
CREATE ROLE eafheatreport_readWrite AUTHORIZATION dbo 
GO  

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

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

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

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

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

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

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

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

DECLARE @ModuleId INT

INSERT INTO [SECURITY].[Modules]
		   ([Code]
		   ,[Name])
	 VALUES
		   ('eaf_heat_report'
		   ,'EAF Heat Report')

		  SET @ModuleId = @@IDENTITY

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

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


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



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











/*-------------------------------------------------------------------------------------------------------------------------*/
USE [MES]
GO
/****** Object:  StoredProcedure [EAF].[GetHeatReport]    Script Date: 27/9/2022 16:39:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [EAF].[GetHeatReport]
	@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:'
		+ ', @heatName: ' + ISNULL(CAST(@heatName AS VARCHAR), '')	
		+ ', @timeZoneCode: ' + ISNULL(CAST(@timeZoneCode AS VARCHAR), '')
	


	-----------------------
	-- Variables
	-----------------------
		DECLARE @heatId INT,
				@processId INT,
				@ladleFurnaceCode INT

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

		--Navigation
				DECLARE @start DATETIME
		--EAF Process
		DECLARE @currentProcesses TABLE (Id INT)
		--Phases
		DECLARE @eafHeatDurationStandard INT = CAST(SYSTEM.GetConfigVariable('EAF_HEAT_DURATION_STANDARD_SECONDS') AS INT),
				@nowUtc DATETIME = GETUTCDATE()

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

		--IF NOT EXISTS (SELECT 1 FROM  EAF.Processes WHERE Id = @processId)
		--	RAISERROR ('Process not found',13,1)
		select top 1
			@processId = p.Id
			,@heatId = p.HeatId
			,@ladleFurnaceCode = p.FurnaceCode
		from eaf.Processes p
		left join mes.heats h on h.Id = p.heatId 
		where [Name] = @heatName
	-----------------------
	-- Set Slag Limits
	-----------------------
		DECLARE @FeOMax_Structural FLOAT = (select top 1 k.[Value] from cat.KpiVariables k left join cat.ProductTypes p on p.Id = k.productTypeId
											where k.Code = 'SLAG_FeO_MAX_LIMIT' and p.[Name]= 'Structural')
		DECLARE @FeOMin_Structural FLOAT = (select top 1 k.[Value] from cat.KpiVariables k left join cat.ProductTypes p on p.Id = k.productTypeId
											where k.Code = 'SLAG_FeO_MIN_LIMIT' and p.[Name]= 'Structural')

		DECLARE @MgOMax_Structural FLOAT = (select top 1 k.[Value] from cat.KpiVariables k left join cat.ProductTypes p on p.Id = k.productTypeId
											where k.Code = 'SLAG_MgO_MAX_LIMIT' and p.[Name]= 'Structural')
		DECLARE @MgOMin_Structural FLOAT = (select top 1 k.[Value] from cat.KpiVariables k left join cat.ProductTypes p on p.Id = k.productTypeId
											where k.Code = 'SLAG_MgO_MIN_LIMIT' and p.[Name]= 'Structural')

		DECLARE @B3Max_Structural FLOAT = (select top 1 k.[Value] from cat.KpiVariables k left join cat.ProductTypes p on p.Id = k.productTypeId
											where k.Code = 'SLAG_B3_MAX_LIMIT' and p.[Name]= 'Structural')
		DECLARE @B3Min_Structural FLOAT = (select top 1 k.[Value] from cat.KpiVariables k left join cat.ProductTypes p on p.Id = k.productTypeId
											where k.Code = 'SLAG_B3_MIN_LIMIT' and p.[Name]= 'Structural')


		DECLARE @FeOMax_Rail FLOAT = (select top 1 k.[Value] from cat.KpiVariables k left join cat.ProductTypes p on p.Id = k.productTypeId
											where k.Code = 'SLAG_FeO_MAX_LIMIT' and p.[Name]= 'Rail')
		DECLARE @FeOMin_Rail FLOAT = (select top 1 k.[Value] from cat.KpiVariables k left join cat.ProductTypes p on p.Id = k.productTypeId
											where k.Code = 'SLAG_FeO_MIN_LIMIT' and p.[Name]= 'Rail')

		DECLARE @MgOMax_Rail FLOAT = (select top 1 k.[Value] from cat.KpiVariables k left join cat.ProductTypes p on p.Id = k.productTypeId
											where k.Code = 'SLAG_MgO_MAX_LIMIT' and p.[Name]= 'Rail')
		DECLARE @MgOMin_Rail FLOAT = (select top 1 k.[Value] from cat.KpiVariables k left join cat.ProductTypes p on p.Id = k.productTypeId
											where k.Code = 'SLAG_MgO_MIN_LIMIT' and p.[Name]= 'Rail')

		DECLARE @B3Max_Rail FLOAT = (select top 1 k.[Value] from cat.KpiVariables k left join cat.ProductTypes p on p.Id = k.productTypeId
											where k.Code = 'SLAG_B3_MAX_LIMIT' and p.[Name]= 'Rail')
		DECLARE @B3Min_Rail FLOAT = (select top 1 k.[Value] from cat.KpiVariables k left join cat.ProductTypes p on p.Id = k.productTypeId
											where k.Code = 'SLAG_B3_MIN_LIMIT' and p.[Name]= 'Rail')
		DECLARE @RefractoryWearLimit FLOAT = CAST(0.0000 AS FLOAT)
		DECLARE @TotalLimit FLOAT = CAST(90.0000 AS FLOAT)
	-----------------------
	-- Begin Query
	-----------------------
		INSERT INTO @currentProcesses
			SELECT
				process.Id
			FROM EAF.Processes process,
				(SELECT [Timestamp] = MAX([Start]) FROM EAF.Processes WHERE EndTap IS NULL GROUP BY FurnaceCode) AS currentProcess
			WHERE
				process.[Start] = currentProcess.[Timestamp]
		

		-----------------------
		-- Process
		-----------------------
		SELECT 
			process.*,
			
			MeltOrder = mo.MeltOrderNumber,
			HeatName = heat.[Name],
			StartLocal = IH.ToLocal(process.[Start], @timeZoneCode),
			EndLocal = IH.ToLocal(process.[End], @timeZoneCode),
		
			InitialGradeName = Grade.[Name],
			GradeName = grade.[Name],
			LadleName = ladle.[Name],
			OperatorName = operator.[Name],

			BoreInTime	= (select top 1 datediff(second,z.[Start],z.[End]) from EAF.Phases z where z.ProcessId = process.id and PhaseId=1),
			MeltTime	= (select top 1 datediff(second,z.[Start],z.[End]) from EAF.Phases z where z.ProcessId = process.id and PhaseId=2),
			RefineTime	= (select top 1 datediff(second,z.[Start],z.[End]) from EAF.Phases z where z.ProcessId = process.id and PhaseId=3),
			TapTime		= (select top 1 datediff(second,z.[Start],z.[End]) from EAF.Phases z where z.ProcessId = process.id and PhaseId=4),
			TapToTap	= case when process.EndTap IS NOT NULL then isnull(datediff(second
								,(
									select top 1 EndTap FROM EAF.Processes z 
									WHERE z.[Start] < process.[Start] and z.FurnaceCode = Process.FurnaceCode and z.EndTap is not null 
									order by z.[Start] desc --Previous EndTap not null
								),process.EndTap -- Current EndTap
							), 0) else 0 end,
			--TapToTap = case when process.EndTap is not null then datediff(second,process.[StartTap],process.[EndTap]) else 0 end,


			ChargeWeight = (select isnull(sum(ScrapWeight),0) from EAF.Buckets z where z.ProcessId = process.id),
			SteelWeight = CASE WHEN ((heat.SteelWeight IS NOT NULL) OR (currentProcess.Id IS NULL)) THEN
									cast(heat.SteelWeight as decimal(10,2))
								ELSE
									MES.GetSteelWeightOnline(process.FurnaceCode)
							END,
			Yield =  CASE WHEN (select isnull(sum(ScrapWeight),0) from EAF.Buckets z where z.ProcessId = process.id) <> 0 THEN -- if ChargeWeight <> 0
								(CASE WHEN ((heat.SteelWeight IS NOT NULL) OR (currentProcess.Id IS NULL)) THEN heat.SteelWeight
									ELSE MES.GetSteelWeightOnline(process.FurnaceCode) END) * 2000  -- SteelWeight
								/ (select isnull(sum(ScrapWeight),0) from EAF.Buckets z where z.ProcessId = process.id) -- ChargeWeight is not 0
								* 100
							ELSE NULL END,

			RegulationModeName = CAT.GetRegulationModeName(process.RegulationModeId, process.FurnaceCode),
			PowerOnCalculated = CASE WHEN ((process.PowerOn IS NOT NULL) OR (currentProcess.Id IS NULL)) THEN process.PowerOn
					  ELSE EAF.GetPowerOnOnline(process.FurnaceCode) / 1000 --Convert from milliseconds to seconds
				END, 

			EnergyCalculated = CASE WHEN ((process.Energy IS NOT NULL) OR (currentProcess.Id IS NULL)) THEN process.Energy
								 ELSE EAF.GetEnergyOnline(process.FurnaceCode) END

		FROM EAF.Processes process
			JOIN MES.Heats heat ON heat.Id = process.HeatId
			LEFT JOIN CAT.HeatStatus heatStatus ON heatStatus.Code = heat.StatusCode
			LEFT JOIN CAT.Ladles ladle ON ladle.Id = heat.LadleId
			LEFT JOIN Delays.Shifts shifts ON shifts.Id = process.ShiftId
			LEFT JOIN CAT.Furnaces furnace ON furnace.Code = process.FurnaceCode
			LEFT JOIN CAT.Grades grade ON grade.Id = process.GradeId
			LEFT JOIN CAT.Operators operator ON operator.Id = process.OperatorId
			LEFT JOIN CAT.ArcLengthSettings arcLengthSetting ON arcLengthSetting.Id = process.ArcLengthSettingId
			LEFT JOIN CAT.RegulationModes regulationMode ON regulationMode.Id = process.RegulationModeId
			LEFT JOIN @currentProcesses currentProcess ON currentProcess.Id = process.Id	
			left join MES.MeltOrders mo on heat.MeltOrderId = mo.id
			

		WHERE process.Id = @processId 
	
		-----------------------
		-- Phases
		-----------------------
		IF @processId > 54026 -- NEW PHASE TRACKING
		BEGIN

			select PhaseId=1,Code =cast('Bore In' as varchar(50)) into #Codes
			insert into #Codes values (2,'Melting')
			insert into #Codes values (3,'Refining')
			insert into #Codes values (4,'Tapping')
			
			SELECT
			Id = ph.id
			,ProcessId = ph.ProcessId
			,Code = cast(c.Code as varchar(10))
			,[Order] = row_number() over (partition by ProcessId order by ph.Start)
			,Energy
			,Carbon
			,NaturalGas
			,O2SS
			,Oxygen
			,[Timestamp]
			,Duration = datediff(SS,Start,isnull([End],getutcdate()))
			--,ProcessDuration = (select (datediff(SS,Start,isnull([End],getutcdate()))) from EAF.Processes z where z.id = @processId)
			,ProcessDuration = (select sum(datediff(SS,Start,isnull([End],getutcdate()))) from EAF.Phases where ProcessId = @processId and PhaseId in (select PhaseId from #Codes))

			FROM EAF.Phases ph
			left join #codes c on c.PhaseId = ph.PhaseId
			where ph.ProcessId = @ProcessId and [Order]=0 and ph.PhaseId in (select PhaseId from #Codes)


		END
		ELSE 
		BEGIN -- OLD PHASE TRACKING
			SELECT
			*,
			ProcessDuration = CASE WHEN ((ProcessEnd IS NOT NULL) OR (DATEDIFF(SS, ProcessStart, @nowUtc) > @eafHeatDurationStandard)) THEN
								DATEDIFF(SS, ProcessStart, ISNULL(ProcessEnd, @nowUtc))
							ELSE @eafHeatDurationStandard END
			FROM (SELECT 
					phase.Id, 
					phase.ProcessId, 
					phase.Code, 
					phase.[Order], 
					Energy = phase.Energy - ISNULL(prevPhase.Energy, 0),
					Carbon = phase.Carbon - ISNULL(prevPhase.Carbon, 0),
					NaturalGas = phase.NaturalGas - ISNULL(prevPhase.NaturalGas, 0),
					Oxygen = phase.Oxygen - ISNULL(prevPhase.Oxygen, 0),
					O2SS = phase.O2SS - ISNULL(prevPhase.O2SS, 0),
					phase.[Timestamp],

					Duration = CASE WHEN (nextPhase.ProcessId IS NOT NULL) THEN
										DATEDIFF(SS, phase.[Timestamp], nextPhase.[Timestamp])
									WHEN (process.[End] IS NOT NULL) THEN
										DATEDIFF(SS, phase.[Timestamp], process.[End])
									WHEN (process.[End] IS NULL) THEN
										DATEDIFF(SS, phase.[Timestamp], @nowUtc)
								END,
					ProcessStart = process.Start,
					ProcessEnd = process.[End]
					--TimestampString = CONVERT(VARCHAR, IH.ToLocal(phase.[Timestamp], @timeZoneCode), 121)
				FROM EAF.Phases phase
					LEFT JOIN EAF.Phases nextPhase ON nextPhase.ProcessId = phase.ProcessId AND nextPhase.[Order] = phase.[Order] + 1
					LEFT JOIN EAF.Phases prevPhase ON prevPhase.ProcessId = phase.ProcessId AND prevPhase.[Order] = phase.[Order] - 1
					JOIN EAF.Processes process ON process.Id = phase.ProcessId
				WHERE phase.ProcessId = @processId
					) AS records
			ORDER BY
				records.[Order] ASC
		END

		-----------------------
		-- Buckets
		-----------------------
		select 
			c.Id
			,ProcessId = p.Id
			,BucketId = c.Bucket
			, OthersWeight = ISNULL((SELECT SUM(cd.Weight) FROM SCRAP.ChargeDetails cd WHERE cd.ScrapMaterialId in (select fm.ScrapMaterialId from [SCRAP].[GetFluxMaterialsIds]() fm) and cd.ChargeId = c.Id), 0)
			,ScrapWeight =ISNULL(TotWeight, 0) - ISNULL((SELECT SUM(cd.Weight) FROM SCRAP.ChargeDetails cd WHERE cd.ScrapMaterialId in (select fm.ScrapMaterialId from [SCRAP].[GetFluxMaterialsIds]() fm) and cd.ChargeId = c.Id), 0)
			,[Timestamp] = IH.ToLocal(c.ChargeTimestamp,@TimeZoneCode)
			,BucketName = bucketCat.[Name]
			,fc.[HiCal]			--[FluxWeight1]
			,fc.[Carbon]		--[FluxWeight2]
			,fc.[ProSlag]		--[FluxWeight3]
			,fc.[Dolo]			--[FluxWeight4]
		FROM SCRAP.Charges c
		left join mes.heats h on h.Id = c.HeatId
		left join eaf.processes p on p.HeatId = h.Id
		LEFT JOIN CAT.Buckets bucketCat ON bucketCat.Id = c.Bucket
		Outer apply SCRAP.GetFluxesChargeByChargeId(c.Id) fc
		WHERE p.Id = @processId

		-----------------------
		-- Temperatures
		-----------------------
		SELECT
			Id,
			ProcessId,
			Carbon =case when Carbon > 2.0 then 'BAD VALUE' else cast(Carbon as varchar) end,
			Oxygen,
			Temperature,
			EstimatedCarbon = cast(EstimatedCarbon as decimal(18,2)),
			EstimatedO2ppm  = cast(EstimatedO2ppm  as decimal(18,2)),
			[Timestamp]= IH.ToLocal([Timestamp],@timeZoneCode)
		FROM EAF.Temperatures 
		WHERE ProcessId = @processId 
		AND ((Temperature >0 or Oxygen >0) or (Carbon <2.0))
		ORDER BY [Timestamp] ASC


		--------------------------------
		-- SLAG CHEMISTRIES
		---------------------------------
		
		SELECT 
			Heat.[Name],
			TimestampString = CONVERT(VARCHAR, IH.ToLocal(record.[Timestamp], @timeZoneCode), 121),
			record.*,
			HeatName = heat.[Name],
			TypeName = cst.[Name],
			Total = ISNULL([MgO], 0) + ISNULL([CaO], 0) + ISNULL([Fe2O3], 0) + ISNULL([Al2O3], 0) 
					+ ISNULL([SiO2], 0) + ISNULL([MnO], 0) + ISNULL([Cr2O3], 0) + ISNULL([TiO2], 0) + ISNULL([ZrO2], 0) + ISNULL([P2O5], 0) + ISNULL([SO3], 0),
			HiCal, 
			Dolo,
			ProSlag, 
			Carbon,
			TotalSlagVolume =(0.94*c.HiCal+0.58*c.Dolo)/([CaO]/100),
			LbsMgOAdded = 0.41*c.Dolo + 0.02*c.HiCal,
			LbsMgOnSlag = ((0.94*c.HiCal+0.58*c.Dolo)/([CaO]/100))*([MgO]/100.0),
			RefractoryWear =(0.41*c.Dolo + 0.02*c.HiCal) - ((0.94*c.HiCal+0.58*c.Dolo)/([CaO]/100))*([MgO]/100.0) ,
			DirtLoad = (0.94*c.HiCal+0.58*c.Dolo) / B3ratio,
			FurnaceCode = ep.FurnaceCode,
			FeinSlag = ((0.94*c.HiCal+0.58*c.Dolo)/([CaO]/100))*([FeO]/100)*0.7773
			
			,B3Status = case when B3ratio < @B3Min_Structural or B3ratio between @B3Max_Structural and @B3Min_Rail or B3ratio > @B3Max_Rail then 'WARNING' else 'NORMAL' end
			,MgOStatus = case when MgO < @MgoMin_Rail or MgO between @MgoMax_Rail and @MgOMin_Structural or MgO > @MgOMax_Structural then 'WARNING' else 'NORMAL' end
			,FeOStatus = case when FeO < @FeOMin_Rail or FeO between @FeOMax_Rail and @FeOMin_Structural or FeO > @FeOMax_Structural then 'WARNING' else 'NORMAL' end
			,RefractoryWearStatus = case when  ((0.41*c.Dolo + 0.02*c.HiCal) - ((0.94*c.HiCal+0.58*c.Dolo)/([CaO]/100))*([MgO]/100.0)) < @RefractoryWearLimit then 'WARNING'  when ((0.41*c.Dolo + 0.02*c.HiCal) - ((0.94*c.HiCal+0.58*c.Dolo)/([CaO]/100))*([MgO]/100.0)) is null then 'NOVALUE' else 'NORMAL' end
			,TotalStatus = case when (ISNULL([MgO], 0) + ISNULL([CaO], 0) + ISNULL([Fe2O3], 0) + ISNULL([Al2O3], 0) + ISNULL([SiO2], 0)
							+ ISNULL([MnO], 0) + ISNULL([Cr2O3], 0) + ISNULL([TiO2], 0) + ISNULL([ZrO2], 0) + ISNULL([P2O5], 0) + ISNULL([SO3], 0)) < @TotalLimit then 'WARNING' else 'NORMAL' end
	FROM LAB.SlagChemistrySamples record
		JOIN MES.Heats heat ON heat.Id = record.HeatId
		join EAF.Processes ep on heat.id = ep.HeatId 
		LEFT JOIN CAT.ChemistrySampleTypes cst ON cst.Code = record.TypeCode
		Cross apply SCRAP.GetFluxesChargeByHeatId(ep.HeatId) c
		WHERE ep.Id = @processId
		ORDER BY  left(Heat.[Name],1) ASC, record.[Timestamp] desc


		-----------------------
		-- Consumptions
		-----------------------
		--All Data
		SELECT 
			cons.Code, 
			[Total] = SUM(CASE WHEN ((cons.[Counter] IS NOT NULL) OR (process.[End] IS NOT NULL)) THEN cons.[Counter]
							ELSE EAF.GetConsumptionOnline(process.FurnaceCode, cons.Code) END)
		into #allconsumptions
		FROM EAF.Consumptions cons
			JOIN EAF.GetConsumptionCodes(@ladleFurnaceCode) consCode ON consCode.Code = cons.Code
			JOIN EAF.Processes process ON process.Id = cons.ProcessId
		WHERE cons.ProcessId = @processId
		group by cons.Code

		
			-----------------------
			-- Total Consumptions
			-----------------------
			SELECT * 
			INTO #totalconsumptions
			FROM
			(
				select * from #allconsumptions
			)t
			Pivot(
				MAX(Total)
				FOR [Code]
				IN (
					[INJECTED_LIME],[LANCE_CARBON],[LANCE_OXYGEN],[NATURAL_GAS_BURNER_1],[NATURAL_GAS_BURNER_2],[NATURAL_GAS_BURNER_3]
					,[NATURAL_GAS_BURNER_4],[NATURAL_GAS_BURNER_5],[O2SS_BURNER_1],[O2SS_BURNER_2],[O2SS_BURNER_3],[O2SS_BURNER_4],[O2SS_BURNER_5]
					,[OXYGEN_BURNER_1],[OXYGEN_BURNER_2],[OXYGEN_BURNER_3],[OXYGEN_BURNER_4],[OXYGEN_BURNER_5],[SIDEWALL_CARBON]
				) 
			) AS PivotTable

			--Summarized data
			SELECT 
				TotalInjectedLime = INJECTED_LIME,
				TotalLanceCarbon = LANCE_CARBON,
				TotalLanceOxygen = LANCE_OXYGEN,
				TotalNaturalGasBurner = NATURAL_GAS_BURNER_1 + NATURAL_GAS_BURNER_2 + NATURAL_GAS_BURNER_3 + NATURAL_GAS_BURNER_4 + NATURAL_GAS_BURNER_5,
				TotalOxygenBurner = OXYGEN_BURNER_1 + OXYGEN_BURNER_2 + OXYGEN_BURNER_3 + OXYGEN_BURNER_4 + OXYGEN_BURNER_5, 
				TotalO2SSBurner = O2SS_BURNER_1 + O2SS_BURNER_2 + O2SS_BURNER_3 + O2SS_BURNER_4 + O2SS_BURNER_5,
				TotalSidewallCarbon = SIDEWALL_CARBON
			into #temp
			FROM #totalconsumptions

			select [Code] = 'Injected Lime', [Total] = TotalInjectedLime from #temp union ALL
			--select [Code] = 'Lance Carbon',	 [Total] = TotalLanceCarbon	 from #temp union ALL
			--select [Code] = 'Lance Oxygen',	 [Total] = TotalLanceOxygen	 from #temp union ALL
			select [Code] = 'Natural Gas Burner',	 [Total] = TotalNaturalGasBurner	 from #temp union ALL
			select [Code] = 'O2SS Burner',	 [Total] = TotalO2SSBurner	 from #temp union ALL
			select [Code] = 'Oxygen Burner',	 [Total] = TotalOxygenBurner	 from #temp union ALL
			select [Code] = 'Sidewall Carbon',	 [Total] = TotalSidewallCarbon	 from #temp 

			-----------------------
			-- ByInjector - Sidewall
			-----------------------
			select * into #gas_burner	 from #allconsumptions where Code like '%NATURAL_GAS_BURNER%'
			select * into #o2ss_burner	 from #allconsumptions where Code like '%O2SS_BURNER%' 
			select * into #oxygen_burner from #allconsumptions where Code like '%OXYGEN_BURNER%'

			SELECT [Injector], [NATURAL_GAS_BURNER_1] AS [Pos1],[NATURAL_GAS_BURNER_2] AS [Pos2],[NATURAL_GAS_BURNER_3] AS [Pos3],[NATURAL_GAS_BURNER_4] AS [Pos4],[NATURAL_GAS_BURNER_5] AS [Pos5]
			into #summ_gas_burner
			FROM
			(
				select
				[Injector] = 'NaturalGas', 
				Code,
				Total
				from
				#gas_burner
			)t
			Pivot(
				MAX(Total)
				FOR [Code]
				IN (
					[NATURAL_GAS_BURNER_1],[NATURAL_GAS_BURNER_2],[NATURAL_GAS_BURNER_3],[NATURAL_GAS_BURNER_4],[NATURAL_GAS_BURNER_5]
				) 
			) AS PivotTable

			SELECT [Injector], [O2SS_BURNER_1] AS [Pos1],[O2SS_BURNER_2] AS [Pos2],[O2SS_BURNER_3] AS [Pos3],[O2SS_BURNER_4] AS [Pos4],[O2SS_BURNER_5] AS [Pos5]
			into #summ_o2ss_burner
			FROM
			(
				select
				[Injector] = 'O2SS', 
				Code,
				Total
				from
				#o2ss_burner
			)t
			Pivot(
				MAX(Total)
				FOR [Code]
				IN (
					[O2SS_BURNER_1],[O2SS_BURNER_2],[O2SS_BURNER_3],[O2SS_BURNER_4],[O2SS_BURNER_5]
				) 
			) AS PivotTable

		
			SELECT [Injector], [OXYGEN_BURNER_1] AS [Pos1],[OXYGEN_BURNER_2] AS [Pos2],[OXYGEN_BURNER_3] AS [Pos3],[OXYGEN_BURNER_4] AS [Pos4], [OXYGEN_BURNER_5]  AS [Pos5]
			into #summ_oxygen_burner
			FROM
			(
				select
				[Injector] = 'Oxygen', 
				Code,
				Total,
				Pos5 = null
				from
				#oxygen_burner
			)t
			Pivot(
				MAX(Total)
				FOR [Code]
				IN (
					[OXYGEN_BURNER_1],[OXYGEN_BURNER_2],[OXYGEN_BURNER_3],[OXYGEN_BURNER_4],[OXYGEN_BURNER_5]
				) 
			) AS PivotTable


			select * from #summ_gas_burner
			union all 
			select * from #summ_o2ss_burner
			union all 
			select * from #summ_oxygen_burner


		-------------------------
		---- Alloys
		-------------------------
		SELECT
		
			[Datetime] = IH.ToLocal(BatchStart,@timeZoneCode),
			[Weight] = det.WeightPV,
			[Material] = mat.[Name]
		FROM EAF.AdditionBatches a
		left join EAF.Processes p on p.Id = a.ProcessID
		left join EAF.AdditionBatchDetails det on det.AdditionBatchID = a.ID
		left join cat.Materials mat on mat.Id = det.MaterialId
		WHERE ProcessId = @processId 
	
		-----------------------
		-- 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

		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

		
		
		-----------------------
		-- Slag Limits
		-----------------------
		select 
			p.[Name]
			,k.Code
			,k.[Value]
		from cat.KpiVariables k
		left join cat.ProductTypes p on p.Id = k.productTypeId
		where k.Code like '%SLAG_MgO%' or  k.Code like '%SLAG_FeO%' OR k.Code like '%SLAG_B3%'

		-----------------------
		-- Pourback
		-----------------------
		--From another Heat to current Heat
		select p.*, [HeatName] = h.[Name] from eaf.Pourbacks p
		left join mes.Heats h on h.Id = p.FromHeatId
		where EAFProcessId  = @processId

		--Send current Heat to another Heat
		select p.*, [HeatName] = h.[Name] from eaf.Pourbacks p
		left join mes.Heats h on h.Id = (select heatId from eaf.processes where Id = p.EAFProcessId)
		where FromHeatId  = @heatId


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

	RETURN 0
END












/*--------------------------------- 8 / 11 / 2023 ------------------------------------------*/
USE [MES]
GO
/****** Object:  UserDefinedFunction [CAS].[GetProductString]    Script Date: 8/11/2023 21:32:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [EAF].[GetElectrodeEventsByHeatAndElectrode]
(
	@heatId int,
	@PhaseCode int
)
RETURNS varchar(20)
AS
BEGIN
	DECLARE @resultString NVARCHAR(MAX);
	
	SELECT @resultString = STUFF((
    SELECT ' / ' + ElectrodeEventCode
    FROM (
        SELECT ElectrodeEventCode, [Datetime]
        FROM EAF.ElectrodeEvents
        WHERE heatId = @heatId
		and PhaseCode =@PhaseCode
    ) AS SubQuery
    ORDER BY [Datetime] DESC
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');


	
	return @resultString	
END



/* Add in line 163 of eaf.getHeatReport these lines */

--KwhTon = process.KwhTon,
--			Electrode1 = [EAF].[GetElectrodeEventsByHeatAndElectrode](process.heatId,1),
--			Electrode2 = [EAF].[GetElectrodeEventsByHeatAndElectrode](process.heatId,2),
--			Electrode3 = [EAF].[GetElectrodeEventsByHeatAndElectrode](process.heatId,3)

--		FROM EAF.Processes process