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

CREATE ROLE vtdheatreport_read AUTHORIZATION dbo 
GO  

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

USE [IHBoxSystem]
GO
EXEC sp_addrolemember 'vtdheatreport_read', 'SDI/australtek'
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


/*--------------------------------------------------------------------------------------------------------------------------------*/
/*--------------------------------------------------------------------------------------------------------------------------------*/
/*--------------------------------------------------------------------------------------------------------------------------------*/
/*--------------------------------------------------------------------------------------------------------------------------------*/
/*--------------------------------------------------------------------------------------------------------------------------------*/
EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [vtdheatreport_read] -- The role
,@name = N'securedsp-MES.GetAutocompleteHeats' -- the procedure
,@value = N'' 


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



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

Go



-- MES.GetAutocompleteHeats ---> add VTD Option
-- MES.GetNavigatedHeat  ---> add VTD option
-- Change role from readWrite to new role "read" 
-- [LF].[GetChartData] this sp is called from vtd.getheatreport --> change it


/* ------------------------------------ 4/2/2024 : Manual Additions --------------------------------------------------*/

/* ------------------------------------ 4/2/2024 : Manual Additions --------------------------------------------------*/

/* ------------------------------------ 4/2/2024 : Manual Additions --------------------------------------------------*/

/* ------------------------------------ 4/2/2024 : Manual Additions --------------------------------------------------*/

/* ------------------------------------ 4/2/2024 : Manual Additions --------------------------------------------------*/

/* ------------------------------------ 4/2/2024 : Manual Additions --------------------------------------------------*/

/* ------------------------------------ 4/2/2024 : Manual Additions --------------------------------------------------*/

/* ------------------------------------ 4/2/2024 : Manual Additions --------------------------------------------------*/

/* ------------------------------------ 4/2/2024 : Manual Additions --------------------------------------------------*/

/* ------------------------------------ 4/2/2024 : Manual Additions --------------------------------------------------*/

/* ------------------------------------ 4/2/2024 : Manual Additions --------------------------------------------------*/


/* This role is used in LF Production to give write permissions to edit the Stirred and Foamed ladles */
use IHBoxSystem
go

	USE [IHBoxSystem]
	GO
	CREATE ROLE vtdp_w AUTHORIZATION dbo 
	GO  

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

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



	/* new sps permissions*/

	use ihboxsystem
	go

	EXECUTE sys.sp_addextendedproperty 
		@level0type = N'USER' -- The object type
		,@level0name = [vtdp_w] -- The role
		,@name = N'securedsp-VTD.MoveAddition' -- the procedure
		,@value = N'SendCurrentUser=TRUE' 
		GO


		
	EXECUTE sys.sp_addextendedproperty 
		@level0type = N'USER' -- The object type
		,@level0name = [vtdp_w] -- The role
		,@name = N'securedsp-VTD.UpdateAdditionManually' -- the procedure
		,@value = N'SendCurrentUser=TRUE' 
		GO




		
		
	EXECUTE sys.sp_addextendedproperty 
		@level0type = N'USER' -- The object type
		,@level0name = [vtdp_w] -- The role
		,@name = N'securedsp-VTD.InsertAdditionManually' -- the procedure
		,@value = N'SendCurrentUser=TRUE' 
		GO


		

		
		
	EXECUTE sys.sp_addextendedproperty 
		@level0type = N'USER' -- The object type
		,@level0name = [vtdp_w] -- The role
		,@name = N'securedsp-VTD.RemoveAdditionManually' -- the procedure
		,@value = N'SendCurrentUser=TRUE' 
		GO


	/*  Main table of alloys  */
	use mes

	go


	CREATE TABLE [VTD].[AlloysConsumption](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ProcessId] [int] NULL,
	[SourceCode] [char](1) NULL,
	[Timestamp] [datetime] NULL,
	[DumpStart] [datetime] NULL,
	[DumpEnd] [datetime] NULL,
	[BatchStart] [datetime] NULL,
	[BatchEnd] [datetime] NULL,
	[Destination] [int] NULL,
	[WeightValue] [float] NULL,
	[WeightSetpoint] [float] NULL,
	[WeightMaxValue] [float] NULL,
	[MaterialId] [int] NULL,
	[BinNumber] [int] NULL,
	[isManual] [bit] NOT NULL,
	[isActive] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [VTD].[AlloysConsumption] ADD  CONSTRAINT [DF_AlloysConsumption_isManual]  DEFAULT ((0)) FOR [isManual]
GO

ALTER TABLE [VTD].[AlloysConsumption] ADD  CONSTRAINT [DF_AlloysConsumption_isActive]  DEFAULT ((1)) FOR [isActive]
GO



/* New version of vtd.getHeatReport */

