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

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

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


	CREATE ROLE casterproducts_readWrite AUTHORIZATION dbo 
	GO  

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

	USE [IHBoxSystem]
	GO
	EXEC sp_addrolemember 'casterproducts_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-products(/:start)(/:end)(/:casterCode)(/:strandId)(/:crewId)(/:shiftId)(/)'
			   , 'caster-products'
			   , 'custom-screens/caster-products/'
			   , 'casterproducts_read'           
			   )
			   SET @result_routeId = @@IDENTITY
	INSERT INTO [FrontEnd].[Menu]
			   ([Parent]
			   ,[Name]
			   ,[RouteID]
			   ,[SortOrder]
			   ,[Enabled]
			   ,[Visible]
			   ,[RequiredRole]
			   ,[Group]
			  )
		 VALUES
			   (
			   @parent
			   ,'Products'
			   ,@result_routeId 
			   ,1
			   ,1
			   ,1
			   ,'casterproducts_read'
			   ,NULL
			 )
	GO


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

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

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


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

	DECLARE @ModuleId INT

	INSERT INTO [SECURITY].[Modules]([Code],[Name])
	VALUES('caster_products','Caster Products')

	SET @ModuleId = @@IDENTITY

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


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



USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[GetProducts]    Script Date: 5/7/2023 23:19:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [CAS].[GetProducts]
	@start DATETIME = NULL,
	@end DATETIME = NULL,
	@casterCode INT = NULL,
	@crewId CHAR(1) = NULL,
	@shiftId INT = NULL,
	@strandId INT = NULL,
	
	@SearchText VARCHAR(50) = NULL,
	@timeZoneCode CHAR(10) = 'EDTIND',

	@fromRow INT = NULL, -- From what row to start fetching from
	@toRow INT = NULL, -- To row (@toRow row num not included in result data)
	@rowsToFetch INT = NULL, -- Amount of rows to fetch
	@sortType VARCHAR(50) = NULL -- To specify how to sort the data
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:'
		+ ', @start: ' + ISNULL(CAST(@start AS VARCHAR), '')
		+ ', @end: ' + ISNULL(CAST(@end AS VARCHAR), '')
		+ ', @casterCode: ' + ISNULL(CAST(@casterCode AS VARCHAR), '')
		+ ', @crewId: ' + ISNULL(CAST(@crewId AS VARCHAR), '')
		+ ', @shiftId: ' + ISNULL(CAST(@shiftId AS VARCHAR), '')
		+ ', @timeZoneCode: ' + ISNULL(CAST(@timeZoneCode AS VARCHAR), '')
		+ ', @fromRow: ' + ISNULL(CAST(@fromRow AS VARCHAR), '')
		+ ', @toRow: ' + ISNULL(CAST(@toRow AS VARCHAR), '')
		+ ', @rowsToFetch: ' + ISNULL(CAST(@rowsToFetch AS VARCHAR), '')
		+ ', @sortType: ' + ISNULL(CAST(@sortType AS VARCHAR), '')

	-----------------------
	-- Parameters default
	-----------------------

	IF ((@rowsToFetch IS NULL)	OR (@rowsToFetch = - 1))
		SET @rowsToFetch = 100000

	IF (@fromRow IS NULL)
		SET @fromRow = 0

		
	DECLARE @SearchTextString VARCHAR(100)
	IF @SearchText is not null
		SET @searchTextString = '%' + @SearchText + '%'
	-----------------------
	-- Begin Query
	-----------------------

	select 
		Id = pd.Id
		,[HeatName]			= case when ps.HeatId is not null then h.[Name] else ps.HeatName	end			
		,CasterCode
		,ProductNumber = case when ps.HeatId is not null then
		(
		 cast(h.[Name] as varchar)  
			+ ' ' + cast(pd.StrandId as varchar) 
			+ ' ' + case when pd.[Sequence] < 10 then '0' + cast(pd.[Sequence] as varchar) else  cast(pd.[Sequence] as varchar) end
		) else
		(
		cast(ps.HeatName as varchar)  
			+ ' ' + cast(pd.StrandId as varchar) 
			+ ' ' + case when pd.[Sequence] < 10 then '0' + cast(pd.[Sequence] as varchar) else  cast(pd.[Sequence] as varchar) end
		) end
		
		,[Datetime]			= CONVERT(VARCHAR, IH.ToLocal(pd.[Datetime], @timeZoneCode),120)
		,pd.StrandId	
		,ProductionDate     = CONVERT(VARCHAR, IH.ToLocal(ps.ProductionDate, @timeZoneCode),23)
		,[Shift]			= case when ps.ShiftId = 1 then 'D' else 'N' end
		,ps.CrewId
		,Destination		= case when pd.Destination = 0 then 'UNDEF' when pd.Destination =1 then 'FURN' when pd.Destination =2 then 'SKID' end
		,pd.[Weight]
		,[Length]			= cast(pd.[Length]			as decimal(18,2))
		,[MetLengthStart]	= cast(pd.[MetLengthStart]	as decimal(18,2))
		,[MetLengthEnd]		= cast(pd.[MetLengthEnd]	as decimal(18,2))		
		,CutStart			= CONVERT(VARCHAR, IH.ToLocal(pd.CutStart, @timeZoneCode),120)
		,CutEnd				= CONVERT(VARCHAR, IH.ToLocal(pd.CutComplete, @timeZoneCode),120)
		,ShipTime			= CONVERT(VARCHAR, IH.ToLocal(pd.[Datetime], @timeZoneCode),120)
		,[Sequence] = pd.[Sequence]
		,SentToL3 = case when pd.SentToL3 = 1 then 'Yes' else 'No' end
		,GradeName = g.[Name]
		
		-- Sulfur
		,hasSulfur = case when s.Id is not null then 'Yes' else 'No' end 
		,s.[AddDate] 
		,s.[SurfaceCracks] 
		,s.[SubsurfaceCracks] 
		,s.[Pinholes] 
		,s.[InternalCracks]
		,s.[CenterCracks] 
		,s.[CenterSegregation]
		,rb.[Description] as [RatedBy]
		,s.[H1] 
		,s.[H2] 
		,s.[H3] 
		,s.[H4] 
		,s.[H5] 
		,s.[W1] 
		,s.[W2] 
		,s.[W3] 
		,s.[W4] 
		,s.[W5] 
		,s.[D1] 
		,s.[D2] 
		,s.[RatedById]


	INTO #temp
	from cas.Products pd
	left join cas.Processes ps on ps.Id = pd.ProcessId
	left join mes.heats h on h.Id = pd.heatId
	left join CAT.Grades g on g.Id = h.GradeId
	left join SUL.SulfurPrintRatings s on s.ProductId = pd.Id
	left join SUL.RatedBy rb on s.RatedById = rb.Id
	WHERE 
		@SearchText is null AND ( 
			pd.[Datetime] between @Start and DATEADD(DAY,1,@End)
			AND (@shiftId	IS NULL OR ps.ShiftId = @shiftId)
			AND (@crewId	IS NULL OR ps.CrewId = @crewId)
			AND (@strandId IS NULL OR  pd.StrandId = @strandId)
			AND pd.isActive = 1
			AND  (@casterCode is null or ps.CasterCode = @casterCode)		
		)
		OR h.[Name] LIKE @SearchTextString	
	order by [CutEnd] desc


	SELECT *
	FROM #temp record 
	ORDER BY 
		record.CutEnd DESC
		OFFSET @fromRow ROWS
	FETCH NEXT @rowsToFetch ROWS ONLY
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0

