USE [IHBoxSystem]
GO
CREATE ROLE lw_r AUTHORIZATION dbo 
GO  

USE [IHBoxSystem]
GO
CREATE ROLE lw_w AUTHORIZATION dbo 
GO  

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

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





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

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

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


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

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

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

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


declare @parent int, @result_routeId INT
SELECT TOP 1 @parent=Id FROM FrontEnd.Menu WHERE [Name] = 'Ladles'

INSERT INTO [FrontEnd].[Routes]
		   ([Route]
		   ,[ModuleName]
		   ,[ModulePath]
		   ,[RequiredRole]
	   )
	 VALUES
		   (
		   '!/ladle-wall-manager(/)'
		   , 'ladle-wall-manager'
		   , 'custom-screens/ladle-wall-manager/'
		   , 'lw_r'           
		   )
		   SET @result_routeId = @@IDENTITY
INSERT INTO [FrontEnd].[Menu]
		   ([Parent]
		   ,[Name]
		   ,[RouteID]
		   ,[SortOrder]
		   ,[Enabled]
		   ,[Visible]
		   ,[RequiredRole]
		   ,[Group]
		  )
	 VALUES
		   (
		   @parent
		   ,'Ladle Wall Manager'
		   ,@result_routeId 
		   ,1
		   ,1
		   ,1
		   ,'lw_r'
		   ,NULL
		 )
GO


INSERT INTO [SECURITY].[Modules]([Code],[Name])
VALUES('ladle_wall_manager','Ladle Wall Manager')

SET @ModuleId = @@IDENTITY

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


INSERT INTO [SECURITY].ModuleActions(ModuleId,Code,[Name],[Role])
VALUES (@ModuleId,'read','Read','lw_r')



