-------------
-- ROLES
-------------
	USE [IHBoxSystem]
	GO
	CREATE ROLE caster_torch_read AUTHORIZATION dbo 
	GO  

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

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

	CREATE ROLE caster_torch_readWrite AUTHORIZATION dbo 
	GO  

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

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


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

	INSERT INTO [FrontEnd].[Routes]
			   ([Route]
			   ,[ModuleName]
			   ,[ModulePath]
			   ,[RequiredRole]
		   )
		 VALUES 
			   (
			   '!/caster-torch(/)'
			   , 'caster-torch'
			   , 'custom-screens/caster-torch/'
			   , 'caster_torch_read'           
			   )
			   SET @result_routeId = @@IDENTITY
	INSERT INTO [FrontEnd].[Menu]
			   ([Parent]
			   ,[Name]
			   ,[RouteID]
			   ,[SortOrder]
			   ,[Enabled]
			   ,[Visible]
			   ,[RequiredRole]
			   ,[Group]
			  )
		 VALUES
			   (
			   @parent
			   ,'Caster Torch'
			   ,@result_routeId 
			   ,1
			   ,1
			   ,1
			   ,'caster_torch_read'
			   ,NULL
			 )
	GO


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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [caster_torch_read] -- The role
,@name = N'securedsp-CAS.SplitProduct' -- the procedure
,@value = N'' 
GO
-------------------
--	Profiles
-------------------
	USE [IHBoxSystem]
	GO

	DECLARE @ModuleId INT

	INSERT INTO [SECURITY].[Modules]([Code],[Name])
	VALUES('caster_torch','Caster Torch')

	SET @ModuleId = @@IDENTITY

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


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

	USE [MES]
GO
------------------------------
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
--	SP AND FUNCTIONS - 3 JUN
-----------------------------


