USE [IHBoxSystem]
GO
CREATE ROLE meltshopoverview_readWrite AUTHORIZATION dbo 
GO  

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

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

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

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


declare @parent int, @result_routeId INT
select @parent=id from [FrontEnd].[Menu] where [Name] = 'Ladles' and Parent is null

INSERT INTO [FrontEnd].[Routes]
		   ([Route]
		   ,[ModuleName]
		   ,[ModulePath]
		   ,[RequiredRole]
	   )
	 VALUES
		   (
		   '!/melt-shop-overview(/)'
		   , 'melt-shop-overview'
		   , 'custom-screens/melt-shop-overview/'
		   , 'meltshopoverview_readWrite'           
		   )
		   SET @result_routeId = @@IDENTITY
INSERT INTO [FrontEnd].[Menu]
		   ([Parent]
		   ,[Name]
		   ,[RouteID]
		   ,[SortOrder]
		   ,[Enabled]
		   ,[Visible]
		   ,[RequiredRole]
		   ,[Group]
		  )
	 VALUES
		   (
		   @parent
		   ,'Melt Shop Overview'
		   ,@result_routeId 
		   ,1
		   ,1
		   ,1
		   ,'meltshopoverview_readWrite'
		   ,NULL
		 )
GO

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

DECLARE @ModuleId INT

INSERT INTO [SECURITY].[Modules]
		   ([Code]
		   ,[Name])
	 VALUES
		   ('melt_shop_overview'
		   ,'Melt Shop Overview')

		  SET @ModuleId = @@IDENTITY

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

/*---------------------------------------------------------------------------------------------------------------------------------------------*/
USE [MES]
GO
/****** Object:  StoredProcedure [LAD].[GetLadlesLocationsStatus]    Script Date: 17 ago. 2020 11:45:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAD].[GetLadlesLocationsStatus]
AS
BEGIN

		select 
			[HeatName] = l.HeatNumber, 
			[LadleID] = LadleID, 
			[HeatsOnLining] = HeatsOnLining, 
			[LadlePositionID] = l.LadlePositionID, 
			[AllowedLocations] = (select AllowedLocations from LAD.GetAllowedPositions(l.LadlePositionID)),
			[MeltOrder] = h.MeltOrderNumber + '-' + cast(h.MeltOrderSequence as varchar),
			[GradeName] = m.GradeName,
			[AimLength]= m.AimLength,
			[HeatStatusID] = h.HeatStatusID,
			[HeatStatusName] = hs.[Name],
			[PositionName] = pos.[Name]
		from lad.ladles l
		left join lad.Heats h on h.HeatNumber = l.HeatNumber
		left join lad.MeltOrders m on h.MeltOrderNumber = m.MeltOrderNumber and h.MeltOrderSequence = m.MeltSequence
		left join lad.catHeatStatus hs on hs.HeatStatusID = h.HeatStatusID
		left join lad.catLadlePositions pos on pos.LadlePositionID = l.LadlePositionID
	
	

		select 
			LadlePostionID = 1,
			Name = 'HeatName',
			Value = (select top 1 h.name from eaf.Processes p 
						left join mes.heats h on h.Id =p.HeatId
						where p.FurnaceCode = 1 and p.EndTap is null
						order by p.Id desc
					)

		union all
		select 
			LadlePostionID = 1,
			Name = 'Power',
			Value = '1000'
			--Value = (select top 1 CAST(ISNULL(value,'0') as varchar) from ih.ltreal p where tagId = 1110 order by offset desc	
			--		) -- last value of Power EAF 1  

		union all
		select 
			LadlePostionID = 2,
			Name = 'HeatName',
			Value = (select top 1 h.name from eaf.Processes p 
						left join mes.heats h on h.Id =p.HeatId
						where p.FurnaceCode = 2 and p.EndTap is null
						order by p.Id desc
					)

		union all
		select 
			LadlePostionID = 2,
			Name = 'Power',
			Value = '1520'
			--Value = (select top 1 CAST(ISNULL(value,'0') as varchar) from ih.ltreal p where tagId = 1112 order by offset desc	
			--		) -- last value of Power EAF 1  



END


GO
/*---------------------------------------------------------------------------------------------------------------------------------------------*/
USE [MES]
GO
/****** Object:  StoredProcedure [LAD].[ChangeLadlePositionWeb]    Script Date: 17 ago. 2020 08:25:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAD].[ChangeLadlePositionWeb]
@LadleId int,
@LadlePositionId int
AS
BEGIN

	DECLARE @result INT,
			@outputMessage VARCHAR(MAX)


	IF (@LadlePositionId = 70) 
	BEGIN
			select top 1 
				@LadlePositionId = p.LadlePositionID 
			from lad.catLadlePositions p
			left join lad.Ladles l on l.LadlePositionID = p.LadlePositionID
			where p.LadlePositionID between 70 and 84  
					and l.LadleID is null
			order by l.LadlePositionID 
	END

	EXEC @result = LAD.ChangeLadlePosition
		@LadleID = @LadleId,
		@LadlePositionID = @LadlePositionId,
		@EventSourceID = 41,
		@message = @outputMessage output

	IF (@result <> 0)
		RAISERROR(@outputMessage, 11, 1)
	ELSE
		SELECT Id = @LadleId
	
END
GO
/*--------------------------------------------------------------------------------------------------------------------------------------------------------*/
USE [MES]
GO
/****** Object:  UserDefinedFunction [IH].[GetLastTagChange]    Script Date: 17 ago. 2020 11:38:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER FUNCTION  LAD.GetAllowedPositions
(	
	@LadlePositionID INT

)
RETURNS @dataT TABLE
(
	AllowedLocations VARCHAR(200)
)
AS
BEGIN

	INSERT INTO @dataT(AllowedLocations)
	SELECT 
	
		STUFF((SELECT ',' + CAST(LadlePositionIDTo AS VARCHAR(10)) [text()]
         FROM lad.catLadlePositionAllowedMoves
         WHERE LadlePositionIDFrom = @LadlePositionID
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
	FROM lad.catLadlePositionAllowedMoves t
	GROUP BY LadlePositionIDFrom

	RETURN
END
GO


/*--------------------------------------------------------READ ONLY ROLE------------------------------------------------------------------------------------*/

USE [IHBoxSystem]
GO
CREATE ROLE meltshopoverview_read AUTHORIZATION dbo 
GO  

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

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


declare @ModuleId int

select top 1 @ModuleId= Id from [SECURITY].[Modules]
where [Code] = 'melt_shop_overview'


insert into [SECURITY].ModuleActions(ModuleId,Code,[Name],[Role])
values (
	@ModuleId,
	'read',
	'Read',
	'meltshopoverview_read'
)



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


update FrontEnd.Menu
set RequiredRole = 'meltshopoverview_read'
where RequiredRole= 'meltshopoverview_readWrite'

update FrontEnd.[Routes]
set RequiredRole = 'meltshopoverview_read'
where RequiredRole= 'meltshopoverview_readWrite'




