USE [IHBoxSystem]
GO


USE [IHBoxSystem]
GO
CREATE ROLE lmfproduction_readWrite AUTHORIZATION dbo 
GO  

USE [IHBoxSystem]
GO
EXEC sp_addrolemember 'lmfproduction_readWrite', 'admin'
GO

USE [IHBoxSystem]
GO
EXEC sp_addrolemember 'lmfproduction_readWrite', 'SDI/australtek'
GO

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



declare @parent int, @result_routeId INT
select @parent=id from [FrontEnd].[Menu] where name='LMF'

INSERT INTO [FrontEnd].[Routes]
           ([Route]
           ,[ModuleName]
           ,[ModulePath]
           ,[RequiredRole]
       )
     VALUES
           (
		   '!/lmf-production(/:start)(/:end)(/:ladleFurnaceCode)(/:crewId)(/:shiftId)'
           , 'lmf-production'
		   , 'custom-screens/lmf-production/'
           , 'lmfproduction_readWrite'           
		   )
		   SET @result_routeId = @@IDENTITY
INSERT INTO [FrontEnd].[Menu]
           ([Parent]
           ,[Name]
           ,[RouteID]
           ,[SortOrder]
           ,[Enabled]
           ,[Visible]
           ,[RequiredRole]
           ,[Group]
          )
     VALUES
           (
		   @parent
		   ,'LMF Production'
           ,@result_routeId 
           ,3
           ,1
           ,1
           ,'lmfproduction_readWrite'
           ,NULL
         )
GO


/*------------------------------------------------------Profiles------------------------------------------------------*/
USE [IHBoxSystem]
GO

DECLARE @ModuleId INT

INSERT INTO [SECURITY].[Modules]
           ([Code]
           ,[Name])
     VALUES
           ('lmf_production'
           ,'LMF Production')

		  SET @ModuleId = @@IDENTITY

INSERT INTO [SECURITY].[ModuleActions]
           ([ModuleId]
           ,[Code]
           ,[Name]
           ,[Role])
     VALUES
           (@ModuleId
           ,'readWrite'
           ,'Read/Write'
           ,'lmfproduction_readWrite')
GO