/****** Object:  UserDefinedFunction [CAS].[GetEndCastOffset]    Script Date: 3 jun. 2021 23:27:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [CAS].[GetPercent]
(
	@BL float
	,@MLS float
	
	,@MLE float
)
RETURNS FLOAT
AS
BEGIN

	DECLARE @Percent float

	set @Percent = (@MLE-@BL)*100 / (@MLE-@MLS)

	RETURN @Percent
END


USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[GetTorchDataWeb]    Script Date: 3 jun. 2021 23:14:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [CAS].[GetTorchDataWeb]
	@Id int = 1
AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE
		--This prefix is used on all the prints of the procedure to tabulate them depending
		--its nested level.
		@printPrefix VARCHAR(200)
		,
		--The name of the procedure with its schema.
		@procName VARCHAR(300)
		,
		--The result of the procedure. If all goes ok it will be 0, else it will contain the code
		--of the error that has been thrown.
		@result INT
		,
		-- To be used for log.
		@logMsg VARCHAR(MAX)

	SELECT @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
		,@printPrefix = REPLICATE('    ', @@NESTLEVEL - 1)
		,@result = 0


	DECLARE @buf varchar(200)
	SET NOCOUNT ON

	declare @TransitionHeats table (
		 Strand int
		 ,HeatName varchar(50)
		 ,HeatStartLineML float	
		 ,PreviousHeatName varchar(50)
		 ,LineML float
		 ,Active   bit  
	)
	
	select 
			[SP_S1]			=544	
	,[SP_S2]				=546	
	,[SP_S3] 				=546	
	,[SP_S4] 				=545	
	,[Products_S1]      	=2.022223977	
	,[Products_S2]      	=1.80093149	
	,[Products_S3]      	=1.957911229	
	,[Products_S4]      	=2.698726885	
	,[Line_Length_S1]   	=1100.089844	
	,[Line_Length_S2]   	=983.3085938	
	,[Line_Length_S3]   	=1069.019531	
	,[Line_Length_S4]   	=1470.806152	
	,[MetLengthStart_S1]	=544.0429688	
	,[MetLengthStart_S2]	=546.4570313	
	,[MetLengthStart_S3]	=549.859375	
	,[MetLengthStart_S4]	=546.2700195	
	,Speed_S1				=32.20307922	
	,Speed_S2				=32.33927917	
	,Speed_S3				=32.54410934	
	,Speed_S4				=32.34093094	
	,TipML_S1           	=42789.28125	
	,TipML_S2           	=43127.58984	
	,TipML_S3           	=44082.17969	
	,TipML_S4           	=6689.288086	
	,MoldML_S1          	=43889.37109	
	,MoldML_S2          	=44110.89844	
	,MoldML_S3          	=45151.19922	
	,MoldML_S4          	=8160.094238	
	,HeatStartML_S1     	=43345.32813	
	,HeatStartML_S2     	=43564.44141	
	,HeatStartML_S3     	=44601.33984	
	,HeatStartML_S4     	=7613.824219	
	,[LastHeat_S1]      	='A195425'	
	,[LastHeat_S2]      	='A195425'	
	,[LastHeat_S3]      	='A195425'	
	,[LastHeat_S4]      	='A195425'	
	 into #data

	 select * from #data

	select top 4 
		[HeatName]
		,S1Products = (select count(*) from cas.Products z where ProcessId=p.id and z.strandId = s1.StrandId)
		,S2Products = (select count(*) from cas.Products z where ProcessId=p.id and z.strandId = s2.StrandId)
		,S3Products = (select count(*) from cas.Products z where ProcessId=p.id and z.strandId = s3.StrandId)
		,S4Products = (select count(*) from cas.Products z where ProcessId=p.id and z.strandId = s4.StrandId)
		,[MeltOrder] = ld.MeltOrderNumber
		,ProductsWeight = cast( (select ISNULL(sum(z.[Weight]),0) from cas.Products z where ProcessId=p.id and z.strandId = s1.StrandId) as int)
		+ cast( (select ISNULL(sum(z.[Weight]),0) from cas.Products z where ProcessId=p.id and z.strandId = s2.StrandId) as int)
		+ cast( (select ISNULL(sum(z.[Weight]),0) from cas.Products z where ProcessId=p.id and z.strandId = s3.StrandId) as int)
		+ cast( (select ISNULL(sum(z.[Weight]),0) from cas.Products z where ProcessId=p.id and z.strandId = s4.StrandId) as int)
		,[Grade] = cg.[Name]
		,LiqWeight = cast(h.SteelWeight as decimal(10,2))
		,[Status] = ch.[Name]
		,[SPLength] = mo.AimLength
		
	from cas.Processes p 	
	left join mes.heats h on h.id = p.HeatId
	left join  CAS.StrandProcesses s1 on s1.ProcessId = p.id  and s1.strandid=1
	left join  CAS.StrandProcesses s2 on s2.ProcessId = p.id  and s2.strandid=2
	left join  CAS.StrandProcesses s3 on s3.ProcessId = p.id  and s3.strandid=3
	left join  CAS.StrandProcesses s4 on s4.ProcessId = p.id  and s4.strandid=4
	left join lad.heats ld on ld.HeatNumber = p.HeatName
	left join cat.Grades cg on cg.Id = h.GradeId
	left join LAD.catHeatStatus ch on ch.HeatStatusID = ld.HeatStatusID
	left join  lad.MeltOrders mo on mo.MeltOrderNumber = ld.MeltOrderNumber
	where p.CasterCode = 1
	order by p.[LadleOpen] desc

	select top 8 
		HeatName
		,StrandId
		,[Sequence]
		,MetLengthStart	= cast(MetLengthStart  as decimal(10,2))
		,MetLengthEnd   = cast(MetLengthEnd    as decimal(10,2))
	 from cas.Products order by [Datetime] desc,StrandId asc,[Sequence] asc


	 
	
	insert into @TransitionHeats
	values
	(1,'A195425'	,544.0429688	,'A195424'	,1100.089844,	1)
	,(1,'A195424'	,951.800781	,'UNKNOWN3'	,1100.089844,	1)
	,(1,'UNKNOWN3'	,2863.222656	,NULL		,1100.089844,	0)
	,(2,'A195425'	,546.4570313	,'A195424'	,983.3085938,	1)
	,(2,'A195424'	,1718.886719	,'UNKNOWN3'	,983.3085938,	0)
	,(2,'UNKNOWN3'	,2850.476563	,NULL			,983.3085938,	0)
	,(3,'A195425'	,549.859375		,'A195424'	,1069.019531,	1)
	,(3,'A195424'	,1730.738281	,'UNKNOWN3'	,1069.019531,	0)
	,(3,'UNKNOWN3'	,2875.050781	,NULL		,1069.019531,	0)
	,(4,'A195425'	,546.2700195	,'A195424'	,1470.806152,	1)
	,(4,'A195424'	,1732.745117	,'UNKNOWN3'	,1470.806152,	0)
	,(4,'UNKNOWN3'	,2885.806152	,NULL		,1470.806152,	0)
	

	
	select * from @TransitionHeats
	where Active = 1
	
	select distinct(HeatName) from @TransitionHeats
	

	--;with cte as(
	--	select 
	--		1 as Strand
	--		,[Products_S1] as TotalProducts
	--		,cast([Products_S1] as int) as FinishedProduct
	--		,[Products_S1]  -cast([Products_S1] as int) as UnfinishedProduct
	--		,Line_Length_S1 as LineML
	--		,UnfinishedProduct_MLE = ([Products_S1]  -cast([Products_S1] as int)) * Line_Length_S1 / [Products_S1]
	--		,FirstSolidProduct_MLE = ([Products_S1]  -cast([Products_S1] as int)) * Line_Length_S1 / [Products_S1] + Line_Length_S1 /[Products_S1]
	--	from #data 

	--	union all

	--	select 
	--		2 as Strand
	--		,[Products_S2] as TotalProducts
	--		,cast([Products_S2] as int) as FinishedProduct
	--		,[Products_S2]  -cast([Products_S2] as int) as UnfinishedProduct
	--		,Line_Length_S2 as LineML
	--		,UnfinishedProduct_MLE = ([Products_S2]  -cast([Products_S2] as int)) * Line_Length_S2 / [Products_S2]
	--		,FirstSolidProduct_MLE = ([Products_S2]  -cast([Products_S2] as int)) * Line_Length_S2 / [Products_S2] + Line_Length_S2 /[Products_S2]
	--	from #data
	--	union all

	--	select 
	--		3 as Strand
	--		,[Products_S3] as TotalProducts
	--		,cast([Products_S3] as int) as FinishedProduct
	--		,[Products_S3]  -cast([Products_S3] as int) as UnfinishedProduct
	--		,Line_Length_S3 as LineML
	--		,UnfinishedProduct_MLE = ([Products_S3]  -cast([Products_S3] as int)) * Line_Length_S3 / [Products_S3]			
	--		,FirstSolidProduct_MLE = ([Products_S3]  -cast([Products_S3] as int)) * Line_Length_S3 / [Products_S3] + Line_Length_S3 /[Products_S3]
	--	from #data

	--	union all

	--	select 
	--		4 as Strand
	--		,[Products_S4] as TotalProducts
	--		,cast([Products_S4] as int) as FinishedProduct
	--		,[Products_S4]  -cast([Products_S4] as int) as UnfinishedProduct
	--		,Line_Length_S4 as LineML
	--		,UnfinishedProduct_MLE = ([Products_S4]  -cast([Products_S4] as int)) * Line_Length_S4 / [Products_S4]
			
	--		,FirstSolidProduct_MLE = ([Products_S4]  -cast([Products_S4] as int)) * Line_Length_S4 / [Products_S4] + Line_Length_S4 /[Products_S4]
	--	from #data
	--)
	--select * from cte

	-------------------------------
	------- TEST 1
	------------------------------

	--declare @products table (strand int, TotalProducts float,Quantity int,FractionProduct float, LineML float, MLS float, MLE float)
	--declare @products2 table (strand int, TotalProducts float,Quantity int,FractionProduct float, LineML float, MLS float, MLE float,Series int)


	--insert into @products(strand,TotalProducts,Quantity,LineML,MLS,MLE)
	--	select 
	--		1 as Strand
	--		,[Products_S1] as TotalProducts
	--		,cast([Products_S1] as int) + 1 as Quantity 
	--		,Line_Length_S1 as LineML
	--		,NULL
	--		,NULL
	--	from #data 


	--select * from @products
	
	 
	--;with t as (
		 
	--	 select 
	--		strand
	--		,1 as start
	--		,TotalProducts
	--		,Quantity
	--		,cast([TotalProducts] - cast([TotalProducts] as int) as decimal(10,3)) as FractionProduct
	--		,LineML
	--		,0 as MLS
	--		,([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts] as MLE 
	--	 from @products


	--	 union all
	--	 select 
	--		strand
	--		,start + 1
	--		,TotalProducts
	--		,Quantity
	--		, cast(1.0 as decimal(10,3)) as FractionProduct
	--		,LineML
	--		,NULL as MLS
	--		,NULL as MLE
	--	 from t
	--	 where start < Quantity
	-- ) 
	--insert into @products2
	--select strand, TotalProducts,Quantity,FractionProduct,LineML,MLS,MLE, start as Series  
	--from t; 
	
	-----------------------------
	----- TEST 2
	----------------------------

	declare @products table (strand int, TotalProducts float,Quantity int,FractionProduct float, LineML float, MLS float, Width float)
	declare @products2 table (strand int, TotalProducts float,Quantity int,FractionProduct float, LineML float, MLS float, Width float,Series int)
	declare @products3 table (strand int, TotalProducts float,Quantity int,FractionProduct float, LineML float, MLS float, Width float,Series int,MLE float, BL1_PrevHeat varchar(50), BL1 float, BL1_Heat varchar(50), BL2_PrevHeat varchar(50), BL2 float, BL2_Heat varchar(50))


	insert into @products(strand,TotalProducts,Quantity,LineML,MLS,Width)
		select 
			1 as Strand
			,[Products_S1] as TotalProducts
			,cast([Products_S1] as int) + 1 as Quantity 
			,Line_Length_S1 as LineML
			,NULL
			,NULL
		from #data 

			insert into @products(strand,TotalProducts,Quantity,LineML,MLS,Width)
		select 
			2 as Strand
			,[Products_S2] as TotalProducts
			,cast([Products_S2] as int) + 1 as Quantity 
			,Line_Length_S2 as LineML
			,NULL
			,NULL
		from #data 

		
			insert into @products(strand,TotalProducts,Quantity,LineML,MLS,Width)
		select 
			3 as Strand
			,[Products_S3] as TotalProducts
			,cast([Products_S3] as int) + 1 as Quantity 
			,Line_Length_S3 as LineML
			,NULL
			,NULL
		from #data 

				insert into @products(strand,TotalProducts,Quantity,LineML,MLS,Width)
		select 
			4 as Strand
			,[Products_S4] as TotalProducts
			,cast([Products_S4] as int) + 1 as Quantity 
			,Line_Length_S4 as LineML
			,NULL
			,NULL
		from #data 

	 
	;with t as (
		 
		 select 
			strand
			,1 as start
			,TotalProducts
			,Quantity
			,cast([TotalProducts] - cast([TotalProducts] as int) as decimal(10,3)) as FractionProduct
			,LineML
			,cast(0.0 as float) as MLS
			,([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts] as Width 
		 from @products
		 where strand = 1


		 union all
		 select 
			strand
			,start + 1
			,TotalProducts
			,Quantity
			, cast(1.0 as decimal(10,3)) as FractionProduct
			,LineML
			,case when [start] = 1 then cast ((([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts]) as float)  else 
			
			cast ((([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts]) as float) + cast(LineML/ TotalProducts as float) *  cast(([Start] - 1) as float) end as MLS

			 
			,LineML/ TotalProducts as Width
		 from t
		 where start < Quantity
	 ) 
	insert into @products2
	select strand, TotalProducts,Quantity,FractionProduct,LineML,MLS,Width, start as Series  
	from t; 


	;with t as (
		 
		 select 
			strand
			,1 as start
			,TotalProducts
			,Quantity
			,cast([TotalProducts] - cast([TotalProducts] as int) as decimal(10,3)) as FractionProduct
			,LineML
			,cast(0.0 as float) as MLS
			,([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts] as Width 
		 from @products
		 where strand = 2


		 union all
		 select 
			strand
			,start + 1
			,TotalProducts
			,Quantity
			, cast(1.0 as decimal(10,3)) as FractionProduct
			,LineML
			,case when [start] = 1 then cast ((([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts]) as float)  else 
			
			cast ((([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts]) as float) + cast(LineML/ TotalProducts as float) *  cast(([Start] - 1) as float) end as MLS

			 
			,LineML/ TotalProducts as Width
		 from t
		 where start < Quantity
	 ) 
	insert into @products2
	select strand, TotalProducts,Quantity,FractionProduct,LineML,MLS,Width, start as Series  
	from t; 

		;with t as (
		 
		 select 
			strand
			,1 as start
			,TotalProducts
			,Quantity
			,cast([TotalProducts] - cast([TotalProducts] as int) as decimal(10,3)) as FractionProduct
			,LineML
			,cast(0.0 as float) as MLS
			,([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts] as Width 
		 from @products
		 where strand = 3


		 union all
		 select 
			strand
			,start + 1
			,TotalProducts
			,Quantity
			, cast(1.0 as decimal(10,3)) as FractionProduct
			,LineML
			,case when [start] = 1 then cast ((([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts]) as float)  else 
			
			cast ((([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts]) as float) + cast(LineML/ TotalProducts as float) *  cast(([Start] - 1) as float) end as MLS

			 
			,LineML/ TotalProducts as Width
		 from t
		 where start < Quantity
	 ) 
	insert into @products2
	select strand, TotalProducts,Quantity,FractionProduct,LineML,MLS,Width, start as Series 
	from t; 


		;with t as (
		 
		 select 
			strand
			,1 as start
			,TotalProducts
			,Quantity
			,cast([TotalProducts] - cast([TotalProducts] as int) as decimal(10,3)) as FractionProduct
			,LineML
			,cast(0.0 as float) as MLS
			,([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts] as Width 
		 from @products
		 where strand = 4


		 union all
		 select 
			strand
			,start + 1
			,TotalProducts
			,Quantity
			, cast(1.0 as decimal(10,3)) as FractionProduct
			,LineML
			,case when [start] = 1 then cast ((([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts]) as float)  else 
			
			cast ((([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts]) as float) + cast(LineML/ TotalProducts as float) *  cast(([Start] - 1) as float) end as MLS

			 
			,LineML/ TotalProducts as Width
		 from t
		 where start < Quantity
	 ) 
	insert into @products2
	select strand, TotalProducts,Quantity,FractionProduct,LineML,MLS,Width, start as Series  
	from t; 



	insert into @products3
	select *
	,[MLE]= [MLS] + Width


	
	,(select  top 1 z.PreviousHeatName from @TransitionHeats z where z.Strand = p2.strand and Active = 1 order by z.HeatStartLineML desc )
	,(select  top 1 z.HeatStartLineML from @TransitionHeats z where z.Strand = p2.strand and Active = 1 order by z.HeatStartLineML desc)
	,(select  top 1 z.HeatName from @TransitionHeats z where z.Strand = p2.strand and Active = 1 order by z.HeatStartLineML desc )	
	
	,(select  top 1 z.PreviousHeatName from @TransitionHeats z where z.Strand = p2.strand and Active = 1 order by z.HeatStartLineML asc )	
	,(select  top 1 z.HeatStartLineML from @TransitionHeats z where z.Strand = p2.strand and Active = 1 order by z.HeatStartLineML asc )	
	
	,(select  top 1 z.HeatName from @TransitionHeats z where z.Strand = p2.strand and Active = 1 order by z.HeatStartLineML asc )		

		
	from @products2 p2

	select *
	,[Heat] = case when BL1 between MLS and MLE then ( case when [CAS].[GetPercent](BL1,MLS,MLE) > 50 then BL1_Heat else BL1_PrevHeat end) --percent calculation
			    when BL2 between MLS and MLE then ( case when [CAS].[GetPercent](BL2,MLS,MLE) > 50 then BL2_Heat else BL2_PrevHeat end)	 --percent calculation
				when MLE between BL2 and BL1 then BL1_Heat
				when MLE < BL2 then BL2_Heat
				when MLE > BL1 then BL1_PrevHeat
				end 
	,[Unfinished] = case when FractionProduct < 1 then 1 else 0 end 
	from @products3

	--select *
	--,[Heat] = case when Bo
	--				when MLS > BoundaryLine1 and MLS > BoundaryLine2 then BL2_PrevHeat
	--				when 
	
	--from @products3

	--update P2
	--	set [MLS] = ( select z.Width + z.MLS  from @products2 z where z.[Series] = P2.[Series] -1) 
		
	--from @products2 P2
	--where P2.[Series] > 1

	--	update P2
	--	set [MLS] = ( select z.Width + z.MLS  from @products2 z where z.[Series] = P2.[Series] -1) 
		
	--from @products2 P2
	--where P2.[Series] > 2
	
	-----------------------
	-- Finish procedure
	-----------------------
	RETURN 0
END

GO 
exec [CAS].[GetTorchDataWeb]


------------------------------
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
--	SP AND FUNCTIONS - 4 JUN
-----------------------------
USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[GetTorchDataWeb]    Script Date: 4 jun. 2021 09:28:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [CAS].[GetTorchDataWeb]
	@Id int = 1
AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE
		--This prefix is used on all the prints of the procedure to tabulate them depending
		--its nested level.
		@printPrefix VARCHAR(200)
		,
		--The name of the procedure with its schema.
		@procName VARCHAR(300)
		,
		--The result of the procedure. If all goes ok it will be 0, else it will contain the code
		--of the error that has been thrown.
		@result INT
		,
		-- To be used for log.
		@logMsg VARCHAR(MAX)

	SELECT @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
		,@printPrefix = REPLICATE('    ', @@NESTLEVEL - 1)
		,@result = 0


	DECLARE @buf varchar(200)
	SET NOCOUNT ON

	declare @TransitionHeats table (
		 Strand int
		 ,HeatName varchar(50)
		 ,HeatStartLineML float	
		 ,PreviousHeatName varchar(50)
		 ,LineML float
		 ,Active   bit  
	)
	
	select 
			[SP_S1]			=544	
	,[SP_S2]				=546	
	,[SP_S3] 				=546	
	,[SP_S4] 				=545	
	,[Products_S1]      	=2.022223977	
	,[Products_S2]      	=1.80093149	
	,[Products_S3]      	=1.957911229	
	,[Products_S4]      	=2.698726885	
	,[Line_Length_S1]   	=1100.089844	
	,[Line_Length_S2]   	=983.3085938	
	,[Line_Length_S3]   	=1069.019531	
	,[Line_Length_S4]   	=1470.806152	
	,[MetLengthStart_S1]	=544.0429688	
	,[MetLengthStart_S2]	=546.4570313	
	,[MetLengthStart_S3]	=549.859375	
	,[MetLengthStart_S4]	=546.2700195	
	,Speed_S1				=32.20307922	
	,Speed_S2				=32.33927917	
	,Speed_S3				=32.54410934	
	,Speed_S4				=32.34093094	
	,TipML_S1           	=42789.28125	
	,TipML_S2           	=43127.58984	
	,TipML_S3           	=44082.17969	
	,TipML_S4           	=6689.288086	
	,MoldML_S1          	=43889.37109	
	,MoldML_S2          	=44110.89844	
	,MoldML_S3          	=45151.19922	
	,MoldML_S4          	=8160.094238	
	,HeatStartML_S1     	=43345.32813	
	,HeatStartML_S2     	=43564.44141	
	,HeatStartML_S3     	=44601.33984	
	,HeatStartML_S4     	=7613.824219	
	,[LastHeat_S1]      	='A195425'	
	,[LastHeat_S2]      	='A195425'	
	,[LastHeat_S3]      	='A195425'	
	,[LastHeat_S4]      	='A195425'	
	 into #data

	 select * from #data

	select top 4 
		[HeatName]
		,S1Products = (select count(*) from cas.Products z where ProcessId=p.id and z.strandId = s1.StrandId)
		,S2Products = (select count(*) from cas.Products z where ProcessId=p.id and z.strandId = s2.StrandId)
		,S3Products = (select count(*) from cas.Products z where ProcessId=p.id and z.strandId = s3.StrandId)
		,S4Products = (select count(*) from cas.Products z where ProcessId=p.id and z.strandId = s4.StrandId)
		,[MeltOrder] = ld.MeltOrderNumber
		,ProductsWeight = cast( (select ISNULL(sum(z.[Weight]),0) from cas.Products z where ProcessId=p.id and z.strandId = s1.StrandId) as int)
		+ cast( (select ISNULL(sum(z.[Weight]),0) from cas.Products z where ProcessId=p.id and z.strandId = s2.StrandId) as int)
		+ cast( (select ISNULL(sum(z.[Weight]),0) from cas.Products z where ProcessId=p.id and z.strandId = s3.StrandId) as int)
		+ cast( (select ISNULL(sum(z.[Weight]),0) from cas.Products z where ProcessId=p.id and z.strandId = s4.StrandId) as int)
		,[Grade] = cg.[Name]
		,LiqWeight = cast(h.SteelWeight as decimal(10,2))
		,[Status] = ch.[Name]
		,[SPLength] = mo.AimLength
		
	from cas.Processes p 	
	left join mes.heats h on h.id = p.HeatId
	left join  CAS.StrandProcesses s1 on s1.ProcessId = p.id  and s1.strandid=1
	left join  CAS.StrandProcesses s2 on s2.ProcessId = p.id  and s2.strandid=2
	left join  CAS.StrandProcesses s3 on s3.ProcessId = p.id  and s3.strandid=3
	left join  CAS.StrandProcesses s4 on s4.ProcessId = p.id  and s4.strandid=4
	left join lad.heats ld on ld.HeatNumber = p.HeatName
	left join cat.Grades cg on cg.Id = h.GradeId
	left join LAD.catHeatStatus ch on ch.HeatStatusID = ld.HeatStatusID
	left join  lad.MeltOrders mo on mo.MeltOrderNumber = ld.MeltOrderNumber
	where p.CasterCode = 1
	order by p.[LadleOpen] desc

	select top 8 
		HeatName
		,StrandId
		,[Sequence]
		,MetLengthStart	= cast(MetLengthStart  as decimal(10,2))
		,MetLengthEnd   = cast(MetLengthEnd    as decimal(10,2))
	 from cas.Products order by [Datetime] desc,StrandId asc,[Sequence] asc


	 
	
	insert into @TransitionHeats
	values
	(1,'A195425'	,544.0429688	,'A195424'	,1100.089844,	1)
	,(1,'A195424'	,951.800781	,'UNKNOWN3'	,1100.089844,	1)
	,(1,'UNKNOWN3'	,2863.222656	,NULL		,1100.089844,	0)
	,(2,'A195425'	,546.4570313	,'A195424'	,983.3085938,	1)
	,(2,'A195424'	,1718.886719	,'UNKNOWN3'	,983.3085938,	0)
	,(2,'UNKNOWN3'	,2850.476563	,NULL			,983.3085938,	0)
	,(3,'A195425'	,549.859375		,'A195424'	,1069.019531,	1)
	,(3,'A195424'	,1730.738281	,'UNKNOWN3'	,1069.019531,	0)
	,(3,'UNKNOWN3'	,2875.050781	,NULL		,1069.019531,	0)
	,(4,'A195425'	,546.2700195	,'A195424'	,1470.806152,	1)
	,(4,'A195424'	,1732.745117	,'UNKNOWN3'	,1470.806152,	0)
	,(4,'UNKNOWN3'	,2885.806152	,NULL		,1470.806152,	0)
	

	
	select * from @TransitionHeats
	where Active = 1
	
	declare @ActiveHeats table(HeatName varchar(50),MaxProductSequence int, MaxProcessId int)


	insert into @ActiveHeats(HeatName)
	select distinct(HeatName) 
	from @TransitionHeats

	update AH
		set AH.MaxProcessId = (select max(ProcessId) from cas.Products z where z.HeatName = AH.HeatName )
	from @ActiveHeats AH
	
	update AH
		set AH.MaxProductSequence = (select max([Sequence]) from cas.Products z where z.HeatName = AH.HeatName and z.ProcessId = AH.MaxProcessId)
	from @ActiveHeats AH

	select * from @ActiveHeats
	--;with cte as(
	--	select 
	--		1 as Strand
	--		,[Products_S1] as TotalProducts
	--		,cast([Products_S1] as int) as FinishedProduct
	--		,[Products_S1]  -cast([Products_S1] as int) as UnfinishedProduct
	--		,Line_Length_S1 as LineML
	--		,UnfinishedProduct_MLE = ([Products_S1]  -cast([Products_S1] as int)) * Line_Length_S1 / [Products_S1]
	--		,FirstSolidProduct_MLE = ([Products_S1]  -cast([Products_S1] as int)) * Line_Length_S1 / [Products_S1] + Line_Length_S1 /[Products_S1]
	--	from #data 

	--	union all

	--	select 
	--		2 as Strand
	--		,[Products_S2] as TotalProducts
	--		,cast([Products_S2] as int) as FinishedProduct
	--		,[Products_S2]  -cast([Products_S2] as int) as UnfinishedProduct
	--		,Line_Length_S2 as LineML
	--		,UnfinishedProduct_MLE = ([Products_S2]  -cast([Products_S2] as int)) * Line_Length_S2 / [Products_S2]
	--		,FirstSolidProduct_MLE = ([Products_S2]  -cast([Products_S2] as int)) * Line_Length_S2 / [Products_S2] + Line_Length_S2 /[Products_S2]
	--	from #data
	--	union all

	--	select 
	--		3 as Strand
	--		,[Products_S3] as TotalProducts
	--		,cast([Products_S3] as int) as FinishedProduct
	--		,[Products_S3]  -cast([Products_S3] as int) as UnfinishedProduct
	--		,Line_Length_S3 as LineML
	--		,UnfinishedProduct_MLE = ([Products_S3]  -cast([Products_S3] as int)) * Line_Length_S3 / [Products_S3]			
	--		,FirstSolidProduct_MLE = ([Products_S3]  -cast([Products_S3] as int)) * Line_Length_S3 / [Products_S3] + Line_Length_S3 /[Products_S3]
	--	from #data

	--	union all

	--	select 
	--		4 as Strand
	--		,[Products_S4] as TotalProducts
	--		,cast([Products_S4] as int) as FinishedProduct
	--		,[Products_S4]  -cast([Products_S4] as int) as UnfinishedProduct
	--		,Line_Length_S4 as LineML
	--		,UnfinishedProduct_MLE = ([Products_S4]  -cast([Products_S4] as int)) * Line_Length_S4 / [Products_S4]
			
	--		,FirstSolidProduct_MLE = ([Products_S4]  -cast([Products_S4] as int)) * Line_Length_S4 / [Products_S4] + Line_Length_S4 /[Products_S4]
	--	from #data
	--)
	--select * from cte

	-------------------------------
	------- TEST 1
	------------------------------

	--declare @products table (strand int, TotalProducts float,Quantity int,FractionProduct float, LineML float, MLS float, MLE float)
	--declare @products2 table (strand int, TotalProducts float,Quantity int,FractionProduct float, LineML float, MLS float, MLE float,Series int)


	--insert into @products(strand,TotalProducts,Quantity,LineML,MLS,MLE)
	--	select 
	--		1 as Strand
	--		,[Products_S1] as TotalProducts
	--		,cast([Products_S1] as int) + 1 as Quantity 
	--		,Line_Length_S1 as LineML
	--		,NULL
	--		,NULL
	--	from #data 


	--select * from @products
	
	 
	--;with t as (
		 
	--	 select 
	--		strand
	--		,1 as start
	--		,TotalProducts
	--		,Quantity
	--		,cast([TotalProducts] - cast([TotalProducts] as int) as decimal(10,3)) as FractionProduct
	--		,LineML
	--		,0 as MLS
	--		,([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts] as MLE 
	--	 from @products


	--	 union all
	--	 select 
	--		strand
	--		,start + 1
	--		,TotalProducts
	--		,Quantity
	--		, cast(1.0 as decimal(10,3)) as FractionProduct
	--		,LineML
	--		,NULL as MLS
	--		,NULL as MLE
	--	 from t
	--	 where start < Quantity
	-- ) 
	--insert into @products2
	--select strand, TotalProducts,Quantity,FractionProduct,LineML,MLS,MLE, start as Series  
	--from t; 
	
	-----------------------------
	----- TEST 2
	----------------------------

	declare @products table (strand int, TotalProducts float,Quantity int,FractionProduct float, LineML float, MLS float, Width float)
	declare @products2 table (strand int, TotalProducts float,Quantity int,FractionProduct float, LineML float, MLS float, Width float,Series int)
	
	insert into @products(strand,TotalProducts,Quantity,LineML,MLS,Width)
		select 
			1 as Strand
			,[Products_S1] as TotalProducts
			,cast([Products_S1] as int) + 1 as Quantity 
			,Line_Length_S1 as LineML
			,NULL
			,NULL
		from #data 

			insert into @products(strand,TotalProducts,Quantity,LineML,MLS,Width)
		select 
			2 as Strand
			,[Products_S2] as TotalProducts
			,cast([Products_S2] as int) + 1 as Quantity 
			,Line_Length_S2 as LineML
			,NULL
			,NULL
		from #data 

		
			insert into @products(strand,TotalProducts,Quantity,LineML,MLS,Width)
		select 
			3 as Strand
			,[Products_S3] as TotalProducts
			,cast([Products_S3] as int) + 1 as Quantity 
			,Line_Length_S3 as LineML
			,NULL
			,NULL
		from #data 

				insert into @products(strand,TotalProducts,Quantity,LineML,MLS,Width)
		select 
			4 as Strand
			,[Products_S4] as TotalProducts
			,cast([Products_S4] as int) + 1 as Quantity 
			,Line_Length_S4 as LineML
			,NULL
			,NULL
		from #data 

	 
	;with t as (
		 
		 select 
			strand
			,1 as start
			,TotalProducts
			,Quantity
			,cast([TotalProducts] - cast([TotalProducts] as int) as decimal(10,3)) as FractionProduct
			,LineML
			,cast(0.0 as float) as MLS
			,([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts] as Width 
		 from @products
		 where strand = 1


		 union all
		 select 
			strand
			,start + 1
			,TotalProducts
			,Quantity
			, cast(1.0 as decimal(10,3)) as FractionProduct
			,LineML
			,case when [start] = 1 then cast ((([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts]) as float)  else 
			
			cast ((([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts]) as float) + cast(LineML/ TotalProducts as float) *  cast(([Start] - 1) as float) end as MLS

			 
			,LineML/ TotalProducts as Width
		 from t
		 where start < Quantity
	 ) 
	insert into @products2
	select strand, TotalProducts,Quantity,FractionProduct,LineML,MLS,Width, start as Series  
	from t; 


	;with t as (
		 
		 select 
			strand
			,1 as start
			,TotalProducts
			,Quantity
			,cast([TotalProducts] - cast([TotalProducts] as int) as decimal(10,3)) as FractionProduct
			,LineML
			,cast(0.0 as float) as MLS
			,([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts] as Width 
		 from @products
		 where strand = 2


		 union all
		 select 
			strand
			,start + 1
			,TotalProducts
			,Quantity
			, cast(1.0 as decimal(10,3)) as FractionProduct
			,LineML
			,case when [start] = 1 then cast ((([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts]) as float)  else 
			
			cast ((([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts]) as float) + cast(LineML/ TotalProducts as float) *  cast(([Start] - 1) as float) end as MLS

			 
			,LineML/ TotalProducts as Width
		 from t
		 where start < Quantity
	 ) 
	insert into @products2
	select strand, TotalProducts,Quantity,FractionProduct,LineML,MLS,Width, start as Series  
	from t; 

		;with t as (
		 
		 select 
			strand
			,1 as start
			,TotalProducts
			,Quantity
			,cast([TotalProducts] - cast([TotalProducts] as int) as decimal(10,3)) as FractionProduct
			,LineML
			,cast(0.0 as float) as MLS
			,([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts] as Width 
		 from @products
		 where strand = 3


		 union all
		 select 
			strand
			,start + 1
			,TotalProducts
			,Quantity
			, cast(1.0 as decimal(10,3)) as FractionProduct
			,LineML
			,case when [start] = 1 then cast ((([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts]) as float)  else 
			
			cast ((([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts]) as float) + cast(LineML/ TotalProducts as float) *  cast(([Start] - 1) as float) end as MLS

			 
			,LineML/ TotalProducts as Width
		 from t
		 where start < Quantity
	 ) 
	insert into @products2
	select strand, TotalProducts,Quantity,FractionProduct,LineML,MLS,Width, start as Series 
	from t; 


		;with t as (
		 
		 select 
			strand
			,1 as start
			,TotalProducts
			,Quantity
			,cast([TotalProducts] - cast([TotalProducts] as int) as decimal(10,3)) as FractionProduct
			,LineML
			,cast(0.0 as float) as MLS
			,([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts] as Width 
		 from @products
		 where strand = 4


		 union all
		 select 
			strand
			,start + 1
			,TotalProducts
			,Quantity
			, cast(1.0 as decimal(10,3)) as FractionProduct
			,LineML
			,case when [start] = 1 then cast ((([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts]) as float)  else 
			
			cast ((([TotalProducts] - cast([TotalProducts] as int))*LineML /[TotalProducts]) as float) + cast(LineML/ TotalProducts as float) *  cast(([Start] - 1) as float) end as MLS

			 
			,LineML/ TotalProducts as Width
		 from t
		 where start < Quantity
	 ) 
	insert into @products2
	select strand, TotalProducts,Quantity,FractionProduct,LineML,MLS,Width, start as Series  
	from t; 


	declare @products3 table (strand int, TotalProducts float,Quantity int,FractionProduct float, LineML float, MLS float, Width float,Series int,MLE float
	, BL1_PrevHeat varchar(50), BL1 float, BL1_Heat varchar(50), BL2_PrevHeat varchar(50), BL2 float, BL2_Heat varchar(50))



	declare @products4 table (strand int, TotalProducts float,Quantity int,FractionProduct float, LineML float, MLS float, Width float,Series int,MLE float
	, BL1_PrevHeat varchar(50), BL1 float, BL1_Heat varchar(50), BL2_PrevHeat varchar(50), BL2 float, BL2_Heat varchar(50), Heat varchar(50), [Unfinished] bit, [Sequence] int, [ProductNumber] int)

	insert into @products3
	select *
		,[MLE]= [MLS] + Width	
		,(select  top 1 z.PreviousHeatName from @TransitionHeats z where z.Strand = p2.strand and Active = 1 order by z.HeatStartLineML desc )
		,(select  top 1 z.HeatStartLineML from @TransitionHeats z where z.Strand = p2.strand and Active = 1 order by z.HeatStartLineML desc)
		,(select  top 1 z.HeatName from @TransitionHeats z where z.Strand = p2.strand and Active = 1 order by z.HeatStartLineML desc )	
		,(select  top 1 z.PreviousHeatName from @TransitionHeats z where z.Strand = p2.strand and Active = 1 order by z.HeatStartLineML asc )	
		,(select  top 1 z.HeatStartLineML from @TransitionHeats z where z.Strand = p2.strand and Active = 1 order by z.HeatStartLineML asc )		
		,(select  top 1 z.HeatName from @TransitionHeats z where z.Strand = p2.strand and Active = 1 order by z.HeatStartLineML asc )	

	from @products2 p2

	insert into @products4
	select *
	,[Heat] = case when BL1 between MLS and MLE then ( case when [CAS].[GetPercent](BL1,MLS,MLE) > 50 then BL1_Heat else BL1_PrevHeat end) --percent calculation
			    when BL2 between MLS and MLE then ( case when [CAS].[GetPercent](BL2,MLS,MLE) > 50 then BL2_Heat else BL2_PrevHeat end)	 --percent calculation
				when MLE between BL2 and BL1 then BL1_Heat
				when MLE < BL2 then BL2_Heat
				when MLE > BL1 then BL1_PrevHeat
				end 
	,[Unfinished] = case when FractionProduct < 1 then 1 else 0 end 
	,[Sequence] =ROW_NUMBER() OVER(partition by strand order by Series desc)
	,NULL
	from @products3 p3
	order by strand asc, Series desc

	UPDATE P4
		set [ProductNumber] = (select ISNULL(z.MaxProductSequence,0) + ISNULL(P4.[Sequence],0) from @ActiveHeats z where P4.Heat = z.HeatName)
	FROM @products4 P4

	select * from @products4

	-----------------------
	-- Finish procedure
	-----------------------
	RETURN 0
END

GO

exec [CAS].[GetTorchDataWeb]





	
	

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

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



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

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

/*--------------------------------------------------------------------09 Nov 2021------------------------------------------------------------------------------------*/
/*--------------------------------------------------------------------09 Nov 2021------------------------------------------------------------------------------------*/
/*--------------------------------------------------------------------09 Nov 2021------------------------------------------------------------------------------------*/
/*--------------------------------------------------------------------09 Nov 2021------------------------------------------------------------------------------------*/
/*--------------------------------------------------------------------09 Nov 2021------------------------------------------------------------------------------------*/
/*--------------------------------------------------------------------09 Nov 2021------------------------------------------------------------------------------------*/
/*--------------------------------------------------------------------09 Nov 2021------------------------------------------------------------------------------------*/
/*--------------------------------------------------------------------09 Nov 2021------------------------------------------------------------------------------------*/
/*--------------------------------------------------------------------09 Nov 2021------------------------------------------------------------------------------------*/
/*--------------------------------------------------------------------09 Nov 2021------------------------------------------------------------------------------------*/
/*--------------------------------------------------------------------09 Nov 2021------------------------------------------------------------------------------------*/
/*--------------------------------------------------------------------09 Nov 2021------------------------------------------------------------------------------------*/



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


