USE [IHBoxSystem]
GO
CREATE ROLE castertrackingtesting_read AUTHORIZATION dbo 
GO  

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

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

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [castertrackingtesting_read] -- The role
,@name = N'securedsp-CAS.GetTrackingTestingChartData' -- 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-tracking-testing(/:processId)(/)'
		   , 'caster-tracking-testing'
		   , 'custom-screens/caster-tracking-testing/'
		   , 'castertrackingtesting_read'           
		   )
		   SET @result_routeId = @@IDENTITY
INSERT INTO [FrontEnd].[Menu]
		   ([Parent]
		   ,[Name]
		   ,[RouteID]
		   ,[SortOrder]
		   ,[Enabled]
		   ,[Visible]
		   ,[RequiredRole]
		   ,[Group]
		  )
	 VALUES
		   (
		   @parent
		   ,'Caster Tracking Testing'
		   ,@result_routeId 
		   ,1
		   ,1
		   ,1
		   ,'castertrackingtesting_read'
		   ,NULL
		 )
GO




USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[GetProcessChartData]    Script Date: 5 mar. 2021 21:58:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [CAS].[GetTrackingTestingChartData]
	@id INT = NULL,
	@points INT = NULL,

	@timeZoneCode CHAR(10) = NULL
AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--The name of the procedure with its schema.
		@procName VARCHAR(300)

	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
		+ '@id: ' + ISNULL(CAST(@id AS VARCHAR), '')
		+ ', @points: ' + ISNULL(CAST(@points AS VARCHAR), '')
		+ ', @timeZoneCode: ' + ISNULL(CAST(@timeZoneCode AS VARCHAR), '')

	IF @id is null set @id = (select top 1 Id from cas.Processes order by [Start] desc)
	-----------------------
	-- Variables
	-----------------------
	DECLARE @startDate DATETIME,
			@endDate DATETIME,
			@startTap DATETIME,
			@endTap DATETIME,
			@now DATETIME,
			@tagList VARCHAR(MAX)
			
	DECLARE @eventsT TABLE(
		[Time] DATETIME, 
		EventType INT, 
		[Description] NVARCHAR(100)
	)
	DECLARE @tagsT TABLE(ID INT, Name VARCHAR(50), MinScale FLOAT, MaxScale FLOAT, LineColor VARCHAR(30), IsDigital BIT, Pane INT, ConsumptionCode VARCHAR(20))


	-----------------------
	-- Parameters default
	-----------------------
	IF (@points IS NULL)
		SET @points = 400


		
	DECLARE @LadleArrivalTimestamp BIGINT = (SELECT IH.Date2Ms([LadleArrival]) FROM CAS.Processes WHERE Id = @Id) 	
	DECLARE @LadleCloseTimestamp BIGINT = (SELECT IH.Date2Ms([LadleCLose]) FROM CAS.Processes WHERE Id = @Id) 	
	DECLARE @LadleOpenTimestamp BIGINT = (SELECT IH.Date2Ms([LadleOpen]) FROM CAS.Processes WHERE Id = @Id) 	
	DECLARE @ProductsStartTimestamp BIGINT = (SELECT IH.Date2Ms(MIN([Datetime])) FROM CAS.Products WHERE ProcessId = @Id) 
	
	DECLARE @ProductsEnd DATETIME = (SELECT MAX([Datetime]) FROM CAS.Products WHERE ProcessId = @Id) 
	-----------------------
	-- Set variables
	-----------------------			
	SELECT 	
		@startDate = DATEADD(MINUTE,-1,[LadleArrival]), 
		@endDate = DATEADD(MINUTE,15, [LadleCLose])
	FROM CAS.Processes
	WHERE 
		Id = @id


	SET @now = GETUTCDATE()
	

	IF (@startDate IS NULL)
	BEGIN
		SET @startDate = null
		SET @endDate = null
	END
	ELSE IF (@endDate IS NULL)
	BEGIN
		SET @endDate = @now
	END


	------------------------
	-- Tags Declaration
	------------------------
	DECLARE @TagIdHeatsInMold		INT = (SELECT TOP 1 Id FROM IH.Tags WHERE [Name] = 'CAS1.COMMON.Global.AT_Heats_In_Mold' AND [Deleted] = 0)
	

	
	DECLARE @TagIdTurrentArriveTime	INT = (SELECT TOP 1 Id FROM IH.Tags WHERE [Name] = 'CAS1.COMMON.Global.AT_Turret_Arrive_Time'	AND [Deleted] = 0)
	DECLARE @TagIdTurretEntersCast INT = (SELECT TOP 1 Id FROM IH.Tags WHERE [Name] = 'CAS1.COMMON.Global.AT_Turret_Arm_Enters_Cast_Pos' AND [Deleted] = 0)
	
	--- Strands
	DECLARE @TagIdStrand1_MoldHeatNum		INT = (SELECT TOP 1 Id FROM IH_100020.dbo.catStringTags WHERE OPCTagName = 'CAS1.STRAND1.Global.AT_Mold_Heat_Num.DATA')
	DECLARE @TagIdStrand2_MoldHeatNum		INT = (SELECT TOP 1 Id FROM IH_100020.dbo.catStringTags WHERE OPCTagName = 'CAS1.STRAND2.Global.AT_Mold_Heat_Num.DATA')
	DECLARE @TagIdStrand3_MoldHeatNum		INT = (SELECT TOP 1 Id FROM IH_100020.dbo.catStringTags WHERE OPCTagName = 'CAS1.STRAND3.Global.AT_Mold_Heat_Num.DATA')
	DECLARE @TagIdStrand4_MoldHeatNum		INT = (SELECT TOP 1 Id FROM IH_100020.dbo.catStringTags WHERE OPCTagName = 'CAS1.STRAND4.Global.AT_Mold_Heat_Num.DATA')
	
	DECLARE @TagsMoldHeatNum TABLE (StrandId INT, MoldHeatNum INT)
	
	INSERT INTO @TagsMoldHeatNum
	values (1,@TagIdStrand1_MoldHeatNum),(2,@TagIdStrand2_MoldHeatNum),(3,@TagIdStrand3_MoldHeatNum),(4,@TagIdStrand4_MoldHeatNum)

	--- Products
	DECLARE @TagIdStrand1_Counter		INT = (SELECT TOP 1 Id FROM IH.Tags WHERE [Name] = 'CAS1.STRAND1.Global.AT_Product_Buffer_x.AT_Product_Buffer_00.Counter' AND [Deleted] = 0)
	DECLARE @TagIdStrand2_Counter		INT = (SELECT TOP 1 Id FROM IH.Tags WHERE [Name] = 'CAS1.STRAND2.Global.AT_Product_Buffer_x.AT_Product_Buffer_00.Counter' AND [Deleted] = 0)
	DECLARE @TagIdStrand3_Counter		INT = (SELECT TOP 1 Id FROM IH.Tags WHERE [Name] = 'CAS1.STRAND3.Global.AT_Product_Buffer_x.AT_Product_Buffer_00.Counter' AND [Deleted] = 0)
	DECLARE @TagIdStrand4_Counter		INT = (SELECT TOP 1 Id FROM IH.Tags WHERE [Name] = 'CAS1.STRAND4.Global.AT_Product_Buffer_x.AT_Product_Buffer_00.Counter' AND [Deleted] = 0)
	
	DECLARE @TagsProductCounters TABLE (StrandId INT, [Counter] INT)
	
	INSERT INTO @TagsProductCounters
	values (1,@TagIdStrand1_Counter),(2,@TagIdStrand2_Counter),(3,@TagIdStrand3_Counter),(4,@TagIdStrand4_Counter)
	
	

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

	-----------------------
	-- Events internal
	-----------------------


	-----------
	-- Products
	-----------

	--INSERT @eventsT	
	--SELECT 
	--	IH.ToLocal(CutStart, @timeZoneCode), 
	--	3, 
	--	'Prod - Strand: ' + ISNULL(CAST(StrandId AS NVARCHAR(1)), '')
	--FROM CAS.Products
	--WHERE ProcessId = @Id


	----------
	-- Strands
	----------
	
	--INSERT @eventsT	
	--SELECT 
	--	IH.ToLocal(StartCast, @timeZoneCode), 
	--	1, 
	--	ISNULL(CAST(StrandId AS NVARCHAR(1)), '')
	--FROM CAS.StrandProcesses	
	--WHERE ProcessID = @Id

	--INSERT @eventsT	
	--SELECT 
	--	IH.ToLocal(EndCast, @timeZoneCode), 
	--	1, 
	--	ISNULL(CAST(StrandId AS NVARCHAR(1)), '')
	--FROM CAS.StrandProcesses
	--WHERE ProcessId = @Id	


	-----------
	-- Process
	-----------


	INSERT @eventsT	
	SELECT 
		IH.ToLocal([LadleArrival], @timeZoneCode), 
		2, 
		CAST('Arrival' AS NVARCHAR(10))  
	FROM CAS.Processes
	WHERE
		Id = @id
	INSERT @eventsT	
	SELECT 
		IH.ToLocal([LadleInCastPosition], @timeZoneCode), 
		2, 
		CAST('In Cast' AS NVARCHAR(10))  
	FROM CAS.Processes
	WHERE
		Id = @id

	INSERT @eventsT	
	SELECT 
		IH.ToLocal([LadleOpen], @timeZoneCode), 
		2, 
		CAST('Open' AS NVARCHAR(10))  
	FROM CAS.Processes
	WHERE
		Id = @id

	
	INSERT @eventsT	
	SELECT 
		IH.ToLocal([LadleClose], @timeZoneCode), 
		2, 
		CAST('Close' AS NVARCHAR(10))  
	FROM CAS.Processes
	WHERE
		Id = @id
	
	-----------------------
	-- Tags internal
	-----------------------
	DECLARE @TagIdStrand1_StrandSpeed INT = (SELECT TOP 1 Id FROM IH.Tags WHERE [Name] = 'CAS1.STRAND1.Global.AT_Strand_Speed' AND [Deleted] = 0)
	DECLARE @TagIdStrand2_StrandSpeed INT = (SELECT TOP 1 Id FROM IH.Tags WHERE [Name] = 'CAS1.STRAND2.Global.AT_Strand_Speed' AND [Deleted] = 0)
	DECLARE @TagIdStrand3_StrandSpeed INT = (SELECT TOP 1 Id FROM IH.Tags WHERE [Name] = 'CAS1.STRAND3.Global.AT_Strand_Speed' AND [Deleted] = 0)
	DECLARE @TagIdStrand4_StrandSpeed INT = (SELECT TOP 1 Id FROM IH.Tags WHERE [Name] = 'CAS1.STRAND4.Global.AT_Strand_Speed' AND [Deleted] = 0)
	
	
	DECLARE @TagIdTundishActualNetWeight INT = (SELECT TOP 1 Id FROM IH.Tags WHERE [Name] = 'CAS1.COMMON.Global.AT_Tundish_Actual_Net_Weight' AND [Deleted] = 0)


	DECLARE @TagIdLadleNetWeight INT = (SELECT TOP 1 Id FROM IH.Tags WHERE [Name] = 'CAS1.COMMON.Global.AT_Ladle_Actual_Net_Weight' AND [Deleted] = 0)
	DECLARE @TagIdFinalWeight				INT = (SELECT TOP 1 Id FROM IH.Tags WHERE [Name] = 'CAS1.COMMON.Global.AT_Ladle_Final_Weight' AND [Deleted] = 0)


	INSERT INTO @tagsT (ID, Name, MinScale,MaxScale,LineColor,IsDigital,Pane)
		values (@TagIdStrand1_StrandSpeed, 'Speed 1', 0, 100,  '#FF0000', 0,0)

		
	INSERT INTO @tagsT (ID, Name, MinScale,MaxScale,LineColor,IsDigital,Pane)
		values (@TagIdStrand2_StrandSpeed, 'Speed 2', 0, 95,  '#00FF00', 0,0)


	INSERT INTO @tagsT (ID, Name, MinScale,MaxScale,LineColor,IsDigital,Pane)
		values (@TagIdStrand3_StrandSpeed, 'Speed 3', 0, 90,  '#8030A0', 0,0)


	INSERT INTO @tagsT (ID, Name, MinScale,MaxScale,LineColor,IsDigital,Pane)
		values (@TagIdStrand4_StrandSpeed, 'Speed 4', 0, 85,  '#f267b5', 0,0)

		
	INSERT INTO @tagsT (ID, Name, MinScale,MaxScale,LineColor,IsDigital,Pane)
		values (@TagIdTundishActualNetWeight, 'Tundish Weight', 0, 150000,  '#0000FF', 0,0)

		
		
	INSERT INTO @tagsT (ID, Name, MinScale,MaxScale,LineColor,IsDigital,Pane)
		values (@TagIdLadleNetWeight, 'Ladle Weight', -10000, 300000,  '#ddc315', 0,0)

		
		
	--INSERT INTO @tagsT (ID, Name, MinScale,MaxScale,LineColor,IsDigital,Pane)
	--	values (@TagIdFinalWeight, 'Final Weight', -10000, 40000,  '#000000', 0,0)
		
	SELECT 
		@tagList = COALESCE(@tagList + ', ', '') + CAST(ID AS VARCHAR)
	FROM @tagsT 
	

	-----------------------
	-- Table definitions
	-----------------------	
	SELECT 
		ROW_NUMBER () OVER (ORDER BY TableIndex), 
		Name 
	FROM (SELECT 
			[TableIndex] = 1,
			[Name] = 'Variables'
		UNION ALL
		SELECT 
			TOP 1 
			[TableIndex] = 2,
			[Name] = 'AnalogValues'	
		FROM @tagsT where IsDigital = 0
		UNION ALL
		SELECT 
			TOP 1 
			[TableIndex] = 3,
		[Name] = 'DigitalValues'
			FROM @tagsT where IsDigital = 1
		UNION ALL
		SELECT 
			TOP 1 
			[TableIndex] = 4,
			[Name] = 'Events'
		FROM @eventsT
		--UNION ALL
		--SELECT 
		--	 [TableIndex] = 5
		--	,[Name] = 'Sections'
		--	WHERE @startTap is not null
		) AS record
	ORDER BY 
		TableIndex	
	
	
	-----------------------
	-- Variables
	-----------------------
	SELECT
		*,
		IsEnabled = CAST(1 AS BIT),
		MarkerShape = 1, 
		MarkerShapeSize = 1
	FROM @tagsT


	-----------------------
	-- Analog values
	-----------------------
	IF (EXISTS(SELECT * FROM @tagsT WHERE IsDigital = 0))
	BEGIN
	   SELECT 
			ID = TagId,
			XValue = IH.ToLocal(XValue, NULL), 
			YValue 
		FROM IH.GetTagValues(-1, @tagList, @startDate, @endDate, @points, 'Real', 1)
		ORDER BY ID, XValue
	END


	-----------------------
	-- Digital values
	-----------------------
	IF (EXISTS(SELECT * FROM @tagsT WHERE IsDigital = 1))
	BEGIN
		SELECT 
			ID = TagId, 
			Start = IH.ToLocal(Start, NULL),
			[End] = IH.ToLocal([End], NULL),
			[Description] 
		FROM IH.GetDigitalEvents(-1, @tagList, @startDate, @endDate)
	END
	

	-----------------------
	-- Events
	-----------------------
	IF (EXISTS(SELECT * FROM @eventsT))
	BEGIN
		SELECT
			EventType,
			XValue = [Time],
			[Description],
			Pane = 0 --tag.Pane
		FROM @eventsT
		ORDER BY
			XValue
	END


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

	RETURN 0
END