USE [MES]
GO
/****** Object:  StoredProcedure [VTD].[GetHeatReport]    Script Date: 2/6/2024 10:05:32 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [VTD].[GetHeatReport]
	@heatName varchar(7),
	@treatment int = 1,
	@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:'
		+ ', @timeZoneCode: ' + ISNULL(CAST(@timeZoneCode AS VARCHAR), '')	
		+ ', @treatment: ' + ISNULL(CAST(@treatment AS VARCHAR), '')	
		+ ', @heatName: ' + ISNULL(CAST(@heatName AS VARCHAR), '')	

	-----------------------
	-- Variables
	-----------------------
	DECLARE @heatId INT,
			@processId INT,
			@start DATETIME,
			@startProcess DATETIME,			
			@endProcess DATETIME

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

	-----------------------
	-- Begin Query
	-----------------------
	select @heatId = Id from mes.Heats where [Name] = @heatName
	select @processId = Id from vtd.Processes where Treatment = @treatment and HeatId = @heatId

	
		SELECT
			@startProcess = p.[Start],
			@endProcess = p.[End]
		FROM MES.Heats hs
		LEFT JOIN VTD.Processes p on p.HeatId = hs.Id
		WHERE p.Id = @processId 
			

		-----------------------
		-- Process
		-----------------------
		SELECT 
			process.*,
			StartLocal = IH.ToLocal(process.[Start], @timeZoneCode),
			ArriveTimestampLocal = IH.ToLocal(process.[ArriveTimestamp], @timeZoneCode),
			EndLocal = IH.ToLocal(process.[End], @timeZoneCode),
			HeatName = heat.[Name],
			SteelWeight = cast (heat.SteelWeight as decimal(18,2)),
			GradeName = grade.[Name],
			TankCode = process.TankCode,
			LadleName = ladle.[Name],
			OperatorName = operator.[Name],			
			MeltOrder = mo.MeltOrderNumber,
			TotalTreatmentTime = DATEDIFF(SECOND,process.[Start],process.[End]),
			TotalSequenceTime = DATEDIFF(SECOND,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.Ladles ladle ON ladle.Id = heat.LadleId
		LEFT JOIN CAT.Operators operator ON operator.Id = process.OperatorId
		left join MES.MeltOrders mo on heat.MeltOrderId = mo.id
		WHERE process.Id = @processId 
	
		-----------------------
		-- Probes
		-----------------------
		SELECT * FROM (
				SELECT
				[Datetime] = IH.ToLocal([Datetime],@timeZoneCode),
				[Value]= cast([Value] as decimal(8,2)),
				[ProbeType]
				FROM VTD.Probes t
				left join VTD.Processes p on t.ProcessId = p.Id 
				WHERE ProcessId = @processId and t.[Datetime] between p.[Start] and p.[End]
		) ProbesResult
		PIVOT (
			MAX([Value])
			FOR [ProbeType]
			IN (
			[O],
			[T],
			[C]
			)
		) AS PivotTable
		-----------------------
		-- Alloys
		-----------------------
		SELECT
			a.ID,
			a.ProcessID,
			a.SourceCode,
			BatchStart = IH.ToLocal(BatchStart,@timeZoneCode),
			BatchEnd =   IH.ToLocal(BatchEnd,@timeZoneCode),
			DumpStart = IH.ToLocal(DumpStart,@timeZoneCode),
			DumpEnd =   IH.ToLocal(DumpEnd,@timeZoneCode)

		FROM VTD.AdditionBatches a
		left join VTD.Processes p on p.Id = a.ProcessID
		WHERE ProcessId = @processId 
		ORDER BY BatchStart ASC

		SELECT 
			ID = ab.ID,
			AdditionBatchID = ab.AdditionBatchID,
			WeightSP = cast(ab.WeightSP as int),
			WeightPV = cast(ab.WeightPV as int),
			MaterialName = mat.[Name],
			Parameter1 = null,			
			ab.BinNumber
		FROM VTD.AdditionBatchDetails ab
		LEFT JOIN CAT.Materials mat on mat.Id = ab.MaterialId
		WHERE AdditionBatchID IN(
								SELECT a.ID 
								FROM VTD.AdditionBatches a
								left join VTD.Processes p on p.Id = a.ProcessID
								WHERE ProcessID = @processId 
							 )
		AND mat.[Name] is not null
		ORDER BY ab.AdditionBatchID ASC


		
		
		-----------------------
		-- Navigate Buttons
		-----------------------
		
			select top 5 [Text]= 'EAF', Treatment = null, HeatName = h.[Name], HeatId = h.Id, [CurrentPage]= 'false'
		from EAF.Processes p 
		left join mes.Heats h on h.Id = p.HeatId
		where p.HeatId = @heatId
		
		UNION all
		select top 1 [Text]= 'LMF', Treatment = NULL , HeatName = h.[Name], HeatId = h.Id, [CurrentPage]= 'false' 
		from LF.Processes p 
		left join mes.Heats h on h.Id = p.HeatId
		where p.HeatId = @heatId 
		
		UNION all
		select top 5 [Text]= 'VTD', Treatment = p.Treatment, HeatName = h.[Name], HeatId = h.Id, [CurrentPage]= 'true'
		from VTD.Processes p 
		left join mes.Heats h on h.Id = p.HeatId
		where p.HeatId = @heatId

		union all
		select top 1 [Text]= 'CASTER', Treatment =null, HeatName = h.[Name], HeatId = h.Id, [CurrentPage]= 'false'
		from CAS.Processes p
		left join mes.Heats h on h.Id = p.HeatId
		where p.HeatId = @heatId
	
		union all
		select top 1 [Text]= 'LAB', Treatment = null, HeatName = h.[Name], HeatId = h.Id, [CurrenPage]= 'false'
		from LAB.ChemistrySamplesWithCalculated l 
		left join mes.Heats h on h.Id = l.HeatId
		where l.HeatId = @heatId

		union all
		select top 5 [Text]= 'SLAG EAF', Treatment = null, HeatName = h.[Name], HeatId = h.Id, [CurrentPage]= 'false'
		from LAB.SlagChemistrySamples l
		left join mes.Heats h on h.Id = l.HeatId
		where l.HeatId = @heatId

		union all
		select top 1 [Text]= 'THERMAL', Treatment =null, HeatName = h.[Name], HeatId = h.Id, [CurrentPage]= 'false'
		from mes.HeatTemperatures p
		left join mes.Heats h on h.Id = p.HeatId
		where p.HeatId = @heatId

	-----------------------
	-- Chart 
	-----------------------

		EXEC [VTD].[GetChartData] @id  = @processId



		-----------------------
		-- Alloys
		-----------------------
		
		-------------------------------
		-- Next Version Alloys ------
		------------------------------
		select
			ac.Id, 
			ProcessId,
			SourceCode,
			isManual,
			[Timestamp] = left(convert(varchar,IH.ToLocal(ac.[Timestamp],@timeZoneCode),108),5),
			[DumpStart], 
			WeightSP = WeightSetpoint ,
			--WeightPV = cast(WeightValue as int),			
			WeightPV = WeightValue,
			MaterialName = mat.[Name],
			MaterialId = ac.materialId,
			Parameter1 = null,			
			BinNumber,
			UoM = case SourceCode   when 'B' then 'lbs'		-- Bags (Manual Additions)
								when 'A' then 'lbs'		-- Alloys
								when 'L' then 'lbs'		-- Lime
								when 'W' then 'ft'		-- Wirefeeders
								when 'C' then 'lbs'		-- Carbon
								when 'M' then 'lbs'		-- MicroAlloys
								else '-'
								end
		from VTD.AlloysConsumption ac 
		LEFT JOIN CAT.Materials mat on mat.Id = ac.MaterialId   
		where ac.ProcessId = @processId and isActive = 1
		and mat.id in (76,82,84 ) -- FeTi / 84 - Al / 76 - CaSi / 82
		order by DumpStart

		declare @siblingProcesses table ([value] int, [label] varchar(500), [start] datetime, currentProcessId int)

		insert into @siblingProcesses
		select top 5 
			[value] = p.Id, 
			[label] = 
				+ left(convert(varchar,IH.ToLocal(p.[Start],@timeZoneCode),101),5) + ' ' 
				+ left(convert(varchar,IH.ToLocal(p.[Start],@timeZoneCode),108),5) 
				+ ' VTD ' + case when p.TankCode = 1 then 'A' else 'B' end
				+ ' ( ' + h.[Name] + ' - ' + CAST(p.Treatment as varchar) + ' )',
			[Start],
			@processId  
		from VTD.Processes p
		left join MES.Heats h on h.Id = p.HeatId		
		where p.[Start] > @startProcess order by p.[Start] asc

		insert into @siblingProcesses
		select top 5 
			[value] = p.Id, 
			[label] = 
				+ left(convert(varchar,IH.ToLocal(p.[Start],@timeZoneCode),101),5) + ' ' 
				+ left(convert(varchar,IH.ToLocal(p.[Start],@timeZoneCode),108),5) 
				+ ' VTD ' + case when p.TankCode = 1 then 'A' else 'B' end
				+ ' ( ' + h.[Name] + ' - ' + CAST(p.Treatment as varchar) + ' )',
			[Start],
			@processId  
		from VTD.Processes p
		left join MES.Heats h on h.Id = p.HeatId		
		where p.[Start] < @startProcess order by p.[Start] desc

		insert into @siblingProcesses
		select top 5 
			[value] = p.Id, 
			[label] = 
				+ left(convert(varchar,IH.ToLocal(p.[Start],@timeZoneCode),101),5) + ' ' 
				+ left(convert(varchar,IH.ToLocal(p.[Start],@timeZoneCode),108),5) 
				+ ' VTD ' + case when p.TankCode = 1 then 'A' else 'B' end
				+ ' ( ' + h.[Name] + ' - ' + CAST(p.Treatment as varchar) + ' )',
			[Start],
			@processId  
		from VTD.Processes p
		left join MES.Heats h on h.Id = p.HeatId		
		where p.Id = @processId			
		

		select [value],[label],currentProcessId from @siblingProcesses order by [Start]
			
		select 
			m.Id as [value]
			,m.[Name] as [label]
		from CAT.Materials m 
		
		where m.id in (76,82,84 ) -- FeTi / 84 - Al / 76 - CaSi / 82

		-------------------------------
		-- Next Version Alloys ------
		------------------------------

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

	RETURN 0
END

exec  [VTD].[GetHeatReport] 'B236918'
go


/*---- [RemoveAdditionManually]  -----*/


