USE [MES]
GO
/****** Object:  StoredProcedure [LAB].[GetSlagChemistrySamplesReport]    Script Date: 8/13/2019 8:51:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAB].[GetSlagChemistrySamplesReport]
@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(2.0000 AS FLOAT),
B3Min = CAST(1.5000 AS FLOAT),
FeOMax = CAST(30.0000 AS FLOAT),
FeOMin = CAST(15.0000 AS FLOAT),
MgOMax = CAST(15.0000 AS FLOAT),
MgOMin = CAST(5.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.95*c.HiCal+0.58*c.Dolo)/([CaO]/100))*[MgO]/100.0 - (0.38*c.Dolo + 0.01*c.HiCal + 0.75*ProSlag),

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
LEFT JOIN CAT.ChemistrySampleTypes cst ON cst.Code = record.TypeCode
cross apply (
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] = (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*/
  ,[ProSlag] =( 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*/
  )
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
)


) 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