USE [IHBoxSystem]
GO
CREATE ROLE casterheatreport_readWrite AUTHORIZATION dbo 
GO  

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

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




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

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

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [casterheatreport_readWrite] -- The role
,@name = N'securedsp-CAS.GetProcessChartData' -- the procedure
,@value = N'' 
GO



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

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

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

DECLARE @ModuleId INT

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

		  SET @ModuleId = @@IDENTITY

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

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

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

USE IHBoxSystem

go


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


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


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


update [FrontEnd].[Routes]
	set  [Route]  = '!/caster-heat-report(/:heatName)(/)'
where [Route] like '%!/caster-heat-report(/:processId)(/)%' 


GO







SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [CAS].[GetHeatReport]
	@heatName varchar(7),
	@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:'
		+ ', @heatName: ' + ISNULL(CAST(@heatName AS VARCHAR), '')	
		+ ', @timeZoneCode: ' + ISNULL(CAST(@timeZoneCode AS VARCHAR), '')
	


	-----------------------
	-- Variables
	-----------------------
		declare @heatId INT,@processId INT,@casterCode INT

		--Navigate Buttons
		declare @buttons table ([Text] VARCHAR(50), HeatId INT, Treatment INT)

		--Navigation
		declare @start DATETIME

		--CAS Processes
		declare @currentProcesses TABLE (Id INT)

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

		select top 1
			@processId = p.Id
			,@heatId = p.HeatId
			,@casterCode = p.CasterCode
		from cas.Processes p
		left join mes.heats h on h.Id = p.heatId 
		where [Name] = @heatName
	
	-----------------------
	-- Begin Query
	-----------------------
	

		-----------------------
		-- Process
		-----------------------
		SELECT 
			process.*
			,StartLocal = IH.ToLocal(process.[Start], @timeZoneCode)
			,EndLocal = IH.ToLocal(process.[End], @timeZoneCode)
			,LadleArrivalLocal  = IH.ToLocal(process.LadleArrival, @timeZoneCode)
			,LadleOpenLocal		= IH.ToLocal(process.LadleOpen, @timeZoneCode)
			,LadleCloseLocal	= IH.ToLocal(process.LadleClose, @timeZoneCode)
			,MinTundishTemperature
			,GradeName = grade.[Name]
			,MESHeatName = heat.[Name]
			,LadleName = ladle.[Name]
			,WaitTime = DATEDIFF(SECOND,process.LadleArrival,process.LadleOpen)
			,OpenTime = DATEDIFF(SECOND,process.LadleOpen,process.LadleClose)				
			,MeltOrder = mo.MeltOrderNumber
			,CastTime = DATEDIFF(SECOND,(select MIN(StartCast) from CAS.StrandProcesses where ProcessId = @processId),(select MAX(EndCast) from CAS.StrandProcesses where ProcessId = @processId))	
		FROM cas.Processes process
			LEFT JOIN MES.Heats heat ON heat.Id = process.HeatId
			LEFT JOIN CAT.Ladles ladle ON ladle.Id = heat.LadleId
			LEFT JOIN CAT.Grades grade ON grade.Id = heat.GradeId
			left join MES.MeltOrders mo on heat.MeltOrderId = mo.id	
		WHERE process.Id = @processId and process.CasterCode =@casterCode
	
		-----------------------
		-- Strands
		-----------------------
				
		SELECT *
		,SumProducts = (select COUNT(*) from CAS.Products where isActive = 1 and ProcessId = @processId and StrandId = 1)
		,MoldNumber = (select top 1 z.Abbrev from CAS.catMoldTypes z where z.MoldTypeId = p.MoldTypeId)
		FROM CAS.StrandProcesses p WHERE ProcessId = @processId and StrandId = 1

		SELECT *
		,SumProducts = (select COUNT(*) from CAS.Products where isActive = 1 and ProcessId = @processId and StrandId = 2)
		,MoldNumber = (select top 1 z.Abbrev from CAS.catMoldTypes z where z.MoldTypeId = p.MoldTypeId)
		FROM CAS.StrandProcesses p WHERE ProcessId = @processId and StrandId = 2

		SELECT *
		,SumProducts = (select COUNT(*) from CAS.Products where isActive = 1 and ProcessId = @processId and StrandId = 3)
		,MoldNumber = (select top 1 z.Abbrev from CAS.catMoldTypes z where z.MoldTypeId = p.MoldTypeId)
		FROM CAS.StrandProcesses p WHERE ProcessId = @processId and StrandId = 3

		SELECT *
		,SumProducts = (select COUNT(*) from CAS.Products where isActive = 1 and ProcessId = @processId and StrandId = 4)
		,MoldNumber = (select top 1 z.Abbrev from CAS.catMoldTypes z where z.MoldTypeId = p.MoldTypeId)
		FROM CAS.StrandProcesses p WHERE ProcessId = @processId and StrandId = 4


		-----------------------
		-- Products
		-----------------------
		SELECT 
		[ProductNumber] = ISNULL(CAST(h.[Name] AS VARCHAR(10)), '') +  ' - '  + ISNULL(CAST(StrandId AS VARCHAR(1)),'')   + ' - ' + ISNULL(CAST(ROW_NUMBER() over (partition by strandId order by CutStart asc) AS VARCHAR(2)),'')
		,[TorchStart]= CONVERT(VARCHAR, IH.ToLocal([CutStart], @timeZoneCode), 8)
		,[TorchEnd]= CONVERT(VARCHAR, IH.ToLocal([CutComplete], @timeZoneCode), 8)
		,[LocalDatetime]= CONVERT(VARCHAR, IH.ToLocal([Datetime], @timeZoneCode), 8)
		,[Weight] = cast([Weight] as int)
		,[Length] = cast([Length] as int)
		FROM CAS.Products prod
		left join cas.Processes p on p.Id = prod.ProcessId
		left join mes.heats h on h.Id = prod.HeatId
		where prod.ProcessId = @processId and p.CasterCode =@casterCode and prod.isActive = 1
		order by StrandId asc, [CutStart] asc

		-----------------------
		-- Navigate Buttons
		-----------------------		
		if @heatId is not null 
			begin
			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]= 'VTD', Treatment = p.Treatment, HeatName = h.[Name], HeatId = h.Id
			from VTD.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
			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
			from LAB.SlagChemistrySamples l
			left join mes.Heats h on h.Id = l.HeatId
			where l.HeatId = @heatId
		end
		
		-----------------------
		-- Caster Log Book
		-----------------------
		select [Description]
		,[CasterProcessId] 
		from LogBook.Lb.LogEntries where CasterProcessId = @processId
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END