CREATE PROCEDURE [VTD].[RemoveAdditionManually]
	@id INT,	
	@comment VARCHAR(MAX),	
	@CurrentUser VARCHAR(50) = NULL -- auto included by 4i
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:'
		+ '@id: ' + ISNULL(CAST(@id AS VARCHAR), '')		
		+ ', @comment: ' + ISNULL(CAST(@comment AS VARCHAR), '')
		+ ', @CurrentUser: ' + ISNULL(CAST(@CurrentUser AS VARCHAR), '')
 
	-----------------------
	-- Variables
	-----------------------
	DECLARE @logMsgAudit VARCHAR(MAX), @logMsgLogBook VARCHAR(MAX), @logMsg VARCHAR(MAX)
		SET NOCOUNT ON

	-- current values
	declare @timestamp datetime = (select top 1 [Timestamp] from VTD.AlloysConsumption where Id = @Id)
	declare @currProcessId	int =	(select top 1 ProcessId from VTD.AlloysConsumption where Id = @Id)
	declare @currHeatId		int =	(select top 1 HeatId from VTD.Processes where Id = @currProcessId)
	declare @currHeatName	varchar(max) = cast((select h.[Name] from MES.Heats h where h.Id = @currHeatId) as varchar)
	declare @currVTD		varchar(max) = cast((select top 1 case when TankCode = 1 then ' A ' else ' B ' end from VTD.Processes where Id = @currProcessId) as varchar)
 	declare @currTreat		varchar(max) = cast((select top 1 Treatment from VTD.Processes where Id = @currProcessId) as varchar)	
	declare @materialId int = (select top 1 MaterialId from VTD.AlloysConsumption where Id = @Id)


	-- Alloy entry data
	declare @weightValue	varchar(max)	= cast((select WeightValue from VTD.AlloysConsumption where Id = @id)  as varchar)
	declare @weightSetPoint varchar(max) = cast((select WeightSetpoint from VTD.AlloysConsumption where Id = @id)  as varchar)
	declare @material		varchar(max) = cast((select m.[Name] from VTD.AlloysConsumption a left join CAT.Materials m on m.Id = a.MaterialId where a.Id = @id)  as varchar)
	declare @source			varchar(max) = cast((select SourceCode from VTD.AlloysConsumption where Id = @id)  as varchar)
	
	-- Log Book 
	declare @ProductionDate datetime ,@CrewID char(1),@ShiftID char(1)	
	declare @LogEntryTypeID int = (select top 1 [Id] from LogBook.lb.catLogEntryTypes where [Name] = 'Operations')
	declare @AssetId int = (select AssetId from LogBook.LB.catAssets where [AssetName] = 'VTD')
	
	select 
		@ProductionDate = gsd.ProductionDate
		,@CrewID = gsd.CrewID
		,@ShiftID = gsd.ShiftID
	from LogBook.LB.GetShiftData(GETUTCDATE()) as gsd

	
	declare @newMaterialName varchar(50) = (select top 1 [Name] from CAT.Materials where Id = @materialId) 
	
	-----------------------
	-- Previous values
	-----------------------
	SET @logMsgAudit =    'The alloy with: ' 
					+ ' Id: ' + CAST(@id as varchar) 
					+ ' ,Heat: ' + CAST(@currHeatName as varchar)
					+ ' ,ProcessId: ' + CAST(@currProcessId as varchar)
					+ ' ,Weight: ' + CAST(@weightValue as varchar) 
					+ ' ,Weight SP: ' +  CAST(@weightSetPoint as varchar) 
					+ ' ,Material: ' + CAST(@material as varchar)
					+ ' was manually removed '
					+ ' by ' + isnull(@CurrentUser,'NULL') 
					+ ' comment: ' + @comment


	SET @logMsgLogBook =  'The alloy with: '	
					+ 'Heat: ' + CAST(@currHeatName as varchar)
					+ ' ,Weight: ' + CAST(@weightValue as varchar) 
					+ ' ,Weight SP: ' +  CAST(@weightSetPoint as varchar) 
					+ ' ,Material: ' + CAST(@material as varchar)
					+ ' was manually removed '
					+ ' by ' + isnull(@CurrentUser,'NULL') 
					+ ' ,Comment: ' + @comment


	BEGIN TRY
		
		update VTD.AlloysConsumption
			set isActive = 0
		where Id = @id

		-- AUDIT
		insert into MES.[Audit](HeatId, HeatName, Station, [Type], [Timestamp], [User], [Description])
		values(
			@currHeatId
			,@currHeatName
			,'VTD ' + @currVTD
			,'A'	-- Alloys System: it includes Batch-dump, Micro alloys, Lime, Carbon, Manual alloys, wirefeeders, etc..
			,GETUTCDATE()
			,@CurrentUser
			,@logMsgAudit
		)

		-- LOG BOOK
		INSERT INTO LogBook.LB.LogEntries ([ProductionDate],[ShiftID],[Time],[CrewID]
				,[LogEntryTypeID],[AssetId],[Title]
				,[CreateTimestamp],[LogBookTypeCode],[CasterProcessId], [Description],[Author]
				)
			VALUES (
				@ProductionDate
				, CASE WHEN @ShiftID = 1 THEN 'D' ELSE 'N' END
				,@timestamp
				,@CrewID
				,@LogEntryTypeID
				,@AssetId
				,'Addition manually removed ' 
				,GETUTCDATE()
				,'C'
				,null
				,@logMsgLogBook
				,@CurrentUser
				)
							   				 			  
		select * from VTD.AlloysConsumption where Id = @Id

	END TRY
	BEGIN CATCH
		SELECT 	@logMsg = ERROR_MESSAGE()

			SET @logMsg = 'Failed VTD.RemoveAdditionManually ' + @logMsg + ' - It tried to do: ' + @logMsgAudit
				RAISERROR( @logMsg ,0,1) WITH NOWAIT

			EXEC SYSTEM.LogMsg @message = @logMsg
				,@procedure = @procName
				,@typeCode = 'ERROR'
				,@logError = 1
	END CATCH


	-----------------------
	-- Begin Query
	-----------------------
	
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END