END






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

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


























USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[GetProducts]    Script Date: 5/7/2023 23:19:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [CAS].[GetProducts]
	@start DATETIME = NULL,
	@end DATETIME = NULL,
	@casterCode INT = NULL,
	@crewId CHAR(1) = NULL,
	@shiftId INT = NULL,
	@strandId INT = NULL,
	
	@SearchText VARCHAR(50) = NULL,
	@timeZoneCode CHAR(10) = 'EDTIND',

	@fromRow INT = NULL, -- From what row to start fetching from
	@toRow INT = NULL, -- To row (@toRow row num not included in result data)
	@rowsToFetch INT = NULL, -- Amount of rows to fetch
	@sortType VARCHAR(50) = NULL -- To specify how to sort the data
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:'
		+ ', @start: ' + ISNULL(CAST(@start AS VARCHAR), '')
		+ ', @end: ' + ISNULL(CAST(@end AS VARCHAR), '')
		+ ', @casterCode: ' + ISNULL(CAST(@casterCode AS VARCHAR), '')
		+ ', @crewId: ' + ISNULL(CAST(@crewId AS VARCHAR), '')
		+ ', @shiftId: ' + ISNULL(CAST(@shiftId AS VARCHAR), '')
		+ ', @timeZoneCode: ' + ISNULL(CAST(@timeZoneCode AS VARCHAR), '')
		+ ', @fromRow: ' + ISNULL(CAST(@fromRow AS VARCHAR), '')
		+ ', @toRow: ' + ISNULL(CAST(@toRow AS VARCHAR), '')
		+ ', @rowsToFetch: ' + ISNULL(CAST(@rowsToFetch AS VARCHAR), '')
		+ ', @sortType: ' + ISNULL(CAST(@sortType AS VARCHAR), '')

	-----------------------
	-- Parameters default
	-----------------------

	IF ((@rowsToFetch IS NULL)	OR (@rowsToFetch = - 1))
		SET @rowsToFetch = 100000

	IF (@fromRow IS NULL)
		SET @fromRow = 0

		
	DECLARE @SearchTextString VARCHAR(100)
	IF @SearchText is not null
		SET @searchTextString = '%' + @SearchText + '%'
	-----------------------
	-- Begin Query
	-----------------------

	select 
		Id = pd.Id
		,[HeatName]			= case when ps.HeatId is not null then h.[Name] else ps.HeatName	end			
		,CasterCode
		,ProductNumber = case when ps.HeatId is not null then
		(
		 cast(h.[Name] as varchar)  
			+ ' ' + cast(pd.StrandId as varchar) 
			+ ' ' + case when pd.[Sequence] < 10 then '0' + cast(pd.[Sequence] as varchar) else  cast(pd.[Sequence] as varchar) end
		) else
		(
		cast(ps.HeatName as varchar)  
			+ ' ' + cast(pd.StrandId as varchar) 
			+ ' ' + case when pd.[Sequence] < 10 then '0' + cast(pd.[Sequence] as varchar) else  cast(pd.[Sequence] as varchar) end
		) end
		
		,[Datetime]			= CONVERT(VARCHAR, IH.ToLocal(pd.[Datetime], @timeZoneCode),120)
		,pd.StrandId	
		,ProductionDate     = CONVERT(VARCHAR, IH.ToLocal(ps.ProductionDate, @timeZoneCode),23)
		,[Shift]			= case when ps.ShiftId = 1 then 'D' else 'N' end
		,ps.CrewId
		,Destination		= case when pd.Destination = 0 then 'UNDEF' when pd.Destination =1 then 'FURN' when pd.Destination =2 then 'SKID' end
		,pd.[Weight]
		,[Length]			= cast(pd.[Length]			as decimal(18,2))
		,[MetLengthStart]	= cast(pd.[MetLengthStart]	as decimal(18,2))
		,[MetLengthEnd]		= cast(pd.[MetLengthEnd]	as decimal(18,2))		
		,CutStart			= CONVERT(VARCHAR, IH.ToLocal(pd.CutStart, @timeZoneCode),120)
		,CutEnd				= CONVERT(VARCHAR, IH.ToLocal(pd.CutComplete, @timeZoneCode),120)
		,ShipTime			= CONVERT(VARCHAR, IH.ToLocal(pd.[Datetime], @timeZoneCode),120)
		,[Sequence] = pd.[Sequence]
		,SentToL3 = case when pd.SentToL3 = 1 then 'Yes' else 'No' end
		,GradeName = g.[Name]
		
		-- Sulfur
		,hasSulfur = case when s.Id is not null then 'Yes' else 'No' end 
		,s.[AddDate] 
		,s.[SurfaceCracks] 
		,s.[SubsurfaceCracks] 
		,s.[Pinholes] 
		,s.[InternalCracks]
		,s.[CenterCracks] 
		,s.[CenterSegregation]
		,s.[RatedBy] 
		,s.[H1] 
		,s.[H2] 
		,s.[H3] 
		,s.[H4] 
		,s.[H5] 
		,s.[W1] 
		,s.[W2] 
		,s.[W3] 
		,s.[W4] 
		,s.[W5] 
		,s.[D1] 
		,s.[D2] 
		,s.[RatedById]


	INTO #temp
	from cas.Products pd
	left join cas.Processes ps on ps.Id = pd.ProcessId
	left join mes.heats h on h.Id = pd.heatId
	left join CAT.Grades g on g.Id = h.GradeId
	left join SUL.SulfurPrintRatings s on s.ProductId = pd.Id
	WHERE 
		@SearchText is null AND ( 
			pd.[Datetime] between @Start and DATEADD(DAY,1,@End)
			AND (@shiftId	IS NULL OR ps.ShiftId = @shiftId)
			AND (@crewId	IS NULL OR ps.CrewId = @crewId)
			AND (@strandId IS NULL OR  pd.StrandId = @strandId)
			AND pd.isActive = 1
			AND  (@casterCode is null or ps.CasterCode = @casterCode)		
		)
		OR h.[Name] LIKE @SearchTextString	
	order by [CutEnd] desc


	SELECT *
	FROM #temp record 
	ORDER BY 
		record.CutEnd DESC
		OFFSET @fromRow ROWS
	FETCH NEXT @rowsToFetch ROWS ONLY
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0

END