USE [MES]
GO
/****** Object:  StoredProcedure [LAD].[GetMeltshopOverviewWeb]    Script Date: 31 ene. 2021 09:51:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAD].[GetMeltshopOverviewWeb]
AS
BEGIN
		--------------------------------------
		-- Tables and Variables Declaration
		--------------------------------------
		declare @mainTable table
		(
		[Ladle]			int                
		,[Position]		varchar(100)                
		,[Melt Order]	varchar(100)                 
		,[Heat]         varchar(100)                
		,[Status]       varchar(100)                
		,[Grade]        varchar(100)                
		,[Last Temperature] varchar(100)            
		,[Last Sample]      varchar(100)            
		,[Contact Time]     varchar(100)           
		,[Steel Weight]    	real	
		-- Mandatory columns and names
		,[_LadleID]        int             
		,[_LadlePositionID] int            
		,[_HeatsOnLining]      int         
		,[_AllowedLocations]      varchar(500)      
		,[_HeatName]              varchar(10)   

		,[_ScreenPositionID]      varchar(10)  --only available for Caster Positions    
		,[_ScreenPositionLabel]   varchar(50)  --only available for Caster Positions
		--Others
		,[_AimLength]		varchar(50)
		,[_HeatStatusID]    int            
		)
			   
		declare @Attributes table( LadlePositionID varchar(10), Name varchar(10), Value varchar(20))	
		declare @VisualAttributes table( LadlePositionID varchar(10), Name varchar(10), Value varchar(10))
		
		--------------------
		-- Casters Variables
		--------------------
		declare @castersLabels table (LadlePositionId int, [Name] varchar(50))
		insert into @castersLabels
		values ((32),('Cas 1 Arm 1 In')),((33),('Cas 1 Arm 1 Out')),((34),('Cas 1 Arm 2 In')),((35),('Cas 1 Arm 2 Out'))
				,((42),('Cas 2 Arm 1 In')),((43),('Cas 2 Arm 1 Out')),((44),('Cas 2 Arm 2 In')),((45),('Cas 2 Arm 2 Out'))


				
				
		-- Caster 1
		declare @ShowCaster1Positions table (LadlePositionId int, [Name] varchar(50),ScreenPosition int)
		declare @TagId_Cas1_TurretArmAtCast int = (select top 1 Id from IH.Tags where [Name] = 'CAS1.COMMON.Global.AT_Turret_Arm_At_Cast')
		declare @Cas1_Arm_At_Cast int
		declare @caster1Pos1 varchar(10)		
		declare @caster1Pos2 varchar(10)
		declare @speedCas1 varchar(20)

		
		declare @Cas1Arm1ScreenPosition varchar(10)
		declare @Cas1Arm2ScreenPosition varchar(10)
		declare @UnkLadleCas1Arm1 bit
		declare @UnkLadleCas1Arm2 bit
		
		-- Caster 2
		declare @ShowCaster2Positions table (LadlePositionId int, [Name] varchar(50),ScreenPosition int)
		declare @TagId_Cas2_TurretArmAtCast int = (select top 1 Id from IH.Tags where [Name] = 'CAS1.COMMON.Global.AT_Cas2_Turret_Arm_At_Cast')
		declare @Cas2_Arm_At_Cast int
		declare @caster2Pos1 varchar(10)			
		declare @caster2Pos2 varchar(10)
		declare @speedCas2 varchar(20)
		
		
		declare @Cas2Arm1ScreenPosition varchar(10)
		declare @Cas2Arm2ScreenPosition varchar(10)
		declare @UnkLadleCas2Arm1 bit
		declare @UnkLadleCas2Arm2 bit

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


		-------------------
		-- Main Grid Table
		-------------------
		-- Grid columns without "_"
		insert into @mainTable
		select 
			--Grid Columns
			[Ladle] = LadleID,
			[Position] = pos.[Name],
			[Melt Order] = h.MeltOrderNumber + '-' + cast(h.MeltOrderSequence as varchar) + '^melt-orders' ,
			[Heat] = left(l.HeatNumber,7) + '^eaf-heat-report/'+left(l.HeatNumber,7) ,
			[Status] = hs.[Name],
			[Grade] = m.GradeName,
			[Last Temperature] = cast(cast(h.LastTemperature as int)  as varchar) + ' @ ' + CONVERT(VARCHAR, IH.ToLocal(h.LastTemperatureTime, 'EDTIND'),108), -- convert to hh mm ,
			[Last Sample] = h.ChemistrySample + '^chemistry-samples-report/'+left(l.HeatNumber,7),
			[Contact Time] = convert(char(8),dateadd(s,datediff(s,h.TapTime,GETUTCDATE()),'1900-1-1'),8),
			[Steel Weight] = h.NetWeight,


			-- Mandatory columns and names
			[_LadleID] = LadleID,
			[_LadlePositionID] = l.LadlePositionID, 
			[_HeatsOnLining] = HeatsOnLining,
			[_AllowedLocations] =  pos.AllowedMoves,
			[_HeatName] = left(l.HeatNumber,7),


			[_ScreenPositionID] = null, 	--only available for Caster Positions
			[_ScreenPositionLabel] = null,  --only available for Caster Positions

			
			--Others
			[_AimLength]= m.AimLength,
			[_HeatStatusID] = h.HeatStatusID

		from lad.ladles l
		left join lad.Heats h on h.HeatNumber = l.HeatNumber
		left join lad.MeltOrders m on h.MeltOrderNumber = m.MeltOrderNumber
		left join lad.catHeatStatus hs on hs.HeatStatusID = h.HeatStatusID
		left join lad.catLadlePositions pos on pos.LadlePositionID = l.LadlePositionID
		--where l.HeatNumber is not null
		order by l.LadlePositionID asc


		---------------------------------------------------------------------
		------------------------ Position Attributes ------------------------
		---------------------------------------------------------------------
		insert into @Attributes select top 1 01, 'MWH',  [Value] = left(cast(cast(z.Value/1000.0 as decimal(10,1)) as varchar),10) from IH.LTReal z where z.TagId=159729 order by offset desc
		insert into @Attributes select top 1 03, 'MWH',  [Value] = left(cast(cast(z.Value/1000.0 as decimal(10,1)) as varchar),10) from IH.LTReal z where z.TagId=160357 order by offset desc
		insert into @Attributes select top 1 10, 'Time', [Value] = convert(char(8),dateadd(s,datediff(s,z.Start,GETUTCDATE()),'1900-1-1'),8) from LF.Processes z where [end] is null and z.LadleFurnaceCode=1 order by start desc
		insert into @Attributes select top 1 20, 'Time', [Value] = convert(char(8),dateadd(s,datediff(s,z.Start,GETUTCDATE()),'1900-1-1'),8) from LF.Processes z where [end] is null and z.LadleFurnaceCode=2 order by start desc
		insert into @Attributes select top 1 11, 'Time', [Value] = convert(char(8),dateadd(s,datediff(s,z.Start,GETUTCDATE()),'1900-1-1'),8) from vtd.Processes z where [end] is null and z.VTDTankCode=1 order by start desc
		insert into @Attributes select top 1 31, 'Time', [Value] = convert(char(8),dateadd(s,datediff(s,z.LastPositionChange,GETUTCDATE()),'1900-1-1'),8) from LAD.Ladles z where [LadlePositionID] = 31 		insert into @Attributes select top 1 12, 'Time', [Value] = convert(char(8),dateadd(s,datediff(s,z.Start,GETUTCDATE()),'1900-1-1'),8) from vtd.Processes z where [end] is null and z.VTDTankCode=2 order by start desc
		
	
		--- EAF status goes here because they might not have a ladle
		declare @EAFStatus table (FurnaceCode int, HeatNumber varchar(10), Status varchar(20), LadleId int,  start datetime,  LadleInEAF int, LadleInTap int )

		insert into @EAFStatus
			select
			furnaceCode,
			heatNumber = case when h.heatstatusid <> 7 then h.HeatNumber else 'No Heat' end, 
			case when h.heatstatusid = 7 then 'Idle' else hs.Name end, 
			l.LadleID,
			case when h.HeatStatusID = 7 then ep.EndTap else ep.Start end
			, (select ladleid from LAD.Ladles z where z.LadlePositionID = case when furnacecode=1 then 1 else 3 end )
	     	, (select ladleid from LAD.Ladles z where z.LadlePositionID = case when furnacecode=1 then 2 else 4 end )
		from lad.Heats h
			left join mes.heats mh on mh.Name= h.HeatNumber
			left join lad.catHeatStatus hs on h.HeatStatusID = hs.HeatStatusID
			left join lad.ladles l on l.HeatNumber = h.HeatNumber
			left join eaf.Processes ep on ep.heatid = mh.id
		where 
			h.HeatStatusID in (2,3,4,5,6,7)
			and  (
			      ep.id = (select max(z.id) from EAF.Processes z where z.furnaceCode=1) or 
			      ep.id = (select max(z.id) from EAF.Processes z where z.furnaceCode=2)
			  )

		-- Repeat EAF status as attribute 
		insert into @Attributes select case when FurnaceCode=1 then 1 else 3 end ,'HeatNumber', left(HeatNumber,7) from @EAFStatus where LadleId is null
		insert into @Attributes select case when FurnaceCode=1 then 1 else 3 end ,'Status', left(replace([Status],'EAF',''),10) from @EAFStatus
		insert into @Attributes select case when FurnaceCode=1 then 1 else 3 end ,'Time', convert(char(8),dateadd(s,datediff(s,Start,GETUTCDATE()),'1900-1-1'),8) from @EAFStatus
		insert into @Attributes select case when FurnaceCode=1 then 1 else 3 end ,'WARNING', 'No Ladle' from @EAFStatus where  [Status] in ('EAF REFINE','EAF TAPPING') and LadleInEAF is null and Ladleid is null
		insert into @Attributes select case when FurnaceCode=1 then 1 else 3 end ,'WARNING', 'Pos taken' from @EAFStatus where [Status] in ('EAF REFINE','EAF TAPPING') and LadleInTap is not null  and Ladleid is null


		---------------------------------------------------------------------
		------------------------ Visual Attributes --------------------------
		---------------------------------------------------------------------
		
		-- visual attributes for EAF Ladle problems
		insert into @VisualAttributes 
		select
		case when furnacecode=1 then 1 else 3 end
		,'WARNING'
		,case when [status] = 'EAF TAPPING'  then '#f6A0A0' else '#f6f080' end
		from @EAFStatus where [Status] in ('EAF REFINE','EAF TAPPING') and Ladleid is null and (LadleInEAF is null or LadleinTap is not null)

		--------------------------------------------------------------------------------
		--------------------------------- CASTERS --------------------------------------
		--------------------------------------------------------------------------------

				-------------------------------------------------
				-- Get where Casters Positions will be allocated
				-------------------------------------------------
				----------
				--Caster 1
				----------

				if exists (select 1 from @mainTable where _LadlePositionID=32 or _LadlePositionID=35)
				begin
					insert into @ShowCaster1Positions(LadlePositionID,ScreenPosition)
					values (35,1),(32,2)
		
					if exists (select 1 from @mainTable where _LadlePositionID=33 or _LadlePositionID=34)
					insert into @ShowCaster1Positions(LadlePositionID,ScreenPosition)
					values (33,3),(34,4)
	
				end
				else if exists (select 1 from @mainTable where _LadlePositionID=33 or _LadlePositionID=34)
				begin
					insert into @ShowCaster1Positions(LadlePositionID,ScreenPosition)
					values (33,1),(34,2)
	
				end
				else
				begin
					-- Use Tag
					if (select top 1 [value] from IH.LTReal where TagId = @TagId_Cas1_TurretArmAtCast order by Offset desc) = 1
					insert into @ShowCaster1Positions(LadlePositionId,ScreenPosition) values (35,1),(32,2)

					else if (select top 1 [value] from IH.LTReal where TagId = @TagId_Cas1_TurretArmAtCast order by Offset desc) = 2
					insert into @ShowCaster2Positions(LadlePositionId,ScreenPosition)	values (34,1),(33,2)
					
					else insert into @ShowCaster2Positions(LadlePositionId,ScreenPosition)	values (35,1),(32,2),(33,3),(34,4)
				end

				update sw
					set [Name] = (select z.[Name] from @castersLabels z where z.LadlePositionID = sw.LadlePositionId)
				from @ShowCaster1Positions sw

				----------
				--Caster 2
				----------
				if exists (select 1 from @mainTable where _LadlePositionID=42 or _LadlePositionID=45)
				begin
					insert into @ShowCaster2Positions(LadlePositionID,ScreenPosition)
					values (45,1),(42,2)
		
					if exists (select 1 from @mainTable where _LadlePositionID=43 or _LadlePositionID=44)
					insert into @ShowCaster2Positions(LadlePositionID,ScreenPosition)
					values (43,3),(44,4)
	
				end
				else if exists (select 1 from @mainTable where _LadlePositionID=43 or _LadlePositionID=44)
				begin
					insert into @ShowCaster2Positions(LadlePositionID,ScreenPosition)
					values (43,1),(44,2)
	
				end
				else
				begin
					-- Use Tag
					if (select top 1 [value] from IH.LTReal where TagId = @TagId_Cas2_TurretArmAtCast order by Offset desc) = 1
					insert into @ShowCaster2Positions(LadlePositionId,ScreenPosition) values (45,1),(42,2)
					
					else if (select top 1 [value] from IH.LTReal where TagId = @TagId_Cas2_TurretArmAtCast order by Offset desc) = 2
					insert into @ShowCaster2Positions(LadlePositionId,ScreenPosition)	values (44,1),(43,2)
					
					else insert into @ShowCaster2Positions(LadlePositionId,ScreenPosition)	values (45,1),(42,2),(43,3),(44,4)
				end
				
				update sw
					set [Name] = (select z.[Name] from @castersLabels z where z.LadlePositionID = sw.LadlePositionId)
				from @ShowCaster2Positions sw

				-------------------------------------------------------------------
				-- IMPORTANT - Update Main Table with The Screen Casters Positions
				-------------------------------------------------------------------

				update mt set
					mt.[_ScreenPositionID]  = cast(s1.ScreenPosition as varchar(1)) + 'cas1' 
					,mt.[_ScreenPositionLabel] =  s1.[Name]

				from @mainTable mt
				join @ShowCaster1Positions s1 on s1.LadlePositionId = mt._LadlePositionID
				where mt._LadlePositionID in (select LadlePositionId from @ShowCaster1Positions)
	
				update mt set  
					mt.[_ScreenPositionID]  = cast(s2.ScreenPosition as varchar(1)) + 'cas2' 
					,mt.[_ScreenPositionLabel] =  s2.[Name]
				from @mainTable mt
				join @ShowCaster2Positions s2 on s2.LadlePositionId = mt._LadlePositionID
				where mt._LadlePositionID in (select LadlePositionId from @ShowCaster2Positions)


				-------------------------------------------------
				--------------- Get Casters Speeds --------------
				-------------------------------------------------				

				select @caster1Pos1 = cast(s1.ScreenPosition as varchar(1)) + 'cas1'  from @ShowCaster1Positions s1 where s1.ScreenPosition = 1
				select @caster2Pos1 = cast(s2.ScreenPosition as varchar(1)) + 'cas2'  from @ShowCaster2Positions s2 where s2.ScreenPosition = 1				
				select @caster1Pos2 = cast(s1.ScreenPosition as varchar(1)) + 'cas1'  from @ShowCaster1Positions s1 where s1.ScreenPosition = 2
				select @caster2Pos2 = cast(s2.ScreenPosition as varchar(1)) + 'cas2'  from @ShowCaster2Positions s2 where s2.ScreenPosition = 2

				set @speedCas1 = (select top 1 left(cast(cast(z.Value as decimal(10,0)) as varchar),10) from IH.LTReal z where z.TagId=170225 order by offset desc)
	                       + ' '+ (select top 1 left(cast(cast(z.Value as decimal(10,0)) as varchar),10) from IH.LTReal z where z.TagId=170241 order by offset desc)
	                       + ' '+ (select top 1 left(cast(cast(z.Value as decimal(10,0)) as varchar),10) from IH.LTReal z where z.TagId=170257 order by offset desc)
	                       + ' '+ (select top 1 left(cast(cast(z.Value as decimal(10,0)) as varchar),10) from IH.LTReal z where z.TagId=170273 order by offset desc)
				
				set @speedCas2 = (select top 1 left(cast(cast(z.Value as decimal(10,0)) as varchar),10) from IH.LTReal z where z.TagId= 170829 order by offset desc)
	                       + ' '+ (select top 1 left(cast(cast(z.Value as decimal(10,0)) as varchar),10) from IH.LTReal z  where z.TagId= 170830 order by offset desc)
	                       + ' '+ (select top 1 left(cast(cast(z.Value as decimal(10,0)) as varchar),10) from IH.LTReal z  where z.TagId= 170831 order by offset desc)
	                       + ' '+ (select top 1 left(cast(cast(z.Value as decimal(10,0)) as varchar),10) from IH.LTReal z  where z.TagId= 170832 order by offset desc)
				
				
				insert into @Attributes select top 1 @caster1Pos1, 'Sp',  [Value] = @speedCas1
				insert into @Attributes select top 1 @caster2Pos1, 'Sp',  [Value] = @speedCas2

				
				-------------------------------------------------
				--------------- Get Casters ALARMS --------------
				-------------------------------------------------	
				select top 1 @UnkLadleCas1Arm1=[Value] from ih.LTDigital where tagId = 170825 order by offset desc
				select top 1 @UnkLadleCas1Arm2=[Value] from ih.LTDigital where tagId = 170826 order by offset desc
				select top 1 @UnkLadleCas2Arm1=[Value] from ih.LTDigital where tagId = 170827 order by offset desc
				select top 1 @UnkLadleCas2Arm2=[Value] from ih.LTDigital where tagId = 170828 order by offset desc
				

				-- Get where the Arm 1 is allocated (can be in or out)
				select top 1 @Cas1Arm1ScreenPosition = cast(ScreenPosition as varchar(1)) + 'cas1'  from @ShowCaster1Positions where [LadlePositionId] in (32,33) order by ScreenPosition asc
				-- Get where the Arm 2 is allocated (can be in or out)
				select top 1 @Cas1Arm2ScreenPosition = cast(ScreenPosition as varchar(1)) + 'cas1'  from @ShowCaster1Positions where [LadlePositionId] in (34,35) order by ScreenPosition asc

				-- Get where the Arm 1 is allocated (can be in or out)
				select top 1 @Cas2Arm1ScreenPosition = cast(ScreenPosition as varchar(1)) + 'cas2'  from @ShowCaster1Positions where [LadlePositionId] in (42,43) order by ScreenPosition asc
				-- Get where the Arm 2 is allocated (can be in or out)
				select top 1 @Cas2Arm2ScreenPosition = cast(ScreenPosition as varchar(1)) + 'cas2'  from @ShowCaster1Positions where [LadlePositionId] in (44,45) order by ScreenPosition asc
		
				if @UnkLadleCas1Arm1 = 1 insert into @Attributes select @Cas1Arm1ScreenPosition,'WARNING', 'Unk Ladle'
				if @UnkLadleCas1Arm2 = 1 insert into @Attributes select @Cas1Arm2ScreenPosition,'WARNING', 'Unk Ladle'

				if @UnkLadleCas2Arm1 = 1 insert into @Attributes select @Cas2Arm1ScreenPosition,'WARNING', 'Unk Ladle'
				if @UnkLadleCas2Arm2 = 1 insert into @Attributes select @Cas2Arm2ScreenPosition,'WARNING', 'Unk Ladle'



				
				-----------------------
				-- Position Attributes
				-----------------------
				insert into @Attributes select top 1 @caster1Pos1, 'Time', [Value] = convert(char(8),dateadd(s,datediff(s,z.Start,GETUTCDATE()),'1900-1-1'),8) from cas.Processes z where [end] is null and z.CasterCode=1 order by start desc
				insert into @Attributes select top 1 @caster1Pos1, 'Weight',  [Value] = left(cast(cast(z.Value as decimal(10,0)) as varchar),10) from IH.LTReal z where z.TagId=170200 order by offset desc
				insert into @Attributes select top 1 @caster1Pos2, 'Time', [Value] = convert(char(8),dateadd(s,datediff(s,z.LastPositionChange,GETUTCDATE()),'1900-1-1'),8) from LAD.Ladles z where [LadlePositionID] = 33 
	
				-----------------------
				-- Visual Attributes
				-----------------------
				-- Warnings for turret problems

				-----------
				-- Caster 1
				-----------
				declare @Warning_Cas1Arm1In	  varchar(10)
				declare @Warning_Cas1Arm2In   varchar(10)
				declare @Warning_Cas1Arm1Out  varchar(10)
				declare @Warning_Cas1Arm2Out  varchar(10)

				if (select count(*) from lad.Ladles where  LadlePositionid=34 or LadlePositionId=35) > 1
				begin
						select @Warning_Cas1Arm2In = cast(ScreenPosition as varchar(1)) + 'cas1'  from @ShowCaster1Positions where [LadlePositionId] = 34
						select @Warning_Cas1Arm2Out = cast(ScreenPosition as varchar(1)) + 'cas1'  from @ShowCaster1Positions where [LadlePositionId] = 35

						insert into @VisualAttributes values(@Warning_Cas1Arm2In,'WARNING','#f6ff67')
						insert into @VisualAttributes values(@Warning_Cas1Arm2In,'WARNING','#f6ff67')
				end

				if (select count(*) from lad.Ladles where  LadlePositionid=32 or LadlePositionId=33) > 1
				begin
					select @Warning_Cas1Arm1In = cast(ScreenPosition as varchar(1)) + 'cas1'  from @ShowCaster1Positions where [LadlePositionId] = 32
					select @Warning_Cas1Arm1Out = cast(ScreenPosition as varchar(1)) + 'cas1'  from @ShowCaster1Positions where [LadlePositionId] = 33


					insert into @VisualAttributes values(@Warning_Cas1Arm1In,'WARNING','#f6ff67')
					insert into @VisualAttributes values(@Warning_Cas1Arm1Out,'WARNING','#f6ff67')
				end

				----------------------------------
				---- Finish Casters Configuration
				---------------------------------

	




		-----------------
		-- Return Tables
		-----------------
		select * from @mainTable

		select * from @Attributes order by LadlePositionID, [Name] 
	
		select * from @EAFStatus 

		select * from @VisualAttributes		

		select * from @ShowCaster1Positions
		
		select * from @ShowCaster2Positions



END
GO


/*----------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------*/
/*-------------------------------------------------11 May 2021 ---------------------------------------------------------*/
/*-------------------------------------------------11 May 2021 ---------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------*/


