--

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

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


---- Change route:
	update top(1) FrontEnd.Routes
	set ModulePath = 'pages/eaf/eaf-production/',
		[Route] = '!/eaf-production(/:start)(/:end)(/:furnaceCode)(/:shiftId)(/:crewId)(/)'
	where ModulePath = 'custom-screens/eaf-production/'



	update top(1) FrontEnd.[Routes]
		set [Route] = '!/eaf-production(/:start)(/:end)(/:furnaceCode)(/:shiftId)(/:crewId)(/)'
		where Id = 2015
GO 

-- New SP:
USE [MES]
GO
ALTER PROCEDURE [EAF].[GetProcessesReport]
    @Start DATE = NULL,
    @End DATE = NULL,
    @FurnaceCode INT = NULL,
	@ShiftId INT = NULL,
	@CrewId char(1) = NULL
AS
BEGIN
    SET NOCOUNT ON;
	-- procedure initialization
	DECLARE @procName VARCHAR(300),
            @finishEndMSG VARCHAR(300);

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

    PRINT @procName + ' Parameters:'
        + '@Start: ' + ISNULL(CAST(@Start AS VARCHAR), '')
        + '@End: ' + ISNULL(CAST(@End AS VARCHAR), '')
        + ', @furnaceCode: ' + ISNULL(CAST(@furnaceCode AS VARCHAR), '')
	
	-- Parameters validation and default values
	DECLARE @StartUtc DATETIME,
			@EndUtc DATETIME,
			@StartWithTime DATETIME,
			@EndWithTime DATETIME;

	-- If @End is not provided, set it to the current date
	IF @End IS NULL 
		SET @End = CAST(GETDATE() AS DATE);

	-- If @Start is not provided, set it to one day before @End
	IF @Start IS NULL 
		SET @Start = DATEADD(DAY, -1, @End);

	-- Convert DATE to DATETIME and add time components
	SET @StartWithTime = CAST(@Start AS DATETIME);  -- This implicitly sets the time to 00:00:00
	SET @EndWithTime = DATEADD(SECOND, -1, DATEADD(DAY, 1, CAST(@End AS DATETIME)));  -- This sets the time to 23:59:59

	-- Convert to UTC
	SET @StartUtc = IH.ToUTC(@StartWithTime, NULL);
	SET @EndUtc = IH.ToUTC(@EndWithTime, NULL);

	--- Variables
	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] = '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');

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


	SELECT 
		process.Id,
		process.FurnaceCode,
		process.HeatId,
		process.[Start], 
		process.StartTap, 
		process.[End], 
		process.EndTap, 
		process.PowerOff, 
		process.ShiftId, 
		process.CrewId,
		heat.LadleId,			
		HeatName = heat.[Name],
		Energy = process.Energy,
		PowerOn = process.PowerOn,
		SteelWeight = heat.SteelWeight,		

		PrevTap = (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)
		
		,process.PowerProgram
		,process.BurnerProgram
		,ChargeWeight = (select isnull(sum(ScrapWeight),0) from EAF.Buckets z where z.ProcessId = process.id) 
		,Charges = (select count(*) from EAF.Buckets z where z.ProcessId = process.id) 
			
		,Argon = process.Argon		
		,CO2 = CO2
		,kwhperton  = KwhTon
		,Ncc1 = process.NCC1
		,Ncc2 = process.NCC2
		,Ncc3 = process.NCC3
		,CaveIn1 = process.CaveIn1
		,CaveIn2 = process.CaveIn2
		,CaveIn3 = process.CaveIn3
	INTO #records
	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 IHDelays2.cat.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
	
		
	WHERE
	process.[Start] between @StartUtc AND @EndUtc
	AND (@FurnaceCode is null or process.FurnaceCode = @FurnaceCode)
	AND (@ShiftId is null or process.ShiftId = @ShiftId)
	AND (@CrewId is null or process.CrewId = @CrewId)

	SELECT 
		record.*,
		KwhTon =  isnull(record.kwhperton, record.Energy * 1000.0 / nullif(record.ChargeWeight/2000.0,0)),
		MwhMin =  record.Energy / nullif(record.PowerOn,0) *60,
		TapToTap = CASE WHEN (record.EndTap IS NOT NULL) THEN
							ISNULL(DATEDIFF(
								SECOND,
								record.PrevTap,
								record.EndTap
							), 0)
						ELSE
							0
					END,
		
		TapToTapString =  CONVERT(VARCHAR, DATEADD(second,CASE WHEN (record.EndTap IS NOT NULL) THEN
							ISNULL(DATEDIFF(
								SECOND,
								record.PrevTap,
								record.EndTap
							), 0)
						ELSE
							0
					END,0),108),

		TapTemp = EAF.GetTapTemperature(record.FurnaceCode) 
	
	INTO #records2
	FROM #records record



	SELECT 
		Id = record.Id,
		BucketsWeight = ChargeWeight,
		ConsumptionsInjectedCarbon = consumptions.InjectedCarbon,
		ConsumptionsCarbon = consumptions.InjectedCarbon,
		ConsumptionsInjectedLime = consumptions.InjectedLime,
		ConsumptionsNaturalGas = consumptions.NaturalGas,
		ConsumptionsOxygen = consumptions.Oxygen,
		ConsumptionsO2SS = consumptions.O2SS,
		FluxesHiCal = ISNULL((Select sum([Weight]) from Scrap.Charges c left join Scrap.ChargeFluxes cf on c.id = cf.ChargeId where c.HeatId = record.HeatId and FluxMaterialId=@HiCal_MaterialId),0),
		FluxesCarbon = ISNULL((Select sum([Weight]) from Scrap.Charges c left join Scrap.ChargeFluxes cf on c.id = cf.ChargeId where c.HeatId = record.HeatId and FluxMaterialId=@Carbon_MaterialId),0),
		FluxesProSlag = ISNULL((Select sum([Weight]) from Scrap.Charges c left join Scrap.ChargeFluxes cf on c.id = cf.ChargeId where c.HeatId = record.HeatId and FluxMaterialId=@ProSlag_MaterialId),0),
		FluxesDolomite = ISNULL((Select sum([Weight]) from Scrap.Charges c left join Scrap.ChargeFluxes cf on c.id = cf.ChargeId where c.HeatId = record.HeatId and FluxMaterialId=@Dolo_MaterialId),0),
		ScrapRecipe = (
			SELECT TOP 1 recipes.[Name]
			FROM SCRAP.Charges charges 
			JOIN SCRAP.ScrapRecipes recipes ON recipes.Id = charges.ScrapRecipeId
			WHERE charges.HeatId = record.HeatId
		),
		LatestOxygen = (SELECT TOP 1 Oxygen FROM EAF.Temperatures temp WHERE temp.ProcessId = record.Id and oxygen>0 ORDER BY Timestamp DESC),
		LastTemperature = (SELECT TOP 1 Temperature FROM EAF.Temperatures temp WHERE temp.ProcessId = record.Id and Temperature>0 ORDER BY Timestamp DESC),
		-- In percentage
		Yield =  CASE WHEN ((ChargeWeight IS NOT NULL) AND (ChargeWeight<> 0)) THEN
						record.SteelWeight * 2000
						/ ChargeWeight
						* 100
					ELSE
						NULL
				END
	INTO #records3
	FROM #records record
	LEFT JOIN (SELECT
				ProcessId,
				InjectedCarbon = SUM(CASE WHEN (Code IN ('CARBON', 'LANCE_CARBON', 'SIDEWALL_CARBON')) THEN [Counter] ELSE 0 END),
				InjectedLime = SUM(CASE WHEN (Code IN ('INJECTED_LIME')) THEN [Counter] ELSE 0 END),
				NaturalGas = SUM(CASE WHEN (Code IN ('NATURAL_GAS_BURNER_1', 'NATURAL_GAS_BURNER_2', 'NATURAL_GAS_BURNER_3', 'NATURAL_GAS_BURNER_4', 'NATURAL_GAS_BURNER_5')) THEN [Counter] ELSE 0 END),
				Oxygen = SUM(CASE WHEN (Code IN ('LANCE_OXYGEN', 'OXYGEN_BURNER_1', 'OXYGEN_BURNER_2', 'OXYGEN_BURNER_3', 'OXYGEN_BURNER_4', 'OXYGEN_BURNER_5')) THEN [Counter] ELSE 0 END),
				O2SS = SUM(CASE WHEN (Code IN ('O2SS_BURNER_1', 'O2SS_BURNER_2', 'O2SS_BURNER_3', 'O2SS_BURNER_4', 'O2SS_BURNER_5')) THEN [Counter] ELSE 0 END)
			FROM EAF.Consumptions
			WHERE
				ProcessId IN (SELECT Id FROM #records)
			GROUP BY
				ProcessId) AS consumptions ON consumptions.ProcessId = record.Id			
		


	   SELECT 
        heat.[Name] AS Heat,
		process.FurnaceCode ,
		ladle.[Name] AS Ladle,
        IH.ToLocal(process.[Start], NULL) AS [Start],
        IH.ToLocal(process.[StartTap], NULL) AS [StartTap],
        IH.ToLocal(process.[End], NULL) AS [End],
        process.CrewId AS Crew,
        shifts.[Name] AS [Shift],
        grade.[Name] AS Grade,
        r3.ScrapRecipe AS [ScrapRecipe],
        record.Charges,
        r3.BucketsWeight AS [ScrapWeight],
        record.SteelWeight AS [SteelWeight],
        r3.FluxesHiCal AS HiCal,
        r3.FluxesDolomite AS Dolo,
        r3.FluxesCarbon AS Carbon,
        r3.Yield,
        record.PowerProgram AS [PwrProg],
        record.TapToTap AS TTT,
        record.PowerOn AS [On],
        record.PowerOff AS [Off],
        record.Energy AS MWH,
        record.KwhTon AS KWHtn,
        record.MwhMin AS MWHmin,
        record.BurnerProgram AS [BurnP],
        r3.ConsumptionsNaturalGas AS [NatGas],
        r3.ConsumptionsOxygen AS O2,
        r3.ConsumptionsO2SS AS O2SS,
        r3.LastTemperature AS [LastT],
        r3.LatestOxygen AS [LastO2],   
		r3.ConsumptionsInjectedCarbon as [SouthInjectedCarbon],
		r3.ConsumptionsInjectedLime as [NorthInjectedCarbon],
		record.Ncc1,
		record.Ncc2, 
		record.Ncc3, 
		record.CaveIn1, 
		record.CaveIn2, 
		record.CaveIn3, 
        process.Argon,
        process.CO2,
		PourbackHeatName = (
				select top 1 h.[name] from eaf.Pourbacks z
				left join mes.heats h on h.Id = z.FromHeatId
				where z.EAFProcessId = process.Id
			),
		SlagIndex = process.SlagIndex,
		FurnacePokes  = process.FurnacePokes,
		BadProbes     = process.BadProbes,
		TempToTapTime = process.TempToTapTime

    FROM EAF.Processes process
    JOIN MES.Heats heat ON heat.Id = process.HeatId
    LEFT JOIN IHDelays2.cat.shifts shifts ON shifts.Id = process.ShiftId
    LEFT JOIN CAT.Grades grade ON grade.Id = process.GradeId
    LEFT JOIN CAT.Ladles ladle ON ladle.Id = heat.LadleId
    JOIN #records2 record ON record.Id = process.Id
    JOIN #records3 r3 ON r3.Id = process.Id
	order by [Start] desc

end

go