USE [IHBoxSystem]
GO
CREATE ROLE cdm_r AUTHORIZATION dbo 
GO  

USE [IHBoxSystem]
GO
CREATE ROLE cdm_w AUTHORIZATION dbo 
GO  

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

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

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

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



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-delays-manager'
		   , 'caster-delays-manager'
		   , 'custom-screens/caster-delays-manager/'
		   , 'cdm_r'           
		   )
		   SET @result_routeId = @@IDENTITY
INSERT INTO [FrontEnd].[Menu]
		   ([Parent]
		   ,[Name]
		   ,[RouteID]
		   ,[SortOrder]
		   ,[Enabled]
		   ,[Visible]
		   ,[RequiredRole]
		   ,[Group]
		  )
	 VALUES
		   (
		   @parent
		   ,'Caster Delays Manager'
		   ,@result_routeId 
		   ,1
		   ,1
		   ,1
		   ,'cdm_r'
		   ,NULL
		 )
GO

/*------------------------------------------------------Profiles------------------------------------------------------*/

USE [IHBoxSystem]
GO

DECLARE @ModuleId INT

INSERT INTO [SECURITY].[Modules]([Code],[Name])
VALUES('caster_delays_manager','Caster Delays Manager')

SET @ModuleId = @@IDENTITY

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


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

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

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

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


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

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

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


---------------------------
-- PROCEDURES----
---------------------------
USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[GetDelaysCodeGroup]    Script Date: 12/10/2023 10:32:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   PROCEDURE [CAS].[GetDelaysCodeGroup]
	@From DATETIME = NULL,
	@To DATETIME = NULL,
	@Timezone CHAR(10) = NULL,
	@ShiftId INT = NULL,
	@CrewId CHAR(1) = NULL,
	@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
	@OperatingUnitId INT = NULL, -- Level 1
	@CauseId INT = NULL, -- Level 2
	@AreaId INT = NULL -- Level 3
AS
BEGIN

	IF (@fromRow IS NULL)
	SET @fromRow = ISNULL(@fromRow, 1)

	IF (@rowsToFetch IS NULL)
	SET @rowsToFetch = ISNULL(@rowsToFetch, 20)

	IF (@toRow IS NULL)
	SET @toRow = ISNULL(@toRow, @fromRow + @rowsToFetch)

	SET @From = dateadd(HOUR, 9, @From)
	SET @To = dateadd(HOUR, 9, @To)

	DECLARE @now DATETIME = GETUTCDATE()
	DECLARE @fromUTC DATETIME, @toUTC DATETIME

	SET @fromUTC = ih.ToUTC(@From, @Timezone)
	SET @toUTC = IH.ToUTC(@To, @Timezone)

	declare @CasterCode int = @OperatingUnitId


	SELECT
		[Id] = d.[DelayId],
		[DelayCodeId] = d.[DelayCodeId],
		[ParentId] = d.[DelayCodeId], -- ?
		[Start] = ih.toLocal(d.[Start],null),
		[End] = ih.tolocal(d.[End],null),
		[AreaName] = ISNULL(c.DelayName, 'Not Declared'),
		[Duration] = IHDelays2.dbo.GetHHMMSSFromSeconds(DATEDIFF(SECOND, d.Start, d.[End])),
		[Seconds] = DATEDIFF(SECOND, d.Start, d.[End]),
		d.[Comments],
		[CauseName] = ISNULL((select DelayName from CAS.DelayCodes dc where dc.DelayCodeId = c.ParentDelayCodeId ), 'Not Declared'),
		[Shift] = case when SD.Id = 1 then 'D' else 'N' end,
		[Crew] = SD.CrewId,
		[CasterName] = case when d.CasterCode = 1 then 'Caster 1' else 'Caster 2' END
		FROM CAS.Delays d WITH(NOLOCK)
		LEFT JOIN CAS.DelayCodes c on d.DelayCodeId = c.DelayCodeId
		CROSS APPLY SYSTEM.GetShiftDataForDatetime(d.Start) SD
		WHERE
		d.[Start] < @toUTC and (d.[End] is null or d.[End] > @fromUTC)
		AND (@ShiftId IS NULL OR SD.Id = @ShiftId)
		AND (@CrewId IS NULL OR SD.CrewId = @CrewId)
		AND (
            (@OperatingUnitId is not null and @CauseId is null and @AreaId is null and d.CasterCode = @CasterCode)
            or (@OperatingUnitId is null and  @CauseId is null and @AreaId is null) 
            or (d.DelayCodeId in (select DelayCodeId from [CAS].GetDelayCodesByCasterAndCause(@OperatingUnitId, @CauseId, @AreaId)))
            )
END

GO


