USE [IHBoxSystem]
GO

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

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')


------------------ Permissions --------------------

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


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


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








-------------------- STORED PROCEDURES --------------------


USE [MES]
GO
/****** Object:  StoredProcedure [WebApp].[GetDelayCategoriesStacked]    Script Date: 19/10/2023 19:41:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [CAS].[GetDelayCategoriesStacked]
	@From DATETIME = NULL, 
	@To DATETIME = NULL,
	@Timezone CHAR(10) = NULL, 
	@Divisions INT = 12, 
	@ShiftId INT = NULL, 
	@delayCategoryId INT = NULL

AS
BEGIN

	------------------------------------------
	---- Variables and Table Definition ------
	------------------------------------------

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

	DECLARE @fromUTC DATETIME = IH.ToUTC(@From, @Timezone)
	DECLARE @toUTC DATETIME = IH.ToUTC(@To, @Timezone)
	DECLARE @totalSeconds INT = DATEDIFF(SECOND, @fromUTC, @toUTC)

	DECLARE @groupBase int
	DECLARE @start datetime2(2) = @fromUTC,
		@end datetime2(2) = @toUTC

	declare @startMs bigint = IH.Date2Ms(@start),
			@endMs bigint = IH.Date2Ms(@end)
	DECLARE @points real = @divisions

	set @groupBase =round((@endMs - @startMs) / @points, 0);
	
	declare @base table (row int, ti datetime2(2), te datetime2(2), tiOffset bigint, teOffset bigint);
	
	
	-----------------------
	---- Begin Query ------
	-----------------------

	--CREATE TIME BASE TABLE FOR CURVE AND CLOUD MODES
	WITH baseTime AS (
	 select ti, DATEADD (ms, @groupBase-1, ti) te, cnt from (SELECT convert(datetime2(3),  @start) ti, 1 cnt ) as  base
	 union all
	 select DATEADD (ms, @groupBase, baseTime.ti) ti, DATEADD (ms, @groupBase-1, DATEADD (ms, @groupBase, baseTime.ti)) te, cnt + 1 from (SELECT convert(datetime2(3), @start) ti ) as  base2
	 CROSS JOIN baseTime 
	 where cnt < @points --DATEADD (ms, @groupBase, baseTime.ti) <  '2010-02-20 11:02:00'
	)
	insert @base 
	SELECT ROW_NUMBER() OVER(ORDER BY ti) row, convert(datetime2(2), ti), convert(datetime2(2), te), IH.Date2Ms(ti), IH.Date2Ms(te) from  baseTime
	OPTION (MAXRECURSION 32767);


	-----------------------------------------
	---- Delay Categories Stacked Info ------
	-----------------------------------------

	SELECT
	[Timestamp] = b.ti, 
	[TimestampEnd] = b.te, 
	[Duration] = SUM(
					ISNULL(
						DATEDIFF(SECOND, 
							CASE WHEN d.Start < b.ti THEN b.ti ELSE d.Start END, 
							CASE WHEN d.[End] > b.te THEN b.te ELSE d.[End] END
						), 0
					)
					),
	[CategoryId] = max(dcat2.SRDCategoryId), 
	[Category] =	max(dcat2.[Description]),
	[Color] = max(dcat2.[Color])
	INTO #secondsPerDivision
	FROM @base b
	LEFT JOIN (
		SELECT d.*
		FROM CAS.Delays d WITH(NOLOCK)
		LEFT JOIN CAS.Delays children WITH(NOLOCK) ON children.ParentDelayId = d.DelayId
		LEFT JOIN CAS.DelayCodes dc ON dc.DelayCodeId = d.DelayCodeId
		LEFT JOIN CAS.catDelayCategories dcat ON dcat.DelayCodeId = IHDelays2.[dbo].[GetRootParentByDelayCodeId](dc.DelayCodeId) --------------------------------> new
		WHERE children.DelayId IS NULL
		AND d.Deleted = 0

		AND ((d.Start >= @fromUTC and d.[Start] <= @toUTC)
		OR (d.[End] >= @fromUTC and d.[End] <= @toUTC)
		OR (d.[Start] <= @fromUTC and d.[End] >= @toUTC))
		AND (dcat.srdcategoryId = @delayCategoryId OR @delayCategoryId IS NULL)
	) d ON
	(
		(d.Start >= b.ti AND d.Start <= b.te)
		OR (d.[End] >= b.ti AND d.[End] <= b.te)
		OR (d.[Start] <= b.ti AND d.[End] >= b.te)
	)
	LEFT JOIN CAS.catDelayCategories dcat2 ON dcat2.DelayCodeId = IHDelays2.[dbo].[GetRootParentByDelayCodeId](d.DelayCodeId) --------------------------------> new
	
	GROUP BY b.ti, b.te
	ORDER BY b.ti DESC

END
GO




USE [MES]
GO
/****** Object:  StoredProcedure [WebApp].[GetDelaysTimeSummary]    Script Date: 19/10/2023 20:09:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[GetDelaysTimeSummary]    Script Date: 20/10/2023 08:48:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [CAS].[GetDelaysTimeSummary]
	@ShiftId INT = NULL, 
	@CrewId CHAR(1) = NULL, 
	@ProductId INT = NULL, 
	@From DATETIME,
	@To DATETIME, 
	@Timezone VARCHAR(10)= NULL,

	@delayCategoryId INT = NULL
AS
BEGIN
	-------------------------------
	---- Varibles and Tables ------
	-------------------------------

	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 @delayCodesDuration table (Id int, Code varchar(100),Category varchar(100),[Description] varchar(100)
	,[Color] varchar(10),[Duration] INT,[ProductionItem] INT,[Count] INT)

	declare @delays table (DelayId int, DelayCodeId int, DelayCodeParentId int,[Start] datetime, [End] datetime, Comments varchar(200),ProductionItem int)
	-------------------------------
	---- Begin Query ------
	-------------------------------
		insert into @delays
		SELECT
			d.[DelayId], 
			d.[DelayCodeId], 
			[DelayCodeParentId] = dc.ParentDelayCodeId, 
			[Start] = CASE WHEN d.[Start] < @fromUTC THEN @fromUTC ELSE d.[Start] END, 
			[End] = ISNULL(CASE WHEN d.[End] > @toUTC THEN @toUTC ELSE d.[End] END, CASE WHEN @now > @toUTC THEN @toUTC ELSE @now END), 
			d.[Comments], 
			[ProductionItem]= d.casterCode
		FROM CAS.Delays d WITH(NOLOCK)
		LEFT JOIN CAS.Delays children WITH(NOLOCK) ON children.ParentDelayId = d.DelayCodeId
		LEFT JOIN CAS.DelayCodes dc ON dc.DelayCodeId = d.DelayCodeId
		CROSS APPLY MES.[system].GetShiftDataForDatetime(d.Start) SD
		WHERE
		d.[Start]  < @toUTC and d.[End] > @fromUTC
		AND (@ShiftId IS NULL OR SD.Id = @ShiftId)
		AND (@CrewId IS NULL OR  sd.CrewId = @CrewId)
		AND d.Deleted <> 1


		insert into @delayCodesDuration
		SELECT
			[Id] = dc.DelayCodeId, 
			[Code] = MAX(dc.DelayName),
			[Category] = MAX(c.[Description]),

			[Description] = MAX(c.[Description]), 
			[Color] = MAX(c.Color), 
			[Duration] = ISNULL(SUM(ISNULL(DATEDIFF(SECOND, d.[Start], d.[End]), 0)), 0),
			[ProductionItem] = MAX(d.productionItem),
			[Count] = count(*)
		--INTO #delayCodesDuration
		FROM CAS.DelayCodes dc
		LEFT JOIN CAS.DelayCodes children ON children.ParentDelayCodeId = dc.DelayCodeId
		LEFT JOIN @delays d ON d.[DelayCodeId] = dc.DelayCodeId
		LEFT JOIN CAS.catDelayCategories c on c.DelayCodeId = [CAS].[GetRootParentByDelayCodeIdAndHierarchy](dc.DelayCodeId,2)   
		WHERE dc.isActive = 1
		AND children.DelayCodeId IS NULL
		AND (@delayCategoryId IS NULL OR c.SRDCategoryId = @delayCategoryId)  
		AND d.DelayCodeId is not null
		GROUP BY dc.DelayCodeId

		
		insert into @delayCodesDuration
		values(
		-1,
		'NOTDECLARED',
		'NOTDECLARED',
		'Not Declared',
		'#f84e4e',
		0,
		1,  -- Not Declared just for CAS 1
		1
		)

		

		SELECT
		[Id] = dcd.Id, 
		[Code] = dcd.Code, 
		[Category] = dcd.Category, 
		[Description] = dcd.[Description],
		[Color] = dcd.Color, 
		[Duration] = dcd.Duration
		,[DurationDaysAndTime] = CONVERT(varchar, dcd.Duration / 86400 ) + ' ' + -- Days
			CONVERT(varchar, DATEADD(ms, ( dcd.Duration % 86400 ) * 1000, 0), 108)	
		,[Count] = dcd.[Count]
		,[dcd].[ProductionItem]
		FROM @delayCodesDuration dcd
		ORDER BY dcd.Duration DESC


END


go

exec  [CAS].[GetDelaysTimeSummary] @From = '2023-09-01',@To='2023-10-20', @ShiftId = 2,@CrewId= 'B'

GO

/*---------------------*/
/*--- Function to generate random values of delayCodeIds*/








-- Calculate total numbers in range (300 in this case)
DECLARE @Total INT = 314 - 15 + 1;

-- Create a temporary table
declare @RandomNumbers TABLE (rn int identity(1,1),RandomDelayCodeId INT);

-- Insert random numbers
DECLARE @Counter INT = 0;
WHILE @Counter < @Total
BEGIN
    INSERT INTO @RandomNumbers
    SELECT CONVERT(INT, (RAND(CHECKSUM(NEWID())) * @Total) + 15);
    SET @Counter = @Counter + 1;
END


; with cte as(
	select 
	*
	,rn = ROW_NUMBER() OVER(ORDER BY DelayId asc)
	from CAS.Delays

)
update cd 
	set DelayCodeId = (
select 
	RandomDelayCodeId
from cte c
left join @RandomNumbers r on r.rn = c.rn
where c.DelayId = cd.DelayId
)
from cas.delays cd