USE [MES]
GO
/****** Object:  StoredProcedure [LAD].[GetLadleInfo]    Script Date: 12/18/2023 3:03:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [LAD].[GetLadleInfo]

@start datetime = NULL,
@end datetime = NULL,
@LadleId int =1

as
begin

if @end is NULL 
		set @end = GETUTCDATE()

	if @start is NULL 
		set @start = @end-1

	if datediff(DAY,@start,@end)>3
		set @start = dateadd(DAY,-3,@end)

select top 1 
HeatId = m.HeatNumber,
StartTap, 
TapTime =   CONVERT(VARCHAR(23), IH.ToLocal(StartTap, 'EDTUSCND'), 121),
TapTemperature =  (select top 1 temperature from EAF.Temperatures z where z.ProcessId = e.id order by z.Timestamp desc)
into #t
from LAD.Ladles m left join MES.Heats h
on h.[Name] = m.HeatNumber 
left join EAF.Processes e on h.id = e.HeatId where m.LadleId = @LadleId order by TapTime desc

insert into #t (HeatId,TapTime,startTap)
	select 'Drain'
		,TapTime =   CONVERT(VARCHAR(23), IH.ToLocal([Timestamp], 'EDTUSCND'), 121)
	,[Timestamp]
	from MES.LAD.LadleDrains
	where [Timestamp] between @start and dateadd(DAY,1,@end)
	and LadleID = @LadleID

select 
	#t.*
	,typeid
	,[Name]=replace(et.[Name],' ','')
	,heatcount
	,offset 
	,drains
	,correctedHeatcount = heatcount + offset + case when typeid=49 then drains else 0 end
	,EquipmentID
	into #t2
	from #t
	outer apply LAD.GetEquipmentHeatCountersByLadle(@LadleID, null) 
	left join IHBoxSystem.equip.EquipmentTypes et on et.id=typeid

select *
		from 
	(
	  select HeatId, StartTap, TapTime, TapTemperature ,correctedHeatcount,[Name]
	  from #t2
	) src   
	pivot
	(
	  max(correctedHeatCount)
	  for [Name] in (
		[PlateAssembly],
		[UpperNozzle],
		[WorkingLining],
		[PorousPlugNorth],
		[PorousPlugSouth]
	  )
	) piv 
	order by StartTap desc

	---- CURRENT EQUIPMENTS ----
	select  
	HeatId
	,TypeId = et.Id
	,EquipmentID
	,TypeName = et.[Name]
	,[Key] = REPLACE(et.[Name], ' ', '')
	,Disposable = ~et.Replaceable
	,EquipmentName = e.Name
	,CampaignCounter = HeatCount + Offset + case when et.id=49 then Drains else 0 end  -- 49 = Working Lining
	,Warning = (select top 1 Warning from MES.LAD.catEquipmentAlarms ea where ea.EquipmentTypeId = TypeId)
	,Alarm = (select top 1 Alarm from MES.LAD.catEquipmentAlarms ea where ea.EquipmentTypeId = TypeId)
	from 
	LAD.catEquipmentTypes et
	left join #t2 hc on hc.TypeID = et.id
	left join LAD.catEquipments e on e.Id = hc.EquipmentID


	---- ALL EQUIPMENTS ----

	select 
	EquipmentId=Id
	,[Name] 
	,[EquipmentTypeId]
	,[InstalledIn] = 'Ladle '+ cast(hc.LadleId as varchar(2)) 
	,class=case when hc.LadleId is null then 'primary' else '' end
	,hc.*
	-- absolute heat count
	from [LAD].[GetEquipmentHeatCountersByType](NULL,NULL) hc
	right join LAD.catEquipments e on e.Id=hc.EquipmentId
	order by EquipmentTypeId, LEN([Name]), [Name]
end



-------------------------------------------------

-------------------------------------------------
USE [MES]
GO
/****** Object:  StoredProcedure [LAD].[ChangeLadleEquipment_new]    Script Date: 12/18/2023 9:54:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAD].[ChangeLadleEquipment_new]
@LadleID int
,@WorkingLiningChange bit =0 
,@UpperNozzleChange bit =0 
,@PlateAssemblyChange bit =0 
,@PorousPlugNorthChange bit =0 
,@PorousPlugSouthChange bit =0 
,@WorkingLining int = null 
,@UpperNozzle int = null 
,@PlateAssembly int = null 
,@PorousPlugNorth int = null 
,@PorousPlugSouth int = null 
,@WorkingLiningComment varchar(max) =NULL 
,@UpperNozzleComment varchar(max) =NULL 
,@PlateAssemblyComment varchar(max) =NULL 
,@PorousPlugNorthComment varchar(max) =NULL 
,@PorousPlugSouthComment varchar(max) =NULL 

,@currentUser VARCHAR(MAX) = NULL
AS
BEGIN
	SET NOCOUNT ON
	-----------------------
	-- Common procedures variables
	-----------------------
	
	DECLARE @result INT = 0,
			@outputMessage VARCHAR(MAX)

	-----------------------
	-- Execute Action
	-----------------------
	DECLARE @LadleName varchar(10) = 'Ladle ' + cast(@LadleId as varchar(2))
	DECLARE @TypeId int
			,@CurrentEquipmentId int
			,@NewEquipmentId int
			,@Comments varchar(200)
			, @EquipmentTypeName varchar(50)
			, @CurrentEquipmentName varchar(50)
			, @NewEquipmentName varchar(50)
			, @replaceable bit

	
	select TypeId=49, Change = @WorkingLiningChange, Comments = @WorkingLiningComment, [Value] = @WorkingLining into #parts2
	union select 47, @UpperNozzleChange , @UpperNozzleComment, @UpperNozzle
	union select 45, @PlateAssemblyChange , @PlateAssemblyComment, @PlateAssembly
	union select 58, @PorousPlugNorthChange , @PorousPlugNorthComment, @PorousPlugNorth
	union select 59, @PorousPlugSouthChange , @PorousPlugSouthComment, @PorousPlugSouth
	
	select EquipmentId, EquipmentTypeId = lc.TypeId, CurrentValue = lc.HeatCount + case when lc.TypeID = 49 then lc.Drains else 0 end + lc.Offset into #current 
	from 
	[LAD].[GetEquipmentHeatCountersByLadle](@LadleID,NULL) lc
	
	-- Find Out equipment changes
	select 
		c.Equipmenttypeid
		,CurrentEquipmentId = c.EquipmentID
		,[Value] = n.[Value]
	into #changes
	from 
	#current c 
	inner join #parts2 n on n.Typeid = c.EquipmentTypeId 
	where  n.[Value] <> c.CurrentValue and n.Change = 1

	DECLARE @Value int; 
	DECLARE @TypeIdCur int;

	-- Declare the cursor
	DECLARE changes_cursor CURSOR FOR 
	SELECT EquipmentTypeId, [Value]
	FROM #changes;
	-- Open the cursor
	OPEN changes_cursor;

	-- Fetch the first row from the cursor
	FETCH NEXT FROM changes_cursor INTO @TypeIdCur, @Value;

	-- Loop through the rows
	WHILE @@FETCH_STATUS = 0
	BEGIN
		declare @CurrentCounter int = (select z.HeatCount + case when @TypeIdCur=49 then z.Drains else 0 end  from [LAD].[GetEquipmentHeatCountersByLadle](@LadleID,null) z  where z.TypeId=@TypeIdCur)
		declare @ChangeID int = (select top 1 cce.ChangeID from LAD.EquipmentChangeEvents cce where cce.LadleID=@LadleID AND CCE.TypeID=@TypeIdCur ORDER By cce.DateTime desc)
		insert into MES.LAD.HeatCountersOverrides  ( Offset,[Datetime],Comments, ChangeID, [User])
		values
		( @Value - @CurrentCounter , GETUTCDATE(),'',@ChangeID,'ladlewall')

		-- Log into events
		declare @ComponentType varchar(20)
		select @ComponentType = [Name] from [IHBoxSystem].Equip.[EquipmentTypes] Where ID=@TypeIdCur
		declare @Message varchar(max)
	
		select @Message ='Ladle '+ rtrim(@LadleName) +' Override Counter for: ' + @ComponentType + ' from ' + cast(@CurrentCounter as varchar) + ' to ' + cast(@Value as varchar)

		insert into LadleEvents (LadleID,HeatNumber, LadleEventTypeID, [Description], Comments)
		values (
		@LadleID,
		'HeatCount',
		18,
		@Message
		, 'ladlewall'
		)

		-- Fetch the next row from the cursor
		FETCH NEXT FROM changes_cursor INTO @TypeIdCur, @Value;
	END

	-- Close the cursor
	CLOSE changes_cursor;

	-- Deallocate the cursor
	DEALLOCATE changes_cursor;



	---- Update comments ----

	update 
			C 
			set
			Comments = x.Comments 	
			from
			(select * from #parts2) x
			left join LAD.EquipmentChangeEvents C
			ON 	C.ChangeId= (select top 1 Changeid from LAD.EquipmentChangeEvents z  where z.TypeId = x.TypeID and z.LadleID=@LadleID order by z.DateTime desc,z.ChangeID desc)
			where x.Comments is not null


	select Message='Ok'
END


---------------------------------------------------------
---------------------------------------------------------
USE [MES]
GO
/****** Object:  StoredProcedure [LAD].[GetEquipmentTypes]    Script Date: 21/11/2023 09:23:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAD].[GetEquipmentTypes]
AS

BEGIN
	
	select [Name], 
	[Id] ,
	Replaceable, 
	Alarm, 
	Warning,
	[Key] = REPLACE(e.Name, ' ', '') 
	from LAD.catEquipmentAlarms al 
	left join LAD.catEquipmentTypes e on e.Id = al.EquipmentTypeID
	
	
END