/*------------------------------------------------------Profiles------------------------------------------------------*/
USE [MES]
GO
/****** Object:  StoredProcedure [LF].[GetProductionReport]    Script Date: 1 mar. 2021 09:35:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LF].[GetProductionReport]
	@start DATETIME = NULL,
	@end DATETIME = NULL,
	@ladleFurnaceCode INT = NULL,
	@crewId CHAR(1) = NULL,
	@shiftId INT = NULL,
	@timeZoneCode CHAR(10) = NULL,

	@sortType VARCHAR(50) = 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:'
		+ ', @start: ' + ISNULL(CAST(@start AS VARCHAR), '')
		+ ', @end: ' + ISNULL(CAST(@end AS VARCHAR), '')
		+ ', @ladleFurnaceCode: ' + ISNULL(CAST(@ladleFurnaceCode AS VARCHAR), '')
		+ ', @crewId: ' + ISNULL(CAST(@crewId AS VARCHAR), '')
		+ ', @shiftId: ' + ISNULL(CAST(@shiftId AS VARCHAR), '')
		+ ', @timeZoneCode: ' + ISNULL(CAST(@timeZoneCode AS VARCHAR), '')
	


	-----------------------
	-- Variables
	-----------------------
	-----------------------
	-- Validation
	-----------------------
	IF (@timeZoneCode IS NULL)
		SET @timeZoneCode = 'EDTIND'

	IF (@rowsToFetch IS NULL)
		SET @rowsToFetch = 100
	
	IF (@rowsToFetch > 0)
	BEGIN
		IF (@fromRow IS NULL)
		SET @fromRow = 1
	
		IF (@toRow IS NULL)
		SET @toRow = @fromRow + @rowsToFetch
	END


	-----------------------
	-- Begin Query
	-----------------------
	declare @LimeId int = (select top 1 Id from cat.Materials where [Name] = 'Lime')
	declare @FeVnId int = (select top 1 Id from cat.Materials where [Name] = 'FeVn')
	declare @NiId int = (select top 1 Id from cat.Materials where [Name] = 'Ni')
	declare @LCFeCrId int = (select top 1 Id from cat.Materials where [Name] = 'LCFeCr')
	declare @CarbonId int = (select top 1 Id from cat.Materials where [Name] = 'Carbon')
	declare @SiMnId int = (select top 1 Id from cat.Materials where [Name] = 'SiMn')
	declare @LoAlSiId int = (select top 1 Id from cat.Materials where [Name] = 'LoAlSi')
		SELECT 
			process.*,
			StartLocal = IH.ToLocal(process.[Start],'EDTIND'),
			Duration = DATEDIFF(MINUTE,process.[Start],process.[End]),
			InitialTemperature = (select top 1 z.[Value] from lf.Probes z where z.ProbeType = 'T' and z.ProcessId = process.Id order by z.[Datetime] ASC),
			FinalTemperature = (select top 1 z.[Value] from lf.Probes z where z.ProbeType = 'T' and z.ProcessId = process.Id order by z.[Datetime] DESC),
			FinalOxygen = (select top 1 z.[Value] from lf.Probes z where z.ProbeType = 'O' and z.ProcessId = process.Id order by z.[Datetime] DESC),
			FinalCarbon = (select top 1 z.[Value] from lf.Probes z where z.ProbeType = 'C' and z.ProcessId = process.Id order by z.[Datetime] DESC),
			TotalArgonVolume = process.ArgonVolume1 + process.ArgonVolume2,
			ShiftName = case when process.ShiftId = 1 then 'D' else 'N' end,
			MWH = process.Energy / 1000.0,
			Lime  = (
				select top 1 
					cast(WeightPV as int)
				from lf.AdditionBatchDetails a
				left join lf.AdditionBatches z on z.ID = a.AdditionBatchID
				where 
					z.ProcessID = process.Id
					and a.MaterialId = @LimeId
				order by z.BatchStart desc
			),
			FeVn   = (
				select top 1 
					cast(WeightPV as int)
				from lf.AdditionBatchDetails a
				left join lf.AdditionBatches z on z.ID = a.AdditionBatchID
				where 
					z.ProcessID = process.Id
					and a.MaterialId = @FeVnId
				order by z.BatchStart desc
			),
			Ni	   = (
				select top 1 
					cast(WeightPV as int)
				from lf.AdditionBatchDetails a
				left join lf.AdditionBatches z on z.ID = a.AdditionBatchID
				where 
					z.ProcessID = process.Id
					and a.MaterialId = @NiId
				order by z.BatchStart desc
			),
			LCFeCr = (
				select top 1
					cast(WeightPV as int)
				from lf.AdditionBatchDetails a
				left join lf.AdditionBatches z on z.ID = a.AdditionBatchID
				where 
					z.ProcessID = process.Id
					and a.MaterialId = @LCFeCrId
				order by z.BatchStart desc
			),
			Carbon = (
				select top 1 
					cast(WeightPV as int)
				from lf.AdditionBatchDetails a
				left join lf.AdditionBatches z on z.ID = a.AdditionBatchID
				where 
					z.ProcessID = process.Id
					and a.MaterialId = @CarbonId
				order by z.BatchStart desc
			),
			SiMn   = (
				select top 1 
					cast(WeightPV as int)
				from lf.AdditionBatchDetails a
				left join lf.AdditionBatches z on z.ID = a.AdditionBatchID
				where 
					z.ProcessID = process.Id
					and a.MaterialId = @SiMnId
				order by z.BatchStart desc
			),
			LoAlSi = (
				select top 1 
					cast(WeightPV as int)
				from lf.AdditionBatchDetails a
				left join lf.AdditionBatches z on z.ID = a.AdditionBatchID
				where 
					z.ProcessID = process.Id
					and a.MaterialId = @LoAlSiId
				order by z.BatchStart desc
			),
			HeatName = heat.[Name],
			SteelWeight = heat.SteelWeight,
			GradeName = grade.[Name],
			InitGradeName = '-',
			LadleFurnaceName = ladleFurnace.[Name],
			LadleName = ladle.[Name],
			OperatorName = operator.[Name]
		into #temp
		FROM LF.Processes process
		LEFT JOIN MES.Heats heat ON heat.Id = process.HeatId
		LEFT JOIN CAT.Grades grade ON grade.Id = heat.GradeId
		LEFT JOIN CAT.LadleFurnaces ladleFurnace ON ladleFurnace.Code = process.LadleFurnaceCode
		LEFT JOIN CAT.Ladles ladle ON ladle.Id = heat.LadleId
		LEFT JOIN CAT.Operators operator ON operator.Id = process.OperatorId
		WHERE [Start] between @start and @end
		AND
		(
			@crewId IS NULL
			OR
			process.CrewId = @crewId
		)
		AND
		(
			@shiftId IS NULL
			OR
			process.ShiftId = @shiftId
		)
		AND
		(
			@ladleFurnaceCode IS NULL
			OR
			process.LadleFurnaceCode = @ladleFurnaceCode
		)
		order by process.[Start] ASC

	SELECT *
	FROM #temp record
	ORDER BY CASE 
			WHEN @sortType IS NULL
				THEN record.[Start]
			END desc
	OFFSET @fromRow ROWS

	FETCH NEXT @rowsToFetch ROWS ONLY
		
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END


GO