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



CREATE TABLE [CAS].[catDelayCategories](
    [SRDCategoryId] [int] IDENTITY(1,1) NOT NULL,
    [DelayCodeId] [int] NOT NULL,
	[Name] [varchar] (50) NOT NULL,
    [Description] [varchar](50) NOT NULL,
    [Color] [varchar](50) NOT NULL
    
)

insert into [CAS].[catDelayCategories]
values
(3    ,'C1OperatingPlanned','Operating Planned','#669999'),
(4    ,'C1OperatingUnplanned','Operating Unplanned','#ffb3ff'),
(5    ,'C1Electrical','Electrical','#80ffA0'),
(6    ,'C1Mechanical','Mechanical','#00c749'),
(7    ,'C1ScheduledDown','Scheduled Down','#8280fc'),
(8    ,'C1Other','Other','#ff8080'),
(9    ,'C2OperatingPlanned','Operating Planned','#e6f28c'),
(10    ,'C2OperatingUnplanned','Operating Unplanned','#dced5a'),
(11    ,'C2Electrical','Electrical','#ffa366'),
(12    ,'C2Mechanical','Mechanical','#718fc6'),
(13    ,'C2ScheduledDown','ScheduledDown','#ff5252'),
(14    ,'C2Other','Other','#ff3020')
(14    ,'CAS 2 - Other                    ','#ff3020')

USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[GetDelayCategories]    Script Date: 12/10/2023 17:18:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [CAS].[GetDelayCategories]

AS
BEGIN
select 
	Id = SRDCategoryId, -- or DelayCodeId
	[Name],
	[Description],
	[Color]
from [CAS].catDelayCategories

END

--------------------------------------------------------------
USE [MES]
GO
/****** Object:  StoredProcedure [CAS].[GetDelaysTimeSummaryByDateAndCodeGroup]    Script Date: 13/10/2023 10:58:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   PROCEDURE [CAS].[GetDelaysTimeSummaryByDateAndCodeGroup]

@ShiftId INT = NULL, 
@From DATETIME,
@To DATETIME, 
@Timezone VARCHAR(10),

@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,
@CauseId INT = NULL,
@AreaId INT = NULL
AS
BEGIN

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

IF (@rowsToFetch > 0)
BEGIN
	IF (@fromRow IS NULL)
		SET @fromRow = 1

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

DECLARE @now DATETIME = GETUTCDATE()
DECLARE @fromUTC DATETIME, @toUTC DATETIME
SET @fromUTC = IHDelays2.dbo.IHBoxSystem_ToUTC(@From, @Timezone)
SET @toUTC = IHDelays2.dbo.IHBoxSystem_ToUTC(@To, @Timezone)

DECLARE @daysT TABLE ([Start] DATETIME, [End] DATETIME)


INSERT INTO @daysT
SELECT 
	Start =  IHDelays2.dbo.IHBoxSystem_ToUTC([Timestamp], @Timezone),
	[End] = DATEADD(DAY, 1, IHDelays2.dbo.IHBoxSystem_ToUTC([Timestamp], @Timezone))
FROM IHDelays2.dbo.GetTimeRange(@fromUTC, @toUTC, 'DAY')

SELECT
	@fromUTC = MIN(Start),
	@toUTC = MAX([End])
FROM @daysT

declare @CasterCode int = @OperatingUnitId

SELECT
d.[DelayCodeId], 
[DelayCodeParentId] = dc.ParentDelayCodeId, 
d.Start,
d.[End],
d.[Comments],
[CauseName] = ISNULL((select DelayName from CAS.DelayCodes dc where dc.DelayCodeId = dc.ParentDelayCodeId ), 'NOTDECLARED'),
d.CasterCode
INTO #delays
FROM CAS.Delays d WITH(NOLOCK)
LEFT JOIN CAS.DelayCodes dc ON dc.DelayCodeId = d.DelayCodeId
CROSS APPLY SYSTEM.GetShiftDataForDatetime(d.Start) SD
WHERE
d.[Start] < @toUTC and d.[End] > @fromUTC
		AND (@ShiftId IS NULL OR SD.Id = @ShiftId)
		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)))
            )

SELECT
	[Date] = SUBSTRING(CONVERT(VARCHAR, dateRow.[Start], 121), 1, 10),
	[Category] = MAX(c.Name),
	[Description] = MAX(c.[Description]),
	[Duration] = ISNULL(SUM(ISNULL(DATEDIFF(
					SECOND, 
					CASE WHEN d.[Start] < dateRow.Start THEN dateRow.Start ELSE d.[Start] END,
					ISNULL(CASE WHEN d.[End] > dateRow.[End] THEN dateRow.[End] ELSE d.[End] END, CASE WHEN @now > dateRow.[End] THEN dateRow.[End] ELSE @now END)
				), 0)), 0),
	[CasterCode] = MAX(d.CasterCode)
INTO #temp
FROM @daysT dateRow,
	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 = dc.ParentDelayCodeId--IHDelays2.[dbo].[GetRootParentByDelayCodeId](dc.Id) 
WHERE 
	(
		d.Start BETWEEN dateRow.Start AND dateRow.[End]
		or
		d.[End] BETWEEN dateRow.Start AND dateRow.[End]
	)
	AND dc.isActive = 1
GROUP BY 
	dateRow.[Start], dc.DelayCodeId
UNION ALL
SELECT
	[Date] = SUBSTRING(CONVERT(VARCHAR, dateRow.[Start], 121), 1, 10),
	[Category] = CASE WHEN d.CasterCode = 1 THEN 'C1NotDeclared' ELSE 'C2NotDeclared' END, 
	[Description] = 'Not Declared', 
	[Duration] = ISNULL(SUM(ISNULL(DATEDIFF(
					SECOND, 
					CASE WHEN d.[Start] < dateRow.Start THEN dateRow.Start ELSE d.[Start] END,
					ISNULL(CASE WHEN d.[End] > dateRow.[End] THEN dateRow.[End] ELSE d.[End] END, CASE WHEN @now > dateRow.[End] THEN dateRow.[End] ELSE @now END)
				), 0)), 0),
	[CasterCode] = d.CasterCode
FROM @daysT dateRow,
	#delays d
WHERE 
	(
		d.Start BETWEEN dateRow.Start AND dateRow.[End]
		or
		d.[End] BETWEEN dateRow.Start AND dateRow.[End]
	)
	AND
	d.DelayCodeId IS NULL
GROUP BY 
	dateRow.[Start], d.DelayCodeParentId, d.CasterCode


 --Rank date records specified on the pagination paramaters.
SELECT
	*,
	RowNum = ROW_NUMBER() OVER (ORDER BY [Date] DESC)
INTO #temp2
FROM (SELECT DISTINCT [Date] FROM #temp) AS record


SELECT
	temp1.*
FROM #temp temp1
	JOIN #temp2 record ON record.[Date] = temp1.[Date]
WHERE
	@rowsToFetch = -1
	OR
	(
		record.RowNum >= @fromRow
		AND 
		record.RowNum <= @toRow
	)
ORDER BY 
	record.RowNum ASC,
	[Description] ASC
	

DROP TABLE #delays
DROP TABLE #temp
DROP TABLE #temp2

END