ALTER TABLE CAS.Products 
ADD isActive bit

go
update CAS.Products	
	set isActive = 1
go

-- set defualt isActive = 1 
--CAS.GetProducts --> Add isActive = 1
--CAS.GetTorchDataWeb --> Add isACtive = 1
--CAS.GetProcesses --> Add isActive = 1
--CAS.GetProcess --> Add isActive = 1
--CAS.GetCoolingBedDataWeb --> Add isActive = 1





--CAS.GetTorchDataWeb --> New Table added at the end

ALTER TABLE CAS.Products 
ADD ManuallyCreated bit

go
update CAS.Products	
	set ManuallyCreated = 0
go

-- set defualt ManuallyCreated = 0 

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



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




















/****** Object:  StoredProcedure [LAD].[ForceOpenLadleEvent]    Script Date: 22/4/2022 10:43:54 ******/
/****** Object:  StoredProcedure [LAD].[ForceOpenLadleEvent]    Script Date: 22/4/2022 10:43:54 ******/
/****** Object:  StoredProcedure [LAD].[ForceOpenLadleEvent]    Script Date: 22/4/2022 10:43:54 ******/
/****** Object:  StoredProcedure [LAD].[ForceOpenLadleEvent]    Script Date: 22/4/2022 10:43:54 ******/
/****** Object:  StoredProcedure [LAD].[ForceOpenLadleEvent]    Script Date: 22/4/2022 10:43:54 ******/
/****** Object:  StoredProcedure [LAD].[ForceOpenLadleEvent]    Script Date: 22/4/2022 10:43:54 ******/
/****** Object:  StoredProcedure [LAD].[ForceOpenLadleEvent]    Script Date: 22/4/2022 10:43:54 ******/
/****** Object:  StoredProcedure [LAD].[ForceOpenLadleEvent]    Script Date: 22/4/2022 10:43:54 ******/
/****** Object:  StoredProcedure [LAD].[ForceOpenLadleEvent]    Script Date: 22/4/2022 10:43:54 ******/
/****** Object:  StoredProcedure [LAD].[ForceOpenLadleEvent]    Script Date: 22/4/2022 10:43:54 ******/
/****** Object:  StoredProcedure [LAD].[ForceOpenLadleEvent]    Script Date: 22/4/2022 10:43:54 ******/
/****** Object:  StoredProcedure [LAD].[ForceOpenLadleEvent]    Script Date: 22/4/2022 10:43:54 ******/