go
/* UPDATE*/


CREATE PROCEDURE [VTD].[UpdateAdditionManually]
	@id INT,
	@materialId INT,  
	@weight FLOAT,      
	@weightSP FLOAT,    
	@comment VARCHAR(MAX),	
	@CurrentUser VARCHAR(50) = NULL -- auto included by 4i
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:'
		+ '@id: ' + ISNULL(CAST(@id AS VARCHAR), '')
		+ ', @materialId: ' + ISNULL(CAST(@materialId AS VARCHAR), '')
		+ ', @weight: ' + ISNULL(CAST(@weight AS VARCHAR), '')
		+ ', @weightSP: ' + ISNULL(CAST(@weightSP AS VARCHAR), '')
		+ ', @comment: ' + ISNULL(CAST(@comment AS VARCHAR), '')
		+ ', @CurrentUser: ' + ISNULL(CAST(@CurrentUser AS VARCHAR), '')
 
	-----------------------
	-- Variables
	-----------------------
	DECLARE @logMsgAudit VARCHAR(MAX), @logMsgLogBook VARCHAR(MAX), @logMsg VARCHAR(MAX)
		SET NOCOUNT ON

	-- current values
	declare @timestamp datetime = (select top 1 [Timestamp] from VTD.AlloysConsumption where Id = @Id)
	declare @currProcessId	int =	(select top 1 ProcessId from VTD.AlloysConsumption where Id = @Id)
	declare @currHeatId		int =	(select top 1 HeatId from VTD.Processes where Id = @currProcessId)
	declare @currHeatName	varchar(max) = cast((select h.[Name] from MES.Heats h where h.Id = @currHeatId) as varchar)
	declare @currVTD		varchar(max) = cast((select top 1 case when TankCode = 1 then ' A ' else ' B ' end from VTD.Processes where Id = @currProcessId) as varchar)
 	declare @currTreat		varchar(max) = cast((select top 1 Treatment from VTD.Processes where Id = @currProcessId) as varchar)	



	-- Alloy entry data
	declare @weightValue	varchar(max)	= cast((select WeightValue from VTD.AlloysConsumption where Id = @id)  as varchar)
	declare @weightSetPoint varchar(max) = cast((select WeightSetpoint from VTD.AlloysConsumption where Id = @id)  as varchar)
	declare @material		varchar(max) = cast((select m.[Name] from VTD.AlloysConsumption a left join CAT.Materials m on m.Id = a.MaterialId where a.Id = @id)  as varchar)
	declare @source			varchar(max) = cast((select SourceCode from VTD.AlloysConsumption where Id = @id)  as varchar)
	
	-- Log Book 
	declare @ProductionDate datetime ,@CrewID char(1),@ShiftID char(1)	
	declare @LogEntryTypeID int = (select top 1 [Id] from LogBook.lb.catLogEntryTypes where [Name] = 'Operations')
	declare @AssetId int = (select AssetId from LogBook.LB.catAssets where [AssetName] = 'VTD')
	
	select 
		@ProductionDate = gsd.ProductionDate
		,@CrewID = gsd.CrewID
		,@ShiftID = gsd.ShiftID
	from LogBook.LB.GetShiftData(GETUTCDATE()) as gsd

	
	declare @newMaterialName varchar(50) = (select top 1 [Name] from CAT.Materials where Id = @materialId) 
	
	-----------------------
	-- Previous values
	-----------------------
	SET @logMsgAudit =    'The alloy with: ' 
					+ ' Id: ' + CAST(@id as varchar) 
					+ ' ,Heat: ' + CAST(@currHeatName as varchar)
					+ ' ,ProcessId: ' + CAST(@currProcessId as varchar)
					+ ' ,Weight: ' + CAST(@weightValue as varchar) 
					+ ' ,Weight SP: ' +  CAST(@weightSetPoint as varchar) 
					+ ' ,Material: ' + CAST(@material as varchar)
					+ ' was manually edited with the values: '
					+ ' Weight: ' + CAST(@weight as varchar) 
					+ ' ,Weight SP: ' +  CAST(@weightSP as varchar) 
					+ ' ,Material: ' + CAST(@newMaterialName as varchar)
					+ ' by ' + isnull(@CurrentUser,'NULL') 
					+ ' comment: ' + @comment


	SET @logMsgLogBook =  'The alloy with: '	
					+ 'Heat: ' + CAST(@currHeatName as varchar)
					+ ' ,Weight: ' + CAST(@weightValue as varchar) 
					+ ' ,Weight SP: ' +  CAST(@weightSetPoint as varchar) 
					+ ' ,Material: ' + CAST(@material as varchar)
					+ ' was manually edited with the values: '
					+ ' Weight: ' + CAST(@weight as varchar) 
					+ ' ,Weight SP: ' +  CAST(@weightSP as varchar) 
					+ ' ,Material: ' + CAST(@newMaterialName as varchar)
					+ ' by ' + isnull(@CurrentUser,'NULL') 
					+ ' ,Comment: ' + @comment


	BEGIN TRY
		
		update VTD.AlloysConsumption
			set WeightValue = @weight
				,WeightSetpoint = @weightSP
				,MaterialId = @materialId
				,isManual = 1
		where Id = @id

		-- AUDIT
		insert into MES.[Audit](HeatId, HeatName, Station, [Type], [Timestamp], [User], [Description])
		values(
			@currHeatId
			,@currHeatName
			,'VTD ' + @currVTD
			,'A'	-- Alloys System: it includes Batch-dump, Micro alloys, Lime, Carbon, Manual alloys, wirefeeders, etc..
			,GETUTCDATE()
			,@CurrentUser
			,@logMsgAudit
		)

		-- LOG BOOK
		INSERT INTO LogBook.LB.LogEntries ([ProductionDate],[ShiftID],[Time],[CrewID]
				,[LogEntryTypeID],[AssetId],[Title]
				,[CreateTimestamp],[LogBookTypeCode],[CasterProcessId], [Description],[Author]
				)
			VALUES (
				@ProductionDate
				, CASE WHEN @ShiftID = 1 THEN 'D' ELSE 'N' END
				,@timestamp
				,@CrewID
				,@LogEntryTypeID
				,@AssetId
				,'Addition manually edited' 
				,GETUTCDATE()
				,'C'
				,null
				,@logMsgLogBook
				,@CurrentUser
				)
							   				 			  
		select * from VTD.AlloysConsumption where Id = @Id

	END TRY
	BEGIN CATCH
		SELECT 	@logMsg = ERROR_MESSAGE()

			SET @logMsg = 'Failed VTD.UpdateAdditionManually ' + @logMsg + ' - It tried to do: ' + @logMsgAudit
				RAISERROR( @logMsg ,0,1) WITH NOWAIT

			EXEC SYSTEM.LogMsg @message = @logMsg
				,@procedure = @procName
				,@typeCode = 'ERROR'
				,@logError = 1
	END CATCH


	-----------------------
	-- Begin Query
	-----------------------
	
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END


