USE [IHBoxSystem]
GO
CREATE ROLE vtdheatreport_readWrite AUTHORIZATION dbo 
GO  

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

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

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

Go

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

Go

insert into FrontEnd.Menu
		([Name],SortOrder,[Enabled],Visible,[Group],IHBoxModule)
		VALUES
			(
			'VTD'
			,7
			,1
			,1
			,'navbar_left_with_modules'
			,'MES'
			)


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

INSERT INTO [FrontEnd].[Routes]
		   ([Route]
		   ,[ModuleName]
		   ,[ModulePath]
		   ,[RequiredRole]
	   )
	 VALUES
		   (
		   '!/vtd-heat-report(/:heatName)(/:treatmentNumber)(/)'
		   , 'vtd-heat-report'
		   , 'custom-screens/vtd-heat-report/'
		   , 'vtdheatreport_readWrite'           
		   )
		   SET @result_routeId = @@IDENTITY
INSERT INTO [FrontEnd].[Menu]
		   ([Parent]
		   ,[Name]
		   ,[RouteID]
		   ,[SortOrder]
		   ,[Enabled]
		   ,[Visible]
		   ,[RequiredRole]
		   ,[Group]
		  )
	 VALUES
		   (
		   @parent
		   ,'VTD Heat Report'
		   ,@result_routeId 
		   ,1
		   ,1
		   ,1
		   ,'vtdheatreport_readWrite'
		   ,NULL
		 )
GO

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

DECLARE @ModuleId INT

INSERT INTO [SECURITY].[Modules]
		   ([Code]
		   ,[Name])
	 VALUES
		   ('vtd_heat_report'
		   ,'VTD Heat Report')

		  SET @ModuleId = @@IDENTITY

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

/*---------------------------------------------------------------------------------------------------------------------------------------------*/
USE [MES]
GO
/****** Object:  StoredProcedure [VTD].[GetProcess]    Script Date: 5 ago. 2020 15:02:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [VTD].[GetProcess]
	@processId INT = NULL,
	@direction BIT = NULL,
	@VTDTankCode 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 + ' Parameters:'
		+ ', @processId: ' + ISNULL(CAST(@processId AS VARCHAR), '')
		+ ', @direction: ' + ISNULL(CAST(@direction AS VARCHAR), '')
		+ ', @VTDTankCode: ' + ISNULL(CAST(@VTDTankCode AS VARCHAR), '')
		+ ', @timeZoneCode: ' + ISNULL(CAST(@timeZoneCode AS VARCHAR), '')
	


	-----------------------
	-- Variables
	-----------------------
	DECLARE @newProcessId INT,
			@start DATETIME

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

	IF NOT EXISTS (SELECT 1 FROM VTD.Processes WHERE Id = @processId)
		RAISERROR ('Process not found',13,1)

	IF (@direction IS NOT NULL)
	BEGIN
		SET @start = (SELECT TOP 1 [Start] FROM VTD.Processes WHERE Id = @processId)

		SET @newProcessId = CASE 
						WHEN (@direction = 1) THEN
								(SELECT TOP 1 Id 
									FROM VTD.Processes 
									WHERE [Start] > @start 
									AND (@VTDTankCode IS NULL OR 
										VTDTankCode = @VTDTankCode) 
									ORDER BY [Start] ASC)
						WHEN (@direction = 0) THEN
							(SELECT TOP 1 Id 
								FROM VTD.Processes 
								WHERE [Start] < @start 
								AND (@VTDTankCode IS NULL OR 
								VTDTankCode = @VTDTankCode) 
								ORDER BY [Start] DESC)
					END

		IF (@newProcessId) IS NOT NULL 
			SET @processId =@newProcessId
	END

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

		-----------------------
		-- Process
		-----------------------
		SELECT 
			process.*,
			HeatName = heat.[Name],
			SteelWeight = heat.SteelWeight,
			GradeName = grade.[Name],
			InitGradeName = initGrade.[Name],
			LadleFurnaceName = ladleFurnace.[Name],
			LadleName = ladle.[Name],
			OperatorName = operator.[Name]

			,TotalTreatmentTime = DATEDIFF(SS,process.[Start],process.[End])
			,TotalSequenceTime = DATEDIFF(SS,process.[StartSequence],process.[EndSequence])
		FROM VTD.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.Grades initGrade ON initGrade.Id = heat.InitialGradeId
		LEFT JOIN CAT.LadleFurnaces ladleFurnace ON ladleFurnace.Code = process.VTDTankCode
		LEFT JOIN CAT.Ladles ladle ON ladle.Id = heat.LadleId
		LEFT JOIN CAT.Operators operator ON operator.Id = process.OperatorId
		WHERE process.Id = @processId 
	
		-----------------------
		-- Probes
		-----------------------
		SELECT * FROM (
				SELECT
				[Datetime] = IH.ToLocal([Datetime],@timeZoneCode),
				[Value],
				[ProbeType]
				FROM VTD.Probes
				WHERE ProcessId = @processId
		) ProbesResult
		PIVOT (
			MAX([Value])
			FOR [ProbeType]
			IN (
			[T],
			[O],
			[H]
			)
		) AS PivotTable
		-----------------------
		-- Additions
		-----------------------


		-----------------------
		-- Navigate Buttons
		-----------------------
		declare @heatId int = (select top 1 heatId from vtd.Processes where Id = @processId)
		
		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]= '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

		union all
		
		select top 5 [Text]= 'SLAG 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
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END
GO
/*---------------------------------------------------------------------------------------------------------------------------------------------*/
USE [MES]
GO
/****** Object:  StoredProcedure [VTD].[GetProcessCatalogs]    Script Date: 5 ago. 2020 10:48:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [VTD].[GetProcessCatalogs]
 	@heatName VARCHAR(50) = NULL,
	@treatment INT = 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 + ' Parameters:'
		+ ', @heatName: ' + ISNULL(CAST(@heatName AS VARCHAR), '')
		+ ', @treatment: ' + ISNULL(CAST(@treatment AS VARCHAR), '')

	-----------------------
	-- Begin Query
	-----------------------
	IF (@heatName IS NOT NULL)

	SELECT
		[Name] = h.[Name]
		,[Treatment] = p.Treatment
		,[ProcessId] = p.Id
	FROM MES.Heats h 
	LEFT JOIN VTD.Processes p on p.HeatId = h.Id
	WHERE h.[Name] LIKE '%' + @heatName + '%'
		AND( @treatment IS NULL OR p.Treatment = @treatment )
	
	ELSE
		SELECT TOP 1
			[Name] = h.[Name]
			,[Treatment] = p.Treatment
			,[ProcessId] = p.Id
		FROM VTD.Processes p 
		LEFT JOIN MES.Heats h on p.HeatId = h.Id
		ORDER BY p.ProductionDate DESC

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

	RETURN 0
END
GO