/****** Object:  StoredProcedure [LAD].[ForceOpenLadleEvent]    Script Date: 22/4/2022 10:43:54 ******/
/****** Object:  StoredProcedure [LAD].[ForceOpenLadleEvent]    Script Date: 22/4/2022 10:43:54 ******/
/****** Object:  StoredProcedure [LAD].[ForceOpenLadleEvent]    Script Date: 22/4/2022 10:43:54 ******/
/****** Object:  StoredProcedure [LAD].[ForceOpenLadleEvent]    Script Date: 22/4/2022 10:43:54 ******/
/****** Object:  StoredProcedure [LAD].[ForceOpenLadleEvent]    Script Date: 22/4/2022 10:43:54 ******/
/****** Object:  StoredProcedure [LAD].[ForceOpenLadleEvent]    Script Date: 22/4/2022 10:43:54 ******/
/****** Object:  StoredProcedure [LAD].[ForceOpenLadleEvent]    Script Date: 22/4/2022 10:43:54 ******/

/****** Object:  StoredProcedure [LAD].[ForceOpenLadleEvent]    Script Date: 22/4/2022 10:43:54 ******/
/****** Object:  StoredProcedure [LAD].[ForceOpenLadleEvent]    Script Date: 22/4/2022 10:43:54 ******/