go


USE [MES]
GO
/****** Object:  StoredProcedure [LF].[InsertAdditionManually]    Script Date: 4/2/2024 17:10:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [VTD].[InsertAdditionManually]
	@processId INT,
	@dumpStart DATETIME = null,
	@materialId INT,  
	@weight FLOAT,      
	@weightSP FLOAT,    
	@comment VARCHAR(MAX),	
	@CurrentUser VARCHAR(50) = NULL -- auto included by 4i
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), '')
		+ ', @dumpStart: ' + ISNULL(CAST(@dumpStart AS VARCHAR), '')
		+ ', @materialId: ' + ISNULL(CAST(@materialId AS VARCHAR), '')
		+ ', @weight: ' + ISNULL(CAST(@weight AS VARCHAR), '')
		+ ', @weightSP: ' + ISNULL(CAST(@weightSP AS VARCHAR), '')
		+ ', @comment: ' + ISNULL(CAST(@comment AS VARCHAR), '')
		+ ', @CurrentUser: ' + ISNULL(CAST(@CurrentUser AS VARCHAR), '')
 
	-----------------------
	-- Variables
	-----------------------
	DECLARE @logMsgAudit VARCHAR(MAX), @logMsgLogBook VARCHAR(MAX), @logMsg VARCHAR(MAX)
		SET NOCOUNT ON

		
	-- current values
	
	declare @currHeatId		int =	(select top 1 HeatId from VTD.Processes where Id = @processId)
	declare @currHeatName	varchar(max) = cast((select h.[Name] from MES.Heats h where h.Id = @currHeatId) as varchar)
	declare @currVTD		varchar(max) = cast((select top 1 case when TankCode = 1 then ' A ' else ' B ' end from VTD.Processes where Id = @processId) as varchar)
 	declare @currTreat		varchar(max) = cast((select top 1 Treatment from VTD.Processes where Id = @processId) as varchar)


	-- Log Book 
	declare @ProductionDate datetime ,@CrewID char(1),@ShiftID char(1)	
	declare @LogEntryTypeID int = (select top 1 [Id] from LogBook.lb.catLogEntryTypes where [Name] = 'Operations')
	declare @AssetId int = (select AssetId from LogBook.LB.catAssets where [AssetName] = 'VTD')
	
	select 
		@ProductionDate = gsd.ProductionDate
		,@CrewID = gsd.CrewID
		,@ShiftID = gsd.ShiftID
	from LogBook.LB.GetShiftData(GETUTCDATE()) as gsd
	


	declare @newMaterialName varchar(50) = (select top 1 [Name] from CAT.Materials where Id = @materialId) 
	declare @newId int
	
	--------------------------------
	--- Source Code vs Material ----
	--------------------------------
	declare @sourceMaterial table (MaterialId int, SourceCode char(1))
	;with cte as (		
		select distinct(SourceCode) from VTD.AlloysConsumption
	)
	insert into @sourceMaterial
	select distinct(MaterialId),SourceCode from VTD.AlloysConsumption where SourceCode in (select SourceCode from cte)


	BEGIN TRY
		
		
		insert into VTD.AlloysConsumption(ProcessId,SourceCode,[Timestamp],DumpStart,Destination,WeightValue,WeightSetpoint,MaterialId,isManual)
		values(
			@processId
			,(select top 1 SourceCode from @sourceMaterial where MaterialId = @materialId)
			,GETUTCDATE()
			,case when @dumpStart is null then GETUTCDATE() else @dumpStart end
			,(select top 1 TankCode from VTD.Processes where Id = @processId)
			,@weight
			,@weightSP
			,@materialId
			,1
		)
		set @newId = @@IDENTITY


	-----------------------
	-- Log Messages
	-----------------------
	SET @logMsgAudit =    'The alloy with: '
					+ ' Id: ' + CAST(@newId as varchar)
					+ ' ,ProcessId: ' + CAST(@processId as varchar)
					+ ' ,Heat: ' + CAST(@currHeatName as varchar)
					+ ' was manually created with the values: '
					+ ' Weight: ' + CAST(@weight as varchar) 
					+ ' ,Weight SP: ' +  CAST(@weightSP as varchar) 
					+ ' ,Material: ' + CAST(@newMaterialName as varchar)
					+ ' by ' + isnull(@CurrentUser,'NULL') 
					+ ' comment: ' + @comment


	SET @logMsgLogBook =  'The alloy with: '	
					+ ' Heat: ' + CAST(@currHeatName as varchar)
					+ ' was manually created with the values: '
					+ ' Weight: ' + CAST(@weight as varchar) 
					+ ' ,Weight SP: ' +  CAST(@weightSP as varchar) 
					+ ' ,Material: ' + CAST(@newMaterialName as varchar)
					+ ' by ' + isnull(@CurrentUser,'NULL') 
					+ ' ,Comment: ' + @comment

		-- AUDIT
		insert into MES.[Audit](HeatId, HeatName, Station, [Type], [Timestamp], [User], [Description])
		values(
			@currHeatId
			,@currHeatName
			,'VTD ' + @currVTD
			,'A'	-- Alloys System: it includes Batch-dump, Micro alloys, Lime, Carbon, Manual alloys, wirefeeders, etc..
			,GETUTCDATE()
			,@CurrentUser
			,@logMsgAudit
		)

		-- LOG BOOK
		INSERT INTO LogBook.LB.LogEntries ([ProductionDate],[ShiftID],[Time],[CrewID]
				,[LogEntryTypeID],[AssetId],[Title]
				,[CreateTimestamp],[LogBookTypeCode],[CasterProcessId], [Description],[Author]
				)
			VALUES (
				@ProductionDate
				, CASE WHEN @ShiftID = 1 THEN 'D' ELSE 'N' END
				,GETUTCDATE()
				,@CrewID
				,@LogEntryTypeID
				,@AssetId
				,'Addition manually created' 
				,GETUTCDATE()
				,'C'
				,null
				,@logMsgLogBook
				,@CurrentUser
				)
							   				 			  
		select * from VTD.AlloysConsumption where Id = @newId

	END TRY
	BEGIN CATCH
		SELECT 	@logMsg = ERROR_MESSAGE()

			SET @logMsg = 'Failed VTD.InsertAdditionManually ' + @logMsg + ' - It tried to do: ' + @logMsgAudit
				RAISERROR( @logMsg ,0,1) WITH NOWAIT

			EXEC SYSTEM.LogMsg @message = @logMsg
				,@procedure = @procName
				,@typeCode = 'ERROR'
				,@logError = 1
	END CATCH


	-----------------------
	-- Begin Query
	-----------------------
	
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END
go


/* MOVE */