/****** Object:  UserDefinedFunction [CAS].[GetDelayCodesByCasterAndCause]    Script Date: 9/10/2023 15:42:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [CAS].[GetDelayCodesByCasterAndCause]
(
    @CasterCodeId int = null,
    @CauseId int = null,
@AreaId int = null
)
RETURNS @dataT TABLE
(
    DelayCodeId INT
)
AS
BEGIN

    declare @causeName varchar(50) = (select DelayName from CAS.DelayCodes where DelayCodeId = @CauseId)

    declare @areaName varchar(50) = (select DelayName from CAS.DelayCodes where DelayCodeId = @AreaId)

if (@AreaId is null)
begin

if(@CasterCodeId is null and @CauseId is null)
BEGIN
INSERT INTO @dataT    
select
DelayCodeId from CAS.DelayCodes

return
END
   
if(@CasterCodeId is null and @CauseId is not null)
begin
INSERT INTO @dataT    
select
z.DelayCodeId
from CAS.DelayCodes z
where z.ParentDelayCodeId in (
select x.DelayCodeId from cas.DelayCodes x where x.DelayName = @causeName
)
return
end

if(@CasterCodeId is not null and @CauseId is null)
begin
INSERT INTO @dataT    
select
z.DelayCodeId
from CAS.DelayCodes z
where z.ParentDelayCodeId in (
select x.DelayCodeId from cas.DelayCodes x where x.ParentDelayCodeId = @CasterCodeId
)
return
end

if(@CasterCodeId is not null and @CauseId is not null)
begin
INSERT INTO @dataT    
select
DelayCodeId from CAS.DelayCodes where ParentDelayCodeId = @CauseId
return
end
end




if (@AreaId is not null)
begin

if(@CasterCodeId is null and @CauseId is null)
BEGIN
INSERT INTO @dataT    
select
DelayCodeId from CAS.DelayCodes where DelayName = @areaName

return
END
   
if(@CasterCodeId is null and @CauseId is not null)
begin
INSERT INTO @dataT    
select
z.DelayCodeId
from CAS.DelayCodes z
where z.ParentDelayCodeId in (
select x.DelayCodeId from cas.DelayCodes x where x.DelayName = @causeName
)
and z.DelayCodeId in (
select y.DelayCodeId from cas.DelayCodes y where y.DelayName  = @areaName
)
return
end

if(@CasterCodeId is not null and @CauseId is null)
begin
INSERT INTO @dataT    
select
z.DelayCodeId
from CAS.DelayCodes z
where z.ParentDelayCodeId in (
select x.DelayCodeId from cas.DelayCodes x where x.ParentDelayCodeId = @CasterCodeId
)
and z.DelayCodeId in (
select y.DelayCodeId from cas.DelayCodes y where y.DelayName  = @areaName
)
return
end

if(@CasterCodeId is not null and @CauseId is not null)
begin
INSERT INTO @dataT    
select
DelayCodeId from CAS.DelayCodes where DelayCodeId = @AreaId
return
end
end

    return
END


USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[GetDelayCodes]    Script Date: 11/10/2023 15:06:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [CAS].[GetDelayCodes]

AS
BEGIN

select 
	Id = DelayCodeId, -- or DelayCodeId
	ParentId = case when  (ParentDelayCodeId = 1) or( ParentDelayCodeId = 2) then null else ParentDelayCodeId end,
	[Name] = [DelayName],
	[Description] = DelayName,
	[Color] = '#eee',
	[RootParentId] = (select CAS.GetRootParentByDelayCodeId(DelayCodeId))
from CAS.DelayCodes where ParentDelayCodeId is not NULL

	-----------------------
	-- Operating Unit
	-----------------------
	 SELECT 
		Id = DelayCodeId,
		[Name] = [DelayName],
		[Description] = [DelayName]
	 FROM CAS.DelayCodes where Hierarchy = 1

	-----------------------
	-- Cause
	-----------------------
	 SELECT
		Id = MAX(DelayCodeId),
		[Name] = MAX([DelayName]),
		[Description] = MAX([DelayName])
	 FROM CAS.DelayCodes where Hierarchy = 2
	 Group by [DelayName]

	-----------------------
	-- Area
	-----------------------
	 SELECT
		Id = MAX(DelayCodeId),
		[Name] = MAX([DelayName]),
		[Description] = MAX([DelayName])
	 FROM CAS.DelayCodes where Hierarchy = 3
	 Group by [DelayName]
END


USE [MES]
GO
/****** Object:  UserDefinedFunction [CAS].[GetRootParentByDelayCodeId]    Script Date: 11/10/2023 15:56:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [CAS].[GetRootParentByDelayCodeId]
(
    @DelayCodeId INT
)
RETURNS  INT
AS
BEGIN

    declare @return int

  ; with name_tree as (
   select DelayCodeId, ParentDelayCodeId,[DelayName]
   from CAS.DelayCodes
   where DelayCodeId = @DelayCodeId  -- this is the starting point you want in your recursion
   union all
   select c.DelayCodeId, c.ParentDelayCodeId, c.[DelayName]
   from CAS.DelayCodes   c
     join name_tree p on p.ParentDelayCodeId = c.DelayCodeId  -- this is the recursion
    ) 
    select top 1  @return = DelayCodeId  from name_tree where ParentDelayCodeId is null
 

return @return
END


GO

USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[SplitDelayAt]    Script Date: 12/10/2023 15:27:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [CAS].[SplitDelayAt]

@Id INT, 
@Seconds INT,
@CurrentUser VARCHAR(MAX), 
@Automatic BIT = 0

AS
BEGIN

BEGIN TRY

DECLARE @DStart DATETIME, @DEnd DATETIME,
@DProductionItemId INT, @DDelayCodeId INT, @DComments VARCHAR(MAX), 
@DBatchId INT, @DShiftId INT,@CasterCode int

SELECT 
	@DStart = d.Start, 
	@DEnd = d.[End], 
	@DDelayCodeId = d.DelayCodeId, 
	@DComments = d.Comments,
	@CasterCode = d.CasterCode
FROM CAS.Delays d
WHERE d.DelayId = @Id

DECLARE @SplitTime DATETIME
SET @SplitTime = DATEADD(SECOND, @Seconds, @DStart)

DECLARE @d1 INT, @d2 INT

--EXEC @d1 = CAS.CreateDelay @CasterCode=@CasterCode,@ParentId = @Id, @DelayCodeId = @DDelayCodeId, @Start = @DStart, @End = @SplitTime, @Comments = @DComments, @Automatic = @Automatic


update cas.Delays
	set [End] = @SplitTime
	where delayId = @Id

EXEC @d2 = CAS.CreateDelay @CasterCode=@CasterCode,@ParentId = @Id, @DelayCodeId = NULL, @Start =@SplitTime, @End = @DEnd, @Comments = NULL, @Automatic = @Automatic
--INSERT INTO [AUDIT].Delays (DelayId, [Action], [Description], [User])
--VALUES (@Id, 'SPLIT', 'The delay was split at [' + CONVERT(VARCHAR, @SplitTime, 121) + '], new delays were created with ids [' + CONVERT(VARCHAR, @d1) + ', ' + CONVERT(VARCHAR, @d2) + '] .', @CurrentUser)

	SELECT
	[d1] = @Id
	,[d2] = @d2



END TRY
BEGIN CATCH
	THROW; 
END CATCH

END

go

USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[CreateDelay]    Script Date: 12/10/2023 12:04:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [CAS].[CreateDelay]

@ParentId INT = NULL, 
@DelayCodeId INT = NULL, 
@Start DATETIME, 
@End DATETIME = NULL, 
@BatchId INT = NULL, 
@ShiftId INT = NULL, 
@Comments VARCHAR(MAX) = NULL, 
@Automatic BIT = 0,  
@Deleted BIT = 0,
@CasterCode INT = NULL

AS
BEGIN
	BEGIN TRY
	
	IF (NOT (@End > @Start OR @End IS NULL))
	BEGIN
		RAISERROR (N'Error: The @End parameters must be after @Start or NULL.', 16, 1)
	END

	INSERT INTO CAS.Delays (ParentDelayId,DelayCodeId, [Start], [End], Comments, [Automatic], Deleted,CasterCode)
	VALUES (@ParentId,@DelayCodeId, @Start, @End, @Comments, @Automatic, @Deleted,@CasterCode)

	RETURN SCOPE_IDENTITY()

	END TRY
	BEGIN CATCH
		THROW;
	END CATCH
END
go

USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[GetDelayData]    Script Date: 12/10/2023 12:20:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [CAS].[GetDelayData]

@DelayId INT, 
@Timezone VARCHAR(10) = NULL

AS
BEGIN

    declare @CasterCode Int  = (select CasterCode from cas.Delays where DelayId = @DelayId)

    SELECT 
    [DelayId] = d.DelayId
    , [Line] = d.CasterCode
    , [ProductionItem] = 1
    , [ParentId] = null
    , [Start] = CONVERT(VARCHAR,ih.ToLocal(d.Start, @Timezone),120)
    , [End] = CONVERT(VARCHAR,ih.ToLocal(d.[End], @Timezone),120)
    , [Duration] = DATEDIFF(ss, d.Start, isnull(d.[End],GETUTCDATE()))
    , [DelayCodeID] = d.DelayCodeID
    , [BatchId] = 1
    , [BatchNumber] = 1
    , [Comments] = d.Comments
    FROM CAS.Delays d
    WHERE d.DelayId = @DelayId

  
	

END





update cas.DelayCodes set DelayName = 'Caster 1' where DelayCodeId = 1
update cas.DelayCodes set DelayName = 'Caster 2' where DelayCodeId = 2