USE [IHBoxSystem]
GO
EXEC sp_addextendedproperty  @level0type = N'USER' ,@level0name = [lad_ladlesdashboard_write] ,@name = N'securedsp-LAD.UpsertHeatTareAndGrossWeightsWeb'  		,@value =  null
 GO

 USE [MES]
GO

CREATE PROCEDURE [LAD].[UpsertHeatTareAndGrossWeightsWeb]
	@HeatNumber char(10), 
	@GrossWeight [decimal](18, 2) NULL,
	@TareWeight [decimal](18, 2) NULL
	
AS
BEGIN

	-------------
	-- Variables
	-------------
	declare @EventSourceID int = 41 
	declare @message varchar(150)

	-------------
	-- Query
	-------------

	if @GrossWeight is not null and @TareWeight is not null and @GrossWeight < @TareWeight
	begin
		select @message = 'Weights are wrong ' 
		return 3
	end
	else
		UPDATE [LAD].[Heats]
			   SET [GrossWeight] = @GrossWeight
				  ,[TareWeight] = @TareWeight
			 WHERE HeatNumber =@HeatNumber


	IF (@message is not null)
		RAISERROR(@message, 11, 1)
	ELSE
		SELECT Id = @HeatNumber
	
	
END


GO





/*----------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------*/
/*-------------------------------------------------14 May 2021 ---------------------------------------------------------*/
/*-------------------------------------------------14 May 2021 ---------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------*/