USE [MES]
GO
/****** Object:  StoredProcedure [LF].[MoveAddition]    Script Date: 4/2/2024 17:12:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [VTD].[MoveAddition]
	@id INT,
	@newProcessId INT,
	@comment VARCHAR(MAX),	
	@CurrentUser VARCHAR(50) = NULL -- auto included by 4i
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:'
		+ '@id: ' + ISNULL(CAST(@id AS VARCHAR), '')
		+ ', @newProcessId: ' + ISNULL(CAST(@newProcessId AS VARCHAR), '')
		+ ', @comment: ' + ISNULL(CAST(@comment AS VARCHAR), '')
		+ ', @CurrentUser: ' + ISNULL(CAST(@CurrentUser AS VARCHAR), '')


	-----------------------
	-- Variables
	-----------------------
	DECLARE @logMsgAudit VARCHAR(MAX), @logMsgLogBook VARCHAR(MAX), @logMsg VARCHAR(MAX)
		SET NOCOUNT ON

	-- current values
	declare @timestamp datetime = (select top 1 [Timestamp] from VTD.AlloysConsumption where Id = @Id)
	declare @currProcessId	int =	(select top 1 ProcessId from VTD.AlloysConsumption where Id = @Id)
	declare @currHeatId		int =	(select top 1 HeatId from VTD.Processes where Id = @currProcessId)
	declare @currHeatName	varchar(max) = cast((select h.[Name] from MES.Heats h where h.Id = @currHeatId) as varchar)
	declare @currVTD		varchar(max) = cast((select top 1 case when TankCode = 1 then ' A ' else ' B ' end from VTD.Processes where Id = @currProcessId) as varchar)
 	declare @currTreat		varchar(max) = cast((select top 1 Treatment from VTD.Processes where Id = @currProcessId) as varchar)
	
	-- next values
	declare @newHeatId		int =	(select top 1 HeatId from VTD.Processes where Id = @newProcessId)
	declare @newHeatName	varchar(max) = cast((select h.[Name] from MES.Heats h where h.Id = @newHeatId) as varchar)
	declare @newVTD			varchar(max) = cast((select top 1 case when TankCode = 1 then ' A ' else ' B ' end from VTD.Processes where Id = @newProcessId) as varchar)
	declare @newTreat		varchar(max) = cast((select top 1 Treatment from VTD.Processes where Id = @newProcessId) as varchar)


	-- Alloy entry data
	declare @weightValue	varchar(max)	= cast((select WeightValue from VTD.AlloysConsumption where Id = @id)  as varchar)
	declare @weightSetPoint varchar(max) = cast((select WeightSetpoint from VTD.AlloysConsumption where Id = @id)  as varchar)
	declare @material		varchar(max) = cast((select m.[Name] from VTD.AlloysConsumption a left join CAT.Materials m on m.Id = a.MaterialId where a.Id = @id)  as varchar)
	declare @source			varchar(max) = cast((select SourceCode from VTD.AlloysConsumption where Id = @id)  as varchar)
	
	
	declare @FromTo varchar(max) = ' from: ' + ' VTD ' + CAST(@currVTD as varchar) + ' ( '  + CAST(@currHeatName as varchar) + ' - ' + CAST(@currTreat as varchar) + ' ) ' + ' to: ' + ' VTD ' + CAST(@newVTD as varchar)  + ' ( '  + CAST(@newHeatName as varchar)  + ' - ' + CAST(@newTreat as varchar)  + ' ) '	
	
	-- Log Book 
	declare @ProductionDate datetime ,@CrewID char(1),@ShiftID char(1)	
	declare @LogEntryTypeID int = (select top 1 [Id] from LogBook.lb.catLogEntryTypes where [Name] = 'Operations')
	declare @AssetId int = (select AssetId from LogBook.LB.catAssets where [AssetName] = 'VTD')
	
	select 
		@ProductionDate = gsd.ProductionDate
		,@CrewID = gsd.CrewID
		,@ShiftID = gsd.ShiftID
	from LogBook.LB.GetShiftData(GETUTCDATE()) as gsd

	
	
	
	-----------------------
	-- Previous values
	-----------------------
	SET @logMsgAudit =    'The alloy with: ' 
					+ ' Id: ' + CAST(@id as varchar) 
					+ ' ,ProcessId: ' + CAST(@currProcessId as varchar)
					+ ' ,Weight: ' + CAST(@weightValue as varchar) 
					+ ' ,Weight SP: ' +  CAST(@weightSetPoint as varchar) 
					+ ' ,Material: ' + CAST(@material as varchar)
					+ ' was moved' + @FromTo 		
					+ ' New ProcessId: ' + CAST(@newProcessId as varchar)
					+ ' by ' + isnull(@CurrentUser,'NULL') 
					+ ' comment: ' + @comment


	SET @logMsgLogBook =  'Weight: ' + CAST(@weightValue as varchar) 
					+ ' ,Weight SP: ' +  CAST(@weightSetPoint as varchar) 
					+ ' ,Material: ' + CAST(@material as varchar)
					+ ' ,User: ' + isnull(@CurrentUser,'NULL')  
					+ ' ,Comment: ' + @comment


	BEGIN TRY

		-- UPDATE
		update VTD.AlloysConsumption
			set ProcessId = @newProcessId
			,isManual = 1
		where Id = @id
			
		-- AUDIT
		insert into MES.[Audit](HeatId, HeatName, Station, [Type], [Timestamp], [User], [Description])
		values(
			@newHeatId
			,@newHeatName
			,'VTD ' + @newVTD
			,'A'	-- Alloys System: it includes Batch-dump, Micro alloys, Lime, Carbon, Manual alloys, wirefeeders, etc..
			,GETUTCDATE()
			,@CurrentUser
			,@logMsgAudit
		)

		-- LOG BOOK
		INSERT INTO LogBook.LB.LogEntries ([ProductionDate],[ShiftID],[Time],[CrewID]
				,[LogEntryTypeID],[AssetId],[Title]
				,[CreateTimestamp],[LogBookTypeCode],[CasterProcessId], [Description],[Author]
				)
			VALUES (
				@ProductionDate
				, CASE WHEN @ShiftID = 1 THEN 'D' ELSE 'N' END
				,@timestamp
				,@CrewID
				,@LogEntryTypeID
				,@AssetId
				,'Addition moved' + @FromTo 
				,GETUTCDATE()
				,'C'
				,null
				,@logMsgLogBook
				,@CurrentUser
				)
							   				 			  
		select * from VTD.AlloysConsumption where Id = @Id

	END TRY
	BEGIN CATCH
		SELECT 	@logMsg = ERROR_MESSAGE()

			SET @logMsg = 'Failed VTD.MoveAddition ' + @logMsg + ' - It tried to do: ' + @logMsgAudit
				RAISERROR( @logMsg ,0,1) WITH NOWAIT

			EXEC SYSTEM.LogMsg @message = @logMsg
				,@procedure = @procName
				,@typeCode = 'ERROR'
				,@logError = 1
	END CATCH


	-----------------------
	-- Begin Query
	-----------------------
	
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END
GO




/*Missing:*/

