INSERT INTO [FrontEnd].[Routes]
           ([Route]
           ,[ModuleName]
           ,[ModulePath]
           ,[RequiredRole]
       )
     VALUES
           (
		   '!/slag-chemistry-samples-report-eaf(/:from)(/:to)(/)'
           , 'slag-chemistry-samples-report-eaf'
		   , 'custom-screens/slag-chemistry-samples-report-eaf/'
           , 'lab_slagchemistrysamplesreport_read'
           
		   )



INSERT INTO [FrontEnd].[Menu]
           ([Parent]
           ,[Name]
           ,[RouteID]
           ,[SortOrder]
           ,[Enabled]
           ,[Visible]
           ,[RequiredRole]
           ,[Group]
          )
     VALUES
           (
		   2895
		   ,'EAF Slag Chimestries Report'
           ,@@IDENTITY
           ,1
           ,1
           ,1
           ,'lab_slagchemistrysamplesreport_read'
           ,NULL
         )
GO



USE IHBoxSystem

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

Go


/*-----------------------------------------------------------------------------------------------------------------------------------------*/
update frontend.[Routes] 
set [Route] = '!/slag-chemistry-samples-report-eaf(/:heatName)(/:from)(/:to)(/)'
where ModuleName = 'slag-chemistry-samples-report-eaf' 
go

/*----------------------------------------------------------------------------------------------------------------------------------------*/
USE [MES]
GO
/****** Object:  StoredProcedure [LAB].[EAFGetSlagChemistrySamplesReport]    Script Date: 5 ago. 2020 15:07:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAB].[EAFGetSlagChemistrySamplesReport]
	@from DATETIME = NULL,
	@to DATETIME = NULL,
	@heatName VARCHAR(50) = NULL,
	@excel CHAR(1) = 0,
	@furnaceCode CHAR(1)= 1,
	@timeZoneCode CHAR(10) = NULL,
	@currentUser VARCHAR(MAX) = NULL,
	@fromRow INT = NULL, -- From what row to start fetching from
	@toRow INT = NULL, -- To row (@toRow row num not included in result data)
	@rowsToFetch INT = NULL -- Amount of rows to fetch
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), '')
	+ ', @fromRow: ' + ISNULL(CAST(@fromRow AS VARCHAR), '')
	+ ', @toRow: ' + ISNULL(CAST(@toRow AS VARCHAR), '')
	+ ', @rowsToFetch: ' + ISNULL(CAST(@rowsToFetch AS VARCHAR), '')
	+ ', @furnaceCode: ' + ISNULL(CAST(@furnaceCode AS VARCHAR), '')
	+ ', @excel: ' + ISNULL(CAST(@excel AS VARCHAR), '')
	
	
	DECLARE @now DATETIME,
	@fromUTC DATETIME,
	@toUTC DATETIME,
	@heatId INT
	
	SET @now = GETUTCDATE()
	
	-----------------------
	-- Parameters default
	-----------------------
	print @heatName
	if @heatName is not null set @heatId = (select top 1 Id from mes.Heats where [Name] = @heatName )
	print @heatId
	if @timeZoneCode is null set @timeZoneCode = 'EDTIND'

	if @from is null set @from = dateadd(day,-1,GETUTCDATE())
	if @to is null set @to = GETUTCDATE()
	
	set @fromUTC = IH.ToUTC(@from, @timeZoneCode) 
	set @toUTC = IH.ToUTC(@to, @timeZoneCode)
	
	if @rowsToFetch IS NULL set @rowsToFetch = -1
	
	if @rowsToFetch > 0
	begin
		if @fromRow is null set @fromRow = 1
		if @toRow is null set @toRow = @fromRow + @rowsToFetch
	end
	-----------------------
	-- Begin Query
	-----------------------
	SELECT 
		Heat.[Name],
	
		TimestampString = CONVERT(VARCHAR, IH.ToLocal(record.Timestamp, @timeZoneCode), 121),
		record.*,
		HeatName = heat.Name,
		TypeName = cst.Name,
		B3Max = CAST(17000 AS FLOAT),
		B3Min = CAST(-13000 AS FLOAT),
		FeOMax = CAST(30.0000 AS FLOAT),
		FeOMin = CAST(10.0000 AS FLOAT),
		MgOMax = CAST(16.0000 AS FLOAT),
		MgOMin = CAST(11.0000 AS FLOAT),
		RefWearLimit = CAST(0.0000 AS FLOAT),
		TotalMin = CAST(90.0000 AS FLOAT),
		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  


			INTO #TempTable
			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 
			record.TypeCode = 'EAFSL'
			and (
				(@heatId is not null and heat.Id = @heatId) 
				or 
				(@heatId is null and record.[Timestamp] between @fromUTC and @toUTC)
			 )
			


	

	
	IF (@excel = 0)
	BEGIN
		IF (@furnaceCode = 1)
		BEGIN

			SELECT
				tt.*,
				RowNum = ROW_NUMBER() OVER (ORDER BY tt.[Timestamp] DESC)
			INTO #EAFATable 
			FROM #TempTable tt
			WHERE tt.[FurnaceCode] = @furnaceCode

			SELECT
			*
			FROM #EAFATable tt
			WHERE
			(@rowsToFetch = -1 OR (tt.RowNum BETWEEN @fromRow AND  @toRow))
			ORDER BY tt.[Timestamp] DESC	
		END
		ELSE IF (@furnaceCode = 2)
		BEGIN
			
			SELECT
			tt.*,
			RowNum = ROW_NUMBER() OVER (ORDER BY tt.[Timestamp] DESC)
			INTO #EAFBTable 
			FROM #TempTable tt
			WHERE tt.[FurnaceCode] = @furnaceCode


			SELECT
			*
			FROM #EAFBTable tt
			WHERE
			(@rowsToFetch = -1 OR (tt.RowNum BETWEEN @fromRow AND  @toRow))
			ORDER BY tt.[Timestamp] DESC
		END
		
		-----------------------
		-- Navigate Buttons
		-----------------------
		
		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]= '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 5 [Text]= 'LAB', 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



	END
	ELSE
	BEGIN
	SELECT
		*
		FROM #TempTable tt
		ORDER BY tt.[Timestamp] DESC
	END
	
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG
	
	RETURN 0
END
GO


 /*---------------------------------------------------------------17 October 2020---------------------------------------------------------------------------*/

 USE IHBoxSystem

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

