USE [MES]
GO
/****** Object:  StoredProcedure [EAF].[GetShiftReport]    Script Date: 3/25/2024 4:41:32 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [EAF].[GetShiftReport] 
	@ProductionDate DATETIME = NULL
	,@ShiftId 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 + '  @ProductionDate: ' + ISNULL(CAST(@ProductionDate AS VARCHAR), '') 
					+ ', @ShiftId: '	  + ISNULL(CAST(@ShiftId AS VARCHAR), '') 
					+ ', @timezoneCode: ' + ISNULL(CAST(@timezoneCode AS VARCHAR), '')

	-----------------------
	-- Variables
	-----------------------
	DECLARE @startUtc DATETIME, @endUtc DATETIME

	DECLARE @HiCal_MaterialId	INT = (SELECT TOP 1 Id FROM [CAT].[Materials] WHERE [Name] = 'HiCal')
--	DECLARE @Carbon_MaterialId	INT = (SELECT TOP 1 Id FROM [CAT].[Materials] WHERE [Name] = 'Carbon')
	
    DECLARE @Carbon_MaterialId	INT = (SELECT TOP 1 Id FROM [CAT].[Materials] WHERE [Name] = 'Charge Carbon')
	DECLARE @Dolo_MaterialId	INT = (SELECT TOP 1 Id FROM [CAT].[Materials] WHERE [Name] = 'Dolo')
	DECLARE @ProSlag_MaterialId INT = (SELECT TOP 1 Id FROM [CAT].[Materials] WHERE [Name] = 'ProSlag')

	-----------------------
	-- Parameters default
	-----------------------
	IF (@timeZoneCode IS NULL)		SET @timeZoneCode = 'EDTIND'
	IF (@ProductionDate IS NULL)	SET @ProductionDate = dateadd(day,-1,getdate());
	IF (@ShiftId IS NULL)			SET @ShiftId = 1;

	-----------------------
	-- Set Delays variables
	-----------------------
	SELECT @startUtc = IH.ToUTC(dateadd(hour, 9 + 12 * (@ShiftID - 1), @ProductionDate), @timezoneCode)
	SET @endUtc = dateadd(hour, 12, @startUtc)

	-----------------------
	-- Set Slag Limits
	-----------------------
	DECLARE @FeOMax_Structural FLOAT = (select top 1 k.[Value] from cat.GradeGroupAttributes k left join cat.GradeGroups p on p.Id = k.gradeGroupId
									    where k.Code = 'SLAG_FeO_MAX_LIMIT' and p.[Name]= 'Structural')
	DECLARE @FeOMin_Structural FLOAT = (select top 1 k.[Value] from cat.GradeGroupAttributes k left join cat.GradeGroups p on p.Id = k.gradeGroupId
									    where k.Code = 'SLAG_FeO_MIN_LIMIT' and p.[Name]= 'Structural')

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

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


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

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

	DECLARE @B3Max_Rail FLOAT = (select top 1 k.[Value] from cat.GradeGroupAttributes k left join cat.GradeGroups p on p.Id = k.gradeGroupId
									    where k.Code = 'SLAG_B3_MAX_LIMIT' and p.[Name]= 'Rail')
	DECLARE @B3Min_Rail FLOAT = (select top 1 k.[Value] from cat.GradeGroupAttributes k left join cat.GradeGroups p on p.Id = k.gradeGroupId
									    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
	-----------------------
		-----------------------------------
		-- |Main| Data - EAF Processes List
		-----------------------------------
		SELECT 
			 [Id] = process.Id
			,[HeatId] = process.HeatId
			,[EAFName] = furnace.[Name]
			,[HeatName] = heat.[Name] 
			,[ShiftId] = process.ShiftId
			,[CrewId] = process.CrewId
		
			,[Start] = IH.ToLocal(process.[Start], @timeZoneCode)
			,[End] = IH.ToLocal(process.[End], @timeZoneCode)
			,[Duration] = CAST(DATEDIFF(MINUTE, process.[Start], process.[End]) AS VARCHAR) + ':' + CAST(DATEDIFF(SECOND, process.[Start], process.[End]) % 60 AS VARCHAR)
			,[StartTap] = IH.ToLocal(process.StartTap, @timeZoneCode)
			,[EndTap] = IH.ToLocal(process.EndTap, @timeZoneCode)
			,[PowerOn] = process.PowerOn
			,[TapToTap] = DATEDIFF(SECOND, (SELECT TOP 1 [EndTap] FROM EAF.PRocesses z WHERE z.FurnaceCode = process.FurnaceCode AND z.EndTap < process.EndTap ORDER BY id DESC), [EndTap])
		

			,[SteelWeight] = heat.SteelWeight
			,[ScrapWeight] = (SELECT sum(z.ScrapWeight) FROM EAF.Buckets z WHERE z.ProcessId = process.id )
		
			,[ElectricEnergy] = process.Energy--MWH
			,[SpecificEnergy] = process.Energy * 1000.0 / (nullif((SELECT sum(z.ScrapWeight) FROM EAF.Buckets z WHERE z.ProcessId = process.id ),0)/2000) --KwhTon
			,[ElectricPower] = process.Energy / process.PowerOn * 60 --MwhMin
		
			,[Electrode1Changed] = CASE WHEN process.Electrode1Changed = 1 THEN 1 ELSE 0 END
			,[Electrode2Changed] = CASE WHEN process.Electrode2Changed = 1 THEN 1 ELSE 0 END
			,[Electrode3Changed] = CASE WHEN process.Electrode3Changed = 1 THEN 1 ELSE 0 END
			,[SmartARC] = case when process.RegulationModeId = 1 then process.PowerOn * 1.0 else 0.0 end --SMART ARC ID = REGULATION MODE 1
		
			/* Fluxes */
			,[InjectedC] = (SELECT sum(z.[Counter])	FROM EAF.Consumptions z WHERE z.processid = process.id AND z.code IN ('CARBON','LANCE_CARBON','SIDEWALL_CARBON'))
			,[InjectedLime] = ( SELECT sum(z.[Counter]) FROM EAF.Consumptions z WHERE z.processid = process.id AND z.code IN ('INJECTED_LIME'))
			,[FluxWeight] = (SELECT sum(z.[Weight]) FROM SCRAP.ChargeFluxes z LEFT JOIN SCRAP.Charges x ON z.ChargeId = x.Id WHERE x.HeatId = process.Heatid)
			,[HiCal] = ISNULL((
					SELECT sum(z.[Weight])
					FROM SCRAP.ChargeFluxes z
					LEFT JOIN SCRAP.Charges x ON z.ChargeId = x.Id
					WHERE x.HeatId = process.Heatid	AND z.FluxMaterialId = @HiCal_MaterialId), 0)
			,[Carbon] = ISNULL((
					SELECT sum(z.[Weight])
					FROM SCRAP.ChargeFluxes z
					LEFT JOIN SCRAP.Charges x ON z.ChargeId = x.Id
					WHERE x.HeatId = process.Heatid	AND z.FluxMaterialId = @Carbon_MaterialId), 0)
			,[ProSlag] = ISNULL((
					SELECT sum(z.[Weight])
					FROM SCRAP.ChargeFluxes z
					LEFT JOIN SCRAP.Charges x ON z.ChargeId = x.Id
					WHERE x.HeatId = process.Heatid	AND z.FluxMaterialId = @ProSlag_MaterialId), 0)
			,[Dolomite] = ISNULL((
					SELECT sum(z.[Weight])
					FROM SCRAP.ChargeFluxes z
					LEFT JOIN SCRAP.Charges x ON z.ChargeId = x.Id
					WHERE x.HeatId = process.Heatid
						AND z.FluxMaterialId = @Dolo_MaterialId
						AND z.SiloCode = 3
					), 0)
			,[Dolomite2] = ISNULL((
					SELECT sum(z.[Weight])
					FROM SCRAP.ChargeFluxes z
					LEFT JOIN SCRAP.Charges x ON z.ChargeId = x.Id
					WHERE x.HeatId = process.Heatid
						AND z.FluxMaterialId = @Dolo_MaterialId
						AND z.SiloCode = 4
					), 0)
			/* Temperatures */
			,[EAFLastTemperature] = (SELECT TOP 1 z.Temperature FROM EAF.Temperatures z	WHERE z.ProcessId = process.id  and z.Temperature <> 0 and z.Temperature is not null  ORDER BY z.Timestamp DESC)
			,[EAFLastO2] = (SELECT TOP 1 z.Oxygen FROM EAF.Temperatures z WHERE z.ProcessId = process.id and z.Oxygen <> 0 and z.Oxygen is not null ORDER BY z.Timestamp DESC)
			
			,[EAFAimTemperature] = process.AimTemperature
			,[EAFAimO2]		 = process.AimOxygen
		
			/* Residuals */
			,[Cu] = (
				SELECT TOP 1 Cu
				FROM LAB.ChemistrySamples z
				WHERE z.HeatId = process.heatid
					AND z.TypeCode = 'LMFLD'
				ORDER BY z.[Timestamp] ASC
				)
			,[Sn] = (
				SELECT TOP 1 Sn
				FROM LAB.ChemistrySamples z
				WHERE z.HeatId = process.heatid
					AND z.TypeCode = 'LMFLD'
				ORDER BY z.[Timestamp] ASC
				)
			,[Mo] = (
				SELECT TOP 1 Mo
				FROM LAB.ChemistrySamples z
				WHERE z.HeatId = process.heatid
					AND z.TypeCode = 'LMFLD'
				ORDER BY z.[Timestamp] ASC
				)
			,[P] = (
				SELECT TOP 1 P
				FROM LAB.ChemistrySamples z
				WHERE z.HeatId = process.heatid
					AND z.TypeCode = 'LMFLD'
				ORDER BY z.[Timestamp] ASC
				)
			,[Cr] = (
				SELECT TOP 1 Cr
				FROM LAB.ChemistrySamples z
				WHERE z.HeatId = process.heatid
					AND z.TypeCode = 'LMFLD'
				ORDER BY z.[Timestamp] ASC
				)
			,[Zn] = (
				SELECT TOP 1 Zn
				FROM LAB.ChemistrySamples z
				WHERE z.HeatId = process.heatid
					AND z.TypeCode = 'LMFLD'
				ORDER BY z.[Timestamp] ASC
				)
			,[Ni] = (
				SELECT TOP 1 Ni
				FROM LAB.ChemistrySamples z
				WHERE z.HeatId = process.heatid
					AND z.TypeCode = 'LMFLD'
				ORDER BY z.[Timestamp] ASC
				)
			,[Pb] = (
			SELECT TOP 1 Pb
			FROM LAB.ChemistrySamples z
			WHERE z.HeatId = process.heatid
				AND z.TypeCode = 'LMFLD'
			ORDER BY z.[Timestamp] ASC
			)
			,[RowNum] = ROW_NUMBER() OVER (	ORDER BY process.[Start] DESC)
			,ScrapRecipeId = (select top 1 scrapRecipeId from SCRAP.Charges charges where charges.HeatNumber = heat.[Name])
			, HeatsOnTapHole
			, NonFreeOpenDuration
		INTO #records
		FROM EAF.Processes process
		left JOIN MES.Heats heat ON heat.Id = process.HeatId
		LEFT JOIN CAT.Furnaces furnace ON furnace.Code = process.FurnaceCode
		--LEFT JOIN SCRAP.Charges charges on charges.HeatNumber = heat.[Name]
		WHERE process.ProductionDate = @ProductionDate AND process.ShiftId = @ShiftId and process.[End] is not null 
	
			-------------------------------------
			-- Table |0| ----> Totals and Averages
			-------------------------------------
			SELECT 
				EAF = CASE WHEN r.EAFName IS NULL THEN 'Total' ELSE r.EAFName END
				,TotalHeats = count(*)
				,TotalSteelWeight = sum(SteelWeight)
				,TotalScrapWeight = sum(ScrapWeight) / 2000.0
				,TotalElectricEnergy = sum(ElectricEnergy)
				,TotalElectrodeAdds = cast(sum(Electrode1Changed) AS VARCHAR) + '-' + cast(sum(Electrode2Changed) AS VARCHAR) + '-' + cast(sum(Electrode3Changed) AS VARCHAR)
				,SteelWeight = avg(SteelWeight)
				,ScrapWeight = avg(ScrapWeight) / 2000.0
				,Fluxes = avg(FluxWeight)
				,Yield = (sum(SteelWeight) / NULLIF(sum(ScrapWeight), 0) * 2000.0) * 100.0
				,TapToTap = avg(r.TapToTap) / 60.0
				,PowerOn = avg(r.PowerOn) / 60.0
				,InjectedC = avg(r.InjectedC)
				,EAFLastTemperature = avg(EAFLastTemperature)
				,ElectricEnergy = avg(ElectricEnergy)
				,SpecificEnergy = avg(ElectricEnergy /(nullif(ScrapWeight,0)/2000)) * 1000.0 
				,ElectricPower = avg(ElectricEnergy / NULLIF(r.PowerOn, 0) ) * 3600
				,SmartARC = cast (sum(SmartArc) / sum(r.PowerOn) * 100.0 as decimal(10,2))
				,SteelWeightSTD =	cast( stdev(SteelWeight) as decimal(18,2))
				,ScrapWeightSTD =	cast( stdev(ScrapWeight/ 2000.0)  as decimal(18,2))
				,YieldSTD =			cast( stdev(SteelWeight / NULLIF(ScrapWeight, 0) * 2000.0) * 100.0 as decimal(18,2))
				,TapToTapSTD =		cast( stdev(r.TapToTap / 60.0 ) as decimal(18,2))
				,PowerOnSTD =		cast( stdev(r.PowerOn / 60.0)  as decimal(18,2))
				,SpecificEnergySTD =cast( stdev(ElectricEnergy / (nullif(ScrapWeight,0)/2000) * 1000.0) as decimal(18,2))
				,CrewId = max(CrewId)
			FROM #records r
			GROUP BY r.EAFName
			WITH rollup
			----------------------
			-- Table |1| -- Recipes
			-----------------------
			-------
			-- 4 --
			-------
			declare @actual table (Recipe varchar(100), Material varchar(100), [Weight] float, Price float, ScrapRecipeId int)
			declare @plan table (Recipe varchar(100), Material varchar(100), [Weight] float, Price float, ScrapRecipeId int)			
			--declare @recipeResults table (Recipe varchar(200), Material varchar(200),[Plan] int, [Actual] int, [DiffWeight] int, [DiffPercent] decimal(10,2))

			-- STEP 1
			insert into @actual(Recipe,Material,[Weight], ScrapRecipeId)
			select
			 sr.Name
			 ,sm.Name
			 ,sum(cd.[Weight])
			 ,MAX( c.ScrapRecipeId)
			from SCRAP.Charges c
			JOIN SCRAP.ChargeDetails cd on cd.ChargeId = c.Id
			JOIN SCRAP.ScrapMaterials sm on sm.Id =cd.ScrapMaterialId
			LEFT JOIN MES.Heats h on h.Id = c.HeatId
			LEFT JOIN SCRAP.ScrapRecipes sr on sr.Id = c.ScrapRecipeId
			WHERE h.ShiftId = @ShiftId AND h.ProductionDate = @ProductionDate and cd.[Weight] < 2000000000 --overflow error
			group by sr.Name, sm.Name	
			
			-- STEP 2
			insert into @plan(Recipe,Material,[Weight], ScrapRecipeId)
			select
			 sr.Name
			 ,sm.Name
			 ,sum(srl.[Weight])
			 , MAX(c.ScrapRecipeId)
			from SCRAP.Charges c
			JOIN scrap.ScrapRecipeLayers srl on srl.ScrapRecipeId = c.ScrapRecipeId
			JOIN SCRAP.ScrapMaterials sm on sm.Id =srl.ScrapMaterialId
			LEFT JOIN MES.Heats h on h.Id = c.HeatId
			LEFT JOIN SCRAP.ScrapRecipes sr on sr.Id = c.ScrapRecipeId
			WHERE h.ShiftId = @ShiftId AND h.ProductionDate = @ProductionDate and srl.[Weight] < 2000000000 --overflow error
			group by sr.Name, sm.Name
				
			
			
			--	return 0 --everything ok

			CREATE TABLE #recipeResults (
			Recipe varchar(200),
			Material varchar(200),
			[Plan] int,
			[Actual] int,
			[DiffWeight] int,
			[DiffPercent] decimal(10,2),
			ScrapRecipeId int
			);

			
			-- STEP 3
			INSERT INTO #recipeResults
			select
				--Recipe = ISNULL(a.Recipe,p.Recipe + '*')
				--,Material = ISNULL(a.Material,p.Material + '*')	
				Recipe = ISNULL(a.Recipe,p.Recipe)
				,Material = ISNULL(a.Material,p.Material)	
				,[Plan] = cast(ISNULL(p.[Weight],0) as int) 
				,Actual =  cast(ISNULL(a.[Weight],0) as int)	 		
				,DiffWeight = cast(ISNULL(p.[Weight],0) as int) - cast(ISNULL(a.[Weight],0) as int)
				,DiffPercent = case when (ISNULL(p.[Weight],0)  - ISNULL(a.[Weight],0)) > 0 then
								cast(((ISNULL(p.[Weight],0)  - ISNULL(a.[Weight],0)) / ISNULL(p.[Weight],0)  ) * 100  as decimal(10,2))
								else 
									cast(((ISNULL(p.[Weight],0)  - ISNULL(a.[Weight],0)) / ISNULL(a.[Weight],0)  ) * 100  as decimal(10,2))
								end
				, a.ScrapRecipeId
			from @actual a
			full join @plan p on p.Recipe  = a.Recipe and p.Material = a.Material
 			where p.[weight] > 0
			-- and p.[Weight] < 2000000000 and a.[Weight] < 2000000000
			
			-- STEP 4
			--select * from @recipeResults
			--order by Recipe,Material

			DECLARE @RecipeName VARCHAR(100); 
			declare @SQLQuery VARCHAR(MAX);

			CREATE TABLE #result (
			 Material VARCHAR(200)
			);

			
			DECLARE recipes_cursor CURSOR FOR 
			SELECT DISTINCT [Recipe]
			FROM #recipeResults rr inner join #records rec on rr.ScrapRecipeId = rec.ScrapRecipeId;
			-- Open the cursor
			OPEN recipes_cursor;
			
			FETCH NEXT FROM recipes_cursor INTO @RecipeName;

			WHILE @@FETCH_STATUS = 0
			BEGIN

			SET @SQLQuery = '
        ALTER TABLE #result
        ADD [' + @RecipeName + '-Plan] INT,
            [' + @RecipeName + '-Actual] INT;';

		--SELECT @SQLQuery
		EXEC(@SQLQuery);

			SET @SQLQuery = '
			INSERT INTO #result (Material, [' + @RecipeName + '-Plan], [' + @RecipeName + '-Actual])
				SELECT
					Material,
					MAX([' + @RecipeName + '-Plan])   AS [' + @RecipeName + '-Plan],
					MAX([' + @RecipeName + '-Actual]) AS [' + @RecipeName + '-Actual]
				FROM (
					SELECT Recipe, Material, [Plan] AS [' + @RecipeName + '-Plan], [Actual] AS [' + @RecipeName + '-Actual]
					FROM #recipeResults WHERE Recipe = ''' + @RecipeName + '''
				) AS SourceTable
				GROUP BY Material';
		EXEC(@SQLQuery);

				-- Fetch the next row from the cursor
				FETCH NEXT FROM recipes_cursor INTO @RecipeName;
			END

			-- Close the cursor
			CLOSE recipes_cursor;

			-- Deallocate the cursor
			DEALLOCATE recipes_cursor;


			select * from #result
			drop table #result
			----------------------------------------------------------
			--Table |2| ---- TOP 3 EAF A DELAYS and TOP 3 EAF B DELAYS
			----------------------------------------------------------
				;WITH CTE
			AS (
				SELECT rn = ROW_NUMBER() OVER (
						PARTITION BY equip.Code ORDER BY DATEDIFF(MINUTE, d.[Start], d.[End]) DESC
						)
					,Furnace = equip.[Name]
					,[Code] = case when dc.[Code] is not null then dc.[Code] else 'Not in the List' END
                    
					--,[TopDelayCodeReason] = case when dc.[Code] is not null then dc.[Code] else 'Not in the List' end
					--,[TopDelayCodeReason] = (select top 1 z.[Code] from IHDelays2.CAT.DelayCodes z where z.Id =					
					--	( 					
					--	select top 1 x.Id from EAF.vDelayCodesForPLC x where  x.Id0 = dc.Id  or x.Id1 = dc.Id or  x.Id2 = dc.Id or x.Id3 = dc.Id or x.Id4 =dc.Id
					--	)
					--)
					,[TopDelayCodeReason] = 			
						( 					
						select top 1 x.Level0 from EAF.vDelayCodesForPLC x where  x.Id0 = dc.Id  or x.Id1 = dc.Id or  x.Id2 = dc.Id or x.Id3 = dc.Id or x.Id4 =dc.Id
						)
					
					,[Start] = IH.ToLocal(d.Start, @timezoneCode)
					,[End] = IH.ToLocal(d.[End], @timezoneCode)
					,[Duration] = DATEDIFF(MINUTE, d.[Start], d.[End])
					,d.DelayCodeName
					FROM IHDelays2.PRD.Delays d
				LEFT JOIN IHDelays2.PRD.Delays children ON children.ParentId = d.Id
				JOIN IHDelays2.CAT.ProductionItems pitems ON pitems.Id = d.MachineProductionItemId
				JOIN CAT.Furnaces equip ON equip.[Name] = pitems.[Name]
				LEFT JOIN IHDelays2.CAT.DelayCodes dc ON dc.id = d.DelayCodeId
					WHERE d.Deleted = 0
					AND children.Id IS NULL
					--AND d.[Start] <= @endUtc
					--AND d.[End] >= @StartUtc
					AND d.ShiftId IS NOT NULL
					AND d.[Start] between @startutc and @endUtc
					AND DATEDIFF(MINUTE, d.[Start], d.[End]) >= 3
				)
			SELECT *
			FROM CTE
			WHERE rn < 4
			ORDER BY Furnace,DATEDIFF(MINUTE, [Start], [End]) desc , rn			
			

		select * 
		INTO #tempTable_LogBook
		from (
		SELECT 
			[Title] = l.Title
			,[Author] = l.Author
			,[ShiftID] = l.ShiftID
			,[CrewId] = l.CrewId
			,[Description] = l.[Description]			
			,[Timestamp] = IH.ToLocal(l.[CreateTimestamp],'EDTIND')
		FROM LogBook.LB.LogEntries l
		WHERE @ProductionDate = l.ProductionDate 
			AND l.ShiftID = case when @ShiftId = 1 then 'D' when @ShiftId = 2 then 'N' end		
			AND LogBookTypeCode = 'M'
		UNION
	
		SELECT h.[Name]
			,'EAF Op'
			,case when ep.ShiftId = 1 then 'D' when ep.ShiftId = 2 then 'N' end
			,ep.CrewId
			,ep.Comments			
			,[Timestamp] = IH.ToLocal(ep.[Start],'EDTIND')
		FROM EAF.Processes ep
		LEFT JOIN MES.Heats AS h ON ep.HeatId = h.Id
		LEFT JOIN LogBook.LB.[catLogEntryTypes] let ON let.ID = 4
		WHERE ep.Id IN (SELECT Id From #records) -- was replaced by <--  WHERE p.ProductionDate = @ProductionDate AND p.ShiftId = @ShiftId  
		) a

		select * from #tempTable_LogBook order by [Timestamp] desc
   
   ----------------------------------------------
   -- GANTT DIAGRAM --- Table |4| and Table |5|
   ----------------------------------------------

		----------------------------
		-- Table |4| -- HEATS GANTT
		----------------------------

		SELECT 
			[Furnace] = furnace.[Name]
			,[HeatName] = heat.[Name]
			,[Start] = IH.ToLocal(process.[Start], @timeZoneCode)
			,[End] = IH.ToLocal(process.[End], @timeZoneCode)
			,[PowerOn] = process.PowerOn
			,[Duration] = DATEDIFF(MINUTE, process.[Start], process.[End])
			,[Color]= case when DATEDIFF(MINUTE, process.[Start], process.[End]) < 40 then '#3ebf3c'
						   when DATEDIFF(MINUTE, process.[Start], process.[End]) > 50 then '#ff5900'
						   else '#f3f317'	   
						   end

		FROM EAF.Processes process
		JOIN MES.Heats heat ON heat.Id = process.HeatId
		LEFT JOIN CAT.Furnaces furnace ON furnace.Code = process.FurnaceCode
		WHERE process.Id IN (SELECT Id From #records) -- was replaced by <--  WHERE process.ProductionDate = @ProductionDate AND process.ShiftId = @ShiftId
		ORDER BY 
			furnace.[Name] ASC, 
			IH.ToLocal(process.[Start], @timeZoneCode) ASC

		----------------------------
		-- Table |5| -- DELAYS GANTT
		----------------------------
		
		SELECT Furnace = equip.[Name]
			,dc.[Code]
			,[Start] = IH.ToLocal(d.Start, @timeZoneCode)
			,[End] = IH.ToLocal(d.[End], @timeZoneCode)
			,[Color] = CASE WHEN scat.Color IS NULL THEN '#eee' ELSE scat.Color end
			,[Duration] = DATEDIFF(MINUTE, d.[Start], d.[End])
			,dc.CategoryId
		FROM IHDelays2.PRD.Delays d
		LEFT JOIN IHDelays2.PRD.Delays children ON children.ParentId = d.Id
		JOIN IHDelays2.CAT.ProductionItems pitems ON pitems.Id = d.MachineProductionItemId
		JOIN CAT.Furnaces equip ON equip.[Name] = pitems.[Name]
		LEFT JOIN IHDelays2.CAT.DelayCodes dc ON dc.id = d.DelayCodeId
		LEFT JOIN IHDelays2.CAT.DelayCategories dCat ON dCat.Id = dc.CategoryId
		LEFT JOIN IHDelays2.CAT.SRDCategories scat ON scat.DelayCodeId = IHDelays2.[dbo].[GetRootParentByDelayCodeId](dc.Id)
		WHERE d.Deleted = 0
			AND children.Id IS NULL
			AND d.[Start] <= @endUtc
			AND d.[End] >= @StartUtc
			AND d.ShiftId IS NOT NULL
			AND DATEDIFF(MINUTE, d.[Start], d.[End]) >= 3
		ORDER BY equip.[Name] ASC
			,IH.ToLocal(d.Start, NULL) ASC


		--SELECT Furnace = equip.[Name]
		--	,dc.[Code]
		--	,[Start] = IH.ToLocal(d.Start, @timeZoneCode)
		--	,[End] = IH.ToLocal(d.[End], @timeZoneCode)
		--	,[Color] = CASE WHEN dCat.Color IS NULL THEN '#8280fc' ELSE dCat.Color end
		--	,[Duration] = DATEDIFF(MINUTE, d.[Start], d.[End])
		--	,dc.CategoryId
		--FROM IHDelays2.PRD.Delays d
		--LEFT JOIN IHDelays2.PRD.Delays children ON children.ParentId = d.Id
		--JOIN IHDelays2.CAT.ProductionItems pitems ON pitems.Id = d.MachineProductionItemId
		--JOIN CAT.Furnaces equip ON equip.[Name] = pitems.[Name]
		--LEFT JOIN IHDelays2.CAT.DelayCodes dc ON dc.id = d.DelayCodeId
		--LEFT JOIN IHDelays2.CAT.DelayCategories dCat ON dCat.Id = dc.CategoryId
		--WHERE d.Deleted = 0
		--	AND children.Id IS NULL
		--	AND d.[Start] <= @endUtc
		--	AND d.[End] >= @StartUtc
		--	AND d.ShiftId IS NOT NULL
		--	AND DATEDIFF(MINUTE, d.[Start], d.[End]) >= 3
		--ORDER BY equip.[Name] ASC
		--	,IH.ToLocal(d.Start, NULL) ASC

	

		------------------------------------------
		-- Table |6| -- TEMPERATURES AND RESIDUALS
		------------------------------------------
		SELECT 
			HeatName
			,EAFName
			,EAFLastTemperature
			,EAFLastO2
			,Cu
			,Sn
			,Cr
			,Mo
			,P
			,Zn
			,[EAFAimTemperature] 
			,[EAFAimO2]		
		FROM #Records 
		ORDER BY [Start] ASC
	
		-----------------------------------
		-- Table |7| -- BUCKET FLUXES EAF A
		-----------------------------------

		SELECT 
			HeatName
			,EAFName
			,Carbon
			,HiCal
			,Dolo = Dolomite + Dolomite2
			,InjCarbon = InjectedC
			,InjDolo = InjectedLime
			,TotalCarbon = InjectedC + Carbon
			,TotalDolo = InjectedLime + Dolomite + Dolomite2
		FROM #records
		WHERE EAFName = 'EAF A'

		-----------------------------------
		-- Table |8| -- BUCKET FLUXES EAF B
		-----------------------------------

		SELECT
			HeatName
			,EAFName
			,Carbon
			,HiCal
			,Dolo = Dolomite + Dolomite2
			,InjCarbon = InjectedC
			,InjDolo = InjectedLime
			,TotalCarbon = InjectedC + Carbon
			,TotalDolo = InjectedLime + Dolomite + Dolomite2
		FROM #records
		WHERE EAFName = 'EAF B'


			
		-----------------------------------
		-- Table |9| -- INJECTION FLUXES AND KHW / Ton EAF A
		-----------------------------------

		SELECT 
			r.HeatName
			,r.EAFName
			,Carbon = r.InjectedC
			,Dolo = r.InjectedLime 			
			,[SpecificEnergy] = r.SpecificEnergy
		FROM #records r
		WHERE EAFName = 'EAF A'

		-----------------------------------
		-- Table |10| -- INJECTION FLUXES AND KHW / Ton EAF B 
		-----------------------------------

		SELECT 
			r.HeatName
			,r.EAFName
			,Carbon = r.InjectedC
			,Dolo = r.InjectedLime 
			,[SpecificEnergy] = r.SpecificEnergy
		FROM #records r
		WHERE EAFName = 'EAF B'

		--------------------------------
		-- Table |11| -- 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

			,FeOMax = (select top 1 k.[Value] from cat.GradeGroupAttributes k where k.Code = 'SLAG_FeO_MAX_LIMIT'  and k.gradeGroupId =gradegroup.Id )	
			,FeOMin = (select top 1 k.[Value] from cat.GradeGroupAttributes k where k.Code = 'SLAG_FeO_MIN_LIMIT'  and k.gradeGroupId =gradegroup.Id )
			,MgOMax = (select top 1 k.[Value] from cat.GradeGroupAttributes k where k.Code = 'SLAG_MgO_MAX_LIMIT'  and k.gradeGroupId =gradegroup.Id )						
			,MgOMin = (select top 1 k.[Value] from cat.GradeGroupAttributes k where k.Code = 'SLAG_MgO_MIN_LIMIT'  and k.gradeGroupId =gradegroup.Id )					
			,B3Max=	  (select top 1 k.[Value] from cat.GradeGroupAttributes k where k.Code = 'SLAG_B3_MAX_LIMIT'   and k.gradeGroupId 	 =gradegroup.Id )		
			,B3Min=	  (select top 1 k.[Value] from cat.GradeGroupAttributes k where k.Code = 'SLAG_B3_MIN_LIMIT'   and k.gradeGroupId 	 =gradegroup.Id )
			
			--,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
	into #tempSlagChemistries
	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
		left join cat.grades grades on grades.Id = heat.GradeId
		left join cat.GradeGroups gradegroup on gradegroup.Id = grades.GradeGroupId
		Cross apply SCRAP.GetFluxesChargeByHeatId(ep.HeatId) c
		WHERE record.TypeCode = 'EAFSL'  and ep.Id IN (SELECT Id From #records)
		ORDER BY  left(Heat.[Name],1) ASC, record.[Timestamp] desc

	select 
	* 
	,B3Status = case when B3Max is not null and B3ratio is not null and B3ratio >= B3Max then 'DANGER' 
						when B3Min is not null and B3ratio is not null and B3ratio <= B3Min then 'WARNING' 
						else 'NORMAL' end
	,MgOStatus = case when MgOMax is not null and MgO is not null and MgO >= MgOMax then 'DANGER'
						when MgOMin is not null and MgO is not null and MgO <= MgOMin then 'DANGER' 
					else 'NORMAL' end 
	,FeOStatus = case when FeOMax is not null and FeO is not null and FeO >= FeOMax then 'DANGER' 
					else 'NORMAL' end 
	,RefractoryWearStatus = case when RefractoryWear is not null and RefractoryWear < -300.0 then 'DANGER' else 'NORMAL' end
	from #tempSlagChemistries
	--------------------------------
	-- Table |12| -- Baghouse
	---------------------------------

	--select * from [EAF].[GetBaghouseFilteredData](@ShiftId,@ProductionDate,@timezoneCode,5)
	--order by [datetime] asc

	select 
		*
		,FromHeatName = (select h.[Name] from mes.heats h where h.id = p.FromHeatId)
		,ToHeatName = (select h.[Name] from mes.heats h where h.id = (select z.heatId from eaf.processes z where z.Id = p.EAFProcessId))
	from eaf.Pourbacks 	p
	where EAFProcessId in (select distinct(Id) from #records)



	----------------------
	---- Yield Plot ------
	----------------------
	select 
		ProductionDate = cast(ProductionDate as varchar)
		,ShiftId = ShiftID
		,EAFAYield = cast(EAFAYield as decimal(10,2))
		,EAFBYield = cast(EAFBYield as decimal(10,2))
	
	from mes.ShiftPerformance
	where ProductionDate between cast(dateadd(DAY,-30,GETUTCDATE()) as date) and cast(GETUTCDATE() as date)
	and (EAFAYield is not null and EAFBYield is not null)	
	order by ProductionDate,ShiftID
	
	---------------------------
	---- Spray Effectiveness and Bypass -----
	---------------------------
	select 
		EAFASprayRingEffectiveness, -- = '-',
		EAFBSprayRingEffectiveness, -- = '-',
		EAFABypassBreakerUtilization, -- = '-',
		EAFAConditionsBypassBreakerMaintained -- = '-'
	 from mes.ShiftPerformance where ProductionDate = @ProductionDate and ShiftId = (case when @ShiftId = 1 then 'D' else 'N' end)



	--------------------------------
	-- Table |13| -- SRDCategories
	---------------------------------
	select * from IHDelays2.CAT.SRDCategories order by [Description] ASC
    
	--------------------------------
	-- Table |14| -- Recipes Averages
	---------------------------------

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

	DECLARE @SQLQuery1 NVARCHAR(MAX)

	select @colNameList = 'Cu,P,Cr,Ni,Pb,Mo,Sn'

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

		select
		[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 in (SELECT HeatId from #records)
		and c.SampleIdCode in ('FINAL 1', 'FINAL 2')
		order by [Timestamp] desc

		insert into @SampleByElementsAndLimits
		select
		r.[HeatName]
		,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.[HeatName]

		
		SET @SQLQuery1 ='
		select [TypeCode], [SampleIdCode],[HeatName], Code, [Value] = cast([Value] as varchar)
		from (select [TypeCode], [SampleIdCode],[HeatName],'+@colNameList+'
		from #chemistriesByHeat ) p
		UNPIVOT (
		[Value] for Code IN
		('+@colNameList+')
		) AS unpvt2 '


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

	insert into @unPivotChemistriesByHeat(TypeCode,SampleIdCode, [HeatName],[Code], [Value])
	EXEC(@SQLQuery1)
	declare @finalTable table (TypeCode varchar(max),SampleIdCode varchar(max), [HeatName] varchar(max),[Code] varchar(50), [Value] varchar(50),[Min] float, [Max] float,[isOutOfLowRange] bit,[isOutOfHighRange] bit, isOutOfRange bit, recipeId int)


	insert into @finalTable
	select
	TypeCode
	,SampleIdCode
	,HeatName
	,p.[Code]
	,[Value]
	,[Min] = (select top 1 z.[MIN] from @SampleByElementsAndLimits z where z.[Code]  = p.[Code] and z.HeatName = p.HeatName)
	,[Max] = (select top 1 z.[MAX] from @SampleByElementsAndLimits z where z.[Code]  = p.[Code] and z.HeatName = p.HeatName)
	--,[isOutOfLowRange] = (
	--case when (select top 1 z.[Min] from @SampleByElementsAndLimits z where z.[Code]  = p.[Code] ) is null then 0
	--when p.[Value] < (select top 1 z.[Min] from @SampleByElementsAndLimits z where z.[Code]  = p.[Code] ) then 1
	--else 0
	--end
	--  )
	,[isOutOfLowRange] = 0
	,[isOutOfHighRange] = (
	case when (select top 1 z.[Max] from @SampleByElementsAndLimits z where z.[Code]  = p.[Code] and z.HeatName = p.HeatName ) is null then 0
	when p.[Value] > (select top 1 z.[Max] from @SampleByElementsAndLimits z where z.[Code]  = p.[Code] and z.HeatName = p.HeatName) then 1
	else 0
	end
	  )
	,0
	,(SELECT TOP 1 ScrapRecipeId from #records rec where rec.HeatName = p.HeatName) 
	from @unPivotChemistriesByHeat p

	
	update @finalTable
	set isOutOfRange = case when  isOutOfHighRange = 1 or   isOutOfLowRange = 1 then 1 else 0 end


	select HeatName, Code, recipeId,CAST(MAX(CAST(isOutOfRange as INT)) AS BIT) as isOutOfRange 
	into #groupedTable
	from @finalTable group by HeatName, Code, recipeId
	

	select 
	Code, 
	SUM(CAST(isOutOfRange as INT)) as SummedCode
	, recipeId
	into #summedTable
	from #groupedTable gt
	group by Code, recipeId


	select DISTINCT rec.ScrapRecipeId
	,Recipe = (select MAX(Recipe) from #recipeResults rr where rr.ScrapRecipeId = rec.ScrapRecipeId)
	,HeatsProduced = COUNT(*) over (partition by rec.ScrapRecipeId)
	,Yield = cast((sum(SteelWeight) over (partition by rec.ScrapRecipeId) / NULLIF(sum(ScrapWeight) over (partition by rec.ScrapRecipeId), 0) * 2000.0) * 100.0 as decimal(10,2))
	--,PMiss =  cast( sum(rec.P) over (partition by rec.ScrapRecipeId) as decimal(10,2))
	,PMiss =  (select SummedCode from #summedTable st where st.Code = 'P' and st.recipeId = rec.ScrapRecipeId)
	,CuMiss =  (select SummedCode from #summedTable st where st.Code = 'Cu' and st.recipeId = rec.ScrapRecipeId)
	,CrMiss =  (select SummedCode from #summedTable st where st.Code = 'Cr' and st.recipeId = rec.ScrapRecipeId)
	,NiMiss = (select SummedCode from #summedTable st where st.Code = 'Ni' and st.recipeId = rec.ScrapRecipeId)
	,PbMiss = (select SummedCode from #summedTable st where st.Code = 'Pb' and st.recipeId = rec.ScrapRecipeId)
	,MoMiss = (select SummedCode from #summedTable st where st.Code = 'Mo' and st.recipeId = rec.ScrapRecipeId)
	,SnMiss =  (select SummedCode from #summedTable st where st.Code = 'Sn' and st.recipeId = rec.ScrapRecipeId)
	from #records rec
	order by Recipe asc



	--------------------------------
	-- Table |15| -- Manual Temp Counters
	---------------------------------
	--select MAX(r.HeatName), ManualTemp = count(*) from #records r

	select FurnaceCode = rec.EAFName, rec.[HeatName], ManualTemp = (select count(*) from EAF.Temperatures temp where temp.ProcessId = rec.Id and temp.isManual = 1) from #records rec

	--------------------------------
	-- Table |16| -- Manual Temp Counters
	---------------------------------

	select FurnaceCode = rec.EAFName ,HeatName = rec.[HeatName], HeatsOnTapHole = rec.HeatsOnTapHole, NonFreeOpenDuration = rec.NonFreeOpenDuration from #records rec

	drop table #chemistriesByHeat
	drop table #summedTable
	drop table #groupedTable
	drop table #recipeResults
	drop table #records
	PRINT @finishEndMSG
	RETURN 0
END