/*
	 1 - Material lists --> check which are the correct materials to display in the list box
	 2 - When there is no addition, the table should be able to right-click and display the "add" option available and the rest must be blocked
	 3 - The old tables of vtd.AdditionsBatches and vtd.additionsBatchesDetails should be removed and change it to the new table format 
	 4 - Explain that the vtd production is able to edit in the grid the foamed and stirred ladles
	 5 - In the LMF Heat Report add the ability to add rows if the table is empty

*/


USE [MES]
GO
/****** Object:  StoredProcedure [VTD].[GetHeatReport]    Script Date: 2/7/2024 3:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [VTD].[GetHeatReport]
	@heatName varchar(7),
	@treatment int = 1,
	@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:'
		+ ', @timeZoneCode: ' + ISNULL(CAST(@timeZoneCode AS VARCHAR), '')	
		+ ', @treatment: ' + ISNULL(CAST(@treatment AS VARCHAR), '')	
		+ ', @heatName: ' + ISNULL(CAST(@heatName AS VARCHAR), '')	

	-----------------------
	-- Variables
	-----------------------
	DECLARE @heatId INT,
			@processId INT,
			@start DATETIME,
			@startProcess DATETIME,			
			@endProcess DATETIME

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

	-----------------------
	-- Begin Query
	-----------------------
	select @heatId = Id from mes.Heats where [Name] = @heatName
	select @processId = Id from vtd.Processes where Treatment = @treatment and HeatId = @heatId

	
		SELECT
			@startProcess = p.[Start],
			@endProcess = p.[End]
		FROM MES.Heats hs
		LEFT JOIN VTD.Processes p on p.HeatId = hs.Id
		WHERE p.Id = @processId 
			

		-----------------------
		-- Process
		-----------------------
		SELECT 
			process.*,
			StartLocal = IH.ToLocal(process.[Start], @timeZoneCode),
			ArriveTimestampLocal = IH.ToLocal(process.[ArriveTimestamp], @timeZoneCode),
			EndLocal = IH.ToLocal(process.[End], @timeZoneCode),
			HeatName = heat.[Name],
			SteelWeight = cast (heat.SteelWeight as decimal(18,2)),
			GradeName = grade.[Name],
			TankCode = process.TankCode,
			LadleName = ladle.[Name],
			OperatorName = operator.[Name],			
			MeltOrder = mo.MeltOrderNumber,
			TotalTreatmentTime = DATEDIFF(SECOND,process.[Start],process.[End]),
			TotalSequenceTime = DATEDIFF(SECOND,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.Ladles ladle ON ladle.Id = heat.LadleId
		LEFT JOIN CAT.Operators operator ON operator.Id = process.OperatorId
		left join MES.MeltOrders mo on heat.MeltOrderId = mo.id
		WHERE process.Id = @processId 
	
		-----------------------
		-- Probes
		-----------------------
		SELECT * FROM (
				SELECT
				[Datetime] = IH.ToLocal([Datetime],@timeZoneCode),
				[Value]= cast([Value] as decimal(8,2)),
				[ProbeType]
				FROM VTD.Probes t
				left join VTD.Processes p on t.ProcessId = p.Id 
				WHERE ProcessId = @processId and t.[Datetime] between p.[Start] and p.[End]
		) ProbesResult
		PIVOT (
			MAX([Value])
			FOR [ProbeType]
			IN (
			[O],
			[T],
			[C]
			)
		) AS PivotTable
		-----------------------
		-- Alloys
		-----------------------
		--SELECT
		--	a.ID,
		--	a.ProcessID,
		--	a.SourceCode,
		--	BatchStart = IH.ToLocal(BatchStart,@timeZoneCode),
		--	BatchEnd =   IH.ToLocal(BatchEnd,@timeZoneCode),
		--	DumpStart = IH.ToLocal(DumpStart,@timeZoneCode),
		--	DumpEnd =   IH.ToLocal(DumpEnd,@timeZoneCode)

		--FROM VTD.AdditionBatches a
		--left join VTD.Processes p on p.Id = a.ProcessID
		--WHERE ProcessId = @processId 
		--ORDER BY BatchStart ASC

		--SELECT 
		--	ID = ab.ID,
		--	AdditionBatchID = ab.AdditionBatchID,
		--	WeightSP = cast(ab.WeightSP as int),
		--	WeightPV = cast(ab.WeightPV as int),
		--	MaterialName = mat.[Name],
		--	Parameter1 = null,			
		--	ab.BinNumber
		--FROM VTD.AdditionBatchDetails ab
		--LEFT JOIN CAT.Materials mat on mat.Id = ab.MaterialId
		--WHERE AdditionBatchID IN(
		--						SELECT a.ID 
		--						FROM VTD.AdditionBatches a
		--						left join VTD.Processes p on p.Id = a.ProcessID
		--						WHERE ProcessID = @processId 
		--					 )
		--AND mat.[Name] is not null
		--ORDER BY ab.AdditionBatchID ASC


		
		
		-----------------------
		-- Navigate Buttons
		-----------------------
		
			select top 5 [Text]= 'EAF', Treatment = null, HeatName = h.[Name], HeatId = h.Id, [CurrentPage]= 'false'
		from EAF.Processes p 
		left join mes.Heats h on h.Id = p.HeatId
		where p.HeatId = @heatId
		
		UNION all
		select top 1 [Text]= 'LMF', Treatment = NULL , HeatName = h.[Name], HeatId = h.Id, [CurrentPage]= 'false' 
		from LF.Processes p 
		left join mes.Heats h on h.Id = p.HeatId
		where p.HeatId = @heatId 
		
		UNION all
		select top 5 [Text]= 'VTD', Treatment = p.Treatment, HeatName = h.[Name], HeatId = h.Id, [CurrentPage]= 'true'
		from VTD.Processes p 
		left join mes.Heats h on h.Id = p.HeatId
		where p.HeatId = @heatId

		union all
		select top 1 [Text]= 'CASTER', Treatment =null, HeatName = h.[Name], HeatId = h.Id, [CurrentPage]= 'false'
		from CAS.Processes p
		left join mes.Heats h on h.Id = p.HeatId
		where p.HeatId = @heatId
	
		union all
		select top 1 [Text]= 'LAB', Treatment = null, HeatName = h.[Name], HeatId = h.Id, [CurrenPage]= 'false'
		from LAB.ChemistrySamplesWithCalculated l 
		left join mes.Heats h on h.Id = l.HeatId
		where l.HeatId = @heatId

		union all
		select top 5 [Text]= 'SLAG EAF', Treatment = null, HeatName = h.[Name], HeatId = h.Id, [CurrentPage]= 'false'
		from LAB.SlagChemistrySamples l
		left join mes.Heats h on h.Id = l.HeatId
		where l.HeatId = @heatId

		union all
		select top 1 [Text]= 'THERMAL', Treatment =null, HeatName = h.[Name], HeatId = h.Id, [CurrentPage]= 'false'
		from mes.HeatTemperatures p
		left join mes.Heats h on h.Id = p.HeatId
		where p.HeatId = @heatId

	-----------------------
	-- Chart 
	-----------------------

		EXEC [VTD].[GetChartData] @id  = @processId



		-----------------------
		-- Alloys
		-----------------------
		
		-------------------------------
		-- Next Version Alloys ------
		------------------------------
		select
			ac.Id, 
			ProcessId,
			SourceCode,
			isManual,
			[Timestamp] = left(convert(varchar,IH.ToLocal(ac.[Timestamp],@timeZoneCode),108),5),
			[DumpStart], 
			WeightSP = WeightSetpoint ,
			--WeightPV = cast(WeightValue as int),			
			WeightPV = WeightValue,
			MaterialName = mat.[Name],
			MaterialId = ac.materialId,
			Parameter1 = null,			
			BinNumber,
			UoM = case SourceCode   when 'B' then 'lbs'		-- Bags (Manual Additions)
								when 'A' then 'lbs'		-- Alloys
								when 'L' then 'lbs'		-- Lime
								when 'W' then 'ft'		-- Wirefeeders
								when 'C' then 'lbs'		-- Carbon
								when 'M' then 'lbs'		-- MicroAlloys
								else '-'
								end
		from VTD.AlloysConsumption ac 
		LEFT JOIN CAT.Materials mat on mat.Id = ac.MaterialId   
		where ac.ProcessId = @processId and isActive = 1
		--and mat.id in (76,82,84 ) -- FeTi / 84 - Al / 76 - CaSi / 82
		order by DumpStart

		declare @siblingProcesses table ([value] int, [label] varchar(500), [start] datetime, currentProcessId int)

		insert into @siblingProcesses
		select top 5 
			[value] = p.Id, 
			[label] = 
				+ left(convert(varchar,IH.ToLocal(p.[Start],@timeZoneCode),101),5) + ' ' 
				+ left(convert(varchar,IH.ToLocal(p.[Start],@timeZoneCode),108),5) 
				+ ' VTD ' + case when p.TankCode = 1 then 'A' else 'B' end
				+ ' ( ' + h.[Name] + ' - ' + CAST(p.Treatment as varchar) + ' )',
			[Start],
			@processId  
		from VTD.Processes p
		left join MES.Heats h on h.Id = p.HeatId		
		where p.[Start] > @startProcess order by p.[Start] asc

		insert into @siblingProcesses
		select top 5 
			[value] = p.Id, 
			[label] = 
				+ left(convert(varchar,IH.ToLocal(p.[Start],@timeZoneCode),101),5) + ' ' 
				+ left(convert(varchar,IH.ToLocal(p.[Start],@timeZoneCode),108),5) 
				+ ' VTD ' + case when p.TankCode = 1 then 'A' else 'B' end
				+ ' ( ' + h.[Name] + ' - ' + CAST(p.Treatment as varchar) + ' )',
			[Start],
			@processId  
		from VTD.Processes p
		left join MES.Heats h on h.Id = p.HeatId		
		where p.[Start] < @startProcess order by p.[Start] desc

		insert into @siblingProcesses
		select top 5 
			[value] = p.Id, 
			[label] = 
				+ left(convert(varchar,IH.ToLocal(p.[Start],@timeZoneCode),101),5) + ' ' 
				+ left(convert(varchar,IH.ToLocal(p.[Start],@timeZoneCode),108),5) 
				+ ' VTD ' + case when p.TankCode = 1 then 'A' else 'B' end
				+ ' ( ' + h.[Name] + ' - ' + CAST(p.Treatment as varchar) + ' )',
			[Start],
			@processId  
		from VTD.Processes p
		left join MES.Heats h on h.Id = p.HeatId		
		where p.Id = @processId			
		

		select [value],[label],currentProcessId from @siblingProcesses order by [Start]
			
		select 
			m.Id as [value]
			,m.[Name] as [label]
		from CAT.Materials m 
		
		where m.id in (76,82,84 ) -- FeTi / 84 - Al / 76 - CaSi / 82

		-------------------------------
		-- Next Version Alloys ------
		------------------------------

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

	RETURN 0
END

go

exec [VTD].[GetHeatReport] 'B236918'

go