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



INSERT INTO [FrontEnd].[Menu]
           ([Parent]
           ,[Name]
           ,[RouteID]
           ,[SortOrder]
           ,[Enabled]
           ,[Visible]
           ,[RequiredRole]
           ,[Group]
          )
     VALUES
           (
		   2895
		   ,'LMF 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-CAT.LMFGetSlagChemistrySampleTypes' -- the procedure
,@value = N'' 

Go


USE IHBoxSystem

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

Go


/*-----------------------------------------------------------------------------------------------------------------------------------------------------*/

USE [MES]
GO
/****** Object:  StoredProcedure [CAT].[GetSlagChemistrySampleTypes]    Script Date: 26 mar. 2020 23:10:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [CAT].[LMFGetSlagChemistrySampleTypes]
AS
BEGIN
	-----------------------
	-- Begin Query
	-----------------------
	SELECT 
		* 
	FROM CAT.ChemistrySampleTypes cst
	WHERE cst.Name LIKE '%LMF Slag%'
	
END
GO

USE [MES]
GO
/****** Object:  StoredProcedure [LAB].[LMFGetSlagChemistrySamplesReport]    Script Date: 29 mar. 2020 17:53:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAB].[LMFGetSlagChemistrySamplesReport]
@from DATETIME = NULL,
@to DATETIME = NULL,
@timeZoneCode CHAR(10) = NULL,
@code VARCHAR(20) = 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), '')


DECLARE @now DATETIME,
@fromUTC DATETIME,
@toUTC DATETIME

SET @now = GETUTCDATE()

-----------------------
-- Parameters default
-----------------------

IF (@from IS NOT NULL)
SET @fromUTC = IH.ToUTC(@from, @timeZoneCode)

IF (@to IS NOT NULL)
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
*
FROM (SELECT 
Heat.[Name],
record.*,
--Datetime fields to string
TimestampString = CONVERT(VARCHAR, IH.ToLocal(record.Timestamp, @timeZoneCode), 121),
--Catalog fields name
HeatName = heat.Name,
TypeName = cst.Name,
B3Max = CAST(1.5000 AS FLOAT),
B3Min = CAST(1.2000 AS FLOAT),
FeOMax = CAST(25.0000 AS FLOAT),
FeOMin = CAST(15.0000 AS FLOAT),
MgOMax = CAST(13.0000 AS FLOAT),
MgOMin = CAST(10.0000 AS FLOAT),
RefWearLimit = CAST(-100 AS FLOAT),
-- Total value
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),

/*
Josh Hausbach Aug/2019
((0.95 * HICAL) + (0.58 DOLO)) / (% CaO from slag chemistry) = Total Slag Volume
(0.38 * DOLO) + (0.01 * HICAL) + (0.75 Proslag) = lbs MgO added to heat
(Total Slag Volume) * (% MgO from slag chemistry) = lbs MgO in slag
(lbs MgO in slag) – (lbs MgO added to heat) = lbs of refractory wear for that heat – this is the value to be displayed

(total slag volume) * (% FeO in slag from slag chemistry) * (0.78) = lbs of Fe in slag – this is the value to be displayed
 */
 HiCal, Dolo,ProSlag,
TotalSlagVolume =(0.95*c.HiCal+0.58*c.Dolo)/([CaO]/100),
LbsMgOAdded = 0.38*c.Dolo + 0.01*c.HiCal + 0.75*ProSlag,	
LbsMgOnSlag = ((0.95*c.HiCal+0.58*c.Dolo)/([CaO]/100))*[MgO]/100.0,
RefractoryWear =(0.38*c.Dolo + 0.01*c.HiCal + 0.75*ProSlag) - ((0.95*c.HiCal+0.58*c.Dolo)/([CaO]/100))*[MgO]/100.0 ,

FeinSlag = (0.95*c.HiCal+0.58*c.Dolo)/([CaO]/100)*[FeO]/100*0.78,
RowNum = ROW_NUMBER() OVER (ORDER BY record.Timestamp DESC)
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 (
/* Changes due to modification in bins content JH August 2019 */
select [HiCal] = (select  sum([Weight]) from SCRAP.ChargeFluxes z join SCRAP.Charges y on y.Id = z.ChargeId where y.HeatId=heat.Id and FluxMaterialId=1) /*HICAL*/
  ,  [Dolo] = case when ep.id > 47181 then (select sum([Weight]) from SCRAP.ChargeFluxes z join SCRAP.Charges y on y.Id = z.ChargeId where y.HeatId=heat.Id and FluxMaterialId=4) /*DOLO*/
			  else 
			  case ep.FurnaceCode 
				WHEN 1 THEN 
					(select sum([Weight]) from SCRAP.ChargeFluxes z join SCRAP.Charges y on y.Id = z.ChargeId where y.HeatId=heat.Id and FluxMaterialId in (3,4)) /*DOLO*/
				else
					(select sum([Weight]) from SCRAP.ChargeFluxes z join SCRAP.Charges y on y.Id = z.ChargeId where y.HeatId=heat.Id and FluxMaterialId=4) /*DOLO*/
				end end

  ,[ProSlag] = case when ep.id > 47181 then ( select sum([Weight]) from SCRAP.ChargeFluxes z join SCRAP.Charges y on y.Id = z.ChargeId where y.HeatId=heat.Id and FluxMaterialId=3) /*PROSLAG*/
			   else
			   case ep.FurnaceCode
				WHEN 1 THEN 0 
				else 
				( select sum([Weight]) from SCRAP.ChargeFluxes z join SCRAP.Charges y on y.Id = z.ChargeId where y.HeatId=heat.Id and FluxMaterialId=3) /*PROSLAG*/
				end end
				)
as c
WHERE
(
@from IS NULL
OR
record.Timestamp >= @from
)
AND 
(
@to IS NULL
OR record.Timestamp <= @to
)
AND
(
@code IS NULL
OR
record.TypeCode = @code
)
AND
cst.AreaCode = 'LF'


) AS record
WHERE
@rowsToFetch = -1
OR
(
record.RowNum >= @fromRow
AND 
record.RowNum <= @toRow
)
ORDER BY 
record.RowNum


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

RETURN 0
END
GO


