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

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

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

	CREATE ROLE cooling_bed_readWrite AUTHORIZATION dbo 
	GO  

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

	USE [IHBoxSystem]
	GO
	EXEC sp_addrolemember 'cooling_bed_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 
			   (
			   '!/cooling-bed(/)'
			   , 'cooling-bed'
			   , 'custom-screens/cooling-bed/'
			   , 'cooling_bed_read'           
			   )
			   SET @result_routeId = @@IDENTITY
	INSERT INTO [FrontEnd].[Menu]
			   ([Parent]
			   ,[Name]
			   ,[RouteID]
			   ,[SortOrder]
			   ,[Enabled]
			   ,[Visible]
			   ,[RequiredRole]
			   ,[Group]
			  )
		 VALUES
			   (
			   @parent
			   ,'Cooling Bed'
			   ,@result_routeId 
			   ,1
			   ,1
			   ,1
			   ,'cooling_bed_read'
			   ,NULL
			 )
	GO


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

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

-------------------
--	Profiles
-------------------
	USE [IHBoxSystem]
	GO

	DECLARE @ModuleId INT

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

	SET @ModuleId = @@IDENTITY

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


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

GO




USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[GetCoolingBedDataWeb]    Script Date: 15 ago. 2021 18:40:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [CAS].[GetCoolingBedDataWeb]
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

	select
	 [S1_P1] = 0
	,[S1_P2] = 1
	,[S1_P3] = 0
	,[S1_P4] = 0
	,[S1_P5] = 0
	,[S1_P6] = 0

	
	,[S2_P1] = 1
	,[S2_P2] = 0
	,[S2_P3] = 0
	,[S2_P4] = 0
	,[S2_P5] = 0
	,[S2_P6] = 0
	
	
	,[S3_P1] = 0
	,[S3_P2] = 0
	,[S3_P3] = 0
	,[S3_P4] = 1
	,[S3_P5] = 0
	,[S3_P6] = 0
	
	,[S4_P1] = 0
	,[S4_P2] = 0
	,[S4_P3] = 0
	,[S4_P4] = 0
	,[S4_P5] = 0
	,[S4_P6] = 1

	,[S1_DD0] = 0
	,[S1_DD1] = 0
	,[S1_DD2] = 0
	,[S1_DD3] = 1

	
	,[S2_DD0] = 0
	,[S2_DD1] = 1
	,[S2_DD2] = 0
	,[S2_DD3] = 0

	
	,[S3_DD0] = 0
	,[S3_DD1] = 0
	,[S3_DD2] = 1
	,[S3_DD3] = 0

	
	,[S4_DD0] = 1
	,[S4_DD1] = 0
	,[S4_DD2] = 0
	,[S4_DD3] = 0

	,[S1_ProductId] = (select top 1 Id from cas.Products where StrandId = 1 and DeliveredDestination is null  order by [Datetime] desc)
	,[S2_ProductId] = (select top 1 Id from cas.Products where StrandId = 2 and DeliveredDestination is null  order by [Datetime] desc)
	,[S3_ProductId] = (select top 1 Id from cas.Products where StrandId = 3 and DeliveredDestination is null  order by [Datetime] desc)
	,[S4_ProductId] = (select top 1 Id from cas.Products where StrandId = 4 and DeliveredDestination is null  order by [Datetime] desc)
	
	into #temp


	select 
	t.*

	,S1_HeatData = cast(p1.HeatName as varchar(10)) + ' ' + cast(p1.[Sequence] as varchar(10)) + ' ' + cast(p1.StrandId as varchar(10))
	,S1_Weight = 'Weight : ' + cast(p1.[Weight] as varchar(10))
	,S1_Length = p1.[Length]

	,S2_HeatData = cast(p2.HeatName as varchar(10)) + ' ' + cast(p2.[Sequence] as varchar(10)) + ' ' + cast(p2.StrandId as varchar(10))
	,S2_Weight = 'Weight : ' + cast(p2.[Weight] as varchar(10))
	,S2_Length = p2.[Length]

	,S3_HeatData = cast(p3.HeatName as varchar(10)) + ' ' + cast(p3.[Sequence] as varchar(10)) + ' ' + cast(p3.StrandId as varchar(10))
	,S3_Weight = 'Weight : ' + cast(p3.[Weight] as varchar(10))
	,S3_Length = p3.[Length]

	,S4_HeatData = cast(p4.HeatName as varchar(10)) + ' ' + cast(p4.[Sequence] as varchar(10)) + ' ' + cast(p4.StrandId as varchar(10))
	,S4_Weight = 'Weight : ' + cast(p4.[Weight] as varchar(10))
	,S4_Length = p4.[Length]
	from #temp t
	left join cas.Products p1 on p1.Id = t.S1_ProductId
	left join cas.Products p2 on p2.Id = t.S2_ProductId
	left join cas.Products p3 on p3.Id = t.S3_ProductId
	left join cas.Products p4 on p4.Id = t.S4_ProductId
	--,[S1_HeatData] = 'B193483 3 04'
	--,[S1_HeatData] = 'B193483 3 04'
	--,[S1_HeatData] = 'B193483 3 04'
	--,[S1_HeatData] = 'B193483 3 04'
	--,[S1_HeatData] = 'B193483 3 04'
	--,[S1_HeatData] = 'B193483 3 04'
	
	--,[S1_WeightData] = 'Weight : 185.5 lbs'
	--,[S1_WeightData] = 'Weight : 185.5 lbs'
	--,[S1_WeightData] = 'Weight : 185.5 lbs'
	--,[S1_WeightData] = 'Weight : 185.5 lbs'
	--,[S1_WeightData] = 'Weight : 185.5 lbs'
	--,[S1_WeightData] = 'Weight : 185.5 lbs'


	declare @products table (HeatName varchar(10),strandId int, [Sequence] int, [Datetime] datetime, [Weight] float, [Length] float, [DeliveredDestination] char(2),[DeliveredDatetime] datetime)
	
	insert into @products
	select top 6 
	HeatName
	,StrandId
	,[Sequence]
	,[Datetime]
	,[Weight]
	,[Length]
	,[DeliveredDestination]
	,[DeliveredDatetime]
	from cas.Products
	where DeliveredDestination is not null and StrandId = 1
	order by [Datetime] desc

	
	insert into @products
	select top 6 
		HeatName
		,StrandId
		,[Sequence]
		,[Datetime]
		,[Weight]
		,[Length]
		,[DeliveredDestination]
		,[DeliveredDatetime]
	from cas.Products
	where DeliveredDestination is not null and StrandId = 2
	order by [Datetime] desc
	
	insert into @products
	select top 6 
		HeatName
		,StrandId
		,[Sequence]
		,[Datetime]
		,[Weight]
		,[Length]
		,[DeliveredDestination]
		,[DeliveredDatetime]
	from cas.Products
	where DeliveredDestination is not null and StrandId = 3
	order by [Datetime] desc


	insert into @products
	select top 6 
		HeatName
		,StrandId
		,[Sequence]
		,[Datetime]
		,[Weight]
		,[Length]
		,[DeliveredDestination]
		,[DeliveredDatetime]
	from cas.Products
	where DeliveredDestination is not null and StrandId = 4
	order by [Datetime] desc

	select 
	ROW_NUMBER() OVER(Partition BY DeliveredDestination ORDER BY [Datetime] ASC) AS [Order]
	
	,HeatData = cast(HeatName as varchar(10)) + ' ' + cast([Sequence] as varchar(10)) + ' ' + cast(StrandId as varchar(10))
	,[WeightData] = 'Weight : ' + cast([Weight] as varchar(10))
	,[Length] = [Length]
	
	,* 
	from @products

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

GO
EXEC [CAS].[GetCoolingBedDataWeb]
