USE [IHBoxSystem]
GO


IF DATABASE_PRINCIPAL_ID('shiftreport_readWrite') IS NULL
BEGIN
	CREATE ROLE shiftreport_readWrite;
end
go

delete from [FrontEnd].[Routes] where [ModuleName]='shift-report'
delete from [FrontEnd].[Menu] where [Name]='shift-report'



INSERT INTO [FrontEnd].[Routes]
           ([Route]
           ,[ModuleName]
           ,[ModulePath]
           ,[RequiredRole]
       )
     VALUES
           (
		   '!/shift-report(/:productionDate)(/:shiftId)(/:crewId)'
           , 'shift-report'
		   , 'custom-screens/shift-report/'
           , 'shiftreport_readWrite'
           
		   )



INSERT INTO [FrontEnd].[Menu]
           ([Parent]
           ,[Name]
           ,[RouteID]
           ,[SortOrder]
           ,[Enabled]
           ,[Visible]
           ,[RequiredRole]
           ,[Group]
          )
     VALUES
           (
		   56
		   ,'Shift Report'
           ,@@IDENTITY
           ,1
           ,1
           ,1
           ,'shiftreport_readWrite'
           ,NULL
         )
GO

USE IHBoxSystem

EXEC sp_addrolemember 'shiftreport_readWrite', 'admin'
EXEC sp_addrolemember 'shiftreport_readWrite', 'test'

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

Go

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

Go


USE [MES]
GO
/****** Object:  StoredProcedure [EAF].[GetShiftReport]    Script Date: 31 oct. 2019 14:10:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [EAF].[GetShiftReport]
@ProductionDate DATETIME = NULL,
@ShiftId CHAR(1) = NULL
AS
BEGIN
-----------------------A
-- 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:'
+ '@ProductionDate: ' + ISNULL(CAST(@ProductionDate AS VARCHAR), '')
+ ', @ShiftId: ' + ISNULL(CAST(@ShiftId AS VARCHAR), '')


-----------------------
-- Variables
-----------------------
DECLARE @startUtc DATETIME,
@endUtc DATETIME,
@timezoneCode char(10)


-----------------------
-- Parameters default
-----------------------
IF (@ProductionDate IS NULL)
SET @ProductionDate = getdate();
IF (@ShiftId IS NULL)
SET @ShiftId = 1;


-----------------------
-- Set variables
-----------------------

SET @timeZoneCode = 'EDTIND'
SELECT @startUtc = IH.ToUTC(dateadd(hour,9 + 12 *(@ShiftID-1) ,@ProductionDate) ,@timezoneCode)
set @endUtc = dateadd(hour,12, @startUtc)

print @StartUTC print @EndUTC

-----------------------
-- Begin Query
-----------------------

-----------------------
-- EAF Processes List
-----------------------
SELECT  
[Id] = process.Id,
[HeatId]= process.HeatId,
[RegulationModeId] = CASE WHEN (process.RegulationModeId IS NOT NULL) THEN
process.RegulationModeId
ELSE
CAT.GetRegulationModeIdByCode(EAF.GetRegulationModeOnline(process.FurnaceCode))
END,
[EAFName] = furnace.Name, -- EAF A OR EAF B
[HeatName] = heat.Name,
[ProcessId] = process.Id,
[ShiftId] = process.ShiftId,
[ShiftName] = shifts.[Name],
[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),
   [RowNum] = ROW_NUMBER() OVER (ORDER BY process.Start DESC),
[StartTap] = IH.ToLocal(process.StartTap, @timeZoneCode),
[EndTap] =IH.ToLocal(process.EndTap, @timeZoneCode),
[SteelWeight] = heat.SteelWeight,
[ScrapWeight] = (select sum(z.ScrapWeight) from EAF.Buckets z where z.ProcessId = process.id),
[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]),
[InjectedC] = (select sum(z.[Counter]) from EAF.Consumptions z where z.processid = process.id and z.code IN ('CARBON', 'LANCE_CARBON', 'SIDEWALL_CARBON')),

[ElectricEnergy] = process.Energy , --MWH
[SpecificEnergy] = process.Energy  * 1000.0 / nullif(heat.SteelWeight,0), --KwhTon
[ElectricPower] = process.Energy / nullif(process.PowerOn,0) *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] = 0,

/* Fluxes */
[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]  = (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=1),
[Carbon]  = (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=2),
[ProSlag]  = (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=3),
[Dolomite]  = (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=4),
[Dolomite2]  = (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=4), --dave