go

USE [MES]
GO
/****** Object:  StoredProcedure [MES].[GetAutocompleteHeats]    Script Date: 8/10/2022 18:38:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [MES].[GetAutocompleteHeats]
 	@heatName VARCHAR(10) = NULL
	,@treatment INT = NULL
	,@SelectedHeatName VARCHAR(7) = NULL
	,@SelectedTreatment INT = NULL
	,@Asset CHAR(3) = 'LMF'
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), '')

		
	DECLARE @SearchTextString VARCHAR(100)

	SET @searchTextString = '%' + @heatName + '%'

	declare @heatNames table ([value] varchar(9), [label] varchar(9),[Datetime] datetime, [processId] int,[treatment] int)
	
	declare @filterHeats table ([value] varchar(9), [label] varchar(9),[processId] int)

	declare @selectedHeatId int 
	-----------------------
	-- Begin Query
	-----------------------
	if (@Asset = 'LMF')
	begin		
		insert into @heatNames
		select top 20 
			cast(h.[Name] as varchar(7)) + '-' +  cast(l.Treatment as varchar(1)),
			cast(h.[Name] as varchar(7)) + '-' +  cast(l.Treatment as varchar(1)),
			l.[Start],
			l.Id,
			l.Treatment
		from mes.heats h
		left join lf.Processes l on l.HeatId = h.Id
		where 
		@heatName is null 
		or (
			h.[Name] like @searchTextString 
		)
		and l.HeatId is not null	
		order by l.[Start] desc		



		
		if (@heatName is not null and @treatment is not null)	
			insert into @filterHeats
			select [value],[label],[processId] from @heatNames where treatment = @treatment order by [Datetime] desc
		else if (@heatName is null)  	-- When the page is first opened, we need to get the last process
			insert into @filterHeats
			select top 1	
				[value] = h.[Name],
				[label] = h.[Name],
			    [processId] = p.Id
			from lf.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where [Start] is not null and [End] is not null order by p.[Start] desc	
		else -- When the user searches by heat, we need to show the last 20 heats 
			insert into @filterHeats
			select [value],[label],[processId] from @heatNames order by [Datetime] desc


		-- If the pikcer has a selected heat is needed to add this to the list
		if (@SelectedHeatName is not null)
		begin
			set @selectedHeatId = (select top 1 Id from mes.heats where [Name] = @SelectedHeatName)
			insert into @filterHeats
			values(
				 @SelectedHeatName  + '-' + cast(@SelectedTreatment as varchar(1))
				, @SelectedHeatName + '-' + cast(@SelectedTreatment as varchar(1))
				,(select top 1 Id from lf.Processes where Treatment = @SelectedTreatment and HeatId = @selectedHeatId)
			)
		end
	
		select distinct([value]),[label],[processId] from @filterHeats
		
	end


	if (@Asset = 'EAF')
	begin		
		insert into @heatNames
		select top 20 
			cast(h.[Name] as varchar(7)),
			cast(h.[Name] as varchar(7)),
			l.[Start],
			l.Id,
			null
		from mes.heats h
		left join eaf.Processes l on l.HeatId = h.Id
		where 
		@heatName is null 
		or (
			h.[Name] like @searchTextString 
		)
		and l.HeatId is not null	
		order by l.[Start] desc		



		
		if (@heatName is not null)	-- When the user searches by heat, we need to show the last 20 heats 
			insert into @filterHeats
			select [value],[label],[processId] from @heatNames order by [Datetime] desc
		else if (@heatName is null)  	-- When the page is first opened, we need to get the last process
			insert into @filterHeats
			select top 1	
				[value] = h.[Name],
				[label] = h.[Name],
			    [processId] = p.Id
			from eaf.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where [Start] is not null and [End] is not null order by p.[Start] desc	

		-- If the pikcer has a selected heat is needed to add this to the list
		if (@SelectedHeatName is not null)
		begin
			set @selectedHeatId = (select top 1 Id from mes.heats where [Name] = @SelectedHeatName)
			insert into @filterHeats
			values(
				 @SelectedHeatName 
				, @SelectedHeatName
				,(select top 1 Id from eaf.Processes where HeatId = @selectedHeatId)
			)
		end
	
		select distinct([value]),[label],[processId] from @filterHeats
		
	end



	
	if (@Asset = 'CAS')
	begin		
		insert into @heatNames
		select top 20 
			cast(h.[Name] as varchar(7)),
			cast(h.[Name] as varchar(7)),
			l.[Start],
			l.Id,
			null
		from mes.heats h
		left join cas.Processes l on l.HeatId = h.Id
		where 
		@heatName is null 
		or (
			h.[Name] like @searchTextString 
		)
		and l.HeatId is not null	
		order by l.[Start] desc		



		
		if (@heatName is not null)	-- When the user searches by heat, we need to show the last 20 heats 
			insert into @filterHeats
			select [value],[label],[processId] from @heatNames order by [Datetime] desc
		else if (@heatName is null)  	-- When the page is first opened, we need to get the last process
			insert into @filterHeats
			select top 1	
				[value] = h.[Name],
				[label] = h.[Name],
			    [processId] = p.Id
			from cas.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where [Start] is not null and [End] is not null order by p.[Start] desc	

		-- If the pikcer has a selected heat is needed to add this to the list
		if (@SelectedHeatName is not null)
		begin
			set @selectedHeatId = (select top 1 Id from mes.heats where [Name] = @SelectedHeatName)
			insert into @filterHeats
			values(
				 @SelectedHeatName 
				, @SelectedHeatName
				,(select top 1 Id from cas.Processes where HeatId = @selectedHeatId)
			)
		end
	
		select distinct([value]),[label],[processId] from @filterHeats
		
	end


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

	RETURN 0
END


go

USE [MES]
GO
/****** Object:  StoredProcedure [MES].[GetNavigatedHeat]    Script Date: 8/10/2022 18:42:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [MES].[GetNavigatedHeat]
	@heatName VARCHAR(10),
	@treatment INT  = 1,
	@direction SMALLINT,
	@Asset CHAR(3) = 'LMF'
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), '')
		+ ', @direction: ' + ISNULL(CAST(@direction AS VARCHAR), '')


	-----------------------
	-- Navigation Variables
	-----------------------
	declare @heatId int = (select Id from MES.Heats where [Name] = @heatName)

	declare @EAFProcessStart datetime = (select [Start] from EAF.Processes where HeatId = @heatId)
	declare @LMFProcessStart datetime = (select [Start] from LF.Processes where HeatId = @heatId and Treatment = @treatment)
	declare @CASProcessLadleOpen datetime -- for caster is different because we can have an UNK heat with heatId = NULL
	
	declare @newHeatName VARCHAR(10) = @heatName
	declare @newTreatment INT = 1
	---------------------------------------------------
	------------------- BEGIN QUERY -------------------
	---------------------------------------------------	
	IF (@Asset = 'EAF' or @Asset = 'LAB')
	BEGIN
		if (@direction = 1 and @EAFProcessStart is not null)
			select top 1 
				@newHeatName = isNULL(h.[Name],@heatName) 
			from eaf.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where p.[Start] > @EAFProcessStart order by p.[Start] asc

		if (@direction = -1 and @EAFProcessStart is not null)
			select top 1 
				@newHeatName = isNULL(h.[Name],@heatName) 
			from eaf.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where p.[Start] < @EAFProcessStart order by p.[Start] desc

		-- Result:
		select [HeatName] = cast(@newHeatName as varchar(7))

		return
	END		


	IF (@Asset = 'LMF')
	BEGIN
		if (@direction = 1 and @LMFProcessStart is not null)
			select top 1 
				@newHeatName = isNULL(h.[Name],@heatName) 
				,@newTreatment = p.Treatment
			from lf.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where p.[Start] > @LMFProcessStart order by p.[Start] asc

		if (@direction = -1 and @LMFProcessStart is not null)
			select top 1 
				@newHeatName = isNULL(h.[Name],@heatName) 
				,@newTreatment = p.Treatment
			from lf.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where p.[Start] < @LMFProcessStart order by p.[Start] desc

		-- Result:
		select 
			[HeatName] = cast(@newHeatName as varchar(7))
			,[Treatment] = @newTreatment

		return
	END		

	IF (@Asset = 'CAS')
	BEGIN
		-----------------------------
		--- GET CURRENT LadleOpen  --
		-----------------------------

		-- we try to find the cas process using the heatId
		select
			@CASProcessLadleOpen = p.[LadleOpen]
		from cas.Processes p 
		where p.HeatId = @heatId

		-- maybe the process exists as UNK heat
		if (@CASProcessLadleOpen is null)
		begin
			select
				@CASProcessLadleOpen = p.[LadleOpen]
			from cas.Processes p 
			where p.L1HeatName = @heatName
			
		end
		
		----------------------------------------------------------
		--- GET Next/Previous HeatName  ----------------------
		--  NOTE: The next/previous heat can have heatId null
		----------------------------------------------------------


		-- we try to find the cas process using the heatId
		if (@direction = 1 and @CASProcessLadleOpen is not null)
			select top 1 
				@newHeatName = h.[Name]
			from cas.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where p.[LadleOpen] > @CASProcessLadleOpen order by p.[LadleOpen] asc

		if (@direction = -1 and @CASProcessLadleOpen is not null)
			select top 1 
				@newHeatName = h.[Name]
			from cas.Processes p
			left join mes.heats h on h.Id = p.HeatId
			where p.[LadleOpen] < @CASProcessLadleOpen order by p.[LadleOpen] desc

		-- it means the next/previous heat is probably a UNK
		if (@newHeatName is null)
		begin
			if (@direction = 1 and @CASProcessLadleOpen is not null)
				select top 1 
					@newHeatName = p.L1HeatName
				from cas.Processes p
				where p.[LadleOpen] > @CASProcessLadleOpen order by p.[LadleOpen] asc

			if (@direction = -1 and @CASProcessLadleOpen is not null)
				select top 1 
					@newHeatName = p.L1HeatName
				from cas.Processes p
				where p.[LadleOpen] < @CASProcessLadleOpen order by p.[LadleOpen] desc
		end

		-- Result:
		-- if @newHeatName is null means that there is no next/previus heat in the database, so, we keep the same heat
		select [HeatName] = cast(isNULL(@newHeatName,@heatName) as varchar(7))

		return
	END		





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

	RETURN 0
END

go



select 
	distinct(SourceCode) as [value]
	,[label] = case SourceCode 
				when 'A' then 'Batches'
				when 'B' then 'Bags (Manual)'
				when 'L' then 'Lime'
				when 'C' then 'Carbon'
				when 'W' then 'Wirefeeders'
				when 'M' then 'Micro Alloys'
 				end
from LF.AlloysConsumption a