update lad.catLadlePositions
set [ReturnDestination] = 1
 where Name like '%LMF%'

 update lad.catLadlePositions
set [ReturnDestination] = 1
 where Name like '%VTD%'

 update lad.catLadlePositions
set [ReturnSource] = 1
 where Name like '%CASTER%'


 USE [MES]
GO
/****** Object:  StoredProcedure [LAD].[GetLadlePositions]    Script Date: 14 may. 2021 11:41:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAD].[GetLadlePositions]
@onlyAvailablePositions BIT = 0
,@onlyAvailableDestinations BIT = 0
AS
BEGIN

	SELECT
		Id = LadlePositionID,
		Name
	FROM LAD.catLadlePositions
	WHERE
		@onlyAvailableDestinations = 0 
		AND (
			@onlyAvailablePositions = 0
			OR
			LadlePositionID  not IN (SELECT LadlePositionID FROM LAD.Ladles where not LadlePositionID is null)
			or
			MultipleLadles=1
		) 
		OR ReturnDestination = 1

END

GO


USE [MES]
GO
/****** Object:  StoredProcedure [LAD].[GetMeltshopOverviewWeb]    Script Date: 14 may. 2021 11:19:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAD].[GetMeltshopOverviewWeb]
AS
BEGIN
		--------------------------------------
		-- Tables and Variables Declaration
		--------------------------------------
		declare @mainTable table
		(
		[Ladle]			int                
		,[Position]		varchar(100)                
		,[Melt Order]	varchar(100)                 
		,[Heat]         varchar(100)                
		,[Status]       varchar(100)                
		,[Grade]        varchar(100)                
		,[Last Temperature] varchar(100)            
		,[Last Sample]      varchar(100)            
		,[Contact Time]     varchar(100)           
		,[Steel Weight]    	real	
		-- Mandatory columns and names
		,[_LadleID]        int             
		,[_LadlePositionID] int            
		,[_HeatsOnLining]      int         
		,[_AllowedLocations]      varchar(500)      
		,[_HeatName]              varchar(10)   

		,[_ScreenPositionID]      varchar(10)  --only available for Caster Positions    
		,[_ScreenPositionLabel]   varchar(50)  --only available for Caster Positions
		--Others
		,[_AimLength]		varchar(50)
		,[_HeatStatusID]    int  
		,[_ShowInGrid]  bit   
		,[_Order] int

		--Ladle Dashboard
		,[_Option_ChangePosition] bit
		,[_Option_AssignHeat] bit
		,[_Option_ChangeWeights] bit
		,[_Option_ReturnLadle] bit
		,[_Option_ReLadle] bit
		,[_Option_PourBack] bit
		,[_Option_AssignMeltOrder] bit
	
		,[_TareWeight] float
		,[_SlagWeight] float
		,[_NetWeight] float
		,[_AlloyWeight] float

		,[_LastTemperature] float
		,[_LastChemistry]  varchar(20)
		
		,[_MeltOrder] varchar(20)
		,[_GradeName] varchar(20)
		,[_Dim] varchar(20)
		,[_InSource] bit
		)
			   
		declare @Attributes table( LadlePositionID varchar(10), Name varchar(10), Value varchar(20))	
		declare @VisualAttributes table( LadlePositionID varchar(10), Name varchar(10), Value varchar(10))
		
		--------------------
		-- Casters Variables
		--------------------
		declare @castersLabels table (LadlePositionId int, [Name] varchar(50))
		insert into @castersLabels
		values ((32),('Cas 1 Arm 1 In')),((33),('Cas 1 Arm 1 Out')),((34),('Cas 1 Arm 2 In')),((35),('Cas 1 Arm 2 Out'))
				,((42),('Cas 2 Arm 1 In')),((43),('Cas 2 Arm 1 Out')),((44),('Cas 2 Arm 2 In')),((45),('Cas 2 Arm 2 Out'))


				
				
		-- Caster 1
		declare @ShowCaster1Positions table (LadlePositionId int, [Name] varchar(50),ScreenPosition int)
		declare @TagId_Cas1_TurretArmAtCast int = (select top 1 Id from IH.Tags where [Name] = 'CAS1.COMMON.Global.AT_Turret_Arm_At_Cast')
		declare @Cas1_Arm_At_Cast int
		declare @caster1Pos1 varchar(10)		
		declare @caster1Pos2 varchar(10)
		declare @speedCas1 varchar(20)
		declare @timeRemainingCas1 decimal(10,2)
		
		declare @Cas1Arm1ScreenPosition varchar(10)
		declare @Cas1Arm2ScreenPosition varchar(10)
		declare @UnkLadleCas1Arm1 bit
		declare @UnkLadleCas1Arm2 bit
		
		-- Caster 2
		declare @ShowCaster2Positions table (LadlePositionId int, [Name] varchar(50),ScreenPosition int)
		declare @TagId_Cas2_TurretArmAtCast int = (select top 1 Id from IH.Tags where [Name] = 'CAS1.COMMON.Global.AT_Cas2_Turret_Arm_At_Cast')
		declare @Cas2_Arm_At_Cast int
		declare @caster2Pos1 varchar(10)			
		declare @caster2Pos2 varchar(10)
		declare @speedCas2 varchar(20)
		declare @timeRemainingCas2 decimal(10,2)
		
		
		declare @Cas2Arm1ScreenPosition varchar(10)
		declare @Cas2Arm2ScreenPosition varchar(10)
		declare @UnkLadleCas2Arm1 bit
		declare @UnkLadleCas2Arm2 bit

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


		-------------------
		-- Main Grid Table
		-------------------
		-- Grid columns without "_"
		insert into @mainTable
		select 
			--Grid Columns
			[Ladle] = LadleID,
			[Position] = pos.[Name],
			[Melt Order] = h.MeltOrderNumber + '-' + cast(h.MeltOrderSequence as varchar) + '^melt-orders' ,
			[Heat] = left(l.HeatNumber,7) + '^eaf-heat-report/'+left(l.HeatNumber,7) ,
			[Status] = hs.[Name],
			[Grade] = m.GradeName,
			[Last Temperature] = cast(cast(h.LastTemperature as int)  as varchar) + ' @ ' + CONVERT(VARCHAR, IH.ToLocal(h.LastTemperatureTime, 'EDTIND'),108), -- convert to hh mm ,
			[Last Sample] = h.ChemistrySample + '^chemistry-samples-report/'+left(l.HeatNumber,7),
			[Contact Time] = convert(char(8),dateadd(s,datediff(s,h.TapTime,GETUTCDATE()),'1900-1-1'),8),
			[Steel Weight] = h.NetWeight,


			-- Mandatory columns and names
			[_LadleID] = LadleID,
			[_LadlePositionID] = l.LadlePositionID, 
			[_HeatsOnLining] = HeatsOnLining,
			[_AllowedLocations] =  pos.AllowedMoves + ',1cas1'+ ',2cas1'+ ',3cas1'+ ',4cas1'+ ',1cas2'+ ',2cas2'+ ',3cas2'+ ',4cas2',
			[_HeatName] = left(l.HeatNumber,7),


			[_ScreenPositionID] = null, 	--only available for Caster Positions
			[_ScreenPositionLabel] = null,  --only available for Caster Positions

			
			--Others
			[_AimLength]= m.AimLength,
			[_HeatStatusID] = h.HeatStatusID
			
			
			,[_ShowInGrid] = case when l.HeatNumber is null or DATEDIFF(HOUR,(select top 1 z.[datetime] from lad.LadleEvents z where z.LadleID =l.LadleID order by z.[DateTime] desc ),IH.ToLocal(GETUTCDATE(),'EDTIND')) > 3 then 0 else 1 end
			,[_Order] = l.LadlePositionID

				--Ladle Dashboard
			,[_Option_ChangePosition] = CAST(0 AS BIT)
			,[_Option_AssignHeat] = CAST(0 AS BIT)
			,[_Option_ChangeWeights] = CAST(0 AS BIT)
			,[_Option_ReturnLadle] = CAST(0 AS BIT)
			,[_Option_ReLadle] = CAST(0 AS BIT)
			,[_Option_PourBack] = CAST(0 AS BIT)
			,[_Option_AssignMeltOrder] = CAST(1 AS BIT)
	
			,[_TareWeight] = h.TareWeight
			,[_SlagWeight] = h.SlagWeight
			,[_NetWeight] = h.NetWeight
			,[_AlloyWeight] = h.AlloyWeight

			,[_LastTemperature] = h.LastTemperature
			,[_LastChemistry] =  h.ChemistrySample

			
			,[_MeltOrder] = LAD.GetMeltOrderString(h.MeltOrderNumber,h.MeltOrderSequence)
			,[_GradeName] = g.GradeName
			,[_Dim]= m.MaterialSize
			,[_InSource] = pos.ReturnSource
		from lad.ladles l
		left join lad.Heats h on h.HeatNumber = l.HeatNumber
		left join lad.MeltOrders m on h.MeltOrderNumber = m.MeltOrderNumber
		left join lad.catHeatStatus hs on hs.HeatStatusID = h.HeatStatusID
		left join lad.catLadlePositions pos on pos.LadlePositionID = l.LadlePositionID	
		left join LAD.Grades g on m.GradeName = g.GradeName
		--where l.HeatNumber is not null 		
		--order by l.LadlePositionID asc
		
	

		---------------------------------------------------------------------
		------------------------ Position Attributes ------------------------
		---------------------------------------------------------------------
		insert into @Attributes select top 1 01, 'MWH',  [Value] = left(cast(cast(z.Value/1000.0 as decimal(10,1)) as varchar),10) from IH.LTReal z where z.TagId=159729 order by offset desc
		insert into @Attributes select top 1 03, 'MWH',  [Value] = left(cast(cast(z.Value/1000.0 as decimal(10,1)) as varchar),10) from IH.LTReal z where z.TagId=160357 order by offset desc
		insert into @Attributes select top 1 10, 'Time', [Value] = convert(char(8),dateadd(s,datediff(s,z.Start,GETUTCDATE()),'1900-1-1'),8) from LF.Processes z where [end] is null and z.LadleFurnaceCode=1 order by start desc
		insert into @Attributes select top 1 20, 'Time', [Value] = convert(char(8),dateadd(s,datediff(s,z.Start,GETUTCDATE()),'1900-1-1'),8) from LF.Processes z where [end] is null and z.LadleFurnaceCode=2 order by start desc
--		insert into @Attributes select top 1 11, 'Time', [Value] = convert(char(8),dateadd(s,datediff(s,z.Start,GETUTCDATE()),'1900-1-1'),8) from vtd.Processes z where [end] is null and z.VTDTankCode=1 order by start desc
		--insert into @Attributes select top 1 31, 'Time', [Value] = convert(char(8),dateadd(s,datediff(s,z.LastPositionChange,GETUTCDATE()),'1900-1-1'),8) from LAD.Ladles z where [LadlePositionID] = 31 		insert into @Attributes select top 1 12, 'Time', [Value] = convert(char(8),dateadd(s,datediff(s,z.Start,GETUTCDATE()),'1900-1-1'),8) from vtd.Processes z where [end] is null and z.VTDTankCode=2 order by start desc
		
	
		--- EAF status goes here because they might not have a ladle
		declare @EAFStatus table (FurnaceCode int, HeatNumber varchar(10), Status varchar(20), LadleId int,  start datetime,  LadleInEAF int, LadleInTap int )

		insert into @EAFStatus
			select
			furnaceCode,
			heatNumber = case when h.heatstatusid <> 7 then h.HeatNumber else 'No Heat' end, 
			case when h.heatstatusid = 7 then 'Idle' else hs.Name end, 
			l.LadleID,
			case when h.HeatStatusID = 7 then ep.EndTap else ep.Start end
			, (select ladleid from LAD.Ladles z where z.LadlePositionID = case when furnacecode=1 then 1 else 3 end )
	     	, (select ladleid from LAD.Ladles z where z.LadlePositionID = case when furnacecode=1 then 2 else 4 end )
		from lad.Heats h
			left join mes.heats mh on mh.Name= h.HeatNumber
			left join lad.catHeatStatus hs on h.HeatStatusID = hs.HeatStatusID
			left join lad.ladles l on l.HeatNumber = h.HeatNumber
			left join eaf.Processes ep on ep.heatid = mh.id
		where 
			h.HeatStatusID in (2,3,4,5,6,7)
			and  (
			      ep.id = (select max(z.id) from EAF.Processes z where z.furnaceCode=1) or 
			      ep.id = (select max(z.id) from EAF.Processes z where z.furnaceCode=2)
			  )

		-- Repeat EAF status as attribute 
		insert into @Attributes select case when FurnaceCode=1 then 1 else 3 end ,'HeatNumber', left(HeatNumber,7) from @EAFStatus where LadleId is null
		insert into @Attributes select case when FurnaceCode=1 then 1 else 3 end ,'Status', left(replace([Status],'EAF',''),10) from @EAFStatus
		insert into @Attributes select case when FurnaceCode=1 then 1 else 3 end ,'Time', convert(char(8),dateadd(s,datediff(s,Start,GETUTCDATE()),'1900-1-1'),8) from @EAFStatus
		insert into @Attributes select case when FurnaceCode=1 then 1 else 3 end ,'WARNING', 'No Ladle' from @EAFStatus where  [Status] in ('EAF REFINE','EAF TAPPING') and LadleInEAF is null and Ladleid is null
		insert into @Attributes select case when FurnaceCode=1 then 1 else 3 end ,'WARNING', 'Pos taken' from @EAFStatus where [Status] in ('EAF REFINE','EAF TAPPING') and LadleInTap is not null  and Ladleid is null


		---------------------------------------------------------------------
		------------------------ Visual Attributes --------------------------
		---------------------------------------------------------------------
		
		-- visual attributes for EAF Ladle problems
		insert into @VisualAttributes 
		select
		case when furnacecode=1 then 1 else 3 end
		,'WARNING'
		,case when [status] = 'EAF TAPPING'  then '#f6A0A0' else '#f6f080' end
		from @EAFStatus where [Status] in ('EAF REFINE','EAF TAPPING') and Ladleid is null and (LadleInEAF is null or LadleinTap is not null)

		--------------------------------------------------------------------------------
		--------------------------------- CASTERS --------------------------------------
		--------------------------------------------------------------------------------

				-------------------------------------------------
				-- Get where Casters Positions will be allocated
				-------------------------------------------------
				----------
				--Caster 1
				----------

				if exists (select 1 from @mainTable where _LadlePositionID=32 or _LadlePositionID=35)
				begin
					insert into @ShowCaster1Positions(LadlePositionID,ScreenPosition)
					values (35,1),(32,2)
		
					if exists (select 1 from @mainTable where _LadlePositionID=33 or _LadlePositionID=34)
					insert into @ShowCaster1Positions(LadlePositionID,ScreenPosition)
					values (33,3),(34,4)
	
				end
				else if exists (select 1 from @mainTable where _LadlePositionID=33 or _LadlePositionID=34)
				begin
					insert into @ShowCaster1Positions(LadlePositionID,ScreenPosition)
					values (33,1),(34,2)
	
				end
				else
				begin
					-- Use Tag
					if (select top 1 [value] from IH.LTReal where TagId = @TagId_Cas1_TurretArmAtCast order by Offset desc) = 1
					insert into @ShowCaster1Positions(LadlePositionId,ScreenPosition) values (35,1),(32,2)

					else if (select top 1 [value] from IH.LTReal where TagId = @TagId_Cas1_TurretArmAtCast order by Offset desc) = 2
					insert into @ShowCaster1Positions(LadlePositionId,ScreenPosition)	values (33,1),(34,2)
					
					else insert into @ShowCaster1Positions(LadlePositionId,ScreenPosition)	values (35,1),(32,2),(33,3),(34,4)
				end

				update sw
					set [Name] = (select z.[Name] from @castersLabels z where z.LadlePositionID = sw.LadlePositionId)
				from @ShowCaster1Positions sw

				----------
				--Caster 2
				----------
				if exists (select 1 from @mainTable where _LadlePositionID=42 or _LadlePositionID=45)
				begin
					insert into @ShowCaster2Positions(LadlePositionID,ScreenPosition)
					values (45,1),(42,2)
		
					if exists (select 1 from @mainTable where _LadlePositionID=43 or _LadlePositionID=44)
					insert into @ShowCaster2Positions(LadlePositionID,ScreenPosition)
					values (43,3),(44,4)
	
				end
				else if exists (select 1 from @mainTable where _LadlePositionID=43 or _LadlePositionID=44)
				begin
					insert into @ShowCaster2Positions(LadlePositionID,ScreenPosition)
					values (43,1),(44,2)
	
				end
				else
				begin
					-- Use Tag
					if (select top 1 [value] from IH.LTReal where TagId = @TagId_Cas2_TurretArmAtCast order by Offset desc) = 1
					insert into @ShowCaster2Positions(LadlePositionId,ScreenPosition) values (45,1),(42,2)
					
					else if (select top 1 [value] from IH.LTReal where TagId = @TagId_Cas2_TurretArmAtCast order by Offset desc) = 2
					insert into @ShowCaster2Positions(LadlePositionId,ScreenPosition)	values (43,1),(44,2)
					
					else insert into @ShowCaster2Positions(LadlePositionId,ScreenPosition)	values (45,1),(42,2),(43,3),(44,4)
				end
				
				update sw
					set [Name] = (select z.[Name] from @castersLabels z where z.LadlePositionID = sw.LadlePositionId)
				from @ShowCaster2Positions sw

				-------------------------------------------------------------------
				-- IMPORTANT - Update Main Table with The Screen Casters Positions
				-------------------------------------------------------------------

				update mt set
					mt.[_ScreenPositionID]  = cast(s1.ScreenPosition as varchar) + 'cas1' 
					,mt.[_ScreenPositionLabel] =  s1.[Name]

				from @mainTable mt
				join @ShowCaster1Positions s1 on s1.LadlePositionId = mt._LadlePositionID
				where mt._LadlePositionID in (select LadlePositionId from @ShowCaster1Positions)
	
				update mt set  
					mt.[_ScreenPositionID]  = cast(s2.ScreenPosition as varchar) + 'cas2' 
					,mt.[_ScreenPositionLabel] =  s2.[Name]
				from @mainTable mt
				join @ShowCaster2Positions s2 on s2.LadlePositionId = mt._LadlePositionID
				where mt._LadlePositionID in (select LadlePositionId from @ShowCaster2Positions)


				-------------------------------------------------
				--------------- Get Casters Speeds --------------
				-------------------------------------------------				

				select @caster1Pos1 = cast(s1.ScreenPosition as varchar) + 'cas1'  from @ShowCaster1Positions s1 where s1.ScreenPosition = 1
				select @caster2Pos1 = cast(s2.ScreenPosition as varchar) + 'cas2'  from @ShowCaster2Positions s2 where s2.ScreenPosition = 1				
				select @caster1Pos2 = cast(s1.ScreenPosition as varchar) + 'cas1'  from @ShowCaster1Positions s1 where s1.ScreenPosition = 2
				select @caster2Pos2 = cast(s2.ScreenPosition as varchar) + 'cas2'  from @ShowCaster2Positions s2 where s2.ScreenPosition = 2

				set @speedCas1 = (select top 1 left(cast(cast(z.Value as decimal(10,0)) as varchar),10) from IH.LTReal z where z.TagId=170225 order by offset desc)
	                       + ' '+ (select top 1 left(cast(cast(z.Value as decimal(10,0)) as varchar),10) from IH.LTReal z where z.TagId=170241 order by offset desc)
	                       + ' '+ (select top 1 left(cast(cast(z.Value as decimal(10,0)) as varchar),10) from IH.LTReal z where z.TagId=170257 order by offset desc)
	                       + ' '+ (select top 1 left(cast(cast(z.Value as decimal(10,0)) as varchar),10) from IH.LTReal z where z.TagId=170273 order by offset desc)
				
				set @speedCas2 = (select top 1 left(cast(cast(z.Value as decimal(10,0)) as varchar),10) from IH.LTReal z where z.TagId= 170829 order by offset desc)
	                       + ' '+ (select top 1 left(cast(cast(z.Value as decimal(10,0)) as varchar),10) from IH.LTReal z  where z.TagId= 170830 order by offset desc)
	                       + ' '+ (select top 1 left(cast(cast(z.Value as decimal(10,0)) as varchar),10) from IH.LTReal z  where z.TagId= 170831 order by offset desc)
	                       + ' '+ (select top 1 left(cast(cast(z.Value as decimal(10,0)) as varchar),10) from IH.LTReal z  where z.TagId= 170832 order by offset desc)
				
				set @timeRemainingCas1 = (select top 1 cast(z.Value as decimal(10,2)) from IH.LTReal z where z.TagId=170846 order by offset desc)
				
				set @timeRemainingCas2 = (select top 1 cast(z.Value as decimal(10,2)) from IH.LTReal z where z.TagId=170849 order by offset desc)
			
				insert into @Attributes select top 1 @caster1Pos2, 'Sp',  [Value] = @speedCas1 --befeore was @caster1Pos1
				insert into @Attributes select top 1 @caster2Pos2, 'Sp',  [Value] = @speedCas2 --befeore was @caster2Pos1

				
				insert into @Attributes select top 1 @caster1Pos2, 'Time Rem',  --befeore was @caster1Pos1
				[Value] = (SELECT
							RIGHT('0' + CAST (FLOOR(COALESCE (@timeRemainingCas1, 0) / 60) AS VARCHAR ), 2) + ':' + 
							RIGHT('0' + CAST (FLOOR(COALESCE (@timeRemainingCas1, 0) % 60) AS VARCHAR ), 2) + ':' + 
							RIGHT('0' + CAST (FLOOR((@timeRemainingCas1* 60) % 60) AS VARCHAR ), 2))

				insert into @Attributes select top 1 @caster2Pos2, 'Time Rem',  --befeore was @caster1Pos1
				[Value] = (SELECT
							RIGHT('0' + CAST (FLOOR(COALESCE (@timeRemainingCas2, 0) / 60) AS VARCHAR ), 2) + ':' + 
							RIGHT('0' + CAST (FLOOR(COALESCE (@timeRemainingCas2, 0) % 60) AS VARCHAR ), 2) + ':' + 
							RIGHT('0' + CAST (FLOOR((@timeRemainingCas2* 60) % 60) AS VARCHAR ), 2))

				-------------------------------------------------
				--------------- Get Casters ALARMS --------------
				-------------------------------------------------	
				select top 1 @UnkLadleCas1Arm1=[Value] from ih.LTDigital where tagId = 170825 order by offset desc
				select top 1 @UnkLadleCas1Arm2=[Value] from ih.LTDigital where tagId = 170826 order by offset desc
				select top 1 @UnkLadleCas2Arm1=[Value] from ih.LTDigital where tagId = 170827 order by offset desc
				select top 1 @UnkLadleCas2Arm2=[Value] from ih.LTDigital where tagId = 170828 order by offset desc
				

				-- Get where the Arm 1 is allocated (can be in or out)
				select top 1 @Cas1Arm1ScreenPosition = cast(ScreenPosition as varchar) + 'cas1'  from @ShowCaster1Positions where [LadlePositionId] in (32,33) order by ScreenPosition asc
				-- Get where the Arm 2 is allocated (can be in or out)
				select top 1 @Cas1Arm2ScreenPosition = cast(ScreenPosition as varchar) + 'cas1'  from @ShowCaster1Positions where [LadlePositionId] in (34,35) order by ScreenPosition asc

				-- Get where the Arm 1 is allocated (can be in or out)
				select top 1 @Cas2Arm1ScreenPosition = cast(ScreenPosition as varchar) + 'cas2'  from @ShowCaster2Positions where [LadlePositionId] in (42,43) order by ScreenPosition asc
				-- Get where the Arm 2 is allocated (can be in or out)
				select top 1 @Cas2Arm2ScreenPosition = cast(ScreenPosition as varchar) + 'cas2'  from @ShowCaster2Positions where [LadlePositionId] in (44,45) order by ScreenPosition asc
		
				if @UnkLadleCas1Arm1 = 1 insert into @Attributes select @Cas1Arm1ScreenPosition,'WARNING', 'Unk Ladle'
				if @UnkLadleCas1Arm2 = 1 insert into @Attributes select @Cas1Arm2ScreenPosition,'WARNING', 'Unk Ladle'

				if @UnkLadleCas2Arm1 = 1 insert into @Attributes select @Cas2Arm1ScreenPosition,'WARNING', 'Unk Ladle'
				if @UnkLadleCas2Arm2 = 1 insert into @Attributes select @Cas2Arm2ScreenPosition,'WARNING', 'Unk Ladle'



				
				-----------------------
				-- Position Attributes
				-----------------------
				insert into @Attributes select top 1 @caster1Pos2, 'Time', [Value] = convert(char(8),dateadd(s,datediff(s,z.Start,GETUTCDATE()),'1900-1-1'),8) from cas.Processes z where [end] is null and z.CasterCode=1 order by start desc --before was @caster1Pos1
				insert into @Attributes select top 1 @caster1Pos2, 'Weight',  [Value] = left(cast(cast(z.Value as decimal(10,0)) as varchar),10) from IH.LTReal z where z.TagId=170200 order by offset desc --before was @caster1Pos1
				insert into @Attributes select top 1 @caster2Pos2, 'Weight',  [Value] = left(cast(cast(z.Value as decimal(10,0)) as varchar),10) from IH.LTReal z where z.TagId=170848 order by offset desc --before was @caster1Pos1
				
				--insert into @Attributes select top 1 @caster1Pos2, 'Time', [Value] = convert(char(8),dateadd(s,datediff(s,z.LastPositionChange,GETUTCDATE()),'1900-1-1'),8) from LAD.Ladles z where [LadlePositionID] = 33 --It was commented when @caster1Pos1 was changed by Pos2 because Time is two rows higher.
	
				-----------------------
				-- Visual Attributes
				-----------------------
				-- Warnings for turret problems

				-----------
				-- Caster 1
				-----------
				declare @Warning_Cas1Arm1In	  varchar(10)
				declare @Warning_Cas1Arm2In   varchar(10)
				declare @Warning_Cas1Arm1Out  varchar(10)
				declare @Warning_Cas1Arm2Out  varchar(10)

				if (select count(*) from lad.Ladles where  LadlePositionid=34 or LadlePositionId=35) > 1
				begin
						select @Warning_Cas1Arm2In = cast(ScreenPosition as varchar) + 'cas1'  from @ShowCaster1Positions where [LadlePositionId] = 34
						select @Warning_Cas1Arm2Out = cast(ScreenPosition as varchar) + 'cas1'  from @ShowCaster1Positions where [LadlePositionId] = 35

						insert into @VisualAttributes values(@Warning_Cas1Arm2In,'WARNING','#f6ff67')
						insert into @VisualAttributes values(@Warning_Cas1Arm2In,'WARNING','#f6ff67')
				end

				if (select count(*) from lad.Ladles where  LadlePositionid=32 or LadlePositionId=33) > 1
				begin
					select @Warning_Cas1Arm1In = cast(ScreenPosition as varchar) + 'cas1'  from @ShowCaster1Positions where [LadlePositionId] = 32
					select @Warning_Cas1Arm1Out = cast(ScreenPosition as varchar) + 'cas1'  from @ShowCaster1Positions where [LadlePositionId] = 33


					insert into @VisualAttributes values(@Warning_Cas1Arm1In,'WARNING','#f6ff67')
					insert into @VisualAttributes values(@Warning_Cas1Arm1Out,'WARNING','#f6ff67')
				end

				----------------------------------
				---- Finish Casters Configuration
				---------------------------------

	




		-----------------
		-- Return Tables
		-----------------
		select * from @mainTable
		order by [_Order] asc

		select * from @Attributes order by LadlePositionID, [Name] 
	
		select * from @EAFStatus 

		select * from @VisualAttributes		

		select * from @ShowCaster1Positions
		
		select * from @ShowCaster2Positions

		select top 1 [ActiveL1] = Value from ih.LTDigital where TagId = 170847 order by offset desc

		-- Must Under Cover: heats on screen with hydrogen > 1.5 (same logic as SendHydrogenAlertEmail), split by under cover
		SELECT
			HeatName = h.[Name],
			HydrogenValue = CASE WHEN cs.H1 > cs.H2 THEN cs.H1 ELSE cs.H2 END,
			UnderCover = CASE WHEN covhe.CoverId IS NOT NULL THEN 1 ELSE 0 END
		FROM (SELECT DISTINCT _HeatName FROM @mainTable WHERE _HeatName IS NOT NULL) mt
		INNER JOIN MES.Heats h ON LEFT(h.[Name], 7) = LEFT(mt._HeatName, 7)
		CROSS APPLY (
			SELECT TOP 1 H1, H2
			FROM LAB.ChemistrySamples
			WHERE HeatId = h.Id
			ORDER BY ProcessTimestamp DESC
		) cs
		INNER JOIN CAT.Grades grad ON grad.Id = h.GradeId AND grad.GradeGroupId = 40
		LEFT JOIN CAS.CoverHeats covhe ON covhe.HeatName = h.[Name]
		WHERE (cs.H1 > 1.5 OR cs.H2 > 1.5)

END
GO



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



--------------------------------------------------------
------------- 2023-02-16 -------------------------------
---------------------------------------------------------


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

USE [MES]
GO
/****** Object:  StoredProcedure [MES].[GetAutocompleteMeltOrders]    Script Date: 2/16/2023 2:18:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [MES].[GetAutocompleteMeltOrders]
 	@MeltOrder VARCHAR(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:'
		+ ', @MeltOrder: ' + ISNULL(CAST(@MeltOrder AS VARCHAR), '')

		
	DECLARE @SearchTextString VARCHAR(100)

	SET @searchTextString = '%' + @MeltOrder + '%'

	declare @MeltOrders table ([value] varchar(200), [label] varchar(200),isActive bit)
	

	-----------------------
	-- Begin Query
	-----------------------
		
	insert into @MeltOrders
	select top 250
		rtrim(MeltOrderNumber) + ',' + trim(GradeName) + ',' + rtrim(MaterialSize),
		rtrim(MeltOrderNumber) + case when IsActive = 0 then ' (inactive)' else '' end,			
		isActive
	from LAD.MeltOrders  m	
	where (@MeltOrder is null or MeltOrderNumber like @searchTextString) 
		and datediff(day,LastUpdate,GETUTCDATE()) <  30
	order by 
	isActive desc,MeltOrderNumber asc

	

	select * from @MeltOrders 
	where ((@MeltOrder is null and isActive = 1) or @MeltOrder is not null)
	group by [value],[label],isActive
	order by isActive desc,[label] asc


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

	RETURN 0
END

go


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


---// 1- In LAD.catLadlePositions add isActive bit and AllowedMoves varchar(200) 
-- // 2 - set isActive as defualt 1 and update isActive = 1
-- // 3 - [LAD].[GetMeltshopOverviewWeb] add column [_NotAllowedLocation] varchar(200)

--- // 4 - modify this two columns  
--- //[_AllowedLocations] =  pos.AllowedMoves, --+ ',1cas1'+ ',2cas1'+ ',3cas1'+ ',4cas1'+ ',1cas2'+ ',2cas2'+ ',3cas2'+ ',4cas2',
--- //			[_NotAllowedLocation] = pos.NotAllowedMoves,

alter PROCEDURE [LAD].[EnableOrDisableLadlePosition]
@LadlePositionId int,
@Enable bit,
@CurrentUser varchar(MAX) = NULL
AS
BEGIN
	-----------------
	--- Variables ---
	-----------------
	declare @NewPositions varchar(200)
	declare @NotAllowedPositions varchar(200)
 
	update LAD.catLadlePositions
		set isActive = @Enable
	where LadlePositionID = @LadlePositionId


	set @NewPositions = (
		SELECT STRING_AGG(LadlePositionId, ',') 
		FROM LAD.catLadlePositions
		WHERE isActive = 1 
	)
	

	set @NotAllowedPositions = (
		SELECT STRING_AGG(LadlePositionId, ',') 
		FROM LAD.catLadlePositions
		WHERE isActive = 0 and LadlePositionID < 70 
	)




	
	update LAD.catLadlePositions
		set AllowedMoves = @NewPositions
			,NotAllowedMoves = @NotAllowedPositions

		----- ADD Log using the currentUser
END


go



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



/*------------------------------ 05/09/2023 --------------------------------*/
EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [meltshopoverview_readWrite] -- The role
,@name = N'securedsp-CAS.UpdateRemainingHeats' -- the procedure
,@value = N'SendCurrentUser=TRUE'  
GO