/* Temperatures */
[EAFLastTemperature] = (select top 1 z.Temperature from EAF.Temperatures z where z.ProcessId = process.id order by z.Id desc),
[LMFFirstTemperature] = (select top 1 z.Temperature from LF.Temperatures z left join LF.Processes zp on z.ProcessId = zp.id  where zp.HeatId=process.heatId order by z.Id asc),

[LMFLastTemperature] = (select top 1 z.Temperature from LF.Temperatures z left join LF.Processes zp on z.ProcessId = zp.id  where zp.HeatId=process.heatId order by z.Id desc), --dave
[EAFLastO2] = (select top 1 z.Oxygen from EAF.Temperatures z where z.ProcessId = process.id order by z.Id desc), --dave
[LMFFirstO2] = (select top 1 z.Oxygen from LF.Temperatures z left join LF.Processes zp on z.ProcessId = zp.id  where zp.HeatId=process.heatId order by z.Id asc), --dave
[LMFLastO2] = (select top 1 z.Oxygen from LF.Temperatures z left join LF.Processes zp on z.ProcessId = zp.id  where zp.HeatId=process.heatId order by z.Id desc), --dave

/* 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), --dave
[P] = (select top 1 P from LAB.ChemistrySamples z where z.HeatId=process.heatid and z.TypeCode='LMFLD'   order by z.[Timestamp] asc), --dave
[Cr] = (select top 1 Cr from LAB.ChemistrySamples z where z.HeatId=process.heatid and z.TypeCode ='LMFLD' order by z.[Timestamp] asc) --dave

INTO #records
FROM EAF.Processes process
JOIN MES.Heats heat ON heat.Id = process.HeatId
LEFT JOIN Delays.Shifts shifts ON shifts.Id = process.ShiftId
LEFT JOIN CAT.RegulationModes regulationMode ON regulationMode.Id = process.RegulationModeId
LEFT JOIN CAT.Furnaces furnace ON furnace.Code = process.FurnaceCode
WHERE
process.ProductionDate = @ProductionDate
and process.ShiftId = @ShiftId

----------------------
-- 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) / sum(ScrapWeight) * 2000.0
 ,TapToTap = avg(r.TapToTap)/60.0
 ,PowerOn = avg(r.PowerOn)/60.0
 ,InjectedC = avg(r.InjectedC)
 ,EAFLastTemperature = avg(EAFLastTemperature)
 ,ElectricEnergy = avg(ElectricEnergy)
 ,SpecificEnergy = sum(ElectricEnergy)/sum(SteelWeight)
 ,ElectricPower = sum(ElectricEnergy)/sum(r.PowerOn)
 ,SmartARC = avg(SmartArc)
 

 from #records r
 group by r.EAFName
 with rollup


----------------------
-- Scrap Usage
-----------------------

SELECT

Recipe = r.[Name]
,Material = min(m.[Name])
,[Plan]  = sum(rl.[Weight])
,Actual = sum(cd.[Weight])
,DiffWeight = sum(cd.[Weight]) - sum(rl.[Weight])
,DiffUSD =  (sum(cd.[Weight]) - sum(rl.[Weight]) ) * min(m.Price)/1000.0
,Heats = count(distinct p.id)
FROM
EAF.Processes p
LEFT  join SCRAP.Charges c on c.HeatId = p.HeatId
LEFT  join SCRAP.ChargeDetails cd on cd.chargeid = c.id
left  join scrap.ScrapMaterials m on cd.ScrapMaterialId = m.id

LEFT JOIN SCRAP.ScrapRecipes r ON r.Id = c.ScrapRecipeId
left join scrap.ScrapRecipeLayers rl on rl.ScrapRecipeId = r.id and rl.ScrapMaterialId=m.id
where p.ProductionDate = @ProductionDate and p.ShiftId = @ShiftId
group by r.[Name],m.id


-----------------------
-- TOP 3 EAF A 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],
dc.[Code] ,
[Start] = IH.ToLocal(d.Start,  @timezoneCode),
[End] = IH.ToLocal(d.[End],  @timezoneCode),
[Duration] = DATEDIFF(MINUTE, d.[Start], d.[End])

FROM Delays.Delays d
LEFT JOIN Delays.Delays children ON children.ParentId = d.Id
JOIN Delays.ProductionItems pitems ON pitems.Id = d.MachineProductionItemId
JOIN CAT.Furnaces equip ON equip.[Name] = pitems.[Name]
LEFT JOIN Delays.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 DATEDIFF(MINUTE, d.[Start], d.[End]) >= 3
)

SELECT * from CTE where rn<4 order by Furnace, rn


-----------------------
-- LOG BOOK
-----------------------
select
[Title] = l.Title
,[Author] = l.Author
,[ShiftId] = l.ShiftID
,[CrewId] = l.CrewId
,[Description] = l.Description

from LogBook.LB.LogEntries l
where @ProductionDate = l.ProductionDate and l.ShiftID =@SHiftID

-----------------------
-- ALL HEATS
-----------------------
SELECT
[Furnace] = furnace.Name, -- EAF A OR EAF B
[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])

FROM EAF.Processes process
JOIN MES.Heats heat ON heat.Id = process.HeatId
LEFT JOIN Delays.Shifts shifts ON shifts.Id = process.ShiftId
LEFT JOIN CAT.RegulationModes regulationMode ON regulationMode.Id = process.RegulationModeId
LEFT JOIN CAT.Furnaces furnace ON furnace.Code = process.FurnaceCode
WHERE
process.ProductionDate = @ProductionDate
and process.ShiftId = @ShiftId
ORDER BY furnace.[Name] ASC, IH.ToLocal(process.[Start], @timeZoneCode) ASC

-----------------------
-- ALL DELAYS
-----------------------
SELECT
Furnace = equip.[Name],
dc.[Code] ,
[Start] = IH.ToLocal(d.Start,  @timeZoneCode),
[End] = IH.ToLocal(d.[End],  @timeZoneCode),
[Color] = dCat.Color,
[Duration] = DATEDIFF(MINUTE, d.[Start], d.[End])

FROM Delays.Delays d
LEFT JOIN Delays.Delays children ON children.ParentId = d.Id
JOIN Delays.ProductionItems pitems ON pitems.Id = d.MachineProductionItemId
JOIN CAT.Furnaces equip ON equip.[Name] = pitems.[Name]
LEFT JOIN Delays.DelayCodes dc on dc.id = d.DelayCodeId
LEFT JOIN IHDelays.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

select 
Carbon	 
,HiCal	
,ProSlag
,Dolomite
,Dolomite2
from #records where EAFName = 'EAF B'

select 
Carbon	
,HiCal	
,ProSlag
,Dolomite 
,Dolomite2
from #records where EAFName = 'EAF A'


/* Temperature and Residuals  */
select
HeatName,
EAFName,
EAFLastTemperature,
LMFFirstTemperature,
EAFLastO2, --dave
LMFFirstO2, --dave
LMFLastO2, --dave
LMFLastTemperature, --dave
Cu,
Sn,
Cr, --dave
Mo, --dave
P --dave
from #Records