Go


USE [MES]
GO
/****** Object:  StoredProcedure [LAB].[EAFGetSlagChemistrySamplesReport]    Script Date: 17 oct. 2020 15:51:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [LAB].[EAFGetSlagChemistrySamplesReportPickers]
	
AS
BEGIN
	-----------------------
	-- 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%'
	
	-----------------------
	-- Finish procedure
	-----------------------
	RETURN 0
END

GO

USE [MES]
GO
/****** Object:  StoredProcedure [LAB].[EAFGetSlagChemistrySamplesReport]    Script Date: 17 oct. 2020 16:25:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAB].[EAFGetSlagChemistrySamplesReport]
	@from DATETIME = NULL,
	@to DATETIME = NULL,
	@heatName VARCHAR(50) = NULL,
	@excel CHAR(1) = 0,
	@furnaceCode CHAR(1)= 1,
	@timeZoneCode CHAR(10) = NULL,
	@currentUser VARCHAR(MAX) = NULL,
	@fromRow INT = NULL, -- From what row to start fetching from
	@toRow INT = NULL, -- To row (@toRow row num not included in result data)
	@rowsToFetch INT = NULL -- Amount of rows to fetch
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), '')
	+ ', @fromRow: ' + ISNULL(CAST(@fromRow AS VARCHAR), '')
	+ ', @toRow: ' + ISNULL(CAST(@toRow AS VARCHAR), '')
	+ ', @rowsToFetch: ' + ISNULL(CAST(@rowsToFetch AS VARCHAR), '')
	+ ', @furnaceCode: ' + ISNULL(CAST(@furnaceCode AS VARCHAR), '')
	+ ', @excel: ' + ISNULL(CAST(@excel AS VARCHAR), '')
	
	
	DECLARE @now DATETIME,
	@fromUTC DATETIME,
	@toUTC DATETIME,
	@heatId INT = null,
	@calculatedFurnaceCode INT = null
	SET @now = GETUTCDATE()
	
	-----------------------
	-- Parameters default
	-----------------------
	if @heatName is not null set @heatId = (select top 1 Id from mes.Heats where [Name] = @heatName )

	if @heatId is not null
	begin
		set @from =	dateadd(day,-1,(select top 1 [Timestamp] from LAB.SlagChemistrySamples where HeatId = @heatId))
		set @to =	dateadd(day,1,(select top 1 [Timestamp] from LAB.SlagChemistrySamples where HeatId = @heatId))
		set @calculatedFurnaceCode = (select top 1 ep.FurnaceCode from LAB.SlagChemistrySamples l
								join EAF.Processes ep on l.HeatId = ep.HeatId 
								where l.HeatId = @heatId
							)
	end
	if @timeZoneCode is null set @timeZoneCode = 'EDTIND'

	if @from is null set @from = dateadd(day,-1,GETUTCDATE())
	if @to is null set @to = GETUTCDATE()
	
	set @fromUTC = IH.ToUTC(@from, @timeZoneCode) 
	set @toUTC = IH.ToUTC(@to, @timeZoneCode)
	
	if @rowsToFetch IS NULL set @rowsToFetch = -1
	
	if @rowsToFetch > 0
	begin
		if @fromRow is null set @fromRow = 1
		if @toRow is null set @toRow = @fromRow + @rowsToFetch
	end

	
	-----------------------
	-- 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
	-----------------------
		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' 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 #TempTable
			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 
			record.[Timestamp] between @fromUTC and @toUTC
			and
			record.TypeCode = 'EAFSL' 
			and (
					@excel = 1 or
					(
					(@calculatedFurnaceCode is null and	@furnaceCode = ep.FurnaceCode) 
					or
					(@calculatedFurnaceCode is not null and @calculatedFurnaceCode = ep.FurnaceCode)
					)
			)
			


	

	
	IF (@excel = 0)
	BEGIN
		IF (@furnaceCode = 1)
		BEGIN

			SELECT
				tt.*,
				RowNum = ROW_NUMBER() OVER (ORDER BY tt.[Timestamp] DESC)
			INTO #EAFATable 
			FROM #TempTable tt
			WHERE tt.[FurnaceCode] = @furnaceCode

			SELECT
			*
			FROM #EAFATable tt
			WHERE
			(@rowsToFetch = -1 OR (tt.RowNum BETWEEN @fromRow AND  @toRow))
			ORDER BY tt.[Timestamp] DESC	
		END
		ELSE IF (@furnaceCode = 2)
		BEGIN
			
			SELECT
			tt.*,
			RowNum = ROW_NUMBER() OVER (ORDER BY tt.[Timestamp] DESC)
			INTO #EAFBTable 
			FROM #TempTable tt
			WHERE tt.[FurnaceCode] = @furnaceCode


			SELECT
			*
			FROM #EAFBTable tt
			WHERE
			(@rowsToFetch = -1 OR (tt.RowNum BETWEEN @fromRow AND  @toRow))
			ORDER BY tt.[Timestamp] DESC
		END
		
		-----------------------
		-- Navigate Buttons
		-----------------------
		
		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]= '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


	END
	ELSE
	BEGIN
	SELECT
		*
		FROM #TempTable tt
		ORDER BY tt.[Timestamp] DESC
	END
	
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG
	
	RETURN 0
END


GO