--- Step 1: Add at the bottom of the SP LAD.GetMeltShopOverview these lines:
		-- CAS STATUS
		select * from cas.[Status]

--- Step 2: Create New SP UpdateRemainingHeats
USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[UpsertSequences]    Script Date: 5/9/2023 20:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [CAS].[UpdateRemainingHeats]
@CasterCode int,
@RemainingHeats int,
@currentUser VARCHAR(MAX) = NULL

as
begin
	BEGIN TRY
		DECLARE	@procName VARCHAR(300)
		DECLARE	@logMsg VARCHAR(MAX)
		SELECT @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
		SET NOCOUNT ON
		

		update cas.[Status] 
		set
			RemainingHeats = @RemainingHeats
		where CasterCode = @CasterCode
		
		select * from cas.[Status]
	set @logMsg = 'The Caster: ' + cast(@CasterCode as varchar(max)) + ' has a new Remaining Heats value of : '+ cast(@RemainingHeats as varchar(max)) + ' User:' + cast(@currentUser as varchar(max))

	EXEC SYSTEM.LogMsg @message = @logMsg
		,@procedure = @procName
		,@typeCode = 'DEBUG'
		,@logError = 1

		END TRY
		BEGIN CATCH
			SELECT @logMsg = ERROR_MESSAGE()
			print @logMsg
			EXEC SYSTEM.LogMsg @message = @logMsg
				,@procedure = @procName
				,@typeCode = 'ERROR'
				,@logError = 1
		END CATCH
end



/*--------------------2025/03/26---------------------*/

--1. Add new column comments 
ALTER TABLE [CAS].[Processes]
ADD [Comments] VARCHAR(MAX) NULL;

--2. Add Permissions

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