PRINT @finishEndMSG

RETURN 0

END



GO

USE [MES]
GO
/****** Object:  StoredProcedure [RFAC].[GetShiftReportPickers]    Script Date: 7/15/2019 12:11:15 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	Returns a list of catalogs used to filter log entries
*/
ALTER PROCEDURE [EAF].[GetShiftReportPickers]
AS
BEGIN
	
	-----------------------
	-- list of catalogs
	-----------------------
	select [Index]=1, Name='CREWS'  
	union select 2,'SHIFTS'
	-----------------------
	-- Crews
	-----------------------
	select Crew = 'A' 
	union select 'B'
	union select 'C'
	union select 'D'

	-----------------------
	-- Shifts
	-----------------------
	select [Shift]='D'
	union select 'N'

	-----------------------
	-- Finish procedure
	-----------------------
	RETURN 0
END
GO

--USE [MES]
--GO
--/****** Object:  UserDefinedFunction [SYSTEM].[GetShiftDataForDatetime]    Script Date: 7/4/2019 10:34:47 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
--create FUNCTION [SYSTEM].[GetDatetimesForShift]
--(
--	@ProductionDate DATETIME,
--	@ShiftId CHAR(1)
--)
--RETURNS @shiftDataT TABLE
--(
--	ShiftStart DATETIME,
--	ShiftEnd DATETIME
--)
--AS
--BEGIN
--	insert into @shiftDataT
--	select 
--	 dateadd(HH,12,@ProductionDate)
--	 ,dateadd(HH,24,@ProductionDate)
--	RETURN
--END

--GO

--USE [MES]
--GO

--alter table CAT.ProductTypes 
--add Color varchar(20)

--update CAT.ProductTypes  set 
--color = '#'+replace(master.dbo.fn_varbintohexstr(id),'0x000000','')
--+replace(master.dbo.fn_varbintohexstr(id*2),'0x000000','')
--+replace(master.dbo.fn_varbintohexstr(id*3),'0x000000','')

--go




------------------------------------------------------------------------------------------------------------------------------------------
----------------------------
----
---- Script Date: 14 oct.
----
----------------------------
USE [MES]
GO
/****** Object:  StoredProcedure [EAF].[GetShiftReportPickers]    Script Date: 14 oct. 2020 16:25:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [EAF].[GetShiftReportPickers]
AS
BEGIN
	
	-----------------------
	-- list of catalogs
	-----------------------
	select [Index]=1, Name='CREWS'  
	union select 2,'SHIFTS'
	union select 3, 'SLAG-LIMITS'
	-----------------------
	-- Crews
	-----------------------
	select Crew = 'A' 
	union select 'B'
	union select 'C'
	union select 'D'

	-----------------------
	-- Shifts
	-----------------------
	select [Shift]='D'
	union select 'N'

	-----------------------
	-- 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 [EAF].[GetShiftReport]    Script Date: 16 oct. 2020 08:50:39 ******/
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 @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.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
	-----------------------
		-----------------------------------
		-- |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(heat.SteelWeight, 0) --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] = 0
		
			/* 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 ORDER BY z.Id DESC)
			,[EAFLastO2] = (SELECT TOP 1 z.Oxygen FROM EAF.Temperatures z WHERE z.ProcessId = process.id ORDER BY z.Id DESC)
			/* 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
				)
			,[RowNum] = ROW_NUMBER() OVER (	ORDER BY process.[Start] DESC)
		INTO #records
		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.ProductionDate = @ProductionDate AND process.ShiftId = @ShiftId

			-------------------------------------
			-- 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 = sum(ElectricEnergy) / NULLIF(sum(SteelWeight), 0) * 1000.0
				,ElectricPower = sum(ElectricEnergy) / NULLIF(sum(r.PowerOn), 0) / 3600.0
				,SmartARC = avg(SmartArc)
			FROM #records r
			GROUP BY r.EAFName
			WITH rollup

			----------------------
			-- Table |1| -- Recipes
			-----------------------
			SELECT Recipe = r.[Name]
				,Material = min(m.[Name])
				,[Plan] = sum(rl.[Weight])
				,Actual = sum(cd.[Weight])
				,DiffWeight = sum(cd.[Weight]) - sum(rl.[Weight])
				,DiffPercent = cast((sum(cd.[Weight]) - sum(rl.[Weight])) / NULLIF(sum(rl.[Weight]), 0) * 100.0 AS DECIMAL(18, 2))
				,DiffUSD = (sum(cd.[Weight]) - sum(rl.[Weight])) * min(m.Price) / 1000.0
				,Heats = count(DISTINCT p.id)
			FROM EAF.Processes p
			LEFT JOIN SCRAP.Charges c ON c.HeatId = p.HeatId
			LEFT JOIN (
				SELECT ChargeID
					,ScrapMaterialId
					,[Weight] = sum(Weight)
				FROM SCRAP.ChargeDetails
				GROUP BY ChargeId
					,ScrapMaterialId
				) cd ON cd.chargeid = c.id
			LEFT JOIN scrap.ScrapMaterials m ON cd.ScrapMaterialId = m.id
			LEFT JOIN SCRAP.ScrapRecipes r ON r.Id = c.ScrapRecipeId
			LEFT JOIN (
				SELECT ScrapRecipeID
					,ScrapMaterialId
					,Weight = sum(Weight)
				FROM scrap.ScrapRecipeLayers
				GROUP BY ScrapRecipeId
					,scrapmaterialId
				) rl ON rl.ScrapRecipeId = r.id
				AND rl.ScrapMaterialId = m.id
			WHERE p.Id IN (SELECT Id From #records) -- was replaced by <--  WHERE p.ProductionDate = @ProductionDate AND p.ShiftId = @ShiftId   
			GROUP BY r.[Name], m.id
			ORDER BY r.[Name], m.id

			----------------------------------------------------------
			--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]
					,dc.[Code]
					,[Start] = IH.ToLocal(d.Start, @timezoneCode)
					,[End] = IH.ToLocal(d.[End], @timezoneCode)
					,[Duration] = DATEDIFF(MINUTE, d.[Start], d.[End])
				FROM Delays.Delays d
				LEFT JOIN Delays.Delays children ON children.ParentId = d.Id
				JOIN Delays.ProductionItems pitems ON pitems.Id = d.MachineProductionItemId
				JOIN CAT.Furnaces equip ON equip.[Name] = pitems.[Name]
				LEFT JOIN Delays.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 DATEDIFF(MINUTE, d.[Start], d.[End]) >= 3
				)
			SELECT *
			FROM CTE
			WHERE rn < 4
			ORDER BY Furnace, rn

		--------------------------
		-- Table |3|---- LOG BOOK
		--------------------------
		SELECT 
			[Title] = l.Title
			,[Author] = l.Author
			,[ShiftID] = l.ShiftID
			,[CrewId] = l.CrewId
			,[Description] = l.[Description]
		FROM LogBook.LB.LogEntries l
		WHERE @ProductionDate = l.ProductionDate 
			AND l.ShiftID = case when @ShiftId = 1 then 'D' when @ShiftId = 2 then 'N' end		
		
		UNION
	
		SELECT h.[Name]
			,'EAF Op'
			,case when ep.ShiftId = 1 then 'D' when ep.ShiftId = 2 then 'N' end
			,ep.CrewId
			,ep.Comments
		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  

   
   ----------------------------------------------
   -- 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])
		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] = dCat.Color
			,[Duration] = DATEDIFF(MINUTE, d.[Start], d.[End])
		FROM Delays.Delays d
		LEFT JOIN Delays.Delays children ON children.ParentId = d.Id
		JOIN Delays.ProductionItems pitems ON pitems.Id = d.MachineProductionItemId
		JOIN CAT.Furnaces equip ON equip.[Name] = pitems.[Name]
		LEFT JOIN Delays.DelayCodes dc ON dc.id = d.DelayCodeId
		LEFT JOIN IHDelays.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
		FROM #Records 
		ORDER BY [Start] ASC
	
		-----------------------------------
		-- Table |7| -- BUCKET FLUXES EAF A
		-----------------------------------

		SELECT 
			HeatName
			,EAFName
			,Carbon
			,HiCal
			,Dolo = Dolomite + Dolomite2
		FROM #records
		WHERE EAFName = 'EAF A'

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

		SELECT
			HeatName
			,EAFName
			,Carbon
			,HiCal
			,Dolo = Dolomite + Dolomite2
		FROM #records
		WHERE EAFName = 'EAF B'


			
		-----------------------------------
		-- Table |9| -- INJECTION FLUXES EAF A
		-----------------------------------

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

		-----------------------------------
		-- Table |10| -- INJECTION FLUXES EAF B
		-----------------------------------

		SELECT 
			r.HeatName
			,r.EAFName
			,Carbon = r.InjectedC
			,Dolo = r.InjectedLime 
		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
			
			,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
	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 ep.Id IN (SELECT Id From #records)
		ORDER BY  left(Heat.[Name],1) ASC, record.[Timestamp] desc


	PRINT @finishEndMSG

	RETURN 0
END


GO