USE [MES]
GO
/****** Object:  StoredProcedure [LAD].[ForceOpenLadleEvent]    Script Date: 22/4/2022 10:43:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [LAD].[GetHeatAtCas1ArmPosition]
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.'

	
	-----------------------
	-- Query
	-----------------------
	declare @Caster1InBoardPositions table (LadlePositionID int)

	insert into @Caster1InBoardPositions
	select 
		LadlePositionID 
	from lad.catLadlePositions 
	where ([Name] like '%CAS 1 Arm%' and [Name] like '%In%')
	or ([Name] like '%CASTER 1 Arm%' and [Name] like '%Inboard%')

	select top 1 
		LadleId
		,HeatNumber
	from lad.Ladles 
	where LadlePositionID in (select LadlePositionID from @Caster1InBoardPositions)
	order by LastPositionChange desc
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END



GO

USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[AddProductManually]    Script Date: 28/4/2022 10:56:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [CAS].[SplitProduct]
	@productId INT 
	,@pieceLength FLOAT
	,@scrapLength FLOAT
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)

	PRINT @procName + ' Parameters:'
		+ ', @productId: ' + ISNULL(CAST(@productId AS VARCHAR), '')
		+ ', @pieceLength: ' + ISNULL(CAST(@pieceLength AS VARCHAR), '')
		+ ', @scrapLength: ' + ISNULL(CAST(@scrapLength AS VARCHAR), '')


	DECLARE  @logMsg varchar(max)



	-----------------------
	-- Begin Query
	-----------------------
	DECLARE @ProcessId INT
	DECLARE @HeatName varchar(50)
	DECLARE @StrandId INT
	select 
		@ProcessId = p.ProcessId
		,@HeatName = pr.HeatName
		,@StrandId = p.StrandId
	from CAS.Products p 
	left join CAS.Processes pr on pr.Id = p.ProcessId
	where p.Id = @productId


	-- INSERT NEW PIECE
	insert into cas.Products(ProcessId,HeatName,HeatId,StrandId,[sequence],[Length],ManuallyCreated,[Datetime],CutComplete,CutStart,Destination)
		values(
			@ProcessId
			,@heatName
			,(select top 1 Id from mes.heats where [Name] = @heatName)
			,@strandId
			,(select max([Sequence]) + 1 from cas.Products where ProcessId = @ProcessId and StrandId = @strandId)
			,@pieceLength
			,1
			,GETUTCDATE()
			,GETUTCDATE()
			,GETUTCDATE()
			,0
		)
	-- INSERT NEW Scrap
	insert into cas.Products(ProcessId,HeatName,HeatId,StrandId,[sequence],[Length],ManuallyCreated,[Datetime],CutComplete,CutStart,Destination,isManScrap)
		values(
			@ProcessId
			,@heatName
			,(select top 1 Id from mes.heats where [Name] = @heatName)
			,@strandId
			,99
			,@scrapLength
			,1
			,GETUTCDATE()
			,GETUTCDATE()
			,GETUTCDATE()
			,0
			,1
		)
	

	--set @logMsg = 'The product with Id: ' + isnull(cast(@newProductId as varchar(max)),'-') + ' was created by: '+ cast(isnull(@currentUser,'-') as varchar(max))

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


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

	SELECT [ProductMsg] = 'The product was splited'
END

